Задание 9: обработка данных электронной таблицы

Учимся отвечать на вопросы по таблице чисел двумя путями: формулами Calc/Excel и кодом на Python.

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

Что проверяет задание 9

Задание 9 — базовый уровень, 1 балл. К ЕГЭ прилагается файл электронной таблицы (например, 9.xlsx) с сотнями строк чисел. Вопрос обычно такой: «В скольких строках выполняется условие …?» или «Чему равно среднее/максимум среди строк, удовлетворяющих …?». Считать руками невозможно — нужно либо формулы в LibreOffice Calc / Excel, либо обработка в коде.

На самом экзамене стоит LibreOffice Calc, поэтому формулы знать обязательно. Но идею расчёта удобно сначала продумать и проверить на Python — здесь покажем оба пути.

Приём работы в LibreOffice Calc / Excel

Базовые функции, которых хватает почти на все варианты:

Типовой приём «строка-помощник». Если условие сложное (зависит от нескольких ячеек строки), заведите вспомогательный столбец. Например, в столбце D напишите формулу для каждой строки, которая выдаёт 1, если строка подходит, и 0 иначе. Затем =СУММ(D2:D1001) даст число подходящих строк.

Пусть в строке три числа: A, B, C (столбцы A, B, C).
Условие: максимум больше удвоенного минимума.
В ячейку D2 пишем:
  =ЕСЛИ(МАКС(A2:C2) > 2*МИН(A2:C2); 1; 0)
Протягиваем D2 вниз до конца данных.
Ответ: =СУММ(D2:D1001)

Аналог расчёта на Python

Тот же расчёт на чистом Python (имитируем данные таблицы списком кортежей — на экзамене вы бы читали файл, но здесь важна логика). Это помогает проверить формулу и понять, что именно считаем.

import statistics

# Каждая строка таблицы — кортеж (A, B, C)
data = [
    (12, 45, 78), (90, 11, 33), (5, 60, 22),
    (40, 40, 40), (7, 8, 100), (55, 55, 5),
]

# Сколько строк, где максимум больше удвоенного минимума
count = 0
for a, b, c in data:
    if max(a, b, c) > 2 * min(a, b, c):
        count += 1

# Среднее значение максимумов по всем строкам
avg_max = statistics.mean(max(row) for row in data)

print("Строк, где max > 2*min:", count)
print(f"Среднее максимумов: {avg_max:.2f}")

Вывод:

Строк, где max > 2*min: 5
Среднее максимумов: 70.50

Логику проверили на 6 строках — на экзамене тот же цикл прогонит хоть 5000 строк. Если данные читаются из файла, замените список на чтение и разбор строк.

Чтение реального файла (для понимания)

На КЕГЭ к таблице иногда прилагают и текстовую версию. Принцип чтения такой:

# Имитация содержимого файла: список строк (на экзамене это f.readlines())
lines = ["12 45 78", "90 11 33", "5 60 22",
         "40 40 40", "7 8 100", "55 55 5"]

rows = []
for line in lines:
    nums = [int(x) for x in line.split()]   # разбили строку на числа
    rows.append(nums)

count = sum(1 for r in rows if max(r) > 2 * min(r))
print("Подходящих строк:", count)

Вывод:

Подходящих строк: 5

Условие по среднему всего столбца

Частый сюжет: «в скольких строках значение в столбце B больше среднего по всему столбцу B?» В Calc среднее столбца считают функцией СРЗНАЧ, а затем сравнивают с ним каждую ячейку. Важно: ссылка на ячейку со средним должна быть абсолютной (со знаками $), чтобы при протягивании формулы вниз она не «съезжала».

В ячейку E1 кладём среднее всего столбца B:
  =СРЗНАЧ(B2:B1001)
В столбце F (строка-помощник) для каждой строки:
  =ЕСЛИ(B2 > $E$1; 1; 0)      // $E$1 — абсолютная ссылка, не сдвигается
Ответ — число таких строк:
  =СУММ(F2:F1001)

Тот же расчёт на Python — для проверки логики на маленьком наборе:

data = [
    (12, 45, 78), (90, 11, 33), (5, 60, 22),
    (40, 40, 40), (7, 8, 100), (55, 55, 5),
]

col_b = [row[1] for row in data]          # второй столбец
avg_b = sum(col_b) / len(col_b)           # среднее всего столбца
count = sum(1 for v in col_b if v > avg_b)

print(f"Среднее столбца B: {avg_b:.2f}")
print("Строк, где B больше среднего:", count)

Вывод:

Среднее столбца B: 36.50
Строк, где B больше среднего: 4

Среднее столбца B (45, 11, 60, 40, 8, 55) равно 36,5; больше него — четыре значения (45, 60, 40, 55). Абсолютная ссылка в Calc играет ту же роль, что вынесенная за цикл переменная avg_b в коде: считается один раз, используется во всех сравнениях.

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

• Сдвиг диапазона. Если данные с 2-й строки (1-я — заголовок), не включайте заголовок в СУММ/цикл.
• Относительная ссылка вместо абсолютной. Среднее/порог фиксируйте через $E$1, иначе при протягивании формула «поедет».
• Путают «строки» и «ячейки». Считайте именно то, о чём спрашивают: число строк, а не число ячеек.
• Неверный разделитель десятичных. В русской локали Calc — запятая, а в коде — точка.
• Среднее по всем вместо «по подходящим». Читайте, по какому подмножеству берут среднее.

Итог

• Задание 9 решается формулами Calc/Excel; для сложного условия заводите столбец-помощник с 0/1 и суммируйте его.
• Ключевые функции: СЧЁТЕСЛИ, СУММЕСЛИ, СРЗНАЧ, МИН, МАКС, ЕСЛИ.
• Логику расчёта удобно проверить на Python: цикл по строкам + условие.