Конспект лекций по Excell. 2011 печ. 184Л Электронные таблицы.Кон.лек- 26.12. Конспект лекций по дисциплине компьютернаятехникаипрограммировани е для студентов 1го курса дневной и заочной форм обучения образовательно
 Скачать 3.95 Mb.
|
|
8.1. Создание Сводные таблицы формируются с помощью Мастера сводных таблиц и диаграмм за три шага. Его работу продемонстрируем на Примере 8.1. Пример 8.1. На основе исходной информации, представленной на рис. 8.1, опреде- лить общие суммы продаж по филиалам компании. Мастер сводных таблиц и диаграмм можно активизировать по команде: 1) Данные → Сводная таблица из основного меню, или 2) Сводная таблица → Мастер из меню панели инструментов Сводные таблицы. За 3 шага он в наглядной форме позволяет создавать требуемую свод- ную таблицу: Шаг 1. Определение типа источника данных и вида создаваемого отчета В окне диалога Мастер сводных таблиц и диаграмм – шаг 1 из 3 необходимо указать: 1. Под заголовком Создать таблицу на основе данных, находящих- ся – источник данных для создаваемой сводной таблицы: 168 • в списке или базе данных Microsoft Excel – если сводная табли- ца создается на основе данных, расположенных на рабочем листе. • во внешнем источнике данных – данные находятся в документах (файлах), созданных другими приложениями, дос- туп к которым осуществляется при помощи Microsoft Query. • в нескольких диапазонах консолидации – источником данных являются несколько диапазонов ячеек, расположенных на рабо- чих листах Microsoft Excel. • в другой сводной таблице или диаграмме – источником дан- ных является другая сводная таблица. Для рассматриваемого примера необходимо выбрать значение в списке или базе данных Microsoft Excel. 2. Под заголовком Вид создаваемого отчета: • сводная таблица – для создания только сводной таблицы. • сводная диаграмма (со сводной таблицей) – для создания свод- ной диаграммы вместе со сводной таблицей. Для нашего примера необходимо указать значение сводная таблица. Шаг 2. Определение места расположения источника данных По умолчанию, если возможно, Excel сам определяет исходный блок ячеек и обводит его бегущей пунктирной линией. Для нашего примера, например, необходимо, чтобы до запуска мастера была активной одна из ячеек исходного диапазона A2:G17. Первая строка выделенного диапазона данных обязательно должна содержать заголовки столбцов. Они используются для создания полей сводной таблицы и идентификации отдельных эле- ментов данных. 169 В случае если данные находятся в закрытых рабочих книгах, их следует указать с помощью окна диалога Обзор, аналогичного стан- дартному окну открытия документа, которое активизируется по нажа- тию одноименной кнопки. Если на предыдущем шаге было указано, что информация находится во внешнем источнике данных, то для доступа к ней будет запущена программа Microsoft Query, которую не- обходимо настроить соответствующим образом, в зависимости от ме- стоположения и типа источника данных. Шаг 3. Определение структуры и местоположения сводной таблицы Этот шаг является самым важным и самым ответственным, поскольку именно здесь необходимо определить макет создаваемой сводной таблицы – т.е. указать в каких областях сводной таблицы будут раз- мещены какие поля исходных данных, а также их порядок, итоговые функции и другие параметры. В раскрывшемся окне диалога Мастер сводных таблиц и диаграмм – шаг 3 из 3 необходимо указать: 1. С помощью переключателя Поместить таблицу в – где именно бу- дет отображаться сводная таблица: • новый лист – если сводную таблицу необходимо расположить на новом рабочем листе, или • существующий лист – если сводная таблица будет размещаться на существующем листе. При этом необходимо указать также адрес ее левого верхнего угла. Для нашего примера необходимо установить значение новый лист. 2. Определить макет (структуру, разместить поля) будущей сводной таблицы. Это можно выполнить двумя различными способами – по нажатию кнопки: а) Готово – вручную непосредственно на рабочем листе, и б) Макет – с помощью специального окна диалога Мастер свод- ных таблиц и диаграмм – Макет. 170 8.2. Формирование сводной таблицы вручную непосредственно на рабочем листе В результате щелчка по кнопке Готово в окне диалога Мастер сводных таблиц и диаграмм – шаг 3 из 3, как показано ниже на рисунке, на рабочем листе появляются следующие элементы: 1) разметка для будущей таблицы, 2) панель инструментов Сводные таблицы, 3) окно диалога Список полей сводной таблицы. Структура сводной таблицы формируется путем перемещения полей из окна Список полей сводной таблицы в соответствующим образом обозна- ченные области будущей сводной таблицы. Это перемещение можно выполнить двумя различными способами: 1. Методом перетаскивания названия требуемого поля из окна Список полей сводной таблицы в необходимую область разметки на рабочем листе. Во время перетаскивания названия поля форма указателя мыши изме- няется в зависимости от того, над какой областью он находится. 2. Методом выбора из списка. а) Указать требуемое поле в окне Список полей сводной таблицы. б) В раскрывающемся списке выбрать область, куда будет помещено поле: • область строк, • область столбцов, • область страниц, • область данных. в) Щелкнуть по кнопке Поместить в. Некоторые поля можно использовать только в определенных областях. Если такое поле попытаться поместить в область, где его использовать нельзя, оно в ней не появится. 171 Если окно Список полей сводной таблицы не отображается, то необходимо щелкнуть по кнопке Отобразить список полей на панели инструментов Сводные таблицы. В результате кнопка примет «утоплены» вид и сменит свое называние на Скрыть список полей. Повторный щелчок по ней вызывает скрытие окна Список полей сводной таблицы и возврат ей предыдущего названия. 8.3. Формирование сводной таблицы с помощью специального окно диалога Мастер сводных таблиц и диаграмм – макет В результате щелчка по кнопке Макет в окне диалога Мастер сводных таблиц и диаграмм – шаг 3 из 3 активизируется специализированное окно диалога – Мастер сводных таблиц и диаграмм – макет, в правой части ко- торого расположены кнопки с названиями всех полей исходного диапазона данных, и которое представлено на следующем ниже рисунке: Формирование сводной таблицы, как и в предыдущем варианте, осущест- вляется методом прямого перетаскивания кнопок с названиями полей в соответ- ствующие области (Страница, Строка, Столбец и Данные) будущей таблицы. Основное различие между формированием сводных таблиц непосредст- венно на рабочем листе и их формированием с помощью специального окно диалога Мастер сводных таблиц и диаграмм – макет состоит в том, что в первом случае, как правило, элементы данных появляются в сводной таблице сразу после завершения их перетаскивания, а во втором – только после полно- го завершения формирования макета сводной таблицы, и закрытия окон диа- лога Мастер сводных таблиц и диаграмм – макет и Мастер сводных таб- лиц и диаграмм – шаг 3 из 3. В некоторых случаях, особенно при формировании вручную больших сводных таблиц на маломощном компьютере, могут возникать некоторые за- 172 держки в появлении данных на рабочем листе. В этом случае необходимо от- ключить отображение перемещаемых данных, щелкнув по кнопке Всегда отображать элементы на панели инструментов Сводные таблицы. Если же скорость отображения информации все равно не увеличилась, или появляются сообщения об ошибках, то формирование сводной таблицы необходимо выпол- нить с помощью окно диалога Мастер сводных таблиц и диаграмм – макет. Для нашего Примера 8.1, вне зависимости от способа формирования ма- кета сводной таблицы, необходимо поместить: 1. Поле Филиал – в область строк. 2. Поле Сумма – в область данных. В результате будет получена сводная таблица, которая представлена на следующем рисунке: Она полностью удовлетворяет поставленной в Примере 8.1 задаче – под- счет общих сумм продаж по филиалам компании. Однако для более наглядно- го представления полученной информации необходимо еще откорректировать ее внешнее представление – т.е. отформатировать. Форматирование сводной таблицы выполняется точно также как и любой другой таблицы. Для нашей сводной таблицы такое форматирование может заключаться, например, в выполнении следующих шагов: 1. Удалить строки 1 и 2, поскольку в ней область страниц не используется. 2. В ячейке A1 стандартный заголовок – Сумма по полю Сумма – заменить на более информативный – Общие продажи, а в ячейке A2 – Филиал на Филиалы. 3. В ячейках B3:B6 вместо используемого по умолчанию формата Общий ус- тановить формат Денежный с отображением денежной единицы, двумя 173 знаками после десятичной точки (копейками) и разделением на тетрады (тысячи). 4. Заголовок числового столбика B2 Итог выровнять по правой стороне ячейки. 5. Ширину столбиков A и B установить в соответствии с их содержанием. В результате, после того как активная ячейка «покинет» пределы свод- ной таблицы, она примет следующий вид: В ней будут скрыты окно диалога Список полей сводной таблицы, и обо- значены границы синего цвета вокруг соответствующих областей перетаскивания. Активизация же любой ячейки в пределах сводной таблицы вновь ото- бражает эти элементы. В этой сводной таблице, помимо автоматически подводимого итога по столбцу данных B, есть еще одна особенность – в ячейке A2, которая содер- жит название поля строк – Филиалы – имеется кнопка со стрелкой направ- ленной вниз. Щелчок по ней вызывает отображение окна со списком флажков, соответствующих элементам значений этого поля. В нашем примере – это на- звания филиалов компании. По умолчанию все флажки установлены – т.е. отображаются все значения этого поля. После сброса флажка, соответствующего значению, например, Фили- ал2, в сводной таблице будет отображаться информация обо всех филиалах, кроме этого. Результат такого действия представлен на следующем рисунке: Изменение макета уже созданной сводной таблицы также выполняется достаточно легко – необходимо просто перетащить требуемое поле из одной области в другую. Во время такого перемещения указатель мыши все время 174 меняется в зависимости от того, в пределах какой области он сейчас находит- ся. Если для нашего примера заголовок поля Филиалы из области строк (ячейка A2) переместить в область столбцов (ячейка B1), то наша сводная таб- лица примет вид, как показано на рисунке далее: Выполненную операцию можно представить как отражение (вращение) части таблицы относительно условной оси, проведенной из левого верхнего угла таблицы в правый нижний. Именно за столь легкое (буквально одним движением) изменение структуры сводной таблицы в оригинальном, англий- ском, варианте Microsoft Excel они называются Pivot Tables (Pivot по- английски «точка», «ось», «стержень» и «поворот», «вращение» – т.е. «точка вращения», «ось вращения», «шарнир», «вертлюг» и т.д.). Таким образом, созданную с помощью мастера сводную таблицу затем можно сколь угодно много трансформировать и «улучшать» для придания ей наиболее легко воспринимаемого внешнего представления. При этом, как во время создания, так и во время настройки сводных таб- лиц необходимо придерживаться некоторых правил и ограничений. Большин- ство из них ранее уже упоминались. Однако здесь они собраны все вместе, и сформулированы более четко и более формально. 1. Количество сводных таблиц на рабочем листе не ограничено – оно ограничивается только ресурсами компьютера. 2. Число полей столбцов и строк сводной таблицы ограничивается только числом полей (столбцов) исходного списка. Однако число полей данных и полей страниц не может быть больше 256. 3. Исходные данные для сводных таблиц должны быть организованы в виде списков. В первой строке такого списка должны обязательно находиться заголовки столбцов. 4. В областях группировки – страниц, строк или столбцов – любое поле ис- ходных данных может быть размещено только один раз. 5. В области данных любое поле исходных данных может быть размещено многократно, например, для вычисления различных видов итогов. 8.4. Настройка Отчет сводной таблицы представляет собой интерактивную таблицу, с помощью которой можно автоматически выполнять различные сортировки, 175 вычислять промежуточные и общие итоги, быстро обобщать большие объемы данных, сравнивать связанные итоги, особенно при сравнении нескольких фактов по каждому числу из длинного списка обобщаемых чисел. При этом можно менять местами строки и столбцы для получения различных сводок по исходным данным, фильтровать данные, отображая различные страницы, либо отображать подробности по нужным областям. Изменение и настройка параметров сводных таблиц выполняется при помощи следующих 3-х наборов инструментов: 1. Панели инструментов Сводные таблицы (рис. 8.4), которая активизируется: 1) как и любая другая панель инструментов, или 2) во время построения сводной таблицы, или 3) с помощью контекстного меню. Вид панели инструментов Сводные таблицы приведен на рис. 8.4. 1. Меню Сводная таблица 2. Кнопка Формат отчета 3. Кнопка Мастер диаграмм 4. Кнопка Скрыть детали 5. Кнопка Отобразить детали 6. Кнопка Обновить данные 7. Кнопка Учитывать скрытые элементы в итогах 8. Кнопка Всегда отображать эле- менты 9. Кнопка Параметры поля 10. Кнопка Отобразить список полей Рис. 8.4 – Панель инструментов Сводные таблицы 2. Контекстного меню (рис. 8.5), которое активизируется по щелчку правой кнопки мыши в пределах сводной таблицы, и в котором продублировано 1 3 2 4 6 8 10 5 7 9 176 большинство команд меню Сводная таблица из панели инструментов Сводные таблицы. Рис. 8.5 – Контекстное меню сводных таблиц 3. Окна диалога Параметры сводной таблицы (рис. 8.6), которое активизи- руется: а) щелчком по кнопке Параметры в окне диалога 3-го шага Мастера сводных таблиц и диаграмм, или б) по команде Параметры таблицы из контекстного меню. Рис. 8.6 – Окно диалога Параметры сводной таблицы Использование того или иного инструмента изменения и настройки сводных таблиц зависит от обстоятельств. Так, например, доступ к функциям изменения сводных таблиц в большинстве случаев гораздо быстрее выполня- ется при помощи панели инструментов Сводные таблицы. Однако в «пла- вающем» состоянии она занимает некоторую часть рабочего листа и может перекрывать сводную таблицу. Контекстное меню постоянно не отображается, а потому не мешает работе со сводными таблицами, но для выполнения любой из его команд необходимо выполнить, по крайней мере, два щелчка мышью, в 177 то время как для активизации команды из панели инструментов необходимо щелкнуть по кнопке только один раз. Окно диалога Параметры сводной таблицы служит для настройки па- раметров формирования и отображения сводных таблиц. Оно состоит из двух групп элементов управления, которые озаглавлены: • Формат – служат для установки общих параметров отображения сводной таблицы, • Данные – устанавливают параметры управления данными, на основании которых построена сводная таблица. Сами же параметры сводных таблиц устанавливаются при помощи стан- дартных элементов управления следующим образом: Поле редактирования Имя – содержит имя сводной таблицы, которое по умолчанию принимает значение СводнаяТаблица1. Флажки в выставленном состоянии имеют такие значения: • общая сумма по столбцам – формировать итоговые значения по столбцам. • общая сумма по строкам – формировать итоговые значения по строкам. • автоформат – применение используемого по умолчанию автоформата Microsoft Excel к сводной таблице. • сохранять форматирование – сохранение приоритета индивидуального пользовательского форматирования сводной таблицы при ее обновлении над автоформатированием. • включать скрытые значения – включение в итоговые значения свод- ной таблицы неотображаемых элементов. • объединять ячейки заголовков – объединение групп ячеек, которые образуют внешние заголовки строк и столбцов сводной таблицы. • для ошибок отображать: – в поле редактирования справа указывается значение, которое будет выводиться в случае ошибки. По умолчанию отображаются стандартные значения ошибок Microsoft Excel. • для пустых ячеек отображать: – в поле редактирования справа указы- вается значение, которое будет отображаться для пустых ячеек. По умолчанию пустые ячейки остаются пустыми. • повторять подписи на каждой странице печати – печать подписей элементов полей внешних строк на каждой странице. • печать заголовков – использование подписей полей и элементов свод- ной таблицы в качестве заголовков строк и столбцов при печати. • |