Šta je novo?

Питање за Excel експерташе

Corey

Slavan
Učlanjen(a)
14.01.2005
Poruke
868
Poena
319
Имам више фајлова који су форматирани на исти начин, а то значи да су у истим колонама исти подаци. Оно што ме интересује је да ли могу да формирам нови фајл, који ће да повуче један део података из другог фајла на основу подударности вредности из једне колоне. Желим да се аутоматски попуни колона L повлачењем вредности из другог фајла на основу подударности вредности из A колоне.
На пример
Фајл А:
Позиција 2A: 11330
Позиција 2L: 147871517

Фајл B:
Позиција 4A: 11330
Позиција 4L: овде ћелим да ми се аутоматски упише 147871517 на основу подударности са вредношћу у пољу 4A.
 
funkcija vlookup.

Preporučujem ti da tu funciju dobro prostudiraš. Meni zaradi pola plate ;)
 
Имам више фајлова који су форматирани на исти начин, а то значи да су у истим колонама исти подаци. Оно што ме интересује је да ли могу да формирам нови фајл, који ће да повуче један део података из другог фајла на основу подударности вредности из једне колоне. Желим да се аутоматски попуни колона L повлачењем вредности из другог фајла на основу подударности вредности из A колоне.
На пример
Фајл А:
Позиција 2A: 11330
Позиција 2L: 147871517

Фајл B:
Позиција 4A: 11330
Позиција 4L: овде ћелим да ми се аутоматски упише 147871517 на основу подударности са вредношћу у пољу 4A.

Mozes to da uradis ovako. Recimo da imas celije popunjene na sledeci nacin:

A B C D E
1 6-- 3
2 7-- 5
3 8-- 7
4 9-- 9
5 10- 2

Tebi treba sledece:

A B C D E
1 6-- 3 8
2 7-- 5 10
3 8-- 7
4 9-- 9
5 10- 2 7


U polje E2 (tu gde gore stoji 8) upisi =IFERROR(VLOOKUP(D2,A:B,2,FALSE),"") i primeni formulu na citavu kolonu E. Dao sam ovo kao primer unutar jednog lista jer je jednostavnije, ali lako u formuli mozes da se pozoves na druge listove ili fajlove. Ako bude bilo muke napisi ovde imena fajlova pa cemo da napravimo formulu koja ti treba. :wave:
 
Poslednja izmena:
Smem li ja da iskoristim ovu temu i postavim pitanje - da ne bih otvarao novu temu:

Imam spisak (xls tabela) koji je "dugacak" nekoliko stotina redova. Informacije za svaki red imaju po 40 kolona (potpuno nebitna informacija, ali hajde da budem koncizan). Spisak treba da podelim na 25 delova na osnovu prvog polja (dakle filtriranjem kolonu A). Ovo je prilicno jednostavna stvar kada se koristi osnovni filter (filtriram podatke i onda radim prost c/p u novi sheet - 25 puta :d), medjutim, meni treba nesto brze/jednostavnije:

Da li postoji mogucnost u excel-u (ili to mozda moze da uradi access) da, umesto koriscenja filtera i cisto mehanickog posla c/p 25* u novi sheet, koriscenjem neke formule...ili kako god....automatizujem ovaj proces - odnosno automatski razdvojim u 25 novih sheetova potrebne podatke koji se filtriraju na osnovu podataka iz prve (A) kolone.

Guglao sam malo, ali sve sto sam nalazio su eksterna resenja u vidu instaliranja kojekakvih plug-inova za excel. Moze li bez toga?
Ukoliko ne, molim za smernicu putem access-a (mislim da access u zivotu nisam pokrenuo) :S:

Hvala
 
Moze to da se uradi ovako:

Otvori svoj dokument pa u DEVELOPER jezicku idi na Visual Basic ikonicu. Posle toga idi na Insert>Module pa ubaci ovaj kod unutra:

Sub Kopiranje()

Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long, iCol As Long
Dim ws As Worksheet, MyArr As Variant, vTitles As String, TitleRow As Long

Application.ScreenUpdating = False


vCol = 1

Set ws = Sheets("Sheet1")


vTitles = "A1:Z1"

TitleRow = Range(vTitles).Cells(1).Row


LR = ws.Cells(ws.Rows.Count, vCol).End(xlUp).Row


iCol = ws.Columns.Count
ws.Cells(1, iCol) = "key"

For Itm = TitleRow + 1 To LR
On Error Resume Next
If ws.Cells(Itm, vCol) <> "" And Application.WorksheetFunction _
.Match(ws.Cells(Itm, vCol), ws.Columns(iCol), 0) = 0 Then
ws.Cells(ws.Rows.Count, iCol).End(xlUp).Offset(1) = ws.Cells(Itm, vCol)
End If
Next Itm

ws.Columns(iCol).Sort Key1:=ws.Cells(2, iCol), Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal


MyArr = Application.WorksheetFunction.Transpose _
(ws.Columns(iCol).SpecialCells(xlCellTypeConstants))


ws.Columns(iCol).Clear


ws.Range(vTitles).AutoFilter

For Itm = 2 To UBound(MyArr)
ws.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=CStr(MyArr(Itm))

If Not Evaluate("=ISREF('" & CStr(MyArr(Itm)) & "'!A1)") Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CStr(MyArr(Itm))
Else
Sheets(CStr(MyArr(Itm))).Move After:=Sheets(Sheets.Count)
Sheets(CStr(MyArr(Itm))).Cells.Clear
End If

ws.Range("A" & TitleRow & ":A" & LR).EntireRow.Copy _
Sheets(CStr(MyArr(Itm))).Range("A1")

ws.Range(vTitles).AutoFilter Field:=vCol
MyCount = MyCount + Sheets(CStr(MyArr(Itm))).Range("A" & Rows.Count) _
.End(xlUp).Row - Range(vTitles).Rows.Count
Sheets(CStr(MyArr(Itm))).Columns.AutoFit
Next Itm


ws.AutoFilterMode = False
ws.Activate
MsgBox "Broj redova sa podacima: " & (LR - TitleRow) & vbLf & "Redova kopirano na druge listove: " _
& MyCount & vbLf

Application.ScreenUpdating = True
End Sub

Evo redova koje ces mozda hteti da promenis:

vCol = 1 - Ovde mu kazes da radi raslojavanje redova po vrednostima celija u prvoj koloni, A. Ako zelis da raslojava po koloni C recimo, kazes mu vCol = 3.

Set ws = Sheets("Sheet1") - Ovde mu kazes kako se zove tvoj glavni list, iz koga raslojavas. Sheet1 je genericko ime pa sam ga zato ostavio.

vTitles = "A1:Z1" - Ovde mu kazes u kom opsegu se nalaze imena tvojih kolona. Posto kazes da ih imas cetrdesetak ovo ces promeniti u nesto kao vTitles = "A1:XXX", gtde je XXX tvoja poslednja kolona sa podacima. Ovaj ce red da bude prekopiran na sve listove. Podaci treba da zapocinju u drugom redu.

Eksel bi trebalo da sve sam rasloji i to tako sto ce ti napraviti po jedan novi list za svaku grupu redova. Makro pokreces klikom na ikonicu Macro pa onda "Kopiranje">Run.

Ovaj makro nisam ja napravio nego sam ga negde pokupio, ni sam ne znam gde. Meni je nekad trebalo nesto slicno, pa ga od tada cuvam. Samo sam ga malo doterao za ono sto tebi treba. :wave:
 
Prijatelju, nisi svestan koliko si mi pomogao!
Da se oduzim? Jedino da mi pustis broj tekuceg racuna na PP - da ti platim bar pivo...ili jedan korektan rucak! ;)

P.S. ozbiljan sam
 
Ma kakav crni tekuci. :D
Vazno je da radi kako treba. Ako se jednom negde sretnemo, popicemo pivce u to ime. ;)

Uzdravlje!
 
hajde da se i ja prikljucim pitanjima jer me ubi vise ovaj excel.
Dakle situacija je sledeca:

imam 4 kolone, od toga treba da pravim korelaciju izmedju 2 i 3, a da mi ispisuje vrednost celije iz kolone 4. Ne mogu da ih sortiram ili filtriram jer se odredjene vrednsoti ponavljaju. Drugi problem je sto je u koloni 2 tri karaktera vise nego u koloni 3. Pokusao sam da skinem ta tri ponavaljajuca karaktera sa komandom right (a1;5), tj da mi ostane poslednjih pet ali mi ne vrsi relaciju izmedju takve celije i celije iz kolone 3.

Vlookup mi koliko sam shvatio ne radi posao. Ili ja tu nesto nisam skontao. Svaka pomoc vise nego dobrodosla
 
Postavi primer file, pa ćemo sve rešiti. Ovako na slepo je teško.

Pozdrav,
 
Tema u stiki!
 
dakle, sto se tice mog problem i kako sam ga veceras uspesno resio:
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),"") je sasvim legitimna formula koja ce odraditi posao.

ALI, treba imati u vidu sledece:

pokusao sam da izbacim tri karaktera iz te kolone dva komandom RIGHT koja ce uraditi ono sto se trazi, ali vrednost celije nece biti broj, i samim tim formula nece uzeti u razmatranje. Ja sam tu kolonu dobijenih vrednosti uradio i copy - paste Special as values u sledecu kolonu.

Sad krece zurka: excel 2007 javlja gresku u svakoj celiji da je vrednost setovana kao tekst. Normalno bi bilo da se sad selektuje cela kolona i uradi Format cells kao number?

E pa nije.

Jer iako ce to uraditi, vrednost u samoj celiji i dalje ostaje tekst! Nego je potrebno selektovati sve celije od poslednje ispunjene do prve u kojoj prijavljuje gresku (znak uzvika) i onda kliknuti na to i COnvert value to numbers. Tek onda formula pokupi te borjeve i odradi muljanje koje treba.

Voleo bih da mi neko kaze ako nesto ja gresim u ovom formatiranju celija, ali ja stvanro ne nadjoh drugo resenje.

Sledeci korak koji treba da preduzmem jeste da uzimam tri kriterijuma za vlookup a za to sam skapirao da je bolje raditi intex ili tako nesto?
 
Postavi excel fajl, na ovaj nacin ti niko ne moze pomoci
 
Kako da napravim command button ili sta vec koji ce klikom na njega da izvrsi precicu ALT + A + S + S to je precica do sortiranja preko custom liste.
 
Snasao sam se.

Application.Dialogs(xlDialogSort).Show
 
dakle, sto se tice mog problem i kako sam ga veceras uspesno resio:
=IFERROR(VLOOKUP(D2,A:B,2,FALSE),"") je sasvim legitimna formula koja ce odraditi posao.

ALI, treba imati u vidu sledece:

pokusao sam da izbacim tri karaktera iz te kolone dva komandom RIGHT koja ce uraditi ono sto se trazi, ali vrednost celije nece biti broj, i samim tim formula nece uzeti u razmatranje. Ja sam tu kolonu dobijenih vrednosti uradio i copy - paste Special as values u sledecu kolonu.

Sad krece zurka: excel 2007 javlja gresku u svakoj celiji da je vrednost setovana kao tekst. Normalno bi bilo da se sad selektuje cela kolona i uradi Format cells kao number?

E pa nije.

Jer iako ce to uraditi, vrednost u samoj celiji i dalje ostaje tekst! Nego je potrebno selektovati sve celije od poslednje ispunjene do prve u kojoj prijavljuje gresku (znak uzvika) i onda kliknuti na to i COnvert value to numbers. Tek onda formula pokupi te borjeve i odradi muljanje koje treba.

Voleo bih da mi neko kaze ako nesto ja gresim u ovom formatiranju celija, ali ja stvanro ne nadjoh drugo resenje.

Sledeci korak koji treba da preduzmem jeste da uzimam tri kriterijuma za vlookup a za to sam skapirao da je bolje raditi intex ili tako nesto?
U VLOOKUP za ref. value u (D2) koristi formulu trim() da broj pretvoriš u text ili value() ako ti treba obrnuto. Sve u zavisnosti šta ti je u prvoj koloni ref. range, u tvom slučaju koloni A.

Sent from my SM-G935F using Tapatalk
 
Pitao bih nesto, .XML datoteke, da li je neko popunjavao poresku prijavu u XML?
 
Coveku ce pasti kamen sa srca sad kad ste mu posle 4 godine resili problem.
 
Imam sledeci problem:
pravim neku tabelu po datumima u mesecu (svaki sheet mi je nov mesec) potro[nje po prodavnicama (poseban dokument je [ta sam kupio u prodavnici) za svaki dan u mesecu.
Problem mi je kada ocu da vidim (do sada sam to rucno gledao i analizirao) koliko sam para ostavio u odredjenoj prodavnici odredjenog dana.

B2: datum
B3: 7.1.2017
B4: 7.1.2017
B5: 7.1.2017
B6: 8.1.2017
C2: prodavnica
C3: Roda
C4: Roda
C5: Maxi
C6: Roda
D2: potroseno
D3: 1500
D4: 500
D5: 1250
D6: 2370

Proba sam SUMIF, ali mi nije dao ono sto trazim posto ne znam da mu stavim da gleda i po datumu
Konkretno u ovom primeru, potrosnja na dan 7.1 u marketu RODA

vlookup verovatno ne znam pravilno da koristim, kao ni IF (mada bi if trebalo u ovom primeru verovatno koristiti kao podfunkciju, ili da datum ide u posebnu kolonu pa if sa sumif da se kombinuje).
za ovo index+match nisam ni cuo

Cisto da ne pomislite da nisam pokusavao, ali znanje mi je limitirano.
 
Ako ti treba samo informativno za konkretan datum, to mozes da vidis i filtriranjem podataka: oznacis sve kolone sa relevantnim podacima, ides na filter, u koloni "prodavnice" odstikliras "select all" i oznacis samo "Roda" i isti princip u koloni "datum". Dobices mini-tabelu, u kojoj zbir dobijas samom selekcijom celija sa iznosima (dole desno u dnu pisace SUM), bez potrebe za funkcijama.
sum_not_appearing_in_status_bar.jpeg
 
Hvala jericho. Nasao sam funkciju (zivela sumifS).
Imam podatke za svaki dan, svaku kupovinu u zadnjih god dana.
 
Opet ja i problem...
Sef odrzavanja drobilica sam. Posto u firmi naravno nemamo nista od tabela, a dobro je da imamo i kompjuter, dosao sam na ideju da pratim potrosnju obloga (tona po satu) i caura (sati rada)

Napravio sam 4 razlicite excel datoteke odakle vucem podatke u jednu u zavisnosti sta mi treba.
Oprema: primarno - 2 drobilice, razlicite starosti i proizvidjaca
sekundarno - 4 drobilice, 2 + 2 sa stanovista proizvodjaca, ali su sve razlicite godine
tercijarno - 6 drobilica, 2+2+2 (3 razlicita proizvodjaca, od svakog po dve drobilice), naravno opet razlicite godine

Sad imam sledeci problem:
u jednom situ sam stavio drobilice i vreme a u drugom sam stavio:
red 1 starost drobilice(A kolona) naziv drobilica (B, C, D, E, F kolone)
red 2 god dana
red 3 2 godine
red 4 5 godina
red 5 10 godina
red 6 +20 godina
ispod b, c, d, e, f kolona sam upisao sati zamena caura

kako da na osnovu pozicije drobilice i godine starosti iz prvog sita, iz drugog izvucem vreme yamene caura?

Sve pokazano u screenshotu
http://prntscr.com/hz2ve0 - podaci o drobilicama - sheet 1
http://prntscr.com/hz2tb2 - caure i sati - sheet 2

Podaci mi trebaju od B2:L32
 
Nije bas pitabnje za eksperte, ali ajd sad, kako da u tabeli dobijem = |=650,00| a da mi se ne potire?
 
Decimale vratis desnim klikom na celije/kolone koje ti trebaju: format cells - (category) number - decimal places: 2
 
Pa ako ne mora biti formatirana celija kao broj, onda samo prebacis format u text i pises sta ti je volja.
 
Nazad
Vrh Dno