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

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

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

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

Задачи:

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

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

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

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

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

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

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

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

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

a. Титульный лист.. Цель.. Задачи.

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

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

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

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

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

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

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

Задания

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

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

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

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

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

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

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

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

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

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

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

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

Если после заполнения ячейки данными поменять формат на "Общий", то мы увидим десятичное число, например "41580,41667", в котором целая часть (41580) означает число целых дней, прошедших с 1 января 1900 г., а дробная часть (0,41667) означает число часов в долях от суток, т.е. от 24 часов. Для определения числа часов, прошедших с момента взятия автомобиля до момента оплаты (или возврата), нужно из большей даты вычесть меньшую и полученную разницу умножить на 24. Например, дата 12.11.2013 0: 00 в общем формате равна 41590,0, дата 02.11.2013 10: 00 в общем формате равна 41580,41667, вычитая из большего числа меньшее, получаем 41580,41667 - 41590,0 = 9,58333. Умножив на 24, получим 9,58333*24 = 230 (часов). Таким образом для подсчета оплаченных часов и часов разницы (столбцы D и G), нужно в этих столбцах устанговить формат "Общий", тогда результат будет в часах. Для этого выделим ячейки D8: D12 и G8: G12, выберем Формат Ячейки "Общий". Для подсчета количества оплаченных часов введем в ячейку D8 формулу <= (С8-В8) *24> и размножим ее в ячейки D9: D12, аналогично для подсчета количества часов разницы введем в ячейку G8 формулу <= (F8-С8) *24> и размножим ее в ячейки G9: G12. Для подсчета суммы оплаты введем в ячейку Е8 формулу <A8*D8> и размножим ее в ячейки E9: E12, аналогично для подсчета суммы разницы в ячейку Е8 формулу <A8*G8> и размножим ее в ячейки Н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%.

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

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

Откроем Лист2, переименуем его в Зад_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 массив приложение

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

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

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

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

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

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

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

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

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

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

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