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

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

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

 

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

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

 

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

 

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

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

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

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

-     нажать клавишу Enter;

-     щелкнуть мышью подругой ячейке;

-     перейти к другой ячейке с помощью клавиш управления курсором;

-     щелкнуть мышью на кнопке Ввод в строке формул

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

2.      Ввод формул всегда начинается со знака равенства =

3.      Адреса ячеек нужно вводить без пробелов и по латинскому регистру.

4.      Адреса ячеек можно водить в формулы без использования клавиатуры, а просто щелкая по ним мышью.

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

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

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.      Создайте на нем следующую таблицу:

 

	A	B	C	D	E	F
1	№	Страна	Площадь тыс. кв.км.	Население тыс. человек	Плотность населения чел./ кв. км	% от всего населения мира
2	1	Россия	17075	149 000
3	2	США	9363	252 000
4	3	Канада	9976	27 000
5	4	Франция	552	56 000
6	5	Китай	9561	1 160 000
7	6	Япония	372	125 000
8	7	Индия	3288	850 000
9	8	Израиль	14	47 000
10	9	Бразилия	2767	154 000
11	10	Египет	1002	56 000
12		СУММА			Среднее значение
13		МИН/МАКС
14			Весь мир	5 292 000

 

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 тыс. км².

•       На листе 6 - страны с населением меньше 150 млн. чел.

•       На листе 7 - выберите страны с плотностью населения от 100 до 300 чел./км².

•       На листе 8 - страны, население которых составляет более 2% от всего населения Земли.

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