Электронные таблицы
Задание 9 ЕГЭ по информатике: электронные таблицы и условия на строки — разбор и примеры
В девятом задании к условию приложен файл электронной таблицы: в каждой строке записано несколько натуральных чисел (обычно от пяти до семи), а строк — несколько тысяч. Нужно определить, сколько строк удовлетворяют одновременно двум условиям. Первое условие обычно касается повторов чисел в строке («ровно одно число повторяется дважды, остальные различны», «все числа различны», «есть три одинаковых числа»). Второе — соотношения величин: сравнение сумм, средних, наибольшего и наименьшего значений.
Задание проверяет умение организовать вычисления в электронной таблице: вспомогательные столбцы, подсчёт повторов, логические функции. С тем же успехом его можно решить программой на Python, прочитав данные из файла.
Что проверяет задание
- умение использовать функции
СЧЁТЕСЛИ,СУММ,МАКС,МИН,СРЗНАЧ,СУММПРОИЗВ, логические функцииЕСЛИ,И,ИЛИ; - понимание относительных и абсолютных ссылок при копировании формул на тысячи строк;
- умение точно перевести словесное условие в формулу или проверку.
Типичные формулировки
- «В строке только одно число повторяется ровно два раза, остальные числа различны».
- «Все числа строки различны» или «в строке есть число, повторяющееся трижды».
- Второе условие: «сумма повторяющихся чисел больше …», «среднее неповторяющихся чисел не больше …», «наибольшее число строки меньше суммы трёх остальных», «повторяющееся число больше разности наибольшего и наименьшего».
- Вопрос: «Сколько строк удовлетворяют обоим условиям?» или «Найдите номер первой (последней) такой строки».
Пошаговый метод
- Разберите условие на части. Для каждой части решите, какая величина нужна: количество повторов, повторяющееся число, сумма неповторяющихся, максимум, минимум.
- Посчитайте повторы. Для каждой ячейки строки
СЧЁТЕСЛИ(строка; ячейка)показывает, сколько раз число встречается в строке. Условие «ровно одно число дважды, остальные различны» для шести чисел означает: ровно две ячейки со счётчиком 2 и четыре ячейки со счётчиком 1. - Найдите нужные величины вспомогательными формулами.
- Сведите всё в одну логическую формулу, которая даёт 1 для подходящей строки и 0 иначе.
- Скопируйте формулы на все строки и сложите итоговый столбец.
- Проверьте вручную две-три строки, особенно такие, где условие «почти» выполняется.
Разбор примера
Дан фрагмент таблицы из 10 строк по шесть чисел (в настоящем файле строк гораздо больше):
| № | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | 12 | 7 | 30 | 12 | 5 | 9 |
| 2 | 4 | 18 | 4 | 18 | 2 | 6 |
| 3 | 25 | 3 | 25 | 40 | 31 | 27 |
| 4 | 8 | 8 | 8 | 1 | 2 | 3 |
| 5 | 15 | 11 | 6 | 9 | 15 | 19 |
| 6 | 9 | 14 | 22 | 5 | 17 | 1 |
| 7 | 20 | 13 | 20 | 7 | 3 | 16 |
| 8 | 6 | 33 | 10 | 6 | 21 | 2 |
| 9 | 11 | 2 | 11 | 4 | 1 | 3 |
| 10 | 27 | 19 | 35 | 27 | 10 | 40 |
Сколько строк удовлетворяют обоим условиям: в строке ровно одно число повторяется дважды, остальные четыре числа различны; повторяющееся число больше разности между наибольшим и наименьшим числами строки?
Шаг 1. Первое условие. Строка 2 не подходит — повторяются два числа (4 и 18). Строка 4 не подходит — число 8 встречается трижды. Строка 6 не подходит — повторов нет. Остальные строки (1, 3, 5, 7, 8, 9, 10) удовлетворяют первому условию.
Шаг 2. Второе условие для оставшихся строк:
| Строка | Повтор | Макс − мин | Подходит |
|---|---|---|---|
| 1 | 12 | 30 − 5 = 25 | нет |
| 3 | 25 | 40 − 3 = 37 | нет |
| 5 | 15 | 19 − 6 = 13 | да |
| 7 | 20 | 20 − 3 = 17 | да |
| 8 | 6 | 33 − 2 = 31 | нет |
| 9 | 11 | 11 − 1 = 10 | да |
| 10 | 27 | 40 − 10 = 30 | нет |
Ответ: 3 (строки 5, 7 и 9).
Решение формулами
Пусть числа строки 1 находятся в ячейках A1:F1. В соседних столбцах:
- G1 — число ячеек, значение которых встречается дважды:
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(A1:F1;A1:F1)=2)); - H1 — число ячеек с уникальными значениями:
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(A1:F1;A1:F1)=1)); - I1 — повторяющееся число:
=СУММПРОИЗВ((СЧЁТЕСЛИ(A1:F1;A1:F1)=2)*A1:F1)/2; - J1 — итог:
=ЕСЛИ(И(G1=2;H1=4;I1>МАКС(A1:F1)-МИН(A1:F1));1;0).
Формулы протягиваются вниз на все строки, ответ — =СУММ(J:J). Конструкция СЧЁТЕСЛИ(A1:F1;A1:F1) возвращает массив из шести счётчиков — по одному для каждой ячейки, а СУММПРОИЗВ их обрабатывает без ввода формулы массива. Если такая формула кажется сложной, сделайте шесть отдельных столбцов =СЧЁТЕСЛИ($A1:$F1;A1) и считайте по ним.
Код на Python
Если сохранить таблицу в формате CSV (разделитель — точка с запятой), задача решается за несколько строк:
count = 0
with open('9.csv', encoding='utf-8') as f:
for line in f:
r = [int(x) for x in line.split(';')]
rep = [x for x in set(r) if r.count(x) == 2]
single = [x for x in r if r.count(x) == 1]
if len(rep) == 1 and len(single) == 4 and rep[0] > max(r) - min(r):
count += 1
print(count)Для фрагмента из примера программа выдаёт 3. Проверка len(single) == 4 вместе с len(rep) == 1 гарантирует, что повтор ровно один и других совпадений нет: строка с тройкой одинаковых чисел даст rep пустым, строка с двумя парами — len(rep) == 2.
Частые ошибки
- Неверно проверяют «ровно один повтор». Условие «есть число, встречающееся дважды» не исключает второй пары или тройки.
- Путают число и количество его вхождений. В сумме повторяющихся чисел повтор учитывается дважды (число × 2), а «повторяющееся число» — один раз.
- Строгие и нестрогие неравенства. «Больше» и «не меньше» дают разный ответ на граничных строках.
- Ошибаются при копировании формул, если внутри стоят абсолютные ссылки на строку 1.
- Сохраняют CSV с запятой в качестве разделителя, а в программе делят по точке с запятой — проверьте первую строку файла.
Потренируйтесь
Попробуйте разные сочетания условий: задания № 9 в банке. Приёмы работы с формулами собраны в статье «Электронные таблицы: формулы и рабочие шаблоны». Следующее задание — задание 10: IP-адреса и маски.