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

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

Здравствуйте, дорогие читатели.

Перед началом данной темы я бы хотел вам посоветовать отличный обучающий продукт по теме экселя, по названием « Неизвестный Excel » , там всё качественно и понятно изложено. Рекомендую.

Ну а теперь вернёмся к теме.

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

Однако не все, кто работает с этим приложением, знает его полную функциональность и умеет ее применять на практике. Вы один из них? Тогда вы обратились по адресу. В частности, сегодня мы разберем, как в excel подсчитать количество ячеек со значением. Есть несколько способов, как это сделать. Они зависят от того, какое именно содержимое вам нужно посчитать. Разберем самые популярные из них.

Самый быстрый способ

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

Если вы хотите задать определенные несложные параметры подсчетам, откройте настройки строки состояния. Это можно сделать, щелкнув правой кнопкой мыши по ней. В появившемся окне обратите внимание на ту часть, где написано «Среднее», «Количество», «Количество чисел», «Минимум», «Максимум» и «Сумма».

Выберите нужный параметр и узнайте больше о том, что содержит ваша таблица.

Подсчет ячеек в строках и столбцах

Существует два способа, позволяющие узнать количество секций. Первый — дает возможность посчитать их по строкам в выделенном диапазоне. Для этого необходимо ввести формулу =ЧСТРОК(массив) в соответствующее поле. В данном случае будут подсчитаны все клетки, а не только те, в которых содержатся цифры или текст.

Второй вариант — =ЧИСЛСТОЛБ(массив) — работает по аналогии с предыдущей, но считает сумму секций в столбце.

Считаем числа и значения

Я расскажу вам о трех полезных вещах, помогающих в работе с программой.

  1. Сколько чисел находится в массиве, можно рассчитать с помощью формулы СЧЁТ(значение1;значение2;…). Она учитывает только те элементы, которые включают в себя цифры.То есть если в некоторых из них будет прописан текст, они будут пропущены, в то время как даты и время берутся во внимание. В данной ситуации не обязательно задавать параметры по порядку: можно написать, к примеру, =СЧЁТ(А1:С3;В4:С7;…).
  2. Другая статистическая функция — СЧЕТЗ — подсчитает вам непустые клетки в диапазоне, то есть те, которые содержат буквы, числа, даты, время и даже логические значения ЛОЖЬ и ИСТИНА.
  3. Обратное действие выполняет формула, показывающая численность незаполненных секций — СЧИТАТЬПУСТОТЫ(массив). Она применяется только к непрерывным выделенным областям.

Ставим экселю условия

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

Массивом называется диапазон элементов, среди которых ведется учет. Это может быть только прямоугольная непрерывная совокупность смежных клеток. Критерием считается как раз таки то условие, согласно которому выполняется отбор. Если оно содержит текст или цифры со знаками сравнения, мы его берем в кавычки. Когда условие приравнивается просто к числу, кавычки не нужны.

Разбираемся в критериях

  • «>0» — считаются ячейки с числами от нуля и выше;
  • «Товар» — подсчитываются секции, содержащие это слово;
  • 15 — вы получаете сумму элементов с данной цифрой.

Для большей ясности приведу развернутый пример.

Чтобы посчитать ячейки в зоне от А1 до С2, величина которых больше прописанной в А5, в строке формул необходимо написать =СЧЕТЕСЛИ(А1:С2;«>»&А5).

Задачи на логику

Хотите задать экселю логические параметры? Воспользуйтесь групповыми символами * и ?. Первый будет обозначать любое количество произвольных символов, а второй — только один.

К примеру, вам нужно знать, сколько имеет электронная таблица клеток с буквой Т без учета регистра. Задаем комбинацию =СЧЕТЕСЛИ(А1:D6;«Т*»). Другой пример: хотите знать численность ячеек, содержащих только 3 символа (любых) в том же диапазоне. Тогда пишем =СЧЕТЕСЛИ(А1:D6;«. »).

Средние значения и множественные формулы

В качестве условия может быть задана даже формула. Желаете узнать, сколько у вас секций, содержимое которых превышают среднее в определенном диапазоне? Тогда вам следует записать в строке формул следующую комбинацию =СЧЕТЕСЛИ(А1:Е4;«>»&СРЗНАЧ(А1:Е4)).

Если вам нужно сосчитать количество заполненных ячеек по двум и более параметрам, воспользуйтесь функцией СЧЕТЕСЛИМН. К примеру, вы ищите секций с данными больше 10, но меньше 70. Вы пишете =СЧЕТЕСЛИМН(А1:Е4;«>10»;А1:Е4;« Этой статьей стоит поделиться

Источник: profi-user.ru

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

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

Функция СЧЁТЕСЛИ может быть использована для анализа числовых значений, текстовых строк, дат и данных другого типа. С ее помощью можно определить количество неповторяющихся значений в диапазоне ячеек, а также число ячеек с данными, которые совпадают с указанным критерием лишь частично. Например, таблица Excel содержит столбец с ФИО клиентов. Для определения количества клиентов-однофамильцев с фамилией Иванов можно ввести функцию =СЧЁТЕСЛИ(A1:A300;”*Иванов*”). Символ «*» указывает на любое количество любых символов до и после подстроки «Иванов».

Примеры использования функции СЧЁТЕСЛИ в Excel

Пример 1. В таблице Excel содержатся данные о продажах товаров в магазине бытовой техники за день. Определить, какую часть от проданной продукции составляет техника фирмы Samsung.

Вид исходной таблицы данных:

Для расчета используем формулу:

  • C3:C17 – диапазон ячеек, содержащих названия фирм проданной техники;
  • “Samsung” – критерий поиска (точное совпадение);
  • A17 – ячейка, хранящая номер последней продажи, соответствующий общему числу продаж.

Доля проданной продукции техники фирмы Samsung в процентах составляет – 40%.

Подсчет количества определенного значения ячейки в Excel при условии

Пример 2. По итогам сдачи экзаменов необходимо составить таблицу, в которой содержатся данные о количестве студентов, сдавших предмет на 5, 4, 3 балла соответственно, а также тех, кто не сдал предмет.

Вид исходной таблицы:

Читайте также:  Как в экселе посчитать количество ячеек

Предварительно выделим ячейки E2:E5, введем приведенную ниже формулу:

  • B3:B19 – диапазон ячеек с оценками за экзамен;
  • D2:D5 – диапазон ячеек, содержащих критерии для подсчета числа совпадений.

В результате получим таблицу:

Статистический анализ посещаемости с помощью функции СЧЁТЕСЛИ в Excel

Пример 3. В таблице Excel хранятся данные о просмотрах страниц сайта за день пользователями. Определить число пользователей сайта за день, а также сколько раз за день на сайт заходили пользователи с логинами default и user_1.

Вид исходной таблицы:

Поскольку каждый пользователь имеет свой уникальный идентификатор в базе данных (Id), выполним расчет числа пользователей сайта за день по следующей формуле массива и для ее вычислений нажмем комбинацию клавиш Ctrl+Shift+Enter:

Выражение 1/СЧЁТЕСЛИ(A3:A20;A3:A20) возвращает массив дробных чисел 1/количество_вхождений, например, для пользователя с ником sam это значение равно 0,25 (4 вхождения). Общая сумма таких значений, вычисляемая функцией СУММ, соответствует количеству уникальных вхождений, то есть, числу пользователей на сайте. Полученное значение:

Для определения количества просмотренных страниц пользователями default и user_1 запишем формулу:

В результате расчета получим:

Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

=СЧЕТЕСЛИ( диапазон; критерий )

  • диапазон – обязательный аргумент, принимающий ссылку на одну либо несколько ячеек, в которых требуется определить число совпадений с указанным критерием.
  • критерий – условие, согласно которому выполняется расчет количества совпадений в рассматриваемом диапазоне. Условием могут являться логическое выражение, числовое значение, текстовая строка, значение типа Дата, ссылка на ячейку.
  1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;”*стол*”)+СЧЁТЕСЛИ(B3:B200;”*стул*”).
  2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;”Петров”) вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
  3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
  4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
  5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:
  • записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
  • записать вторую функцию с критерием «>=8»;
  • определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;”>3″)-СЧЁТЕСЛИ(A1:A10;”>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

Источник: exceltable.com

Подсчет значений с множественными критериями (Часть 1. Условие И) в EXCEL

Произведем подсчет строк, удовлетворяющих сразу двум критериям, которые образуют Условие И.

В качестве исходной таблицы возьмем таблицу с двумя столбцами: текстовым « Фрукты » и числовым « Количество на складе » (См. файл примера ).

Задача1

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

Найдем число партий товара с определенным Фруктом И с Количеством на складе не менее минимального (Условие И – условие при котором строка считается удовлетворяющей критерию, когда оба ее поля одновременно соответствуют критериям). Например, число партий персики ( ячейка D 2 ) с количеством ящиков на складе >=5 ( ячейка Е2 ) . Результат очевиден: 2. Для наглядности, строки в таблице, удовлетворяющие критериям, выделяются Условным форматированием с правилом =И($A2=$D$2;$B2>=$E$2)

Подсчет можно реализовать множеством формул, приведем несколько:

1. = СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;”>=”&E2) Это решение является самым простым и понятным.

2. =СУММПРОИЗВ(–(A2:A13=D2);–(B2:B13>=E2)) Это решение сложнее, но позволяет понять работу функции СУММПРОИЗВ() , которая может быть полезна для подсчета с множественными критериями в других случаях.

Разберем подробнее применение функции СУММПРОИЗВ() :

  • Результатом вычисления A2_A13=D2 является массив <ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ>Значение ИСТИНА соответствует персики . Результат можно увидеть, выделив A2_A13=D2 , а затем нажав клавишу F9 ;
  • Двойное отрицание (–) преобразует вышеуказанный массив в числовой:
  • Аналогично, второй массив возвращает <0:1:1:1:0:1:1:0:0:1:1:1>, где 0 соответствует значениям =5, поэтому второе значение в массиве =1 и т.д.
  • Далее, функция попарно перемножает элементы массивов и суммирует их. Получаем – 2.

3. Другим вариантом использования функции СУММПРОИЗВ() является формула =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) . Здесь, знак Умножения (*) эквивалентен Условию И.

4. Формула массива = СУММ((A2:A13=D2)*(B2:B13>=E2)) эквивалентна вышеупомянутой формуле =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, после ее ввода нужно вместо ENTER нажать CTRL + SHIFT + ENTER

5. Формула массива = СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет еще один вариант многокритериального подсчета значений.

6. Формула = БСЧЁТА(A1:B13;A1;D14:E15) требует предварительного создания таблицы с условиями. Заголовки этой таблицы должны в точности совпадать с заголовками исходной таблицы. Размещение условий в одной строке соответствует Условию И.

Здесь есть один трюк: в качестве второго аргумента функции БСЧЁТА() ( поле) нужно ввести ссылку на заголовок столбца с текстовыми значениями, т.к. БСЧЁТА() подсчитывает текстовые значения . В случае использования БСЧЁТ() нужно записать другую формулу =БСЧЁТ(A1:B13;B1;D14:E15) . Табличка с критериями не изменится.

Задача2

Рассмотрим задачу, когда критерии применяются к значениям из одного столбца.

Найдем число партий товара с Количеством на складе не менее минимального и не более максимального (Условие И – строка таблицы соответствует критерию, когда ее поле удовлетворяет обоим критериям одновременно).

Решение стоится аналогично предыдущей задачи. Например, с использованием функции СЧЁТЕСЛИМН() формула выглядит так (см. лист один столбец в файле примера ):

Альтернативное решение

Подсчитать количество строк, удовлетворяющим 2-м критериям (Условие И) можно без применения формул с помощью стандартного Автофильтра .

Установите автофильтр к столбцу Количество ящиков на складе , выделив заголовок столбца и нажав CTRL+SHIFT+L. Выберите числовой фильтр Между .

Убедитесь, что результат такой же как в задаче2 – т.е. будет отобрано 7 строк (см. строку состояния в нижней части окна).

Примечание : подсчет значений с множественными критерями также рассмотрен в статьях Подсчет значений с множественными критериями (Часть 2. Условие ИЛИ) , Часть3 , Часть4 .

Источник: excel2.ru

Как в Экселе посчитать количество ячеек

Посчитать количество ячеек в Эксель может потребоваться в различных случаях. В данной статье, мы и рассмотрим, как посчитать блоки с определенными значениями, пустые и если они попадают под заданные условия. Использовать для этого будем следующие функции: СЧЁТ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СЧИТАТЬПУСТОТЫ.

Заполненных

Для начала, рассмотрим самый простой способ. Если Вам нужно посчитать количество заполненных блоков в Excel, выделите диапазон ячеек в документе – существуют различные способы, прочтите про них, перейдя по ссылке – и в «Строке состояния» обратите внимание на пункт «Количество» . Там будет написано количество непустых блоков: учитываются все с текстом и цифрами, а пустые – нет.

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

Где введены числа

Функция СЧЁТ – подсчитывает блоки, заполненные только числовыми значениями. Выделяем Н1 , ставим «=» , пишем функцию «СЧЁТ» . В качестве аргумента функции укажите нужный диапазон (F1:G10) . Если диапазонов несколько, разделите их «;» – (F1:G10;B3:C8) .

Всего заполнено 20 блоков. Тот, в котором записан текст, был не учтен, но посчитаны те, которые заполнены датой и временем.

С определенным текстом или значением

Функция СЧЁТЕСЛИ – позволяет рассчитать количество блоков, которые соответствуют заданному критерию. В качестве аргумента прописывается диапазон – В2:В13 , и через «;» указывается критерий – «>5» .

Например, есть таблица, в которой указано, сколько килограмм определенного товара было продано за день. Посчитаем, сколько товаров было продано весом больше 5 килограмм. Для этого нужно посчитать сколько блоков в столбце Вес, где значение больше пяти. Функция будет выглядеть следующим образом: =СЧЁТЕСЛИ(В2:В13;»>5″) . Она рассчитает количество блоков, содержимое в которых больше пяти.

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

Функция также может рассчитать:

– количество ячеек с отрицательными значениями: =СЧЁТЕСЛИ(В2:В13;» ;
– количество блоков, содержимое в которых больше (меньше) чем в А10 (для примера): =СЧЁТЕСЛИ(В2:В13;»>»&A10) ;
– ячейки, значение в которых больше 0: =СЧЁТЕСЛИ(В2:В13;»>0″) ;
– непустые блоки из выделенного диапазона: =СЧЁТЕСЛИ(В2:В13;»<>») .

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

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

В качестве критерия также можно использовать специальные символы: «*» и «?» . Они применяются только к тексту.

Посчитаем сколько товаров начинается на букву А: «А*» . Если указать «абрикос*» , то учтутся все товары, которые начинаются с «абрикос»: абрикосовый сок, абрикосовое варенье, абрикосовый пирог.

Символом «?» можно заменить любую букву в слове. Написав в критерии «ф?укт» – учтутся слова фрукт, фуукт, фыукт.

Чтобы посчитать слова в ячейках, которые состоят из определенного количества букв, поставьте знаки вопросов подряд. Для подсчета товаров, в названии которых 5 букв, поставим в качестве критерия «. » .

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

С несколькими критериями

Функция СЧЁТЕСЛИМН используется в том случае, когда требуется указать несколько условий, максимальное их количество в Excel – 126. В качестве аргумента: задаем первый диапазон значений, и указываем условие, через «;» задаем второй диапазон, и пишем условие для него – =СЧЁТЕСЛИМН(B2:B13;»>5″;C2:C13;»фрукт») .

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

Пустых блоков

Функция СЧИТАТЬПУСТОТЫ рассчитает количество пустых ячеек в заданном диапазоне.

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

Источник: comp-profi.com

Подсчет ячеек в Excel, используя функции СЧЕТ и СЧЕТЕСЛИ

Очень часто при работе в Excel требуется подсчитать количество ячеек на рабочем листе. Это могут быть пустые или заполненные ячейки, содержащие только числовые значения, а в некоторых случаях, их содержимое должно отвечать определенным критериям. В этом уроке мы подробно разберем две основные функции Excel для подсчета данных – СЧЕТ и СЧЕТЕСЛИ, а также познакомимся с менее популярными – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН.

Статистическая функция СЧЕТ подсчитывает количество ячеек в списке аргументов, которые содержат только числовые значения. Например, на рисунке ниже мы подсчитали количество ячеек в диапазоне, который полностью состоит из чисел:

В следующем примере в двух ячейках диапазона содержится текст. Как видите, функция СЧЕТ их игнорирует.

А вот ячейки, содержащие значения даты и времени, учитываются:

Функция СЧЕТ может подсчитывать количество ячеек сразу в нескольких несмежных диапазонах:

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

Решить обратную задачу, т.е. подсчитать количество пустых ячеек в Excel, Вы сможете, применив функцию СЧИТАТЬПУСТОТЫ:

СЧЕТЕСЛИ()

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

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

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

Логическое условие функции СЧЕТЕСЛИ может содержать групповые символы: * (звездочку) и ? (вопросительный знак). Звездочка обозначает любое количество произвольных символов, а вопросительный знак – один произвольный символ.

Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Н (без учета регистра), можно воспользоваться следующей формулой:

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

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

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

К примеру, следующая формула подсчитывает ячейки, значения которых больше нуля, но меньше 50:

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

Функции Excel для подсчета данных очень полезны и могут пригодиться практически в любой ситуации. Надеюсь, что данный урок открыл для Вас все тайны функций СЧЕТ и СЧЕТЕСЛИ, а также их ближайших соратников – СЧЕТЗ, СЧИТАТЬПУСТОТЫ и СЧЕТЕСЛИМН. Возвращайтесь к нам почаще. Всего Вам доброго и успехов в изучении Excel.

Источник: office-guru.ru

Как подсчитать количество повторений

В этой статье я хочу рассказать, как можно подсчитать количество повторений какого-либо значения в таблице или в ячейке. Начнем по порядку. Имеется таблица:

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция – СЧЁТЕСЛИ, при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
Диапазон ( $A$2:$A$30 ) – указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий ( A2 ) – указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ( $A$2:$A$30 ;”Яблоко”) . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия “*банан*” можно подсчитать количество ячеек, в которых встречается слово “банан” (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав “банан*” — значения, начинающиеся на “банан” (бананы, банановый сок, банановая роща и т.п.). “?” — заменяет лишь один символ, т.е. указав “бан?н” можно подсчитать строки и со значением “банан” и со значением “банон” и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ( $A$2:$A$30 ;”*”) , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и ?) не получится применить к числовым значениям – исключительно к тексту. Т.е. если если указать в качестве критерия “12*”, то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ( $A$2:$A$30 ;”>12″)

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;”>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;”<>“)

Как видно из второго рисунка – там наименования не повторяются, хотя в таблице они все записаны вперемешку. Я не буду заострять на этом внимание – я уже описывал это в статье Как получить список уникальных(не повторяющихся) значений? и при необходимости можно воспользоваться любым описанным в ней методом.

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $D$1 ):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР( $D$1 )-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;””)))/ДЛСТР( D3 )
ДЛСТР – подсчитывает количество символов в указанной ячейке/строке( $D$1 , D3 )
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) – заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан( D3 ) пошаговый разбор формулы:

  • при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом( $D$1 ) =(170-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;””)))/ДЛСТР( D3 ) ;
  • при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом( $D$1 ) все значения Банан( D3 ) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР( D3 ) ;
  • вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

И простая функция пользователя, которая так же подсчитывает повторения внутри ячейки:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) – Len(Replace(sTxt, sCntWord, “”))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(Insert –Module) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt – текст, в котором подсчитываем кол-во вхождения.
sCntWord – текст для подсчета. Может быть символом или словом.

Tips_All_Count_Duplicate.xls (39,0 KiB, 9 484 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Добрый день.
Можете ли вы мне помочь?

Нужно посчитать количество повторений по столбцу рейс только в одной строке.
Рейс. Объем. Колич
повтор
1аб. 2 2
1ав. 3. 1
1 аб. 0.3. 2 (2нужно посчитать как 0)
2аг. 9. 2
2аг. 3. 2 (2нужно посчитать как 0)
2ав. 0.4. 1

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

Попробуйте задать вопрос в форуме , приложив пример файла с данными. По одному описанию у меня не получается понять задачу.

Источник: www.excel-vba.ru