Практическая работа на тему "Расчет в электронной таблице с использованием встроенных функций" по специальности 23.02.01 Организация перевозок и управление на транспорте

Практическая работа     

Специальность 23.02.01   Организация перевозок и управление на транспорте

Тема: Расчет в электронной таблице с использованием встроенных функций.

Цель: - изучить структуру встроенных функций;

-         научиться применять встроенные функции при решении прикладных задач в ЭТ

Необходимо знать:

ü  виды адресации, используемые в формулах;

ü  знать особенности ввода формул с абсолютной адресацией ячеек;

ü  составные части функций; правила использования Мастера функций;

Необходимо уметь:

ü  вводить формулы, используя абсолютные адреса ячеек;

ü  применять встроенные функции при решении прикладных задач в ЭТ

ü  пользоваться автозаполнением ячеек таблицы;

оборудование: ПК  с  установленной операционной системой Windows,   ПО  MS  Excel, инструкционные карты, карточки-задания

Краткие теоретические сведения

В поставку EXCEL 2007 входит более 300 функций. Используя VBA можно создавать свои функции.

1. Формат стандартной функции:

Имя_Функции (Аргумент)

2.  Аргумент функции

Аргументом может быть:

1. Пустой аргумент () - СЕГОДНЯ().

2. Константа - КОРЕНЬ(124).

3. Ссылка на ячейку (адрес) - КОРЕНЬ(А4).

4. Диапазон - СУММ(А3:А8).

5. Несколько аргументов -

а) фиксированное число - ОКРУГЛ(123,4565; 2) = 123,46.

б) неопределенное число (до 30) - СРЗНАЧ(А5:В8; D5:E8; F12; 125).

6. Выражения - КОРЕНЬ(A1^2+A2^2).

7. Другие функции - SIN(РАДИАНЫ(В2)).

3. Ввод функций

Функции могут использоваться самостоятельно и в составе выражений. Порядок ввода функций одинаковый.

1. Выделить ячейку, в которую вставляем функцию

2. Вставка ð Функция или <f_x> или <Shift + F3>

3. В поле Категория выбрать нужную категорию.

4. В поле Функция -функцию. ОК

Внизу диалогового окна появиться краткое описание этой функции. Если необходимо узнать подробнее, то вызвать справку (слева внизу).

5. В появившемся диалоговом окне ввести аргументы.

6. После выбора аргументов в нижней части диалогового окна будет виден результат. Если он правильный, то <ОК>.

7. Если аргументом функции должна быть другая функция, то щелкнуть в поле ввода аргумента и выбрать нужную функцию из списка.

Правила ввода функции:

1.     Ввод функции начинается с набора имени функции русскими буквами или ее выбора из списка поля имени, или с помощью меню Вставка/Функция… .

2.     За именем в круглых скобках набираются аргументы функций согласно синтаксису конкретной функции. В некоторых функциях количество аргументов непостоянно. Необязательные аргументы в написании синтаксиса функции указаны в квадратных скобках.

3.     Ввод адресов и ключевых слов, используемых в функциях, можно осуществлять как заглавными, так и строчными английскими буквами с клавиатуры или с помощью мыши (щелчок на ячейке, значение которой используется в формуле).

4.     Иногда формулы могут иметь очень длинный, необозримый вид. В этих случаях удобно разбивать их на отдельные строки внутри ячейки (как текст), используя клавиши. Левый Alt + Enter.

4. Основные функции

Название	Назначение	Действие
1. Математические – 50 штук
1. ЦЕЛОЕ	Определяет целую часть числа	=ЦЕЛОЕ(412,98)             –412
2. СЛЧИС	Определяет случайное число из [0,1)	=ЦЕЛОЕ(6*СЛЧИС()+1) – опр. случайное число от 1 до 6
3. РИМСКОЕ	Преобразует число римское	=РИМСКОЕ(1998) –MCMXCVIII
4. ОКРУГЛ	Округляет значение до указанного количества десятичных разрядов	=ОКРУГЛ(123,456; 2)    – 123,46 =ОКРУГЛ(123,456; 1)    – 123,50 =ОКРУГЛ(123,456; -2)   – 100,00
5. ПИ	Число p – 14 знаков
6.SIN	Sin угла в радианах
7. КОРЕНЬ	Квадратный корень Кубический корень – ^1/3	=КОРЕНЬ (А4+В4)
8.СУММ	Вычисляет сумму – до 30 аргументов кнопка <S>	=СУММ(А1:А7; В1:В7; Е7; С12)

2. Статистические – 80 шт.
1. СРЗНАЧ	Определяет среднее значение		=СРЗНАЧ(А1:А12; С1:С12)
2. МИН	Определяет наименьшее знач.		=МИН(А3:С3; А8:С8)
3. МАКС	Определяет наибольшее знач.		=МАКС(А3:С3; А8:С8)
3. Текстовые – 23 шт.
1. ПРОПНАЧ		В словах первую букву делает прописной, а остальные – строчными.	=ПРОПНАЧ(ИВАНОВ И.И.)                  Иванов И.И.
2. СИМВОЛ		Преобразует ANSI код в символ	=СИМВОЛ(169)                 –©
4. Дата и время – 14 шт.
1. СЕГОДНЯ		Вставляет сегодняшнюю дату	=СЕГОДНЯ()
2. ДЕНЬНЕД		Определяет день недели указанной даты	=ДЕНЬНЕД(Дата; код) =ДЕНЬНЕД (СЕГОДНЯ();1)
3. ДЕНЬ		Выделяет день месяца из указанной даты	=ДЕНЬ(12.09.2006) – 12 =ДЕНЬ(СЕГОДНЯ())
3. МЕСЯЦ		Выделяет месяц	=МЕСЯЦ(12.09.2002) –9
3. ГОД		Выделяет год	=год(12.05.2006) – 2006

Значение второго параметра для функции ДЕНЬНЕД:

Тип	Возвращаемое число
1 или опущен	Число от 1 (воскресенье) до 7 (суббота).
2	Число от 1 (понедельник) до 7 (воскресенье)
3	Число от 0 (понедельник) до 6 (воскресенье)

5.             Ошибки в формулах.

Значение	Описание
########	Получилось слишком длинное число – нужно увеличить ширину столбца или изменить формат ячейки
#ДЕЛ/0	Попытка деления на ноль
#ИМЯ?	В формуле используется несуществующее имя
#ЗНАЧ!	Введено арифметическое выражение, содержащее адрес ячейки с текстом
#ССЫЛКА!	Отсутствуют ячейки, адреса которых используются в формуле
#Н/Д	Нет данных для вычислений. Удобно использовать для резервирования данных под ожидаемые данные. Формула, содержащая адрес ячейки со значением #Н/Д, возвращает результат #Н/Д
#число!	Задан неправильный аргумент функции
#пусто!	В формуле используется пересечение диапазонов, не имеющих общих ячеек

Ход работы

1.   Ознакомиться с правилами по ТБ.

2.   Включите компьютер

3.   Запустите программу Microsoft Excel

Индивидуальные задания.

1. Составить и оформить документ Excel, состоящий из нескольких листов. На каждом листе оформить отдельную задачу.
2. Сохранить таблицу в папке со своей фамилией под именем ПР- функции.

Задание 1.

В MS Excel создать по образцу бланк счета для аренды автомобиля. 

Автомобиль использовался с 17.02.18 07:00 до 19.02.18 18:30

Тарифная ставка = 200 р./ч.

ВНЕШНИЙ ВИД РАБОЧЕГО ЛИСТА

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

1.     Составить таблицу по образцу.

2.     Для ввода даты в ячейку F3 используйте функцию «Сегодня».

3.     При вводе периода аренды автомобиля в ячейки F6:F7используйте формат «Дата», в котором присутствует дата и время.

4.     Для подсчета количества часов аренды автомобиля установите в ячейке «Итого» числовой формат и введите формулу Итого=(Дата по − Дата с)*24

5.     Расчет суммы счета произведите по формуле:

6.     Всего = «Тариф за час» * Итого.

Задание №2.

 

Автотранспортное предприятие выполняет грузовые перевозки для разовых клиентов. Для расчета стоимости перевозки используются различные виды тарифов на перевозки. Вид тарифа зависит от объема заказанной услуги и от пробега автомобиля. Если грузооборот P составляет менее 1300 ткм, то используется тариф за тонну, если более, то тариф за километр. В таблице приведены значения тарифов для различных значений показателей.

Рассчитать стоимость перевозки грузов для следующих клиентов

Показатель	Клиент 1		Клиент 2	Клиент 3	Клиент 4
Объем перевозок (Q), т	200	115		300	120
Грузоподъемность авто­мобиля, т	5	10		8	6
Средняя длина груженой ездки, км	8	14		4	8
Коэффициент использования пробега	0,5	0,65		0,5	0,61
Коэффициент использова­ния грузоподъемности	0,9	0,7		0,8	0,6

Аналитическое решение

Грузооборот и общий пробег автомобилей рассчитываются по формулам:

    (1)

                       (2)

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

Себестоимость перевозок при тарифе за один километр

S=T_кL_общ                                (3)

где Т_к - тариф за километр, руб/км.

Себестоимость перевозок при тарифе за одну тонну

S=T_тQ                   (4)

где Т_т - тариф за тонну, руб/т.

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

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

2.     Составить таблицу по образцу

3.     Введите в ячейку B22 формулу =B15*B17 и скопируйте ее в диапазон С22:Е22.

4.      В ячейку B23 введите формулу =B15*B17/(B16*B18*B19) и скопируйте ее в С23:Е23.

5.     Для определения вида используемого тарифа необходимо проверить условие: если грузооборот меньше 1300, то используется тариф за тонну, если больше, за километр.

6.     Выделите ячейку B26 и запустите Мастер функций. Выберите категорию Логические и функцию ЕСЛИ. На следующем шаге укажите параметры: Условие B22>$D$12, Значение если истина -2 , Значение если ложь -1. В результате в ячейке B26 будет формула =ECЛИ(B22>$D$12;2;1). Скопируйте ее в диапазон С26:Е26

(Результат  расчёта по формуле показывает вид используемого тарифа. Если результат равен 1, то используется тариф за тонну, если 2, то тариф за километр)

7.     Постройте таблицу, в которой рассчитывается стоимость перевозки по каждому тарифу. Для этого, используя Мастер функций за­несите в ячейку B29 формулу =ЕСЛИ (И(В$15>$В6; B$15<=$B7);$C6*B$15;0). Скопируйте формулу в ячейки B29:E31.

8.     В ячейку B32 занесите формулу =ЕСЛИ (И(B$23>$D6; B$23<=$D7);B$23*$E6;0) и скопируйте ее в диапазон B32:E34

9.     Выполните расчет оплаты за перевозку. Используя Ма­стер функций, введите в ячейку B37 формулу
=ЕСЛИ(B26=1; СУММ(B29:B31); СУММ(B32:B34)). Скопируйте ее в диапазон С37:Е37.

10.   Отформатируйте таблицу по образцу