2020-05-12
420
Практическая работа
Выполнив задания данной темы, вы:
· научитесь создавать и производить обработку данных простейших таблиц;
· научитесь производить сортировку данных таблиц;
· научитесь производить вычисления по данным таблиц с применением встроенных формул;
· научитесь использовать Мастер функций в экономических и математических расчетах
Технология выполнения задания:
Задание 1. Создать таблицу расчета реализации продукции.
1. Запустите программу Excel.
2. Дважды щелкните на ярлычке чистого рабочего листа и дайте ему название Таблица.
3. Оформите таблицу так как представлено на (Рис1.):
| A | B | C | D | E | F | ||||
| 1 | Данные по реализации продукции ОАО «Карандаш» | ||||||||
| 2 | Наименование товара | Предприятие | Дата приобретения | Количество (шт.) | Цена за ед. (руб.) | Стоимость (руб.) | |||
| 3 | Тетрадь общая | Школа№1 | 18.12.01 | 56 | 6,50 | Формула | |||
| 4 | Тетрадь 12 лист | ЧИП «Эль» | 01.03.02 | 100 | 1,50 | Формула | |||
| 5 | Альбом д/рисования | Школа изобразительных искусств | 14.02.02 | 70 | 9,20 | Формула | |||
| … |
| Формула | |||||||
| 14 |
| Формула | |||||||
| 15 |
| Формула | |||||||
| 16 | ИТОГО |
| S | S | S | ||||
Рис. 1 Исходные данные таблицы
1. При оформлении таблицы необходимо использовать панель инструментов Форматирование.
2. При вводе Даты приобретения активизируйте Формат – Ячейки. Выберите формат Дата и выберите вид вводимой даты.
3. Аналогично произведите ввод данных по ценам, в данном случае формат – Денежный.
4. В ячейку F3 в Строке формул введите формулу расчета Стоимости: D3*E3.
5. Скопируйте данную формулу в следующие ячейки: активизируйте ячейку F3 и с помощью маркера протяните до окончания таблицы.
6. Для подсчета последней строки Итого, в ячейке D16 выполните команду Автосуммирование на панели инструментов Стандартная.
7. После заполнения всей таблицы выделите ее и на панели инструментов Форматирование активизируйте кнопку Границы. Данная команда позволит вывести сетку таблицы, при распечатке документа.
8. Выделите всю таблицу, при помощи контекстного меню скопируйте ее и вставьте в следующий пустой Лист
9. Переименуйте лист Таблица 2.
10. В данной таблице необходимо произвести сортировку данных в алфавитном порядке, для этого: выделите первый столбец Наименование товара и на панели инструментов Форматирование выберите кнопку Сортировка по возрастанию, список товаров будет
Задание №2. Создайте таблице с данными и выполните сортировку списка таблиц Excel
1. Запустите программу Excel.
2. Откройте рабочую книгу, созданную в предыдущих заданиях (Файл-Открыть).
3. Дважды щелкните на ярлычке чистого рабочего листа и дайте ему название Список. Если в Рабочей книге нет чистых листов вставьте их, выбрав из меню Вставка команду Лист.
|
|
|
4. Создайте список как показано на рисунке 2:
| Предприятие | Продукция | Объем | Сбыт |
| Прометей | Напитки | 4879 | 54500 р. |
| Седьмой элемент | Шампунь | 1966 | 120000 р. |
| Прометей | Напитки | 323 | 126660 р. |
| Прометей | Мясо | 65565 | 15400 р. |
| Седьмой элемент | Красящий шампунь | 56565 | 456000 р. |
| Седьмой элемент | Губная помада | 121 | 12100 р. |
| Прометей | Мясо | 12212 | 1200 р. |
| Прометей | Продукты | 454 | 121540 р. |
| Галина | Сигареты | 545 | 5454 р. |
| Седьмой элемент | Пена для ванн | 56565 | 45000 р. |
| Прометей | Сигареты | 78 | 1200 р. |
| Галина | Напитки | 545 | 4545 р. |
| Седьмой элемент | СМС | 232 | 12125 р. |
| Прометей | Мясо | 55656 | 12200 р. |
| Прометей | Мясо | 2121 | 5487 р. |
| Седьмой элемент | Лак для волос | 121 | 12800 р. |
| Прометей | Продукты | 3265 | 12120 р. |
| Галина | Продукты | 554 | 58545 р. |
Рис.2 Данные для подведения промежуточных итогов
5. Выделите список, включая метки столбцов. В меню Данные выберите команду Сортировка.
11. В появившемся окне «Сортировка диапазона» в поле сортировать по выберите Продавец и установите флажок «по убыванию».
12. В поле "Затем по" выберите Продукция и установите флажок «по возрастанию».
13. В поле "В последнюю очередь по" выберите Объем и установите флажок «по возрастанию». Нажмите ОК. Вы получите отсортированный список.
14. Теперь необходимо подвести итоги по каждому продавцу, выбрав из меню Данные команду Итоги….
15. В появившемся окне «Промежуточные итоги» в поле При каждом изменении в выберите Продавец. Далее определите Операцию – Сумма. В поле Добавить итоги по установите «галочки» для Объема и Сбыта. Нажмите ОК. Список будет иметь вид, рисунок 3:
| Предприятие | Продукция | Объем | Сбыт |
| Галина | Сигареты | 545 | 5 454р. |
| Галина | Напитки | 545 | 4 545р. |
| Галина | Продукты | 554 | 58 545р. |
| Галина Всего | 1644 | 68 544р. | |
| Прометей | Напитки | 4879 | 54 500р. |
| Прометей | Напитки | 323 | 126 660р. |
| Прометей | Мясо | 65565 | 15 400р. |
| Прометей | Мясо | 12212 | 1 200р. |
| Прометей | Продукты | 454 | 121 540р. |
| Прометей | Сигареты | 78 | 1 200р. |
| Прометей | Мясо | 55656 | 12 200р. |
| Прометей | Мясо | 2121 | 5 487р. |
| Прометей | Продукты | 3265 | 12 120р. |
| Прометей Всего | 144553 | 350 307р. | |
| Седьмой элемент | Шампунь | 1966 | 120 000р. |
| Седьмой элемент | Красящий шампунь | 56565 | 456 000р. |
| Седьмой элемент | Губная помада | 121 | 12 100р. |
| Седьмой элемент | Пена для ванн | 56565 | 45 000р. |
| Седьмой элемент | СМС | 232 | 12 125р. |
| Седьмой элемент | Лак для волос | 121 | 12 800р. |
| Седьмой элемент Всего | 115570 | 658 025р. | |
| Общий итог | 261767 | 1 076 876р. | |
Рис.3 Подведение промежуточных итогов
16. Из полученного списка необходимо выделить в отдельную таблицу информацию о продавцах, торгующих мясом с объемом более 500 и сбытом, не превышающим 100000 р. Для этого скопируйте метки столбцов в ячейки Н1:К1. В ячейку I2 занесите Мясо, в ячейку J2 - >500, в ячейку K2 - <=100000. Таким образом, вы определили интервал критериев.
17. Аналогично выведите информацию о фирме Седьмой элемент.
18. Выделите ячейку списка. В меню Данные выберите команду Расширенный фильтр. В появившемся на экране диалоговом окне Исходный диапазон указан автоматически (проверьте его правильность). Щелкните мышью в поле Диапазон условий и выделите интервал I1:K2. Установите переключатель скопировать результат в другое место. Щелкните в поле Поместить результат в диапазон и выделите любую свободную ячейку рабочего листа, например А29. Нажмите кнопку ОК.
19. С помощью расширенного фильтра из исходного списка выделите и поместите в отдельную таблицу данные о товарах со сбытом более 10000р, которыми торгует Прометей (используйте навыки, полученные при выполнении п.11-12).
20. Аналогичным образом из исходного списка выделите и поместите в отдельную таблицу данные о товарах с объемом не более 1000 и сбытом не менее 12500р.
|
|
|
21. Сохраните данные в рабочей книге.
Задание №3. Создайте таблицу Excel и произведите вычисления используя встроенные функций
Пусть заданы 10 фамилий студентов и их оценки по результатам сессии. Вычислить средний балл каждого студента и произвести начисление стипендии по следующим правилам: если средний балл превышает 4,5, то увеличить размер стипендии в 1,5 раза; если средний балл больше 2,99 и меньше 4,6, то начислить стандартный размер стипендии; в остальных случаях стипендию не начислять.
1. Запустите программу Excel.
2. Откройте рабочую книгу, созданную в предыдущем задании.
3. Дважды щёлкните на ярлычке следующего чистого рабочего листа и дайте ему название Начисление стипендии.
4. В ячейки А1 – В4 занесите данные, на основе которых будет начислена стипендия, рисунок 4:
| А | В | |
| 1 | Интервал | Коэффициент |
| 2 | 0..2,99 | 0 |
| 3 | .…4,5 | 1 |
| 4 | 4,6…5 | 1,5 |
Рис. 4 Коэффициенты для начисления стипендий
5. В ячейке Е1 сделайте запись Размер стипендии, а в ячейку Е2 занесите стандартный размер стипендии.
6. Оформите таблицу как показано на рисунке 5, включающую фамилии студентов группы с оценками по предметам.
| A | B | C | D | E | F | |
| 10 | ФИО | Математика | Ин-яз | История | Средний бал | Сумма |
| 11 | Кириллов | |||||
| 12 | Наумов | |||||
| 13 | Захарова | |||||
| 14 | Терехова | |||||
| 15 | Свиридов | |||||
| 20 | ……. | |||||
Рис. 5 Оценки студентов по предметам, с расчетом среднего балла
7. Первые четыре столбца заполняются исходной информацией.
8. Для вычисления среднего балла запишите в ячейку Е11 формулу с использованием функции СР3НАЧ. Скопируйте формулу на остальные ячейки столбца Е.
9. Для расчёта стипендии в ячейку F11 запишите следующую формулу с использованием логической функции ЕСЛИ: =ЕСЛИ(Е11>4,5;Е2*В4;ЕСЛИ(Е11>=3;Е2*В3;0))
10. Проверьте, правильно ли начислена стипендия для первого студента в списке. Размножьте формулу, записанную в ячейке F11, на остальные ячейки столбца F.
|
|
|
11. Убедитесь, что результат расчёта оказался неверным. Это связано с тем, что адреса Е2, В3 и В4 в формуле заданы относительными ссылками и при копировании изменяются.
12. Исправьте формулу в ячейке F11, задав соответствующие ссылки как абсолютные, и снова размножьте её в столбце F.
13. Отсортируйте данные в таблице.
14. Проверьте правильность результатов расчёта по заданной формуле, и, если они верны, сохраните данные.
Задание №4. Создайте таблицу Excel и произведите вычисления используя встроенные функций
1. Запустите программу Excel.
2. Введите в таблицу следующий документ рисунок 6 (порядок ввода данных документа рассматривается ниже).
Рис.6 Вводимый документ
3. Ввод данных осуществляется в активную ячейку. Введите данные в пока пустую электронную таблицу в следующем порядке.
4. Сделайте активной ячейку В2 и введите название таблицы “Сведения о заработной плате сотрудников”. Обратите внимание, что вводимая информация дублируется в строке формул. Для окончания ввода текста в ячейку необходимо или нажать клавишу <Enter>, или перевести табличный курсор в другую ячейку.
5. Сделайте активной ячейку А3 и введите название первой колонки “табельный номер”. Введенная информация размещается пока в нескольких ячейках.
6. Переведите курсор мыши в ячейку В3 и введите название второй колонки “Ф.И.О.”.
7. Аналогично введите названия остальных столбцов документа.
8. Введите собственно данные документа за исключением столбца и строки “Итого”, для его расчета выполните следующие операции.
9. Сделайте активной ячейку «Итого»-«Сидоров». Нажмите кнопку в пиктографическом меню 
(“Автосуммирование”), в ячейке появится формула =СУММ(С4:F4). Нажмите <Enter>. В ячейку G4 будет внесено число 1470, которое получено при сложении С4+D4+E4+F4=320+350+400+400=1470, а в строке формул будет записана соответствующая расчетная формула.
10. Сделайте активной ячейку «Итого»-«Иванов». Вызовите команду “ВСТАВКА/ФУНКЦИЯ”. В списке “Категория” выберите класс функций “Математические”. В списке “Функция” с помощью кнопок линейки прокрутки найдите и выделите функцию СУММ, нажмите кнопку «Далее». В строку аргумента введите с помощью мыши диапазон суммируемых ячеек, нажмите «Enter».
11. Скопируйте содержимое ячейки G5 в ячейки G6:G12, для чего сделайте активной ячейку G5. Нажав правую кнопку мыши, вызовите контекстное меню, в котором выделите команду “КОПИРОВАТЬ”. Ячейка G5 будет помещена в рамку с движущимися элементами. Маркируйте (т.е. сделайте активной) область G6:G12 и нажмите <Enter>. Любым из способов заполните ячейки, в каждой из которых содержится сумма заработной платы сотрудников отдела за соответствующий месяц.
12. Для улучшения внешнего вида таблицы выделите рамкой названия столбцов. Маркируйте область, названий столбцов таблицы (A3:G3). Вызовите команду через контекстно-зависимое меню “ФОРМАТ ЯЧЕЕК” и перейдите к листу “Рамка”. Определите положение рамки и тип линии. Нажмите кнопку ОК.
13. Расположите табельные номера по центру, для чего маркируйте содержащую их область (А4:А12). Нажмите кнопку в пиктографическом меню 
(“По центру”).
14. Расположите название первого столбца в две строчки, для чего сделайте активной ячейку А3. По команде “ФОРМАТ ЯЧЕЕК” после открытия диалогового окна “ФОРМАТ ЯЧЕЕК” на листе “Выравнивание” включите переключатель “Переносить по словам” и нажмите кнопку ОК.
15. Измените ширину второго столбца. Подведите курсор мыши к координатной ячейке столбца В справа. Курсор трансформируется в двунаправленную стрелку. Отбуксируйте вправо на нужное расстояние разделитель столбцов и отпустите кнопку мыши.
16. Расположите в центре ячейки название столбцов документа.
17. Заключите аналогичным способом в рамку содержимое подстроки «Итого» (А13:G13).
18. Измените шрифт для заголовка документа, используя команду контекстно-зависимого меню “ФОРМАТ ЯЧЕЕК” и лист “Шрифт”. Поэкспериментируйте с различными характеристиками оформления текста, такими как шрифт, стиль, размер, эффект. Выберите наиболее удачный с Вашей точки зрения вариант шрифта для заголовка.
19. Измените также шрифт текста в последней строке таблицы, для чего используйте кнопку в пиктографическом меню 
(“ПОВТОРИТЬ”). Команда “Повторить” ускоряет работу в ситуации, когда одна и та же операция выполняется над различными объектами.
20. Если Вы успешно справились с каждой операцией по вводу данных и оформлению внешнего вида документа, то в конечном итоге Ваш документ должен приобрести следующий вид (рис.7)
Рис.7. Итоговый документ
Задание № 5. Используя Мастер функций выполните экономические и математические расчеты
1. Запустите программу Excel.
2. Откройте рабочую книгу, созданную в предыдущем задании.
3. Дважды щелкните на ярлычке чистого рабочего листа и дайте ему название Мастер функций.
4. Использование математических функций.
5. Создадим электронную таблицу по обработке статистической информации. Для этого:
6. В ячейки А2:А13 занесите следующий набор случайных чисел:
4,407678 9,914596 3,361304
3,481150 0,490590 1,194127
8,629884 6,119983 2,654380
8,779637 7,755563 5,058417
7. Дайте заголовок этому столбцу: Случайное число
8. В ячейке В1 напечатайте заголовок: Округление. В столбец В (диапазон В2:В13) поместите числа, представляющие собой округленные значения чисел из столбца А с точностью до 2 значащих цифр после запятой. Для этого выполните следующие действия:
9. Установив курсор в ячейку В2, щелкните по кнопке вызова Мастера функций (fx) на стандартной панели инструментов.
10. В поле Категория открывшегося окна выберите Математические, в поле Функция найдите в списке и щелкните мышью на функции с названием ОКРУГЛ.
11. Для выбранной функции следует указать два параметра: ссылку на округляемое число и количество цифр после запятой. Щелкните мышью по ячейке А2 - в поле Число отразится адрес округляемого числа. Перейдите в поле Количество цифр и напечатайте 2 (это количество значащих цифр после запятой).
12. В ячейке В2 появится результат округления числа, находящегося в ячейке А2 (4,41). В строке формул отражается формула, записанная в ячейке В2.
13. Скопируйте формулу из ячейки В2 на остальные ячейки столбца В. Для этого поместите табличный курсор на ячейку В2, наведите указатель мыши на маркер заполнения (черный крестик в правом нижнем углу табличного курсора) зафиксируйте левую кнопку мыши и протяните прямоугольный контур до ячейки В13. Отпустите кнопку мыши, формула из ячейки В2 будет скопирована на все выделенные ячейки столбца В и вы увидите результат вычисления по этой формуле. Проверьте правильность вычислений.
14. Составьте еще 3 столбца с заголовками Корень (в ячейке С1), Целое (в ячейке D1) и Факториал (в ячейке Е1).
15. Для создания третьего столбца, содержащего квадратные корни из соответствующих ячеек столбца В, используйте математическую функцию КОРЕНЬ. Используя Мастер функций, запишите формулу сначала в ячейку С2 (указав в качестве параметра ссылку на ячейку В2), а затем скопируйте ее на остальные ячейки столбца С.
16. Для записи значений в четвертый столбец D, содержащий целые значения соответствующих ячеек столбца С, используйте математическую функцию ЦЕЛОЕ.
17. Для создания пятого столбца, содержащего факториалы чисел, расположенных в соответствующих ячейках столбца D, используйте математическую функцию ФАКТР.
18. Использование статистических функций.
19. На базе полученных выборок составьте вторую таблицу, в которой будут использованы результаты первой таблицы. Таким образом, эти две таблицы окажутся связанными.
20. В столбце А, начиная с ячейки А15, расположите названия:
Среднее значение
Дисперсия
