Както споменах в много от моите блогове, че SUMPRODUCT е много гъвкава функция и може да се използва за множество цели. В тази статия ще видим как можем да използваме тази функция за преброяване на стойности с множество или критерии.
Обща формула на SUMPRODUCT за броене с множество или критерии
| = SUMPRODUCT (-(((критерии1)+(критерии2)+…)> 0) |
Критерии 1: Това е всеки критерий, който връща масив от TRUE и FALSE.
Критерии 2: Това е следващият критерий, който искате да проверите. По същия начин можете да имате колкото искате критерии.
Горната обща формула често се променя, за да отговаря на изискванията за броене с множество критерии ИЛИ. Но основната формула е следната. Първо ще видим как това работи чрез пример и след това ще обсъдим други сценарии, при които ще трябва да промените тази формула малко.
Проблем:
Цената в клетка трябва да бъде разделена точно на 5 подобни плащания. Когато просто разделим цената $ 88,04 на 5 и закръглим резултата до два знака след десетичната запетая, получаваме индивидуални плащания от $ 17,61. Това обаче ще доведе до общо плащане от $ 88,05, което не е точната сума, с която започваме. Следователно искаме да изчислим 5 подобни числа, които се събират до общата сума.
Решение:
Първо Изчислете първото плащане, като разделите цената на 5 и закръглете резултата.
Използвайте функцията ROUND в следната формула:
= КРЪГЛО (B1/5,2)
Стъпка 2:
Изчислете всяко останало плащане.
Използвайте функциите ROUND, SUM и ROW в следната формула:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Пример:
Пребройте потребителите, ако кодът на търговеца съвпада или годината, използвайки SUMPRODUCT
Тук имаме набор от данни за продавачи. Данните съдържат много колони. Това, което трябва да направим, е да преброим броя на потребителите, които имат код „INKA“ или годината е „2016“. Уверете се, че ако някой има и двете (код като "inka" и 2016 година), трябва да се брои като 1.
И така, чуйте, че имаме два критерия. Използваме гореспоменатата формула на СУМПРОДУКТ:
| = SUMPRODUCT (-(((Код = I3)+(Година = K3))> 0)) |
Тук кодът и годината са именувани диапазони. Това връща 7.
В данните имаме 5 записа на INKA код и 4 записа на 2016 г. Но 2 записа имат както „INKA“, така и 2016 като код и съответно година. И тези записи се отчитат като 1. И така получаваме 7.
Да видим как работи?
Така че нека да разгледаме как формулата се решава стъпка по стъпка, след което ще обсъдя как работи.
-
- = СУМПРОДУКТ(-(((Код = I3)+(Година = K3))> 0))
- СУМПРОДУКТ(-(({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- СУМПРОДУКТ(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- СУМПРОДУКТ(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- СУМПРОДУКТ({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Двойните отрицателни (-) знаци се използват за преобразуване на булеви стойности в 1s и 0s. Така всяка TRUE стойност в масива се преобразува в 1 и FALSE в 0.
Добавяне на още или критерии за преброяване с помощта на SUMPRODUCT, така че ако трябва да добавите повече или критерии за преброяване, можете просто да добавите критерии, като използвате знак + към функцията.
Например, ако искате да добавите друг критерий към горната формула, така че да добави броя на служителите, които са продали повече от 5 продукта. Формулата SUMPRODUCT просто ще изглежда така:
| = SUMPRODUCT (-(((Код = I3)+(Година = K3)+(Продажби> 5))> 0)) |
Просто! нали?
Но да речем, че искате да имате два критерия от обхвата на кода. Да предположим, че искате да преброите "INKB". Тогава как правите това? Един метод използва горната техника, но това би било повтарящо се. Да речем, че искам да добавя още 10 критерия от същия диапазон. В такива случаи тази техника не е толкова умна за броене с SUMPRODUCT.
Да приемем, че имаме подредени по този начин данни.
Кодовете на критериите са в един ред I2: J2. Подреждането на данните е важно тук. Формулата SUMPRODUCT за настройка за броене на 3 критерия ИЛИ ще бъде:
| = SUMPRODUCT (-(((Код = I2: J2)+(Година = I3: J3))> 0)) |
Това е формулата SUMPRODUCT за отчитане с множество критерии, когато множество критерии от един диапазон са написани на ред.
Горната формула връща верния отговор, който е 10.
Ако въведете всяка година в J3, формулата ще добави и този брой.
Това се използва, когато критериите са в един ред. Ще работи ли, когато критериите в една колона за същия диапазон? Не. Няма да стане.
В този пример имаме множество кодове за преброяване, но тези кодове са написани в една колона. Когато използваме горната формула SUMPRODUCT, получаваме Ans #N/A грешка. Няма да навлизаме в това как възниква тази грешка, тъй като това прави тази статия твърде дълга. Нека да видим как можем да накараме тази работа да работи.
За да работи тази формула, трябва да увиете критериите на кода във функцията TRANSPOSE. Така формулата ще работи.
| = SUMPRODUCT (-(((Код = TRANSPOSE (H3: H4))+(Година = TRANSPOSE (I3: I4)))> 0)) |
Това е формулата за преброяване с множество или условия в същия диапазон, когато критериите са изброени в колона.
Ето всички забележки от наблюденията относно използването на формулата.
Бележки:
- Формулата работи както за текст, така и за числа.
- Стойностите за търсене могат да бъдат дадени като препратка към клетка или директно като се използва кавичка (") във формулата като аргументи.
Надявам се, че сте разбрали как да разделите сума на равни плащания, като същевременно избягвате грешките при разделяне / закръгляване в Excel. Разгледайте още статии за стойността на търсене в Excel и функциите на Excel 2019 тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.
Използвайте INDEX и MATCH за търсене на стойност : INDEX & MATCH функция за търсене на стойност според нуждите.
Обхват SUM с INDEX в Excel : Използвайте INDEX функцията, за да намерите SUM на стойностите, както е необходимо.
Как да използвате функцията INDEX в Excel : Намерете INDEX на масив, като използвате функцията INDEX, обяснена с пример.
Как да използвате функцията MATCH в Excel : Намерете MATCH в масива, като използвате стойността INDEX вътре в функцията MATCH, обяснена с пример.
Как да използвате функцията LOOKUP в Excel : Намерете стойността за търсене в масива, като използвате функцията LOOKUP, обяснена с пример.
Популярни статии:
Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.
Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.