Encuentre la segunda o tercera aparición de valor en una fila específica - Excel

Question

Estoy intentando obtener la posición de la columna de una fecha que ocurre más de una vez seguida.

Datos de ejemplo:
.

La siguiente fórmula identifica correctamente la mayoría de las posiciones de las columnas, pero si hay un duplicado, siempre se devuelve la posición de la primera aparición.

La formula :

Gracias de antemano.

Answer 1

La siguiente fórmula seleccionará la n-thcoincidencia en un conjunto de celdas coincidentes y no coincidentes. Se puede adaptar a varios diseños de datos de origen, hablaremos más adelante. Es básico en el sentido de que no proporciona ninguna verificación de errores, ni siquiera su IF(FL2<>0parte, ya que está diseñada para una amplia gama de usos, pero puede agregar fácilmente dicha verificación para adaptarse a sus circunstancias exactas:

=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&"]")

Se configura asumiendo que iría en la columna A de una fila con el valor que debe coincidir en la columna B y el n-thvalor en la columna C. Por supuesto, puede adaptarlos.

Una versión que utiliza LET()para colocar todas las variables en un lugar fácil de editar es:

=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&"]"))

Eso LET()es todo: hacer que la fórmula sea más fácil de editar.

Entonces, ¿qué hace? Primero, aunque su diseño podría ser una sola fila, no estaba seguro, así que lo dejé diseñado para funcionar en cualquier fila. Esto se soluciona usando la ROW()función dentro de la ADDRESS()función y luego en una SUBSTITUTE()función que elimina el número de fila (para que solo tenga la columna que es el resultado deseado). Si lo desea, puede colocar un número "1" en ambos lugares para garantizar que no se produzca actividad en las filas. Incluso podrías agregar una cláusula al LET()si quisieras poder modificarla aquí y allá. No lo hice aquí porque la LET()línea se extiende a dos líneas aquí y eso haría que esto fuera complicado. Por cierto, así es como se convierte una columna NÚMERO en una columna LETRA, no todas esas fórmulas divertidas con MOD()(y cosas peores). No se requieren UDF.

Por lo tanto, ADDRESS()utiliza Spillla funcionalidad para crear una matriz de todas las direcciones de celda en el rango que se examina. FILTER()luego examina el rango objetivo en busca de coincidencias y enumera las direcciones de celda correspondientes. (Nuevamente, ¡no está configurado para errores!) SUBSTITUTE()luego elimina el número de fila (o la constante si realizó ese cambio) para que Excel ahora tenga una matriz de solo las etiquetas de letras de las columnas en las que se encontraban sus coincidencias.

Debo mencionar brevemente que el delimitador que utilicé aquí no siempre es necesariamente bueno (en otros casos). Pero como no está conectado a los datos subyacentes, sino a los datos de la dirección, solo necesita ser algo que nunca se use en una dirección devuelta por ADDRESS()lo que no puede fallar aquí. Sin embargo, utilizando el enfoque de "caracteres raramente usados" en otros lugares, es posible que tengas que seleccionar algo extraño que encuentres en el Mapa de caracteres.

A continuación, TEXTJOIN()convierte la matriz de direcciones en una cadena y SUBSTITUTE()reemplaza ese delimitador con la </Inner><Inner>cadena mientras también agrega una cadena antes y después para convertir el resultado hasta ahora en XML aceptable. Podría usarse casi cualquier cosa para estructurar el XML, siempre y cuando haya al menos un nivel superior que envuelva cosas y al menos un nivel más bajo que envuelva cada porción de la TEXTJOIN()cadena. Me gusta usarlo <Outer>para el nivel superior que lo envuelve todo y <Inner>para el nivel de trabajo.

Vi esto por primera vez en algún lugar con una forma muy mal diseñada de explicarlo, pero fui aChandoo.orgpara ver si tenía algún consejo ya que sus explicaciones son (casi) siempre bastante claras y útiles. ¡Ha sido muy útil, así que debo dar crédito y gracias!

Finalmente, FILTERXML()hace una última cosa. Le permite elegir qué instancia de las coincidencias desea devolver. En el consejo de Chandoo, él descompone una oración de esta manera, pero como puede ver, puede descomponer cualquier cosa que obtenga o convertirla en una cadena, incluidos los datos en columnas. En este caso, si quieres una tercera instancia, agregarías [3]cosas.

Sin embargo, Excel nos hace algo mejor aquí. Primero, puede usar la instancia numérica, como [3] para la tercera instancia, pero también puede usarla [last()]para encontrar la última instancia sin tener que encontrar también su número cardinal. Pero donde la función realmente brilla, sorprendentemente (ya que no parecería estar dirigida a este tipo de cosas) es que puedes "construir" esa cláusula, o dicho de otra manera, puedes construir una cadena que incluya datos variables como parte, que luego haga que su fórmula responda en lugar de tener que escribirla y cambiarla según sea necesario dentro de la fórmula. Eso significa que sus usuarios pueden obtener esa funcionalidad usando entradas en celdas en lugar de editar fórmulas. O que podrías hacer que refleje el resultado de alguna otra fórmula si fuera necesario.

Esa última parte ocurre en la última línea de la versión formateada de la fórmula. Usted proporciona la "Ruta XML" (`/Externa/Interior" en este caso) seguida inmediatamente por un conjunto de corchetes ("[ ]") con el número de instancia o expresión dentro de ellos.

En uso general, recomiendo verificar errores para buscar:

1. Su artículo a coincidir no está en el rango que se está examinando
2. El número de instancia que desea es mayor que el total de coincidencias: desea el número 6, pero solo existen cuatro...
3. De manera similar, si desea encontrar la enésima instancia desde la última instancia, que también exista: si desea seis desde "última()", que haya al menos otras seis...

y que tengas cuidado con el delimitador que elijas si los datos son más variados que letras y números.

Si desea una dirección de celda en lugar de solo una columna, elimine la SUBSTITUTE()que elimina la fila de la dirección completa.

Bueno, lo siento hasta cierto punto: ES una solución general en lugar de adaptarse completamente a tu publicación, pero no estaba seguro del uso exacto que tienes y, por lo tanto, también podría ser útil y cualquier edición es bastante fácil de ver. La experiencia aquí muestra que las publicaciones omiten información útil muy a menudo y quien publica debe hacer al menos una pregunta más para obtener la respuesta que necesita. Tratando de cortocircuitar cualquier necesidad de que usted pase por ese proceso.

Answer 2

¿Estás buscando la fórmula CONTAR.SI? =SI(A2<>0,CONTAR.SI($A$2:A2,A2),0)