В начало

Приложение. Тексты программ пользовательских функций

Глава 10. Оперативный и производственный учет

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

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

Учет доверенностей

Рассмотрим пример, когда организация оказывает услуги по транспортно-экспедиционному обслуживанию грузовых железнодорожных перевозок.

Для ведения работ на станциях выдаются доверенности работникам фирмы. Форма доверенности стандартная, установленная Управлением железной дороги, в которой меняются только индивидуальные данные. Она показана на рис. 10.1. На предприятии должен вестись строгий учет доверенностей.

Для этой задачи применен используемый ранее принцип - связанные между собой таблицы-базы данных и таблица-форма, куда попадают все необходимые реквизиты из баз данных при занесении в форму очередного номера. В книге Доверенности создаем три листа: Бланк доверенности, Журнал учета и Экспедиторы. Журнал учета показан на рис. 10.2, а лист Экспедиторы - на рис. 10.3. Обратите внимание, что все три листа - просто базы данных, содержащие цифровую и текстовую формацию.

На бланке доверенности в ячейке В3 находится число 350. Это идентификационный номер доверенности в базе данных, аналогичный номеру платежного поручения в рассмотренной в предыдущей главе книге Bank. Находим его на листе Журнал учета и видим, что занесенная в этой строке информация соответствует той, что мы видим в бланке доверенности.

Рис. 10.1. Доверенность

Определенное недоумение может вызвать второй номер 169, присвоенный данной доверенности. Что это такое? Дело в том, что учет доверенности ведется индивидуально по каждому клиенту - перевозчику грузов, поэтому в поле № д проставляется порядковый номер по данному перевозчику, а в поле перевозчик - числовой код перевозчика (в данном случае 10).

В ячейке А4 бланка доверенности формируется слово "доверенность" с ее официальным номером, представляющим собой номер доверенности по данному перевозчику / код перевозчика. Формула проста и знакома, образуется с помощью функции ВПР или ПРОСМОТР и сцепки текстовой информации.

Рис. 10.2. Журнал учета доверенностей

Рис. 10.3. Паспортные данные

Заглянем в ячейку А6 и увидим там довольно большую формулу:

="Выдана настоящая доверенность "&ВПР(В3;'журнал учета'!А:F;6;ЛОЖЬ)&", "&ВПР(В3;'журнал учета'!А:I;9;ЛОЖЬ)&", имеющему паспорт серии "&ВПР(ВПР(В3;'журнал учета'!А:F;6;ЛОЖЬ);экспедиторы!А:Е;2;ЛОЖЬ)&" № "&ВПР (ВПР(В3;'журнал учета'!А:F;6;ЛОЖЬ);экспедиторы!А:Е;3;ЛОЖЬ) &", выданный "&ВПР(ВПР(В3;'журнал учета'!А:F;6;ЛОЖЬ);экспедиторы!А:Е;4;ЛОЖЬ)&", проживающему по адресу: "&ВПР(ВПР(В3;'журнал учета'!А:F;6;ЛОЖЬ);экспедиторы!А:Е;5;ЛОЖЬ)&" в том, что ему доверяется от имени ООО ""Лес"", соглашение № 6138667, на станции "&ВПР(В3;'журнал учета'!А:С;7;ЛОЖЬ)&" ОЖД производить нижеследующие операции: [1]

По сравнению с тем, что было ранее рассмотрено, здесь нет ничего принципиально нового, кроме объема текста, с которым производятся операции. Данный пример демонстрирует широкие возможности автоматизации создания и учета стандартных документов. Практически у каждой организации есть какие-то похожие проблемы: стандартные письма, телеграммы, приказы и т. д. Обычно они создаются в текстовом редакторе один на основе другого. Первоначальный вариант либо стирается, и тогда его не восстановить, либо сохраняется под своим именем, и тогда память на диске засоряется большим количеством неупорядоченной информации, в которой трудно разобраться даже ее автору. Очень много подобных документов у всякого рода официальных государственных и муниципальных структур. Каждому, наверное, приходилось получать официальные письма, где основной текст отпечатан, а индивидуальные сведения вносятся вручную. А подход, который только что был рассмотрен, позволяет легко и красиво создавать документы, а также компактно и удобно сохранять всю информацию и при необходимости восстанавливать любой документ.

Теперь еще раз вернемся к бланку доверенности - заглянем в ячейку А16. Формула там выглядит следующим образом:

="Доверенность действительна с "&""""&ДЕНЬ(ВПР(В3;' журнал учета'!А:F;4;ЛОЖЬ))&"""("&PERSONAL.XLS!чиспроп(ДЕНЬ(ВПР(В3;' журнал учета'!А:F;4;ЛОЖЬ)))&") "&PERSONAL.XLS!месяцпроп(МЕСЯЦ(ВПР(В3;'журнал учета'!А:F;4;ЛОЖЬ)))&ГОД(ВПР(В3;'журнал учета'!А:Р;4;ЛОЖЬ))&" года "&"до """&ДЕНЬ(ВПР(В3;'журнал учета'!А:F;5;ЛОЖЬ))&"""("&PERSONAL.XLS!чиспроп(ДЕНЬ(ВПР(В3;'журнал учета'!А:F;5;ЛОЖЬ)))&")"&PERSONAL.XLS!месяцпроп(МЕСЯЦ(ВПР(В3;'журнал учета'!А:F;5;ЛОЖЬ)))&ГОД(ВПР(В3;'журнал учета'!А:F;5;ЛОЖЬ))&" года включительно [2]

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

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

Учет расчетов с партнерами

Хотя учет расчетов с партнерами входит в круг задач, решаемых бухгалтерским учетом, в реальной деловой жизни одних регистров бухгалтерского учета бывает зачастую недостаточно. Есть ряд вопросов, не отражаемых в бухучете, но совершенно необходимых для ведения дел. Авторы не имеют в виду пресловутые "особенности" отечественной экономики, мы говорим о совершенно нормальных вещах. Строгие каноны бухучета, необходимые для упорядочения деловой жизни, далеко не всегда способны решить все возникающие задачи. Например, по некоторым реально прошедшим операциям пока нет документов или имеются только факсимильные копии; какие-то операции планируются на ближайшее время, и требуется их учет для оценки реальных финансовых взаимоотношений; может потребоваться гибкий оперативный учет вспомогательных затрат, или поступлений, или дополнительных платежей. Может возникнуть множество разнообразных ситуаций, требующих оперативного учета, выходящего за рамки бухгалтерского. Но при этом в большинстве подобных ситуаций все это должно быть тесно связано с бухгалтерским учетом, который либо служит источником информации либо, наоборот, использует ее.

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

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

Рис. 10.4. Лист Перечень

Рассмотрим структуру книги Excel, реализующую эту задачу. Она состоит из следующих листов: Перечень (рис. 10.4), Зачисления (рис. 10.5), Справка (рис. 10.6), Св_перевозки (рис. 10.7), Клиенты (рис. 10.8).

Первый лист содержит информацию об осуществленных перевозках. Он создан на основе перечней ТехПД, куда добавлена информация о клиентах, для которых выполняются перевозки. Для удобства и надежности идентификации каждому клиенту присвоен числовой код. Это не ново, такой подход широко применяется в реляционных базах данных.

Второй лист Зачисления содержит информацию обо всех поступлениях на соглашение.

Третий лист Справка содержит информацию о суммарных перевозках и поступлениях. Информация на этом листе формируется на основании данных из первых двух листов и остатка на лицевом счете. Формулы не содержат ничего нового. Для примера приведем формулы четвертой строки.

Ячейка В4:

=СУММЕСЛИ(зачисления!$С:$С;А4;зачисления!D:D) [3]

Ячейка С4:

=СУММЕСЛИ (зачисления!$С:$С;А4;зачисления!Е:Е) [4]

Ячейка Е4:

=E3+B4+C4-D4 [5]

Рис. 10.5. Лист Зачисления

Рис. 10.6. Лист Справка

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

Теперь переходим к расчетам с партнерами. Рассмотрим взаимоотношения с клиентами. Для обеспечения этой возможности в таблицу Перечень введена информация о клиентах. Таким образом, каждая железнодорожная квитанция или друга запись в перечне строго привязана к конкретному клиенту. Это создает принципиальную возможность индивидуального учета перевозок по каждому клиенту. Принцип аналогичен используемому ранее. Однако в данном случае есть дополнительные нюансы. В отличие от бухгалтерского учета, учет тарифов ведется с начал года единой таблицей без разбивки на месяцы и кварталы. В то же время для оперативного учета отношений с клиентами существует необходимость иметь информацию не только о состоянии расчетов на текущий момент, но и о ее динамике. Желательно иметь сведения об использованных тарифах за каждый месяц. Поэтому необходимо создать промежуточное звено для помесячной разбивки исходных данных о перевозках.

Сама информация о месяце, в котором осуществлена перевозка, образуется непосредственно на листе Перечень. Для этого введено справа от поля ДАТА поле МЕСЯЦ, содержащее стандартную функцию месяц().

Далее необходимо скомпоновать информацию по клиентам и календарным месяцам. Сделать это непосредственно теми методами, применявшимися ранее, затруднительно, так как функция СУММЕСЛИ работает только по одному критерию.

Но в Excel есть семейство функций, обладающих в этом плане более широкими возможностями. Они так и называются - Функции работы с базой данных. Однако их использование делает нашу таблицу более громоздкой и менее удобной и требует специально отведенного места для критериев. Еще одни недостаток этого класса функций - недостаточная гибкость задания критериев, их нельзя задавать в виде формул и ссылок. Такая функция хороша для одноразового использования, но не для тиражирования. Мы же стремимся к живой и гибкой системе, позволяющей легко добавлять и убирать информацию.

Поэтому в данном случае выбран другой путь, а именно применение сводных таблиц. Так как нас интересует, сколько каждый клиент использовал в месяц провозных платежей, при создании сводной таблицы зададим на первом шаге В списке или базе данных Microsoft Excel (см. рис. 7.17). На втором - диапазон B1-L8913 листа Перечень. На третьем - в поле строка укажем клиент, а в поле столбец - месяц. Последний шаг - это размещение сводной таблицы на новом листе.

Пройдя таким образом ряд шагов, мы создаем сводную таблицу, показанную на рис. 10.7.

Рис. 10.7. Вспомогательная сводная таблица св_перевозки

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

Рис. 10.8. Лист Клиенты, вариант 1

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

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

В столбце С указано наименование информации, отображаемой в данной строке по данному клиенту.

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

Теперь рассмотрим информацию построчно. В верхней строке расположена информация об использованных тарифах, получаемая из таблицы св_перевозки. Для этого служит формула:

=ЕСЛИ(Е0ШИБКА(ВПР($А7;(св_перевозки'!$В:$М;Е$2+1;ЛОЖЬ));0;ВПР($А7; 'св_перевозки'! $B:$N; Е$2+1;ЛОЖЬ)) [6]

Логическая функция в формуле [6] необходима для того, чтобы в случае если месяц текущей даты меньше, чем в данном столбце, то есть перевозок еще не было, или в данном месяце не было перевозок по данному клиенту, в данной ячейке не сформировалась информация об ошибке, а появился бы 0.

Кроме того, в формуле [6] есть один нюанс - структура сводной таблицы не является жестко заданной, как структура таблиц, созданных пользователем. Так, если, например, сейчас февраль, то в сводной таблице не будет столбцов с номерами месяцев больше 2. В то же время формулы в таблице Клиенты рассчитаны на наличие полной структуры. Можно, конечно, и ее сделать гибкой и вставлять столбцы по мере перехода к новому временному интервалу, но это неудобно. Поэтому в листе Перечень предусмотрены пустые строки с датами, соответствующими всем последующим месяцам до конца года.

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

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

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

Формулы массива

Только что рассмотренный пример с применением сводных таблиц имеет и некоторые неудобства - структура исходной базы данных должна отвечать определенным требованиям. Задачу эту можно решить несколько по-иному - с использованием формул массива. Формулы массива - достаточно мощный и интересный инструмент Excel, которого мы еще не касались в этой книге. Необходимо объяснить, что это такое.

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

Рассмотрим применение формул массива для случая, рассмотренного в предыдущем разделе.

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

{=СУММ(ЕСЛИ(МЕСЯЦ=Е$2;ЕСЛИ(КЛИЕНТ=$А15;Сумма_с_НДС)))} [7]

Рис. 10.9. Лист Клиенты, вариант 2

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

Теперь посмотрим, как она работает. В формуле [7] фигурируют три массива: МЕСЯЦ, КЛИЕНТ и Сумма_с_НДС, каждый из которых представляет собой столбец с одноименным названием из листа ПЕРЕЧЕНЬ (рис. 10.4).

В связи с вышесказанным отметим еще одну не совсем обычную конструкцию - знак равенства между массивом и ячейкой. Такое допускают только формулы массива. Для облегчения восприятия рассмотрим формулу [7] в усеченном виде:

{=СУММ(ЕСЛИ(МЕСЯЦ=Е$2;Сумма_с_НДС))} [8]

Формула [8] напоминает хорошо знакомую конструкцию:

=СУММЕСЛИ(МЕСЯЦ;Е$2;Сумма_с_НДС) [9]

Результат работы формул [8] и [9] одинаков. Но разница в том, что возможности функции СУММЕСЛИ на этом и кончаются, в то время как формула массива позволяет вставить встроенную конструкцию ЕСЛИ и получить из формулы [8] формулу [7], дающую принципиально новое качество - суммирование по двум критериям. В результате использование формул массива позволило усовершенствовать учет оплат за тарифы. Если в таблице на рис. 10.8 производился только расчет суммарных оплат, то здесь есть возможность осуществлять этот расчет помесячно по следующей формуле:

{=СУММ(ЕСЛИ('продажи.xls'!месяц=Е$2;ЕСЛИ('продажи.xls'!кл_т=$А15; 'продажи.xls'!Сумма1)))+СУММ(ЕСЛИ('продажи.xls'!м_ц=Е$2;ЕСЛИ('продажи.xls'!клиент=$А15; 'продажи.xls'!сумма)))} [10]

Построение формулы [10] аналогично формуле [7], поэтому в комментариях она не нуждается.

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

Для удобства пользователя введена дополнительная страница св_кл, в которой компактно отображается сводная информация из таблицы Клиенты (рис. 10.10). Информация в ней формируется тривиальным уже способом с помощью функции ВПР. Например, формула в ячейке Е4 следующая:

=ВПР(Е$2;кл2;15;ЛОЖЬ) [11]

Рис. 10.10. Лист св_кл

Страховые компании

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

В этом случае возникает достаточно много различных, но связанных между собой учетных задач:

• расчет стоимости медицинских услуг, оказываемых каждому конкретному клиенту;
• учет суммы и сроков действия страховых полисов;
• учет финансовых взаимоотношений со страховыми компаниями;
• аналитический бухгалтерский учет по расчетам с партнерами;
• учет работы врачей;
• формирование документов (медицинские карты, отчеты страховым компаниям).

Рассмотрим книгу Excel, реализующую эти задачи и состоящую из следующих листов:

• Прейскурант;
• Страховые компании;
• Врачи;
• Пациенты;
• ск1, ск2, ...;
• Мед. карта;
• Отчет.

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

Рис. 10.11. Лист Прейскурант

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

Первые три столбца содержат порядковый номер и наименование страховой компании, а также номер и дату договора.

Следующий столбец, КОЭФФИЦИЕНТ, нуждается в некоторых комментариях. Дело в том, что с отдельными компаниями по условиям договора могут устанавливаться особые финансовые отношения, - например, может предоставляться скидка. Это и учитывает коэффициент, представляющий собой число, на которое нужно умножить стоимость каждой работы по прейскуранту. Как правило, он равен 1, что и имеет место в данном случае (рис. 10.12).

Рис. 10.12. Лист Страховые компании

Далее в столбцах Е-Р формируются по уже знакомым формулам помесячные суммы стоимости работ по страховым компаниям. На рис. 10.12 показаны компании, с которыми установились отношения с 8-го месяца. Формула в ячейке L3 следующая:

=CУMMЕСЛИ(ск1!$L$1:$L$142;L$2;ск1!$K$1:$K$142) [12]

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

Лист Врачи по своей идеологии аналогичен листу Страховые компании. Информация из первого столбца заносится, когда это необходимо, на листы ск1, ск2 и т. д., содержащие основную информацию по услугам, оказанным страховым компаниям. Обратите внимание, что перенос обязательно должен осуществляться путем копирования, иначе возможны ошибки. Применение цифрового кодирования в данном случае не очень удобно.

В следующих столбцах формируется информация об услугах, оказанных конкретным врачом данной страховой компании за тот или иной месяц. Данные с номером месяца заводятся в ячейку В7. Результат образуется путем суммирования по двум критериям - номеру месяца и Ф.И.О. врача с использованием формулы массива. В качестве примера приведем формулу в ячейке В10:

{=СУММ(ЕСЛИ(ск1!$D$1:$D142=$А10;ЕСЛИ(ск1!L1:L142=врачи!В7;ск1!$К$1:$К$142)))} [13]

В столбце Е формируется суммарная информация по всем страховым компаниям. Общий вид листа Врачи приведен на рис. 10.13.

Рис. 10.13. Лист Врачи

Перейдем теперь к листу Пациенты. Принцип опять-таки тот же - совмещаем исходные данные с результатами расчета в одной таблице. Таблица содержит достаточно много полей, поэтому она представлена на рисунках 10.14 и 10.15 (один является продолжением другого).

Сведения на рис. 10.14 относятся к справочной информации о клиенте. Она необходима для служебных целей.

Столбцы G и Н на рис. 10.15 также содержат справочную информацию - срок действия полиса и его сумму - но эти данные уже участвуют в расчетах. По ним осуществляется контроль в столбцах N и O.

В столбце I осуществляется расчет произведенных по данному пациенту работ по формуле:

=СУММЕСЛИ(ВЫБОР(М2;ск1!В1:В142;ск2!В1:В92;ск3!В1:В27);А2; ВЫБОР(М2;ск1!К1:L142;ск2!К1:К92;ск3!К1:К27))+Р2 [14]

Формула [14] в зависимости от номера страховой компании осуществляет выбор массива информации, а из него уже выбирает сведения по данному пациенту и прибавляет сумму ранее выполненных работ, если они имели место. В данном случае все компании начали работать только в текущем году, поэтому в столбце Р информация отсутствует.

Столбец J информирует о том, сколько еще осталось у пациента средств на его страховом полисе.

Рис. 10.14. Лист Пациенты, фрагмент 1

Рис. 10.15. Лист Пациенты, фрагмент 2

Столбец К содержит чисто справочную информацию о перенесенных и сопутствующих заболеваниях.

В столбце N осуществляется контроль за сроком действия полиса по формуле:

=ЕСЛИ(СЕГОДНЯ()>С9;"СРОК ДЕЙСТВИЯ ПОЛИСА ИСТЕК!";"норма") [15]

Функция СЕГОДНЯ() возвращает текущую дату и по вполне понятным причинам аргументов не имеет. Таким образом, как только текущая дата перейдет рубеж срока действия полиса, в соответствующей строке столбца N появляется предупреждение: "СРОК ДЕЙСТВИЯ ПОЛИСА ИСТЕК!", из чего следует, что данного пациента клиника больше не имеет права обслуживать, если страховая компания не продлит с ним отношения.

Далее в столбце O ведется контроль соответствия стоимости произведенных работ с суммой полиса. Контроль осуществляется по формуле:

=ЕСЛИ(J9<0;"ПРЕВЫШЕНИЕ СУММЫ ПОЛИСА";"норма") [16]

С помощью формулы [16] отслеживается остаток средств пациента. Как только он станет меньше нуля, появится информация об этом обстоятельстве.

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

Теперь перейдем к основной информации - базе данных по страховым компаниям. Она разбита на отдельные листы Ск1, Ск2 и т. д. по количеству страховых компаний. В рассматриваемом примере таковых показано три. Можно было бы всю эту информацию располагать и в одной таблице, а затем делать выборку по отдельным страховым компаниям с помощью уже известных методов. Однако, во-первых, это сделало бы информацию чересчур громоздкой, а во-вторых, чаще пришлось бы применять формулы массива, которые, кроме своих безусловных достоинств, замедляют вычислительный процесс и требуют больших объемов памяти.

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

В поле Дата заносится дата выполнения работы, в поле ФИО - фамилия, имя и отчество пациента, при этом они должны точно соответствовать их написанию на листе Пациенты (информацию желательно заносить путем копирования).

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

=ВПР(В4;пациенты!A:F;6;ЛОЖЬ) [17]

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

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

Рис. 10.16. Лист Ск1

Следующие три столбца содержат чисто медицинскую информацию, которую регистратор переносит из медицинской карточки пациента.

Столбцы G и Н содержат формулы, возвращающие наименование вида лечения и его цену из прейскуранта. Формула в столбце G следующая:

=ВПР(G6;прейскурант!А:С;2;ЛОЖЬ) [18]

В столбце Н аналогичная, но с маленьким нюансом:

=ВПР(Н6;прейскурант!В:0;2;ЛОЖЬ)*КОЭФФИЦИЕНТ1 [19]

В формуле [19] происходит также умножение на коэффициент. В данном случае КОЭФФИЦИЕНТ1 - это имя ячейки стр_комп!D3, содержащей значение коэффициента для страховой компании 1.

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

=ЕСЛИ(EЧИСЛО(J5);I5*J5;I5) [20]

То есть если в поле Количество есть число, оно умножается на цену, если нет - берется просто цена.

И наконец, последний столбец содержит номер месяца, получаемый из значения даты выполненной работы:

=МЕСЯЦ(А5) [21]

Рис. 10.17. Лист Мед. карта

Рис. 10.18. Отчет (первый вариант)

Основная часть листа Мед. карта приведена на рис. 10.17. Это в общем-то обычная медицинская карта традиционного медицинского учреждения. В основном это неавтоматизированный документ, поскольку он предназначен для занесения туда информации в процессе осуществления работ.

Автоматизирована только шапка документа с основными реквизитами пациента. Они появляются автоматически при занесении в соответствующую ячейку Ф.И.О. пациента. Естественно, Ф.И.О. должны точно совпадать с тем, что занесено на лист Пациенты, и на этот лист должны быть занесены все реквизиты пациента. Информация в шапке медицинской карты формируется обычно - например, в ячейке D9, отражающей номер полиса, формула следующая:

=ВПР(С8;пациенты!А:J;6;ЛОЖЬ) [22]

Небольшую сложность представляет отображение наименования страховой компании, поскольку на листе Пациенты находится только ее числовой код. Поэтому используется функция ВПР, вложенная в функцию Просмотр. Формула в ячейке F7 выглядит следующим образом:

=ПРОСМОТР(ВПР(С8;пациенты!А1:М180;13;ЛОЖЬ);стр_комп!А3:А5;стр_комп!В3:В5) [23]

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

На рис. 10.18 показан фрагмент отчета, созданного на основе сводной таблицы.

На рис. 10.19 приведен фрагмент результирующей части отчета, созданного с помощью команды ИТОГИ.

Рис. 10.19. Отчет (второй вариант)

Заключение

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