Применение Расширенного фильтра
С помощью Расширенного фильтра осуществляется сложный многоуровневый поиск и отбор записей по критериям, которые задаются непосредственно в клетках рабочего листа.
Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки. На листе также должно быть не менее трех пустых строк сверху от списка. Эти строки будут использованы в качестве диапазона условий отбора.
Для создания Расширенного фильтра необходимо выполнить:
1 шаг Добавить три строки над списком.
2 шаг Выделить заголовки фильтруемых столбцов списка → «Правка» → «Копировать» → курсор установить на первую пустую строку → «Правка» → «Вставить».
3 шаг Ввести в строку под заголовками столбцов требуемые критерии отбора. Убедитесь, что между значениями условий и списком находится как минимум одна пустая строка.
4 шаг Курсор установить на ячейку в списке.
5 шаг «Данные» → «Фильтр» → «Расширенный фильтр».
6 шаг Для отображения результата фильтрации установить переключатель «Обработка» с признаком «Фильтровать список на месте».
7 шаг Для отображения результата фильтрации в другую область листа установить переключатель «Обработка» с признаком «Скопировать результаты в другое место» и в поле «Поместить результат в диапазон» указать левую верхнюю ячейку области вставки
8 шаг В поле «Диапазон условий» установите ссылку на диапазон условий отбора, включающий заголовки столбцов.
ê Отобразите записи кадрового состава: сотрудники, имеющие высшее образование и общий стаж работы менее 25 лет.
| Ü Вставьте три дополнительные строки перед строкой заголовков столбцов списка.
Ü Скопируйте строку заголовков столбцов в первую вставленную строку.
Ü Во вторую вставленную строку введите условия отбора: под заголовком столбца «Образование» - Высш., а под заголовком столбца «Общий стаж» - <25.
После выполненных действий таблица должна принять вид:
Ü Курсор установите на любую ячейку заголовка столбцов списка.
Ü «Данные» → «Фильтр» → «Расширенный фильтр».
Ü Введите значение поле: в поле «Исходный диапазон» установите A6: I16 → в поле «Диапазон условий» установите A3:I4 → в поле «Обработка» установите признак «Фильтровать список на месте».
Ü Нажмите «OK».
После выполненных действий таблица должна принять вид:
Ü «Данные» → «Фильтр» → «Отобразить все».
O Отобразите записи кадрового состава: сотрудники, пришедшие на работу после 1994 года и имеющие стаж по специальности не менее 15 лет.
|
O Отобразите записи кадрового состава: сотрудники, не моложе 30 лет и имеющие высшее образование.
|
Задания для самостоятельной работы:
1. Создайте электронную таблицу вида:
2. Произведите анализ данных:
· Какие типы самолетов летают по маршруту «Москва - Пафос» компании «AEROFLOT»?
· Выполняет ли самолет типа «Боинг» рейс по маршруту «Москва - Ларнака»?
· Выполняет ли самолет компании «ANA» рейс по маршруту «Пафос - Москва» после 15:00?
Лабораторная работа № 8 ОБРАБОТКА ДАННЫХ
В программу Excel встроен аппарат быстрого и удобного извлечения и форматирования различных обобщенных сводок. Эти операции производятся с помощью команд Итоги, Консолидация, Сводная таблица.
8.1. Работа с листами в электронных таблицах
Задание №1. Работа с листами
Основными командами работы с листами являются:
· Удалить лист;
· Добавить лист;
· Переименовать лист;
· Переместить лист.
Для выполнения команды работы с листом необходимо выполнить следующие шаги:
1 шаг Курсор подвести на ярлычок листа → нажать правую кнопку мыши.
2 шаг Выбрать необходимую команду.
Рассмотрим основные команды работы с листами на примере задачи «Расчет с клиентами в отеле» (см. лабораторную работу № 6, стр. 60).
O Загрузите файл электронной таблицы «Расчет с клиентами отеля».
|
Рассмотренная задача учитывала расчет клиентов, проживающих на первом этаже.
Рассмотрим, как произвести расчет клиентов, проживающих на трех этажах.
ê Переименуйте «Лист1» на «Этаж1»:
| Ü Курсор подведите к ярлыку «Лист1» и нажмите правую кнопку мыши:
Ü Выберите параметр «Переименовать».
Ü Введите «Этаж1».
O Переименуйте «Лист2» на «Этаж2»
|
O Переименуйте «Лист3» на «Этаж3»
|
O Переименуйте «Лист3» на «Итог»
|
Исходные и расчетные данные для расчета клиентов на втором этаже аналогичны данным клиентов на первом этаже.
Ü Перейдите на лист «Этаж1».
Ü Выделите содержание всего листа.
Ü «Правка» → «Копировать».
Ü Перейдите на лист «Этаж2».
Ü Курсор установите на ячейку A1
Ü «Правка» → «Вставить»
O Измените значения данных: ФИО клиентов, дни проживания, наличие брони на листе «Этаж2».
|
O Скопируйте значения листа «Этаж2» на лист «Этаж3».
|
O Измените значения данных: ФИО клиентов, дни проживания, наличие брони на листе «Этаж3».
|
Консолидация данных
Задание №2. Консолидация данных
Команда Консолидация позволяет объединить таблицы, находящиеся в разных местах и даже в разных книгах. Все таблицы должны иметь абсолютно идентичную структуру. Итоговая таблица будет содержать только одну строку с ключевым полем, а числовые данные в ней будут суммами (или другими функциями) всех строк объединенного подмножества. Ключевым полем считается самое левое поле (колонка) таблицы.
Для выполнения команды консолидации необходимы следующие шаги:
1 шаг Курсор установить на свободное место таблицы (справа и снизу должно быть свободно) или на новый лист.
2 шаг «Данные» → «Консолидация»:
3 шаг В поле «Функция» установить вид операции (чаще всего Сумма).
4 шаг В поле «Ссылка» ввести диапазон ячеек, для которых будет производиться анализ.
5 шаг Нажать кнопку «Добавить».
6 шаг По необходимости вернуться к 4 и 5 шагам.
7 шаг Установить или сбросить признаки в поле «Использовать в качестве имен» - если установлены, то в консолидирующей таблице подписи верхней строки и левого столбца будут применены в качестве имен.
8 шаг Установить или сбросить признак «Создавать связи с исходными данными» - установка признака предназначена для обновления данных в консолидирующей таблице при изменении исходных данных в составляющих таблицах.
Команду Консолидация разберем на примере создания итоговых (консолидирующих) данных о клиентах, проживающих на трех этажах.
ê Создайте консолидирующую (объединенную) таблицу краткого анализа о клиентах, проживающих на трех этажах:
| Ü Скопируйте строку 23листа «Этаж1» на строку 1 листа «Итог».
Ü Скопируйте блок ячеек В25:Е31 листа «Этаж1» в блок ячеек В5:Е11 листа «Итог».
Ü Скопируйте блок ячеек Н25:J35 листа «Этаж1» в блок ячеек J5:L15 листа «Итог».
Ü Курсор установите на ячейку Н5
Ü «Данные» → «Консолидация».
Ü В поле «Функция» установите «Сумма».
Ü В поле «Ссылка» нажмите на пиктограмму установки диапазона ячеек:
Ü Перейдите на лист «Этаж1».
Ü Курсором установите диапазон ячеек J25:J31.
Ü Нажмите пиктограмму конца определения диапазона:
Ü Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»).
Ü Перейдите на лист «Этаж2».
Ü Курсором установите диапазон ячеек J25:J31.
Ü Нажмите пиктограмму конца определения диапазона
Ü Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»)
Ü Перейдите на лист «Этаж3».
Ü Курсором установите диапазон ячеек J25:J31.
Ü Нажмите пиктограмму конца определения диапазона
Ü Нажмите кнопку «Добавить» (диапазон установится в поле «Список диапазонов»)
Ü Перейдите на лист «Итог».
Ü В поле «Использовать в качестве имен» сбросьте признаки «Подписи верхней строки» и «Значение левого столбца».
Ü Нажмите кнопку «ОК».
После выполненных действий будет создана объединенная таблица вида:
O Произведите консолидацию по итоговым суммам.
|
Создание итоговых таблиц
Задание №3. Создание промежуточных итогов
Использование команды Итоги позволяет выполнить детальный анализ сводных показателей для группы данных, объединенных каким-либо общим признаком. Чтобы форматирование итогов имело смысл, необходимо предварительно отсортировать по желаемому признаку.
Разберем создание промежуточных итогов для задачи «Учет реализации путевок».
O Создайте электронную таблицу «Учет реализации путевок» вида:
|
Для создания промежуточных итогов необходимо выполнить следующие шаги:
1 шаг Произвести сортировку по тому показателю, по которому будут создаваться Итоги.
2 шаг Курсор установить на любое место таблицы.
3 шаг «Данные» → «Итоги».
4 шаг Установите значения полей и признаков:
· В поле «При каждом изменении в» ввести имя поля, по которому будут создаваться промежуточные итоги;
· В поле «Операция» ввести вид итоговой операции:
Сумма - сумма элементов; используется автоматически для подведения итогов по числовым полям;
Кол-во значений - число строк данных; используется по умолчанию для подведения итогов по числовым данным;
Среднее - среднее арифметическое элементов;
Максимум - максимальное значение в группе;
Минимум - минимальное значение в группе;
Произведение - произведение всех значений в группе;
Кол-во чисел - количество строк, содержащих числовые данные;
Смещенное/несмещенное отклонение - смещенная/несмещенная оценка среднего квадратического отклонения генеральной совокупности по выборке данных;
· В поле «Добавить итоги по» ввести столбец, в котором должны появиться итоги, можно указывать более одного столбца;
· Признак «Заменить текущие итоги» - если установлен, то полученные ранее итоги заменяются новыми;
· Признак «Конец страницы между группами» - если установлен, то автоматически вставляется конец страницы перед каждой новой группой данных;
· Признак «Итоги под данными» - если установлен, то итоговые данные размещаются под данными, иначе над данными.
· Признак «Убрать все» - убирает все итоги из таблицы.
5 шаг Нажать кнопку «ОК».
ê Создайте итоги «Объем продаж по каждому филиалу»:
| Ü Курсор установите на ячейку списка.
Ü Произведите сортировку по Наименованию филиала.
Ü «Данные» → «Итоги».
Ü Установите параметры создания итогов:
· В поле «При каждом изменении в» введите: Наименование филиала;
· В поле «Операция» установите «Сумма»;
· В поле «Добавить итоги по» курсором установите «Количество»;
· Установите признак «Заменить текущие итоги»;
· Установите признак «Итоги под данными».
После выполнения всех действий таблица должна принять вид, представленный на рис. 8.1.
Дальнейшую возможность обобщения предоставляет появившаяся слева «карта» структуры данных с кнопками +и - .
ê Отобразите только итоговые данные «Общее Количество по Москве»
| Ü Курсором щелкните по – в структуре таблицы для группы Ногинск.
Ü Курсором щелкните по - в структуре таблицы для группы Пушкино.
Ü Курсором щелкните по - в структуре таблицы для группы Серпухов.
Рис.8.1. Вид таблицы после применения команды Итоги
После выполненных действий таблица должна принять вид:
ê Уберите итоги: «Общее количество по Москве»
| Ü Курсор установите на ячейку списка.
Ü «Данные» → «Итоги».
Ü Установите признак «Убрать итоги».
ê Создайте итоги: «Количество наименований туров в каждом филиале»
| Ü Курсор установите на ячейку списка.
Ü Произведите сортировку по Наименованию филиала.
Ü «Данные» → «Итоги».
Ü Установите параметры создания итогов:
· В поле «При каждом изменении в» введите: Наименование филиала;
· В поле «Операция» установите «Количество значений»;
· В поле «Добавить итоги по» курсором установите «Наименование тура»;
· Установите признак «Заменить текущие итоги»;
· Установите признак «Итоги под данными».
После выполнения всех действий таблица должна принять вид:
O Создайте итоги: «Сумма продаж по каждому туру»
|
O Создайте итоги: «Максимально продаваемый тур в каждом филиале»
|
Создание сводной таблицы
Задание №4. Создание сводной таблицы
Инструмент Сводная таблица является удобным средством анализа и трансформации данных. С помощью создания Сводной таблицы можно сделать практически любой «разрез» таблицы, получить итоги по любым данным. Сводная таблица во многом объединяет возможности других обобщенных инструментов, таких как Итоги, Фильтр, Консолидация.
С помощью Сводной таблицы можно произвольным способом изменить структуру исходных данных, располагая заголовки строки столбцов удобным образом, а также получить все необходимые промежуточные итоги.
Для формирования Сводной таблицы программа Excel предлагает использовать Мастер сводной таблицы.
Для создания сводной таблицы необходимо выполнить следующие шаги:
1 шаг «Данные» → «Сводная таблица»
2 шаг Установить признаки «в списке или в базе данных Microsort Excel» и «сводная таблица»
3 шаг Нажать кнопку «Далее»:
4 шаг В поле «Диапазон» установить диапазон ячеек, где располагаются исходные данные.
5 шаг Нажать кнопку «Далее»:
6 шаг Нажать кнопку «Макет»:
7 шаг Перетащить кнопки полей с помощью мыши в строки или столбцы таблицы, где они будут располагаться в сводной таблице.
8 шаг Нажать «OK».
9 шаг Установить признак «Поместить на новый лист» и нажать «Готово».
Задания для самостоятельной работы:
1. Создайте электронную таблицу «Учет платежей» вида:
Кате-
гория
| ФИО
| Органи-
зация
| Регион
| Вид платежа
| Количество путевок
| Сумма
(руб.)
| ФЛ
| Хохлов В. И.
|
| Москва
| Наличный
|
|
| ФЛ
|
| Магазин
№2
| МО
| Банк
|
|
| ЮЛ
|
| МКАМС
| МО
| Банк
|
|
| ФЛ
| Уварова В.И.
|
| Москва
| Наличный
|
|
| ЮЛ
|
| МКАМС
| МО
| Банк
|
|
| ФЛ
| Хохлов В.И.
|
| Москва
| Нал
|
|
|
2. Произведите обработку данных, используя инструмент обобщения данных:
· подведите итоги по наличному и безналичному расчету;
· подведите итоги:
- по каждой категории определите сумму платежей, число, платежей, максимальное значение платежа;
- по каждому региону (Москва, МО) определите сумму платежей.
3. Постройте сводную таблицу и сделайте анализ данных:
· отобразите платежи только по Москве;
· определите сумму платежей по регионам;
· определите максимальные выплаты по месяцам.
Не нашли, что искали? Воспользуйтесь поиском по сайту:
©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.
|