Комплекс практических работ в MS Excel

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

Тема: Редактирование и форматирование таблиц в Excel.

Цель работы: научиться создавать, заполнять, редактировать и форматировать таблицы в программе Excel.

Практические задания и методические указания:

Задание 1. В диапазоне ячеек А1:Е3 создайте копию, приведенной ниже таблицы.

Методические указания:

Введите необходимый текст в нескольких ячейках, предварительно объединив ячейки В1:В3, С1:С3, D1:D3, Е1:Е3, и расположите его различными способами в различных форматах.

Для объединения ячеек используйте режим отображения ОБЪЕДИНЕНИЕ ЯЧЕЕК вкладки ВЫРАВНИВАНИЕ команды ФОРМАТ ЯЧЕЕК.

Для направления текста в ячейках нужно выбрать нужную ОРИЕНТАЦИЮ вкладки ВЫРАВНИВАНИЕ команды ФОРМАТ ЯЧЕЕК.

Для форматирования текста воспользуйтесь командой ФОРМАТ ЯЧЕЕК – ШРИФТ, для задания границ – ФОРМАТ ЯЧЕЕК – ГРАНИЦА.

Задание 2. Введите в одну ячейку А1 листа 2 предложение и отформатируйте следующим образом:

Методические указания:

Для добавления новой строки в ячейку используется комбинация клавиш ALT + ENTER. Для расположения текста в ячейке в несколько строк также можно применить вкладку ВЫРАВНИВАНИЕ команды ФОРМАТ ЯЧЕЕК и установить флажок ПЕРЕНОСИТЬ ПО СЛОВАМ.

Граница в ячейке А1 (пунктир точка) устанавливается также через ФОРМАТ ЯЧЕЕК – ГРАНИЦА – выбираем тип границы.

Задание 3. На листе 3 постройте таблицу следующего вида:

Методические указания:

С помощью команды ФОРМАТ ЯЧЕЕК подготовьте и отформатируйте таблицу, применив: объединение ячеек, заливка, выравнивание по центру текст, границы (пожирнее линия).

Задание 4. На листе 4

a.       Записать в ячейки А1-А12 названия всех месяцев года, начиная с января.

b.      Записать в ячейки B1-G1 названия всех месяцев второго полугодия.

c.       Записать в ячейки А13- G13 названия дней недели.

Методические указания:

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

Задание 5. На листе 5 создайте таблицу по образцу.

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

                Задание 6. На листе 6 создайте таблицу по образцу.

                 Контрольные вопросы:

1. Дайте определение электронной таблицы.
2. Как оформляется таблица в MS Excel?
3. Как объединить ячейки в таблице?
4. Как выровнять текст по центру в таблице и по горизонтали и по вертикали?
5. Как залить столбец в таблице?
6. Как в одной ячейке написать несколько строк?

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

Тема: Загрузка табличного процессора. Элементы окна.

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

Практические задания:

Задание 1. Заполнить таблицу, рассчитав значения по следующим формулам:

Начислено = ставка за час * количество отработанных часов

Налог = 13% * Начислено

Итого к выдаче = Начислено – Налог

Всего = Сумма значений «Итого к выдаче»

Ход выполнения:

1.     Создайте таблицу по образцу:

2.     Отрегулируйте ширину столбцов.

3.     В ячейку D3 ввести формулу: =B3*C3; в ячейку Е3: =13%* D3; в ячейку F3: = D3-Е3.

4.     Аналогично рассчитайте для других людей.

5.     Рассчитаем общую сумму. В ячейку F10 ввести формулу: =СУММ(F3: F9) и нажать Enter.

6.     Оформить внешний вид таблицы.

Задание 2. Рассчитать доли каждого вклада в общей сумме.

Указание:

1.     Посчитать общую сумму вкладов

2.     Доля рассчитывается по формуле: =сумма вклада вкладчика/общую сумму

3.     Перевести полученные значения в %.

4.     Оформить внешний вид таблицы.

Задание 3. Подсчитать средний балл.

Указания:

1.     Чтобы разместить названия предметов вертикально сделать так: набрать все предметы, выделить эти ячейки, ФОРМАТ ЯЧЕЕК, раздел ВЫРАВНИВАНИЕ – ОРИЕНТАЦИЯ, повернуть текст вверх.

2.     Для подсчета среднего балла использовать функцию СРЗНАЧ, т.е. для ячейки I3 будет формула: =СРЗНАЧ(С3:H3). Аналогично рассчитайте остальные ячейки.

3.     Оформить внешний вид таблицы.

Задание 4. Создайте по образцу таблицу.

1.     Для того чтобы в столбце ЦЕНА установить денежный формат (р.) необходимо выделить ячейки со значениями, правой кнопкой мыши ФОРМАТ ЯЧЕЕК – ЧИСЛО – ДЕНЕЖНЫЙ – ЧИСЛО ДЕСЯТИЧНЫХ ЗНАКОВ – 2 и ОБОЗНАЧЕНИЕ – р.

2.     Посчитайте Сумму по следующей формуле, в ячейку Е4: =С4*D4.

3.     Аналогично рассчитайте остальные ячейки.

4.     ИТОГО рассчитайте самостоятельно через: =СУММ.

5.     Оформить внешний вид таблицы.

Задание 5. Создайте по образцу таблицу.

Указания:

1.     Подготовьте таблицу.

2.     Внесите все значения в таблицу.

3.     Оформите внешний вид таблицы.

4.     СУММУ рассчитайте самостоятельно по формуле.

5.     ИТОГО рассчитайте тоже самостоятельно по формуле.

Контрольные вопросы:

1.     Что такое электронная таблица?

2.     Какое основное достоинство ЭТ?

3.     Как правильно составлять формулы в ЭТ?

4.     Как оформить внешний вид таблицы?

5.     Как выставить денежный формат (р.)?

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

Тема: Работа с формулами в MS Excel.

Цель работы: Обобщение и закрепление знаний и практических навыков по созданию и оформлению таблиц, диаграмм, организации расчетов.

Практические задания:

Задание 1. Подготовьте таблицу по образцу и выполните расчеты.

1. Подготовьте таблицу. Внесите значения. В ячейках С2:С5 необходимо выставить денежный формат, для этого: в ячейки С2:С5 внесите значения 520, 400, 220, 380. Затем ФОРМАТ ЯЧЕЙКИ – ЧИСЛО – ДЕНЕЖНЫЙ – ЧИСЛО ДЕСЯТИЧНЫХ ЗНАКОВ -2, ОБОЗНАЧЕНИЕ: р.
2. Рассчитайте столбец D. В ячейку D2 введите формулу: =В2*С2.
3. Аналогично рассчитайте остальные ячейки (D3, D4, D5).
4. Ячейки D2: D5 сделайте формат денежный (р.).
5. Рассчитайте ИТОГО в ячейках В6, С6, D6 с помощью =СУММ.
6. Оформите внешний вид таблицы.

Задание 2. Подготовьте таблицу и сделайте расчеты самостоятельно и оформить внешний вид таблицы.

Задание 3. Подготовьте таблицу по образцу и сделайте необходимые расчеты и оформить внешний вид таблицы.

Остаток на конец месяца рассчитывается по формуле:  =ОНМ+приход-расход.

Задание 4.Создайте таблицу по образцу и оформите внешний вид.

Рассчитать ИТОГО по формуле через =СУММ.

Задание 5. Подготовьте таблицу по образцу, рассчитайте самостоятельно ФИНАНСОВЫЙ РЕЗУЛЬТАТ и ИТОГО, оформите внешний вид таблицы.

Задание 6. Подготовьте таблицу по образцу, рассчитайте самостоятельно СУММУ, РУБ. и ВСЕГО. В столбцах С и Е выставить денежный формат (Р.).

Контрольные вопросы:

1. Что подразумевается под ОФОРМИТЬ ВНЕШНИЙ ВИД ТАБЛИЦЫ?
2. Как сделать заливку столбца или строки?
3. Как подсчитать ВСЕГО?
4. Как объединить ячейки?

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

Тема: Использование абсолютных и смешанных ссылок в электронных таблицах.

Цель работы: Развивать логическое мышление и навыки в ЭТ Excel; научиться применять смешанные, абсолютные и относительные ссылки.

Практические задания: Задание 1. Создайте таблицу умножения.

Методические указания:

1.      Подготовьте таблицу:

2.      В ячейку В3 запишите формулу: =А3*$B$2 и растяните ее вниз (от В3 до В12).

3.      В ячейке В3 измените формулу на: =$A3*B$2 и растяните полученную формулу сначала вправо, а затем вниз.

4.      Убедитесь, что теперь формулы заполнены верно:

5.      Оформите внешний вид таблицы и переименуйте Лист 1 на ТАБЛИЦУ УМНОЖЕНИЯ.

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

Методические указания:

1.      Подготовьте таблицу по образцу и оформите внешний вид таблицы.

2.      В ячейку С2 ввести формулу: =В2*$E$2. Ячейку Е2 делаем абсолютной при помощи знака $ для того чтобы в дальнейшем можно было копировать формулу не записывая ее снова (т.е. ячейку Е2 мы замораживаем).

3.      При помощи маркера автозаполнения рассчитайте остальные ячейки С3:С11.

4.      Ячейку С12 рассчитайте через =СУММ по столбцу С.

5.      Переименуйте Лист 2 в КОМПЬЮТЕРЫ.

Задание 3. Создайте ЭТ «Доставка груза» по образцу и вычислите ДОСТАВКУ (при помощи абсолютной адресации) и ВСЕГО (при помощи =СУММ).

Примечание: Формула в ячейке F3 вводится с учетом того, что стоимость доставки груза складывается из следующих трех величин (наценок):

ü    5% от стоимости груза

ü    (230 р./км) от расстояния

ü    (100 р./этаж) номера этажа

Переименуйте Лист 3 на ДОСТАВКА ГРУЗА.

Задание 4. Создайте таблицу по образцу и рассчитайте столбец С (С6:С12) по формулам, используя абсолютную адресацию.

Примечание: Прежде чем вбивать значения в ячейки В6:В12 необходимо выставить денежный формат. ФОРМАТ ЯЧЕЙКИ – ЧИСЛО – ДЕНЕЖГЫЙ – ЕВРО.

Переименуйте Лист 4 на КАНЦТОВАРЫ.

Задание 5. Создайте таблицу по образцу и выполните вычисления, используя формулу с относительной адресацией для столбца D (стоимость = цена * количество). Для столбца Е необходимо ячейку В1 сделать абсолютной (налог = стоимость * подоходный налог).

Задание 6. Подготовьте таблицу по образцу и рассчитайте самостоятельно столбец Е, если известно, что 1 карат=0,2 грамма (с использованием абсолютной адресации).

Задание 7. Подготовьте таблицу по образцу.

Прежде чем внести значения в ячейки С6:С15 необходимо выставить денежный формат. ФОРМАТ ЯЧЕЕК – ЧИСЛО – ДЕНЕЖНЫЙ – ОБОЗНАЧЕНИЕ (р.) – число десятичных знаков -2.

Рассчитайте самостоятельно ячейки D6:D15 по формулам с абсолютной адресацией, если вам известно стоимость в рублях и курс доллара.

Контрольные вопросы:

1.      Чем отличается абсолютная ссылка от относительной?

2.      Приведите пример абсолютной адресации.

3.      Приведите пример относительной адресации.

4.      Какая функция у смешанных ссылок?

5.      Приведите пример формулы со смешанной ссылкой.

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

Тема: Работа с формулами в MS Excel.

Цель работы: Освоить технологии создания табличного документа. Научиться созданию формулы и правилам изменения ссылок в них.

Практические задания:

Задание 1. Введите в ячейку А1 число 10, а в ячейку А2 – число 15. В ячейке А3 введите формулу: =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 – 25. Поменяйте значения ячеек А1 и А2 любыми другими числами (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3. Оформить таблицу границами внешними и внутренними.

Задание 2. Рассчитать количество прожитых дней.

Технология выполнения:

1.      В ячейку А1 ввести дату своего рождения (число, месяц, год – 20.12.81).

2.                  Просмотреть различные форматы представления даты (Формат ячейки – Число – Числовые форматы – Дата). Перевести дату в тип ЧЧ.месяц прописью.ГГГГг. Пример, 14 марта 2001г.

3.      В ячейку А2 ввести сегодняшнюю дату.

4.                  В ячейке А3 вычислить количество прожитых дней по формуле =А2-А1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

5.      Оформить таблицу границами внешними и внутренними.

Задание 3. По заданному списку учащихся и даты их рождения определить, кто родился раньше (позже), определить кто самый старший (младший).

Технология выполнения:

1.      Наберите таблицу по образцу.

2.                  Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю дату. Из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося.

В ячейку D3 ввести формулу:

=ГОД(СЕГОДНЯ()-С3)-1900

3.      Аналогично рассчитайте остальные ячейки, с помощью маркера автозаполнения.

ВНИМАНИЕ! Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип (Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

4.                  Определим самый ранний день рождения. В ячейку С22 записать формулу =МИН(С3:С21).

         Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21).

         Определим самый поздний день рождения. В ячейку С23 записать формулу =МАКС(С3:С21).

         Определим самого старшего учащегося. В ячейку D23 записать формулу = МАКС(D3:D21).

            5. Оформить внешний вид таблицы (выравнивание, границы).

Задание 4. Произвести необходимые расчеты роста учеников в разных единицах измерения.

Технология выполнения:

1.      Подготовьте таблицу по образцу.

2.      Заполните ячейки В2:В7 и С2:С7 произвольными фамилиями, именами и рост (см). Рост (см) указывается таким образом: 167, 178, 149 и т.д.

3.      Ниже приведены значения для перевода роста в другие единицы измерения. Оформите данную таблицу на этом же листе в других ячейках.

4.      Переведите рост каждого ученика из СМ в ДЮЙМЫ, АРШИНЫ, ВЕРШКИ, ФУТЫ.

В ячейку D2 введите формулу =C2/$J$2 (знаком $ мы фиксируем ячейку для того чтобы можно было использовать маркер автозаполнения).

Остальные формулы ячеек D3: D7 заполните маркером автозаполнения.

5. Выделите ячейки D2: D7 (Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 2).

6. Аналогично самостоятельно рассчитайте остальные столбы (аршины, вершки, футы), используя знак $.

7. Чтобы рассчитать СРЕДНИЙ РОСТ необходимо использовать функцию =СРЗНАЧ. В ячейку С8 введите формулу =СРЗНАЧ(С2:С7). Аналогично рассчитайте D8, E8, F8, G8.

8. Чтобы рассчитать МАКСИМАЛЬНЫЙ РОСТ необходимо использовать функцию =МАКС. В ячейку С9 введите формулу =МАКС(С2:С7). Аналогично рассчитайте D9, E9, F9, G9.

9. Чтобы рассчитать МИНИМАЛЬНЫЙ РОСТ необходимо использовать функцию =МИН. В ячейку С10 введите формулу =МИН(С2:С7). Аналогично рассчитайте D10, E10, F10, G10.

10. Оформите внешний вид таблицы.

Задание 5. Создайте таблицу по образцу.

Технология выполнения:

1.      В ячейке В1 установить денежный формат: выделить ее, Формат ячеек, Число, Числовые форматы, денежный, Обозначение:р.

2.      Аналогично для ячеек В3:В6 выставить денежный формат Английский (США).

ТОЛЬКО ПОСЛЕ ВЫСТАВЛЕНИЯ ДЕНЕЖНОГО ФОРМАТА ВБИВАТЬ ЧИСЛА! ДЛЯ ЯЧЕЙКИ В1 внести значение 68,75 (р. поставится автоматически). ДЛЯ ЯЧЕЕК В3:В6 внести значения 1200, 350, 1349, 1558 (знак $ и знаки после запятой выставятся автоматически).

3.                  В ячейках С3:С6 будут расчетные формулы. Так как эти формулы однотипны: нужно перемножить курс доллара на стоимость путевки в долларах, то запишем одну формулу в ячейку С3, а остальные получим ее копированием (маркером автозаполнения).

В ячейку С3 ввести формулу =В3*$B$1. Адрес В1 делаем абсолютным, так как курс доллара одинаков во всех формулах и не должен меняться при копировании.

При помощи маркера автозаполнения рассчитайте остальные формулы.

4.      Оформите внешний вид таблицы.

Задание 6. Компания по обслуживанию жилых домов установила следующие тарифы на свои услуги: 1 литр холодной воды стоит 38,06р., 1кВт/ч электроэнергии стоит 4,72р., 1 кубометр газа – 242,78р. Квартиросъемщик №1 израсходовал за месяц 300 литров воды, 69 кВт/ч электроэнергии и 0,5 кубометров газа. Квартиросъемщик №2 израсходовал за месяц 50 литров воды, 200 кВт/ч электроэнергии. Квартиросъемщик №3 израсходовал за месяц 150 литров воды, 150 кВт/ч и 0,3 кубометров газа.

Построить таблицу и рассчитать оплату за месяц каждого квартиросъемщика (по формулам).

               Контрольные вопросы:

1.      Какие бывают ссылки в электронной таблице и в чем их отличие?

2.      Когда применяются абсолютные адреса?

3.      С чего начинается формула в MS Excel?

4.      Что подразумевается под внешним видом таблицы?

5.      Можно ли одновременно использовать относительную и абсолютную адресацию в одной ячейке? Приведите пример.

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

Тема: Статистическая обработка данных с помощью стандартных функций.

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

Краткие теоретические сведения: Статистика – наука о сборе, измерении и анализе массовых количественных данных.

Одним из способов обработки данных является вычисление различных обобщающих показателей – среднее арифметическое, мода, медиана, размах, дисперсия.

Среднее арифметическое – это частное от деления суммы этих чисел на число слагаемых. Находят тогда, когда хотят определить среднее значение для некоторого ряда данных.

Мода ряда чисел – число, чаще других, встречающееся в данном ряду. Находят тогда, когда хотят выявить некоторый типичный показатель.

Медиана – число, записанное посередине.

Размах ряда чисел – разность между наибольшим и наименьшим из этих чисел. Находят тогда, когда хотят определить, как велик разброс данных в ряду.

Дисперсия – это среднее арифметическое квадратов разностей между значениями случайной величины и ее средним значением.

Практические задания:

Задание 1. Подготовьте таблицу по образцу.

1. Необходимо рассчитать значения там где стоит знак ? Для этого:

А) выделить В3, вкладка ФОРМУЛЫ, ВСТАВИТЬ ФУНКЦИЮ, выбрать функцию СРЗНАЧ, в строке Число1 ввести В2:Н2;

Б) выделить В4, вкладка ФОРМУЛЫ, ВСТАВИТЬ ФУНКЦИЮ, выбрать функцию ДИСПР, в строке Число1 ввести В2:Н2;

В) выделить В5, вкладка ФОРМУЛЫ, ВСТАВИТЬ ФУНКЦИЮ, выбрать функцию МИН, в строке Число1 ввести В2:Н2;

Г) выделить В6, вкладка ФОРМУЛЫ, ВСТАВИТЬ ФУНКЦИЮ, выбрать функцию МАКС, в строке Число1 ввести В2:Н2;

Д) выделить В7, ввести формулу =В6-В5;

Е) выделить В8, вкладка ФОРМУЛЫ, ВСТАВИТЬ ФУНКЦИЮ, выбрать функцию МОДА, в строке Число1 ввести В2:Н2 (результатом будет #Н/Д, т.к. в выборке нет повторяющихся значений).

2. Отформатируйте таблицу.

Задание 2. Отмечая время (с точностью до минут), которое токари бригады затратили на обработку одной детали, получили такой ряд данных: 30, 32, 32, 38, 36, 31, 32, 38, 35, 32, 40, 42, 36, 33, 35, 32, 32, 40, 38. Для полученного ряда данных найдите среднее арифметическое, дисперсию, размах, моду и медиану.

Методические указания: Для расчета медианы, сначала нужно упорядочить выборку, для этого, выделите А2:А21, вкладка ГЛАВНАЯ, СОРТИРОВКА И ФИЛЬТР, сортировка от А до Я.

Для расчета Ср.арифм., Моды, Дисперсии и Медианы использовать функции аналогично предыдущему заданию. Размах это разность между наибольшим и наименьшим значением. Рассчитать самостоятельно.

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

Задание 3. В организации вели ежедневный учет поступивших в течении месяца писем. В результате получили такой ряд данных: 39, 43, 40, 0, 56, 38, 24, 21, 35, 38, 0, 58, 31, 49, 38, 25, 34, 0, 52, 40, 42, 40, 39, 54, 0, 64, 44, 50, 38, 37, 32. Для полученного ряда данных найдите Среднее арифметическое, Дисперсию, Размах, Моду и Медиану.

Указания: Необходимо отсортировать от А до Я весь ряд данных. Все функции рассчитать самостоятельно из предыдущих заданий.

Задание 4. Ниже указана среднесуточная переработка сахара заводами сахарной промышленности некоторого региона: 12,2; 13,2; 13,7; 18,0; 18,6; 12,2; 18,5; 12,4; 14,2; 17,8. Найдите Среднее арифметическое, Дисперсию, Размах, Моду и Медиану.

Указания: Необходимо отсортировать от А до Я весь ряд данных. Все функции рассчитать самостоятельно из предыдущих заданий.

Задание 5. Подготовьте таблицу по образцу и рассчитайте самостоятельно по формулам Максимальную, Минимальную температуру, Размах и Среднее значение.

Контрольные вопросы:

1.      Что такое статистика?

2.      Какие показатели вычисляют для обработки статистических данных?

3.      Что такое Мода, Медиана, Дисперсия, Размах, Среднее арифметическое?