Использование логической функции и поиск данных
ОК.
Рис. 11. Параметры окна Промежуточные итоги.
В результате получим:
Рис. 12. Результаты окна Промежуточных итогов в развернутом виде.
Управляя кнопками окна, можно получить искомый результат (рис. 13):
Рис. 13. Определение средних значений показателей за каждый месяц первого полугодия.
Обязательным условием здесь является упорядочение данных по ключевому столбцу, то есть по месяцу. Если же подвести итоги надо по районам, то первой должна быть операция сортировки таблицы по столбцу Район.
Пример 9.Определить средний уровень преступность и процент раскрываемости за первое полугодие по каждому району. Для этого реализуем следующую последовательность действий:
1.Сортировать таблицу по столбцу Район:
a. Выделить таблицу с «шапкой»;
b. Команда: ДАННЫЕ – СОРТИРОВКА;
c. В появившемся окне выставить параметры сортировки (рис. 14);
d. ОК (рис.15);
2.Для подведения итогов выделить таблицу, включая «шапку»;
3.Команда: ДАННЫЕ – ИТОГИ (рис. 16);
4.Определить параметры подведения итогов:
e. При каждом изменении в: Район;
f. Операция: Среднее;
g. Добавить итоги по: Количество преступлений, ед.; Процент раскрываемости, %;
5.ОК.
Развернутый и краткий результата подведения итогов отображен на рис. 17 – 18.
Рис. 14. Ввод параметров сортировки.
Рис.15. Результаты сортировки.
Рис. 16. Параметры окна подведения Промежуточных итогов по полю Район.
Рис. 17. Развернутые результаты подведения Промежуточных итогов по полю Район.
Рис. 18. Краткие результаты подведения Промежуточных итогов по полю Район.
Анализируя результаты итоговой таблицы (рис.18), можно сделать вывод, что наиболее высокий уровень преступности в Краснополянском районе, но здесь наблюдается и наилучший процент раскрываемости преступлений. Данные Пырьевского района близки к средним по городу. В Октябрьском районе уровень преступности ниже, но и хуже раскрываемость преступлений.
Поиск информации в таблицах будет рассмотрен на примере использования функции ВПР(…) и фильтрации данных.
Пример 10.Используя дополнительные сведения о численности населения в районе (рис.19), рассчитать показатель районного уровня преступности «Количество преступлений, число случаев на 1000 населения». Дополнительные сведения о численности населения в районе оформлены в виде таблицы-справочника в диапазоне ячеек J6:K8.
Выбор численности населения из справочника с помощью функции ВПР(…) осуществляется следующим образом (рис. 19).
1. Курсор поместить в ячейку результата – F6;
2. Открыть окно мастера функций;
3. Выбрать категорию Ссылки и массивы;
4. Выбрать функцию ВПР(…), ввести параметры:
a. В поле Значение указать адрес ячейки, содержащей ключевое значение поиска - C6;
b. В поле Таблица указать диапазон ячеек, содержащих справочник - J6:K8;
c. В поле Номер_столбца ввести число, соответствующее номеру столбца таблицы-справочника, содержащей искомую информацию – 2;
d. В поле Интервальный_просмотр для точного поиска информациивводится 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. Ввести параметры функции=ЕСЛИ(…):
a. В поле Лог_выражение: G6<$G$24;
b. В поле Значение_если_истина: ниже среднего;
c. В поле Значение_если_ложь: выше среднего;
5. ОК
Рис. 21. Пример применения логической функции
В результате получим таблицу (рис. 22):
Рис. 22. Пример получения качественной оценки уровня преступности.
Для поиска и осуществления выборки информации из базы данных используют Автофильтр и Расширенный фильтр. Рассмотрим эти возможности Excel на примерах.
Пример 12.С помощью автофильтра выбрать из базы данных наиболее неблагополучные районы, у которых соответствующие оценки уровня преступности – Выше среднего, а процент раскрываемости ниже 80.
Порядок действий следующий:
1. Выделить таблицу «с шапкой», но без итоговых строк;
2. Команда: ДАННЫЕ – ФИЛЬТР – АВТОФИЛЬТР;
3. Раскрыть список оценки уровня преступности (рис. 23)
Рис. 23. Автофильтр. Выбор в качестве критерия оценки уровня
преступности – Выше среднего.
4. Для задания критерия процент раскрываемости менее 80-ти в списке критериев столбца Процент раскрываемостизадаем условие«меньше 80»(рис. 24).
Рис. 24. Окно задания второго критерия.
В результате получим одну запись (рис. 25).
Рис. 25. Результаты автофильтрации по заданным двум критериям.
Таким образом, наиболее криминогенная ситуация наблюдалась в Октябрьском районе в феврале.
Пример 13. С помощью расширенного фильтра сформировать отдельные таблицы (выходные документы) по каждому району, позволяющие определить месяцы наилучшей раскрываемости преступлений (Процент раскрываемости > 90%).
Этапы реализации задачи:
1. Сформировать таблицу критериев – ячейки J10:K11;
2. Выделить таблицу, включая «шапку», но без итоговых строк;
3. Команда: ДАННЫЕ – ФИЛЬТР – РАСШИРЕННЫЙ ФИЛЬТР;
4. Ввести параметры (рис. 26):
a. Обработка – Скопировать результат в другое место;
b. Исходный диапазон - $B$5:$H$24;
c. Диапазон условий - $J$10:$K$11;
d. Поместить результат в диапазон - $L$10 (указывается левая верхняя ячейка таблицы, содержащая в будущем выходную информацию);
5. ОК
Рис. 26. Ввод параметров расширенного фильтра.
В результате получили, что для Краснополянского района наиболее благоприятная обстановка была в марте и июне (рис. 27).
Рис. 27. Определение наиболее благоприятного периода