9. Ведение базы данных
об оплате работ по договорам
Цель работы – проверка навыков работы с базами данных
в электронных таблицах
Общие сведения
занимается работами по договорам. Каждый договор разбивается на этапы. Например, договор №1 называется "Разработка информационной системы маркетинга для ". Он состоит из следующих этапов.
1. Детализация исходных требований к системе.
2. Разработка программного обеспечения.
3. Отладка системы.
4. Сдача-приемка.
5. Обучение персонала.
Фирма ведет несколько (порядка 10) аналогичных договоров одновременно.
Оплата выполненных работ обычно производится поэтапно с авансовым платежом.
Ход оплаты необходимо контролировать. Для этого периодически требуется определять общую стоимость этапов, которые должны завершиться по плану, а также общую стоимость поступивших платежей. Затем определяется разность между ними. Значительное отставание фактической оплаты от плановой требует принятия решительных мер.
Сведения о договорах хранятся в двух таблицах – базах данных в Excel. Одна из таблиц – Плановый ход оплаты по договорам. Ее столбцы: Номер договора, Номер этапа, Сумма, Дата оплаты плановая. Сведения в нее заносятся после оформления каждого договора.
Вторая таблица отражает фактическую оплату. Ее столбцы: Номер платежного документа, Номер договора, Номер этапа, Сумма, Дата оплаты фактическая. Таблица заполняется по мере поступления платежей, поэтому в ней не может быть дат, относящихся к будущему.
Важным требованием к отчетам является сохранение их правильности при добавлении и удалении строк исходных таблиц.
Задание
Требуется разработать три отчета: о состоянии оплаты на текущую дату, на заданную дату и на заданный период. Каждый отчет должен содержать заголовок, в котором записаны даты. Содержание отчета – три числа: Оплата по плану, Оплачено фактически, Разница.
Отчет должен автоматически определять требуемые суммы при вводе в его заголовок желаемых дат.
Отчеты должны иметь вид, годный для предоставления руководству фирмы.
Рекомендации по работе
Таблицы плановой и фактической оплаты, а также каждый из отчетов лучше выполнять на отдельных листах. Столбцы в таблицах должны иметь названия.
Заголовки отчетов должны занимать несколько ячеек и иметь вид:
Отчет по состоянию на |
Отчет за период от | до |
Если отчет составляется на сегодняшнюю дату, то она должна вставляться в свободную ячейку автоматически (используйте функцию СЕГОДНЯ(). В остальных случаях пользователь может вставить в свободные ячейки заголовка требуемую дату.
Для простоты расчетов в отчетах на текущую и заданную дату учитываются данные с начала года. Договоров, продолжающихся с прошлого года, пока нет[1].
Для извлечения данных используются функции работы с базами данных. К ним относятся функции БДСЧЁТ и БСУММА. Среди их аргументов следует задать:
1. Базу данных. Это блок ячеек, в которых содержатся данные. Заголовки столбцов также следует включить в этот блок. Для того чтобы можно было вносить новые данные, следует включить в блок одну или несколько лишних строк снизу.
2. Поле. Задается название требуемого поля для суммирования. Это чаще всего ячейка с заголовком поля Сумма. Можно задать ссылку на ячейку заголовка поля или название поля в кавычках. Если используется функция подсчета строк, то поле можно выбирать произвольно.
3. Критерий. О критериях отбора рассказывалось в работе, посвященной фильтрации данных. Следует иметь в виду следующие моменты.
q В критериях можно использовать условие типа больше (меньше) заданной даты. Будут выбраны соответственно более поздние и более ранние даты.
q В критерии нельзя вставлять формулы, поэтому в нем не удается использовать ссылку на дату или функцию СЕГОДНЯ().
Рекомендуется добавить в таблицы данных новые столбцы. Они будут содержать разность дат оплаты и дат, взятых из заголовков отчетов.
Не забудьте присвоить новым столбцам имена.
При создании формулы разности дат Excel может ошибочно установить для этой ячейки формат даты. Установите вручную числовой формат и Вы увидите в ячейке количество дней между двумя датами. Эта разность может быть положительной или отрицательной в зависимости от того, какая из сравниваемых дат является более ранней.
Теперь можно строить критерий. Он будет указывать на определенный знак разности дат. В качестве условия установите >0 или <0.
В случае вычислений для заданного интервала дат постройте два столбца для сравнения даты оплаты с началом и концом периода и используйте сложный критерий. Наиболее просто выбрать какой-либо этап, оплата по которому входит в выбранный диапазон, посмотреть на знаки разностей дат и использовать эти сведения для построения критерия.
Перед сдачей работы не забудьте красиво оформить таблицы отчетов.
Результат работы
q Рабочая книга с двумя таблицами исходных данных и тремя отчетами.
Работа принимается по экрану предварительного просмотра перед печатью. Проверяется также правильность работы при добавлении новых записей
[1] Такая ситуация возникает в первый год деятельности фирмы.
Основные порталы (построено редакторами)