Файл: Практикум Для студентов вузов Кемерово 2013 4 удк 004 (076) ббк 32. 81я7 И74.pdf

3. Электронные таблицы Excel
151
Основными видами анализа данных являются:

сортировка – перестановка записей в порядке возраста- ния/убывания значений какого-то числового поля или в алфа- витном порядке значений какого-то текстового поля;

фильтрация – выбор из ТД записей, удовлетворяющих каким-то признакам (критериям отбора).
Задания для самостоятельной работы
1. Создать на листах ТД-1, ТД-2, …, ТД-6 6 одинаковых экземпляров ТД на основе таблицы расчета заработной платы, созданной в лабораторной работе № 4.
2. На листе ТД-1 провести анализ данных в ТД с исполь- зованием встроенных функций Excel: определить 3 характери- стики в соответствии с требованиями индивидуального варианта задания (табл. 3.9). Создать текстовые пояснения к ячейкам по образцу рис. 3.26.
3. На листе

3. Электронные таблицы Excel
152 3. Вставить на лист ТД-1 содержимое буфера обмена (при копировании и вставке всего листа на новый лист переносятся все форматы ячеек, чего не происходит при копировании и вставке отдельных диапазонов).
4. Вставленная таблица в диапазоне А4: L25 не удовле- творяет требованиям к ТД, т.к. снизу к записям примыкают ячейки, в которых вычисляется итоговая сумма. Поэтому выде- лить строку 26 щелчком по ее номеру и удалить ее командой
«Удалить» контекстного меню строки.
5. Выделить лист ТД-1, скопировать в буфер обмена и вставить содержимое буфера на вновь созданные листы ТД-2,
ТД-3, ТД-4, ТД-5, создав 5 одинаковых копий ТД.
Анализ данных с помощью встроенных функций Excel
Допустим, мы хотим определить, какова максимальная зарплата одного сотрудника в данном месяце, выданная на руки.
Это значит, что надо найти максимальное из значений в столбце
«Сумма к выдаче».
Последовательность действий:
6. Перейти на лист ТД-1. Активизировать какую-нибудь пустую ячейку, например, С28.
7. Ввести в ячейку формулу =МАКС(L5:L25). Функция
МАКС(ячейки) (категория «Статистические») определяет мак- симальное из значений в ячейках, указанных в качестве аргу- ментов (аргументами могут быть ссылки на отдельные ячейки или диапазоны, разделенные символом «точка с запятой»).
8. В ячейку А28 ввести пояснительный текст Макси-
мальная сумма на руки: . Назначить ячейкам А28:С28 стиль
«Студенческий», затем текст в ячейках А28:В28 выровнять по центру выделения. Для числа в ячейке С28 установить такой же формат, как и в ячейках столбца «Сумма к выдаче» (использо- вать кнопку «Главная» – [Формат по образцу]).
Результат показан на рис. 3.26.
Рис. 3.26. Использование функции МАКС(…)

3. Электронные таблицы Excel
153
Аналогичным образом действуют функции МИН(ячейки) и СРЗНАЧ(ячейки). Они определяют соответственно мини- мальное значение среди аргументов и среднее арифметическое из значений аргументов.
Сортировка записей и подведение итогов
Последовательность действий:
9. Перейти на лист ТД-2. Активизировать ТД щелчком по любой ее ячейке.
В разделе «Сортировка и фильтр» вкладки «Данные» на инструментальной ленте имеются кнопки [А

Я] и [Я

А].
Они позволяют быстро произвести сортировку ТД по возраста- нию или убыванию, соответственно, значений одного поля, ячейка которого выделена в таблице. В отличие от этого, кла- виша [Сортировка] открывает окно диалога (рис. 3.27), в кото- ром можно настроить команду сортировки по нескольким по- лям.
Рис. 3.27. Диалоговое окно «Сортировка»
10. Щелкнуть клавишу «Данные» – «Сортировка и фильтр» –
[Сортировка]. В окне диалога первоначально имеется одна строка выбора критерия сортировки.
Допустим, мы хотим иметь упорядоченную ведомость расчета, в которой записи о сотрудниках будут рассортированы по отделам, а внутри списка каждого отдела сотрудники будут перечислены в алфавитном порядке. Для этого необходимо:

3. Электронные таблицы Excel
154 11. В строке выбора критерия в окнах ввода установить:
«Столбец» – «Сортировать по» – Отдел (имя поля, по ко- торому идет сортировка);
«Сортировка» – Значения (сортировать по значениям ячеек; возможны также сортировки, например, по цвету ячейки или шрифта в ней);
«Порядок» – От А до Я, т.е. по возрастанию для чисел, или в обычном алфавитном порядке для текстов (обратный по- рядок обозначается От Я до А). Возможна также сортировка по порядку следования элементов любого стандартного списка, хранящегося в памяти компьютера (см. § 3.6), если поле содер- жит элементы такого списка.
12. Щелкнуть в окне клавишу [Добавить уровень] – появ- ляется следующая строка критериев, в которой надо устано- вить: «Затем по» – ФИО, «Сортировка» – Значения, «Поря- док» – От А до Я.
Если необходимо, можно добавлять следующие строки уровней сортировки. Число уровней может быть любым.
Замечание.Добавление нового уровня сортировки имеет смысл только в том случае, когда после сортировки на послед- нем имеющемся уровне в ТД образовались группы последова- тельно расположенных записей, для которых значения поля этой сортировки повторяются. Сортировка на следующем уровне по- зволит упорядочить записи внутри такой группы.
Подведение промежуточных итогов
Для вычисления итоговых величин в ТД (общая сумма по полю, максимум, минимум и др.) можно использовать встроен- ные функции, как это было сделано ранее. Однако, если в ходе сортировки в таблице образовались группы последовательно расположенных записей, для которых значения поля этой сор- тировки повторяются, итоговые значения можно вычислить для каждой такой группы отдельно.
Например, при сортировке по названиям отделов в преды- дущем пункте работы возникают группы записей, каждая из ко- торых объединяет сотрудников одного из отделов. Допустим, мы хотим узнать, какие суммы выплачиваются в целом сотруд- никам каждого отдела.

3. Электронные таблицы Excel
155
Последовательность действий:
13. Активизировать отсортированную ТД.
14. Щелкнуть «Данные» – «Структура» – [Промежу- точные итоги]. Появляется окно диалога (рис. 3.28).
15. В окне ввода «При каждом изменении в» указать имя поля, сортировка по которому выявила группы записей – От-
дел.
16. В окне «Операция» выбрать из списка вид подводимо- го итога. В нашем примере это сумма значений (выплат на руки сотрудникам отдела). Другими видами могут быть максималь- ное или минимальное значение какого-то поля, среднее арифме- тическое и др. или просто количество записей в группе.
17. В окне «Добавить итоги по» выбрать и пометить флажками поля, по которым будут подводиться итоги (в нашем примере – Сумма к выдаче).
18. Щелкнуть [ОК].
После этого таблица принимает вид, показанный на рис. 3.29.
Как видно из рисунка, для каждого отдела подсчитана нужная итоговая сумма. Кроме того, подведен и общий итог для всей таблицы, поэтому отпадает необходимость искать этот итог с помощью функции СУММ(…), как это было сделано в преды- дущей работе.
Рис. 3.28. Окно диалога «Промежуточные итоги»

3. Электронные таблицы Excel
156
На панели слева от таблицы отображена ее структура – выделены отдельные группы, для которых подводятся итоги, а против строк итогов помещены окошки, в которых видны знаки
«минус». Щелчок по такому окошку скрывает строки, относя- щиеся к данной группе, и оставляет в таблице только строку итогов. При этом знак «минус» заменяется на «плюс». Щелчок по окошку со знаком «плюс» вновь показывает в таблице все записи группы.
Фильтрация с помощью автофильтра
Рассмотрим пример: надо составить список зарплат со- трудников разных отделов, занимающих определенную долж- ность (например, начальников отделов). Для этого применяется
автофильтр по значению:
19. Перейти на лист ТД-3. Активизировать ТД.
20. Щелкнуть клавишу «Данные» – «Сортировка и фильтр» –
[Фильтр]. После этого рядом с заголовками полей появляются зна- ки списков. Щелчок по знаку открывает ниспадающее меню для работы с соответствующим полем ТД (рис. 3.30).
В верхней части меню находятся уже знакомые команды сортировки ТД по данному полю. В нижней части помещен спи- сок всех значений данного поля в разных записях. По умолча- нию все они помечены флажками, а первая строка списка имеет заголовок «Выделить все». Если снять щелчком флажок в этой строке, пометки снимаются и с остальных значений. После это- го надо установить флажок на нужное значение («Начальник отдела») и щелкнуть [ОК]. В ТД остаются только записи о со- трудниках, занимающих эту должность.
21. Более сложные возможности фильтрации – по условию – открывает команда меню (рис. 3.30) «Текстовые фильтры»
(фильтрацию по условию выполнить на листе ТД-4). Для число- вых полей аналогичная команда носит название «Числовые фильтры». При наведении мыши на этот пункт меню открывает- ся подменю со списком условий, которым должно удовлетво- рять значение поля у отбираемых записей. Щелчок по любому пункту списка открывает окно диалога (рис. 3.31).

157
Рис. 3.29. Таблица после сортировки и подведения промежуточных итогов
157
3.
Эл
ект
ро
нны
е
т
аб
лиц
ы
E
xce
l
3.
Эл
ект
ро
нны
е
т
аб
лиц
ы
E
xce
l

3. Электронные таблицы Excel
158
Рис. 3.30. Меню работы с полем таблицы данных
Рис. 3.31. Настройка фильтра по условию

3. Электронные таблицы Excel
159
Оно содержит две однотипные строки условий, между ко- торыми с помощью переключателя можно установить союз
ИЛИ либо союз И, объединяя два условия операцией логическо- го сложения или умножения. Каждое из условий содержит окно ввода операции сравнения (равенства или неравенства) из за- данного списка, и окно для ввода ограничивающего значения.
Если в списке условий в подменю выбрать готовый вариант
(«больше», «равно», «между» и т.д.), то в окнах операций в диа- логовом окне сразу указываются нужные виды операций.
Примеры условий:

Величина заработка должна лежать внутри отрезка
[10 000 р.; 20 000 р.] – заполнение окна показано на рис. 3.31;

Имя должно начинаться с буквы «О» – заполнение ок- на показано на рис. 3.32.
Во втором случае использована маска ввода для тексто- вых значений: символ «
*
» заменяет собой любую последова- тельность символов; символ «?» заменяет один символ.
Примеры масок ввода: запись Я?? служит маской для слов Яга, Яго, Ява, Яна, состоящих ровно из трех букв, тогда как Я
*
– для любых слов, начинающихся на «Я»: Яга, Яро-
слав, Ярцев и т.п.
На рис. 3.32 маска ввода имеет структуру: * О.* ,
Рис. 3.32. Отбор фамилий на И

3. Электронные таблицы Excel
160 т.е. «звездочка» – пробел – буква «О» – точка – «звездочка»
(перед первым инициалом в записи поля Ф. И. О обязательно стоит пробел, а после инициала – точка; прочие символы могут быть любыми).
Для показа всех записей надо либо вновь открыть меню
(рис. 3.30) и установить флажок «Выделить все», либо вообще отключить фильтр щелчком по клавише «Данные» – «Сорти- ровка и фильтр» – [Фильтр]. Результаты автофильтрации при этом не сохраняются и ее каждый раз надо производить заново.
Фильтрация с помощью расширенного фильтра
Если фильтрацию необходимо произвести по значениям более чем одного поля или более чем по двум условиям, а также если требуется сохранить результаты фильтрации, используется
расширенный фильтр. Для его применения необходима некото- рая подготовительная работа. Сначала следует сформировать диапазон условий. Для этого надо скопировать через буфер на свободную часть листа заголовки нужных полей базы. Затем в ячейках под копиями заголовков записываются условия фильт- рации в виде сокращенных равенств или неравенств (в них опускается левая часть, так как подразумевается, что слева от знака отношения стоит значение соответствующего поля). Если все условия записаны в одной и той же строке диапазона, то считается, что они объединены логической операцией И, если в разных строках – операцией ИЛИ.
Для примера рассмотрим задачу: выбрать из таблицы за- писи об инженерах, имеющих стаж работы более 10 лет и отра- ботавших за неделю более 35 часов.
Последовательность действий:
22. Перейти на лист ТД-5.
23. Скопировать ячейку С4 ( + ) и вставить ко- пию ее содержания в ячейку С27. Аналогичным образом скопи- ровать ячейки: D4 в D27, F4 в E27.
24. Ввести в ячейку С28: ="Инженер", в ячейку D28: >10, в ячейку Е28: >35. Назначить ячейкам С27: Е28 стиль «Студен- ческий».
Теперь ячейки С27: Е28 образуют диапазон условий.
25. Активизировать ТД. Щелкнуть клавишу «Данные» –
«Сортировка и фильтр» – [Дополнительно]. Открывается диало- говое окно настройки расширенного фильтра (рис. 3.33).