☰

¿Encontrar la enésima instancia de una cadena de texto dentro de un rango?

tag-icon • tag-icon openoffice openoffice-calc
¿Encontrar la enésima instancia de una cadena de texto dentro de un rango?

Tengo una lista de nombres al lado de una lista de turnos. Quiero recopilar una lista de personas en cada turno por día, que se actualizará de forma automática y dinámica. No puedo encontrar la mejor manera de hacerlo.

En la lista principal, habrá una columna de nombres de empleados, seguida de 14 columnas de turnos. Luego quiero crear 14 listas de nombres según quién está en qué turno en la primera tabla.

Por ejemplo: el rango principal tiene:

        A      B   C   D   E
   ---------- --- --- --- ---
1  Joe Blogs   E   E   L   O
2  Jill Bleg   L   L   E   E
3  Geoff Ted   O   L   L   L

Luego me gustaría buscar la columna D, por ejemplo, y buscar la primera instancia de L para enumerar Joe Blogs, luego buscar la segunda instancia de L para enumerar a Geoff Ted, y así sucesivamente.

¿Hay alguna manera de buscar la enésima instancia de una cadena dentro de un rango de celdas?

Respuesta1

Esto no es tan fácil como parece. Primero, para encontrar los números de fila de todas las apariciones de "L" en la columna D, usarías:

=IF(D1:D3="L"; ROW(D1:D3))

Guarda esto comouna fórmula matricial: después de escribirlo, presione Ctrl+Shift+Return (o Comando+Shift+Return en una Mac). Entonces seráespectáculoentre llaves. Y no tendrá una sola celda como resultado, sino tantas celdas como el rango en el que estás trabajando. En el ejemplo anterior, obtendrías 3 celdas, con valores 1, en blanco y 3.

Próximo,usarSMALLpara encontrar el enésimo valor:

SMALL(numberlist; n)

devuelve el número nmás pequeño dentro del rango (desordenado) o matriz de números numberlist.

Aplicando SMALLlo anterior, volverás a tener una sola celda como resultado:

=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)

Aún así, a pesar de que el resultado es una sola celda, es necesario guardarlo usando Ctrl+Shift+Retorno.

Ahora, conociendo el número de fila, INDEXpodemos encontrar el nombre en la primera columna de A1:A3:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)

Nuevamente, todos deben guardarse usando Ctrl+Shift+Retorno.

Sin embargo, dicha fórmula no se puede arrastrar para extenderla a otras celdas, ya que los rangos "1" y "2" no se convertirían automáticamente en "3", y así sucesivamente. En su lugar, para calcular el rango requerido en función de la fila en la que se encuentra la fórmula:

=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)

Después de guardar esto como una fórmula matricial en algún lugar de la fila 1, se puede arrastrar hacia abajo para agregarlo a las filas 2, 3, etc.

Alternativamente, en lugar de extender el resultado, puede copiar/pegar la fórmula matricial para ajustar las referencias de columnas y filas sobre la marcha. En la siguiente captura de pantalla, copié lo siguiente de B7, B12 y B17 en las otras celdas:

=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)

Tenga en cuenta que el método abreviado de teclado para fórmulas matriciales sólo funcionará después de realizar cambios en la fórmula; cuando simplemente presionas Retorno, luego ingresas la fórmula nuevamente y luego presionas Ctrl+Shift+Retorno no tendrá ningún efecto.

Además, una vez que se ha extendido una fórmula matricial, deberá seleccionar todas las celdas de resultados para cambiar esa fórmula. De lo contrario obtendrás"No se puede cambiar sólo parte de una matriz".

información relacionada