Подсчет полных текстовых строк

Полезные функции Excel

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

Быстрый подсчет и суммирование

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

 

 

Функция ЕСЛИ

В MS Excel существует функция ЕСЛИ(), позволяющая выбрать одно из двух заданных значений в зависимости от условия.

Вот простой пример использования. Допустим, имеется столбец цифр в ячейках B2-B11. Необходимо в соседнем столбце в ячейках D2-D11 вывести индикатор превышения заданного возраста: если значение в левом столбце больше заданного значения, допустим 18, необходимо выводить слово «совершеннолетний», в противном случае не выводить ничего.

Формула, решающая данную задачу, для ячейки B2 выглядит следующим образом:

=ЕСЛИ(B2>18;"совершеннолетний";"")

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

После подстановки данного значения получаем.

После добавления формулы во все ячейки столбца D получим таблицу следующего вида.

Функция СУММЕСЛИ

Критерий суммирования на основе сравнения текста.

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

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

Наиболее простой способ решить данную задачу – использовать функцию СУММЕСЛИ(), осуществляющую выборочное суммирование значений. Функция принимает три аргумента: диапазон, по которому осуществляется отбор, критерий отбора и диапазон суммируемых значений. Решением для нашей таблицы является следующая формула:

=СУММЕСЛИ(A2:A11;"=жен";B2:B11)

По диапазону A2:A11осуществляется отбор в соответствии с условием "= жен". Для строк, удовлетворяющих условию отбора, значения суммируются.

Обратите внимание, что использовать знак равенства не обязательно. Поэтому следующая формула эквивалентна предыдущей:

=СУММЕСЛИ(A2:A11;"жен";B2:B11)

Следующая формула суммирует годы всех опрошенных, за исключением женщин:

=СУММЕСЛИ (A2:A11;"<>жен"; B2:B11)

На вашем листе это может выглядеть так:

=СУММЕСЛИ(A2:A11;"<>A2";B2:B11)

 

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

Функция СЧЁТЕСЛИ очень полезна при создании формул подсчета с одним критерием.

Эта функция имеет два аргумента:

■ диапазон, ячейки которого подсчитываются;

■ критерий, или логическое условие, по которому определяется, какие ячейки будут включены в подсчет.

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

=СЧЁТЕСЛИ(C2:C11;"=музыка")

Первый аргумент задает диапазон, по которому осуществляется отбор, второй – критерий отбора.

 

Подсчет полных текстовых строк

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

=СЧЁТЕСЛИ(Данные;Текст)

На реальном листе это может выглядеть так:

=СЧЁТЕСЛИ(A2:A11;A9)

Эта формула не учитывает регистр символов.