4605

Рисунок 16.

2. Показатели по клиентам занесите в том же виде, в коком они представлены в условиях задачи (рис. 17).

Рисунок 17.

3.Используя исходные формулы, выполните расчет грузооборота и общего пробега по каждому клиенту. Занесите в ячейку В22 формулу =B15*B17 и распространите ее на в В22:С22. В ячейку В23 занесите формулу

=B15*B17/(B16*B19*B19) и распространите ее в С23:Е23.

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

5.Выделите ячейку В26 и запустите Мастер функций (кнопкой на панели инструментов). Выберите категорию Логические и функцию ЕСЛИ. На следующем шаге укажите параметры: Условие В22>$D$12, Значение если истина

–2, Значение если ложь – 1. В результате в ячейке В26 будет формула =ЕСЛИ(B22>$D$12;2;1). Распространите ее в диапазон С26. Результат расчета по формуле показывает вид используемого тарифа. Если результат равен 1, то используется тариф за тонн, если 2, то тариф за километр (рисунок 18).

Рисунок 18.

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

=ЕСЛИ(И(B$23>$D6;B$23<=$D7);B$23*$E6;0) и распространите ее в диапазон

В32:Е32 (рисунок 19).

Рисунок 19.

7. В заключении выполните расчет платы за перевозку. Используя Мастер функций, занесите в ячейку В37 формулу

=ЕСЛИ(B26=1;СУММ(B29:B31);СУММ(B32:B34)). Распространите ее в

С37:Е37. По пулученной формуле вычсляетя общая сумма оплаты за перевозки в зависимости от вида тарифа. Результаты расчетов помещаются в диапазон

В37:Е37.

Ссылки и автоподстановки

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

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

EXCEL предлагает целый класс специальных функций для обработки таблиц и списков и выбора из них нужных значении. Эти функции объединены в разделе Функции ссылки и автоподстановки.

Среди них выделяются следующие группы функций.

Функции для выбора значений по индексу Функция ВЫБОР возвращает значение по его номеру в списке

ВЫБОР(Номер _ индекса; Значение 1, Значение 2….),

где Номер _ индекса – номер выбираемого из списка значения; Значение 1, Значение 2…. – список значений или ссылки на список

значений.

Функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанной строки и столбца в заданном диапазоне.

ИНДЕКС (Ссылка_На_Диапазон; Номер_Строки; Номер_Столбца)

где Ссылка_На_Диапазон – адрес диапазона ячеек, в котором производится выбор значений.

Номер_Строки – число, указывающее номер строки внутри диапазона; Номер_Столбца – число, указывающее номер столбца внутри диапазона.

Функции для выбора значений по ключу

Функция ГПР ищет значение ключа в верхней строке таблицы и возвращает значение из того же столбца, но для указанной строки.

ГПР (Искомое_значение ; Ссылка_На_Диапазон; Номер_Строки; Интервальный_Просмотр),

где Искомое_значение - ключевое значение, по которому выполняется поиск;

Ссылка_На_Диапазон – адрес диапазона ячеек, содержащего исходную таблицу, в которой выполняется поиск;

Номер_Строки – номер строки внутри диапазона поиска, из которой будет возвращено значение;

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

Функция ВПР ищет значение ключа в крайнем левом столбце указанной таблицы и возвращает значение из той же строки, но для заданного столбца.

ВПР (Искомое_значение; Ссылка_На_Диапазон; Номер_Столбца; Интервальный _Просмотр),

где Номер_Столбца – номер строки внутри диапазона поиска, из которой будет возвращено значение.

Остальные аргументы функции ВПР совподают со значением аргументов функции ГПР.

Для функции ГПР и ВПР в случае, если аргумент Интервальный_Просмотр равен ИСТИНА, то значения в диапазоне поиска следует упорядочивать по возростанию.

Функция ПОИСКПОЗ возвращает относительное положение элемента таблицы, который соответствует указанному значению в указанном порядке.

Функция ПОИСКПОЗ используется вместо функции ГПР или ВПР, когда требуется найти позицию элемента в диапазоне, а не сам элемент.

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

Тип_Сопоставления соответствует аргументу Интервальный_Просмотр,

но дополнительно позволяет указать способы сортировки. Дополнительные функции

Функция АДРЕС возвращает адрес ячейки в виде текста, используя номер строки и столбца.

АДРЕС (Номер_Строки, Номер_Столбца, Тип_Ссылки),

где Тип_Ссылки – аргумент, позволюящий указать в каком виде будет возвращена ссылка (абсолютная или относительная).

Функция ДВССЫЛ возвращает значение из ячейки по ссылке заданной текстовой строкой.

ДВССЫЛ (строка_Ссылка_На_Ячейку).

Функция СМЕШ возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов. Возвращаемая ссылка может быть отдельной ячейкой или диапазоном ячеек.

СМЕШ (ссылка; смещение_по_строкам; смещение_по_столбцам; высота; ширина).

Лабораторная работа 8

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

Решение в Excel

1.Занесите в таблицу с исходными данными на рабочий лист.

Рисунок 20

2. Используя функции из раздела Функции ссылки и автоподстановки, занесите следующие формулы в ячейки для расчетов:

Рисунок 21.

Формулы массива

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

Формула массива создается также, как и обычная формула, но для завершения ввода такой формулы следует использовать комбинацию клавиш

Ctrl+Shift+Enter.

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

Рисунок 22

Например, по данным, приведенным на рисунке 22, необходимо вычислить общую грузоподъемность парка.

Без использования формул массива было бы необходимо сначала вычаслить общую грузоподъемность по каждой марке, затем сложить их. В таком случае понадобилось бы записать четыре формулы. Формула массива требуется всего одна. Запишите в в ячейку С5 формулу =СУММ (В2:В4*С2:С4) и нажмите Ctrl+Shift+Enter. В результате выполнения формулы число автомобилей и грузоподъемность по каждой марке будут перемножены, а полученные результаты сложены.

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

Лабораторная работа 9

Автобус совершил три оборота по городскому тангенциальному маршруту. Известны следующие показатели по каждому обороту:

На конечных остановках автобус простаивал одинаковое время. Определить показатели: длину маршрута Lм, число промежуточных остановок nпо, время простоя на конечных остановках tко.

Аналитическое решение Время затрачиваемое автобусом на один оборот

to 2LM 2nno 2tko

VT 3600 60

Для решения задачи необходимо составить систему из трех уравнений:

где VТ1, VТ2, VТ3 – техническая скорость для каждого оборота;

tno1, tno2, tno3, - время простоя на промежуточных остановках для каждого оборота.

Решение системы дает искомые показатели. Решение в Excel

Для решения системы уравнений используется метод Крамера, он основан на матричной записи системы уравнений. Решение находится перемножением обратной матрицы коэффициентов на вектор свободных членов. Для преобразований используются функции рабочего листа Excel: МОБР – возвращает обратную матрицу, МУМНОЖ – вычисляет произведение двух матриц.

1.Занесите исходные данные на рабочий лист.

Рисунок 22 2. Подготовьте матрицу коэффициентов, для этого занесите в ячейки

таблицы формулы:

Рисунок 23

4.Вычислите обратную матрицу, для этого занесите в ячейку А17 формулу =МОБР(А12:С14). Выделите диапазон А17:С19, нажмите клавишу F2 и Ctrl+Shift+Enter. Этим будет выполнено преобразование исходной формулы в формулу массива.

5.Выполните перемножение матриц и получите решение уравнения. Занесите в ячейку F 17 формулу = МУМНОЖ(А17:С19;Е12:Е14), выделите диапазон F17:F19, нажмите клавишу F2 и Ctrl+Shift+Enter.

В ячейках F17:F19 будут рассчитаны искомые показатели: длина маршрута, число промежуточных остановок, время простоя на конечных остановках.

6. Для проверки полученного решения занесите в ячейку В23 формулу

=2*$F$17/C5+2*$F$18*C6/3600+2*$F$19 и распространите ее в ячейки

С27:D27.

7. Сравните исходное время оборота (ячейки D23-D24) и рассчитанное

(А24-С24).

Рисунок 24

8.Решение можно также получить, если воспользоваться функцией ЛИНЕЙН, которая используется в статистических расчетах для построения уравнений множественной линейной регрессии. Эта функция может быть использована для решения систем линейных уравнений. Занесите в ячейку В12

формулу =ЛИНЕЙН(Е12:Е14;А12:С14;0).

Выделите ячейки D13-D14? И нажмите F2 и Ctrl+Shift+Enter.

9.Сравните полученное решение с предыдущим.

Библиографический список Основная литература:

1. Карпов А. С. Автомобильные перевозки [Текст] : учеб. пособие / А. С. Карпов, В. И. Ключников; ВГЛТА. - Воронеж, 2009. - 336 с.

Дополнительная литература:

2.Гаджинский А. М. Логистика [Текст] : рек. М-вом образования Рос. Федерации в качестве учеб. / А. М. Гаджинский. - 20-е. изд. - М. : Дашков и К,

2012. - 484 с.

3.Автомобильный транспорт [Текст] : ежемес. науч.-произв. журн. / М-во автомоб. транспорта РСФСР, ЦК профсоюза рабочих автомоб. транспорта и шоссейных дорог. - М. : Транспорт, 1923 -.