Как сделать СРЗНАЧЕСЛИ в Excel?

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

Описание

СЧЕТЕСЛИ можно отнести к группе критериев. Это вычисления, которые можно получить по заранее заданным условиям. К подобным инструментам также относятся СЧЕТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН.

Оператор СЧЕТЕСЛИ в «Эксель» применяется для поиска значений, удовлетворяющих заданным условиям. Рассмотрим детально синтаксис. Функция записывается в ячейке в виде =СЧЕТЕСЛИ(диапазон;критерий)

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

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

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

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

Как пользоваться?

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

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

Функция «ЕСЛИ» в Эксель

Последний способ – открыть окно с функциями. Это верхняя шапка программы. Вы можете сделать следующим образом:

1. Зайти во вкладку «Формулы» и нажать на кнопку «Вставить функцию».

1. После этого воспользоваться поиском (1) или выбрать группу «Статистические» и найти нужный оператор в списке (2).

Или так:

1. Снова откройте раздел «Формулы» и нажмите на кнопку «Другие функции».

1. В открывшемся списке выберите «Статистические» и найдите нужный инструмент в списке.

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

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

• Начнем с простых операций – поиск определенных слов/чисел по одному заданному условию. Считать будем на примере простой таблицы:
• Необходимо найти, сколько предметов мебели из данного списка превышает количество 100 единиц. Для этого делаем следующее:

1. Встаем в нужную ячейку и нажимаем отмеченную на скриншоте кнопку.

1. Находим СЧЕТЕСЛИ (можно написать в поиске для удобства).

1. Теперь задаем диапазон. Для этого мышкой выделяем промежуток с количеством единиц мебели.
2. Задаем критерий. Вписываем то значение, которое необходимо найти в выбранном массиве.
3. Нажмите кнопку «ОК» для начала вычисления. В выбранной клетке появится результат. В примере мало строк, поэтому его можно проверить вручную и убедиться в правильности выполнения.

Считаем текстовые значения

Теперь необходимо сделать так, чтобы оператор нашел количество позиций с торшерами в данной таблице. Разберемся, как вписывать данные вручную. Для этого необходимо:

1. Снова выберите ячейку, в которой должен появиться конечный результат, и перейдите в верхнюю строку.
2. Вписываем команду «=СЧЕТЕСЛИ» и открываем скобку.
3. Выделяем диапазон столбца с названиями позиций.
4. Теперь ставим знак «;» и пишем «торшеры». Между диапазоном и критерием пробел не ставится.
5. Закрываем скобку и жмем Enter для выполнения операции. В выбранной клетке появится необходимое решение. Поскольку значений, опять же, не так много, результат можно проверить вручную.

Поиск по части слова или букве

Инструмент позволяет подсчитать количество клеток, которые начинаются или заканчиваются на определенную букву. Также поиск можно осуществить по части слова. Рассмотрим следующий пример:

1. Найдем количество позиций, которые начинаются на букву «Т». Для этого вновь выбираем отдельную ячейку и начинаем писать «СЧЕТЕСЛИ».
2. Выбираем диапазон с названиями продукции.
3. Далее вписываем букву «Т» и ставим знак «*» (звездочка). Если поставить его после вписанного знака, то буква будет считаться начальной. Если поставить звездочку перед буквой, то оператор проведет поиск по последним символам слов.
4. Жмем Enter и смотрим на результат.

Поиск исключений

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

1. Найдем количество позиций, в которых нет слова «столы». Для этого в ячейке необходимо ввести уже знакомую формулу и выделить список названий.
2. Через точку с запятой в кавычках вписать «столы*». Звездочка необходима для того, чтобы в выборку входила ячейка «Столы1».
3. В клетке появится результат вычислений.

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

Зачастую пользователям требуется провести поиск по нескольким названиям или условиям сразу. В этом случае вы можете объединить несколько формул в одну с помощью знака «+». Посчитаем, сколько позиций столов в количестве более 140 встречается в таблице:

1. Вводим синтаксис в необходимую клетку и выделяем диапазон. Вписываем «столы» и закрываем оператор.
2. Далее ставим знак «+» и пишем новую формулу, в которой используем те же значения списка, но вместо критерия пишем «>140».

Как снять защиту с листа Эксель

1. Смотрим результат и сверяем.

Несколько полезных советов:

• если ваша формула отсылается на ячейки из другой базы данных, то в момент вычисления она должна быть открыта в Excel;
• при поиске текста не учитывается регистр символов;
• чтобы программа искала сами знаки >,

Источник: https://os-helper.ru/excel/funkciya-schetesli.html

Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

Функция Счётесли

Счётесли (диапазон; критерий)

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

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

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

Критерий проверки необходимо заключать в кавычки.

Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».

Примеры использования функции Счётесли.

1. Подсчет количества ячеек, содержащих отрицательные значенияСчётесли(А1:С2;»»&A4) Диапазон — А1:С2 , критерий — «>»&A4

1. Подсчет количества ячеек со словом «текст» (регистр не имеет значения).Счётесли(А1:С2;»текст») Диапазон — А1:С2 , критерий — «текст»

Для текстовых значений в критерии можно использовать подстановочные символы * и ? .

• Вопросительный знак соответствует одному любому символу, звездочка— любому количеству произвольных символов.
• Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак ~ .
• Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:
• Счётесли(А1:С2;»Т * «) Диапазон — А1:С2 , критерий — «Т * «

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

Счётесли(А1:С2;»????») Диапазон — А1:С2 , критерий — «????»

В функции Счётесли используется только один критерий.

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

Функция Счётеслимн

Счётеслимн (диапазон1; условие1; [диапазон2]; [условие2]; …).

Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.

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

Пример использования:

1. Подсчет количества ячеек, в которых находятся даты из определенного периода (например, после 15 января и до 1 марта 2015г.).

Счётеслимн(C1:C8;»>15.01.2015″;C1:C8;»15.01.2015″ и »

Источник: https://lk.usoft.ru/allfunction/f_Excel/f_e_20150723

Функция СЧЁТЕСЛИ() в MS Excel — Подсчет значений с единственным критерием

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

• Синтаксис
• СЧЁТЕСЛИ(диапазон;критерий)
• Диапазон    — диапазон, в котором нужно подсчитать ячейки, содержащие числа, текст или даты.

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

Например, критерий может быть выражен следующим образом: 32, «32», «>32», «яблоки» или B4.

Подсчет числовых значений с одним критерием

Данные будем брать из диапазона A15:A25 (см. файл примера).

Критерий	Формула	Результат	Примечание
>=10	=СЧЁТЕСЛИ($A$2:$A$12; «>=10»)	4	Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле
= (ячейка С4)11(ячейка С5)

1. =СЧЁТЕСЛИ($A$2:$A$12; C4&C5)
2. Подсчитывает количество ячеек, содержащих числа равных или более 11. Критерий указан через ссылку и параметр

Примечание. О подсчете значений, удовлетворяющих нескольким критериям читайте в статье Подсчет значений со множественными критериями. О подсчете чисел с более чем 15 значащих цифр читайте статью Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL.

Подсчет Текстовых значений с одним критерием

Функция СЧЁТЕСЛИ() также годится для подсчета текстовых значений (см. Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL).

Подсчет дат с одним критерием

Так как любой дате в MS EXCEL соответствует определенное числовое значение, то настройка функции СЧЕТЕСЛИ() для дат не отличается от рассмотренного выше примера (см. файл примера Лист Даты).

Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)

Подсчет с несколькими условиями

Обычно, в качестве аргумента критерий у функции СЧЁТЕСЛИ() указывают только одно значение. Например, =СЧЁТЕСЛИ(H2:H11;I2). Если в качестве критерия указать ссылку на целый диапазон ячеек с критериями, то функция вернет массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив {3:2:5}.

Для ввода формулы выделите диапазон ячеек такого же размера как и диапазон содержащий критерии. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER, т.е. введите ее как формулу массива.

Это свойство функции СЧЁТЕСЛИ() используется в статье Отбор уникальных значений.

Специальные случаи использования функции

Возможность задать в качестве критерия несколько значений открывает дополнительные возможности использования функции СЧЁТЕСЛИ().

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

Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел {1:4:4:4:4:1:3:3:3}, который говорит о том, что значение 1 из списка в диапазоне А6:А15 — единственное, также в диапазоне 4 значения 2, одно значение 3, три значения 4. Это позволяет подсчитать количество неповторяющихся значений формулой =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)).  Формула =СЧЁТЕСЛИ(A6:A14;»

Источник: https://excel2.ru/articles/funkciya-schyotesli-v-ms-excel-podschet-znacheniy-s-edinstvennym-kriteriem-schyotesli

Функция СЧЁТЕСЛИ в Excel

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

Описание функции СЧЁТЕСЛИ

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

• Диапазон — диапазон ячеек по которым необходимо выполнить подсчет;
• Критерий — условие, определяющее какие ячейки нужно подсчитать. Условие должно быть в виде числа, ссылки на ячейку, выражения и т.д.

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

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

Подсчитаем количество категорий продуктов, относящихся отдельно к овощам и фруктам (критерий №1 в нашем примере).
В качестве диапазона выбираем ячейки A2:A13 (категория продукта) и задаем критерий подсчета F3 («Овощи»):

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

Теперь подсчитаем количество проданных продуктов свыше определенного количества килограмм (критерий №2 в нашем примере).
В качестве диапазона выбираем ячейки C2:C13 (объем продаж в кг) и задаем критерий подсчета F7 («>500»):

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

Источник: https://tutorexcel.ru/funktsii/funkciya-schyotesli-v-excel/

Функция СЧЁТЕСЛИ в Excel

Вот и снова пришла очередь рассказать о еще одной полезной функции в табличном редакторе, это функция СЧЕТЕСЛИ в Excel.

В моих статьях вы уже встречали функции похожего направления, такие как СУММЕСЛИ, СЧЁТ, ЕСЛИ, их работа в чём-то сродни как работает функция СЧЕТЕСЛИ в Excel, но условия исполнения и специфика всё же отличаются, а поэтому и ситуации для применения также отличаются.

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

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

Я думаю, что надо стоить изучить функцию СЧЕТЕСЛИ с ее синтаксиса:

= СЧЕТЕСЛИ(диапазон; критерий), где все критерии обязательные и обозначают:

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

критерий – это выражение, текстовое значение, ссылка на ячейку или число, то есть любое значение, которое может определить, что же именно нужно найти и какие ячейки будет считать. К примеру, критерий может выглядеть как «40», «=>40», «С65», «менеджер».

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

=СЧЕТЕСЛИ(C2:C10;»>=100″), в критериях мы используем символы и числа;

=СЧЕТЕСЛИ(C2:C10;$E$8), здесь в критериях мы используем ссылку на условие.      Надеюсь моя статья о том, как работает функция СЧЕТЕСЛИ в Excel вам пригодилась, вы почерпнули с нее нужную вам информацию и успешно использовали ее в своей работе!

Источник: http://topexcel.ru/funkciya-schyotesli-v-excel/

Архив рубрики: СЧЕТЕСЛИ

• =СЧЕТЕСЛИ(диапазон,критерий)
• Подсчет ячеек, содержащих определенный текст

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

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

Пример проблемы и решения

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

=СЧЕТЕСЛИ(цвета,G9)

где «цвет» — это именованный диапазон ячеек E5:E13. Мы используем именованный диапазон в этом случае, чтобы принять формулу легко скопировать сводную таблицу. Кроме того, можно использовать абсолютную ссылку вот так:

=СЧЕТЕСЛИ($E$5:$E$11,G9) ; абсолютный адрес вариант

Как эта формула работает

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

В этом случае, мы тестируем равенства, поэтому нам не нужно использовать какие-либо логические операторы. Можно просто ввести именованный диапазон «цветной» ассортимент, и ссылку на соседнюю ячейку в столбце F для критериев.

Файл Подсчет-позиций-по-цвету-СЧЕТЕСЛИ-devsap

Подсчет значений на другом листе (СЧЕТЕСЛИ)

1. =СЧЁТЕСЛИ(Лист2!$1:$1048576;C5) С5= Катя
2. Объяснение
3. Чтобы осуществить поиск на другом листе для значения , вы можете использовать формулу на основе функции СЧЕТЕСЛИ.
4. В показанном примере, формула в С5:
5. =СЧЕТЕСЛИ(Лист2!1:1048576,С4)
6. Как эта формула работает
7. Второй лист книги, Лист2, содержит неопределенное количество имен в диапазоне.

Функция СЧЕТЕСЛИ имеет диапазон и критерии. В этом случае, СЧЕТЕСЛИ, мы выделяем диапазон равный всем строкам в Лист2.

Лист2!1:1048576

Для критериев, мы используем ссылку на С4, в которых содержится «Катя». Тогда СЧЕТЕСЛИ возвращает 6, так как есть 6 ячеек в Лист2 равные значению «Катя».

• Содержит и равна
• Если вы хотите посчитать все ячейки, содержащие значение в С4, вместо того, чтобы все клетки равна С4, добавить символы к таким критериям, как этот:
• =СЧЕТЕСЛИ(Лист2!1:1048576,»*»&С4&»*»)
• Теперь СЧЕТЕСЛИ будет подсчитать ячейки с подстроки «Катя» в любом месте в клетке.
• Производительности

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

Поиск на нескольких листах

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

1. Подсчет количества повторов во всей книге
2. Для подсчета матчей за всю книгу, вы можете использовать формулу на основе функции СЧЕТЕСЛИ и функции СУММПРОИЗВ. Что то похожее на
3. D5: = СУММПРОИЗВ( СЧЕТЕСЛИ (ДВССЫЛ( «‘» & листы & «‘!А1:Z10000»
4. Функция СЧЕТЕСЛИ в Excel

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

Файл Excel ПОДСЧЕТ-значений-на-ДРУГОМ-ЛИСТЕ-(СЧЕТЕСЛИ)-devsap

Поиск в Диапазоне совпадения определенного текста СЧЕТЕСЛИ devsap

• =СЧЕТЕСЛИ («диапазон»;»*»&значение&»*»)>0
• Связанные формулы
• Диапазон содержит конкретное значение
  Спектр содержит одну из многих значений
  Спектр содержит один из множества подстрок
  Ячейка содержит определенный текст
• Ячейка содержит одну из многих вещей

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

В показанном примере, формула в E6:

=СЧЕТЕСЛИ («Диапазон»,»*»&E6&»*»)>0

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

Звездочка (*) является подстановочным знаком для одного или нескольких символов. Объединяя звездочки перед и после значения в E^, в Формуле будет рассчитывать на подстроки, если он появляется в любом месте в любую ячейку диапазона.

• Любое положительное число означает значение было найдено, так что вы можете использовать оператор «больше» ( > ), определяющий правду или ложь и фиксирует результат.
• Вы также можете обернуть формулы внутри оператора ЕСЛИ, чтобы добиться конкретного результата. Например, чтобы получить «да» или «нет», используйте:
• =Если(СЧЕТЕСЛИ(диапазон,»*»&значение&»*»),»Да»,»Нет»)
• =СЧЁТЕСЛИ($B$6:$B$10;»*»&E6&»*»)>0
• =ЕСЛИ(СЧЁТЕСЛИ($B$6:$B$10;»*»&E6&»*»)>0;»ДА»;»НЕТ»)

Как использовать функцию СЧЕТЕСЛИ devsapexcel

1. Описание
2. Функция СЧЕТЕСЛИ в Excel подсчитывает количество ячеек в диапазоне, который соответствует заданным критериям.
3. Если вы хотите применить несколько критериев, попробуйте использовать функции СЧЕТЕСЛИМН.
4. Синтаксис
5. Синтаксис для функцмии СЧЕТЕСЛИ в  Excel-это:
6. СЧЕТЕСЛИ( диапазон, критерий )
   Параметры или Аргументы
7. диапазон
   Диапазон ячеек, которые вы хотите рассчитывать на основе критериев.
   критерии
8. Критерии, используемые для определения, какие клетки считать.
9. Пример (как функция)
10. Давайте посмотрим на некоторые функции Excel СЧЕТЕСЛИ примеры и узнайте, как использовать функцию СЧЕТЕСЛИ в функцию рабочего листа в программе Excel:
11. На основании изложенного таблицы Excel, СЧЕТЕСЛИ в следующих примерах вернется:
12. 1. =СЧЕТЕСЛИ(C8:C12; С8)
    Результат: 1
13. 2. =СЧЕТЕСЛИ(C:C; С8)
    Результат: 1
14. 3. =СЧЕТЕСЛИ(C8:C12; «>=2001»)
    Результат: 2
15. Используя Именованные Диапазоны

Вы также можете использовать именованный диапазон в функции СЧЕТЕСЛИ. Именованный диапазон-это описательное имя для набора ячеек или диапазона на листе.

• Например, мы создали именованный диапазон называемой «семьи», который ссылается на столбец C на листе 1.
• Затем мы ввели следующие данные в Excel:
• На основе таблицы Excel выше:
•  =СЧЕТЕСЛИ(семья, С8)
  Результат: 1
•  =СЧЕТЕСЛИ («семья», «>=2001»)
  Результат: 2
• Для просмотра именованных диапазонов: в меню Вставка выберите пункт имя > определить.

пытаюсь использовать СЧЕТЕСЛИ на выделение ячеек (не обязательно один сплошной спектр), и синтаксис функции не позволит. Есть ли другой способ сделать это?

Вот пример что я хочу быть в состоянии сделать:

6. не работает  =СЧЕТЕСЛИ(c7,c11,d10,c18,c17;»>2003″)

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

1. Например:
2. 7.  =Сумм(СЧЕТЕСЛИ(c7;»>2003″);СЧЕТЕСЛИ(c11;»>2003″);СЧЕТЕСЛИ(d10;»>2003″);СЧЕТЕСЛИ(c18;»>0″);СЧЕТЕСЛИ(c17;»>0″))
3. Или
4. 8. =СЧЁТЕСЛИ(C6;»>2003″)+СЧЁТЕСЛИ(C10;»>2003″)+СЧЁТЕСЛИ(D9;»>2003″)+СЧЁТЕСЛИ(C17;»>0″)+СЧЁТЕСЛИ(C16;»>0″)
5. Вопрос: я использую функцию СЧЕТЕСЛИ и я хотел бы сделать критерии равные ячейки.
6. Например:
7. 9. =СЧЁТЕСЛИ(C3:C18;»>=2004″)

Я хочу заменить 2004 с ячейки F6. Как мне это сделать?

• Ответ: чтобы использовать ссылку на ячейку в критерии, вы можете сделать следующее:
• 10.  =СЧЁТЕСЛИ(C4:C19;»>=»&F7)
• Вопрос:я хотел бы сделать следующее:
• 11.  =СЧЁТЕСЛИ(АБС(C4:C19);»>2004″) т. е. подсчитать количество значений в диапазоне C4:А19, которые имеют ненулевой величины. Синтаксис я пробовал не работает. Не могли бы вы помочь?

1. Ответ: потому что Вы не можете применить функцию ABS в диапазоне C4:А19, то надо разбить формулу на две функции СЧЕТЕСЛИ следующим образом:
2. 12.  =СЧЁТЕСЛИ(C3:C18;»>2004″)+СЧЁТЕСЛИ(C3:C17;»0))
3. Связанные формулы
4. ЕЧИСЛО   Выдает логическое значение ИСТИНА, если аргумент ссылается на число.
5. НАЙТИ     Ищет вхождение одного текста в другой (с учетом регистра).
6. Вариант № 1
7. Ячейка содержит определенный текст
   Подсчет ячеек, содержащих определенный текст
8. Подсчет клеток
9. Для подсчета ячеек, содержащих либо одно значение, либо другое, можно либо использовать вспомогательном столбце или более сложные формулы ячейки.

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

1. Решение, вы можете использовать функцию СУММПРОИЗВ с функцией ЕЧИСЛО + НАЙТИ  комбо. Формула в ячейке E12-это:
2. =СУММПРОИЗВ(—((ЕЧИСЛО(НАЙТИ(«asd»;D5:D11)) + ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)))>0))
3. Эта формула основана на формуле:
4. Функции ЕЧИСЛО(НАЙТИ(«edf»;D5:D11)

При заданном диапазоне ячеек, этот фрагмент возвратит массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Так как мы создаем два массива (один раз для «asd» и еще для «edf»).

Далее, мы добавляем эти массивы вместе (с +), которая создает новый один массив чисел. Каждое число в этом массиве является результатом сложения значений Истины и Ложь в двух исходных массивов.

Нам нужно добавить эти цифры, но мы не хотим двойной счет. Поэтому мы должны убедиться, что любое значение больше нуля, это всего лишь раз пересчитать. Чтобы сделать это, мы заставляем все значения Истина или Ложь с «>0», потом заставляют 1/0 с двойной отрицательной (—).

• Наконец, функция СУММПРОИЗВ добавляет эти цифры вверх.
• Вариант № 2
• Вспомогательный столбец решение

С вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложна. Мы можем использовать СЧЕТЕСЛИ с двумя значениями (при условии, как «массив»). Формула в H5 :

=—(СУММ(СЧЁТЕСЛИ(G5;{«*asd*»;»*edf*»}))>0)

СЧЕТЕСЛИ возвращает массив, который содержит два пункта: счетчик для «asd» и рассчитывает на «edf». Во избежание двойного счета, мы добавляем элементы и затем принудительно результат «истина/ложь» с «>0». Наконец, преобразуем значения Истина или Ложь на 1 и 0 с двойной отрицательный (—).

Конечный результат 1 или 0 для каждой ячейки. Чтобы получить общую для всех ячеек в диапазоне, вы будете просто просуммировать столбец.

Файл Подсчет клеток, которые содержат либо x или Y xls

Подсчет ячеек, содержащих по пять символов EXCEL

=СЧЕТЕСЛИ («диапазон»,»?????»)

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

В приведенном примере в активной ячейке содержится такая формула:

=СЧЁТЕСЛИ(C5:C11;»?????»)

Вот как эта формула работает:

СЧЕТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат пять знаков соответствия содержимого каждой ячейки с образцом»?????», который подается в качестве критериев для СЧЕТЕСЛИ.

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

Содержащие клетки удовлетворяющие критерию возвращается как число, которое, в примере это число 1.

Файл подсчет ячеек, содержащих по пять символов  xls

=СЧЕТЕСЛИ («диапазон»;»

Источник: http://devsap.ru/category/schetesli-2/

Как Начать Использовать СЧЕТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel

Обучение работе в Microsoft Excel — это добавление все большего и большего числа формул и функций к вашему инструментарию. Комбинируйте их, и вы можете делать со своими электронными таблицами, практически все что угодно.

В этом уроке, вы узнаете, как можно использовать три весьма полезных формулы в Excel: СУММЕСЛИ, СЧЕТЕСЛИ  и СРЗНАЧЕСЛИ.

Здесь у нас список транзакций за месяц с классификацией по типам расходов.

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

С правой стороны, есть ячейка: Dining Out Expense в которой используются эти три формулы, что бы отследить мои затраты:

• СЧЕТЕСЛИ — Используется для того чтобы подсчитать количество раз, когда в списке появляется слово «Restaurant».
• СУММЕСЛИ — Рассчитывает сумму всех затрат со словом «Restaurant».
• СРЗНАЧЕСЛИ — Рассчитывает среднее значения для расходов со словом «Restaurant».

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

• СУММЕСЛИ — суммирует значения, удовлетворяющие заданным условиям, как например, суммирует все затраты из одной категории.
• СЧЕТЕСЛИ — Подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, например, сколько раз какое-то название повторяется в списке.
• СРЗНАЧЕСЛИ — Рассчитывает условное среднее значение; вы можете рассчитать среднюю оценку только для экзаменов.

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

СЧЁТЕСЛИ, СУММЕСЛИ и СРЗНАЧЕСЛИ в Excel (Короткий ВидеоУрок)

Скринкаст — один из лучших способов посмотреть и приобрести новый навык, включая и этот — как начать использовать эти три ключевые формулы в Microsoft Excel. Посмотрите этот видеоурок ниже, что узнать как я работаю в Excel. Загрузите бесплатно Книгу с примерами, которую я использую в этом уроке.

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

Как Использовать СУММЕСЛИ в Excel

Используйте для этой части урока, вкладку SUMIF (лист с таким названием) в закачанном вами файле примеров.

Подумайте о СУММЕСЛИ, как  о способе сложения значений, удовлетворяющих определенному правилу. Мы можем суммировать значения из списка, которые принадлежат определенной категории, или все значения которые больше или меньше какого-то значения.

Вот как работает формула СУММЕСЛИ:

=СУММЕСЛИ(ячейки которые нужно проверить на условие; само условие; какие ячейки складывать при удовлетворении условию)

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

У меня есть список транзакций, и я собираюсь использовать СУММЕСЛИ, что бы отследить на что я трачу деньги.

Я хочу знать две вещи:

1. 1.Сколько всего денег я потратил в ресторанах в этом месяце.
2. Все затраты в этом месяце, из любой категории, которые превысили значение 50$.

Вместо того, чтобы вручную складывать данные, мы можем добавить пару формул СУММЕСЛИ, чтобы автоматизировать весь процесс. Я помещу результаты в таблицу с зеленой шапкой Restaurant Expense, которая находится справа. Посмотрите как я это делаю.

Сумма Затрат на Ресторан

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

Вот формула, которую я использую в этом примере:

• =СУММЕСЛИ(B2:B17;»Restaurant»;C2:C17)
• Обратите внимание, что элементы разделены точкой с запятой (в онлайн версии разделителем служит запятая) . Эта формула выполняет три вещи:
• Смотрит, что находится в ячейках с В2 по В17 в категориях затрат
• Использует слово «Restaurant» в качестве критерия для выбора того, что суммировать
• Использует значения в ячейках С2-С17, что бы суммировать
• В этом примере, я суммирую все величины, с категорией затрат —  «Restaurant».

Когда я жму ввод, Excel вычисляет сумму моих расходов на ресторан. Используя СУММЕСЛИ, легко делать легкие статистические расчеты, которые помогут вам отслеживать данные определенного типа.

Затраты Выше 50$

Мы посмотрели как делать проверку условия, по конкретной категории, а теперь давайте выполним суммирование все величин, значения которых больше чем какое-то значение, в не зависимости от категории. В этом случае, я хочу найти все затраты, которые превысили 50$.

Давайте напишем простую формулу, что бы найти сумму всех затрат выше 50$:

=СУММЕСЛИ(C2:C17;»>50″)

В этом случае, формула чуть проще: так как мы суммируем те же величины, что мы и проверяем на условие (С2-С17), мы просто должны указать эти ячейки. Затем мы должны добавить точку с запятой и потом «>50», что бы суммировать только те значения, которые больше 50$.

Сумма всех затрат, которые превышают 50 долларов, с помощью простой формулы в Excel/

В этом примере используется знак «больше», но в качестве дополнительной тренировки: попробуйте суммировать все маленькие расходы, например все расходы, которые меньше 20 долларов или меньше.

Как Использовать СЧЕТЕСЛИ в Excel

1. Используйте для этой части урока, вкладку COUNTIF (лист с таким названием) в закачанном вами файле примеров.
2. Если СУММЕСЛИ используется для того, что сложить значения, удовлетворяющие определенным условиям, то СЧЕТЕСЛИ подсчитает сколько раз нечто появилось в наборе данных.
3. Вот общий формат для формулы СЧЕТЕСЛИ:
4. = СЧЕТЕСЛИ(ячейки которые надо подсчитывать, критерий по которым ячейку принимать в расчет)
5. Используя те же данные, давайте посчитаем случаи появления такой информации:
6. Сколько раз в течение месяца я покупал одежду

• Количество затрат, значение которых равно или больше 100 долларов

Число Случаев Покупки Одежды

• Мой первый СЧЕТЕСЛИ будет смотреть на тип расходов и подсчитывать количество покупок с категорией «Clothing» среди моих транзакций.
• Окончательная формула будет выглядеть следующим образом:
• =СЧЕТЕСЛИ(B2:B17;»Clothing»)

Эта формула смотрит в столбец с названием «Expense Type», подсчитывает количество раз, сколько ей встретилось слово «clothing», и суммирует их. В результате получается 2.

Формула СЧЁТЕСЛИ подсчитывает количество расходов, с названием одежда «Clothing» и суммирует их.

Количество Затрат на Сумму 100$+

1. Теперь давайте рассмотрим количество транзакций в моем списке значение которых от 100 долларов и выше.
2. Вот формула, которую я буду использовать:
3. =СЧЕТЕСЛИ(C2:C17;»>100″)

Это простая формула, состоящая из двух частей: она просто указывает Excel список данных которые нужно считать, и дает правило для подсчета. В этом случае, мы просматриваем ячейки С2-С17, на предмет значений, которые выше 100 долларов.

Подсчет числа транзакции на сумму 100$+ с помощью формулы СЧЕТЕСЛИ в Excel.

Как Использовать СРЗНАЧЕСЛИ в Excel

Используйте для этой части урока, вкладку СРЗНАЧЕСЛИ (лист с таким названием) в закачанном вами файле примеров.

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

• Формат формулы СРЗНАЧЕСЛИ следующий:
• =СРЗНАЧЕСЛИ(ячейки которые нужно проверить на условие; само условие; для каких ячеек рассчитывать среднее при удовлетворении условию)
• Формат формулы СРЗНАЧЕСЛИ, такой же как у формулы СУММЕСЛИ.
• Давайте используем СРЗНАЧЕСЛИ формулу, для расчета двух статистических величин для моих затрат:
• Средние затраты на рестораны..
• Среднее для всех затрат, которые меньше 25 долларов.

Затраты на Рестораны в Среднем

1. Чтобы подсчитать  сколько в среднем я потратил на рестораны, я написал формулу СРЗНАЧЕСЛИ, что бы рассчитать среднее значение на основании категории.
2. =СРЗНАЧЕСЛИ(B2:B17;»Restaurant»,C2:C17)
3. В этой формуле, есть три часть, каждая из которых отделена точкой с запятой:
4. В2:В17 задает диапазон ячеек для которых проверяется выполнение условия. Так как категория затрат записана в этом столбце.
5. Слово «Restaurant» задает значение, которое нужно искать.
6. И наконец С2-С17 — из которого берутся значения для расчета среднего.

Здесь я использую СРЗНАЧЕСЛИ, что бы усреднить мои затраты на еду в ресторанах.

И наконец, Excel усредняет все мои затраты на рестораны в путешествии. По формуле, которую я ему дал.

Вы можете так же попробовать как работает эта формула заменив категорию «Restaurant» другой категорией, например «Clothing.»

Среднее для Затрат Меньше чем 25$

• Если я слежу за своими незначительными покупками, и хочу знать сколько я потратил в среднем, я могу написать СРЗНАЧЕСЛИ для затрат, значение которых меньше некой величины.
• Вот формула, которую я использую, для того, чтобы сделать это:
• =СРЗНАЧЕСЛИ(C2:C17;»

Источник: https://business.tutsplus.com/ru/tutorials/how-to-start-using-countif-sumif-and-averageif-in-excel—cms-28086