2.3. Подведение промежуточных итогов

Кроме использования функций =СУММ(…), =СУММЕСЛИ(…), =СРЗНАЧ(…), =МАКС(…), =МИН(…), агрегировать (укрупнить, обобщить) информацию можно с помощью подведения промежуточных итогов.

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

1. Выделить таблицу, включая «шапку»;

2. Команда: ДАННЫЕ – ИТОГИ (рис. 11);

3. Определить параметры подведения итогов:

   1. При каждом изменении в: Месяц;

   2. Операция: Среднее;

   3. Добавить итоги по: Количество преступлений, ед.; Процент раскрываемости, %;

4. ОК.

Рис. 11. Параметры окна Промежуточные итоги.

В результате получим:

Рис. 12. Результаты окна Промежуточных итогов в развернутом виде.

Управляя кнопками окна, можно получить искомый результат (рис. 13):

Рис. 13. Определение средних значений показателей за каждый месяц первого полугодия.

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

Пример 9. Определить средний уровень преступность и процент раскрываемости за первое полугодие по каждому району. Для этого реализуем следующую последовательность действий:

1. Сортировать таблицу по столбцу Район:

1. Выделить таблицу с «шапкой»;

2. Команда: ДАННЫЕ – СОРТИРОВКА;

3. В появившемся окне выставить параметры сортировки (рис. 14);

4. ОК (рис.15);

2. Для подведения итогов выделить таблицу, включая «шапку»;

3. Команда: ДАННЫЕ – ИТОГИ (рис. 16);

4. Определить параметры подведения итогов:

1. При каждом изменении в: Район;

2. Операция: Среднее;

3. Добавить итоги по: Количество преступлений, ед.; Процент раскрываемости, %;

5. ОК.

Развернутый и краткий результата подведения итогов отображен на рис. 17 – 18.

Рис. 14. Ввод параметров сортировки.

Рис.15. Результаты сортировки.

Рис. 16. Параметры окна подведения Промежуточных итогов по полю Район.

Рис. 17. Развернутые результаты подведения Промежуточных итогов по полю Район.

Рис. 18. Краткие результаты подведения Промежуточных итогов по полю Район.

Анализируя результаты итоговой таблицы (рис.18), можно сделать вывод, что наиболее высокий уровень преступности в Краснополянском районе, но здесь наблюдается и наилучший процент раскрываемости преступлений. Данные Пырьевского района близки к средним по городу. В Октябрьском районе уровень преступности ниже, но и хуже раскрываемость преступлений.

2.4. Использование логической функции и поиск данных

Поиск информации в таблицах будет рассмотрен на примере использования функции ВПР(…) и фильтрации данных.

Пример 10. Используя дополнительные сведения о численности населения в районе (рис.19), рассчитать показатель районного уровня преступности «Количество преступлений, число случаев на 1000 населения». Дополнительные сведения о численности населения в районе оформлены в виде таблицы-справочника в диапазоне ячеек J6:K8.

Выбор численности населения из справочника с помощью функции ВПР(…) осуществляется следующим образом (рис. 19).

1. Курсор поместить в ячейку результата – F6;

2. Открыть окно мастера функций;

3. Выбрать категорию Ссылки и массивы;

4. Выбрать функцию ВПР(…), ввести параметры:

   1. В поле Значение указать адрес ячейки, содержащей ключевое значение поиска - C6;

   2. В поле Таблица указать диапазон ячеек, содержащих справочник - J6:K8;

   3. В поле Номер_столбца ввести число, соответствующее номеру столбца таблицы-справочника, содержащей искомую информацию – 2;

   4. В поле Интервальный_просмотр для точного поиска информации вводится 0.

5. ОК.

Для копирования функции =ВПР(C6;$J$6:$K$8;2;0) по столбцу, необходимо диапазону таблицы-справочника назначить абсолютную адресацию.

Рис. 19. Пример ввода функции =ВПР(…).

Уровень преступности (число преступлений на 1000 населения) определяется по формуле: =D6/F6 (рис. 20).

Рис. 20. Пример расчета районного уровня преступности

Пример 11. Для получения качественной оценки уровня преступности (выше среднего – ниже среднего) используется логическая функция =ЕСЛИ(…). Для этого рассчитывается средний за период уровень преступности: в ячейку G24 вводится функция =СРЗНАЧ(G6:G23). Далее анализируется каждое текущее значение районного уровня преступности с помощью логической функции (рис. 21):

1. Курсор поместить в ячейку результата Н6;

2. Загрузить окно мастера функций;

3. Категория – Логические;

4. Ввести параметры функции =ЕСЛИ(…):

   1. В поле Лог_выражение: G6<$G$24;

   2. В поле Значение_если_истина: ниже среднего;

   3. В поле Значение_если_ложь: выше среднего;

5. ОК

Рис. 21. Пример применения логической функции

В результате получим таблицу (рис. 22):

Рис. 22. Пример получения качественной оценки уровня преступности.

Для поиска и осуществления выборки информации из базы данных используют Автофильтр и Расширенный фильтр. Рассмотрим эти возможности Excel на примерах.

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

Порядок действий следующий:

1. Выделить таблицу «с шапкой», но без итоговых строк;

2. Команда: ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР;

3. Раскрыть список оценки уровня преступности (рис. 23)

Рис. 23. Автофильтр. Выбор в качестве критерия оценки уровня