
Інструкція для користувачів Excel, які рахують ціни, знижки та інші показники й стикаються з довгими формулами. Показуємо типовий сценарій із XLOOKUP, поступове ускладнення, а потім — як спростити все за допомогою LET.
Підготуйте вихідні дані
Приклад базується на двох таблицях: одна з цінами та базовими знижками товарів, друга — із замовленнями клієнтів (товар, кількість, рівень клієнта). Імена таблиць у прикладі: tblProducts і tblOrders.


Крок 1. Отримайте ціну товару через XLOOKUP
- Виділіть клітинку в стовпці, де потрібна ціна (наприклад, у таблиці замовлень).
- Введіть формулу для пошуку ціни за Product ID у таблиці товарів: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price])
- Натисніть Enter, скопіюйте формулу на інші рядки за потреби.


Крок 2. Додайте кількість і стандартну знижку
- У клітинці з формулою розкрийте обчислення: перемножте ціну на кількість і застосуйте знижку з таблиці товарів.
- Введіть формулу: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 — XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Discount Rate]))
- Натисніть Enter. За потреби розтягніть формулу вниз по стовпцю.

Крок 3. Додайте додаткову знижку для Premium-клієнтів
- У формулі додайте перевірку рівня клієнта — Premium отримують додаткові п’ять відсоткових пунктів.
- Введіть формулу: =XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 — (XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Discount Rate]) + IF([@[Customer Tier]]=»Premium», 0.05, 0)))
- Підтвердіть Enter і перевірте розрахунок на кількох рядках.
Крок 4. Приберіть помилки, коли товар не знайдено
- Оточіть обчислення перевіркою: якщо товар не знайдено, повертайте порожній рядок.
- Введіть формулу: =IF(XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Standard Price], «»)=»»,»»,XLOOKUP([@[Product ID]],tblProducts[Product ID],tblProducts[Standard Price]) * [@Quantity] *(1 — (XLOOKUP([@[Product ID]], tblProducts[Product ID], tblProducts[Discount Rate]) +IF([@[Customer Tier]]=»Premium», 0.05, 0))))
- Натисніть Enter. Тепер відсутній Product ID (наприклад, P999) не викликає помилки, а лишає клітинку порожньою.
Крок 5. Зробіть формулу читабельною з LET
На цьому етапі обчислення працює, але має повторювані фрагменти XLOOKUP для ціни й ставки знижки. За даними How-To Geek, саме повтори — головний сигнал, що час навести лад і структуризувати формулу. Щоразу, коли дублюєте один і той самий розрахунок, зростає ризик помилок і ускладнюється підтримка.
- Клацніть у рядку формул і натискайте Alt+Enter, щоб рознести частини формули на кілька рядків. Це не змінює обчислення, але полегшує читання та налагодження.
- Перепишіть формулу з функцією LET: спочатку дайте імена проміжним результатам (наприклад, Price і Discount), а потім використайте ці імена в кінцевому виразі.
- Залиште перевірку на порожню ціну: якщо пошук повертає порожнє значення, фінальний результат теж має бути порожнім.
Приклад даних
Для відтворення прикладу достатньо таких стовпців у двох таблицях:
| tblProducts | |
|---|---|
| Product ID | Standard Price |
| Discount Rate |
| tblOrders | |
|---|---|
| Order ID | Product ID |
| Customer Tier | Quantity |
Поради й типові помилки
- Слідкуйте за повтореннями в довгих формулах. Якщо однакові XLOOKUP або обчислення з’являються кілька разів, це знак перейти на LET і винести ці частини в іменовані змінні.
- Форматуйте формули для читабельності: Alt+Enter у рядку формул переносить частину виразу на новий рядок, що допомагає в пошуку помилок.
- Для відсутніх ідентифікаторів використовуйте аргумент значення-не-знайдено в XLOOKUP (у прикладі — порожній рядок «») разом із IF, щоб уникати помилок у підсумку.
Фото: How-To Geek: Excel



