Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
помечать итоги * – промежуточные и окончательные итоги в сводных таблицах, которые используют в качестве источника данные куба, будут помечаться звездочками (*). 178 • сохранять данные вместе с таблицей – сохранение содержимого внут- ренней кэш-памяти в файле рабочей книги с целью предотвращения по- вторного обновления сводной таблицы после следующего открытия это- го файла рабочей книги, что существенно увеличивает размер этого файла. Однако в случае сброса этого флажка, для того чтобы начать ра- ботать со сводной таблицей в открываемой рабочей книге необходимо либо установить флажок обновить при открытии, либо обновить со- держимое кэш-памяти щелкнув, например, по кнопке Обновить дан- ные на панели инструментов Сводные таблицы. • обновить при открытии – обновление сводной таблицы в соответствии с исходными данными при открытии рабочей книги. • развертывание разрешено – двойной щелчок мышью вызывает ото- бражение, или сворачивание, подробных данных содержимого ячейки или соответствующего окна диалога – в зависимости от объекта сводной таблицы, по которому был выполнен двойной щелчок. Если же этот флажок сброшен, двойной щелчок, как обычно, вызывает переход в ре- жим непосредственного редактирования содержимого ячейки. • обновлять каждые – в поле редактирования со счетчиком справа ука- зывается интервал времени автоматического обновления содержимого кэш-памяти (и, соответственно, сводной таблицы) по содержимому источ- ника данных, в соответствии с которым была построена сводная таблица. • сохранять пароль – если при построении сводной таблицы был исполь- зован внешний источник данных, который требует пароль, то он сохра- няется как часть сводной таблицы, и при ее обновлении пароль повторно вводить не требуется. • фоновый запрос – загрузка указанных в запросе внешних данных будет выполняться в фоновом режиме. Пользователь при этом может продол- жать работу, не дожидаясь окончания выполнения запроса. • оптимизировать память – экономия памяти, которая требуется во вре- мя обновления сводной таблицы из внешнего источника данных. Раскрывающийся список макет страницы – указывает расположение полей в области страниц. По умолчанию используется макет вниз, затем поперек. Поле редактирования со счетчиком число полей в столбце – указывается число полей страниц в столбце или строке, которые должны располагаться перед началом новой строки или столбца. Пример 8.2. На основе исходных данных, представленных на рис. 8.1, определить общие суммы продаж каждого товара по филиалам компании. 179 Поставленную задачу можно решить с применением одного из двух воз- можных методов: 1) перестроить макет уже имеющейся таблицы, или 2) создать новый макет сводной таблицы. Используем второй метод. В результате после выполнения 2-го шага Мастера сводных таблиц и диаграмм будет выдано следующее окно сообщений: При этом можно нажать кнопку: • Да – чтобы для создания новой сводной таблицы использовать уже имею- щийся блок кэш-памяти, имеющей тот же источник данных, что и существующая сводная таблица, или • Нет – чтобы во время создания новой сводной таблицы был создан еще один блок кэш-памяти на основе прежнего источника данных. Каждый из этих вариантов имеет свои особенности. Так, использование уже имеющейся копии исходных данных в кэш-памяти экономит память сис- темы и обновление всех сводных таблиц, созданных на основе этой копии, выполняется одновременно. С другой стороны, использование еще одной ко- пии тех же исходных данных в некоторой степени делает сводные таблицы более независимыми. В случае щелчка по кнопке Да будет активизировано представленное далее окно диалога Мастер сводных таблиц и диаграмм – шаг 2 из 3, список которого Укажите сводную таблицу, содержащую ис- ходные данные содержит все сводные таблицы, имеющиеся в текущей рабо- чей книге. В этом списке необходимо указать сводную таблицу, на основании содержи- мого кэш-памяти которой будет построена новая сводная таблица. Такое же окно диалога появляется, если на 1-м шаге Мастера сводных таблиц и диа- грамм указать в качестве источника данных в другой сводной таблице или 180 сводной диаграмме. В результате будет создана, например, такая сводная таблица: Рис. 8.7 – Продажи товаров в разрезе их наименований и филиалов компании В ней суммы продаж по каждому наименованию товаров представлены в разрезе филиалов компании. Однако в ней ни как не отображается информа- ция о качестве работы продавцов. Пример 8.3. Изменить макет сводной таблицы, представленной на рис. 8.7, так чтобы в ней отображалась также информация о сумме проданного товара каждым продавцом компании. Для этого необходимо: 1. Выделить сводную таблицу, щелкнув по любой из ее ячеек. 2. Перетащить поле Продавец в требуемую область сводной таблицы, вос- пользовавшись для этого: а) окном Список полей сводной таблицы, или б) Мастером сводных таблиц и диаграмм. В последнем случае Мастер сводных таблиц и диаграмм будет активизи- рован на 3-м шаге, и для открытия специального окно диалога Мастер сводных таблиц и диаграмм – макет необходимо щелкнуть по кнопке Макет. При перемещении названий полей в области строк, столбцов или стра- ниц имеет значение их относительный порядок расположения – поле, нахо- дящееся первым, является «старшим», по отношению к следующему. Это значит, что одному значению «старшего» поля будут соответствовать все значения «младших» полей. Поэтому, например, если поле Продавец поместить в области строк пе- ред полем Филиал, то будет получена приведенная ниже сводная таблица. В таком виде сводная таблица, не только не добавляет ни какой новой информации, а, наоборот, запутывает. 181 Поэтому поле Продавец следует располагать после поля Филиал, поскольку между ними существует вполне определенная иерархическая зависимость – каждый продавец может работать только в одном, вполне определенном, филиа- ле компании. В результате будет получена приведенная далее сводная таблица: Рис. 8.8 – Продажи товаров различными продавцами компании Однако даже в таком представлении сводной таблицы довольно трудно опре- делить самого лучшего и самого худшего из продавцов. Для этого ее необхо- димо отсортировать. 8.5. Сортировка и изменение порядка заголовков полей в сводных таблицах Сортировку сводной таблицы можно выполнить несколькими различ- ными способами, при помощи: 1) специального окна диалога Лучшая десятка и сортировка сводной таблицы, 2) окна диалога Сортировка, 3) кнопок Сортировка по возрастанию и Сортировка по убыванию на панели инструментов Стандартная. Сортировка сводной таблицы с помощью окна диалога Лучшая десятка и сортировка сводной таблицы выполняется в такой последовательности: 182 1. Щелкнуть заголовок поля, по которому необходимо отсортировать табли- цу. Для нашего примера – это Продавец. 2. Выполнить команду Сводная таблица → Сортировка и десятка лучших из меню панели инструментов Сводные таблицы. 3. В раскрывшемся при этом окне диалога Лучшая десятка и сортировка сводной таблицы с помощью имеющихся стандартных элементов управле- ния указать параметры сортировки сводной таблицы следующим образом: а) Переключатель Параметры сортировки выставить в положение, соот- ветствующее порядку сортировки: • вручную (разрешается перетаскивание) – чтобы отменить сорти- ровку сводной таблицы. • по возрастанию – чтобы упорядочить сводную таблицу по возраста- нию значения ключевого поля сортировки. • по убыванию – чтобы упорядочить сводную таблицу по убыванию значения ключевого поля сортировки. Для нашего примера выберем значение по возрастанию. б) В раскрывающемся списке с помощью поля указать: • заголовок поля – чтобы упорядочить по заголовкам поля сортировки, • поле в области данных – чтобы упорядочить по значениям поля сор- тировки. Для нашего примера – это Сумма по полю Сумма. в) Щелкнуть по кнопке OK. В результате сводная таблица будет выглядеть следующим образом: 183 В ней суммы продаж каждым продавцом компании упорядочены только в пределах более высокого уровня группировки, т.е. в пределах филиала. Для того чтобы сортировка была выполнена по всем продавцам компании подряд, необходимо удалить более высокий уровень группировки – перетащив, на- пример, поле Филиал из области строк за пределы сводной таблицы. Сортировка сводных таблиц при помощи окна диалога Лучшая десятка и сортировка сводной таблицы имеет одну особенность – при обновлении или изменении макета сводной таблицы она всегда будет переупорядочиваться в соответствии с новыми значениями ключевого поля сортировки. Для того что- бы отключить это свойство необходимо в окне диалога Лучшая десятка и сортировка сводной таблицы переключатель Параметры сортировки уста- новить в положение вручную (разрешается перетаскивание). Сводная таблица в результате вернется в исходное (до выполнения сортировки) состояние. Сортировка сводной таблицы при помощи окна диалога Сортировка выполняется в такой последовательности: 1. Выделить любую ячейку в пределах сводной таблицы 2. Выполнить команду Данные → Сортировка. 3. В раскрывшемся в результате окне диалога Сортировка указать при по- мощи имеющихся стандартных элементов управления параметры сорти- ровки следующим образом: а) В поле редактирования Сортировать по – одну из ячеек сортируемого диа- пазона. В рассматриваемом примере это может быть, например, ячейка H6. б) С помощью одноименного переключателя (Сортировать по) – выста- вить требуемый порядок сортировки: • по возрастанию, или • по убыванию. Для нашего примера выберем значение по возрастанию. в) С помощью переключателя Сортировать – установить объект сортировки: • значения – чтобы упорядочить сводную таблицу по значениям клю- чевого поля, или 184 • подписи – чтобы упорядочить сводную таблицу по заголовкам клю- чевого поля. В нашем примере переключатель Сортировать установим в положение значения. г) Щелкнуть по кнопке Параметры – чтобы установить дополнительные параметры сортировки, такие, например, как упорядочивание сводной таблицы по специальному списку. д) Щелкнуть по кнопке OK – чтобы упорядочить сводную таблицу в соответствии с установленными выше параметрами. В результате сводная таблица будет упорядочена по значениям столбика Общий итог, но только в пределах области , содержащей активную ячейку. В нашем примере – это лишь в пределах Филиала1 (или строк 5:7). Для сортировки общих продаж продавцов по другим филиалам необходимо последовательно вы- делить соответствующие ячейки в столбце Общий итог и повторить сортировку. Сортировка сводной таблицы при помощи кнопок Сортировка по возрастанию и Сортировка по убыванию на панели инструментов Стан- дартная выполняется аналогичным образом, т.е.: 1. Выделить ячейку в требуемой области сортировки. Например, H6. 2. На панели инструментов Стандартная щелкнуть кнопку, соответствую- щую направлению упорядочивания – Сортировка по возрастанию или Сортировка по убыванию. В результате сводная таблица также будет упорядочена, но опять же только в пределах своей группы – например, Филиала1. Сортировка сводной таблицы, выполненная при помощи окна диалога Сортировка или кнопок Сортировка по возрастанию и Сортиров- ка по убыванию на панели инструментов Стандартная, в отличие от сор- тировки, выполненной при помощи специального окна диалога Лучшая десят- ка и сортировка сводной таблицы, может быть нарушена в результате ее обновления или изменении макета. Изменение порядка заголовков полей сводных таблиц в пределах одной области может быть выполнено двумя различными способами: 1) перетаскиванием, и 2) переименованием. Для изменения заголовков полей сводных таблиц в пределах одной об- ласти методом перетаскивания необходимо: 1. Выделить заголовок требуемого поля. 185 2. Поместить указатель мыши на одну из границ выделенной ячейки – при этом он примет вид четырехнаправленной стрелки. 3. При нажатой левой кнопке мыши отбуксировать выделенный заголовок в необходимую позицию. При этом во время перемещения заголовка его текущее положение ото- бражается при помощи специальной линии, и его нельзя переместить за пре- делы текущего поля. На следующем рисунке представлен момент перемещения заголовка по- ля Товар5 в новую позицию: Для изменения заголовков полей сводных таблиц в пределах одной об- ласти методом переименования необходимо в ячейке нового положения заго- ловка поля просто изменить его на заголовок поля, которое сюда необходимо переместить. На рисунке далее приведен момент переименования заголовка поля Товар5 в Товар1: В результате столбик Товар1 переместится в положение столбика То- вар5. Все остальные столбики при этом просто сместятся на освободившееся место столбика Товар1 влево. Перемещение заголовков полей методом переименования имеет сле- дующие две особенности: 186 1. Переименование заголовков полей производится в поле редактирования строки формул, поскольку по умолчанию двойной щелчок по ячейке заго- ловка приводит не к началу ее редактирования на месте, а к активизации окна диалога Показать детали. Для того чтобы изменить такую реакцию системы необходимо в окне диалога Параметры сводной таблицы сбро- сить флажок развертывание разрешено. 2. Если заголовку присвоить не одно из существующих имен, а любое, про- извольное – он будет не перемещен, а просто переименован. При перемещении заголовков полей любым способом в случае наруше- ния порядка сортировки сводной таблицы, выполненной при помощи специ- ального окна диалога Лучшая десятка и сортировка сводной таблицы, бу- дет выдано следующее окно сообщений: Для того чтобы все-таки выполнить перемещение полей необходимо предварительно отменить выполненную ранее сортировку сводной таблицы, установив вокне диалога Лучшая десятка и сортировка сводной таблицы переключатель Параметры сортировки в положение вручную (разрешается перетаскивание). 8.6. Группировка элементов сводных таблиц Одним из широко распространенных приемов обработки и анализа боль- ших объемов разнородной информации является организация ее в виде неко- торой древовидной (иерархической) структуры, т.е. когда одному элементу информации более высокого уровня соответствуют все, «принадлежащие ему», элементы информации более низкого уровня. При этом способ группи- ровки информации по уровням зависит от целей ее обработки и, в общем слу- чае, может иметь много различных вариантов. Microsoft Excel имеет довольно удобные средства отображения именно того уровня в иерархии данных, кото- рый непосредственно нужен пользователю в данный момент. Но для того что- бы воспользоваться всеми возможностями иерархической структуры данных их необходимо предварительно сгруппировать по требуемым уровням иерархии. Группировка информации в сводных таблицах Microsoft Excel может быть выполнена несколькими различными способами: 1. Во время создания или изменения макета – при помощи размещения полей в определенном порядке. 187 В созданной ранее, согласно условиям Примера 8.2, сводной таблице поле Филиал расположено на более высоком уровне иерархии, чем поле Продавец. При этом установленный порядок уровней иерархии меняется совершенно легко – для этого необходимо просто методом перетаскивания разместить их в нужном порядке непосредственно на рабочем листе, или в окне диалога Мастер сводных таблиц и диаграмм – макет. Однако такой метод группировки информации в виде иерархических структур имеет одну особенность – группировать информацию можно только по полям, которые присутствуют в исходных данных. 2. Группировка информации по полям, которые отсутствуют в исходных данных. Ее выполнение рассмотрим на следующем примере. Пример 8.4. На основании представленной на рис. 8.8 сводной таблицы сгруппиро- вать филиалы компании по регионам их расположения, если в регионе Вос- ток расположены Филиал1 и Филиал3, а в региона Запад – Филиал2. Решение поставленной задачи выполняется в такой последовательности: а) Выделить ячейки с названиями Филиала1 и Филиала3. Это могут быть как отдельные ячейки из диапазонов A5:A7 и A12:A14, так и диапазоны целиком. б) Выполнить команду Группа и структура → Группировать: • из контекстного меню, или • меню Сводная таблица на панели инструментов Сводные таблицы. При этом в сводной таблице появится новое поле Филиал 2 с элементами Группа 1 и Филиал 2. в) Откорректировать названия вновь добавленных полей и их элементов, сменив заголовок поля Филиал 2 на Регион, а заголовки элементов Груп- па 1 и Филиал 2 на Восток и Запад, соответственно. В результате будет получена следующая сводная таблица (рис. 8.9): Рис. 8.9 – Группировка филиалов компании по их расположению 188 Она отображает иерархические взаимосвязи между Регионами, Филиалами и Продавцами компании. Но столь подробная и развернутая информация не все- гда нужна. Иногда необходимы лишь итоговые данные по некоторому элемен- ту иерархии, без всех его составляющих. Для того чтобы в сводной таблице Microsoft Excel скрыть подробную информацию по некоторому элементу данных в его иерархической структуре (кроме самого «нижнего» уровня) необходимо: |