Вставка и перемещение рабочих листов

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

Для выполняемой работы требуется рабочая книга из 5 рабочих листов.

Упражнение. К существующим рабочим листам «Прайс-лист», «Техношок» и «Техносила» необходимо добавить 2 новых листа – «Итог» и «Выручка», для чего:

· Нажать ярлычок Вставить лист, расположенный после листа с наименованием «Техносила».

· Аналогично вставить еще 1 рабочий лист.

Таким образом, в рабочей книге сейчас открыто 5 листов.

· Переименовать вставленные листы соответственно в «Итог» и «Выручка».

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

Создание итоговых таблиц

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

Структура итоговой таблицы имеет следующий вид:

Рис.9

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

· Перейти на лист «Итог».

· В ячейку А1 ввести заголовок таблицы в соответствии с рисунком 8.

· Скопировать с листа «Техносила» во вторую строку текущей таблицы шапку таблицы.

· Удалить столбцы В, С, D, H. Для этого сначала нужно выделить столбец В, щелкнув левой кнопкой мыши на его идентификаторе, а затем нажать клавишу Ctrl и, удерживая ее нажатой, щелкнуть указателем мыши идентификаторы остальных столбцов – С, D и H (так выделяются несмежные блоки ячеек). Нажать правую кнопку мыши и в контекстном меню выбрать команду Удалить.

· Скопировать в первую графу создаваемой итоговой таблицы блок ячеек А3:А11 с листа «Техносила».

Объединение и связывание нескольких электронных таблиц

EXCEL дает возможность создания итоговых или сводных таблиц различными способами. Можно просто суммировать данные рабочих листов и помещать результаты на итоговый лист, можно использовать кнопку Консолидация из группы Работа с данными на вкладке Данные, которая может объединить информацию из исходных листов в одном итоговом листе.

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

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

Консолидировать данные можно по расположению или по категории. При консолидации по расположению Excel применяет итоговую функцию к ячейкам с одинаковыми адресами в каждом исходном листе. Это простейший способ консолидации, при котором консолидируемые данные во всех исходных листах должны иметь совершенно одинаковое расположение. Консолидация по категории в качестве основы для объединения использует заголовки строк или столбцов.

Итоговые таблицы без использования связей с исходными данными

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

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

· Выделить диапазон ячеек В3:В10, т.е. выделить область назначения, куда будут помещены консолидированные данные.

· Активизировать вкладку Данные.

· Нажать кнопку Консолидация в группе Работа с данными.

· В окне Функция из появившегося списка выбрать Сумма.

· Установить курсор в окно Ссылка.

· Щелкнуть по листу «Техношок».

· Выделить на листе «Техношок» диапазон ячеек Е3:Е10 (графа «Количество»), обратив внимание, что вокруг выделенного блока появилась двигающаяся рамка.

· Щелкнуть по кнопке Добавить в окне Консолидация..

· Перейти на лист «Техносила».

· Выделить на листе «Техносила» диапазон Е3:Е10.

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

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

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

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

Графа таблицы «Количество» заполнилась рассчитанными данными. Если установить курсор на любую ячейку этого диапазона, то в строке ввода вы не увидите формулы, по которой Excel рассчитывал итоги, так как при таком способе консолидации в итоговом листе бывает получен ряд констант, и последующие изменения в исходных листах не оказывают никакого воздействия на итоговый лист.

· Выполнить аналогичные действия по консолидации данных для графы «Сумма». При повторной консолидации в пределах одного и того же рабочего листа необходимо сначала удалить имеющиеся ссылки и диапазоны ячеек, выделяя их в окне Консолидация и щелкая по кнопке Удалить.

Если графы как в итоговой таблице, так и в исходных таблицах расположены последовательно, консолидировать эти данные можно единым блоком.

Итоговые таблицы, полученные методом суммирования

Для консолидации данных о НДС можно воспользоваться методом суммирования.

Упражнение. Консолидировать данные методом суммирования. Для этого необходимо:

· Проверить, что вы работаете с листом «Итог».

· Установить курсор в ячейку D3 на листе «Итог».

· Щелкнуть в строке формул по кнопке Вставка функции.

· В диалоговом окне Мастер функций в списке категорий выбрать Математические, а в списке функций – СУММ.

· В окне СУММ установить курсор в поле Число 1 (очистить поле, если в нем введены какие-либо данные, нажав клавишу Delete) и перейти на лист «Техношок» в ячейку G3.

· Перейти в поле Число 2 и в нем отразить ячейку G3 с листа «Техносила».

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

Если установить курсор в ячейку D3 листа «Итог», то в строке ввода можно увидеть формулу, по которой рассчитывалась итоговая сумма за квартал:

=СУММ(Техношок!G3;Техносила!G3).

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

· Скопировать полученную формулу из ячейки D3 в ячейки D4:D10, используя метод копирования с помощью маркера заполнения.

· В ячейках С11 и D11 подсчитать итоговые суммы с помощью пиктограммы Автосумма.

Результат выполненных действий представлен на рисунке 10.

Рис.10

Итоговые таблицы с использованием связей с исходными данными

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

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

· Скопировать с листа «Итог» заголовок таблицы на лист «Выручка» (ячейка А1).

· Скопировать с листа «Техносила» название первой и последней графы (графа «Наименование товара» и «Выручка») на лист «Выручка» (в ячейки А2:В2).

· Скопировать содержимое блока ячеек А3:А11 с листа «Итог» в соответствующие ячейки Листа «Выручка».

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

Рис. 11

Обратите внимание на полученную таблицу: ее результаты такие же, как и при расчете предыдущей итоговой таблицы. Однако изменился вид экрана: в его левой вертикальной части появились символы структуры документа и некоторые строки стали невидимыми (это строки 3, 4, 7, 9, 10, 12, 13 и т.д.). Структурирование данных рабочих листов используется в Excel при создании итоговых отчетов, позволяя показывать или скрывать уровни структурированных данных с большими или меньшими подробностями.

В полученной таблице видны символы структуры – это кнопки с номерами уровней 1 и 2, знаки + (плюс) и/или – (минус), позволяющие соответственно скрывать или раскрывать детали структурированного документа.

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

· Щелкнуть по кнопке 2. При этом таблица «распахнулась», предоставив возможность просмотреть консолидируемые данные по двум магазинам.

· Щелкнуть по кнопке 1. При этом скроются исходные данные из таблиц источников.

· Щелкнуть по любому из значков «+» (плюс). При этом откроется список составляющих величин итогового значения.

· Щелкнуть по значку «-» (минус). При этом скроются исходные данные из таблиц источников.

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

Дата добавления: 2014-01-04