Поддерживаемые версии: MS Excel 97SR2/2000/2002/2003SP2.

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

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

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

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

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

При распечатке документов из программы НЭП данные переносятся (экспортируются) в 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 – эта опция позволяет автоматически увеличить высоту строк области в случае, если значения ячеек не помещаются в текущую указанную в шаблоне высоту. Вручную это достигается выделением строк области и двойным щелчком на границе между этими строками. Эта опция просто незаменима в случае, если в полях набора данных встречены строковые значения с переводом строки.

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

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

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

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

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

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

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

Count – количество значений в столбцу;

CountNums – количество не пустых значений в столбце;

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

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

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

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

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

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

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

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

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

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

Вы можете указывать одновременно до 16-ти полей, по которым можно группировать промежуточные итоги. В готовом отчете будут содержатся как оригинальные данные, так и строки с промежуточными итогами. А, в-третьих, область группируется слева направо. То есть, рассчитываются итоги по крайнему левому столбцу группировки, потом по столбцу, расположенному правее его, и т.д.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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