Использование средства «Консолидация»

Пусть нам необходимо получить отчет об остаточных количествах товаров на складе.

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

Для этого:

- подготовим шаблон следующего вида (рис. 5.15).

	I	J	K	L	M	N
		Наименование	Количество	Наименование	Количество	Остаток
		Лапти липовые		Лапти липовые
		Лапти дубовые		Лапти дубовые
		Галоши		Галоши
		Валенки		Валенки
		Кепка a ’la Lenin		Кепка a ’la Lenin
		Кепка a 'la Luzkov		Кепка a 'la Luzkov

Рис. 5.15. Шаблон отчета об остаточных количествах товаров на складе

- в колонки J и L скопируем с листа «Товары» список товаров;

- в ячейку N5 введем формулу =K5-M5, которую скопируем до строки 10;

- на листе «Поставки» диапазону E11:F311 присвоим имя “ Поставка»;

- на листе «Продажи» диапазону E11:F311 присвоим имя “ Продажа»;

- выполним команды:

Сервис > Макрос > Зададим имя макроса «Остатки» > Выделяем диапазон J4:K10 > Данные > Консолидация > В поле функция укажем «Сумма» > В поле список диапазонов укажем «Поставка» > Установим переключатели «Подписи верхней строки» и «Подписи верхнего столбца» > Ok > Выделяем диапазон L4:M10 > Данные > Консолидация > В поле функция укажем «Сумма» > В поле список диапазонов укажем «Продажа» > Установим переключатели «Подписи верхней строки» и «Подписи верхнего столбца» > Ok > Сервис > Макрос > Остановить запись

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

Sub Остатки()

Range("J4:K10").Select

Selection.Consolidate Sources:= _

"'E:\Магазин сувениров.xls'!Поставка", Function:=xlSum, _

TopRow:=True, LeftColumn:=True, CreateLinks:=False

Range("L4:M10").Select

Selection.Consolidate Sources:= _

"'E:\Магазин сувениров.xls'!Продажа", Function:=xlSum, _

TopRow:=True, LeftColumn:=True, CreateLinks:=False

Range("J4").Select

End Sub

- создадим кнопку «Остатки» и назначим ей только что созданный макрос;

- скроем столбцы K, L и M.

Окончательный интерфейс отчета может выглядеть следующим образом (рис. 5.16):

Рис. 5.16. Окончательный интерфейс отчета об остаточных количествах товаров на складе