( А7 ) Электронные таблицы

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

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

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

Ячейка может содержать:

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

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

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

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

• формулу.

Формулы

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

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

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

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

• арифметические операции: + — сложение, вычитание,

* — умножение, / — деление, % — вычисление процентов;

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

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

= — равенство, >— больше, >= — больше или равно, <— меньше,

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

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

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

• функции.

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

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

Примеры:

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

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

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

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

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

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

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

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

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

Функции

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

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

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

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

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

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

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

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

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

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

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

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

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

Пи()	Возвращает число π = 3,14159265358979
ABS(⅛ucλo)	Возвращает модуль (абсолютную величи­ну) числа
ЗНАК(чнсло)	Определяет знак ве­щественного числа: равна 1, если число по­ложительное; 0, если число равно 0, и -1, если число отрица­тельное
КОРЕНЬ(число)	Возвращает значение квадратного корня
OCT АТ(число; Делитель)	Возвращает остаток от деления аргумента на делитель
СЛЧИС( )	Возвращает равномер­но распределенное случайное число из диапазона [0,1)
СТЕПЕНЬ(число;степекь)	Возвращает результат возведения веществен­ного числа в заданную степень (аналог опера­тора ^ )
СУММ(чнсло1;число2; …)	Сумма чисел, задан­ных в качестве аргу­ментов
СРЗНАЧ(число1; Число2; …)	Возвращает среднее арифметическое аргу­ментов

СЧЁТЕСЛИ(диапазо«;критперий)	Подсчитывает коли­чество ячеек внутри диапазона, удовлетво­ряющих заданному критерию (он записы­вается в форме числа, выражения или текс­та, который определя­ет, какие ячейки надо подсчитывать, напри­мер: 32, «32», «>32», «яблоки» и т. п.)
МАКС(число1;число2; …)	Возвращает наиболь­шее (максимальное) из набора значений
МИН(число1;число2; …)	Возвращает наимень­шее (минимальное) из набора значений
ЛЕВСИМВ( Текст; Количество _знаков)	Возвращает подстроку из указанного коли­чества символов с на­чала текстовой строки
ПРАЪСИМВ(текст;число_знаков)	Возвращает подстроку из указанного коли­чества символов, от­считанного с конца текстовой строки
ΠCTP(τneκcτn; Начальная_позиция; число знаков)	Возвращает подстроку из указанного коли­чества символов, от­считанного с указан­ной начальной пози­ции текстовой строки
НАЙ. ТИ(искомый_текст; просматриваемый текст; начпозиция)	Возвращает номер знаковой позиции, на­чиная с которой в тек­сте Просматривае — мый_текст содер­жится фрагмент Искомый_гпексгп.

	Нач_позиция — зна­ковая позиция в ис­ходном тексте, с кото­рой следует начинать поиск (по умолчанию — 1, т. е. поиск с нача­ла исходной строки)
ΠOBTOP(Текст;число повторений)	Повторяет указанный текст заданное коли­чество раз (положи­тельное число)
СЦЕПИТЬ(текст/;текст2;…)	Соединяет несколько текстовых строк в од­ну (операция Конка­тенации) (аналог опе­рации &)
НЛЩлогическоезначение 1; Логическое_значение2; …)	Логическая операция ИЛИ (дизъюнкция, логическое сложение)
!!(логическое значение 1; Логическое_значение2; …)	Логическая операция И (конъюнкция, логи­ческое умножение)
!!^(логическое значение)	Логическая операция НЕ (отрицание)
ЕСЛИ(лог_выражение; значение если истина; значение если ложь)	Аналог условного оператора IF: сначала определяется истин­ность заданного логи­ческого выражения, а затем в ячейке, содер­жащей данную функ­цию, отображается, соответственно, одно из значений (аргумент Значение _если_ложъ Может быть опущен, тогда при ложном значении логического выражения в ячейке ничего не выводится).

Окончание табл.

	Лог —выражение — это любое значение или выражение, прини­мающее значения ИСТИНА или ЛОЖЬ. Функции ЕСЛИ могут быть вложенными, когда, например, вместо Значение_если_истина И/или Значение_если_ложь Записывается ещё од­на функция ЕСЛИ со своими тремя (либо двумя) аргументами
СЕГОДНЯ( )	Возвращает текущую дату (по показаниям системного календа­ря) в числовом фор­мате

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

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

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

Решение

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

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

(5 + B3)∕3 = 3.

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

5 + ВЗ = 9;

Отсюда ВЗ = 9-5 = 4.

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

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

Название автохозяйства	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

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

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

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

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

Решение

Необходимо сравнивать средний расход топлива на 100 км пути за четыре дня. Его можно вычислить как частное от деления значения в графе «Расход» на значе­ние в графе «Пробег» из колонки таблицы «За четыре дня» (остальная информация в данном случае избы­точна).

Составим таблицу:

Название автохозяйства	Пробег за четыре дня	Расход за четыре дня	Средний расход (при­близительно)
Автоколонна №11	38890	8419	0,216
Грузовое такси	2942	631	0,214
Автобаза №6	8240	1174	0,142
Трансавтопарк	8064	1200	0,49

Вывод: наименьший средний расход топлива на 100 км пути за четыре дня — в автохозяйстве «Автобаза №6 ».

Ответ: Автобаза №6 (вариант ответа №3)

Задача 3*. В Динамической (электронной) таблице приведены значения посевных площадей (в га) и уро­жая (в центнерах) четырёх зерновых культур в четырёх хозяйствах одного района. В каком из хозяйств достиг­нута максимальная урожайность зерновых (по валово­му сбору)? (Урожайность измеряется в центнерах с гек­тара.)

Зерновые культуры	Названия хозяйства
Заря	Перво­майское	Победа	Рассвет
По­севы	Уро­жай	По­севы	Уро­жай	По­севы	Уро­жай	По­севы	Уро­жай
Пшеница	600	15300	900	23800	300	7500	1200	31200
Рожь	100	2150	500	1200	50	1100	250	5500
Овёс	100	2350	400	10000	50	1200	200	4800
Ячмень	200	6000	200	6300	100	3100	350	10500
Всего зерновые	1000	25800	2000	52100	500	12900	2000	52000

1) Заря

2) Первомайское

3) Победа

4) Рассвет

Решение

Необходимо определить максимальную урожай­ность всех зерновых в центнерах с гектара. Поэтому для расчётов нужно брать данные из последней строки таб­лицы («Всего зерновые»). Урожайность определяется как отношение значения урожая (в центнерах) к значе­нию посевной площади («Посев») в гектарах.

Составляется таблица:

Хозяйство	Площадь земель	Урожай	Урожайность
Заря	1000	25800	25,8
Первомайское	2000	52100	26,05
Победа	500	12900	25,8
Рассвет	2000	52000	26

Вывод: максимальная урожайность получена в хо­зяйстве «Первомайское».

Ответ: Первомайское (вариант ответа №2).

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

	А	В	C	D
1	Страна	Население (тыс. чел.)	Площадь (кв. км.)	Плотность насел, (чел. / кв. км.)
2	Бельгия	10415	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 (население указано в тысячах человек) на значение 74 640 (площадь), получается искомое значе­ние в ячейке D5: 365,4.

Поскольку в списке ответов точное значение отсутс­твует, оно округляется до: 365,4 ≈ 365.

Ответ:365 чел / кв. км, (вариант ответа №1).

§ Следует не забывать переводить приведённые в условии задачи значения в нужную размерность.

Если среди приведённых в задаче вариантов ответа отсут­ствует вычисленное точное значение, нужно найти в вариан­тах ответа округлённое значение.