Школа бухгалтера № 8 (9.6.2003)
Автоматизация учета

Автоматизация учета электронных карточек расхода ГСМ в программе Excel

• Електронная учетная таблица
• Расчетный блок
• Формулы расчетного блока
• Как пользоваться расчетным блоком
• Стандарные возможности таблицы-ведомости

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

¹ Подробнее об учете топлива и ГСМ см. "Школу бухгалтера" №19/2003.

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

1) сколько израсходовано денежных средств по карточке за определенный период или на конкретную дату;

2) сколько израсходовано денежных средств водителем по указанной карточке;

3) сколько израсходовано денежных средств на ГСМ по каждому автомобилю за любой его период эксплуатации.

Электронная учетная таблица

Таблица, которая будет являться базой для расчета аналитических данных (рис.1), в основном повторяет Ведомость по учету электронных карточек на АТП ² .

---

² Образец Ведомости см. в "Школе бухгалтера" №19/2003.

---

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

Рисунок 1

Основная особенность ее структуры заключается в том, что ячейки граф 1, 3, 8 и 9 должны иметь числовой формат, а ячейки граф 2 и 10 - формат даты. Причем для того, чтобы в графах 2 и 10 иметь возможность вносить не только дату, но и время выдачи (возврата) карточки, установите для ячеек этих граф формат, показанный на рисунке 2. Для этого выделите их, затем в контекстном меню откройте окно Формат ячеек и на вкладке Число выберите в формате Дата тип 14.03.99 13:30.

Рисунок 2

Теперь, при заполнении соответствующих граф, после введения даты можно записывать (обязательно через пробел после даты!) время в формате типа "13:30".

Расчетный блок

Главное преимущество Excel - автоматизация сложных расчетов - не всегда применяется пользователями этой программы. Чтобы получить необходимые данные для анализа использования электронных карточек, постройте дополнительный блок (В2:Н6, рис.1).

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

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

Последовательность действий такова. Выделите ячейки графы 2 "Дата выдачи карточек". Размер выделенного интервала ячеек будет зависеть только от общего количества "движений" по карточкам за год (строк в таблице). Для примера использован интервал В10:В25. Установите курсор в Поле имени (слева от строки формул), введите в него имя Дата и нажмите клавишу Еnter. Теперь, чтобы перейти к именованному объекту, достаточно будет выбрать его в списке Имя (рис.3). Последовательно проведите указанные действия еще для шести интервалов ячеек. Необходимые для этого данные указаны в таблице 1.

Рисунок 3

Таблица 1

Обратите внимание на позицию №7 таблицы 1. Интервал ячеек под именем Таблица имеет другие параметры и будет использоваться для автоматизации внесения имени водителя в расчетный блок.

Размер первых шести интервалов ячеек обязательно должен быть одинаковым, причем первые ячейки всех указанных интервалов должны находиться на одной строке (в данном случае строка №10).

Для корректных вычислений установите для ячеек расчетного блока следующие форматы:

1) формат даты для E3, F3;

2) числовой формат для E4:F6, G4, H4.

3) общий формат для остальных ячеек.

Формулы расчетного блока

1. Расчет общего расхода денежных средств по электронной карточке, номер которой указан в ячейке D4, за весь период включительно до даты, записанной в ячейку Е3, определяется формулой массива:

{=СУММ(ЕСЛИ(НомерКар=D4;1;0)*ЕСЛИ(Дата<=E3;1;0)*Расход)}.

После введения формулы массива и каждый раз, закрывая такую формулу после редактирования, подтверждайте ввод одновременным нажатием комбинации клавиш Ctrl-Shift-Enter. Отличительным признаком формулы массива являются фигурные скобки по краям формулы, которые появляются автоматически после корректного ее ввода.

2. Расчет общего расхода денежных средств по электронной карточке, номер которой указан в ячейке D4, за день, дата которого записана в ячейку F3, определяется формулой массива:

{=СУММ(ЕСЛИ(НомерКар=D4;1;0)*ЕСЛИ(Дата=F3;1;0)*Расход)}.

3. Расчет общего расхода денежных средств по электронной карточке (ее номер указан в ячейке D4), использованных для закупки ГСМ на автомобиль (его номер записан в ячейку D5), за весь период включительно до даты (записана в ячейку Е3) определяется формулой массива:

{=СУММ(ЕСЛИ(НомерАвто=D5;1;0)*ЕСЛИ(Дата<=E3;1;0)*Расход)}.

4. Аналогичный расчет по конкретному автомобилю только за один день, дата которого записана в ячейку F3, определяется формулой массива:

{=СУММ(ЕСЛИ(НомерАвто=D5;1;0)*ЕСЛИ(Дата=F3;1;0)*Расход)}.

5. Данный расчетный блок позволяет определить аналитические данные пользования карточкой по каждому водителю. Выбор фамилии водителя осуществляется путем ввода в ячейку D3 его табельного номера. Для автоматической вставки фамилии, введите в ячейку D6 формулу:

=ЕСЛИ(D3=0;"Введи таб.№";ВПР(D3;Таблица;2)).

Обратите внимание на структуру формулы. В случае, если ячейка D3 останется пустой, то вместо фамилии в ячейке D6 появится напоминание "Введи таб.№" (рис.4).

Рисунок 4

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

1) расход средств за определенный период по конкретной карточке;

2) расход средств за один день по конкретной карточке;

3) общий расход средств за определенный период по всем карточкам.

Данная аналитика рассчитывается с помощью формулы массива, введенной в ячейку Е6 (данные определяются за весь период):

{=СУММ(ЕСЛИ(D4=0;(ТабНомер=D3)*(Дата<=
E3)*Расход); (НомерКар=D4)*(ТабНомер=D3)*(Дата<=E3)* Расход)}.

7. Для расчета аналогичных данных за один день по конкретному водителю используется формула массива, введенная в ячейку F6: {=СУММ(ЕСЛИ(ТабНомер=D3;1;0)*ЕСЛИ (Дата=E3;1;0)*Расход)}.

8. Для определения общего остатка средств на электронной карточке на дату, указанную в ячейке Е3, введите в ячейку G4 формулу массива: {=СУММ((НомерКар=D4)* (Дата=E3)*Остаток)}.

9. Если вы хотите определить общую сумму средств, которые были аккумулированы на определенной карточке, введите в ячейку Н4 формулу: =E4+G4. В результате вы получите суммарный результат по карточке, номер которой указан в ячейке D4.

Как пользоваться расчетным блоком

1. Введите в ячейку D3 табельный номер водителя.

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

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

4. Введите в ячейку Е3 конечную дату анализируемого периода.

5. Введите в ячейку F3 дату анализируемого дня (при необходимости).

Расчетные данные появятся в ячейках автоматически.

Стандартные возможности таблицы-ведомости

Дополнительные аналитические данные можно получить, используя стандартные возможности Excel. Наиболее доступный способ - это установка фильтров, позволяющих группировать данные по определенным признакам. Выделите ячейки A9:J9 и выберите команду Автофильтр (меню Данные - Фильтр). Признаком появления новой возможности по обработке данных будет установка в каждой ячейке стрелки автофильтра. Теперь щелкните стрелку, например, в ячейке D4, и укажите в появившемся списке строку "Гонтар В.А.". В результате в таблице будут представлены данные только по этому водителю (рис.5). Аналогичные выборки можно делать по любому списку (графе).

Рисунок 5

Появление ошибки #Н/Д.

Наиболее распространенная проблема при использовании формул массива. Такое сообщение появляется в ячейке, если используемые в формулах интервалы ячеек не равны и не симметричны. Чтобы исправить это, достаточно открыть окно Присвоение имени (меню Вставка-Имя-Присвоить…) и скорректировать формулу имени. Числовые аргументы должны быть одинаковыми (рис.6).

Эта же операция пригодится вам, если объем данных, внесенных в таблицу-ведомость, превысит используемый размер именованных интервалов ячеек. Пусть, рассмотренная таблица полностью заполнена и данные необходимо располагать ниже 25 строки (см. графу Интервал, Таблица 1 и рис.1). Тогда просто увеличьте последний числовой аргумент с 25 до нужного размера, например, до 110 (поле Формула, рис.6).

Рисунок 6

Документальное оформление путевок
Практика
Пути получения путевок Вариант 1. Оформление путевок, которые были получены за счет средств ФСС по ВПТ Вариант 2. Оформление путевок, которые были приобретены за счет средств предприятия...

В рубрике:

Мир связи в программе «1С: Бухгалтерия 7.7 для Украины»
№ 38 (22.9.2003) :: Автоматизация учета
Приобретение, установка телефона Оплата услуг телефонной связи и Интернета Приобретение, подключение мобильного телефона и оплата связи...

Учет запасов на предприятии общественного питания в программе «1С: Бухгалтерия 7.7 для Украины»
№ 36 (8.9.2003) :: Автоматизация учета
Учет сырья по закупочной стоимости Учет сырья по ценам реализации Учет товаров Подведение итогов Рассмотрим, как можно вести учет запасов на предприятии общественного питания в типовой конфигурации, без дополнительных настроек прог...