5 Полезных Расширенных Формул Excel, Которые Вы Должны Знать

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

Содержание

• 1. Поиск значений в вашем листе: XLOOKUP
• 2. Альтернативный поиск значений в листе: INDEX и MATCH
• 3. Суммировать или считать с условиями: SUMIF, SUMIFS, COUNTIF и COUNTIFS
• 4. Тестирование данных с различными условиями: IFS
• 5. Фильтрация с использованием нескольких критериев: FILTER
• Часто задаваемые вопросы

1. Поиск значений в вашем листе: XLOOKUP

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

Примечание: на момент написания XLOOKUP доступен только для подписчиков Microsoft 365. Для других версий Excel ознакомьтесь с комбинацией INDEX и MATCH в следующем разделе.

Синтаксис функции:

XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)

Только первые три аргумента являются обязательными. Ниже приведено описание каждого аргумента:

• Lookup_value: значение для поиска.
• Lookup_range: диапазон, содержащий значение для поиска.
• Return_range: диапазон, содержащий значение для возврата.
• Not_found: текст для возврата, если значение не найдено. “#N/A” — это значение по умолчанию, если оно опущено.
• Match_mode: тип совпадения, используя “0” для точного совпадения и “#N/A” для отсутствия (по умолчанию, если опущено), “1” для точного совпадения и следующего меньшего элемента, если ничего не найдено, “-1” для точного совпадения и следующего большего элемента, если ничего не найдено, или “2” для совпадения с подстановочным знаком, используя вопросительный знак, звездочку или тильду.
• Search_mode: режим поиска, используя “1” для начала с первого элемента (по умолчанию, если опущено), “-1” для начала с последнего элемента, “2”, когда “lookup_range” отсортирован по возрастанию, или “-2”, когда “lookup_range” отсортирован по убыванию.

Например, мы ищем продажи для сектора 2, поэтому мы используем следующую формулу:

=XLOOKUP(2,A19:A24,D19:D24)

Разбирая формулу, 2 — это “lookup_value”, A19:A24 — это “lookup_range”, а D19:D24 — это аргумент “return_range”.

Результат, 74,000, является правильным соответствующим значением для сектора 2.

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

Мы заменяем “lookup_value” 2 на ячейку F19:

=XLOOKUP(F19,A19:A24,D19:D24)

Когда мы вводим 2 в ячейку F19, мы получаем результат 74,000, а если мы вводим другое значение, например 5, мы автоматически получаем обновленный результат, который составляет 61,000.

Вы можете включить аргумент “not_found”, чтобы вам не пришлось смотреть на сообщение об ошибке, если совпадение не найдено. Мы добавляем “No dice” для аргумента.

=XLOOKUP(F19,A19:A24,D19:D24,"No dice")

Когда значение введено в ячейку F19, которое не имеет совпадения, вы увидите ваше сообщение вместо “#NA”.

2. Альтернативный поиск значений в листе: INDEX и MATCH

Хотя XLOOKUP предоставляет вам надежный способ поиска значений, в настоящее время он доступен только для подписчиков Microsoft 365 Excel. Если вы используете другую версию Excel, вы можете использовать INDEX и MATCH для выполнения той же задачи.

С помощью функции INDEX значение в ячейке возвращается на основе позиции, которую вы вводите в формулу. Синтаксис для INDEX:

INDEX(range, row_number, column_number)

С помощью функции MATCH возвращается позиция для значения, которое вы вводите в формулу. Синтаксис для MATCH:

MATCH(value, range, match_type)

Чтобы объединить эти две функции и их формулы, поместите формулу MATCH внутри формулы INDEX в качестве позиции поиска (“row_number” и “column_number”).

Используя те же данные, что и в примере XLOOKUP выше, мы хотим увидеть продажи для сектора, который мы вводим в ячейку F19. Формула:

=INDEX(D19:D24,MATCH(F19,A19:A24))

Чтобы разобрать эту формулу, мы начинаем с INDEX и его аргумента “range”, который равен D19:D24. Это диапазон, содержащий результат, который мы хотим.

Формула MATCH использует F19 в качестве аргумента “value” и A19:A24 в качестве аргумента “range”, содержащего это значение.

Когда мы вводим 4 в ячейку F19, мы получаем результат 75,000, что является правильным значением.

Если мы вводим другой сектор в ячейку F19, например 6, формула автоматически обновляется, чтобы дать правильное значение 58,000.

Совет: попробуйте вводить функции в ячейку, нажав Shift + F3. Меню “Вставить функцию” позволяет вам искать и выбирать функции. Ознакомьтесь с нашей шпаргалкой по горячим клавишам Excel для получения дополнительных советов по экономии времени.

3. Суммировать или считать с условиями: SUMIF, SUMIFS, COUNTIF и COUNTIFS

Функции SUM и COUNT в Excel предназначены для сложения чисел и подсчета ячеек, но они ограничены простыми вычислениями. Вариации этих функций позволяют вам суммировать или считать с использованием критериев. Например, вы можете суммировать только числа, большие 12, или считать только ячейки, содержащие имя Билла.

С помощью SUMIF и COUNTIF вы можете суммировать или считать с одним условием, а с помощью SUMIFS и COUNTIFS вы можете суммировать или считать с несколькими условиями. Ниже приведен синтаксис для каждой:

SUMIF

SUMIF(range, criteria, sum_range)

COUNTIF

COUNTIF(range, condition)

SUMIFS

SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2,…)

COUNTIFS

COUNTIFS(condition_range1, condition1, condition_range2, condition2,…)

Смотрите ниже пример каждой функции.

Чтобы сложить числа в нашем диапазоне B2 через B7 только для тех чисел, которые больше 12, мы используем функцию SUMIF и эту формулу:

=SUMIF(B2:B7,">12")

Результат равен 31, так как формула сложила 16 и 15, единственные числа, превышающие 12 в наборе данных.

В следующем примере мы считаем ячейки со значениями меньше 12 в том же диапазоне, B2 через B7, с помощью следующей формулы:

=COUNTIF(B2:B7,"<12")

Результат для этой формулы равен 4, так как это количество ячеек в диапазоне со значениями меньше 12.

Давайте поднимем планку с SUMIFS и используем два условия. Чтобы сложить числа в ячейках B2 через B7 только для тех, кто подписался на нашу рассылку (Y) в ячейках C2 через C7 и чей город — Сан-Диего в ячейках D2 через D7, мы используем следующую формулу:

=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")

Результат для этой формулы равен 19, так как только два клиента имеют Y для рассылки и Сан-Диего для города. Билл Браун имеет 11 предметов, а Сью Смит — 8, всего 19.

Для примера с использованием функции COUNTIFS мы считаем количество ячеек с теми же двумя условиями, описанными выше. Мы считаем количество клиентов, которые подписались на рассылку (Y) и чей город — Сан-Диего.

=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")

Как и ожидалось, результат равен 2 для Билла Брауна и Сью Смит, единственных подписчиков на рассылку в Сан-Диего.

4. Тестирование данных с различными условиями: IFS

Функция IF в Excel — это мощный инструмент для тестирования данных на соответствие условиям. Например, вы можете использовать ее для отображения буквенной оценки для числовой оценки студента или “Да”, если продавец приносит достаточно дохода, чтобы заработать свой бонус, и “Нет”, если нет.

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

Похожие на SUMIFS и COUNTIFS выше, IFS позволяет вам добавлять несколько критериев в вашу формулу в ясной и удобочитаемой форме.

Синтаксис: IFS(test1, if_test1_true, test2, if_test2_true, …), где вы можете протестировать до 127 различных критериев.

Используя один из наших примеров, мы начинаем просто, чтобы указать бонусы. Если сумма в ячейке B2 превышает 20,000, отображается “Да”. Если нет, отображается “Нет”. Формула становится:

=IFS(B2>20000,"Да",B2<20000,"Нет")

В первом тесте, если значение в B2 больше 20,000, результат, если это правда, равен “Да”. Во втором тесте, если значение в B2 меньше 20,000, результат, если это правда, равен “Нет”.

К счастью, наш продавец получает свой бонус. Так как его продажи составляют 21,000, в колонке Бонус появляется “Да”.

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

Для еще одного примера IFS у нас есть наш список менеджеров, дежурящих в каждый день недели. Когда текущий день отображается в ячейке D2, соответствующее имя менеджера отображается в ячейке E2. Формула становится:

=IFS(D2="Понедельник",B2,D2="Вторник",B3,D2="Среда",B4,D2="Четверг",B5,D2="Пятница",B6)

Это показывает, что если значение в ячейке D2 — Понедельник, отображается имя в ячейке B2, если значение в ячейке D2 — Вторник, отображается имя в ячейке B3, если значение в ячейке D2 — Среда, отображается имя в ячейке B3, и так далее.

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

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

5. Фильтрация с использованием нескольких критериев: FILTER

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

Синтаксис функции: FILTER(range, range=criteria, if_empty), где только первые два аргумента обязательны для набора данных плюс критерии и его контейнерный диапазон. Третий аргумент полезен, если вы хотите вернуть что-то конкретное, если формула дает нулевые результаты, например, “нет данных” или “нет”.

Важно знать, как функция FILTER работает с одним условием, прежде чем добавлять несколько критериев. Например, вы можете отфильтровать данные в ячейках A2 через C9 по Гарольду Хиллу, который существует в B2 через B9. Формула для этого:

=FILTER(A2:C9,B2:B9="Гарольд Хилл")

У нас есть два результата для Гарольда Хилла. Легко, правда?

Давайте добавим больше условий в формулу. Несколько критериев добавляются с помощью звездочки (*) для И и знака плюс (+) для ИЛИ.

В качестве примера мы фильтруем как Гарольда Хилла, так и Электронику, используя следующую формулу:

=FILTER(A2:C9,(B2:B9="Гарольд Хилл")*(A2:A9="Электроника"))

Чтобы разобрать формулу, A2:C9 — это набор данных, B2:B9=”Гарольд Хилл” — это первое условие, звездочка представляет И, а A2:A9=”Электроника” — это второе условие.

Мы получили один результат с нашим фильтром, так как формула должна соответствовать обоим условиям, Гарольду Хиллу и Электронике.

В другом примере вы можете фильтровать по Одежде или Автомобилям:

=FILTER(A2:C9,(A2:A9="Одежда")+(A2:A9="Автомобили"))

Разбирая эту формулу, A2:C9 — это набор данных, A2:A9=”Одежда” — это первое условие, знак плюс представляет ИЛИ, а A2:A9=”Автомобили” — это второе условие.

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

Совет: чтобы узнать больше о продвинутых навыках Excel, ознакомьтесь с нашими руководствами по Power Query и Power Pivot.

Часто задаваемые вопросы

Как мне найти структуру формулы функции непосредственно в Excel?

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

Выберите пустую ячейку, перейдите на вкладку “Формулы” и выберите “Вставить функцию” на левой стороне ленты. Введите функцию в строке поиска вверху и нажмите “Перейти”. Когда вы увидите нужную вам функцию, выберите ее, чтобы увидеть ее описание и синтаксис внизу окна.

Как мне отладить формулу, которая не работает правильно?

Выберите формулу, с которой у вас возникают проблемы, и перейдите на вкладку “Формулы”. Выберите “Оценить формулу” в разделе аудита формул на ленте.

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

Как мне узнать причину, по которой я получаю ошибку формулы?

Когда вы видите ошибку для формулы, которую вы создали, вы можете сделать две вещи, чтобы получить больше информации. Во-первых, нажмите кнопку ошибки, которая появляется рядом с ячейкой, затем выберите “Помощь по этой ошибке”, чтобы получить больше деталей от Microsoft в боковой панели, которая появляется.

В качестве альтернативы выберите “Показать шаги вычисления”, чтобы открыть окно “Оценка формулы”, описанное выше. Вы увидите результат вычисления и процесс, чтобы добраться до него, что должно помочь определить причину ошибки.

Кредит изображения: Pixabay. Все скриншоты предоставлены Сэнди Уритхаус.