Как создать шаблон для быстрого поиска данных о дебиторе в Excel
Как контролировать дебиторскую задолженность с помощью Excel
Иветта Новикова, финансовый менеджер ООО «ТЕТ-а-ТЕТ»
Чем полезно это решение
Это решение поможет сформировать оперативный отчет о работе с дебитором на текущую дату. Оно подскажет, как с помощью Excel собрать все данные о работе с ним: задолженность, последние отгрузки, оплаты. Предложенную модель без труда можно адаптировать под нужды компании.
Преимущества и недостатки
Универсальность предложенного подхода можно отнести к главным преимуществам решения. Предлагаемый алгоритм проработки информации можно использовать не только для анализа дебиторов, но и для оперативной консолидации или выделения любых других данных. Для его реализации не потребуется писать макросы, достаточно стандартных формул Excel.
Информация об отгрузках и оплатах клиентов компании может содержаться в нескольких отчетах. Быстро сгруппировать только самые необходимые данные в одну таблицу, не прибегая к ручному поиску и копированию, можно с помощью Excel. Для этого нужно:
· определить, какие цифры нужно собрать;
· выбрать источники информации, которые потребуются для отчета;
· структурировать необходимые данные;
· воспользоваться специальными формулами Excel.
Тогда форма итоговой таблицы будет выглядеть как на рисунке 1.
Рисунок 1. Форма для поиска информации по дебитору
скачать.xls
В готовой форме нужно только выбрать из выпадающего списка искомого дебитора и вручную ввести в поле «Текущая дата» день и месяц. Таблица автоматически заполнится информацией об отгрузках и оплатах по дням, данными о задолженности на начало периода (например, года) и на дату составления отчета, вычленит сумму просроченных долгов.
|
|
|
Как подготовить исходные данные для контроля задолженности контрагента с помощью Excel
Подробные сведения о каком-либо клиенте могут понадобиться финансовой службе при серьезном нарушении им условий договора. Чтобы разобраться в ситуации, потребуется вся информация о его отгрузках:
· даты, номера накладных, счетов и заказов;
· суммы отгрузок;
· суммы бонусов и скидок, если они предоставлялись;
· планируемые даты оплат;
· фактические даты и суммы оплаты каждого счета;
· количество дней просрочки.
В качестве дополнения (если есть данные в управленческом учете) по тем же отгрузкам:
· их себестоимость;
· прибыль, полученная от отгрузки;
· рентабельность.
Исходную информацию для подготовки отчета с таким наполнением можно найти в следующих отчетах:
· об отгрузках (например, см. Отчет о продажах по покупателям);
· о рентабельности продаж;
|
|
|
· о дебиторской задолженности;
· о просроченной дебиторской задолженности;
· о движении денежных средств;
· о поступлении денежных средств от клиентов и в других документах (подробнее о том, какие еще формы могут пригодиться, см. Какой отчет поможет контролировать рентабельность продаж).
Вполне возможно, что данные в них заполняют разные службы. Отчет об отгрузках может вести коммерческая служба нарастающим итогом ежедневно, отчет о дебиторской задолженности – она же, но ежемесячно. Отчет о движении денежных средств в компетенции финансовой службы и заполняется еженедельно. Тогда процесс сбора аналитики по конкретному клиенту может оказаться слишком трудоемким.
К примеру, в компании «Альфа» коммерческая служба ведет отчет, представленный на рисунке 2. В нем собирается информация из управленческой учетной системы, данные финансовой службы и менеджеров продаж. Отгрузки фиксируются ежедневно в календарном порядке.
Рисунок 2. Отчет об отгрузках
скачать.xls
В свою очередь финансовая служба заполняет сведения о поступлениях и выплатах в виде отчета, представленного на рисунке 3. В нем отображаются данные не только о клиентах, но и о других оборотах по расчетным счетам компании – кредитные средства и займы, инвестиции, прочие доходы от неосновной деятельности и т. д.
|
|
|
Рисунок 3. Отчет о поступлениях денежных средств
скачать.xls
Чтобы прийти к форме, показанной на рисунке 1, необходимо создать соответствующий ей шаблон в новой книге Excel и дополнить его справочником (см. рисунок 4. Справочник дебиторов). В справочнике можно привести данные о дебиторской задолженности на начало отчетного года по каждому клиенту.
Рисунок 4. Справочник дебиторов
скачать.xls
Если отчеты об отгрузках и поступлениях хранятся в разных источниках, то лучше продублировать их в той же книге, где будет лежать шаблон поиска данных по клиенту. Это позволит обеспечить сохранность данных и избавит от необходимости увязывать разные книги Excel.
Как создать шаблон для быстрого поиска данных о дебиторе в Excel
В шаблоне отчета, который будет автоматически формироваться по выбранному дебитору, сначала нужно создать поле для ввода наименования клиента. Для этого в шапке отчета следует выделить специальную ячейку либо объединить несколько ячеек в одну. Например, в отчете компании «Альфа» поле для выбора дебитора – диапазон D3:F3 (см. рисунок 5. Выбор дебитора).
|
|
|
Наименование дебитора в этом поле можно вводить вручную либо создать выпадающий список. Для создания списка можно воспользоваться функцией Excel «Проверка данных», в которой сослаться на лист «Справочник дебиторов» (подробно о том, как создать выпадающий список в Excel, см. здесь).
Рисунок 5. Выбор дебитора
Далее нужно определиться с полями таблицы, в которые будет извлекаться требуемая информация по выбранному контрагенту. В шаблоне отчета компании «Альфа» данные о дебиторе разнесены на два блока – отгрузки и поступления (см. рисунок 1. Форма шаблона таблицы поиска информации по дебитору).
Теперь следует визуально расчертить диапазон, который будет содержать расчетные формулы. На рисунке 6 эта граница проходит по верхней линии строки 23. Такая мера необходима, чтобы поместились все отгрузки клиента. Поскольку для каждого дебитора количество строк может разниться, лучше взять максимально допустимый диапазон.
Рисунок 6. Выделение массива в шаблоне таблицы поиска
Для автоматического заполнения порядковых номеров в шаблоне отчета можно воспользоваться следующими простыми формулами:
· в ячейку A6 ввести формулу: =ЕСЛИ(B6="";"";1) – она будет проверять наличие записи в поле «Дата отгрузки» и оставаться пустой при отсутствии информации либо 1;
· в ячейку А7 ввести формулу: =ЕСЛИ(B7="";"";A6+1);
· выделив ячейку А7, протянуть формулу до конца массива.
Чтобы оставшиеся поля шаблона заполнялись в соответствии с выбранным клиентом, необходимо использовать формулу массива. Например, чтобы заполнить поле «Дата отгрузки», нужно:
1) выделить столбец «Дата отгрузки»;
2) в поле формул ввести формулу массива, которая должна сверить имя клиента с таблицей отчета об отгрузках, и последовательно вывести все даты отгрузок этого клиента в столбец В. Получится следующая запись: =ЕСЛИОШИБКА(ИНДЕКС('Отчет об отгрузках'!$A$4:$A$100;НАИМЕНЬШИЙ(ЕСЛИ($D$3='Отчет об отгрузках'!$C$4:$C$100;СТРОКА('Отчет об отгрузках'!$A$4:$A$100)-3;"");СТРОКА()-5));""). Подробнее о ней ниже;
3) нажать Ctrl + Shift + Enter.
В столбце А появятся порядковые номера позиций, поскольку столбец B заполнится.
В той же форме заполняются остальные поля шаблона – изменяться будет только столбец поиска в соответствии со столбцом отчета: для поля «Накладная» – 'Отчет об отгрузках'!$В$4:$В$100, для поля «Договор» – 'Отчет об отгрузках'!$D$4:$D$100 и т. д.
Рисунок 7. Заполнение поля «Дата отгрузки»
Функция ИНДЕКС() возвращает значение или ссылку на значение из указанной таблицы или диапазона и состоит из следующих аргументов: ИНДЕКС(массив, номер_строки, [номер_столбца]), где:
· массив – диапазон, в котором находится искомое значение, может быть как таблицей, так и одним столбцом или строкой;
· номер_строки – обязательный аргумент функции (особенно если массив состоит только из одного столбца). В нем нужно обозначить, из какой строки следует вернуть данные. Если массив содержит только одну строку, то ставится 0;
· [номер_столбца] – если в формуле ИНДЕКС() указывается и номер строки, и номер столбца, то функция вернет значение, находящееся на их пересечении. Этот аргумент обязателен только в том случае, если не задан номер строки. Во всех остальных ситуациях – по необходимости.
В отличие от функции ВПР(), ИНДЕКС() не требует жесткой сортировки и может находить значения любой позиции.
Для того чтобы в поле «Дата отгрузки» выгрузились искомые данные, в качестве массива нужно указать столбец А листа 'Отчет об отгрузках'!, выбрав диапазон от первой строки таблицы до последней. Однако, поскольку отчет заполняется нарастающим итогом, чтобы формулы в будущем не пришлось корректировать, последним номером строки стоит взять какое-нибудь большое число. Тогда массив будет, например, следующим: А4:А100 – функция ИНДЕКС() будет искать значения в столбце А в ячейках от четвертой до сотой.
Так как массив состоит из одного столбца, то аргумент [номер_столбца] не потребуется.
В качестве аргумента номер_строки необходимо вызвать еще одну функцию Excel – НАИМЕНЬШИЙ(). Эта функция возвращает наименьшее значение или диапазон значений в заданном массиве данных и состоит из следующих аргументов – НАИМЕНЬШИЙ(массив; k), где:
· массив – таблица, строка или столбец, в которых нужно найти значение;
· k – искомая позиция: 1 – первое же наименьшее значение, {1;2;3} – первые три наименьших значения и т. д.
Первым аргументом функции выступает формула ЕСЛИ($D$3='Отчет об отгрузках'!$C$4:$C$100;СТРОКА('Отчет об отгрузках'!$A$4:$A$100)-3;""), где:
· $D$3='Отчет об отгрузках'!$C$4:$C$100 – условие, при котором поиск в поле «Наименование покупателя» листа 'Отчет об отгрузках'! будет вестись только по выбранному в ячейке $D$3 листа 'Поиск по дебитору'! клиенту (см. рисунок 7. Заполнение поля «Дата отгрузки»);
· СТРОКА('Отчет об отгрузках'!$A$4:$A$100)-3 – эта функция выдаст номер строки выбранного столбца «Дата отгрузки», для которого выполняется условие. Из полученного номера нужно вычесть число строк, на которые отступает первая запись в таблице, – строки шапки отчета, заголовки и т. д. Например, если нужно найти номер строки таблицы «Отчет об отгрузках», где указана дата отгрузки контрагента ООО «Гамма» (см. рисунок 8. Поиск по значению), то стандартная функция СТРОКА() вернет значение 11. Однако в самой таблице строке 11 будет соответствовать дата 05.02.2015 компании ООО «Бета». То есть будут учитываться строки всего листа. Чтобы исключить верхние три строчки, занимаемые шапкой отчета, нужно вычесть из выражения СТРОКА() – 3;
· "" – если условие не выполняется, выдается массив пустых значений.
Рисунок 8. Поиск по значению
Остается определить, какой номер искомой позиции задать в аргументе k. Указать фиксированную величину или диапазон затруднительно, так как заведомо неизвестно, сколько будет всего значений в исходной таблице. Поэтому удобно снова обратиться к функции СТРОКА(). Если не вписывать в нее никаких условий, то функция вернет номер той строки, в которую она помещена. Например, на рисунке 9 показан шаблон отчета, поля которого нужно заполнить. Если в аргумент k будет вписано выражение СТРОКА(), то для первой строчки отчета она вернет номер 6. И тогда функция НАИМЕНЬШЕЕ будет искать шестое значение в столбце «Дата отгрузки» листа 'Отчет об отгрузках'!. Чтобы сначала помещалось первое наименьшее, нужно из выражения СТРОКА() вычесть число верхних строк, не относящихся к массиву. Тогда формула СТРОКА()-5 будет возвращать значение 1 в первую строчку отчета, 2 – во вторую и т. д. Соответственно, функция НАИМЕНЬШЕЕ() сначала будет искать первое значение, затем – второе, пока не переберет все поля массива.
Рисунок 9. Заполнение массива данными
Функция НАИМЕНЬШИЙ() последовательно выдаст номера строк начиная с первой, в которых указаны даты отгрузок выбранного клиента, а ИНДЕКС() – их содержимое. Наконец, функция ЕСЛИОШИБКА(), в которую заключено все выражение, поможет очистить таблицу от некорректной информации.
Аналогично заполняются поля, связанные с поступлением денежных средств. В диапазон О6:О22 вводится формула: =ЕСЛИОШИБКА(ИНДЕКС('Отчет о поступлениях'!$B$4:$B$100;НАИМЕНЬШИЙ(ЕСЛИ($D$3='Отчет о поступлениях'!$D$4:$D$100;СТРОКА('Отчет о поступлениях'!$B$4:$B$100)-3;"");СТРОКА()-5));""). Далее в каждом диапазоне указываются соответствующие полю столбцы таблицы «Отчет о поступлениях».
Вопрос: Чтобы не менять границы отчета вручную, воспользуйтесь условным форматированием в Еxcel
Дата добавления: 2018-06-27; просмотров: 1690; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!