Excel 2007: консолидация данных (ч. 2)
 | В предыдущей статье мы использовали несколько методов консолидации данных, но все они обладали общим недостатком — при формировании итогового отчета мы опирались на физические атрибуты таблицы. Так, при использовании формул это были ссылки на соответствующие ячейки. При работе с инструментом консолидации мы объединяли данные по их расположению на рабочем листе. Это не всегда удобно: человеку свойственно оперировать логическими понятиями. И было бы гораздо лучше консолидировать данные, используя не реальные адреса, а названия строк и колонок исходной таблицы. Как реализовать эту возможность в Excel 2007 и каковы особенности этого способа консолидации, мы разберемся в этой статье, а заодно познакомимся с некоторыми полезными приемами формирования консолидированных отчетов. Юрий ЦЫГАНОК, главный редактор |
Исходные таблицы, по которым строится консолидированный отчет, всегда имеют заголовки. Это могут быть заголовки строк, колонок или того и другого. Логично было бы организовать объединение данных, используя эти заголовки. Такая возможность в Excel 2007 есть, причем для ее использования достаточно просто перенастроить параметры инструмента «
Консолидация».
К
онсолидация данных по заголовкамРаботу с инструментом «
Консолидация» мы продолжим на данных примера из нашей предыдущей статьи. Напомним, что эти данные представлены в виде четырех листов с именами «ЗУ», «ЛЗ», «МЗ», «МСЗ». Структура данных на всех листах одинакова, фрагмент одной из таблиц показан на рис. 1. Сейчас наша задача — объединить информацию из этих листов в один итоговый отчет. При этом мы будем ориентироваться не на местоположение ячеек, а на заголовки строк и колонок таблицы. Делаем так: 
1) открываем документ с исходными данными. От предыдущего примера у нас остался консолидированный отчет на листе «Свод_». Удаляем данные с листа «Свод_»;
2) щелкаем на ячейке «A1»;
3) выбираем раздел «Данные» главного меню Excel;
4) в группе иконок «Работа с данными» щелкаем на элементе «Консолидация» (рис. 2). Появится окно, изображенное на рис. 3. В нем будут представлены настройки инструмента «Консолидация», как они были сделаны в предыдущем сеансе работы;
Важно!
Excel 2007 запоминает настройки (параметры) консолидации индивидуально для каждого листа, где есть итоговый отчет.5) в окошке «Список диапазонов:» выделяем диапазон «$ЗУ!$С$1:$H$26»;
6) нажимаем кнопку «Удалить»;
7) повторяем это действие для всех оставшихся элементов в окошке «Список диапазонов:»;
8) щелкаем на параметре «Ссылка:»;
9) переходим на лист «ЗУ»;
10) удерживая кнопку мыши, обводим блок «А1:H26». В этот блок попадут все данные вместе с заголовками строк и колонок таблицы;
11) нажимаем кнопку «Добавить» (рис. 3). Адрес выделенного блока появится в области «Список диапазонов:»;
12) переходим на лист «ЛЗ». В нем Excel автоматически выделит диапазон «А1:H26»;
13) нажимаем кнопку «Добавить»;
14) повторяем эти действия для листов «МЗ» и «МСЗ», пока не сформируем все диапазоны для консолидации. В нашем примере это «$ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;
15) включаем флажок «значения левого столбца»;
16) Нажимаем «ОК». Фрагмент отчета с результатом консолидации показан на рис. 4 на с. 29.
Итоговые данные в таблице на рис. 4 остались те же, но ее форма изменилась. Первое, что бросается в глаза, — в таблице появились заголовки строк. Такими заголовками стали номера счетов. Справа от колонки с номерами счетов появился пустой столбец. В принципе на его месте должны были бы находиться наименования счетов, но их нет. Причина этого явления понятна, так как следует из логики работы Excel. Для объединения данных мы указали использовать заголовки строк (это левый столбец диапазона). Эти заголовки Excel рассматривал как ключевые поля. Совпадение значений ключевых полей для него — своеобразный сигнал для обработки (в нашем случае для накопления суммы). Остальные ячейки области консолидации Excel считает данными. В эту категорию попали и ячейки с наименованиями счетов. Так как в этих ячейках находился текст, результатом суммирования стали пустые значения.
Заголовки колонок в результирующей таблице отсутствуют, так как мы не включили флажок «подписи верхней строки» (рис. 3). В принципе это не важно: добавить две строки в начале таблицы и скопировать заголовки колонок через буфер обмена займет не больше одной минуты.
Прежде чем продолжить работу, вернемся к таблице на рис. 1. На нем показан пример исходных данных, для которых мы делаем консолидацию. Такая структура таблицы выбрана не случайно. Обратите внимание, что шапка таблицы занимает две строки и некоторые ячейки в ней оформлены с параметром объединения. Посмотрим, что получится, если к такой таблице применить инструмент консолидации с использованием заголовков и строк и колонок. Делаем так:
1) удаляем данные с листа «Свод_», делаем активной ячейку «A1»;
2) выбираем раздел «Данные», затем щелкаем на иконке «Консолидация» (рис. 2). Появится окно, изображенное на рис. 3;
3) в окошке «Список диапазонов:» ничего не меняем. Там должны остаться такие диапазоны для консолидации: $ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;
4) включаем флажки «значения левого столбца» и «подписи верхней строки»;
5) нажимаем «ОК». Фрагмент отчета с результатом консолидации показан на рис. 5.
В итоговом отчете остались три колонки с данными. Это произошло потому, что Excel использовал в качестве заголовков колонок первую строку таблицы. А в этой строке (рис. 1) всего пять элементов: номер счета, наименование счета, начальное сальдо, сумма оборотов и конечное сальдо. Исходя из этого Exсеl и построил структуру результирующего отчета. Он объединил данные начальных остатков по дебету и кредиту в одно целое и то же самое сделал с оборотами и конечным сальдо.
Важно!
При работе с консолидацией не используйте в шапке таблицы объединение ячеек. Лучше вначале сделайте консолидацию данных и только потом красиво оформляйте заголовки.Как выйти из создавшейся ситуации? Очень просто. Нужно перестроить заголовки в исходных таблицах, убрав параметр объединения ячеек.
Применительно к нашему примеру заголовки таблиц для консолидации можно было бы оформить, как показано на рис. 6.
В этом случае объединение с использованием заголовков строк и колонок даст результат, фрагмент которого показан на рис. 7 на с. 30. Все, что мы изменили в исходных данных, — это отказались от объединения ячеек и каждому ключевому полю в колонке присвоили уникальное имя. При таких условиях структура результирующей таблицы практически совпадает с исходными данными. Единственное, чего в ней не хватает, — это наименования счетов (рис. 7).
Важно!
Изменяя форму исходных таблиц, помните, что при удалении или добавлении строк или колонок адреса диапазонов консолидации не меняются. Если вы добавили строки (колонки) в исходную таблицу или наоборот удалили их из нее, настройки инструмента «Консолидация» придется повторить.Консолидация таблиц по названиям заголовков имеет очень важное преимущество. Оно заключается в том, что в исходных таблицах последовательность строк и колонок может отличаться. А на практике обычно так и есть! Например, вы можете в качестве упражнения переставить местами колонки или пересортировать строки на листах «ЗУ», «ЛЗ», «МЗ», «МСЗ» и затем повторить консолидацию. Excel 2007 все делает правильно. Главное, чтобы текст в названиях заголовков строк и колонок в точности совпадал.
Консолидация и автоподстановка
Решить проблему недостающих значений в консолидированном отчете можно по-разному. Один из вариантов — воспользоваться буфером обмена и заполнить, например, колонку «
Наименование счета» (рис. 6) значениями из соседнего листа. Способ простой, но не лишен недостатков. А что получится, если последовательность наименований на листах отличается? При копировании придется внимательно следить за соответствием каждого наименования и номера счета. Задача несложная, но утомительная. К тому же есть более изящное решение — воспользоваться функцией данных «ВПР()». У этой функции четыре параметра: искомое значение «ИскЗнач», блок ячеек или область поиска «Блок», индекс смещения «Индекс», тип сравнения «ТипСравн». А синтаксис функции выглядит так: «ВПР(ИскЗнач;Блок; Индекс;ТипСравн)». Функция просматривает значения первой (левой) колонки области «Блок», пытаясь найти значение «ИскЗнач». Если такое значение найдено, функция зафиксирует номер строки и вернет в качестве результата значение ячейки со смещением по колонке на величину «Индекс». Посмотрим, как применить эту функцию к нашей консолидированной таблице.Итак, наша задача — в итоговом отчете заполнить колонку с наименованиями счетов. Алгоритм наших действий будет такой. Начинаем со второй строки таблицы (рис. 7). В ячейке «
A2» находится номер счета «10». Запоминаем это значение и начинаем просматривать номера счетов в таблице, где они представлены в полном объеме. У нас это может быть, например, лист «ЗУ». В процессе сканирования колонки «А» на листе «ЗУ» мы пытаемся отыскать значение «10». Оно будет найдено во второй строке таблицы (рис. 6). В колонке «B2» находится наименование для счета «10» — «Основные средства». Это значение расположено со смещением «1» от начала области поиска. Его мы и вернем в качестве результата. А формула для ячейки «B2» на листе «Свод_» выглядит так: «=ВПР(A2;ЗУ!$A$1:$B$65000;2;0)». Вводим эту формулу в ячейку «B2», затем копируем ее на всю высоту таблицы. Окончательная форма консолидированного отчета по данным листов «ЗУ», «ЛЗ», «МЗ», «МСЗ» показана на рис. 8.
Организация связей с исходными данными
Недостаток полученных нами ранее итогов состоит в том, что они никак не связаны с исходными данными. Попробуйте изменить данные на листах «
ЗУ», «ЛЗ», «МЗ», «МСЗ». Консолидированные итоги на листе «Свод_» останутся прежними. Для того чтобы внедрить связь между исходными данными и результатом консолидации, служит флажок «Создавать связи с исходными данными» (рис. 3). Установим его и повторим консолидацию. Делаем так:1) удаляем данные с листа «
Свод_», делаем активной ячейку «A1»;2) выбираем раздел «
Данные», затем щелкаем на иконке «Консолидация» (рис. 2). Появится окно, изображенное на рис. 3;3) в окошке «
Список диапазонов:» ничего не меняем. Там должны остаться такие значения для консолидации: «$ЗУ!$AС$1:$H$26», «$ЛЗ!$A$1:$H$26», «$МЗ!$A$1:$H$26», «$МСЗ!$A$1:$H$26»;4) включаем флажки «
значения левого столбца», «подписи верхней строки» и «Создавать связи с исходными данными»;5) нажимаем «
ОК». Фрагмент отчета с результатом консолидации показан на рис. 9. 
Внешний вид таблицы сильно изменился. Появились признаки группировки данных в виде значков со знаком «+». Щелкаем на одном из таких значков, например слева возле счета «10» (в строке с номером 6 на рабочем листе). Значок «+» изменит свою форму и станет выглядеть как «-», а на рабочем листе появятся четыре скрытые строки, в которых находятся данные с итогами по соответствующему счету (рис. 9). Эти данные оформлены в виде ссылок на ячейки диапазонов консолидации. Например, для получения итогов по счету «10» («Основные средства») Excel суммирует такие ячейки: «=ЗУ!$C$2», «=ЛЗ!$C$2», «=МЗ!$C$2», «=МСЗ!$C$2». Ссылки на эти ячейки расположены по адресам «D2», «D3», «D4», «D5» листа с консолидированным отчетом. Сама же формула определения начального дебетового сальдо по счету «10» для всего предприятия выглядит так: «=СУММ(D2:D5)» (ячейка «D6»). Пробуем откорректировать исходные данные в любой исходной таблице. Итоги в консолидированном отчете тоже изменятся.
Консолидация по заголовкам таблиц с организацией связей с исходными данными — наиболее универсальный способ объединения данных с нескольких рабочих листов в один итоговый отчет, хотя и в этом способе работы есть свои нюансы. С одним из них мы сейчас разберемся.
Консолидация по рабочему ключу
Работая с инструментом консолидации, мы неоднократно сталкивались с такой ситуацией, когда часть колонок в итоговом отчете оставалась пустой. Примером может быть столбец «
Наименование счета» на рис. 7. Это возникало в тех случаях, когда внутри таблицы (в области данных) находились колонки с текстовой информацией. Поскольку выполнить арифметические операции с такими данными Excel не может, он оставляет в таблице пустые значения. С точки зрения правильности итоговых значений в этом нет ничего страшного. Но вот для дальнейшей обработки данных ситуация с пустыми ячейками может превратиться в проблему. Представьте себе, что вы захотите использовать итоговый отчет в качестве базы данных Excel или захотите применить к нему фильтр или сортировку. При наличии пустых ячеек на месте ключевых полей сделать это будет невозможно. В любом случае придется заполнить все пустые ячейки реальными данными. Для большой таблицы такая операция может занять немало времени…Решить проблему можно разными способами. Мы уже использовали два из них: заполняли недостающую информацию вручную (через буфер обмена) и применяли функцию «
ВПР()». В большинстве случаев этого хватает, но так бывает не всегда. Представьте себе, что в исходных таблицах часть информации уникальна. Применительно к нашему примеру это означает, что в каждой таблице есть несколько счетов, которые не повторяются в других таблицах. В этом случае мы не сможем воспользоваться функцией «ВПР()» для подстановки недостающих значений в итоговый отчет, так как у нас нет единого «справочника» с номерами всех счетов, которые понадобятся для такой подстановки. В этой ситуации можно посоветовать удобный прием, позволяющий решить проблему независимо от содержимого исходных таблиц. Смысл его такой. Перед выполнением консолидации мы добавим к исходным таблицам вспомогательную колонку, которую принято называть рабочим ключом. Столбец с рабочим ключом мы поставим первым (крайним слева) в каждой таблице. В нем с помощью формулы мы объединим данные из нескольких колонок, причем формулу составим так, чтобы между каждым элементом находился символ-разделитель. Затем выполним консолидацию данных. Поскольку столбец с рабочим ключом стоит первым, Excel будет использовать его для объединения таблиц. После консолидации мы получим итоговый отчет. В нем будет колонка с рабочими ключами и итоговые значения. А поскольку в рабочем ключе мы соединили данные из нескольких столбцов, все они будут видны в итоговом отчете. Думаю, все станет понятно, когда мы применим этот прием для консолидации наших таблиц.Итак, за основу мы возьмем данные, изображенные на рис. 7. Делаем так:
1) открываем файл, щелкаем на листе «
ЗУ»;2) удерживая клавишу «
Ctrl», щелкаем на ярлычках остальных листов с исходными данными: мы собираемся делать групповую операцию, т. е. откорректировать несколько таблиц одновременно;3) выделяем колонку «
А»;4) щелкаем правой кнопкой мыши. Из контекстного меню выбираем «
Вставить». Слева от таблицы мы добавили новый столбец;5) в ячейку «
А1» пишем заголовок, например «Кл»;6) в ячейку «
A2» вводим формулу «=B2&"-" &C2». Эта формула объединяет данные из колонок «B» и «C», т. е. она соединяет номер и название счета, а между ними ставит разделитель «-»;7) копируем формулу на всю высоту таблицы. В результате все исходные данные примут форму, как на рис. 10. В данный момент нам не нужны колонки «
B» и «C» (все продублировано в колонке «А»), но удалить их мы не можем, так как неправильно сработают формулы. Поэтому сейчас мы превратим все формулы в значения; 
8) выделяем колонку «А»;
9) нажимаем «Ctrl+С» (копируем ее в буфер обмена);
10) не снимая выделения, раскрываем список иконки «Вставить» главного меню. Из предложенных вариантов выбираем «Специальная вставка»;
11) в окне «Специальная вставка» переводим переключатель «Вставить» в положение «Значения»;
12) нажимаем «ОК». Формулы в таблицах пропали, на листе остались только результаты их работы;
13) теперь удаляем колонки «B» и «C», так как они нам больше не нужны;
14) выполняем консолидацию, используя для объединения заголовки строк и колонок. Фрагмент итогового отчета показан на рис. 11 на с. 33.
На наш взгляд, вполне симпатичный результат. В колонке «А» видны и номера, и названия счетов. Это результат объединения в рабочем ключе сведений из двух соседних колонок. Единственный минус в том, что эти данные занимают один столбец. Неплохо было бы разнести их на две колонки, тем более что в итоговой таблице справа от колонки «B» есть свободное место (на рис. 11 это колонки «С» и «D», скрытые в целях экономии места).
Как разделить рабочий ключ
Задачу разделения рабочего ключа мы решим с помощью формул. Это не единственный способ решения проблемы. В свое время мы обязательно познакомимся и с другими вариантами. Но сейчас использование формул нам кажется вполне уместным: и проблему решим, и вспомним работу со встроенными функциями, тем более что функции работы с текстом подробно мы еще не применяли.
Итак, наша цель. В колонке «
А» консолидированного отчета есть данные о номере счета и его названии. Номер от наименования счета отделен символом «-». Справа от колонки «А» есть три пустых столбца. Нам нужно в колонку «B» записать номер счета, в колонку «С» перенести наименование счета и все лишнее удалить. Делаем так:1) выделяем колонки «
B», «С», «D»;2) нажимаем клавишу «
Del», чтобы очистить их содержимое;3) становимся в ячейку «
B1», вводим заголовок «Счет». В ячейку «С1» вводим «Наименование»;4) в ячейку «
B6» пишем формулу «=ПСТР(A6;1;ПОИСК("-";A6;1)-1)», заполнять мы будем только строки с итогами. Рассмотрим работу формулы подробнее. Функция «ПОИСК("-";A6;1)» служит для поиска символа или подстроки в текстовой строке. У нее три параметра: что искать, где искать и с какой позиции текста начинать поиск. В нашем случае функция ищет символ «"-"» в ячейке «A6», начиная с позиции «1». Для строки «10-Основные средства/ЗУ» результат ее работы будет «3». Функция «=ПСТР(A6;1;ПОИСК("-";A6;1)-1)» теперь эквивалентна «=ПСТР(A6;1;3-1)». У этой функции три параметра: строка, начальная позиция и конечная позиция. Функция вырезает часть строки от начальной до конечной позиции. В нашем случае «=ПСТР (A6;1;3-1)» из строки «10-Основные средства/ЗУ» вернет результат «10»;.5) переходим к ячейке «
С6». Вводим формулу «=ПСТР(A6;ПОИСК("-"; A6;1)+1;ДЛСТР(A6))». Здесь функция «ПОИСК("-";A6;1)+1» находит позицию начала оставшейся части строки (это значение равно «3+1», т. е. «4»). А конечная позиция — это длина строки, ее находит функция «ДЛСТР(A6). Таким образом, для ячейки «A6» с текстом «10-Основные средства/ЗУ» функция «=ПСТР(A6;ПОИСК("-";A6;1)+1;ДЛСТР(A6))» эквивалентна «=ПСТР(A6;4;25), а ее результатом будет строка «Основные средства/ЗУ»;6) копируем формулы на всю высоту таблицы;
7) выделяем столбцы «
B» и «С». При помощи специальной вставки преобразуем их в значения;8) удаляем лишние колонки (в нашем случае это «
A» и «D»). Задача решена. Теперь и номер счета, и его наименование занимают два отдельных столбца.Прием разделения колонок — очень полезный инструмент для обработки данных. Поэтому нам хотелось бы обобщить его на более сложный случай, когда исходный материал нужно разделить на несколько колонок. Если вы внимательно присмотритесь, то увидите, что формулы нашего примера для этого не подходят. Обратимся к таблице, показанной на рис. 12. Исходная строка в ячейке «
А2» содержит код счета, наименование счета, название подразделения и номер года отчетного периода. Эти данные нужно распределить по отдельным колонкам. Такую задачу удобно решать по частям. 
Добавляем 7 колонок. Первая («Ключ») содержит исходную строку. Это текст такого содержания: «10-Осн. Средства-ЗУ-2010». В «B1» пишем заголовок «Счет». В «B2» ставим формулу «=ПСТР(A2;1;ПОИСК("-";A2;1)-1)». Она вырезает из исходного текста номер счета, т. е. результат работы формулы равен «10». В колонку «C» запишем остаток от исходной строки. Для этого в «C1» печатаем заголовок «Ост1», а в «C2» заносим формулу «=ПСТР(A2;ПОИСК("-"; A2;1)+1;ДЛСТР(A2))». Результат ее работы — текст «Осн. Средства-ЗУ-2010».
Из ячейки «C2»получим название счета. Для этого в «D1» пишем текст «Наимен.», а в «D2» — формулу «=ПСТР(C2;1;ПОИСК("-";C2;1)-1)». Аналогичным образом поступаем с остальными колонками. Вот содержимое отдельных ячеек:
Формулы для разделения текста на несколько колонок
| Адрес ячейки | Содержимое ячейки | Результат |
| 1 | 2 | 3 |
| « E1» | текст « Ост 2» | Ост 2 |
| « E2» | формула « =ПСТР(C2;ПОИСК("-";C2;1)+1;ДЛСТР(C2))» | ЗУ-2010 |
| « F1» | текст « Подр.» | Подр. |
| « F2» | формула « =ПСТР(E2;1;ПОИСК("-";E2;1)-1)» | ЗУ |
| « G1» | текст « Ост 3» | Ост 3 |
| « G2» | формула « =ПСТР(E2;ПОИСК("-";E2;1)+1;ДЛСТР(E2))» | 2010 |
| « H1» | текст « Год» | Год |
| « H2» | формула « =G2» | 2010 |
Когда формулы готовы, копируем их на всю высоту таблицы. Затем через инструмент специальной вставки преобразуем формулы в обычный текст и удаляем лишние колонки.
Предложенный способ — всего лишь один из возможных вариантов разделения данных на несколько столбцов. Для решения этой задачи в Excel 2007 есть замечательный инструмент — Мастер текстов. В одной из наших публикаций мы обязательно напишем, как с ним работать.
На этом мы завершаем тему консолидации таблиц в Excеl 2007. Надеемся, что материал этой статьи был для вас интересен и пригодится в практической работе. А в следующий раз мы приступим к работе со сводными таблицами — основным бухгалтерским инструментом в Excel 2007.
Ждем ваших вопросов, писем, предложений и замечаний на
bk@id.factor.ua, nictomlar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
