В тази статия ще научим как да броим редове, ако отговаря на множество критерии в Excel.
Сценарий:
С прости думи, докато работим с таблици с данни, понякога се налага да броим клетките, където повече от два диапазона отговарят на критериите. Това може да стане по формулата, обяснена по -долу.
Как да решим проблема?
За този проблем ще трябва да използваме функцията SUMPRODUCT. Тук ни се дават два диапазона и имаме нужда от броя на редовете, който отговаря на 3 критерия. Функцията SUMPRODUCT връща SUM на съответните TRUE стойности (като 1) и игнорира стойности, съответстващи на FALSE стойности (като 0) в връща един масив, където условията са TRUE.
Обща формула:
| = SUMPRODUCT ((rng_1 op_1 crit_1) + 0, (rng_2 op_2 crit_1) + 0, rng_2 op_2 crit_1) + 0) |
rng: диапазон за търсене
crit: критерии за прилагане
op: оператор на критерии, условие, дадено като оператор между диапазон и критерии
+0: преобразува логическите стойности в двоични (0 & 1).
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу.
Тук трябва да намерим броя на редовете, изброени в диапазона, имащи 3 условия. Тук имаме списък на дипломатическите срещи, проведени между Индия и САЩ от 2014 г. Таблицата показва президента / премиера с етикета на страната и годината. Таблицата също е разделена на части, представляващи родната страна и списъка на гостуващите страни.
Условия, изброени по -долу:
Американският президент "Барак Обама посети Индия с проблеми по -малко от 2.
Използвайте формулата:
| = SUMPRODUCT ((C4: C10 = "Барак Обама") + 0, (F4: F10 = "Индия") + 0, (G4: G10 <2) + 0)) |
C4: C10 = "Барак Обама": Президентът отговаря на "Барак Обама" в списъка за посещения.
F4: F10 = "Индия": приемащата държава отговаря на "Индия".
G4: G10 <2: издава по -малко от две.
+0: преобразува логическите стойности в двоични (0 & 1).
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите, един път американският президент Барак Обама посети Индия, което се случи през 2015 г. Това показва, че формулата извлича броя на съвпаденията в съответния масив. Както има 1 път, когато американският президент "Барак Обама" посети Индия, където проблемите също са равни на 1, което е по -малко от 2
С равно на критериите:
Горният пример беше лесен. Така че, за да стане интересно, ние ще преброим колко пъти САЩ са били домакини на Индия от 2014 г. според данните.
Условия, изброени по -долу:
Приеманата от САЩ Индия с проблеми е равна на 2.
Използвайте формулата:
| = SUMPRODUCT ((F4: F10 = "САЩ") + 0, (D4: D10 = "Индия") + 0, (G4: G10 = 2) + 0) |
F4: F10 = "САЩ": приемащата държава отговаря на "САЩ".
D4: D10 = "Индия": посещение на държава, съответстваща на "Индия".
G4: G10 = 2: въпросите са равни на две.
+0: преобразува логическите стойности в двоични (0 & 1).
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите, има 2 пъти, когато САЩ бяха домакини на Индия и издаването е равно на две. Това показва, че формулата извлича броя на съвпаденията в съответния масив. Тъй като има 5 пъти, когато САЩ бяха домакини на Индия, но проблемите бяха 1 или 3, но тук се нуждаем от въпроси, които да бъдат съчетани с 2.
С по -високи от критериите:
Тук, за да стане интересно, ще преброим колко пъти американският президент „Доналд Тръмп“ е бил домакин на индийския премиер, започвайки от 2014 г. според данните.
Условия, изброени по -долу:
Американският президент „Доналд Тръмп“ беше домакин на Индия с проблеми, по -големи от 1.
Използвайте формулата:
| = SUMPRODUCT ((C4: C10 = "Доналд Тръмп") + 0, (F4: F10 = "Индия") + 0, (G4: G10> 1) + 0) |
F4: F10 = "САЩ": президент на домакин, съответстващ на "Доналд Тръмп".
D4: D10 = "Индия": посещение на държава, съответстваща на "Индия".
G4: G10 = 2: въпросите са равни на две.
+0: преобразува логическите стойности в двоични (0 & 1).
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите, един път, когато американският президент "Доналд Тръмп" беше домакин на Индия и издава по -големи от два. Това показва, че формулата извлича броя на съвпаденията в съответния масив. Тъй като има 2 пъти, когато американският президент „Доналд Тръмп“ беше домакин на Индия, но проблемите бяха или 1, или 3, но тук се нуждаем от въпроси, които да бъдат по -големи от 1, което е 3 лъжи през 2019 г.
С въпроси, които не се разглеждат в критериите:
Тук, за да бъде лесно и удобно за разбиране, ние ще преброим колко пъти общо американският президент е посетил Индия от 2014 г. според данните.
Условия, изброени по -долу:
Американският президент посети Индия общо от 2014 г.
Използвайте формулата:
| = SUMPRODUCT ((F4: F10 = "Индия")+0, (D4: D10 = "САЩ")+0) |
F4: F10 = "САЩ": приемащата държава отговаря на "САЩ".
D4: D10 = "Индия": посещение на държава, съответстваща на "Индия".
G4: G10 = 2: въпросите са равни на две.
+0: преобразува логическите стойности в двоични (0 & 1).
Тук диапазонът е даден като референтна клетка. Натиснете Enter, за да получите броя.
Както можете да видите, 2 пъти, когато САЩ са посещавали Индия и издават по -големи от два. Това показва, че формулата извлича броя на съвпаденията в съответния масив. Както имаше един път, когато американският президент „Барак Обама“ посети Индия през 2015 г. и един път, когато американският президент „Доналд Тръмп“ посети Индия през 2020 г.
Можете също така да изпълнявате диапазони като критерии. Пребройте клетките, където 2 диапазона отговарят на критериите. Научете повече за Countif с SUMPRODUCT в Excel тук.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа.
- Масивите във формулата трябва да са равни по дължина, тъй като формулата връща грешка, когато не.
- Функцията SUMPRODUCT разглежда нецифрени стойности като 0s.
- Функцията SUMPRODUCT разглежда логическата стойност TRUE като 1 и False като 0.
- Масивът на аргументите трябва да бъде със същия размер, в противен случай функцията връща грешка.
- Функцията SUMPRODUCT връща сумата след вземане на отделни продукти в съответния масив.
- Оператори като равен на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се извърши в рамките на приложена формула, само с числа.
Надявам се, че тази статия за Как да броите редове, които отговарят на множество критерии в Excel, е обяснима. Намерете още статии за формулите за броене тук. Ако нашите блогове са ви харесали, споделете го с вашите първенци във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Намерете последния ред данни с числа в Excel : В диапазон от текстови стойности намерете последните данни в Excel.
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
COUNTIFS с динамичен диапазон от критерии : Преброяване на клетките с дефиниция на други стойности на клетки в Excel.
КОНФИЦИИ Съответствие по два критерия : Брой клетки, отговарящи на два различни критерия в списъка в Excel.
ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии : Преброяване на клетки с множество съвпадащи критерии с помощта на функцията ИЛИ.
Функцията COUNTIFS в Excel : Преброяване на клетки в зависимост от други стойности на клетките.
Как да използвате Countif във VBA в Microsoft Excel : Пребройте клетките, като използвате кода на Visual Basic за приложения.
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.