Консолидация

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

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

Существует два основных метода консолидации данных – консолидация по расположению и консолидация по категориям (заголовкам).

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

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

Выполнение консолидации:

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

2. Меню Данные-Консолидация. При этом появляется окно, в которое необходимо ввести следующие данные и нажать <ОК>:

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

Замечание 2: адрес консолидируемого диапазона удобнее вводить указанием мыши, для этого перейти на нужный лист и обвести мышью нужный диапазон. Для занесения адреса в список диапазонов нажать кнопку Добавить. Ввод адреса и добавление его в список выполняется столько раз, сколько имеется консолидируемых списков. Если был введен неверный адрес диапазона, то для удаления его из списка нажать кнопку Удалить.

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

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

2 Практическая часть

Создайте на листе Ведомость таблицу (рис. 32).

Рис. 32

Зададим контроль значений для поля Код поставщика. Выделив контролируемые значения выберем команду меню Данные – Проверка и зададим значения как на рис. 33.

Рис. 33

После этого, невозможно будет ввести значение, кроме указанных.

Для того, чтобы можно было подставлять данные автоматически необходимо создать справочники (рис. 34)

Рис. 34

Для простоты дальнейшей работы присвоим имена справочникам. Для этого нужно выделить диапазоны с данными и выполнить команду Вставка – Имя – Присвоить (рис. 35). Набрав имя щелкнуть Добавить.

Рис. 35

Аналогично присвойте имя справочнику Товары.

Подставлять данные из справочников по коду или по имени товара можно с помощью функций ПРОСМОТР или ВПР (Вертикальный просмотр). Разберем подробнее обе функции. Основное требование для использования этих функций: данные в столбце по которому будет производиться поиск (в нашем случае это первые столбцы) должны быть отсортированы в порядке возрастания!

Н апример, подставим данные по поставщикам с использованием функции ПРОСМОТР. Установите курсор в первую ячейку столбца Поставщики и вставьте функцию ПРОСМОТР (рис. 36).

Рис. 36

Данная функция имеет два формата. Нам удобнее работать со вторым списком аргументов: искомое_значение; массив.

Задайте аргументы, как показано на рис. 37. Искомое значение (код поставщика в основной таблице – для 4 строки B4)– будет найдено в первом столбце указанного массива (Поставщики) и значение из второго столбца этого же массива.

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

Рис. 37

С использованием функции ПРОСМОТР возможно заполнить и столбец Цена, однако мы воспользуемся другой функцией – ВПР, которая имеет более широкие возможности. Установите курсор в первую ячейку столбца Цена и вставьте функцию ВПР (рис. 38).

Рис. 38

Аргументы функции:

Искомое значение - ячейка с наименованием товара, который надо найти в Таблице Товары.

Номер столбца – из него данные будут подставлены в поле Цена (в нашем случае – это 2-ой столбец справочника)

Интервальный просмотр – поставим 0.

Нажимаем ОК, и любым способом копируем функцию в другие ячейки поля Цена.

Принципиальным отличием функции ВПР является возможность подставлять данные из справочника, в котором больше, чем 2 столбца, например, справочник Поставщики мог содержать Адрес, р/счет и др. , а справочник Товары – артикул, страну-производитель и др.

Кроме того, если данные отсутствуют в справочнике, то функция ПРОСМОТР вернет значение из соседней строки, а это неверно. У функции ВПР аргумент Интервальный_просмотр = 0 позволяет избежать такой ситуации.

Создадим возможность задавать единицы измерения из выпадающего списка. Выберем пункт меню Данные – Проверка и зададим условия как на рис. 39.

Рис. 39

После этого заполните Единицы измерения значениями.

Заполните оставшиеся столбцы как на рис 40, в столбце Стоимость вставьте соответствующую формулу (Количество*Цена). С помощью Автосуммирования вычислите Итого.

Рис. 40

Для получения отчетов разной формы удобно использовать Сводные таблицы (Данные – Сводная таблица). Перетаскивая различные поля из Списка полей в Область строк и Область столбцов можно получать таблицы требуемой формы. (рис. 41). Заметим, что предварительно нужно выделить таблицу с заголовками столбцов, затем согласиться со всеми предложениями Мастера Сводной таблицы.

Рис. 41

Перетаскивая поле Товар в Область строк, поле Дата в Область столбцов, поле Сумма в Область элементов данных мы получим динамику получения различных товаров (рис. 42).

Рис. 42

Самостоятельно постройте таблицы следующего вида (каждую на отдельном листе):

• для поставщиков – суммы поставок по датам;

• для товаров – количество поставок по датам.