Разработка учетных приложений в 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 Расчет выполнен
Вывод: все задачи выполнены полностью,
цель контрольной работы достигнута.