Комбинированное использование Sumif (vlookup)
Sumif с VLOOKUP - это комбинация двух различных условных функций, SUMIF используется для суммирования ячеек на основе некоторого условия, которое принимает аргументы диапазона, в котором есть данные, а затем критерии или условие и ячейки для суммирования вместо критериев, которые мы используем ВПР в качестве критерия, когда в нескольких столбцах доступен большой объем данных.
СУММЕСЛИ - это функция, представленная в Excel из версии 2007 для суммирования различных значений, соответствующих критериям. ВПР - одна из лучших формул для сбора данных из других таблиц. Когда имеется несколько условий и столбцов, sumif (vlookup) используется для выполнения нескольких вычислений на листе Excel. Недостаток функции СУММЕСЛИ, возвращающей единственное число, преодолевается с помощью ВПР. VLOOKUP помогает вернуть любые данные из таблицы на основе согласованных критериев.
Объяснение
Функция СУММЕСЛИ: это функция тригонометрии и математики для суммирования значений, когда установленное условие истинно. Сумма рассчитывается только по одному критерию.
Когда мы имеем дело с функцией СУММЕСЛИ в excel, используется следующая формула
- Диапазон: это диапазон ячеек, используемых для оценки установленных критериев.
- Критерии: это условие суммирования значений. Это может быть ссылка на ячейку, номер или другая функция Excel. Когда мы хотим объединить СУММЕСЛИ и ВПР, функция vlookup будет введена вместо критериев.
- Диапазон сумм : это диапазон ячеек, указанный для суммирования числовых значений.
Теперь формула изменена на
Формула = СУММЕСЛИ (диапазон, Vlookup (значение поиска, массив таблицы, номер индекса столбца, [поиск диапазона]), [диапазон суммы])
- Значение поиска: указывает значение для поиска в таблице. Это может быть ссылка или значение.
- Массив таблицы: это диапазон таблицы, содержащей два или более двух столбцов.
- Номер индекса столбца: это относительный индекс столбца, который нужно указать для возврата необходимых данных из определенного столбца.
- [Поиск по диапазону]: 0 или 1, чтобы указать, следует ли возвращать точное значение или приближенное значение. Но для пользователя это необязательно. 0 указывает на точное совпадение, а 1 указывает на приблизительное совпадение.
Как использовать СУММЕСЛИ с функцией ВПР?
Комбинированное использование sumif (vlookup) полезно при поиске данных на основе одного критерия. Они во многом используются в Excel для выполнения вычислений путем поиска данных. Они все вместе используются в бизнес-среде для выполнения различных задач и принятия правильных решений. Чтобы эффективно использовать эти функции вместе,
Во-первых, необходимо ввести функцию СУММЕСЛИ двумя способами.
Первый способ. Формулу необходимо ввести с клавиатуры, как показано на рисунке ниже.
Второй метод: функцию СУММЕСЛИ можно вставить из вкладки «Формула», как показано на рисунке.
После входа в функцию СУММЕСЛИ формула для ВПР вводится внутри функции СУММЕСЛИ, заменяя элемент «Критерии». Все параметры ВПР, включая значение поиска, массив таблицы, номер индекса столбца и поиск диапазона. Их следует заключать в круглые скобки, чтобы избежать ошибок в формуле. Диапазон суммируемых значений включается в элементы диапазона сумм функции СУММЕСЛИ. Наконец, клавиши CTRL, SHIFT и ENTER были нажаты вместе, чтобы преобразовать значения в массив.
Примеры
Вы можете скачать этот шаблон Excel СУММЕСЛИ с ВПР здесь - СУММЕСЛИ с шаблоном ВПР в ExcelПример # 1 - Использование sumif (vlookup) вместе для определения некоторого значения
В этом примере показано, как использовать sumif (vlookup) вместе, чтобы найти сумму продаж за один и тот же месяц в разные годы. Следующие данные рассматриваются для этого примера, как показано на снимке экрана.
Рассмотренная таблица поиска показана, как указано ниже. Он включал справочные значения за месяцы с января по декабрь.
После ввода данных в основную таблицу и справочную таблицу функция СУММЕСЛИ используется для определения общих продаж, произведенных в разные месяцы года. Здесь рассматриваемое значение поиска - это месяц. Формула, объединяющая sumif (vlookup), отображается как,
Общий объем продаж в январе месяце определен как 17263,3 . Когда мы изменили значение внешнего вида на другой месяц, генерируются соответствующие общие продажи.
Пример № 2 - Определение суммы на основе критериев соответствия в различных рабочих листах
В этом примере таблица поиска и основная таблица взяты на разных листах, а не на одном листе. Данные таблицы поиска показаны на снимке экрана ниже.
Основные данные таблицы показаны на скриншоте ниже.
Чтобы определить общий объем продаж, имя продавца используется в качестве справочного значения, а идентификаторы сотрудников используются для справочных целей. Формула вводится, как показано ниже, и по сравнению с первым примером в формуле наблюдаются некоторые изменения.
В этом примере вместо выбора массива поиска упоминается только Lookup_table. При нажатии трех клавиш, включая CTRL, SHIFT и ENTER, получаются точные результаты.
Когда имя продавца изменяется на имена, упомянутые в поиске, сумма продаж изменяется и дает новый результат.
Преимущества
Ниже приведены преимущества использования этих функций.
- Мы можем легко извлечь значения из другой таблицы для выполнения вычислений.
- Определение суммы значений, представленных в диапазоне, который соответствует критериям, упомянутым в различных аспектах бизнеса.
То, что нужно запомнить
- При использовании функции ВПР номер индекса столбца не должен быть меньше 1, чтобы избежать ошибок.
- Индексы должны быть присвоены столбцам справочной таблицы, указав их числами 1, 2, 3 и так далее.
- CTRL + SHIFT + ENTER в Excel следует использовать вместо ввода ключа, поскольку ВПР вводится как формула массива.
- Для извлечения значений и определения суммы значений массива требуется определение двух таблиц, включая основную и уточняющую.
- СУММЕСЛИ предоставляет точные результаты только для числовых данных, он не будет работать для других типов данных.