Полученные расчеты представить наглядно в виде диаграмм

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

Цель:Цель занятия – познакомиться со встроенными функциями электронной таблицы EXCEL и получить навык применения их при решении стандартных задач профессиональной деятельности.

Задание

Составить таблицу для расчета суммы по перевозке сыпучих грузов (щебня, песка) самосвалами в зависимости от расстояния и веса. Тариф на перевозку: до 1000 км – 7,5 руб./куб.м., от 1000 км.до 2000 км. – 6,5 руб./куб.м., от 2000 км. до 3000 км. – 6 руб./куб.м., свыше 3000 км – 5 руб./куб.м.

На отдельном листе (дайте листу название, например АНАЛИЗ) введите формулы для определения следующих величин:

1. Найти максимальное и минимальное значение начислений по перевозке грузов.

2. Найти средний объем и расстояние перевозимых грузов.

3. Найти количество поездок на ближние расстояния (до 1000 км)

4. Подсчитать сумму начислений по перевозимым грузам на дальние расстояния

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

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

Пример выполнения задания

Оформить на рабочем листе Excel (в диапазоне A1:G18) таблицу с исходными данными.

В ячейках диапазона I2:J6 справочные данные для вычисления тарифа на перевозку сыпучих грузов.

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

В появившемся окне в первой строке ввода вводим условие E3<1000

Во второй строке ввода с меткой Значение_если_истина_ введите формулу для расчета тарифа D3*7,5 (объем умноженный на тариф).

В третьей строке ввода с меткой Значение_если_ложь необходимо ввести еще одну функциюЕсли, т.к. необходимо продолжить проверку вариантов.

Нажмите на стрелку в поле имени и в появившемся списке выберите функцию Если.

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

Введите следующее условие в первую строку ввода E3<2000

Во второй строке ввода с меткой Значение_если_истина_ введите формулу для расчета тарифа D3*6,5 (объем умноженный на тариф).

Нажмите на стрелку в поле имени и в появившемся списке выберите функцию Если. Появляется пустая форма функции Если, которая вложена во вторую (см. строку формул).

Введите в первую строку ввода условие E3<3000

Во второй строке ввода с меткой Значение_если_истина_ введите формулу для расчета тарифа D3*6 (объем умноженный на тариф).

В третьей строке ввода с меткой Значение_если_ложь необходимо ввести формулу для расчета тарифа D3*5 (объем умноженный на тариф).

Нажмите кнопку ОК.

Формула примет вид

=ЕСЛИ(E3<1000;D3*7,5;ЕСЛИ(E3<2000;D3*6,5;ЕСЛИ(E3<3000;D3*6;D3*5)))

При помощи автозаполнения заполняем введенной формулой ячейки диапазона диапазон F4:F17.

В ячейку G3 введите формулу, для расчета начислений =F3*E3 (тариф умноженный на расстояние). При помощи автозаполнения заполняем введенной формулой ячейки диапазона диапазон G4:G17. При помощи команды Формат ячеек отформатируйте диапазон G3: G17 как Числовой; установите флаг Разделитель групп разрядов.

В ячейках D18 и G18 при помощи функции автосуммированияподстилайте итоговые значения.

Создайте новый лист и переименуйте его ( например, АНАЛИЗ).

В ячейку А1 введите Наименование показателя

В ячейку В1 введите Значение

В ячейку А2 введите Максимальное начисление

В ячейку А3 введите Минимальное начисление

В ячейку А4 введите Среднее начисление

В ячейку А5 введите Средний объем перевезенных грузов

В ячейку А6 введите Количество грузов, перевезенных на ближние расстояния (до 1000 км)

В ячейку А7 введите Начислено по перевозимым грузам на дальние расстояния (более 3000)

Для выполнения этого задания необходимо использовать следующие функции: МАКС, МИН, СРЗНАЧ, СЧЁТЕСЛИ, СУММЕСЛИ.

Функции МАКС, МИН, СРЗНАЧ можно ввести, выбрав их из списка, появляющегося при нажатии на стрелке возле кнопки Автосумма (вкладка Главная) или выбрав их из категории Статистические.

Для поиска максимального значения в ячейку В2 введите функцию МАКС(Максимум). Перейдите на лист, содержащий таблицу с расчетами и выделите диапазон G3: G17.Нажмите клавишу Enter.

Для поиска Минимального значения в ячейку В3 введите функцию МИН (Минимум). Перейдите на лист, содержащий таблицу с расчетами и выделите диапазон G3: G17.Нажмите клавишу Enter.

Для поиска Среднего значения начислений в ячейку В4 введите функцию СРЗНАЧ (Среднее). Перейдите на лист, содержащий таблицу с расчетами и выделите диапазон G3: G17.Нажмите клавишу Enter.

Для поиска среднего объема перевезенных грузов в ячейку В5 введите функцию СРЗНАЧ (Среднее). Перейдите на лист, содержащий таблицу с расчетами и выделите диапазон D3:D17.Нажмите клавишу Enter.

Для подсчета количество грузов, перевезенных на ближние расстояния (до 1000 км) необходимо воспользоваться функцией СЧЕТЕСЛИ(категория Статистические), которая позволяет подсчитать количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию.

Для задания значения в строке Диапазон выделите листе содержащий таблицу с расчетами диапазон E3:E17.

В строке критерий задайте условие <1000

Нажмите кнопку ОК

Для подсчета суммы начислений по перевозимым грузам на дальние расстояния (более 3000) необходимо воспользоваться функцией СУММЕСЛИ (категория Математические), которая позволяет подсчитать сумму значений ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию.

Диапазон - это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем пример это диапазон, содержащий расстояние Пример!E3:E17

Критерий- это аргумент, который может быть обозначен в виде числа, выражения, текста и др., который определяет, какие ячейки необходимо суммировать. В нашем примере критерием выступает логическое выражение >3000

Для того, чтобы вычислить по каждому транспортному средству общую сумму начислений и общий вес перевезенного груза необходимо воспользоваться функцией СУММЕСЛИ.

Введите в диапазон ячеек D1:F5 рабочего листа Анализ макет таблицы.

В ячейку Е2 введите функцию СУММЕСЛИ

Диапазон - это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем примере это названия транспортных средств- Пример!C3:C17, чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, (клавиша F4) поставив знак $. Получаем Пример!$C$3:$C$17

Критерий- это аргумент, который определяет, какие ячейки необходимо суммировать. В нашем примере критерием выступает транспортное средство, название которого содержится в ячейке D2 рабочего листа Анализ.

Диапазон_суммирования -это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон диапазонрасстояний Пример!E3:E17. Чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, (клавиша F4) поставив знак $. Пример!$E$3:$E$17

В ячейку F2 введите функцию СУММЕСЛИ

Диапазон_суммирования -это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон диапазонначислений Пример!G3:G17. Чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, (клавиша F4) поставив знак $. Пример!$G$3:$G$17

Выделите диапазон ячеек Е2:F2 и при помощи автозаполнения заполняем введенными формулами ячейки диапазона Е3:F5

Для проверки правильности вычислений добавьте строку Итого. И вычислите Итоговые значения. Обратите внимание, у вас должны совпасть значения данными в исходной таблице!

Представим графически полученные данные.

График начислений по клиентам

Доходность транспорта

Варианты заданий

Полученные расчеты представить наглядно в виде диаграмм

(не мене 2 диаграмм)

Вариант 1

На складе фирмы хранятся скоропортящиеся продукты. Известны для каждого из них объем, цена единицы продукта и срок хранения к текущему моменту. По истечении срока хранения производится уценка продукта или его списание. Если продукт хранится к настоящему моменту не более 10 суток, то уценка не производится; от 10 до 15 суток – уценка на 15% от первоначальной цены; от 15 до 20 суток – на 30%; от 20 до 30 суток – на 50 %; свыше 30 суток – продукт списывается. Определить, на какую сумму к началу месяца хранится каждого вида продукции.

1. Максимальное и минимальное значение сумм уцененных продуктов.

2. Количество «списанных товаров».

3. Сколько товаров не было уценено?

4. Количество товаров, хранение которых больше заданной величины.

5. На какую сумму хранится товаров, по которым уценка составила 50%?

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

Вариант 2

При продаже путевок сотрудникам предприятия фирмы в зависимости от стажа работы предусмотрены следующие скидки: при стаже от 5 до 7 лет скидка 3% от номинальной стоимости путевки; при стаже от 7 до 10 лет скидка 5%; при стаже от 10 до 15 лет скидка 10%; при стаже больше 15 лет скидка 20% от номинальной стоимости путевки. Рассчитать стоимость путевки с учетом скидки в зависимости от стажа работы сотрудника. Номинальная стоимость путевки (это данное поместите в отдельную ячейку) и стаж работы сотрудников известны.

Стоимость путевки =Номинальная стоимость-Номинальная стоимость*Процент скидки.

1. Максимальное и минимальное значение отличия льготной суммы путевки от ее номинальной величины.

2. Средняя номинальная и льготная стоимость путевки.

3. Количество путевок, по которым не было скидки.

4. Общая сумма путевок, по которым предоставлена скидка 20%

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

Вариант 3

Для пяти арендаторов известны: название организации-арендатора, площадь арендуемого помещения (в м²), минимальная оплата за аренду (это данное поместите в отдельную ячейку). Составить таблицу расчета оплаты за аренду помещений в зависимости от площади с учетом поправочного коэффициента, используя формулу: Минимальная плата за аренду*Площадь+Минимальная плата за аренду*Коэффициент. Если арендуемая площадь меньше 100 м², то коэффициент равен 0,5, если арендуемая площадь больше, чем 100 м², но не превышает 200 м² , то коэффициент равен 0,7, если площадь более 200 м², коэффициент – 0,8.

1. Максимальная и минимальная арендуемая площадь.

2. Средний размер арендной платы по всем договорам.

3. Количество договоров аренды, арендуемая площадь которых превышает 150 м².

4. Средний размер арендной платы по договорам, в которых арендуемая площадь больше 200 м².

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

Вариант 4

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

На отдельном листе ЭТ (дайте листу название, например АНАЛИЗ) введите формулы для определения следующих величин:

1) Минимальная сумма налога.

2) Максимальная мощность двигателя.

3) Количество владельцев автомобилей с мощностью двигателя больше 200 л.с.

4) Сколько владельцев, имеющих транспортные средства с двигателем мощностью менее 150 л.с. заданной величины, заплатили налог?

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

Вариант 5

Продуктовый склад отпускает муку для предприятий по оптовым ценам в зависимости от объема закупок. Для муки используются следующие цены: более 10000 кг – по 12 руб. за 1кг, от 5000 до 10000 кг – по 13 руб. за 1кг, от 1000 до 5000 кг – по 14 руб. за 1кг, менее 1000 кг – по 15 руб. за 1 кг. Рассчитайте стоимость закупленных продуктов и общую стоимость для каждого предприятия

1. Максимальная и минимальная сумма реализации.

2. Средний размер реализации.

3. Количество продаж, сумма реализации которых меньше 50 000 руб.

4. Общую сумму реализации муки, для объема закупок более 10000 кг.

5. Вычислить объем и сумму реализации за каждый месяц и оформить в виде таблицы в соответствии с образцом.

Вариант 6

С помощью электронной определить сумму стоимость проката автомобиля. Если дляза каждый день проката начисления производятся в соответствии со следующими условиями: 1-2 дня - 2150 руб, 3-6 дней - 2000 руб, 7-14 дней 1850 руб., 15-31 дней – 1700 руб.

1. Найти максимальное и минимальное значение дней проката.

2. Найти среднее значение начисленной арендной платы.

3. Найти количество заказов, срок аренды которых 1 неделя

4. Подсчитать сумму начислений для долговременных заказов (больше чем 3 недели)

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

Вариант 7

Для сотрудников двух отделов: фамилии, начисленная заработная плата и количество отработанных дней, пришедшиеся на выходные и праздники. Величина начисляемой премии сотрудникам учреждения зависит от количества отработанных дней в выходные и праздники: если 1 или 2 дня, то надбавка к основному заработку составляет 5%, если 3-5 дней – 7%, больше 5 дней – 10%. Необходимо рассчитать величину премии и сумму заработной платы с учетом премии в зависимости от указанных параметров.

1. Максимальная и минимальная начисленная премия.

2. Средний размер премии.

3. Количество премиальных, размер которых превышает 1500 руб.

4. Сумму премиальных, для сотрудников, отработавших более 5 дней

5. Вычислить сумму премиальных по каждому отделу и оформить в виде таблицы в соответствии с образцом.

Вариант 8

Составить таблицу для расчета скидки в оплате товара, если стоимость покупки более 1000 руб, то 10%, если боле 7000 руб, то 5%, иначе скидки нет.

1. Найти максимальное и минимальное значение размера скидки.

2. Найти среднее значение суммы реализации продукции.

3. Найти количество реализаций, по которым скидка составила 0%

4. Общую скидки на реализацию, если объем продаж составил более 100 кг

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

Вариант 9

Определить надбавку за стаж работы. Значение в столбце Надбавка вычислить в соответствии с условием: если общий стаж работы меньше 5 лет, то надбавка 10%, если от 5 до 10 лет – 15%, если больше 10 лет – 20%.

1. Максимальная и минимальная начисленная надбавка.

2. Средний размер набавки.

3. Количество набавок, размер которых превышает 2500 руб.

4. Общую сумму надбавки, для сотрудников, стаж которых более 15 лет

5. Вычислить сумму премиальных по каждому отделу и оформить в виде таблицы в соответствии с образцом.

Вариант 10

Вычислить общую стоимость ягод проданных, за указанные даты. Определить цену реализации скоропортящихся ягод (клубники и малины). Если срок хранения ягод не превышает 4 дней, то реализация составляет 100% закупочной цены, если от 4 до 8 дней – то 80% цены, если больше 8 – то 50%.

1. Максимальное и минимальное значение сумм реализации.

2. Среднюю сумму реализации.

3. Общий реализованных вес фруктов, по которым уценка составила 50%.

4. На какую сумму был реализован товар за первые 2 суток от поступления?

5. Вычислить вес и сумму реализации сумму реализации каждого товара и оформить в виде таблицы в соответствии с образцом

Вариант 11

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

Столбец Премия, % заполнить на основе правил расчета. Премия рассчитывается по следующему условию:если оклад меньше20000 руб., то премия составляет 5% от оклада;от 20000 до 25000 руб. – 8% от оклада, от 25000 до 30000 руб. – 10% от оклада, если больше 30000 руб. – 6% от оклада.

1. Максимальный и минимальный размер начисленной заработной платы с учетом премии

2. Средний размер премии.

3. Количество сотрудников, которым была начислена премия более 2000 руб.

4. Сумму премиальных, для сотрудников, изготовивших более 50 деталей

5. Вычислить сумму премиальных по каждому цеху и оформить в виде таблицы в соответствии с образцом.

Вариант 12

Составить таблицу для начисления штрафов нерадивым водителям. Составьте формулу расчета значений в столбце Количество дней просрочки платежа. Если платеж был осуществлен в срок, отведенный для оплаты, то просрочка = 0. Столбец Пени заполните расчетными формулами на основе правил расчета из диапазона A1:B5, составив единую формулу для всех строк таблицы. В формуле обязательно использовать ячейки B2:B5 – при изменении значений в этом диапазоне результаты вычислений в таблице должны автоматически изменяться.

1. Максимальное и минимальное значение сумм пени.

2. Среднее значение дней просроченных платежей.

3. Количество штрафов, по которым не просроченных платежей

4. На какую сумму было выписано пени за первые 10 суток просроченных платежей?

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

Вариант 13

Составить таблицу для расчета стоимости проката спортинвентаря с учетом начисленной пени. Пеня рассчитывается в соответствии со следующим условием: если количество просроченных дней меньше 3 – 0,1% за каждый день, от 3 до 5 дней – 0,3%, свыше 5 дней – 0,05%.

1. Найти максимальное и минимальное значение дней просрочки по кредиту

2. Найти среднее значение начисленной пени.

3. Найти количество непросроченных кредитов

4. Подсчитать сумму пени для кредитов, просроченных на 1 неделю

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

Вариант 14

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

Исходными данными для расчета являются: месяц, фамилия пассажира, вес багажа. К справочным данным относится Порядок применения багажных тарифов.

1) Минимальный, максимальный и средний вес багажа.

2) Средняя стоимость провоза багажа.

3) Количество пассажиров, которым с бесплатным провозом багажа.

4) Общая стоимость провоза багажа, вес которого превышает 30 кг.

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

Вариант 15

1. Найти максимальное и минимальное значение дней проката.

2. Найти среднее значение начисленной пени.

3. Найти количество непросроченных заказов

4. Подсчитать сумму пени для заказов, просроченных на 1 день

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

Вариант 16

Составить таблицу для расчета суммы детских пособий. Вычислить возраст детей. Для этого воспользоваться встроенной функцией ГОД. Значения в столбце Сумма пособия вычислить в соответствии с условием: если возраст меньше 3 лет, то 100% отМРОТ, если возраст старше 3 лет, но не превышает 8 лет – 80%, от 8 до 17 лет – 60%, старше 17 лет – пособия нет

1. Вычислить средний возраст детей

2. Вычислить средний размер пособия

3. Определить количество детей младше 3 лет

4. Определить сумму пособий для детей старше 8 лет

5. Вычислить сумму пособий по месяцам и оформить в виде таблицы в соответствии с образцом

Вариант 17

Составить таблицу для расчета суммы начисления премиальных работникам цеха производственного предприятия. Тарифная ставка определяется в зависимости от разряда: 1-й разряд – 4000 руб.; 2-й разряд – 6500 руб.; 3-й разряд – 8000 руб. Тарифные ставки оформить отдельной таблицей.Размер премиальных определяется в зависимости от выполнения плана:

- ниже 100% – премия не начисляется;

- 100% – премия 20% от тарифной ставки;

- 101…110% – премия 30%;

- свыше 110% – премия 40%

1. Максимальный и минимальный размер начисленной заработной платы с учетом премии

2. Средний размер премии.

3. Количество сотрудников, которым не была начислена премия

4. Сумму премиальных, для сотрудников, выполнивших план на 100%

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

Вариант 18

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

- ниже 40 – неудовлетворительно;

- от 41 до 61 – удовлетворительно;

- от 62 до 81 – хорошо;

- свыше 82 – отлично.

1. Максимальный и минимальный итоговых баллов

2. Средний итоговый балл.

3. Количество обучающихся, которым выставлена неудовлетворительная оценка.

4. Средний балл, для студентов, которым выставили отличную оценку.

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

Дата добавления: 2018-06-01; просмотров: 3083; Мы поможем в написании вашей работы!

Поделиться с друзьями:

Мы поможем в написании ваших работ!