Содержание
Вы, наверное замечали, что при большом количестве строк распределяемая сумма распределяется не «по честному».
В данной обработке приведены несколько методов распределения суммы по таблице значений. При изменении суммы распределения и порядка строк базы можно получать совершенно разные результаты.
Вариант 1. «Стандартный» с отнесением не распределенных сумм на последнюю строку. Чаще всего встречается в программах 1С.
Вариант 2. «По эффективному остатку». Остаток округления распределяется повторно по новому коэффициенту на столько строк сколько осталось копеек. Пока все остатки не распределятся. При этом выбираются строки с наибольшей базой.
Вариант 3. «С пересчетом базы распределения». Строки сортируются по базе распределения. После распределения по строке уточняется база распределения для последующих строк. Работает сравнительно быстро с учетом объема математики.
Вариант 4. «Статистический». Как мне кажется наиболее точный. Остаток округления распределяется на столько строк сколько осталось копеек. При этом выбираются строки с наибольшей погрешностью в округлении. Реализован несколькими способами, в т.ч. с использованием всего одного пакетного запроса.
Колонка «Сумма» рассчитана с использованием коэффициента. С математической точки зрения результат правильный, но распределена не вся сумма из за ошибок округления. Колонка «Сумма 4» рассчитана с использованием 4 варианта. Как видите погрешность в расчетах, возникающая при округлении, полностью устранена, а результат наиболее близок к «математическому», т.е. при проверке по строкам мы будем получать минимальную погрешность в округлении.
Процедурки можно легко прикрутить к любой таблице значений или табличной части. Примеры вызовов внутри.
Для четвертого варианта есть четыре реализации: по таблице значений, по табличной части и по таблице значений через запросы.
Примеры использования для типовых конфигураций 1С:Предприятия 8.1
В обработке ОбработкаТабличнойЧастиТовары изменил процедуру РаспределитьСуммуПоКолонке. Исправлены ошибки распределения сумм типовых конфигураций.
- Пример использования в УТ на базе типовой обработки из версии 10.3.6.8
- Пример использования в УПП на базе типовой обработки из версии 1.2.21.1
- Пример использования в БП на базе типовой обработки из версии 1.6.14.4
Примеры использования для типовых конфигураций 1С:Предприятия 7.7
В обработке ГрупповаяОбработкаМнЧДокументов изменил функцию РаспределитьСуммуПоКолонке и обработку константы РозничныйТипЦен в процедуре ПересчитатьСтрокуТаблицы. Работает «правильнее» (исправлены ошибки) и быстрее типовой.
- Пример использования в ТиС на базе типовой обработки из версии 7.70.954
- Пример использования в Комплексной на базе типовой обработки из версии 7.70.487
Обработки для ТиС и Комплексной абсолютно одинаковые, поэтому можно использовать любой архив.
Инструкция по установке для 1С:Предприятия 7.7
Для установки обработок в конфигурации 1С:Предприятия 7.7 можно использовать следующий порядок действий:
- Проверить соответствие версии конфигурации и обработки. 7.70.954
- Скачать с сайта обработку и сохранить, например, на рабочий стол
- Запустить 1С:Предприятие 7.7 в режиме «Конфигуратор» и выбрать соответствующую базу
- Сделать архивную копию. В меню «Администрирование» — «Сохранить данные. » указать имя файла архива, например: db_20090220-1512.zip
- Открыть конфигурацию. В меню «Конфигурация» — «Открыть конфигурацию»
- В дереве конфигурации выделить строку «Обработки» — «ГрупповаяОбработкаМнЧДокументов»
- В меню «Действия» — «Внешний отчет(обработка)» — «Заменить на внешний отчет(обработку). » и указать имя скаченного файла на рабочем столе
- Сохранить конфигурацию. В меню «Файл» — «Сохранить»
- Закрыть конфигуратор и запустить базу в режиме 1С:Предприятия
- Проверить работу обработки в документе реализация с заполненными строками. Нажать кнопку «Действия. » — «Изменить спецификацию». Сменить режим работы «Установить ставку НДС» на «Распределить сумму по суммам». Ввести сумму. Нажать «Выполнить». Проверить результат
[03.11.2008] Добавлен вариант 4 (самый «честный»
[09.11.2008] Пример для УТ 8.
[10.11.2008] Пример для ТиС 7.
[06.01.2009] Пример для УПП 8.
[16.01.2009] Пример для БП 8.1
Многие сталкивались с такой проблемой, что при проведении документа начисление зарплаты, документ не проводится и возникает предупреждение — В строке номер «такой-то» табл. части «НДФЛ»: Сумма распределений по источникам меньше результата расчета.
Обычно это суммы в один или минус один рубль (колонка налог). Программа не знает, как их распределить, и бухгалтерам приходится распределять их вручную. Когда большое количество сотрудников, такая операция занимает некоторое время. Эта обработка упрощает работу бухгалтера.
Обработка ищет суммы нераспределенного налога 1 и -1 рубль.
Распределение начислений по платежам
Подробности Создано 26 Январь 2012
| Содержание |
|---|
| Программа на VBA |
| Формулы рабочего листа |
| Итеративный расчет |
| Заключение |
| partpayments_formulas.xls | [Метод формул] | 98 kB |
| partpayments_iterations.xls | [Метод итераций] | 33 kB |
| partpayments_vba.xls | [Метод VBA] | 47 kB |
При ведении финансового учета в электронных таблицах обычно ограничиваются, так называемым «котловым» методом расчета задолженности контрагентов. То есть начисления и платежи не связываются между собой, а рассчитывается только разница, образующая кредиторскую или дебиторскую задолженности. Используя нормативы отсрочки платежа можно также определить уровень просроченной задолженности без точной привязки оплаты и отгрузки. Этой информации вполне достаточно для целей управленческого учета (т.е. для предоставления информации руководству). При этом иногда такой расчет может противоречить информации бухгалтерского учета – контрагент может потерять или пропустить счета для оплаты. В этой ситуации поиск расхождений представляет достаточно сложную задачу в зависимости от количества операций. Аналогичная задача сверки и распределения оплат по начислениям возникает при предоплатной схеме работы с контрагентом. Типичный случай – это работа с таможенными органами, когда средства списываются с депозитов на основании данных государственных таможенных деклараций (ГТД).
Подбор счетов к оплатам или анализ списаний, несмотря на кажущуюся простоту, представляет собой нетривиальную для решения в электронных таблицах задачу. Требуется указать список и сумму начислений, относящихся к каждой оплате. При этом суммы платежей и начислений могут быть произвольными, нет ограничений на кратность или размер оплаты. Алгоритм расчета носит циклический характер, что затрудняет решение с использованием формул рабочего листа. Тем не менее, при известных ограничениях можно построить последовательность формул.
В файлах, приложенных к статье, задача решена тремя различными методами:
- программа на VBA
- формулы рабочего листа
- итеративный расчет Excel без VBA.
Все файлы примеров используют один и тот же набор исходных данных. Следует заметить, что общую сумму неиспользованных платежей определить очень просто – надо вычесть сумму начислений из суммы платежей. Но задача состоит не в поиске этого значения, а в подборе информации о списании затрат по платежам. Решением задачи является текстовая информация с номерами ГТД и суммами по ним в каждой строке оплаты. Для сокращения текста принято условие, что если ГТД списана полностью, то указывается только ее номер, а если частично, то номер и сумма.
Программа на VBA
После открытия файла необходимо включить поддержку макросов. Для запуска расчетной процедуры необходимо нажать кнопку «Расчет».
При редактировании исходных данных, обратите внимание на именованные диапазоны rngCosts и rngPayments (границы выделены жирной рамкой) – программа анализирует информацию только в их пределах. Можно свободно добавлять строки внутрь диапазонов. Подбор будет производиться в порядке расположения строк, вне зависимости от поля даты или других параметров.
Как ни странно, этот способ, пожалуй, самый простой для понимания из всех представленных. Трудности могут возникнуть у тех, кто совсем не знаком с программированием. Алгоритм расчетной процедуры предельно прост:
- копируются данные о начальных остатках по ГТД и оплатам
- запускается цикл по двум диапазонам данных
- на каждом этапе анализируется текущее значение для списания и уменьшается остаток ГТД и остаток платежа на меньшую из этих величин. Таким образом, сумма в остатке одного из диапазонов становится равной нулю.
- в поле результата добавляется информация о списании
- производится переход к следующей строке того диапазона, сумма в котором достигла нуля
Код основной процедуры расчета:
Функции RngCost_ и RngPay_ служат в основном для удобства восприятия текста программы.
Формулы рабочего листа
Как уже отмечалось, алгоритм расчета носит циклических характер – т.е. требует повторения одних и тех же действий несколько раз. Через формулы рабочего листа впрямую этот механизм не реализуется. В нормальной ситуации при стандартных параметрах вычислений Excel – такой алгоритм приводит к ошибке «циклическая ссылка» в тот момент, когда формула попытается быть рассчитанной второй раз. Для реализации подобных расчетных процедур требуется ввести ограничение на возможное количество повторяющихся действий и создать последовательность одинаковых формул.
В представленном файле-примере используются ограничения:
- максимум 10 платежей
- максимум 20 ГТД
Имеется 2 служебных массива расчетов по ГТД и по оплатам.
В служебном массиве справа от диапазона ГТД хранится информация об оплатах. Каждая оплата разбита на два столбца, где рассчитывается:
1) остаток оплаты
Если текущий шаг, то уменьшается остаток по ГТД.
Функция SUMIF($F$6:F$6;»>0";$F8:F8) выбирает для анализа только нечетные столбцы служебного диапазона.
2) остаток начисления по ГТД по данной оплате
Если текущий шаг, то уменьшается остаток по оплате.
В служебном массиве справа от строк платежей хранится информация о всех ГТД. Рассчитывается:
1) сумма ГТД в данной оплате
Выбирается разность значений из служебной таблицы ГТД по остаткам до и после шага.
Функция OFFSET используется для вычисления адреса в служебном диапазоне ГТД.
2) формируется информационный текст платежа
Формируется текст служебной информации для данного шага. Этот значение добавляется к тексту, сформированному на предыдущем шаге.
Функция TEXT(H25;"0") преобразует число в текст без дробной части.
На последнем этапе формируется полный текст с информацией о платеже. Данные берутся из последнего служебного столбца:
Формула убирает запятую, сформированную при добавлении информации на каждом шаге.
Все ячейки, имеющие одинаковый цвет фона, содержат копируемые формулы (идентичные в записи R1C1).
Недостатки этого метода очевидны – необходимо предварительно заготовить служебные диапазоны на определенное количество строк. Для расширения лимитов можно скопировать формулы в служебных столбцах вправо. Кроме того, при добавлении строк необходимо аккуратно копировать формулы, так как в некоторых служебных ячейках использованы ссылки вверх. Если применять эту модель для реальной работы, то лучше перенести массив формул из столбцов в строки на других служебных листах – таким образом можно существенно расширить ограничения.
Итеративный расчет
Данный способ уже неоднократно описан в других статьях на нашем сайте. Назвать его популярным в использовании довольно сложно, но показать еще раз возможности этого метода решения задач в Excel будет нелишним.
По описанию задачи сразу можно заметить, что там, где используются циклические вычисления, можно попытаться решить задачу методом итераций.
Обратите внимание на включенный параметр итерационных вычислений после открытия файла. Файл необходимо открывать в отдельном приложении Excel, либо после закрытия всех остальных открытых файлов.
Для запуска расчета требуется ввести в ячейку B5 любое, отличное от нуля, число, затем запустить расчет – проще всего нажать клавишу [F9]. Для изменения исходных данных надо очистить ячейку B5 (или ввести туда «0») и также запустить пересчет. Ячейке B5 присвоено имя flag, используемое во всех расчетных формулах.
Алгоритм расчета с использованием итераций аналогичен примеру, реализованному на VBA. Но для понимания он гораздо сложнее. Используются два служебных диапазона с текущими остатками по ГТД и платежам – скрытый столбец F. Основной механизм цикла находится в скрытых строках 6-8.
Рассмотрим формулы поподробнее.
Ячейки B6 и B7 содержат информацию о текущем шаге для массивов.
Текущий шаг определяется через сравнение общего количества строк с нулевыми остатками (уже обработанными строками).
Во всех формулах начальная часть текста =IF(flag=0;0;… предназначена для сброса значения в исходное состояние при выключенном механизме итеративного расчета.
Ячейки C6 и C7 содержат формулы с вычисляемой адресацией для выборки значений остатков ГТД и платежа по текущему шагу. Формула в ячейке С8 выбирает сумму, которая должна быть списана на текущем шаге.
Служебные диапазоны в столбце F содержат копируемые формулы, вычисляющие новое значение остатка для текущего шага.
Условие IF($B$6<>ROWS(F$12:F13)-1;… является проверкой соответствия строки текущему шагу массива ГТД.
Аналогично условие IF($B$7<>ROWS(F$29:F30)-1;… является проверкой соответствия строки текущему шагу массива платежей.
В ячейках D6 и D8 формируется текстовая информация для платежа текущего шага.
D8 – сумма списания по ГТД
В результате в ячейках со светло-желтым фоном формируются остатки по платежам и заполняется текстовая информация о списанных ГТД.
Выбирается текущее значение из служебного массива платежей.
К текущему значению ячейки добавляется текст из ячеек D6 и D8.
Заключение
Все методы решения задачи можно применять на практике.
Пример с использованием итераций, пожалуй, наиболее сложен для понимания формул, и рекомендуется только для «продвинутых» пользователей, принципиально не желающих изучать VBA. Главным недостатком итеративного метода расчета, помимо сложности для понимания, является неудобство его использования вместе с другими расчетными процедурами в составе больших файлов, так как параметры вычислений Excel будут действовать на все открытые рабочие книги.
Пример с использованием формул рабочего листа имеет существенные недостатки из-за введения искусственных ограничений. Зато он может применяться в файлах с дальнейшими расчетами.
Однозначно настаивать на методе решения задачи при помощи VBA также нельзя. Во-первых, это требует от пользователя знания основ программирования. Во-вторых, VBA может быть просто отключен стандартами корпоративной безопасности на предприятии.
Вообще-то есть смутное подозрение, что рассмотренная в примерах задача может быть решена гораздо проще без «заумных» методов. Если кто-то знает, подскажите. Но даже в этом случае, описанные методы работы могут пригодиться на практике для других задач.