Как да преброя филтрирани клетки с текст в Excel?
В Excel преброяването на клетки с текст е просто. Но когато става въпрос за преброяване на филтрирани клетки с текст, нещата стават трудни. Този урок ще въведе три начина за преброяване на клетките с текст от филтриран списък.
Преброяване на филтрирани клетки с текст с помощта на помощна колона
Използвайки функцията COUNTIF, в допълнение с помощта на помощна колона, можем лесно да преброим филтрираните клетки с текст. Моля, направете следното.
1. Моля, копирайте формулата по-долу в клетка D2, след което натиснете клавиша Enter, за да получите първия резултат.
=МЕЖДИНЕН ОБЩ (103, A2)
Забележка: Помощната колона с формулата МЕЖДИННА СУМА е за тестване дали редът е филтриран или не. И 103 означава COUNTA функция в номер_функция аргумент.
2. Тогава плъзнете манипулатора за запълване надолу към клетките, които искате да приложите и тази формула.
3. Моля, копирайте формулата по-долу в клетка F2, след това натиснете Въведете ключ за получаване на крайния резултат.
=COUNTIFS(A2:A18,"*", D2:D18, 1)
Виждаме, че има 4 клетки с текст във филтрираните данни.
Друг метод за преброяване на филтрираните клетки с текст е използването на съчетание от SUMPRODUCT, SUBTOTAL, НЕПРЯК, РЕД & ISTEXT Функции. Моля, направете следното.
Моля, копирайте формулата по-долу в клетка E2, след това натиснете Въведете ключ за получаване на резултата.
=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18)))
Обяснение на формулата:
- РЕД(A2:A18) връща съответните номера на редове от диапазона A2:A18.
- ИНДИРЕКТНО("A"&РЕД(A2:A18)) връща валидните препратки към клетки от дадения диапазон.
- МЕЖДИНЕН ОБЩ(103, ИНДИРЕКТНО("A"&РЕД(A2:A18))) проверява дали редът е филтриран или не и връща 1 за видимите клетки, 0 за скритите и празните клетки.
- ISTEXT(A2:A18) проверява дали всяка клетка в диапазон A2:A18 съдържа текст и връща True за клетките с текст, False за другите клетки. Двойният унарен оператор (--) принуждава стойностите TRUE и FALSE към 1 и 0.
- SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A18))), --(ISTEXT(A2:A18))) може да се разглежда като SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). След това SUMPRODUCT умножава двата масива и връща сумата от стойности, която е 4.
Третият метод за преброяване на клетките с текст от филтрирани данни е съединяване - SUMPRODUCT, SUBTOTAL, ИЗМЕСТВАНЕ, MIN, РЕД & ISTEXT Функции. Моля, направете следното.
Моля, копирайте формулата по-долу в клетка E2, след това натиснете Въведете ключ за получаване на резултата.
=SUMPRODUCT(МЕЖДУНАРОДЕН(103, ОТСТЪПКА(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18)) )
Обяснение на формулата:
- OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1) връща отделните препратки към клетки от диапазон A2:A18.
- SUBTOTAL(103, OFFSET(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)) проверява дали редът е филтриран или не и връща 1 за видимите клетки, 0 за скритите и празните клетки.
- ISTEXT(A2:A18) проверява дали всяка клетка в диапазон A2:A18 съдържа текст и връща True за клетките с текст, False за другите клетки. Двойният унарен оператор (--) принуждава стойностите TRUE и FALSE към 1 и 0.
- SUMPRODUCT(МЕЖДУНАРОДЕН(103, ОТСТЪПКА(A2:A18, ROW(A2:A18)-2 -- MIN(ROW(A2:A18)-2),,1)), -- (ISTEXT(A2:A18))) може да се разглежда като SUMPRODUCT({1;1;1;1;1;1;1;1;1}, {0;0;0;1;1;0;0;1;1}). След това SUMPRODUCT умножава двата масива и връща сумата от стойности, която е 4.
Други операции (статии)
Комбинирайте функциите COUNTIF и LEFT, за да преброите клетките да започнат с определен знак в Excel
За да преброите броя на клетките, които започват с определен знак в Excel, можете да комбинирате функциите COUNTIF и LEFT, за да го направите. Всъщност има различни видове формули, които могат да го постигнат. Този урок ще ви води стъпка по стъпка.
Как да преброим клетки, съдържащи числа или не в Excel?
Ако има диапазон от клетки, някои от които съдържат числа, а другите съдържат текст, как можете да преброите клетките, съдържащи числа или не бързо в Excel?
Как да преброим клетки, ако един от множество критерии е изпълнен в Excel?
Какво става, ако преброяването на клетки съдържа един от множество критерии? Тук ще споделя начините за преброяване на клетки, ако съдържат X или Y или Z ... и т.н. в Excel.
Как да броим клетки с конкретен текст и цвят на запълване/шрифт в Excel?
Знаете ли как да броите клетки с множество условия? Например, пребройте броя на клетките, които съдържат конкретен текст и цвят на шрифта/запълването. Тази статия ще ви покаже решението.
Най-добрите инструменти за производителност в офиса
Kutools за Excel решава повечето от вашите проблеми и увеличава вашата производителност с 80%
- Супер Формула Бар (лесно редактиране на няколко реда текст и формула); Оформление за четене (лесно четене и редактиране на голям брой клетки); Поставяне във филтриран диапазон...
- Обединяване на клетки/редове/колони и съхраняване на данни; Съдържание на разделени клетки; Комбинирайте дублиращи се редове и сума/средно... Предотвратяване на дублиращи се клетки; Сравнете диапазони...
- Изберете Дублиран или Уникален редове; Изберете Празни редове (всички клетки са празни); Super Find и Fuzzy Find в много работни тетрадки; Произволен избор...
- Точно копие Множество клетки без промяна на референтната формула; Автоматично създаване на препратки към множество листа; Вмъкване на куршуми, квадратчета за отметка и други...
- Любими и бързо вмъкнати формули, диапазони, диаграми и снимки; Шифроване на клетки с парола; Създаване на пощенски списък и изпращайте имейли...
- Извличане на текст, Добавяне на текст, Премахване по позиция, Премахване на пространството; Създаване и отпечатване на междинни суми за пейджинг; Конвертиране на съдържание и коментари между клетки...
- Супер филтър (запазване и прилагане на филтърни схеми към други листове); Разширено сортиране по месец/седмица/ден, честота и други; Специален филтър с удебелен шрифт, курсив...
- Комбинирайте работни тетрадки и работни листове; Обединяване на таблици въз основа на ключови колони; Разделете данните на няколко листа; Пакетно конвертиране на xls, xlsx и PDF...
- Групиране на обобщена таблица по номер на седмицата, ден от седмицата и други... Показване на отключени, заключени клетки с различни цветове; Маркирайте клетки, които имат формула/име...
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
