В тази статия ще научим как да броим клетки, които не са равни на много неща в Excel.
С прости думи, помислете за сценарий, при който се изисква да преброим данните въз основа на специфични критерии в масив. броене на клетки, които не съдържат стойности за улавяне на стойността на клетката с Excel функция, обяснена тук с пример. Използвайте заместващи знаци за улавяне на низове и изпълнявайте функции върху него.
За целта ще използваме следните функции.
- Функция SUMPRODUCT
- MATCH функция
- ISNA функция
- COUNTA функция SEO
- COUNTIFS функция
Сега ще научим за тях накратко. Имаме различни методи да направим същото. Така че ще го видим един по един.
Първият метод
Пребройте клетки, които не съдържат стойности в диапазон. Тук имаме формула за това. Ще използваме функцията MATCH, за да намерим къде са стойностите, които трябва да бъдат преброени и функцията ISNA улавя грешка #NA. Тогава SUMPRODUCT връща сумата от стойностите.
- Функция SUMPRODUCT
- MATCH функция
- ISNA функция
Обща формула:
| = SUMPRODUCT ( - (ISNA (MATCH (диапазон, стойности, 0))) |
Променливи:
range: масив от клетки за броене
ценности: масив от стойности, които да се игнорират в обхвата.
Пример
Нека разберем тази функция, като я използваме в пример.
Тук имаме плодове в една колона и зеленчуци в друга. Първите похоти имат някои зеленчуци в списъка и трябва да броим само плодовете, без да се съобразяваме със зеленчуците.
Използвайте формулата:
| = СУМПРОДУКТ ( -- (ISNA (MATCH (плодове, зеленчуци, 0)))) |
плодове: именуван диапазон за масива B2: B10
Зеленчуци: именен диапазон, използван за масива D3: D5
Обяснение:
- Функцията MATCH връща позицията на всеки индекс, където стойностите са съпоставени в диапазона или връща грешка #NA, където не е намерена.
- Функцията ISNA улавя грешка #NA и връща TRUE за грешката #NA и FALSE другаде.
- -- операторът преобразува всички TRUEs в 1s и FALSEs в 0s.
- Функцията SUMPRODUCT връща сумата от всички числа в масива, което наистина е броят на клетките, където стойностите не се вземат предвид.
Както можете да видите, формулата връща броя на плодовете, без да отчита никаква стойност от зеленчуците.
Втори метод:
Ще обмислим други методи, тъй като различните видове проблеми имат различни решения. Използване на функцията COUNTIFS в Excel.
Обща формула:
| = COUNTIFS (диапазон, "стойност1", диапазон, "стойност2", …) |
Променливи:
range: масив от клетки за броене
value1: стойност за игнориране в диапазона
value2: стойност за игнориране в диапазона
Пример
Нека разберем тази функция, като я използваме в пример.
Тук имаме плодове в една колона и зеленчуци в друга. Първата колона съдържа някои зеленчуци в списъка и трябва да преброим само плодовете, без да обмисляме зеленчуците.
Използвайте формулата:
| = COUNTIFS (плодове, "" & D3, плодове, "" & D4, плодове, "" & D5) |
плодове: именуван диапазон за масива B2: B10
D3, D4 и D5 са препратките към клетки, използвани във формулата
Обяснение:
- Тук диапазонът се проверява с отделните стойности и връща броя на клетките.
- операторът е не е равно на
- & оператор, обединете стойностите с референтната стойност на клетката.
Използвайте формулата:
| = COUNTIFS (плодове, "" & D3, плодове, "" & D4, плодове, "" & D5) |
плодове: именуван диапазон за масива B2: B10
D3, D4 и D5 са препратките към клетки, използвани във формулата
Обяснение:
- Тук диапазонът се проверява с отделните стойности и връща броя на клетките.
- операторът е не е равно на
- & обединете оператора със стойност, използвана от препратка към клетка.
Както можете да видите, формулата връща броя на плодовете, без да отчита никаква стойност от зеленчуците.
Трети метод
Ще обмислим други методи, тъй като различните видове проблеми имат различни решения. Използване на функцията COUNTA в Excel. Тук логиката е да се изчисли броят на клетките със стойности и след това да се извади от броя на всички стойности в диапазона.
Обща формула:
| = COUNTA (диапазон) - SUMPRODUCT (COUNTIF (диапазон, стойности)) |
Променливи:
range: масив от клетки за броене
ценности: масив от стойности, които да се игнорират в обхвата.
Пример
Нека разберем тази функция, като я използваме в пример.
Тук имаме плодове в една колона и зеленчуци в друга. Първите похоти имат някои зеленчуци в списъка и трябва да броим само плодовете, без да се съобразяваме със зеленчуците.
Използвайте формулата:
| = COUNTA (плодове) - СУМПРОДУКТ (COUNTIF (плодове, зеленчуци)) |
плодове: именуван диапазон за масива B2: B10
Зеленчуци: именен диапазон, използван за масива D3: D5
Обяснение:
- Функцията COUNTIF преброява клетките в плодовете, съдържащи зеленчуци.
- SUMPRODUCT получава сумата, като вземе предвид всички стойности.
- Функцията COUNTA изчислява броя на непразни клетки в диапазон.
- Разликата ще доведе до броя на клетките (плодове), които нямат стойности (зеленчуци).
Както можете да видите, формулата връща броя на плодовете, без да отчита никаква стойност от зеленчуците.
Надявам се, че сте разбрали как да броите клетки, които не са равни на много неща в Excel. Разгледайте още статии за функциите на Excel Count тук. Моля, не се колебайте да заявите вашето запитване или обратна връзка за горната статия.
COUNTIFS с динамичен диапазон от критерии: Преброяване на клетки с динамичен диапазон от критерии с помощта на функцията COUNTIFS.
КОНФИЦИИ Съответствие по два критерия: Преброяване на клетки с множество критерии, като се използва функцията COUNTIFS.
ИЗЧИСЛЕНИЯ С ИЛИ За множество критерии: Пребройте клетките, като използвате логическите функции с функцията COUNTIFS.
Функцията COUNTIFS в Excel: използване на функция COUNTIFS за преброяване на клетки с множество критерии
Как да използвате Countif във VBA в Microsoft Excel: Използвайте функцията COUNTIF във VBA за преброяване на клетки.
Как да използвате заместващи символи в Excel: Хванете фрази и ключови думи, като използвате обяснените заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност
Редактиране на падащ списък
Ако с условно форматиране
Ако с заместващи символи
Преглед по дата