L'altro giorno stavo per creare una combinazione unica di due colonne non adiacenti in Excel. Di solito lo faccio con Rimuovi duplicati o con Filtro avanzato, ma ho pensato di provare a farlo con la nuova funzione UNICA in arrivo su Office 365 nel 2019. Ho provato diverse idee e nessuna avrebbe funzionato. Quindi, sono andato dal maestro di Dynamic Arrays, Joe McDaid, per assistenza. La risposta è piuttosto interessante e sono sicuro che la dimenticherò, quindi la sto documentando per te e per me. Sono sicuro, tra due anni, cercherò su Google come farlo e realizzerò "Oh, guarda! Sono io che ho scritto l'articolo su questo!"
Prima di arrivare alla funzione UNIQUE, dai un'occhiata a quello che sto cercando di fare. Voglio ogni combinazione univoca di rappresentante di vendita dalla colonna B e Prodotto dalla colonna C. Normalmente, seguirei questi passaggi:
- Copiare le intestazioni da B1 e D1 in una sezione vuota del foglio di lavoro
- Da B1, scegli Dati, Filtro, Avanzate
- Nella finestra di dialogo Filtro avanzato, scegli Copia in una nuova posizione
- Specificare le intestazioni dal passaggio 1 come intervallo di output
- Scegli la casella Solo per valori univoci
- Fare clic su OK
Il risultato è ogni combinazione unica dei due campi. Tieni presente che il filtro avanzato non ordina gli elementi: vengono visualizzati nella sequenza originale.
Questo processo è diventato più semplice in Excel 2010 grazie al comando Rimuovi duplicati nella scheda Dati della barra multifunzione. Segui questi passi:
- Seleziona B1: D227 e Ctrl + C per copiare
-
Incolla in una sezione vuota del foglio di lavoro.
Crea una copia dei dati poiché Remove Duplicates è distruttivo - Scegli Dati, Rimuovi duplicati
- Nella finestra di dialogo Rimuovi duplicati, deseleziona Data. Questo dice a Excel di guardare solo Rep e Prodotto.
-
Fare clic su OK
Dì a Remove Duplicates di considerare solo Rep e Date
I risultati sono quasi perfetti: devi solo eliminare la colonna Data.
La domanda: esiste un modo per fare in modo che la funzione UNIQUE esamini solo le colonne B & D? (Se non hai ancora visto la nuova funzione UNICA, leggi: Funzione UNICA in Excel.)
Chiedere ti =UNIQUE(B2:D227)darebbe ogni combinazione unica di rappresentante, data e prodotto che non è quello che stiamo cercando.
Quando gli array dinamici sono stati introdotti a settembre, ho detto che non avremmo più dovuto preoccuparci della complessità delle formule Ctrl + Maiusc + Invio. Ma per risolvere questo problema, userete un concetto chiamato sollevamento. Si spera che a questo punto abbiate scaricato il mio e-book di Excel Dynamic Arrays Straight To The Point. Vai alle pagine 31-33 per una spiegazione completa del sollevamento.
Prendi una funzione di Excel che si aspetta un singolo valore. Ad esempio, =CHOOSE(Z1,"Apple","Banana")restituirebbe Apple o Banana a seconda che Z1 contenga 1 (per Apple) o 2 (per Banana). La funzione SCEGLI si aspetta uno scalare come primo argomento.
Invece, passerai una costante di matrice di (1,2) come primo argomento di SCEGLI. Excel eseguirà l'operazione di sollevamento e calcolerà SCEGLI due volte. Per il valore 1, vuoi i rappresentanti di vendita in B2: B227. Per il valore di 2, vuoi i prodotti in D2: D227.
Normalmente, nel vecchio Excel, l'intersezione implicita avrebbe rovinato i risultati. Ma ora che Excel può trasferire i risultati a molte celle, la formula sopra restituisce correttamente una matrice di tutte le risposte in B e D:
Mi sento come se stessi insultando la tua intelligenza scrivendo il resto dell'articolo, perché da qui è semplicissimo.
Avvolgi la formula dello screenshot precedente in UNICO e otterrai solo le combinazioni uniche di rappresentante di vendita e prodotto utilizzando =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).
Per verificare la tua comprensione, prova a modificare la formula sopra per restituire tutte le combinazioni univoche di tre colonne: Rappresentante di vendita, Prodotto, Colore.
Innanzitutto, modificare la costante dell'array in modo che faccia riferimento a (1,2,3).
Quindi, aggiungere un quarto argomento a scegliere di tornare colore da E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).
Sarebbe bello ordinare questi risultati, quindi ci rivolgiamo a Ordina con una formula che utilizza SORT e SORTBY.
Normalmente, la funzione per ordinare in base alla prima colonna crescente sarebbe =SORT(Array)o =SORT(Array,1,1).
Per ordinare per tre colonne, è necessario eseguire un po 'di sollevamento a coppie con =SORT(Array,(1,2,3),(1,1,1)). In questa formula, quando arrivi al secondo argomento di SORT, Excel vuole sapere in base a quale colonna ordinare. Invece di un singolo valore, invia tre colonne all'interno di una costante di matrice: (1,2,3). Quando si arriva al terzo argomento in cui si specifica 1 per Ascendente o -1 per Discendente, inviare una costante di matrice con tre 1 per indicare Ascendente, Ascendente, Ascendente. Lo screenshot seguente mostra =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).
Almeno fino alla fine del 2018, puoi scaricare gratuitamente il libro Excel Dynamic Arrays utilizzando il link in fondo a questa pagina.
Sono incoraggiato a scoprire che la risposta alla domanda di oggi è un po 'complicata. Quando è uscito Dynamic Arrays, ho immediatamente pensato a tutte le formule incredibili pubblicate nella bacheca dei messaggi da Aladin Akyurek e altri e come quelle formule sarebbero diventate molto più semplici nel nuovo Excel. Ma l'esempio di oggi mostra che ci sarà ancora bisogno per i geni delle formule di creare nuovi modi per utilizzare gli array dinamici.
Guarda un video
Scarica il file Excel
Per scaricare il file excel: univoco da colonne non adiacenti.xlsx
Excel pensiero del giorno
Ho chiesto ai miei amici di Excel Master per i loro consigli su Excel. Pensiero di oggi su cui riflettere:
"Regole per gli elenchi: nessuna riga vuota, nessuna colonna vuota, intestazioni di una cella, come con Mi piace"
Anne Walsh
