Contenuti
Abbiamo due tabelle (ad esempio la vecchia e la nuova versione del listino), che dobbiamo confrontare e trovare velocemente le differenze:
Si vede subito che qualcosa è stato aggiunto al nuovo listino (datteri, aglio…), qualcosa è scomparso (more, lamponi…), i prezzi sono cambiati per alcuni beni (fichi, meloni…). È necessario trovare e visualizzare rapidamente tutte queste modifiche.
Per qualsiasi attività in Excel, c'è quasi sempre più di una soluzione (di solito 4-5). Per il nostro problema, possono essere utilizzati molti approcci diversi:
- function VPR (CERCA.VERT) — cerca i nomi dei prodotti dal nuovo listino in quello vecchio e visualizza il vecchio prezzo accanto a quello nuovo, quindi rileva le differenze
- unisci due elenchi in uno e quindi crea una tabella pivot basata su di esso, in cui le differenze saranno chiaramente visibili
- utilizzare il componente aggiuntivo Power Query per Excel
Mettiamoli tutti in ordine.
Metodo 1. Confronto delle tabelle con la funzione CERCA.VERT
Se non hai dimestichezza con questa meravigliosa funzione, guarda prima qui e leggi o guarda un video tutorial su di essa: risparmia un paio d'anni di vita.
In genere, questa funzione viene utilizzata per eseguire il pull dei dati da una tabella all'altra facendo corrispondere alcuni parametri comuni. In questo caso, lo useremo per inserire i vecchi prezzi nel nuovo prezzo:
Quei prodotti, rispetto ai quali si è verificato l'errore #N/D, non sono nel vecchio elenco, ovvero sono stati aggiunti. Anche le variazioni di prezzo sono chiaramente visibili.
Vantaggi questo metodo: semplice e chiaro, “classico del genere”, come si suol dire. Funziona in qualsiasi versione di Excel.
Svantaggi c'è anche Per cercare i prodotti aggiunti al nuovo listino, dovrai eseguire la stessa procedura in senso inverso, ovvero riportare i nuovi prezzi al vecchio prezzo con l'aiuto di VLOOKUP. Se le dimensioni delle tabelle cambiano domani, le formule dovranno essere modificate. Bene, e su tavoli davvero grandi (> 100 mila righe), tutta questa felicità rallenterà decentemente.
Metodo 2: confronto di tabelle utilizzando un pivot
Copiamo le nostre tabelle una sotto l'altra, aggiungendo una colonna con il nome del listino, in modo che in seguito possiate capire da quale listino quale riga:
Ora, in base alla tabella creata, creeremo un riepilogo tramite Inserisci – Tabella pivot (Inserisci — Tabella pivot). Lanciamo un campo Prodotto all'area delle linee, campo Prezzo all'area e al campo della colonna Цena nella gamma:
Come puoi vedere, la tabella pivot genererà automaticamente un elenco generale di tutti i prodotti del vecchio e del nuovo listino (nessuna ripetizione!) e ordinerà i prodotti in ordine alfabetico. Puoi vedere chiaramente i prodotti aggiunti (non hanno il vecchio prezzo), i prodotti rimossi (non hanno il nuovo prezzo) e le eventuali variazioni di prezzo.
I totali generali in una tabella del genere non hanno senso e possono essere disabilitati nella scheda Costruttore – Totali generali – Disattiva per righe e colonne (Design — Totali generali).
Se cambiano i prezzi (ma non la quantità di merce!), è sufficiente aggiornare semplicemente il riepilogo creato facendo clic con il tasto destro su di esso – ricaricare.
Vantaggi: questo approccio è un ordine di grandezza più veloce con tabelle di grandi dimensioni rispetto a VLOOKUP.
Svantaggi: è necessario copiare manualmente i dati uno sotto l'altro e aggiungere una colonna con il nome del listino. Se le dimensioni dei tavoli cambiano, devi rifare tutto da capo.
Metodo 3: confronto delle tabelle con Power Query
Power Query è un componente aggiuntivo gratuito per Microsoft Excel che consente di caricare i dati in Excel da quasi tutte le origini e quindi di trasformare questi dati nel modo desiderato. In Excel 2016, questo componente aggiuntivo è già integrato per impostazione predefinita nella scheda Dati (Dati), e per Excel 2010-2013 devi scaricarlo separatamente dal sito Web di Microsoft e installarlo: ottieni una nuova scheda Power Query.
Prima di caricare i nostri listini in Power Query, devono prima essere convertiti in tabelle intelligenti. Per fare ciò, seleziona l'intervallo con i dati e premi la combinazione sulla tastiera Ctrl+T oppure seleziona la scheda sulla barra multifunzione Home – Formatta come una tabella (Home — Formatta come tabella). I nomi delle tabelle create possono essere corretti nella scheda Costruttore (Lascerò lo standard Tabella 1 и Tabella 2, che vengono ottenuti per impostazione predefinita).
Carica il vecchio prezzo in Power Query utilizzando il pulsante Dalla tabella/intervallo (Dalla tabella/intervallo) dalla scheda Dati (Data) o dalla scheda Power Query (a seconda della versione di Excel). Dopo il caricamento, torneremo in Excel da Power Query con il comando Chiudi e carica – Chiudi e carica in... (Chiudi e carica — Chiudi e carica in...):
… e nella finestra che appare seleziona Basta creare una connessione (Solo connessione).
Ripetere lo stesso con il nuovo listino.
Ora creiamo una terza query che combinerà e confronterà i dati delle due precedenti. Per fare ciò, seleziona in Excel nella scheda Dati – Ottieni dati – Combina richieste – Combina (Dati — Ottieni dati — Unisci query — Unisci) oppure premere il pulsante Combinare (Unisci) linguetta Power Query.
Nella finestra di unione, seleziona le nostre tabelle negli elenchi a discesa, seleziona le colonne con i nomi delle merci in esse e, in basso, imposta il metodo di unione – Esterno completo (esterno completo):
Dopo aver fatto clic su OK dovrebbe apparire una tabella di tre colonne, dove nella terza colonna è necessario espandere il contenuto delle tabelle nidificate utilizzando la doppia freccia nell'intestazione:
Di conseguenza, otteniamo la fusione dei dati da entrambe le tabelle:
È meglio, ovviamente, rinominare i nomi delle colonne nell'intestazione facendo doppio clic su quelli più comprensibili:
E ora il più interessante. Vai alla scheda Aggiungi colonna (Aggiungi colonna) e fare clic sul pulsante Colonna condizionale (colonna condizionale). Quindi, nella finestra che si apre, inserisci diverse condizioni di test con i relativi valori di output:
Resta da cliccare OK e caricare il report risultante in Excel utilizzando lo stesso pulsante chiudi e scarica (Chiudi e carica) linguetta Casa (Casa):
Beauty.
Inoltre, se in futuro dovessero verificarsi delle modifiche ai listini (vengono aggiunte o cancellate righe, cambiano i prezzi, ecc.), basterà aggiornare le nostre richieste con una scorciatoia da tastiera Ctrl+altro+F5 o tramite pulsante Aggiorna tutto (Aggiorna tutto) linguetta Dati (Data).
Vantaggi: Forse il modo più bello e conveniente di tutti. Funziona in modo intelligente con tavoli di grandi dimensioni. Non richiede modifiche manuali durante il ridimensionamento delle tabelle.
Svantaggi: richiede l'installazione del componente aggiuntivo Power Query (in Excel 2010-2013) o di Excel 2016. I nomi delle colonne nei dati di origine non devono essere modificati, altrimenti verrà visualizzato l'errore "Colonna tale e tale non è stata trovata!" quando si tenta di aggiornare la query.
- Come raccogliere dati da tutti i file di Excel in una determinata cartella utilizzando Power Query
- Come trovare corrispondenze tra due elenchi in Excel
- Unire due liste senza duplicati