ДомойСтатьиExcelЯк приборкати довгі формули в Excel за допомогою LET

Як приборкати довгі формули в Excel за допомогою LET

Як приборкати довгі формули в Excel за допомогою LET

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

Підготуйте вихідні дані

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

Таблиця товарів tblProducts
Таблиця товарів tblProducts
Таблиця замовлень tblOrders
Таблиця замовлень tblOrders

Крок 1. Отримайте ціну товару через XLOOKUP

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

Крок 2. Додайте кількість і стандартну знижку

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

Крок 3. Додайте додаткову знижку для Premium-клієнтів

  1. У формулі додайте перевірку рівня клієнта — Premium отримують додаткові п’ять відсоткових пунктів.
  2. Введіть формулу: =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)))
  3. Підтвердіть Enter і перевірте розрахунок на кількох рядках.

Крок 4. Приберіть помилки, коли товар не знайдено

  1. Оточіть обчислення перевіркою: якщо товар не знайдено, повертайте порожній рядок.
  2. Введіть формулу: =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))))
  3. Натисніть Enter. Тепер відсутній Product ID (наприклад, P999) не викликає помилки, а лишає клітинку порожньою.

Крок 5. Зробіть формулу читабельною з LET

На цьому етапі обчислення працює, але має повторювані фрагменти XLOOKUP для ціни й ставки знижки. За даними How-To Geek, саме повтори — головний сигнал, що час навести лад і структуризувати формулу. Щоразу, коли дублюєте один і той самий розрахунок, зростає ризик помилок і ускладнюється підтримка.

  1. Клацніть у рядку формул і натискайте Alt+Enter, щоб рознести частини формули на кілька рядків. Це не змінює обчислення, але полегшує читання та налагодження.
  2. Перепишіть формулу з функцією LET: спочатку дайте імена проміжним результатам (наприклад, Price і Discount), а потім використайте ці імена в кінцевому виразі.
  3. Залиште перевірку на порожню ціну: якщо пошук повертає порожнє значення, фінальний результат теж має бути порожнім.

Приклад даних

Для відтворення прикладу достатньо таких стовпців у двох таблицях:

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

Джерела

- Advertisment -

Як проставити місця в Excel без сортування даних: RANK.EQ, COUNTIFS, IFERROR

Потрібно дізнатися лідерів за показником і не ламати поточне сортування таблиці? Додайте стовпець з рангом у Excel — рядки лишаться на місці, а ви побачите 1‑ше, 2‑ге, 3‑тє місця.

Excel RANDARRAY: як швидко створювати випадкові списки з гнучкими параметрами

Інструкція допоможе зробити у Excel випадкові числа списком — від простого прикладу до гнучкої формули з керуванням довжиною та діапазоном значень. Працює в Excel для Microsoft 365 та інших версіях із підтримкою Spill-функцій.

VLOOKUP (ВПР) в англомовному Excel: покроковий гід, XLOOKUP і альтернативи

Як перекласти ВПР у VLOOKUP і навпаки, чим XLOOKUP краща, як уникати #N/A та що робити у старих версіях Excel — з прикладами і формулами.

Еще записей в тему?
Если честно, некоторые могут быть не свежие:)