Темы статей

Выбрать темы

Налог на прибыль НДС РРО ЕСВ ФЛП Увольнение Штрафы НДФЛ Отпуск Единый налог Больничные Все темы

Excel 2007: консолидация данных

Редакция БК

Бухгалтер&Компьютер Март, 2010/№ 6

Статья

Приєднуйтесь до Telegram спільноти та дізнавайтесь найважливіші новини першими!

Работая с базами данных Excel, мы до сих пор двигались от детальной информации к ее обобщению. Режим автофильтра, подведения итогов — все они в качестве исходных данных используют подробную базу, а в качестве результатов возвращают итоговые значения или локальную выборку записей по заданным критериям. То есть так или иначе эти инструменты решают похожие задачи: из развернутого, подробного списка формируют компактный итоговый отчет. Однако в практике бухгалтерского учета бывает и другая ситуация: когда нужно свести несколько разрозненных таблиц в один обобщенный отчет, объединив в нем данные в одноименных строках и колонках. Для больших таблиц сделать такую операцию вручную очень трудно. Работа с несколькими таблицами всегда утомляет и чревата множеством ошибок. К счастью, в Excel 2007 есть простое решение этой проблемы — инструмент консолидации данных. Разобраться с ним и будет целью этой статьи.

Юрий ЦЫГАНОК, главный редактор
Николай КАРПЕНКО,канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства

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

Способы консолидации

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

1) консолидация с помощью формул. Здесь имеется в виду ручной способ консолидирования таблиц. В этом случае расчет итоговых значений выполняется по формулам с трехмерными ссылками, т. е. ссылками на диапазоны и ячейки с разных листов. Такой способ применяют, когда исходные таблицы не обладают общей структурой, заголовки у них разные, данные расположены бессистемно. Применяют этот метод не потому, что он хорош, — просто ничего другого не остается;

2) консолидация по расположению ячеек. В данном случае предполагается, что исходные данные на всех листах имеют совершенно одинаковую структуру. Например, если на рабочем листе «

Кв1» в ячейке «A10» записан дебетовый оборот по счету 281 за первый квартал, то и в таблице для второго квартала «Кв2» этот оборот тоже находится в ячейке «A10». Собрать такие данные в общий отчет можно автоматически, через инструмент консолидации: вызываем функцию объединения, указываем диапазоны данных, выбираем операцию, остальное Excel сделает сам. Этот способ объединения чем-то напоминает операцию сложения матриц, причем матрицами являются листы рабочей книги. В бухгалтерской практике такой метод консолидации применяют редко. Более распространен третий вариант;

3) консолидация по заголовкам строк или столбцов. Здесь данные для объединения идентифицируются не адресом ячейки, а заголовками строк и (или) колонок. Это означает, что исходная информация на рабочих листах может быть расположена произвольным образом. Например, дебетовый оборот по счету 281 за первый квартал — в ячейке «

A10» листа «Кв1», а тот же оборот по второму кварталу — в ячейке «A15» листа «Кв2». Свободы при объединении в этом случае гораздо больше. Да и сам метод нагляднее. Рассмотрим работу со всеми упомянутыми вариантами консолидации на практике.

Исходные данные для примера

Чтобы разобраться с инструментами консолидации, нам понадобится какой-то пример. Мы выбрали в качестве такого примера задачу формирования оборотного баланса производственного объединения. Информация эта получена из отчетности конкретного предприятия. Правда, часть данных откорректирована (упрощена для большей наглядности), и по понятным причинам изменено название предприятия.

Итак, в зоне нашего внимания производственное объединение «Завод им. Большакова». Предприятие выпускает мини-технику сельскохозяйственного назначения (мотоплуги, минитракторы и т. п.). В состав объединения входят три производственных подразделения, не являющиеся юридическими лицами: литейный завод, дизельный (моторостроительный) завод, механосборочный (машиностроительный) завод. Это означает, что каждое подразделение самостоятельно ведет бухгалтерский учет и ежемесячно сдает в заводоуправление свой оборотный баланс. В заводоуправлении посредством консолидации составляется оборотный баланс объединения в целом.

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

683», на котором для каждого подразделения, а также для заводоуправления открыты отдельные субсчета, а именно:

— «

6830» — заводоуправление (ЗУ);

— «

6831» — литейный завод (ЛЗ), выпускающий детали для двигателей и литье под заказ;

— «

6832» — моторостроительный завод (МЗ), выпускающий двигатели для собственной продукции и на продажу;

— «

6833» — машиностроительный завод (МСЗ), выпускающий готовую продукцию производственного объединения.

Например, заготовки для литья, приобретенные заводоуправлением и оприходованные на субсчет «

201», оно передает литейному заводу с распределительного склада проводкой «Дт 6831 — Кт 201». Литейный завод, в свою очередь, отражает получение заготовок на свой склад проводкой «Дт 201 — Кт 6830».

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

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

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

Итак, принцип организации бухгалтерского учета на производственном объединении «Завод им. Большакова» мы изложили. Пора перейти к практике и продемонстрировать сам процесс выполнения консолидации. Для этого на отдельных листах рабочей книги разместим оборотные балансы всех производственных подразделений и заводоуправления, чтобы провести их консолидацию средствами Excel 2007. Рабочие листы назовем по сокращенным названиям подразделений: «

ЗУ», «ЛЗ», «МЗ», «МСЗ».

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

20», «31» и т. д.), за исключением счета «68», в котором выделим субсчет внутрихозяйственных расчетов «683». Такое ограничение непринципиально: уловив принцип проведения консолидации в Excel, вы без труда распространите его на свои реальные данные.

Начнем с заводоуправления. Его оборотный баланс, в котором есть все счета нашего условного рабочего плана счетов, показан на рис. 1. С целью экономии места для остальных подразделений мы не будем перечислять все счета, а ограничимся только теми, которые реально используется (табл. 1, 2, 3). Баланс каждого подразделения мы разместим на отдельном листе начиная с ячейки «

A1». Наша задача — построить сводный оборотный баланс всего производственного объединения.

Таблица 1

Оборотный баланс по подразделению «Литейный завод»

Счет	Наименование счета	Сальдо на 01.04.2009		Обороты, апрель 2009		Сальдо на 30.04.2009
Счет	Наименование счета	Дт	Кт	Дт	Кт	Дт	Кт
1	2	3	4	5	6	7	8
10	Основные средства	80000000				80000000
11	Прочие необоротные материальные активы	7650000				7650000
13	Износ необоротных активов		23600000		701300		24301300
15	Капитальные инвестиции
20	Производственные запасы	978600		48000	495200	531400
22	Малоценные и быстроизнашиваемые предметы	244400		13600	65800	192200
23	Производство	834800		652400	798500	688700
24	Брак в производстве	35900		3600	9500	30000
25	Полуфабрикаты	154000		64500	25000	193500
26	Готовая продукция	654800		798500	697500	755800
30	Касса
31	Счета в банках
36	Расчеты с покупателями и заказчиками
40	Уставный капитал
44	Нераспределенная прибыль (непокрытые убытки)
63	Расчеты с поставщиками и подрядчиками
64	Расчеты по налогам и платежам
65	Расчеты по страхованию
66	Расчеты по оплате труда
683	Расчеты по прочим операциям (внутрихозяйственные расчеты)		66952500	1212200			65740300
70	Доходы от реализации
90	Себестоимость реализации
98	Налоги на прибыль
	ИТОГО	90552500	90552500	2792800	2792800	90041600	90041600

Таблица 2

Оборотный баланс по подразделению «Моторостроительный завод»

Счет	Наименование счета	Сальдо на 01.04.2009		Обороты, апрель 2009		Сальдо на 30.04.2009
Счет	Наименование счета	Дт	Кт	Дт	Кт	Дт	Кт
1	2	3	4	5	6	7	8
10	Основные средства	85000000				85000000
11	Прочие необоротные материальные активы	8032400				8032400
13	Износ необоротных активов		25060000		801300		25861300
15	Капитальные инвестиции
20	Производственные запасы	874060		58000	595200	336860
22	Малоценные и быстроизнашиваемые предметы	244400		23600	75800	192200
23	Производство	978950		752400	898500	832850
24	Брак в производстве	32900		4600	10500	27000
25	Полуфабрикаты	268000		64500	35000	297500
26	Готовая продукция	987600		898500	797500	1088600
30	Касса
31	Счета в банках
36	Расчеты с покупателями и заказчиками
40	Уставный капитал
44	Нераспределенная прибыль (непокрытые убытки)
63	Расчеты с поставщиками и подрядчиками
64	Расчеты по налогам и платежам
65	Расчеты по страхованию
66	Расчеты по оплате труда
683	Расчеты по прочим операциям (внутрихозяйственные расчеты)		71358310	1412200			69946110
70	Доходы от реализации
90	Себестоимость реализации
98	Налоги на прибыль
	ИТОГО	96418310	96418310	3213800	3213800	95807410	95807410

Таблица 3

Оборотный баланс по подразделению «Машиностроительный завод»

Счет	Наименование счета	Сальдо на 01.04.2009		Обороты, апрель 2009		Сальдо на 30.04.2009
Счет	Наименование счета	Дт	Кт	Дт	Кт	Дт	Кт
1	2	3	4	5	6	7	8
10	Основные средства	122600000				122600000
11	Прочие необоротные материальные активы	9832400				9832400
13	Износ необоротных активов		28060000		801300		28861300
15	Капитальные инвестиции
20	Производственные запасы	874060		58000	595200	336860
22	Малоценные и быстроизнашиваемые предметы	244400		23600	75800	192200
23	Производство	978950		752400	898500	832850
24	Брак в производстве	32900		4600	10500	27000
25	Полуфабрикаты	268000		64500	35000	297500
26	Готовая продукция	996780		898500	797500	1097780
30	Касса
31	Счета в банках
36	Расчеты с покупателями и заказчиками
40	Уставный капитал
44	Нераспределенная прибыль (непокрытые убытки)
63	Расчеты с поставщиками и подрядчиками
64	Расчеты по налогам и платежам
65	Расчеты по страхованию
66	Расчеты по оплате труда
683	Расчеты по прочим операциям (внутрихозяйственные расчеты)		107767490	1714560	302360		106355290
70	Доходы от реализации
90	Себестоимость реализации
98	Налоги на прибыль
	ИТОГО	135827490	135827490	3516160	3516160	135216590	135216590

Консолидация с помощью формул

Прежде всего нужно понять, что мы хотим получить на самом деле. И лучший способ сделать это — решить для начала задачу вручную. Что мы сейчас и проделаем. А инструментом для получения консолидированного отчета будут… трехмерные формулы. Последовательность наших действий следующая. Вначале создаем новый рабочий лист. Для этого делаем так:

1) щелкаем правой кнопкой мыши на ярлычке любого листа;

2) из контекстного меню выбираем «

Вставить…»;

3) в окне «

Вставка» выбираем «Лист»;

4) нажимаем «

ОК».

Формировать итоговый отчет на новом листе — необязательное условие, но так удобнее работать. Да и результат будет нагляднее. Теперь изменим имя листа:

1) щелкаем правой кнопкой мыши на ярлычке созданного листа;

2) из контекстного меню выбираем «

Переименовать»;

3) вводим новое имя, например «

Свод»;

4) нажимаем «

Enter».

Заголовки в итоговом отчете будут в точности повторять исходные таблицы, поэтому просто скопируем их через буфер обмена:

1) переходим на лист «

ЗУ»;

2) выделяем блок «

A1:H2»;

3) нажимаем «

Ctrl+C»;

4) переходим на лист «

Свод»;

5) становимся на ячейку «

А1»;

6) нажимаем «

Ctrl+V».

Переходим собственно к расчетам итоговых значений. Делаем так:

1) ставим указатель на «

C3» листа «Свод»;

2) вводим формулу «

=ЗУ!C3+ЛЗ!C3+МЗ!C3+МСЗ!C3». Напомню, что печатать ее не нужно. Набираем символ «=», щелкаем на лист «ЗУ» в ячейке «C3», печатаем «+», щелкаем на листе «ЛЗ» в ячейке «C3» и т. д.;

3) завершив формирование формулы, нажимаем «

Enter»;

4) копируем формулу из «

C3» на всю ширину таблицы (до ячейки «H3»);

5) в ячейку «

A3» листа «Свод» вводим ссылку «=МСЗ!A3»;

6) в ячейку «

B4» вводим формулу «=МСЗ!B3»;

7) выделяем блок «

A3:H3», копируем его в буфер обмена и вставляем на все ячейки консолидированного отчета. В примере это блок «C3:H26».

Форматируем итоговый отчет

. Здесь есть одна хитрость. По идее лист итоговых значений «Свод» должен быть отформатирован так же, как исходные таблицы. Казалось бы, можно воспользоваться инструментом «Формат по образцу» и быстро решить эту задачу. Но при этом возникает одна проблема. В исходной таблице часть ячеек отформатирована с параметром объединения. При попытке перекопировать формат Excel выдаст предупреждение об ошибке и отменит эту операцию. Поэтому мы поступим иначе и вспомним, что в Excel 2007 можно форматировать и заполнять данными несколько листов одновременно. Делаем так:

1) щелкаем левой кнопкой мыши на ярлычке листа, пусть это будет «

МСЗ»;

2) удерживая нажатой клавишу «

Ctrl», щелкаем на ярлычке листа «Свод». Так мы выделили оба листа одновременно. Теперь все операции с листом «МСЗ» будут синхронно выполняться и на листе «Свод». В нашем примере таким способом удобно привести в соответствие ширину колонок на разных листах;

3) ставим указатель мыши на границу колонки в области ее заголовка;

4) когда указатель примет форму двунаправленной стрелки, нажимаем левую кнопку мыши;

5) удерживая ее, слегка перемещаем указатель в сторону и тут же возвращаем на место. Тем самым мы проимитировали изменение ширины колонки на листе «

МСЗ». На связанном листе «Свод» соответствующая колонка станет такой же ширины;

6) проделываем эту операцию со всеми колонками таблицы на листе «

МСЗ»;

7) выделяем весь лист («

Ctrl+A»);

8) устанавливаем тип и размер шрифта, а также формат отображения чисел. Как это делать, думаю, повторять не нужно;

9) щелкаем на кнопке «

Офис», затем на кнопке «Параметры Excel»;

10) щелкаем на группе параметров «

Дополнительно»;

11) находим раздел «

Показывать параметры для следующего листа»;

12) смотрим на значение в списке листов, там должен быть указан «

Свод». Если это не так, раскройте список и выберите из него лист, где будут расположены итоги;

13) снимите флажок «

Показывать нули в ячейках, которые содержат нулевые значения»;

14) нажмите «

ОК». Теперь нулевые значения не будут видны на экране;

15) последовательно щелкаем мышью на ярлычке листа «

ЗУ», затем на ярлычке «Свод», чтобы отменить группировку.

Консолидированный отчет готов. Его форма показана на рис. 2 (см. с. 32).

Объединение значений через инструмент «Консолидация»

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

1) исходная таблица невелика;

2) все данные для консолидации имеют совершенно одинаковую структуру.

Представьте себе, что таблицы «

ЗУ», «ЛЗ», «МЗ», «МСЗ» были бы разные. Например, если у них отличается количество строк или порядок следования счетов. Тогда каждую формулу в итоговом отчете пришлось бы формировать отдельно! Ни о каком копировании не могло быть и речи. Конечно, такая работа потребовала бы намного больше времени и сил. Поэтому разработчики Excel 2007 снабдили свое детище мощным и удобным инструментом для консолидации данных. И мы сейчас разберемся, как с ним работать.

онсолидация данных по их расположению на листе

Инструмент консолидации данных Excel 2007 расположен в группе иконок «

Работа с данными». Эта группа относится к разделу главного меню «Данные». Посмотрим, как можно применить этот инструмент для решения задачи из нашего примера. Делаем так:

1) создаем новый лист, назовем его «

Свод_»;

2) ставим указатель мыши на ячейку «

A1»;

3) выбираем раздел «

Данные» главного меню Excel 2007;

4) в группе иконок «

Работа с данными» щелкаем на элементе «Консолидация» (рис. 3). Появится окно, изображенное на рис. 4. В нем нужно заполнить параметры консолидации.

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

В центре окна видим информационное поле, оно называется «Ссылка». Сюда нужно последовательно вводить диапазоны с данными для консолидации. Адреса диапазонов вручную выбирать не нужно. Вы можете просто выделить область на рабочем листе, а Excel подставит адреса выделенного блока в область «Ссылка:».

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

Ниже параметра «Ссылка:» находится область «Список диапазонов:». Здесь перечислены все фрагменты рабочих листов и книг MS Excel, которые будут участвовать в консолидации.

В правой части окна расположены три кнопки. С кнопкой «Обзор…» мы уже знакомы. Кнопка «Добавить» — добавляет текущий диапазон к списку консолидации. По сути дела она переносит содержимое параметра «Ссылка:» в область «Список диапазонов:». Кнопка «Удалить» убирает выделенный элемент из списка диапазонов.

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

С окном мы разобрались, испытаем инструмент консолидации на практике. Напомним, что в настоящий момент у нас открыт лист «Свод_» и активно окно «Консолидация». Настало время заполнить его параметры. Делаем так:

1) щелкаем на параметре «Ссылка:»;

2) переходим на лист «ЗУ»;

3) удерживая кнопку мыши, обводим блок ячеек «C3:H26». Это все данные для консолидации за исключением заголовков строк и колонок таблицы;

4) нажимаем кнопку «Добавить» (см. рис. 4 на с. 32). Адрес выделенного блока появится в области «Список диапазонов:»;

5) переходим на лист «ЛЗ». В нем Excel автоматически выделит диапазон «ЛЗ!C3:H26»;

6) нажимаем кнопку «Добавить». В списке диапазонов консолидации появится «C3:H26»;

7) повторяем эти действия для листов «МЗ» и «МСЗ», пока не укажем все диапазоны для консолидации. В нашем примере это «$ЗУ!$С$3:$H$26», «$ЛЗ!$С$3:$H$26», «$МЗ!$С$3:$H$26», «$МСЗ!$С$3:$H$26»;

8) завершив формирование диапазонов, нажимаем «ОК». Результат показан на рис. 5.

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

Важно!

Excel обработал ячейки рабочей книги без оглядки на их смысловую нагрузку. Например, если бы в качестве диапазонов консолидации мы указали блоки «$A$1:$H$26», он просуммировал бы даже номера счетов… Откровенно говоря, хотелось бы найти более аккуратный способ формирования итогового отчета.

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

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

bk@id.factor.ua, nictomlar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!

PRO-доступ ко всем закрытым материалам сайта Получить доступ

Следующая публикация