Использование MS Excel

Материал из AutoPark
Перейти к: навигация, поиск

Содержание

Отчеты с использованием сводных таблиц

Для удобства часть отчетов выполнена в виде сводных таблиц Excel. Помечаются в меню АвтоПарка знаком "[E]". Для каждого периода (или даты) выполнения отчета имеется возможность сохранить данные и внешнее представление отчета, чтобы воспользоваться ими впоследствии.

Прежде всего, для работы Excel-отчетов на конкретном компьютере, необходимо выполнить в АвтоПарк "Главное меню-Прочее-Администратор-Настройка системного окружения-... для работы с MS Excel".

Термины

<форма_отчета>
Название программного модуля, выполняющего отчет. Так же этим именем назван шаблон отчета и подкаталог в каталоге BASIS, содержащий книги Excel и файлы базисов.
<шаблон_отчета> 
Файл Excel, служащий образцом при создании нового отчета. Не содержит никаких данных и определяет лишь внешний вид нового отчета (группировка и сортировка данных, фильтры, раскраска и т.п.). Для одной формы отчета может быть подготовлено несколько шаблонов, (если для данной формы надо настроить много разных сводных таблиц, помещение их в один шаблон может создавать неудобства - например, длительное время обновления отчета).
Шаблон имеет имя <форма_отчета> или <форма_отчета>#NN, где # - символ "#", NN - номер шаблона. Например "T9021#01". Расширение - XLT.
<экземпляр_отчета>
Комплект из трёх файлов: книги и базиса. Имеет уникальное имя, присваиваемое файлам базиса и XLS-файлу книги. Его можно увидеть в заголовке окна Excel или в отчете на листе "Заголовок", там же рядом показан путь к этим файлам. Правила формирования имени экземпляра отчета:
На дату
<имя>_YYYYMMDD
За период (маленькие буквы - конец периода)
полный месяц
<имя>_YYYYMM
в пределах месяца
<имя>_YYYYMM_DD_dd
несколько полных месяцев
<имя>_YYYYMM_yyyymm
остальное
<имя>_YYYYMMDD_yyyymmdd,
где <имя> для книги = <шаблон_отчета>, для файлов базиса = <форма_отчета>.
Книга
Собственно отчет, сводная таблица Excel, построенная на основе шаблона и содержащая в себе данные из базиса. Внешний вид книги можно изменить (пересортировать данные, раскрасить таблицы) и сохранить, чтобы при следующем открытии отчета он предстал в том же виде. Находится в каталоге BASIS\<форма_отчета>\.
Базис
Данные, подготовленные в системе для отражения с помощью Excel. В случае каких-либо изменений в базе данных, которые должны повлиять на отчет, базис необходимо создать заново. Состоит из двух текстовых файлов: <экземпляр_отчета>.TXT – содержит данные, и <экземпляр_отчета>.CSV – содержит сведения о периоде (дате), за который(-ую) выполнялся отчет, исполнителе, подготовившем базис, дате создания отчета и его названии. Находятся в каталоге BASIS\<форма_отчета>\.
Обновить данные
При выборе в Excel кнопки Обновить данные с таким названием или пункта меню "АвтоПарк"-"Обновить данные" данные из базиса заносятся в сводную таблицу. Не путать со стандартным пунктом меню Excel "Данные"-"Обновить данные".

Шаблоны отчетов

Виды шаблонов

Шаблоны, предоставляемые Разработчиком
предоставляются с заранее разработанными формами отчетов. Шаблоны, предоставляемые Разработчиком, недоступны для редактирования.
Пользовательские шаблоны
находятся в каталоге XLT. Ни при каких обстоятельствах не модифицируются Разработчиком.

Выбор шаблонов

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

Создание нескольких шаблонов одного отчета

Некоторые отчеты требуется отображать в различных разрезах. Это приводит к увеличению количества листов в XLT-шаблоне и увеличению времени обновления. Чтобы избежать перегруженности отчета данными, существует возможность использования нескольких вариантов шаблона для одного отчета. Для создания дополнительного шаблона необходимо скопировать основной шаблон со специальным именем: <имя XLT-шаблона>#NN.xlt, где NN - двухзначный номер варианта шаблона (рекомендуется начать с номера 01, последующие варианты нумеровать в порядке возрастания). При подготовке отчета в АвтоПарке будет предложено выбрать нужный шаблон из списка доступных.

Подготовка шаблона

Процедура подготовки шаблона позволяет делать только пользовательские шаблоны (в качестве образца может выступать шаблон любого вида).

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

  1. Выполнить отчет и привести его к желаемому виду:
    1. Откройте любой имеющийся отчет или изготовьте новый, сделайте копию листа "Исходная", настройте нужный вид отчета.
    2. Когда получили на листе то, что хочется, дайте этому листу понятное название.
    3. Если требуется несколько разных форм (а обычно требуется), повторить п. 2-3 для каждой формы.
    4. В шаблон может быть добавлено текстовое описание. Рекомендуется размещать описание на листе "Заголовок". Добавление текстового описания:
      • MS Office XP/2003: выделить ячейку, в которой будет находиться описание, пункт меню "Вставка" - "Имя" - "Присвоить", присвоить имя "RepComment".
      • MS Office 2007: выделить ячейку, в которой будет находиться описание, на вкладке "Формулы" в группе "Присвоенные имена" выберите команду "Присвоить имя". В диалоговом окне "Создание имени" в поле "Имя" укажите имя "RepComment".
  2. В меню Excel «АвтоПарк» - «Подготовка шаблона» выбрать один из режимов подготовки Excel-шаблонов:
    БЕЗ зачистки данных
    Автоматически будет создан шаблон по подготовленному отчету с сохранением всех пользовательских настроек этого отчета (группировка, скрытие/отображение данных и т.п.).
    с ЧАСТИЧНОЙ зачисткой данных
    в этом режиме сохраняются пользовательские настройки в полях области страницы сводной таблицы и группировки полей. Из-за особенностей работы MS Excel могут быть сохранены только "пользовательские фильтры" (установка пользовательского фильтра в MS Excel 2007, установка пользовательского фильтра в MS Excel 2003), установленный отбор по единственному значению из списка возможных значений НЕ СОХРАНЯЕТСЯ. Таким образом, если в шаблоне требуется сохранить отбор по конкретному значению поля сводной таблицы, необходимо наложить пользовательский фильтр так, чтобы это значение осталось единственным доступным из всех возможных. Подготовка шаблона в этом режиме может занимать продолжительное время.
    с ПОЛНОЙ зачисткой данных
    Перед созданием шаблона будут зачищены все сводные таблицы отчета, все пользовательские настройки будут потеряны.
  3. После окончания процедуры подготовки будет предложено сохранить шаблон (по умолчанию - в каталог пользовательских шаблонов \AutoPark\XLT) с возможностью изменить имя шаблона. Если шаблон с указанным именем уже присутствует в выбранном каталоге, перед сохранением будет сделана резервная копия существующего шаблона с именем <имя шаблона>.xl_.

Ранее подготовленные отчеты

Если отчет выполняется за период (или дату), за который(-ую) он уже ранее запускался (т.е. базис уже создан), пользователю будет предложен выбор:

"Использовать имеющийся базис и Excel книгу" 
Будут использоваться базис и книга, полученные при предыдущем выполнении отчета. Данный пункт может отсутствовать, если отсутствует книга (например, удалена в целях экономии места на сервере).
"Использовать имеющийся базис и Excel шаблон"
Будет взят готовый базис, но старая книга будет удалена (если она существовала) и создана заново на основе шаблона.
"Заново построить базис"
Будут заново подготовлены данные базиса с учетом изменений в системе, которые произошли с момента предыдущего запуска отчета. Книга будет создана заново на основе шаблона.

Полезные приемы

Разделение поля ФИО по столбцам отчета

Разделение возможно только в обычных таблицах Excel, в сводных таблицах эта операция не может быть выполнена.

  1. При разделении текста по столбцам данные вставляются в режиме замены в соседние столбцы справа, заменяя данные и в текущем столбце. Если в заменяемых столбцах присутствуют данные, Excel выдаст соответствующее предупреждение. Для сохранения этих данных перед разделением текста рекомендуется либо вставить несколько (по количеству элементов ИО, разделенных пробелом, как правило - 2) столбцов справа от текущего столбца, либо указать начальную ячейку столбца, в которую будет помещен первый элемент (поле "Поместить в" мастера разделения текста по столбцам). Остальные элементы будут помещены в соседние столбцы справа от указанного.
  2. Выделить колонку
  3. Меню: Данные -> Текст по столбцам
Формат данных
С разделителем
Символ разделитель
Пробел

Размещение папки хранения отчетов вне папки AutoPark

  1. Для размещения отчетов в каталоге, отличном от AutoPark\BASIS, необходимо в RED-файле в раздел [ASB Special Folders] добавить новый путь в формате UNC
    BasisRoot = путь к папке, где необходимо хранить отчеты, например, \\Server1\AutoParkBasisAndXLT\BASIS.
  2. Для размещения пользовательских шаблонов в каталоге, отличном от AutoPark\XLT, необходимо в RED-файле в раздел [Redirection] добавить новый путь в формате UNC
    *.xlt = путь к папке, где необходимо хранить пользовательские шаблоны, например, \\Server1\AutoparkBasisAndXLT\XLT. Строку можно добавить выше или ниже уже существующей в RED файле строки "*.xlt = пути" раздела [Redirection].
  3. Наличие в путях к BASIS и к XLT папки с именем AutoParkBasisAndXLT (или любой другой, содержащей в имени AutoPark) обязательно.
  4. Перенос папок BASIS и XLT должен осуществляться одновременно (нельзя папку BASIS перенести, а XLT нет и наоборот).
  5. Копирование папок необходимо выполнять с сохранением разрешений NTFS. Это можно сделать, используя консольную команду xcopy (для получения справки о ключах команды наберите xcopy /?): Для выполнения консольных команд нажмите в Widows Пуск -> Выполнить. Введите команду cmd и нажмите ОК. В открывшемся окне введите:
    • xcopy /S /E /I /H /R /K /O /X AutoPark\BASIS \\Server1\AutoParkBasisAndXLT\BASIS
    • xcopy /S /E /I /H /R /K /O /X AutoPark\XLT \\Server1\AutoparkBasisAndXLT\XLT

Для выполнения

  1. Чтобы изменения red файла вступили в силу, пользователям необходимо перезайти в АвтоПарк.

Известные проблемы

Ошибка при удалении файла <имя отчета>.xls

При формировании Excel-отчета возможно возникновении ошибки "Ошибка при удалении файла <имя отчета>.xls. Файл используется на других рабочих станциях или доступ к файлу запрещен". Возможные причины возникновения:

"AUTOPARK.XLS уже используется. Редактирование AUTOPARK.XLS запрещено пользователем XXX"

Необходимо проверить права на каталог ASBClient - права на запись должны быть только у Администратора ДОМЕНА.

"RunTime Error 1004 Не удалось найти файл AUTOPARK.XLS"

Возможные причины возникновения:

"RunTime error 91 ... variable not set"

При открытии Excel-отчета ошибка "RunTime error 91 ... variable not set". Следует снять галочку в AVP Касперского "макросы" - "включить постоянную проверку VBA-макросов".

Прочие RunTime ошибки

При возникновении RunTime ошибок с другим номером рекомендуется в каталоге AutoPark\BASIS\Имя отчета найти последний по дате (самый новый) рабочий xls-отчет (т.е. при открытии отчета не случается ошибки) и на основе этого отчета переформировать шаблон XLT.

В данных какие-то 7,78755Е-12?

При суммировании данных в большом количестве ячеек Excel "путается" и показывает очень малое число (что-то в минус большой степени) вместо нуля. Это ошибка Excel, поскольку исходные данные представлены с конкретной точностью и должны в сумме давать чистый ноль. Для исправления ситуации следует задать формат вывода (числа) проблемного поля (в мастере сводных таблиц) с необходимым количеством знаков после точки.

Отчеты с использованием технологии OLE

Технология OLE позволяет использовать в ячейках Excel формулы, извлекающие данные из АвтоПарка, что позволяет реализовать сложные отчеты, например, требующие настройки состава выдаваемой информации, а также отчетов, в которых наряду с данными АвтоПарка выводится другая информация (например, данные из отчетов прошлых периодов). Помечаются в меню АвтоПарка знаком "[O]".

Необходимые настройки

Для работы отчета необходимо, чтобы на компьютере была выполнена сетевая инсталляция АвтоПарка
Для Office версий XP и выше требуется доверить программный доступ к проекту Visual Basic.
Для Excel XP
Сервис - Макрос - Безопасность, на закладке "Надежные источники" поставить галочку "Доверять доступ к объектной модели VBA".
Для Excel 2007
Нажать кнопку MS Office.jpg, в появившемся окне нажать кнопку "Параметры Excel". Перейти на закладку "Центр управления безопасностью". Нажать кнопку "Параметры центра управления безопасностью". В открывшемся окне перейти на закладку "Параметры макросов" и поставить галочку "Доверять доступ к объектной модели VBA".
Для Excel 2010
Выбрать закладку "Файл". В появившемся окне перейти на закладку "Параметры", далее на закладку "Центр управления безопасностью". Нажать кнопку "Параметры центра управления безопасностью". В открывшемся окне перейти на закладку "Параметры макросов" и поставить галочку "Доверять доступ к объектной модели VBA".

Если этого не сделать, то при открытии отчета возникает ошибка "Compile error: Sub or Function not defined".

Известные проблемы

"Compile Error: User-defined type not defined"

Из-за ошибки в MS Excel при открытии отчета в редких случаях может выдаваться указанное в заголовке сообщение об ошибке. Способ обойти проблему: открыть отчет с отключенными макросами, сохранить его, закрыть Excel, открыть отчет с включенными макросами.

"Subscript out of range"

Решение проблемы: при получении такой ошибки продолжить открытие отчёта (выбрать "End"), сохранить его, закрыть Excel и открыть отчет снова из АвтоПарк. После этого может произойти такая же ошибка - это нормально, следует повторить то же самое ещё раз. После максимум 3-4 раз отчет должен "заработать". Может возникать после обновления используемых в отчете Excel-модулей (XLA) разработчиком. Дело в том, что Excel хранит в своих файлах прямые ссылки на связанные модули. Если имеющаяся в файле ссылка оказывается неверной, эта ссылка исправляется, но затем возникает данная ошибка. Если "портятся" сразу несколько ссылок, то исправятся они только после нескольких "исправлений".

Работа с отчетами

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

В меню Excel "АвтоПарк" имеются специальные пункты для работы с OLE-отчётами:

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

Для подготовки такого отчета обычно следует ввести в специально выделенные ячейки параметры отчета (например, дата или период отчета), Выполнить пункт "Пересчитать", сохранить полученный отчет с помощью пункта "Слепок".

Специальный отчет - построитель разовых списков

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

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

Построить список
Заполнение отчета по подготовленной форме с помещением полученных данных на новый лист (делается "слепок").
Вставить определение поля
Серия диалогов ("мастер") для построения определения значения, которое нужно выводить.

В книге Excel в таком отчете есть лист "Подготовка отчета", на котором настраивается формат отчета. На нем в верхней части листа производится настройка состава данных, а также имеются две строки с формулами, извлекающими данные из АвтоПарка. Подготовка отчета производится следующим образом:

Указывается дата, на которую строится список
С помощью пункта "Вставить определение поля" настраивается набор сведений, выводимый в отчет
В строках-заготовках отчета настраиваются формулы
С помощью пункта "Построить список" получается список объектов с необходимыми данными на другом листе
Подготовленный список обрабатывается средствами Excel - сортируется, фильтруется, оформляется внешний вид
Система всегда готовит "полный" список объектов, например, в список работников выводятся все работнике, числящиеся в штате на дату отчета. Если нужен список только объектов с определенными свойствами, следует вывести эти свойства в отчет, а затем отсортировать список по этим свойствам и удалить лишние строки.

Выгрузка произвольных файлов в Excel

Для самостоятельного изготовления отчетов можно воспользоваться выгрузкой произвольных файлов в Excel. Доступны три варианта выгрузки:

Весь текущий файл
"F7-Выгрузка в Excel-Весь текущий файл". Будут выгружены записи текущего файла.
Текущая запись файла
"F7-Выгрузка в Excel-Текущая запись файла". Будет выгружена одна выбранная запись текущего файла.
Выбрать файл
"Главное меню-Прочее-Сервис-Выгрузка в Excel-Выбрать файл". Будет предложен перечень всех файлов базы данных для выбора файла для выгрузки.

В зависимости от выгружаемого файла доступны разные варианты выгрузки:

Файлы, чьи записи относятся к конкретной дате
Только записи за отчетный период.
Файлы, чьи записи имеют период действия
Все записи.
Только действующие записи на текущую дату.
Только записи за отчетный период.
Остальные файлы
Все записи.

Кроме того, можно выбрать перечень связанных с выгружаемым файлов (подчиненных или справочников), которые будут выгружены на отдельные листы MS-Excel. Связанные файлы выгружаются не полностью, а лишь те записи, которые связаны с выгружаемыми записями основного файла.

Названия полей выгруженного файла будут выведены в первой строке листа Excel, в соответствии со структурой базы данных. Для получения привычного вида отчета, можно оформить новый лист Excel, в который скопировать данные из листов, созданных в результате выгрузки. Для того, чтобы увязать между собой коды справочника на одном листе и содержимое этого справочника (например, значения поля "Наименование") из листа с содержимым этого справочника, можно воспользоваться функцией Excel ВПР:

В первой строке с кодом справочника в ячейке столбца, отведенного под наименования справочника, добавляем функцию ВПР
В качестве параметра "Искомое_значение" указываем ячейку в этой же строке, в которой выведен код справочника
В качестве параметра "Таблица" на странице с данными справочника нужно выделить несколько столбцов таким образом, чтобы первым столбцом был столбец с кодами справочника, а последним - столбец с наименованиями
В качестве параметра "Номер_столбца" указываем порядковый номер столбца с наименованиями, считая столбец с кодами справочника за первый
В параметре "Интервальный_просмотр" указываем 0
Заполнить "Наименованиями" всю колонку таблицы отчета можно с помощью "Автозаполнения" (левой кнопкой мыши нажать в правый нижний угол ячейки с введенной функцией ВПР и "растянуть" вниз)

Проблемы совместимости Excel 2003 и Excel 2007

Лучше, конечно, сказать - проблемы несовместимости.

"В книге обнаружено содержимое, которое не удалось прочитать"

Такое сообщение возникает при открытии в MS Excel 2007 книги, изготовленной (сохраненной) в MS Excel 2003 (или более ранней версии). Проблема проявляется лишь иногда - только в некоторых книгах шаблонах и/или книгах. Следует выбирать "Да" из двух предложенных MS Excel вариантов ("Пробовать восстановить? Да/Нет"). Никакой закономерности в том, когда и почему возникают такие галлюцинации, пока не установлено. Да, собственно, и проблемой это не является - просто несколько надоедает. Лекарство - переход на какую-нибудь одну версию MS Office, по крайней мере, в пределах одного отдела (службы).

Источник — «http://www.autopark.ru/wiki/%D0%98%D1%81%D0%BF%D0%BE%D0%BB%D1%8C%D0%B7%D0%BE%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5_MS_Excel»
Личные инструменты
Пространства имён
Варианты
Действия
Навигация
Инструменты
Другие ресурсы