Шаблоны первичных документов

Поддерживаемые версии Excel: 2000/2002/2003SP2/2010/2016/2021/2024/365.

Внимание! В Excel 2002/2003 необходимо включить доверительный доступ к VB-проекту (меню “Сервис\Макрос\Безопасность…”, закладка “Надежные издатели”, флажок “Доверять доступ к Visual Basic Project”).

• Создайте при помощи Excel новый файл (например: РасхНакладная.xls). На основе этого файла вы будете создавать новый шаблон первичных документов.
• Сохраните этот файл в папку с шаблонами «НЭП». Путь к этой папке вы можете найти в окне «Редактирование списка рабочих баз», которое можно открыть из стартового окна «НЭП» (кнопка под списком рабочих баз).

Новый шаблон необходимо регистрировать для каждой из своих фирм по каждой из операций.

• Откройте окно Меню\База\Настройки\Установки, перейдите на закладку «Операции».
• Выберите из выпадающего меню свою фирму.
• Выберите в таблице операцию.
• В нижней части окна находятся 6 полей ввода для определения наборов шаблонов в зависимости от направления документа (входящие/исходящие) и типа операции (резерв/оплата/реализация).
• Выберите поле ввода (например: Шаблоны для исходящих документов: реализация) и добавьте в него строку вида: Расходная накладная=РасхНакладная.xls, где фраза слева от знака равенства – наименование шаблона, которое вы увидите в выпадающем списке кнопки «Печать» окна редактирования документа, а фраза справа от знака равенства – имя файла-шаблона, который вы регистрируете.
• После того, как вы добавили строку во все необходимые поля (по своим фирмам и операциям), нажмите кнопку «Применить» внизу окна. Шаблон зарегистрирован.

Примечание: Для привязки к документу зависимого отчёта в список шаблонов необходимо добавить командную строку <:Reports>. В результате к списку шаблонов печатных форм в документе добавится подменю «Отчёты по документу». Подробнее о зависимых отчётах см. в разделе «Отчёты».

Печатная форма со временем меняется: новый бланк, другая ставка НДС, другой состав колонок. При этом документы прошлых периодов должны печататься по тому бланку, который действовал на их дату, а не по текущему.

Для этого в регистрационной строке в имени файла пишется тег <:DATE>:

Расходная накладная=РасхНакладная<:DATE>.xls

А в папке шаблонов лежат файлы с датой в имени: на месте тега стоит _D и дата в формате ГГГГММДД.

РасхНакладная_D20230101.xls
РасхНакладная_D20260101.xls

Дата в имени — это дата, с которой шаблон действует. При печати программа смотрит на дату документа (Дата1, поле CHECK_DATE) и берёт самый поздний шаблон, дата которого не больше даты документа. Документ, датированный ровно днём начала действия, печатается уже по новому шаблону.

Если документ старше всех датированных шаблонов, программа возьмёт файл с «чистым» именем (РасхНакладная.xls), если он есть в папке. Если такого файла нет — возьмётся самый ранний датированный шаблон, чтобы печать не прерывалась ошибкой «шаблон не найден».

Отсюда два рабочих приёма:

• положить рядом файл без даты в имени — он будет работать как «шаблон для всего, что было раньше»;
• не класть его вовсе — тогда старые документы просто печатаются по самому раннему бланку.

Где тег работает. При печати из документа — одинаково для Excel, Word и «блокнота», в том числе при групповой печати (каждый документ получает шаблон по своей дате) и при Ctrl+клике по названию шаблона (открыть шаблон на редактирование). В конструкторе отчётов (меню «Отчёты») тег не разбирается — там шаблон выбирается явно.

Если дата в имени записана неверно (на месте тега не 8 цифр), шаблон пропускается, в лог программы попадает запись Ошибка [FindTemplateByTag], а печать идёт по имени без тега.

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

При экспорте в Excel все наборы данных делятся на две группы: NoRange-наборы данных и Range-наборы данных.

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

Для использования данных NoRange -набора при непосредственном выводе или использования в формулах необходимо в ячейке шаблона Excel указать формулу с участием имени конкретного поля этого набора.

Имена имеют вид “Alias_FieldName”. Где Alias – имя таблицы NoRange-набора, а FieldName – имя поля этой таблицы.

При создании шаблона с несколькими листами вид переменных шапки остаётся таким же, т.е. на каждом из листов вы можете повторно использовать эти переменные.

При экспорте документа доступны следующие имена NoRange-таблиц:

TITLE – Шапка (заголовок) документа.

CLIENT – Реквизиты клиента документа.

FIRM – Реквизиты своей фирмы (подразделения) документа.

Описание полей указанных NoRange-наборов можно найти в таблице «Переменные шапки документа».

В примере будем использовать таблицу TITLE.

Простой вывод данных в ячейку (Номер документа):

В выбранной ячейке указываем следующую формулу: =TITLE_CHECK_NUMBER

Использование в формулах:
=ЕСЛИ(TITLE_CHECK_NUMBER1<>“”;TITLE_PAY_DATE;“”)
или
=TITLE_AMOUNT*TITLE_RANGE2

При экспорте в Excel по умолчанию передаются данные всех полей из No-Range набора. В случае, когда из NoRange-набора используется в отчете два-три поля, у вас может возникнуть вопрос о необходимости переноса значений всех полей. Было бы совсем не оптимально сканировать все ячейки шаблона для того, чтобы узнать какие поля NoRange-наборов использовать в отчете. Для увеличения скорости передачи данных существует способ явно указать, какие поля из набора данных участвуют в отчете. Назовите любую неиспользуемую ячейку шаблона именем набора данных (например: TITLES) и перечислите в ней через точку с запятой имена всех необходимых полей (например: CHECK_NUMBER; CHECK_DATE; USER_NAME; OPER_NAME). При экспорте данных программа будет использовать только их, а в отчете эту ячейку очистит.

Ячейки с формулами полей NoRange-наборов данных вы можете форматировать любым известным способом, применяя при этом и условное форматирование. На условном форматировании хотелось бы остановиться подробнее, так как это достаточно мощный способ условной визуализации данных.

Ячейке C3 присвоим значение =TITLE_CHECK_DATE.

Зададим в ячейке D3 формулу условного форматирования при выполнении условия =$C$3<“01.01.2003”

Range -наборы используются для экспорта в Excel таблиц с несколькими строками данных.

Для использования Range -набора необходимо указать в шаблоне область ячеек, куда будут переноситься данные и присвоить ей определенное имя, а именно Alias+«Range», где Alias имя экспортируемой таблицы, «Range» – ключевое слово для имен областей.

Для вывода таблицы с тем же именем на другом листе необходимо присвоить имя Alias+«Range 1», для третьего листа Alias +«Range 2» и т.д.

Область в шаблоне Excel имеет строгий формат:

1. Область может быть только прямоугольной формы.

2. Область должна быть непрерывной.

3. Ячейки области могут содержать формулы полей, обычные формулы Excel.

4. Ячейки могут быть пустыми.

5. При использовании формул в шаблонах Range областей есть только одно ограничение. Вы не можете использовать формулы в ячейках вне области, в которых имеются ссылки на ячейки области данных.

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

Для каждого Range-набора в шаблоне можно указать служебные опции.

Служебные опции помещают в левую нижнюю ячейки области и разделяют символом «;».

OnlyValues – эта опция позволяет отключить все формулы в области.

AutoFilter – эта опция включает автофильтр для области-списка. Использование этой опции в произвольных областях (где есть объединение ячеек или область данных Range -набора содержит несколько строк) приводит к ошибке.

RowsFit – эта опция подгоняет высоту строк области под содержимое ячеек. Вручную это достигается выделением строк области и двойным щелчком на границе между этими строками. Эта опция просто незаменима в случае, если в полях набора данных встречены строковые значения с переводом строки. Чтобы она работала, в свойствах ячейки должно быть включено «Переносить по словам».

RowsFit2 – то же самое, но высота строки меняется только в сторону увеличения: если значение помещается в заданную шаблоном высоту, она сохраняется. Обычный RowsFit работает в обе стороны и строку с запасом по высоте ужимает под содержимое, то есть заданная в шаблоне высота теряется.

ColsFit – эта опция делает автоматическое расширение столбцов области согласно значениям, содержащимся в ячейках области. Вручную это достигается выделением всех столбцов области и двойным щелчком на границе между этими столбцами. Эта опция просто незаменима в случае, если в полях набора данных встречены строковые значения, длину которых вы не в состоянии точно предсказать.

GroupNoSort – отключает предварительную сортировку при использовании промежуточных итогов «Group».

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

Строки области можно отфильтровать: не подошедшие записи в отчёт не попадут, а итоги будут посчитаны уже по отфильтрованным строкам.

Фильтр пишется в левую (служебную) ячейку строки данных области — в ту же строку, где стоят поля (=TABLE_GOODS_NAME и т.д.).

FILTER – условие в стиле SQL. Имена полей пишутся без префикса области.

Например, чтобы в расходную накладную не попадали услуги (тип позиции MIX_ID = 2):

FILTER=MIX_ID<>2

Поддерживаются: сравнения, AND / OR / NOT, скобки, IS NULL, LIKE, IN (…), BETWEEN … AND …, арифметика, сравнение поля с полем, даты.

EXFILTER – то же самое, но условие задаётся формулой Excel (поля пишутся полностью):

EXFILTER=И(TABLE_MIX_ID<>2; TABLE_QUANT>0)

Подробности и примеры — на странице «Опции шаблонов Excel».

Нижняя строка области Range-набора состоит из служебных ячеек для указания опций для соответствующих столбцов.

Область можно сортировать по столбцам. Это указывается опцией столбца «Sort» в служебной ячейке нужных столбцов. Вы можете включить сортировку по убыванию, дописав к опции «Sort» опцию столбца «Desc», разделив их точкой с запятой.

Сортировка в Range-наборах имеет свои ограничения. Во-первых, область может быть отсортирована одновременно максимум по трем столбцам — это ограничение метода Sort объекта Range. Во-вторых, Excel сортирует область только слева направо.

Важно. Если сортируемых столбцов окажется больше трёх, область не будет отсортирована вообще — не «по первым трём», а совсем, и никакого сообщения об этом не будет. Причём столбцы с Sort и столбцы с Group считаются вместе: три уровня группировки плюс один столбец сортировки — это уже четыре ключа. В таких случаях сортируйте данные заранее и ставьте области GroupNoSort.

Проектируйте области-списки в своих шаблонах, не забывая об этих ограничениях.

В служебной ячейке нужных столбцов можно указать опцию итого которая является аналогом Excel-функции «Промежуточные Итоги» и позволяет получить по указанному диапазону ячеек не только сумму, но и среднее, минимум, максимум и пр.. НЭП поддерживает все допустимые этой функцией виды итогов. Вот опции, которые вы можете использовать для получения различных итогов по столбцу:

Sum – отобразит сумму по столбцу;

Count – количество непустых значений в столбце, включая текстовые (функция СЧЁТЗ);

CountNums – количество числовых значений в столбце (функция СЧЁТ);

Avg или Average – среднее значение по столбцу;

Max – максимальное значение в столбце;

Min – минимальное значение в столбце;

Product – произведение по столбце;

StDev – стандартное отклонение;

StDevP – стандартное отклонение по генеральной совокупности;

Var – дисперсия;

VarP – дисперсия для генеральной совокупности.

Подробное описание всех функций вы найдете в электронной справке Excel. По столбцу можно получить только один итог. Попробуйте написать в шаблон предыдущего примера несколько опций итогов, разделив их символом «;». В качестве формулы итога будет использована в этом случае только последняя указанная функция. Обратите внимание на “Итого” в ячейке, расположенной слева от итоговой. В служебной строке, в случае если она не удаляется, вы можете использовать не только константы, но и формулы Excel.

Все перечисленные выше опции промежуточных итогов можно использовать совместно с опцией «Group». Опция «Group» должна находиться в столбцах, по которым вы хотите сгруппировать данные, получив указанные промежуточные итоги.

В готовом отчёте будут содержаться как оригинальные данные, так и строки с промежуточными итогами. Область группируется слева направо: сначала рассчитываются итоги по крайнему левому столбцу группировки, потом по столбцу правее него, и так далее.

Ограничений на число уровней группировки два, и оба жёстче, чем кажется: три ключа предварительной сортировки (см. выше, столбцы с Group и Sort считаются вместе) и восемь уровней структуры Excel.

В отличие от общих итогов по столбцу области, допустимо применять несколько сгруппированных промежуточных итогов. В тот же шаблон вы можете добавить опцию «Sum» и «Avg» по одному из числовых столбцов и посмотреть на готовый отчет. По этому столбцу вы увидите сумму и «среднее».

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

Если одна и та же позиция попадает в документ несколькими строками (разные партии, ГТД), её можно показать одной строкой, сложив количество и суммы.

GroupRowID – ставится в служебной ячейке столбца-ключа (столбцов-ключей может быть несколько).

GroupRowSum – ставится в служебной ячейке столбца, значения которого нужно складывать.

Остаётся первая строка группы, остальные удаляются. Две опции в одной ячейке разделяются символом ; – например Sum;GroupRowSum.

Подробности — на странице «Опции шаблонов Excel».

Если к опции Group предыдущего примера добавить параметр «Collapse» (Group\Collapse) и посмотреть на готовый отчет, то можно обнаружить, что дерево промежуточных итогов свернулось до уровня итогов по «Payment method».

Часто необходимо видеть сразу сгруппированные итоги, которые можно затем раскрыть, воспользовавшись деревом групп, расположенным слева номеров строк листа.

Дерево сворачивается до уровня того столбца, в котором стоит Collapse. Если опция указана в нескольких столбцах, работает самый левый из них, остальные игнорируются.

Помимо основных передаваемых данных, программа НЭП передает в отчет дополнительные информационные параметры.

Такие параметры описывается в шаблоне следующим образом:

В любой свободной ячейке шаблона необходимо написать следующее:

=XLRParams_ИмяПараметра,

где ИмяПараметра – наименование дополнительного информационного параметра.

Список передаваемых дополнительных информационных параметров (полное описание):

XLRParams_UserName – Имя Пользователя Программы, сформировавшего отчёт.

XLRParams_BaseAlias – Имя базы данных, из которой сформировался отчёт.

XLRParams_CompName – Имя компьютера, на котором сформировался отчёт.

XLRParams_AccountName – Имя аккаунта Windows, из под которого сформировался отчёт.

XLRParams_CreateDate – Дата формирования отчёта.

XLRParams_TargetIDs – Список ID атрибутов документа (вида: [id1][id2][id3], где idX – ID атрибута).

XLRParams_TargetNames – Список Имен атрибутов документа.

Помимо ячейки A1 (опции всей книги) можно использовать ячейку A2 любого листа — опции в ней действуют только на этот лист.

OnlyValues2 – выборочная заморозка формул: замораживаются только формулы со ссылками на данные программы (поля и XLRParams_), а пользовательские формулы Excel и итоги =СУММ(…) остаются рабочими.

FitMergedCells – подгон высоты строк под текст для ячеек вне Range-областей, в том числе объединённых (обычный автоподбор Excel их не умеет). Например:

FitMergedCells\E8,E9,E10,E11

RangeBreaks – разрывы страниц по области с повтором шапки таблицы на каждой странице, промежуточными итогами и заголовком листа («Лист 2 из 3»). Используется для УПД, счетов-фактур, ТОРГ-12.

Подробное описание и примеры — на странице «Опции шаблонов Excel».

Опции в A1 действуют на всю книгу. Кроме OnlyValues и DeleteMacro там задаются макросы шаблона:

MacroAfter=Module1.MacroAfter

• MacroBefore — макрос выполняется до построения отчёта;
• MacroAfter — после построения.

Эта опция перекрывает два прежних способа задать тот же макрос — именованную ячейку MACROAFTER в шаблоне и решётку в конце имени файла шаблона (РасхНакл#.xls). Оба продолжают работать, но считаются устаревшими: опция в A1 нагляднее и позволяет указать любое имя процедуры, тогда как решётка всегда подставляет только Module1.MacroAfter.

Подробно — на странице «Опции шаблонов Excel».

Title – Реквизиты документа.

Firm – Реквизиты своей фирмы (подразделения).

Client – Реквизиты клиента.

Advanced – Ссылка на зависимые документы.

Создаём новую область (Range) в шаблоне Excel. Эта область включает на один столбец слева и одну строку снизу больше, нежели необходимо для той строки таблицы, которая будет размножаться. Не включайте в эту область заголовок таблицы!

Для этого выделим прямоугольную область (например «A10:F11» для случая, когда размножается одна строка) и поименуем ее, использовав для этого поле редактирования с выпадающим списком, расположенное левее строки формул. Для первого листа Excel необходимо использовать в качестве имени TableRange, второго – TableRange1 и так далее.

Вставим в ячейки размножаемой строки переменные, руководствуясь следующим правилом: перед именем переменной необходимо добавить TABLE_ для именованной области TableRange или соответственно добавить TABLE1_ для именованной области TableRange1.

Например, переменная GOOD_ID для именованной области TableRange3 будет выглядеть следующим образом: =TABLE3_GOOD_ID.

Примечание 1. Для последующего переименования именованной области (Range) или изменения её размера используйте меню Excel: Вставка\Имя\Присвоить.

Примечание 2. Для автоматического увеличения высоты размножаемой строки (в случае,если информации больше, чем может поместиться в ячейке) впишите в левую нижнюю ячейку именованной области кодовое слово RowsFit. Для ячеек вне области (например объединённая ячейка с адресом клиента) используйте опцию листа FitMergedCells — см. «Опции шаблонов Excel».

Для скрытия формул при отображении документа Excel впишите в ячейку A1 первого листа шаблона Excel кодовое слово OnlyValues.