загрузка...

Информатика: Новый полный справочник для подготовки к ЕГЭ - 2018 год

Раздел 8. Обработка числовой информации

Электронные таблицы. Ссылки. Формулы

Конспект

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

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

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

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

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

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

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

• формулу.

Формулы

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

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

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

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

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

+ — сложение,

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

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

/ — деление,

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

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

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

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

> — больше,

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

< — меньше,

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

<> — не равно;

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

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

• функции.

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

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

Примеры:

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

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

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

image137

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

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

image138

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

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

image139

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

image140

В данной смешанной ссылке абсолютным является номер строки. Поэтому при копировании формулы из ячейки В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.





загрузка...

загрузка...