contingut
Totes les funcions clàssiques de cerca i substitució de tipus VPR (VISUALITZACIÓ), GPR (RECUPERACIÓ), MÉS EXPOSADA (PARTIDA) i els com ells tenen una característica important: cerquen des del principi fins al final, és a dir, d'esquerra a dreta o de dalt a baix a les dades font. Tan bon punt es troba la primera coincidència, la cerca s'atura i només es troba la primera ocurrència de l'element que necessitem.
Què hem de fer si hem de trobar no la primera, sinó l'última? Per exemple, l'última transacció per al client, l'últim pagament, la comanda més recent, etc.?
Mètode 1: trobar l'última fila amb una fórmula matricial
Si la taula original no té una columna amb una data o un número de sèrie d'una fila (comanda, pagament...), aleshores la nostra tasca és, de fet, trobar l'última fila que compleixi la condició donada. Això es pot fer amb la fórmula de matriu següent:
Aquí:
- function IF (SI) verifica totes les cel·les d'una columna una per una Client i mostra el número de línia si conté el nom que necessitem. El número de línia del full ens el dóna la funció LÍNIA (FILERA), però com que necessitem el número de fila a la taula, a més hem de restar 1, perquè tenim una capçalera a la taula.
- Després la funció MAX (MÀX.) selecciona el valor màxim del conjunt format de números de fila, és a dir, el número de la línia més recent del client.
- function ÍNDEX (ÍNDEX) retorna el contingut de la cel·la amb l'últim número trobat de qualsevol altra columna de taula necessària (Codi d'ordre).
Tot això s'ha d'introduir com fórmula de matriu, és a dir:
- A Office 365 amb les últimes actualitzacions instal·lades i suport per a matrius dinàmiques, només podeu prémer Enter.
- En totes les altres versions, després d'introduir la fórmula, hauràs de prémer la drecera del teclat Ctrl+Canviar+Enter, que li afegirà automàticament claus a la barra de fórmules.
Mètode 2: cerca inversa amb la nova funció LOOKUP
Ja vaig escriure un article llarg amb un vídeo sobre una nova funció MÉS INFO (CERCA XL), que va aparèixer a les últimes versions d'Office per substituir l'antiga VLOOKUP (VISUALITZACIÓ). Amb l'ajuda de BROWSE, la nostra tasca es resol de manera bastant elemental, perquè. per a aquesta funció (a diferència de VLOOKUP), podeu establir explícitament la direcció de cerca: de dalt a baix o de baix a dalt; el seu últim argument (-1) és responsable d'això:
Mètode 3. Cerqueu una cadena amb l'última data
Si a les dades d'origen tenim una columna amb un número de sèrie o una data que té un paper similar, la tasca es modifica: no hem de trobar l'última línia (la més baixa) amb una coincidència, sinó la línia amb l'última ( data màxima).
Ja he parlat detalladament de com fer-ho amb funcions clàssiques, i ara intentem utilitzar el poder de les noves funcions de matriu dinàmica. Per a una major bellesa i comoditat, també convertim la taula original en una taula "intel·ligent" mitjançant una drecera de teclat Ctrl+T o ordres Inici – Format com a taula (Inici — Format com a taula).
Amb la seva ajuda, aquesta "parella assassina" resol el nostre problema amb molta gràcia:
Aquí:
- Funció primer FILTRE (FILTRE) selecciona només les files de la nostra taula a la columna Client - el nom que necessitem.
- Després la funció GRAU (ORDENAR) ordena les files seleccionades per data en ordre descendent, amb l'oferta més recent a la part superior.
- function ÍNDEX (ÍNDEX) extreu la primera fila, és a dir, retorna l'últim comerç que necessitem.
- I, finalment, la funció FILTRE externa elimina les columnes 1 i 3 addicionals dels resultats (Codi d'ordre и Client) i deixa només la data i l'import. Per a això, s'utilitza una matriu de constants. {0;1;0;1}, definint quines columnes volem (1) o no volem que (0) es mostrin.
Mètode 4: trobar l'última coincidència a Power Query
Bé, per tal de completar-ho, mirem una solució al nostre problema de cerca inversa mitjançant el complement Power Query. Amb la seva ajuda, tot es resol molt ràpidament i molt bé.
1. Convertim la nostra taula original en una de "intel·ligent" mitjançant una drecera de teclat Ctrl+T o ordres Inici – Format com a taula (Inici — Format com a taula).
2. Carregueu-lo a Power Query amb el botó De Taula/Range llengüeta dades (Dades: de la taula/interval).
3. Ordenam (a través de la llista desplegable del filtre de la capçalera) la nostra taula en ordre descendent de data, de manera que les transaccions més recents estiguin a la part superior.
4… A la pestanya Transformació triar un equip Agrupar per (Transforma — Agrupa per) i establiu l'agrupació per clients, i com a funció d'agregació, seleccioneu l'opció Totes les línies (Totes les files). Podeu anomenar la columna nova com vulgueu, per exemple Detalls.
Després de l'agrupació, obtindrem una llista de noms únics dels nostres clients i a la columna Detalls – taules amb totes les transaccions de cadascuna d'elles, on la primera línia serà l'última transacció, que és el que necessitem:
5. Afegeix una nova columna calculada amb el botó Columna personalitzada llengüeta Afegeix columna (Afegeix una columna — Afegeix una columna personalitzada)i introduïu la fórmula següent:
Aquí Detalls – aquesta és la columna de la qual prenem les taules per clients, i 0 {} és el número de la fila que volem extreure (la numeració de les files a Power Query comença des de zero). Obtenim una columna amb registres (Registre), on cada entrada és la primera fila de cada taula:
Queda per ampliar el contingut de tots els registres amb el botó amb fletxes dobles a la capçalera de la columna Últim tracte seleccionant les columnes desitjades:
… i després suprimiu la columna que ja no és necessària Detalls fent clic amb el botó dret al seu títol - Elimina columnes (Elimineu columnes).
Després de carregar els resultats al full a través Inici — Tancar i carregar — Tancar i carregar (Inici — Tancar i carregar — Tancar i carregar a...) obtindrem una taula tan bonica amb una llista de transaccions recents, com volíem:
Quan canvieu les dades d'origen, no us oblideu d'actualitzar els resultats fent clic amb el botó dret sobre ells: l'ordre Actualitza i desa (Actualització) o drecera de teclat Ctrl+Alt+F5.
- La funció BUSCAR és un descendent de BUSCARV
- Com utilitzar les noves funcions de matriu dinàmica SORT, FILTER i UNIC
- Trobar l'última cel·la no en blanc d'una fila o columna amb la funció CERCA