6. Консолидация данных в отчете сводной таблицы

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

Если данные вводятся в несколько листов-форм, основанных на одном, и при этом требуется объединить данные из форм на отдельном листе, следует воспользоваться мастером шаблонов с функцией автоматического сбора данных.

Для реализации консолидации данных в отчете сводной таблицы в табличном процессоре MS Excel выполните следующие действия:

1. Откройте книгу, в которой требуется создать отчет сводной диаграммы. Если отчет создается на основе списка MS Excel или базы данных, щелкните ячейку в этом списке или базе данных.

2. Выполните команду Вставка / Таблицы / Сводная таблица / Сводная таблица, как показано на рисунке.

3. В диалоговом окне Создание сводной таблицы выберите данные для анализа и нажмите ОК.

4. На новом листе появится макет сводной таблицы.

!!! Создайте на листе Сводную таблицу.

!!! Создайте несколько различных сводных таблиц на основе одного макета.

Просмотрите оборудование для лабораторной работы и порядок ее выполнения, перейдя по ссылке.

3. Вопросы для самоконтроля

1. Для каких целей используется функция консолидация?

2. Как могут располагаться подлежащие консолидации диапазоны ячеек?

3. Какие методы консолидации данных существуют?

4. В каком случае следует использовать Консолидацию по расположению?

5. В каком случае следует использовать консолидацию по категории?

6. В каком случае следует использовать отчет сводной таблицы?

7. Назовите существующие функции для обработки данных при их консолидации?

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

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

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

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

12. Продемонстрируйте консолидацию данных с учетом Заголовков столбцов.

13. Продемонстрируйте консолидацию данных по заработанной плате, рассмотренных в работе, за 2-3 последовательных месяца.

14. Продемонстрируйте консолидацию данных по НДФЛ, рассмотренных в работе, за 2-3 непоследовательных месяца.

15. Какие существуют правила ввода трехмерных ссылок в формулах консолидации данных на исходные области, находящиеся на том же листе?

16. Какие существуют правила ввода трехмерных ссылок в формулах консолидации данных на исходные области, находящиеся на разных листах?

17. Объедините данные, находящиеся в ячейке B3, начиная с Лист2 по Лист7, с помощью функции СУММ.

18. Как объединить данные с помощью функции СУММ, если данные, которые требуется объединить, находятся в разных ячейках на разных листах: Лист3!B4, Лист4!A7, Лист5!C5. Что необходимо ввести после ввода названия функции =СУММ()?

19. Какие существуют правила ввода трехмерных ссылок в формулах консолидации данных на исходные области, находящиеся в разных книгах?

20. Что нужно ввести, чтобы включить в формулу консолидации диапазон «Зарплата» с листа «Январь» книги «Заработная плата 2002 год», которая находится в папке «Отчетность», расположенной на диске С:\?

21. Что нужно выполнить, чтобы задать описание источника данных, не нажимая клавиш клавиатуры?

22. Что нужно выполнить, чтобы автоматически обновлять итоговую таблицу при изменении источников данных?

РЕШЕНИЕ УРАВНЕНИЙ В EXCEL

1. Цель и задачи лабораторной работы

Освоение функции Подбор параметра, входящей в состав MS Excel.

2. Теоретические сведения

   1. Основные понятия

Функция Подбор параметра может быть использована в следующих случаях:

А) нахождение значения аргумента (параметра) функции, соответствующего определённому значению функции (в том числе 0);

Б) нахождение значений аргумента (параметра) функции при изменении вида её графика (метод реализован только в Excel 2003 и более ранних версиях, в Excel 2007/2010 данный метод не поддерживается).

А) Использование функции Подбор параметра для нахождения значения аргумента функции, соответствующего заданному значению функции.

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

Например, одна ячейка содержит формулу, в которой есть ссылки на другую ячейку.

Значение в ячейке С1 представляет собой среднее арифметическое значение в ячейках А1 и В1:

Допустим, что для целей исследования необходимо найти значение, которое должна принять ячейка А1, для того чтобы ячейка С1 приняла значение 855.

Безусловно, можно самостоятельно путём перебора значений в ячейке А1 достичь необходимый результат. Однако, в целях минимизации затрат времени следует воспользоваться функцией Подбор параметра. Для этого необходимо:

1. о ткрыть вкладку Данные, в группе Работа с данными, открыть команду Анализ «что если» и выбрать команду Подбор параметра.

В результате появится запрос Подбор параметра:

!!! Выполните команду Подбор параметра

2. в поле Установить в ячейке ввести ссылку или имя ячейки, содержащую формулу, для которой следует подобрать параметр. Автоматически в поле Установить в ячейке отображается имя ячейки, которая была активной на момент выполнения команды Подбор параметра. Кнопка свёртывания окна диалога , расположенная справа от поля, позволяет временно убрать диалоговое окно с экрана, чтобы было удобнее выделить диапазон на листе. Выделив диапазон, следует нажать кнопку для вывода на экран диалогового окна.

3. в поле Значение ввести число, которое должно возвращать формула с искомым значением параметра. Например, 855.

4. в поле Изменяя значение ячейки указать ссылку на ячейку, содержащую параметр, значение которого требуется подобрать для получения требуемого результата. На эту ячейку прямо или косвенно должна ссылаться формула, содержащаяся в ячейке, адрес которой указан в поле Установить в ячейке. В нашем случае это А1.

5. нажать кнопку ОК для закрытия диалогового окна. После выполнения этого действия появляется запрос Результат подбора параметра, а искомое значение параметра отображается в ячейке А1.

!!! Получите результаты подбора параметра

Б) Использование функции Подбор параметра для нахождения значения аргумента функции при изменении вида ее графика (метод реализован только в Excel 2003 и более ранних версиях, в Excel 2007/2010 данный метод не поддерживается).

Допустим, что для решения поставленной задачи нам предстоит проанализировать построенный в MS Excel график функции y = 2*x-2 в диапазоне аргумента от –3 до 6.

Для этого следует:

1) в ячейки А1-А10 ввести значения от –3 до 6 с шагом 1; в ячейку В1 – ввести формулу 2*А1-2 и путём перетаскивания маркера заполнения скопировать эту формулу на ячейки В2-В10. В результате соответствующий участок листа примет следующий вид:

!!! Добавьте на лист указанные данные

2. выделив диапазон В1-В10, нажать кнопку Мастер диаграмм на Стандартной панели инструментов.

3. на первом шаге работы Мастера диаграмм выбрать тип диаграммы График;

4. на втором шаге работы Мастера диаграмм на вкладке Ряд в поле Подписи по оси Х задать диапазон А1-А10.

5) последовательными нажатиями кнопки Далее и Готово закончите работу Мастера.

В результате должен быть построен график функции:

!!! Постройте требуемый график

Далее предположим, что необходимо узнать значение аргумента данной функции, при котором значение самой функции будет равно 0.

Чтобы решить эту задачу с помощью построенного графика и функции Подбор параметра необходимо:

1. щелчком левой кнопки мыши на графике выделить ряд данных, содержащий маркер данных, который нужно изменить,

а затем выделить щелчком сам маркер

2. перетащить маркер до уровня, соответствующего требуемому значению функции:

3. если значение маркера данных получено из формулы, появится диалоговое окно Подбор параметра:

в поле Установить в ячейке отображается ссылка на ячейку, содержащую формулу, в поле Значение — требуемая величина

4. в поле Изменяя значение ячейки выбрать ячейку, значение которой нужно изменить (А6) и нажать кнопку ОК.

При подборе можно изменять только одну ячейку.

При этом исходное значение аргумента в ряде данных сменится на значение, полученное в результате подбора параметра:

!!! Произведите требуемые манипуляции и получите итоговые результаты по подбору параметра