Как заставить Excel не считаться в ячейке

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Используйте СЧЁТЕСЛИ, одну из статистических функций, чтобы подсчитать количество ячеек, соответствующих критерию; например, чтобы подсчитать, сколько раз тот или иной город появляется в списке клиентов.

В своей простейшей форме СЧЁТЕСЛИ говорит:

=COUNTIF(Где вы хотите искать?, Что вы хотите искать?)

СЧЁТЕСЛИ(диапазон, критерии)

Имя аргумента

Описание

диапазон (обязательно)

Группа ячеек, которую вы хотите подсчитать. Диапазон может содержать числа, массивы, именованный диапазон или ссылки, содержащие числа. Пустые и текстовые значения игнорируются.

критерии (обязательно)

Число, выражение, ссылка на ячейку или текстовая строка, определяющая, какие ячейки будут учитываться.

Например, вы можете использовать число, например 32, сравнение, например ">32", ячейку, например B4, или слово, например "яблоки".

COUNTIF использует только один критерий. Используйте СЧЁТЕСЛИМН, если вы хотите использовать несколько критериев.

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа.

Описание

Подсчитывает количество ячеек с яблоками в ячейках с A2 по A5. Результат равен 2.

Подсчитывает количество ячеек с персиками (значение в A4) в ячейках с A2 по A5. Результат равен 1.

Подсчитывает количество яблок (значение в A2) и апельсинов (значение в A3) в ячейках с A2 по A5. Результат равен 3. В этой формуле дважды используется СЧЁТЕСЛИ, чтобы указать несколько критериев, по одному критерию на выражение. Вы также можете использовать функцию СЧЁТЕСЛИМН.

Подсчитывает количество ячеек со значением больше 55 в ячейках с B2 по B5. Результат равен 2.

Подсчитывает количество ячеек со значением, отличным от 75, в ячейках с B2 по B5. Амперсанд (&) объединяет оператор сравнения для не равно (<>) и значение в B4 для чтения =СЧЁТЕСЛИ(B2:B5,"<>75"). Результат равен 3.

Подсчитывает количество ячеек со значением больше (>) или равным (=) 32 и меньше (

Распространенные проблемы

Что пошло не так

Для длинных строк возвращается неверное значение.

Функция СЧЁТЕСЛИ возвращает неверные результаты, если вы используете её для сопоставления строк длиннее 255 символов.

Чтобы сопоставить строки длиннее 255 символов, используйте функцию СЦЕПИТЬ или оператор объединения &. Например, =СЧЁТЕСЛИ(A2:A5,"длинная строка"&"еще одна длинная строка").

Значение не возвращается, когда вы ожидаете значение.

Не забудьте заключить аргумент criteria в кавычки.

Эта ошибка возникает, когда формула, содержащая функцию, ссылается на ячейки или диапазон в закрытой книге, а ячейки вычисляются. Чтобы эта функция работала, должна быть открыта другая книга.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Функция COUNT подсчитывает количество ячеек, содержащих числа, и подсчитывает числа в списке аргументов. Используйте функцию COUNT, чтобы получить количество записей в числовом поле, которое находится в диапазоне или массиве чисел. Например, вы можете ввести следующую формулу для подсчета чисел в диапазоне A1:A20: =COUNT(A1:A20). В этом примере, если пять ячеек в диапазоне содержат числа, результатом будет 5.

Синтаксис

Синтаксис функции COUNT имеет следующие аргументы:

значение1 Обязательно. Первый элемент, ссылка на ячейку или диапазон, в котором вы хотите подсчитать числа.

значение2, . Необязательный. До 255 дополнительных элементов, ссылок на ячейки или диапазонов, в которых вы хотите подсчитывать числа.

Примечание. Аргументы могут содержать различные типы данных или ссылаться на них, но учитываются только числа.

Примечания

Учитываются аргументы, которые являются числами, датами или текстовым представлением чисел (например, число, заключенное в кавычки, например "1").

Учитываются логические значения и текстовые представления чисел, которые вы вводите непосредственно в список аргументов.

Аргументы, представляющие собой значения ошибок или текст, который нельзя преобразовать в числа, не учитываются.

Если аргумент является массивом или ссылкой, учитываются только числа в этом массиве или ссылке. Пустые ячейки, логические значения, текст или значения ошибок в массиве или ссылке не учитываются.

Если вы хотите подсчитать логические значения, текст или значения ошибок, используйте функцию СЧЁТ.

Если вы хотите подсчитывать только числа, соответствующие определенным критериям, используйте функцию СЧЁТЕСЛИ или функцию СЧЁТЕСЛИМН.

Пример

Скопируйте данные примера из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы формулы отображали результаты, выберите их, нажмите F2, а затем нажмите клавишу ВВОД. При необходимости вы можете изменить ширину столбцов, чтобы увидеть все данные.

Для подсчета ячеек, не равных ни одному из многих элементов, вы можете использовать формулу, основанную на функциях ПОИСКПОЗ, ЕСНА и СУММПРОИЗВ. В показанном примере формула в ячейке F5:

где "данные" — это именованный диапазон B5:B16, а "исключить" — именованный диапазон D5:D7.

Во-первых, немного контекста. Обычно, если у вас есть только пара вещей, которые вы не хотите считать, вы можете использовать COUNTIFS следующим образом:

Но это не очень хорошо масштабируется, если у вас есть список из многих вещей, потому что вам придется добавить дополнительную пару диапазон/критерий для каждой вещи, которую вы не хотите учитывать. Было бы намного проще создать список и передать ссылку на этот список как часть критериев. Это именно то, что делает формула на этой странице.

По сути, эта формула использует функцию ПОИСКПОЗ для поиска ячеек, не равных "a", "b" или "c" с этим выражением:

Обратите внимание, что искомое значение и массив искомого массива «обратны» по сравнению с обычной конфигурацией — мы предоставляем все значения из именованного диапазона «данные» в качестве искомых значений, а все значения, которые мы хотим исключить из именованного диапазона, «исключаем». Поскольку мы даем MATCH более одного значения поиска, мы получаем более одного результата в массиве, подобном этому:

Затем мы используем двойное отрицание, чтобы привести TRUE к 1, а FALSE к нулю. Результирующий массив внутри СУММПРОИЗВ выглядит следующим образом:

Обрабатывая только один массив, СУММПРОИЗВ суммирует и возвращает окончательный результат 4.

Примечание. Использование СУММПРОИЗВ вместо СУММ избавляет от необходимости использовать Ctrl + Shift + Enter.

Счетчик минус совпадение

Еще один способ подсчета ячеек, не равных ни одному из нескольких параметров, — это подсчет всех значений и вычитание совпадений. Вы можете сделать это с помощью следующей формулы:

Здесь COUNTA возвращает количество всех непустых ячеек. Функция СЧЁТЕСЛИ, учитывая именованный диапазон «исключить», вернёт три счётчика, по одному для каждого элемента в списке. СУММПРОИЗВ суммирует общее количество, и это число вычитается из числа всех непустых ячеек. Конечным результатом является количество ячеек, которые не равны значениям в «исключить».

Литералы содержат логику типов

Формула на этой странице рассчитывается по логике "равно".Если вам нужно посчитать ячейки, которые не содержат много строк, где «содержит» означает, что строка может появиться в любом месте ячейки, вам понадобится более сложная формула.

Таблицы Excel — отличный способ хранения и анализа данных. Электронные таблицы обычно состоят из ячеек с комбинацией чисел и текста. Чтобы лучше понять ваши данные, вам нужно различать ячейки с текстом. Это намного проще, чем кажется.

В этой статье объясняется, как выбрать диапазон ячеек, а затем использовать функцию «СЧЕТЕСЛИ» для подсчета ячеек, содержащих текст. Кроме того, вы узнаете, как находить определенный, повторяющийся и цветной текст в электронной таблице.

Как подсчитать ячейки с текстом в Excel на ПК с Windows

Чтобы подсчитать ячейки с текстом в электронной таблице на компьютере с Windows, выполните следующие действия:

Как считать ячейки с текстом в Excel на Mac

Действия для подсчета ячеек с текстом в электронной таблице через macOS такие же, как и в Excel для Windows:

Запустите Excel, затем откройте таблицу, которую хотите проанализировать. Вы также можете просто "дважды щелкнуть" файл, если он использует Excel в качестве программы по умолчанию.
Выберите пустую ячейку в электронной таблице, чтобы ввести формулу.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек в указанном диапазоне с текстом в них.
В поле "диапазон" введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки от B2 до B10, введите « B2:B10 ».
В части «критерии» введите « «*» » в кавычках. Это подсчитывает количество ячеек с текстом в введенном вами диапазоне. Например, ваша полная формула должна выглядеть примерно так: «=СЧЕТЕСЛИ (B2:B10, «*»)».
На клавиатуре нажмите Enter, чтобы применить формулу. Результат появится в выбранной ячейке.

Как подсчитать ячейки с текстом в Excel 365

Чтобы подсчитать ячейки, содержащие текст в электронной таблице, с помощью Excel 365, вы применяете ту же функцию «СЧЁТЕСЛИ», которая используется в Excel для Windows и macOS. Вот как:

Откройте таблицу Excel, которую вы хотите изучить.
Нажмите пустую ячейку, чтобы ввести формулу.
В пустой ячейке введите: « =СЧЁТЕСЛИ (диапазон, критерии) ». Эта формула подсчитывает количество ячеек с текстом в указанном диапазоне ячеек.
В части «диапазон» введите диапазон ячеек, который вы хотите просмотреть. Введите первую и последнюю ячейки, разделенные двоеточием. Например, чтобы подсчитать ячейки с C2 по C11, введите « C2:C1 ».
В поле "критерии" введите или вставьте ""*"" в кавычках. Это подсчитывает количество ячеек (в указанном диапазоне), в которых есть текст. Например, полная формула должна выглядеть примерно так: « =СЧЁТЕСЛИ (C2:C11, «*»)».
Теперь нажмите Enter, чтобы применить только что созданную формулу. Результат отобразится в ячейке формулы.

Как подсчитать ячейки с текстом в Excel в приложении для iPhone

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel на iPhone, выполните следующие действия:

Как подсчитать ячейки с текстом в Excel в приложении для Android

Чтобы подсчитать количество ячеек с текстом в электронной таблице с помощью приложения Excel для Android, выполните следующие действия:

Запустите приложение Excel для Android
Откройте таблицу, которую хотите просмотреть, нажав Открыть, чтобы просмотреть сохраненные таблицы, затем нажмите нужный файл, чтобы автоматически открыть его.< бр />
Дважды нажмите на пустой ячейке, чтобы ввести формулу «СЧЁТЕСЛИ». Либо нажмите и удерживайте пустую ячейку, затем нажмите Изменить во всплывающем меню.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек. Эта формула подсчитывает количество ячеек с текстом внутри диапазона ячеек.
Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки с E2 по E12 в одном столбце, введите « E2:E12 ».
Для части формулы "критерии" введите " "*" " с кавычками. Это подсчитывает количество ячеек с текстом в указанном диапазоне, включая более одной строки. Ваша полная формула должна выглядеть примерно так: " =СЧЁТЕСЛИ (A2:E12, "*") ".
Теперь нажмите Ввод, чтобы применить формулу. Результат отображается в ячейке формулы.

Как подсчитать количество ячеек в Excel с определенным текстом

С помощью функции «СЧЁТЕСЛИ» вы можете подсчитать количество ячеек, содержащих определенные текстовые строки, например «Excel», «Джон» или «Джон Мейерс». Формула аналогична подсчету ячеек, содержащих любой текст, но вы изменяете часть формулы «критерии» для поиска определенного текста. В этом примере вы увидите, сколько раз слово «Excel» появляется в определенном диапазоне ячеек:

Запустите Excel на своем устройстве и откройте "назначенный файл" или просто запустите "назначенный файл", если он настроен на открытие Excel по умолчанию.
Нажмите пустую ячейку, чтобы ввести формулу.
В пустой ячейке введите « =СЧЁТЕСЛИ (диапазон, критерии) » без кавычек.
Для части формулы "диапазон" введите диапазон ячеек, который вы хотите подсчитать. Введите первую и последнюю ячейки, разделенные двоеточием. Чтобы подсчитать ячейки от A2 до A20, введите « A2:A20 » без кавычек.
В разделе формулы "критерии" введите ""Excel"" с кавычками. Это подсчитывает количество ячеек с «Excel» в указанном диапазоне. Ваша формула должна выглядеть примерно так: « =СЧЕТЕСЛИ (A2:A20, «Excel») ».

Как подсчитать ячейки с повторяющимся текстом в Excel

Помимо подсчета ячеек с текстом и определенного текста, вы можете подсчитать количество ячеек с повторяющимся содержимым.

В следующем примере мы ищем повторяющиеся оценки учащихся. Наша таблица настроена следующим образом:

Столбец A – список наших учеников A2:A10.
Столбец B – перечислены оценки каждого учащегося (A, B или C).
Столбец D — список доступных оценок. D2 – "А", D3 – "В" и D4 – "В".
Столбец E — количество баллов по каждой оценке.

Подсчет ячеек с повторяющимся текстом, включая первый экземпляр

Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", включая первый экземпляр, введите следующие формулы:

Теперь у вас есть количество повторяющихся оценок, включая первый экземпляр, указанный в столбце "E".

Подсчет ячеек с повторяющимся текстом, исключая первый экземпляр

Чтобы подсчитать количество ячеек в электронной таблице с экземплярами оценок "A", "B" или "C", исключая первый экземпляр, введите следующие формулы:

Для экземпляров класса "A", за исключением первого экземпляра, щелкните ячейку E2 и введите формулу "=СЧЁТЕСЛИ ($B$2 : $B$10, D2)-1".
Для экземпляров класса «B», за исключением первого экземпляра, щелкните ячейку E3 и введите формулу «=СЧЁТЕСЛИ ($B$2: $B$10, D3)-1».
Для экземпляров категории "C", за исключением первого экземпляра, щелкните ячейку E4 и введите формулу "=СЧЁТЕСЛИ ($B$2: $B$10, D4)-1".

Теперь у вас есть количество повторяющихся оценок, за исключением первого экземпляра, указанного в столбце "E".

Как посчитать ячейки с цветным текстом в Excel

В Excel нет формулы для подсчета ячеек на основе цвета их текста. Чтобы обойти это, отфильтруйте результаты, а затем посчитайте. Вот как:

После того как вы нажмете Ввод, чтобы применить фильтр, Excel отобразит только ячейки, содержащие этот цвет, и скроет остальные значения.

Функция «ПРОМЕЖУТОЧНЫЙ ИТОГ» исключит значения в скрытых строках, поэтому возвращает количество только для выбранного цвета текста.

Поиск ячеек с текстом

Приложение Excel отлично справляется с хранением данных и упрощает их анализ. Он обрабатывает как текст, так и числа.

Его более четырехсот функций, включая функцию «СЧЁТЕСЛИ». Эта функция полезна для определения общего количества ячеек, содержащих определенную информацию, например ячеек с текстом, и количества вхождений определенного текста.

Удалось ли вам выяснить, что вам нужно знать о данных вашей электронной таблицы? Насколько полезным вы считаете Excel в целом? Поделитесь своим мнением в разделе комментариев ниже.

Как посчитать игнорирование нулей или пустых ячеек в Excel?

Предположим, что у вас есть диапазон данных, который включает в себя несколько нулевых значений и пустых ячеек, как показано на снимке экрана ниже, и ваша задача состоит в подсчете ячеек, игнорируя как нулевые значения, так и пустые ячейки, как вы можете быстро их правильно подсчитать?

Вот формула со стрелкой может помочь вам подсчитать ячейки, игнорируя нули и пустые ячейки.

Выберите пустую ячейку, в которую вы хотите поместить результат подсчета, и введите в нее эту формулу =СЧЕТ(ЕСЛИ(A1:E5<>0, A1:E5)) и нажмите клавиши Shift + Ctrl + Enter, чтобы получить результат.

Совет. В формуле A1:E5 — это диапазон ячеек, который вы хотите подсчитать, игнорируя как пустые ячейки, так и нулевые значения.

Если вы хотите подсчитать ячейки, исключая нулевые значения и пустые ячейки, а затем выбрать их, вы можете применить Kutools for ExcelВыбрать определенные ячейки, чтобы быстро справиться с этим.

После бесплатной установки Kutools for Excel сделайте следующее:

<р>1. Выберите ячейки, которые вы хотите подсчитать, и нажмите Kutools > Выбрать > Выбрать определенные ячейки. Смотрите скриншот:

<р>2. В диалоговом окне «Выбор определенных ячеек» установите флажок «Ячейка» в разделе «Тип выбора» и выберите «Не равен критерию» в первом раскрывающемся списке в разделе «Определенный тип», затем введите 0 в следующее текстовое поле. Смотрите скриншот:

<р>3. Нажмите "ОК" или "Применить". Появится диалоговое окно с напоминанием о том, сколько ячеек выбрано, и в то же время были выбраны все ячейки, кроме нулевых значений и пустых ячеек.

Если вы хотите, чтобы подсчет ячеек игнорировал только пустые ячейки, вы можете выбрать ячейки, а затем просмотреть результат подсчета в строке состояния.

Выберите ячейки, которые вы хотите подсчитать, за исключением пустых ячеек, а затем перейдите в правый нижний угол строки состояния, чтобы просмотреть результат подсчета. Смотрите скриншот:

Читайте также:


Добавление столбцов в сводную таблицу в Excel

Как рисовать в Word

В Word две страницы на одном листе как убрать

Как сделать сложную диаграмму в Excel

Zenkitpartnerconfig что это за программа для андроида