Часто задаваемые вопросы и проблемы
Я надеюсь, что эти примеры помогли вам понять, как работает функция Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникли вопросы, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.
- Возможен ли подсчет в несмежном диапазоне клеток?
Вопрос: Может ли быть использована функция СЧЕТЕСЛИ для несмежного диапазона или ячеек?
Ответ: Она не работает с несмежными диапазонами, синтаксис не позволяет указывать несколько отдельных ячеек в качестве первого параметра. Вместо этого вы можете использовать комбинацию нескольких функций СЧËТЕСЛИ:
Неправильно: =СЧËТЕСЛИ(A2;B3;C4;»>0″)
Правильно: =СЧËТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)
Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT на английском) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:
=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))
Или же
=СЧËТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)
- Амперсанд и кавычки в формулах СЧËТЕСЛИ
Вопрос: когда мне нужно использовать амперсанд?
Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.
Итак, вы можете придерживаться этих правил:
Если вы используете число или ссылку на ячейку в критериях точного соответствия, вам не нужны ни амперсанд, ни кавычки. Например:
=СЧËТЕСЛИ(A1:A10;10) или =СЧËТЕСЛИ(A1:A10;C1)
Если ваши условия содержат текст, подстановочный знак или логический оператор с числом, заключите его в кавычки. Например:
=СЧËТЕСЛИ(A2:A10;»яблоко») или =СЧËТЕСЛИ(A2:A10;»*») или =СЧËТЕСЛИ(A2:A10;»>5″)
Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:
=СЧËТЕСЛИ(A2:A10;»>»&D2) или = СЧËТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())
Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.
Например, СЧËТЕСЛИ(C2: C8;»<=5″) и = СЧËТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.
- Как сосчитать ячейки по цвету?
Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?
Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA. Читайте подробнее: Сумма по цвету и подсчет по цвету в Excel.
- Ошибка #ИМЯ?
Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?
Ответ: Скорее всего, вы указали неверный диапазон или ошиблись в имени функции. Пожалуйста, проверьте пункт 1 выше.
- Формула не работает
Проблема: моя формула не работает! Что я сделал не так?
Ответ: Если вы написали формулу, которая на первый взгляд верна, но она не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазон, условия, ссылки, использование амперсанда и кавычек.
Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….
Если вы используете функцию с несколькими критериями, разделите формулу на несколько частей и проверьте каждую из них отдельно.
И это все на сегодня. Надеюсь, функция СЧЕТЕСЛИ теперь правильно подсчитывает количество по заданному условию и не предстваляет для вас затруднений. В следующей статье мы рассмотрим несколько способов подсчета ячеек в Excel с несколькими условиями.
С определенным текстом или значением
Например, есть таблица, в которой указано, сколько килограмм определенного товара было продано за день. Посчитаем, сколько товаров было продано весом больше 5 килограмм. Для этого нужно посчитать сколько блоков в столбце Вес, где значение больше пяти. Функция будет выглядеть следующим образом: =СЧЁТЕСЛИ(В2:В13;»>5″)
. Она рассчитает количество блоков, содержимое в которых больше пяти.
Для того чтобы растянуть функцию на другие блоки, и скажем поменять условия, необходимо закрепить выбранный диапазон. Сделать Вы это сможете, используя абсолютные ссылки в Excel .
– количество ячеек с отрицательными значениями: =СЧЁТЕСЛИ(В2:В13;»;
– количество блоков, содержимое в которых больше (меньше) чем в А10 (для примера): =СЧЁТЕСЛИ(В2:В13;»>»&A10)
;
– ячейки, значение в которых больше 0: =СЧЁТЕСЛИ(В2:В13;»>0″)
;
– непустые блоки из выделенного диапазона: =СЧЁТЕСЛИ(В2:В13;»»)
.
Применять функцию СЧЁТЕСЛИ можно и для расчета ячеек в Excel, содержащих текст. Например, рассчитаем, сколько в таблице фруктов. Выделим область и в качестве критерия укажем «фрукт». Будут посчитаны все блоки, с данным словом. Можно не писать текст, а просто выделить прямоугольник, который его содержит, например С2
.
Для формулы СЧЁТЕСЛИ регистр не имеет значения, будут подсчитаны ячейки содержащие текст «Фрукт» и «фрукт».
В качестве критерия также можно использовать специальные символы: «*»
и «?»
. Они применяются только к тексту.
Посчитаем сколько товаров начинается на букву А: «А*»
. Если указать «абрикос*»
, то учтутся все товары, которые начинаются с «абрикос»: абрикосовый сок, абрикосовое варенье, абрикосовый пирог.
Символом «?»
можно заменить любую букву в слове. Написав в критерии «ф?укт»
– учтутся слова фрукт, фуукт, фыукт.
Чтобы посчитать слова в ячейках, которые состоят из определенного количества букв, поставьте знаки вопросов подряд. Для подсчета товаров, в названии которых 5 букв, поставим в качестве критерия «?????»
.
Если в качестве критерия поставить звездочку, из выбранного диапазона будут посчитаны все блоки, содержащие текст.
Обновление за апрель 2024 года:
Теперь вы можете предотвратить проблемы с ПК с помощью этого инструмента, например, защитить вас от потери файлов и вредоносных программ. Кроме того, это отличный способ оптимизировать ваш компьютер для достижения максимальной производительности. Программа с легкостью исправляет типичные ошибки, которые могут возникнуть в системах Windows — нет необходимости часами искать и устранять неполадки, если у вас под рукой есть идеальное решение:
- Шаг 1: (Windows 10, 8, 7, XP, Vista — Microsoft Gold Certified).
- Шаг 2: Нажмите «Начать сканирование”, Чтобы найти проблемы реестра Windows, которые могут вызывать проблемы с ПК.
- Шаг 3: Нажмите «Починить все», Чтобы исправить все проблемы.
Определяем, есть ли повторения
Не во всех случаях в Excel нужно посчитать количество повторений в столбце. Если нужно узнать, присутствуют ли значения, которые дублируются, но не нужно определять сколько раз, можно сделать отметку напротив каждой строки. Такой вариант подсчета в таблице может использоваться, например, для определения наличия определенных наименований без прямой привязки к их количеству.
Для этого немного модифицируем предыдущую формулу. Для проверки наличия дубликатов используется формула СЧЁТЕСЛИ. Однако она не подсчитывает, сколько раз слово в столбике повторяется. Она проверяет, больше ли вхождений, чем одно. Если это так, в результате отображается одна команда, если нет — другая.
После ввода необходимо кликнуть Enter. Напротив строки с определенным наименованием будет написано, встречается ли такая позиция или нет. Результат этой работы для нашей таблицы будет выглядеть следующим образом:
В целом, это довольно удобный способ выявления повторов, если не нужно найти их точное количество. Как и в предыдущем случае, этот метод можно применять к нескольким колонкам одновременно, если правильно проставить диапазон при указании аргументов.
Посчитать заполненные ячейки функцией СЧЁТЕСЛИ
Второй подход – использовать функцию СЧЁТЕСЛИ, которая более широко применяется и может легче запомниться:
=СЧЁТЕСЛИ(массив>;«<>»)
Первый и второй способы дали одинаковые результаты – 21 значение в массиве. Однако, если пересчитать вручную – их окажется только 20. Это и есть та особенность, о которой я говорил выше. В ячейке E5 содержится формула, которая результатом возвращает пустую строку. Запомните, функции СЧЁТЗ и СЧЁТЕСЛИ считают подобные ячейки непустыми.
Если для Вас это имеет значение, и пустые ячейки с формулами считать не нужно, используйте описанный дальше подход.
Как посчитать количество уникальных значений в Excel?
Как в Excel можно посчитать количество уникальных значений в столбце или определенном диапазоне?
Под уникальными значениямм понимаем те, которые встречаются в диапазоне только один раз.
Допустим значения содержатся в диапазоне С2:С9
Водим диапазон E2:E9 в ячейках которого содержится количество повторов каждого значения в диапазоне С2:С9
Формула количества повторов для значения C2 =СЧЁТЕСЛИ($C$2:$C$9;C2)
Эта формула растягивается на весь диапазон
Количество уникальных значений определится по формуле =СУММЕСЛИ(E2:E9;»=1″)
Чтобы увидеть уникальные элементы нужно наложить фильтр на столбец E
Если возникла задача посчитать количество уникальных значений в Экселе (Excel), то её можно решить несколькими способами — с формулами и без формул.
1) В том случае, если диапазон данных (числовых или текстовых) является статичным и меняться не будет, то можно воспользоваться командой «Удалить дубликаты».
Для этого выделяем диапазон (в нашем примере — это B1:B14) и на панели инструментов Excel выбираем «Данные» -> «Удалить дубликаты».
Появится окно, в котором указываются столбец / диапазон, из которых удаляются дубликаты.
Нужно, чтобы обязательно стояла галочка «Мои данные содержат заголовки» — иначе заголовок тоже будет учитываться при отборе уникальных значений.
Теперь нажимаем на кнопку «ОК».
В результате Эксель выведет сообщение о том, сколько найдено дубликатов и сколько осталось уникальных значений.
Таким образом, количество уникальных значений в выбранном диапазоне будет равно 10.
2) Для определения количества уникальных значений в Excel можно использовать специальную формулу, состоящую из 2 функций:
Функция СЧЁТЕСЛИ будет использоваться для того, чтобы посчитать, сколько раз встречается то или иное значение в диапазоне.
Далее с помощью функции СУММ суммируются дроби вида:
1 / Число вхождений каждого элемента.
Для нашего примера формула будет выглядеть так:
При этом вводить её нужно как формулу массива — через комбинацию Ctrl + Shift + Enter.
В результате получится всё те же 10 уникальных значений.
А вот примеры работы этой формулы, если в таблице Excel имеются текстовые данные:
Функция СЧЁТЕСЛИ: подсчет количества ячеек по определенному критерию в Excel | Эксель Практик
- при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом($D$1) =(170-ДЛСТР(ПОДСТАВИТЬ($D$1 ; D3 ;»»)))/ДЛСТР(D3) ;
- при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом($D$1) все значения Банан(D3) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР(D3) ;
- вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .
В следующем окне ставим галочку в пункте «Мои данные содержат заголовки» , если Вы выделили таблицу вместе с заголовками. Дальше отметьте галочками столбцы, в которых нужно найти повторы, и нажмите «ОК» .
СЧЕТЕСЛИ с несколькими условиями.
На самом деле функция СЧЁТЕСЛИ в Excel не предназначена для подсчёта количества ячеек по различным условиям. В большинстве случаев я рекомендую использовать ее множественный аналог — функцию СЧЁТЕСЛИМН. Он просто предназначен для подсчета количества ячеек, соответствующих двум или более условиям (логическое И). Однако некоторые проблемы можно решить, объединив две или более функций СЧЁТЕСЛИ в одно выражение.
Количество чисел в диапазоне
Одним из наиболее распространенных применений функции СЧЁТЕСЛИ с двумя критериями является определение количества чисел в определённом диапазоне, то есть меньше X, но больше Y.
Например, вы можете использовать для вычисления ячеек в диапазоне B2:B9, где значение больше 5 и меньше или равно 15:
=СЧЁТЕСЛИ(B2:B11;»>5″)-СЧЁТЕСЛИ(B2:B11;»>15″)
Количество ячеек с несколькими условиями ИЛИ.
Если вы хотите найти количество нескольких различных элементов в диапазоне, добавьте в выражение 2 или более функций СЧЁТЕСЛИ. Допустим, у вас есть список покупок, и вы хотите узнать, сколько в нем безалкогольных напитков.
Давай сделаем это:
=СЧЁТЕСЛИ(A4:A13;»лимонад»)+СЧЁТЕСЛИ(A2:A11;»*сок»)
Обратите внимание, что мы включили подстановочный знак (*) во второй критерий. Он используется для расчета количества всех видов сока в списке
Как вы понимаете, здесь можно добавить больше условий.
Использование функции COUNT
Функция COUNT в Excel позволяет подсчитать количество значений в столбце. Это очень полезная функция, особенно при работе с большими объемами данных. Применение функции COUNT позволяет легко определить, сколько элементов находится в столбце и использовать эту информацию в дальнейшем анализе данных.
Для использования функции COUNT необходимо выбрать ячейку, в которую необходимо вывести результат подсчета. Затем в формулу вводим название функции COUNT, открываем скобки и указываем диапазон ячеек, в котором находятся данные, количество которых нужно посчитать. Можно указать одну ячейку или диапазон ячеек с помощью двоеточия. После этого закрываем скобки и нажимаем Enter.
Результатом работы функции COUNT будет количество значений, содержащихся в указанном диапазоне ячеек. Функция COUNT считает только те ячейки, которые содержат числовые значения или текст. Если в столбце есть ячейки со значениями ошибок или пустые ячейки, функция COUNT их не учитывает.
Использование функции COUNT может быть очень полезным при анализе данных. Например, если в столбце содержатся данные о продажах, то с помощью функции COUNT можно подсчитать количество проданных товаров или количество сделанных операций. Также с помощью функции COUNT можно проверить, есть ли пустые ячейки или ячейки с ошибками в столбце и принять соответствующие меры.
Метод 3: использование функции СЧЕТ
Функция СЧЕТ подойдет, если вы работаете исключительно с числами. Ячейки, заполненные текстовыми значениями, этой функцией учитываться не будут. В остальном СЧЕТ почти идентичен СЧЕТЗ из ранее рассмотренного метода.
Так выглядит формула функции СЧЕТ:
- =СЧЕТ(ячейка1;ячейка2;…ячейкаN)
- =СЧЕТ(ячейка1:ячейкаN)
Алгоритм действий также похож на тот, что мы рассмотрели выше:
- Выбираем ячейку, где будет сохранен и отображен результат подсчета значений.
- Заходим в Мастер функций любым удобным способом, выбираем в категории “Статистические” необходимую строку СЧЕТ и щелкаем OK.
- В «Аргументах функции» задаем диапазон ячеек или перечисляем их. Далее жмем OK.
- В выбранной ячейке будет выведен результат. Функция СЧЕТ проигнорирует все ячейки с пустым содержанием или с текстовыми значениями. Таким образом, будет произведен подсчет исключительно тех ячеек, которые содержат числовые данные.
Как подсчитать уникальные значения в Excel
Допустим, у нас есть набор данных, как показано ниже:
Для целей этого урока я назову диапазон A2: A10 как NAMES. В дальнейшем мы будем использовать этот именованный диапазон в формулах.
В этом наборе данных есть повторение в диапазоне NAMES. Чтобы получить количество уникальных имен из этого набора данных (A2: A10), мы можем использовать комбинацию функций COUNTIF и SUMPRODUCT , как показано ниже:
Как работает эта формула?
Эта формула работает до тех пор, пока в диапазоне не останется пустых ячеек. Но если у вас есть пустые ячейки, он вернет # DIV / 0! ошибка.
Давайте сначала поймем, почему он возвращает ошибку, когда в диапазоне есть пустая ячейка. Предположим, у нас есть набор данных, как показано ниже (с пустой ячейкой A3):
Теперь, если мы используем ту же формулу, которую использовали выше, часть формулы COUNTIF возвращает массив . Поскольку в ячейке A3 нет текста, ее счетчик возвращается как 0.
И поскольку мы делим 1 на весь этот массив, возвращается # DIV / 0! ошибка.
Чтобы обработать эту ошибку деления в случае пустых ячеек, используйте следующую формулу:
Одно изменение, которое мы внесли в эту формулу, — это часть критериев функции COUNTIF. Мы использовали NAMES& ”” вместо NAMES. Таким образом формула вернет количество пустых ячеек (раньше она возвращала 0 — там, где была пустая ячейка).
В приведенном выше примере результат должен быть 5, но он возвращает 6, поскольку пустая ячейка считается одним из уникальных значений.
Вот формула, которая обрабатывает пустые ячейки и не учитывает их в конечном результате:
В этой формуле вместо 1 в качестве числителя мы использовали NAMES ””. Это возвращает массив значений ИСТИНА и ЛОЖЬ. Он возвращает FALSE всякий раз, когда есть пустая ячейка. Поскольку в расчетах ИСТИНА равно 1, а ЛОЖЬ равно 0, пустые ячейки не учитываются, так как числитель равен 0 (ЛОЖЬ).
Теперь, когда у нас готов базовый скелет формулы, мы можем пойти дальше и подсчитать различные типы данных.
Как подсчитать уникальные значения в Excel, которые являются текстом
Мы будем использовать ту же концепцию, о которой говорилось выше, для создания формулы, которая будет подсчитывать только уникальные текстовые значения.
Вот формула, которая будет подсчитывать уникальные текстовые значения в Excel:
Все, что мы сделали, это использовали формулу ISTEXT (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит текст, и ЛОЖЬ, если нет. Он не будет считать пустые ячейки, но будет считать ячейки с пустой строкой («»).
Как подсчитать уникальные числовые значения в Excel
Вот формула, которая будет подсчитывать уникальные числовые значения в Excel
Здесь мы используем ISNUMBER (NAMES) в качестве числителя. Он возвращает ИСТИНА, если ячейка содержит числовой тип данных, и ЛОЖЬ, если нет. Пустые ячейки не учитываются.
Как посчитать, содержит ли ячейка текст или часть текста в Excel?
Предположим, у вас есть данные, указанные ниже, и вы хотите подсчитать количество ячеек, содержащих текст «Apple», количество ячеек, содержащих текст»Оранжевая»и ячейки содержат»персик»отдельно, как это сделать? В этом руководстве мы объясняем COUNTIF Подробная функция, которая поможет вам быстро подсчитать количество ячеек, если они содержат текст или часть определенного текста в Excel. Кроме того, мы представляем замечательную функцию, позволяющую легко достичь ее с помощью только щелчков мышью.
Подсчитайте, если ячейка содержит текст или часть текста, с помощью функции СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ может помочь подсчитать ячейки, содержащие часть текста в диапазоне ячеек в Excel. Пожалуйста, сделайте следующее.
1. Выберите пустую ячейку (например, E5), скопируйте в нее приведенную ниже формулу и нажмите Enter ключ. Затем перетащите маркер заполнения вниз, чтобы получить все результаты.
=COUNTIF(B5:B10,»*»&D5&»*»)
Синтаксис
=COUNTIF (range, criteria)
аргументы
- Диапазон (обязательно): диапазон ячеек, которые вы хотите подсчитать.
- Критерии (обязательно): число, выражение, ссылка на ячейку или текстовая строка, определяющая, какие ячейки будут учитываться.
Базовые ноты:
- В формуле B5: B10 — это диапазон ячеек, который нужно подсчитать. D5 — это ссылка на ячейку, содержащую то, что вы хотите найти. Вы можете изменить ссылочную ячейку и критерии в формуле по своему усмотрению.
- Если вы хотите напрямую вводить текст в формуле для подсчета, примените следующую формулу:=COUNTIF(B5:B10,»*Apple*»)
- В этой формуле регистр не учитывается.
Самый большой Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро подсчитать количество ячеек в диапазоне, если они содержат определенный текст или часть текста. После получения результата во всплывающем диалоговом окне все совпавшие ячейки будут выбраны автоматически. .Загрузите Kutools for Excel прямо сейчас! (30-дневная бесплатная трасса)
Счетные ячейки содержат текст с функцией СЧЁТЕСЛИ
Как показано на скриншоте ниже, если вы хотите подсчитать количество ячеек в определенном диапазоне, которые содержат только текст, метод в этом разделе может вам помочь.
1. Выберите пустую ячейку для вывода результата, скопируйте в нее приведенную ниже формулу и нажмите Enter ключ.
=COUNTIF(B5:B10,»*»)
Подсчитайте, если ячейка содержит текст или часть текста с помощью Kutools for Excel
Чаевые: Помимо приведенной выше формулы, здесь представлена замечательная функция, позволяющая легко решить эту проблему. С Выбрать определенные ячейки полезности Kutools for Excel, вы можете быстро подсчитать, содержит ли ячейка текст или часть текста, щелкнув мышью. С помощью этой функции вы даже можете подсчитать с помощью OR или AND, если вам нужно. Пожалуйста, сделайте следующее.
Перед использованием Kutools for Excel, вам нужно потратить несколько минут, чтобы в первую очередь.
1. Выберите диапазон, в котором вы хотите подсчитать количество ячеек, содержащих определенный текст.
2. Нажмите Kutools > Выберите > Выбрать определенные ячейки.
3. в Выбрать определенные ячейки диалоговое окно, вам необходимо:
- Выберите Ячейка вариант в Тип выбора раздел;
- В Конкретный тип раздел, выберите Комплект в раскрывающемся списке введите Apple в текстовом поле;
- Нажмите OK кнопку.
- Затем появляется окно подсказки, в котором указано, сколько ячеек соответствует условию. Щелкните значок OK кнопка и все соответствующие ячейки выбираются одновременно.
Наконечник. Если вы хотите получить бесплатную (60-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Используйте countif с несколькими критериями в Excel В Excel функция СЧЁТЕСЛИ может помочь нам вычислить количество определенного значения в списке. Но иногда нам нужно использовать несколько критериев для подсчета, это будет сложнее. Из этого туториала Вы узнаете, как этого добиться.Нажмите, чтобы узнать больше …
Подсчитайте, начинаются ли ячейки или заканчиваются определенным текстом в Excel Предположим, у вас есть диапазон данных, и вы хотите подсчитать количество ячеек, которые начинаются с «kte» или заканчиваются «kte» на листе. Эта статья знакомит вас с некоторыми хитростями вместо ручного подсчета.Нажмите, чтобы узнать больше …
Подсчитайте конкретное значение на нескольких листах Как можно подсчитать конкретные значения на нескольких листах? Например, получить количество вхождений определенного значения «Excel» из нескольких листов. В этом руководстве представлены методы, позволяющие это сделать.Нажмите, чтобы узнать больше …
Метод 5: функция СЧЁТЕСЛИМН
Функция СЧЁТЕСЛИМН представляет из себя еще более гибкий инструмент для подсчета ячеек, позволяющий определить количество соответствий нескольким критериям. Для каждого диапазона можно указать свой критерий и всего подобных комбинаций может быть 126.
- Выбираем ячейку для вывода конечного результата и кликаем по кнопке “Вставить функцию”, после чего откроется Мастер функций.
- останавливаемся на категории “Полный алфавитный перечень“;
- в списке предлагаемых функций кликаем по оператору “СЧЁТЕСЛИМН” и подтверждаем действие нажатием кнопки OK.
- Теперь нам нужно настроить аргументы оператора СЧЕТЕСЛИМН. Указываем адрес первого диапазона ячеек, затем в следующем поле прописываем условие для этого диапазона. После этого появится поле для ввода координат второго диапазона условий, затем – условия для него и т.д. Как уже было отмечено выше, всего таких диапазонов с привязанными к ним критериями отбора может быть 126 (в нашем случае мы ограничимся двумя). Когда все готово, жмем кнопку OK.Примечание: Как и при использовании вышеописанных функций, формулу с заполненными аргументами можно сразу прописать в результирующей ячейке. =СЧЁТЕСЛИМН(диапазон_условия1;условие1; диапазон_условия2;условие2;…).
- В результате мы видим в заданной ячейке количество, удовлетворяющие условиям функции.
Нулевые строки.
Формулы для подсчета пустых ячеек.
Функция СЧИТАТЬПУСТОТЫ.
Функция СЧИТАТЬПУСТОТЫ предназначена для подсчета пустых ячеек в указанном диапазоне. Она относится к категории статистических функций и доступна во всех версиях Excel начиная с 2007.
Синтаксис этой функции очень прост и требует только одного аргумента:
СЧИТАТЬПУСТОТЫ(диапазон)
Где диапазон — это область вашего рабочего листа, в которой должны подсчитываться позиции без данных.
Вот пример формулы в самой простейшей форме:
Чтобы эффективно использовать эту функцию, важно понимать, что именно она подсчитывает
- Содержимое в виде текста, чисел, дат, логических значений или ошибок, не учитывается.
- Нули также не учитываются, даже если скрыты форматированием.
- Формулы, возвращающие пустые значения («»), — учитываются.
Глядя на рисунок выше, обратите внимание, что A7, содержащая формулу, возвращающую пустое значение, подсчитывается по-разному:
- СЧИТАТЬПУСТОТЫ считает её пустой, потому что она визуально кажется таковой.
- СЧЁТЗ обрабатывает её как имеющую содержимое, потому что она фактически содержит формулу.
Это может показаться немного нелогичным, но Excel действительно так работает
Как вы видите на рисунке выше, для подсчета непустых ячеек отлично подходит функция СЧЁТЗ:
СЧИТАТЬПУСТОТЫ — наиболее удобный, но не единственный способ подсчета пустых ячеек в Excel. Следующие примеры демонстрируют несколько других методов и объясняют, какую формулу лучше всего использовать в каждом сценарии.
Применяем СЧЁТЕСЛИ или СЧЁТЕСЛИМН.
Другим способом подсчета пустых ячеек в Excel является использование функций СЧЁТЕСЛИ или СЧЁТЕСЛИМН с пустой строкой («») в качестве критериев.
В нашем случае формулы выглядят следующим образом:
или
Возвращаясь к ранее сказанному, вы можете также использовать выражение
Результаты всех трёх формул, представленных выше, будут совершенно одинаковыми. Поэтому какую из них использовать — дело ваших личных предпочтений.
Подсчёт пустых ячеек с условием.
В ситуации, когда вы хотите подсчитать пустые ячейки на основе некоторого условия, функция СЧЁТЕСЛИМН является весьма подходящей, поскольку ее синтаксис предусматривает несколько критериев.
Например, чтобы определить количество позиций, в которых записано «Бананы» в столбце A и ничего не заполнено в столбце C, используйте эту формулу:
Или введите условие в предопределенную позицию, скажем F1, что будет гораздо правильнее:
Как получить количество значений в столбце Excel
Когда вам нужно узнать количество значений в определенном столбце в Excel, существует несколько способов это сделать. Один из самых простых способов – использовать функцию “Подсчет” (COUNT). Данная функция позволяет подсчитать количество ячеек с числовыми значениями в указанном диапазоне. Чтобы использовать функцию “Подсчет”, просто выберите столбец, в котором хотите посчитать значения, и введите формулу “=COUNT(A:A)”, где “A:A” – это диапазон столбца.
Другой способ подсчета значения в столбце – использование функции “Подсчет чисел” (COUNTA). Эта функция считает количество непустых ячеек в указанном диапазоне. Чтобы использовать функцию “Подсчет чисел”, выберите столбец и введите формулу “=COUNTA(A:A)”, где “A:A” – это диапазон столбца.
Если у вас есть столбец с текстовыми значениями, и вы хотите посчитать количество уникальных значений, вы можете использовать функцию “Подсчет уникальных значений” (COUNTIF). Для этого выберите столбец и введите формулу “=COUNTIF(A:A, A1)”, где “A:A” – это диапазон столбца, а “A1” – это ячейка, содержащая значение, которое вы хотите посчитать.
Используя эти простые функции, вы сможете быстро и легко получить количество значений в любом столбце в Excel. Не забывайте, что эти функции могут быть применены не только к одному столбцу, но и к диапазону столбцов или строк, в зависимости от ваших потребностей.
Количество пустых ячеек как условие.
В некоторых случаях вам может потребоваться не просто пересчитать пустые ячейки в диапазоне, но предпринять некоторые действия в зависимости от того, есть ли таковые, либо нет. Самый популярный случай — подсчитать итоги, только если все данные в таблице заполнены.
Хотя в Excel нет встроенной функции ЕСЛИСЧИТАТЬПУСТОТЫ, вы можете легко создать свою собственную формулу, используя вместе функции ЕСЛИ и СЧИТАТЬПУСТОТЫ. Вот как:
- Создаем условие, что количество пробелов равно нулю, и помещаем это выражение в логический тест ЕСЛИ:СЧИТАТЬПУСТОТЫ(B2:D2)=0
- Если логический результат оценивается как ИСТИНА, выведите «Нет пустых».
- Если же — ЛОЖЬ, возвращаем «Пустые».
Полная формула принимает такой вид:
В результате формула определяет все строки, в которых отсутствует одно или несколько значений:
Или вы можете создать другой расчет в зависимости от количества незаполненных позиций. Например, если в диапазоне нет пустот (т.е. если СЧИТАТЬПУСТОТЫ возвращает 0), сложите цифры продаж, в противном случае покажите предупреждение:
То есть, сумма за квартал будет рассчитана только тогда, когда будут заполнены все данные по месяцам.
Подсчет дубликатов с порядковой нумерацией
Иногда посчитать количество повторяющихся значений в столбце недостаточно. Может потребоваться прописать порядковый номер для каждого повторения. В зависимости от количества строк, подсчет вручную может оказаться слишком трудоемким для выполнения вручную. В данном случае мы можем прописать номер для всех повторяющихся значений в столбце Excel. Для этого:
- Напротив строки с первым наименованием прописываем модифицированную формулу СЧЁТЕСЛИ. В качестве аргументов указываем адрес ячейки, с которой начинаем проверку.
- Далее применяем заданную формулу для всех строк. Таким образом, мы получим список, где каждая цифра определяет порядковый номер дубликата.
Независимо от количества дубликатов, программа считает порядковый номер довольно быстро. В случае если объем документа большой, на обработку может потребоваться несколько секунд
При работе с большими документами крайне важно проверить правильность написания аргументов функции, чтобы исключить ошибки при выполнении расчетов