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

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

Пример 10. Используя дополнительные сведения о численности населения в районе (рис. 19), рассчитать показатель районного уровня преступности «Количество преступлений, число случаев на 1 000 населения». Дополнительные сведения о численности населения в районе оформлены в виде таблицы-справочника в диапазоне ячеек 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. Пример ввода функции =ВПР(…)

Уровень преступности (число преступлений на 1 000 населения) определяется по формуле: =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.1, 24.2).

Рисунок 24.1. Окно задания второго критерия

Рисунок 24.2. Окно задания второго критерия

В результате получим одну запись (рис. 25).

Рисунок 25. Результаты автофильтрации по заданным двум критериям

Таким образом, наиболее криминогенная ситуация наблюдалась в Октябрьском районе в феврале.

Пример 13. С помощью расширенного фильтра сформировать отдельные таблицы (выходные документы) по каждому району, позволяющие определить месяцы наилучшей раскрываемости преступлений (Процент раскрываемости >90).

Этапы реализации задачи:

1. Сформировать таблицу критериев – ячейки J10:K11.

2. Выделить таблицу, включая «шапку», но без итоговых строк.

3. Команда: ДАННЫЕ – ФИЛЬТР – РАСШИРЕННЫЙ ФИЛЬТР, ввести параметры (рис. 26):

a. Обработка – Скопировать результат в другое место;

b. Исходный диапазон – $B$5:$H$24;

c. Диапазон условий – $J$10:$K$11;

d. Поместить результат в диапазон – $L$10 (указывается левая верхняя ячейка таблицы, содержащая в будущем выходную информацию).

4. ОК.

Рисунок 26. Ввод параметров расширенного фильтра

В результате получили, что для Краснополянского района наиболее благоприятная обстановка была в марте и июне (рис. 27).

Рисунок 27. Определение наиболее благоприятного периода для Краснополянского района

Пример 14. Определить первые пять наиболее опасных для рассматриваемых районов периодов по уровню преступности. Для этого используется автофильтр (рис. 28).

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

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

3. В списке критериев поля «Уровень преступности, число случаев на 1 000 населения» выбрать строку (Первые 10 …).

4. Ввести параметры поиска: Показать первые 5 наибольших элементов списка.

5. ОК.

Рисунок 28. Ввод параметров автофильтра при определении районов с наиболее высоким уровнем премтупности

Результат отображен на рис. 29.

Рисунок 29. Результат автофильтра при определении районов с наиболее высоким уровнем преступности

Таким образом, можно сделать вывод, что наиболее проблемным является Октябрьский район, в котором, несмотря на достаточно высокий процент раскрываемости преступлений (от 69 до 92 %), уровень преступности на протяжении всего первого полугодия самый высокий.