ОГЭ 14. Операция ЕСЛИ() и функции СЧЁТ(), СЧЁТЕСЛИ(), СЧЁТЕСЛИМН() в электронных таблицах + файлы

Введение

Электронные таблицы, такие как Microsoft Excel или Google Sheets, предоставляют мощные инструменты для анализа данных. Среди них особое место занимают логические операции и функции подсчёта, которые позволяют автоматизировать расчёты и принимать решения на основе заданных условий. В этой статье мы подробно рассмотрим операцию ЕСЛИ() и функции СЧЁТ(), СЧЁТЕСЛИ(), СЧЁТЕСЛИМН().

Операция ЕСЛИ()

Основы синтаксиса

Функция ЕСЛИ() позволяет выполнять проверку условия и возвращать разные значения в зависимости от того, истинно (TRUE) или ложно (FALSE) условие.

Синтаксис:

=ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь)

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

1. Простая проверка:CopyDownload=ЕСЛИ(A1>10; "Больше 10"; "10 или меньше")
   Если значение в ячейке A1 больше 10, формула вернёт "Больше 10", иначе - "10 или меньше".
2. Вложенные ЕСЛИ:CopyDownload=ЕСЛИ(A1>90; "Отлично"; ЕСЛИ(A1>70; "Хорошо"; ЕСЛИ(A1>50; "Удовлетворительно"; "Неудовлетворительно")))
   Эта формула реализует множественную проверку и возвращает оценку в зависимости от числа в A1.

Советы по использованию

• Избегайте слишком глубокой вложенности ЕСЛИ (более 3-4 уровней) - это усложняет чтение и отладку формулы.
• Для сложных условий можно использовать функции И(), ИЛИ() внутри ЕСЛИ().
• В современных версиях Excel можно использовать IFS() для множественных условий без вложенности.

Функция СЧЁТ()

Назначение и синтаксис

Функция СЧЁТ() подсчитывает количество ячеек в диапазоне, которые содержат числа.

Синтаксис:

=СЧЁТ(диапазон1; [диапазон2]; ...)

Примеры

1. Подсчёт числовых значений:CopyDownload=СЧЁТ(A1:A10)
   Вернёт количество ячеек в диапазоне A1:A10, содержащих числа.
2. Подсчёт по нескольким диапазонам:CopyDownload=СЧЁТ(A1:A10; C1:C10)
   Суммирует количество числовых ячеек в обоих диапазонах.

Особенности

• Не учитывает текстовые значения, логические значения (TRUE/FALSE), пустые ячейки и ошибки.
• Для подсчёта всех непустых ячеек используйте СЧЁТЗ().

Функция СЧЁТЕСЛИ()

Назначение и синтаксис

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

Синтаксис:

=СЧЁТЕСЛИ(диапазон; условие)

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

1. Простое условие:CopyDownload=СЧЁТЕСЛИ(B1:B20; ">20")
   Подсчитывает ячейки в B1:B20 со значениями больше 20.
2. Текстовое условие:CopyDownload=СЧЁТЕСЛИ(A1:A30; "Яблоки")
   Считает количество ячеек со словом "Яблоки".
3. Условие с подстановочными знаками:CopyDownload=СЧЁТЕСЛИ(C1:C100; "*-2023")
   Подсчитывает ячейки, заканчивающиеся на "-2023".

Особенности

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

Функция СЧЁТЕСЛИМН()

Назначение и синтаксис

СЧЁТЕСЛИМН() расширяет возможности СЧЁТЕСЛИ(), позволяя задавать несколько диапазонов и соответствующих условий.

Синтаксис:

=СЧЁТЕСЛИМН(диапазон_условия1; условие1; [диапазон_условия2; условие2]; ...)

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

1. Два условия:CopyDownload=СЧЁТЕСЛИМН(A1:A100; ">50"; B1:B100; "<100")
   Подсчитывает строки, где значение в столбце A >50 и в столбце B <100.
2. Несколько критериев:CopyDownload=СЧЁТЕСЛИМН(C1:C500; "Восточный"; D1:D500; ">=100000"; E1:E500; "<=500000")
   Считает количество записей, где регион "Восточный", а продажи между 100,000 и 500,000.

Важные особенности

• Количество пар "диапазон-условие" может быть до 127 в Excel.
• Все условия работают по принципу "И" - все должны выполняться одновременно.
• Для условий "ИЛИ" потребуется несколько функций СЧЁТЕСЛИМН() или другие подходы.

Файлы для тренировки

Сравнительная таблица функций

ФункцияНазначениеМакс. условийПоддержка нескольких диапазоновСЧЁТ()Числовые ячейкиНетДаСЧЁТЕСЛИ()Одно условие1НетСЧЁТЕСЛИМН()Множественные условия127Да

Практические советы

1. Оптимизация производительности:
   Избегайте использования целых столбцов (A:A) в функциях подсчёта - ограничьте диапазон реальными данными.
   Для сложных анализов комбинируйте эти функции с другими, например, СУММЕСЛИМН().
2. Отладка формул:
   Если функция возвращает неожиданный результат, проверьте:
   Форматы данных (особенно когда числа сохранены как текст)
   Регистр символов в текстовых условиях
   Границы диапазонов
3. Альтернативы:
   Для более сложного анализа данных рассмотрите использование сводных таблиц.
   В Excel 365 можно использовать динамические массивы и функции типа FILTER().

Разбор заданий

Файлы для тренировки

Заключение

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