Trikovi u Excelu 62. deo – Smestite sve u jedan red.

Sredite podatke kod kojih svaka stavka zauzima pet fizičkih redova

Problem korisnika:

Nekada davno, u vreme programskog jezika COBOL, jedan programer se suočavao s ograničenjima fizičke širine strane. Napravio je izveštaj u kojem je svaka stavka faktički zauzimala pet redova izveštaja.

Hteo bih da mogu da analiziram te podatke u Excelu (Slika 1).

Slika 1.

Rešenje:

Vaš cilj je da stavite podatke u jedan red po stavci. Taj proces dodaje nove kolone,

Group i Sequence:

Slika 2.

1. Dodajte novi red br. 1. Ubacite dve nove kolone, A i B. U ćelije A1 do C1 dodajte

naslove Group, Sequence i Text (Slika 2).

Slika 3.

2. U koloni A dodelite broj grupe svakoj logičkoj stavci. Jedan od načina da se to učini je da se proveri da li su u koloni C prva četiri slova ACCT. Ako jesu, upišite broj 1 kao broj grupe u ćeliju A2. U ćeliju A3 upišite formulu =IF(LEFT(C3,4)=”ACCT”,1+A2,A2) (Slika 3). Iskopirajte je u sve redove. Excel će dodeliti broj grupe svakoj logičkoj grupi stavki.

Slika 4.

3. Napravite formulu za redni broj. Da biste to uradili, u ćeliju B2 upišite formulu =IF(A2=A1,B1+1,1) (Slika 4). Iskopirajte je u ćelije ispod. Ta formula će numerisati svaku stavku u grupi. To bi trebalo da osigura da svi brojevi računa budu u stavci pod rednim brojem 1.

4. (Ovaj korak je presudan) Kopirajte formule u kolonama A i B pa ih iskopirajte tu gde jesu izborom kartice Home, padajućeg menija Paste i opcije Paste Values kako biste osigurali da možete bezbedno da sortirate podatke.

Slika 5.

5. Sortirajte podatke po rednom broju u koloni B. To će izgledati kao na Slici 5.

Sada ste uspeli da inteligentno razdvojite podatke tako da sve slične stavke budu zajedno. Kontinualni opseg ćelija C2:C7 sadrži sve prve redove iz svake stavke. Svaka od stavki u prvom redu ima tri polja koja zapravo treba razdvojiti u tri zasebne kolone.

To razdvajanje možete lako uraditi pomoću “čarobnjaka” Text to Columns Wizard.

1. Izaberite ćelije C2:C7. Izaberite opcije Data pa Text to Columns da otvorite Convert Text to Columns Wizard. Izaberite Fixed Width pa pritisnite dugme Next.

2. Excel bi trebalo da zaključi gde se kolone nalaze. Pritisnite dugme Next.

Slika 6.

3. Izaberite naslov za svaku kolonu i definišite format podataka. Ne treba Vam svaki put reč ACCT, pa preskočite prvo, treće i peto polje. Šesto polje definišite kao datum.

Kada Vaši podaci budu izgledali kao što je prikazano u donjem delu, pritisnite dugme Finish (kraj). Dobićete podatke u tri kolone Grupe 1 (Slika 6).

4. Promenite naslov u ćeliji C1 u Acct, naslov u ćeliji D1 u Inv, a naslov u ćeliji E1 u Date.

5. Izaberite ćelije A8:C13 pa ih premestite (“cut” pa “paste”) u ćeliju F2.

6. Iz kolona F i G izbrišite Group i Sequence.

7. U ćeliju F1 dodajte naslov Inv $.

8. Izaberite ćelije F2:F6, opciju Data pa Text to Columns. U prvom koraku “čarobnjaka” izaberite opciju Fixed Width pa pritisnite dugme Next. U drugom koraku “čarobnjaka” Excel nudi da razdvoji podatke na tri polja. Nema potrebe da imate jednu kolonu za reč Invoice a drugu za reč Total.

Slika 7.

Slika 8

9. Pritisnite taster miša dva puta na liniji između reči Invoice i Total da je izbrišete

(Slike 7 i 8).

Slika 9.

10. U trećem koraku “čarobnjaka”, preskočite polje koje sadrži Invoice Total (slika 9). Pritisnite dugme Finish.

11. Stavke za grupe broj 3 do 5 imaju samo po jedno polje bez naslova. Iskopirajte ćelije C14:C19 u ćeliju G2. Dodajte naslov “Company”.

12. Iskopirajte ćelije kolone C Grupe 4 u ćeliju H2. Dodajte naslov “Address”.

13. Iskopirajte ćelije kolone C Grupe 5 u ćeliju I2. Dodajte naslov “City ST Zip”

14. Pošto stavke Grupe 6 nemaju nikakve podatke nego su samo isprekidane linije, izbrišite te redove. Sada imate sva polja, jedan red po stavci.

15. Obrišite suvišne kolone A i B.

Rezultat:

Sada imate verziju originalnog skupa podataka koja može da se sortira, filtrira i stavi u izveštaj (Slika 10). Svaka stavka se sastoji iz jednog reda u Excelu.

Slika 10.