Come confrontare due colonne in Excel: metodi di confronto dei dati di Excel

Questo è un capitolo del libro: Michael Girwin. Ctrl + Maiusc + Invio. Padroneggiare le formule di matrice in Excel.

Campioni basati su una o più condizioni.Riga funzioni di Excel utilizzare operatori di confronto. Ad esempio, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIFS e AVERAGEIFS. Queste funzioni effettuano le selezioni in base a una o più condizioni (criteri). Il problema è che queste funzioni possono solo aggiungere, contare e fare la media. E se desideri imporre condizioni alla tua ricerca, come il valore massimo o la deviazione standard? In questi casi, poiché non esiste una funzione incorporata, è necessario inventare una formula di matrice. Ciò è spesso dovuto all'uso dell'operatore di confronto degli array. Il primo esempio in questo capitolo mostra come calcolare il valore minimo in una condizione.

Usiamo la funzione IF per selezionare gli elementi dell'array che soddisfano una condizione. Nella fig. 4.1 nella tabella di sinistra c'è una colonna con i nomi delle città e una colonna con l'ora. È necessario trovare il tempo minimo per ogni città e inserire questo valore nella cella corrispondente della tabella di destra. La condizione per la selezione è il nome della città. Se utilizzi la funzione MIN, puoi trovare il valore minimo per la colonna B. Ma come fai a selezionare solo quei numeri che sono specifici di Oakland? E come si copiano le formule nella colonna? Poiché Excel non dispone di una funzione MINESLI incorporata, è necessario scrivere una formula originale che combini le funzioni SE e MIN.

Figura: 4.1. Scopo della formula: scegli il tempo minimo per ogni città

Scarica una nota in formato o formato

Come mostrato in fig. 4.2, dovresti iniziare a inserire la formula nella cella E3 con la funzione MIN. Ma non puoi mettere in discussione numero 1 tutti i valori nella colonna B!? Si desidera selezionare solo i valori specifici di Auckland.

Come mostrato in fig. 4.3, nel passaggio successivo, inserisci la funzione IF come argomento numero 1 per MIN. Metti IF all'interno di MIN.

Posizionando il cursore nel punto in cui è inserito l'argomento log_expression funzione IF (Fig. 4.4), si seleziona un intervallo con nomi di città A3: A8, quindi si preme F4 per rendere assoluti i riferimenti di cella (vedere, ad esempio, per maggiori dettagli). Quindi digiti l'operatore di confronto, il segno di uguale. Infine, selezionerai la cella a sinistra della formula - D3, mantenendo il riferimento ad essa relativo. La condizione formulata ti consentirà di selezionare solo Aucklands quando visualizzi l'intervallo A3: A8.


Figura: 4.4. Crea operatore di matrice sull'argomento log_expression funzioni IF

Quindi hai creato un operatore di matrice utilizzando l'operatore di confronto. In qualsiasi momento durante l'elaborazione di un array, l'operatore di array è un operatore di confronto, quindi il risultato della sua operazione sarà un array di valori TRUE e FALSE. Per verificarlo, seleziona l'array (per fare ciò, fai clic nel tooltip sull'argomento log_expression) e premere F9 (fig. 4.5). Di solito usi un argomento log_expression, restituendo TRUE o FALSE; qui, la matrice risultante restituirà più valori VERO e FALSO, quindi la funzione MIN selezionerà solo il numero minimo di città che corrispondono al valore VERO.


Figura: 4.5. Per vedere un array di valori VERO e FALSO, fare clic sull'argomento nella descrizione comando log_expression e premere F9

Abbiamo due tabelle di ordini copiate in un foglio di lavoro. È necessario confrontare i dati di due tabelle in Excel e verificare quali posizioni sono nella prima tabella, ma non nella seconda. Non ha senso confrontare manualmente il valore di ogni cella.

Confronta due colonne per le corrispondenze in Excel

Come confrontare i valori in Excel di due colonne? Per risolvere questo problema, ti consigliamo di utilizzare formattazione condizionaleper evidenziare rapidamente gli elementi che si trovano in una sola colonna. Foglio di lavoro con tabelle:

Il primo passaggio consiste nell'assegnare nomi a entrambe le tabelle. Ciò rende più semplice capire quali intervalli di celle vengono confrontati:

  1. Selezionare lo strumento FORMULE - Nomi definiti - Assegna nome.
  2. Nella finestra che appare, nel campo "Nome:", inserisci il valore - Tabella_1.
  3. Fare clic con il pulsante sinistro del mouse sul campo di immissione "Intervallo:" e selezionare l'intervallo: A2: A15. E fai clic su OK.


Per il secondo elenco, fai lo stesso, ma assegna un nome: Table_2. E indica l'intervallo C2: C15 - rispettivamente.

Consiglio utile! I nomi degli intervalli possono essere assegnati più rapidamente utilizzando il campo del nome. Si trova a sinistra della barra della formula. Basta selezionare gli intervalli di celle e nel campo del nome digitare il nome appropriato per l'intervallo e premere Invio.

Ora usiamo la formattazione condizionale per confrontare due elenchi in Excel. Dobbiamo ottenere il seguente risultato:

Verranno visualizzate le posizioni che si trovano nella Tabella_1, ma non nella Tabella_2 in verde... Allo stesso tempo, le posizioni situate nella Tabella_2, ma assenti nella Tabella_1, verranno evidenziate in blu.

Principio di confronto dei dati di due colonne in Excel

Abbiamo utilizzato la funzione CONTA.SE per definire le condizioni per la formattazione delle celle delle colonne. In questo esempio, questa funzione controlla quante volte il valore del secondo argomento (ad esempio, A2) si trova nell'elenco del primo argomento (ad esempio, Table_2). Se il numero di volte \u003d 0, la formula restituisce VERO. In questo caso, alla cella viene assegnato il formato personalizzato specificato nelle opzioni di formattazione condizionale. Il riferimento nel secondo argomento è relativo, il che significa che tutte le celle dell'intervallo selezionato verranno controllate a turno (ad esempio, A2: A15). La seconda formula funziona in modo simile. Lo stesso principio può essere applicato a vari compiti simili. Ad esempio, per confrontare due prezzi in Excel, anche

Quando si lavora in Excel, un'attività abbastanza comune è confrontare diversi tipi di elenchi di valori. Gli strumenti standard di Excel come la formattazione condizionale e le funzioni possono essere utilizzati per confrontare intervalli di valori in generale e colonne di valori in particolare. Inoltre, è possibile risolvere tali attività utilizzando macro VBA e componenti aggiuntivi per Excel basati su di essi.

La necessità di confrontare intervalli in generale e colonne in particolare nasce quando si aggiornano vari report, elenchi e database generati in Excel e richiedono modifiche nel tempo.NEL reti Internet Sono stati descritti molti esempi che utilizzano varie funzioni (formule) e formattazione condizionale per confrontare i valori nelle colonne. Per coloro per i quali tali metodi non sono adatti per qualsiasi motivo, è possibile utilizzare un altro strumento che consente di confrontare entrambe le colonne e qualsiasi intervallo di valori costituito da un numero qualsiasi di righe e colonne. Utilizzando il componente aggiuntivo per Excel, è possibile confrontare due intervalli qualsiasi, o meglio, confrontare ogni elemento di un intervallo con ciascun elemento dell'altro e trovare sia le corrispondenze che le differenze tra di loro di propria scelta.

Componente aggiuntivo per il confronto di Excel per valori in due intervalli

Il componente aggiuntivo, che verrà discusso di seguito, è progettato per confrontare i valori in due intervalli, costituiti da un numero qualsiasi di righe e colonne. Questo componente aggiuntivo utilizza una procedura per confrontare ogni elemento in un intervallo con ogni elemento in un altro intervallo, determinare quali elementi in questi intervalli sono uguali e quali sono diversi e riempie le celle con un colore specificato dall'utente.

Come puoi vedere dalla finestra di dialogo, tutto è abbastanza semplice, l'utente seleziona due intervalli da confrontare, determina cosa cercare, valori diversi o uguali, seleziona un colore e avvia il programma. Il risultato finale dell'utilizzo del componente aggiuntivo è riempire le celle con un colore specifico, che indica differenze o sovrapposizioni nei valori degli intervalli selezionati.

C'è una sfumatura quando si confrontano i valori in Excel. I numeri possono essere formattati come testo, che non è sempre identificabile visivamente (). Cioè, un numero in Excel può essere un valore numerico o un valore di testo e questi due valori non sono uguali tra loro. Molto spesso questo fenomeno causa vari tipi di errori. Per escludere tali errori, viene utilizzata l'opzione "Confronta numeri come testo", che è abilitata per impostazione predefinita. L'utilizzo di questa opzione consente di confrontare valori non numerici, ma il testo convertito da essi.

L'add-on consente:

1. Con un clic del mouse, aprire la finestra di dialogo della macro direttamente dalla barra degli strumenti di Excel;

2. trova gli elementi della gamma # 1 che non sono nella gamma # 2;

3. trova gli elementi della gamma # 2 che non sono nella gamma # 1;

4. trova gli elementi della gamma # 1 che sono nella gamma # 2;

5. trova gli elementi della gamma # 2 che sono nella gamma # 1;

6. scegli uno dei nove colori di riempimento per le celle con i valori desiderati;

7. selezionare rapidamente gli intervalli utilizzando l'opzione "Limita intervalli", mentre è possibile selezionare intere righe e colonne, riducendo l'intervallo selezionato a quello utilizzato viene eseguita automaticamente;

8. invece di confrontare i valori numerici, utilizzare il confronto dei valori di testo utilizzando l'opzione "Confronta numeri come testo";

9. confrontare i valori nelle celle dell'intervallo senza tener conto degli spazi extra;

10. Confronta i valori nelle celle di un intervallo senza distinzione tra maiuscole e minuscole.

Come confrontare due colonne utilizzando una macro (componente aggiuntivo) per Excel?

Il confronto di colonne è un caso speciale di confronto di intervalli arbitrari. Negli intervalli # 1 e # 2, seleziona due colonne e puoi selezionare esattamente le colonne e non trascinare il riquadro di selezione sugli intervalli con celle con il mouse (per comodità, l'opzione "Limita intervalli" è abilitata per impostazione predefinita, che , nel caso di selezione di intere colonne o righe, limita tale selezione utilizzando l'intervallo), selezionare l'azione richiesta per cercare differenze o corrispondenze, selezionare il colore di riempimento delle celle ed eseguire il programma. Di seguito puoi vedere il risultato della ricerca di valori corrispondenti in due colonne.

La possibilità di confrontare due set di dati in Excel è spesso utile per le persone che elaborano grandi quantità di dati e lavorano con tabelle enormi. Ad esempio, il confronto può essere utilizzato nella correttezza dell'inserimento di dati o dell'inserimento di dati in una tabella in tempo. L'articolo seguente descrive diverse tecniche per confrontare due colonne con i dati in Excel.

Utilizzo dell'operatore condizionale IF

Il metodo di utilizzo dell'operatore condizionale IF è diverso in quanto solo la parte necessaria per il confronto viene utilizzata per confrontare due colonne e non l'intera matrice. I passaggi per implementare questo metodo sono descritti di seguito:

Posiziona entrambe le colonne per il confronto nelle colonne A e B del foglio di lavoro.

Nella cella C2, inserisci la seguente formula \u003d IF (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) ed estendila alla cella C11. Questa formula cerca in sequenza ogni elemento dalla colonna A nella colonna B e restituisce il valore dell'elemento se trovato nella colonna B.


Utilizzando la formula di sostituzione CERCA.VERT

Il principio della formula è simile al metodo precedente, la differenza sta in, invece di RICERCA. Caratteristica distintiva Questo metodo è anche la capacità di confrontare due array orizzontali utilizzando la formula HLP.

Per confrontare due colonne con i dati nelle colonne A e B (simile al metodo precedente), inserisci la seguente formula \u003d CERCA.VERT (A2; $ B $ 2: $ B $ 11; 1; 0) nella cella C2 e trascinala su cella C11.


Questa formula esamina ogni elemento dalla matrice principale nella matrice confrontata e restituisce il suo valore se è stato trovato nella colonna B. In caso contrario, Excel restituirà un errore # N / D.

Utilizzo di VBA Macro

L'utilizzo delle macro per confrontare due colonne può unificare il processo e ridurre i tempi di preparazione dei dati. La decisione su quale risultato del confronto deve essere visualizzato dipende interamente dalla tua immaginazione e abilità nell'uso delle macro. Di seguito la tecnica pubblicata sul sito Web ufficiale di Microsoft.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub Find_Matches ()
Dim CompareRange As Variant, x As Variant, y As Variant
"Impostazione della variabile CompareRange uguale all'intervallo confrontato
Imposta intervallo confronto \u003d intervallo ("B1: B11")
"Se l'intervallo confrontato si trova su un foglio o una cartella di lavoro diversi,
"utilizza la seguente sintassi
"Set CompareRange \u003d Workbooks (" Book2 "). _
"Fogli di lavoro (" Foglio2 "). Intervallo (" B1: B11 ")
"
"Confronto di ogni articolo nell'intervallo selezionato con ogni articolo
"Variabile CompareRange
Per ogni x nella selezione
Per ogni y in CompareRange
Se x \u003d y Allora x. Offset (0, 2) \u003d x
Avanti y
Avanti x
End Sub

In questo codice, alla variabile CompareRange viene assegnato un intervallo con l'array confrontato. Quindi esegue un ciclo che scorre ogni elemento nell'intervallo selezionato e lo confronta con ogni elemento nell'intervallo confrontato. Se sono stati trovati elementi con gli stessi valori, la macro inserisce il valore dell'elemento nella colonna C.

Per utilizzare la macro, torna al foglio di lavoro, seleziona l'intervallo principale (nel nostro caso, queste sono celle A1: A11), premi Alt + F8. Nella finestra di dialogo visualizzata, seleziona la macro Trova_Partitee fare clic sul pulsante di esecuzione.


Dopo aver eseguito la macro, il risultato dovrebbe essere il seguente:

Utilizzo del componente aggiuntivo Inquire

Risultato

Quindi, abbiamo esaminato diversi modi per confrontare i dati in Excel che ti aiuteranno a risolvere alcuni problemi analitici e semplificare la ricerca di valori duplicati (o univoci).

Quando si confrontano diversi articoli comparabili in Fogli di calcolo Excel, i dati sono spesso organizzati in colonne in modo che sia conveniente confrontare le caratteristiche di questi oggetti riga per riga. Ad esempio, modelli di automobili, telefoni, gruppi sperimentali e di controllo, un certo numero di catene di negozi al dettaglio, ecc. Con un numero elevato di linee, l'analisi visiva non può essere affidabile. Le funzioni CERCA.VERT, INDICE, RICERCA (CERCA.VERT, INDICE, CONFRONTA) sono utili per confrontare i dati tra le celle e non forniscono un quadro generale. Come fai a scoprire quanto sono simili le colonne in generale? Le colonne sono identiche?

Il componente aggiuntivo Mappa colonne ti consente di mappare le colonne e vedere il quadro generale:

  • Confronta due o più colonne tra loro
  • Confronta colonne con valori di riferimento
  • Calcola la percentuale esatta di corrispondenza
  • Presenta il risultato in una tabella pivot visiva

Lingua video: inglese. Sottotitoli: russo, inglese. (Attenzione: il video potrebbe non riflettere ultimi aggiornamenti... Utilizzare le istruzioni di seguito.)

Aggiungi "Corrispondenza colonne" a Excel 2016, 2013, 2010, 2007

Adatto a: Microsoft Excel 2016-2007, desktop Office 365 (32 bit e 64 bit).

Come lavorare con il componente aggiuntivo:

Come confrontare due o più colonne tra loro e calcolare la percentuale di corrispondenza

Considera un esempio di sviluppo del prodotto. Supponi di dover confrontare tra loro diversi prototipi finiti e scoprire quanto sono simili, diversi e forse anche identici.

  1. Fare clic sul pulsante Mappa colonne nella barra degli strumenti XLTools\u003e Seleziona mappare colonne tra loro.

  3. Fare clic su OK\u003e


Consigli:
Per favore seleziona tabella riassuntiva risultato\u003e Fare clic sull'icona Express Analysis\u003e Applica "Scala colori".

Leggendo il risultato: i prototipi di Tipo 1 e Tipo 3 sono quasi identici, il tasso di corrispondenza del 99% significa che il 99% dei loro parametri nelle linee sono gli stessi. Il Tipo 2 e il Tipo 4 sono i meno simili: i loro parametri coincidono solo del 30%.

Come confrontare le colonne con i valori di riferimento e calcolare il grado di corrispondenza

Considera un esempio di sviluppo del prodotto. Supponiamo di dover confrontare diversi prototipi finiti con un determinato standard di destinazione e calcolare anche il grado in cui i prototipi soddisfano questi standard.

  1. Seleziona le colonne da confrontare.
    Ad esempio, colonne con dati prototipo.
  2. Fare clic sul pulsante Colonne mappa nella barra degli strumenti XLTools.
  3. Selezionare Mappa su intervallo di colonne di riferimento\u003e Seleziona colonne di riferimento.
    Ad esempio, colonne con standard.
  4. In tal caso, seleziona "Le colonne contengono titoli".
  5. Seleziona "Mostra percentuale di corrispondenza" per visualizzare la corrispondenza come percentuale.
    In caso contrario, il risultato viene visualizzato come 1 (corrispondenza completa) o 0 (nessuna corrispondenza).
  6. Indicare dove posizionare il risultato: su un nuovo foglio o su un foglio esistente.
  7. Fare clic su OK\u003e Fine, il risultato viene visualizzato nella tabella pivot.


Consigli: Per semplificare l'interpretazione del risultato, applica la formattazione condizionale:
Seleziona la tabella pivot del risultato\u003e Fai clic sull'icona Analisi rapida\u003e Applica "Scala colori".

Lettura del risultato: il prototipo di tipo 2 è conforme al 99% allo standard 2, ad es. Il 99% dei loro parametri nelle stringhe sono gli stessi. Il prodotto 5 è il più vicino allo standard 3: il 96% dei parametri è identico. Allo stesso tempo, il prodotto 4 è ben lungi dal soddisfare nessuno dei tre standard. Ora possiamo concludere quanto ciascuno dei prototipi si discosta dai valori di riferimento target.

Quali attività saranno utili per il componente aggiuntivo Abbina colonne?

Il componente aggiuntivo analizza le celle riga per riga e calcola la percentuale di valori identici nelle colonne. XLTools Match Columns non è adatto per il confronto normale dei valori nelle celle: non è progettato per trovare duplicati o valori univoci.

Il componente aggiuntivo Colonne mappa ha uno scopo diverso. Sua il compito principale - scoprire come, in generale, i dataset (colonne) sono simili o differenti. Il componente aggiuntivo aiuta con l'analisi di una grande quantità di dati quando è necessario guardare a un livello macro più ampio, ad esempio. rispondere a queste domande:

  • Quanto sono simili le prestazioni dei gruppi sperimentali
  • Quanto sono simili i risultati dei gruppi sperimentali e di controllo
  • Quanto simili / diversi sono diversi prodotti della stessa categoria
  • Quanto sono vicini i KPI dei dipendenti agli indicatori pianificati?
  • Quanto sono simili gli indicatori di diversi negozi al dettaglio, ecc.


Ti consigliamo di leggere