Практическая работа по информатике в Excel

 (файл .xls на странице  www.matburo.ru/sub_appear.php?p=l_excel ) 

Тема: Отчет по командировке

Содержание

2.         Задание .......................................................................................................... 3

3.         Анализ задачи ............................................................................................... 4

4.         Тестовый пример .......................................................................................... 7

5.         Инструкция пользователю ........................................................................... 9

6.         Список литературы .................................................................................... 10

Задание

Сформировать таблицу для составления отчета по командировке. Предусмотреть возможность автоматического расчета суммы аванса в зависимости от длительности командировки, региона, удаленности пункта назначения, вида транспорта. Количество регионов - не менее 5, количество градаций по удаленности - не менее 5. Виды транспорта: самолет, поезд, автобус. Построить диаграмму изменения размера расходов на проживание и размера суточных по регионам.

Структуры таблиц исходных данных.

                    Список сотрудников                                     Размер суточных и стоимость проживания

                                Средняя стоимость проезда в один конец

Структуры таблиц выходных данных.

Имя ...

Табельный номер ...

Место назначения ...

Регион ....

Удаленность ....

Цель поездки ...

Срок командировки с ... по ...

Всего суток ...

Транспорт ... Аванс 

 на проезд ...  суточные ...

                на проживание ...

                итого ...

Расходы по факту

                на проезд ...      на проживание ...  итого (с учетом суточных)  ...

Итого к выплате ....

Итого к возврату ...

Анализ задачи

Решение поставленной задачи направлено на автоматизацию составления отчета по командировке. Автоматически рассчитывается суммы аванса в зависимости от длительности командировки, региона, удаленности пункта назначения, вида транспорта

Выбор вида транспорта происходит также автоматически в зависимости от дальности: до 150 км – автобус, от 150 до 1500 км – поезд и свыше 1500 км – самолет.

Структура таблиц исходных и выходных данных сформирована в соответствии с условием задачи.

Таблица «Список сотрудников» включает в себя табельный номер, фамилию и инициалы сотрудников. В нашем примере это 6 человек.

Таблица «Размер суточных и стоимость проживания» состоит из 3 столбцов (регион, суточные, проживание в сутки) и 6 строк (без учета заголовка).

Таблица «Стоимость проезда за 1 км в один конец состоит из 4 столбцов. В первом указана удаленность населенных пунктов из предыдущей таблицы, во 2-5 столбцах – тарифы за проезд 1 км на самолете, поезде, автобусе.

Размер суточных          Средняя стоимость проезда  Список сотрудников                   и стоимость проживания           за 1 км в один конец

Таблица выходных данных формируется автоматически после ввода региона и сроков командировки.

Оператор заполняет столбцы, выделенные желтым цветом: регион, цель поездки, сроки командировки с (дата) по (дата).

Удаленность вычисляется по формуле:

=ЕСЛИ(B$14=$D$3;$H$3;ЕСЛИ(B$14=$D$4;$H$4;ЕСЛИ(B$14=$D$5;$H$5;ЕСЛИ(B$14=$D$6;$

H$6;ЕСЛИ(B$14=$D$7;$H$7;$H$8)))))

Цель поездки вводится, но на расчет выплат не влияет.

Срок командировки вводится в формате даты, а количество суток, проведенных в командировке вычисляется по формуле:

=B19-B18+1

Последующие данные берутся из исходных таблиц в соответствии с удаленностью региона командировки. 

Выбор вида транспорта производится автоматически в зависимости от дальности: 

=ЕСЛИ(B$15>=1500;$I$2;ЕСЛИ(И(B$15<1500;B$15>=150);$J$2;$K$2))

Аванс на проезд: =ЕСЛИ(B$15>=1500;$I$3*2*B15;ЕСЛИ(И(B$15<1500;B$15>=150);$J$3*2*B15;$K$3*2*B15))

Тариф за 1 км умножается на расстояние до пункта назначения и умножается на 2, т.к. нужно оплатить дорогу туда и обратно.

Суточные:

=ЕСЛИ(B$15>=1500;$E$7*B$20;ЕСЛИ(И(B$15<1500;B$15>=150);$E$6*B$20;$E$3*B$20)) На проживание:

=ЕСЛИ(B$15>=1500;$F$7*B$20;ЕСЛИ(И(B$15<1500;B$15>=150);$F$6*B$20;$F$3*B$20))

Суточные и стоимость проживания в сутки умножаются на количество дней, проведенных в командировке.

Итоговый аванс представляет сумму аванса на проезд, суточных и на проживание.

Расходы          по        факту заполняются оператором   в          соответствии с          документами, представленными вернувшимся из командировки.

Итоговая сумма расходов составляется из фактических расходов на проезд, на проживание и суточных.

=СУММ(B28:B29)+B24

Итоговая сумма к выплате вычисляется как разность между фактическими затратами и выплаченным авансом при условии, что затраты превышают аванс.

=ЕСЛИ(B30-B26>0;B30-B26;0)

Итоговая сумма к возврату вычисляется как разность между выплаченным авансом и фактическими затратами при условии, что аванс превышает затраты.

=ЕСЛИ(B26-B30>0;B26-B30;0)

Форматы представления данных в ячейках:

Имя, регион, цель поездки, транспорт – текстовый формат.

Сроки командировки с… по… – формат даты.

Остальные данные числовые.

Тестовый пример

Приведем один столбец с формулами, остальные столбцы аналогичны первому.

Выходные данные                                      

Составим диаграмму расходов на проживание и размера суточных по регионам.

Тип диаграммы – трехмерная гистограмма.

Исходные данные:

Ряд «суточные» =Лист1!$B$24:$G$24

Ряд «на проживание» =Лист1!$B$25:$G$25

Инструкция пользователю

Вводятся только данные, выделенные желтым цветом.

Регион, возможно 2 способа: 

1.      Ввод слова с клавиатуры в таком виде, как оно написано в столбце D исходной таблицы (с заглавной буквы, без пробелов и переходов на другую строку).

2.      Выбор из таблицы. Вводите знак = и затем мышью выберите ячейку с нужным городом, нажмите клавишу «Enter». Цель поездки: вводите с клавиатуры.

Срок командировки с… по…: вводите с клавиатуры в формате даты дд.мм.гггг (день.месяц.год), разделитель – точка.

Расходы по факту на проезд и на проживание: вводите числа, десятые доли отделяются запятой.

Список литературы

1.                  Информатика(методические указания к выполнению расчетного задания). Окунев Б.В., Прохоренков П.А., Фомченков В.П. – Смоленск, СФ МЭИ – СИБП, 1998. –  15 с., тираж 50 экз.

2.                  Excel для Windows 95. Энциклопедия пользователя: пер. с англ. /  Пол МакФедриз. – К.: НИФП «ДиаСофт Лтд.», 1997. – 624 с.