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

Практическая работа №1

Тема. Основы работы с электронной таблицей Excel.

Цель. Приобрести практические навыки по созданию и оформлению ЭТ, вводу данных, использованию функции Автосумма.

Задание. Создать таблицу, показанную на рисунке.

	A	B	C	D
1	Среднегодовая численность работающих
2	Категории должностей	Механический цех	Сборочный цех	Всего
3	Рабочие	295	308	603
4	Ученики	15	12	27
5	ИТР	14	15	29
6	Служащие	12	14	26
7	МОП	5	4	9
8	Пожарно-сторожевая охрана	4	6	10
9	ИТОГО:

Алгоритм выполнения задания.

1. В ячейку А1 записать Среднегодовая численность работающих, завершение записи - Enter или стрелки курсора.
2. В ячейку А2 записать Категории должностей.
3. Увеличить ширину столбца А так, чтобы запись появилась в ячейке А2, для этого подвести указатель мыши на границу между заголовками столбцов А и В, указатель примет вид двунаправленной стрелки ↔, с нажатой левой кнопкой передвинуть границу столбца.
4. В ячейки B2, C2, D2 записать соответственно Механический цех Сборочный цех Всего.
5. Отформатировать текст в строке 2 по центру.
6. В ячейки А3:А8 записать наименование должностей, а в А9 написать ИТОГО:
7. Подобрать ширину столбца А так, чтобы запись поместилась в ячейке А8, действия аналогичны п. 3.
8. Отформатировать текст в ячейке А9 по правому краю.
9. В ячейки В3:С8 записать цифровые данные по численности.
10. Произвести суммирование численности по Механическому цеху, для этого выделить ячейку В9, выполнить команду ∑ (Автосумма) на вкладке  Главная. Появится формула СУММ(В3:В8), обратить внимание, что диапазон В3:В8 выделяется пунктирной рамкой. Для закрепления формулы нажать Enter, вместо формулы появится числовое значение суммы по столбцу.
11. Произвести суммирование численности по Сборочному цеху, повторив действия п.10 для ячейки С8.
12. Произвести суммирование численности по категории Рабочие, для этого выделить ячейку D3, выполнить команду ∑ (Автосумма).
13. Произвести суммирование численности по всем остальным категориям должностей, повторяя действия по п. 12.
14. При выполнении команды ∑ (Автосумма) в некоторых ячейках столбца D происходит автоматическое выделение не строки слева от ячейки, а столбца над выделенной ячейкой. Для изменения неверного диапазона суммирования необходимо при появлении пунктирной рамки выделить нужный диапазон ячеек с нажатой левой кнопкой мыши, нажать Enter.
15. В ячейке D9 подсчитать общую численность работающих, выполнив команду ∑(Автосумма) и указывая нужный диапазон с помощью мыши.
16. Отформатировать заголовок таблицы, для этого выделить ячейки А1:D1, выполнить команду Объединить и поместить в центре (кнопка ←а→ на панели инструментов Форматирование).
17. Сохранить в своей папке под именем «Работа 1» (без кавычек)

Практическая работа №2

Тема. Основы работы с электронной таблицей Excel.

Цель. Приобрести практические навыки по созданию и оформлению ЭТ, вводу данных, написание формул.

Задание. На отрезке [0;2] с шагом 0,2 протабулировать функцию

Алгоритм выполнения задания.

ПРИМЕР ЗАДАНИЯ НЕОБХОДИМО ВЫПОЛНИТЬ

1. Оформить заголовки расчетной таблицы - В ячейку А1 записать «Х», в В1 – «Y»
2. В ячейку А2 ввести «0», в А3 – «0,2». Далее, используя автозаполнение, ввести значения Х до «2».
3. В ячейку В2 ввести формулу «=A2/(A2+1)». Далее, используя автозаполнение, рассчитать значение Y для всех Х.

Результат работы:

	Каждый пример рассчитывается на отдельном листе!!! В одном документе MS Excel В итоге выполнения работы в Вашей папке должен быт файл «Работа 2» с четырьмя листами

 

Задания

1. На отрезке [2;3] с шагом 0,1 протабулировать функцию
2. На отрезке [5;15] с шагом 0,8 протабулировать функцию
3. На отрезке [0;2] с шагом 0,2 протабулировать функцию

Сохранить в своей папке под именем «Работа№2» (без кавычек)

Практическая работа №3

Тема. Основы работы с электронной таблицей Excel.

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

Задание. Создать таблицу, показанную на рисунке.

	A	B	C	D	E
1	Выполнение плана предприятиями области
2	Наименование предприятия	Среднегодовая стоимость основных фондов (млн. руб.)	Среднесписочное число работающих за отчётный период	Производство продукции за отчётный период (млн. руб.)	Выполнение плана (в процентах)
3	Авиаприбор	3,0	360	3,2	103,1
4	Стеклозавод	7,0	380	9,6	120,0
5	Медтехника	2,0	220	1,5	109,5
6	Автопровод	3,9	460	4,2	104,5
7	Темп-Авиа	3,3	395	6,4	104,8
8	Приборостроительный завод	2,8	280	2,8	108,1
9	Автонормаль	6,5	580	9,4	94,3
10	Войлочная	6,6	200	11,9	125,0
11	Машиностроительный завод	2,0	270	2,5	101,4
12	Легмаш	4,7	340	3,5	102,4
13	ИТОГО:	41,8	3485	55

Алгоритм выполнения задания.

1.      В ячейке А1 записать название таблицы.

2.      В ячейках А2:Е2 записать шапки таблицы с предварительным форматированием ячеек, для этого

3.      Набрать цифровые данные таблицы.

4.      Подсчитать итоговые данные по столбцам, используя команду Автосумма.

5.       Сохранить в своей папке под именем «Работа 3» (без кавычек)

Практическая работа №4

Тема. Основные навыки работы с электронной таблицей Excel.

Цель. Приобрести и закрепить практические навыки по созданию электронной таблицы с использованием возможностей автозаполнения, автосуммирования и копирования.

Задание. Создать шаблон для заполнения электронной таблицы, показанный на рисунке.

	A	B	C	D	E	F	G	H
1	Ведомость выдачи заработной платы
2	№	Фамилия	Январь					Итого
3	1	Иванов
4	2	Петров
5		Сидоров
6		Глухов
7		Галкин
8		Смирнов
9		Горшков
10		Авдеев
11		Сумма:

Алгоритм выполнения задания.

1.      Набрать заголовки таблицы, для этого:

2.      Заполнить таблицу данными.

2.1.   Заполнить диапазон A5:A10 с помощью процедуры автозаполнения

2.2.   Заполнить диапазон D2:G2 с помощью процедуры автозаполнения

2.3.   Ввести цифровые данные в диапазон C3:G10

3.      Рассчитать сумму Итого, полученную каждым работником за пять месяцев, для этого:

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

5.       Сохранить в своей папке под именем «Работа 4» (без кавычек)

Практическая работа №5

Тема. Мастер функций в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению функций категории Статистические с использованием Мастера функций.

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

	A	B	C	D	E	F	G	H
1	Продажа комплектующих к персональным компьютерам
2	Месяц	Центр ЭВМ	ЭВМ-сервис	Дом бизнеса	Техноцентр	Среднее	Максимум	Минимум
3	Январь	18420	10305	25420	15940
4	Февраль	18300	10370	25400	15880
5	Март
6	Апрель
7	Май
8	Июнь
9	Июль
10	Август
11	Сентябрь
12	Октябрь
13	Ноябрь
14	Декабрь
15	Итого:
16	Максимум
17	Минимум

Алгоритм выполнения задания.

1.      Записать заголовок и шапочки таблицы.

2.      Заполнить диапазон A3:A14.

3.      Заполнить четыре столбца цифровыми данными:

3.1.   Заполнить две строки указанными на рисунке цифрами.

3.2.   Выделить диапазон ячеек В3:Е4, выполнить операцию Автозаполнение до строки Итого.

4.      Заполнить графу Итого, используя операции Автосумма и Автозаполнение.

5.      Рассчитать Среднее в ячейке F3.

6.      Заполнить столбец Среднее по Декабрь, используя операцию Автозаполнение.

7.      Рассчитать Максимум в ячейке G3.

8.      Заполнить столбец Максимум по Декабрь, используя операцию Автозаполнение.

9.      Рассчитать Минимум в ячейкеН3.

10.  Заполнить столбец Минимум по Декабрь, используя операцию Автозаполнение.

11.  Рассчитать строку 16 Максимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

12.  Рассчитать строку 17 Минимум с помощью мастера функций, исправляя диапазон адресов на В3:В14 и применяя операцию Автозаполнение.

13.   Сохранить в своей папке под именем «Работа 5» (без кавычек)

Практическая работа №6

Тема. Мастер функций в MS Excel.

Цель. Приобрести и закрепить практические навыки по применению встроенных функций.

Задание. Создать таблицу, показанную на рисунке.

1      Ячейки А1, В1, С1 и D1 должны быть объединены, в получившуюся ячейку вводится название «реки Евразии»

2      Ячейка А1 – заливка желтым цветом

3      В ячейку А2 вводится текст «наименование реки», В2 – «длина, км», С2 – «площадь бассейна, км^2» (чтобы написать символ «^» необходимо перейти на английский язык, далее удерживая клавишу Shift нажать 6), D2 – «сток».

4      Ширину столбцов необходимо подобрать по содержимому ячейки

5      В ячейках А2, В2, С2 и D2 выравнивание текста по центру

6      В диапазон ячеек А3:А8 вводятся наименование рек

7      В диапазон ячеек В3:В8 вводятся длины рек

8      В диапазон ячеек С3:С8 вводятся площади бассейнов

9      В диапазон ячеек D3:D8 вводится сток рек

10  В диапазон ячеек В3:В8, С3:С8 – выравнивание по центру

11  В диапазон ячеек А3:А8 цвет текста - синий

12  В ячейку А9 ввести «общая длина рек Евразии»

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

14  Ячейка В9 имеет следующие параметры – шрифт полужирный, размер шрифта 14 пт, залита голубым цветом

15  В ячейку А10 ввести «общая площадь бассейнов»

16  В ячейке В10 написать формулу, которая будет вычислять общую площадь бассейнов

17  Ячейка В10 имеет следующие параметры – шрифт полужирный, размер шрифта 14 пт, залита голубым цветом

18  У всей таблицы шрифт – Times New Roman

19  Каждая ячейка таблицы должна быть ограничена

20  Сохранить в своей папке под именем «Работа 6» (без кавычек)

Практическая работа №7

Формат ячеек. Построение графиков

Тема. Мастер построения диаграмм в MS Excel.

Цель. Приобрести практические навыки по созданию графиков и диаграмм в ТП MS Excel

Задание.

Оформить таблицу согласно представленному ниже образцу

Выделить диапазон ячеек В3:G11. По выделенному диапазону нажимаем 1 раз ПКМ. Выбираем пункт меню Формат ячеек на вкладке Число выбираем пункт Денежный -> ОК

В результате выполнения данного действия таблица примет следующий вид

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

(использовать встроенную формулу СУММА)

Диапазон ячеек G4:G10 заполняется с помощью процедуры автозаполнения.

В ячейку B11 ввести формулу, которая будет рассчитывать сколько в январе было получено всеми сотрудниками (использовать встроенную формулу СУММА).

Диапазон ячеек  В11:G11 заполняется с помощью процедуры автозаполнения.

В результате выполнения данных действий таблица примет следующий вид

Необходимо построить круговую диаграмму, отражающую зарплату каждого сотрудника за январь.

Для этого необходимо выделить диапазон А3:В10

Вкладка «Вставка»,

группа инструментов «Диаграмма»,

Круговая

После выполнения действия результат:

Далее необходимо написать имя диаграммы: выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», название диаграммы

Выбираем «Над диаграммой». Вводим в появившейся рамке на диаграмме «заработная плата за январь».

Результат:

Необходимо подписать данные (т.е. каждая часть диаграммы должна отражать сколько именно в рублях получил сотрудник).

Далее необходимо подписать данные : выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Подписи данных»

Выбираем «У вершины снаружи»

Результат:

Далее необходимо изменить местоположение легенды : выделяем диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Легенда»

Выбираем «Добавить легенду снизу»

Результат:

Необходимо построить круговую диаграмму, отражающую зарплату Алексея за 5 месяцев

Для этого выделяем диапазон ячеек B2:F2 Вкладка «Вставка», группа инструментов «Диаграмма», Круговая

После выполнения действия результат:

Необходимо задать имя диаграммы, разместить легенду слева, подписать данные в процентах.

Чтобы подписать данные в процентах необходимо выделить диаграмму (щелкаем по ней 1 раз ЛКМ), далее вкладка «Макет», группа инструментов «Подписи», «Подписи данных», «Дополнительные параметры подписи данных».

Ставим галочку «Доли», снимаем галочку «Значения». Нажать «Закрыть»

Результат:

Построить диаграмму «Гистограмма», отражающую сколько получили все сотрудники за каждый месяц.

Для этого выделяем диапазон ячеек B2:F2 зажимаем клавишу CTRL НЕ ОТПУСКАЯ КЛАВИШУ выделяем диапазон  B11:F11 Вкладка «Вставка», группа инструментов «Диаграмма», Гистограмма

Результат:

 

Необходимо задать имя диаграммы, удалить легенду, подписать данные в значениях

Результат:

Построить диаграмму «Круговая», отражающую сколько получили каждый сотрудник за все месяца.

Для этого выделяем диапазон ячеек А3:А10 зажимаем клавишу CTRL НЕ ОТПУСКАЯ КЛАВИШУ выделяем диапазон  G3:G10 Вкладка «Вставка», группа инструментов «Диаграмма», Круговая

Результат:

Необходимо задать имя диаграммы, подписать данные в долях

Результат:

Сохранить в своей папке под именем «Работа 7» (без кавычек)

Практическая работа №8

Тема. Решение расчетных задач и построение диаграмм.

Цель. Закрепить практические навыки по созданию формул. Приобрести практические навыки по построению диаграмм в электронной таблице.

Задание. Создать шаблон для заполнения электронной таблицы, показанный на рисунке.

Заполнить таблицу по представленному ниже образцу:

1        Заполнить произвольными значениями столбец «поставлено».

2        Заполнить произвольными значениями столбец «продано» (продано не может быт больше, чем поставлено)

3        Рассчитать столбец «осталось» (поставлено – продано)

4        Рассчитать столбец «выручка» (цена * продано)

5        Найти для каждого столбца сумму, среднее, наименьшее и наибольшее значение

6        Построить круговую диаграмму, отражающую информацию о товаре и его цене

7        Построить круговую диаграмму, отражающую информацию о товаре и о поставленном количестве

8        Построить круговую диаграмму, отражающую информацию о товаре и о проданном объеме товара

9        Построить круговую диаграмму, отражающую информацию о товаре и остатках

10    Построить круговую диаграмму, отражающую информацию о товаре и выручке, полученной от реализации товара

11    Сохранить в своей папке под именем «Работа 8» (без кавычек)

Практическая работа №9

Тема. Решение расчетных задач и построение диаграмм.

Цель. Закрепить практические навыки по созданию формул. Приобрести и закрепить практические навыки по построению диаграмм в электронной таблице.

Задание

Заданы стоимость 1 кВт./ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.

Оформить таблицу по представленному ниже образцу

1        Найти сумму, среднее значение, максимум и минимум потребленной эл/энергии

2        Построить диаграмму типа «Гистограмма», отражающую информацию о том, сколько расходует каждая квартира электроэнергии

3        Построить диаграмму типа «Гистограмма», отражающую информацию о том, сколько расходует денег каждая квартира на оплату электроэнергии

4        Сохранить в своей папке под именем «Работа 9» (без кавычек)

Практическая работа №10

Тема. Решение расчетных задач и построение диаграмм.

Цель. Закрепить практические навыки по созданию формул. Приобрести и закрепить практические навыки по построению диаграмм в электронной таблице.

Задание

Оформить таблицу по представленному ниже образцу:

наличие товара в магазине
		цена руб. (за кг)	вес, гр	вес, кг	итого общая стоимость
1	груша	35,00р.	5236
2	яблоко	47,00р.	7525
3	апельсин	37,00р.	2173
4	мандарин	41,00р.	18892
5	ананас	105,00р.	7892
6	персик	95,00р.	7891
7	нектарин	89,00р.	3289
1	рассчитать вес в кг (вес в гр/1000)
2	рассчитать общую стоимость (цена руб.*вес, кг)
3	диапазон C3:C9; F3:F9 должен иметь формат Денежный
4	найти общий вес в гр
5	найти общий вес в кг
6	найти максимальную цену
7	найти минимальную цену
8	найти среднюю цену
9	найти максимальный вес в гр
10	найти минимальный вес в гр
11	найти средний вес в гр
12	найти максимальный вес в кг
13	найти минимальный вес в кг
14	найти средний вес в кг
15	найти максимальную итоговую стоимость
16	найти минимальную итоговую стоимость
17	найти среднюю итоговую стоимость
18	построить круговую диаграмму, отражающую название продукта и его вес в кг
19	построить круговую диаграмму, отражающую название продукта и его итоговую стоимость
20	диаграмма должна иметь название, легенду, подписи данных (значение либо доли)
	21  Сохранить в своей папке под именем «Работа 10» (без кавычек)

Практическая работа №11

Тема. Решение расчетных задач и построение диаграмм.

Цель. Закрепить практические навыки по созданию формул. Приобрести и закрепить практические навыки по построению диаграмм в электронной таблице.

Задание

Оформить таблицу по представленному ниже образцу:

1                    Оформить таблицу в MS Excel согласно представленному нижу образцу.

2                    Диапазон ячеек В2:В4 имеет формат ячеек «Финансовый»

3                    Заполнить диапазон В7:D17 произвольными данными.

4                    Ввести в ячейку Е7 формулу, которая будет рассчитывать сколько Алексей потратил на проезд на поезде (необходимо количество км, которое он проехал умножить на стоимость одного км, в нашем случае стоимость = 28 р.).

5                    Заполнить   диапазон ячеек Е8:Е17 с помощью процедуры автозаполнения

6                    Ввести в ячейку F7 формулу, которая будет рассчитывать сколько Алексей потратил на перелет (необходимо количество км, которое он пролетел на самолете  умножить на стоимость одного км, в нашем случае стоимость = 89 р.).

7                    Заполнить   диапазон ячеек F8:F17 с помощью процедуры автозаполнения

8                    Ввести в ячейку G7 формулу, которая будет рассчитывать сколько Алексей потратил на путешествие на катере (необходимо количество км, которое он проплыл  умножить на стоимость одного км, в нашем случае стоимость = 20 р.).

9                    Заполнить   диапазон ячеек G8:G17 с помощью процедуры автозаполнения

10                Рассчитать сумму, наименьшее, наибольшее по каждому столбцу.

11                Построить круговую диаграмму, отражающую сколько каждый турист проехал на поезде

12                Построить круговую диаграмму, отражающую сколько каждый турист пролетел на самолете

13                Построить круговую диаграмму, отражающую сколько каждый турист проплыл на катере

14                Построить круговую диаграмму, отражающую сколько каждый турист затратил на проезд на поезде

15                Построить круговую диаграмму, отражающую сколько каждый турист затратил на перелет на самолете

16                Построить круговую диаграмму, отражающую сколько каждый турист затратил на путешествие на катере

17                Сохранить в своей папке под именем «Работа 11» (без кавычек)