Лабораторная работа по MS Excel
методическая разработка по информатике и икт (10 класс) по теме

Лабораторная работа

«Организация расчетов в табличном процессоре MS Excel.

Относительная и абсолютная адресация»

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

 Ознакомление с методами сортировки и фильтрации данных

Краткие теоретические сведения.

1. Способы выделения фрагментов электронной таблицы

2. Ввод данных в ячейки.

1. При вводе данных в ячейки электронной таблицы их необходимо зафиксировать, т.е. сообщить программе об окончании ввода.

Зафиксировать данные можно одним из способов:

• нажать клавишу Enter;
• щелкнуть мышью подругой ячейке;
• перейти к другой ячейке с помощью клавиш управления курсором;
• щелкнуть мышью на кнопке Ввод в строке формул
• щелкнуть мышью на квадратике в нижнем правом углу ячейки.

2. Ввод формул всегда начинается со знака равенства =
3. Адреса ячеек нужно вводить без пробелов и по латинскому регистру.
4. Адреса ячеек можно водить в формулы без использования клавиатуры, а просто щелкая по ним мышью.
5. Для ввода данных и стандартных рядов можно использовать маркер автозаполнения – квадрат в правом нижнем углу ячейки. Выделить ячейку. Установить указатель мыши на маркер и, удерживая нажатой левую клавишу мыши, переместиться в заданном направлении.

1. Редактирование данных.

1. Редактирование данных в ячейке осуществляется в строке формул при выделенной ячейке.
2. Требуемого расположения текста в ячейках добиваются с помощью меню ФОРМАТЯчейки вкладка ВЫРАВНИВАНИЕ

• Расположение текста Выравнивание  по горизонтали и по вертикали
• Ориентация текста  вертикально, под заданным углом
• Разделения текста на строки на устанавливают команду Переносить по словам.
• Расположение в нескольких ячейках Объединение ячеек 

Задание 1. Освоение приемов работы с электронными таблицами

1. Запустите табличный процессор Excel.
2. Опробуйте все приведенные выше в таблице способы выделения фрагментов электронной таблицы.
3. Выделите столбец D.
4. Выделите вместе столбцы B,C,D. Отмените выделение.
5. Выделите блок C4:F9 с помощью мыши. Отмените выделение.
6. Выделите блок А2:Е11 при нажатой клавише Shift и клавиш управления курсором.
7. Выделите одновременно несмежные блоки А5:В5, D3:D15, Н12, F5:G10.
8. Выделите весь рабочий лист 2. Отмените выделение.
9. Вставьте 5 новых листов в таблице с помощью команды меню ВставкаЛист.
10. Вернитесь к Листу 1. С помощью контекстного меню присвойте ему имя Таблица 
11. Сохраните работу в своей папке в виде файла с именем Таблица1.xls
12. В ячейку А1 Листа 2 введите текст: Зеленоград.
13. В ячейку В1 введите число – 1959 (год основания Зеленограда).
14. В ячейку С1 введите число – текущий год.

Обратите внимание на то, что в Excel текстовые данные выравниваются по левому краю, а числа и даты – по правому краю.

15. Выделите ячейку D1, введите формулу для вычисления возраста Зеленограда: =С1-В1
16. Удалите содержимое ячейки D1 и повторите ввод формулы с использованием мыши.
17. Измените ширину столбца А, перетащив мышью правый разделитель в строке заголовка столбца или дважды щелкнув по разделителю столбца.
18. Для изменения ширины столбца используют также команды меню ФОМАТСтолбецШирина (Автоподбор ширины или Стандартная ширина).
19. Измените высоту строки 2 с помощью мыши и сделайте ее равной 30 пт. Верните первоначальную высоту строки (12, 75 пт.).
20. С помощью команды меню ФОРМАТСтрокаВысота строки измените высоту строки 5 до 40 пт. Верните высоту строки, используя команду Автоподбор высоты.
21. В ячейку А2 введите текст Мой возраст.
22. В ячейку В2 введите свой год рождения.
23. В ячейку С2 введите текущий год.
24. Введите в ячейку D2 формулу для вычисления вашего возраста.
25. Очистите ячейку D2 командой меню ПравкаОчиститьвсе.
26. Выделите ячейку D1. Укажите мышью на маркер заполнения – маленький квадрат в правом нижнем углу ячейки. После того, как курсор превратится в черное перекрестие, нажмите левую клавишу мыши и, удерживая ее нажатой, переместите маркер вниз, в ячейку D2. Что произошло? (Вы скопировали формулу из ячейки D1 в ячейку D2)
27. Выделите ячейку D2. Обратите внимание на то, что в строке ввода высвечивается формула для расчета, а в самой ячейке отображается значение – число, полученное в результате вычисления по этой формуле.
28. Определите свой возраст в 2024 году. Для этого замените год в ячейке С2 на 2025. Обратите внимание, что при вводе новых данных пересчет в таблице произошел автоматически.
29. В ячейке А3 введите текст: Количество прожитых дней.
30. С помощью команды меню ФорматСтолбецШирина (или Автоподбор ширины) отрегулируйте ширину столбца так, чтобы был виден весь текст.
31. В ячейку В3 введите полную дату своего рождения в одном из следующих форматов: 1.04.1977, 1/04/77, 1 апрель 1977 или

1 апр. 77.

Если при вводе даты или числа вначале поставить пробел, кавычки или апостроф, то программа воспримет такие данные как текст и выровняет их по левому краю.

32. В ячейку С3 введите сегодняшнюю дату.
33. Скопируйте формулу из ячейки D2 в ячейку D3. Полученный результат – количество прожитых Вами дней.

Если результат в ячейке D3 представлен в виде даты, то с помощью команды меню ФорматЯчейка установите для этой ячейки Числовой формат.

34. Сохраните работу под тем же именем.
35. Отредактируйте текст в ячейке А1. Новый текст: Зеленоград – центр микроэлектроники.
36. С помощью комбинации клавиш Alt+Enter разделите текст на две строки
37. В ячейке А2 измените ориентацию текста – вертикально на 90 градусов
38. Выделите 1-ю строку. Командой меню ВставкаСтроки добавьте сверху новую строчку.
39. Скопируйте текст ячейки А2 в ячейку А1.
40. В ячейке А1 установите полужирное начертание шрифта и размер 12 пт.
41. Объедините ячейки А1, B1, C1, D1 и расположите текст в центре диапазона.
42. В ячейке А2 установите выравнивание по вертикали – по высоте.
43. Сделайте активным 3 лист.
44. В ячейку А1 введите число 1.
45. В ячейку В1 введите слово ПРИМЕР.
46. В ячейку С1 введите 2000.
47. Выделите блок А1:С1 и используя маркер заполнения размножьте его на 5 строк вниз.
48. В ячейку D5 введите слово ЯНВАРЬ и, используя автозаполнение, заполните ячейки на 4 вверх и на 5 строк вниз.
49. В ячейку Е5 введите сегодняшнюю дату и, используя автозаполнение, заполните ячейки на 4 вверх и на 5 строк вниз.
50. В ячейку F5 введите слово СРЕДА и, используя автозаполнение, заполните ячейки на 4 вверх и на 5 строк вниз.
51. В ячейку G5 введите число 1, в G6 – число 2. Выделите блок G5:G6 и, используя автозаполнение, заполните ячейки на 4 вверх и на 4 строки вниз.
52. Выделите столбец Н. Установите в нем процентный формат. В ячейку H5 введите 10%, в Н6 - 20% и, используя автозаполнение, заполните ячейки на 4 вверх и на 4 строки вниз.
53. Выделите столбец J. Установите в нем денежный формат. В ячейку J1 введите 5,00р., в J2 -20,00р. и, используя автозаполнение, заполните ячейки на 8 строк вниз.
54. Сохраните работу под тем же именем.

Задание 2. Создание таблицы и выполнение расчетов

1. Сделайте активным лист Таблица.
2. Создайте на нем следующую таблицу:

3. Используя кнопку Вставка функции и функцию СУММ в ячейке С12 вычислите суммарную площадь перечисленных стран ( =СУММ(С2:C11) )
4. Используя кнопку Автосумма вычислите в ячейке D12 общую численность населения перечисленных стран
5. Используя кнопку Вставка функции и функцию МИН в ячейке С13 определите минимальную величину площади

         (=МИН(С2:C11) )

6. Используя кнопку Вставка функции и функцию МАКС, в ячейке D13 вычислите максимальную численность населения
7. Для каждой страны вычислите:

• плотность населения, (=население/площадь), чел/кв.км;
• долю (в %) каждой страны от всего населения Земли. (=население страны/население мира).

При вычислении долей (в %) используйте формулу, содержащую в качестве делителя абсолютный адрес ячейки с числом, обозначающим количество населения Земли, - 5 292 000: $адрес столбца $адрес строки, например, =D2/$D$14.

Установите в столбце F процентный формат.

8. Используя кнопку Вставка функции и функцию СРЗНАЧ, в ячейке Е13 вычислите среднюю плотность населения.
9. С помощью кнопки уменьшите разрядность установите точность – 1 знак после запятой.
10. Выделите созданную таблицу за исключением последних трех строк и скопируйте её ниже на этом же листе и на листы с 4-го по 8-ой.
11. Отформатируйте исходный экземпляр таблицы – задав границы, выбрав цвет шрифта и заливку ячеек.
12. Отформатируйте второй экземпляр с помощью команды меню ФорматАвтоформат.
13. Сохраните работу под тем же именем.

Задание 3. Сортировка данных

(Осуществляется с помощью команды меню ДанныеСортировка)

1. Сделайте активным 4 лист и назовите его Сортировка.
2. Выделите таблицу и скопируйте её ниже на том же листе еще 4 раза.
3. Во втором экземпляре таблицы выполните сортировку по столбцу Плотность населения (по убыванию). Для этого:

• Выделите таблицу;
• Меню ДанныеСортировка;
• Выбрать столбец и способ сортировки.

4. В третьем экземпляре таблицы расположить страны па алфавиту.
5. В четвертом экземпляре таблицы выполните сортировку по данным последнего столбца.
6. В последнем экземпляре таблицы расположите страны по численности населения.
7. Сохраните работу под тем же именем.

Задание 4. Фильтрация (выборка) данных с использованием автофильтра

(Осуществляется с помощью команды меню ДанныеФильтрАвтофильтр)

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

1. Сделайте активным 5 лист и назовите его Фильтрация.
2. Произведите фильтрацию записей таблицы на листах с 5-го по 8-ой согласно следующим критериям:

• На листе Фильтрация выберите страны с площадью более 5 000 тыс. км2.
• На листе 6 - страны с населением меньше 150 млн. чел.

• На листе 7 - выберите страны с плотностью населения от 100 до 300 чел./км2.
• На листе 8 - страны, население которых составляет более 2% от всего населения Земли.

3. Сохраните работу под тем же именем.