ОС-ч2 ЛАБ РАБ (1085723), страница 2

Просмтор этого файла доступен только зарегистрированным пользователям. Но у нас супер быстрая регистрация: достаточно только электронной почты!

Текст из файла (страница 2)

Фильтрация списков

По созданной базе данных на листе «Командировки» требуется составить отчет о тех сотрудниках организации, которые направлялись в командировку на предприятие «Темп»,

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

1. Создадим копию листа «Командировки».

2. Переименуем его в «Фильтр».

3. Выделим ячейку ВЗ. Если выделить любую ячейку списка, то Excel автоматически обнаружит список на вашем рабочем листе.

4. Выполним команду Данные->Фильтр-> Автофильтр. В заголовках каждого столбца данных появятся кнопки фильтра со стрелочками вниз.

5. Щелкнем на стрелке фильтра в ячейке С1. Это название организации. В появившемся окне списка критериев необходимо выбрать нужный критерий (ключ) фильтрации.

Критерии фильтрации находятся в списке в алфавитном порядке. Когда мы выберем критерий фильтрации «Темп», все записи, не удовлетворяющие этому критерию, становятся невидимыми. Результат фильтрации представлен на рис. 37.

6. Для вывода на экран снова полного списка, нужно щелкнуть на любой кнопке со стрелкой фильтра и в списке критериев фильтрации выделить строку Все. Или выполнить команду Данные ->Фильтр->Отобразить все.

Технология использования расширенного фильтра Excel

Для фильтрации данных по сложным критериям, а также для получения результата фильтрации в другом месте рабочего листа применяется команда из меню Данные->Фильтр-> Расширенный фильтр. Прежде чем выполнить вызов диалогового окна Расширенный фильтр, необходимо сформировать диапазон условий, который можно разместить в любом месте текущего рабочего листа. Диапазон условий должен включать заголовки граф исходных данных и хотя бы одну строку с критериями отбора записей данных. При этом число строк в области критериев определяется только количеством условий, однако включение пустых строк недопустимо, поскольку в этом случае будут найдены все записи. Результат фильтрации данных можно скопировать в другую область рабочего листа. С копией отфильтрованной таблицы можно выполнять новые действия, не испортив исходные данные.

Рассмотрим использование расширенного фильтра на нашем примере.

Создадим отдельную таблицу для сотрудников, уезжавших в командировки после 16 мая 2001 года, продолжительность которых составила больше 6 дней. В новую таблицу должна входить информация: фамилия сотрудника, название организации, куда он ездил

и количество дней командировки.

Продолжим работу на листе «Фильтр».

1. Подготовим диапазон условий. Для этого скопируем содержимое ячейки A1, т.е. название графы «Дата» в ячейку А10 и содержимое ячейки D1, т.е. название графы «Дни» в ячейку В10:

• установим курсор в ячейку А1 и выполним команду

Правка->-Копировать;

• установим курсор в ячейку А10 и выполним команду

Правка-> Вставить;

• установим курсор в ячейку D1 и выполним команду

Правка->Копировать;

• установим курсор в ячейку В10 и выполним команду

Правка-> Вставить.

2. В ячейки А11 и В11 введем критерии отбора:

• в ячейку А11 запишем >16.05.2001;

• в ячейку ВП запишем >6.

3. Подготовим область для вывода результатов фильтрации. Таблица будет содержать 3 графы. Скопируем в ячейку А13 название графы «Фамилия», в ячейку В13 - название графы «Организация», в ячейку СЗ - название графы «Дни».

4. Установим курсор мыши на исходную таблицу. Выполним команду

Данные->Фильтр->Расширенный фильтр. Появится диалоговое окно.

• установим опцию «скопировать результат в другое место»;

• в строку Исходный диапазон введем диапазон таблицы $A$1:$G$8;

• в строку Диапазон условий введем критерии отбора $А$10:$В$11;

• в строку Поместить результат в диапазон введем область отбора данных $А$13:$С$17. Окно Расширенный фильтр после ввода данных будет выглядеть следующим образом:

Результат работы расширенного фильтра должен совпадать с данными на рис. 39.

Группировка данных для формирования итогов

Сортировка данных.

При работе со списками часто возникает необходимость сортировки строк списка в заданном порядке. Это можно выполнить с помощью команды меню

Данные->Сортировка, предварительно выделив весь список с заголовками столбцов, кроме итоговых строк. Команда Сортировка открывает одноименное диалоговое окно, в котором можно задать до 3 ключей с указанием порядка сортировки. Сортировка выполняется сначала по первому ключу, затем в строках с совпадающим значением первого ключа - по второму ключу, наконец, в строках с одинаковыми значениями первого и второго ключа - по третьему ключу.

Рассмотрим использование сортировки на нашем примере.

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

1. Создадим копию листа «Командировки».

2. Переименуем его в «Итоги».

3. Выделим весь список, т.е. диапазон ячеек A1:G8.

4. Выполним команду Данные->Сортировка. В появившемся диалоговом окне в строке Сортировать по укажем первый ключ -Организация, во второй строке окна Затем по укажем второй ключ - Фамилия. В результате сортировки данные отсортируются сначала по названию организаций, а затем внутри организаций фамилии отсортируются по алфавиту. Окно Сортировка диапазона выглядит так:

5. Нажмем ОК.

Список данных после сортировки представлен на рис. 41.

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

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

После того как список отсортирован, можно применить команду из меню

Данные->Итоги для создания промежуточных и общих итогов в списке. По этой команде открывается диалоговое окно Промежуточные итоги. В нем задаются поле, при каждом изменении значения которого будут вычисляться итоговые значения, и операция, которая будет применяться к значениям полей, отмеченных в списке Добавить итоги по. Подведение итогов может осуществляться по нескольким показателям: определению количества элементов списка, суммированию величин, нахождению максимального, минимального или среднего значения, а также использовании более сложных статистических функций.

Рассмотрим использование команды Итоги на нашем примере.

Подсчитаем сумму расходов, затраченных на командировки в отдельные организации, и общее количество дней командировок в эти организации. Например, каковы общие расходы на все командировки в организацию «Темп», в организацию «Сатурн» и т.д.

Продолжаем работать на листе «Итоги».

1. Установим курсор на любую ячейку отсортированной таблицы, таблица была отсортирована в предыдущем пункте.

Выполним команду Данные->Итоги. В появившемся диалоговом окне Промежуточные итоги в строке При каждом изменении в выберем из открывшегося списка название графы «Организация», в строке Операция - функцию Сумма, в строке Добавить итоги по -выберем название графы «Сумма», а также для подсчета общего количества дней командировок графу «Дни». Активизируем опцию Итоги под данным. Внешний вид окна Промежуточные итоги представлен на рис. 42. Нажмем клавишу ОК.

3. Полученные результаты должны совпадать с таблицей на рис. 43.

При составлении отчета, как правило, требуются не все данные рабочего листа, а только результаты промежуточных итогов. В процессе подведения промежуточных итогов Excel автоматически структурирует рабочий лист. При этом взаимосвязанные данные группируются вместе, как это видно на рис. 44.

Вертикальные линии слева на полях рабочего листа заканчиваются вверху рядом кнопок, называемых кнопками уровней. Эти кнопки позволяют контролировать количество уровней деталей, попадающих в отчет. Сейчас на нашем рабочем листе «Итоги» имеются три структурных уровня. Первый уровень состоит только из одной позиции, определяющей общие суммы затрат на все командировки. Уровень 2 разбивает все организации на четыре группы по названиям организаций и подсчитывает сумму затрат на командировки по каждой из них. Уровень три показывает все элементы базы данных.

Детали структурированных листов можно скрывать, щелкая на пронумерованных кнопках уровней структуры. Чтобы увидеть только общий итог по всем данным, следует щелкнуть на кнопке первого уровня, а для получения детальной картины всех элементов нужно воспользоваться кнопкой третьего уровня. Знак «+» работает как кнопка отображения деталей, знак «-» работает как кнопка скрытия.

4. Щелкнем на кнопке структуры второго уровня. На рабочем листе отображаются только суммарные значения количества дней и затрат на командировки по названиям организаций. Результаты представлены на рис. 44.

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

Данные->Группа и структура->Создание структуры, не убирая при этом

подведенные промежуточные итоги. После выполнения этой команды на рабочем листе «Итоги» появятся еще два горизонтальных уровня. Результат выполнения команды представлен на рис. 45.

6. С помощью кнопок управления структурой вы можете изменять объем отображаемой информации. Например, щелкнув по кнопке первого уровня, структуры горизонтальной ориентации, вы получите результат (см. рис. 46).

7. Для удаления структуры и итоговых значений с рабочего листа в случае неправильного получения результатов нужно выполнить команду Данные-»Итоги. В появившемся окне щелкнуть по кнопке Убрать все.

Технология создания сводных таблиц

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

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

Характеристики

Список файлов книги