Построение смешанной диаграммы, в которой сочетаются гистограмма и точечная диаграммы в MS Excel с использованием вспомогательной вертикальной оси

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

Таблица 7

Данные о горящих турах ООО «Тревел-Пермь»

Страна	Количество горящих путевок	Средняя стоимость тура на человека тыс. руб.
Россия	40	25,2
Абхазия	25	30,5
Франция	69	68,9
Италия	47	45,6
Испания	16	38,6
Греция	36	26,3
Индия	25	25,6
Вьетнам	96	49,3

 

Рис. 20. Смешанная диаграмма о горящих турах ООО «Тревел-Пермь»

1. Создадим таблицу с данными о горящих турах ООО «Тревел-Пермь» в MS Excel.

2. На листе документа выбрать пустую ячейку и выполнить следующие действия Вставить → Диаграммы→Гистограмма→Гистограмма с группировкой находясь в пустой Области диаграммы вызываем диалоговое окно для добавления рядов данных Работа с диаграммами → Выбрать данные → Добавить в всплывающем диалоговом окне добавляем ряды данных «Средняя стоимость тура на человека тыс. руб.» и «Количество горящих путевок». При этом диалоговое окно добавления рядов данных отличается от Точечной диаграммы рис. 21. Не отформатированный вид диаграммы показан на рис. 22.

 

Название ряда

Данные ряда гистограммы

Рис. 21. Не отформатированный вид диаграммы с данными о сотрудниках

Рис. 22. Не отвформатированный вид диаграммы о горяших турах

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

3. Выполним форматирование простой точечной диаграммы.

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

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

- Вызвать диалоговое окно для работы с данными Работа с диаграммами → Выбрать данные → Подписи горизонтальной оси→Изменить→Выбрать данные в столбце «Страна».

- Добавим основные вертикальные линии сетки Работа с диаграммами → Макет → Сетка → Вертикальные линии сетки по основной оси→Основные линии сетки.

- Добавим названия осей на область диаграммы Работа с диаграммами → Макет → Названия осей → Название основной вертикальной оси→Повернутое название в появившейся рамке записать «Кол-во горящих туров» при помощи строки формул, Работа с диаграммами → Макет → Названия осей → Название вспомогательной вертикальной оси→Повернутое название в появившейся рамке записать «Средняя стоимость тура на чел., тыс. руб.».

- Выбираем ряд данных «Количество горящих путевок», нажатием правой кнопки мыши вызвать всплывающее диалоговое окно, из предложенных функций выбрать Формат ряда данных → Заливка→Сплошная заливка →Цвет: Серый. Выбираем ряд данных «Средняя стоимость тура на человека тыс. руб.», нажатием правой кнопки мыши вызвать всплывающее диалоговое окно, из предложенных функций выбрать Формат ряда данных → Цвет линии→Сплошная линия →Цвет: Черный не закрывая диалоговое окно выбираем Заливка маркера→Сплошная заливка →Цвет: Черный, Цвет линии маркера→Сплошная линия →Цвет: Черный.

- Изменим легенду диаграммы Работа с диаграммами → Макет → Легенда→Добавить легенду с низу.

После форматирования диаграмма примет вид рис. 20. Построение смешанных диаграмм реализуется в задании 5 контрольной работы.

Выбор варианта задания самостоятельной работы

Сочетание последней и предпоследней цифры номера зачетной книжки определяет комбинацию заданий. Находим пересечение последней и предпоследней цифры номера зачетной книжки – пять цифр ячейки соответствуют вариантам пяти заданий соответственно. Например № 1458, дает нам вариант заданий с следующими 5-ю цифрами: 13, 11, 5, 2, 1. Первая цифра относятся к заданию №1 – 13 – это означает, что нам необходимо взять 13 вариант задания №1. Вторая цифра относиться к заданию № 2 – 11. Третья цифра означает вариант задания №3 контрольной работы – 5, четвертая вариант задания №4 – 2 и соответственно последняя цифра означает вариант задания №5 – 1. Все задания выполняются в табличном процессоре MS Excel, результаты выполнения заданий оформляются в виде отчета в MS Word.

последняя→	0	1	2	3	4	5	6	7	8	9
предпоследняя ↓
0	10, 10, 1, 1, 5	1, 1, 5, 2, 1	9, 9, 5, 3, 3	10, 15, 4, 4, 1	1, 13, 3, 5, 1	28, 17, 2, 1, 5	28, 11, 1, 2, 1	1, 23, 3, 3, 2	11, 18, 4, 4, 1	30, 1, 5, 5, 1
1	21, 21, 5, 5, 1	18, 8, 2, 4, 4	3, 23, 4, 3, 5	12, 24, 5, 2, 3	13, 3, 1, 1, 2	3, 6,5, 1, 1	12, 2, 2, 4, 2	5, 5, 3, 5, 5	12, 4, 4, 2, 2	14, 16, 2, 3, 4
2	26, 26, 4, 3, 2	2, 2, 5, 5, 1	6, 26, 3, 4, 1	24, 19, 3, 2, 4	5, 8, 4, 1, 5	17, 14, 1, 5, 4	11, 20, 2, 4, 5	25, 8, 3, 2, 3	22, 21, 4, 3, 3	15, 19, 5, 1, 4
3	19, 19, 3, 1, 5	24, 2, 2, 1, 2	5, 12, 2, 5, 2	15, 22, 4, 5, 5	4, 20, 2, 4, 5	6, 10, 1, 4, 3	2, 29, 2, 2, 5	22, 6, 1, 2, 1	27, 4, 1, 3, 4	1, 30, 4, 3, 3
4	8, 8, 2, 5, 3	12, 12, 3, 4, 1	8, 21, 5, 3, 4	19, 25, 4, 2, 2	8, 7, 5, 1, 2	10, 25, 1, 5, 2	20, 5, 3, 4, 4	14, 27, 3, 3, 3	16, 13, 2, 2,2	16, 5, 3, 1, 5
5	12, 12, 1, 3, 1	25, 5, 5, 3, 1	7, 7, 2, 4, 5	22, 2, 3, 4, 2	20, 15, 5, 5, 3	3, 18, 1, 5, 5	30, 2,2, 1, 3	9, 29, 4, 1, 4	13, 11, 5, 2, 1	15, 25, 1, 2, 5
6	14, 14, 3, 1, 5	30, 3, 1, 2, 1	11, 11, 5, 3, 1	25, 16, 4, 4, 2	7, 29, 1, 5, 4	14, 13, 4, 1, 3	22, 6, 2, 2, 3	30, 6, 3, 3, 5	18, 9, 3, 4, 3	13, 22, 2, 5, 1
7	9, 9, 5, 5, 4	4, 4, 2, 4, 5	27, 27, 3, 3, 3	2, 7, 2, 2, 1	15, 23, 5, 1, 5	25, 22, 5, 1, 5	24, 14, 1, 2, 2	24, 4, 3, 3, 5	4, 15, 1, 4,1	26, 1, 5, 5,1
8	1, 1, 2, 3, 1	15, 15, 2, 5, 2	24, 24, 1, 4, 5	16, 28, 3, 2, 3	29, 3, 4, 1, 4	30, 27, 1, 4, 4	25, 11, 2, 3, 1	11, 28, 3, 5, 2	21, 12, 4, 1, 5	20, 20, 1, 2, 2
9	17, 17, 1, 4, 5	3, 3, 3, 5, 3	16, 16, 4, 2, 1	7, 10, 1, 3, 5	8, 28, 3, 1, 4	29, 17, 5, 5, 1	30, 26, 2, 4, 1	30, 3, 4, 3, 3	29, 5, 5, 2, 1	16, 30, 5, 1,1

 

 

Задание № 1

Рассчитать по формулам величину значений функций  и , построить на двух отдельных диаграммах зависимость  от ,  от . Тип диаграммы – точечная. Выполнить форматирование диаграммы.

1. , ,  с шагом 0,5;

2. , ,  с шагом 0,5;

3. ; ,  с шагом 0,5;

4. , ,  с шагом 0,5;

5. ; ,  с шагом 0,5;

6. ; ,  с шагом 0,5;

7. ; ,  с шагом 0,5;

8. ; ,  с шагом 0,5;

9. ; ,  с шагом 0,5;

10. ; ,  с шагом 0,5;

11. ; ,  с шагом 0,5;

12. ; ,  с шагом 0,5;

13. ; ,  с шагом 0,5;

14.  ; ,  с шагом 0,5;

15. ; ,  с шагом 0,5;

16. ; ,  с шагом 0,5;

17. ; ,  с шагом 0,5;

18. ; ,  с шагом 0,5;

19. ; ,  с шагом 0,5;

20. ; ,  с шагом 0,5;

21. ; ,  с шагом 0,5;

22. ; ,  с шагом 0,5;

23. ; ,  с шагом 0,5;

24. ; ,  с шагом 0,5;

25. ; ,  с шагом 0,5;

26. ; ,  с шагом 0,5;

27. ; ,  с шагом 0,5;

28. ; ,  с шагом 0,5;

29. ; ,  с шагом 0,5;

30. ; ,  с шагом 0,5.

Задание № 2

Рассчитать по формулам величину значений функций  от переменной , используя функцию MS Excel ЕСЛИ. Построить диаграмм зависимость  от . Тип диаграммы – точечная. Выполнить форматирование диаграммы.

1. ,  с шагом 0,5;

2. ,  с шагом 0,5;

3. ,  с шагом 0,5;

4. ,  с шагом 0,5;

5. ,  с шагом 0,5;

6. ,  с шагом 0,5;

7. ,  с шагом 0,5;

8. ,  с шагом 0,5;

9. ,  с шагом 0,5;

10. ,  с шагом 0,5;

11. ,  с шагом 0,5;

12. ,  с шагом 0,5;

13. ,  с шагом 0,5;

14. ,  с шагом 0,5;

15. ,  с шагом 0,5;

16. ,  с шагом 0,5;

17. ,  с шагом 0,5;

18. ,  с шагом 0,5;

19. ,  с шагом 0,5;

20. ,  с шагом 0,5;

21. ,  с шагом 0,5;

22. ,  с шагом 0,5;

23. ,  с шагом 0,5;

24. ,  с шагом 0,5;

25. ,  с шагом 0,5;

26. ,  с шагом 0,5;

27. ,  с шагом 0,5;

28. ,  с шагом 0,5;

29. ,  с шагом 0,5;

30. ,  с шагом 0,5.

Задание № 3

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

1.	Построить трехлепестковую розу: , ,  принадлежит диапазону от 0 до 3,2 шагом 0,1.
2.	Построить Верзьеру: . Принять t от -5 до 5 шагом 0,25.
3.	Построить Лемнискату Бернулли: .  возьмите из диапазона от -3 до 0 с шагом 0,1.
4.	Построить Улитку Паскаля: .  от -2 до 4,3 с шагом 0,1.
5.	Построить Астроиду: . Примите t от -3 до 3 с шагом 0,1.

 

Задание № 4

При помощи средств MS Excel создать таблицы с данными согласно вашему варианту. Необходимо построить диаграмму по приведенным данным. Вид диаграммы должен полностью совпасть с видом диаграммы на соответствующем варианту рисунке.

 

№ п/п	Таблица с данными	Вид диаграммы
1.	Затраты человека чесов и машино-часов на устройство укрепительных полос и укрепление обочин Наименование элемента затрат Ед. измер. Вид работ 1 2 3 4 5 Затраты труда рабочих-строителей чел.-ч. 92,11 8,24 97,24 7,9 219,52 Средний разряд работы   3,6 3,6 4 4 2 Затраты труда машинистов чел.-ч. 44,15 0 29,79 0 100,9 Машины и механизмы             Катки дорожные самоходные гладкие 8 т маш.-ч 16,76 - 5,81 - 30,67 Катки дорожные самоходные гладкие 13 т маш.-ч 26,68 - 23,48 - 56,66 Котлы битумные передвижные 400 л маш.-ч 0,79 0,01 0,73 - - Гудронаторы ручные маш.-ч 2,52 0,04 2,32 - - Автопогрузчики 5 т маш.-ч - - - - 10,61 Автомобили бортовые грузоподъемностью до 5 т маш.-ч 0,4 - 0,28 - - Машины поливомоечные 6000 л маш.-ч - - - - 2,96
2.	Стоимость дорог в России и за рубежом № п\п Проект Протяженность (км) Наличие мостов* Кол-во полос Общая стоимость (млн долл.) Стоимость километра полосы (млн долл.) 1 Route 87, San Jose, США, штат Калифорния, реконструкция 5 3 6 291 9,72 2 US 365 / North Spokane Corridor, строительство 44,8 – 6 1 939 7,21 3 SR 527 Widening – 164th Street SE to 132nd Street SE, США, Вашингтон, реконструкция и расширение 3,2 1 4 33 2,57 4 США, Вашингтон, реконструкция и расширение SR 18-180 to MapleValley, 4,3 2 4 49 2,8 5 Route 228, США, Виргиния, строительство 28,2 11 4 412 3,66 6 Строительство автодороги в сельской местности на юго-западе Швеции, Road 40 Dallebo–Hester 17 – 4 159,6 2,35 7 Строительство дороги в сельской местности на юге Швеции, Tvarleden Osby–Markaryd 20,6 – 2 45,2 1,1 8 Строительство участка магистрали «Дон» км 355 – км 414,7 (обход г. Елец) в Липецкой области 56,1 24 4 811 3,61 9 Строительство дороги Шали–Бавлы, участок Шали–Сорочьи Горы 54 1 4 479 2,22 10 Строительство участка магистрали «Дон» от Москвы через Воронеж, Ростов-на-Дону, Краснодар до Новороссийска, км 355 – км 414,7 (обход г. Елец) в Липецкой области 56,1 – 4 442 1,97 11 Строительство автодороги М7 «Волга», обход г. Нижний Новгород (3-я очередь, 1-й пусковой комплекс) 3,5 – 2 12,3 1,76 12 Строительство автодороги в обход г. Ядрин 10,3 – 2 41 1,99
3.	Затраты на лежневые дороги из круглого леса Диаметр лаг, см Затраты в зависимости от ширины проезжей части дороги, тыс. руб. при ширине дороги 3,5 м добавляются на каждые последующие 0,5 м Число рядов Однорядный Двухрядный Трехрядный Однорядный Двухрядный Трехрядный При цене на круглый лес хвойных пород (франко-станция отправления) 15 26,8 50,0 75,0 7,2 13,9 20,7 16 28,3 54,0 79,5 7,3 14,7 21,6 18 31,8 60,4 89,0 8,1 14,9 23,4 20 35,8 68,5 101,0 9,0 17,8 26,5 При ценах на круглый лес хвойных пород, отпускаемых на корню 15 7,2 14,0 20,8 1,0 2,0 2,9 16 7,4 14,9 22,0 1,1 2,2 3,1 18 8,3 15,0 23,8 1,2 2,4 3,2 20 9,1 18,0 27,0 1,3 2,5 3,4
4.	Численность студентов Отделения Численность студентов, чел. В том числе по полу в возрасте, лет мужчины женщины до 20 20-23 23 и более 1 2 3 4 5 6 7 Дневное 1200 400 800 860 120 220 Вечернее 800 300 500 320 180 300 Всего 2000 700 1300 1180 300 520
5.	Затраты на лежневые дороги из сборно-разборных щитов, тыс. руб. Число перекладок щитов Затраты в зависимости от ширины проезжей части дороги, тыс. руб. при ширине дороги 3,5 м добавляются на каждые последующие 0,5 м ширины дороги При ценах на круглый лес (франко-станция назначения) 1 70,0 10,0 2 42,0 8,0 3 32,7 4,5 4 28,0 4,0 5 25,2 3,6 6 23,3 3,4 7 22,0 3,1 8 21,0 3,0 9 20,2 2,9 10 19,6 2,8 При ценах на круглый лес хвойных пород, отпускаемых на корню 1 32,4 4,6 2 23,2 3,7 3 20,1 2,1 4 18,6 1,8 5 17,7 1,7 6 17,0 1,6 7 16,7 1,5 8 16,3 1,4 9 16,1 1,3 10 15,9 1,3

 

Задание № 5

При помощи средств MS Excel создать таблицы с данными согласно вашему варианту. Выполнить операции с данными в соответствии с вариантом, построить диаграмму по приведенным данным. Вид смешанной диаграммы должен полностью совпасть с видом диаграммы на соответствующем варианту рисунке.

 

№ п/п	Таблица с данными	Операции над данными
1.	Данные о товаре на складе № п/п Наименование Название Кол-во товара на складе, кг Стоимость единицы товара, руб. Стоимость всего товара, руб. 1 Конфеты Бурундук 120 400 48000 2 Пряники Белочка 20 200 4000 3 Конфеты Ромашка 15 165,5 2482,5 4 Леденцы Бон-Пари 142 125 17750 5 Леденцы Цитрус 78 95,6 7456,8 6 Конфеты Морские 96 145,6 13977,6 7 Конфеты Марс 36 460 16560 8 Пряники Шоколадные 56 120 6720 9 Пряники Ванильные 48 120 5760 10 Леденцы Микс ягод 203 154 31262 11 Леденцы Барбарис 256 145 37120 12 Пряники Молочные 289 119,5 34535,5 13 Конфеты Славянка 145 365 52925 14 Конфеты Царь 145 340,2 49329 15 Конфеты Желейные 157 201,3 31604,1	1. Вычислить количество и общую стоимость конфет на складе (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти количество единиц продукции на складе со стоимостью от 150 до 201 руб. за кг; 3. Построить диаграмму вида:

 

2.

Статистика спортсменов № п/п Пол спортсмена ФИО Кол-во приседаний за минуту Кол-во отжиманий за минуту Кол-во подтягиваний за минуту 1 Мужской Иванов А.А. 20 25 10 2 Женский Перова Т.Г. 15 20 2 3 Женский Зайцева О.А. 10 14 3 4 Женский Кац Т.О. 4 15 5 5 Мужской Круц А.В. 5 6 4 6 Женский Круц Л.В. 12 2 12 7 Мужской Петров А.И. 18 10 9 8 Мужской Кроликов А.А. 25 14 8 9 Мужской Ван О.О. 26 12 7 10 Мужской Ванькин А.С. 14 15 2 11 Женский Зарова О.А. 16 22 6 12 Женский Закирова Е.А. 10 25 4 13 Мужской Занов С.С. 9 26 3 14 Женский Занова С.А. 15 14 0 15 Мужской Тугаев А.С. 16 9 5

1. Вычислить количество и общее количество приседаний спортсменов мужского пола на складе (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти сумму отжиманий спортсменов, уложившихся в нормативы (количество отжиманий от 10 до 19); 3. Построить диаграмму вида:

 

3.

Данные о товаре на складе № п/п Наименование Название Кол-во товара на складе, кг Стоимость единицы товара, руб. Стоимость всего товара, руб. 1 Конфеты Бурундук 120 400 48000 2 Пряники Белочка 20 200 4000 3 Конфеты Ромашка 15 165,5 2482,5 4 Леденцы Бон-Пари 142 125 17750 5 Леденцы Цитрус 78 95,6 7456,8 6 Конфеты Морские 96 145,6 13977,6 7 Конфеты Марс 36 460 16560 8 Пряники Шоколадные 56 120 6720 9 Пряники Ванильные 48 120 5760 10 Леденцы Микс ягод 203 154 31262 11 Леденцы Барбарис 256 145 37120 12 Пряники Молочные 289 119,5 34535,5 13 Конфеты Славянка 145 365 52925 14 Конфеты Царь 145 340,2 49329 15 Конфеты Желейные 157 201,3 31604,1

1. Вычислить количество и общую стоимость леденцов на складе (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти количество единиц продукции количество килограмм на складе, которых от 30 до 150 кг; 3. Построить диаграмму вида:

 

4.

Статистика детей сотрудников организации № п/п Пол ребенка ФИО Возраст Кол-во кружков, шт Кол-во секций, шт 1 Мужской Иванов А.А. 9 3 2 2 Женский Перова Т.Г. 5 5 2 3 Женский Зайцева О.А. 10 4 3 4 Женский Кац Т.О. 4 3 5 5 Мужской Круц А.В. 5 2 4 6 Женский Круц Л.В. 12 1 12 7 Мужской Петров А.И. 13 4 9 8 Мужской Кроликов А.А. 15 7 8 9 Мужской Ван О.О. 9 8 7 10 Мужской Ванькин А.С. 10 5 2 11 Женский Зарова О.А. 10 6 6 12 Женский Закирова Е.А. 10 2 4 13 Мужской Занов С.С. 9 3 3 14 Женский Занова С.А. 5 4 0 15 Мужской Тугаев А.С. 5 9 5

1. Вычислить количество и общее количество кружков у детей женского пола (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти количество секций у детей в возрасте от 9 до 15 лет; 3. Построить диаграмму вида:

 

5.

Информация о группе ИТ-15-1б № п/п Пол студента ФИО Возраст Средний бал семестра Количество оценок отлично 1 Мужской Арутунян А.А. 18 3,714286 2 2 Мужской Аттонов С.А. 19 4,285714 4 3 Женский Васина В.А. 22 2,714286 0 4 Мужской Войнов С.С. 18 4 2 5 Мужской Грицюк В.В. 19 5 7 6 Женский Заяц Я.А. 20 4,142857 1 7 Женский Икуткина И.В. 21 3,285714 0 8 Мужской Крапивин А.А. 18 3,571429 1 9 Мужской Луц А.С. 18 5 7 10 Мужской Лыков М.М. 18 3,857143 2 11 Женский Миронова М.А. 18 4,142857 4 12 Мужской Паров В.А. 19 3,428571 2 13 Мужской Парова С.В. 20 2,428571 0 14 Женский Тудвасев С.А. 20 5 7 15 Мужской Тупин А.С. 18 3 0

1. Вычислить количество студентов старше 18 лет и их общий средний балл (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти общее количество пятерок у студентов со средним баллом больше 3,5 и меньше 4,5; 3. Построить диаграмму вида: