Условия, повторения и итоги в таблицах

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

Ячейка, значение и диапазон

Ячейка — место на пересечении строки и столбца. Адрес B4 указывает на столбец B и строку 4. Значение — содержимое ячейки: например, число 12. Две разные ячейки могут хранить одинаковое значение.

Диапазон задаёт группу ячеек. Запись A1:D1 обозначает четыре ячейки одной строки, а A1:D100 — прямоугольник из ста строк и четырёх столбцов. Если условие касается чисел одной строки, поиск по всей таблице меняет смысл проверки.

При копировании формул относительные ссылки сдвигаются. Например, в формуле =СЧЁТЕСЛИ($A1:$D1;A1) копирование вправо меняет искомую ячейку A1 на B1, но оставляет диапазон A1:D1. Копирование вниз меняет все номера строк на 2. Так одна формула сохраняет свой смысл для каждого набора.

Условие как ответ «да» или «нет»

Логическое условие для строки имеет два возможных результата: истина или ложь. Например, «все числа различны» либо выполняется, либо нет. Если обязательны два требования, строка подходит только при выполнении обоих. В электронной таблице их соединяет И.

«Хотя бы одно» включает случай, когда верны оба требования. Функция НЕ меняет истину на ложь и наоборот. Например, «нет разбиения на равные пары» отрицает возможность такого разбиения целиком.

Знаки сравнения сохраняют границу: «меньше» означает <, «не больше» — \leq, «больше» — >, «не меньше» — \geq. В формулах таблицы нестрогие знаки записываются как <= и >=.

Повторения: значения и их вхождения

Частота значения — количество его вхождений в рассматриваемой строке. Для учебной строки 4, 9, 4, 7, 9 частоты по позициям равны 2, 2, 2, 1, 2. Различных значений три: 4, 7, 9. Повторяющихся значений два: 4 и 9. Ячеек с повторяющимися значениями четыре.

Если некоторое значение повторяется трижды, частота 3 будет записана у каждой из трёх его ячеек. Поэтому число ячеек с частотой 3 нужно делить на 3, чтобы получить количество различных значений, встречающихся трижды.

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

Сумма, среднее и крайние значения

Пусть строка содержит n чисел x_1,\ldots,x_n. Их сумма обозначается S, а среднее арифметическое — \overline{x}:

S=x_1+\cdots+x_n,\qquad \overline{x}=\frac{S}{n}.

Знаменатель — количество складываемых чисел, включая повторы. Для 4, 4, 4, 10, 10 среднее всех пяти чисел равно (4+4+4+10+10)/5=6{,}4. Среднее двух различных значений равно (4+10)/2=7. Замена одного другим теряет информацию о частотах.

Если два значения a и b встречаются по два раза, среднее четырёх вхождений можно сократить:

\frac{a+a+b+b}{4}=\frac{2(a+b)}{4}=\frac{a+b}{2}.

Такое сокращение возможно благодаря одинаковым частотам. При разных частотах, как в примере с тремя четвёрками и двумя десятками, оно неверно.

Максимум M — наибольшее значение строки. Сумма после удаления одного его вхождения равна S-M, даже если максимум повторяется. Для строки 8, 8, 3 сумма равна 19; после удаления одной восьмёрки остаются 8 и 3, их сумма 19-8=11. Удаление обеих восьмёрок решает другую задачу.

В выражении «среднее неповторяющихся чисел» сначала выбираются ячейки с частотой 1, затем их сумма делится на их количество. Условие о составе строки часто заранее задаёт этот знаменатель. Если требуется три одиночных числа, делить нужно на 3, а не на длину всей строки.

Почему для равных пар достаточно крайних чисел

Пусть четыре числа расположены по неубыванию: a\leq b\leq c\leq d. Их можно разбить на две пары с равными суммами тогда и только тогда, когда a+d=b+c. Это позволяет проверять крайние числа вместе и два средних вместе.

Равенство сразу даёт нужные пары. Обратное утверждение следует из трёх возможных разбиений. Если равны a+b и c+d, то каждое число первой пары не больше соответствующего числа второй. Равенство сумм возможно лишь при равенстве всех четырёх чисел. Тогда равны и суммы крайних и средних.

Если равны a+c и b+d, то (b-a)+(d-c)=0. Оба слагаемых неотрицательны, поэтому каждое равно нулю: a=b и c=d. Снова получается a+d=b+c. Третье разбиение как раз и проверяет это равенство. Доказательство допускает повторяющиеся числа.

Итог после отбора

Подсчёт подходящих строк складывает единицы: по одной за строку. Сумма чисел складывает сами значения. Поиск первого номера возвращает положение строки в исходном файле. Эти действия нельзя заменять друг другом даже при одинаковом наборе подходящих строк.

Если отобраны k строк по n чисел и сумма всех их значений равна T, то среднее всех чисел равно T/(kn). Среднее сумм строк равно T/k. Они отличаются множителем n.

В таблицах измерений прежде всего определяется группа наблюдений. Среднее температуры с 9:00 до 12:00 включительно использует четыре показания за день. Максимум и среднее должны относиться к тем датам и часам, которые указаны в условии. Если число наблюдений в группах различается, среднее общих данных нельзя получать простым усреднением средних групп: сначала нужны их суммы и количества.

Отбрасывание дробной части выполняется только тогда, когда этого требует вопрос, и только после полного расчёта. Например, для 5{,}7 получается 5, а не 6. В электронной таблице для усечения используется ОТБР (TRUNC).

Применение к данным файла

Как превратить фразу «две пары и три одиночных числа» в проверку частот? Как после одного и того же отбора получить количество строк, исходный номер или среднее всех выбранных чисел?

Эти вопросы рассмотрены на данных файлов в статье «Задание №9. Условия и подсчёт в электронных таблицах».