Электронные таблицы: формулы и рабочие шаблоны

Задачи на электронные таблицы решают формулами в Excel или LibreOffice Calc. В русской версии разделитель аргументов — точка с запятой, а десятичный разделитель — запятая.

Основные функции

Когда условий много, не пишите одну длинную формулу: добавьте вспомогательный столбец с 1 и 0 (подходит строка или нет) и просуммируйте его.

Задача № 9: условия на строки

Типичная постановка: в каждой строке записано несколько чисел, нужно сосчитать строки, у которых выполняется набор условий, например «в строке есть ровно два одинаковых числа» и «сумма остальных не больше среднего».

Порядок работы:

1. Выпишите условия по пунктам.
2. Для каждого условия сделайте столбец с формулой, которая возвращает 1 или 0 (=ЕСЛИ(...; 1; 0)).
3. В последнем столбце перемножьте условия или проверьте их через И(...).
4. Сосчитайте результат функцией СУММ по этому столбцу.

Условие «в строке ровно два одинаковых значения» удобно проверять по количеству повторов: для ячейки A2 значение =СЧЁТЕСЛИ($A2:$E2; A2) показывает, сколько раз число из A2 встречается в строке.

Задача № 18: робот на клетчатом поле

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

Заполняем второй лист той же формы. В каждую клетку записываем лучшую сумму пути до этой клетки:

• в левую верхнюю клетку — её значение;
• в клетки первой строки — значение плюс сумма слева;
• в клетки первого столбца — значение плюс сумма сверху;
• в остальные клетки — значение плюс МАКС(слева; сверху) для наибольшей суммы или МИН(слева; сверху) для наименьшей.

Пример поля:

Формула для клетки второго листа B2: =Лист1!B2 + МАКС(B1; A2). Заполненная таблица наибольших сумм:

Наибольшая сумма равна 12, наименьшая — 7. Проверьте результат на таком маленьком поле до того, как переходить к большому.

Для двух вопросов сразу заведите два листа (с МАКС и с МИН) и копируйте одну и ту же формулу по всей области.

Задача № 22: зависимые процессы

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

завершение(P) = длительность(P) + МАКС(завершение зависимостей)

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

Типичные ошибки

• В русской версии пишут запятую вместо точки с запятой в аргументах.
• Забывают закрепить диапазон знаком $, и формула «уезжает» при копировании.
• Сравнивают числа, записанные как текст: проверьте формат ячеек.
• В задаче про робота берут МАКС вместо МИН или наоборот и не замечают этого: контрольный пример на малом поле помогает.