Ссылки и функции в электронных таблицах

ССЫЛКИ И ФУНКЦИИ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Ссылка – адрес ячейки, используемый при записи формул.

Ссылки бывают:

ü  абсолютные (не изменяются при копировании формул) $A$5

ü  относительные (изменяются при копировании формул) A5

ü  смешанные (частично изменяются при копировании формул) $A5, A$5

Решение задач

В ячейке B4 электронной таблицы записана формула = $C3*2. Какой вид приобретет формула, после того как ячейку B4 скопируют в ячейку B6? Примечание: знак $ используется для обозначения абсолютной адресации.

1) =$C5*4 2) =$C5*2 3) =$C3*4 4) =$C3*2

Решение:

1)  ссылка $C3 – это смешанная ссылка, в которой «заблокирован» столбец C, а строка 3 – это относительный адрес;

2)  после того, как ячейку B4 скопировали в B6, номер строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная часть) также увеличится на 2, ссылка превратится в $C5

3)  константы при копировании формул не меняются, поэтому получится =$C5*2

4)  таким образом, правильный ответ – 2.

В электронной таблице значение формулы =СУММ(B1:B2) равно 5. Чему равно значение ячейки B3, если значение формулы =СРЗНАЧ(B1:B3) равно 3?

1 4

Решение:

1)  функция СУММ(B1:B2) считает сумму значений ячеек B1 и B2, поэтому B1 + B2 = 5

2)  функция СРЗНАЧ(B1:B3) считает среднее арифметическое диапазона B1:B3

3)  строго говоря, такие задачи некорректны, потому что

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

СРЗНАЧ(B1:B3)=СУММ(B1:B3), если есть только одна числовая ячейка

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/2, если есть две числовых ячейки

СРЗНАЧ(B1:B3)=СУММ(B1:B3)/3, если все три ячейки – числовые

б)  в условии не задано, сколько числовых ячеек в диапазоне B1:B3

4)  в такой ситуации логичнее всего считать, что все три ячейки содержат числовые данные (во всех известных автору задачах такого типа используется именно это допущение)

5)  итак, в диапазон B1:B3 входят три ячейки; предполагаем, что все они содержат числовые данные, тогда среднее арифметическое – это сумма их значений, деленная на 3; таким образом B1 + B2 + B3 = 3 · 3 = 9

6)  поскольку B1 + B2 = 5, сразу получаем B3 = 9 – 5 = 4

7)  таким образом, правильный ответ – 4.

ПРАКТИЧЕСКАЯ ЧАСТЬ «ССЫЛКИ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ»

Задание 1. Создать расчетную таблицу в среде MS EXCEL по образцу. Рассчитать оплату труда (столбец C). При выполнении расчетов использовать относительную и абсолютную ссылки (можно использовать смешанные ссылки).

Задание 2. Составить таблицу квадратов чисел по приведенному ниже образцу. В ячейку B3 ввести формулу и скопировать ее в интервал B3:J11 с помощью маркера автозаполнения. При выполнении задания использовать смешанные ссылки.

Функции – это заранее определенные формулы, для которых пользователь должен задать конкретные значения аргументов.

ПРАВИЛА ЗАПИСИ ФУНКЦИЙ

1.  Функция состоит из имени и аргумента, записанного в скобках.

2.  В качестве аргументов функций могут использоваться:

ü  числа;

ü  ссылки на ячейки и интервалы;

ü  текст;

ü  формулы;

ü  другие функции;

ü  логические значения и др.

Для облегчения работы со встроенными функциями используется Мастер функций. Для его вызова можно использовать:

ü  команду меню Вставка – Функция;

ü  кнопку Вставка функции на панели инструментов;

ü  комбинацию клавиш Shift+F3

В диалоговом окне Мастер функций имеются два подокна: Категория и Функция. При выборе определенной функции в нижней части диалогового окна появляется краткое ее описание.

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

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

Примеры:

Математические – sin(A1) – синус от значения ячейки A1, cos(B1) - косинус от значения ячейки B1, ABS(C5) – абсолютная величина (модуль) значения ячейки C5, SUM(B1:D5) – сумма значений интервала от B1 до D5 (15 ячеек).

Статистические – MIN(A1:A5) – находит минимальное значение в интервале, MAX(A1:A5) – находит максимальное значение в интервале, AVERAGE(A1:A5) – находит среднее арифметическое значений в интервале, COUNT(A1:A5) – подсчитывает количество непустых ячеек в интервале.

Логические – AND(A1;B5) – одновременное выполнение условий, записанных в заданных ячейках; OR(A1;B5) – выполнение хотя бы одного из условий, записанных в заданных ячейках; NOT(A1) – отрицание; IF(условие; значение 1;значение 2) – если условие выполняется в ячейку запишется значение 1, в противном случае – значение 2.

Практическая работа «Функции в электронных таблицах»

Создать таблицу по приведенному ниже образцу

В ячейки B9:E9 ввести формулы для подсчета максимальной оценки для каждого спортсмена. Пример: =MAX(B3:B7)

В ячейки B10:E10 ввести формулы для подсчета минимальной оценки для каждого спортсмена.

В ячейки B12:E12 ввести формулы для подсчета итоговой оценки для каждого спортсмена:

(сумма оценок всех судей – минимальная оценка – максимальная оценка)/3

В ячейку B14 ввести формулу для подсчета оценки победителя (функция MAX)

ЛОГИЧЕСКИЕ ФОРМУЛЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Логическое выражение – это выражение, которое может принимать значения «истина» или «ложь».

Например:

Если в какую-либо ячейку ЭТ ввести формулу =1>3, то результатом будет значение «ложь»

Если в какую-либо ячейку ЭТ ввести формулу =1<3, то результатом будет значение «истина»

Логическая функция IF

IF(<условие>; <значение 1>;<значение 2>) – если условие выполняется в ячейку запишется значение 1, в противном случае – значение 2.

Примеры вложенных логических функций:

IF(<условие>;<значение 1>; IF(<условие>;<значение 2>;<значение 3>))

IF(AND(<условие 1>;<условие 2>;<условие 3>);<значение 1>; <значение 2>)

IF(OR(<условие 1>;<условие 2>;<условие 3>);<значение 1>; IF(<условие 4>; <значение 2>;<значение 3>))

ПРАКТИЧЕСКАЯ РАБОТА «ЛОГИЧЕСКИЕ ФОРМУЛЫ В ЭЛЕКТРОННЫХ ТАБЛИЦАХ

Задание 1.

Написать функцию «ЕСЛИ» для расчета оплаты электроэнергии. За первые 100 кВт клиент платит 2 руб. за 1 кВт, за превышение расхода свыше 100 кВт в месяц - по 2,5 руб. за каждый кВт.

	A	B	C
1	Клиент	Кол-во электроэнергии	Оплата
2	Коржова	140
3	Давыдова	100
4	Козлов	80
5	Литвинов	50
6	Новиков	120

Задание 2.

Создать таблицу, выполнив словесную оценку результатов конкурсного отбора по результатам экзаменов «Принят» - «Не принят» («Принят» - если набрал не менее 12 баллов).

	A	B	C	D	E	F	G
1.  1						проходной балл	12
2.	Ф. И.О	Матема-тика	Физика	Русский язык	общая сумма баллов	результат решения
3.	Исаченко	4	5	4
4.	Сумина	4	4	4
5.	Рябова	3	4	3
6.	Кузнецова	5	4	5
7.	Насонов	4	4	3

Задание 3.

Составить таблицу расчета оплаты за аренду помещений в зависимости от площади: если арендуемая площадь меньше 100 м2, то арендная плата составляет 500 руб. за 1 м2, если арендуемая площадь больше, чем 100 м2, но не превышает 200 м2 , то арендная плата составляет 700 руб. за 1 м2. За площадь более 200 м 2 арендная плата – 800 руб. за 1 м2 .

Составляется таблица в следующем виде:

Кафе	Площадь (кв. м)	Сумма арендной платы
«Эдельвейс»	167
«Нарцисс»	95
«Камелот»	234
«Рандеву»	128
«Экспромт»	173

Задание 4.

Продуктовый склад отпускает муку и сахар для предприятий по оптовым ценам в зависимости от объема закупок.

Для муки используются следующие цены: более 10000 кг – по 12 руб. за 1кг, от 5000 до 10000 – по 13 руб. за 1кг, от 1000 до 5000 кг – по 14 руб. за 1кг, менее 1000 кг – по 15 руб. за 1 кг. Для сахара: более 5000 кг – 13 руб. за 1кг, от 3000 до 5000 кг – по 14 руб. за 1 кг, от 1000 до 3000 кг – по 16 руб. за 1 кг, менее 1000 кг – по 17 руб. за 1 кг. Рассчитайте стоимость муки и сахара, используя функцию ЕСЛИ; а также общую сумму стоимости этих продуктов для каждого предприятия.

Постройте:

а) гистограмму, показывающую объем закупок муки и сахара для каждого предприятия;

б) круговую диаграмму, показывающую долю каждого предприятия в общей сумме потраченных средств для закупок муки и сахара.

Пример таблицы: