Сделай Сам Свою Работу на 5

Применение Расширенного фильтра





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

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

Для создания Расширенного фильтра необходимо выполнить:

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 Все материалы защищены законодательством РФ.