Формула для электронной таблицы какая.

Тема : Электронные таблицы.

Что нужно знать :

адрес ячейки в электронных таблицах состоит из имени столбца и следующего за ним номера строки, например, C15
формулы в электронных таблицах начинаются знаком = («равно»)
знаки +, –, *, / и ^ в формулах означают соответственно сложение, вычитание, умножение, деление и возведение в степень
запись B2:C4 означает диапазон, то есть, все ячейки внутри прямоугольника, ограниченного ячейками B2 и C4:

например, по формуле =СУММ(B2:C4) вычисляется сумма значений ячеек B2, B3, B4, C2, C3 и C4
в заданиях ЕГЭ могут использоваться стандартные функции СЧЕТ (количество непустых ячеек), СУММ (сумма), СРЗНАЧ (среднее значение), МИН (минимальное значение), МАКС (максимальное значение)
функция СРЗНАЧ при вычислении среднего арифметического не учитывает пустые ячейки и ячейки, заполненные текстом; например, после ввода формулы в C2 появится значение 2 (ячейка А2 – пустая):

функция СЧЕТ(A1:B2) в этом случае выдаст значение 3 (а не 4).

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

в абсолютных адресах перед именем столбца и перед номером строки ставится знак доллара $, такие адреса не изменяются при копировании; вот что будет, если формулу =$B$2+$ C $3 скопировать из D5 во все соседние ячейки

знак $ как бы «фиксирует» значение: в абсолютных адресах и имя столбца, и номер строки зафиксированы

в относительных адресах знаков доллара нет, такие адреса при копировании изменяются: номер столбца (строки) изменяется на столько, на сколько отличается номер столбца (строки), где оказалась скопированная формула, от номера столбца (строки) исходной ячейки; вот что будет, если формулу =B2+ C 3 (в ней оба адреса – относительные) скопировать из D5 во все соседние ячейки:

в смешанных адресах часть адреса (строка или столбец) – абсолютная, она «зафиксирована» знаком $, а вторая часть – относительная; относительная часть изменится при копировании так же, как и для относительной ссылки:

Пример задания:

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

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

Решение:

ссылка $C3 – это смешанная ссылка, в которой «заблокирован» столбец C, а строка 3 – это относительный адрес;
после того, как ячейку B4 скопировали в B6, номер строки увеличился на 2, поэтому и в ссылке $C3 номер строки (относительная часть) также увеличится на 2, ссылка превратится в $C5
константы при копировании формул не меняются, поэтому получится =$C5*2
таким образом, правильный ответ – 2 .

Ещё пример задания:

Три страны: Королевство Бельгия, Королевство Нидерланды и Великое Герцогство Люксембург образуют экономико-политический союз, который носит название Бенилюкс. Ниже приведен фрагмент электронной таблицы, характеризующий каждую из стран союза и союз в целом:

	А	B	C	D
1	Страна	Население (тыс. чел)	Площадь (кв. км)	Плотность населения (чел / кв.км)
2	Бельгия	10 415	30 528	341
3	Нидерланды	16 357	41 526	394
4	Люксембург	502	2 586	194
5	Бенилюкс в целом	27 274	74 640

Какое значение должно стоять в ячейке D5?

1) 365 2) 929 3) 310 4) 2,74

Решение:

нужно не забыть, что плотность населения вычисляется как отношение населения к площади (не наоборот!);
население не забываем перевести из тысяч человек в единицы: 27 274 000 чел
поэтому для всего Бенилюкса получаем 27 274 000 / 74 640 ≈ 365
таким образом, правильный ответ – 1 .

Еще пример задания:

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

1) 8 2) 2 3) 3 4) 4

Решение:

функция СУММ(B1:B2) считает сумму значений ячеек B1 и B2, поэтому B1 + B2 = 5
функция СРЗНАЧ(B1:B3) считает среднее арифметическое диапазона B1:B3
строго говоря, такие задачи некорректны, потому что
1. функция СРЗНАЧ учитывает только числовые данные (числа или формулы, при вычислении которых получается число), то есть возможны варианты:

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

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

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

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

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

Еще пример задания:

	А	В	С
1	10	20	= A1+B$1
2	30	40

Чему станет равным значение ячейки С2, если в нее скопировать формулу из ячейки С1? Знак $ обозначает абсолютную адресацию.

1) 40 2) 50 3)60 4) 70

Решение:

это задача на использование абсолютных и относительных адресов в электронных таблицах
вспомним, что при копировании все относительные адреса меняются (согласно направлению перемещения формулы), а абсолютные – нет
в формуле, которая находится в C1, используются два адреса: A1 и B$1
адрес A1 – относительный, он может изменяться полностью (и строка, и столбец)
адрес B$1 – смешанный, в нем номер строки «зафиксирован» знаком доллара, а имя столбца – нет, поэтому при копировании может измениться только имя столбца
при копировании из C1 в C2 столбец не изменяется, а номер строки увеличивается на 1, поэтому в C2 получим формулу = A 2+ B $1 (здесь учтено, что у второго адреса номер строки «зафиксирован»)
сумма ячеек A2 и B1 равна 30 + 20 = 50

Еще пример задания:

	А	В	С
1	1	2
2	2	6	=СЧЁТ(A1:B2)
3			=СРЗНАЧ(A1:C2)

Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1)

1) –2 2) –1 3) 0 4) +1

Решение:

это задача на знание особенностей функций СЧЕТ и СРЗНАЧ, которые не учитывают пустые ячейки
после ввода формул в С2 окажется количество непустых ячеек диапазона А1:В2, равное 4

(1+2+2+6+4)/5 = 3

после перемещения (не копирования!) содержимого ячейки В2 в В3 ячейка В2 окажется пустой, поэтому в С2 выводится число 3 – количество непустых ячеек диапазона А1:В2
в С3 будет выведено среднее значение диапазона А1:С2 равное

(1+2+2+3)/4 = 2,

то есть значение С3 уменьшится на 1

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

Задачи для тренировки 1:

В ячейке B1 записана формула =2*$A1 . Какой вид приобретет формула, после того как ячейку B1 скопируют в ячейку C2?

1) =2*$B1 2) =2*$A2 3) =3*$A2 4) =3*$B2Н

В ячейке C2 записана формула =$E$3+D2 . Какой вид приобретет формула, после того как ячейку C2 скопируют в ячейку B1?

1) =$E$3+C1 2) =$D$3+D2 3) =$E$3+E3 4) =$F$4+D2

Дан фрагмент электронной таблицы:

	A	B	C	D
1	5	2	4
2	10	1	6

В ячейку D2 введена формула =А2*В1+С1 . В результате в ячейке D2 появится значение:

1) 6 2) 14 3) 16 4) 24

В ячейке А1 электронной таблицы записана формула =D1-$D2 . Какой вид приобретет формула после того, как ячейку А1 скопируют в ячейку В1?

1) =E1-$E2 2) =E1-$D2 3) =E2-$D2 4) =D1-$E2

Дан фрагмент электронной таблицы:

	А	В	С	D
1	1	2	3
2	4	5	6
3	7	8	9

В ячейку D1 введена формула =$А$1*В1+С2 , а затем скопирована в ячейку D2. Какое значение в результате появится в ячейке D2?

1) 10 2) 14 3) 16 4) 24

В ячейке В2 записана формула =$D$2+Е2 . Какой вид будет иметь формула, если ячейку В2 скопировать в ячейку А1?

1) =$D $ 2+E1 2) =$D$2+C2 3) =$D$2+D2 4) =$D$2+D1

В ячейке СЗ электронной таблицы записана формуле =$А$1+В1 . Какой вид будет иметь формула, если ячейку СЗ скопировать в ячейку ВЗ?

1) =$A$1+А1 2) =$В$1+ВЗ 3) =$А$1+ВЗ 4) =$B$1+C1

При работе с электронной таблицей в ячейке ЕЗ записана формула =В2+$СЗ . Какой вид приобретет формула после того, как ячейку ЕЗ скопируют в ячейку D2?

1) =А1+$СЗ 2) =А1+$С2 3) =E2+$D2 4) =D2+$E2

В ячейке электронной таблицы В4 записана формула =С2+$A$2 . Какой вид приобретет формула, если ячейку В4 скопировать в ячейку С5?

1) =D2+$В$3 2) =С5+$A$2 3) =D3+$A$2 4) =СЗ+$А$3

В ячейке электронной таблицы А1 записана формула =$D1+D$2 . Какой вид приобретет формула, если ячейку А1 скопировать в ячейку ВЗ?

1) =D1+$E2 2) =D3+$F2 3) =E2+D$2 4) =$D3+Е$2

Дан фрагмент электронной таблицы:

	А	В	С
1	2	3
2	4	5	=СЧЁТ(A1:B2)
3			=СРЗНАЧ(A1:C2)

Как изменится значение ячейки С3, если после ввода формул переместить содержимое ячейки В2 в В3? («+1» означает увеличение на 1, а «–1» – уменьшение на 1):

1) –1 2) –0,6 3) 0 4) +0,6

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно (-2 ). Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно 5?

1) 1 2) -1 3) -3 4) 7

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно 0,1. Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно (–1)?

1) – 0,7 2) - 0,4 3) 0,9 4) 1,1

В электронной таблице значение формулы =СРЗНАЧ(B 5: E 5) равно 100. Чему равно значение формулы =СУММ(B 5: F 5) , если значение ячейки F5 равно 10?

1) 90 2) 110 3) 310 4) 410

В электронной таблице значение формулы =СРЗНАЧ(A 6: C 6) равно 2 . Чему равно значение формулы =СУММ(A 6: D 6) , если значение ячейки D6 равно -5?

1) 1 2) -1 3) -3 4) 7

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

1) 20 2) 10 3) 5 4) 4

В динамической (электронной) таблице приведены значения пробега автомашин (в км) и общего расхода дизельного топлива (в литрах) в четырех автохозяйствах с 12 по 15 июля.

	12 июля		13 июля		14 июля		15 июля		За четыре дня
Название автохозяйства	Пробег	Расход	Пробег	Расход	Пробег	Расход	Пробег	Расход	Пробег	Расход
Автоколонна №11	9989	2134	9789	2056	9234	2198	9878	2031	38890	8419
Грузовое такси	490	101	987	215	487	112	978	203	2942	631
Автобаза №6	1076	147	2111	297	4021	587	1032	143	8240	1174
Трансавтопарк	998	151	2054	299	3989	601	1023	149	8064	1200

В каком из хозяйств средний расход топлива на 100 км пути за эти четыре дня наименьший?

1) Автоколонна № 11

2) Грузовое такси

3) Автобаза №6

4) Трансавтопарк

В электронной таблице значение формулы =СРЗНАЧ(A 1: C 1) равно 5. Чему равно значение ячейки D1, если значение формулы =СУММ(A 1: D 1) равно 7?

1) 2 2) -8 3) 8 4) -3

В электронной таблице значение формулы =СРЗНАЧ(B 1: D 1) равно 4. Чему равно значение ячейки A1, если значение формулы =СУММ(A 1: D 1) равно 9?

1) -3 2) 5 3) 1 4) 3

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

1) -11 2) 11 3) 4 4) -9

=СУММ(B1: C 4)+F2* E 4– A 3

	A	B	C	D	E	F
1	1	3	4	8	2	0
2	4	–5	–2	1	5	5
3	5	5	5	5	5	5
4	2	3	1	4	4	2

1) 19 2) 29 3) 31 4) 71

На рисунке приведен фрагмент электронной таблицы. Определите, чему будет равно значение, вычисленное по следующей формуле =СУММ(A1:C2)*F4*E2-D3

	A	B	C	D	E	F
1	1	3	4	8	2	0
2	4	–5	–2	1	5	5
3	5	5	5	5	5	5
4	2	3	1	4	4	2

1) –15 2) 0 3) 45 4) 55

В электронной таблице значение формулы =СРЗНАЧ(A 4: C 4) =СУММ(A 4: D 4) , если значение ячейки D4 равно 6?

1) 1 2) 11 3) 16 4) 21

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

1) 1 2) 3 3) 4 4) 6

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

1) –6 2) –4 3) 2 4) 4

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

Зерновые культуры	Заря		Первомайское		Победа		Рассвет
Зерновые культуры	Посевы	Урожай	Посевы	Урожай	Посевы	Урожай	Посевы	Урожай
Пшеница	600	15600	900	23400	300	7500	1200	31200
Рожь	100	2200	500	11000	50	1100	250	5500
Овёс	100	2400	400	9600	50	1200	200	4800
Ячмень	200	6000	200	6000	100	3100	350	10500
Всего	1000	26200	2000	50000	500	12900	2000	52000

В каком из хозяйств достигнута максимальная урожайность зерновых (по валовому сбору, в центнерах с гектара)?

1) Заря 2) Первомайское 3) Победа 4) Рассвет

Дан фрагмент электронной таблицы:

	B	C	D
69	5	10
70	6	9	=СЧЁТ(B69:C70)
71			=СРЗНАЧ(B69:D70)

После перемещения содержимого ячейки C70 в ячейку C71 значение в ячейке D71 изменится по абсолютной величине на:

1) 2,2 2) 2,0 3) 1,05 4) 0,8

Дан фрагмент электронной таблицы:

	B	C	D
69	5	10
70	6	9	=СЧЁТ(B69:C70)
71			=СРЗНАЧ(B69:D70)

После перемещения содержимого ячейки B69 в ячейку D69 значение в ячейке D71 изменится по сравнению с предыдущим значением на:

1) –0,2 2) 0 3) 1,03 4) –1,3

В динамической (электронной) таблице приведены данные о продаже путевок турфирмой «Все на отдых» за 4 месяца. Для каждого месяца вычислено общее количество проданных путевок и средняя цена одной путевки.

Страна	май		июнь		июль		август
Страна	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.	Продано, шт.	Цена, тыс. руб.
Египет	12	24	15	25	10	22	10	25
Турция	13	27	16	27	12	26	11	28
ОАЭ	12	19	12	22	10	21	9	22
Хорватия	5	30	7	34	13	35	10	33
Продано, шт.	42		50		45		40
Средняя цена, тыс.руб.	25		27		26		27

Известно, что доход фирмы от продажи каждой путевки не зависит от места отдыха и равен 10% от средней цены путевки в текущем месяце. В каком месяце доход турфирмы был максимальный?

В электронной таблице значение формулы =СРЗНАЧ(D1: D 4) равно 8. Чему равно значение формулы =СРЗНАЧ(D 2: D 4) , если значение ячейки D1 равно 11?

1) 19 2) 21 3) 7 4) 32

На рисунке приведен фрагмент электронной таблицы. В ячейку B2 записали формулу =($A2*10+B$1)^2 и скопировали ее вниз на 2 строчки, в ячейки B3 и B4. Какое число появится в ячейке B4?

	A	B	C	D
1		0	1	1
2	1
3	2
4	3
5

1) 144 2) 300 3) 900 4) 90

На рисунке приведен фрагмент электронной таблицы. Чему будет равно значение ячейки B4, в которую записали формулу =СУММ(A 1: B 2; C 3) ?

	A	B	C	D
1	1	2	3
2	4	5	6
3	7	8	8
4

1) 14 2) 15 3) 17 4) 20

В ячейке электронной таблицы С3 записана формула = B 2+$ D $3- E $2 . Какой вид приобретет формула, если ячейку C3 скопировать в ячейку С4?

1) =B3+$G$3-E$2 2) =B3+$D$3-E$3
3) =B3+$D$3-E$2 4) =B3+$D$3-F$2

На рисунке приведен фрагмент электронной таблицы. В ячейку D3 введена формула = B 2+$ B 3-$ A $1 . Какое число появится в ячейке C4, если скопировать в нее формулу из ячейки D3?

	A	B	C	D
1	5	10
2	6	12
3	7	14
4	8	16

1) 8 2) 18 3) 21 4) 26

1 Источники заданий:

Демонстрационные варианты ЕГЭ 2004-2011 гг.
Гусева И.Ю. ЕГЭ. Информатика: раздаточный материал тренировочных тестов. - СПб: Тригон, 2009.
Крылов С.С., Ушаков Д.М. ЕГЭ 2010. Информатика. Тематическая рабочая тетрадь. - М.: Экзамен, 2010.
Якушкин П.А., Ушаков Д.М. Самое полное издание типовых вариантов реальных заданий ЕГЭ 2010. Информатика. - М.: Астрель, 2009.
М.Э. Абрамян, С.С. Михалкович, Я.М. Русанова, М.И. Чердынцева. Информатика. ЕГЭ шаг за шагом. – М.: НИИ школьных технологий, 2010.
Чуркина Т.Е. ЕГЭ 2011. Информатика. Тематические тренировочные задания. - М.: Эксмо, 2010.
Якушкин П.А., Лещинер В.Р., Кириенко Д.П. ЕГЭ 2011. Информатика. Типовые тестовые задания. - М.: Экзамен, 2011.
Самылкина Н.Н., Островская Е.М. ЕГЭ 2011. Информатика. Тематические тренировочные задания. - М.: Эксмо, 2010.

http://kpolyakov.narod.ru

Содержимое ячейки воспринимается программой Excel как формула, если оно начинается со знака «=». Формула может содержать числовые константы , функции Excel и ссылки на ячейки. Ввод формулы заканчивается нажатием клавиши или щелчком на кнопке Ввод в строке формул. В ячейке выводится результат вычисления, а при активизации ячейки в строке формул отображается введенная формула. Примечание . Чтобы увидеть формулы в ячейках таблицы , нужно в диалоговом окне Сервис Параметры на вкладке Вид в области Параметры окна установить флажок Формулы . Для возвращения к обычному виду ячеек необходимо сбросить этот флажок. Правило использования формул в программе Excel состоит в том, что если вычисляемое значение зависит от других ячеек таблицы, то всегда следует использовать формулу со ссылками на эти ячейки. Ссылка задается указанием адреса ячейки. На рисунке 5.1 показан пример вычисления в ячейке С2 по формуле: = A2*B2

ввести адрес ячейки с клавиатуры;
по ходу ввода формулы щелкать на нужной ячейке.

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

активизировать ячейку С2 ;
ввести с клавиатуры знак "=";
щелкнуть в ячейке А2 ;
ввести с клавиатуры знак " * ";
щелкнуть в ячейке В2 ;
нажать .

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

Копирование формул

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

Другие способы копирования формул:

Ссылки на адреса ячеек при копировании формулы автоматически изменяются в соответствии с относительным расположением исходной ячейки и создаваемых копий (рисунок 5.2).


Цена	Количество	Стоимость
		A2*B2	Исходная формула
		A3*B3	Формула после копирования
		A4*B4	Формула после копирования
		A5*B5	Формула после копирования

Формулы

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

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

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

Ссылку на ячейку можно задать разными способами. Во-первых, адрес ячейки можно ввести вручную. Другой способ состоит в щелчке на нужной ячейке или выборе диапазона, адрес которого требуется ввести. Ячейка или диапазон при этом выделяются пунктирной рамкой.

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

Рис. 5.3. Диалоговое окно в развернутом и свернутом виде

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

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

Пусть, например, в ячейке В2 имеется ссылка на ячейку A3. В относительном представлении можно сказать, что ссылка указывает на ячейку, которая располагается на один столбец левее и на одну строку ниже данной. Если формула будет скопирована в другую ячейку, то такое относительное указание ссылки сохранится. Например, при копировании формулы в ячейку ЕА27 ссылка будет продолжать указывать на ячейку, располагающуюся левее и ниже, в данном случае на ячейку DZ28.

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

Копирование содержимого ячеек

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

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

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

Применение буфера обмена. Передача информации через буфер обмена имеет в программе Excel определенные особенности, связанные со сложностью контроля над этой операцией. Вначале необходимо выделить копируемый (вырезаемый) диапазон и дать команду на его помещение в буфер обмена: Правка Копировать или Правка Вырезать. Вставка данных в рабочий лист возможна лишь немедленно после их помещения в буфер обмена. Попытка выполнить любую другую операцию приводит к отмене начатого процесса копирования или перемещения. Однако утраты данных не происходит, поскольку «вырезанные» данные удаляются из места их исходного размещения только в момент выполнения вставки.

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

Автоматизация ввода

Так как таблицы часто содержат повторяющиеся или однотипные данные, программа Excel содержит средства автоматизации ввода. К числу предоставляемых средств относятся: автозавершение, автозаполнение числами и автозаполнение формулами.

Автозавершение. Для автоматизации ввода текстовых данных используется метод автозавершения. Его применяют при вводе в ячейки одного столбца рабочего листа текстовых строк, среди которых есть повторяющиеся. В ходе ввода текстовых данных в очередную ячейку программа Excel проверяет соответствие введенных символов строкам, имеющемся в этом столбце выше. Если обнаружено однозначное совпадение, введенный текст автоматически дополняется. Нажатие клавиши ENTERподтверждает операцию автозавершения, в противном случае ввод можно продолжать, не обращая внимания на предлагаемый вариант.

Можно прервать работу средства автозавершения, оставив в столбце пустую ячейку. И наоборот, чтобы использовать возможности средства автозавершения, заполненные ячейки должны идти подряд, без промежутков между ними.

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

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

Пусть, например, ячейка А1 содержит число 1, Наведите указатель мыши на маркер заполнения, нажмите правую кнопку мыши иперетащите маркер заполнения так, чтобы рамка охватила ячейки А1, В1 и С1, и отпустите кнопку мыши. Если теперь выбрать в открывшемся меню пункт Копировать ячейки, все ячейки будут содержать число 1. Если же выбрать пункт Заполнить, то в ячейках окажутся числа 1, 2 и 3.

Чтобы точно сформулировать условия заполнения ячеек, следует дать команду Правка Заполнить Прогрессия. В открывшемся диалоговом окне Прогрессия выбирается тип прогрессии, величина шага и предельное значение. После щелчка на кнопке OK программа Excel автоматически заполняет ячейки в соответствии с 1 заданными правилами.

Автозаполнение формулами. Эта операция выполняется так же, как автозаполнение числами. Ее особенность заключается в необходимости копирования ссылок на другие ячейки. В ходе автозаполнения во внимание принимается характер ссылок в формуле: относительные ссылки изменяются в соответствии с относительным расположением копии и оригинала, абсолютные остаются без изменений.

Для примера предположим, что значения в третьем столбце рабочего листа (столбце С) вычисляются как суммы значений в соответствующих ячейках столбцов А и В, Введем в ячейку С1 формулу =А1+В1. Теперь скопируем эту формулу методом автозаполнения во все ячейки третьего столбца таблицы. Благодаря относительной адресации формула будет правильной для всех ячеек данного столбца.

В
таблице 5.1 приведены правила обновления ссылок при автозаполнении вдольстроки или вдоль столбца.

Таблица 5.1. Правила обновления ссылок при автозаполнении

Использование стандартных функций

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

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

Использование мастера функций. При выборе пункта Другие функции запускается Мастер функций, облегчающий выбор нужной функции. В раскрывающемся списке Категория выбирается категория, к которой относится функция (если определить категорию затруднительно, используют пункт Полный алфавитный перечень), а в списке Выберите функцию - конкретная функция данной категории. После щелчка на кнопке ОК имя функции заносится в строку формул вместе со скобками, ограничивающими список параметров. Текстовый курсор устанавливается между этими скобками. Вызвать Мастер функций можно и проще, щелчком на кнопке Вставка функции в строке формул.

А
ргументы функции. Как только имя функции выбрано, на экране появляется диалоговое окно Аргументы функции (в предыдущих версиях Ехсе l это окно рассматривалось как палитра формул). Это окно, в частности, содержит значение, которое получится, если немедленно закончить ввод формулы (рис. 5.4).

Рис. 5.4. Строка формул и диалоговое окно Аргументы функции

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

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

Тема:Вычисления в электронных таблицах.
Применение итоговых функций

Время на выполнение – 2 часа

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

Основные сведения по теме

Вычисления в электронных таблицах

Вычисления в таблицах программы Excel осуществляются при помощи формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединенные знаками математических операций. Скобки позволяют изменять стандартный порядок выполнения действий. Если ячейка содержит формулу, то в рабочем листе отображается только текущий результат вычисления этой формулы. Чтобы увидеть саму формулу, а не результат ее работы, надо выделить ячейку (сделать ее текущей) и посмотреть на запись, которая отображается в строке формул (рис. 40).

Рис. 40. В ячейке отображается только результат работы формулы,
а сама формула видна в строке формул

Основное правило использования формул в программе Excel состоит в том, что, если значение ячейки действительно зависит от других ячеек таблицы, всегда следует использовать формулу, даже если операцию легко выполнить «в уме»! Это гарантирует, что последующее редактирование таблицы не нарушит ее целостности и правильности производимых в ней вычислений.

Взгляните на формулу, представленную на рис. 40. В ней записана операция над содержимым двух ячеек. Вместо чисел в формуле использованы имена ячеек – принято говорить, ссылки на ячейки. Ссылки выполняют роль адресов ячеек, содержимое которых используется в вычислениях. Это означает, что результат расчета зависит от того, какие числа находятся в ячейках, участвующих в вычислении. Таким образом, ячейка, содержащая формулу, является зависимой. Значение в зависимой ячейке подлежит пересчету всякий раз, когда изменяются значения в ячейках, на которые указывают ссылки, входящие в формулу.

· во-первых, адрес ячейки можно ввести вручную;

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

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

Рис. 41. Диалоговое окно в развернутом и свернутом виде

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

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

Ссылки на ячейки.

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