4608
.pdf4. Наведите указатель мыши на правый нижней угол ячейки D12, чтобы он принял вид маленького тонкого крестика. Нажмите левую кнопку мыши и,
удерживая ее, распространите формулу в диапазоне D12:G12. Обратите внимание, как EXCEL преобразовал адреса ячеек в распространенных формулах и выполнил расчеты рисунок 7.
Рисунок 7.
5.Оформите таблицы и сохраните рабочую книгу.
Лабораторная работа 4
Бригада в составе Асс =15 автомобилей-самосвалов МАЗ-503Б
грузоподъемностью q = 7 т осуществляет перевозку щебня на строительство автомобильной дороги. Показатели работы бригады: среднее время погрузочно-
разгрузочных |
операций |
tп/р |
=0,2 |
ч, |
коэффициент |
использования |
||||
грузоподъемности g = 1, коэффициент использования пробега β = 0,5. |
|
|||||||||
Какой объем перевозок Q и грузооборот Р по дням выполняла бригада, если |
||||||||||
остальные показатели составляют следующие величины: |
|
|
|
|||||||
|
|
|
|
|
|
|
|
|
|
|
Показатели |
|
|
|
|
|
Дни |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
2 |
3 |
4 |
|
5 |
6 |
7 |
|
|
|
|
|
|
|
|
|
||
Средняя длина ездки с грузом lег, км |
2 |
3 |
3,5 |
4 |
|
4,2 |
4,7 |
5,2 |
||
|
|
|
|
|
|
|
|
|
|
|
Техническая скорость Vт, км/ч |
|
30 |
27 |
31 |
32 |
|
28 |
28 |
30 |
|
|
|
|
|
|
|
|
|
|
|
|
Время в наряде Тн, ч |
|
11,2 |
12,3 |
13,2 |
12,3 |
|
12,1 |
12,5 |
11,9 |
|
|
|
|
|
|
|
|
|
|
|
|
Аналитическое решение
1. Объем перевозок для бригады автомобиле за рабочий день, т
Q Vт q .
lег Vт tп / р
2. Грузооборот, выполняемый бригадой автомобилей за день, ткм
W Q lег
Решение в EXCEL с использованием абсолютных ссылок
1. Создайте рабочую книгу, занесите в нее таблицу с исходными данными. Вверху таблицы разместите показатели, которые имеют постоянные значения Асс, q, tп/р, а, b, g, а внизу – показатели, которые изменяются по дням lег, Vт, Tн.
2.Для изменяемых показателей занесите все значения по дням.
3.В ячейку занесите D16 формулу
=$D$5*$D$6*E13*$D$8*$D$9*$D$10*E12/(E11+E12*$D$10*$D$7).
Заметьте, что для показателей, которые имеют постоянные значения, -
ссылка абсолютная, а на изменяемые показатели – обычная (рисунок 8,9).
Рисунок 8.
Рисунок 9.
4.Распространите формулу в диапазоне D16 : J16 и обратите внимание, как произошло изменение адресов в формуле.
5.В ячейку D17 занесите формулу =D16*E11 и распространите ее в диапазоне D17 : J17.
6. Отформатируйте таблицы и сохраните рабочую книгу рисунок
10.
Рисунок 10.
Решение в EXCEL с использованием именованных ячеек
1.Выполните 1 и 2 пункты предыдущего решения.
2.Присвойте имена тем ячейкам, которые хранят неизменяемые данные.
D5 – Асс, D6 – q, D7 -tпр , D8 – g, D9 – а, D10 – b.
3.В ячейку D 22 занесите формулу =Асс*q*E13*g*a*b*E12/ (E11+E12*b*tпр) и распространите ее в диапазоне D22: J22.
4.В ячейку D 23 занесите формулу =D22*E11 и распространите ее в диапазоне D23: J23 (рисунок 11).
Рисунок 11.
5. Сравните результаты этого и предыдущего решения.
Задача для самостоятельного решения
Автомобили самосвалы ЗИЛ грузоподъемностью q=8 т в течении недели работали на маятниковом маршруте с обратным холостым пробегом. Нулевой пробег за день l0 = 10 км, длина ездки с грузом lег = 6 км, техническая скорость
Vт=24 км/ч. Каждый день недели на линии работало различное число автомобилей:
Показатели |
|
Дни |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
1 |
|
2 |
3 |
4 |
5 |
6 |
7 |
|
|
|
|
|
|
|
|
|
Число автомобилей, Асс |
8 |
|
12 |
14 |
10 |
6 |
12 |
9 |
|
|
|
|
|
|
|
|
|
Время в наряде Тн, ч |
10 |
|
12,2 |
11,7 |
11,4 |
10,9 |
14,2 |
17 |
|
|
|
|
|
|
|
|
|
Определить объем перевозок по каждому дню недели.
Использование функций
Помимо основных арифметических операций EXCEL позволяет использовать в формулах функции. Функциями в EXCEL называют объединение нескольких вычислительных операций для решения определенной задачи.
EXCEL представляет большое число встроенных функций, позволяющих выполнять как простые расчеты, например суммировать, так и сложные,
например вычислять плотность нормального распределения.
Функция может быть записана в ячейку вручную или вставлена с помощью
Мастера функций. Использование Мастера функций значительно облегчает работу с функциями, так как позволяет выбрать нужную функцию из списка и задать ее аргументы. При ручном наборе функции следует обратить внимание на правильность набора имени. Если имя функции будет набрано неправильно, то возникнет ошибка #ИМЯ.
В качестве аргумента функции обычно выступает одна ячейка или диапазон ячеек.
Использование диапазонов позволяет значительно сократить и упростить запись формул. Для указания диапазонов используется операция объединения,
записываемая знаком «:». Например, если задан диапазон В7:В11, то значит, в
операции будет использованы ячейки В7, В8, В9, В10, В11.
Аналогично можно задавать и прямоугольные диапазоны, определяемые адресами левого верхнего и правого нижнего углов.
EXCEL представляет пользователю более 200 встроенных функций,
используемых для различных расчетов. Каждая функция принимает различное количество аргументов. Аргументы, в свою очередь, могут иметь различный вид.
Поэтому достаточно сложно вручную вводить функции в ячейки без ошибок.
Использование Мастера функций значительно облегчает ввод функции и позволяет быстро найти необходимую функцию и задать ее аргументы.
Для удобства использования в Мастере функций все функции разбиты на категории. Характеризующие принадлежность функции к определенному виду вычислений. Выделено двенадцать категорий по видам расчетов: Финансовые,
Дата время, Математические, Статистические, Ссылки и массивы, Работа с базой данных, Текстовые, Логические, Проверка свойств и значений,
Инженерные, Информационные, Мат. И тригонометрия. Дополнительно имеются еще две категории общего характера: Полный алфавитный перечень и 10
недавно использовавшихся. Внутри категории все функции отсортированы по алфавиту.
Для запуска Мастера функций выделите ячейку, в которую необходимо занести функцию, и нажмите кнопку Мастер функций на панели инструментов.
Работа мастера разбита на два шага. Первым шагом производится выбор категории функции и выбор нужной функции из списка. На втором шаге в специальном диалоговом окне задают аргументы функции.
Каждый аргумент задается в отдельном поле. В нижней части диалогового окна приводится краткое описание аргумента.
Рисунок 12.
В дальнейшем введенную функцию можно редактировать вручную или с помощью мастера функций.
Для изменения порядка вычисления в зависимости от выполнения каких-
либо условий или соотношений между данными рабочего листа используются логические функции.
Эти функции сгруппированы в мастере функций в категорию Логические функции. Ряд дополнительных логических функций содержится в разделах
Математические, Информационные и Проверка свойств и значений.
Функции из категории Логические функции
Функции этой категории позволяют выполнять проверку условий и соответственно изменять порядок вычислений формул.
Функция ЕСЛИ является наиболее часто используемой логической функцией. Она возвращает в результате одно из двух выражений в зависимости от проверки условий. Эта функция имеет следующий синтаксис:
ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь),
где
лог_выражение – логическое значение, принимающее два значения
ИСТИНА и ЛОЖЬ. |
|
|
|
|
значение_если_истина – |
значение, |
которое |
возвращается, |
если |
лог_выражение равно ИСТИНА. |
|
|
|
|
значение_если_ложь – |
значение, |
которое |
возвращается, |
если |
лог_выражение равно ЛОЖЬ. |
|
|
|
|
Функция ЕСЛИ применяется в тех случаях, когда необходимо в зависимости от содержимого ячеек выполнить расчет по различным функциям или использовать различные значения.
Функция И используется для объединения нескольких логических условий.
Если все объединяемые логические условия имеют значение ИСТИНА, то функция возвращает ИСТИНА. Если все условия имеют значение ЛОЖЬ, то
ИЛИ возвращает ЛОЖЬ.
Функции И и ИЛИ используются совместно с функцией ЕСЛИ для проверки сложных условий.
Функции из категории Математические
Логические функции этой категории позволяют выполнять некоторые математические операции не над всеми данными из заданного диапазона, а только над теми, которые удовлетворяют условию.
Функция СУММЕСЛИ выполняет суммирование только тех ячеек,
которые удовлетворяют условию. Синтаксис функции СУММЕСЛИ:
СУММЕСЛИ (проверяемый_диапазон; условие; фактический_диапазон) где
проверяемый_диапазон – ячейки, для которых проверяется условие;
условие – логическое выражение, которому должны удовлетворять ячейки
из диапазона;
фактический_диапазон – ячейки, для которых будет выполняться суммирование, если соответствующая им ячейка из проверяемого диапазона удовлетворяет условию.
Аналогичный синтаксис имеет функция СЧЕТЕСЛИ. Эта функция не суммирует, а подсчитывает количество ячеек, удовлетворяющих условию.
Функции из категории Проверка свойств и значений
В отличие от функции из категории Логические функции, функции этой категории проверяют на соответствие типа, а не значение результата. Наиболее часто используются следующие функции этой категории:
ЕОШИБКА – возвращает ИСТИНА, если заданная ячейка содержит
ошибку;
ЕТЕКСТ – возвращает ИСТИНА, если заданная ячейка содержит текст;
ЕЧИСЛО – возвращает ИСТИНА, если заданная ячейка содержит число.
Функции раздела Информационные
Раздел содержит две функции: ЕНЕЧЕТ и ЕЧЕТ. Эти функции возвращают значение ИСТИНА, если заданное значение является соответственно нечетным или четным числом.
Лабораторная работа 5
Коков общий пробег автомобиля КамАЗ 5511 за пять дней и
среднесуточный пробег автомобиля по зафиксированным в путевом листе
показаниям спидометра?
Пробег |
1 |
2 |
3 |
4 |
5 |
|
|
|
|
|
|
При выезде, км |
14850 |
15050 |
15230 |
15500 |
15690 |
|
|
|
|
|
|
При возврате, км |
15050 |
15230 |
15500 |
15690 |
15880 |
|
|
|
|
|
|
Аналитическое решение Вначале вычисляется ежесуточный пробег автомобиля как разность
показаний спидометра при выезде и возврате.
Затем, просуммировав ежесуточный пробег, можно найти общий пробег, а
разделив общий пробег на дни работы, - среднесуточный пробег.
Решение в EXCEL
1. Создайте новую рабочую книгу, сохраните ее под именем Задача 5,
занесите на рабочий лист таблицу с исходными данными рисунок 13.
Рисунок 13
2.В ячейку В8 занесите формулу =B5-B4, позволяющую рассчитать пробег за сутки, и распространите ее в ячейки В8:F8.
3.Первый способ расчета общего пробега состоит в простом суммировании ячеек диапазона В8:F8. Занесите в В9 формулу =B8+C8+D8+E8+F8.
4.Второй способ заключается в использовании специальной функции
СУММ и диапазона ячеек. Занесите в В10 формулу =СУММ(B8:F8).
5. Среднесуточный пробег можно вычислить по формуле =B9/5. Занесите
эту формулу в ячейку В9.