Как сделать дебиторку в экселе
Анализ дебиторской задолженности в Excel
В анализе дебиторской задолженности некоторые задачи, которые на первый взгляд кажутся сложными, на поверку часто оказываются простыми. Достаточно только вникнуть в их суть и воспользоваться для их решения программой Excel. Научимся вычленять из общего списка тех клиентов, сумма задолженности которых больше судебных издержек.
Расчет количества просроченных дней
В ячейке B2 текущая дата прописана не цифрами, а формулой, чтобы при открытии документа всегда проставлялась актуальная дата. Столбец с датами отгрузки товара представлен в формате ДАТА, а с суммами задолженности – в финансовом.
Чтобы рассчитать количество просроченных дней по дебиторской задолженности, нужно из фактической даты, на которую должен был быть произведен платеж, вычесть текущую. Добавим еще один столбец, в котором запишем простую формулу: прибавим к дате отгрузки количество дней отсрочки. И протянем формулу до конца таблицы.
По дебиторке получилось, что, ИП «Карпов», например, должен был выплатить задолженность еще 4 февраля, а сегодня уже 3 марта. А вот у предприятия ИП «Стригунова» еще есть 6 дней для выплаты, т.к. ее крайний срок – 9 марта.
Теперь подсчитаем количество просроченных дней, не забыв изменить формат ячеек нового столбца на числовой.
Расчет пени за период просрочки
Просрочка клиента не должна оставаться безнаказанной. Поэтому начисляем пеню, которая составляет 0,1% за каждый просроченный день. Умножим 0,1% на сумму долга и количество дней просрочки.
Двоих клиентов без долга, выделенных красным, пока скроем. Но убирать из списка не будем, чтобы при открытии этого же документа через неделю, задолженность просчиталась автоматически. Выделим обе строки, кликнем правой кнопкой и выберем СКРЫТЬ.
О том, что у нас есть еще два клиента, напоминает нарушенная последовательность строк.
Расчет ставки рефинансирования на день расчета
Второй вариант начисления процентов на сумму долга – в зависимости от ставки рефинансирования на день расчета. Предположим, она составляет 10%. Умножаем ставку на количество просроченных дней и на сумму долга, поделенную на 365.
Видим, что пени при таком расчете получились меньше, чем при прибавлении 0,1% за каждый день просрочки. Поэтому делаем вывод, какой способ начисления процентов выгоднее указывать в договоре.
Как вычленить недобросовестных клиентов
Осталось главное: определить искомых клиентов. Для начала сложим долг и пени, начисленные через 0,1%.
Предположим, что судебные издержки составляют 5000 рублей на одного клиента. Подсчитаем внизу сумму, которую мы сможем получить после подачи в суд на тех, у кого задолженность более 5000. Для этого понадобится функция СУММЕСЛИ.
Первый аргумент: диапазон, в котором будет искаться критерий. Второй: собственно, критерий, (>5000). Третий: диапазон суммирования (он совпадает с первым). И не забываем вычесть скрытых Стригунову и Малышева (H12 и H13). Получаем 73984 рубля.
Чтобы быстро определить тех, на кого следует подать в суд, можно воспользоваться функцией ЕСЛИ. Пропишем ее в новом столбце.
Прочитать формулу можно так: если общая сумма задолженности превышает 5000 рублей (H4>5000), то выводим «в суд». В противном случае выводим пробел. Таким образом, мы вычленили клиентов, чья сумма задолженности превышает судебные издержки.
Дебиторская задолженность в Excel с условным форматированием
Финансово-бухгалтерские или ERP системы преимущественно предлагают инструменты служащие для анализа дебиторской задолженности за период времени определяемый пользователем. Если же нет такого инструмента или он не соответствует всем требованиям пользователя, то лицо занимающиеся взысканием по дебиторской задолженности существенно ограничивается в своей профессиональной деятельности. Тогда на помощь приходит условное форматирование ячеек, которое позволяет выполнить и упростить анализ дебиторской задолженности в Exel. В данном примере покажем, как в Excel сделать отчет по дебиторской задолженности клиентов.
Простейший отчет по дебиторской задолженности в Excel
Сначала необходимо скопировать на рабочий лист список фактур по определенному контрагенту и сроки оплат. Конечно можно фильтровать данные фактур по датам и таким способом хронологически сегментировать их для взыскания задолженности. Однако в нашем случае необходимо дополнительно разделить журнал истории фактур на несколько периодов продолжительности времени для взаиморасчетов до просрочки дебиторской задолженности.
Ниже на рисунке представлена таблица со списком фактор из истории взаиморасчетов с клиентами фирмы, который имеет 4 столбца:
С целью создания динамического инструмента для поиска просроченных дебиторских задолженностей необходимо предусмотреть два нюанса:
Для актуализации текущей даты перейдите в ячейку F1 и выберите инструмент: «ФОРМУЛЫ»-«Библиотека функций»-«Дата и время»-«СЕГОДНЯ». В результате будет введена функция =СЕГОДНЯ().
Данная функция не требует аргументов. Она возвращает дату на сегодняшний день. В Excel любая дата — это число, которое является порядковым номером дня начиная от 1 января 1900 года. Если ячейка F1 имела «Общий» формат до введения функции СЕГОДНЯ, то после ввода ее формат автоматически изменяется на «Дата». Если нужно узнать какое число присвоено для текущей даты, то достаточно изменить формат ячейки F1 на «Числовой». Для пользователя значение, которое возвращает функция СЕГОДНЯ более полезнее если оно будет отображаться в формате «Дата». А для формул более важно числовое значение, которое возвращает эта функция.
После ввода функции в ячейке F1 отображается актуальная дата на сегодняшний день. Благодаря этому теперь при каждом открытии данной рабочей книги Excel автоматически будет вводиться дата текущего дня в ячейку F1. И нет необходимости постоянно помнить об ручном обновлении текущей даты.
Работа с дебиторской задолженностью в Excel
Используя условное форматирование, отметим разными цветами строки с фактурами для каждого периода допустимой дебиторской задолженности:
Так как каждое новое условие выделения цветом ячеек охватывает больший диапазон промежутка времени следует применять их в обратном порядке для сохранения последовательности приоритетов правил. Ведь в условном форматировании Excel каждое созданное новое правило имеет более высокий приоритет по отношению к старым. И при накладке дат цвета будут перекрывать друг друга – что не есть хорошо. Но в любые случаи имеются средства для управления приоритетами порядка выполнения правил, о которых будет упомянуто ниже. Для этого:
В результате наш отчет получил выделение красным цветом тех строк, которые содержат данные документов с датами старше 60-ти дней дебиторской задолженности начиная от сегодняшнего дня:
Теперь необходимо создать еще 3 правила условного форматирования для текущей табличной части:
Готов отчет по дебиторской задолженности с четырьмя разными цветами выделения строк по условию просроченных документов для каждого диапазона дат:
Теперь таблица имеет читабельный вид и хорошо подходит для визуального анализа данных. Более того можно выполнять фильтр или сортировку по цвету ячеек:
Если нам вдруг потребуется изменить диапазоны дат для просроченных дебиторских задолженностей следует выделить диапазон ячеек A2:D15 и выбрать инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».
В появившемся диалоговом окне «Диспетчер правил условного форматирования» где нам доступны все правила для выделенного диапазона. Здесь х можно изменять, редактировать, создавать новые или удалять лишние правила. На против каждого правила в секции «Применяется к:» доступны к редактированию ссылки на диапазоны ячеек, к которым применено конкретное правило.
Так же важно отметить что именно здесь в диспетчере правил условного форматирования настраиваются приоритеты и порядок выполнения правил условного форматирования с помощью стрелок вверх и вниз, которые отмечены на рисунке. Этими стрелками мы настраиваем и управляем приоритетами при неправильной последовательности выполнения правил или наложении цветов друг на друга. Чем выше правило находиться в диспетчере, тем выше его приоритет выполнения. То есть если бы мы создавали правила не в обратной последовательности заданных условий в поставленной задаче, то все было бы выделено красным и оранжевым цветом. Ведь самого нового верхнего (созданного в последнюю очередь) правила всегда будет выше приоритет.
Пример функции СУММЕСЛИ в Excel при анализе дебиторской задолженности
Практический пример применения функции Excel «СУММЕСЛИ» при анализе дебиторской задолженности из списка клиентов.
Делаем выбор компаний-контрагентов, объем денежной задолженности, которых превышает сумму издержек, в случае судебного разбирательства.
В условиях рыночной экономики подавляющее число предприятий работают по принципу отсрочки платежа. Как следствие, около девяноста процентов всех фирм непременно сталкиваются с проблемами просроченной дебиторской задолженности. Нередко дело доходит до судебных разбирательств.
При этом общий объем просроченной дебиторской задолженности может быть как незначительным, так и вовсе мизерным. Другими словами, нет особого смысла доводить дело до суда, потому как уровень судебных издержек превышает объем просроченной оплаты.
Сегодня мы будем составлять каталог компаний-неплательщиков, а также определять те предприятия-должники, на которых можно будет подавать в судебные инстанции, рассчитывая на положительный исход дела (при учете выплаты всех набежавших процентов).
Для этого будет использоваться оператор «СУММЕСЛИ» в документе Excel.
Начинать сортировку контрагентов-должников нужно с того, что в документе Excel создается каталог фирм следующего вида:
Применив пакет информации о времени отгрузки товара (либо поставки услуги) можно вычислить крайний временной срок оплаты по дебиторской задолженности, исходя из представленной отсрочки. Необходимо прибавить число дней предоставленной отсрочки ко дню, когда была произведена физическая поставка продукции.
Дабы можно было добавить количество дней ко времени отгрузки товара в файле Excel, следует выполнить следующий порядок действий.
Вычисление всех просроченных суток
Для начала активируется соответствующая ячейка, после чего в ней же прописывается соответствующая информация (без пробелов), а также производится активация необходимых полей следующим образом:
= ДАТА + ЧИСЛО и нажимаем ENTER.
В случае если дата не была отображена в таблице соответствующим образом, следует проверить формат поля, и увериться в том, что он установлен на значении «Дата»:
Если же все действия выполнены правильно, получаем следующее:
Теперь можно приступить к расчету всех просроченных суток. Для этого следует вычесть с текущей даты расчета день последнего срока оплаты дебиторской задолженности.
Быстро и эффективно вычесть в документе Excel из одной даты иную, следует сделать следующее:
Проводится активация необходимой ячейки, после чего в ней же прописывается (БЕЗ ПРОБЕЛОВ) необходимое число. Также можно произвести активацию ячеек следующим образом:
= ДАТА(1) – ДАТА(2) ENTER.
Если в конечном результате дата отобразилась некорректно, стоит проверить, какой установлен на данной ячейке формат. Если же установлен какой-нибудь другой, отличающийся от «числового», его стоит заменить.
В текущем случае будем ссылаться только на одну и ту же дату, а потому можно воспользоваться абсолютной ссылкой.
Протянув нашу формулу до конца таблицы Excel, мы получим следующее:
Теперь можно приступать к расчету начисленного процентного штрафа за просрочку оплаты.
Расчет пени за все время просрочки
Рассмотрим два возможных варианта:
Ежедневно на сумму просроченной дебиторской задолженности начислялся 0,1 процент в сутки.
Исходим из объема рефинансирования на время расчета (возьмем десять процентов)
Расчет начисленной пени в документе Excel:
1. Ежедневно на сумму просроченной задолженности начислялось 0,1 процент
Проводим активацию необходимой ячейки в таблице, а также прописываем в ней соответствующие числовые значения (БЕЗ ПРОБЕЛОВ). Проводим активацию следующим образом:
= 0,1 процент х объем просроченной задолженности х число просроченных календарных суток ENTER.
2. Процент рефинансирования на время расчета с контрагентом (десять процентов).
Производим активацию необходимой ячейки в Excel. Можем также активировать требуемые поля следующим способом:
= 10 процентов х Объем задолженности контрагента/365 х Число просроченных календарных дней ENTER.
Просуммируем рассчитанный объем набежавшей пени и общую сумму просроченной дебиторской задолженности.
Теперь можно приступить к ранжировке тех предприятий-должников, на которых есть экономический смысл подавать в судебные инстанции.
Логический оператор «СУММЕСЛИ»
Допустим ситуацию, при которой стоимость судебных издержек достигает 950 российских рублей в расчете на одного клиента. Дабы узнать объем денежных средств, на которую можно рассчитывать после положительного завершения судебной тяжбы, используем логический оператор.
Сделаем активную ячейку, в которой должен быть получен расчет денежных средств. Находим в меню (выделено красным цветом) «Математические» и выбираем «СУММЕСЛИ».
Теперь посмотрим, что нужно указать:
Давайте разбираться по порядку.
Окно под названием «Диапазон» необходимо с той целью, дабы можно было выбрать сферу значений, среди которых будет произведена необходимая нам выборка. В данном случае, это должна быть вся информация в последнем столбике, помимо итогового объема денежных средств.
Окно «Критерий» необходимо для того, чтобы можно было задать требуемые условия ранжировки. В текущем случае, этот диапазон будет достигать «>950».
«Диапазон суммирования» требуется для установления значений, объем которых нам необходимо рассчитать при учете условия. В частности, окно «Диапазон» будет совпадать с «Диапазон суммирования» по причине того, что нами устанавливается числовое значение, которое имеет прямое отношение исключительно к последнему столбцу.
После того как заполнены все поля:
По результату произведенных расчетов, мы получаем общий объем денежных средств, планирующихся к получению при исключении всех компаний-должников, чей объем долга не превышает девятьсот пятьдесят российских рублей (стоимости судебного разбирательства).
Для практической демонстрации возможностей «СУММЕСЛИ» приведем еще одну выборку контрагентов.
В первом приведенном примере «Диапазон суммирования» и «Диапазон» полностью совпали. Теперь же рассмотрим случай, при котором в данных столбцах указываются совершенно иные значения.
К примеру, наша задача заключается в расчете объема денежного долга только по «Контрагенту №1»
В данном случае, нам следует в окне «Диапазон» проставить значения первого столбца, а в окне «Критерий» вручную прописать «Контрагент №1». Также можно выбрать любую ячейку, которая содержала бы в себе подобные значения.
В окне «Диапазон суммирования» стоит выбрать последний столбец, потому как нам необходимо выбрать дебиторскую задолженность по Контрагенту №1 из общего объема.
Получаем следующий результат:
Как видно из представленного примера, функция Excel «СУММЕСЛИ» располагает широким спектром возможностей, а при правильном использовании может оказать незаменимую помощь для решения многих задач.
Как создать шаблон для быстрого поиска данных о дебиторе в Excel
Как контролировать дебиторскую задолженность с помощью Excel
Иветта Новикова, финансовый менеджер ООО «ТЕТ-а-ТЕТ»
Чем полезно это решение
Это решение поможет сформировать оперативный отчет о работе с дебитором на текущую дату. Оно подскажет, как с помощью Excel собрать все данные о работе с ним: задолженность, последние отгрузки, оплаты. Предложенную модель без труда можно адаптировать под нужды компании.
Преимущества и недостатки
Универсальность предложенного подхода можно отнести к главным преимуществам решения. Предлагаемый алгоритм проработки информации можно использовать не только для анализа дебиторов, но и для оперативной консолидации или выделения любых других данных. Для его реализации не потребуется писать макросы, достаточно стандартных формул Excel.
Информация об отгрузках и оплатах клиентов компании может содержаться в нескольких отчетах. Быстро сгруппировать только самые необходимые данные в одну таблицу, не прибегая к ручному поиску и копированию, можно с помощью Excel. Для этого нужно:
· определить, какие цифры нужно собрать;
· выбрать источники информации, которые потребуются для отчета;
· структурировать необходимые данные;
· воспользоваться специальными формулами Excel.
Тогда форма итоговой таблицы будет выглядеть как на рисунке 1.
Рисунок 1. Форма для поиска информации по дебитору
В готовой форме нужно только выбрать из выпадающего списка искомого дебитора и вручную ввести в поле «Текущая дата» день и месяц. Таблица автоматически заполнится информацией об отгрузках и оплатах по дням, данными о задолженности на начало периода (например, года) и на дату составления отчета, вычленит сумму просроченных долгов.
Как подготовить исходные данные для контроля задолженности контрагента с помощью Excel
Подробные сведения о каком-либо клиенте могут понадобиться финансовой службе при серьезном нарушении им условий договора. Чтобы разобраться в ситуации, потребуется вся информация о его отгрузках:
· даты, номера накладных, счетов и заказов;
· суммы бонусов и скидок, если они предоставлялись;
· планируемые даты оплат;
· фактические даты и суммы оплаты каждого счета;
· количество дней просрочки.
В качестве дополнения (если есть данные в управленческом учете) по тем же отгрузкам:
· прибыль, полученная от отгрузки;
Исходную информацию для подготовки отчета с таким наполнением можно найти в следующих отчетах:
· об отгрузках (например, см. Отчет о продажах по покупателям);
· о рентабельности продаж;
· о дебиторской задолженности;
· о просроченной дебиторской задолженности;
· о движении денежных средств;
· о поступлении денежных средств от клиентов и в других документах (подробнее о том, какие еще формы могут пригодиться, см. Какой отчет поможет контролировать рентабельность продаж).
Вполне возможно, что данные в них заполняют разные службы. Отчет об отгрузках может вести коммерческая служба нарастающим итогом ежедневно, отчет о дебиторской задолженности – она же, но ежемесячно. Отчет о движении денежных средств в компетенции финансовой службы и заполняется еженедельно. Тогда процесс сбора аналитики по конкретному клиенту может оказаться слишком трудоемким.
К примеру, в компании «Альфа» коммерческая служба ведет отчет, представленный на рисунке 2. В нем собирается информация из управленческой учетной системы, данные финансовой службы и менеджеров продаж. Отгрузки фиксируются ежедневно в календарном порядке.
Рисунок 2. Отчет об отгрузках
В свою очередь финансовая служба заполняет сведения о поступлениях и выплатах в виде отчета, представленного на рисунке 3. В нем отображаются данные не только о клиентах, но и о других оборотах по расчетным счетам компании – кредитные средства и займы, инвестиции, прочие доходы от неосновной деятельности и т. д.
Рисунок 3. Отчет о поступлениях денежных средств
Чтобы прийти к форме, показанной на рисунке 1, необходимо создать соответствующий ей шаблон в новой книге Excel и дополнить его справочником (см. рисунок 4. Справочник дебиторов). В справочнике можно привести данные о дебиторской задолженности на начало отчетного года по каждому клиенту.
Рисунок 4. Справочник дебиторов
Если отчеты об отгрузках и поступлениях хранятся в разных источниках, то лучше продублировать их в той же книге, где будет лежать шаблон поиска данных по клиенту. Это позволит обеспечить сохранность данных и избавит от необходимости увязывать разные книги 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;»»), где:
· СТРОКА(‘Отчет об отгрузках’!$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 ; просмотров: 927 ;