Я пытаюсь получить позицию столбца даты, которая встречается более одного раза подряд.
Пример данных:
.
Следующая формула правильно определяет большинство позиций столбцов, но при наличии дубликата всегда возвращается позиция первого вхождения.
Формула :
Заранее спасибо.
решение1
Следующая формула выберет n-thсовпадение в наборе совпадающих и несовпадающих ячеек. Ее можно адаптировать к различным макетам исходных данных, подробнее позже. Она голая, в том смысле, что не обеспечивает никакой проверки ошибок вообще, даже вашей IF(FL2<>0части, поскольку она предназначена для широкого спектра применений, но вы можете легко добавить такую проверку, чтобы она соответствовала вашим конкретным обстоятельствам:
=FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN("¢",FALSE,FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(AW2:CW2),4),ROW(),""),AW2:CW2=B2),ROW(),""),"¢","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner["&C2&"]")
Он настроен, предполагая, что он будет находиться в столбце A строки со значением, которое должно совпадать в столбце B, и n-thзначением в столбце C. Конечно, вы можете адаптировать их.
Версия, позволяющая LET()поместить все переменные в одно удобное для редактирования место:
=LET(
RangeToExamine, AW2:CW2, Delimiter, "¢", ItemToMatch, B2, InstanceToMatch, C2,
FILTERXML("<Outer><Inner>"
&SUBSTITUTE(TEXTJOIN(Delimiter,FALSE,
FILTER(SUBSTITUTE(ADDRESS(ROW(),COLUMN(RangeToExamine),4),ROW(),""),
RangeToExamine=ItemToMatch),
ROW(),""), Delimiter,"</Inner><Inner>") & "</Inner></Outer>",
"/Outer/Inner["&InstanceToMatch&"]"))
Это все LET()для того, чтобы сделать формулу более удобной для редактирования.
Итак, что он делает. Во-первых, хотя ваш макет может быть однострочным, я не был уверен, поэтому оставил его разложенным для работы с любой строкой. Это делается с помощью функции ROW()внутри ADDRESS()функции, а затем в SUBSTITUTE()функции, которая удаляет номер строки (так что у вас есть только столбец, что и является вашим желаемым результатом). Вы можете поместить цифру «1» в оба ее места, чтобы гарантировать отсутствие активности по строкам, если это необходимо. Вы даже можете добавить предложение в if, LET()чтобы иметь возможность изменять его здесь и там. Я не сделал этого здесь, потому что он сворачивает строку LET()в две строки здесь, и это сделало бы это беспорядочным. Кстати, вот как вы преобразуете столбец NUMBER в столбец LETTER, а не все эти забавные формулы с MOD()(и хуже). Не требуется никаких UDF.
Итак, ADDRESS()использует Spillфункциональность для создания массива всех адресов ячеек в проверяемом диапазоне. FILTER()Затем проверяет целевой диапазон на совпадения и выводит список соответствующих адресов ячеек. (Опять же, не настроено на ошибки!) SUBSTITUTE()Затем удаляет номер строки (или константу, если вы внесли это изменение), поэтому в Excel теперь есть массив только из буквенных обозначений столбцов для столбцов, в которых были совпадения.
Я должен быстро упомянуть, что разделитель, который я здесь использовал, не всегда хорош — в других случаях. Но поскольку он не связан с базовыми данными, а скорее с адресными данными, он должен быть чем-то, что никогда не использовалось в адресе, возвращаемом , ADDRESS()поэтому он не может здесь потерпеть неудачу. Однако, используя подход «редко используемый символ» в другом месте, вам может потребоваться выбрать что-то странное, что вы найдете в таблице символов.
Далее TEXTJOIN()преобразует массив адресов в строку и SUBSTITUTE()заменяет этот разделитель строкой, </Inner><Inner>в то время как вы также добавляете строку до и после, чтобы превратить результат в приемлемый XML. Для структурирования XML может использоваться почти все, что угодно, пока это по крайней мере один более высокий уровень, оборачивающий вещи, и по крайней мере один самый низкий уровень, оборачивающий каждую часть строки TEXTJOIN(). Мне нравится использовать <Outer>для более высокого уровня, который оборачивает все это, и <Inner>для рабочего уровня.
Я впервые увидел это где-то с очень плохо изложенным объяснением, но пошелChandoo.orgчтобы узнать, есть ли у него подсказка по этому поводу, так как его объяснения (почти) всегда довольно ясны и полезны. Это было очень удобно, поэтому я должен отдать должное и поблагодарить!
Наконец, FILTERXML()делает еще одну последнюю вещь. Позволяет вам выбрать, какой экземпляр совпадений вы хотите вернуть. В совете Чанду он разбивает предложение таким образом, но, как вы видите, он может разбить все, что вы получаете как или превращаете в строку, включая столбчатые данные. В этом случае, если вам нужен третий экземпляр, вы добавляете [3]к вещам.
Однако Excel здесь делает нас лучше. Во-первых, вы можете использовать числовой экземпляр, например [3] для третьего экземпляра, но вы также можете использовать его [last()]для поиска последнего экземпляра без необходимости находить его кардинальное число. Но где эта функция действительно блистает, что удивительно (поскольку она, казалось бы, не предназначена для такого рода вещей), так это то, что вы можете «построить» это предложение, или, другими словами, вы можете построить строку, включающую переменные данные как часть, что затем сделает вашу формулу адаптивной, вместо того, чтобы вводить ее и изменять по мере необходимости внутри формулы. Это означает, что ваши пользователи могут получить эту функциональность, используя записи в ячейках, а не редактируя формулы. Или что вы можете заставить ее отражать результат какой-то другой формулы, если это необходимо.
Эта последняя часть происходит в последней строке отформатированной версии формулы. Вы указываете "XML Path" (в данном случае `/Outer/Inner"), за которым сразу следует набор квадратных скобок ("[ ]") с номером экземпляра или выражением внутри них.
В общем случае я рекомендую проверять ошибки, чтобы выявить:
- Ваш элемент для сопоставления не входит в рассматриваемый диапазон
- Номер нужного вам экземпляра больше общего количества совпадений: вам нужен № 6, но существует только четыре...
- Аналогично, если вы хотите найти n-ный экземпляр из последнего экземпляра, то он также должен существовать: если вы хотите найти шесть экземпляров из «last()», то должно быть не менее шести других экземпляров...
и будьте внимательны с выбором разделителя, если данные содержат больше разнообразных символов, чем буквы и цифры.
Если вам нужен адрес ячейки, а не просто столбец, удалите , SUBSTITUTE()который удаляет строку с полным адресом.
Ну, извините в некоторой степени: это общее решение, а не полностью подходящее к вашему посту, но я не был уверен в точном использовании, которое вы имеете, и поэтому это также может быть полезным, и любые правки довольно легко увидеть. Опыт показывает, что посты очень часто упускают полезную информацию, и затем автор должен задать по крайней мере еще один вопрос, чтобы получить нужный ему ответ. Попытка сократить любую необходимость для вас проходить через этот процесс.
решение2
Вы ищете формулу СЧЁТЕСЛИ? =ЕСЛИ(A2<>0,СЧЁТЕСЛИ($A$2:A2,A2),0)
