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

Задание 1: Установить свойства ячеек и ввести соответствующие данные.

Цель: Научиться устанавливать свойства ячеек, вводить и редактировать данные ячеек электронной таблицы.

Выполнение работы:

1. Запустить табличный процессор, выполнив команду Пуск - Все программы -Microsoft Office - Microsoft Office Excel.

2. Создать новую рабочую книгу MS Excel и сохранить книгу, используя пункт меню Файл команду Сохранить как … на съемном носителе под именем ЛР2_Фамилия.

3. Создать таблицу в книге в соответствии с таблицой 2.

4. В первой ячейке ввести текст (рисунок 18): №. Во второй ячейке первой строки ввести текст: № зачетной книжки. В третьей ячейке первой строки ввести текст: № группы. В четвертой ячейке первой строки ввести текст: Фамилия. В пятой ячейке первой строки ввести текст: Имя. В шестой ячейке первой строки ввести текст: Отчество. В седьмой ячейке первой строки ввести текст: Математика. В восьмой ячейке первой строки ввести текст: Физика. В девятой ячейке первой строки ввести текст: Философия. В десятой ячейке первой строки ввести текст: Средний балл. В одиннадцатой ячейке первой строки ввести текст: Стипендия.

Рисунок 18 – Ввод данных в таблицу

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

Таблица 2 – Задание

№п/п	№ зачетной книжки	№ группы	Фамилия	Имя	Отчество	Математика	Физика	Философия	Средний балл	Стипендия
1	2	3	4	5	6	7	8	9	10	11
1	12350	1192	Артемов	Павел	Николаевич	5	5	5
2	12351	1193	Артемов	Никита	Николаевич	4	4	4
3	12361	1193	Борзова	Ирина	Олеговна	4	5	3
4	12360	1190	Дергунова	Анна	Кирилловна	4	4	5
5	12359	1192	Дернова	Мария	Михайловна	5	5	5
6	12349	1190	Дружинина	Наталья	Сергеевна	5	5	2
7	12358	1191	Дурягин	Иван	Иванович	3	3	5
8	12357	1192	Железнов	Петр	Петрович	4	4	4
9	12345	1190	Иванов	Иван	Иванович	5	5	5
10	12352	1191	Краснова	Мария	Игоревна	3	3	4
11	12355	1193	Ладнова	Катерина	Андреевна	5	4	2
12	12356	1190	Ларина	Анна	Александровна	5	5	4
13	12354	1190	Любимов	Илья	Николаевич	3	3	3
14	12348	1193	Некрасов	Андрей	Алексеевич	4	3	4
15	12346	1191	Петров	Петр	Петрович	3	2	4
16	12353	1192	Погорелов	Андрей	Андреевич	4	4	4
17	12347	1192	Сидоров	Алексей	Алексеевич	5	4	4
18	12364	1193	Щеглова	Юлия	Николаевна	3	3	5
19	12362	1191	Юркина	Светлана	Николаевна	5	3	2
20	12363	1192	Яхонтов	Матвей	Иванович	2	4	5

6. В диалоговом окне на вкладке Число выбрать тип данных: Текстовый. Перейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру, установить Отображение: Переносить по словам. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

 

м

 

 

 

Рисунок 19 – Вид окна Формат ячеек

7. Выбрать пункт меню Главная, выполнить команду Формат. Из вложенного меню выбрать команду – Автоподбор ширины столбца.

8. Ввести во вторую строку в первую ячейку (А2) цифру 1 и в третью строку в первую ячейку (А3) цифру 2. Выделить ячейки А2 и А3 мышью (рисунок 20). Для автоматического заполнения ячеек столбца А навести курсор мыши на правый нижний угол выделенного диапазона до появления маленького черного крестика и, нажав левую кнопку мыши, протянуть вниз по столбцу А до ячейки А11. Выделить заполненные ячейки первого столбца. Выбрать пункт меню Главная, установить шрифт: Times New Roman, размер (кегль): 12 и выполнить команду Выравнивание по центру (рисунок 20).

Рисунок 20 - Вид листа книги

9. В ячейки второго столбца (В2-В11) № зачетной книжки ввести 10 произвольных значений номеров зачетных книжек (пример данных приведен в таблице 2). Выделить заполненные ячейки второго столбца. Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек (рисунок 21). В диалоговом окне на вкладке Число выбрать тип данных: Числовой, в поле Число десятичных знаков указать 0. Перейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

 

 

Рисунок 21 – Вид окна Формат ячеек

10. В ячейки третьего столбца (С2-С11) № группы ввести 10 произвольных значений номеров групп (пример данных приведен в таблице 2). Выделить заполненные ячейки третьего столбца. Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек. В диалоговом окне на вкладке Число выбрать тип данных: Числовой, в поле Число десятичных знаков указать 0. Перейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

11. В ячейки четвертого, пятого и шестого столбцов Фамилия (D2-D11), Имя (E2-E11), Отчество студентов (F2-F11) ввести 10 произвольных значений фамилий, имен и отчеств студентов (пример данных приведен в таблице 2). Выделить заполненные ячейки четвертого, пятого и шестого столбцов. Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек. В диалоговом окне на вкладке Число выбрать тип данных: Текстовый. Перейти на вкладку Выравнивание, установить Отображение: Переносить по словам. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

12. Выделить ячейки седьмого, восьмого и девятого столбцов Математика (G2-G11), Физика (H2-H11), Философия (I2-I11). Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек. В диалоговом окне на вкладке Число выбрать тип данных: Числовой, в поле Число десятичных знаков указать 0. Перейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

13. Выделить ячейки седьмого, восьмого и девятого столбцов Математика (G2-G11), Физика (H2-H11), Философия (I2-I11). Выбрать пункт меню Данные, команду Проверка данных (рисунок 22). В диалоговом окне Проверка вводимых значений выбрать в разделе Условие проверки Тип данных: Целое, Значение: Между, Минимум: 2, Максимум: 5. Перейти на вкладку Сообщение об ошибке. В диалоговом окне Вид сообщения выбрать: Предупреждение. В поле Заголовок ввести текст: Неверный диапазон данных. В поле Сообщение ввести текст: Введите данные в диапазоне от 2 до 5. Нажмите кнопку ОК.

14. В ячейки седьмого, восьмого и девятого столбцов Математика (G2-G11), Физика (Н2-Н11), Философия (I2-I11) ввести 10 произвольных значений оценок по дисциплинам (пример данных приведен в таблице 2).

 

 

Рисунок 22 – Вид окна Проверка вводимых значений

15. Выделить ячейки десятого столбца Средний балл (J2-J11). Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек. В диалоговом окне на вкладке Число выбрать тип данных: Числовой, в поле Число десятичных знаков указать: 2. П ерейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

16. Выделить ячейки десятого Средний балл (J2-J11) столбца. Выбрать пункт меню Данные, команду Проверка данных. В диалоговом окне Проверка вводимых значений выбрать в разделе Условие проверки Тип данных: Действительное, Значение: Между, Минимум: 2, Максимум: 5. Перейти на вкладку Сообщение об ошибке. В диалоговом окне Вид сообщения выбрать: Предупреждение. В поле Заголовок ввести текст: Неверный диапазон данных. В поле Сообщение ввести текст: Диапазон данных от 2 до 5. Нажмите кнопку ОК.

17. Выделить ячейки одиннадцатого столбца Стипендия (K2-K11). Вызвать контекстное меню, нажав правую кнопку мыши, выбрать пункт меню Формат ячеек. В диалоговом окне на вкладке Число выбрать тип данных: Денежный, в поле Число десятичных знаков указать 2, в обозначении выбрать: р. П ерейти на вкладку Выравнивание, установить Выравнивание по горизонтали: по центру. Перейти на вкладку Шрифт, установить шрифт: Times New Roman, начертание: обычный, размер (кегль): 12, цвет текста: черный. Нажать кнопку OK.

18. Выделить ячейки одиннадцатого столбца Стипендия (K2-K11). Выбрать пункт меню Данные, команду Проверка данных. В диалоговом окне Проверка вводимых значений выбрать в разделе Условие проверки Тип данных: Действительное, Значение: Между, Минимум: 0, Максимум: 5000. Перейти на вкладку Сообщение об ошибке. В диалоговом окне Вид сообщения выбрать: Предупреждение. В поле Заголовок ввести текст: Неверный диапазон данных. В поле Сообщение ввести текст: Диапазон данных от 0 до 5000. Нажмите кнопку ОК.

19. Выделить всю таблицу и установить границы ячеек, выбрав пункт меню Главная, команду Ячейки: Формат: Формат ячеек (рисунок 23). Перейти на вкладку Границы, установить: внутренние и внешние границы. Нажать кнопку OK.

Рисунок 23 – Вид окна Формат ячеек

Задание 2: Создать формулы и функции для обработки данных в электронной таблице.

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

Выполнение работы:

1. Перейти во вторую ячейку десятого столбца Средний балл (J2). Перейти в Строку формул (рисунок 24). Поставить знак равенства (=), выбрать пункт меню Формулы, команду Вставить функцию, выбрать категорию Полный алфавитный перечень, выбрать функцию СРЗНАЧ. Нажать кнопку ОК. В диалоговом окне функции в поле Число 1 мышью указать диапазон ячеек четвертого столбца: ячейка7 - ячейка9 (G2-I2). Нажать кнопку ОК.

 

 

Рисунок 24 – Вид окна Вставка функции

2. Выделить вторую ячейку десятого столбца Средний балл (J2), подвести курсор мыши к правому нижнему углу ячейки до появления черного крестика, нажать левую кнопку мыши и протянуть (транспонировать) формулу до окончания столбца - ячейки (J11).

3. Перейти во вторую ячейку одиннадцатогостолбца Стипендия (K2). Перейти в Строку формул. Поставить знак равенства (=), выбрать пункт меню Формулы, команду Логические: ЕСЛИ (рисунок 25).

Рисунок 25 – Вид меню Функции

4. В диалоговом окне Аргументы функции Если в поле Лог_выражение вставить, используя выпадающий список функций, логическую функцию И (рисунок 26).

Рисунок 26 – Вид окна Аргументы функции Если

5. В окне Аргументы логической функции И (рисунок 27)ввести Логическое_значение 1: G2=5, Логическое_значение 2: H2=5, Логическое_значение3: I2=5. Нажать кнопку ОК.

Рисунок 27 – Вид окна Аргументы функции И

6. В командной строке функции (рисунок 28) проставить точку с запятой (;), написать число 5000 и проставить точку с запятой (;).

Рисунок 28 – Вид командной строки

7. Далее в командной строке функции вставить, используя выпадающий список функций, логическую функцию Если (рисунок 29).

Рисунок 29 – Вид командной строки

8. В диалоговом окне Аргументы функции Если в поле Лог_выражение вставить, используя выпадающий список функций, логическую функцию И. В окне Аргументы логической функции И (рисунок 30)ввести Логическое_значение 1: G2>3, Логическое_значение 2: H2>3, Логическое_значение3: I2>3. Нажать кнопку ОК.

Рисунок 30 – Вид окна Аргументы функции И

9. В командной строке функции (рисунок 31) проставить точку с запятой (;), написать число 3500, проставить точку с запятой (;) и написать число 0. Нажать кнопку ОК.

10. Выделить вторую ячейку одиннадцатогостолбца Стипендия (K2), подвести курсор мыши к правому нижнему углу ячейки до появления черного крестика, нажать левую кнопку мыши и протянуть (транспонировать) формулу до окончания столбца - ячейки (K11).

Рисунок 31 – Вид командной строки

 

11. Выделить четвертый столбец Фамилия студента. Выбрать пункт меню Главная, команду Сортировка и фильтр и пункт Сортировка от А до Я. В диалоговом окне нажать кнопку Сортировка.

12. Выделить третий столбец Группа. Выбрать пункт меню Главная, команду Сортировка и фильтр и команду Фильтр. В заголовке второго столбца в списке последовательно отфильтровать студентов по номерам групп (рисунок 32).

Рисунок 31 – Вид окна выбора параметра фильтра

13. В диалоговом окне Фильтра установить значение Выделить все.

1. Сохранить файл на съемном носителе.

Задание 3: Создать сводную таблицу.

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

Выполнение работы:

1. Выделить таблицу. Выбрать пункт меню Вставка команду Сводная таблица. В окне создания сводной таблицы указать Куда следует поместить отчет сводной таблицы: На новый лист (рисунок 32).

2. В окне Поля сводной таблицы выделить мышью поле № группы и перетащить в раздел СТРОКИ и в раздел ∑ ЗНАЧЕНИЯ (рисунок 33). Выделить мышью поле Стипендия и перетащить в раздел КОЛОННЫ.

3. Из списка раздела ∑ ЗНАЧЕНИЯ выбрать команду Параметры полей значений (рисунок 34). В окне Параметры поля по значению выбрать: Количество.

Рисунок 32 – Вид окна Создание сводной таблицы

Рисунок 33 – Вид окна выбора параметрв таблицы

 

 

 

 

Рисунок 34 – Вид окна выбора параметров поля значений

4. Выделить ячейку названия строк, ввести текст: Номер групп. Выделить ячейку Названия столбцов и ввести текст: Количество студентов, получающих стипендию.

5. Сохранить файл на съемном носителе.

 

Задание 4: Создать диаграмму.

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

Выполнение работы:

1. Выделить сводную таблицу. Выбрать пункт меню Вставить, команду Диаграммы: Вставить гистрограмму (диаграмму).

2. Установить параметры диаграммы (название диаграммы, название осей).

3. Сохранить файл на съемном носителе.

 

Задание 5: Установить защиту информации от несанкционированного доступа.

Цель: Научиться защищать данные от модификации и уничтожения.

Выполнение работы:

1. Установить защиту листа или книги, используя команды пункта меню Рецензирование.

2. Сохранить файл на съемном носителе.