Формули в Excel - це вже мова програмування. Створюючи модель у Excel, ви по суті пишете програму для обчислення набору результатів з набору входів. Команда Calc, Редмонд, працювала над кількома вдосконаленнями мови формул Excel, щоб зробити Excel більш схожим на мову програмування. Перша з них, функція LET тепер вийшла в бета-версії. Той, хто приєднається до каналу Insider Fast Office 365, повинен мати доступ до LET.
Іноді ви будуєте формулу, яка повинна посилатися на той самий підрахунок знову і знову. Функція LET дозволяє визначити змінну та обчислити цю змінну. Ваш розрахунок може містити до 126 змінних. Кожна змінна може повторно використовувати обчислення в попередніх змінних. Кінцевим аргументом у функції LET є формула, яка повертає значення (або масив) до комірки. Ця остаточна формула буде посилатися на змінні, визначені раніше у функції LET.
Це найпростіше побачити, якщо я покажу вам приклад. Я випадково знайшов формулу, опубліковану на Дошці повідомлень у 2010 році. Ця формула від учасника Special-K99 призначена для пошуку наступного до останнього слова у фразі.
Якби я будував поетапну формулу оригінальної формули, то будував би її поетапно.
-
Крок 1: У B4 візьміть TRIM оригінальної фрази, щоб позбутися повторення пробілів.
Функція TRIM для позбавлення від повторюваних пробілів. -
Крок 2: З’ясуйте, скільки слів є в B4, порівнявши LEN обрізаного тексту з довжиною обрізаного тексту після видалення пробілів за допомогою SUBSTITUTE. У фразі з чотирьох слів є три пробіли. У поточній задачі ви хочете знайти друге слово, отже, мінус один у кінці цієї формули.
Функції LEN і SUBSTITUTE підраховують слова -
Крок 3: Додайте карат (^) перед потрібним словом. Це знову використовує SUBSTITUTE, але використовує третій аргумент у SUBSTITUTE, щоб знайти 2 пробіл. Це не завжди буде другий пробіл. Ви повинні використовувати результат з кроку 2 як третій аргумент на кроці 3.
Використання карата в ЗАМІННИЦІ -
Крок 4: Ізолюйте всі слова після карата, використовуючи MID і FIND.
Виділіть усі слова після карата, використовуючи MID і FIND -
Крок 5: Ізолюйте наступне до останнього слова, використовуючи MID і ЗНАЙТИ знову.
Виділіть наступне до останнього слова, використовуючи MID і FIND
Розбивши на невеликі розрахунки, як показано вище, багато людей можуть слідувати логіці розрахунку. Я часто будую формули, використовуючи метод, показаний вище.
Але я не хочу брати п’ять стовпців для однієї формули, тому я починаю об’єднувати ці п’ять формул в одну формулу. Формула в F4 використовує E4 двічі. Скопіюйте все в рядку формул для E4 після знаку рівності. Використовуйте пасту, щоб замінити E4 в обох місцях. Продовжуйте замінювати посилання на клітинки їхніми формулами, поки єдине, на що посилається кінцева формула, це клітинка A4. На даний момент у вас є шалено довга формула:
Чому це так бентежить? Як п’ять формул із середньою довжиною 24 символи перетворились на формулу з 370 символів? Це тому, що проста формула в B4 у кінцевій формулі посилається загалом на 12 разів. Якщо ви не зберегли = TRIM (A4) у комірці B4, то в підсумку ви введете TRIM (A4) дванадцять разів у остаточну формулу.
Ось, скільки разів кожна з підформул використовується у кінцевій формулі.
НЕХАЙТЕ на порятунок
Функція LET дозволяє визначити змінні один раз у формулі та повторно використовувати ці змінні пізніше у формулі. На малюнку нижче, чотири змінні визначені у визначеннях змінних, перш ніж остаточне обчислення поверне останнє слово.
Здається, найкращою практикою тут є використання Alt + Enter після кожного визначення змінної у формулі. Хоча ваші формули можуть бути A, B, C і D, не завадить використовувати значущі імена змінних, як це робили б у будь-якій мові програмування.
Зверніть увагу на малюнку вище, що після визначення TRIMTEXT як = TRIM (A4) змінна TRIMTEXT повторно використовується у визначенні WhichSpace та CaratText.
Переглянути відео
Ви можете переглянути кроки для об’єднання підформул у мегаформулу та функцію LET тут:
В інших тестуваннях формула LET обчислює приблизно на 65% швидше, ніж аналогічна мегаформула.
