СЧЕТЕСЛИ
Функция СЧЕТЕСЛИ возвращает количество ячеек в наборе, удовлетворяющих заданному условию.
СЧЕТЕСЛИ(тест-множество; условие)
тест-множество: набор, содержащий значения для проверки. Набор тест-множество может содержать любые значения.
условие: выражение, которое сравнивает или проверяет значения и возвращает логическое значение ИСТИНА или ЛОЖЬ. Аргумент условие может включать в себя операторы сравнения, константы, оператор сцепления (знак &), ссылки и подстановочные знаки. Подстановочные знаки могут заменять в выражении отдельные символы или наборы символов. Вопросительный знак (?) заменяет один символ, звездочка (*) заменяет несколько символов, а тильда (~) указывает на то, что следующий за ней символ не является подстановочным знаком, а соответствует только собственному значению. Аргумент условие может также включать функцию РЕГВЫРАЖЕН вместо подстановочных знаков.
Примечания
Каждое значение из набора тест-множество проверяется на соответствие условию, определяемому аргументом условие. Если значение удовлетворяет заданному условию, оно учитывается при вычислении значения функции.
Примеры |
|---|
Данные в следующей таблице не имеют никакого специального смысла, но удобны для иллюстрации типов аргументов, которые функция СЧЕТЕСЛИ включает в результат. Возьмем следующую таблицу: |
A | B | C | D | |
|---|---|---|---|---|
1 | 100 | 200 | 300 | 400 |
2 | lorem | ipsum | dolor | sit |
3 | 100 | 200 | 300 | sit |
4 | ИСТИНА | ИСТИНА | ЛОЖЬ | ЛОЖЬ |
5 | 200 | 400 |
=СЧЕТЕСЛИ(A1:D1; ">0") возвращает 4, так как значения всех ячеек в наборе больше 0. =СЧЕТЕСЛИ(A1:D1; "<>100") возвращает 3, так как значения трех ячеек в наборе не равны 100. Также можно использовать оператор ≠. =СЧЕТЕСЛИ(A3:D3; ">=100") возвращает 3, так как все три числа больше или равны 100, а текстовые значения не проверяются на соответствие условию. Также можно использовать оператор ≥. =СЧЕТЕСЛИ(A1:D5; "=ipsum") возвращает 1, так как в наборе, определяемом этим диапазоном, текстовая строка «ipsum» встречается один раз. =СЧЕТЕСЛИ(A1:D5; "=*t") возвращает 2, так как в наборе, определяемом этим диапазоном, есть две строки, оканчивающиеся буквой «t». |
Пример с использованием функции РЕГВЫРАЖЕН |
|---|
Возьмем следующую таблицу: |
A | B | |
|---|---|---|
1 | 45 | marina@example.com |
2 | 41 | Иван |
3 | 29 | michael@example.com |
4 | 64 | katrina@example.com |
5 | 12 | Ирина |
=СЧЕТЕСЛИ(B1:B5; РЕГВЫРАЖЕН("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+\.[A-Za-z]{2,4})")) возвращает количество ячеек в диапазоне B1:B5, содержащих адрес электронной почты. |
Пример обработки результатов опроса |
|---|
Этот пример иллюстрирует применение различных статистических функций. Он основан на воображаемом опросе. Короткий опрос (всего пять вопросов) был адресован небольшому числу респондентов (10). На каждый вопрос можно было ответить по шкале от 1 до 5 (например, от «никогда» до «всегда») или не отвечать. Перед отправкой по почте каждому опросному листу был назначен номер (№). Результаты показаны в следующей таблице. Пустые ячейки означают, что ответ на вопрос был неверным (вышел за пределы допустимого диапазона) или ответа не было. |
A | B | C | D | E | Ж | |
|---|---|---|---|---|---|---|
1 | № | В1 | В2 | В3 | В4 | В5 |
2 | 101 | 5 | 4 | 4 | 3 | 4 |
3 | 105 | 3 | 2 | 2 | 3 | 3 |
4 | 102 | 4 | 4 | 4 | 4 | |
5 | 104 | 3 | 4 | 2 | 4 | 3 |
6 | 107 | 4 | 3 | 3 | ||
7 | 106 | 4 | 3 | 3 | 4 | |
8 | 109 | 3 | 4 | 1 | 3 | 4 |
9 | 111 | 5 | 2 | 2 | 5 | 3 |
10 | 121 | 4 | 2 | 3 | 3 | 4 |
11 | 115 | 3 | 3 | 3 | 3 |
Для иллюстрации работы с некоторыми функциями предположим, что номер опросного листа включен в буквенный префикс, а шкала ответов задана как A–E (а не 1–5). В этом случае таблица выглядела бы следующим образом: |
A | B | C | D | E | Ж | |
|---|---|---|---|---|---|---|
1 | № | В1 | В2 | В3 | В4 | В5 |
2 | 101 | E | D | D | C | D |
3 | 105 | C | B | B | C | C |
4 | 102 | D | D | D | D | |
5 | 104 | C | D | B | D | C |
6 | 107 | D | C | C | ||
7 | 106 | D | C | C | D | |
8 | 109 | C | D | A | C | D |
9 | 111 | E | B | B | E | C |
10 | 121 | D | B | C | D | |
11 | 115 | C | C | C | C | C |
С помощью этой таблицы и некоторых доступных статистических функций можно собрать информацию о результатах опроса. Следует учитывать, что в этом примере намеренно рассматривается малое количество вопросов и респондентов, поэтому результаты могут казаться очевидными. Однако при наличии 50, 100 или большего количества респондентов, а также значительного количества вопросов результаты не были бы столь очевидными. |
Функция и аргументы | Описание результатов |
|---|---|
=КОРРЕЛ(B2:B11; C2:C11) | Определяет корреляцию между вопросами 1 и 2 методом линейного регрессионного анализа. Корреляция служит показателем согласованности изменения двух переменных (в данном случае — ответов на вопросы из опроса). Точнее, она позволяет ответить на следующий вопрос. Если ответ респондента на вопрос 1 выше (ниже) среднего значения для вопроса 1, можно ли ожидать, что ответ этого респондента на вопрос 2 будет выше (ниже) среднего значения для вопроса 2? В данном случае корреляция ответов незначительна (-0,1732). |
=СЧЕТ(A2:A11) или =СЧЕТЗ(A2:A11) | Определяет общее количество возвращенных опросных листов (10). Следует учитывать, что если идентификатор опросного листа не является числовым, вместо функции СЧЕТ потребуется функция СЧЕТЗ. |
=СЧЕТ(B2:B11) или =СЧЕТЗ(B2:B11) | Определяет общее количество ответов на первый вопрос (9). Распространив эту формулу на всю строку, можно было бы определить количество ответов на каждый вопрос. Поскольку все данные являются числовыми, функция СЧЕТЗ возвращает тот же результат. Однако если бы для опроса применялась шкала от A до E, а не от 1 до 5, результат можно было бы получить только с помощью функции СЧЕТЗ. |
=СЧИТАТЬПУСТОТЫ(B2:B11) | Определяет количество пустых ячеек, соответствующих недопустимым ответам или отсутствию ответов. Распространив эту формулу на всю строку, можно было бы обнаружить, что на вопрос 3 (столбец D) не ответили или ответили неверно три респондента. Это позволяет предположить, что формулировка данного вопроса содержит противоречия или неточна, поскольку ни для одного из остальных вопросов количество не ответивших или неверно ответивших респондентов не превышает 1. |
=СЧЕТЕСЛИ(B2:B11; "=5") | Определяет количество респондентов, давших ответ 5 на конкретный вопрос (в данном случае — на вопрос 1). Распространив эту формулу на всю строку, можно было бы узнать, что ответы 5 были получены только на вопросы 1 и 4. Если бы для опроса применялась шкала от A до E, потребовалась бы формула =СЧЕТЕСЛИ(B2:B11; "=E"). |
=СЧЕТЕСЛИ(B2:B11; "<>5") | Определяет количество респондентов, не давших ответ 5 на конкретный вопрос (в данном случае — на вопрос 1). |
=КОРВАР(B2:B11; C2:C11) | Определяет ковариацию между вопросами 1 и 2. Ковариация служит показателем согласованности изменения двух переменных (в данном случае — ответов на вопросы из опроса). Точнее, она позволяет ответить на следующий вопрос. Если ответ респондента на вопрос 1 выше (ниже) среднего значения для вопроса 1, можно ли ожидать, что ответ этого респондента на вопрос 2 будет выше (ниже) среднего значения для вопроса 2? Примечание. Функция КОВАР неприменима к таблице со шкалой A–E, так как ее аргументы должны быть в числовом формате. |
=СТАНДОТКЛОН(B2:B11) или =СТАНДОТКЛОНП(B2:B11) | Определяет стандартное отклонение — один из показателей разброса — ответов на вопрос 1. Распространив эту формулу на всю строку, можно было бы увидеть, что ответы на вопрос 3 характеризуются наибольшим стандартным отклонением. Если бы результаты представляли ответы не по выборке, а по всей генеральной совокупности, вместо функции СТАНДОТКЛОН следовало бы применить функцию СТАНДОТКЛОНП. Обратите внимание, что значение функции СТАНДОТКЛОН равно квадратному корню из значения функции ДИСП. |
=ДИСП(B2:B11) или =ДИСПР(B2:B11) | Определяет дисперсию — один из показателей разброса — ответов на вопрос 1. Распространив эту формулу на всю строку, можно было бы увидеть, что ответы на вопрос 5 характеризуются наименьшей дисперсией. Если бы результаты представляли ответы не по выборке, а по всей генеральной совокупности, вместо функции ДИСП следовало бы применить функцию ДИСПР. Обратите внимание, что значение функции ДИСП равно квадрату значения функции СТАНДОТКЛОН. |