Вставьте следующее в строку формул для первой ячейки результата (верхний результат). Вставка непосредственно в ячейку может разделить эту формулу на несколько ячеек из-за форматирования разрыва строки:
=IFERROR( INDEX( $R$4:$R$13, MATCH(
MAX( IF( $Q$4:$Q$13=$R$14, IF( NOT( COUNTIF( $Q$14:$Q14, $R$4:$R$13)), $S$4:$S$13, ""), "")),
IF( $Q$4:$Q$13=$R$14, IF( NOT( COUNTIF( $Q$14:$Q14, $R$4:$R$13)), $S$4:$S$13, ""), ""),
0)), "")
Изменить:
- $ Q $ 4:$ Q $ 13 = столбец данных за месяц
- $ R $ 14 = ячейка списка выбора месяца
- $ R $ 4:$ R $ 13 = столбец данных заголовка страницы (заголовки за данный месяц
должен быть уникальным, см. ниже)
- $ Q $ 14:$ Q14 = Ячейка выше формулы верхнего результата (на ячейку ссылаются
не должен содержать возможный заголовок страницы)
- $ S $ 4:$ S $ 13 = Сравнительный номер (просмотров страниц или посетителей) либо
из столбца данных или столбца вычисленного результата (промежуточный / вспомогательный)
- Все ссылки являются абсолютными, кроме номера строки второй ячейки в
"результаты до сих пор" колеблются от $ Q $ 14:$ Q 14
- Следующие диапазоны должны быть одинакового размера:
- Столбец данных за месяц $ Q $ 4:$ Q $ 13
- Колонка данных заголовка страницы $ R $ 4:$ R $ 13
- Номер сравнения (просмотры страниц) Столбец данных $ S $ 4:$ S $ 13
После редактирования введите Ctrl - Shift - Enter, поскольку это формула CSE.
Затем перетащите вниз столько строк, сколько нужно. (Это может быть установлено программно из другого списка выбора.)
Уникальные значения: если два заголовка страницы (скажем, "Домашняя" страница с двух разных сайтов) могут быть одинаковыми для данного месяца, тогда потребуется вспомогательный столбец.
- Вспомогательный столбец будет содержать приведенную выше формулу с некоторыми дополнительными столбцами
объединенные, скажем, <url> & <title>, чтобы однозначно идентифицировать строки внутри
заданный месяц (более двух столбцов конкатенации тоже хорошо).
- Везде, где формула использует заголовок страницы
$R$4:$R$13 , заменяет конкатенацию
$U$4:$U$13 & $R$4:$R$13 , где $ U $ 4:$ U $ 13 - столбец данных url.
- Даже первый аргумент INDEX подставляется, что сгенерирует
конкатенированный вспомогательный столбец результатов.
- Значение $ Q $ 14:$ Q14 теперь является ссылкой на ячейку над столбцом помощника.
ячейка верхнего результата (над ячейкой с этой формулой). Ссылка на ячейку (выше
список результатов вспомогательных столбцов) не должен содержать значений, которые могли бы
значения, найденные в результатах конкатенации этого вспомогательного столбца.
- Формула отображаемых результатов будет
=IFERROR( INDEX( $R$4:$R$13, MATCH( $H15, $U$4:$U$13 & $R$4:$R$13, 0)), "")
где $ H15 - первая (верхняя) ячейка результата в столбце помощника.
- Эта новая формула является CSE из-за конкатенации массива.
- Скопируйте и перетащите вниз, чтобы увеличить относительную ссылку на строку $ H15 (вспомогательный столбец
первая ячейка), поэтому ссылка на строку увеличивается для ссылки на следующие строки.
- Проверено.
Как это устроено
- IFERROR - это простая защита, когда все данные за данный месяц уже ранжированы и отображены в результатах.
INDEX($R$4:$R$13, ) используется для получения результата сопоставления значений из столбца данных Page Title.- MATCH и MAX используют один и тот же список значений. Соответствие находит индекс в этом списке, где находится МАКС. Списка.
- Ряд вложенных IF, приводящих к массиву значений. Результирующий массив содержит значения сравнения, которые должны быть рассмотрены в MAX и найдены в MATCH.
- Первый фильтр
$Q$4:$Q$13=$R$14 сравнивает каждый элемент столбца данных месяца (массива) со значением списка выбора. Результатом является массив значений true/false, основанный на том, соответствует ли строка столбца данных месяца значению списка выбора. Для каждого элемента True IF возвращает вложенный результат IF и «» в противном случае.
- Вложенные IF работают с тем же индексом массива, что и внешние IF. Все массивы должны быть одинакового размера. If ({True, False}, {"a", "b"}, If ({True, True}, {1,2})) равно IF ({True, False}, {"a", "b" }, {1,2}). Строка 1 - это If (True, "a", 1), строка 2 - это If (False, "b", 2), что приводит к {"a", 2}.
- Второй фильтр
NOT(COUNTIF( $Q$14:$Q14, $R$4:$R$13)) также возвращает массив true/false. Элемент массива имеет значение true, если строка столбца данных заголовка страницы (второй аргумент COUNTIF) отсутствует в предыдущих лучших результатах $Q$14:$Q14 .
- Предыдущий верхний массив результатов увеличивается по мере того, как формула копируется / сокращается, поскольку ссылка на второй диапазон диапазона является относительной. Для каждого последующего результата будет больше заголовков страниц, отфильтрованных с ложным.
- Массив true/false второго фильтра имеет один элемент для каждой строки столбца данных Page Title.
- Функция COUNTIF используется для каждого заголовка страницы в столбце данных заголовка страницы. Функция COUNTIF подсчитывает, сколько раз этот конкретный заголовок страницы (из массива данных столбца заголовка страницы) был найден в предыдущих результатах
$Q$14:$Q<grows> . Если найдены ноль, счетчик равен нулю (ожидаемый результат равен нулю или единице).
- Результаты COUNTIF выдаются НЕ. Логический тест числа возвращает false для нуля и true в противном случае. Отрицание функции NOT делает этот результат истинным, если ноль (не найден в предыдущих результатах), и ложь в противном случае (найдено раз COUNT в предыдущих результатах).
- Для каждой строки массива, имеющей значение True, IF возвращает ту же строку массива значений сравнения и «» в противном случае.
- Это эффективно заменяет нежелательные значения на «», сохраняя значения для рассмотрения.
- MAX возвращает ноль, если все значения равны "", в противном случае возвращается наибольшее число, даже если оно отрицательное.
- MATCH не будет сопоставлять ноль с пустой строкой, хотя в некоторых местах Excel рассматривает пустую строку как ноль (это не один из тех случаев). Это защищает от ошибочных результатов, когда есть страницы с нулевым посещением.
- Последний ноль является третьим аргументом для MATCH, поэтому он будет учитывать только точные совпадения (и также необходим для несортированного / неупорядоченного списка, которым это и является).
