Comparando dúas táboas

Temos dúas táboas (por exemplo, a versión antiga e a nova da lista de prezos), que necesitamos comparar e atopar rapidamente as diferenzas:

Inmediatamente queda claro que se engadiu algo á nova lista de prezos (datas, allos...), algo desapareceu (amoras, framboesas...), os prezos cambiaron para algúns produtos (figos, melóns...). Debes atopar e mostrar rapidamente todos estes cambios.

Para calquera tarefa en Excel, case sempre hai máis dunha solución (normalmente 4-5). Para o noso problema, pódense utilizar moitos enfoques diferentes:

• función VPR (BÚSQUEDA V) — busque os nomes de produtos da nova lista de prezos na antiga e amose o prezo antigo xunto á nova e, a continuación, detecte as diferenzas
• combina dúas listas nunha soa e despois crea unha táboa dinámica baseada nela, onde as diferenzas serán claramente visibles
• use o complemento de Power Query para Excel

Poñémolas todas en orde.

Método 1. Comparación de táboas coa función BUSCARV

Se non estás completamente familiarizado con esta función marabillosa, primeiro mira aquí e le ou mira un vídeo titorial sobre ela: aforrache un par de anos de vida.

Normalmente, esta función úsase para extraer datos dunha táboa a outra facendo coincidir algún parámetro común. Neste caso, empregarémolo para incorporar os prezos antigos ao novo:

Aqueles produtos, contra os que resultou o erro #N/A, non están na lista antiga, é dicir, foron engadidos. Os cambios de prezos tamén son claramente visibles.

Pros este método: sinxelo e claro, “clásico do xénero”, como din. Funciona en calquera versión de Excel.

Contra tamén está alí. Para buscar produtos engadidos á nova lista de prezos, terás que facer o mesmo procedemento na dirección oposta, é dicir, subir novos prezos ao prezo antigo coa axuda de BUSCAR V. Se mañá cambian os tamaños das táboas, haberá que axustar as fórmulas. Ben, e en mesas moi grandes (> 100 mil filas), toda esta felicidade ralentizarase decentemente.

Método 2: Comparación de táboas mediante un pivote

Imos copiar as nosas táboas unhas debaixo das outras, engadindo unha columna co nome da lista de prezos, para que despois poidas entender de que lista que fila:

Agora, baseándonos na táboa creada, crearemos un resumo Inserir - Táboa dinámica (Inserir — Táboa dinámica). Imos botar un campo Produto á área de liñas, campo prezo á área da columna e ao campo ЦEna dentro do intervalo:

Como podes ver, a táboa dinámica xerará automaticamente unha lista xeral de todos os produtos das listas de prezos antigas e novas (sen repeticións!) e ordenará os produtos alfabeticamente. Podes ver claramente os produtos engadidos (non teñen o prezo antigo), os produtos eliminados (non teñen o novo prezo) e os cambios de prezo, se é o caso.

Os totais xerais nesta táboa non teñen sentido e pódense desactivar na pestana Construtor - Totales xerais - Desactivar para filas e columnas (Deseño — Totais xerais).

Se os prezos cambian (¡pero non a cantidade de bens!), entón basta con actualizar o resumo creado facendo clic co botón dereito sobre el. refrescar.

Pros: Este enfoque é unha orde de magnitude máis rápido con táboas grandes que BUSCARV. 

Contra: cómpre copiar manualmente os datos uns baixo os outros e engadir unha columna co nome da lista de prezos. Se os tamaños das táboas cambian, entón tes que facer todo de novo.

Método 3: Comparación de táboas con Power Query

Power Query é un complemento gratuíto para Microsoft Excel que che permite cargar datos en Excel desde case calquera fonte e despois transformar estes datos da forma que desexes. En Excel 2016, este complemento xa está integrado por defecto na pestana Data (datos), e para Excel 2010-2013, cómpre descargalo por separado do sitio web de Microsoft e instalalo; obtén unha nova pestana Consulta de enerxía.

Antes de cargar as nosas listas de prezos en Power Query, primeiro deben converterse en táboas intelixentes. Para iso, seleccione o intervalo con datos e prema a combinación no teclado Ctrl+T ou selecciona a pestana da cinta Inicio - Formato como unha táboa (Inicio — Formato como táboa). Os nomes das táboas creadas pódense corrixir na pestana Construtor (Deixarei o estándar Táboa 1 и Táboa 2, que se obteñen por defecto).

Cargue o prezo antigo en Power Query usando o botón De Táboa/Range (De mesa/rango) dende a pestana Data (Data) ou dende a pestana Consulta de enerxía (dependendo da versión de Excel). Despois da carga, volveremos a Excel desde Power Query co comando Pechar e cargar: pechar e cargar en... (Pechar e cargar — Pechar e cargar a...):

… e na xanela que aparece, seleccione Só ten que crear unha conexión (só conexión).

Repita o mesmo coa nova lista de prezos. 

Agora imos crear unha terceira consulta que combinará e comparará os datos das dúas anteriores. Para iso, seleccione en Excel na pestana Datos – Obter datos – Combinar solicitudes – Combinar (Datos — Obter datos — Combinar consultas — Combinar) ou prema o botón Combinar (Combinar) aba Consulta de enerxía.

Na xanela de unión, seleccione as nosas táboas nas listas despregábeis, seleccione as columnas cos nomes dos produtos nelas e, na parte inferior, configure o método de unión: Externo completo (Exterior total):

Despois de premer OK debería aparecer unha táboa de tres columnas, onde na terceira columna cómpre ampliar o contido das táboas aniñadas usando a dobre frecha da cabeceira:

Como resultado, obtemos a combinación de datos de ambas táboas:

É mellor, por suposto, renomear os nomes das columnas na cabeceira facendo dobre clic sobre outros máis comprensibles:

E agora o máis interesante. Vaia á pestana Engade columna (Engadir columna) e fai clic no botón Columna condicional (Columna condicional). E despois, na xanela que se abre, introduza varias condicións de proba cos seus valores de saída correspondentes:

Queda por premer OK e cargue o informe resultante a Excel usando o mesmo botón pechar e descargar (Pechar e cargar) aba casa (Inicio):

A beleza.

Ademais, se se produce algún cambio nas listas de prezos no futuro (engádense ou borran liñas, cambian os prezos, etc.), bastará con actualizar as nosas solicitudes cun atallo de teclado. Ctrl+alt+F5 ou por botón Actualiza todo (Actualizar todo) aba Data (Data).

Pros: Quizais o xeito máis bonito e cómodo de todos. Funciona de forma intelixente con mesas grandes. Non require edicións manuais ao cambiar o tamaño das táboas.

Contra: Require que se instale o complemento Power Query (en Excel 2010-2013) ou Excel 2016. Non se deben cambiar os nomes das columnas nos datos de orixe, se non, obteremos o erro "Non se atopou a columna tal e tal". ao tentar actualizar a consulta.

• Como recompilar datos de todos os ficheiros de Excel nun cartafol determinado usando Power Query
• Como atopar coincidencias entre dúas listas en Excel
• Combinando dúas listas sen duplicados