Создание вычисляемого элемента

Процедуру создания вычисляемого элемента проведем на примере сводной таблицы из части списка БД_Банк, показанного в таблице.

1. Создать сводную таблицу.Строка – клиент,столбец – тип,данные – отделение.

2. Поместитькурсор в область строки, столбца или страницыСТ(для текущегопримерапоместим курсорв столбец).

3. На панели инструментов Сводные таблицы в спискевыбрать команду Формулы|Вычисляемый объект.

4. В диалоговом окне Вставка вычисляемого элемента ввести имя нового элемента и формулу. В формуле могут быть использованы элементы из другихполей, но ни в коем случае не функции р/л. В данном примере новый элемент назвать Депозит & Срочный, а формула должна иметь вид: =Депозит + Срочный.

5. Щелкнуть кнопку Добавить.

6. Для создания других вычисляемых элементов следует повторить пп.3–5. Щелкнуть OK.

Примечания.

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

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

Переименование полей и элементов. Использовать имена полей и элементов, назначенные Мастером, не обязательно. Эти имена можно изменить, отредактировав заголовок поля или элемента в СТ (два щелчка на заголовке). При изменении заголовка поля в СТ его новое имя не должно совпадать ни с одним именем другого поля в источнике данных. Это влечет за собой непредсказуемые последствия.

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

1. Не отображать в СТ промежуточные и общие итоги, так как это разорвет диапазон данных.

2. Не использовать более двух элементов для полей строки или столбца.

3. Перед созданием диаграммы полностью выделить СТ, за исключением поля страницы.

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

Создание консолидированной СТ выполняется по следующему алгоритму. Используются листы с именами Семестр1 – Семестр4 из лабораторной работы 7.

1. Выполнить команду Данные|Сводная таблица. В диалоговом окне Мастер … на шаге 1 установить переключатель В нескольких диапазонах консолидации и нажать клавишу Далее.

2. В диалоговом окне Мастер … на шаге 2а оставить установленным переключатель Создать одно поле страницы (устанавливается по умолчанию) и нажать клавишу Далее.

3. В диалоговом окне Мастер … на шаге 2б указать первый исходный диапазон (Семестр1) и нажать клавишу Добавить. При задании диапазонов не следует включать в них итоговые строки и столбцы, в которых вычисляются суммы или среднее (в частности, в диапазоне выделить все, кроме итоговых столбцов).

4. Повторить п. 3 для каждого исходного диапазона. После задания всех исходных диапазонов нажать клавишу Далее.

5. В диалоговом окне Мастер … на шаге 3 указать место для размещения сводной таблицы и нажать кнопку Готово.

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

Построение консолидированной СТ с созданием полей страницы. Рассмотрим пример с установкой переключателя Создать поля страницы на шаге 2а Мастера …. Для этого создадим листы с именами Кв1_07 - Кв4_07 и Кв1_08 - Кв4_08 соднотипной, но различной по объемуинформацией (на р/л должно быть заполнено разное количество строк). Примерный вид информации показан в таблице. В консолидированную СТ необходимо включить два поля страницы - год и квартал.

1. Выбрать команду Данные|Сводная таблица. В диалоговом окне Мастер … на шаге 1 установить флажок В нескольких диапазонах консолидации и нажать клавишу Далее.

2. В диалоговом окне Мастер … на шаге 2а установить переключатель Создать поля страницы и нажать клавишу Далее.

3. В диалоговом окне Мастер … на шаге 2б указать первый исходный диапазон консолидации (Кв1_07) и нажать клавишу Добавить. При задании диапазонов не следует включать в них итоговые строки и столбцы, в которых вычисляются суммы или среднее.

4. Повторить шаг 3 для каждого исходного диапазона.

5. После задания всех исходных диапазонов установить переключатель 2 для создания двух полей страницы.

6. Выделить первый диапазон в списке Список диапазонов, а затем ввести 2007 в поле Первое поле и Кв1 - в поле Второе поле.

7. Повторить эти действия для каждого диапазона, заданного в списке Список диапазонов, и нажать Далее.

8. В диалоговом окне Мастер … на шаге 3 указать место для размещения сводной таблицы и нажать кнопку Готово.

9. Изменение полей СТ. Доступ к опциям поля в диалоговом окне Вычисление поля сводной таблицы – два щелчка на кнопке поля или команда Параметры поля контекстного меню.

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

Задание 2. Создание макросов.

Методические указания. Существуют два способа создания макросов. Можно автоматически записать последовательность действий пользователя или вручную ввести инструкции на лист, называемый модулем. Для создания инструкций используется язык программирования MS Visual Basic for Application (VBA). Этот язык широко применяется в приложениях Windows.

Рассмотрим создание макроса на примере решения задачи "что–если" с одним параметром в автоматическом режиме. Excel может создать макрос, записывая действия пользователя по выбору команд меню, нажатию клавиш и т. д., необходимые для выполнения задачи. После записи последовательности действий макрос можно запускать всякий раз, когда нужно выполнить задачу снова.

Прежде, чем начать запись макроса, следует перейти на р/л Что_если, выполнить команду Сервис|Параметры|карточка Правка и снять флажок с опции Переход к другой ячейке после ввода ….

Процесс записи макроса состоит из следующих шагов.

1. Выполнить команду Сервис|Макрос|Начать запись.

2. Назначить имя макросу, например Платеж1.

3. Назначить макросу комбинацию клавиш, введя в поле Сочетание клавиш букву, например, латинскую прописную P.

4. Сохранить макрос в текущей книге, выбрав в списке Сохранить пункт Эта книга. OK.

5. На экране появится панель Остановка записи с кнопкой Остановить запись.

6. Выделить ячейку C11 и ввести формулу =ПЛТ(D5/12;D6;D7).

7. Выделить ячейку D11 и ввести формулу = - C11*D6 - D7.

8. Выделить интервал для таблицы данных - наименьший прямоугольный блок, содержащий формулы и все значения из входного интервала, – В11:D16.

9. Выполнить команду Данные|Таблица подстановки. В диалоговом окне Таблица подстановки выбрать Подставлять значения по строкам в: и ввести D5.

10. OK.

Нажать кнопку Остановить запись на панели инструментов Остановка записи.

Для проверки работы макроса очистить лист от выполненных вычислений и нажать клавиши Ctrl+Shift+P. Excel запустит макрос и выполнит все действия в той же последовательности, в которой они были записаны.

Для просмотра созданной программы нужно выполнить команду Сервис|Макрос|Макросы, в диалоговом окне Макросы выделить имя созданного макроса и нажать кнопку Войти или Изменить.

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

1. Что такое pivot table, и для чего они служат?

2. Что нужно сделать, чтобы создать сводную таблицу?

3. Как производится изменение структуры сводной таблицы?

4. Можно ли вносить изменения в сводную таблицу?

5. Как удалить поле сводной таблицы?

6. Как изменить вычисляемую функцию?

7. Как применить автоформат к сводной таблице?

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

9. Как производится группировка элементов сводной таблицы?

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

11. Как выполняется вычисление соотношений между элементами?

12. Как заменить функцию, вычисляющую промежуточные итоги, или применить несколько функций?

13. Как удалить промежуточные итоги?

14. Как выполняется генерация групповых итогов?

15. Как создать вычисляемое поле в сводной таблице?

16. Как вставить вычисляемый элемент в сводную таблицу?

17. Какими рекомендациями следует руководствоваться при создании диаграммы на основе сводной таблицы?

18. Как выполняется создание консолидированной сводной таблицы с одним полем страницы?

19. Как выполняется построение консолидированной сводной таблицы с несколькими полями страницы?

20. Что такое макросы и для чего они используются?

21. Какие существуют способы создания макросов?

22. Как создать макрос для решения задачи Платеж1?

Поиск по сайту: