Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

.

Шаг 2. Определение интервала исходных данных

В этом диалоге (рис. 3) необходимо указать диапазон исход­ных данных, предназначенный для построения сводной таблицы.

• 
  Укажите интервал, выделив диапазон данных, воспользовавшись трехцвет­ной кнопкой.
  

Параметры...



Рис. 3. Диалоговое окно шага 2

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

Вид окна Мастера сводных таблиц и диаграмм на шаге 2 напрямую зависит от источника данных, который был выбран на первом шаге. Если предполагается формировать сводную таблицу из внешних источ­ников данных (в том числе созданных не на основе таблиц Excel, воз­можно, размещенных на удаленных компьютерах сети), то окно шага 2 имеет вид, представленный на рис. 4.



Рис. 4. Диалоговое окно шага 2 Мастера сводных таблиц и диаграмм

при указании на шаге 1 внешнего источника данных

Используя кнопку указанного окна Получить данные, в новом окне (рис. 14.8) можно указать в качестве источника Базы данных, Запросы, Кубы ОLAP. Используя кнопку Обзор, можно найти требуемые источ­ники, а с помощью кнопки ОК можно выбрать искомые файлы, следуя появляющимся инструкциям.

Если для создания сводной таблицы использовались данные, находя­щиеся на нескольких листах рабочей книги, то второй диалог Мастера сводных таблиц и диаграмм разбивается на два подэтапа.

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



Рис. 5 Диалоговое окно шага 2а

Мастера сводных таблиц и диаграмм при указании на шаге 1 нескольких диапазонов консолидации

Кнопка Далее вызывает окно, представленное на рис. 6. Используя возможности этого окна, можно найти и добавить к обра­ботке требуемые диапазоны ячеек, размещенные на одном и том же или разных листах рабочей книги. Для этого в поле

---

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



Рис. 6. Диалоговое окно шага 26

Мастера сводных таблиц и диаграмм при указании на шаге 1 нескольких диапазонов консолидации

Если сводная таблица строится на базе другой сводной таблицы или сводной диаграммы, размещенных в текущей книге, окно шага 2 имеет вид, представленный на рис. 7. В данном окне необходимо выделить требуемый лист.



Рис. 7. Диалоговое окно шага 2

Мастера сводных таблиц и диаграмм при выборе данных на шаге 1 из других сводных таблиц

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

Шаг 3. Формирование таблицы

На этом шаге определяется местоположение сводной таблицы, ее имя, структура и внешний вид.

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



Рис. 8. Окно шага 3 Мастера сводных таблиц и диаграмм

• 
  Присвойте имя сводной таблице можно, нажав кноп­ку Параметры (рис. 9).
  
• 
  Изучите все параметры сводной таблицы.
  

Рис. 9. Окно Параметры сводной таблицы

• 
  Структура сводной таблицы определяется после нажатия кнопки Ма­кет и открытия диалогового окна настройки макета сводной таблицы (рис. 10).
  

---

Рис. 10. Диалоговое окно формирования структуры сводной таблицы

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

Макет сводной таблицы состоит из следующих фиксированных облас­тей:

• 
  Страница. Значения в данной области выступают в качестве эле­ментов страницы сводной таблицы;
  
• 
  Строка. Значения в данной области выступают в качестве эле­ментов строки в сводной таблице, т.е. в качестве заголовков строк таблицы;
  
• 
  Столбец. Значения в данной области выступают в качестве эле­ментов столбца в сводной таблице, т.е. в качестве заголовков столбцов таблицы;
  
• 
  Данные. В данной области отображаются детальные и итого­вые для сводной таблицы поля, которые составляют содержи­мое таблицы.
  

Таблица 2.

Значения параметров сводной таблицы

Параметр	Назначение (действие)
Имя	Присвоение имени. Excel автоматически задает имена в форме СводнаяТаблица1 и т.д.
Общая сумма по столбцам	Вычисление общей суммы для элементов, отображаемых в столбцах
Общая сумма по строкам	Вычисление общей суммы для элементов, отображаемых в строках
Автоформат	Установка одного из типов автоформатирования
Включать скрытые значения	Включение скрытых элементов, находящихся на странице, в промежуточные суммы
Объединять ячейки заголовков	Объединение ячеек внешней строки и столбца заголовков
Сохранять форматирование	Сохранение форматирования после обновления сводной таблицы
Повторять подписи на каждой странице печати	Расположение подписи строк на каждой стра­нице распечатанного отчета сводной таблицы
Макет страницы	Установка порядка, в котором будут отобра­жаться поля страницы
Число полей в столбце	Задание числа полей страницы, отображаемых в одной строке страницы
Для ошибок отображать	Установка значения, которое будет отобра­жаться для ячеек сводной таблицы, содержащих ошибку
Для пустых ячеек отображать	Установка значения, которое будет показано для пустых ячеек сводной таблицы
Печать заголовков	Расположение подписи столбцов на каждой странице распечатанного отчета сводной таблицы
Помечать итоги	Отображение звездочкой индикации того, что промежуточные и итоговые суммы включают и отображаемые, и скрытые элементы. Данный параметр активизирован только в том случае, когда сводная таблица создается из источника данных OLAP
Сохранять данные вместе с таблицей	Сохранение дополнительной копии данных
Развертывание разрешено	Просмотр элементов, составляющих итоговые значения
Обновить при открытии	Обновление сводной таблицы при каждом открытии рабочей книги
Обновлять каждые... минуты1	Задание интервала времени, через который происходит обновление сводной таблицы в открытой рабочей книге
Сохранить пароль1	Сохранение пароля для открытия внешней базы данных
Фоновый запрос1	Выполнение запроса базы данных в фоновом режиме, что позволяет не прерывать работу
Оптимизировать память1	Уменьшение объема памяти, используемой при обновлении запроса внешней базы данных

---

¹ Параметры Обновлять каждые ... минуты, Сохранить пароль, Фоновый запрос, Оптимизировать память активизированы в слу­чае, когда сводная таблица создается из внешней базы данных.

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

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

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



Рис. 11. Диалоговое окно после завершения формирования структуры сводной таблицы

• 
  По завершению формирования макета нажмите ОК, а затем и по кнопке Готово. На рабочем листе появится сводная таблица. Вид ее применительно к рассматрива­емому примеру показан на рис. 12.
  

Рис. 12. Фрагмент окна сформированной сводной таблицы

Как видно, поля, которые были помещены в области макета Страница, Строка и Столбец, отображаются в виде раскрывающихся списков. Так, для элемента области Страница «Отрасль» можно выбрать в списке параметр с именем «Все», что обеспечит вывод сведений по всем отрас­лям базы данных (в нашем примере: для юридических лиц – меди­цина, промышленность, торговля, для физических лиц – предприни­матель), а можно указать только определенные параметры, что обеспе­чит вывод соответствующих данных.

• 
  Выведите информацию только о клиентах – юридических лицах.
  

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

В связи с тем, что сводная таблица представляет собой особый тип диапазона данных, внутри нее нельзя вставлять строки или столбцы, содержащие выражения, элементами которых являются данные этой таб­лицы. Для этой цели можно использовать механизм создания

---

вычисля­емых полей.

Задание 4.

Вычисляемое поле состоит из вычислений, в которых использованы другие поля сводной таблицы. Оно должно находиться только в области данных сводной таблицы и не может использоваться в области стра­ницы, строки или столбца.

В качестве примера приведем механизм формирования вычисляемого поля для расчета платы банку за издержки при оформлении кредита. Пусть данная величина составляет 0,5% от суммы кредита.

Схема создания вычисляемого поля может быть представлена следу­ющим образом.

1. 
   Установите курсор в любую ячейку сводной таблицы.
   
2. 
   На панели инструментов Сводные таблицы раскройте список кнопки Сводная таблица и выберите команды: Формулы → Вычисляемое поле... В результате появится диалоговое окно Вставка вычисляемого поля (рис. 13).
   
3. 
   В поле окна Имя введите название вычисляемого поля (в при­мере – Плата банку за издержки).
   
4. 
   В поле Формула введите формулу расчета нового поля
   

(в при­мере: ='Сумма кредита'*0,5%).

Выражение формулы может содержать поля сводной таблицы, встроенные функции и конс­танты, связанные знаками операций.

Для вставки в выражение полей сводной таблицы в списке Поля выделите тре­буемое поле, после чего выполните щелчок мышью по кнопке Добавить поле.

5. 
   Д ля добавления созданного поля в сводную таблицу нажмите Добавить.
   
6. 
   Для закрытия диалогового окна нажмите ОК.
   

Рис. 13. Окно формирования формулы вычисляемого поля сводной таблицы

Сводная таблица после добавления в нее вычисляемого поля приобре­тет следующий вид (рис. 14).

Excel также позволяет создать для поля сводной таблицы вычисляемый элемент. Такой элемент использует содержимое других элементов в пределах поля сводной таблицы.

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

---