Bill a pus întrebarea de săptămâna aceasta despre datele Excel redundante.
Construiesc o listă lunară de tranzacții în Excel. La sfârșitul lunii, trebuie să elimin datele redundante și să obțin un total după codul contului. Fiecare cod de cont poate apărea de mai multe ori. Apoi, Bill a descris metodologia sa Excel actuală, similară metodei 1 de mai jos, pentru a veni cu o listă unică de coduri de cont, cu planuri de a utiliza o matrice de formule CSE pentru a obține totalurile. El întreabă, există o modalitate mai ușoară de a ajunge la o listă unică de coduri de cont cu totaluri pentru fiecare cont?
Aceasta este o întrebare perfectă de vacanță. Fiind un utilizator Lotus de 15 ani, recunosc metoda lui Bill ca fiind metoda clasică pentru manipularea „rapidă și murdară” a datelor din vremurile bune ale lansării Lotus 2.1. Acesta este un anotimp pentru a ne număra binecuvântările. Când vă gândiți la această întrebare, vă dați seama că oamenii de la Microsoft ne-au oferit într-adevăr o serie de instrumente de-a lungul anilor. Dacă utilizați Excel 97, există cel puțin cinci metode pentru a realiza această sarcină, toate fiind mult mai ușoare decât metoda clasică descrisă de Bill. În această săptămână voi oferi un tutorial despre cele cinci metode.
Setul meu de date simplificat are numere de cont în coloana A și sume în coloana B. Datele rulează de la A2: B100. Nu este sortat la început.
Metoda 1
Utilizați instrucțiunile creative If împreună cu Lipirea valorilor speciale pentru a găsi răspunsul.
Având în vedere instrumentele mai noi oferite de Excel, nu mai recomand această metodă. Obișnuiam să folosesc asta mult înainte să apară lucruri mai bune și există încă situații în care este util. Numele meu alternativ pentru aceasta este metoda „The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM”. Iată pașii.
- Sortați datele după coloana A.
- Inventați o formulă în coloana C, care va păstra un total în funcție de cont. Celula C2 este
=IF(A2=A1,C1+B2,B2). - Inventați o formulă în D care va identifica ultima intrare pentru un anumit cont. Celula D2 este
=IF(A2=A3,FALSE,TRUE). - Copiați C2: D2 în jos pe toate rândurile.
- Copiați C2: D100. Efectuați o editare - PasteSpecial - Valori înapoi pe C2: D100 pentru a schimba formulele în valori.
- Sortați după coloana D descendent.
- Pentru rândurile care au un ADEVĂRAT în coloana D, aveți o listă unică de numere de cont în A și totalul final executat în C.
Pro: Este rapid. Tot ce aveți nevoie este un sentiment acut de a scrie declarații IF.
Contra: Există modalități mai bune.
Metoda 2
Utilizați Filtru date - Filtru avansat pentru a obține lista conturilor unice.
Întrebarea lui Bill a fost cu adevărat cum să obțină o listă unică de numere de cont, astfel încât să poată utiliza formulele CSE pentru a obține totalurile. Aceasta este o metodă pentru obținerea unei liste cu numerele de cont unice.
- Evidențiați A1: A100
- Din meniu, alegeți Date, Filtru, Filtru avansat
- Faceți clic pe butonul radio pentru „Copiați într-o altă locație”.
- Faceți clic pe caseta de selectare „Numai înregistrări unice”.
- Alegeți o secțiune goală din foaia de lucru unde doriți să apară lista unică. Introduceți acest lucru în câmpul „Copiați în:”. (Rețineți că acest câmp este gri până când alegeți „Copiați într-o altă locație”.
- Faceți clic pe OK. Numerele de cont unice vor apărea în F1.
- Introduceți orice manipulare a liniei descendente, formule de matrice etc. pentru a obține rezultatele.
Pro: Mai rapid decât Metoda 1. Nu este necesară sortarea.
Contra: Formulele CSE necesare după aceasta vă vor face să vă rotiți capul.
Metoda 3
Utilizați date Consolidate.
Calitatea vieții mele s-a îmbunătățit atunci când Excel a oferit Data Consolidate. A fost MARE! Durează 30 de secunde pentru a-l configura, dar a indicat moartea pentru DSUM-uri și alte metode. Numărul contului dvs. trebuie să fie în stânga câmpurilor numerice pe care doriți să le totalizați. Trebuie să aveți titluri deasupra fiecărei coloane. Trebuie să atribuiți un nume de interval blocului dreptunghiular de celule care include numerele de cont de-a lungul coloanei din stânga și titlurile de-a lungul de sus. În acest caz, acest interval este A1: B100.
- Evidențiați A1: B100
- Atribuiți un nume de zonă acestei zone făcând clic în caseta de nume (în stânga barei de formule) și tastând un nume precum „TotalMe”. (Alternativ, utilizați Insert - Name).
- Puneți indicatorul de celulă într-o secțiune goală a foii de lucru.
- Alegeți date - Consolidați
- În câmpul de referință, tastați numele intervalului (TotalMe).
- În secțiunea Utilizați etichete din, verificați atât rândul superior, cât și coloana din stânga.
- Faceți clic pe OK
Pro: Aceasta este metoda mea preferată. Nu este necesară sortarea. Comanda rapidă este alt-D N (rangename) alt-T alt-L enter. Este ușor scalabil. Dacă gama dvs. include 12 coloane lunare, răspunsul va avea totaluri pentru fiecare lună.
Contra: Dacă faceți o altă Consolidare de date pe aceeași foaie, trebuie să ștergeți vechiul nume al intervalului din câmpul Toate referințele folosind butonul Ștergere. Numărul contului trebuie să fie în stânga datelor numerice. Este ușor mai lent decât tabelele pivot, ceea ce devine vizibil pentru seturile de date cu peste 10.000 de înregistrări.
Metoda 4
Utilizați subtotale de date.
Aceasta este o caracteristică interesantă. Deoarece datele rezultate sunt ciudate de utilizat, le folosesc mai rar decât Data Consolidate.
- Sortați după coloana A crescătoare.
- Selectați orice celulă din intervalul de date.
- Alegeți Date - Subtotale din meniu.
- În mod implicit, Excel oferă subtotalul ultimei coloane a datelor dvs. Acest lucru funcționează în acest exemplu, dar de multe ori trebuie să parcurgeți lista „Adăugați subtotal la:” pentru a alege câmpurile corecte.
- Faceți clic pe OK. Excel va insera un rând nou la fiecare modificare a numărului de cont cu un total.
După ce ați introdus subtotalurile, veți vedea un mic 123 care apare sub caseta de nume. Faceți clic pe 2 pentru a vedea doar un rând pe cont cu totaluri. Citiți Copierea subtotalelor Excel pentru o explicație a pașilor speciali necesari pentru a le copia într-o locație nouă. Faceți clic pe 3 pentru a vedea toate liniile. Pro: Cool Feature. Excelent pentru imprimarea rapoartelor cu totaluri și pagini după fiecare secțiune.
Contra: Datele trebuie mai întâi sortate. Lent pentru multe date. Trebuie să utilizați Goto-Special-VisbileCellsOnly pentru a obține totalurile în altă parte. Trebuie să utilizați Data-Subtotale-RemoveAll pentru a reveni la datele dvs. originale.
Metoda 5
Utilizați un tabel pivot.
Tabelele pivot sunt cele mai versatile dintre toate. Datele dvs. nu trebuie să fie sortate. Coloanele numerice pot fi la stânga sau la dreapta numărului de cont. Puteți face cu ușurință ca numerele de cont să coboare sau să traverseze pagina.
- Selectați orice celulă din intervalul de date.
- Alegeți Date - PivotTable din meniu.
- Acceptați valorile implicite din pasul 1
- Asigurați-vă că intervalul de date din pasul 2 este corect (de obicei este)
- Dacă utilizați Excel 2000, faceți clic pe butonul Layout la pasul 3. Utilizatorii Excel 95 și 97 merg automat la layout ca la pasul 3.
- În dialogul de aspect, trageți butonul Cont din partea dreaptă a dialogului și plasați-l în zona Rând.
- Trageți butonul Suma din partea dreaptă a dialogului și plasați-l în zona Date.
- Utilizatorii Excel 2000 fac clic pe OK, utilizatorii Excel 95/97 fac clic pe Următorul.
- Specificați dacă doriți rezultatele într-o foaie nouă sau într-o secțiune specifică a unei foi existente. Citiți mai multe despre tabelele pivot în Trucuri avansate ale tabelului pivot Excel.
- Tabelele pivot oferă funcționalități incredibile și fac din această sarcină o apăsare rapidă. Pentru a copia rezultatele tabelului pivot, trebuie să faceți un Edit-PasteSpecial-Values, altfel Excel nu vă va permite să inserați rânduri etc.
Pro: rapid, flexibil, puternic. Rapid, chiar și pentru o mulțime de date.
Contra: Oarecum intimidant.
Bill are acum patru metode noi pentru eliminarea datelor redundante. Deși aceste metode nu au fost disponibile de la începutul timpului, atât Lotus, cât și Excel au fost inovatori extraordinari pentru a ne oferi modalități mai rapide de a realiza această sarcină mondenă.
