contingut
Tenim dues taules (per exemple, la versió antiga i la nova de la llista de preus), que hem de comparar i trobar ràpidament les diferències:
De seguida queda clar que s'ha afegit alguna cosa a la nova llista de preus (dàtils, alls...), alguna cosa ha desaparegut (mores, gerds...), els preus d'alguns productes (figues, melons...). Heu de trobar i mostrar ràpidament tots aquests canvis.
Per a qualsevol tasca a Excel, gairebé sempre hi ha més d'una solució (normalment 4-5). Per al nostre problema, es poden utilitzar molts enfocaments diferents:
- function VPR (VISUALITZACIÓ) — cerqueu els noms de productes de la nova llista de preus a l'antiga i visualitzeu el preu antic al costat de la nova i, a continuació, detecteu les diferències
- fusioneu dues llistes en una i després creeu una taula dinàmica basada en ella, on les diferències seran clarament visibles
- utilitzeu el complement Power Query per a Excel
Posem-los tots en ordre.
Mètode 1. Comparació de taules amb la funció BUSCARV
Si no esteu familiaritzat amb aquesta característica meravellosa, primer mireu aquí i llegiu o mireu un tutorial en vídeo sobre ella: estalvieu-vos un parell d'anys de vida.
Normalment, aquesta funció s'utilitza per extreure dades d'una taula a una altra fent coincidir algun paràmetre comú. En aquest cas, l'utilitzarem per introduir els preus antics al nou preu:
Aquells productes contra els quals va resultar l'error #N/A no es troben a la llista antiga, és a dir, s'han afegit. Els canvis de preu també són clarament visibles.
pros aquest mètode: senzill i clar, “clàssic del gènere”, com diuen. Funciona en qualsevol versió d'Excel.
Contres també hi és. Per cercar productes afegits a la nova llista de preus, haureu de fer el mateix procediment en sentit contrari, és a dir, pujar nous preus al preu antic amb l'ajuda de VLOOKUP. Si les mides de les taules canvien demà, s'hauran d'ajustar les fórmules. Bé, i en taules molt grans (> 100 mil files), tota aquesta felicitat s'alentirà decentment.
Mètode 2: comparació de taules mitjançant un pivot
Copiem les nostres taules una sota l'altra, afegint-hi una columna amb el nom de la llista de preus, de manera que més endavant pugueu entendre de quina llista quina fila:
Ara, a partir de la taula creada, crearem un resum Insereix - Taula dinàmica (Insereix — Taula dinàmica). Tirem un camp Producte a l'àrea de línies, camp preu a l'àrea i al camp de la columna Цena a la gamma:
Com podeu veure, la taula dinàmica generarà automàticament una llista general de tots els productes de les llistes de preus antigues i noves (sense repeticions!) i ordenarà els productes alfabèticament. Podeu veure clarament els productes afegits (no tenen el preu antic), els productes eliminats (no tenen el nou preu) i els canvis de preu, si n'hi ha.
Els totals generals d'aquesta taula no tenen sentit i es poden desactivar a la pestanya Constructor - Totals generals - Desactiva per a files i columnes (Disseny — Totals generals).
Si els preus canvien (però no la quantitat de mercaderies!), n'hi ha prou amb actualitzar el resum creat fent-hi clic amb el botó dret del ratolí. Refresh.
pros: Aquest enfocament és un ordre de magnitud més ràpid amb taules grans que BUSCARV.
Contres: heu de copiar manualment les dades les unes a les altres i afegir una columna amb el nom de la llista de preus. Si les mides de les taules canvien, haureu de tornar a fer-ho tot.
Mètode 3: comparació de taules amb Power Query
Power Query és un complement gratuït per a Microsoft Excel que us permet carregar dades a Excel des de gairebé qualsevol font i després transformar aquestes dades de la manera desitjada. A Excel 2016, aquest complement ja està integrat de manera predeterminada a la pestanya dades (dades), i per a Excel 2010-2013, heu de descarregar-lo per separat del lloc web de Microsoft i instal·lar-lo: obteniu una pestanya nova Power Consulta.
Abans de carregar les nostres llistes de preus a Power Query, primer s'han de convertir en taules intel·ligents. Per fer-ho, seleccioneu l'interval amb dades i premeu la combinació al teclat Ctrl+T o seleccioneu la pestanya de la cinta Inici – Format com a taula (Inici — Format com a taula). Els noms de les taules creades es poden corregir a la pestanya constructor (Deixaré l'estàndard Taula 1 и Taula 2, que s'obtenen per defecte).
Carregueu el preu antic a Power Query amb el botó De Taula/Range (Des de la taula/gama) de la pestanya dades (Data) o des de la pestanya Power Consulta (segons la versió d'Excel). Després de carregar, tornarem a Excel des de Power Query amb l'ordre Tancar i carregar: tancar i carregar a... (Tancar i carregar — Tancar i carregar a...):
… i a la finestra que apareix, seleccioneu Només cal crear una connexió (Només connexió).
Repetiu el mateix amb la nova llista de preus.
Ara creem una tercera consulta que combinarà i compararà les dades de les dues anteriors. Per fer-ho, seleccioneu a Excel a la pestanya Dades – Obtenir dades – Combinar sol·licituds – Combinar (Dades — Obtenir dades — Combinar consultes — Combinar) o premeu el botó Combinar (Combinar) llengüeta Power Consulta.
A la finestra d'unió, seleccioneu les nostres taules a les llistes desplegables, seleccioneu les columnes amb els noms dels productes i, a la part inferior, configureu el mètode d'unió: Extern complet (Complet exterior):
Després de fer clic OK hauria d'aparèixer una taula de tres columnes, on a la tercera columna heu d'ampliar el contingut de les taules imbricades mitjançant la doble fletxa de la capçalera:
Com a resultat, obtenim la fusió de dades de les dues taules:
És millor, per descomptat, canviar el nom dels noms de les columnes de la capçalera fent doble clic en altres més comprensibles:
I ara el més interessant. Vés a la pestanya Afegeix columna (Afegeix una columna) i feu clic al botó Columna condicional (Columna condicional). A continuació, a la finestra que s'obre, introduïu diverses condicions de prova amb els seus valors de sortida corresponents:
Queda per clicar OK i carregueu l'informe resultant a Excel amb el mateix botó tancar i descarregar (Tancar i carregar) llengüeta Home (Inici):
Bellesa.
A més, si es produeixen canvis a les llistes de preus en el futur (s'afegeixen o suprimeixen línies, canvien els preus, etc.), n'hi haurà prou amb actualitzar les nostres peticions amb una drecera de teclat. Ctrl+Alt+F5 o amb botó Actualitza-ho tot (Actualitza-ho tot) llengüeta dades (Data).
pros: Potser la manera més bonica i còmoda de totes. Funciona de manera intel·ligent amb taules grans. No requereix edicions manuals per canviar la mida de les taules.
Contres: requereix la instal·lació del complement Power Query (a Excel 2010-2013) o Excel 2016. Els noms de les columnes de les dades d'origen no s'han de canviar, en cas contrari obtindrem l'error "No s'ha trobat la columna tal i tal!" quan intenteu actualitzar la consulta.
- Com recopilar dades de tots els fitxers d'Excel d'una carpeta determinada mitjançant Power Query
- Com trobar coincidències entre dues llistes a Excel
- Combinant dues llistes sense duplicats