Пребройте няколко диапазона с един критерий в Microsoft Excel

В тази статия ще научим да броим множество диапазони с един критерий в Microsoft Excel.

Сценарий:

С прости думи, докато работим с таблици с данни, понякога се налага да броим клетките, където повече от два диапазона отговарят на критериите. В Excel можете да изпълнявате задачи като операции в множество диапазони, като използвате формулата, обяснена по -долу. Критериите могат да се прилагат върху текст, число или частично съвпадение в Excel. Критерии във формулата, изпълнени с помощта на операторите. Оператори като равен на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на ().

Как да решим проблема?

За този проблем ще трябва да използваме функцията SUMPRODUCT. Сега ще направим формула от функцията. Тук ни се дават два диапазона от данни и трябва да преброим редовете, които отговарят на 3 критерия. Функцията SUMPRODUCT връща SUM на съответните TRUE стойности (като 1) и игнорира стойности, съответстващи на FALSE стойности (като 0) във върнатия масив

Обща формула:

= 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, което е по -малко от 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 връща сумата след вземане на отделни продукти в съответния масив.

Надявам се, че тази статия за Преброяване на няколко диапазона с един критерий в Microsoft Excel е обяснима. Намерете още статии за изчисляване на стойности и свързани формули на Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта.

Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.

Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.

COUNTIFS с динамичен диапазон от критерии : Пребройте клетките, избирайки критериите от списъка с опции в клетката с критерии в Excel, като използвате инструмента за валидиране на данни.

КОНФИЦИИ Съответствие по два критерия : множество критерии съвпадат в различни списъци в таблицата, използвайки функцията COUNTIFS в Excel

ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии : съвпадат две или повече имена в същия списък, като се използват критериите ИЛИ, приложени към списъка в Excel.

Как да използвате Countif във VBA в Microsoft Excel : Преброяване на клетки с критерии, използвайки код на Visual Basic за приложения в макроси на Excel.

Как да използвате заместващи символи в Excel : Преброяване на клетки, съвпадащи с фрази в текстови списъци, използвайки заместващи символи ( * , ? , ~ ) в Excel

Популярни статии:

Как да използвате функцията IF в Excel : Изразът IF в Excel проверява условието и връща конкретна стойност, ако условието е TRUE или връща друга конкретна стойност, ако FALSE.

Как да използвате функцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.

Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.