Электронные таблицы и обработка больших данных
Вспомогательные столбцы, функции СЧЁТЕСЛИМН и СУММЕСЛИМН и переход к Python, когда таблица уже не справляется.
Задание 9 решается в электронной таблице за пять минут, если сразу построить вспомогательные столбцы, а не пытаться уместить всё в одну формулу. Задания 26 и 27 работают с теми же данными, но объём заставляет перейти на Python.
Любое сложное условие для строки таблицы записывают в отдельном столбце формулой =ЕСЛИ(условие;1;0), растягивают на все строки данных, а затем считают итог функцией СУММ по этому столбцу. Это надёжнее одной длинной формулы и позволяет глазами проверить промежуточный результат.
Минимальный набор функций
| Функция | Что делает | Типичное применение в задании 9 |
|---|---|---|
| МАКС / МИН | наибольшее и наименьшее в диапазоне | =МАКС(A2:C2) — максимум по строке |
| СУММ | сумма диапазона | =СУММ(A2:C2) — сумма трёх чисел строки |
| СРЗНАЧ | среднее арифметическое | =СРЗНАЧ(A2:C2) сравнить с порогом |
| СЧЁТЕСЛИ | количество по одному условию | =СЧЁТЕСЛИ(G2:G1001;1) |
| СЧЁТЕСЛИМН | количество по нескольким условиям | две и более пары диапазон/условие |
| И / ИЛИ | логические связки внутри ЕСЛИ | =ЕСЛИ(И(усл1;усл2);1;0) |
Условие: откройте файл электронной таблицы, содержащей в каждой строке три натуральных числа. Определите количество строк, для которых выполнены оба условия одновременно: из трёх чисел можно составить треугольник, и среднее арифметическое трёх чисел больше 30. Решение. Пусть данные занимают строки со 2-й по 1001-ю в столбцах A, B, C. 1) Условие треугольника проще всего записать через максимум и сумму: сумма двух наименьших сторон больше наибольшей, то есть СУММ(A2:C2) − МАКС(A2:C2) > МАКС(A2:C2). В ячейку E2 вводим: =ЕСЛИ(СУММ(A2:C2)-МАКС(A2:C2)>МАКС(A2:C2);1;0) и растягиваем до E1001. 2) В F2 вводим: =ЕСЛИ(СРЗНАЧ(A2:C2)>30;1;0) и растягиваем до F1001. 3) В свободную ячейку: =СЧЁТЕСЛИМН(E2:E1001;1;F2:F1001;1). В бланк: одно число, например 187. Если в задании два вопроса — два числа через один пробел.
Условие: в файле 26.txt в первой строке записаны количество участков N и площадь склада S. В следующих N строках — площади участков. Определите максимальное количество участков, которые можно разместить на складе, и суммарную площадь при этом количестве. Решение: f = open('26.txt') n, s = map(int, f.readline().split()) a = sorted(int(x) for x in f) k = 0 total = 0 for x in a: if total + x <= s: total += x k += 1 else: break print(k, total) Пояснение. Максимум количества достигается на самых малых площадях — отсюда сортировка по возрастанию. Если во второй части вопроса требуется максимальная суммарная площадь при том же k, нужно после подсчёта k попробовать заменить последний взятый элемент на наибольший подходящий из оставшихся. В бланк: два числа через один пробел.
Формулировки «больше 30» и «не менее 30» дают разные ответы: > и >=. В условии треугольника неравенство строгое: при равенстве суммы двух сторон третьей треугольник вырожденный и не считается. Вторая ловушка — диапазон формулы: если данных 1000 строк, диапазон должен заканчиваться строкой 1001, а не 1000. Третья — ответ из двух чисел записывается через один пробел в одном поле, без запятой и без переноса строки.
- ✓Последняя строка данных найдена и включена в диапазоны
- ✓Строка заголовков исключена из вычислений
- ✓Строгие и нестрогие неравенства взяты точно по формулировке условия
- ✓Вспомогательные столбцы растянуты на весь диапазон без пропусков
- ✓Итоговая формула ссылается именно на столбцы-помощники
- ✓Результат проверен на правдоподобие (не 0 и не все строки)
- ✓Ответ записан числом или двумя числами через один пробел