Задание 9: Обработка числовой информации в электронных таблицах

1. Общая информация о задании

• Уровень сложности: базовый
• Максимальный балл: 1
• Примерное время выполнения: около 6 минут
• Проверяемый навык: умение обрабатывать числовую информацию в электронных таблицах
• Формат на экзамене: файл .ods (LibreOffice Calc / OpenOffice Calc). Microsoft Excel не используется.

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

Задания: N9.Таблицы | N9.Таблицы++

2. Все формулы для Excel / LibreOffice Calc

Ниже приведены функции, которые используются в задании 9. В LibreOffice Calc в качестве разделителя аргументов используется точка с запятой (;). В русской версии Excel также точка с запятой (;) при настройке по умолчанию.

2.1. Сумма, количество, среднее, минимум, максимум

2.2. Условные функции (одно условие)

Условие в кавычках задаётся как строка: ">25", "<0", "=яблоки". Для подстановки значения из ячейки используют конкатенацию: ">="&E2*0,9.

2.3. Несколько условий

2.4. Логические функции

Их часто используют внутри ЕСЛИ: =ЕСЛИ(И(A2>5; B2<10); "Да"; "Нет").

2.5. Математические и округление

В ответе часто просят «только целую часть»: для этого подходит ЦЕЛОЕ (не округлять, а отбросить дробную часть).

2.6. Уникальные значения (УНИК)

Функция УНИК (Excel 365, LibreOffice Calc 24.2+) возвращает список уникальных значений из диапазона.

Используется в задачах на проверку «ровно N различных чисел» или «ровно M значений встречаются один раз». В паре с СЧЁТЕСЛИ можно считать повторения и выделять повторяющиеся/неповторяющиеся числа.

2.7. Поиск и подстановка (для связей между данными)

2.8. Операторы сравнения и арифметики в формулах

3. Типы ссылок на ячейки

При копировании и протягивании формул поведение ссылок разное.

Пример: чтобы при копировании формулы по строке диапазон «не съезжал», его задают абсолютно: =ЕСЛИ(МАКС($B$18:$AI$18)=B18; B1; "").

4. Алгоритм решения задания 9

1. Внимательно прочитать условие: что нужно найти (разность макс и среднего, количество по условию, сумму по условию, номер строки с макс/мин и т.д.).
2. Определить диапазоны данных (столбцы и строки), к которым применяются условия.
3. Выбрать подходящие функции:
   • разность макс и среднего → МАКС, СРЗНАЧ, затем вычитание;
   • количество ячеек по условию → СЧЁТЕСЛИ (или СЧЁТЕСЛИМН);
   • среднее/сумма/макс/мин по условию → СРЗНАЧЕСЛИ, СУММЕСЛИ, МАКСЕСЛИ, МИНЕСЛИ;
   • проверка условий по строкам → вспомогательный столбец с ЕСЛИ и И/ ИЛИ, затем СУММ или СЧЁТ.
4. При необходимости ввести вспомогательные столбцы (например, 1/0 по условию «тест сдан»), затем суммировать или посчитать количество.
5. Записать ответ в требуемом формате (целая часть, два числа через пробел и т.д.).

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

• Путать относительные и абсолютные ссылки при копировании: диапазон «уезжает» и результат неверный.
• В СЧЁТЕСЛИ / СУММЕСЛИ и т.п. условие с оператором сравнения писать в кавычках: ">25", а не >25.
• При необходимости подставить в условие значение из ячейки не использовать кавычки для всего выражения, а сцеплять: ">="&A1*0,9.
• В ответе требовать целую часть, а записать округлённое или дробное число: нужно использовать ЦЕЛОЕ или отбросить дробную часть по условию.
• Не учесть пустые ячейки: СРЗНАЧ их не учитывает; СЧЁТ считает только ячейки с числами.

6. Краткий чек-лист

1. Определить, что искать: сумму, среднее, количество, макс/мин, разность, номер строки.
2. Выделить диапазоны и условия.
3. Выбрать функцию (или комбинацию): СУММ / СРЗНАЧ / МАКС / МИН или СЧЁТЕСЛИ / СУММЕСЛИ / ЕСЛИМН и т.д.
4. При копировании формулы проверить $ в ссылках.
5. Ответ записать в нужном формате (целое число, два числа и т.д.).

7. Решение заданий с помощью кода (Python)

Часть заданий 9 можно решить программой, если данные из таблицы экспортированы в текстовый файл (по строкам, числа в строке через пробел). Ниже пример задачи 901: в каждой строке семь натуральных чисел; нужно найти наибольший номер строки, где одно число повторяется трижды, остальные четыре различны, и среднее повторяющихся больше среднего всех чисел в строке.

maxi = 0
n = 0
for i in open('901'):
    n = n + 1
    a = [int(x) for x in i.split()]
    pov3 = [x for x in a if a.count(x) == 3]
    pov1 = [x for x in a if a.count(x) == 1]
    if len(pov3) == 3 and len(pov1) == 4:
        if sum(pov3) / 3 > sum(a) / 7:
            maxi = n
print(maxi)

Идея: перебираем строки файла, ведём номер строки n. Для каждой строки строим список a, через подсчёт вхождений выделяем числа, встречающиеся 3 раза и 1 раз. При выполнении обоих условий запоминаем номер в maxi (не использовать имя max, чтобы не перекрывать встроенную функцию). В конце выводится наибольший такой номер.

Типы ответов в задачах: количество строк (счётчик +1 при выполнении условий), наибольший номер строки (обновлять переменную при каждом совпадении), наименьший номер строки (при первом совпадении запомнить номер и выйти из цикла), сумма чисел в строке с наибольшим номером (хранить номер и сумму, обновлять при совпадении и большем номере).

8. Файлы решений и унифицированный шаблон

В папке Решение/ лежат программы для задач 901-917:

• 901.py … 917.py: решения на Python. Каждый скрипт читает файл с тем же номером (например, open('901')), проверяет условия по строкам и выводит ответ. В конце файла указан полученный ответ.
• пример.md: унифицированный шаблон: справочная таблица всех задач (число чисел в строке, тип ответа, кратко условия), общие шаблоны в трёх форматах (таблица Excel с формулами, псевдокод, Python), полный разбор задачи 905 (таблица, псевдокод, код).

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