Функция счетесли в 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

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

=СЧЁТЕСЛИ(rng;»<>X»)

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

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

=СЧЁТЕСЛИ(D5:D11;»<>Готово»)

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые отвечают критериям.

В примере, мы используем «<>» (логический оператор «не равно») для подсчета ячеек в диапазоне D5:D11, которые не равны «Готово». СЧЁТЕСЛИ возвращает число в качестве результата.

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

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

= СЧЁТЕСЛИ (rng;»<>»&А1)

Если значение в ячейке A1 равно «100», критерии будут «<> 100» после конъюнкции, и СЧЁТЕСЛИ будет считать ячейки не равные 100.

Количество ячеек, не равных нескольким критериям

= СЧЁТЗ (диапазон) — СУММПРОИЗВ (СЧЁТЕСЛИ (диапазон; значения))

Для подсчета ячеек, не равных многим критериям (т.е. не равны х, у, z, и т.д.), вы можете использовать формулу, основанную на СЧЁТЕСЛИ, СУММПРОИЗВ и СЧЁТЗ.

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

=СЧЁТЗ(B4:C9)-СУММПРОИЗВ(СЧЁТЕСЛИ(B4:C9;E5:E7))

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

= СЧЁТЕСЛИМН (диапазон; «<> яблоко»; диапазон; «<> оранжевый»)

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

Эта формула использует именованный диапазон «Критерии» (E5: E7) для хранения значений, которые мы не хотим рассчитывать.

Мы начинаем путем подсчета всех значений в диапазоне с функцией СЧЁТЗ:

=СЧЁТЗ(B4:C9)

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

СЧЁТЕСЛИ(B4:C9;E5:E7)

Поскольку диапазон содержит несколько значений, СЧЁТЕСЛИ будет возвращать несколько результатов. В примере, мы получаем обратно массив значений, как этот:

{1;2;2}

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

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

Количество ячеек, не равных х или у

=СЧЁТЕСЛИМН(rng;»<>x»;rng;»<>y»)

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

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

Для того, чтобы подсчитать количество ячеек, которые не равны «красный» или «синий», нужна формула в Е5:

=СЧЁТЕСЛИМН(B5:B10;»<>красный»;B5:B10;»<>синий»)

В этом примере «rng» именованный диапазон, который равен B5:B10.

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

Ключевым в данном случае является использование оператора «не равно», который пишется <>.

Чтобы добавить еще критерии, нужно просто добавить другой диапазон / критерии пару аргументов.

Альтернатива с СУММПРОИЗВ

Функция СУММПРОИЗВ может также рассчитывать клетки, которые отвечают нескольким условиям.

Для приведенного выше примера, синтаксис для СУММПРОИЗВ является:

=СУММПРОИЗВ((rng<>»синий»)*(rng<>»зеленый»))

24.02.2017 Блог Дмитрия а. Дмитрий Рубрика: Microsoft Office

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

Перед началом данной темы я бы хотел вам посоветовать отличный обучающий продукт по теме экселя, по названием «Неизвестный 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;«<70»).</p>

Помимо этого, у вас есть возможность задать условия И/ИЛИ. Только во втором случае придется использовать сразу несколько правил. Смотрим: вам необходимо найти клетки, в которых слова начинаются с буквы В или Р — пишете =СЧЕТЕСЛИ(А1:Е4;«В*»)+ СЧЕТЕСЛИ(А1:Е4;«Р*»).

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

До новых встреч!

Этой статьей стоит поделиться

  • Гайдамакина Вероника Ивановна, учитель информатики

Разделы: Информатика

Статья представляет собой подборку заданий для проведения итоговых уроков по изучению Excel. Задания снабжены пояснениями, которые с каждым упражнением становятся все менее подробными. Такой подход заставляет учащихся не просто выполнять предложенные упражнения, но и запоминать приемы работы, ведь иначе придется возвращаться к уже выполненной работе и разбираться с заданием заново; а также способствует простому и понятному для учащихся оцениванию их работы.

Упражнение 1. «Магазин»

Приложение 1

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

Комментарии:

I. Создайте две таблицы: с исходными данными и результатами.

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р.  
3 2 Кефир Поставщик № 2 40 26,00р.  
4 3 Варенец Поставщик № 3 86 34,00р.  
5 4 Молоко Поставщик № 3 45 26,00р.  
6 5 Молоко Поставщик № 4 87 24,00р.  
7 6 Варенец Поставщик № 4 34 41,00р.  
8 7 Кефир Поставщик № 4 67 32,00р.  
9 8 Кефир Поставщик № 3 45 39,00р.  
10 9 Молоко Поставщик № 2 43 35,00р.  
11 10 Молоко Поставщик № 5 32 33,00р.  

Таблица 1

  A B C D E

14

Наименование Кол-во поставок Суммарная стоимость Средняя цена

15

1 Молоко      

16

2 Кефир      

17

3 Варенец      

Таблица 2

II. Введите формулы в таблицы.

1. В первой таблице формула вводится только в поле «Стоимость»: для определения стоимости нужно количество умножить на цену. Примерный вид формулы в ячейке

F2: =D2*E2.

Результат:

  A B C D E F
1 Наименование Поставщик Количество, л Цена Стоимость
2 1 Молоко Поставщик № 1 76 43,00р. 3 268,00р.
3 2 Кефир Поставщик № 2 40 26,00р. 1 040,00р.
4 3 Варенец Поставщик № 3 86 34,00р. 2 924,00р.
5 4 Молоко Поставщик № 3 45 26,00р. 1 170,00р.
6 5 Молоко Поставщик № 4 87 24,00р. 2 088,00р.
7 6 Варенец Поставщик № 4 34 41,00р. 1 394,00р.
8 7 Кефир Поставщик № 4 67 32,00р. 2 144,00р.
9 8 Кефир Поставщик № 3 45 39,00р. 1 755,00р.
10 9 Молоко Поставщик № 2 43 35,00р. 1 505,00р.
11 10 Молоко Поставщик № 5 32 33,00р. 1 056,00р.

Таблица 1.1

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

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

=СЧЁТЕСЛИ(B$2:B$11;B15),

где B2:B11 — диапазон наименований молочных продуктов из первой таблицы, а B15 — ячейка второй таблицы, содержащая наименование продукта (для данного примера — «Молоко»).

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5    
16 2 Кефир 3    
17 3 Варенец 2    

Таблица 2.1

b) Для определения суммарной стоимости всех продуктов одного названия нужно выбрать из первой таблицы и сложить стоимость всего, например, молока в магазине. Для этого используйте функцию СУММЕСЛИ, которая суммирует данные, отобранные по заданному критерию в данном диапазоне. Примерный вид формулы в ячейке D15:

=СУММЕСЛИ(B$2:B$11;B15;F$2:F$11),

где F2:F11 — диапазон стоимости продукта из первой таблицы.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р.  
16 2 Кефир 3 4 939,00р.  
17 3 Варенец 2 4 318,00р.  

Таблица 2.2

c) Для определения средней цены нужно сложить все цены на один вид продукта (таблица 1), а затем разделить на количество поставок (таблица 2). Примерный вид формулы в ячейке Е15:

=СУММЕСЛИ(B$2:B$11;B15;E$2:E$11)/C15,

где E2:E11 — диапазон с ценами из таблицы 1, а C15 — ячейка, содержащая количество поставок данного продукта.

Результат:

  A B C D E
14 Наименование Кол-во поставок Суммарная стоимость Средняя цена
15 1 Молоко 5 9 087,00р. 32,20р.
16 2 Кефир 3 4 939,00р. 32,33р.
17 3 Варенец 2 4 318,00р. 37,50р.

Таблица 2.3

Замечания:

Данное упражнение может быть дополнено следующими заданиями (и не только ими):

Упражнение 2. «Студенческие стипендии» [1]

Приложение 1

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

  • стипендия не назначается, если среди оценок есть хотя бы одна двойка;
  • 3,0 <</u> средний балл < 3,5 — 1000р.
  • 3,5 <</u> средний балл < 4,0 — 1200р.
  • 4,0 <</u> средний балл < 4,5 — 1500р.
  • 4,5 <</u> средний балл < 5,0 — 1800р.
  • средний балл = 5,0 — 2000р.

Определить общую сумму назначенных стипендий.

Замечания:

Формулировка данной задачи, с некоторыми изменениями, взята из учебника «Информатика: Практикум по технологии работы на компьютере» под ред. Н.В. Макаровой.

Комментарии:

  • Фамилий в таблице должно быть не менее 10, предметов не менее 5, например:
Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3    
2 Гаврилов 4 4 3 3 3    
3 Зайцев 3 3 3 3 3    
4 Иванов 4 3 5 3 3    
5 Краснов 5 4 4 4 4    
6 Кузнецов 4 5 5 5 5    
7 Петров 5 5 5 5 5    
8 Сидоров 4 3 3 3 5    
9 Смирнов 4 4 4 4 4    
10 Солдатов 5 4 5 2 3    
  • Для определения количества двоек использовать функцию СЧЁТЕСЛИ. Можно вставить еще один столбец для определения количества двоек, а можно вложить функцию СЧЁТЕСЛИ в функцию ЕСЛИ, которая будет использована для определения размера стипендии. Примерный вид формулы:

=ЕСЛИ(СЧЁТЕСЛИ(C3:G3;2)>0;0;ЕСЛИ(H3<3,5;1000;ЕСЛИ(H3<4;1200; ЕСЛИ(H3<4,5;1500;ЕСЛИ(H3<5;1800;2000))))).</em>

Результат:

Фамилия Предметы Средний балл Стипендия
Матем. Физика Информ. Химия История
1 Алексеев 5 3 2 3 3 3,2 0р.
2 Гаврилов 4 4 3 3 3 3,4 1 000р.
3 Зайцев 3 3 3 3 3 3 1 000р.
4 Иванов 4 3 5 3 3 3,6 1 200р.
5 Краснов 5 4 4 4 4 4,2 1 500р.
6 Кузнецов 4 5 5 5 5 4,8 1 800р.
7 Петров 5 5 5 5 5 5 2 000р.
8 Сидоров 4 3 3 3 5 3,6 1 200р.
9 Смирнов 4 4 4 4 4 4 1 500р.
10 Солдатов 5 4 5 2 3 3,8 0р.
              Итого: 11 200р.

Упражнение 3. «Завод железобетонных изделий»

Приложение 1

Завод ЖБИ выпускает бетонные строительные блоки. Характеристики блоков: марка, длина (м), ширина (м), высота (м) и удельный вес бетона, из которого изготовлен блок (кг/м3). На завод поступил заказ, который представляет собой список, содержащий марки требуемых блоков и количество блоков каждой марки. Определить, сколько вагонов потребуется для отправки блоков заказчику, если: блоки разных марок не могут находиться в одном вагоне, а грузоподъемность одного вагона N тонн.

I. Для решения задачи нужно создать две таблицы: с исходными данными и результатами.

II. Ввод формул.

1. Вес блока (в первой таблице) определите с помощью функции ПРОИЗВЕД и переведите в тонны. 2. Чтобы определить количество вагонов для блоков каждой марки, нужно количество блоков умножить на вес блока и разделить на грузоподъемность:              a) Для того чтобы выбрать соответствующий вес блока из первой таблицы, используйте функцию СУММЕСЛИ.              b) Так как количество вагонов может быть только целым числом, то результат округлите до целого с помощью функции ОКРУГЛВВЕРХ. 3. В отдельной ячейке определите общее количество вагонов для блоков всех марок.

Упражнение 4. «Прайс-лист»

Приложение 1

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

Литература

  1. Информатика: Практикум по технологии работы на компьютере/ Под ред. Н.В. Макаровой. – 3-е изд., переработ. – М.: Финансы и статистика, 2003. – 256 с.: ил.

7.07.2011

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

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

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

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

Подготовим диапазон данных и на его основе рассмотрим, как выглядят конкретные по функции «СЧЕТЕСЛИ» в 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. Примеры, рассматриваемые в текущем разделе, будут довольно простыми, однако они основополагающие, и рассмотреть их необходимо, прежде чем приступать к более сложным.

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

Пример

Ответ

Комментарий

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

2

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

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

1

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

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

2

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

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

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

=СЧЁТЕСЛИ(В2:В9; «<55»)</p>

2

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

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

4

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

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

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

Знак

Описание

*

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

?

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

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

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

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

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

Пример

Ответ

Комментарий

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

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

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

3

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

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

3

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

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

1

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

=СЧЁТЕСЛИ(D2:D9; «<» & (СЕГОДНЯ()-10))</p>

3

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

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

3

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

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

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

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий