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

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

Таблица 7

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

Страна	Количество горящих путевок	Средняя стоимость тура на человека тыс. руб.
Россия		25,2
Абхазия		30,5
Франция		68,9
Италия		45,6
Испания		38,6
Греция		26,3
Индия		25,6
Вьетнам		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.

последняя→
предпоследняя ↓
	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
	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
	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
	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
	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
	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
	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
	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
	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
	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.	Затраты человека чесов и машино-часов на устройство укрепительных полос и укрепление обочин Наименование элемента затрат Ед. измер. Вид работ Затраты труда рабочих-строителей чел.-ч. 92,11 8,24 97,24 7,9 219,52 Средний разряд работы   3,6 3,6 Затраты труда машинистов чел.-ч. 44,15 29,79 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.	Стоимость дорог в России и за рубежом № п\п Проект Протяженность (км) Наличие мостов* Кол-во полос Общая стоимость (млн долл.) Стоимость километра полосы (млн долл.) Route 87, San Jose, США, штат Калифорния, реконструкция 9,72 US 365 / North Spokane Corridor, строительство 44,8 – 1 939 7,21 SR 527 Widening – 164th Street SE to 132nd Street SE, США, Вашингтон, реконструкция и расширение 3,2 2,57 США, Вашингтон, реконструкция и расширение SR 18-180 to MapleValley, 4,3 2,8 Route 228, США, Виргиния, строительство 28,2 3,66 Строительство автодороги в сельской местности на юго-западе Швеции, Road 40 Dallebo–Hester – 159,6 2,35 Строительство дороги в сельской местности на юге Швеции, Tvarleden Osby–Markaryd 20,6 – 45,2 1,1 Строительство участка магистрали «Дон» км 355 – км 414,7 (обход г. Елец) в Липецкой области 56,1 3,61 Строительство дороги Шали–Бавлы, участок Шали–Сорочьи Горы 2,22 Строительство участка магистрали «Дон» от Москвы через Воронеж, Ростов-на-Дону, Краснодар до Новороссийска, км 355 – км 414,7 (обход г. Елец) в Липецкой области 56,1 – 1,97 Строительство автодороги М7 «Волга», обход г. Нижний Новгород (3-я очередь, 1-й пусковой комплекс) 3,5 – 12,3 1,76 Строительство автодороги в обход г. Ядрин 10,3 – 1,99
3.	Затраты на лежневые дороги из круглого леса Диаметр лаг, см Затраты в зависимости от ширины проезжей части дороги, тыс. руб. при ширине дороги 3,5 м добавляются на каждые последующие 0,5 м Число рядов Однорядный Двухрядный Трехрядный Однорядный Двухрядный Трехрядный При цене на круглый лес хвойных пород (франко-станция отправления) 26,8 50,0 75,0 7,2 13,9 20,7 28,3 54,0 79,5 7,3 14,7 21,6 31,8 60,4 89,0 8,1 14,9 23,4 35,8 68,5 101,0 9,0 17,8 26,5 При ценах на круглый лес хвойных пород, отпускаемых на корню 7,2 14,0 20,8 1,0 2,0 2,9 7,4 14,9 22,0 1,1 2,2 3,1 8,3 15,0 23,8 1,2 2,4 3,2 9,1 18,0 27,0 1,3 2,5 3,4
4.	Численность студентов Отделения Численность студентов, чел. В том числе по полу в возрасте, лет мужчины женщины до 20 20-23 23 и более Дневное Вечернее Всего
5.	Затраты на лежневые дороги из сборно-разборных щитов, тыс. руб. Число перекладок щитов Затраты в зависимости от ширины проезжей части дороги, тыс. руб. при ширине дороги 3,5 м добавляются на каждые последующие 0,5 м ширины дороги При ценах на круглый лес (франко-станция назначения) 70,0 10,0 42,0 8,0 32,7 4,5 28,0 4,0 25,2 3,6 23,3 3,4 22,0 3,1 21,0 3,0 20,2 2,9 19,6 2,8 При ценах на круглый лес хвойных пород, отпускаемых на корню 32,4 4,6 23,2 3,7 20,1 2,1 18,6 1,8 17,7 1,7 17,0 1,6 16,7 1,5 16,3 1,4 16,1 1,3 15,9 1,3

Задание № 5

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

№ п/п	Таблица с данными	Операции над данными
1.	Данные о товаре на складе № п/п Наименование Название Кол-во товара на складе, кг Стоимость единицы товара, руб. Стоимость всего товара, руб. Конфеты Бурундук Пряники Белочка Конфеты Ромашка 165,5 2482,5 Леденцы Бон-Пари Леденцы Цитрус 95,6 7456,8 Конфеты Морские 145,6 13977,6 Конфеты Марс Пряники Шоколадные Пряники Ванильные Леденцы Микс ягод Леденцы Барбарис Пряники Молочные 119,5 34535,5 Конфеты Славянка Конфеты Царь 340,2 Конфеты Желейные 201,3 31604,1	1. Вычислить количество и общую стоимость конфет на складе (СЧЁТЕСЛИ, СУММЕСЛИ); 2. Найти количество единиц продукции на складе со стоимостью от 150 до 201 руб. за кг; 3. Построить диаграмму вида:

2.

Статистика спортсменов № п/п Пол спортсмена ФИО Кол-во приседаний за минуту Кол-во отжиманий за минуту Кол-во подтягиваний за минуту Мужской Иванов А.А. Женский Перова Т.Г. Женский Зайцева О.А. Женский Кац Т.О. Мужской Круц А.В. Женский Круц Л.В. Мужской Петров А.И. Мужской Кроликов А.А. Мужской Ван О.О. Мужской Ванькин А.С. Женский Зарова О.А. Женский Закирова Е.А. Мужской Занов С.С. Женский Занова С.А. Мужской Тугаев А.С.

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

3.

Данные о товаре на складе № п/п Наименование Название Кол-во товара на складе, кг Стоимость единицы товара, руб. Стоимость всего товара, руб. Конфеты Бурундук Пряники Белочка Конфеты Ромашка 165,5 2482,5 Леденцы Бон-Пари Леденцы Цитрус 95,6 7456,8 Конфеты Морские 145,6 13977,6 Конфеты Марс Пряники Шоколадные Пряники Ванильные Леденцы Микс ягод Леденцы Барбарис Пряники Молочные 119,5 34535,5 Конфеты Славянка Конфеты Царь 340,2 Конфеты Желейные 201,3 31604,1

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

4.

Статистика детей сотрудников организации № п/п Пол ребенка ФИО Возраст Кол-во кружков, шт Кол-во секций, шт Мужской Иванов А.А. Женский Перова Т.Г. Женский Зайцева О.А. Женский Кац Т.О. Мужской Круц А.В. Женский Круц Л.В. Мужской Петров А.И. Мужской Кроликов А.А. Мужской Ван О.О. Мужской Ванькин А.С. Женский Зарова О.А. Женский Закирова Е.А. Мужской Занов С.С. Женский Занова С.А. Мужской Тугаев А.С.

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

5.

Информация о группе ИТ-15-1б № п/п Пол студента ФИО Возраст Средний бал семестра Количество оценок отлично Мужской Арутунян А.А. 3,714286 Мужской Аттонов С.А. 4,285714 Женский Васина В.А. 2,714286 Мужской Войнов С.С. Мужской Грицюк В.В. Женский Заяц Я.А. 4,142857 Женский Икуткина И.В. 3,285714 Мужской Крапивин А.А. 3,571429 Мужской Луц А.С. Мужской Лыков М.М. 3,857143 Женский Миронова М.А. 4,142857 Мужской Паров В.А. 3,428571 Мужской Парова С.В. 2,428571 Женский Тудвасев С.А. Мужской Тупин А.С.

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

Параметры оформления отчета

Отчет по проделанной работе должен быть оформлен в виде форматированного текста в редакторе MS Word. Отчет должен содержать: 1) титульную страницу с указанием ФИО исполнителя, названия группы и номера зачетной книжки (см. Приложение); 2) содержание; 3) номера и текст выполняемых заданий; 4) подробное описание хода выполнения задания с помощью электронных таблиц MS Excel; 5) электронные таблицы MS Excel с графиками, таблицами и диаграммами, полученными в результате выполнения заданий и 6) список использованной литературы.

Оформление отчета

1. Использование для всего текста отчета шрифт Times New Roman.

2. Размер шрифта для всего текста – 14 (подписи рисунков и таблиц можно сделать 12).

3. Выравнивание всего текста, кроме подписей рисунков и текста таблиц, по ширине.

4. Первая строка (красная строка) для основного текста отчета – 1,25 см.

5. Межстрочный интервал для основного текста – одинарный.

6. Положение на странице: запрет висячих строк и запрет автоматического переноса слов.

7. Подписи к рисункам и таблицам – выравнивание по центру. Обязательное наличие подписей у всех рисунков и таблиц.

8. Наличие нумерации страниц отчета (номер на титульном листе отсутствует).

9. Оформление всех таблиц как таблиц MS Word (таблицы должны быть подобраны по ширине окна).

10. Вставка графиков в отчет из MS Excel как картинок (картинки должны быть по размеру окна).

11. Общая красота и грамматика выполнения отчета.

Отчет сдается в распечатанном виде перед сдачей экзамена или зачета. Без предоставления отчета студент не допускается к сдаче экзамена или зачета.

Список рекомендуемой литературы

Основная литератур