В тази статия ще се научим да СУМИМ, ако използваме стойности за търсене в Excel.
С прости думи, докато работим с различни таблици с данни, понякога се налага да изчисляваме стойности в стойности, базирани на първата таблица в друга таблица. Условието е да получите сумата от стойности в първата таблица, като използвате стойностите на втората таблица
За тази статия ще се нуждаем от използването на следните функции:
- Функция SUMPRODUCT
- SUMIF функция
Функцията SUMPRODUCT е математическа функция в Excel. Работи в няколко диапазона. Той умножава съответните масиви и след това ги добавя.
Общо = (A1 * B1 * C1 * … + A2 * B2 * C2 * … + …)
Синтаксис:
= SUMPRODUCT (масив1, [масив2], …)
масив: Това е диапазонът или списък със стойности.
Функцията SUMIF в Excel връща SUM на числата, отговарящи на посочените критерии. Обхватът на SUM и критериите могат да бъдат еднакви или различни.
Обща формула на Excel SUMIF:
= SUMIF (критерий_диапазон, критерии, сума_диапазон)
range_ range: диапазон, в който се прилага условие.
критерии: условие за кандидатстване в диапазон_критерии.
sum_range: диапазон на сумата въз основа на критерии.
Сега ще направим формула от тези функции. Тук ще дадем данните и се нуждаем от сумарни резултати, където стойността съвпада със стойността в таблицата за търсене.
Обща формула:
= SUMPRODUCT (SUMIF (резултат, записи, сума_нуми))
резултат : стойности, които трябва да се съпоставят в таблица за търсене
запис : стойности в първата таблица
sum_nums : стойности за сумиране според съответстващи стойности.
Нека тестваме тази формула, като я изпълним на пример
Тук изчисляваме общите точки на футболен отбор, използвайки таблицата с точки или таблицата за търсене.
Тук резултатът (B8: B10) е зададен като именен диапазон, а точките (C8: C10) са посочени като именен диапазон.
Сега ще използваме формулата по -долу, за да получим сумата от точки за месец1 в клетка F3
Използвайте формулата:
= SUMPRODUCT (SUMIF (резултат, B3: E3, точки))
Обяснение:
- Функцията SUMIF съответства на резултата със стойности в B3: E3.
- Тогава точките, съответстващи на резултата, се връщат към функцията SUMPRODUCT.
- Функцията SUMPRODUCT връща SUM на стойностите, дадени като масив на функцията.
Както можете да видите тук, общият брой точки, събрани от екипа през Първия месец, е 7.
3 (W) + 0 (L) + 1 (D) + 3 (W) = 7 точки
Копирайте формулата в други клетки, изберете клетките, като вземете първата клетка, където вече е приложена формулата, използвайте клавишната комбинация Ctrl + D или използвайте опцията за плъзгане надолу в Excel.
Както можете да видите в горната снимка точките, отбелязани през 4 -те различни месеца.
Вземете СУММАТА на всички точки за 4 месеца.
Това означава, че отборът е събрал общо 18 точки за 4 месеца.
Алтернативен метод
Използвайте метода SUM и масив вместо функцията SUMPRODUCT.
Използвайте формулата:
{= SUM (SUMIF (резултат, B3: E3, точки))}
Къдравите скоби са методът на масива, който предлага на функцията да изчисли цялата върната стойност чрез функцията SUMIF.
Забележка: Не го правете използвайте символите на къдравите скоби ръчно. Използвай Ctrl + Shift + Enter да използвате метода на масива в Excel.
Както можете да видите от горната формула, можете да получите общи точки, като използвате таблица за условно търсене.
Бележки:
- Формулата работи само с числа.
- Формулата работи само когато няма дубликати в таблицата за търсене
- Функцията SUMPRODUCT разглежда нецифрени стойности като 0s.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да е със същата дължина, освен функцията
Надявам се, че тази статия за това как да върнете SUM само от формули в Excel е обяснима. Намерете още статии за функциите SUMPRODUCT тук. Моля, споделете вашето запитване по -долу в полето за коментар. Ние ще ви съдействаме.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Абсолютна справка в Excel
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата
Конвертирайте инчове в крака и инчове в Excel 2016
Присъединете се към собствено и фамилно име в Excel
Пребройте клетки, които съответстват или на А, или на В