text
Читать свежий номер бесплатно >>>
Финансовый директор

Как упростить выборку данных из нескольких однотипных отчетов с помощью Excel

Если информация о расчетах с поставщиками хранится в разных таблицах в Excel, по одной на каждого контрагента, подготовка отчета о прошедших платежах или состоянии кредиторской задолженности может отнимать существенное время. Но вовсе не обязательно вручную «собирать» цифры, переходя с листа на лист. Есть два простых и изящных способа упростить эту задачу. А именно организовать оперативную выборку сумм оплаты в заданном периоде по одному или нескольким поставщикам и размещение найденной информации в общей таблице. Первый способ позволяет найти одно значение по двум заданным параметрам из нескольких однотипных таблиц (например, выплаты конкретному поставщику за определенный период), второй – выбрать несколько значений, но уже по одному критерию (выплаты по всем контрагентам в одном периоде).

Как упростить поиск одного значения по нескольким таблицам

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

В новом файле будет три вида листов. Первый предназначен для оглавления. Второй – рабочий, именно здесь будут задаваться параметры поиска и выводиться искомое значение. Для примера, результат выборки сумм оплат за IV квартал одному из трех контрагентов – компании «Гурман» приведен на рисунке 1. Что касается третьей группы листов, здесь размещается вся исходная информация в однотипных таблицах (одинаковая структура, порядок размещения данных, единые единицы измерения). В нашем случае – те самые отчеты об оплатах поставщикам (по одному листу-таблице для каждого контрагента). Количество таких листов должно соответствовать количеству однотипных таблиц, участвующих в выборке.

Создаем лист «Оглавление книги». Здесь нужно будет перечислить имена листов с исходными данными. Имена вносятся вручную начиная с ячейки А3.

Например, для трех контрагентов это может выглядеть как на рисунке 2.

Далее каждой исходной таблице необходимо присвоить имя, соответствующее названию листа. Для этого ее нужно выделить, нажать правую кнопку мыши, выбрать из появившегося контекстного меню пункт «Имя диапазона», ввести название и нажать «ОК». Например, это может выглядеть как на рисунке 3. Лист исходных данных.

Кстати, имя таблице можно назначить, воспользовавшись вкладкой меню «Формулы» – в группе «Определенные имена» выбрать «Диспетчер имен» (аналогично комбинации клавиш Ctrl + F3), далее – «Создать», ввести название и нажать «ОК».

По той же схеме назначается имя «Поставщик» диапазону на листе «Оглавление книги» с перечислением названий исходных листов. Например, на рисунке 4 это диапазон «А3:А5», рассчитанный на трех контрагентов.

Остается на рабочем листе создать блок для ввода параметров поиска. Допустим, их будет два: поставщик и квартал. Нет смысла вводить имя поставщика вручную, когда потребуется найти по нему информацию. Ведь даже незаметная глазу опечатка приведет к ошибкам выборки. Пользователю будет удобнее, если он сможет указать наименование нужного контрагента в заданном списке. Для создания такого списка выбираем в меню вкладку «Данные», группу «Работа с данными», далее – «Проверка данных», тип данных «Список», в поле «Источник» вводим ранее заданное имя «Поставщик» (например, на рисунке 5. Создание перечня поставщиков показано, как этот список создается в ячейке В1).

Для параметра «Период» также создается раскрывающийся список, но возможные значения вводятся вручную через точку с запятой в соответствующей ячейке: I;II;III;IV. Например, на рисунке 6. Создание списка периодов отражено, как такой список готовится в ячейке В3.

Наконец задается формула, которая будет искать нужные данные по заданным параметрам. Она вводится в ячейку, в которой они должны отражаться. Например, на рисунке 7. Ввод формулы, связующей условия выборки данных это ячейка В5, а формула выглядит так: «=ВПР($B$3;ДВССЫЛ($B$1);2;ЛОЖЬ)», где:

Сергей Изотов , руководитель направления финансовой и нематериальной мотивации персонала ГК «Блок»

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

Для этого нужно открыть редактор Visual Basic Editor (комбинация клавиш «Alt+F11» или кликнуть правой кнопкой мыши на ярлык любого листа и выбрать «Исходный текст» или же в группе «Код» вкладки меню «Разработчик» щелкнуть по пункту «Visual Basic») и вставить в проект стандартный модуль. А в этот модуль добавить два кода программы (см. рисунок 8. Код программы) – ListSheet и NameAdded.

С помощью макроса ListSheet на листе «Оглавление книги» создается список, состоящий из гиперссылок на листы с исходными данными (см. перечень контрагентов в оглавлении книги). Текст макроса выглядит так:

Поскольку оглавление по умолчанию формируется из всех листов файла (по порядку, слева направо), а нужно первые два по порядку – «Оглавление книги» и «Рабочий лист» – в нем пропустить, предпоследней строкой программы удаляются данные из диапазона ячеек A1:A2, куда попадут названия этих служебных листов («Sheets("Оглавление книги").Range("A1:A2").ClearContents»).

Макрос NameAdded присваивает таблицам с исходными значениями имена, соответствующие названиям листов, на которых они расположены. Внимание! Последние не должны содержать пробелов. Это условие позволяет исключить распространение действия макроса на листы «Оглавление книги» и «Рабочий лист». Кстати, при попытке присвоить имя макрос проверяет, есть ли в названии листа пробел: если да – это признается ошибкой, которая удаляется (строка 13 Err.Clear).

Сам макрос приведен ниже:

 

                 Sub NameAdded()

                        Dim nm As Name

                           For Each nm In Names

                           nm.Delete

                       Next nm

                        On Error Resume Next

                       Dim sAlert As String

                       Dim wks As Worksheet

                          For Each wks In Worksheets

                            Names.Add Name:=wks.Name, RefersTo:=wks.Range("A1:B5")

                             If Err <> 0 Then

                             sAlert = sAlert & " - " & wks.Name & vbCrLf

                           Err.Clear

                         End If

                       Next wks

                         If Len(sAlert) > 0 Then _

                       MsgBox "Эти листы не будут использоваться в именах:" & vbCrLf &
                  sAlert, vbCritical

                        Names.Add Name:="Поставщик", RefersTo:=Range("'Оглавление
                  книги'!A3:A5")

                      ActiveWorkbook.Names("Поставщик").Comment = ""

                  End Sub

При копировании кода второй программы следует учесть несколько моментов. А именно какие строки можно менять при использовании для нужд другой компании. В частности, четвертая строка (nm.Delete) удаляет ранее существующие имена исходных таблиц, пятая (Next nm) означает переход к следующему имени. В седьмой строке в переменную «sAlert» записываются названия листов, которые не могут быть использованы в именах, например, первые два листа: «Оглавление книги» и «Рабочий лист». В 10 строке (Names.Add Name:=wks.Name, RefersTo:=wks.Range) указывается диапазон, которому следует присвоить имя.

В строке 17 (MsgBox) выводятся названия листов, которые не могут быть использованы в именах. В строке 18 (Names.Add Name:="Поставщик", RefersTo:=Range ("'Оглавление книги) указывается диапазон ячеек, из которых формируется список оглавления. Например, это может быть диапазон A3:A5.

Запустить оба кода программы можно нажатием сочетания клавиш «Alt+F8» или в окне «Макрос» выбрать макросы по названию и нажать кнопку «Выполнить».

В результате работы макросов получится готовый к работе файл.

Как оптимизировать выборку нескольких искомых значений из разных таблиц в Excel

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

Оптимизировать выборку нескольких искомых значений (сумм оплаты по разным поставщикам) по одному параметру (например, по дате) из небольшого количества таблиц можно практически по той же схеме, что и для одного значения с той лишь разницей, что на листе «Оглавление книги» файла Excel будет отражен и список названий таблиц, участвующих в выборке и ее результаты, а кроме того, будет применяться немного другая формула поиска по таблицам:

«=ВПР($C$1;ДВССЫЛ(A2);2;ЛОЖЬ)», где:

Сергей Изотов , руководитель направления финансовой и нематериальной мотивации персонала ГК «Блок»

Выборку нескольких значений по данным из множества таблиц (диапазонов) можно организовать, дополнив рабочий файл Excel макросами по тому же алгоритму, что и при выборке одного значения, за исключением того, что макрос ListSheet в этой схеме будет иметь несколько иной вид:

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

Кроме того, при использовании этого кода следует обратить внимание на то, что в его предпоследней строке (Sheets("Оглавление книги").Range) указывается одна строка A1. Это необходимо для того, чтобы исключить из выборки первый лист рабочего файла, а именно «Оглавление книги».

Рекомендации по теме

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Мы в соцсетях
А еще:
Сайт использует файлы cookie. Они позволяют узнавать вас и получать информацию о вашем пользовательском опыте. Это нужно, чтобы улучшать сайт. Посещая страницы сайта и предоставляя свои данные, вы позволяете нам предоставлять их сторонним партнерам. Если согласны, продолжайте пользоваться сайтом. Если нет – установите специальные настройки в браузере или обратитесь в техподдержку.