Atopar a última aparición (BUSCAR V INVERTIDA)

Todas as funcións clásicas de busca e substitución de tipos VPR (BÚSQUEDA V), GPR (BUSCAR), MÁIS EXPOÑA (Coincidir) e aqueles como eles teñen unha característica importante: buscan dende o principio ata o final, é dicir, de esquerda a dereita ou de arriba a abaixo nos datos de orixe. En canto se atopa a primeira coincidencia, a busca detense e só se atopa a primeira aparición do elemento que necesitamos.

Que facer se necesitamos atopar non a primeira, senón a última? Por exemplo, a última transacción para o cliente, o último pago, o pedido máis recente, etc.?

Método 1: atopar a última fila cunha fórmula matricial

Se a táboa orixinal non ten unha columna cunha data ou un número de serie dunha fila (pedido, pago...), entón a nosa tarefa é, de feito, atopar a última fila que satisfaga a condición indicada. Isto pódese facer coa seguinte fórmula matricial:

Aquí:

• función IF (SI) comproba todas as celas dunha columna unha por unha Cliente e mostra o número de liña se contén o nome que necesitamos. O número de liña da folla dános a función LIÑA (FILA), pero como necesitamos o número de fila na táboa, ademais temos que restar 1, porque temos unha cabeceira na táboa.
  
• Despois a función MAX (MÁX.) selecciona o valor máximo do conxunto formado de números de fila, é dicir, o número da liña máis recente do cliente.
• función Index (ÍNDICE) devolve o contido da cela co último número atopado de calquera outra columna da táboa obrigatoria (Código de pedido).

Todo isto debe introducirse como fórmula matricial, é dicir:

• En Office 365 coas últimas actualizacións instaladas e compatibilidade con matrices dinámicas, simplemente podes premer Entrar.
• En todas as outras versións, despois de introducir a fórmula, terás que premer o atallo de teclado Ctrl+Desprazarse+Entrar, que engadirá chaves automaticamente na barra de fórmulas.

Método 2: busca inversa coa nova función LOOKUP

Xa escribín un longo artigo cun vídeo sobre unha nova función VER (BUSCAR XL), que apareceu nas últimas versións de Office para substituír o antigo VLOOKUP (BÚSQUEDA V). Coa axuda de BROWSE, a nosa tarefa resólvese de xeito bastante elemental, porque. para esta función (a diferenza de BUSCAR V), pode establecer explícitamente a dirección da busca: de arriba abaixo ou de abaixo cara arriba; o seu último argumento (-1) é responsable diso:

Método 3. Busca unha cadea coa data máis recente

Se nos datos de orixe temos unha columna cun número de serie ou unha data que xoga un papel similar, entón a tarefa é modificada; necesitamos atopar non a última liña (máis baixa) cunha coincidencia, senón a liña coa última ( data máxima).

Xa discutín en detalle como facelo usando funcións clásicas, e agora imos tentar usar o poder das novas funcións de matriz dinámica. Para unha maior beleza e comodidade, tamén convertemos a táboa orixinal nunha táboa "intelixente" mediante un atallo de teclado Ctrl+T ou comandos Inicio - Formato como unha táboa (Inicio — Formato como táboa).

Coa súa axuda, esta "parella asasino" resolve o noso problema con moita graza:

Aquí:

• Función primeiro FILTER (FILTRO) selecciona só aquelas filas da nosa táboa onde están na columna Cliente - o nome que necesitamos.
• Despois a función GRADE (ORDENAR) ordena as filas seleccionadas por data en orde descendente, coa oferta máis recente na parte superior.
• función Index (ÍNDICE) extrae a primeira fila, é dicir, devolve o último comercio que necesitamos.
• E, finalmente, a función de FILTRO externo elimina as columnas 1 e 3 extra dos resultados (Código de pedido и Cliente) e deixa só a data e o importe. Para iso, utilízase unha matriz de constantes. {0;1;0;1}, definindo que columnas queremos (1) ou non queremos (0).
  

Método 4: atopar a última coincidencia en Power Query

Ben, para completar, vexamos unha solución ao noso problema de busca inversa usando o complemento Power Query. Coa súa axuda, todo resólvese moi rápido e moi ben.

1. Imos converter a nosa táboa orixinal nunha "intelixente" usando un atallo de teclado Ctrl+T ou comandos Inicio - Formato como unha táboa (Inicio — Formato como táboa).

2. Cargao en Power Query co botón De Táboa/Range aba Data (Datos: da táboa/rango).

3. Ordenamos (a través da lista despregable do filtro da cabeceira) a nosa táboa por orde de data descendente, de xeito que as transaccións máis recentes estean enriba.

4… Na pestana Transformación escolle un equipo Agrupar por (Transformar — Agrupar por) e establece a agrupación por clientes e, como función de agregación, selecciona a opción Todas as liñas (Todas as filas). Podes poñerlle o nome á nova columna como queiras, por exemplo detalles.

Despois da agrupación, obteremos unha lista de nomes únicos dos nosos clientes e na columna detalles – táboas con todas as transaccións de cada unha delas, onde a primeira liña será a última transacción, que é o que necesitamos:

5. Engade unha nova columna calculada co botón Columna personalizada aba Engade columna (Engadir columna — Engadir columna personalizada)e introduza a seguinte fórmula:

Aquí detalles – esta é a columna da que tomamos táboas por clientes, e 0 {} é o número da fila que queremos extraer (a numeración das filas en Power Query comeza desde cero). Temos unha columna con rexistros (rexistro), onde cada entrada é a primeira fila de cada táboa:

Queda por ampliar o contido de todos os rexistros co botón con dobres frechas na cabeceira da columna Último trato seleccionando as columnas desexadas:

... e despois elimine a columna que xa non é necesaria detalles facendo clic co botón dereito sobre o seu título - Elimina columnas (Eliminar columnas).

Despois de cargar os resultados na folla a través Inicio — Pechar e cargar — Pechar e cargar (Inicio — Pechar e cargar — Pechar e cargar en…) teremos unha táboa tan bonita cunha lista de transaccións recentes, como queriamos:

Cando cambies os datos de orixe, non debes esquecer actualizar os resultados facendo clic co botón dereito sobre eles: o comando Actualizar e gardar (Actualizar) ou atallo de teclado Ctrl+alt+F5.

• A función BUSCAR é un descendente de BUSCARV
• Como usar as novas funcións de matriz dinámica SORT, FILTER e UNIC
• Busca a última cela non en branco nunha fila ou columna coa función BUSCAR