Как выполнить ВПР и одновременно просуммировать все совпадающие значения по строкам или столбцам в Excel?
Функции ВПР и СУММ позволяют быстро находить заданные критерии и одновременно суммировать соответствующие значения. В этой статье мы покажем два метода выполнения ВПР с последующим суммированием первого или всех совпадающих значений по строкам или столбцам в Excel.
ВПР и суммирование совпадений в строке или нескольких строках с помощью формул
ВПР и суммирование совпадений в столбце с помощью формул
Легкий ВПР и суммирование совпадений по строкам или столбцам с помощью мощного инструмента
Дополнительные руководства по функции ВПР…
ВПР и суммирование совпадений в строке или нескольких строках с помощью формул
Формулы в этом разделе позволяют суммировать первое или все совпадающие значения в строке или нескольких строках на основе заданных критериев в Excel. Выполните следующие действия.
ВПР и суммирование первого совпадающего значения в строке
Предположим, у вас есть таблица фруктов, как на скриншоте ниже, и вам нужно найти первое вхождение «Яблоко» в этой таблице, а затем просуммировать все соответствующие значения из той же строки. Для этого выполните следующие действия.
1. Выберите пустую ячейку для вывода результата, например B10. Скопируйте в неё приведённую ниже формулу и нажмите Ctrl+Shift+Enter, чтобы получить результат.
=SUM(VLOOKUP(A10, $A$2:$F$7, {2,3,4,5,6}, FALSE))
Примечания:
- A10— это ячейка, содержащая искомое значение;
- $A$2:$F$7— это диапазон таблицы данных (без заголовков), включающий искомое значение и соответствующие ему значения;
- Числа {2,3,4,5,6} означают, что столбцы с результатами начинаются со второго и заканчиваются шестым столбцом таблицы. Если количество столбцов с результатами превышает 6, замените {2,3,4,5,6} на {2,3,4,5,6,7,8,9…}.
ВПР и суммирование всех совпадающих значений в нескольких строках
Приведённая выше формула суммирует значения только в строке первого совпадения. Чтобы получить сумму всех совпадений по нескольким строкам, выполните следующие действия.
1. Выберите пустую ячейку (в данном случае — B10), вставьте в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.
=SUMPRODUCT((A2:A7=A10)*B2:F7)
Легко выполняйте функцию ВПР и суммируйте совпадения по строкам или столбцам в Excel:
Утилита Поиск и суммирование из Kutools для Excel позволяет мгновенно выполнять ВПР и суммировать совпадения по строкам или столбцам в Excel — как показано на демонстрации ниже.
Скачайте полную версию с 30-дневной бесплатной пробной версией Kutools для Excel прямо сейчас!
ВПР и суммирование совпадающих значений в столбце с помощью формул
В этом разделе представлена формула для суммирования значений столбца в Excel по заданным критериям. Как показано на скриншоте ниже, вы ищете заголовок столбца «Янв» в таблице фруктов, а затем суммируете все значения этого столбца. Выполните следующие действия.
1. Выберите пустую ячейку, вставьте в неё приведённую ниже формулу и нажмите клавишу Enter, чтобы получить результат.
=SUM(INDEX(B2:F7,0,MATCH(A10,B1:F1,0)))
Легкий ВПР и суммирование совпадений по строкам или столбцам с помощью мощного инструмента
Если вы не уверены в работе с формулами, рекомендуем воспользоваться функцией ВПР и сумма из Kutools для Excel. С её помощью вы легко выполните ВПР и просуммируете все совпадения по строкам или столбцам — всего за несколько щелчков!
Перед применением Kutools для Excel пожалуйста, сначала загрузите и установите его.
ВПР и суммирование первого или всех совпадающих значений в строке или нескольких строках
1. Нажмите Kutools > Супер ПОИСК > Поиск и суммирование, чтобы активировать функцию. См. скриншот:
2. В диалоговом окне Поиск и суммированиевыполните следующие настройки.
- 2,1) В разделе Тип поиска и суммированиявыберите опцию Поиск и суммирование строки;
- 2,2) В поле Диапазон значений поискавыберите ячейку, содержащую искомое значение;
- 2,3) В поле Область размещения спискавыберите ячейку для вывода результата;
- 2,4) В поле Диапазон таблицы данныхвыберите диапазон таблицы без заголовков столбцов;
- 2,5) В разделе Параметры, если вы хотите суммировать значения только для первого найденного совпадения, выберите опцию Вернуть суммарный результат первого совпадающего элемента. Если же вам нужно просуммировать значения всех совпадений, выберите опцию Вернуть сумму всех совпадающих значений;
- 2,6) Нажмите кнопку ОК, чтобы сразу увидеть результат. См. скриншот:
Примечание: если вы хотите выполнить ВПР и суммировать первое или все совпадающие значения в одном или нескольких столбцах, установите флажок Поиск и суммирование столбца в диалоговом окне и настройте параметры, как показано на скриншоте ниже.
Подробнее об этой функции — см. здесь.
Если вы хотите воспользоваться бесплатной пробной версией (30 дней) этой утилиты, нажмите, чтобы скачать её, а затем выполните операцию в соответствии с приведёнными выше шагами.
связанные статьи
Функция ВПР по нескольким листам
Функция ВПР отлично справляется с поиском значений в таблице на одном листе. Но что делать, если нужно выполнить поиск сразу по нескольким листам? В этой статье вы найдёте подробные инструкции, которые помогут легко справиться с этой задачей.
ВПР и возврат совпадающих значений из нескольких столбцов
Обычно функция ВПР возвращает совпадающее значение только из одного столбца. Однако иногда нужно извлечь совпадающие значения сразу из нескольких столбцов по заданным критериям. Вот готовое решение для вас!
ВПР с возвратом нескольких значений в одну ячейку
Обычно функция ВПР возвращает только первое найденное значение, даже если по условию есть несколько совпадений. Как получить все подходящие результаты и отобразить их в одной ячейке?
ВПР и возврат всей строки с совпадающим значением
Обычно функция ВПР возвращает результат только из указанного столбца в той же строке. В этой статье мы покажем, как получить всю строку данных по заданным критериям.
Обратный ВПР или ВПР в обратном порядке
Обычно функция ВПР ищет значение слева направо в таблице и требует, чтобы искомое значение находилось левее целевого. Однако иногда вы знаете целевое значение и хотите найти соответствующее ему исходное — справа налево. В этом случае вам понадобится выполнить обратный ВПР в Excel. В этой статье мы покажем несколько простых способов решить эту задачу!
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек