Tenim una llista de comandes amb números i noms de mercaderies. M'agradaria, per exemple, treure de la taula per número de comanda tots els productes que s'hi inclouen. Més o menys així:
Característica meravellosa CERCA V (CERCA V) en aquesta situació només ajudarà parcialment, perquè només pot extreure dades per la primera coincidència trobada, és a dir, només ens donarà pomes. Per trobar i extreure tots els elements de la taula, és millor utilitzar una fórmula matricial. Com aquest:
=ÍNDEX($B$2:$B$16;MENYS(IF($E$2=A2: A16;LÍNIA(B2: B16)-1;»»);LÍNIA()-5))
S'ha d'introduir de la següent manera:
- seleccioneu les cel·les on s'han de mostrar els resultats (en el nostre exemple, aquest és el rang D6:D20)
- introduïu (copieu la fórmula a la primera cel·la) de l'interval
- prémer Ctrl + Canviar + Enter
Resta d'unitats en fragments CADENA (B2:B16)-1 es fa a causa de la capçalera de la taula. Per la mateixa raó, per compensar el desplaçament de l'interval resultant respecte a l'original, es resta el número cinc del fragment. STRING()-5
Per amagar el #NUM! error que apareixerà a les cel·les buides de l'interval resultant D6:D20, podeu utilitzar les funcions de comprovació d'errors IF i EOSH, substituint la nostra fórmula per una una mica més complexa:
=SI(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))
A Excel 2007, va aparèixer una funció IFERROR més convenient: us permet resoldre el problema de manera més compacta:
=SI ERROR(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)
PS
A la versió anglesa d'Excel, aquestes funcions seran així:
=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))
=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)
- Ús de la funció BUSCAR V per trobar dades en una taula
- Una versió millorada de la funció VLOOKUP2 que pot cercar a qualsevol columna i no només al primer valor
- Funcions VLOOKUP2 i VLOOKUP3 del complement PLEX
- Què són les fórmules de matriu i per a què serveixen?