Технология работы с формулами

на примере подсчета стоимости обслуживания.

О каждом маршруте известна стоимость собственно экскурсии и стоимость транспортных расходов. Известна также емкость автобуса. В самой таблице фиксируется желаемый маршрут и число заявок (человек) на обслуживание. Минимальное число автобусов определяется как целая часть от (Число заявок)/(Вместимость автобуса). Фирма обслуживает не всех туристов, а только такое их максимальное количество, чтобы не оказалось ни одного автобуса, заполненного менее чем на 30%. Фактическое число определяется в колонке Выделено автобусов. Для этого, нужно выяснить, сколько туристов еще не размещено в автобусы. Если их оказалось больше чем 30% емкости автобуса, значит, Выделено автобусов будет на единицу больше минимально необходимого их числа. В противном случае, Выделено будет равно минимуму. Стоимость обслуживания определяется произведением числа выделенных автобусов на сумму экскурсионного и транспортного обслуживания маршрута. В колонке Примечание в столбце F следует показать число пустых мест в автобусе или число отклоненных заявок (то, что есть). Рядом в столбце G должно быть выведено (отклонено заявок) или (пустых мест) по факту. В клетке Е14 показать число обращений для обслуживания более 100 заявок. Для выявления стоимости маршрута из таблицы тарифов следует воспользоваться функцией ГПР().

В созданной рабочей книге рассчитайте:

· минимально необходимое число автобусов и количество фактически выделенных автобусов;

· стоимость обслуживания маршрутов;

· количество отклоненных заявок и пустых мест в автобусе.

Все расчеты производятся с помощью формул, которые вводятся в соответствующие ячейки таблицы. В формулах используются функции, встроенные в MS Excel. Формулы будем вводить в строку 9 для маршрута А. Для расчета показателей остальных маршрутов, скопируем формулы в соответствующих столбцах.

1. Минимально-необходимое количество автобусов подсчитывается с помощью функции ОКРВНИЗ().

2. Количество фактически выделенных автобусов, число пустых мест в автобусе и число отклоненных заявок рассчитывается с помощью функции ЕСЛИ().

3. Стоимость обслуживания маршрута подсчитывается с помощью функции ГПР().

Задание 15

Реализовать базу. На листах, содержащих данные, ввести не менее 12-15 записей.

БД оплаты междугородних телефонных разговоров. Справочник абонентов содержит номер телефона (упорядочены), фамилию и адрес, а также вычисляемое поле – сумма неоплаченных переговоров. Второй справочник содержит коды городов, названия городов и зону. Третий справочник определяет стоимость одной минуты разговора в зависимости от зоны. Основная таблица содержит дату переговоров, время переговоров, дату оплаты (отсутствует – не оплачено), номер телефона абонента, код города, продолжительность разговора, стоимость. Стоимость определяется стоимостью вызова (звонка) плюс тариф для данной зоны, умноженный на продолжительность разговора. При времени звонка от 22:00 до 6:00 вводится льготный 60% тариф. Сформировать отчет о работе за последний месяц – сумма оплат и неоплаченных счетов.

 

Задание 16

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

а) ,

при , шаг по x и по y = 0,5;

б) ,

при , шаг по x и по y = 0,5;

в) ,

при , шаг по x = , , шаг по y = 0,1.

Таблицы оформить по образцу:

z=x^2+sin(y)
X	Y	Z
0,5	0,5

Построить графики функций по всем пунктам.

Задание 17

Реализовать базу. На листах, содержащих данные, ввести не менее 12-15 записей.

БД кадров предприятия. Основная таблица содержит фамилию, отдел, оклад, дату рождения, дату приема, дату увольнения (если работает – отсутствует). Справочная таблица отделов содержит название отдела, фамилию начальника. Число сотрудников отдела вычисляется через первую таблицу (функции БДСЧЕТ(), СЧЕТЕСЛИ()). Определяется условие – ветеран труда, работающий в настоящее время и имеющий стаж более 5 лет. Для них определяется 20% надбавка к окладу. В таблицу отделов вносится количество ветеранов труда и фонд заработной платы с учетом надбавок.

 

Задание 18

Подготовить табличную форму, составить и отладить алгоритм ее решения. Ввести наименования переменных для исходных данных и результатов расчетов с указанием их типов. Подобрать исходные данные, позволяющие в полной мере проверить алгоритм решения задачи. При этом число элементов в массивах исходных данных должно быть 10-12.

Дан список продуктов питания, включающий в себя наименование, дату изготовления и срок годности (в месяцах). Определить на текущую дату продукты с просроченным сроком годности (вывести сообщение «Негоден!»). Для остальных продуктов определить оставшийся интервал годности в днях. В случае, если до окончания срока годности осталось менее месяца, вывести сообщение «Срочная реализация!».

Задание 19

Подготовить табличную форму, составить и отладить алгоритм ее решения. Ввести наименования переменных для исходных данных и результатов расчетов с указанием их типов. Подобрать исходные данные, позволяющие в полной мере проверить алгоритм решения задачи. При этом число элементов в массивах исходных данных должно быть 10-12.

Дан список клиентов банка, арендующих сейфы, с указанием сроков аренды (в днях). В списке имеется графа «Постоянный клиент» с пометками «Да» для части клиентов. Определить стоимость аренды для каждого клиента с учетом следующих тарифов: до 30 дней аренды – 20 руб./сутки, от 30 до 60 дней – 15 руб./сутки, свыше 60 дней – 12 руб./сутки. Причем, постоянным клиентам предоставляется скидка 10% от расчетной стоимости аренды.

Задание 20

Реализовать базу. На листах, содержащих данные, ввести не менее 12-15 записей.

БД библиотеки. Основный справочник книг содержит шифр книги (упорядочены), фамилию автора, название книги, код УДК, стоимость, количество экземпляров, количество выданных экземпляров (подсчитывается). Обратная таблица содержит те же данные, но упорядоченные по УДК (тематический каталог). Каталог читателей содержит номер билета, фамилию читателя, адрес, количество выданных книг (подсчитывается). Основная таблица – книговыдача – содержит шифр книги и номер билета, фамилию автора и название книги, дату выдачи, дату возврата (отсутствует – книга на руках), крайнюю дату возврата (2 недели от даты выдачи). Для читателей подсчитывается количество выданных книг и количество просроченных книг. Сформировать отчет о работе библиотеки за последний месяц – количество выданных книг, количество просроченных книг, количество читателей, просрочивших книги. Сумма штрафов подсчитывается из расчета 5% стоимости книги за каждые просроченные сутки.