3. Сложение или подсчет по критериям: СУММЕСЛИ, СУММЕСЛИ, СЧЕТЕСЛИ и СЧЕТЕСЛИ
вентаризации, вы, вероятно, узнаете гораздо больше о доступных функциях и их формулах. Но придумывать что-то новое все равно может быть ошеломляющим или даже пугающим. В этом руководстве представлено несколько полезных функций и их формул, которые помогут вам освоить новую территорию.1. Поиск значений в таблице: XLOOKUP
Если вы хотите найти данные на основе других данных в большой электронной таблице, вам подойдет функция XLOOKUP. В отличие от ВПР , который может искать значения только с
Contents
1. Поиск значений в таблице: XLOOKUP
лева направо или справа налево, что делает его более гибким инструментом.Примечание. На момент написания этой статьи XLOOKUP доступен только подписчикам Microsoft 365. Для других версий Excel ознакомьтесь с комбинацией ИНДЕКС и ПОИСКПОЗ в следующем разделе.
Синтаксис функции:
XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)
Требуются только первые три аргумента. Ниже приведено описание каждого аргумента:
- Lookup_value : значение для поиска.
- Lookup_range : диапазон, содержащий значение для поиска.
- Return_range : диапазон, содержащий возвращаемое значение.
- Not_found : текст, возвращаемый, если значение не найдено. Если опущено, значение «#Н/Д» используется по умолчанию.
- Match_mode : тип соответствия, использующий «0» для точного совпадения и «#N/A», если ничего не найдено (по умолчанию, если опущено), «1» для точного совпадения и следующее элемент меньшего размера, если он не найден, «-1» для точного совпадения и следующий элемент большего размера, если он не найден, или «2» для совпадения с подстановочным знаком с использованием вопросительного знака, звездочки или тильды.
- Search_mode : режим поиска, использующий «1», чтобы начать с первого элемента (по умолчанию, если опущен), «-1», чтобы начать с последнего элемента, «2», если задан «lookup_range». в порядке возрастания или «-2», если «диапазон поиска» находится в порядке убывания.
Например, мы ищем продажи для сектора 2, поэтому используем следующую формулу:
=XLOOKUP(2,A19:A24,D19:D24)
В формуле 2 — это «искомое_значение», A19:A24 — «диапазон_искомого значения», а D19:D24 — аргумент «диапазон_возврата».
Результат — 74 000 — соответствует правильному значению для сектора 2.
Если вам нужно что-то более динамичное, когда вы ищете значение в ячейке, а не константу, а затем получаете обновленный результат при каждом изменении значения в этой ячейке, см. следующий пример:
Мы заменяем «искомое_значение» 2 на ячейку F19:
=XLOOKUP(F19,A19:A24,D19:D24)
Когда мы вводим 2 в ячейку F19, мы получаем результат 74 000, а если мы вводим другое значение, например 5, мы автоматически получаем обновленный результат — 61 000.
Вы можете включить аргумент «not_found», чтобы вам не приходилось просматривать сообщение об ошибке, если совпадение не найдено. Мы добавляем аргумент «Нет кубиков».
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
Если в ячейку F19 введено значение, не имеющее совпадения, вы увидите свое сообщение вместо «#NA».
2. Альтернативный поиск значений в таблице: ИНДЕКС и ПОИСКПОЗ
Хотя XLOOKUP предоставляет надежный способ поиска значений, в настоящее время он доступен только для подписчиков Microsoft 365 Excel. Если вы используете другую версию Excel, вы можете использовать ИНДЕКС и ПОИСКПОЗ, чтобы сделать то же самое.
При использовании функции ИНДЕКС значение в ячейке возвращается на основе позиции, введенной вами в формулу. Синтаксис ИНДЕКС:
INDEX(range, row_number, column_number)
Функция ПОИСКПОЗ возвращает позицию значения, введенного в формулу. Синтаксис ПОИСКПОЗ:
MATCH(value, range, match_type)
Чтобы объединить эти две функции и их формулы, поместите формулу ПОИСКПОЗ в формулу ИНДЕКС в качестве позиции поиска («номер_строки» и «номер_столбца»).
Используя те же данные, что и в примере XLOOKUP выше, мы хотим увидеть продажи для сектора, который мы вводим в ячейку F19. Формула:
=INDEX(D19:D24,MATCH(F19,A19:A24))
Чтобы разобрать эту формулу, мы начнем с ИНДЕКС и его аргумента «диапазон», который равен D19:D24. Это диапазон, содержащий желаемый результат.
Формула ПОИСКПОЗ использует F19 в качестве аргумента «значение» и A19:A24 в качестве аргумента «диапазон», содержащего это значение.
Когда мы вводим 4 в ячейку F19, мы получаем результат 75 000, и это правильно.
Если мы введем в ячейку F19 другой сектор, например 6, формула автоматически обновится и даст правильное значение 58 000.
Совет. попробуйте ввести функции в ячейку, нажав Shift+ F3. Меню «Вставить функцию» позволяет искать и выбирать функции. Ознакомьтесь с нашими Шпаргалка по сочетаниям клавиш Excel , чтобы сэкономить больше времени.
3. Сложение или подсчет по критериям: СУММЕСЛИ, С2. Альтернативный поиск значений в таблице: ИНДЕКС и ПОИСКПОЗ
азначены для сложения чисел и подсчета ячеек, но их возможности ограничены простыми вычислениями. Вариации этих функций позволяют суммировать или считать, используя критерии. Например, вы можете суммировать только числа больше 12 или подсчитывать только ячейки, содержащие имя Билл.
С помощью СУММЕСЛИ и СЧЕТЕСЛИ вы можете складывать или считать с одним условием, а с помощью СУММЕСЛИ и СЧЕТЕСЛИ вы можете складывать или считать с несколькими условиями. Ниже приведен синтаксис каждого из них:
СУММЕСЛИ
SUMIF(range, criteria, sum_range)
COUNTIF
COUNTIF(range, condition)
СУММИФ
SUMIFS(sum_range, condition_range1, condition1, condition_range2, condition2,…)
COUNTIFS
COUNTIFS(condition_range1, condition1, condition_range2, condition2,…)
Ниже приведены примеры каждой функции.
Чтобы сложить числа в нашем диапазоне от B2 до B7 только для чисел больше 12, мы используем функцию СУММЕСЛИ и следующую формулу:
=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.
В примере с использованием функции СЧЁТЕСЛИ мы подсчитываем количество ячеек с теми же двумя условиями, описанными выше. Мы подсчитываем количество клиентов, подписавшихся на рассылку (Y) и чей город — Сан-Диего.
=COUNTIFS(C2:C7,"Y",D2:D7,"San Diego")
Как и ожидалось, результат 2 для Билла Брауна и Сью Смит, единственных подписчиков на рассылку новостей в Сан-Диего.
4. Тестовые данные в различных условиях: IFS
Функция ЕСЛИ в Excel — мощный инструмент для проверки данных на соответствие условиям. Например, вы можете использовать его для отображения буквенной оценки для числовой оценки учащегося или ответа «Да», если продавец приносит достаточный доход для получения бонуса, и ответа «Нет», если этого не происходит.
Проблема функции ЕСЛИ заключается в том, что для проверки данных по нескольким критериям необходимо объединить все операторы ЕСЛИ вместе. Чтобы исправить эту массовую путаницу, используйте функцию IFS.
Подобно SUMIFS и COUNTIFS, описанным выше, IFS позволяет добавлять в формулу несколько критериев в понятной и удобной для чтения форме.
Синтаксис: IFS(test1, if_test1_true, test2, if_test2_true, …), что позволяет проверить до 127 различных критериев.
Используя один из наших примеров сценариев, мы начинаем с простого обозначения бонусов. Если сумма в ячейке B2 превышает 20 000, отобразите «Да». Если нет, отобразите «Нет». Формула принимает вид:
=IFS(B2>20000,"Yes",B2<20000,"No")
В первом тесте, если значение в B2 превышает 20 000, результатом будет «Да». Во втором тесте, если значение в B2 меньше 20 000, результатом будет «Нет».
К счастью, наш продавец получил свой бонус. Поскольку его продажи составляют 21 000, в столбце «Бонус» указывается «Да».
Скопируйте формулу, если у вас есть список, подобный нашему примеру. Перетащите маркер заполнения в правом нижнем углу ячейки вниз к остальным ячейкам для других продавцов. Формула автоматически обновляется с учетом различных ссылок на ячейки.
Еще один пример IFS: у нас есть список дежурных менеджеров на каждый день недели. Когда текущий день отображается в ячейке D2, имя соответствующего менеджера отображается в ячейке E2. Формула принимает вид:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
Это показывает, что если значение в ячейке D2 — понедельник, отобразите имя в ячейке B2, если значение в ячейке D2 — вторник, отобразите имя в ячейке B3, если значение в ячейке D2 — среда, отобразите имя в ячейке B3 и т. д.
При изменении дня недели в ячейке D2 в ячейке E2 отображается соответствующее имя дежурного менеджера.
Проделав небольшую работу по созданию формулы заранее, вы сможете воспользоваться преимуществами некоторой автоматизации таблицы Excel.
5. Фильтрация по нескольким критериям: ФИЛЬТР
Как пользователь Excel, вы, вероятно, знаете, что приложение имеет встроенную функцию фильтра. Однако, если вы хотите отфильтровать данные, используя условие или даже несколько критериев, вам необходимо использовать функцию ФИЛЬТР. Эта гибкая функция и ее аргументы помогут вам сузить большие наборы данных за считанные минуты.
Синтаксис функции: FILTER(range, range=criteria, if_empty), где для набора данных требуются только первые два аргумента, а также критерии и диапазон их контейнеров. Третий аргумент полезен, если вы хотите вернуть что-то, в частности, если формула дает нулевой результат, например «нет данных» или «нет».
Важно знать, как функция ФИЛЬТР работает с одним условием, прежде чем добавлять несколько критериев. Например, вы можете отфильтровать данные в ячейках от A2 до C9 по Гарольду Хиллу, которые существуют в ячейках от B2 до B9. Формула для этого:
=FILTER(A2:C9,B2:B9="Harold Hill")
У нас есть два результата по Гарольду Хиллу. Легко, правда?
Давайте добавим в формулу дополнительные условия. Несколько критериев добавляются с помощью звездочки (*) для И и знака плюс (+) для ИЛИ.
В качестве примера мы фильтруем и Гарольда Хилла, и электронику, используя следующую формулу:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
Для разбивки формулы: A2:C9 — это набор данных, B2:B9=»Гарольд Хилл» — первое условие, звездочка представляет собой И, а A2:A9=»Электроника» — второе условие.
С нашим фильтром мы получили один результат, поскольку формула должна соответствовать обоим условиям: Гарольд Хилл и Электроника.
В другом примере вы можете отфильтровать одежду или автомобили:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Разбивая эту формулу, A2:C9 — это набор данных, A2:A9=»Одежда» — первое условие, знак плюс представляет собой ИЛИ, а A2:A9=»Автомобильная промышленность» — второе условие.
На этот раз мы получили два результата, поскольку формула должна соответствовать любому из условий, а не обоим.
Совет. чтобы освоить более сложные навыки работы с Excel, ознакомьтесь с нашими Руководства по Power Query и Power Pivot .
Часто задаваемые вопросы
Как найти структуру формулы функции непосредственно в Excel?
Excel предлагает встроенный инструмент для поиска нужной функции. С его помощью вы можете увидеть синтаксис формулы этой функции.
Выберите пустую ячейку, перейдите на вкладку «Формулы» и выберите «Вставить функцию» в левой части ленты. Введите функцию в поле поиска вверху и нажмите «Перейти». Когда вы увидите нужную функцию, выберите ее, чтобы увидеть ее описание и синтаксис внизу поля.
Как можно отладить формулу, которая работает неправильно?
Выберите формулу, с которой у вас возникли проблемы, и перейдите на вкладку «Формулы». Выберите «Оценить формулу» в разделе «Аудит формул» на ленте.
Вы увидите свою формулу в окне «Оценить формулу» с подчеркнутой частью. Нажмите «Оценить», чтобы увидеть рассчитанную подчеркнутую часть. Продолжайте этот процесс, продвигаясь по каждой части формулы к результату. Это позволяет увидеть, как обрабатывается формула и где может быть ошибка.
Как узнать причину ошибки в формуле?
Когда вы видите ошибку в созданной вами формуле, вы можете сделать две вещи, чтобы получить дополнительную информацию. Сначала нажмите кнопку ошибки, которая появляется рядом с ячейкой, затем выберите «Справка по этой ошибке», чтобы получить более подробную информацию от Microsoft на появившейся боковой панели.
Либо выберите «Показать шаги расчета», чтобы открыть окно «Оценить формулу», описанное выше. Вы увидите результат расчета и процесс его достижения, что должно помочь определить причину ошибки.
Изображение предоставлено: Pixabay. Все скриншоты сделаны Sandy Writtenhouse.
