Практически у каждого пользователя Excel есть одна функция, на которую он полагается ещё со студенческих лет, потому что она достаточно хорошо работает, чтобы не изучать что-то новое. Вот почему вы найдёте функции VLOOKUP и CONCATENATE в совершенно новых файлах, даже в 2026 году. Я это понимаю, потому что сам до сих пор сомневаюсь... Отказ от функции «Промежуточный итог» в пользу функции «Совокупный итог». После того, как ты долгое время меня поддерживала.
Однако Excel эволюционировал, и многие формулы, которые мы изучали много лет назад, теперь стали сложнее, менее надежными или труднее в обслуживании, чем должны быть. Если ваши формулы выглядят так же, как и раньше, а электронные таблицы кажутся тяжелее, чем должны быть, это может быть признаком того, что вам нужно обновить программу.
Быстрые ссылки
Функция XLOOKUP полностью заменяет функции VLOOKUP и HLOOKUP.
Единая функция, работающая в любом направлении.
Функции VLOOKUP и HLOOKUP — это, по сути, первый набор поисковых функций в Excel. Они работают, они знакомы и повсеместно используются. Проблема в том, что они слишком жёсткие, и это становится всё более раздражающим по мере роста ваших электронных таблиц. Даже такая простая вещь, как вставка столбца в середину таблицы, может испортить результаты, поскольку обе функции используют закодированные номера индексов столбцов или строк.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Кроме того, функция VLOOKUP не может искать слева от ключевого столбца, что вынуждает вас переупорядочивать данные или создавать вспомогательные столбцы, чтобы обеспечить работу базового поиска. Функция XLOOKUP решает все эти проблемы сразу. Вместо того чтобы указывать на большую таблицу и подсчитывать столбцы или строки, вы точно указываете Excel, где искать и что вы хотите получить в результате:
=XLOOKUP(искомое_значение, искомый_массив, возвращаемый_массив, [if_not_found], [match_mode], [search_mode])
Такой подход означает, что ваши формулы не сломаются только потому, что кто-то добавит столбец или изменит порядок полей в таблице. Функция XLOOKUP также работает слева направо, справа налево и сверху вниз, что делает ее более гибкой, когда ваши данные не размещены в традиционной, аккуратной таблице поиска.
Ещё одно важное обновление заключается в том, что функция XLOOKUP теперь включает аргумент if_not_found, поэтому вам больше не нужна функция IFERROR, чтобы избежать некрасивых сообщений #N/A. Вот два примера синтаксиса, демонстрирующие разницу:
=IFERROR(VLOOKUP(B2, C2:E7, 4, TRUE)" ") =XLOOKUP(B2, C2:E7, D2:D7, "Не найдено")
Первая формула ищет значение B2 в первом столбце диапазона C2:E7 и возвращает значение из четвертого столбца, а вторая формула ищет значение B2 в диапазоне C2:E7 и возвращает соответствующее значение из диапазона D2:D7. Функция XLOOKUP по умолчанию использует точные совпадения, что снижает риск случайного получения приблизительных результатов из несортированного списка, а также обрабатывает пропущенные значения, не заставляя вас включать каждый поиск в функцию IFERROR.
XMATCH — это более эффективная версия MATCH.
Точный контроль над типами совпадений и поисковыми трендами.
Весьма вероятно, что вы до сих пор используете MATCH для развития мышечной памяти. Эта функция всегда отлично помогала находить выгодную позицию в диапазоне, особенно когда... MATCH сочетается с INDEXПроблема в том, что функцию MATCH легко использовать неправильно. Если вы забудете указать точный тип совпадения, Excel может отобразить наиболее близкий результат, основываясь на предположениях о сортировке, которые на самом деле не применимы к вашим данным.
=ПОИСКПОЗ(искомое_значение; искомый_массив; [тип_соответствия])
Поскольку этот тип ошибки часто приводит к, казалось бы, разумному результату, её трудно обнаружить и исправить. С помощью XMATCH ваши формулы по умолчанию точно совпадают, что в подавляющем большинстве случаев и нужно большинству пользователей.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Если вы пишете =MATCH(25, A1:A3, 0) По привычке полезно переключиться на =XMATCH(25, A1:A3, 1) Воспользуйтесь этим улучшением. В дополнение к более безопасным настройкам по умолчанию, XMATCH также позволяет управлять направлением поиска. Он может искать с конца списка, а не только с начала, что особенно полезно, когда вам нужна последняя запись или последнее вхождение значения без добавления вспомогательных столбцов.
=XMATCH(E3, C3:C100, 0, -1)
Функция MATCH, конечно, по-прежнему пригодна для использования, но на данном этапе это было бы все равно что выбирать что-то, что просто работает, когда рядом есть более эффективный вариант.
Динамические формулы FILTER, UNIQUE и SUM заменяют собой революционные разработки SUMPRODUCT.
Упрощает чтение, исправление и поддержание работоспособности с течением времени.
Используя функцию SUMPRODUCT, вы можете выполнять многокритериальные вычисления, взвешенные суммы и логику на основе матриц в одной формуле. Именно эта гибкость делает её популярным альтернативным решением на протяжении многих лет. Недостатком является то, что формулы SUMPRODUCT часто выглядят как код, что затрудняет их чтение, отладку и поддержку в долгосрочной перспективе. Теперь, когда они... Встроенная поддержка динамических массивов в ExcelДля многих задач, для решения которых мы раньше использовали функцию SUMPRODUCT, больше не требуются такие сложные и непонятные формулы.
Благодаря динамическим массивам в современном Excel стандартные функции, такие как SUM, теперь могут напрямую обрабатывать вычисления с массивами. Если сравнить два метода, то новый подход обычно оказывается более понятным и простым для восприятия:
| Арифметический процесс | SUMPRODUCT | SUM |
|---|---|---|
| простое сложение | =SUMPRODUCT($A$20:$A$50) | =Сумма($A$20:$A$50) |
| Классическая взвешенная комбинация | =SUMPRODUCT(A2A5, C2:C5) | =Сумма(A2:A5 * C2:C5) |
| Многокритериальная агрегация | =SUMPRODUCT((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) | =SUM((A2:A9=”East”)*(B2:B9=”Cherries”)*C2:C9) |
Кроме того, благодаря новым функциям, таким как FILTER и UNIQUE, вместо создания одной длинной, трудночитаемой формулы, вы можете разбить задачу на более мелкие, понятные шаги. Вы можете отфильтровать интересующие вас данные, при необходимости извлечь уникальные значения, а затем обобщить результат. Логика остается той же, но назначение формулы становится гораздо понятнее для любого, кому придется читать или поддерживать ее в дальнейшем.
Например, если в столбце B у вас есть список продаж, а в столбце A — названия товаров, и вы хотите собрать данные о продажах конкретного товара, например, «Яблоки», вы можете написать одну из следующих двух формул:
=SUMPRODUCT((A2:A10="Apples")*(B2:B10)) =SUM(FILTER(B2:B10, A2:A10="Apples", 0))
В версии с функцией SUMPRODUCT Excel создает массив значений TRUE и FALSE для проверки наличия слова «Apples», преобразует их в 1 и 0 и умножает на объем продаж. Все значения, кроме «Apples», обнуляются и исключаются из итоговой суммы. Во второй формуле функция FILTER просто возвращает данные о продажах, где товаром являются «Apples», и добавляет SUM к этому уже меньшему и более понятному списку. Результат тот же, но второй подход значительно упрощает понимание назначения формулы с первого взгляда.
Функция SUMPRODUCT может показаться удобной, поскольку её структуры array1 и array2 легко модифицировать при экспериментировании. Однако для больших наборов данных, содержащих сотни тысяч или миллионы строк, она может быть значительно медленнее, чем более доступные современные альтернативы. Помимо повышения производительности, новые функции предлагают более понятные формулы, которые легче проверять и которые менее пугающи для следующего пользователя, открывающего вашу электронную таблицу.
IFS и SWITCH устраняют необходимость в вложенных форматах IF.
Более понятная логика без бесконечных скобок.
Технически, вложенные операторы IF работают, но быстро превращаются в кошмар — их трудно читать и поддерживать, особенно если вы не писали их сами. В IFS вместо вложенных операторов IF внутри операторов IF внутри операторов IF, вы перечисляете условия и результаты по порядку, благодаря чему формула выглядит как простая логика: если это, то это; если это что-то другое, то что-то другое. SWITCH идет еще дальше, сравнивая одно значение с несколькими фиксированными вероятностями; это чище, читабельнее и включает опцию по умолчанию, поэтому вам не нужно добавлять дополнительную обработку ошибок в конце.
=IF(test1, result1, IF(test2, result2, IF(test3, result3, ...))) =IFS(logical_test1, value_if_true1, logical_test2, value_if_true2, ...) =SWITCH(expression, value1, result1, value2, result2, ... [default])
Например, если вы хотите присвоить результаты баллы, вы можете использовать вложенные операторы IF, но IFS выражает ту же логику более наглядно:
=IF(A1>90, “A”, IF(A1>80, “B”, IF(A1>70, “C”, “F”))) =IFS(A1>90, “A”, A1>80, “B”, A1>70, “C”, TRUE, “F”)
Значение TRUE в конце формулы IFS действует как глобальное условие, указывая Excel, что нужно вернуть, если ни одно из предыдущих условий не выполняется. В этом случае любой результат ниже 70 преобразуется в «F» без необходимости повторного вложения IF. Функция SWITCH обрабатывает несколько иной шаблон, например, присваивает числовые коды разделам, и включает аргумент по умолчанию в конце, поэтому она не требует решения с использованием значения TRUE.
=SWITCH(A1, 101, “Продажи”, 102, “Маркетинг”, “Другое”)
Если ячейка A1 содержит значение 101, Excel вернет значение «Продажи». Если она содержит значение 102, Excel вернет значение «Маркетинг». Если она содержит любое другое значение, Excel вернет значение «Другое». Логика останется понятной даже по мере увеличения списка случаев.
TEXTJOIN и CONCAT полностью заменяют CONCATENATE.
Объединяйте диапазоны, а не только отдельные ячейки.
Функция CONCATENATE по сути является музейным экспонатом. Вы все еще будете встречать ее во многих электронных таблицах, но она была официально заменена, и на то есть веские причины. Она плохо работает с полными диапазонами, поскольку вам приходится вручную ссылаться на каждую ячейку, и в ней отсутствует встроенное понятие разделителя.
CONCAT, его современная альтернатива, работает практически так же, но добавляет поддержку ссылок на полные домены:
=CONCAT(text1, ...)
Вместо того чтобы вручную выделять каждую ячейку, вы можете ввести формулу, подобную этой, и позволить Excel сделать все остальное:
=CONCAT(B2:C8)
Если вам нужно указать разделитель, например запятую или пробел, и дать указание Excel игнорировать пустые ячейки, то TEXTJOIN — лучший инструмент для этой задачи:
=TEXTJOIN(delimiter, ignore_empty, text1, ...) =TEXTJOIN(", ", TRUE, B2:C8)Первый аргумент — это разделитель, который может быть любым необходимым, а второй аргумент указывает Excel, следует ли пропускать пустые ячейки. Это особенно полезно при создании читаемых списков, написании SQL-запросов или компиляции форматированного текста из столбца или диапазона данных.
Пора отказаться от старых привычек работы с Excel.
Хорошая новость в том, что ни одно из этих обновлений не потребует от вас заново изучать Excel. В большинстве случаев новые функции на самом деле проще, чем те, которые они заменяют; просто к ним нужно немного привыкнуть.
Если на этой неделе вы обновляете только одну привычку, начните с функции, которую используете чаще всего, и замените её современным аналогом. Затем попробуйте переписать небольшой раздел рабочей книги, используя эти пять замен, и посмотрите, насколько проще станут ваши формулы.
