Лабораторная работа № 5. Выполнение типовых экономических расчетов в EXCEL. Задача о командировках.

Задание 1. Задача о командировках.

Определить оплату командировочных расходов группе работников, посетивших научные семинары в городах Москве, С-Петербурге и Новосибирске.

1. Оформить рабочий лист в соответствии с приведенным образцом (рис. 13).

Рис. 13. Исходные данные для задачи о командировках

2. Выполните расчет оплаты проезда в столбце «Оплата проезда», используя функцию ЕСЛИ и учитывая, что проезд не оплачивается в случае отсутствия документов.

3. Выполните расчет проживания в сутки, учитывая, что при наличии документов за проживание расчет производится по предоставленным документам, но не более 270 рублей в сутки. При отсутствии документов начисляется 7 рублей за сутки. Используйте для расчета функцию ЕСЛИ и другие логические функции.

4. Рассчитайте суточные, исходя из приведенных тарифов для различных городов, используя функцию ЕСЛИ.

5. Рассчитайте сумму к оплате для каждого командированного сотрудника, учитывая, что она равна сумме стоимости проезда, суточных и стоимости проживания. С помощью соответствующих формул вычислите и занесите в отдельные ячейки минимальные, максимальные и средние командировочные расходы. Построить диаграмму, иллюстрирующую сумму, полученную каждым работником на руки.

Лабораторная работа № 6. Работа с блоками (функции просмотра)

Задание 1. Задача о складе

Необходимо рассчитать сумму скидки на товар, купленный на мелкооптовом складе. Причем скидка предоставляется в зависимости от количества купленного товара: от 100 до 150 штук – 5%, от 150 до 200 – 10%, от 200 до 250 – 15%, от 250 до300 – 20%, от 300 до 350 – 25%, более 350 – 30%. Ассортимент и цены приведены в соответствующей таблице (рис. 14).

Рис. 14. Исходные данные для задачи

Данная задача может быть решена с помощью применения функции ЕСЛИ. Однако это будет иметь определенные неудобства в силу громоздкости организации формулы. Кроме того, функция ЕСЛИ допускает не более 7 вложений, поэтому не всегда может быть применена. Данную задачу решим с помощью функции ВПР – функции вертикального просмотра данных, которая находится в категории «Ссылки и массивы».

Функция ВПР ищет заданное значение в первом столбце указанной таблицы и возвращает значение, расположенное в той же строке, что и найденное, в указанном столбце. Использование данной функции требует некоторой подготовительной работы. Предварительно необходимо создать таблицу подстановки, содержащую сведения относительно возможных скидок (рис. 15). Обратите внимание, что данная таблица организована таким образом, что в первом столбце, напротив соответствующей величины скидки, располагается нижняя граница заданных интервалов по сумме заказа. Такая организация таблицы подстановки позволяет выводить значение, расположенное во втором столбце и в том случае, когда заданная величина находится в интервале между любыми двумя значениями, находящимися в первом столбце.

Рис. 15. Таблица подстановки

Аргументы функции ВПР:

Искомое_значение– это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Табл_массив– это таблица с информацией, в которой ищутся данные. Можно использовать ссылку на диапазон ячеек или его имя (в случае, если диапазон предварительно был поименован).

Номер_индекса_столбца– это номер столбца в массиве инфо_таблица, в котором должно быть найдено нужное значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента инфо_таблица; если номер_столбца равен 2, то возвращается значение из второго столбца аргумента инфо_таблица и так далее.

Диапазон_просмотра– это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше чем искомое_значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

Итак, заполняя столбец «Скидка», воспользуемся функцией ВПР. В качестве ее аргументов в рассматриваемой задаче необходимо ввести:

- Искомое значение – указать адрес ячейки, в которой будет находиться значение количества купленного товара (Количество);

- Табл_массив – указать координаты созданной таблицы скидок;

- Номер_индекса_столбца – набрать на клавиатуре цифру 2 (столбец, содержащий данные относительно скидок);

- Диапазон просмотра – ИСТИНА (или опущен).

Замечание. Функция ГПР применяется аналогично. Отличие состоит в том, что таблица подстановки для ее применения располагается горизонтально и основными аргументами являются номера строк.

Задание 2. Задача о тестировании.

Имеются сведения о результатах тестирования студентов одной из групп некоторого ВУЗа и таблица, по которой комиссия выводит оценку для каждого студента по итогам трех тестов. Необходимо автоматизировать эту работу.

1. Создать таблицу по образцу (рис.16).

Рис. 16. Исходные данные для задачи о тестировании

2. Добавить столбец «Результат» и вычислить итоги по трем тестам.

3. Ниже таблицы тестирования создать вертикальную Справочную таблицу по образцу (рис. 17).

4. Добавить столбец «Оценка» и заполнить его, ссылаясь на справочную таблицу и используя функцию ВПР.

5. Ниже Справочной таблицы создать горизонтальную Справочную таблицу по образцу (рис. 17).

6. Добавить столбец «Оценка2» и заполнить его оценками, ссылаясь на горизонтальную Справочную таблицу и используя функцию ГПР.

Рис. 17. Справочные таблицы

Задание 3. Расчет окладов и премий.

Используя возможности функции ВПР, рассчитать оклады сотрудникам кафедры «Бухгалтерский учет» и начислить премию в размере 19%. Для расчета оклада использовать таблицу разрядов (рис. 18).

Рис. 18. Исходные данные и таблица подстановки для задачи об окладах

Задание 4. Использование функций ПОИСКПОЗ и ГПР

В магазине имеется лист цен в виде таблицы (рис. 19). В строке «Товар» проставлены граничные значения количества товара, а ниже – цены за единицу товара. Для оптовых покупателей цены снижаются. Например, если покупатель приобретает партию из 6 аккумуляторов, он платит по 300 руб. за шт., если партия составит 30 шт., то – по 270 руб., если 58, то – по 250 руб. Покупатель заказывает товары, представленные в накладной (рис. 19). Необходимо автоматизировать расчет цены за единицу товара, вычислить стоимость товаров каждого наименования и общую сумму заказа.

Рис. 19. Исходные данные для задачи 4

1. Создать «Лист цен» по образцу (рис. 19).

2. Ниже создать накладную по образцу (рис. 19). Дополнить ее столбцами «Цена за 1» и «Стоимость».

3. Изучить справочную информацию о функции ПОИСКПОЗ, которая возвращает позицию искомого значения в массиве, а не само значение. Синтаксис функции приводится ниже.

ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

Искомое_значение может быть значением (числом, текстом или логическим значением) или ссылкой на ячейку, содержащую такое значение

Просматриваемый_массив – блок, состоящий из одного столбца или одной строки.

Тип_сопоставления – число –1,0,1 (в нашем случае, целесообразно выбрать 0).

4. Заполнить столбец «Цена за 1», используя функцию ГПР (в качестве аргумента номер строки использовать функцию ПОИСКПОЗ).

5. Вычислить стоимость закупок каждого наименования и общую стоимость заказа.