Forum » Pomoč in nasveti » Excel 2007: lookup?
Excel 2007: lookup?
motorc ::
V excelu 2007 imam problem z organizacijo podatkov iz podane tabele.
Imam tabelo, ki ima 6 stolpcev in več vrstic. V prvem stolpcu je napisana neka tekstovna vrednost, medtem ko so v ostalih stolpcih (oštevilčimo jih od 1 do 5) oznake "x", "n" ali prazno polje. Spodaj mi sešteva število x-ov in n-jev.
Sedaj bi pa rad naredil seznam, kjer mi za vsak oštevilčeni stolpec (1-5) prikaže vrednosti iz prvega stolpca, vendar samo iz vrstic, kjer je napisan "n".
Problem nastane, ker želim, da mi izpiše vse vrednosti iz vrstic, kjer se nahaja "n".
Za boljšo razlago sem naredil testni excel dokument: primer.xlsx
Poskusil sem že z lookup formulami, index+match in podobno, vendar mi ne uspe. Vsi tutoriali na googlu se narejeni za tabele, ki imajo podatke urejene tako, da "vse paše". Takoj ko je nekaj izven ustaljenih norm, si prepuščen samemu sebi ali forumu. Sam sem že (neuspešno) poskusil rešiti problem, zato se sedaj obračam na vas.
Prosim nekoga, ki zna, če mi lahko svetuje oziroma (še rajši) popravi dokument, da se bom lahko naučil iz konkretnega primera.
Hvala.
Imam tabelo, ki ima 6 stolpcev in več vrstic. V prvem stolpcu je napisana neka tekstovna vrednost, medtem ko so v ostalih stolpcih (oštevilčimo jih od 1 do 5) oznake "x", "n" ali prazno polje. Spodaj mi sešteva število x-ov in n-jev.
Sedaj bi pa rad naredil seznam, kjer mi za vsak oštevilčeni stolpec (1-5) prikaže vrednosti iz prvega stolpca, vendar samo iz vrstic, kjer je napisan "n".
Problem nastane, ker želim, da mi izpiše vse vrednosti iz vrstic, kjer se nahaja "n".
Za boljšo razlago sem naredil testni excel dokument: primer.xlsx
Poskusil sem že z lookup formulami, index+match in podobno, vendar mi ne uspe. Vsi tutoriali na googlu se narejeni za tabele, ki imajo podatke urejene tako, da "vse paše". Takoj ko je nekaj izven ustaljenih norm, si prepuščen samemu sebi ali forumu. Sam sem že (neuspešno) poskusil rešiti problem, zato se sedaj obračam na vas.
Prosim nekoga, ki zna, če mi lahko svetuje oziroma (še rajši) popravi dokument, da se bom lahko naučil iz konkretnega primera.
Hvala.
prtenjam ::
:)
Kar takole čisto preprosto in trivialno pa ne bo šlo. Možnosti imate dve:
1. Ali napišete majhen VBA makro, ki vam takšno tabelico izdela.
2. Ali uporabljate samo funkcije Excla, le da jih bo potrebno uporabiti malce več in uporabiti boste morali več celic. "Problem" je, da vi Excelu ne morete zaukazati, naj poišče drugi ali tretji 'n'... Excel bo vedno našel prvega ==> spremeniti morate območje iskanja.
Rešitev: (nad vašimi podatki v testni datoteki) Prvi 'n' iščete na območju C4 do C25 in ga najdete v C4 ==> naslednji 'n' morate iskati od C5 do C25 in ga najdete v C12, torej naslednjega iščete od C13 dalje.... Torej morate imeti eno kolono, kamor izpisujete zaporedne odmike in potem drugo kolono, kjer iz teh zaporednih odmikov 'izračunate' vrednost v koloni A.
Da vas popeljem na pravo pot vam napišem formule za iskanje 'n' -jev v koloni F. Rezultati pa bodo v celicah od I14 dalje. Začnete z I14, kamor zapišete vrednost, v I15 pa formulo, ki jo kopirate navzdol:
I14: 0
I15: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I14);0);0)
... kopirate navzdol in dobite ...
I16: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I15);0);0)
I17: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I15);0);0)
... in tako dalje...
S tem dobite odmike posameznih 'n'-jev. Najprej od začetka, nato pa eden od drugega... in iz tega lahko preprosto dobite tudi ustrezno črko v koloni B...
Ostalo pa se potrudite sami...
Kar takole čisto preprosto in trivialno pa ne bo šlo. Možnosti imate dve:
1. Ali napišete majhen VBA makro, ki vam takšno tabelico izdela.
2. Ali uporabljate samo funkcije Excla, le da jih bo potrebno uporabiti malce več in uporabiti boste morali več celic. "Problem" je, da vi Excelu ne morete zaukazati, naj poišče drugi ali tretji 'n'... Excel bo vedno našel prvega ==> spremeniti morate območje iskanja.
Rešitev: (nad vašimi podatki v testni datoteki) Prvi 'n' iščete na območju C4 do C25 in ga najdete v C4 ==> naslednji 'n' morate iskati od C5 do C25 in ga najdete v C12, torej naslednjega iščete od C13 dalje.... Torej morate imeti eno kolono, kamor izpisujete zaporedne odmike in potem drugo kolono, kjer iz teh zaporednih odmikov 'izračunate' vrednost v koloni A.
Da vas popeljem na pravo pot vam napišem formule za iskanje 'n' -jev v koloni F. Rezultati pa bodo v celicah od I14 dalje. Začnete z I14, kamor zapišete vrednost, v I15 pa formulo, ki jo kopirate navzdol:
I14: 0
I15: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I14);0);0)
... kopirate navzdol in dobite ...
I16: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I15);0);0)
I17: =MATCH("n";OFFSET($F$4:$F$25;SUM($I$14:I15);0);0)
... in tako dalje...
S tem dobite odmike posameznih 'n'-jev. Najprej od začetka, nato pa eden od drugega... in iz tega lahko preprosto dobite tudi ustrezno črko v koloni B...
Ostalo pa se potrudite sami...
Matjaž Prtenjak
https://mnet.si
https://mnet.si
Zgodovina sprememb…
- spremenil: prtenjam ()
joze67 ::
Varianta 2:
Najprej za vsako celico v stolpcu prešteješ, koliko n-jev je nad njo (countif)
Dobiš stolpce, ki so taki: 0, 0, 0, 1, 1, 2, 2, 2, 2, 3, 4, 4...
Potem napišeš formulo, ki ob vsaki spremembi vrednosti tega stolpca skopira ime vrstice, sicer ohrani prejšnjo
Dobiš stolpce 0, 0, 0, A, A, C, C, E, F, F, F, F, H, J, J...
Na koncu iz tega vlookup-aš 1., 2., 3., ... vrednost.
Če mi bo uspelo pripeti zgled: Rešitev.xlsx
Najprej za vsako celico v stolpcu prešteješ, koliko n-jev je nad njo (countif)
Dobiš stolpce, ki so taki: 0, 0, 0, 1, 1, 2, 2, 2, 2, 3, 4, 4...
Potem napišeš formulo, ki ob vsaki spremembi vrednosti tega stolpca skopira ime vrstice, sicer ohrani prejšnjo
Dobiš stolpce 0, 0, 0, A, A, C, C, E, F, F, F, F, H, J, J...
Na koncu iz tega vlookup-aš 1., 2., 3., ... vrednost.
Če mi bo uspelo pripeti zgled: Rešitev.xlsx
milc ::
Varianta 3: Rešitev.
Ideja je funkcija indirekt in iskanje po premični tabeli, vsak naslednji n se išče od prejšnjega naprej. Tudi v tem primeru se uporablja pomožna tabela.
Veliko lažje bi sestavili rešitev z uporabo funkcije:
=Nth_Occurrence(range_look;find_it;occurrence;offset_row;offset_col)
več.
Ideja je funkcija indirekt in iskanje po premični tabeli, vsak naslednji n se išče od prejšnjega naprej. Tudi v tem primeru se uporablja pomožna tabela.
Veliko lažje bi sestavili rešitev z uporabo funkcije:
=Nth_Occurrence(range_look;find_it;occurrence;offset_row;offset_col)
več.
motorc ::
Hvala vsem za rešitve!
Vse so dobre - katero bom uporabil za končni prikaz sicer še nevem (moram še malo testirati).
Hvala!
@milc: varianta 4 ne dela, če spremeniš vrednosti prvega stolpca (npr. A v AB).
Vse so dobre - katero bom uporabil za končni prikaz sicer še nevem (moram še malo testirati).
Hvala!
@milc: varianta 4 ne dela, če spremeniš vrednosti prvega stolpca (npr. A v AB).
milc ::
RE: @milc: varianta 4 ne dela, če spremeniš vrednosti prvega stolpca (npr. A v AB).
Varianta ne dela, ker formule ne predvidevajo, da bi se te vrednosti lahko spreminjale. V kolikor bi to bila potreba, bi bilo potrebno formule zasnovati drugače, predvsem pa na začetku posta definirati problem kot tak.
Formula uporablja FIND, kjer za iskano rešitev uporabi vrednost predhodne rešitve. S find najdemo vrstico, ... recimo A, kjer se je pojavil zadnji 'n'. Če bi hotel torej spemenit formule, da bi omogočale poljubno poimenovanje polj (A, B... z recimo Miha, Mojca, Andrej, ...) potem bi moral FIND zamenjati z nečem, kar ti bo vrnilo lokacijo (vrstico) v kateri se nahaja rešitev, recimo MATCH. Tu nastane več manjših izzivov. Ponavljanje, pa sortiranje...
Tu si pa skoraj na meji med tem, da se pokaže, da je precej bolj ekonomično napisati svojo lastno VBA funkcijo, ki ti išče kar rabiš.
Varianta ne dela, ker formule ne predvidevajo, da bi se te vrednosti lahko spreminjale. V kolikor bi to bila potreba, bi bilo potrebno formule zasnovati drugače, predvsem pa na začetku posta definirati problem kot tak.
Formula uporablja FIND, kjer za iskano rešitev uporabi vrednost predhodne rešitve. S find najdemo vrstico, ... recimo A, kjer se je pojavil zadnji 'n'. Če bi hotel torej spemenit formule, da bi omogočale poljubno poimenovanje polj (A, B... z recimo Miha, Mojca, Andrej, ...) potem bi moral FIND zamenjati z nečem, kar ti bo vrnilo lokacijo (vrstico) v kateri se nahaja rešitev, recimo MATCH. Tu nastane več manjših izzivov. Ponavljanje, pa sortiranje...
Tu si pa skoraj na meji med tem, da se pokaže, da je precej bolj ekonomično napisati svojo lastno VBA funkcijo, ki ti išče kar rabiš.
Vredno ogleda ...
| Tema | Ogledi | Zadnje sporočilo | |
|---|---|---|---|
| Tema | Ogledi | Zadnje sporočilo | |
| » | pomoč excel (strani: 1 2 )Oddelek: Programska oprema | 7989 (3566) | -JM- |
| » | Excel iskanje podvojenih vnosovOddelek: Pomoč in nasveti | 6593 (5219) | smetko |
| » | excelOddelek: Programska oprema | 4788 (3397) | xtrat |
| » | EXCEL- kopiranje funkcijeOddelek: Programska oprema | 1264 (1054) | Newholland |
| » | Excel: stolpce v vrstice ?Oddelek: Programska oprema | 5183 (4811) | _jackson_ |