Bill ha posto la domanda di questa settimana sui dati Excel ridondanti.
Creo un elenco di transazioni mensili in Excel. Alla fine del mese, devo eliminare i dati ridondanti e ottenere un totale in base al codice account. Ogni codice account può essere ripetuto più volte. Bill ha quindi descritto la sua attuale metodologia Excel che è simile al metodo 1 di seguito per ottenere un elenco univoco di codici account, con l'intenzione di utilizzare una matrice di formule CSE per ottenere i totali. Chiede, esiste un modo più semplice per arrivare a un elenco univoco di codici account con totali per ciascun account?
Questa è una domanda perfetta per le vacanze. Essendo un utente Lotus da 15 anni, riconosco il metodo di Bill come il metodo classico per la manipolazione dei dati "rapida e sporca" dai bei vecchi tempi della versione 2.1 di Lotus. Questa è una stagione per contare le nostre benedizioni. Quando pensi a questa domanda, ti rendi conto che la gente di Microsoft ci ha davvero concesso una serie di strumenti nel corso degli anni. Se stai usando Excel 97, ci sono almeno cinque metodi per eseguire questa operazione, tutti molto più semplici del metodo classico descritto da Bill. Questa settimana offrirò un tutorial sui cinque metodi.
Il mio set di dati semplificato ha numeri di conto nella colonna A e importi nella colonna B. I dati vanno da A2: B100. Non è ordinato all'inizio.
Metodo 1
Usa istruzioni If creative insieme a Paste Special Values per trovare la risposta.
Dati gli strumenti più recenti offerti da Excel, non consiglio più questo metodo. Lo usavo molto prima che arrivassero cose migliori e ci sono ancora situazioni in cui è utile. Il mio nome alternativo per questo è il metodo "The-Lotus-123-When-You-Not-In-The-Mood-To-Use- @ DSUM". Ecco i passaggi.
- Ordina i dati per colonna A.
- Inventa una formula nella colonna C che manterrà un totale parziale per account. La cella C2 è
=IF(A2=A1,C1+B2,B2). - Inventa una formula in D che identificherà l'ultima voce per un particolare account. La cella D2 è
=IF(A2=A3,FALSE,TRUE). - Copia C2: D2 su tutte le tue righe.
- Copia C2: D100. Eseguire una Modifica - IncollaSpeciale - Valori di nuovo su C2: D100 per modificare le formule in valori.
- Ordina per colonna D decrescente.
- Per le righe che hanno TRUE nella colonna D, hai un elenco univoco di numeri di conto in A e il totale parziale finale in C.
Pro: è veloce. Tutto ciò di cui hai bisogno è un acuto senso di scrivere dichiarazioni IF.
Contro: ci sono modi migliori.
Metodo 2
Usa Filtro dati - Filtro avanzato per ottenere l'elenco di account univoci.
La domanda di Bill era davvero come ottenere un elenco univoco di numeri di conto in modo da poter utilizzare le formule CSE per ottenere i totali. Questo è un metodo per ottenere un elenco dei numeri di conto univoci.
- Evidenzia A1: A100
- Dal menu, seleziona Dati, Filtro, Filtro avanzato
- Fare clic sul pulsante di opzione "Copia in un'altra posizione".
- Fare clic sulla casella di controllo "Solo record univoci".
- Scegli una sezione vuota del foglio di lavoro in cui desideri visualizzare l'elenco univoco. Inseriscilo nel campo "Copia in:". (Tieni presente che questo campo è disattivato finché non selezioni "Copia in un'altra posizione".
- Fare clic su OK. I numeri di conto univoci appariranno in F1.
- Immettere eventuali manipolazioni della downline, formule di matrice, ecc. Per ottenere i risultati.
Pro: più veloce del metodo 1. Nessun smistamento richiesto.
Contro: Le formule CSE richieste dopo questo ti faranno girare la testa.
Metodo 3
Usa consolidamento dati.
La mia qualità della vita è migliorata quando Excel ha offerto Data Consolidate. Questo è stato GRANDE! Ci vogliono 30 secondi per configurarlo, ma ha scritto la morte per DSUM e altri metodi.Il tuo numero di conto deve essere a sinistra dei campi numerici che desideri totalizzare. Devi avere intestazioni sopra ogni colonna. È necessario assegnare un nome di intervallo al blocco rettangolare di celle che includa i numeri di conto lungo la colonna di sinistra e le intestazioni in alto. In questo caso, l'intervallo è A1: B100.
- Evidenzia A1: B100
- Assegna un nome di intervallo a quest'area facendo clic nella casella del nome (a sinistra della barra della formula) e digitando un nome come "TotalMe". (In alternativa, usa Inserisci - Nome).
- Posiziona il puntatore di cella in una sezione vuota del foglio di lavoro.
- Scegli dati - Consolida
- Nel campo di riferimento, digita il nome dell'intervallo (TotalMe).
- Nella sezione Usa etichette in, seleziona sia Riga superiore che Colonna sinistra.
- Fare clic su OK
Pro: questo è il mio metodo preferito. Nessun smistamento richiesto. La scorciatoia è alt-D N (rangename) alt-T alt-L invio. È facilmente scalabile. Se il tuo intervallo include 12 colonne mensili, la risposta avrà i totali per ogni mese.
Contro: se si esegue un altro consolidamento dei dati sullo stesso foglio, è necessario cancellare il vecchio nome dell'intervallo dal campo Tutti i riferimenti utilizzando il pulsante Elimina. Il numero di conto deve essere a sinistra dei tuoi dati numerici. È leggermente più lento delle tabelle pivot, il che diventa evidente per i set di dati con oltre 10.000 record.
Metodo 4
Usa i totali parziali dei dati.
Questa è una caratteristica interessante. Poiché è strano lavorare con i dati risultanti, lo uso meno spesso di Data Consolidate.
- Ordina per colonna A crescente.
- Seleziona una cella qualsiasi nell'intervallo di dati.
- Scegli Dati - Subtotali dal menu.
- Per impostazione predefinita, Excel offre il totale parziale dell'ultima colonna dei dati. Questo funziona in questo esempio, ma spesso devi scorrere l'elenco "Aggiungi totale parziale a:" per selezionare i campi corretti.
- Fare clic su OK. Excel inserirà una nuova riga a ogni modifica del numero di conto con un totale.
Dopo aver inserito i subtotali, vedrai apparire un piccolo 123 sotto la casella del nome. Fare clic su 2 per visualizzare solo una riga per account con i totali. Leggere Copia i totali parziali di Excel per una spiegazione dei passaggi speciali necessari per copiarli in una nuova posizione. Fare clic su 3 per vedere tutte le righe. Pro: caratteristica interessante. Ottimo per la stampa di report con totali e interruzioni di pagina dopo ogni sezione.
Contro: i dati devono essere prima ordinati. Lento per molti dati. Devi usare Goto-Special-VisbileCellsOnly per ottenere i totali altrove. Devi usare Data-Subtotals-RemoveAll per tornare ai tuoi dati originali.
Metodo 5
Usa una tabella pivot.
Le tabelle pivot sono le più versatili di tutte. I tuoi dati non devono essere ordinati. Le colonne numeriche possono essere a sinistra oa destra del numero di conto. Puoi facilmente fare in modo che i numeri di conto scendano verso il basso o attraverso la pagina.
- Seleziona una cella qualsiasi nell'intervallo di dati.
- Scegli Dati - Tabella pivot dal menu.
- Accetta le impostazioni predefinite nel passaggio 1
- Assicurati che l'intervallo di dati nel passaggio 2 sia corretto (di solito lo è)
- Se si utilizza Excel 2000, fare clic sul pulsante Layout al passaggio 3. Gli utenti di Excel 95 e 97 passano automaticamente al layout come passaggio 3.
- Nella finestra di dialogo del layout, trascina il pulsante Account dal lato destro della finestra di dialogo e rilascialo nell'area Riga.
- Trascina il pulsante Quantità dal lato destro della finestra di dialogo e rilascialo nell'area Dati.
- Gli utenti di Excel 2000 fanno clic su OK, gli utenti di Excel 95/97 fanno clic su Avanti.
- Specifica se desideri i risultati in un nuovo foglio o in una sezione specifica di un foglio esistente. Ulteriori informazioni sulle tabelle pivot in Trucchi avanzati tabella pivot di Excel.
- Le tabelle pivot offrono funzionalità incredibili e rendono questa attività un gioco da ragazzi. Per copiare i risultati della tabella pivot, è necessario eseguire Modifica-Incolla-valori speciali, altrimenti Excel non ti consentirà di inserire righe, ecc.
Pro: veloce, flessibile, potente. Veloce, anche per molti dati.
Contro: un po 'intimidatorio.
Bill ora ha quattro nuovi metodi per eliminare i dati ridondanti. Sebbene questi metodi non siano stati disponibili dall'inizio dei tempi, sia Lotus che Excel sono stati grandi innovatori per offrirci modi più rapidi per svolgere questo compito banale.
