КАТАЛОГ ТОВАРОВ

Срок доставки товара в течении 1-3 дней !!!

 

ПОЛЬЗОВАТЕЛЬ
КОРЗИНА

Функция СЧЕТЕСЛИ в Excel и примеры ее использования. Условия в excel


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

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

Синтаксис и особенности функции

Сначала рассмотрим аргументы функции:

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

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

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

Рекомендации для правильной работы функции:

  • Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
  • Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
  • Функция не учитывает регистр текстовых значений.
  • При формулировании условия подсчета можно использовать подстановочные знаки. «?» - любой символ. «*» - любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
  • Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.


Функция СЧЕТЕСЛИ в Excel: примеры

Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.

У нас есть такая таблица:

Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;">100"). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:

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

Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.

Формула: =СЧЁТЕСЛИ(A1:A11;"табуреты"). Или:

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

Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;"таб*").

Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;"*и"). Получаем:

Формула посчитала «кровати» и «банкетки».

Используем в функции СЧЕТЕСЛИ условие поиска «не равно».

Формула: =СЧЁТЕСЛИ(A1:A11;""&"стулья"). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».

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

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

  1. Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;"столы")+СЧЁТЕСЛИ(A1:A11;"стулья"). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
  2. Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» - на базе критерия в ячейке А2.
  3. Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;">=100")-СЧЁТЕСЛИ(B1:B11;">200").
  4. Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;">=100")-СЧЁТЕСЛИ(A1:B11;">200"). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
  5. Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.

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

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

Посчитаем количество реализованных товаров по группам.

  1. Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
  2. Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» - «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).

Скачать примеры функции СЧЕТЕСЛИ в Excel

Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.

exceltable.com

СРЗНАЧЕСЛИ (функция СРЗНАЧЕСЛИ) - Служба поддержки Office

В этой статье описаны синтаксис формулы и использование функции СРЗНАЧЕСЛИ в Microsoft Excel.

Описание

Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

Синтаксис

СРЗНАЧЕСЛИ(диапазон, условия, [диапазон_усреднения])

Аргументы функции СРЗНАЧЕСЛИ указаны ниже.

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

  • Условие.    Обязательный. Условие в форме числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при вычислении среднего. Например, условие может быть выражено следующим образом: 32, "32", ">32", "яблоки" или B4.

  • Диапазон_усреднения.    Необязательный. Фактическое множество ячеек для вычисления среднего. Если этот параметр не указан, используется диапазон.

Замечания

  • Ячейки в диапазоне, которые содержат значения ИСТИНА или ЛОЖЬ, игнорируются.

  • Если ячейка в "диапазоне_усреднения" пустая, функция СРЗНАЧЕСЛИ игнорирует ее.

  • Если диапазон является пустым или текстовым значением, то функция СРЗНАЧЕСЛИ возвращает значение ошибки #ДЕЛ/0!.

  • Если ячейка в условии пустая, "СРЗНАЧЕСЛИ" обрабатывает ее как ячейки со значением 0.

  • Если ни одна ячейка в диапазоне не соответствует условию, функция СРЗНАЧЕСЛИ возвращает ошибку #ДЕЛ/0!.

  • В этом аргументе можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).

  • Значение "диапазон_усреднения" не обязательно должно совпадать по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в "диапазоне_усреднения", а затем добавляются ячейки с совпадающим размером и формой. Например:

Если диапазон равен

И "диапазон_усреднения"

Обрабатываемые ячейки

A1:A5

B1:B5

B1:B5

A1:A5

B1:B3

B1:B5

A1:B4

C1:D4

C1:D4

A1:B4

C1:C2

C1:D4

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

  • Среднее значение    — это среднее арифметическое, которое вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

  • Медиана    — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

  • Мода    — это число, наиболее часто встречающееся в данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

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

Примеры

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

Стоимость имущества

Комиссионные

100000

7000

200000

14000

300000

21000

400000

28000

Формула

Описание

Результат

=СРЗНАЧЕСЛИ(B2:B5;"<23000")

Среднее значение всех комиссионных меньше 23 000. Этому условию удовлетворяют три из четырех значений, сумма которых составляет 42 000.

14000

=СРЗНАЧЕСЛИ(A2:A5;"<250000")

Среднее среди всех значений стоимости имущества меньше 250 000. Этому условию удовлетворяет два из четырех значений, их сумма составляет 300 000.

150000

=СРЗНАЧЕСЛИ(A2:A5;"<95000")

Среднее среди всех значений стоимости имущества меньше 95 000. Так как количество значений стоимости имущества, удовлетворяющих этому условию, равно 0, функция СРЗНАЧЕСЛИ вернет ошибку #ДЕЛ/0! из-за попытки деления на 0.

#ДЕЛ/0!

=СРЗНАЧЕСЛИ(A2:A5;">250000";B2:B5)

Среднее значение всех комиссионных для стоимости имущества более 250 000. Этому условию удовлетворяют два значения, сумма которых составляет 49 000.

24500

Пример 2

Регион

Доходы (в тысячах)

Восточный

45678

Западный

23789

Северный

-4789

Южная (новое представительство)

0

Средний Запад

9678

Формула

Описание

Результат

=СРЗНАЧЕСЛИ(A2:A6;"=*Западная";B2:B6)

Среднее значение всех доходов для Западной и Средне-Западной областей.

16733,5

=СРЗНАЧЕСЛИ(A2:A6;"<>*(Новое представительство)";B2:B6)

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

18589

support.office.com

"СЧЕТЕСЛИ" в Excel: примеры, описание

Функция "СЧЕТЕСЛИ" - одна из наиболее часто употребимых функций в мощном табличном процессоре "Эксель" от компании "Майкрософт". Для чего она нужна? Это понятно уже из ее названия. Она поможет любому пользователю, задавшемуся вопросом «Как посчитать ячейки в Excel по заданному критерию?», получить нужные цифры.

Аргументы функции

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

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

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

А

В

С

D

1

Фрукты/овощиЦена закупочная (кг)Надбавка (%)Дата закупки

2

Яблоки голд

74,5

28

01.05.2017

3

Яблоки фуджи

69,8

50

16.05.2017

4

Бананы

41

75

04.05.2017

5

Бананы мини

74,5

42

14.05.2017

6

Яблоки глостер

54,2

34

15.05.2017

7

Огурец китайский

64

17

07.05.2017

8

Огурец опыляемый

110,6

28

16.05.2017

9

Огурец неопыляемый

132,9

28

01.05.2017

Важное дополнение

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

Простые условия для функции

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

работа с таблицами в excel

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

Пример

Ответ

Комментарий

=СЧЁТЕСЛИ(В2:В9; 74,5)

2

Выявляет, сколько товаров на складе закуплено по цене 74.5

=СЧЁТЕСЛИ(А2:А9;А2)

1

Если данную формулу «протянуть» до конца таблицы, то увидим, нет ли у нас дублей по наименованию товаров (если 1 – то дублей нет)

=СЧЁТЕСЛИ(D2:D9; СЕГОДНЯ())

2

Количество ячеек с товарами, поступившими на склад сегодня (используется системная дата компьютера)

=СЧЁТЕСЛИ(С2:С9; "")

0

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

=СЧЁТЕСЛИ(В2:В9; "<55")

2

Количество ячеек со значением менее 55. Аналогичным образом выполняется сравнение больше ">" и не равно "<>"

=СЧЁТЕСЛИ(В2:В9; "<55") + СЧЁТЕСЛИ(В2:В9; ">100")

4

Суммарное количество найденных ячеек по комбинации двух условий

Подстановочные знаки

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

Знак

Описание

*

Текст (любое количество символов)

?

Текст (количество символов соответствует количеству знаков вопроса). Работает только с текстовыми данными! Если в ячейке нет ни одной буквы, покажет всегда 0

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

Условия для функции с подстановочными знаками. Комбинирование функций

Комбинирование с другими функциями с целью создания нужного условия происходит посредством соединения логического условия ("больше", "меньше", "не равно") и нужной функции знаком "амперсанд" (&).

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

Пример

Ответ

Комментарий

=СЧЁТЕСЛИ(А2:А9;"яблоки")

0

Количество ячеек, содержащих только слово «Яблоки» (нечувствительно к регистру) и все

=СЧЁТЕСЛИ(А2:А9;"яблоки*")

3

Количество ячеек, начинающихся со слова «Яблоки» и любым окончанием

=СЧЁТЕСЛИ(А2:А9;"*й*")

3

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

=СЧЁТЕСЛИ(А2:А9; "??????")

1

Товары с наименованием длиной всего в 6 символов

=СЧЁТЕСЛИ(D2:D9; "<" & (СЕГОДНЯ()-10))

3

Количество ячеек с датой закупки старше десяти дней назад

=СЧЁТЕСЛИ(С2:С9; ">" & СРЗНАЧ(С2:С9))

3

Число ячеек со значением продажной надбавки больше среднего

На этом мы заканчиваем рассматривать функцию "СЧЕТЕСЛИ" в Excel. как посчитать ячейки в excel

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

fb.ru

Используем логические функции Excel для задания сложных условий

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

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

Логическая функция И()

Возвращает ИСТИНА, если все используемые условия истинны, или ЛОЖЬ, если хотя бы одно из них ложное.

=И(A1>B1; A2<>25)

Данная формула проверяет сразу два условия и возвращает ИСТИНА, если оба из них выполняются. В следующем примере оба условия истинны, поэтому и функция возвращает ИСТИНА:

Логические функции в Excel

В данном примере первое условие ложно, а второе истинно, поэтому функция вернула ЛОЖЬ:

Логические функции в Excel

Логическая функция ИЛИ()

Возвращает ИСТИНА, если хотя бы одно из условий истинно, или ЛОЖЬ, если все условия ложны.

=ИЛИ(A1>B1; A2>B2; A3>B3)

Данная формула проверяет три условия и вернет логическое ИСТИНА, если хотя бы одно из них выполняется. В примере на рисунке ниже лишь последнее условие истинно, но этого достаточно, чтобы и функция ИЛИ вернула значение ИСТИНА.

Логические функции в Excel

Поскольку в этом примере все условия ложны, то и функция вернула ЛОЖЬ.

Логические функции в Excel

Логическая функция НЕ()

Меняет логическое значение своего аргумента на противоположное. Если аргумент имеет значение ИСТИНА, функция меняет его на ЛОЖЬ. Если же значение аргумента ЛОЖЬ, то функция меняет его на ИСТИНУ.

=НЕ(A1>B1)

Например, на следующем рисунке видно, что число в ячейке A1 больше, чем в A2. Соответственно, выражение A1>B1 – имеет логическое значение ИСТИНА. Применив функцию НЕ в формуле, мы изменили его на противоположное.

Логические функции в Excel

Логическая функция ИСКЛИЛИ()

Возвращает логическое «исключающее или» всех аргументов. Функция вернет ИСТИНА, если число истинных условий нечетное, и ЛОЖЬ, если число истинных условий четное. Если все аргументы функции ИСКЛИЛИ имеют значение ЛОЖЬ, то и функция возвратит ЛОЖЬ.

=ИСКЛИЛИ(A1>B1; A2>B2; A3>B3; A4>B4)

В следующем примере формула вернет ИСТИНА, поскольку три условия из четырех истинны (нечетное количество):

Логические функции в Excel

В данном примере формула вернет ЛОЖЬ, поскольку все 4 условия истинны (четное количество):

Логические функции в Excel

В данном примере формула вернет ЛОЖЬ, поскольку не одно из условий не выполняется:

Логические функции в Excel

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

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru