Практическая работа №6 (для специальности 09.02.03)

Практическая работа № 9 (для специальности 09.02.01)

Тема: Сортировка записей таблицы. Фильтрация. Создание сводных таблиц

Цель: формирование умений сортировки, фильтрации данных и создания сводных таблиц в Excel.

Время выполнения: 2 часа.

Теоретический материал

1. Сортировка записей

С помощью команды Данные / Сортировка можно упорядочить строки в соответствии с содержимым определенных столбцов.

Чтобы отсортировать данные целиком, нужно лишь выделить одну ячейку из списка и выбрать команду Данные / Сортировка . Microsoft Excel автоматически выберет весь список для сортировки. Microsoft Excel определяет расположение меток столбцов, даже если они занимают две строки, и исключает их из сортировки. Можно использовать метки столбцов для того, чтобы указать столбцы, по которым нужно отсортировать список. Microsoft Excel позволяет также сортировать только выделенные строки или столбцы, или данные только в одной строке или столбце.

После задания данных для сортировки и выбора команды на экране появиться окно диалога (рис. 1), в котором следует задать следующие поля:

Рисунок 1 - Окно Сортировка

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

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

Строка Меток Столбцов - Определяет, имеется ли в сортируемой базе строка заголовков, которую нужно исключить из сортировки. Если база содержит метки столбцов, то выберите переключатель Есть, а если меток нет, то выберите переключатель Нет.

Рисунок 2 - Окно Параметры сортировки

Параметры - Выводит окно диалога (рис. 2) Параметры Сортировки, в котором Вы можете:

-            Определить пользовательский порядок сортировки для столбца, указанного в окне Сортировать.

-            Определить сортировку с учетом регистра.

-            Изменить ориентацию сортировки, то есть, вместо сортировки сверху вниз установить сортировку слева направо.

После установки всех необходимых полей и параметров следует нажать на кнопку ОК.

Microsoft Excel использует следующие основные принципы при сортировке:

-            если сортировка ведется по одному столбцу, то строки с одинаковыми значениями в этом столбце сохраняют прежнее упорядочивание;

-            строки с пустыми ячейками в столбце, по которому ведется сортировка, располагаются в конце сортируемого списка;

-            параметрами сортировки являются выбранный столбец, порядок (возрастающий или убывающий) и направление сортировки (сверху вниз или слева направо);

-            результаты сохраняются от предыдущего применения сортировки до тех пор, пока их не изменили;

-            если сортировка ведется по нескольким столбцам, строки с одинаковыми  значениями в первом ключевом столбце сортируются в порядке, определяемом вторым ключевым столбцом, строки с одинаковыми значениями в первых двух ключевых столбцах сортируются по третьему столбцу;

-            если результат сортировки не тот, которого ожидали, обратите внимание на тип сортируемых данных. Убедитесь, что все числа введены как цифровые данные или как текст.

2. Фильтрация или выборка данных

С помощью команды Данные / Фильтр можно отыскать и использовать нужное подмножество данных. В отфильтрованном списке выводятся на экран только те строки, которые содержат определенные значения или отвечают некоторым критериям выборки. При этом прочие строки будут временно скрыты. Подменю Фильтр в Microsoft Excel предоставляет возможность использовать как команду Автофильтр, так и команду Усиленный Фильтр если нужно использовать сложный критерий для выборки данных.

2.1 Применение Автофильтра

Команда Автофильтр помещает кнопки раскрывающихся списков (кнопки со стрелкой) непосредственно в строку с метками столбцов, с их помощью можно выбрать данные, которые следует вывести на экран.

Можно также применять пользовательские критерии сравнения для фильтрации данных. Для этого в списке следует выбрать пункт Условие. На экран выведется окно диалога Пользовательский Автофильтр (рис. 3).

Рисунок 3 - Окно Пользовательский автофильтр

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

Для определения двух критериев выберите один из следующих вариантов:

-            Нажмите переключатель "И" для объединения двух критериев в Вашем фильтре. В этом случае выберутся данные, для которых выполняются сразу оба условия.

-            Нажмите переключатель "Или" для использования двух различных критериев в Вашем фильтре. В этом случае выберутся данные, для которых выполняются хотя бы одно из условий.

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

Команда расширенного фильтра (рис. 4) позволяет отыскивать строки с помощью более сложных критериев.

Рисунок 4 - Окно  Расширенный фильтр

При выборе этой команды в появившемся окне диалога следует установить следующие параметры:

Обработка - Переключатель Фильтровать Список На Месте скрывает строки, которые не удовлетворяют указанному критерию. Переключатель Скопировать результат в Другое Место копирует отфильтрованные данные на другой рабочий лист или на другое место на том же рабочем листе.

Исходный диапазон - Определяет интервал, содержащий данные, подлежащий фильтрации.

Диапазон критериев - Определяет интервал ячеек на рабочем листе, который содержит нужные критерии. Microsoft Excel выводит ссылку на этот  интервал.

Поместить результат в диапазон - определяет интервал ячеек, в который копируются строки, удовлетворяющие определенным критериям. Это поле активно только в том случае, если выбран переключатель Скопировать результат в Другое Место.

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

2.3 Работа с отфильтрованными данными

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

3. Понятие о сводных таблицах

Для всестороннего и эффективного анализа данных больших таблиц в Excel используются так называемые сводные таблицы (СТ). Функции работы с СТ относятся к одному из самых мощных инструментов Excel. Главные достоинства СТ - это представление больших объемов информации в концентрированном и удобном для анализа виде, широкие возможности  для группировки данных, а так же возможность получения промежуточных и общих итогов, которые помещаются в таблицу автоматически.

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

СТ являются “трехмерными”, т.к. к привычным атрибутам таблиц: строка и столбец,  добавляется атрибут - страница  (на основе исходной таблицы с данными по 12 месяцам, можно создать СТ, содержащую, например, 13 страниц; “открывая”, специальным образом, любую из 12 страниц, можно просмотреть данные какого-то одного месяца, а на 13 странице - суммарные данные за все месяцы сразу).

3.1 Мастер сводных таблиц

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

3.1.1 На первом шаге (рис. 5) предлагается выбрать источник данных для построения таблицы. Создать СТ можно на основе данных, находящихся в одном из нижеследующих источников.

Рисунок 5 - Мастер сводных таблиц шаг1.

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

Во внешнем источнике данных, которым может быть база данных, текстовый файл или любой другой источник, кроме книги Microsoft Excel.

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

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

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

3.1.2 На втором шаге в диалоговом окне (рис. 6) указывается интервал клеток списка или базы данных, которые должны быть сведены.

Рисунок 6 - Мастер сводных таблиц шаг 2

В общем случае полное имя интервала задается  в виде:

[имя_ книги]имя_листа!интервал ;

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

3.1.3 На третьем шаге (рис. 7) в макете таблицы в создается структура СТ и определяются ее функции.

Макет представлен в центре окна и состоит из областей: строка, столбец, страница и данные. Справа от макета отображаются все имена полей (заголовки столбцов) в заданном интервале исходной таблицы.

Рисунок 7 - Мастер сводных таблиц шаг 3

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

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

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

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

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

Страница - поля в этой области выступают в качестве фильтров и позволяют просматривать “на отдельных страницах” данные СТ, соответствующие разным значениям поля, помещенного в эту область; поле страницы в режиме просмотра располагаются на две строки выше основной части СТ в крайнем левом столбце. В соседней справа клетке выводится (Все) и рядом раскрывающая стрелка для выбора другого элемента поля; в области страница может быть размещено несколько полей, между которыми устанавливается иерархическая связь - сверху вниз;

Данные - обязательно определяемая область для размещения полей, по которым подводятся итоги, согласно выбранной итоговой функции; размещаемые здесь поля могут быть произвольных типов.

3.2 Редактирование сводной таблицы

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

Совокупность всех способов редактирования в разных режимах сводится к следующим действиям:

-            перетаскивание названия поля в соответствующую область;

-            вызов команды Данные главного меню;

-            обращение к контекстно-зависимому меню  поля;

-            двойной щелчок на имени поля;

-            щелчок на нужной кнопке панели инструментов "Запрос и сведение".

Задания:

Упражнение № 1

1. Заполнить таблицу анализа продаж (табл.1), произвести расчеты, выделить минимальную и максимальную продажи (количество и сумму); произвести фильтрацию по цене, превышающей 9000 р., построить гистограмму отфильтрованных значений изменения выручки по видам продукций.

Формулы для расчета:

Всего=Безналичные платежи + Наличные платежи

Выручка от продаж = Цена * Всего

Таблица 1

Анализ продаж продукции фирмы «Интертрейд» за текущий месяц

Упражнение №2

1.      Создайте таблицу расчета заработной платы. Введите исходные данные (табл.2).

Таблица 2

Ведомость начисления заработной платы за октябрь месяц 2004 г.

2.        Произведите расчеты во всех столбцах таблицы по формулам:

Премия=Оклад * % Премия

Всего начислено = Оклад + Премия

Удержание = Всего начислено * % Удержания

К выдаче = Всего начислено – Удержания

3.        Рассчитайте итоги по столбцам, а также максимальный, и минимальный средний доходы по данным колонки «К выдаче».

4.        Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь».

5.        Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать)

6.        Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы измените значение Премии на 32%.

7.        Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» и рассчитайте значение доплаты по формуле – Доплата = Оклад * % Доплаты. Значение доплаты примите равным 5%.

8.        Измените формулу для расчета значений колонки «всего начислено»:

 Всего начислено = Оклад + Премия + Доплата.

9.        Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта, меньше 7000 – красным, больше или равно – 10000 – синим цветом (Формат/Условное форматирование).

10.   Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (Данные / Сортировка ).

11.   Поставьте к ячейке Премия комментарии «Премия пропорциональна окладу» (Вставка / Примечание).

12.   Защитите лист «Зарплата ноябрь» от изменений (Сервис / Защита / Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля.

13.   Скопируйте содержимое листа «Зарплата ноябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист/Создание копии).

14.   Присвойте скопированному листу название «Зарплата декабрь». Исправьте название месяца в ведомости на декабрь.

15.   Измените значение премии на 46%, Доплаты – на 8%.

16. Скопируйте содержимое листа «Зарплата октябрь» на новый лист электронной книги (Правка/Переместить/Скопировать лист/Создание копии).

17. Присвойте скопированному листу название «Итоги за квартал». Измените название таблицы на «Ведомость начисления заработной платы за 4 квартал».

18. Удалите в этой таблице колонки Оклада и Премии, а также строку с численными значениями % Премии и % Удержания. Удалите также строку «Всего» и строки с расчетом максимального, минимального и среднего доходов под основной таблицей.

19. Вставьте новый столбец «Подразделение» (Вставка/Столбец). Заполните столбец согласно образцу (табл.3).

Таблица 3

Вставка нового столбца в таблицу

20.   Произведите расчет квартальных начислений, удержаний и суммы к выдаче как сумму начислений за каждый месяц. Формула для расчета квартальных начислений «Всего начислено» имеет вид:  =октябрь!Е6+ноябрь!Е6+декабрь!Е6. Аналогично произведите квартальный расчет «Удержания» и «К выдаче».

21.   Для расчета промежуточных итогов проведите сортировку по подразделениям, а внутри подразделений – по фамилиям.

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

При каждом изменении в – Подразделение;

Операция – Сумма;

Добавить итоги по: Всего начислено, Удержания, К выдаче.

Отметьте галочкой операции «Заменить текущие итоги» и «Итоги под данными».

23.   Изучите полученную структуру и формулы подведения промежуточных итогов, устанавливая курсор на разные ячейки таблицы. Научитесь сворачивать и разворачивать структуру до разных уровней (кнопками «+» и «-»).

Упражнение № 3. Задача на оптимизацию (Поиск решения)

Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р., В — 15 р. и С — 20 р. на единицу изделия.

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

Таблица 4

План выгодного производства

Создайте расчетную таблицу. Введите исходные данные и формулы в электронную таблицу. Расчетные формулы имеют такой вид:

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 1) * (норма расхода сырья В) + (количество сырья 1) * (норма расхода сырья С).

Значит, в ячейку F5 нужно ввести формулу = В5 * $В$9 + С5 * $С$9 + D5 * $D$9.

В ячейку F6 нужно поместить формулу = В6 * $В$9 + С6 * $С$9 + D6 * $D$9,

в ячейку F7 нужно поместить формулу = В7 * $В$9 + С7 * $С$9 + D7 * $D$9

Обратите внимание, что значения количества сырья каждого вида пока не известны и будут подобраны в процессе решения  задания (ячейки B9:D9 пока пустые).

(Общая прибыль по А) = (прибыль на ед. изделий А) * (количе­ство А), следовательно в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку Е10 следует ввести формулу = СУММ (В10:В10).

2. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска, как указано на рис.8.

Рисунок 8 - Окно Поиск решения

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (Е10), в качестве изменяемых ячеек — ячейки количества сырья — (B9:D9).

Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А, В, С > = 0.

3. Установите параметры поиска решения (рис.45). Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска решения, установите параметры по образцу, задайте линейную модель расчета (Линейность модели) (рис.9).

Рисунок 9 - Окно Параметры поиска решения

4.   Кнопкой Выполнить запустите Поиск решения. Если вы сделали все верно, то решение будет как на рис. 10.

Рисунок 10 - Результат решения задачи

5.   Сохраните созданный документ под именем «План производства».

Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг продукции С. Продукцию А производить не стоит. Полученная при­быль при этом составит 527,78 р.

Упражнение №4. Создание сводных таблиц

1. По исходной таблице “Учет количества деревьев” построить сводную таблицу (СТ), сгруппировав данные по породе, № делянки и возрасту. Подсчитать количество деревьев в указанных группах (табл.5).

Таблица 5

Учет количества деревьев

2. Выполните команду Данные / Сводная таблица

3. В макете  в область строка разместите два поля: порода дерева и N делянки, в область столбец - поле Возраст. В области данные: Сумма по полю Количество. Готовая СТ выводится на листе рабочей книги Excel в, где клетки, содержащие имена  полей, выделены темным цветом. На пересечении строк с номерами делянок  (которые повторяются для каждой породы деревьев) и столбцов с возрастом выводятся величины, обозначающие общее количество деревьев данного возраста и породы деревьев, произрастающих на данной делянке. По внешнему полю Порода выводятся промежуточные итоги (Дуб всего, Клен всего и т.д.) и общие итоги по строкам и столбцам.

4. В сводных таблицах можно вычислить и представить 11 различных итоговых функций: Сумма, Счет и другие. Кроме того, для каждого поля области данные можно задать одно из 9 дополнительных вычислений, которые задают способ представления.

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

Контрольные вопросы:

1.        Для чего нужна сортировка записей, и как ее осуществить?

2.        Как задать порядок вторичной сортировки?

3.        Что можно сделать с помощью команды автофильтр?

4.        Какие параметры нужно установить, чтобы данные выбирались при одновременном выполнении двух условий?

5.        Какие параметры нужно установить, чтобы данные выбирались при  выполнении хотя бы одного из двух условий?

6.        Что можно сделать с помощью команды расширенный фильтр?

7.        Из каких шагов состоит Мастер сводных таблиц?

8.        Как задать интервал клеток исходной таблицы для создания СТ?

9.        Какие области составляют макет таблицы?

Скачано с www.znanio.ru