Использование функции «СЧЁТЕСЛИМН» для анализа данных

 

Часто бывает необходимо подсчитать количество ячеек в диапазоне, которые удовлетворяют определенному условию. Например, если лист содержит информацию об объеме продаж, может понадобиться подсчитать количество сделок, заключенных продавцом Светланой, или количество сделок, заключенных после 10 июня. Рассмотрим, как использовать функцию «СЧЁТЕСЛИ» для подсчета количества ячеек в диапазоне, которые удовлетворяют условию, определенному на основе одной строки или столбца на листе.

Синтаксис функции «СЧЁТЕСЛИ»:

Синтаксис функции «СЧЁТЕСЛИ»: СЧЁТЕСЛИ (диапазон; условие).

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

Условие — это число, дата или выражение, которое определяет ячейки для подсчета в диапазоне.

Синтаксис функции «СЧЁТЕСЛИМН»:

СЧЁТЕСЛИМН (диапазон 1, условие 1, диапазон 2, условие 2,…, диапазон_n, условие_n).

Функция «СЧЁТЕСЛИМН» подсчитывает количество строк, для которых запись диапазона 1 удовлетворяет условию 1, запись диапазона 2 удовлетворяет условию 2, запись диапазона_n удовлетворяет условию_n, и так далее. Таким образом, функция «СЧЁТЕСЛИМН» позволяет задавать условие для нескольких столбцов или нескольких условий в одном столбце.

 

Примеры допустимых условий Excel для функции «СЧЁТЕСЛИ»

Залогом успешного использования функции «СЧЁТЕСЛИ» и других похожих функций является понимание разнообразия условий, допустимых в Excel.

 


Вопросы для закрепления теоретического материала

 

  1. Какие средства используются в Ms Excel для проведения анализа данных?
  2. Синтаксис функции «СЧЁТЕСЛИ».
  3. Синтаксис функции «СУММЕСЛИ».

Задания для практического занятия

Задание № 1:

1. Создать таблицу 3 «Ведомость правонарушений».

2. Поле «Вид правонарушений» заполняется произвольно и содержит не менее 30 записей (каждый вид не менее 7 раз).

3. Первые 5 ячеек поля «Дата» заполняется, начиная с 15.01.2013, последовательность дат с интервалом дней, равным номеру варианта (например, если вариант № 5, то следующая дата будет 20.01.2013), остальные – полученной последовательностью дат с помощью копирования.

4. Поле «Сумма потерь» заполняется по формулам, где N – номер варианта.

5. Поле «Отметка о раскрытии» заполняется произвольно.

6. В строке «Всего» надо определить общую сумму потерь за указанный период, а также минимальное, общее, и среднее значение потерь.

7. Определить общую стоимость угнанных машин (результат занести в новую строку).

8. Определить сумму потерь от тех происшествий, стоимость которых высока (например, превышает 100 тыс. руб.).

9. Определить количество угнанных машин.

10. Определить общую сумму потерь на вторую дату в поле «Дата».

11. Определить количество правонарушений на третью дату в поле «Дата».

12. Определить количество раскрытых и нераскрытых правонарушений.

Таблица 3

Вид правонарушений Дата Сумма потерь Отметка о раскрытии
Кража 01.01.2013 N*17,5 Закрыто
Угон авто 06.01.2013 N*28 Передано в суд
Коррупция 11.01.2013 N*1000 Закрыто
Хулиганство 16.01.2013 N*58 Закрыто
Разбой 21.01.2013 N*112,3 Передано в суд
Вымогательство 26.01.2013 N*2000 Передано в суд
… 31.01.2013 N*50 Передано в суд
… 05.02.2013 N*60  
    N*70  
… … … …
Всего:      
Минимальная сумма потерь      
…      
…      

 

  Таблица 4
Год Количество правонарушений
?
     

 

Задание № 2:

 

1. Имея известную зависимость количества правонарушений в период с 2002 по 2013 год (табл. 4), определить прогнозируемое значение правонарушений за 2014 год.

2. По данным таблицы построить график «Статистика правонарушений», на котором добавить линию тренда.

3. Определить в процентном соотношении изменение количества правонарушений в 2014 и предыдущие 3 года.

 

Инструкция по выполнению заданий практического занятия

  1. Прочитайте краткие теоретические и учебно-методические материалы по теме практического занятия.
  2. Устно ответьте на вопросы для закрепления теоретического материала к практическому занятию.
  3. Приступите к выполнению заданий практического занятия на компьютере.