Электронные таблицы. Ссылки. Формулы - Обработка числовой информации

Конспект

Электронные таблицы Exel

Имя текущей ячейки образуется именем столбца и номером строки, на пересечении которых она находится. В данном случае это ячейка С3.

Имя диапазона (блока) ячеек образуется записью имён его верхней левой и нижней правой ячеек через двоеточие. В данном случае это диапазон B3:D4. Ячейка может содержать:

• число (в том числе в специальном формате, например, денежном или процентном);

• дату и/или время;

• текст (значение, не являющееся числом, датой или формулой, либо введённое в кавычках);

• логическое значение (ИСТИНА или ЛОЖЬ);

• формулу.

Формулы

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

Формула всегда начинается со знака равенства (=) и может содержать:

• константы (числовые, текстовые, логические);

• ссылки на другие ячейки или диапазоны с данными;

• арифметические операции:

+ — сложение,

- — вычитание,

* — умножение,

/ — деление,

% — вычисление процентов;

ˆ — возведение в степень;

• операции сравнения:

= — равенство,

> — больше,

>= — больше или равно,

< — меньше,

<= — меньше или равно,

<> — не равно;

• операцию конкатенации (& — соединение текстовых строк);

• круглые скобки;

• функции.

Абсолютные, относительные и смешанные ссылки

Ссылка на ячейку представляет собой запись имени ячейки. Ссылка на диапазон представляет собой запись имени диапазона.

Примеры:

=А3 + С5 — сложить числа в ячейках А3 и С5;

=СУММ(А3:С5) — функция вычисления суммы чисел во всём диапазоне А3:С5.

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

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

Запись имени ячейки (или имён ячеек в имени диапазона), в которой имя столбца и номер строки предваряются символом $, являются абсолютными ссылками. Абсолютная ссылка не меняется при копировании формулы в другую ячейку.

При копировании формулы из ячейки В2 в ячейку С5 ссылка не меняется и всегда указывает на одну и ту же ячейку.

Ссылки, в которых символ $ стоит только перед именем столбца или только перед номером строки, называют смешанными. Символ $ в смешанной ссылке делает абсолютным только имя столбца или только имя строки, перед которым он стоит.

В данной смешанной ссылке абсолютным является имя столбца. Поэтому при копировании формулы из ячейки В2 в ячейку С5 в ссылке имя столбца не меняется, а помер строки меняется относительно ячейки с формулой.

В данной смешанной ссылке абсолютным является номер строки. Поэтому при копировании формулы из ячейки В2 в ячейку CS в ссылке номер строки не меняется, а имя столбца меняется относительно ячейки с формулой.

Функции

В Excel предусмотрены стандартные функции, которые можно использовать в формулах:

• математические — различные вычисления (арифметические, тригонометрические, логарифмические, квадратный корень, степень, округление и т.д.);

• текстовые — работа с текстовыми строками;

• логические — работа с логическими значениями;

• дата и время — работа с датой и временем;

• финансовые — денежные расчёты (проценты по вкладам и пр.);

• статистические — статистическая обработка данных, вероятности и пр.;

• ссылки и массивы — работа с данными в диапазонах (например, поиск значения и возврат адреса ячейки с ним);

• работа с базой данных — работа с записями в электронной таблице как базе данных;

• проверка свойств и значений — определение типа данных в ячейке (число? текст? и т.д.).

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

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

Примеры функций:

ПИ()	Возвращает число π = 3,14159265358979
АВS(число)	Возвращает модуль (абсолютную величину) числа
ЗНАК(число)	Определяет знак вещественного числа: равна 1, если число положительное; 0, если число равно 0, и -1, если число отрицательное
КОРЕНЬ(число)	Возвращает значение квадратного корня
ОСТАТ(число;делителъ)	Возвращает остаток от деления аргумента на делитель
СЛЧИС()	Возвращает равномерно распределенное случайное число из диапазона [0,1)
СТЕПЕНЬ(число;степенъ)	Возвращает результат возведения вещественного числа в заданную степень (аналог оператора ˆ)
СУММ(число1;число2; ...)	Сумма чисел, заданных в качестве аргументов
СРЗНАЧ(число1;число2; ...)	Возвращает среднее арифметическое аргументов
СЧЁТЕСЛИ(диапазон;критерий)	Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию (он записывается в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать, например: 32, “32”, “>32”, “яблоки” и т.п.)
МАКС(число1;число2; ...)	Возвращает наибольшее (максимальное) из набора значений
МИН(число1;число2; ...)	Возвращает наименьшее (минимальное) из набора значений
ЛЕВСИМВ(текст; количество_знаков)	Возвращает подстроку из указанного количества символов с начала текстовой строки
ПРАВСИМВ(текст;число_знаков)	Возвращает подстроку из указанного количества символов, отсчитанного с конца текстовой строки
ПСТР(текст;начальная_позиция;число_знаков)	Возвращает подстроку из указанного количества символов, отсчитанного с указанной начальной позиции текстовой строки
НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)	Возвращает номер знаковой позиции, начиная с которой в тексте просматриваемый_текст содержится фрагмент искомый текст. Нач_позиция — знаковая позиция в исходном тексте, с которой следует начинать поиск (по умолчанию — 1, т.е. поиск с начала исходной строки)
ПОВТОР(текст;число_повторений)	Повторяет указанный текст заданное количество раз (положительное число)
СЦЕПИТЬ(текст1;текст2;...)	Соединяет несколько текстовых строк в одну (операция конкатенации) (аналог операции &)
ИЛИ(логическое_значение1;логическое_значение2; ...)	Логическая операция ИЛИ (дизъюнкция, логическое сложение)
И(логическое_значение1;логическое_значение2; ...)	Логическая операция И (конъюнкция, логическое умножение)
НЕ(логическое_значение)	Логическая операция НЕ (отрицание)
ЕСЛИ(лог_выражение;значение_если_истина; значение_если_ложь)	Аналог условного оператора IF: сначала определяется истинность заданного логического выражения, а затем в ячейке, содержащей данную функцию, отображается, соответственно, одно из значений (аргумент значение_если_ложь может быть опущен, тогда при ложном значении логического выражения в ячейке ничего не выводится). Лог_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Функции ЕСЛИ могут быть вложенными, когда, например, вместо значение_если_истина и/или значение_если_ложь записывается ещё одна функция ЕСЛИ со своими тремя (либо двумя) аргументами
СЕГОДНЯ()	Возвращает текущую дату (по показаниям системного календаря) в числовом формате

Разбор типовых задач

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

1) 8

2) 2

3) 3

4) 4

Решение

Речь идёт о таблице, состоящей из трёх ячеек: В1, В2 и В3. При этом В1 + В2 равно 5, а среднее значение, т.е. (В1 + В2 + В3)/3 равно 3. Требуется определить значение В3.

Значение первой суммы подставляется во второе равенство:

(5 + В3)/3 = 3.

Полученное уравнение остаётся решить относительно значения (переменной) ВЗ:

5 + В3 = 9;

отсюда В3 = 9 - 5 = 4.

Ответ: 4 (вариант №4).

Задача 2. Дан фрагмент электронной таблицы. Из ячейки С3 в одну из ячеек столбца D была скопирована формула. После копирования значение этой формулы стало равным 100.

В какую ячейку была скопирована формула? В ответе надо записать только номер строки, в которой находится эта ячейка.

	А	В	С	D
1	5	13
2	6	12
3	7	11	=$В3*А$4
4	8	10

Решение

Сначала определим, как меняется формула при её копировании в ячейки столбца D (с учётом абсолютной адресации):

Остаётся только вычислить произведения и сверить их с требуемым. Впрочем, и так сразу видно, что 100 — это 10*10, а подходящая формула — только в ячейке D4 (=$В4*В$4).

Ответ: 4.

Важно обратить внимание на то, что нужно внимательно читать условие задачи — в том числе о том, как надо записать ответ. Запись ответа в виде “D4” при автоматической проверке ответов будет признана за ошибку!

Задача 3. В ячейки электронной таблицы записаны числа:

	А	В	С	D	Е
1
2			3	2	1
3		1	2	3	4
4	5	4	3	2	1

В ячейку А1 записали формулу =$С3 - D$2. После этого ячейку А1 скопировали в ячейку В2. Какое число будет выведено в ячейке В2?

Примечание: знак $ используется для обозначения абсолютной адресации.

Решение

Достаточно простая задача на знание видов ссылок в электронных таблицах.

1. Исходная формула в ячейке A1: =$С3 — D$2, т.е. используются смешанные ссылки.

2. При копировании ячейки А1 в В2 в относительных ссылках (и в “относительных” частях смешанных ссылок) значения имён столбцов и номеров строк должны увеличиться на единицу, а абсолютные ссылки (и “абсолютные” части смешанных ссылок) остаются неизменными. Поэтому формула в ячейке В2 после копирования примет вид: =$С4 — Е$2.

3. Подставляя в полученную формулу значения из указанных в ней ячеек, определяем результат вычислений по этой формуле, который будет отображён в ячейке В2: 3 - 1 = 2.

Ответ: 2.