INFORMATIKA / Лабораторные работы / Лабораторная работа №2

Лабораторная работа №2 Тема: Обработка числовой информации. Электронные таблицы.

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

Задание 1: В MS Office Excel 2007 создайте расчетную таблицу. Выполните расчеты. Постройте диаграмму по данным и итогам расчетов.

Образец выполнения задания:

Задача. Численность безработных граждан составила на 4-й неделе сентября 1 619 363 человека. Рассчитайте численность безработных граждан на 1-й, 2-й, 3-й неделе сентября, если известно, что на 4-й неделе она снизилась на 1,8% по сравнению с 3-й неделей, на 3-й неделе снизилась на 1,1% по сравнению со 2-й, на 2-й неделе снизилась на 1,2% по сравнению с 1-й. Постройте диаграмму, отражающую динамику уменьшения численности безработных граждан в сентябре.

1. Оформим в MS Office Excel 2007 расчетную таблицу (см. Рисунок 1).

Рисунок 1. Оформление таблицы		Рисунок 2. Расчетные формулы
Внесем расчетные формулы (см. Рисунок 2). Построим диаграмму (см. Рисунок 3).	Рисунок 3. Оформление диаграммы

Упражнение 1. Построение расчетной таблицы, простейшие вычисления

1. Откройте Microsoft Excel.

2. На Листе 1 создайте таблицу (см. Рис.1):

   Рис.4. Таблица «Продажи»

   Рис.5. Оформление таблицы

3. Рассчитайте итоги по кварталам (сумма Север и Юг).

4. Оформите таблицу, используя стили ячеек (см. Рис.2).

5. Над таблицей введите заголовок Продажа по регионам. Разместите заголовок по центру относительно таблицы.

6. Создайте верхний колонтитул ООО «Регион Плюс».

7. Создайте нижний колонтитул, разместите в нем номер страницы.

8. Постройте диаграмму (см. Рис.3) по области данных: A2:B4 + E2:E4. Разместите диаграмму на отдельном листе.

   Рис.6. Диаграмма «Продажи»

   Рис.7. Относительная адресация

9. Переименуйте Лист 1 – Продажи.

10. Переименуйте лист с диаграммой – Диаграмма продажи.

Упражнение 2. Относительная адресация.

1. Перейдите на Лист 2.

2. Подготовьте таблицу (см. Рис.4).

3. Используя относительную адресацию, выполните расчеты в таблице (Всего = Цена*Количество).

4. Рассчитайте сумму налога: общая сумма всех товаров, умноженная на торговый налог.

5. Переименуйте Лист 2 – Товары.

Упражнение 3. Абсолютная адресация.

1. Измените таблицу, добавив столбец Сумма налога на товар (см. Рис.5).

   Рис.8. Абсолютная адресация

   Рис.9. Смешанная адресация

2. Рассчитайте сумму налога на товар, используя абсолютную адресацию. Формула для расчета: Всего * Торговый налог

Упражнение 4. Смешанная адресация.

1. На Листе 3 подготовьте таблицу (см. Рис.6).

2. Используя смешанную адресацию, рассчитайте процент от суммы.

3. Переименуйте Лист 3 – Процент.

Задание 2: В MS Office Excel 2007 создайте расчетную таблицу. Выполните расчеты, используя стандартные функции СЧЁТЕСЛИ, СУММЕСЛИ, ЕСЛИ, И, ИЛИ.

Образец выполнения задания:

Имеется список сотрудников отдела с указанием занимаемых должностей. Необходимо подсчитать количество сотрудников, занимающих определенную должность (см. Рис. 100).

Рис. 10. Использование функции СЧЁТЕСЛИ

1. Построим расчетную таблицу и внесем данные.

2. Выделим ячейку F4.

3. Перейдем на вкладку Формулы. В группе Библиотека функций выберем категорию Другие функции – Статистические, а в ней функцию СЧЁТЕСЛИ.

4. Заполним аргументы функции (см. Рис. 11).

Рис. 11. Аргументы функции СЧЁТЕСЛИ

Диапазон – диапазон, в котором подсчитывается количество непустых ячеек, удовлетворяющий критерию (условию). В примере это столбец С, в котором перечислены должности сотрудников.

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

5. Выполним автозаполнение формулы в диапазон F5:F7.

Упражнение 5. Использование при расчетах стандартных функций

1. Создайте новую рабочую книгу.

2. На Листе 1 подготовьте таблицу (см. Рис. 12):

Рис. 12. Оценки

3. Посчитайте количество выставленных пятерок, четверок, троек, двоек и единиц, используя функцию СЧЁТЕСЛИ.

4. Переименуйте лист – Тестирование.

Упражнение 6. Использование нескольких функций в одной формуле

Задание: Необходимо посчитать среднее значение отрицательных и среднее значение положительных элементов в столбце отклонение.

1. На Листе 3 оформите следующую таблицу (см. Рис. 13):

Рис. 13. Среднее отклонение

2. Рассчитайте среднее положительное отклонение (сумма положительных отклонений деленная на их количество) и среднее отрицательное отклонение (сумма отрицательных отклонений деленная на их количество), используя стандартные функции СУММЕСЛИ и СЧЁТЕСЛИ.

3. Переименуйте лист – Среднее отклонение.

Упражнение 7. Сложные условия

Для создания сложных условий можно использовать функции И, ИЛИ.

Части условий отделяются друг от друга точкой с запятой.

1. Создайте чистый лист.

2. Подготовьте следующую таблицу (см. Рис. 14):

Рис. 14. Сложные условия

3. Используя функции ЕСЛИ, И, проверьте знак всех чисел в диапазоне B3:B5 и выведите сообщение в ячейку D3: «Все отклонения положительные» (если все числа больше нуля) или «Не все отклонения положительные» (если не все числа больше нуля).

4. Используя функции ЕСЛИ, ИЛИ, проверьте знак всех чисел в диапазоне B3:B5 и выведите сообщение в ячейку D4: «Есть отрицательные отклонения» (если хотя бы одно число меньше нуля) или «Нет отрицательных отклонений» (если нет чисел меньших нуля).

5. Переименуйте лист – Сложные условия.

Упражнение 8. Вложение функций

Задание: Производится уценка товара. Если разность между текущей датой и датой привоза:

• больше или равна 30, то уценка идет на 5% от начальной цены;

• больше или равна 60, уценка идет на 10% от начальной цены;

• больше или равна 90, уценка идет на 18% от начальной цены.

Необходимо посчитать текущую цену товара.

1. Создайте новый лист в рабочей книге.

2. Подготовьте следующую расчетную таблицу (см. Рис. 15):

Рис. 15. Товары

3. Используя вложение функций ЕСЛИ друг в друга выполните расчет текущей цены товара.

4. Переименуйте лист – Товары.

5. Сохраните рабочую книгу в своей папке.

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

1. Как создать расчетную формулу в Microsoft Excel?

2. Какие виды адресации ячеек используются в Microsoft Excel?

3. Для каких целей используется относительная адресация ячеек?

4. Когда используются абсолютные адреса ячеек?

5. Когда используются смешанные адреса?

6. Перечислите аргументы функции СЧЁТЕСЛИ.

7. Перечислите аргументы функции ЕСЛИ.

8. Как вложить функции друг в друга?