4608

6. Более правильным для расчета среднесуточного пробега будет

использование специальной функции СРЗНАЧ, возвращающей среднее значение для выбранных ячеек. Занесите в ячейку В12 формулу =СРЗНАЧ(B8:F8).

7. Во многих случаях существует более простой способ выполнения расчетов, так как EXCEL предлагает большое число специализированных функций. Так можно вычислить общий пробег, не делая промежуточных расчетов ежедневных пробегов. Для этого следует использовать специальную формулу массива {СУММ}, которая возвращает сумму диапазонов ячеек и автоматически выполняет промежуточные действия.

Рисунок 14.

2.Выполните расчет числа пассажиров, перевезенных по перегонам, для этого занесите в ячейку Е6 формулу =E5+B6-C6 и распространение ее в диапазоне Е7:Е12.

3.Выполните расчет пассажирооборота по участкам, занесите в ячейку F6

формулу =D6*E6 и распространите ее в диапазон F7:F11.

4. В ячейку В13 зенесите с помощью мастера функций формулу

=СУММ(B6:B11) и распространите ее в ячейку С13. Эти формулы рассчитывают количество перевезенных пассажиров, суммируя число вошедших и вышедших соответственно.

5.Для расчета суммарного пассажирооборота по маршруту занесите с помощью мастера функций в ячейку F12 формулу =СУММ(F6:F11).

6.Расчет средней длины поездки пассажира выполняется по формуле

=F12/B13, которая заносится в ячейку F13 (рисунок 15).

Рисунок 15.

7. Более простым способом расчета средней длины поездки пассажира,

исключающим выполнение промежуточных расчетов, является использование формулы =СУММПРОИЗВ(D6:D11;E6:E11)/CУММ(В6:В11), которая с помощью мастера функций заносится в ячейку F15.

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

Автотранспортное предприятие выполняет грузовые перевозки для разовых клиентов. Для расчета стоимости перевозки используются различные виды тарифов на перевозки.

Вид тарифа зависит от объема заказанной услуги и от пробега автомобиля.

Если грузооборот Р менее 1300 ткм, то используется тариф на тонну, если более,

то тариф за километр. В таблице приведены значения тарифов для различных значений показателей:

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

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

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

P Q lег ;

Q l

Lo q ег ;

S Tk Lo .

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

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

S Tk Lo

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

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

S Tт Q

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

Решение в EXCEL

1. Занесите на рабочий лист исходную таблицу тарифов (рис. 16).

Рисунок 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…. – список значений или ссылки на список значений.

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

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

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

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

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

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

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

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

Интервальный_Просмотр),

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

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

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

возвращено значение;

Интервальный_Просмотр – логическое значение, которое определяет,

нужно ли, чтобы функция искала приблизительное или точное значение ключа.

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

если искомого значения нет в таблице, то возвращается ошибка.

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

таблицы и возвращает значение из той же строки, но для заданного столбца.

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

Интервальный _Просмотр),

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

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

Для функции ГПР и ВПР в случае, если аргумент

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

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

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

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

Тип_Сопоставления).

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

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

Дополнительные функции

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

строки и столбца.

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

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

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

текстовой строкой.

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

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

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

высота; ширина).