Формулы на эксель: как пользоваться, создавать и изменять

Символы в формулах в Google Sheets

Символы, используемые в Google Sheets, идентичны символам, используемым в Excel.

20 расширенных функций и формул Excel (для профессионалов в Excel)

В Excel имеется более 450 функций, которые могут выполнять множество замечательных задач. Если вы использовали Excel хотя бы несколько дней, я уверен, что вы слышали о таких функциях, как ВПР, СУММЕСЛИ, СЧЁТЕСЛИ и так далее.

И что еще более удивительно, так это то, что одна функция Excel — формула, состоящая из двух, трех или более функций.

С помощью комбинации функций вы можете создавать некоторые расширенные формулы Excel, которые могут делать невероятно сложные вещи одним нажатием клавиши.

Так что, если вы такой же поклонник Excel, как и я, я уверен, вам понравится эта статья. В этой статье я расскажу о 20 расширенных функциях Excel, которые вы должны знать.

Я также поделюсь примерами некоторых расширенных формул, которые вы можете создать с помощью этих расширенных функций.

Небольшое примечание о расширенных функциях/формулах. Под расширенными я подразумеваю функции, которые требуют некоторого ноу-хау и обычно не используются базовыми пользователями Excel (такие как СУММ или СЧЁТ).

Итак, приступим!

В этом руководстве рассматриваются:

Функция XLOOKUP

XLOOKUP — король расширенных функций (Microsoft не присвоила это название какой-либо функции, но я уверен, что никто не заслуживает этого больше, чем эта функция).

Это новая функция, доступная в Excel 2021 и Excel для Microsoft 365. Это означает, что люди, использующие более ранние версии Excel, не смогут ее использовать.

XLOOKUP — это усовершенствованная версия VLOOKUP, в которой устранены некоторые недостатки функции VLOOKUP.

Вы можете использовать эту функцию в ситуациях, когда вам нужно просмотреть список значений в строках или столбцах и выяснить, содержит ли список искомое значение (называемое значением поиска).

Это также позволяет найти значение, а затем вернуть любое другое значение из той же строки или столбца.

Если вы все еще не понимаете, что делает эта функция, подумайте вот о чем: вы идете в недавно открывшееся кафе рядом с вашим домом и хотите что-нибудь поесть. так что вы получаете их меню и начинаете просматривать меню сверху вниз.

Когда вы определяете товар, который хотели бы заказать, вы смотрите направо, чтобы проверить цены.

И это то, что XLOOKUP делает для вас. Он может сканировать список, определять положение искомого значения и возвращать значение из любого соответствующего столбца в той же строке.

Есть много удивительных вещей, которые вы можете сделать с помощью XLOOKUP, и я рассказал о некоторых из них в видео ниже:

Примечание : Если вы используете XLOOKUP и делитесь файлом с кем-то, кто использует более старую версию Excel, формулы не будут работать в их книгах. Так что в этом случае лучше не использовать XLOOKUP 9. 0005

Функция ВПР

ВПР была бесспорным королем функций Excel, пока не появилась функция ВПР. Поскольку многие люди до сих пор не используют Excel 2021 или Excel для Microsoft 365, у них нет доступа к XLOOKUP.

Итак, для них ВПР — это функция, которую они должны знать. Для продвинутых пользователей формул полное понимание использования ВПР может действительно изменить их работу.

Как и XLOOKUP, функция VLOOKUP также сканирует список в столбце и может возвращать совпадающее значение поиска или любое значение из любого столбца в той же строке.

Если вы хотите узнать, как лучше всего использовать функцию ВПР, ознакомьтесь с подробным руководством по этой расширенной формуле: 10 примеров ВПР для начинающих и опытных пользователей

Вот несколько примеров расширенных формул использования ВПР:

• Как использовать функцию ВПР с несколькими критериями
• Использование VLookup для получения последнего числа в списке в Excel
• Избегайте вложенных функций ЕСЛИ в Excel… ВПР для спасения
• Используйте ЕСЛИОШИБКА с функцией ВПР, чтобы избавиться от ошибок #Н/Д

Функция ИНДЕКС / ПОИСКПОЗ

Сочетание функций ИНДЕКС и ПОИСКПОЗ может творить чудеса. Поэтому, если вы хотите узнать о расширенных формулах в Excel, вы должны узнать, как работает эта волшебная комбинация.

До появления XLOOKUP многие продвинутые пользователи Excel предпочитали использовать INDEX/MATCH вместо VLOOKUP (и эта комбинация может устранить некоторые недостатки VLOOKUP). Раньше велись жаркие споры о том, какая формула ВПР лучше, чем ИНДЕКС/ПОИСКПОЗ.

Даже с XLOOKUP я настоятельно рекомендую вам учиться с помощью ИНДЕКС/ПОИСКПОЗ.

Хотя по отдельности эти функции не очень полезны, в сочетании они представляют собой мощный инструмент для продвинутых пользователей формул Excel.

Вы можете прочитать следующее руководство, в котором я рассказываю о некоторых основных и расширенных аспектах использования формулы ИНДЕКС/ПОИСКПОЗ – Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel (10 простых примеров) т. е. XLOOKUP, VLOOKUP и INDEX/MATCH), СУММПРОИЗВ — одна из тех функций, которые нравятся продвинутым пользователям Excel.

Его название может ввести вас в заблуждение, заставив думать, что это математическая или статистическая формула, которая может складывать несколько чисел, но это нечто большее.

Первоначально команда Excel хотела, чтобы эта функция выполняла вычисления, где она могла бы дать вам сумму произведения в двух столбцах.

Хотя это достойный вариант использования, пользователи Excel проявили хитрость и обнаружили, что это может быть намного больше.

Опытные пользователи Excel часто используют формулу СУММПРОИЗВ для условных сумм или условных вычислений.

Предположим, у вас есть набор данных о продавцах, их регионе и количестве продаж. Затем вы можете использовать СУММПРОИЗВ для:

• Добавлять только стоимость продаж для определенного региона (или более одного региона)
• Добавлять только значения продаж выше определенного значения
• Добавление значений продаж выше или ниже определенного значения (условие ИЛИ)

Вы можете посмотреть видео ниже, где я показываю примеры СУММПРОИЗВ:

Примечание. Функция СУММПРОИЗВ довольно популярна среди пользователей Excel, которые создают информационные панели и отчеты. В Excel появилась новая функция СУММЕСЛИМН, которая может выполнять часть функций функции СУММПРОИЗВ, но не все. Поэтому, если вы хотите изучить сложные формулы Excel, я бы посоветовал вам изучить и эту.

Функции СУММЕСЛИ/СЧЁТЕСЛИ и СУММЕСЛИМН/СЧЁТЕСЛИМН

Я объединил эти четыре функции вместе, так как они имеют одинаковый принцип действия.

Вы можете использовать это для условного суммирования или подсчета из вашего набора данных.

Например, если у вас есть список имен и вы хотите быстро подсчитать, сколько раз конкретное имя появляется в списке, вы можете использовать формулу СЧЁТЕСЛИ.

Аналогичным образом, если у вас есть набор данных, в котором указаны имена торговых представителей и объемы их продаж, и вы хотите получить сумму всех продаж, совершенных конкретным торговым представителем, вы можете использовать функцию СУММЕСЛИ.

А если вам нужно проверить несколько условий, вы можете использовать формулы СЧЁТЕСЛИМН или СУММЕСЛИМН.

Например, если у меня есть набор данных со столбцом «Дата», столбцом имени торгового представителя и их значениями продаж, я могу использовать формулу СУММЕСЛИМН, чтобы получить сумму всех продаж, совершенных конкретным торговым представителем в определенном месяц.

Поскольку нужно проверить два условия (имя торгового представителя и дату), вам нужно использовать формулу СУММЕСЛИМН. Точно так же, если у вас есть несколько условий для проверки при получении числа в наборе данных, используйте формулу СЧЁТЕСЛИМН.

Функция ФИЛЬТР

ФИЛЬТР — это новая функция в Excel 2021 и Excel для Microsoft 365, и она потрясающая.

Как следует из названия, вы можете использовать его для фильтрации набора данных в Excel и извлечения результатов.

Это была одна из наиболее востребованных функций в Excel, поскольку обходной путь без функции ФИЛЬТР довольно длинный и сложный.

В качестве простого примера, демонстрирующего утилиту функции ФИЛЬТР, предположим, что у вас есть набор данных с именами торговых представителей, их регионами/странами и объемами продаж.

С помощью функции ФИЛЬТР вы можете быстро отфильтровать все записи для любой конкретной страны. И чтобы сделать его еще более мощным, вместо того, чтобы жестко кодировать название страны в формуле, вы можете поместить его в ячейку и ссылаться на эту ячейку в формуле.

Таким образом, вы можете просто изменить название страны в ячейке, и формула мгновенно выдаст вам все записи для этой страны.

Ниже видео, которое я сделал, показывающее некоторые продвинутые примеры функции ФИЛЬТР.

Функция SORT & SORTBY

Опять же, две новые функции в Excel 2021 и Excel для Microsoft 365.

Так же, как и функция ФИЛЬТР, они были очень нужны, поскольку не было простого способа динамической сортировки данных с помощью формулы .

До введения этой формулы большая часть сортировки данных выполнялась с использованием функции сортировки в Excel, которая дает вам статический результат.

Это означает, что если вы измените исходный набор данных, результирующие отсортированные данные не будут обновляться, и вам придется сортировать их снова.

Но не больше.

Используя функцию СОРТИРОВКИ, вы можете быстро отсортировать любой набор данных на основе указанной строки или столбца. Это также дает вам возможность выбирать порядок сортировки (например, по возрастанию или по убыванию).

Итак, если у вас есть набор данных и вы хотите отсортировать эти данные на основе одного определенного столбца, вы можете использовать функцию СОРТИРОВКИ.

Функция SORTBY предоставляет немного больше возможностей при сортировке данных по формуле. С помощью этой функции вы можете сортировать по нескольким столбцам.

Например, если у вас есть 2 столбца, где первый столбец содержит название региона (например, Восток, Запад, Север, Юг), а другой столбец содержит значения продаж, вы можете использовать функцию СОРТИРОВАТЬ ПО сначала отсортируйте это по названию региона, а затем по стоимости продаж.

 Читайте также: Как сортировать по цвету в Excel (менее чем за 10 секунд) 

УНИКАЛЬНАЯ функция

УНИКАЛЬНАЯ Снова новая функция, доступная только пользователям Microsoft 365.

Подобно функциям СОРТИРОВКА и СОРТИРОВКА ПО, эта функция была крайне необходима.

Как следует из названия, функция UNIQUE предоставит вам список уникальных имен или элементов в наборе данных.

До этой функции это можно было сделать с помощью функции удаления дубликатов или функции расширенного фильтра.

Но даже с этими встроенными функциями, с большими наборами данных и большим количеством условий раньше это становилось сложным.

И, конечно же, раньше результат был статическим, а это означало, что в случае изменения исходных данных результирующие данные не будут обновляться, и вам придется повторять процесс заново.

Функция TEXTJOIN

TEXTJOIN — это относительно новая функция, представленная в Excel 2019. Редактирование решает основную проблему.

TEXTJOIN позволяет быстро объединять содержимое выбранного диапазона ячеек без создания формулы конкатенации или использования знака & миллион раз.

Итак, если у вас есть несколько элементов в разных ячейках в строке или столбце, и вы хотите быстро их объединить, вы можете сделать это с помощью одной формулы.

Это также позволяет указать разделитель, например пробел или запятую, чтобы все объединенные ячейки имели этот разделитель между содержимым ячеек.

Честно говоря, это очень простая формула, которую может использовать даже самый простой пользователь Excel, но если вы продвинутый пользователь Excel, вам непростительно использовать конкатенацию и объединение ячеек (вам лучше знать)

 Также читайте: Как объединить имя и фамилию в Excel 

Функция IFS

IFS — еще одна функция, которая была введена в Excel 2019 (слишком поздно, если вы спросите меня).

Мне нравится функция ЕСЛИ, и за эти годы она сделала для меня много тяжелой работы. Но у чувака есть ограничения.

Начнем с того, что вы можете проверить только два условия, поэтому, если вам нужно проверить три или более условий, вы получите длинную вложенную формулу if.

И не дай бог, если придется проверять 5, 10 или 15 условий, я представляю какой ужас будут у ваших формул.

Чтобы уменьшить нагрузку на перегруженную формулу ЕСЛИ, Microsoft представила функцию ЕСЛИ, которая может проверять несколько условий.

Так что, если вы учитель, оценивающий студентов, или менеджер, которому нужно решить, сколько комиссионных получают ваши торговые представители в зависимости от объема их продаж, вам не нужно создавать длинные запутанные формулы ЕСЛИ, вместо этого вы можете использовать IFS функция.

 Читайте также: Тестирование нескольких условий с помощью функции Excel IFS 

Функция ЕСЛИОШИБКА

Вы не можете называть себя продвинутым пользователем формул Excel, если ваш лист Excel полон ошибок, таких как #N/A или #REF! или #ДЕЛ/0!.

Хотя иногда вы не можете контролировать эти ошибки, ЕСЛИОШИБКА позволяет вам обрабатывать эти ошибки, заменяя их чем-то более значимым.

Например, если вы используете формулу ВПР и она не может найти искомое значение, она вернет ошибку NA. с помощью формулы ЕСЛИОШИБКА вы можете заменить эту ошибку NA чем-то более значимым, например, «Искомое значение не найдено» или «Данные недоступны».

 Читайте также: Использование ЕСЛИОШИБКА с ВПР для избавления от ошибок #Н/Д 

Функция СМЕЩ

Функция СМЕЩ может использоваться в очень специфических ситуациях, и большинству продвинутых пользователей Excel редко требуется ее использование.

Функция OFFSET позволяет сместить ссылку на указанное количество строк или столбцов.

Например, я могу использовать функцию OFFSET, чтобы сместить ссылку A1 на две строки и два столбца, чтобы получить C3 (которая является ссылкой на ячейку, расположенную двумя строками ниже и двумя столбцами справа)

Если вам интересно узнать, как работает функция СМЕЩ, у меня есть подробное руководство, в котором я привожу несколько примеров использования формулы СМЕЩ.

Одна важная вещь, которую вам нужно знать о функции OFFSET, это то, что она изменчива, а это означает, что каждый раз, когда в вашем листе происходит изменение, функция будет пересчитываться.

Хотя вы не заметите никаких изменений, если используете функцию OFFSET в нескольких ячейках, но если вы используете внутренний большой набор данных или всю строку или столбец, это может замедлить работу вашего файла Excel.

Функция НАЙТИ / ПОИСК (с подстановочными знаками)

НАЙТИ и ПОИСК — это две похожие функции, которые позволяют вам искать заданный текст в ячейке и возвращать начальную позицию строки, которую вы искали.

Вы не можете утверждать, что являетесь опытным пользователем формул Excel, если не знаете, как использовать все возможности функций НАЙТИ/ПОИСК.

Хотя сами по себе эти функции не очень полезны, в сочетании с другими текстовыми функциями ВЛЕВО/ВПРАВО/СРЕДНЕЕ вы можете выполнять серьезные манипуляции с текстом.

Например, если у вас есть столбец, полный адресов электронной почты, и вы хотите получить идентификатор пользователя, а не все, что находится после символа @, вы можете сделать это с помощью функции НАЙТИ или ПОИСК.

Чтобы сделать это, нам сначала нужно использовать одну из этих функций, чтобы найти позицию добавления скорости в ячейке, и как только вы получите эту позицию, вы можете использовать это значение в левой функции, чтобы извлечь все, что есть слева от символа @.

Обратите внимание, что вы не можете использовать подстановочные знаки в формуле НАЙТИ, но вы можете использовать их в формуле ПОИСК

ВПРАВО / ВЛЕВО / СРЕДНЯЯ Функция

Работа с текстовыми данными является обычной частью повседневной работы многих продвинутых пользователей Excel.

Несмотря на то, что в Excel доступно множество вариантов работы с текстом (например, «Текст в столбцы» или «Power Query»), многие опытные пользователи Excel по-прежнему предпочитают использовать формулу.

Функции RIGHT, LEFT и MID позволяют извлечь часть текстовой строки.

Например, если у вас есть идентификатор электронной почты, такой как [email protected], вы можете использовать функцию ВЛЕВО, чтобы извлечь только имя пользователя из этого идентификатора электронной почты, или вы можете использовать функцию ПРАВО, чтобы извлечь домен имя (которое будет email.com).

С помощью функции MID вы можете извлечь текст из середины текстовой строки. Например, если я хочу извлечь доменное имя между @ и . (точка), я могу легко сделать это с помощью функции MID.

Эти текстовые функции не очень полезны сами по себе, но когда вы комбинируете их с другими формулами, такими как ДЛСТР или НАЙТИ, вы можете делать с ними невероятно сложные вещи.

Вот несколько расширенных примеров формул с использованием функций ВПРАВО/ВЛЕВО/СРЕДНЯЯ:

• Удаление символов слева в Excel (простые формулы)
• Извлечение фамилии в Excel (5 простых способов)
• Как извлечь первое слово из текстовой строки в Excel (3 простых способа)
• Как извлечь подстроку в Excel (с помощью формул TEXT)

ЗАМЕНИТЬ / ПОДСТАВИТЬ Функция

ЗАМЕНИТЬ и ПОДСТАВИТЬ, как следует из названия, позволяет вам найти какой-либо текст в свободном тексте и заменить или заменить его тем, что вы хотите.

С помощью функции REPLACE вы можете указать начальную позицию и общее количество символов, которые вы хотите заменить указанной текстовой строкой.

С другой стороны, с помощью функции ПОДСТАВИТЬ вы можете указать текстовую строку, которую вы хотите заменить другой текстовой строкой. Например, если у вас есть набор данных, в котором вы хотите заменить слово «Private» на «Pvt», вы можете легко сделать это с помощью функции ПОДСТАВИТЬ.

Хотя эти функции весьма полезны сами по себе, они часто используются с другими текстовыми функциями, такими как LEN, TRIM или RIGHT/LEFT/MID

Функция IMAGE

IMAGE — это совершенно новая функция в Microsoft 365 ( по крайней мере на момент написания этой статьи).

Долгое время я хотел, чтобы в Excel была функция, позволяющая использовать URL-адрес и вставлять изображение с этого URL-адреса в ячейку Excel.

Наконец-то мое желание сбылось.

С помощью функции ИЗОБРАЖЕНИЕ вы можете использовать URL-адрес изображения в ячейке, и оно будет вставлять изображение в саму ячейку.

Раньше, чтобы поместить изображение в ячейку, нужно было вручную добавить изображение на рабочий лист, а затем расположить его так, чтобы оно помещалось в ячейку.

Хотя я не думаю, что многие обычные пользователи Excel будут использовать эту функцию, некоторые опытные пользователи Excel могут использовать ее при создании информационных панелей.

Например, вы можете создать шаблон, в котором функция извлекает изображение продукта или логотип компании на основе URL-адреса.

Рискуя показаться нелояльным по отношению к Excel, мы должны поблагодарить Google Sheets за это. Функция ИЗОБРАЖЕНИЕ уже много лет является частью Google Таблиц, и многие пользователи Excel (такие как я) просили хорошо добавить ее в Excel. Итак, в интересах человечества Excel скопировал эту функцию из Google Sheets (и ура, что они сделали)

МАЛЕНЬКИЙ / БОЛЬШОЙ Функция

МАЛЕНЬКИЙ и БОЛЬШОЙ — это две простые формулы, которые делают одну простую вещь — дают вам K-е наименьшее или наибольшее значение из набора данных.

Например, я могу использовать функцию НАИМЕНЬШИЙ, чтобы получить второе или третье наименьшее значение из диапазона ячеек.

И зачем включать эти две формулы в мой лучший учебник по продвинутым формулам, потому что я использовал их в ситуациях, когда никакая другая функция не могла сделать то, что они могли сделать.

Например, я использовал их для управления выбросами в своем наборе данных, использую их с формулами, которые возвращают массив (например, функция ИНДЕКС), и получаю из него первые три или пять первых значений.

Я согласен с тем, что эти две формулы имеют ограниченное применение в расширенных формулах Excel, но я все же призываю вас держать их в заднем кармане на случай, если они вам понадобятся.

 Читайте также: Как найти выбросы в Excel (и как с ними справиться) 

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ

ПОСЛЕДОВАТЕЛЬНОСТЬ — это также новая функция, доступная в Excel 2021 и Excel с Microsoft 365.

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ заполнит диапазон ячеек последовательностью чисел. вы можете указать, сколько строк и столбцов вы хотите заполнить, начальный номер и шаг между каждым номером.

Например, если я введу приведенную ниже формулу в ячейку A1, она заполнит ячейки от A1 до A10 последовательностью чисел, начиная с 1 до 10.

 =ПОСЛЕДОВАТЕЛЬНОСТЬ(10,1,1,1) 

Хотя ранее в Excel были и другие способы сделать это (например, вручную ввести данные, а затем использовать дескриптор заполнения или метод заполнения ряда), наличие формулы делает это не только более удобным, но и необходимым в некоторых ситуациях.

Один из расширенных вариантов использования этой функции может быть полезен при создании календаря, в котором вы хотите заполнить сетку ячеек последовательностью чисел.

Вы можете проверить шаблон календаря Excel, который я создал с помощью этой функции.

Но что более важно, я часто сталкивался с ситуациями, когда мне нужна была последовательность чисел как часть моей формулы. Раньше мне приходилось полагаться на такие хитрости, как использование формул СТРОКИ или СТОЛБЦЫ, теперь я могу легко сделать это с помощью функции ПОСЛЕДОВАТЕЛЬНОСТЬ.

 Читайте также: 7 быстрых и простых способов пронумеровать строки в Excel Когда я начал использовать Excel и изучал эти формулы, я думал о них как о еще одной формуле, которая может быть полезна в некоторых ситуациях. 
 
 Но чем больше я узнаю об этих функциях, тем больше я верю, что вам нужно знать, как правильно их использовать, чтобы называться продвинутым пользователем формул. 
 
 Сначала эти формулы делают простые вещи: 
 
 
 РАБДЕНЬ — вычисляет дату после указанного количества рабочих дней 
 
 ЧИСТРАБДНИ — общее количество рабочих дней между двумя заданными датами 
 
 
 А так как у нас в неделе есть рабочие и нерабочие дни (как и праздничные дни), то эти функции автоматически учитывают выходные дни, а также можно указать праздничные дни, которые будут игнорироваться. 
 
 И поскольку этого было недостаточно, поскольку у многих людей выходные дни отличались от субботы и воскресенья, Excel выпустил две дополнительные функции: РАБДЕНЬ. МЕЖД и ЧИСТРАБДНИ.МЕЖД. 
 
 Эти международные функции позволяли пользователям выбирать дни, которые будут считаться выходными. 
 
 Но подождите, это еще не все. 
 
 Что делать, если вы работали неполный рабочий день и хотите рассчитать данные после определенного количества рабочих дней или общее количество рабочих дней между двумя заданными датами? 
 
 Вы тоже можете это сделать. 
 
 Международные версии этих функций позволяют указать все рабочие дни и нерабочие дни и тогда он будет делать расчет на их основе. 
 
 Например, вы можете указать, что у вас рабочие только понедельник и четверг, а все остальные дни нерабочие. 
 
 Также читайте: Как рассчитать количество дней между двумя датами в Excel 
 Функция TEXTAFTER / TEXTBEFORE 
 Хотя в Excel уже было несколько действительно удивительных текстовых функций, они добавили TEXTBEFORE и TEXTAFTER в версию Excel для Microsoft 365, чтобы сделать вещи еще проще.
 Раньше, если нужно было извлечь имя из полного имени, приходилось использовать комбинацию функции НАЛЕВО с функцией НАЙТИ.