Лабораторная работа №7. Работа в среде Excel

Цель работы: Освоение технологии работы с электронными таблицами.

Краткая теория

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

Лабораторная работа №7. Работа в среде Excel - student2.ru

Рисунок 8. Окно электронной таблицы Excel.

Рабочее поле Excel – это электронная таблица, состоящая из строк и столбцов. Пересечение конкретного столбца и строки образует ячейку. Местоположение ячейки задается адресом, который образуется из имени столбца и номера строки, на пересечении которых находится эта ячейка, а также при необходимости в адрес указывается имя листа и имя книги. Наряду с понятием адреса в электронной таблице используется понятие ссылка. Ссылка – это элемент формулы и используется тогда, когда надо сослаться на какую-нибудь ячейку таблицы. В этом случае адрес будет использоваться в качестве ссылки.

Для просмотра электронной таблицы используются линейки прокрутки.

Для изменения установок среды Excel, что повлечет за собой изменение установок текущей (активной) книги, можно воспользоваться командой Сервис, Параметры, вкладка Общие. Кроме нее имеются другие вкладки: Вид, Вычисления, Правка, Цвет, Диаграмм, Списки, Переход.

К типовым технологическим операциям с рабочими книгами и листами относятся:

ü Создать (открыть, закрыть, сохранить) рабочую книгу – Файл, Создать (Открыть, Закрыть, Сохранить как);

ü Сохранить книгу, которая уже сохранялась –Файл, Сохранить или нажать на панели стандартная кнопку Сохранить;

ü Скрыть (показать скрытую) рабочую книгу – Окно, Скрыть (Отобразить);

ü Поиск файлов –Файл, Открыть, Найти, осуществить расширенный поиск файлов с помощью нажатия кнопки Отбор в окне Открытие документа;

ü Выделить рабочий лист – щелкнуть левой кнопкой мыши на ярлыке рабочего листа;

ü Вызов контекстного меню команд листа – щелкнуть правой кнопкой мыши на ярлыке листа;

ü Выделить несколько смежных рабочих листов – выделить первый и последний рабочие листы диапазона при нажатой клавише Shift;

ü Выделить несколько несмежных листов – выделить первый рабочий лист, а последующие листы выделять при нажатой клавише Ctrl;

ü Снять выделение рабочих листов – через контекстное меню команд листа выполнить команду Разгруппировать листы;

ü Вставить рабочий лист – выделить рабочий лист, перед которым нужно вставить новый лист, вызвать контекстное меню команд листа и выполнить команду Добавить;

ü Переименовать рабочий лист – Формат, Лист, Переименовать;

ü Удалить рабочий лист – Правка, Удалить лист;

ü Скрыть (показать скрытый) рабочий лист – Формат, Лист, Скрыть (Отобразить);

ü Переместить (скопировать) рабочий лист – Правка, Переместить (скопировать) лист.

Ход работы

Работа с таблицей

1. Запустить окно Excel – Пуск, Программы, MS Excel.

2. В ячейку B2 ввести текст: “Продажа видеокассет” (выделить нужную

ячейку щелчком мыши на ней и ввести текст, закончить текст нажатием Enter или щелчком мыши на другой ячейке).

3. В ячейки B3:В9 ввести следующие числа: 8, 13, 20, 14,11, 10, 15

4. Освоить выделение столбцов, строк, блоков ячеек и всего листа:

- выделить столбец A (щелкнуть мышью на заголовке столбца);

- выделить строку 2 (щелкнуть мышью на заголовке строки (слева));

- выделить группу отдельных ячеек (щелкнуть мышью на ячейках при нажатой клавише Ctrl);

- выделить блок ячеек D1:F10 (щелкнуть мышью на первой ячейке блока (D1) и при нажатой кнопке мыши протянуть выделение до последней ячейки блока (F10));

- выделить весь рабочий лист (щелкнуть в верхнем левом поле пересечения заголовков строк и столбцов).

5. Выполнить автоматическое заполнение блока C3:C9 числами 50:

- ввести в ячейку C3 число 50;

- захватить правый нижний угол ячейки C3 (курсор примет вид тонкого креста) и при нажатой левой кнопке мыши протянуть рамку на интервал C3:C9.

6. Выполнить заполнение блока A3:A9 порядковыми номерами (1,2,3…).

7. В ячейки A10:A21 введите названия месяцев (январь, февраль…), используя автозаполнение.

8. Очистить блок ячеек A10:A21- выделить и нажать Delete или Правка, Очистить.

9. Выполнить вставку строк и столбцов:

- вставить строку между второй и третьей (выделить третью строку и выполнить Вставка, Строки);

- вставить столбец между первым и вторым (выделить второй столбец и выполнить Вставка, Столбец).

10. В ячейки С3,D3,Е3 соответственно ввести текст: Кол-во,Цена,Сумма.

11. В ячейку В4 ввести текст:Понедельник.

12.Блок В4:В10 заполнить названиями дней недели (понедельник,вторник...), используя автозаполнение.

13.В ячейку Е4 ввести формулу С4*D4 ( набрать: = С4*D4).

14. Скопировать эту формулу в блок ячеек Е5:Е10:

-выделить ячейку Е4, затем Правка, Копировать

-выделить блок Е5:Е10,затем Правка, Вставить.

15.Изменить данные в ячейке D8 на 45

16. Отформатировать таблицу:

-увеличить высоту первой строки (в поле заголовка строки (слева) передвинуть границу строки с помощью мыши и выделить эту строку, затем Формат, Строка, Высота строки);

- изменить ширину столбцов А и В тремя разными способами (передвинуть границу столбца с помощью мыши; выделить столбец, затем Формат, Столбец, Ширина; выделить оба столбца, затем Формат, Столбец, Автоподбор ширины);

17.В столбцах “Цена” и “Сумма” задать денежный формат чисел:

-выделить блок D4:Е10;

-Формат, Ячейки, вкладка Число, Числовой Формат, Денежный;

-установить в качестве денежной единицы рубль;

-установить число десятичных знаков 0, затем нажать ОК.

18.Отформатировать заголовки таблицы, предварительно их выделив:

-в ячейке С3 установить жирный шрифт с подчеркиванием, размер 16;

-в ячейках В4:В10 установить шрифт курсив;

-в ячейках С3:Е3 установить жирный шрифт и центрирование.

19.Сохранить таблицу в папку “Группа (номер группы)” (Файл, Сохранить как…)

Вычисления в MS Excel

20. Перейти на лист 2 (щелкнуть на ярлычке нужного листа).

21. Создать таблицу:

Таблица 4. Продажа видеокассет

A	B	C	D	E	F	G
	Продажа видеокассет за 1 квартал

Жанр	Январь	Февраль	Март	Итог	Среднее	Процент
Боевики
Комедии
Мелодрамы
Мультфильмы

Всего по месяцам
Среднее по месяцам
Минимум по месяцам
Максимум по месяцам

22. В ячейках Е4:Е7 вычислить итоговую сумму продаж за квартал:

- щелкнуть на ячейке Е4, затем на панели инструментов щелкнуть на кнопке “автосумма” и нажать ENTER;

- ввести формулы в блок Е5:Е7. Вычислить среднее число продаж с помощью функции автозаполнения.

23.Вычислить суммы продаж по месяцам (В9:D9) и общую сумму (Е9) (в ячейке В9 вычислить сумму для блока В4:В7, затем выполнить автозаполнение блока С9:Е9.

24. Вычислить среднее число продаж по месяцам (В10: D10) и в целом за весь квартал (Е10):

- щелкнуть на ячейке Е10, затем на панели инструментов щелкнуть на кнопке Мастер функций;

- выбрать категорию “статистические” и функцию СРЗНАЧ;

- в поле Число 1 ввести аргумент В4:В7 и нажать ОК;

- выполнить автозаполнение блока С10:Е10.

25.Аналогично п.24 вычислить минимум (МИН) и максимум (МАКС) по месяцам и в целом за квартал.

26.В блоке F4:F7 вычислить среднее число продаж по каждому виду жанра.

27.Вычислить процент от общего числа продаж (блок G4:G7) ,т.е. каждое значение из столбца “Итог” разделить на общее число продаж из ячейки Е9:

- в ячейку G4 ввести формулу: =E4/$E$9, используя абсолютный адрес ячейки Е9, чтобы при копировании он не менялся;

- скопировать формулу из G4 в блок G5:G7, используя автозаполнение.

28. Для блока G4:G7 установить процентный формат чисел с числом десятичных знаков 1.

29. Просмотреть формулы в таблице (выполнить Сервис, Параметры, перейти на вкладку Вид, включить опцию Формулы, нажать ОК, отключить опцию просмотра формул).

30. Сохранить рабочую книгу.

31. Скопировать таблицу с листа 2 на лист 3:

- выделить таблицу (щелкнуть на А1, затем с нажатой [Shift] щелкнуть на G12);

- скопировать выделенный блок в буфер;

- открыть лист 3;

- вставить таблицу.

32. Выполнить очистку блока формул В9:D12.

33. Присвоить блоку данных В4:В7 название столбца Январь:

- выделить этот блок;

- выполнить Вставка, Имя, Присвоить;

- указать имя столбца данных и нажать ОК.

Аналогично блокам С4:С7 и D4:D7 присвоить имена Февраль и Март.

34. В ячейки В9:В12 ввести расчетные формулы, используя в качестве аргументов названия месяцев. Например, СУММ (Январь). Блок С9:Е12 заполнить формулами с помощью автозаполнения.

35. Отформатировать таблицу:

- изменить цвет заголовка (синий) и названий жанров (лиловый) (выделить нужные ячейки, затем Формат, Ячейки, Вид или нажать кнопку А на панели форматирования);

- в блоке F4:F7 установить числовой формат чисел с числом десятичных знаков 1;

- изменить ширину столбцов, которые не видны полностью;

- выполнить обрамление таблицы двойной линией (Формат, Ячейки, перейти на вкладку Граница).

36. Выполнить условное форматирование таблицы, а именно: в блоке данных В4:D7 выделить зеленым цветом ячейки с числом продаж больше 80 видеокассет. Для этого:

- выделить блок В4:D7;

- выполнить Формат, Условное форматирование;

- во втором поле списка выбрать оператор «больше»;

- в третьем поле ввести значение 80;

- нажать кнопку Формат и выбрать зеленый цвет ячеек, отвечающих этому условию;

- нажать ОК.

Аналогично выполнить условное форматирование блока данных В4:D7, выделив красным цветом ячейки с числом продаж меньше 50. Для добавления нового условия форматирования надо нажать кнопку А также.

37. Изменить данные в блоке В4:D7. Защитить лист 3 от изменения (Сервис, Защита, Защитить лист, ОК).

38. Изменить данные в блоке В4:D7. Снять защиту листа. Сохранить книгу. Закрыть MS Excel.

Задание

1. Ознакомиться с краткой теорией.

2. Выполнить работу по предложенной технологии.

3. Ответить на контрольные вопросы.

4. Оформить отчет.

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

1. Для чего используют программу MS Excel?

2. Как называются документы, созданные в среде MS Excel7

3. Для чего рабочая книга разделяется на листы?

4. Назовите элементы электронной таблицы?