Формулы в Excel - это уже язык программирования. Когда вы строите модель в Excel, вы, по сути, пишете программу для вычисления набора выходных данных из набора входных данных. Команда Calc Редмонд работала над несколькими улучшениями языка формул Excel, чтобы сделать Excel больше похожим на язык программирования. Первая из них, функция LET, сейчас находится в стадии бета-тестирования. Любой, кто выбирает канал предварительной оценки Office 365, должен иметь доступ к LET.
Иногда вы создаете формулу, которая должна снова и снова ссылаться на один и тот же подсчет. Функция LET позволяет вам определять переменную и расчет для этой переменной. В вашем расчете может быть до 126 переменных. Каждая переменная может повторно использовать вычисления в предыдущих переменных. Последний аргумент функции LET - это формула, которая возвращает значение (или массив) в ячейку. Эта окончательная формула будет относиться к переменным, определенным ранее в функции LET.
Это легче всего увидеть, если я покажу вам пример. Я случайно нашел формулу, опубликованную на доске сообщений в 2010 году. Эта формула от члена Special-K99 предназначена для поиска предпоследнего слова во фразе.
Если бы я создавал исходную формулу поэтапно, я бы построил ее поэтапно.
-
Шаг 1: В B4 возьмите ОБРЕЗКУ исходной фразы, чтобы избавиться от повторяющихся пробелов.
Функция TRIM, чтобы избавиться от повторяющихся пробелов. -
Шаг 2: Определите, сколько слов в B4, сравнив LEN обрезанного текста с длиной обрезанного текста после удаления пробелов с помощью SUBSTITUTE. Во фразе из четырех слов есть три пробела. В текущей задаче вы хотите найти второе слово, отсюда минус в конце этой формулы.
Функции LEN и SUBSTITUTE для подсчета слов -
Шаг 3: Добавьте карат (^) перед желаемым словом. Здесь снова используется SUBSTITUTE, но используется третий аргумент в SUBSTITUTE, чтобы найти второй пробел. Это не всегда будет второй пробел. Вы должны использовать результат шага 2 в качестве третьего аргумента шага 3.
Использование каратов в ЗАМЕЩЕНИИ -
Шаг 4: Выделите все слова после карата с помощью MID и FIND.
Выделите все слова после карата с помощью MID и FIND -
Шаг 5: Изолируйте предпоследнее слово, используя MID и снова FIND.
Изолируйте предпоследнее слово с помощью 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% быстрее, чем аналогичная мегаформула.
