Excel 2010: сводные отчеты без сводных таблиц
 | Сводные отчеты в жизни бухгалтера занимают особое место. Практически любой более-менее сложный бухгалтерский расчет обычно связан с обработкой больших баз данных, с определением итоговых значений. Так что неудивительно, что в бухгалтерской практике Мастер сводных таблиц — едва ли не самый популярный инструмент из арсенала обработки данных программы Excel. Однако даже у этого прекрасного инструмента есть свои недостатки. Дело в том, что сводная таблица — самостоятельный объект. Возможности корректировки сводных таблиц ограничены, обновлять их нужно вручную. Кроме того, если итоговые значения нужно произвольно расположить на рабочем листе, сводные отчеты в данном случае тоже вряд ли помогут. Здесь нужен другой подход — научиться определять итоги с помощью встроенных функций. О том, как это сделать, что это за функции, где они могут быть полезны бухгалтеру, я предлагаю поговорить в этой статье. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
В качестве примера я подготовил небольшую таблицу, фрагмент которой показан на рис. 1. Таблица расположена на листе с именем «БД». Она представляет собой базу данных, где записаны подробные сведения о движении ТМЦ. В таблице восемь полей:
— «Дата» — дата реализации товара;
— «Склад» — место хранения, с которого был отпущен товар;
— «Покупатель» — контрагент, купивший товар;
— «ИНН» — идентификационный код покупателя;
— «НаимТов» — название проданного товара;
— «Кол-во» — объем реализации (в штуках);
— «Цена» — цена за единицу товара;
— «Сумма» — стоимость отгруженного товара.
По этой таблице я хочу построить сводный отчет, где показать движение ТМЦ (в количественном выражении) по каждому складу.
В принципе такие цифры можно получить при помощи сводной таблицы. Для этого нужно обратиться к Мастеру сводных таблиц, в макете отчета в область строк поставить наименования товаров, в область колонок — поле «Склад», а в область данных перенести поле «Сумма».
И все будет нормально, пока нам не понадобится взять отдельные цифры из этой сводной таблицы и подставить их в другой отчет. Здесь сразу же возникнет проблема. В любом случае для решения задачи придется задействовать ссылки на ячейки сводной таблицы. Чтобы эти ссылки работали правильно, после каждой корректировки данных сводную таблицу придется обновлять. Но и это не все! При добавлении новых записей в базу размеры сводного отчета могут измениться. И тогда все ссылки придется полностью поменять… Отсюда следует вывод: сводная таблица хороша, если она используется сама по себе. Но как только итоги нужно посчитать и вставить в отчет произвольной формы, лучше оперировать формулами, обратившись к встроенным функциям MS Excel.
Таких функций существует много. Это и обработка баз данных, и определение промежуточных итогов, и многое другое. О них мы обязательно поговорим в одной из наших статей. Но сейчас я предлагаю ограничиться двумя вариантами — это функции «СУММПРОИЗВ()» и «СУММЕСЛИМН()». На мой взгляд, полезность этих функций для практикующего бухгалтера трудно переоценить. Приступим.
Определение итогов при помощи функции «СУММПРОИЗВ()»
В бухгалтерских расчетах функцию «СУММПРОИЗВ()» используют нечасто. Может быть, потому, что находится она в категории «Математические». А может, причина в том, что официальная справка по этой функции недостаточно хороша, — на этот вопрос я не готов ответить. Но в любом случае максимум, что делают при помощи «СУММПРОИЗВ()», — поэлементно перемножают строки (или колонки) и считают сумму полученных произведений.
Синтаксис формулы выглядит так: «=СУММПРОИЗВ(Массив1; Массив2; Массив3;…; МассивN)», где «МассивX» — блок ячеек на рабочем листе. Количество таких блоков не должно превышать 255. Размеры всех блоков должны быть одинаковыми.
Важно! Все параметры функции «=СУММПРОИЗВ()» должны быть или фрагментами строк, или частью колонок. Строки на столбцы функция перемножать не умеет.
Простейший пример использования функции «=СУММПРОИЗВ()» — найти сумму продаж, если известна цена и объем реализованного товара. Применительно к базе на рис. 1 такая формула будет выглядеть так: «=СУММПРОИЗВ(F:F;G:G)». С этим все понятно. Но на самом деле функция «=СУММПРОИЗВ(F:F;G:G)» обладает гораздо более широкими, даже уникальными возможностями. Для иллюстрации этих возможностей предлагаю вернуться к нашей задаче и построить отчет о движении товаров с детализацией по каждому складу предприятия. Для определенности будем считать объемы в количественном выражении. Теперь делаем так:
1. Открываем файл с базой данных, создаем новый лист. Я назвал его «СТ».
2. Щелкаем на ячейке «A1» и вводим текст «Наим. товара».
3. В ячейку «B1» пишем заголовок «Номер склада».
4. Выделяем блок ячеек «B1:D1».
5. Щелкаем в выделенном блоке правой кнопкой мыши. Из контекстного меню выбираем пункт «Формат ячеек…». Откроется одноименное окно «Формат ячеек», как на рис. 2.
6. В этом окне переходим на закладку «Выравнивание». Включаем флажок «объединение ячеек». Переключатель «по горизонтали:» ставим в положение «по центру».
7. В окне «Формат ячеек» нажимаем «ОК», — таким образом мы объединили ячейки «B1» и «D1».
8. В ячейки «B2», «C2», «D2» вводим названия мест хранения. У нас это будут значения «001», «002», «003».
9. В блоке ячеек «A3:A11» заносим наименования товаров.
10. В ячейку «B3» вводим формулу «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)* (БД!$E$2:$E$65536=$A3)*(БД!$F$2:$F$65536))». Разумеется, формулу нужно вводить при помощи Мастера функций. Адреса блоков выбираем щелчком мыши на рабочем листе.
11. Копируем формулу вправо и вниз на всю высоту сводного отчета (это блок ячеек с «B3» до «D11»).
12. В ячейку «E3» вводим формулу «=СУММ(B3:D3)». Копируем ее вниз до ячейки «E11». Результат нашей работы показан на рис. 3.
13. Сохраняем файл с именем «Отчет_1.xls», — он нам еще пригодится.
Вот и все! Мы построили сводный отчет при помощи встроенной функции «=СУММПРОИЗВ()» программы Excel. Преимущества этого подхода очевидны. При любых изменениях в исходной базе итоги Excel обновит автоматически. Формулы можно использовать в любом документе или перенести в произвольную ячейку рабочего листа, — в этом плане нет никаких ограничений.
Теперь вкратце об алгоритме расчета итогов. Давайте рассмотрим его на примере формулы, которая записана в ячейке «B3». Она выглядит так: «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3)*(БД!$F$2:$F$65536))».
Ключевым элементом формулы является функция «=СУММПРОИЗВ()». Первые два параметра функции работают как логические выражения.
Берем фрагмент формулы «БД!$B$2:$B$65536=B$2». Внутри функции «=СУММПРОИЗВ()» это выражение работает так. Каждое значение из блока «B2:B65536» основной базы (лист «БД») Excel сравнит с содержимым ячейки «B2» сводного отчета. Фактически он сравнит номер склада из колонки «B» базы данных со значением «001» (рис. 3). Если номер склада в базе (колонка «B») равен «001», результатом сравнения будет «ИСТИНА». В противном случае мы получим «ЛОЖЬ». По такой же схеме работает выражение «БД!$E$2:$E$65536=$A3. Только сравнивает оно наименования товаров из колонки «E» базы данных с названием в ячейке «A3» сводного отчета.
После обработки условий Excel перемножит полученные результаты. Если оба логических выражения вернут значение «ИСТИНА» (и склад, и название товаров совпадают с указанными в сводном отчете), результат умножения будет равен «1». Если хотя бы один из сомножителей окажется ложным, мы получим «0».
Этот результат Excel умножит на объем реализации из колонки «F» основной базы, после чего просуммирует полученные значения. В итоге все цифры, которые не попадают под условие отбора, будут умножены на «0». Оставшиеся объемы войдут в сумму с коэффициентом «1». А в результате формула найдет сумму всех объемов из диапазона «БД!$F$2:$F$65536», для которых номер склада и название товара совпадают с теми, которые указаны в ячейках «B2» и «A3» сводного отчета.
Подробный пример расчетов по указанному алгоритму для ячейки «M2» (склад «001», позиция «Карандаш») выглядит так:
| № строки | Склад | НаимТов | Усл.1 (Склад= "001") | Усл.2 (ТМЦ="Карандаш") | Усл1*Усл2 | Кол-во | Сумма |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
| 1 | 001 | Скоросшиватель | ИСТИНА | ЛОЖЬ | 0 | 10 | 0 |
| 2 | 003 | Бумага оф. | ЛОЖЬ | ЛОЖЬ | 0 | 1 | 0 |
| 3 | 003 | Блокнот | ЛОЖЬ | ЛОЖЬ | 0 | 2 | 0 |
| 4 | 002 | Бумага оф. | ЛОЖЬ | ЛОЖЬ | 0 | 4 | 0 |
| 5 | 003 | Ластик канц. | ЛОЖЬ | ЛОЖЬ | 0 | 5 | 0 |
| 6 | 001 | Ластик канц. | ИСТИНА | ЛОЖЬ | 0 | 1 | 0 |
| 7 | 001 | Блокнот | ИСТИНА | ЛОЖЬ | 0 | 3 | 0 |
| 8 | 003 | Скрепка канц. | ЛОЖЬ | ЛОЖЬ | 0 | 8 | 0 |
| 9 | 002 | Скоросшиватель | ЛОЖЬ | ЛОЖЬ | 0 | 6 | 0 |
| 10 | 001 | Бумага оф. | ИСТИНА | ЛОЖЬ | 0 | 2 | 0 |
| 11 | 001 | Скрепка канц. | ИСТИНА | ЛОЖЬ | 0 | 7 | 0 |
| 12 | 002 | Ручка шар. | ЛОЖЬ | ЛОЖЬ | 0 | 9 | 0 |
| 13 | 003 | Блокнот | ЛОЖЬ | ЛОЖЬ | 0 | 6 | 0 |
| 14 | 001 | Файлик пласт. | ИСТИНА | ЛОЖЬ | 0 | 25 | 0 |
| 15 | 003 | Скоросшиватель | ЛОЖЬ | ЛОЖЬ | 0 | 1 | 0 |
| 16 | 001 | Скоросшиватель | ИСТИНА | ЛОЖЬ | 0 | 2 | 0 |
| 17 | 003 | Скотч | ЛОЖЬ | ЛОЖЬ | 0 | 3 | 0 |
| 18 | 001 | Файлик пласт. | ИСТИНА | ЛОЖЬ | 0 | 50 | 0 |
| 19 | 003 | Ручка шар. | ЛОЖЬ | ЛОЖЬ | 0 | 10 | 0 |
| 20 | 002 | Скоросшиватель | ЛОЖЬ | ЛОЖЬ | 0 | 5 | 0 |
| 21 | 001 | Файлик пласт. | ИСТИНА | ЛОЖЬ | 0 | 20 | 0 |
| 22 | 001 | Карандаш | ИСТИНА | ИСТИНА | 1 | 11 | 11 |
| 23 | 002 | Ручка шар. | ЛОЖЬ | ЛОЖЬ | 0 | 4 | 0 |
| 24 | 001 | Карандаш | ИСТИНА | ИСТИНА | 1 | 8 | 8 |
| 25 | 001 | Блокнот | ИСТИНА | ЛОЖЬ | 0 | 2 | 0 |
| 26 | 002 | Скотч | ЛОЖЬ | ЛОЖЬ | 0 | 11 | 0 |
| 27 | 001 | Ластик канц. | ИСТИНА | ЛОЖЬ | 0 | 2 | 0 |
По такому же принципу при помощи функции «=СУММПРОИЗВ()» можно сформировать сводные отчеты практически неограниченной сложности. Все, что да этого нужно, — правильно написать условия для отбора и суммирования записей. В качестве примера я предлагаю построить таблицу, в которой показать динамику движения ТМЦ (по датам) по каждому складу. Иными словами, в этой таблице мы хотим отразить: сколько, какого товара и когда было отпущено с конкретного склада. Для этого делаем так.
1. В документе «Отчет_1.xls» создаем новый лист.
2. В ячейку «A1» этого листа пишем текст «Наим. товара».
3. В ячейку «B1» вводим дату «01.10.2012».
4. В «С1» вводим формулу «=B1+1», копируем ее вправо до ячейки «F1».
5. В ячейки «B2:F2» вводим текст «001» (номер первого склада).
6. В «G1» пишем текст «Итого» — здесь будет посчитана сумма реализации каждого товара.
7. Выделяем блок ячеек «A1:A2».
8. Щелкаем на выделенном блоке правой кнопкой мыши.
9. Из контекстного меню выбираем пункт «Формат ячеек…». Откроется окно «Формат ячеек», как на рис. 2.
10. В этом окне на закладке «Выравнивание» включаем флажок «Объединение ячеек», переключатель «по вертикали:» ставим в положение «по центру».
11. В окне «Формат ячеек» нажимаем «ОК». Таким образом мы объединили ячейки «A1» и «A2».
12. Аналогичным образом объединяем ячейки «G1» и «G2».
13. Выделяем блок «B1:G2», копируем в буфер (комбинация «Ctrl+C») и вставляем дважды, начиная с ячеек «H1» и «N1».
14. В ячейках «H2:L2» и «N2:R2» изменяем номера мест хранения (рис. 4). Шапка отчета готова.
15. В ячейку «B3» вводим формулу «=СУММПРОИЗВ((БД!$A$2:$A$65536=B$1)* (БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3)*(БД!$F$2:$F$65536))».
16. Копируем эту формулу в ячейки «B3:F11», затем — в ячейки «H3:L11» и «N3:R11».
17. В «G3» вводим формулу суммирования «=СУММ(B3:F3)».
18. Копируем формулу из «G3» в ячейки «M3» и «S3».
19. Копируем все формулы вниз на всю высоту таблицы. Результат нашей работы показан на рис. 4.
В этом отчете мы показали движение ТМЦ в трех измерениях: товары, склады и даты реализации. Причем сделали все это при помощи одной-единственной функции! Более того. Добавляя в формулу новые условия, мы можем расширить свой отчет и получить сводную таблицу практически любой сложности. Вот несколько примеров.
Предположим, мы решили изменить сводный отчет, дополнив его условием для формирования итогов по конкретной дате. Делаем так.
1. Открываем документ «Отчет_1.xls».
2. Переходим на лист «СТ».
3. В ячейку «F1» вводим начальное значение интервала, пусть это будет «03.10.2012».
4. В «B3» вводим формулу: «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3) *(БД! $A$2:$A$65536=$F$1)*(БД!$F$2:$F$65536))». Изменения в параметрах функции показаны полужирным начертанием.
5. Копируем формулу в ячейки «B3:D11». Мы построили сводный отчет, включив в него данные только за «03/10/12».
Кстати, в функции «=СУММПРОИЗВ()» можно использовать логические операции. Например, выражение «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3)*(БД!$A$2: $A$65536>$F$1)*(БД!$F$2:$F$65536))» посчитает итоги по датам, которые больше значения в ячейке «F1».
Чтобы посчитать итоги для некоторого интервала дат из основной базы, можно дополнить условие функциями «И()», «ИЛИ()». Но лучше действовать по уже привычной схеме. Вот пример, как это сделать.
1. В ячейку «F2» на листе «СТ» вводим значение «05.10.2012». Это будет конечная дата временного интервала. Дата начала находится в ячейке «F1», это «03.10.2012».
2. В «B3» вводим формулу: «=СУММПРОИЗВ((БД!$B$2:$B$65536=B$2)*(БД!$E$2:$E$65536=$A3) *(БД!$A$2:$A$65536>$F$1)*(БД!$A$2:$A$65536<$F$2)*(БД!$F$2:$F$65536))».
3. Копируем формулу в ячейки «B3:D11».
Мы построили сводный отчет, куда попадут данные только за «04/10/12». Изменяя значения в ячейках «F1» и «F2», мы сможем получить сводную таблицу для произвольного интервала времени. Между прочим, ввести такие гибкие условия отбора записей при работе с Мастером сводных таблиц проблематично. Так что использование формул в этом смысле имеет большие преимущества.
Расчет итогов при помощи функции «СУММЕСЛИМН()»
Среди новых возможностей программы Excel 2010 я хотел бы обратить ваше внимание на встроенную функцию «СУММЕСЛИМН()». Похожая функция была и в предыдущих версиях программы. Она называлась «СУММЕСЛИ()» и позволяла просуммировать данные из указанного диапазона по некоторому условию. В бухгалтерских расчетах функция «СУММЕСЛИ()» оказалась очень полезной. Например, с ее помощью можно найти сумму всех положительных чисел в указанном блоке ячеек. Или просуммировать данные за определенный промежуток времени и т.п. Однако у функции «СУММЕСЛИ()» было существенное ограничение: она могла оперировать всего одним условием. Этого недостатка лишена функция «СУММЕСЛИМН()». Синтаксис у нее такой: «СУММЕСЛИМН(ДСумм; ДУсл1; Усл1; ДУсл2; Усл2; …, ДУслN; УслN;)». У функции минимум три параметра:
— «ДСумм» — блок рабочего листа, откуда функция будет накапливать сумму;
— «ДУсл1» — блок значений для проверки условия суммирования;
— «Усл1» — выражение для проверки условия суммирования.
В таком варианте (с тремя параметрами) функции «СУММЕСЛИМН()» и «СУММЕСЛИ()» эквивалентны. Алгоритм работы «СУММЕСЛИМН()» очень прост. Функция просматривает значения в блоке «ДУсл1», сверяет их с выражением «Усл1». Если условие выполняется, она накапливает сумму из соответствующих ячеек в блоке «ДСумм». Кстати, блоки «ДСумм» и «ДУсл1» могут совпадать. Например, чтобы найти объем реализации товаров по складу «001» (база данных, как на рис. 1), можно воспользоваться одной из формул: «=СУММЕСЛИМН(F2:F28;B2:B28;1)» или «=СУММЕСЛИ(B2:B28;1;F2:F28)».
Важно! У функций «=СУММЕСЛИМН()» и «СУММЕСЛИ()» отличается порядок следования параметров. У «СУММЕСЛИ()» вначале идет блок значений для проверки, затем условие и после него — диапазон суммирования. В «=СУММЕСЛИМН()» первым указывают диапазон суммирования, а затем блок значений и логическое условие для отбора.
В функции «=СУММЕСЛИМН()» можно ввести несколько условий для выборочного суммирования. В этом случае они работают по принципу «И». То есть накопление суммы происходит, если выполняются все заданные условия для отбора значений.
Теперь посмотрим, как применить функцию «=СУММЕСЛИМН()» для формирования сводных отчетов в программе Excel 2010. Для этого с помощью «=СУММЕСЛИМН()» мы построим таблицу, изображенную на рис. 3. База данных у нас будет та же. А дальше делаем так.
1. Открываем документ с базой данных. Добавляем новый лист и создаем шапку таблицы, как показано на рис. 3.
2. Переходим на ячейку «В3». Вводим формулу: «=СУММЕСЛИМН(БД!$F:$F;БД!$B:$B;B$2; БД!$E:$E;$A3)».
3. Копируем эту формулу во все ячейки блока «B3:D11».
4. В ячейку «E3» вводим формулу «=СУММ(B3:D3)». Копируем ее вниз до ячейки «E11».
5. Сохраняем документ с именем «Отчет_2.xls».
Мы получили точную копию отчета, изображенного на рис. 3. В данном случае существенной разницы между функциями «=СУММЕСЛИМН()» и «=СУММПРОИЗВ()» нет. Правда (на мой взгляд), параметры у «=СУММЕСЛИМН()» выглядят проще и понятнее. Но здесь есть одна тонкость. Чтобы разобраться с ней, делаем так.
1. Открываем документ «Отчет_1.xls», как на рис. 3. Напомню, что в этой таблице для определения итогов мы использовали функцию «=СУММПРОИЗВ()».
2. Переходим на лист с базой данных (он называется «БД»).
3. Выделяем все данные на этом листе и переносим их в буфер обмена (иконка «Вырезать» на ленте главного меню или комбинация горячих клавиш «Ctrl+X»).
Важно! В данном случае нужно использовать именно операцию «Вырезать». Копирование данных не даст должного эффекта.
4. Создаем новый документ (комбинация «Ctrl+N»).
5. На свободный лист этого документа вставляем данные из буфера обмена.
6. Сохраняем документ с именем «База.xls».
7. Возвращаемся в документ «Отчет_1.xls».
8. Щелкаем правой кнопкой мыши на ярлычке листа «БД».
9. Из контекстного меню выбираем вариант «Удалить». MS Excel выдаст предупреждение, что удаление листов является необратимой операцией, отменить ее не удастся (рис. 5).
10. Соглашаемся с этим, нажимаем кнопку «Удалить».
11. Сохраняем документ с итоговым отчетом и закрываем его. При сохранении я указал имя «Отчет_11.xls».
Что произошло на самом деле? В результате переноса базы в отдельный файл Excel откорректировал ссылки в формулах для определения итогов. Теперь выражение в ячейке «B3», например, будет таким: «=СУММПРОИЗВ(([База.xls]Лист1’!$B$2:$B$65536=B$2)* ([База.xls]Лист1’!$E$2:$E$65536=$A3)*([База.xls]Лист1’!$F$2:$F$65536))» (изменения в формуле выделены полужирным начертанием).
Повторим то же самое с документом «Отчет_2.xls», где итоги посчитаны функцией «СУММЕСЛИМН()». То есть — открываем файл. Становимся на лист с базой данных. Переносим ее содержимое в новый файл и сохраняем его с тем же именем — «База.xls». Затем удаляем лист «БД» из файла «Отчет_2.xls» и сохраняем результат с именем «Отчет_12.xls». В результате мы получили два сводных отчета. Один из них использует функцию «=СУММПРОИЗВ()», а в другом мы применили «СУММЕСЛИМН()». Оба документа ссылаются на одну и ту же базу данных с именем «База.xls». А дальше делаем так.
1. Открываем файл «Отчет_11.xls». Появится окно с предложением обновить связи между базой и текущим документом (рис. 6).
2. В этом окне нажимаем «Обновить». На экране появится документ с итоговым отчетом, как на рис. 3.
Важно! Несмотря на то что файл с базой данных при этом закрыт, все итоги функция «=СУММПРОИЗВ()» посчитала правильно.
3. Закрываем документ (комбинация «Ctrl+F4»).
4. Открываем файл «Отчет_12.xls». Здесь итоги посчитаны функцией «СУММЕСЛИМН()». Появится окно с предложением обновить связи (рис. 6).
5. В этом окне нажимаем «Обновить». На экране появится документ с итоговым отчетом, как на рис. 3. Однако вместо итогов мы увидим текст «#ЗНАЧ!». Это означает, что данные для расчетов в данный момент недоступны.
6. Не закрывая документ «Отчет_12.xls», открываем базу данных. Для этого нажимаем комбинацию «Ctrl+O» в окне Проводника, выбираем файл «База.xls» и щелкаем на кнопке «Открыть».
7. Возвращаемся в документ «Отчет_12.xls». На месте итогов появятся правильные значения.
Важно! Функция «СУММЕСЛИМН()» не может получить данные из закрытого файла.
И последнее. В некоторых версиях MS Excel действует такое правило. Чтобы функция «=СУММПРОИЗВ()» смогла прочитать данные из закрытого файла, формулу нужно начать с символов «--» (два знака минус, т. н. двойное бинарное отрицание).
На сегодня все. Надеюсь, что материал этой статьи поможет вам создавать сложные сводные отчеты, используя встроенные функции MS Excel. Файл с примерами из этой статьи вы можете скачать на сайте редакции и пользоваться им по своему усмотрению. До новых встреч и удачной работы!
Жду ваши вопросы, замечания и предложения на bk@id.factor.ua , nictomkar@rambler.ru или на форуме редакции.
