СУММЕСЛИ С ВПР

Комбинированное использование sumif(vlookup)

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

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

СУММЕСЛИ — это функцияСУММЕСЛИ — это функцияФункция СУММЕСЛИ Excel вычисляет сумму диапазона ячеек на основе заданных критериев. Критерии могут включать даты, числа и текст. Например, формула «=СУММЕСЛИ(B1:B5, «<=12»)» складывает значения в диапазоне ячеек B1:B5, которые меньше или равны 12. подробнее представлено в Excel из представленной версии 2007 г. в Excel для суммирования различных значений, соответствующих критериям. Функция VLOOKUPVLOOKUPExcel выполняет поиск определенного значения и возвращает соответствующее совпадение на основе уникального идентификатора. Уникальный идентификатор однозначно связан со всеми записями базы данных. Например, идентификатор сотрудника, номер списка учащихся, контактный номер клиента, адрес электронной почты продавца и т. д. являются уникальными идентификаторами. Функция чтения дополнительных данных — одна из лучших формул для сбора данных из других таблиц. При наличии нескольких условий и столбцов функция СУММЕСЛИ(ВПР) используется для выполнения многочисленных вычислений на листе Excel. Недостаток функции СУММЕСЛИ, возвращающей единственное число, преодолевается с помощью ВПР. Функция ВПР помогает вернуть любые данные из таблицы на основе совпадающих критериев.

Оглавление

• Комбинированное использование sumif(vlookup)
  • Объяснение
  • Как использовать СУММЕСЛИ с функцией ВПР?
  • Примеры
    • Пример №1. Использование sumif(vlookup) вместе для определения некоторого значения
    • Пример № 2 – Определение суммы на основе критериев соответствия в разных рабочих листах
  • Преимущества
  • То, что нужно запомнить
  • Рекомендуемые статьи

Объяснение

Функция СУММЕСЛИ: Это математическая и тригонометрическая функция для суммирования значений, когда установленное условие равно «ИСТИНА». Суммарное значение получается на основе только одного критерия.

Когда мы имеем дело с функцией СУММЕСЛИ в Excel, используется следующая формула:

• Диапазон: Это диапазон ячеек, используемый для оценки установленных критериев.
• Критерии: Это условие суммирования значений. Это может быть ссылка на ячейку ReferenceCell ReferenceCell в Excel ссылается на другие ячейки на ячейку, чтобы использовать ее значения или свойства. Например, если у нас есть данные в ячейке A2 и мы хотим использовать их в ячейке A1, используйте =A2 в ячейке A1, и это скопирует значение A2 в A1.Подробнее, число и другую функцию Excel. Например, когда мы хотим объединить СУММЕСЛИ и ВПР, вместо критериев будет введена функция ВПР.
• Суммарный_диапазон: Диапазон ячеек, указанный для суммирования числовых значений.

Теперь формула изменена на

Формула = СУММЕСЛИ (Диапазон, ВПР (искомое_значение, табличный_массив, столбец _индекс _номер, [range_lookup]), [sum_range])

• Искомое_значение: Он указывает значение, которое нужно искать в таблице. Это может быть ссылка или значение.
• Таблица_массив: Это диапазон таблицы, содержащий два или более двух столбцов.
• Столбец_индекс_номер: Относительный индекс столбца указывает на возврат требуемых данных из определенного столбца.
• [Range_lookup]: Это может быть 0 или 1, чтобы указать, должно ли оно возвращать точное или приблизительное значение. Но это необязательно для пользователя. 0 указывает на точное совпадение, а 1 — на приблизительное.

Как использовать СУММЕСЛИ с функцией ВПР?

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

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

Первый метод: Формулу необходимо вводить с клавиатуры, как показано на изображении ниже.

Второй метод: Функцию СУММЕСЛИ можно вставить на вкладке «Формулы», как показано на рисунке.

После входа в функцию СУММЕСЛИ формула ВПР вводится внутри функции СУММЕСЛИ путем замены элемента «Критерии». Все параметры ВПР, включая значение поиска, массив таблиц, порядковый номер столбца и поиск диапазона. Они должны быть заключены в круглые скобки, чтобы избежать ошибок в формуле. Суммируемые значения включаются в элементы диапазона суммы функции СУММЕСЛИ. Наконец, клавиши «CTRL», «SHIFT» и «ENTER» нажимаются вместе, чтобы значения отображались в виде массива.

Примеры

.free_excel_div{фон:#d9d9d9;размер шрифта:16px;радиус границы:7px;позиция:относительная;margin:30px;padding:25px 25px 25px 45px}.free_excel_div:before{content:””;фон:url(центр центр без повтора #207245;ширина:70px;высота:70px;позиция:абсолютная;верх:50%;margin-top:-35px;слева:-35px;граница:5px сплошная #fff;граница-радиус:50%} Вы можете скачать этот шаблон SUMIF с VLOOKUP Excel здесь – SUMIF с шаблоном VLOOKUP Excel

Пример №1. Использование sumif(vlookup) вместе для определения некоторого значения

В этом примере показано, как использовать СУММЕСЛИ(ВПР) вместе, чтобы найти сумму продаж в одном и том же месяце в разные годы.

1. Для этого примера рассматриваются следующие данные, как показано на скриншоте.

   

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

   

3. После ввода данных в основную и справочную таблицы функция СУММЕСЛИ определяет общий объем продаж за другие месяцы. Здесь рассматриваемое значение поиска — это месяц. Формула, объединяющая функцию СУММЕСЛИ (ВПР), выглядит следующим образом:

   

   

Общий объем продаж в январе месяце определяется как 17263,3. Когда мы меняем значение поиска на другой месяц, генерируется соответствующий общий объем продаж.

Пример № 2 – Определение суммы на основе критериев соответствия в разных рабочих листах

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

Данные основной таблицы показаны на скриншоте ниже.

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

В этом примере вместо выбора массива поиска упоминается только таблица просмотра. При нажатии трех клавиш, включая «CTRL», «SHIFT» и «ENTER», получаются точные результаты.

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

Преимущества

Ниже приведены преимущества использования этих функций:

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

То, что нужно запомнить

• При использовании функции ВПР порядковый номер столбца не должен быть меньше 1 во избежание ошибок.
• Мы должны давать индексы столбцам интерполяционной таблицы, указывая числа 1, 2, 3 и так далее.
• CTRL+SHIFT+ENTER в excelCTRL+SHIFT+ENTER в ExcelCtrl-Shift Enter In Excel — это команда быстрого доступа, которая упрощает реализацию формулы массива в функции Excel для выполнения сложных вычислений заданных данных. В целом он преобразует определенные данные в формат массива в Excel с несколькими значениями данных для этой цели. Вместо ввода ключа следует использовать дополнительные сведения, поскольку функция ВПР вводится как формула массива.
• Для извлечения значений и определения суммы значений массива требуется определение двух таблиц, в том числе основной и таблицы поиска.
• Функция СУММЕСЛИ дает точные результаты только для числовых данных. Это не будет работать для другого типа данных.

Рекомендуемые статьи

Эта статья была руководством по СУММЕСЛИ с функцией ВПР в Excel. Здесь мы обсудим примеры СУММЕСЛИ с функцией ВПР для выполнения нескольких вычислений на листе Excel с загружаемым шаблоном Excel. Вы можете узнать больше об Excel из следующих статей: –

• Используйте Not Blank в SUMIF”Используйте”Чтобы узнать больше
• Формула сопоставления с ВПРФормула сопоставления с ВПРФормула ВПР работает только тогда, когда табличный массив в формуле не меняется. Если новый столбец вставляется или удаляется, формула дает неверный результат. Чтобы избежать этого, мы используем функцию сопоставления, чтобы сопоставить индекс данных и вернуть фактический результат.Подробнее
• Учебник по ВПР
• ВПР Примеры Excel