ВЫЧИСЛЕНИЯ В ПРЕДЕЛАХ ЛИСТА. БАЗОВЫЕ ОПЕРАЦИИ

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

Задание

1. Копирование таблицы Кафе "РОЗА" и обработка ее данных

1.1. Создайте в своей папке книгу MS Excel с именем Фамилия_2_n (где Фамилия – Ваша фамилия, n – номер Вашего индивидуального варианта).

1.2. Переименуйте Лист1 на Кафе "РОЗА" и измените цвет ярлычка на оранжевый.

1.3. Сделайте копию листа Кафе "РОЗА" из файла Фамилия_1_n.xls в файл Фамилия_2_n.xls.

1.4. Вставьте перед столбцом Продажи за 2014 г. столбец Продажи за 2013 г. и заполните его числами. Формат ячеек – Денежный с обозначением денежной единицы в тенге.

1.5. В ячейку D12 введите текст "Итого", а правее в ячейку Е12 (под первым числовым столбцом) вставьте формулу вычисления суммы числовых значений этого столбца.

1.6. Для вычисления суммарного значения в соседних столбцах F и G выполните автозаполнение, распространив указанную формулу на ячейки F12 и G12. Задайте для ячеек D12:G12 границы и установите полужирное начертание шрифта (рис. 2.1).

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

1.7. В столбце Н сформируйте вычисление суммарного значения продаж за три года.

1.8. В строке 13 сформируйте вычисление среднего значения за каждый год (см. рис. 2.1).

1.9. Подобным образом ниже в отдельных строках найдите максимальное и минимальное значение в каждом из числовых столбцов (см. рис. 2.1).

1.10. Выделите все заполненные ячейки и задайте соответствующее предыдущим ячейкам форматирование и границы ячеек (см. рис. 2.1).

1.11. Создайте верхний колонтитул со своей фамилией и названием группы (алгоритм см. в п. 2.6 в предыдущей лабораторной работе).

1.12. Для ячеек D6:G11 создайте диаграмму с типом Обычная гистограмма.

1.13. Измените по своему вкусу способ заливки рядов данных в диаграмме. Добавьте заголовок диаграммы – Сравнение продаж в кафе "Роза".

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

Рис. 2.2. Вид таблицы с диаграммой

1.15. Защитите данные листа Кафе "РОЗА".

1.16. Выпишите в свой протокол четыре формулы с разными функциями, применяемыми на этом листе.

1.17. Сохраните файл Фамилия_2_n.xls.

Копирование таблицы ПродИрис и обработка ее данных

2.1. Переименуйте Лист2 файла Фамилия_2_n.xls на ПродИрис.

2.2. Скопируйте на этот лист одноименную таблицу ПродИрис, созданную в предыдущей лабораторной работе (файл Фамилия_1_n.xls).

2.3. Перед столбцом Цена вставьте столбец Количество (столбец D) и заполните его целочисленными значениями (от 1 до 5).

2.4. После столбцов Количество и Цена вставьте столбец Стоимость (столбец F) (рис. 2.3). В ячейку F3 введите формулу = D3 * E3 и выполните автозаполнение этой формулы на ячейки F4:F16.

2.5. С помощью условного форматирования задайте формат:

1) красный, полужирный формат шрифта для числовых ячеек столбца Цена со значениями больше 10000, т. е. условное форматирование числовых ячеек;

2) голубая заливка для ячеек столбца Наименование проданного товара со значениями Клавиатура (рис. 2.3), т. е. условное форматирование текстовых (нечисловых) ячеек;

3) зеленый цвет шрифта с желтой заливкой фона для ячеек со стоимостью возвращенных товаров, т. е. условное форматирование одних ячеек по содержимому других ячеек.

Рис. 2.3. Вид таблицы ПродИрис с результатами вычислений, условным форматированием и примечаниями

2.6. Ниже таблицы в строке 17 объедините ячейки А17:Е17 и впишите в них текст "Суммарная стоимость", а в ячейке F17 создайте формулу:

= СУММ(F1:F16)

2.7. В ячейке G17 создайте формулу:

= СУММПРОИЗВ(D3:D16; Е3:Е16)

Убедитесь в том, что результаты обеих формул в ячейках F17 и G17 совпали.

Таким образом, с помощью функции СУММПРОИЗВ можно было сразу вычислить суммарную стоимость, предварительно не создавая столбец с вычислением стоимости.

2.8. Объедините ячейки А18:Е18 и впишите в них текст "Стоимость проданных", а ниже в объединенные аналогичным образом ячейки А19:Е19 – текст "Стоимость возвращенных". В ячейке F18 создайте формулу:

= СУММЕСЛИ(G3:G16; "Продано"; F3:F16)

Аналогичным образом сформируйте в ячейке F19 формулу для вычисления суммарной стоимости всех возвращенных товаров (см. рис. 2.3).

2.9. Создайте примечание к ячейке Н2 с текстом "Автоматически выделяются ячейки с товаром Клавиатура" (см. рис. 2.3).

2.10. Создайте примечание к ячейке А2 с указанием Вашей группы и фамилии, имени, отчества, например: "Автор работы студент группы ИнУ-202 Иванов Иван Иванович" (см. рис. 2.3).

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

2.12. Выпишите в свой протокол формулы с разными функциями, применяемыми на этом листе.

2.13. Сохраните файл Фамилия_2_n.xls и закройте его.

2.14. Устно проработайте ответы на все контрольные вопросы и письменно ответьте на контрольный вопрос, соответствующий номеру Вашего варианта.__

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

1) На какие категории подразделяются функции в MS Excel?

2) Для чего предназначена кнопка  на панели инструментов?

3) Что обозначает запись =СУММ(В3:С8)?

4) Как создать примечание к ячейке?

5) В ячейку С7 ввели формулу =А7+В7. Затем эту формулу скопировали в ячейку С8. Какая формула содержится в ячейке С8?

6) В ячейку С5 ввели формулу =A5+B5. Затем эту формулу скопировали в ячейку D5. Какая формула содержится в ячейке D5?

7) С какого символа начинается ввод формулы? Приведите примеры формул.

8) Как скопировать формулу в смежные ячейки?

9) Что отображается в ячейке после ввода в нее формулы? Как увидеть формулу?

10) Что происходит со ссылками (адресами) в формуле при автозаполнении (копировании) формулы на смежные ячейки?

11) Для каких целей используют условное форматирование?

12) Какие параметры форматирования можно задавать при установке условного форматирования?

13) Можно ли обычными средствами форматирования изменять оформление данных и ячеек, для которых установлен условный формат?

14) Как вычислить среднее значение с помощью функции Excel?

15) Можно ли вычислить среднее значение, не используя функцию СРЗНАЧ? В случае утвердительного ответа приведите пример.

16) Что вычисляет функция СЧЁТЕСЛИ? Приведите примеры её применения.

17) Какое предназначение кнопки  левее строки формул? Какая команда дублирует действие этой кнопки?

18) Как вставить новый столбец между заполненными столбцами таблицы? Формат какого из столбцов таблицы получит вставленный столбец?

19) Что обозначает двоеточие в формулах между ссылками на ячейки? Приведите пример такой формулы и объясните ее действие.

20) Как выполнить разметку страницы таким образом, чтобы вся информация на ней занимала только одну страницу?

21) Приведите два способа выделения всего содержимого листа.

22) Что находит функция СУММПРОИЗВ? Приведите примеры её применения.

23) Объясните допущенные в формулах ошибки:

1) =СРЕДНЕЕ(E7:E11);

2) =СУМ(В2:В5); 3) СУММ(В2:В5); 4) =MIN(E1:E7); 5) МАКСИМУМ(E2;E11).

24) Как в Excel создать диаграмму?

25) Перечислите известные Вам типы диаграмм.

26) Как изменить тип уже построенной диаграммы?

27) Как добавить название диаграммы и подписи осей?

28) Какое предназначение легенды диаграммы?

29) К какой категории относятся функции для нахождения максимального, минимального, среднего значения в списке?

30) Объясните на примере: как выполнить условное форматирование одних ячеек по содержимому других ячеек?__

ПРАКТИЧЕСКАЯ РАБОТА № 3