Занятие 9

Работа с несколькими таблицами

• Организация связи между таблицами.

• Поиск влияющих ячеек на других листах.

• Выполнение консолидации данных.

• Создание сводной таблицы с помощью мастера.

• Редактирование сводной таблицы.

Связь между таблицами

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

Лист6!$С$7

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

'С:\ЕХСЕL\[ТАВL]Лист6’!$С$7

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

Как организовать связь между таблицами

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

Для того чтобы связать данные, нужно:

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

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

• Выбрать в меню Правка команду Копировать.

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

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

• Выбрать в меню Правка команду Специальная вставка.

Занятие 9

186

Работа с несколькими таблицами

На экране появится диалоговое окно.

• Установить в группе Вставить флажок опции все.

• Установить в группе Операция флажок опции нет.

• Щелкнуть кнопку Вставить связь.

Задание 1.

Откройте книгу exampI8.xls. Введите в листе Калькуляция цену из листа Прейскурант, организовывая связь между листами.

Выполнение задания.

1. Откройте книгу exampl8.xls.

2. Посмотрите на листе Калькуляция, какие данные нужно взять из листа Прейскурант.

3. Перейдите на лист Прейскурант.

4. Поместите курсор на цену 1 литра молока.

5. Выберите в меню Правка команду Копировать.

6. Перейдите на лист Калькуляция.

7. Поместите курсор в ту ячейку, где должна быть цена.

8. Выберите в меню Правка команду Специальная вставка. На экране появится диалоговое окно.

9. Установите в группе Вставить флажок опции все.

10. Установите в группе Операция флажок опции нет.

11. Щелкните кнопку Вставить связь.

12. Посмотрите, какая ссылка ввелась в ячейку с ценой.

Занятие 9

187

Работа с несколькими таблицами

Поиск влияющих и зависимых ячеек на другом листе

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

• Выбрать ячейку, для которой надо найти влияющие или зависимые ячейки.

• Щелкнуть кнопку Влияющие ячейки

или

Зависимые ячейки. Появится черная пунктирная стрелка. В начале стрелки

есть значок Щ

• Дважды щелкнуть стрелку. Появится окно Переход:

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

Задание 2.

Откройте книгу Гранд.хls, Лист5. Отразите стрелку зависимостей к ячейке В5 и перейдите к влияющей ячейке.

Выполнение задания:

1. Выделите ячейку В5.

2. Щелкните кнопку Влияющие ячейки • направленная к выбранной ячейке черная со значком

Появится пунктирная стрелка

3. Дважды щелкните стрелку. Появится окно Переход.

4. Выберите ссылку в поле Перейти к:.

5. Щелкните кнопку ОК. Произойдет переход на Лист4, выделится ячейка С5.

Занятие 9

188

Работа с несколькими таблицами

Задание 3.

Самостоятельно отразите стрелку зависимостей от ячейки С5 и перейдите к зависимой ячейке.

Консолидация рабочих листов

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

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

Итоговая таблица может располагаться в любой книге на любом рабочем листе.

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

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

Занятие 9

189

Работа с несколькими таблицами

Для того чтобы выполнить консолидацию, нужно:

• Выделить ячейки, где будет размещена итоговая таблица. Размер выделяемой области должен быть достаточным, иначе не все результаты консолидации будут включены в итоговую таблицу.

• Выбрать в меню Данные команду Консолидация. На экране появится диалоговое окно:

• В раскрывающемся списке Функция выбрать функцию консолидации. По умолчанию используется функция суммы.

• В поле Ссылка определить области объединяемых данных. Для этого:

— открыть нужный лист с данными;

— выделить область с данными, включая в выделение заголовки данных;

— щелкнуть кнопку Добавить.

Выделенный интервал занесется в поле Список диапазонов:.

• Повторить предыдущий пункт для следующих объединяемых таблиц.

• В области Использовать в качестве имен установить флажок проверки:

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

Если нужно, то можно установить оба флажка.

• Установить флажок проверки Создавать связи с исходными данными,

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

Связь не устанавливается, если объединяемые таблицы и итоговая таблица расположены на одном и том же листе.

• Щелкнуть кнопку ОК.

При любых изменениях исходных обновление данных в итоговой таблице.

данных происходит автоматическое

Занятие 9

190

Работа с несколькими таблицами

Задание 4.

Перейдите в окно с документом exampl8.xls. Подсчитайте итоговые данные по первому разделу балансов двух филиалов. Расчеты произведите, используя консолидацию данных. Данные баланса находятся на листах Филиал №1 и Филиал №2. Результаты разместить на листе Баланс.

Выполнение задания.

1. Перейдите на лист Баланс.

2. Выделите область для итоговой таблицы А5:С11.

3. Выберите в меню Данные команду Консолидация. На экране появится диалоговое окно.

4. В раскрывающемся списке Функция выберите функцию Сумма.

5. Перейдите на лист Филиал №1.

6. Выделите интервал с данными первого раздела А5:С11.

7. Щелкните мышью в диалоговом окне по кнопке Добавить.

8. Перейдите на лист Филиал №2.

9. Выделите интервал с данными первого раздела А5:С11.

10. Щелкните мышью по кнопке Добавить.

11. В области Использовать в качестве имен установите флажки проверки подписи верхней строки и значения левого столбца.

12. Установите флажок проверки Создавать связи с исходными данными.

13. Щелкните кнопку ОК.

14. Посмотрите на итоги и сравните их с исходными данными.

Сводная таблица

Сводная таблица предназначена для анализа данных таблицы большого объема. Для создания сводной таблицы необходимо определить:

— какие данные исходной таблицы будут метками строк сводной таблицы;

— какие данные исходной таблицы будут метками столбцов сводной таблицы;

— какие данные исходной таблицы будут анализироваться в сводной таблице. Данные анализируются относительно меток строк и столбцов сводной таблицы. Данные с одинаковыми метками суммируются.

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

Занятие 9

191

Работа с несколькими таблицами

поля, используемого в качестве столбца, не будет включена в сводную

таблицу.

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

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

В данном примере метками строк сводной таблицы являются элементы столбца Наименование исходной таблицы. Метками столбцов сводной таблицы являются элементы столбца Отправка исходной таблицы.

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

192

Занятие 9

Работа с несколькими таблицами

Как создать сводную таблицу

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

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

• Открыть рабочую книгу с данными, на основе которых будет строиться сводная таблица.

• Выбрать в меню Данные команду Сводная Таблица. На экране появится диалоговое окно Шага 1:

• Выбрать источник исходных данных:

— Список или база данных Microsoft Excel;

— Внешняя база данных, созданная в другом приложении;

— Несколько интервалов консолидации Microsoft Excel;

— Другая сводная таблица этой же рабочей книги.

• Щелкнуть кнопку Далее

Занятие 9

193

Работа с несколькими таблицами

На экране появится диалоговое окно Шага 2:

• Перейти на лист, где расположены исходные данные.

• Выделить интервал данных, на основе которых будет строиться сводная таблица.

• Щелкнуть кнопку Далее. На экране появится диалоговое окно ШагаЗ

Щелкнуть кнопку Макет... На экране появится диалоговое окно:

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

Занятие 9

194

Работа с несколькими таблицами

• Щелкнуть кнопку ОК. Осуществится возврат в диалоговое окно Шага 3.

• Установить нужные параметры, щелкнув кнопку Параметры ... Закончить

установку параметров, щелкнув ОК.

• Выбрать флажок опции новый лист или существующий лист в группе

Поместить таблицу в: .

• Щелкнуть кнопку Готово. Вставится сводная таблица. На листе сводной

таблицы появится панель инструментов Сводные таблицы:

Задание 5.

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

Выполнение задания.

1. Перейдите на лист Школьник.

2. Выберите в меню Данные команду Cводная Таблица. На экране появится диалоговое окно Шага 1.

3. Включите опцию В списке или базе данных Microsoft Excel.

4. Щелкните кнопку Далее. Появится окно Шага 2.

5. Выделите на листе Школьник диапазон ячеек АЗ:Е21.

6. Щелкните кнопку Далее. Появится окно Шага 3.

7. Щелкните кнопку Макет.

8. Перетащите на макет мышью метку поля Дата поставки в область страница, метку поля Наименование в область Строка, метки полей Поставщик и Размер в область столбец, метку поля Количество в область данные.

9. Щелкните кнопку ОК. На экран вернется диалоговое окно Шага 3 с

установленным ключом новый лист. Ю.Щелкните кнопку Готово. Вставится новый лист 1 со сводной таблицей.

Занятие 9

195

Работа с несколькими таблицами

Вывод данных по страницам

Для просмотра сводной таблицы для одного элемента поля страницы нужно:

• Щелкнуть мышью по стрелке, расположенной справа от имени столбца таблицы, используемого в качестве поля страницы. На экране появится раскрывающийся список.

• Выбрать из списка нужный элемент и щелкнуть кнопку ОК.

Задание 6.

Выведите в сводной таблице данные на 15.08.01.

Выполнение задания.

1. Щелкните мышью по кнопке раскрывающегося списка, расположенной справа от поля страницы Дата поставки.

2. Выберите из списка дату 15.08.01.