Задания к практической работе

Практическая работа №45-46

(2 часа)

Тема: «Работа с функциями в ЭТ. Работа с абсолютными и относительными ссылками»

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

План

1) Изучить основные возможности табличного процессора Excel

2) Изучить формулы Excel и способы работы с ними

3) Изучить встроенные функции Excel и способы работы с ними

4) Выполнить создание, сохранение, оформление, редактирование и расчеты с использованием встроенных функций и формул в табличном процессоре Excel

5) Ответить на контрольные вопросы

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

Работа с формулами. Вычисления в таблицах выполняются с помощью формул, состоящих из математических операторов, констант, ссылок на ячейку и имен функций. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Формула начинается со знака равенства =. В формуле могут использоваться арифметические операторы (Табл. 1) Порядок вычислений определяется обычными математическими законами (вычисляются значения функций, выполняется возведение в степень, умножение, деление, сложение, вычитание). Примеры формул:

=(А4+В8)*С6

=F7/С14+B9^2

Таблица 1

Оператор	Значение	Пример
Арифметические операторы
+ (знак плюс) - (знак минус) / (косая черта) * (звездочка) % (знак процента) ^ (крышка)	Сложение Вычитание или унарный минус Деление Умножение Процент Возведение в степень	=А1+В2 =А1-В2 =-В2 =А1/В2 =А1*В2 =20% =5^3 (5 в 3-й степени)
Операторы сравнения
= > < >= <= <>	Равно Больше Меньше Больше или равно Меньше или равно Не равно	=ЕСЛИ (А1=В2; Да; Нет) =ЕСЛИ (А1>В2; А1; В2) =ЕСЛИ (А1<В2; В2; А1) =ЕСЛИ (А1>=В2; А1; В2) =ЕСЛИ (А1<=В2; В2; А1) =ЕСЛИ (А1<>В2; неравны)
Текстовый оператор
& (амперсанд)	Объединение последовательностей символов в одну последовательность символов	=Значение ячейки В2 равняется: &В2
Адресные операторы
Диапазон (двоеточие) Объединение (точка с запятой)	Ссылка на все ячейки между границами диапазона включительно Ссылка на объединение ячеек диапазонов	=СУММ (А1:В2) =СУММ (А1:В2; С3; D4;E5)

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.

Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек. Ссылка на отдельную ячейку – ее координаты. Значение пустой ячейки равно нулю. Ссылки на ячейки бывают двух типов:

1. относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7 ). При копировании формул относительные ссылки изменяются на размер перемещения.

2. абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ ( например: $F$7 ). При копировании формул абсолютные ссылки не изменяются.

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

Абсолютный вид адресации применяется для того, чтобы защитить в формулах адреса от изменения при копировании, если ссылка производиться на одну и ту же ячейку. При абсолютной адресации перед той частью адреса ячейки, которая не должна меняться при копировании ставится символ <$> (Табл. 2).

Таблица 2

Вид адресации	Адрес ячейки (пример)	Действие при копировании
Относительный столбец, относительная строка	В6	Меняются имя столбца и номер строки
Абсолютный столбец, относительная строка	$B6	Не меняется имя столбца, меняется номер строки
Относительные столбцы, абсолютная строка	B$6	Меняется имя столбца, не меняется номер строки
Абсолютный столбец, абсолютная строка	$B$6	Не меняются имя столбца и номер строки

При копировании ячеек, содержащих формулы с относительными ссылками, координаты ячеек аргументов изменяются автоматически. Например, при копировании формулы =А1+А2 из ячейки А3 в ячейку В3 ее содержимое изменится на =В1+В2. Эта формула как и ранее вычисляет сумму двух ячеек слева от ячейки с формулой. При копировании ячеек, содержащих формулы с абсолютными ссылками, адреса ячеек-аргументов не изменяются.

Для ввода формулы в ячейку следует ввести знак = и формулу для вычисления. После нажатия клавиши Enter в ячейке появится результат вычисления. При выделении ячейки, содержащей формулу, формула появляется в строке редактирования.

Работа с функциями. Функция представляет собой программу с уникальным именем, для которой пользователь должен задать конкретные значения аргументов функции, стоящих в скобах после его имени. Функцию (также как и число) можно считать частным случаем формулы. Различают статистические, логические, финансовые и другие функции (рис. 1). Например, ячейка содержит функцию вычисления суммы множества чисел, находящихся в ячейках В4, В5, В6, В8, в виде: СУММ(B4:B6, B8). Вставить в ячейку функцию суммы СУММ можно с помощью кнопки .

Функции в Microsoft Excel представляют собой формулы, которые имеют один или несколько аргументов. В качестве аргументов указываются числовые значения или адреса ячеек. Например: =СУММ(А5:А9) - сумма ячеекА5, А6, А7, А8, А9; =СРЗНАЧ(G4:G6)–среднее значение ячеекG4, G5, G6. Функции могут входить одна в другую, например: =ОКРУГЛ(СРЗНАЧ(H4:H8);2) – округлить до двух знаков после запятой среднее значение из ячеек H4, H5, H6, H7, H8

Рис.1. Мастер функций

Для обращения к группе ячеек используются специальные символы:

: (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например: С4:D6 – обращение к ячейкам С4, С5, С6, D4, D5, D6.

; (точка с запятой) – обозначает объединение ячеек. Например, D2:D4;D6:D8 – обращение к ячейкам D2, D3, D4, D6, D7, D8.

Для введения функции в ячейку необходимо:

a выделить ячейку для формулы;

a вызывать Мастер функций с помощью команды Функция меню Вставка или кнопки ;

a в диалоговом окне Мастер функций (рис.13), выбрать тип функции в поле Категория, затем функцию в списке Функция;

a щелкнуть кнопку ОК;

a в полях Число1, Число2 и др. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);

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

Задания к практической работе

Задание 1. Создать таблицу вкладчиков банка (рис. 2). Выполнить расчет значений доли общего вклада с использованием формул.

Порядок работы 1. Запустите редактор электронных таблиц Microsoft Excel (Пуск – Программы – 0ffiсе_2000 – Miсrosoft Excel) 2. Таблицу сохранить на D:\ Студенты\ Ваша группа\ ВАША ФАМИЛИЯ под именем файла ВАША ФАМИЛИЯ_№ ПРАКТИЧЕСКОЙ РАБОТЫ (например, Иванов 14). 3. На вкладке лист1 напечатать Ф. И.О., группа (в ячейке А1), № практической работы (в ячейке D2), тема практической работы (в ячейке А3), цель практической работы (в ячейке А5). 4. На вкладке лист2 выполнить задание 1: создать таблицу Вклады в банке (исходные данные представлены на рис. 2).

Рис. 2. Исходные данные для задания 2

5. Установите курсор на ячейку А1. Введите заголовок таблицы Вклады в банке. Затем выделите диапазон A1:D1 объедините ячейки командой Формат/Ячейки/вкладка Выравнивание/ установить флажок Объединение ячеек

6. Введите названия столбцов таблицы ‑ «№ п/п», «Фамилия вкладчика», «Сумма вклада, руб.», «Доля от общего вклада, %». Изменение ширины столбцов производите перемещением мышью в строке имен столбцов (А, В, С и т.д.).

7. Для оформления шапки таблицы выделите строку, содержащую шапку таблицы (строка 2), задайте перенос по словам командой Формат/Ячейки/вкладка Выравнивание/Переносить по словам, выберите горизонтальное и вертикальное выравнивание — «по центру».

8. Заполните таблицу исходными данными согласно заданию 1 (рис. 2).

9. Выполните расчет итогового показателя в ячейке С13. Для этого установить курсор в ячейку С13 и дважды нажать кнопку Автосумма на панели инструментов.

10. Для расчета доли от общего вклада необходимо в ячейку D3 ввести формулу =С3/$С$13. Затем произведите автокопирование формулы: установите курсор в нужную ячейку (D3), подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; когда курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу вниз по ячейкам до нужной ячейки (до ячейки D12).

11. Выполните расчет итогового показателя в ячейке D13. Для этого установить курсор в ячейку D13 и дважды нажать кнопку Автосумма на панели инструментов.

12. Затем отформатируйте значения из последнего столбца в процентном формате, для этого выделите диапазон ячеек D3:D13 и выполните команду Формат/Ячейки/вкладка Число/формат Процентный, число десятичных знаков задайте равное 1

13. Произведите обрамление таблицы. Для этого выделите блок ячеек таблицы А2:D13. Откройте окно Обрамление таблиц командой Формат/ Ячейки/вкладка Границы. Для внутренних линий выберите тонкую, а для контура — более толстую непрерывную линию. Макет отображает конечный вид форматирования обрамления, поэтому кнопку ОК нажмите, когда вид обрамления на макете полностью вас удовлетворит.

14. Выполните выравнивание данных таблицы как на рис. 2

15. Сохраните электронную таблицу Файл-Сохранить

16.

Задание 2. Создать таблицу Погода (рис. 3). Выполнить нахождения максимального, минимального и среднего значений в столбцах Температура, Давление и Влажность.

Порядок работы

1. Откройте рабочую книгу из практической работы 13 (Файл – Открыть - D:\ Студенты\Ваша группа\ ВАША ФАМИЛИЯ\ ВАША ФАМИЛИЯ 13)

2. Скопировать таблицу Погода со вкладки Лист 2 из файла ВАША ФАМИЛИЯ 13, на вкладку Лист 3 в файл ВАША ФАМИЛИЯ 14

3. Закройте файл ВАША ФАМИЛИЯ 13 (Файл - Закрыть)

4. Дополните таблицу данными как на рис. 3

5. Произведите расчеты Максимума, для этого установите курсор в ячейку С20 и выберите команду Вставка/Функция/выберите функцию МАКС/Ok (рис. 4). Затем нажать кнопку для уменьшения размеров окна функции МАКС, выделить диапазон С3:С19 и развернув окно кнопкой нажать Ok. Далее произведите автокопирование полученной функции в ячейке С20.

Краткая справка. Для автокопирования функции выполните следующие действия: подведите курсор к маркеру автозаполнения, расположенному в правом нижнем углу ячейки; когда курсор примет вид черного крестика, нажмите левую кнопку мыши и протяните формулу в нужном направлении (вниз, вправо) по ячейкам.

Рис. 3. Исходные данные для задания 2

9. Произведите расчеты Минимума, для этого установите курсор в ячейку С21 и выберите команду Вставка/Функция/ выберите функцию МИН/ Ok. Затем нажать кнопку

для уменьшения размеров окна функции МИН, выделить диапазон С3:С19 и развернув окно кнопкой

нажать Ok. Далее произведите автокопирование полученной функции вправо.

Рис. 4. Использование встроенной функции МАКС

10. Произведите расчеты Среднего значения, для этого установите курсор в ячейку С22 и выберите команду Вставка/Функция/ выберите функцию Срзнач/Ok. Затем нажать кнопку для уменьшения размеров окна функции МИН, выделить диапазон С3:С19 и развернув окно кнопкой нажать Ok. Далее произведите автокопирование полученной функции вправо.

11. Сохраните электронную таблицу Файл-Сохранить

12. Вставить новый лист (лист 4) командой Вставка-Лист и выполните на нем дополнительное задание

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

1. С помощью какого инструмента выполняются вычисления в электронной таблице?

2. Какие объекты могут входить в формулу?

3. Какие арифметические операторы могут быть использованы в формуле? Напишите их обозначения.

4. В какой последовательности выполняются операции в формуле?

5. Что такое ссылка на ячейку?

6. Как обозначаются относительные ссылки?

7. Как обозначаются абсолютные ссылки?

8. Что представляет собой функция?

9. Как вставить функцию в Excel?