Trikovi u Excelu 89. deo – Kako dodati broj kupca svakom detaljnom zapisu?
Dodajte broj kupca svakom detaljnom zapisu.
Problem korisnika: Uvezao sam jedan skup podataka gde se informacije o kupcima pojavljuju jednom u koloni A, iza čega sledi bilo koji broj detaljnih podataka o fakturi. Posle prvog kupca, u koloni A se nalazi sledeći broj kupca pa detaljni podaci za tog kupca (Slika 1). Ti podaci se ne mogu sortirati. Informacija o kupcu mora da bude u sopstvenoj koloni za svaki zapis.
Rešenje: To je uobičajen format podataka ali je u Excel-u užasan. Evo kako ćete rešiti taj problem:
1. Ubacite nove kolone A i B. Dodajte naslove Acct i Customer. Ovo je osnovna logika onoga što hoćete da uradite: pogledajte prva četiri znaka u koloni C. Ako su ista kao Acct, onda znate da taj red sadrži informacije o kupcu pa uzimate podatke iz te ćelije i prebacujete ih u kolonu A. Ako su prva četiri znaka bilo šta drugo osim Acct, koristite iste informacije o broju kupca iz kolone A prethodnog reda.
2. U kolonu A unesite sledeću formulu: =IF(LEFT(C2,4)="Acct",MID(C2,6,5),A1) (Slika 2). Iskopirajte ovu formulu u celoj koloni A. Kad je iskopirate, ona će obaviti ono što treba. U ćeliji A2 je uslov IF ispunjen pa se podaci izvlače iz ćelije C2. U ćeliji A3 uslov nije ispunjen pa se koristi vrednost iz ćelije A2. U ćeliji A7 je pronađen novi broj kupca pa se podaci iz ćelije C7 koriste u ćeliji A7. Ćelije A8 do A59 dobijaju broj kupca iz ćelije A7.
3. Slična logika je potrebna i u koloni B. Međutim, u ovom slučaju treba da preuzmete naziv kupca (Slika 3). Znate da reč "Acct" i razmak koji sledi imaju pet znakova. Znate da je vaš broj kupca još pet znakova pa zatim ide razmak pre naziva kupca. Zato hoćete da ignorišete prvih 11 znakova ćelije C2. Možete da koristite formulu =MID(C2,12,50) da preskočite prvih 11 znakova i da prikažete sledećih 50 slova naziva kupca. Koristite ovu formulu kao TRUE deo IF funkcije.
4. U ćeliju B2 unesite sledeću formulu: =IF(LEFT(C2,4)="Acct",MID(C2,12,50),B1). Iskopirajte je u celoj koloni B. Sada ste uspešno ispunili kolone za broj i naziv kupca. Te formule treba da pretvorite u vrednosti.
5. Izaberite kolone A i B. Pritisnite Ctrl+C da ih iskopirate. Izaberite karticu Home, padajući meni Paste pa opciju Paste Values da pretvorite formule u vrednosti. To radite da biste uklonili naslovne redove za kupce. Dok razmišljate o načinu kako da izdvojite naslovne redove, zapazićete da su naslovni redovi jedini koji imaju prazne ćelije u koloni D. Te ćelije možete da premestite na kraj niza podataka tako što ćete sortirati podatke po koloni D.
6. Izaberite naslov u ćeliji D1. Izaberite opciju Data, pa AZ da sortirate podatke po rastućim datumima. Svi redovi koji nemaju unetu vrednost u koloni D automatski će se sortirati na dno skupa podataka (Slika 4).
Dok je pokazivač ćelije još na ćeliji D1 još označena, pritisnite taster End pa onda dva puta taster strelice nadole. Tada će se pokazivač ćelije nalaziti na prvom naslovu za kupca. Obrišite sve redove ispod reda 564.
Rezultati: Sada u svakom redu imate čist skup podataka s informacijama o kupcima (Slika 5). Te podatke možete da sortirate i koristite na druge načine za analizu podataka.
