Электронные таблицы - Математические инструменты, динамические (электронные) таблицы - ИНФОРМАЦИОННЫЕ И КОММУНИКАЦИОННЫЕ ТЕХНОЛОГИИ

Конспект

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

В основе электронной таблицы лежат понятия таблицы и ячейки. Таблица, как вы прекрасно понимаете, это расчерченное горизонтальными и вертикальными линиями пространство. Горизонтальными линиями отделяются друг от друга строки, вертикальными линиями — столбцы. На пересечении строк и столбцов находятся ячейки. В современных электронных таблицах количество строк таблицы ограничено обычно примерно одним миллионом (= 2²⁰), а количество строк — 16-ю тысячами (= 2¹⁴).

Заметим, что в электронных таблицах документ, как правило, состоит из нескольких таблиц, называемых листами. Листы объединяются в книгу. Книга как раз и является документом с точки зрения электронной таблицы. Не будем подробно останавливаться на листах. Нам вполне хватит того, что предоставляет одна таблица (лист).

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

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

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

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

Вероятно, у вас возникает вопрос, как же можно пронумеровать 16 тысяч столбцов, используя всего 26 букв латинского алфавита? Это решается примерно таким же способом, как и в любой позиционной системе счисления. После того, как “буквы заканчиваются” (это в первый раз происходит после 26-го столбца, на букве Z), нумерация снова начинается с буквы А, но в левый разряд “прибавляется” очередная буква. То есть, после столбца Z следуют столбцы АА, АВ, АС, ... и продолжается до столбца AZ, после которого следуют BA, ВВ, ВС, ..., BZ, СА, СВ, ..., ZZ, AAA, ААВ, и так далее. Впрочем, для учебных задач вполне хватает первых 26-ти столбов от А до Z, поэтому вы в большинстве случаев можете считать, что номер столбца — это просто буква латинского алфавита.

Каждая ячейка электронной таблицы имеет собственный адрес ячейки. Он состоит из номера столбца и номера строки, на пересечении которых находится ячейка. Например, на пересечении строки 2 и столбца В находится ячейка В2.

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

Каждая формула в электронной таблице начинается с символа “=”. Самая простая формула, которая может быть записана, — это “=число”. Например, “=45” (кавычки в ячейку не пишутся; мы приводим их для отделения формулы от текста раздела). Ячейка, которая содержит формулу, имеет также значение, вычисляемое по этой формуле. В приведённом примере ячейка содержит формулу “=45” и имеет значение 45.

Можно записать в формуле выражение. Например, “= 14+53”. Результатом этой формулы будет, естественно, число 67. Такие формулы не очень интересно использовать.

Эта же технология позволяет сделать значением ячейки тем же, что уже является значением другой ячейки. Для этого необходимо записать “=адрес ячейки”. Например, в ячейки D8 написана формула “=F4”. Значение ячейки D8 будет одинаковым со значение ячейки F4. Если изменить значение ячейки F4, автоматически изменится значение ячейки D8.

Рассмотрим пример таблицы:

	А	В	С
1	10	30
2	20	40

Запишем в ячейку С2 формулу, которая вычисляет сумму ячеек А1 и В2. Это достаточно просто: “=А1+В2”.

В данном случае в ячейке будет храниться формула “=А1+В2”, а при просмотре таблицы в ячейке С2 будет отображаться её значение: 50.

	А	В	С
1	10	30
2	20	40	50

В электронных таблицах, кроме приведённой операции сложения, допустимы также и другие арифметические операции:

- вычитание (знак “-”),

- умножение (знак “*”),

- деление (знак “/”).

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

В зависимости от того, насколько русифицирована электронная таблица, формулы могут быть записаны русскими или английскими названиями. Имена функций принято писать заглавными буквами. Например, СУММ() или SUM(). Чаще всего, это сокращения от названий соответствующих действий. Приведённое название функции суммирования, как видите, сокращено до нескольких первых букв.

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

Обычно в электронной таблице требуется обрабатывать достаточно большие объёмы данных. Применять для сложения, например тысячи ячеек, операцию “+” достаточно утомительно (нужно привести адреса тысячи ячеек между ними поставить 999 значков “+”). К тому же, при обработке больших объёмов данных эти данные расположены в соседних относительно друг друга ячейках.

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

Как правило, указывают адрес левой верхней и правой нижней ячеек диапазона. Например, диапазон F4:H5 — это прямоугольная область, состоящая из шести ячеек (три столбца — F, G и Н и две строки — 4 и 5). Адреса диапазонов используются в формулах, обобщающих значения ячеек диапазона.

Ниже в таблице представлен список наиболее часто используемых формул.

Русское обозначение	Английское обозначение	Смысл функции
СУММ	SUM	Сумма всех непустых и нетекстовых значений указанных ячеек
СЧЁТ	COUNT	Количество всех непустых и нетекстовых значений указанных ячеек
СРЗНАЧ	AVERAGE	Среднее арифметическое всех непустых и нетекстовых значений указанных ячеек
МАКС	MAX	Наибольшее значение среди всех непустых и нетекстовых значений указанных ячеек
МИН	MIN	Наименьшее значение среди всех непустых и нетекстовых значений указанных ячеек
ЕСЛИ	IF	Проверка условия. Содержит три аргумента. Первый аргумент должен быть логическим выражением. Если значение первого аргумента — истина, то значением функции является второй аргумент. Если ложно — третий аргумент. Если не указано значение того (второго или третьего) аргумента, который должен являться результатом, то значение функции ЕСЛИ становится пустым.
СЧЁТЕСЛИ	COUNTIF	Количество непустых и нетекстовых ячеек в диапазоне, удовлетворяющих заданному условию. Первый аргумент — диапазон проверяемых ячеек. Второй аргумент — условие, пишется в кавычках.
СУММЕСЛИ	SUMIF	Сумма непустых и нетекстовых ячеек в диапазоне, удовлетворяющих заданному условию. Первый аргумент — диапазон проверяемых ячеек. Второй аргумент — условие, пишется в кавычках.

В качестве логического выражения функции ЕСЛИ (IF) можно использовать числовое значение. Нулевое значение считается ложным, остальные значения — истинными. Мы не рекомендуем использовать эту возможность.

По значениям ячеек электронной таблицы могут быть построены различные диаграммы.

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

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

Примеры диаграмм:

При построении диаграмм (и при решении задач на диаграммы) важно понимать, что они строятся по значениям диапазона ячеек. Эти значения на диаграмме отображаются в том же порядке, что и в диапазоне.

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

Задача 1. Дан фрагмент электронной таблицы.

	А	В	С	D
1	3	4	2	5
2		=D1-1	=А1+В1	=C1+D1

Какая из формул, приведённых ниже, может быть записана в ячейке А2, чтобы построенная после выполнения вычислений диаграмма по значениям диапазона ячеек A2:D2 соответствовала рисунку?

1) =D1-A1

2) =В1/C1

3) =D1-C1+1

4) =В1*4

Решение

Для построения диаграммы нужны значения всех ячеек диапазона, по которым диаграмма строится, то есть, значения всех ячеек А2, В2, С2 и D2.

Сначала вычислим значения тех ячеек, формулы для которых известны.

Получаем, что диаграмма построена по значениям:

неизвестно

4

7

7

Анализируем диаграмму. Это круговая диаграмма. На ней значение каждой ячейки диапазона, по которому строится диаграмма, отображается в виде сектора (доли) такого размера, сколько его значение “вносит” в общую сумму значений ячеек этого диапазона. На диаграмме мы видим две пары секторов одинакового размера. Одна пара имеет значения побольше, другая — поменьше. Анализируем значения, которые нам известны. Это 4, 7 и 7. Семь больше четырёх. Понимаем, что обе семёрки — это значения, которые побольше. Значит, оставшееся значение — 4 — это значение долей/ячеек, которые поменьше. Следовательно, оставшаяся (неизвестная) ячейка имеет значение также 4.

Вычислим значения предлагаемых нам в качестве ответа формул и найдём среди них ту формулу, значение которой равно 4.

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

Итак, вычисляем значения формул:

Ответ: 3.

Теперь постараемся обсудить те навыки, которые нужны для выполнения практического задания по электронным таблицам. Сделаем это на примере электронной таблицы MS Excel. Если вы привыкли пользоваться другим пакетом (например, OpenOffice.org Calc или LibreOffice Calc), то эти отличия несущественны. Разница, в основном, только именах используемых функций. В русских версиях MS Excel имена функций русифицированы, а в Calc — нет. Заметим, что английские имена функций удобнее в использовании, потому что имена ячеек требуется всё равно писать латинскими буквами и при русских именах функций приходится постоянно переключаться с одного языка на другой.

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

Задача 2. В электронную таблицу занесли данные о калорийности продуктов. Ниже приведены первые пять строк таблицы.

	А	В	С	D	Е
1	Продукт	Жиры, г	Белки, г	Углеводы, г	Калорийность, Ккал
2	Арахис	45,2	26,3	9,9	552
3	Арахис жареный	52	26	13,4	626
4	Горох отварной	0,8	10,5	20,4	130
5	Горошек зелёный	0,2	5	8,3	55

В столбце А записан продукт; в столбце В — содержание в нём жиров; в столбце С — содержание белков; в столбце D — содержание углеводов и в столбце Е — калорийность этого продукта.

Всего в электронную таблицу были занесены данные по 1000 продуктам.

Выполните задание.

Откройте файл с данной электронной таблицей (возьмите файл из Демоверсии экзамена за 2016 год на сайте ФИЛИ). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.

1. Сколько продуктов в таблице содержат меньше 50 г углеводов и меньше 50 г белков? Запишите число, обозначающее количество этих продуктов, в ячейку Н2 таблицы.

2. Какова средняя калорийность продуктов с содержанием жиров менее 1 г? Запишите значение в ячейку Н3 таблицы с точностью не менее двух знаков после запятой.

Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.

Решение

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

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

Сначала найдём ответ на первый вопрос.

Выберем среди всех строк те, которые удовлетворяют приведённому условию. Условие, которое нужно проверить (меньше 50 г углеводов и меньше 50 г белков) — сложное, состоящее из двух условий, объединённых союзом И, т. е. логической операцией И.

Данное условие можно проверить несколькими способами.

Способ первый. С использованием вспомогательных столбцов.

Так как при решении нам понадобится несколько вспомогательных столбцов, а ответ нужно поместить в ячейку Н2, будем строить эти столбцы после столбца Н.

Условие про каждую строку будем записывать в ней же.

В столбце I проверим первое условие (меньше 50 г углеводов).

Для этого запишем в ячейку I2 (проверка условия для строки 2) формулу “=ЕСЛИ(D2<50;1;0)”. (Не забудем после написания формулы нажать на [Enter].) В этой формуле проверяем ячейку D2 — именно она хранит информацию о количестве углеводов для продукта строки 2, которое должно быть меньше 50 грамм. В заголовке столбца D написано, что это количество углеводов и что оно приведено в граммах. То есть, нам не нужно указывать граммы при сравнении или преобразовывать величины. Углеводы даны в граммах и нам нужно сравнить их с 50-ю граммами. Поэтому мы и написали условие “D2<50”. Вторым и третьим аргументами мы написали 1 и 0. То есть, если условие “углеводов меньше 50 грамм” выполнится, в ячейке будет записано 1. А если не выполнится — 0. Это (1 и 0) очень удобные обозначения при проверке логических выражений. Мы рекомендуем вам пользоваться именно ими.

На данный момент мы проверили условие только для одной строки. Теперь следует скопировать эту формулу во все нижележащие 999 ячеек. Самый простой (но не самый быстрый и удобный) способ это сделать — воспользоваться специальным маркером заполнения. Выделите ячейку 12, куда мы только что записали указанную формулу. Вокруг выделенной ячейки будет нарисована толстая прямоугольная граница, показывающая, что именно эта ячейка выделена. В правом нижнем углу границы увидите чёрный квадратик. Это и есть маркер заполнения. Наведите на него курсор мыши. Форма курсора мыши изменится. Теперь курсор мыши будет иметь форму тонкого черного плюсика. Нажмите левую кнопку мыши и, не отпуская её, начните тянуть мышь вниз. Тяните мышь вниз, ниже видимой части таблицы, “наехав” мышью на нижнюю часть интерфейса электронной таблицы. Видимая часть таблицы начнёт “уезжать” вверх, переходя все дальше и дальше ко всё более нижним ячейкам таблицы. Таким образом следует “доехать”, не отпуская левую кнопку мыши, до последней, 1001-й строки таблицы. Все ячейки, сквозь которые вы “протянули” таким образом ячейку 12 за маркер заполнения, окажутся заполненными формулой “=ЕСЛИ(...<50;1;0)”. Прелесть ситуации состоит также в том, что для всех этих ячеек формула будет не та, которая была изначально в ячейке 12 (“=ЕСЛИ(D2<50;1;0)”), а измененная в соответствии с текущей строкой. Благодаря технологии автоиндексации адреса, электронная таблица сама изменит номер строки на требуемый. То есть, в строке 3 будет проверяться уже ячейка D3, а в строке 4 — ячейка D4, и т.д.

Неудобство данного метода состоит в том, что при быстром “проматывании” маркером заполнения до необходимой строки, легко “проехать мимо” нужной строки с номером 1001 и случайно заполнить ещё несколько ячеек (несколько десятков ячеек). Тогда следует либо удалить информацию из лишних заполненных ячеек, либо аккуратно учитывать это, применяя результаты (то есть, не учитывать то, что находится в строках ниже 1001-й).

Другой способ заполнить все ячейки от 13 до 11001 формулой, находящейся в ячейке 12, более быстрый, но работающий только в MS Excel.

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

Теперь, удерживая кнопку [Shift] на клавиатуре, щелкните мышью по ячейке 11001. В результате такого действия будет выделен весь диапазон ячеек от ячейки 12 до ячейки 11001. А именно, все те ячейки, в которые нужно поместить формулу, которая уже находится в ячейке 12. Заметим, что текущей ячейкой всё ещё является ячейка 12, в которой находится нужная нам формула.

Перейдём в режим редактирования текущей ячейки (12). Для этого нажмём один раз кнопку [F2] на клавиатуре. Вертикальный моргающий курсор клавиатуры появится в конце формулы ячейки 12. Теперь нажмём на клавиатуре комбинацию клавиш [Ctrl]+[Enter]. В результате этого действия формула из текущей ячейки (12) заполнится во все выделенные ячейки. Этого мы и добиваемся.

Итак, мы проверили первое условие (углеводы меньше 50 грамм) для каждой строки со 2-й по 1001-ю. Точно так же проверим второе условие — белки меньше 50 грамм — в столбце J. Введём в ячейку J2 формулу “=ЕСЛИ(С2<50;1;0)” и заполним этой формулой, одним из приведённых способов, ячейки от J3 до J1001.

Теперь проверим выполнение общего условия — что одновременно углеводов меньше 50 грамм и белков меньше 50 грамм. Для этого просто перемножим в каждой строке полученные в столбцах I и J цифры, т. е. запишем в ячейку К2 формулу “=I2*J2” и заполним ею ячейки от К3 до К1001. Данный способ даст нам в каждой строке число 1, если и углеводов меньше 50 г, и белков меньше 50 г.

Действительно, чтобы выполнилось условие И, необходимо, чтобы оба аргумента были равны 1. Умножение двух чисел, каждое из которых — 0 или 1, даёт 1 только при двух единицах.

Остаётся только посчитать, сколько в столбце К получилось единиц. Для этого достаточно просто сложить все числа в столбце К от ячейки К2 до ячейки К1001. Запишем в требуемую ячейку Н2 формулу “=СУММ(К2:К1001)”.

Другой способ получить нужные 1 и 0 в каждой строке для проверки сложного условия “углеводы меньше 50 грамм И белки меньше 50 грамм” — воспользоваться встроенной логической функцией И (AND). В её аргументах следует указывать логические выражения. Результат функции — ИСТИНА, если все логические выражения ИСТИНА, и ЛОЖЬ, если хотя бы одно из них ложно.

Так как при использовании данного способа требуется меньше вспомогательных ячеек, воспользуемся столбцом F, как это предполагает разработчик задания.

Запишем в ячейку F2 формулу, которая сразу положит в ячейку F2 значение 1, если требуемое сложное условие истинно, и 0, если оно ложно. Используем функцию ЕСЛИ, в которой в первом аргументе через функцию И проверим оба нужные нам условия — и про углеводы, и про белки.

То есть, формула будет такой: “=ЕСЛИ(И(D2<50;С2<50);1;0)”.

Эту формулу одним из двух уже описанных выше способов скопируем/заполним в ячейки от F3 до F1001.

Осталось только в требуемой ячейке Н2 записать формулу, складывающую все значения диапазона F2:F1001, т. е.: “=CУMM(F2:F1001)”.

Нахождение ответа на второй вопрос можно осуществить тем же образом, построив вспомогательный столбец.

Нам теперь необходимо посчитать не количество ячеек, а среднее арифметическое их значений. Поэтому результатом проверки условия ЕСЛИ должны стать не 0 и 1, а те значения, по которым нужно вычислять среднее арифметическое. То есть, значение калорийности в случае истинности условия “содержание жиров менее 1 грамма”. А вот при ложности данного условия очень важно, чтобы значение строки не подсчитывалось функцией “СРЗНАЧ”. Функция СРЗНАЧ не учитывает пустые ячейки и текстовые, значит, при ложности условия следует положить в ячейку пустое значение или текстовое. Например, “ ”.

Получаем формулу для ячейки G2: “=ЕСЛИ(В2<1;Е2;“ ”)”.

Скопируем/заполним эту формулу в ячейки G3:G1001. Теперь достаточно в требуемую ячейку Н3 записать формулу, вычисляющую среднее арифметическое значение ячеек G2:G1001: “=CPЗHA4(G2:G1001)”.

Другой способ выполнения задания — использовать специальные сложные функции СУММЕСЛИ и СЧЕТЕСЛИ.

Чтобы вычислить среднее арифметическое калорийности ячеек, у которых жиров меньше 1 грамма, нужно сумму калорийностей таких ячеек поделить на количество таких ячеек. Сумму с условием можно вычислить при помощи функции СУММЕСЛИ: “=СУММЕСЛИ(В2:B1001;"<1";E2:E1001)”. То есть, проверяем ячейки диапазона В2:В1001 на условие “<1” и складываем те соответствующие им ячейки диапазона Е2:Е1001, для которых это условие выполнилось.

Количество определяем при помощи функции СЧЕТЕСЛИ: “=СЧЕТЕСЛИ(В2:В1001;"<1).

Остаётся только поделить результат первой функции на результат второй функции, записав в требуемую ячейку Н3 формулу:

“=СУММЕСЛИ(В2:В1001;"<1";Е2:Е1001)/СЧЕТЕСЛИ (В2:В1001;"<1").

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

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

Задача 3. В электронную таблицу занесли информацию о грузоперевозках, совершенных некоторым автопредприятием с 1 по 9 октября. Ниже приведены первые пять строк таблицы.

	А	в	С	D	Е	F
1	Дата	Пункт отправления	Пункт назначения	Расстояние	Расход бензина	Масса груза
2	1 октября	Липки	Берёзки	432	63	600
3	1 октября	Орехово	Дубки	121	17	540
4	1 октября	Осинки	Вязово	333	47	990
5	1 октября	Липки	Вязово	384	54	860

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

В столбце А записана дата перевозки (от “1 октября” до “9 октября”); в столбце В — название населённого пункта отправления перевозки; в столбце С — название населённого пункта назначения перевозки; в столбце D — расстояние, на которое была осуществлена перевозка (в километрах); в столбце Е — расход бензина на всю перевозку (в литрах); в столбце F — масса перевезенного груза (в килограммах).

Всего в электронную таблицу были занесены данные по 370 перевозкам в хронологическом порядке.

Выполните задание.

Откройте файл с данной электронной таблицей (возьмите файл из Демоверсии экзамена за 2013 год на сайте ФИЛИ). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.

1. На какое суммарное расстояние были произведены перевозки с 1 по 3 октября? Запишите число, обозначающее это суммарное расстояние, в ячейку Н2 таблицы.

2. Какова средняя масса груза при автоперевозках, осуществлённых из города Липки? Запишите значение в ячейку НЗ таблицы с точностью не менее двух знаков после запятой.

Не забудьте сохранить полученную таблицу.

Решение

Ответ на первый вопрос можно получить несколькими способами. Рассмотрим сначала более универсальный.

Построим вспомогательный столбец I, в котором будем записывать расстояние для тех перевозок, которые были осуществлены с 1 по 3 октября.

Так как по условию задачи перевозки осуществлялись с 1 по 9 октября, будет достаточным проверить, что дата перевозки не позже 3 октября. То есть, проверяемое условие — А2<="3 октября".

Заметим, что дата, используемая в столбце А с датами перевозок, представлена в текстовом формате. Поэтому при сравнении значения ячейки А2 с датой "3 октября" мы используем двойные кавычки, понимая при этом, что сравнение будет происходить в текстовом виде. А именно, будут сравниваться сначала первые символы, при их совпадении — вторые, и т. д. Если бы диапазон дат по условию не ограничивался 9-м октября, пришлось бы писать гораздо более сложное сравнение.

Значение, которое необходимо записать в столбце I, если условие выполнится — это соответствующее расстояние, т. е. D2. Если условие не выполнится, данное расстояние не должно попасть в сумму. Тогда запишем в столбец I ноль. Получаем формулу для ячейки 12: “=ЕСЛИ(А2<="3 октября";D2;0)”. Скопируем/заполним эту формулу в ячейки 13:1371.

Теперь посчитаем сумму всех этих ячеек в требуемой ячейке Н2: “=СУММ(12:1371)”.

Второй вариант решения задания — воспользоваться сразу формулой, считающей сумму с условием.

Запишем в требуемую ячейку Н2 формулу: “=СУММЕСЛИ(А2: А371;"<=’3 октября'"; D2:D371)”.

Заметим, что при указании условия сравнения дату '3 октября' пришлось указать в апострофах (одинарных кавычках), чтобы электронная таблица правильно поняла проверяемое условие, потому что проверяемое условие функции СУММЕСЛИ само должно быть записано в двойных кавычках.

Третий вариант решения. По условию дано, что строки отсортированы по дате перевозки от 1 до 9 октября, все нужные нам строки находятся в первой части таблицы. Поэтому просто найдём, до какой строки продолжаются перевозки с датой 3 октября.

Пролистываем таблицу вниз и находим, что последняя строка с датой “3 октября” — номер 118. Значит, достаточно посчитать сумму расстояний для строк со 2-й по 118-ю. Записываем в требуемую ячейку Н2 формулу: “=CУMM(D2:D118)”.

Есть ещё более простой и наименее универсальный способ решения именно этой задачи.

Так как мы знаем, что строки отсортированы по дате и можем найти номер строки (118), которой заканчиваются интересующие нас перевозки, мы можем просто выделить диапазон ячеек от ячейки D2 до ячейки D118 и в нижней части интерфейса электронной таблицы посмотреть на значение суммы выделенных ячеек. Это значение можно просто вписать руками в требуемую ячейку Н2. Такое решение “законно” и засчитывается проверяющими экспертами. Однако, из-за его малой универсальности мы советуем, всё же, освоить универсальное решение и не надеяться, что на экзамене попадётся настолько простая задача.

Найдём ответ на второй вопрос задачи. Для вычисления среднего арифметического значений диапазона ячеек достаточно найти сумму требуемых ячеек и поделить её на количество этих ячеек.

Один из вариантов решения — построить вспомогательные столбцы для нахождения суммы нужных ячеек и для нахождения их количества. Потом сумму по первому из этих столбцов поделить на сумму второго из этих столбцов.

Запишем в ячейку J2 формулу, выбирающую массу груза, если перевозка отправляется из пункта “Липки” или ноль, в противном случае. Для этого в J2 укажем формулу: “=ЕСЛИ(В2="Липки";F2;0)”. Скопируем/заполним эту формулу в ячейки J3:J371.

Теперь запишем в ячейку К2 формулу, проверяющую, отправляется ли перевозка из пункта “Липки” и выдающая результат 1, если это условие истинно, и 0 — если условие ложно. Укажем в ячейку К2 формулу: “=ЕСЛИ(В2="Липки";1;0)”.

Скопируем/заполним эту формулу в ячейки К3:К371.

Всё готово для итоговых вычислений. Запишем в требуемую ячейку Н3 формулу: “=СУММ(J2:J371)/СУММ(К2:К371)”.

Другой (менее универсальный, но более короткий в данном случае) способ решения задачи — воспользоваться функциями СУММЕСЛИ и СЧЕТЕСЛИ. То есть, можно сразу записать в требуемую ячейку Н3 формулу: “=СУММЕСЛИ(В2:В371;"Липки";F2:F371)/СЧЕТЕСЛИ (В2:В371;"Липки")”.