Бланки, документы, технические спецификации

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

Типовые бланки и отдельные расчеты

Многие   справочно-информационные  системы,   например  «Консультант  Плюс» (http://www.consultant.ru/online), позволяют загружать  файлы  бланков  документов.  С успехом  можно  использовать и справочную  систему  «Гарант» (http://www.garant.ru), которая также предоставляет свободный доступ в Интернете к некоторой части своей информации. В системе «Гарант» имеются две отдельные части: Интернет-версия «Основные  нормативные акты» (http://iv2.garant.ru) и Интернет-версия «Законодательство России» (http://iv.garant.ru). Обратившись к этим  справочным  системам,  можно  получить  большинство  необходимых  документов.

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

. Бланки, документы,технические спецификации

Автоматизация составления документов

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

Унифицированные формы для расчетов с покупателями

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

Для  учета реализации утверждены  формы  документов (ТОРГ-12, счет-фактура, ИНВ-17). Некоторые документы составляются в свободной форме с соблюдением необходимых реквизитов (счет, акт, договор).

Счет-фактура и счет

Создание и автоматическое заполнение  подобных документов в Excel может быть выполнено  различными способами. Рассмотрим заполнение  бланка  документа на примере счета-фактуры.

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

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

Рис. 8.1. Данные счетов-фактур

Рис. 8.2. Счет-фактура

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

Номер счета-фактуры является основным критерием для поиска данных. Во вспомогательную ячейку O9 занесена обычная формула =ВПР(B5; Список!A2:F9; 3; ЛОЖЬ). Таким способом определяется первое наименование товара данного счетафактуры.  В другой  вспомогательной ячейке  O11 находится  формула  =ПОИСКПОЗ(O9; Список!C2:C4; 0). По этой формуле определяется позиция найденного наименования товара (содержимое ячейки O9) в массиве ячеек C2:C4 листа Список (при этом требуется точное совпадение текста).

Основная сложность  состоит в вычислении данных ячеек столбцов таблицы  Наименование товара, Единица измерения, Количество  и Цена. В первой  строке столбца Наименование товара размещена формула =ЕСЛИ( ИНДЕКС( Список!$A$2:$F$9;

$O$11; 1) =$B$5; ИНДЕКС( Список!$A$2:$F$9; $O$11; 3); " ").

По  этой  формуле  присваивается значение  ИНДЕКС(  Список!$A$2:$F$9;

$O$11; 3) (в массиве ячеек !$A$2:$F$9 листа Список ищется значение ячейки

. Бланки, документы,технические спецификации

$O$11, результат вычисления данной функции присваивается из столбца №3 найденной строки).  Этот результат  вычисления присваивается только в том случае, если выполняется условие проверки: содержимое  столбца №1 найденной  строки должно совпадать с содержимым  ячейки  $B$5 (номер  счета-фактуры). В других случаях должно присваиваться значение "" (пустая строка). Следующие  ячейки  этой строки  содержат  аналогичные формулы,  только  результат  присваивается из столбцов  №№4,  5,  6. Далее  в ячейках  этой строки  таблицы  счета-фактуры находятся  простые формулы  вычислений итоговых  сумм, при этом проверяется содержание ячеек Количество и Цена. Если ячейки пусты, то присваивается нулевое значение.  Например, для вычисления стоимости  товара  используется формула

=ЕСЛИ(И(E20<>"";F20<>"");E20*F20;0).

В следующих строках таблицы содержатся аналогичные формулы, но для использования следующего наименования товара приходится делать сдвиг по списку на одну строку вниз, поэтому в следующей строке столбца Наименование товара находится формула =ЕСЛИ(ИНДЕКС(Список!$A$2:$F$9; $O$11+1; 1) =$B$5; ИНДЕКС(Список!$A$2:$F$9; $O$11+1; 3); ""). Аналогичные  изменения формул делаются и для остальных ячеек таблицы.

Приведенный пример  работает  правильно,  если в счетах-фактурах встречаются  только  различные наименования товаров.  Если  есть одинаковые наименования товара в различных счетах-фактурах, то в этом случае выясняется, что данный вариант формул не совсем точен. Например, для счета-фактуры №4 в ячейке O11 вычисляется значение  1, хотя эта позиция  соответствует  другому  счету-фактуре. В результате данные этого счета-фактуры печатаются  правильно,  но не в первой, а в четвертой  строке таблицы  (этот номер соответствует сдвигу данных на листе Список). Это происходит потому, что в формуле поиска в ячейке O11 использовалось значение ячейки O9 (наименование товара). Если для поиска использовать значение ячейки B5 (номер счета-фактуры) и соответственно изменить диапазон ячеек на Список!A2:A9, то в ячейке O11 вычисляется правильное  значение  4. Теперь все работает правильно.  Кроме того, в этом варианте значение ячейки O9 нигде не используется, и ее можно очистить.

Ячейки, в которых вычисляются величины  налогов и итогов, были запрограммированы ранее, поэтому при вводе конкретного номера счета фактуры  в ячейку B5 происходит  автоматическое заполнение  таблицы наименованиями товаров и вычисление всех необходимых итоговых значений.

СОВЕТ

Рассмотренный пример предполагает, что количество товаров в каждом из счетов-фактур не будет превышать количества строк в таблице бланка. Если необходимо составлять более длинные списки товаров в одном счете-фактуре, то необходимо увеличить количество строк, возможно, перенести их на второй лист. При увеличении количества документов нужно будет расширить диапазон ячеек листа Список, в котором происходит поиск данных (вместо

$A$2:$F$9 указать большее значение). Изменение во всех формулах удобно производить операцией Заменить… (кнопка Найти и выделить вкладки ленты Главная). Одновременно с этим будет необходимо изменить диапазон ячеек в формуле, содержащейся в ячейке O11.

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

Приходная и расходная накладные

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

Основные данные могут быть сгруппированы на отдельном листе книги Excel (лист Список, см. рис. 8.3). Этот лист содержит список товаров, даты и номера накладных,  а также другие данные. Одному наименованию товара соответствует  одна строка в общем списке.

Рис. 8.3. Данные приходных накладных

Бланк  накладной  находится  на  листе  Приходная  накладная,  который  показан на рис. 8.4. Для автоматического заполнения бланка в его ячейки  введены формулы, содержащие  функции ВПР и ИНДЕКС. Использование функции ЕСЛИ позволяет дополнительно обрабатывать  вычисленные значения  ячеек.

Рис. 8.4. Приходная накладная

При заполнении бланка накладной пользователю необходимо ввести в ячейке G3 ее номер. Вся дальнейшая работа может быть автоматизирована. По введенному  номеру  накладной  с помощью  формулы  =ВПР(G3;Список!A2:F9;2;ЛОЖЬ) определяется  дата  накладной  (ячейка E4). В данном  случае  в ячейках  Список!A2:F9 ищется точное значение (на это указывает четвертый аргумент, имеющий значение ЛОЖЬ), совпадающее с содержимым ячейки G3. Во втором столбце (на  это указывает  третий  аргумент  функции) строки  с найденным  значением  находится  искомая  дата накладной.  Это значение возвращается функцией ВПР.

. Бланки, документы,технические спецификации

Ячейки  таблицы  с наименованиями товаров  и их данными заполняются другими формулами. Например, в ячейку  B9 (наименование товара)  введена  формула  =ЕСЛИ(ИНДЕКС(Список!$A$2:$F$20;$O$11;1)=$G$3;ИНДЕКС(Список!$A$2:$F$20;$O$11;3);""). Другие ячейки этого столбца таблицы вместо значения  $O$11 содержат $O$11+1,  $O$11+2 и т. д.

Остальные ячейки  таблицы  бланка  накладной  заполнятся аналогичными формулами.  В аргументы  функций вносятся незначительные изменения,  соответствующие номеру столбца или строки таблицы.  Например, в ячейке  E9 находится  формула =ЕСЛИ(ИНДЕКС(Список!$A$2:$F$20;$O$11;1)=$G$3;ИНДЕКС(Список!$A$2:$F$20;$O$11;4);""). Она отличается  от формулы в ячейке B9 заменой значения  3 на 4.

В данных формулах  используется значение ячейки $O$11, содержащей  формулу

=ПОИСКПОЗ(G3;Список!A2:A20;0). Так определяется номер первой  строки,

содержащей  значение ячейки  G3 в массиве ячеек Список!A2:A20. Это значение

многократно  используется в других формулах,  поэтому  оно сохранено  во вспо-

могательной  ячейке. В данном примере столбец O скрыт, чтобы вспомогательные данные не выводились на печать.

Функция ИНДЕКС позволяет  получить  значения соответствующих ячеек массива исходных данных. Функция ЕСЛИ обрабатывает  полученный результат и записы вает в ячейки значение "" (пустая строка), если на листе Список уже использованы все строки, относящиеся к данной накладной.

Для написания суммы прописью  использована функция =руб(H23;1), которая  требует подключения надстройки  Деньги прописью.

СОВЕТ

Этот пример является почти законченным вариантом автоматизации заполнения приходной накладной. Может потребоваться только добавить на лист Список данные для заполнения некоторых полей бланка (Организация, ИНН, Склад, Принял, Сдал). При применении бланка в одной организации достаточно просто ввести эти данные в ячейки листа Приходная накладная. Для практической работы, вероятно, потребуется расширить диапазон ячеек данных, изменив соответствующие формулы. При этом очень важно не забыть изменить диапазон поиска и в ячейке O11.

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

Источник: Трусов А. Ф. Excel 2007 для менеджеров и экономистов: логистические,  производственные и оптимизационные расчеты (+CD). — СПб.: Питер, 2009. — 256 с.: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий