Задания для самостоятельной работы задача №1
По данным, приведённым в таблице вычислить нормативный расход топлива (пробег/100* норма) и определить перерасход топлива (расход - нормативный расход).С помощью операции Данные - Сортировка упорядочить данные в таблице по убыванию перерасхода. Построить гистограмму (Водитель - Перерасход).
Водитель |
Марка авто |
Норма расхода топлива на 100км в литрах |
Пробег за месяц, км |
Расход топлива за месяц, л |
Нормативный расход топлива, л |
Перерасход топлива, л |
Иванов |
Газель |
18 |
3450 |
630 |
|
|
Петров |
Зил |
31 |
2750 |
865 |
|
|
Сидоров |
Маз |
38 |
4120 |
1590 |
|
|
Кузнецов |
Газель |
18 |
3630 |
660 |
|
|
Сергеев |
Ваз |
8 |
2880 |
230 |
|
|
Дмитриев |
Зил |
31 |
3230 |
1050 |
|
|
По данным, приведённым в таблице вычислить расход топлива, % перерасхода топлива ((расход - нормативный расход)/нормативный расход * 100). С помощью операции Данные - Сортировка упорядочить данные в таблице по убыванию % перерасхода. Построить гистограмму (Марка - % Перерасхода)
Марка авто |
Кол-во топлива в баке - начало, л |
Заправлено топлива за период, л |
Кол-во топлива в баке – конец, л |
Расход топлива за период, л |
Нормативный расход топлива, л |
% перерасхода топлива |
Газель |
25 |
600 |
15 |
|
550 |
|
Зил |
45 |
700 |
28 |
|
650 |
|
Маз |
60 |
1450 |
24 |
|
1550 |
|
Газель |
15 |
550 |
22 |
|
560 |
|
Ваз |
24 |
220 |
35 |
|
220 |
|
Зил |
42 |
900 |
31 |
|
850 |
|
1.3. С помощью вложенных друг в друга функций ЕСЛИ( ; ; ) и приёма закрепления адресов ячеек (абсолютных ссылок) создать формулу для расчёта бонуса клиентов в таблице. С её помощью заполнить колонку Бонус, %. Определить средневзвешенную (по объёму продаж) скидку по всем клиентам ( средний бонус, % = (сумма по клиентам(объём*бонус))/(сумма по клиентам(объём))*100 ). Можно воспользоваться функцией СУММПРОИЗВ либо создать дополнительную колонку. Построить круговую диаграмму (Название - Объём продаж за месяц)
Название |
Объём продаж |
Бонус, % |
|
Условие предоставления бонуса |
|
Клиент1 |
150 |
|
|
Объём продаж |
Бонус, % |
Клиент2 |
600 |
|
|
менее 200 ед. |
0 |
Клиент3 |
300 |
|
|
от 200 до 500 ед. |
3 |
Клиент4 |
220 |
|
|
свыше 500 ед |
5 |
Клиент5 |
750 |
|
|
|
|
Клиент6 |
950 |
|
|
|
|
Клиент7 |
130 |
|
|
|
|
1.4. По данным в таблице вычислить сумму продаж для каждого наименования. С помощью операции Данные - Сводная Таблица определить общую сумму продаж по группам товаров. Построить круговую диаграмму (Группа - Сумма продаж по группе)
Наименование |
Группа |
Продано шт. |
Цена за 1 шт |
Продано в рублях |
Порошок1 |
ПОРОШКИ |
120 |
35 |
|
Шампунь1 |
ШАМПУНИ |
150 |
55 |
|
Порошок2 |
ПОРОШКИ |
105 |
45 |
|
Порошок3 |
ПОРОШКИ |
300 |
25 |
|
Шампунь2 |
ШАМПУНИ |
230 |
30 |
|
Порошок4 |
ПОРОШКИ |
170 |
30 |
|
Шампунь3 |
ШАМПУНИ |
220 |
30 |
|
Шампунь4 |
ШАМПУНИ |
115 |
60 |
|
1.5. По данным в таблицах определить процент надбавки, суммы надбавки и премии. Воспользоваться вложенными друг в друга функциями ЕСЛИ( ; ; ). Для определения стажа работы в годах можно воспользоваться формулой ( стаж = (текущая дата - Дата поступления на работу)/365 ). Воспользоваться тем, что в результате операций с датами (например, вычитания) можно получить число (например, число дней между датами) и, наоборот дата плюс число даёт дату. При необходимости сменить формат результирующей ячейки на числовой. Для упрощения формул можно ввести дополнительные колонки. В качестве текущей даты взять 31.12.2007. Использовать приём закрепления адресов - абсолютные ссылки. Построить гистограмму (Фамилия - Премия)
Фамилия |
Дата поступления на работу |
Оклад |
Надбавка, % |
Надбавка, руб. |
Премия = (оклад + надбавка)*20% |
Иванов |
01.04.2004 |
5000 |
|
|
|
Петров |
01.08.2006 |
6000 |
|
|
|
Сидоров |
01.10.2002 |
5000 |
|
|
|
Кузнецов |
01.04.2006 |
6000 |
|
|
|
Сергеев |
01.08.2001 |
5000 |
|
|
|
Стаж |
% надбавки |
|
31.12.2007 |
< 2 лет |
0 |
|
|
от 2 до 4 лет |
10 |
|
|
больше 4 лет |
20 |
|
|
1.6. Вычислить величину бонуса, который предоставляется клиенту при объёме, превышающем 500 000 руб. и скидке менее 10%. Размер бонуса - 3% от объёма продаж. Использовать вложенные друг в друга функции ЕСЛИ( ; ; ), либо функцию ЕСЛИ и функцию И( ; ). С помощью операции Данные - Сводная таблица вычислить общий объём продаж со скидкой 0%, 5% и 10%. Отразить итоги на круговой диаграмме (три сектора).
Название |
Объём продаж за месяц, руб. |
Скидка, % |
Бонус, руб. |
Клиент1 |
150000 |
0 |
|
Клиент2 |
600000 |
5 |
|
Клиент3 |
750000 |
10 |
|
Клиент4 |
950000 |
10 |
|
Клиент5 |
130000 |
0 |
|
Клиент6 |
650000 |
5 |
|
1.7. По данным в таблице вычислить сумму продаж для каждого наименования. С помощью функции СУММЕСЛИ( ; ; ) и приёма закрепления адресов ячеек определить сумму продаж по каждой группе. Построить круговую диаграмму для иллюстрации доли каждой группы в общем объёме продаж (три сектора).
Наименование |
Группа |
Продано шт. |
Цена за 1 шт, руб. |
Продано руб. |
|
Группа |
Сумма продаж, руб. |
Порошок1 |
ПОРОШКИ |
120 |
35 |
|
|
ПОРОШКИ |
|
Шампунь1 |
ШАМПУНИ |
150 |
55 |
|
|
ШАМПУНИ |
|
Порошок2 |
ПОРОШКИ |
105 |
45 |
|
|
ПАСТА |
|
Порошок3 |
ПОРОШКИ |
300 |
25 |
|
|
|
|
Шампунь2 |
ШАМПУНИ |
230 |
30 |
|
|
|
|
Паста1 |
ПАСТА |
170 |
30 |
|
|
|
|
Шампунь3 |
ШАМПУНИ |
220 |
30 |
|
|
|
|
1.8. С помощью функций ТЕНДЕНЦИЯ (предполагает линейное изменение параметра
при изменении аргумента) и РОСТ (предполагает экспоненциальное изменение параметра при изменении аргумента) сделать прогноз объёма продаж на следующий месяц. Абсолютный прирост к январю в месяц№X, % = (объёмМесяц№X - объёмМесяц№1)/объёмМесяц№1*100. Построить точечную диаграмму (месяц - Объём продаж)
Месяц |
Объём продаж, тыс. руб. |
Абсолютный прирост к январю, % |
1 |
1240 |
|
2 |
1370 |
|
3 |
1420 |
|
4 |
1510 |
|
5 |
1440 |
|
6 |
1580 |
|
7 |
|
|
7 |
|
|
1.9. Заполнить колонку Скидка, % при условии, что скидка в 5% предоставляется сельским клиентам по достижении объёма продаж в 200 ед., а городским в 300 ед., то есть значение скидки равны либо 0, либо 5. Воспользоваться функцией если( ; ; ) и вложенной в неё функцией И(). С помощью операции Данные - Сводная таблица вычислить общий объём продаж клиентов, имеющих скидку и не имеющих скидку. Отразить итоги на круговой диаграмме (доля - со скидкой и доля - без скидки).
Наименование |
Район |
Объём продаж за месяц, ед. |
Скидка, % |
Клиент1 |
Сельский |
150 |
|
Клиент2 |
Городской |
600 |
|
Клиент3 |
Сельский |
300 |
|
Клиент4 |
Городской |
220 |
|
Клиент5 |
Сельский |
750 |
|
Клиент6 |
Городской |
950 |
|
Клиент7 |
Городской |
130 |
|
Клиент8 |
Сельский |
650 |
|
1.10. Заполнить колонку Зарплата итого ( = Оклад + Премия). С помощью функций СРЗНАЧ, СУММЕСЛИ и СЧЁТЗ определить среднюю зарплату по всему списку и среднюю зарплату по сотрудникам, получившим премию. Все результаты должны получаться по формулам - при изменении данных результат должен изменяться автоматически и правильно. Построить гистограмму (Фамилия - Зарплата итого).
Фамилия |
Оклад, руб. |
Премия, руб. |
Зарплата итого, руб. |
Иванов |
7 000 |
1400 |
|
Петров |
6 000 |
|
|
Сидоров |
5 000 |
|
|
Кузнецов |
6 500 |
1300 |
|
Сергеев |
5 000 |
1000 |
|
Дмитриев |
6 000 |
|
|
1.11. По данным таблицы определить дату полного износа и % износа на текущую дату. В качестве текущей даты взять 31.12.2007. Дата полного износа = Дата ввода + 365*100/норма амортизации в год, %. % текущего износа = (текущая дата - дата ввода)/(дата полного износа - дата ввода)*100. Воспользоваться тем, что в результате операций с датами (например, вычитания) можно получить число (например, число дней между датами) и, наоборот дата плюс число даёт дату. При необходимости сменить формат результирующей ячейки. Упорядочите данные по убыванию % текущего износа с помощью операции Данные-Сортировка. Постройте гистограмму, на которой отразите % износа каждого наименования.
31.12.07
Наименование |
Норма амортизации в год, % |
Дата ввода в эксплуатацию |
Дата полного износа |
% текущего износа |
Компьютер |
50 |
20.09.2006 |
|
|
Автомобиль ВАЗ |
10 |
21.03.2000 |
|
|
Грузовик |
25 |
25.09.2004 |
|
|
Офисный стол |
33 |
12.08.2005 |
|
|
1.12. С помощью операции Данные-Фильтр-Автофильтр вычислить сумму объёма продаж клиентов по трём группам: с объёмом продаж менее 200, от 200 до 500 и свыше 500. Построить круговую диаграмму, отражающую распределение общего объёма продаж между клиентами этих трёх групп с помощью вспомогательной таблицы. Значения во вспомогательную таблицу удобнее копировать из итогов фильтрации с помощью операций Копировать и Правка - Специальная вставка - Значения.
Наименование |
Объём продаж за месяц, ден. ед. |
|
Группа |
Объём продаж, ден. ед. |
Клиент1 |
150 |
|
менее 200 ден. ед. |
|
Клиент2 |
300 |
|
от 200 до 500 ден. ед. |
|
Клиент3 |
600 |
|
более 500 ден. ед. |
|
Клиент4 |
220 |
|
|
|
Клиент5 |
750 |
|
|
|
Клиент6 |
950 |
|
|
|
Клиент7 |
130 |
|
|
|
Клиент8 |
650 |
|
|
|
1.13. Заполнить колонку Продано в рублях. С помощью операции Фильтр-Автофильтр определить общую сумму продаж шампуней с ценой от 30 до 50 руб. за 1 шт. С помощью операции Данные - Сводная таблица найти общую сумму продаж по группам. Построить круговую диаграмму (Группа - Продано в рублях по группе).
Наименование |
Группа |
Продано шт. |
Цена за 1 шт, руб. |
Продано в руб. |
Порошок1 |
ПОРОШКИ |
120 |
35 |
|
Шампунь1 |
ШАМПУНИ |
150 |
55 |
|
Порошок2 |
ПОРОШКИ |
105 |
45 |
|
Порошок3 |
ПОРОШКИ |
300 |
25 |
|
Порошок4 |
ПОРОШКИ |
170 |
30 |
|
Шампунь2 |
ШАМПУНИ |
220 |
40 |
|
Шампунь3 |
ШАМПУНИ |
115 |
60 |
|
1.14. Определить износ основного средства за месяц и полный накопленный износ с начала эксплуатации до 31.12.2007. Для расчётов удобно ввести колонку Срок эксплуатации в месяцах. Для вычислений использовать функции Год(), Месяц(). Срок эксплуатации, мес. = 12*(годРасчёта - годВвода) + МесяцРасчёта - МесяцВвода. При необходимости сменить формат результирующей ячейки. Упорядочите данные по убыванию % остаточной стоимости с помощью операции Данные-Сортировка. Постройте гистограмму, на которой отразите %-ты износа и остаточной стоимости каждого наименования.
31.12.07
Наименование |
Стоимость, руб. |
Норма амортизации в год, % |
Дата ввода в эксплуатацию |
Износ за месяц, руб. |
Накопленный износ, руб. |
% износа |
% остаточной стоимости |
Компьютер |
30000 |
50 |
20.09.2006 |
|
|
|
|
Автом. ВАЗ |
120000 |
10 |
21.03.2000 |
|
|
|
|
Грузовик |
450000 |
25 |
25.09.2004 |
|
|
|
|
Стол |
5000 |
33 |
12.08.2005 |
|
|
|
|
1.15. Сделать прогноз величины прибыли при объёме продаж 900 тыс.руб., используя функцию ТЕНДЕНЦИЯ. Заполнить колонку Рентабельность продаж, % (=прибыль/объём продаж*100). Построить диаграмму (Точечная) зависимости рентабельности от объёма.
Объём продаж, тыс.руб. |
Прибыль, тыс. руб. |
Рентабельность продаж, % |
400 |
85 |
|
450 |
90 |
|
500 |
95 |
|
550 |
100 |
|
600 |
110 |
|
650 |
120 |
|
700 |
125 |
|
750 |
140 |
|
900 |
|
|
1.16. По данным таблицы заполнить колонку Премия, руб. Премия начисляется в размере 20% от оклада при условии выполнения плана, который установлен для каждого сотрудника индивидуально. Воспользоваться функцией ЕСЛИ( ; ; ). Построить гистограмму (Фамилия - Премия)
Фамилия |
Оклад, руб. |
План |
Факт |
Премия, руб. |
Иванов |
7 000 |
700 |
750 |
|
Петров |
6 000 |
600 |
630 |
|
Сидоров |
5 000 |
500 |
480 |
|
Кузнецов |
6 500 |
650 |
600 |
|
Сергеев |
5 000 |
500 |
600 |
|
Дмитриев |
6 000 |
600 |
600 |
|
1.17. Определить процент надбавки, суммы надбавки и премии. Надбавка производится при наличии стажа более 2 лет за каждый отработанный год в размере 5% (то есть 3 года - 5%, 4 года 10% и т.д.). Воспользоваться функцией ЕСЛИ( ; ; ). Для определения стажа работы в годах можно воспользоваться формулой стаж в годах = (текущая дата - Дата поступления на работу)/365. При необходимости изменить формат ячейки на числовой. Округлить стаж до целых лет с помощью функции ЦЕЛОЕ(). ля упрощения формул можно ввести дополнительные колонки В качестве текущей даты взять 31.12.2007. Построить гистограмму (Фамилия - Премия).
31.12.07
Фамилия |
Дата поступления на работу |
Оклад |
Надбавка, % |
Надбавка, руб. |
Премия = (оклад+надбавка)*20% |
Иванов |
01.04.2003 |
5 000 |
|
|
|
Петров |
01.08.2005 |
6 000 |
|
|
|
Сидоров |
01.10.2001 |
5 000 |
|
|
|
Кузнецов |
01.04.2005 |
6 000 |
|
|
|
Сергеев |
01.08.2000 |
5 000 |
|
|
|
Дмитриев |
01.10.2003 |
6 000 |
|
|
|
1.18. Определить сумму штрафа за просрочку платежа, исходя из 0,1% в день за каждый день просрочки. Воспользоваться тем, что сумма даты и числа даёт дату, отстоящую на данное число дней от исходной (если задать формат даты в результирующей ячейке). Наоборот, разность дат даёт число дней между датами (при задании числового формата в результирующей ячейке). Для автоматической проверки наличия просрочки воспользоваться функцией ЕСЛИ. Упорядочите данные по убыванию суммы штрафа с помощью операции Данные-Сортировка. Построить гистограмму (Наименование - Штраф).
Наименование |
Сумма кредита, руб |
Дата, кредит взят |
Срок кредита, дней |
Фактическая дата возврата |
Просрочка, дней |
Штраф, руб. |
Клиент1 |
100 000 |
01.01.2003 |
90 |
15.04.2003 |
|
|
Клиент2 |
150 000 |
01.02.2003 |
180 |
20.04.2003 |
|
|
Клиент3 |
120 000 |
01.03.2003 |
360 |
20.02.2004 |
|
|
Клиент4 |
200 000 |
01.04.2003 |
90 |
30.07.2003 |
|
|
Клиент5 |
250 000 |
01.05.2003 |
180 |
22.10.2003 |
|
|
1.19. Определить дату полного износа и % износа на текущую дату. Колонку Норма амортизации в год заполнить с помощью функции ПРОСМОТР(). В качестве текущей даты взять 31.12.2007. Дата полного износа = Дата ввода + 365*100/норма аморт в год, %, % текущего износа = (текущая дата - Дата ввода)/(Дата полного износа - Дата ввода*100). Воспользоваться тем, что в результате операций с датами (например, вычитания) можно получить число (например, число дней между датами) и, наоборот дата плюс число даёт дату. При необходимости сменить формат ячейки (на числовой или дату). Постройте гистограмму, на которой отразите % износа каждого наименования.
Наименование |
Код группы |
Норма амортизации в год, % |
Дата ввода в эксплуатацию |
Дата полного износа |
% текущего износа |
Компьютер |
1 |
|
20.09.2006 |
|
|
Автом. ВАЗ |
3 |
|
21.03.2003 |
|
|
Грузовик |
3 |
|
25.09.2004 |
|
|
Офисный стол |
2 |
|
12.08.2005 |
|
|
|
|
|
|
|
|
Код группы |
Норма амортизации в год, % |
31.12.2007
|
|
|
|
1 |
50 |
|
|
|
|
2 |
40 |
|
|
|
|
3 |
20 |
|
|
|
|
1.20. Определить сумму продаж каждого наименования, сумму продаж каждой группы товаров и долю продаж каждого наименования относительно общей суммы продаж наименований своей группы. Общую сумму продаж по группам определить с помощью функции СУММЕСЛИ. При определении доли продаж наименования в своей группе применить функцию ЕСЛИ. Отобразить на круговой диаграмме распределение продаж по группам (два сектора).
Наименование |
Группа |
Продано шт. |
Цена за 1 шт |
Продано в рублях |
Доля продаж наименования относительно продаж группы товаров, % |
Порошок1 |
ПОРОШКИ |
120 |
35 |
|
|
Шампунь1 |
ШАМПУНИ |
150 |
55 |
|
|
Порошок2 |
ПОРОШКИ |
105 |
45 |
|
|
Порошок3 |
ПОРОШКИ |
300 |
25 |
|
|
Шампунь2 |
ШАМПУНИ |
330 |
30 |
|
|
Порошок4 |
ПОРОШКИ |
170 |
30 |
|
|
Шампунь3 |
ШАМПУНИ |
220 |
40 |
|
|
Шампунь4 |
ШАМПУНИ |
115 |
60 |
|
|
|
|
|
|
|
|
Группа |
Сумма продаж |
|
|
|
|
ПОРОШКИ |
|
|
|
|
|
ШАМПУНИ |
|
|
|
|
|
1.21. Определить дату полного износа, принимая в расчётах, что все месяцы имеют 30 дней. В ходе вычислений можно учитывать, что при использовании в формуле данных одновременно в формате даты и в формате числа, результат можно получить в формате даты. Для этого нужно установить в ячейке, содержащей результат, формат даты. Остаточная стоимость, руб. = (1 - % износа/100)*Стоимость, руб. Износ за месяц, руб. = (Стоимость, руб. - Остаточная стоимость, руб.)/(% износа/норма амортизации в год*12). Дата полного износа = текущая дата + Остаточная стоимость, руб/Износ за месяц, руб.*30. Для задания текущей даты воспользоваться функцией СЕГОДНЯ(). Упорядочите данные по убыванию % остаточной стоимости с помощью операции Данные - Сортировка. Постройте гистограмму, на которой отразите Износ за месяц, руб каждого наименования.
Наименование |
Стоимость, руб |
Норма амортизации в год, % |
% износа на текущий момент |
Остаточная стоимость на текущий момент, руб |
Износ за месяц, руб |
Дата полного износа |
Компьютер |
30000 |
50 |
25 |
|
|
|
Автом. ВАЗ |
120000 |
10 |
33,3 |
|
|
|
Грузовик |
450000 |
25 |
50 |
|
|
|
Офисный стол |
5000 |
20 |
40 |
|
|
|
1.22. В таблице приведены данные о продажах автомобильного магазина за месяц. Определить фактическую среднюю цену продажи каждой марки автомобиля (фактическая сума/продано шт.) и определить средний процент скидки ( 100*(предлагаемая цена - факт цена)/предлагаемая цена). С помощью операции Данные - Сортировка упорядочить данные по убыванию суммы продаж и построить круговую диаграмму, отражающую долю суммы продаж каждой марки в общей сумме продаж. Введите текущее значение даты перед таблицей с помощью функции Сегодня().
Марка автомобиля |
Продано шт. |
Предлагаемая цена за 1 шт |
Фактическая сумма продаж |
Фактическая средняя цена |
Средняя скидка, % |
ВАЗ |
25 |
110000 |
2640000 |
|
|
ГАЗ |
3 |
210000 |
592200 |
|
|
Тойота |
6 |
350000 |
1995000 |
|
|
Хонда |
2 |
450000 |
900000 |
|
|
1.23. Определить сумму налога на добавленную стоимость (НДС) по каждому наименованию ( = (сумма продаж * ставка/100) / (1 + ставка / 100)). С помощью операции Данные - Сводная таблица определить итоговую сумму НДС по ставкам 18% и 10%. Введите текущее значение даты перед таблицей с помощью функции Сегодня(). Постройте круговую диаграмму, отражающую общие суммы НДС с разными ставками (два сектора).
Наименование товара |
Сумма продаж, руб. |
Ставка НДС, % |
Сумма НДС, руб. |
Шубы |
295000 |
18 |
|
Памперсы |
2200 |
10 |
|
Пальто мужское |
73750 |
18 |
|
Костюм мужской |
59000 |
18 |
|
Игрушки детские |
55000 |
10 |
|
1.24. С помощью функции ЕСЛИ( ; ; ) и приёма закрепления адресов ячеек заполнить колонку оклад с использованием данных из вспомогательной таблицы. С помощью операции Данные - Сводная таблица определить итоговую сумму окладов по 10-у и 11-у разрядам. Введите текущее значение даты перед таблицей с помощью функции Сегодня(). Постройте круговую диаграмму, отражающую итоговые суммы по разрядам (два сектора).
Фамилия |
Разряд |
Оклад |
|
Разряд |
Оклад |
Иванов |
10 |
|
|
10 |
5000 |
Петров |
11 |
|
|
11 |
6000 |
Сидоров |
10 |
|
|
|
|
Кузнецов |
11 |
|
|
|
|
Сергеев |
10 |
|
|
|
|
Дмитриев |
10 |
|
|
|
|
1.25. По данным таблицы заполнить колонку % студентов с каждым баллом с помощью
приёма закрепления адресов ячеек. % студентов с баллом = Кол-во студентов с баллом/Общее кол-во студентов *100. Определите средневзвешенный балл = сумма по баллам(Количество студентов с данным баллом * Балл)/(общее кол-во студентов). Можно воспользоваться функцией СУММПРОИЗВ или построить дополнительный столбец. Постройте круговую диаграмму, отражающую % студентов по баллам (четыре сектора).
Балл |
Кол-во студентов с данным баллом |
% студентов с данным баллом |
5 |
5 |
|
4 |
17 |
|
3 |
20 |
|
2 |
8 |
|