Разработка учетных приложений в MS Office

Контрольная работа по курсу

" Разработка учетных приложений в MS Office"

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

Задачи:

Изучить средства создания и форматирования электронных таблиц Excel.

Изучить все возможности условного форматирования с использованием инструмента «Условное форматирование» .

Изучить функции Excel следующих категорий: математические, статистические, условные, ссылки и массивы.

Изучить механизм создания сводных таблиц.

Порядок работы:

Создать книгу Excel нового формата.

Решить задачи на каждом листе отдельно, озаглавленном по названию задачи.

Составить отчет в формате А4.

Отчет должен содержать:

a. Титульный лист.

b. Цель.

c. Задачи.

d. Постановку задачи № 1.

e. Полное решение с указанием всех формул задачи № 1.

f. Скриншот листа с задачей № 1.

g. Постановку задачи № 2.

h. Полное решение с указанием всех формул задачи № 2.

l. Скриншот листа с задачей № 2.

j. Постановку задачи № 3.

k. Полное решение задачи № 3.

1. Скриншот листа с задачей № 3.

Задания

Задача № 1. Прокат автомобилей

В таблице должно быть рассчитано:

Количество часов в столбце «Оплачено», пройденных с момента взятия машины по предполагаемую оплаченную дату.

Определить сумму в столбце «Оплачено» как произведение «Часы» и «Цена» .

Количество часов в столбце «Разница», пройденных с момента оплаченной даты по фактическую дату возврата.

Определить сумму в столбце «Разница» как произведение «Часы» и «Цена» .

Определить «Доплата/Возврат» следующим образом:

a. если клиент фактически вернул машину раньше оплаченной даты, то мы ему должны вернуть только половину суммы из столбца «Разница», применив коэффициент возврата 0,5;

b. если клиент фактически вернул машину позже оплаченной даты, то мы с него должны взять сумму из столбца «Разница» больше в 1,3 раза, применив коэффициент доплаты 1,3.

Запустим MS Excel, Переименуем Лист1 в «Зад₁», создадим и заполним данными согласно заданию расчетную таблицу (рис.1).

Рис. 1 Шаблон расчетной таблицы

При заполнении ячеек, содержащих даты и время, установим в них формат «ДД. ММ. ГГГГ ч: мм» (рис.2).

Рис. 2 Установка формата ячеек (дата/время)

Если после заполнения ячейки данными поменять формат на «Общий», то мы увидим десятичное число, например «41 580,41667», в котором целая часть (41 580) означает число целых дней, прошедших с 1 января 1900 г., а дробная часть (0,41 667) означает число часов в долях от суток, т. е. от 24 часов. Для определения числа часов, прошедших с момента взятия автомобиля до момента оплаты (или возврата), нужно из большей даты вычесть меньшую и полученную разницу умножить на 24. Например, дата 12.11.2013 0: 00 в общем формате равна 41 590,0, дата 02.11.2013 10: 00 в общем формате равна 41 580,41667, вычитая из большего числа меньшее, получаем 41 580,41667 — 41 590,0 = 9,58 333. Умножив на 24, получим 9,58 333*24 = 230 (часов). Таким образом для подсчета оплаченных часов и часов разницы (столбцы D и G), нужно в этих столбцах устанговить формат «Общий», тогда результат будет в часах. Для этого выделим ячейки D8: D12 и G8: G12, выберем Формат Ячейки «Общий». Для подсчета количества оплаченных часов введем в ячейку D8 формулу <= (С8-В8) *24> и размножим ее в ячейки D9: D12, аналогично для подсчета количества часов разницы введем в ячейку G8 формулу <= (F8-С8) *24> и размножим ее в ячейки G9: G12. Для подсчета суммы оплаты введем в ячейку Е8 формулу и размножим ее в ячейки E9: E12, аналогично для подсчета суммы разницы в ячейку Е8 формулу и размножим ее в ячейки Н9: Н12.

Для заполнения столбца Доплата/Возврат впишем в ячейку I8 формулу <=ЕСЛИ (H8>=0; $B$ 2*H8; $B$ 1*H8) >, которая будет учитывать применение коэффициентов возврата или доплаты (0,5 или 1,3) в зависимости от знака содержимого ячейки Н8. Размножим эту формулу в ячейки I9: I12. Для наглядности применим к ячейкам I8: I12 условное форматирование, чтобы сумма возврата была красного цвета (как это принято в бухгалтерских документах. Для этого выделим ячейки I8: I12, выберем Формат — Условное форматирование и в появившемся окне введем условие «значение меньше 0», нажмем кнопку «Формат», выберем «Вид», красный цвет (рис.3), нажмем ОК и получим окно «Условное форматирование» (рис.4), нажав ОК, увидим результат (рис.5).

Рис. 3 Выбор цвета ячеек с отрицательным значением

Рис. 4 Условный формат установлен

Рис. 5 Результаты расчета

Как видим, в ячейке I9 доплаты нет (0р.), поскольку дата и время возврата совпадают с предварительной оплатой, в ячейке I12 красным цветом выделена сумма возврата со знаком минус, равная половине переплаты, а в остальных ячейках указана сумма доплаты с учетом коэффициента 1,3.

Задача № 2. Цена товара

Определить новую цену товара. О каждом товаре известна дата поступления и установленная в этот момент на него цена. По условиям магазина после 30 дней хранения на него распространяется скидка в 10%.

1. Определить количество товара, поступившего более чем 30 дней назад.

2. Выделить ячейки в столбце «Дней хранения», у которых значение превышает 50 дней.

Откроем Лист2, переименуем его в Зад₂, заполним расчетную таблицу, согласно заданию (рис.6).

Рис. 6 Шаблон расчетной таблицы

Выделим диапазон В7: В16 и установим в нем формат «ДД МММ», (рис.7).

Рис. 7 Выбор формата

Выделим ячейку D7, впишем в нее формулу для расчета числа дней хранения < =$E$ 1-B7> и размножим ее в ячейки D8: D16, появятся дни хранения. Выделим ячейку Е7, впишем в нее формулу

< =ЕСЛИ (D7>30; C7*$B$ 3; 0) >, которая будет определять скидку на товар (10%), срок хранения которого превысил 30 дней. Размножим формулу в ячейки Е8: Е16. Установим с столбцах С, Е, F формат «Денежный» .

Выделим ячейку F7, впишем в нее формулу < =C7-E7>, Размножим формулу в ячейки F8: F16.

Для выделения ячеек в столбце «Дней хранения», у которых значение превышает 50 дней, применим условное форматирование. Для этого выделим ячейки D7: D16, выберем Формат — Условное форматирование и в появившемся окне введем условие «значение больше 50», нажмем кнопку «Формат», выберем «Вид», красный цвет, нажмем ОК, в окне «Условное форматирование» еще раз нажмм ОК и ячейки с выполненным условием окрасятся в красный цвет.

Выделим ячейки С17, Е17, F17, нажмем знак суммы на панели инструментов и в этих ячейках появятся суммы столбцов.

Выделим ячейку D18, впишем в нее формулу

< =СЧЁТЕСЛИ (D7: D16; «>30») > и получим количество товара, поступившего более чем 30 дней назад (7). Готовая таблица показана на рис. 7.

таблица excel массив приложение Рис. 7 Расчет выполнен

Задача № 3. Сводная таблица

Создайте сводную таблицу, содержащую следующие сведения по таблице «Цена товара» :

средняя исходная цена;

максимальная скидка;

минимальная новая цена.

Откроем Лист 3, переименуем его в Зад₃, заполним расчетную таблицу, согласно заданию (рис. 8).

Рис. 8 Шаблон таблицы задачи № 3

Установим в ячейках В3: В5 формат «Денежный». Выделим ячейку В3, впишем в нее формулу <=СРЗНАЧ (Зад₂! C7: C16) > и в этой ячейке появится значение 327, что является средней исходной ценой. Выделим ячейку В4, впишем в нее формулу <=МАКС (Зад₂! E7: E16) > и в этой ячейке появится значение 70, что является максимальной скидкой. Выделим ячейку В5, впишем в нее формулу <=МИН (Зад₂! F7: F16) > и в этой ячейке появится значение 90, что является минимальной новой ценой.

Окончательный вид таблицы представлен на рис. 9.

Рис. 9 Расчет выполнен

Вывод: все задачи выполнены полностью, цель контрольной работы достигнута.