Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka

Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 04.04.2018. u 11:13 · pre 104 meseca #1

Probacu najednostavnije da objasnim,
imam vise sheet-ova (grupisani po nekom kriterijumu godina 2015,2016,2017 ...) u kojima ima podaci: 1. Dobavlja, 2. broj otpremnice, 3. dan, 4. mesec, 5. godine, 6. proizvod, 7. iznos, pa opet (dan, mesec, godina, proizvod, iznos, i to onoliko puta koliko je nabavljeno za tog proizvodjaca na osnovu te otpremnice).

Sad kako iz horizontalnog unosa prebaciti u vertikalni unos, a da (dan, mesec, godina, proizvod, iznos) PRATI odgovarajuceg dobavljaca i broj otpremnice, kao u prilogu, iz sheet-a PRIMER u sheet RESENJE?

Unapred hvala

primer.xlsx11.77k

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 04.04.2018. u 16:10 · pre 104 meseca #2

Pa možeš da rotiraš tako što iskopiraš i pri paste operaciji odabereš Transpose:
https://support.office.com/en-...e3-beab-4318-aae5-d0f862209744

Ima i neki dodatak, tipa Ablebits Ultimate Suite, koji omogućava još raznih operacija koje nisu podržane u Excel-u (neke, kao npr. rotacija podataka, podržane su u novijim verzijama).

Ovo što ti je uneto u Sheet:PRIMER je besmisleno, ne znam ko ti je to napravio tako i unosio podatke. Ti praktično kao da imaš dve velike kolone (kao npr. u Word-u), pa u njima tabele istog/sličnog formata u svakoj koloni, i sad bi u novom Sheet-u da tu drugu kolonu staviš ispod prve.

Na žalost, Excel ti vidi celu vrstu kao jedan niz podataka, tako da ne možeš tek tako da razdvojiš ove fiktivne kolone.

Uostalom, kako može biti isti broj otpremnice za dva različita datuma (u PRIMER-u imaš u kolonama C,D,E u 7. vrsti jedan datum, a u kolonama M,N,O drugi datum, i to je za istu otpremnicu)?
Takođe, čemu odvajanje kolona za godinu, mesec i dan, kad možeš da imaš kolonu samo za datum i da odabereš format datuma tako da ti sortiranje prema datumu (rastuće/opadajuće) funkcioniše kako treba?

Potpis

Blessed are those who can laugh at themselves, for they shall never cease to be amused.

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 04.04.2018. u 17:33 · pre 104 meseca #3

tako je moralo biti uneto sa razloga sto bi se A i B kolone koje parte ostale.

Nesto slicno je JPECA ranije mi objasnjavao i nasao sam neku formulu, ali kako je primeniti u ovom konretnom ali da prati A i B kolona kopiranje

=INDEX(podaci,1+INT((ROW(A1)-1)/COLUMNS(podaci)),MOD(ROW(A1)-1+COLUMNS(podaci),COLUMNS(podaci))+1)

formula je za>

AABBCC
DDEEFF
GGHHII
JJKKLL
MMNNOO, a koja daje

AA
BB
CC
DD
EE
FF

sada bi bilo:
AAAAABBBBBCCCCC
DDDDDEEEEEFFFFF
GGGGGHHHHHIIII
JJJJJKKKKKLLLLL
MMMMMNNNNNOOOOO

AAAAA
BBBBB
CCCCC
DDDDD
EEEEE
.....

s tim da prate one prve dve dobavljac i otpremnica
dobavljac, otpremnicaAAAAA
dobavlajc, otpremnicaBBBBBB
dobavljac, otpremnicaCCCCC
.....

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 05.04.2018. u 13:27 · pre 104 meseca #4

Verovatno je moguće i pomoću formula ali imaš dva rešenja koja su jednostavnija
A. Jedno relativno jednostavno rešenje je napraviti rutinu u VBA koja to odrađuje. Prolazi se red po red početne tabel i onda uzimaju prve dve po pet kolona iza treće i prepisuju u novu tabelu.
Kod nije komplikovan, dodao sam objašnjenja u samom kodu - vidi radnu svesku u prilogu
Da probaš makroi moraju da budu dozvoljeni u radnoj svesci.
1/ Obriši sadržaj ispod zaglavlja na listu DemoVBA (Zaglavlje ručno formirati gde treba, ne formira se u rutini)
2/ Prebaci se na list PRIMER
3/ Alt+F8, Test da startuješ makro
4/ U prvom dijalogu selektuj oblast A2:Q24 na listu primer
5/ U drugom dijalogu selektuj A2 na listu DemoVBA

B. Drugo rešenje je korišćenjem alata Get & Trasform (u Excel 2016), ranije poznatog kao Power Query. Na forumu koliko ja znam nije bilo mnogo govora o ovom alatu. Osnovna njegova primena je da se u Excel uvuku podaci iz eksternih fajlova ili baze podataka, međutim moguće je kao izvor izabrati tabelu u Excelu, pa tako ovaj alata koristi za razičite pretumbacije.
Kroz ovaj alat svaki korak transformacije se snima i kasnije se jedonstavno može pozvati ceo niz. Tokom rada imaš vizalni prikaz rezultata. Ako nešto pogrešiš obrišeš zadnji korak pa ponovo.
Osnova ideja u tvom slučaju da se iskoriste Unpivot i Pivot alatke za to što ti treba. Obrati pažnju da je za primenu Pivot bitno da postoji jedinstvana kombinacija u ostatku reda (koji se ne pivotuje) Kako kod tebe dobavljač i broj otpremnice nisu jedinstveni dodata je kolona sa brojačem kako bi napravila jedinstvena kombinacija.
Prethodno sam sredio nazive kolona da bi bilo lakše i jasnije - nazivi kolona su bitni za pivot.

Snimio sam postupak u priloženom videu.
Rezime postupka:
0/ Nazive kolona stavio da budu po "sekcijama" Dan1, Mes1...Iznos1, Dan2, Mes2...Iznos2 itd.
1/ Aktiviraš Data, Get i Transform sekcija > From Table. Istu alatku u prethodnoj verziji Excela naći ćeš u Power Query tab-u. Alat će izvori opseg pretvoriti u tabelu ako već nije - mora da bude tabela.
2/ Unutar alata kora po korak
2-1/ Uradio Unpivot za sve kolone osim prve dve. Sad su nazivi prešli u kolonu Attribute a vrednosti u Value.
2-2/ Izbacio red gde nema broj otpreminice (null) i iznos. Ovaj red pravi problem a ne znam da li treba
2-3/ Formirao novu kolonu za budućim nazivima kolona - skinuo broj sa kraja Dan, Mes ... Formula: Text.Start([Attribute], Text.Length([Attribute])-1)
2-4/ Izbacio stare nazive kolona (Attribute)
2-5/ Dodao indeksnu kolonu na osnovu koje ću formirati brojač (vidi gore)
2-6/ Dodao novu kolonu sa formulom za brojač. Forumla celobrojno deljenje indeksa sa 5: Number.IntegerDivide([Index],5)
2-7/ Obrisao indeksnu kolonu - ne treba
2-8/ Stavio brojac kolonu na početak zbog preglednosti
2-9/ Uradio Pivot po koloni ColName za vrednosti u Value koloni - nema agregiranja
Sad smo prebacili vrednosti u odgovarajuće koloen Dan, Mes, ....Iznos
2-10/ Na kraju obrišemo Brojac kolonu kao višak
2-11/ Rezultat prebacimo u Excel - novi list

3/ Kad se izmene podaci u izvornoj tabeli, dovoljnoj je desni klik Refresh na odredišnu tabelu da se osveži sa novim podacima, ceo postupak će se automatki ponoviti

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

ViseOpsegaJednoIspodDrugog.xlsm36.04k GetTransform_UnpivotAndPivot.webm4525k

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 05.04.2018. u 15:54 · pre 104 meseca #5

probao vba,
SVAKA CAST, HVALA PUNO!!!

imam pitanje da li mogu da koristim vise prednjih celija (dobavljac, otpremnica) npr. jos 2, i/ili vise ovih iza (dan,mesec,godina,proizvod,cena) npr. jos 2?

SVAKAKO cu da isprobam i pivot, samo mi je on uvek predstavljao konfuziju, mada i ostalo predstavlja konfuziju (za amatera kao sto sam ja, i loseg matematicara),

ali VBA je odlicna stvar, sta sve moze da uradi, kada se zna.

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 05.04.2018. u 16:04 · pre 104 meseca #6

Postupak koji sam opisao u rešenju B nema veza sa Pivot iz Excel. Pivot/Unpivot odnose se na postupak sređivanja podataka, odnosno korake "širenja" odnosno "skupljanja" tabele u okviru postupka.

Alt+F11 otvoriš VBE. Glavni deo je ova procedura

Code:

Sub CopyAndTransform(srcTbl As Range, dest As Range)
' Prepisuje se tabela srcTbl u novu sa pocetkom u celiji dest
' Svaki red nove tabele sadrzi
' 1/ Prve dve kolone iz reda
' 2/ Narednih pet kolona Dan,Mes,God, Proizvod, Iznos dok god ima popunjenih kolona
' P.Jovanovic za elitesecurity.org

Dim rw As Long, rwEnd As Long
Dim r As Long
Dim cl As Integer, clEnd As Integer
rwEnd = srcTbl.Rows.Count
clEnd = srcTbl.Columns.Count
r = 0   ' Brojac redova u odredisnoj tablei
For rw = 1 To rwEnd  ' Petlja po redovima izvorne tabele
   For cl = 3 To clEnd Step 5   ' Petlja po kolonama izvorne tabele
      If srcTbl.Cells(rw, cl).Offset(0, 3).Value > 0 Then                 ' Ako je Proizvod prazno preskace se
        dest.Offset(r, 0) = srcTbl.Cells(rw, 1)  ' Dobavljac
        dest.Offset(r, 1) = srcTbl.Cells(rw, 2)  ' Broj otpremnice
        dest.Offset(r, 2) = srcTbl.Cells(rw, cl)           'Dan
        dest.Offset(r, 3) = srcTbl.Cells(rw, cl + 1)   'Mes
        dest.Offset(r, 4) = srcTbl.Cells(rw, cl + 2)     'God
        dest.Offset(r, 5) = srcTbl.Cells(rw, cl + 3)    'Proizvod
        dest.Offset(r, 6) = srcTbl.Cells(rw, cl + 4)      'Iznos
        r = r + 1  ' Sledeci red
      End If
   Next cl
Next rw

End Sub

U proceduri cl=3 je od koje kolone se preslikavaju (prve dve se prepisuju). Naravno to može da se menja . Step 5 je korak. Upisao kolonu po kolonu šta se dešava pa bi trebalo da je razumljivo

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 05.04.2018. u 16:09 · pre 104 meseca #7

da li treba da se upise dodatne kolone u postupku, mislim na "Upisao kolonu po kolonu šta se dešava pa bi trebalo da je razumljivo"?

moram detaljno pogledati video da bih to razumeo, i usput raditi isto sto si i ti.

ali svakako HVALA, na detaljnom objasnjenju i uradjenom primeru.

Re: Prebacivanje i horizontale u vertikalu, sa ostavljanjem podataka 05.04.2018. u 16:39 · pre 104 meseca #8

Video se odnosi na rešenje B

Što se tiče koda ovo

Code:
dest.Offset(r, 1) = srcTbl.Cells(rw, 2) ' Broj otpremnice

U odnosu na početnu ćeilju odredišta dest npr. list DemoVBA A2 uzima pozicionira se r redova dole i 1 red desno (znači B2, B3... kako se menja r) i
Upisuje vrednost iz izvornog opsega srcTbl, red rw, kolona 2

i slično za

Code:
dest.Offset(r, 5) = srcTbl.Cells(rw, cl + 3) 'Proizvod

U odnosu na početnu ćeilju odredišta dest uzima pozicionira se r redova dole i 1 red desno (znači F2, F3... kako se menja r) i upisuje vrednost iz izvorog opsega
srCTbl, red rw i kolona cl+3. cl je 3, pa 8(+5), pa 13 itd.

Naravno ako ima dodatnih kolona treba ih ubaciti u kod kao izmeniti step, koliko kolona se prepisuju. Moraš da se potrudiš da razumeš kod da bi mogao da ga prepravljaš

Potpis

Nije to loše Rembrante, samo što ne bi dodao još malo boje?

Forum kao aplikacija na telefonu

Srodne teme