В прошлой статье мы подробно разобрали первый тип 9 задания – изучили формулировки, освоили алгоритмы проверки повторений чисел, научились работать с наименьшими и наибольшими значениями. Теперь пришло время покорить вторую вершину!
Что изменилось? Задача остаётся знакомой: определить строки, где выполняются заданные условия. Но вот финал другой – теперь нужно не просто подсчитать количество таких строк, а найти конкретное значение: наименьший или наибольший номер строки таблицы, в которой выполняются все условия.
Хорошая новость: это почти не повлияет на наш алгоритм решения. Разве что в конце придётся либо вручную поискать нужное значение в таблице, либо воспользоваться фильтрацией.
Не будем долго затягивать и сразу перейдём к разбору алгоритма решений 9 заданий второго типа!
Пример 1
Для начала возьмём задание с такой формулировкой:
«Откройте файл электронной таблицы, содержащей в каждой строке семь натуральных чисел. Определите наименьший номер строки таблицы, для чисел которой выполнены оба условия:
– в строке есть два числа, каждое из которых повторяется дважды, остальные три числа различны;
– среднее арифметическое всех повторяющихся чисел строки меньше её максимального неповторяющегося числа.
В ответе запишите только число.»
Прежде чем браться за решение, давайте освежим в памяти важный момент: как понять, что в наборе чисел ровно два числа повторяются дважды?
Представим себе такой набор: 1, 1, 3, 3, 4, 5, 6.
Сразу видно: единица и тройка появляются по два раза. Значит, условие «два числа, каждое из которых повторяется дважды, остальные три числа различны» выполнено!
В прошлой статье мы уже разбирали формулу, которая позволяет определить, сколько раз встречается заданное число в исходном диапазоне. Выглядит она так: «=СЧЁТЕСЛИ($A1:$G1;A1)», то есть мы подсчитываем, сколько раз число из ячейки А1 встречается в диапазоне от А1 до G1.
Вставим эту формулу в ячейку А3 и растянем вправо до G3.
Смотрите: под единицей и тройкой появились числа 2. Они нам и сигнализируют, что числа 1 и 3 встречаются в исходной строке ровно по 2 раза.
Часто нам нужно работать раздельно: с повторяющимися числами – отдельно, с неповторяющимися – отдельно. Давайте найдём способ их разделить.
Внимательно посмотрите на вторую строку (№3 в таблице) с изображения выше. В ней скрыта подсказка – своеобразная «маска», по которой можно «отфильтровать» исходные числа. Вывод напрашивается сам собой: если нужны только неповторяющиеся числа, берём из исходной строки только те ячейки, для которых функция СЧЁТЕСЛИ() возвращает 1.
Соответственно, для всех повторяющихся чисел эта функция будет возвращать результат больше 1.
Саму «фильтрацию» проведём с помощью функции ЕСЛИ(): когда условие истинно, она вернёт значение ячейки из исходного диапазона, а в противном случае оставит ячейку пустой.
Давайте в ячейку А5 запишем такую формулу: «=ЕСЛИ(СЧЁТЕСЛИ($A1:$G1;A1)>1;A1;"")», тем самым получим строку только с повторяющимися числами исходного набора (единицы и тройки).
Теперь мы готовы к решению задания. Откроем приложенный файл и взглянем на данные.
Нам предстоит работать сразу с семью столбцами. Для удобства сразу уменьшим ширину столбцов, чтобы на экране помещалось больше информации.
Первым делом перенесём только повторяющиеся числа уже знакомой нам формулой: «=ЕСЛИ(СЧЁТЕСЛИ($A1:$G1;A1)>1;A1;"")».
Далее убедимся, что повторяются действительно только 2 числа по 2 раза каждое. Это значит, что в подходящей строке у нас должно быть ровно 4 числа в столбцах от H до N.
Используем функцию СЧЁТ() для подсчёта значений в диапазоне от H до N. Если их ровно четыре – возвращаем истину, иначе – ложь. Формула простая: «=СЧЁТ(H1:N1)=4».
Таким образом, в столбце O у нас теперь отображаются результаты проверки первого условия: «в строке есть два числа, каждое из которых повторяется дважды, остальные три числа различны». Двигаемся дальше!
Вычислим среднее арифметическое повторяющихся чисел. Для этого воспользуемся функцией СРЗНАЧ(). Обратите внимание, что для строк, в которых нет повторяющихся чисел эта функция вернёт ошибку деления на ноль.
Чтобы этого избежать, снова призовём на помощь функцию ЕСЛИ(): если строка уже прошла первое условие (в столбце O стоит ИСТИНА), вычисляем среднее арифметическое повторяющихся чисел (из столбцов H до N), в противном случае записываем в ячейку 0 (можно оставить и пустой, но с нулём нагляднее).
Теперь сформируем 7 столбцов только с неповторяющимися числами исходной строки. Воспользуемся формулой: «=ЕСЛИ(СЧЁТЕСЛИ($A1:$G1;A1)=1;A1;"")»
Найдём максимальное число среди неповторяющихся с помощью функции МАКС(): «=МАКС(Q1:W1)».
Финишная прямая! Осталось лишь потребовать выполнения обоих условий одновременно: в столбце O должна стоять истина, а значения столбца P (среднее арифметическое повторяющихся) должны быть меньше значений столбца X (максимальное из неповторяющихся). Если оба условия выполняются – возвращаем 1, иначе – 0. Формула выглядит так: «=ЕСЛИ(И(O1;P1<X1);1;0)».
Осталось лишь найти номер первой строки, которая в столбце Y имеет значение 1. Можно просто прокрутить таблицу до нужного места или воспользоваться автофильтром, оставив только строки с единицей в столбце Y.
Любым из этих способов мы получаем искомый результат – наименьший номер строки, где выполняются оба условия, а именно 17. Это и есть наш ответ!
Пример 2
Для закрепления решим еще одно задание:
«Откройте файл электронной таблицы, содержащей в каждой строке пять натуральных чисел. Определите наибольший номер строки таблицы, для которой выполнены оба условия:
– в строке все числа различны;
– удвоенная сумма минимального и максимального чисел строки равна утроенной сумме трёх её оставшихся чисел.
В ответе запишите только число.»
Здесь задача немного другая – нужно найти самую последнюю строку таблицы, где выполняются оба условия. Причём с первым условием мы уже встречались в прошлой статье. Давайте сразу приступим к решению.
Откроем файл и взглянем на данные.
По уже отработанному алгоритмы находим количество вхождений каждого числа в исходную строку: «=СЧЁТЕСЛИ($A1:$E1;A1)».
Потребуем, чтобы количество единиц было равно пяти: «=СЧЁТЕСЛИ(F1:J1;1)=5», что и означает наличие 5 различных чисел в строке.
Найдём максимальное (оранжевый столбец) и минимальное (зелёный столбец) числа исходной строки. Здесь нам помогут одноимённые функции МАКС() и МИН(), в которые мы передадим диапазон значений от столбца А до Е.
Вычислим утроенную сумму оставшихся чисел. Это несложно: считаем общую сумму, вычитаем значения из столбцов L и M и умножаем результат на три:: «=3*(СУММ(A1:E1)-L1-M1)».
Чтобы второе условие выполнилось полностью, потребуем, чтобы удвоенная сумма значений столбцов L и M равнялась утроенной сумме оставшихся чисел из столбца N: «=2*СУММ(L1:M1)=N1».
Отлично! Результаты обоих условий перед нами. Теперь в функции ЕСЛИ() будем возвращать 1, если оба условия выполняются одновременно, а в противном случае – 0.
Применяем к данным автофильтр и оставляем только те строки, где в столбце P стоит единица.
Из оставшихся строк выбираем самую последнюю – она имеет номер 13412. Это значение и записываем в ответ.
Мы успешно разобрали алгоритм решения двух типов 9 заданий в LibreOffice Calc. В следующей статье завершим работу с этими заданиями и рассмотрим решение третьего типа, где потребуется не только находить номер подходящей строки, но и вычислять сумму чисел в ней.