Практическая работа № 20, 21

Тема: Информационные технологии.

Наименование: Вставка функций. Копирование формул. Относительны и абсолютные ссылки в формулах.

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

 

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

Понятие функции

Функции в Excel используются для выполнения стандартных вычислений в рабочих книгах. Значения, которые используются для вычисления функций, называются аргументами. Значения, возвращаемые функциями в качестве ответа, называются результатами.

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

Правила синтаксиса при записи функций

Если функция появляется в самом начале формулы, ей должен предшествовать знак равенства «=», так как любая формула должна начинаться с этого знака.

Аргументы функции записываются в круглых скобках сразу после названия функции и отделяются друг от друга точкой с запятой «;». Скобки позволяют Excel определить, где начинается и где заканчивается список аргументов. Нельзя вставлять пробелы между названием функции и скобками. В этом случае Excel выдаст сообщение об ошибке «#ИМЯ?».

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

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

Рассмотрим функции без аргументов, например функцию ПИ, возвращающую число, или функцию СЕГОДНЯ, возвращающую текущую дату. При вводе таких функций нужно сразу после названия функции поставить круглые скобки. Если необходимо получить в ячейке число p или текущую дату, то следует ввести в эту ячейку формулу следующего вида:

=ПИ()

или

=СЕГОДНЯ()

Рассмотрим функцию ОКРУГЛ (арг1;арг2), которая возвращает число, округленное до заданного числа знаков после запятой. Эта функция имеет два аргумента: apг1 — обозначение ячейки с числом (или само число), которое нужно округлить; арг2 — количество цифр после запятой у числа после его округления.

Пример 1.

Для округления числа выполните следующие действия:

1. Введите число 12,34567 в ячейку А1. Это число мы будем округлять.

2. Введите в ячейки Bl, C1 и D1 формулы следующего вида:

=ОКРУГЛ(А1;1);

=ОКРУГЛ(А1;2);

=ОКРУГЛ(А1;3).

Эти формулы округляют число 12,34567, находящееся в ячейке А1, до одной, двух и трех цифр после запятой соответственно. Результаты округления можно наблюдать в ячейках Bl, C1 и D1.

3. Введите в ячейку А2 число 4, а в ячейку С2 формулу следующего вида:

=ОКРУГЛ(А1;А2).

Здесь показано, что вместо числа в функцию можно вставить обозначение ячейки, в которой находится число. Результаты округления числа 12,34567 до четырех цифр после запятой можно видеть в ячейке С2. Курсор находится в ячейке С2, поэтому в строке формул отображена введенная в эту ячейку формула.

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

Вызвать окно диалога мастера функций можно следующими способами:

• с помощью команды Вставка/Функция,

• комбинацией клавиш [Shift]-[F3];

• кнопкой fx на стандартной панели инструментов.

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

Пример 2.

Дана формула, вычисляющая корни квадратного трехчлена ах²+bx+c=0.

В ячейку А2 с клавиатуры введена формула следующего вида:

=(-В1+КОРЕНЬ(В1*В1-4*А1*С1))/2/А1

В ячейках А1, В1 и С1 находятся значения коэффициентов а, b и с соответственно. Сейчас рассмотрим процесс ввода функции КОРЕНЬ в формулу вычисления корня квадратного трехчлена при помощи Мастера функций. Выполните следующие действия:

1. Введите в ячейку А4 первые символы формулы: «=(—В1+». Сейчас курсор находится в строке формул после знака «+».

2. Щелкните мышью на кнопке Мастер функций, расположенной в строке формул. Появится диалоговое окно Мастер функций шаг 1 из 2.

3. Установите категорию Математическая, выберите функцию КОРЕНЬ.

4. Щелкните на кнопке ОК. Появится диалоговое окно КОРЕНЬ. Введите в поле ввода аргумента функции КОРЕНЬ символы «bl^2—4*a1*c1».

5. Щелкните на кнопке ОК. Диалоговое окно исчезнет с экрана, а в строке формул окажется следующее выражение:

=(-В1+КОРЕНЬ(b1^2-4*а1*с1).

6. Введите в строке формул оставшиеся символы формулы: «)/2/а1». Нужная формула будет введена в ячейку А4. Результат работы формулы — число —6 в ячейке А4 и формула в строке формул.

 

Перемещение, копирование и распространение формул и функций

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

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

Теперь поговорим о том, что происходит со ссылками на ячейки при перемещении и копировании формул. Ссылки бывают двух видов — абсолютные и относительные. Абсолютная ссылка на ячейку — это указание на ячейку, положение которой относительно других ячеек не меняется.

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

Абсолютный вид адресации применяется для того, чтобы защитить в формулах адреса от изменения при копировании, если ссылка производится на одну и ту же ячейку. При абсолютной адресации перед той частью адреса ячейки, которая не должна меняться при копировании, ставится символ «$». Для упрощения ввода этого знака в адрес ячейки удобно пользоваться клавишей [F4], при этом курсор должен находиться на нужном адресе в формуле. При каждом повторном нажатии [F4] знак доллара будет появляться перед разными частями адреса: $В$6, В$б, $В6, В6.

 

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

Функции в Excel в значительной степени облегчают проведение расчетов и взаимодействие с электронными таблицами. Наиболее часто применяется функция суммирования значений ячеек. Напомним, что эту функцию можно выполнить, нажав кнопку S на Стандартной панели или ввести функцию СУММ, а в качестве аргументов служат диапазоны суммируемых чисел.

.

2. Задания.

 

Упражнение 1.

Заполните ячейки В1:В5 любыми числами и в ячейке D1 произведите их автосуммирование.

 

Упражнение 2.

На этом же листе выделите ячейку D2, включите Мастер функций, в категории Статистические выберите функцию СРЗНАЧ, в следующем шаге выделите диапазон В1:В5, получено среднее значение чисел. В ячейках D3 и D4 найдите максимальное и минимальное значения этого диапазона. В ячейках С1:С4 сделайте соответствующие подписи к ячейкам D1:D4.

 

Упражнение 3.

На новом листе вычислите значения функции y=k(x²-1)/(x²+1) для всех х на интервале [-2;2] с шагом 0,2 при k=10. Для заполнения столбцов используйте операцию копирования. Для ячейки с числом 10 примените абсолютную адресацию. Решение должно быть получено в виде таблицы:

 

k=	10
№	x	y1=x2-1	y2=x2+1	y=k×(y1/y2)

Сохраните упражнения 1-3 в файле Раб20_Ф.И. в папке вашей группы.

 

Упражнение 4.

4.1.Откройте созданный ранее файл Раб18_Ф.И. и сохраните его под именем Раб21_Ф.И.

4.2. На листе 1 в таблице «Финансовая сводка за неделю» произведите расчеты в графе «Финансовый результат»=Доход-Расход.

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

4.3. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (СРЗНАЧ находится в разделе Статистические), выделяйте соответствующие диапазоны ячеек.

4.4. Выполните расчет общего финансового результата (удобно это сделать кнопкой Автосуммирование), указав соответствующий диапазон.

Окончательный вид таблицы:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.5. На листе 2 «Ведомость учета брака» произвести расчеты в незаполненных полях:

Сумма брака =Процента брака ´ Сумма зарплаты,

остальные функции выберите в категории Статистические, укажите соответствующий диапазон ячеек.

 

 Окончательный вид таблицы:

:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.6. На листе 3 «Анализ продаж…» произвести вычисления:

Всего=Безналичные платежи +Наличные платежи

Выручка от продажи=Цена ´  Всего

Рассчитать ИТОГО выручка от продажи; максимальные и минимальные продажи в соответствующих графах.

 Окончательный вид таблицы:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

4.7. На листе 4 «Счет за аренду автомобиля» произвести следующие вычисления:

В ячейку G1 введите через Мастер функций функцию СЕГОДНЯ.

В ячейку Е6 формулу =Е5-Е4. В ячейку G6 формулу =G5-G4. Отформатируйте их под общий формат.  В ячейку Е9 формулу =Е6*В11+G6*B10. В ячейку F16 функцию ТДАТА.

В ячейку F17 формулу =F16+5.

Окончательный вид таблицы:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

1.       Что такое функция?

2.       Правила записи функций.

3.       Как использовать Мастер функций?

4.       Копирование и перемещение функций и формул.

5.       Что такое абсолютная адресация?

6.       Что такое относительная адресация?

7.