¿Es posible filtrar una matriz por una cadena parcial y enumerar varias entradas solo una vez usando una única fórmula (sin autofiltro, VBA o columnas adicionales)? Por ejemplo, tengo la siguiente hoja de cálculo:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 |
03| 1 | B/as | V2 |
04| 2 | A/ab | V3 |
05| 3 | B/ab | V4 |
06| 3 | B/as | V5 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V5 |
09| 3 | A/ab | V5 |
Si filtro la columna B porClase"A/*" pero mostrando su valor solo una vez, el resultado debería ser:
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V2
05| 3 | A/ab | V1 |
06| 3 | B/as | V4 |
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V2 |
en lugar de
A | B | C | D | F
-----------------------------------
01| ID | Class | Value | Filter
02| 1 | A/as | V1 | V1
03| 1 | B/as | V2 | V3
04| 2 | A/ab | V3 | V1
05| 3 | A/ab | V1 | V2
06| 3 | B/as | V4 | V2
07| 2 | B/ab | V5 |
08| 1 | A/as | V2 |
09| 3 | A/ab | V6 |
Filtre la columna por las obras de cadena parcial (adaptadoesodescripción) para que de alguna manera le guste esto:
...
IF(
ISNUMBER(Search("A/*"; $B$2:$B$9))
...
que da como resultado un array con los índices de la lista $B$2:$B$9:
ISNUMBER(Search("A/*"; $B$2:$B$9): {1;0;1;1;0;0;1;1}
y entonces
IF(IS...): {1;3;4;8;9}
Hasta ahora no he encontrado ninguna manera de combinar eso con el enfoque de "lista de nombres únicos".
`MATCH(0;INDEX(COUNTIF(`
como se describeaquí
Lo poco que tengo es eso, pero no funciona tan bien y provoca una carga considerable de la CPU, por ejemplo, la celda C8.
{=IFERROR(INDEX(
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
MATCH(0;
INDEX(COUNTIF($C$2:C7;
INDEX($D$2:$D$9;
IF(ISNUMBER(SEARCH("A/*";$C$2:$C$9));
ROW($D$2:$D$9)-ROW($D$2)+1)));
0;0);0));
"error")
Respuesta1
Lo resolví, no perfectamente (necesita 3 columnas) pero funciona de maravilla.
A | B | C | D | E | F | G
-------------------------------------------------------
01| Array |Array | Text | search | search | ordered
02| Source|Source | sought| results | results |
03| #1 | #2 | *a* | #1 | #2 |
04| aa | c12 | | c12 | c12 | c02
05| ca | c13 | | c13 | c13 | c06
06| ad | c06 | | c06 | c06 | c12
07| ee | c11 | | c02 | c02 | c13
08| fa | c02 | | c06 | c25 | c25
09| gg | c12 | | c13 | |
10| ba | c06 | | c06 | |
11| aa | c13 | | c25 | |
12| ad | c06 | | #NUM! | |
13| gt | c12 | | #NUM! | |
14| aa | c25 | | #NUM! | |
Columna Eenumere todos los elementos de la columna C si la celda correspondiente en la columna B contiene la expresión de D3. Fórmula en la celda E4 lo que se copia a E5-E14:
{=INDEX(C:C;
SMALL(IF(ISNUMBER(SEARCH($D$3&"/*";
$B$4:$B$14));
ROW($B$4:$B$14));
ROWS($E$4:E4)))}
Debe presionar Ctrl-Shift-Intro para ingresar la fórmula como una matriz, pero tenga cuidado, puede llevar bastante tiempo si busca una tabla grande. Tengo 1300 celdas, eso tomó más de un minuto, pero solo para ingresar la fórmula, pasar a las otras celdas sin demora.
Aquí viene lo que hace:
- ÍNDICE (arg1,arg2)generará el valor del elemento/celda n (arg2) de la columna C (arg1). N se calcula enPequeño.
- PEQUEÑO(arg1,arg2)se supone que debe devolver el k-ésimo (arg2) valor más pequeño en un conjunto de datos (arg1).
Esta función devuelve valores con una posición relativa particular en un conjunto de datos. Eso es exactamente lo que se necesita para el uso adecuado deSIyFILAS, anidado más profundo. SI (prueba lógica,valor si es verdadero)es la parte principal del truco: construye una matriz de números de fila donde la condición IF es verdadera (tenga en cuenta que elSIno tiene 'demás' valor, será soloFALSOdonde la condición no es verdadera)
- prueba lógica:ESNÚMERO(BUSCAR($D$3&"/*"; $B$4:$B$14))devuelve una matriz de Verdadero y Falso dependiendo de si la BÚSQUEDA da como resultado un valor numérico o no para cada celda dada en el rango $B$4:$B$15.
El resultado para el ejemplo superior es:- BUSCAR:1, 2, 1, #VALOR, 2, #VALOR, 2, 1, 1, #VALOR, 1
- ES NÚMERO:VERDADERO, VERDADERO, VERDADERO, FALSO, VERDADERO, FALSO, VERDADERO, VERDADERO, VERDADERO, FALSO, VERDADERO
- valor si es verdadero: FILA($B$4:$B$14)) devuelve una matriz poblada por los números de fila de la matriz $B$4:$B$14
El resultado para el ejemplo superior es: 4, 5, 6, 7, 8 , 9, 10, 11, 12, 13, 14
SIcombinaráprueba lógica#1 yvalor si es verdadero#2. Todos los valores del n.° 1 se ignorarán donde el n.° 2 dice FALSO en la misma posición dentro de la matriz n.° 2. Finalmente tenemos una matriz, que indica en qué fila de la columna B, dentro del rango dado enFILA(...)se encuentra la expresión de D3. El resultado del ejemplo superior es: 4,5,6,8,10,11,12,14
- prueba lógica:ESNÚMERO(BUSCAR($D$3&"/*"; $B$4:$B$14))devuelve una matriz de Verdadero y Falso dependiendo de si la BÚSQUEDA da como resultado un valor numérico o no para cada celda dada en el rango $B$4:$B$15.
FILAS($E$4:E4)es sólo un truco que le dará un número incremental (es decir, 1 en F2, 2 en F3...). que se usa enPEQUEÑOcomoarg2. El resultado en la primera celda (FILAS(...)=1) será 4 (valor más bajo), en el segundo 5 y así sucesivamente. Al final, cada celda siguiente mostrará el número de fila/posición en la columna B donde se encuentra la expresión de D3.
Columna Ffiltra duplicados, cuál fue la parte más difícil. La columna F "sólo" enumerará una vez todos los elementos enumerados en la columna E.
¡Esa es la fórmula ingresada en F5! (F4 es lo mismo que en E4) usando ctrl-shift-enter:
{=IFERROR(INDEX($C$2:$C$14;
MATCH(0;
COUNTIF($E$4:E4;
$C$2:$C$14);
0));
"")}
Aquí viene lo que hace:
- CONTAR.SI(arg1,arg2)da como resultado una matriz de la longitud del rangoarg1, indicando con 1 dónde hay una coincidencia de las entradas enarg2.
El resultado del ejemplo superior es: 1, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0 - Fósforo(valor de búsqueda,formación,tipo de concordancia)busca la primera aparición de 0 en la matriz resultante deCONTAR.SI(...)¿Cuál es la tercera posición en el ejemplo superior?
- valor de búsqueda:0, primer valor nuevo/no duplicado
- formación:conjunto resultante deCONTAR.SI(...)
- tipo de concordancia:0 = exactamente
- ÍNDICE(arg2de CONTAR.SI, Coincidir(...))finalmente mostrará el valor nuevo/no duplicado que está en la tercera posición del rangoarg2, ¿qué es c06 en el ejemplo superior?
Columna GFinalmente todos los artículos estarán ordenados alfabéticamente. Esa es la fórmula ingresada en G4 usando Ctrl-Shift-Enter:
{=IFERROR(INDEX($F$4:$F$14;
MATCH(ROWS($G$4:$G4);
COUNTIF($F$4:$F$14;
"<="&$F$4:$F$14);
0));
"")}
Aquí viene lo que hace:
- CONTAR.SI(arg1,arg2)es la parte principal del truco: compara los valores de texto dados enarg2con todos los demás valores de texto dados enarg1y devuelve su rango relativo (orden alfabético).
El resultado del ejemplo superior es:
3, 4, 2, 1, 5 - FILAS($E$2:E2)es sólo un truco que le dará un número incremental (es decir, 1 en G2, 2 en G3...). que se usa enFósforocomovalor de búsqueda.
- Fósforo(valor de búsqueda,formación,tipo de concordancia)busca la primera aparición deFILAS(...)en la matriz resultante deCONTAR.SI(...).
El resultado del ejemplo superior es:
4, 3, 1, 2, 5- celúla:FILAS(...)=1 => 4
- celúla:FILAS(...)=2 => 3
- celúla:FILAS(...)=3 => 1
- celúla:FILAS(...)=4 => 2
- celúla:FILAS(...)=5 =>5
- ÍNDICE(arg1 de CONTAR.SI, Coincidencia(...))finalmente mostrará la entrada ordenada correspondiente a suFILAS(...)resultado. El resultado para el ejemplo superior es:
- celúla:FILAS(...)=1 => 4 pulgadasCONTAR.SImatriz => c02
- celúla:FILAS(...)=2 => 3 pulgadasCONTAR.SImatriz => c06
- celúla:FILAS(...)=3 => 1 pulgadaCONTAR.SImatriz => c12
- celúla:FILAS(...)=4 => 2 pulgadasCONTAR.SImatriz => c13
- celúla:FILAS(...)=5 => 5 pulgadasCONTAR.SImatriz => c25
Hasta aquí todo bien, el paso final sería combinar todo en una columna. Al menos encontré algoayudafusionando columnas F&G (pero no lo haré hoy).