Задание №9. Условия и подсчёт в электронных таблицах

В задании №9 исходные данные находятся в файле электронной таблицы. Нужно выделить числа или строки, которые удовлетворяют условиям, а затем выполнить указанный расчёт. Например, посчитать строки с различными числами или найти сумму в первой подходящей строке. Условия отбора и вопрос об ответе выполняют разные задачи: первые определяют, что подходит, второй определяет, что с этим делать.

Что считается одной строкой и что требуется в ответе

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

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

Разбор: сколько строк удовлетворяют двум условиям

В задании EGE-09-0009 ↗ файл 325_9.xlsx содержит 3200 строк по четыре натуральных числа. Требуется количество строк, в которых все четыре числа различны, а наибольшее меньше суммы трёх остальных. На листе «Лист1» данные находятся в A1:D3200, заголовков нет: первая строка уже участвует в расчёте.

1. Проверка различия чисел

Для каждого числа определяется, сколько раз оно встречается в своей строке. Это его частота. Если все четыре частоты равны единице, все числа различны.

В свободной ячейке E1 записывается формула:

=СЧЁТЕСЛИ($A1:$D1;A1)

Первый аргумент задаёт диапазон поиска, второй — искомое значение. При копировании вправо до H1 второй аргумент меняется на B1, C1 и D1. Знаки $ перед A и D удерживают границы исходных четырёх столбцов. Номер строки не закреплён: при копировании вниз формулы переходят к следующему набору.

2. Проверка максимума и суммы

Пусть M — максимальное число строки, S — сумма всех четырёх чисел. Сумма остальных трёх равна S-M, поэтому условие записывается как M<S-M. Прибавление M к обеим частям даёт равносильную проверку 2M<S.

Каждая частота не меньше единицы: ячейка как минимум совпадает сама с собой. Поэтому проверка максимальной частоты на равенство единице означает, что все частоты равны единице. Функция И возвращает ИСТИНА только при выполнении обеих проверок.

3. Одна строка вручную и итог по файлу

Первая строка исходного файла содержит 35, 10, 46, 2. Числа различны, поэтому E1:H1 содержат четыре единицы. Максимум равен 46, сумма равна 35+10+46+2=93. Проверка 2\cdot46<93 верна; непосредственно сумма остальных чисел равна 35+10+2=47, и 46<47. В K1 получается ИСТИНА.

Формулы E1:K1 копируются вниз до строки 3200. Итоговая формула располагается за пределами этого рабочего диапазона:

=СЧЁТЕСЛИ(K1:K3200;ИСТИНА)

В полном файле получаются 2322 подходящие строки. Это и есть ответ. Сумма их чисел или номер последней строки в данном вопросе не нужны.

Формулы выше используют русские имена функций и разделитель ;. При английских именах соответствия такие: СЧЁТЕСЛИ → COUNTIF, МАКС → MAX, СУММ → SUM, И → AND, ИСТИНА → TRUE. Разделитель аргументов зависит от настроек Excel или Calc.

Когда в строке есть повторения

Фраза «два числа повторяются дважды, остальные три различны» описывает семь ячеек: две пары и три одиночных значения. Частоты по позициям должны содержать четыре двойки и три единицы. Двойки считаются по ячейкам, поэтому их четыре, хотя различных повторяющихся значений всего два.

В EGE-09-0003 ↗ к этому добавлено условие: среднее всех повторяющихся чисел меньше максимального неповторяющегося. Требуется наименьший номер подходящей строки. Файл 4_9.xlsx содержит данные в A1:G16000.

Частоты можно разместить в H1:N1, начиная с =СЧЁТЕСЛИ($A1:$G1;A1). Тогда проверка состава строки имеет вид:

=И(СЧЁТЕСЛИ(H1:N1;2)=4;СЧЁТЕСЛИ(H1:N1;1)=3)

У первой подходящей строки исходный номер 17. В ней записаны следующие числа:

Повторяющиеся числа с учётом всех вхождений: 67, 67, 40, 40. Их среднее равно (67+67+40+40)/4=53{,}5. Неповторяющиеся числа: 74, 76, 96; их максимум — 96. Неравенство 53{,}5<96 выполнено. Строки 1–16 не содержат нужных двух пар, поэтому ответ — 17.

Для обработки всего файла сумма повторяющихся ячеек определяется формулой =СУММЕСЛИ(H1:N1;2;A1:G1): условие проверяется по частотам, а суммируются соответствующие исходные числа. В строке с нужным составом деление этой суммы на 4 даёт требуемое среднее. Максимум одиночных значений определяется =МАКСЕСЛИ(A1:G1;H1:N1;1). Английские имена — SUMIF и MAXIFS. Итоговый признак соединяет проверку состава и сравнение этих двух результатов через И; после копирования выбирается первая строка с ИСТИНА.

Другой состав требует других частот. Например, «одно число встречается трижды, остальные четыре различны» означает три тройки и четыре единицы. Такой вариант есть в EGE-09-0004 ↗. В нём нужна сумма последней подходящей строки: строка 41990 содержит 10, 176, 155, 176, 176, 141, 67; её сумма — 901.

Другие условия и итоги

Две пары с равными суммами

Равные суммы пар не означают равные числа. Например, во второй строке файла 1_9.xlsx из EGE-09-0010 ↗ записаны 6, 14, 19, 27. Все числа различны, но 6+27=14+19=33.

Для четырёх ячеек достаточно проверить три разбиения: A с B против C с D; A с C против B с D; A с D против B с C. Число из A обязано попасть в пару с одним из остальных трёх, поэтому других разбиений нет.

=ИЛИ(A1+B1=C1+D1;A1+C1=B1+D1;A1+D1=B1+C1)

В ИЛИ достаточно одного истинного равенства. Если разбиение требуется запретить, результат этой формулы отрицается через НЕ. Английские имена — OR и NOT. Остальные условия сохраняются: в EGE-09-0010 максимум также должен быть меньше суммы остальных. Полный подсчёт даёт 118. Противоположное требование «нельзя разбить» есть в EGE-09-0001 ↗.

Квадраты, произведения и несколько наибольших чисел

В EGE-09-0026 ↗ сравниваются квадрат суммы крайних по величине чисел и сумма квадратов остальных. Если m — минимум, M — максимум, а остальные три числа обозначены b,c,d, проверяется (m+M)^2>b^2+c^2+d^2. Скобки обязательны: (m+M)^2 и m^2+M^2 — разные выражения.

Сумма квадратов оставшихся получается из суммы квадратов всех пяти ячеек вычитанием одного квадрата минимума и одного квадрата максимума. Для A1:E1 это СУММКВ(A1:E1)-МИН(A1:E1)^2-МАКС(A1:E1)^2; английские имена — SUMSQ и MIN. Повторы в этом условии не запрещены. Даже если максимум встречается несколько раз, удаляется только одно его вхождение. По полному файлу ответ равен 2623.

В EGE-09-0024 ↗ речь уже о произведениях: 3mM\leq2bcd. В EGE-09-0008 ↗ нужна сумма двух наибольших чисел. Второе по величине с учётом повторов даёт НАИБОЛЬШИЙ(A1:E1;2) (LARGE). Вычитание максимума из суммы оставляет сумму четырёх чисел, а не второе наибольшее число.

Номер, сумма и среднее

В EGE-09-0002 ↗ первая подходящая строка имеет номер 59 и содержит 255, 85, 34, 68, 38. Требуется сумма: 255+85+34+68+38=480. Число 59 было бы ответом на другой вопрос.

В EGE-09-0006 ↗ после отбора остаются 869 строк по шесть чисел. Сумма всех этих чисел — 307439, их количество — 869\cdot6=5214. Поэтому среднее равно 307439/5214\approx58{,}9641, а требуемая целая часть — 58. Деление на 869 дало бы среднюю сумму строки, которая в шесть раз больше.

Измерения за выбранный период

В EGE-09-0039 ↗ строки файла 613_9.xlsx соответствуют дням, столбцы — часам. Нужны апрель и май, с 9:00 до 12:00 включительно. Это четыре показания за каждый из 61 дней, всего 61\cdot4=244 значения. На листе «Лист3» они занимают диапазон K2:N62.

=МАКС(K2:N62)-СРЗНАЧ(K2:N62)

Максимум равен 28{,}8 градуса, среднее — 5636/244\approx23{,}0984 градуса. Разность составляет примерно 5{,}7016 градуса; её целая часть — 5. Английское имя СРЗНАЧ — AVERAGE. Максимум и среднее здесь берутся по одному и тому же диапазону.

В других файлах один день описан несколькими показателями: максимумом, минимумом и средней температурой, давлением, ветром и осадками. Например, в EGE-09-0040 ↗ требуются максимум температуры сентября и минимум декабря. Заголовки определяют нужные столбцы; максимум среднего за сутки не заменяет максимум температуры.

Возможные ловушки

Проверка повторов по всей таблице. В примере с двумя парами ↗ число 67 может встречаться и в других строках. Условие относится только к текущим семи ячейкам. Диапазон $A$1:$G$16000 посчитает посторонние вхождения; $A1:$G1 удержит столбцы и позволит менять строку при копировании.

Две пары приняты за две двойки в частотах. В строке 17 того же задания каждое из двух повторяющихся значений занимает две ячейки. Поэтому частота 2 записана четыре раза. Проверка «двоек ровно две» обнаруживает только одну пару и отбирает строки другого состава.

Подмена строгого сравнения нестрогим. «Не больше» в EGE-09-0008 ↗ допускает равенство; знак < потеряет такие строки. «Меньше» в основном примере ↗, наоборот, исключает равенство. Знак переносится из текста до начала подсчёта.

Первая подходящая строка заменена строкой с наименьшей суммой. В EGE-09-0002 ↗ сравниваются номера, а не суммы. Сортировка по сумме изменит выбор. Сначала определяется исходная строка 59, только затем вычисляется 480.

Добавлено отсутствующее требование различия. В задаче о квадратах ↗ числа могут повторяться. Дополнительный запрет повторов уменьшит количество подходящих строк. «Остальные три» означает три оставшиеся ячейки после удаления по одному минимуму и максимуму, а не удаление всех равных им значений.

Потерян край диапазона или выбрана другая величина. В примере с часами ↗ включены и 9:00, и 12:00: это четыре столбца. Выбор только трёх часов меняет и максимум, и среднее. В таблице суточных показателей ↗ дополнительно проверяется название столбца, а не только месяц.

Целая часть заменена округлением. Для среднего 58{,}9641\ldots в EGE-09-0006 ↗ округление дало бы 59, а условие требует 58. Дробная часть отбрасывается у окончательного результата. Для этого подходит ОТБР(значение;0) (TRUNC); округление промежуточных средних может изменить итоговую целую часть.