Задание параметров для диалогового окна «Консолидация»

В поле «Функция» укажите функцию Сумма, которая показывает тип объединения данных.

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

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

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

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

Построить требуемую диаграмму.

Задание 2.

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

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

=[Имя_рабочей_книги]Имя_листа!Адрес_ячейки

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

=’[Бюджет на 2001 год]Лист1’!A1

Если рабочая книга закрыта и не находится в текущей папке, то в ссылке необходимо указать полный путь к этой рабочей книге с помощью команд Вставить / Специальная вставка. Этот метод применим, если все используемые рабочие таблицы открыты. Недостатком этого метода является то, что консолидация получается нединамической (статическая консолидация). Скопируйте данные из первого диапазона исходной рабочей таблицы в буфер обмена. Активизируйте зависимую рабочую книгу и выберите ячейку, в которую нужно поместить консолидированные данные. Выполните команду Вставить / Специальная вставка, отметьте переключатель сложить и щелкните по кнопке ОК. Выполните эти действия для всех диапазонов рабочих таблиц, которые должны быть консолидированы с помощью команд Данные / Консолидация.

Лабораторная работа № 3.8СВОДНЫЕ ТАБЛИЦЫ

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

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

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

Создайте базу данных, по которой будет составлен отчет (рис. 1).

Рис. 1.

А теперь создадим структуру отчета.

1. Скопируйте столбец Товар в пустую область рабочего листа.

2. Воспользуйтесь командой Данные-Удалить дубликаты (Data-Remove Duplicates), чтобы удалить дубликаты значений в ячейках.

3. Удалите заголовок Товар.

4. Скопируйте уникальный список товаров и выполните команду Специальная вставка-Транспонировать (Paste Special-Transpose), чтобы повернуть список на 90°.

5. Удалите вертикальный список товаров.

6. Скопируйте столбец Регион в пустую область рабочего листа.

7. Воспользуйтесь командой Данные-Удалить дубликаты (Data-Remove Duplicates), чтобы удалить дубликаты значений в ячейках.

8. Удалите заголовок Регион.

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

С помощью функции СУММЕСЛИМН (SUMIFS) просуммируем доход в ячейках, находящихся на пересечении строки товара и столбца региона.

На основе этой функции создается формула =СУММЕСЛИМН($G$2:$G$20;$C$2:$C$20;L$1;$B$2:$B$20;$K2), выполняющая вышеописанные действия (рис. 2).

Рис. 2.

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

В строку и столбец итогов введите заголовок Итого.

Сделайте подсчет итогов.

В результате выполнения всех этих действий должен получиться суммарный отчет (рис. 3.

Рис. 3.

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

· Можно ли отобразить названия товаров слева, а названия регионов — вверху?

· Можно ли создать отчет для клиентов из производственного сектора?

· Можно ли показать прибыль, а не общую выручку?

· Можно ли скопировать отчет для каждого из заказчиков?