excell razvrščanje

Zdravo.
Kako naj uredim tabelo?
IME Vrednost
robi 100
tone 400
marko 100
franci 800
janez 200
Rad bi imel urejena imena oseb s pripadajočo vrednostjo od največje do najmanjše. Kaj naj napišem v celico, da se tam izpiše ime? Če uporabim VLOOKUP, mi vrne dvakrat robi 100. Pisat bi pa moralo robi 100 in za njim marko 100. Uh.
Lepo prosim za pomoč.

Ja. Že.
Ampak. Takole je :
Venem stolpcu so imena, v drugem so vrednosti. V tretji stolpec ali na drug list bom razvrščal podatke. Sprašujem se: Katero ime ima največjo vrednost, katero ime ima drugo majvečjo vrednost, itn.. Ko vpišem ime, naj bi se v trtjem stolpcu že samo izpisovalo padajoče.
Pa še ena težavaje. Tam sta različni imeni z enakima vrednostima. Ko ju začnem razvrščati z ukazoma LARGE ali VLOOKUP, je vse prav dokler, ne pridem do teh dveh imen. Takrat pa mi izpiše prvo enako ime in v naslednjo celico spodaj ponovi enako ime. Tu bi moralo pisati drugo ime.

LP

če prav razumem želi imeti originalno tabelo nedotaknjeno, hkrati pa še sortirano verzijo na nekem drugem mestu v zvezku? Ker je originalna tabela živa, mora sortiranje potekati avtomatsko.

Rešuje pa stvar tako, da je naredil novo tabelo, kjer so v prvem stolpcu large funkcije - v n-ti vrstici n-ta vrednost, v drugem stolpcu pa z vlookupom išče imena za te vrednosti, vendar pri enakih vrednostih vedno dobi isto ime.

Verjetno brez makrota ne bo šlo.

Mislim, da je težava že pri opisu težave in posledično pri razumevanju bistva problema. Imamo torej eno tabelo v kateri prikazujemo kaj?

Morda nekaj takega?

Naziv     Vrednost
Janez          100
Metka          200
Tone           150
Metka          125
Tone           165
Janez          150

Rad bi ba dobil nekaj takega:

Naziv     Vrednost
Janez          100
Janez          150
               ___
               250
Metka          125
Metka          200
               ___
               325
Tone           150
Tone           165
               ___
               315

Za to ne potrebujemo makrov, pač pa uporabimo vrtilno tabelo. Gremo torej na kartico "Vstavljanje" in izberemo "Vrtilna tabela". V oknence vpišemo ime izvorne tabele (vhodne podatke označimo in na kartici osnovno izberemo "Oblikuj kot tabelo"), npr. "Seznam_Vrednosti".

Nato potegnemo polja v ustrezna okenca nastavitev vrtilne tabele, v našem primeru v okence "Vrstice" potegnemo "Naziv" v okence "Vrednosti" pa Vrednost in - voila. Problem rešen.

Ne si greniti življenja s preprostimi rečmi, ni nobene potrebe po tem.

LP, P.J.

Kaj takega?

  Imamo          Želimo
    A       B  C  D     E
  ------------   ------------
  Naziv   | N     N | Naziv
  ------------   ------------
1 franci   800   100  Marko
2 tone     400   100  Robi
3 janez    200   200  Janez
4 marko    100   400  Tone
5 robi     100   800  Franci

Torej ... v za stolpec "N" v tabeli "Želimo" v prvo vrstico vnesemo formulo:

=SMALL($B$1:$B$4;ROW(1:1))

in jo skopiramo navzdol. S tem dobimo ustrezno razvrstitev. Sedaj pride tisti napornejši del. V prvo vrstico za stolpec "Naziv" v tabeli "Želimo" vpišemo formulo:

{=INDEX($A$1:$A$5;SMALL(IF($B$1:$B$5=D1;ROW($B$1:$B$5));COUNTIF($D$1:D1;D1)))}

pri čemer ne pišemo teh zavitih oklepajev, ki sem jihz zapisal, pač pa ko v VNOSNI VRSTICI opravimo, potrdimo s [Ctrl]+[Enter] in ne samo z [Enter], kot je to v navadi - Excel bo zavite oklepaje naredil sam in tako označil formulo kot matrično.

Naj še razložim, kaj sem s tem dosegel:

• Najprej primerjam vrednost celic v rangu $B$1:$B$5 z vrednostjostolpca D v trenutni vrstici in s pomočjo funkcije SMALL poiščem N-to najmanjše pojavljanje te vrednosti, pri čemer mi N-to pojavljanje izračuna funkcija COUNTIF.


• Ko sem tako dobil številko vrstice, v kateri se nahaja N-to pojavljanje vrednosti v stolpcu D, potem v stolpcu A poiščem s funkcijo INDEX ustrezno vrednost.

Skratka, stvar je dokaj preprosta, le nekaj težav smo imeli z razumevanjem problema.

Lep pozdrav,
P.J.

Ti misliš, da sva že fertik?!? Kje pa, ... šele začela sva, dragi moj.

Ja, ob pol drugi zjutraj človeku res uide kaka "brihtna", tako da imaš kar prav v svojih ugotovitvah, pa vendar za samo končno rešitev to niti ni bog-ve-kako-pomembno. Ti takoj povem, zakaj je temu tako.

Dinamični obsegi podatkov
Ker je bilo izpostavljeno, da se tabela "Imamo" ves čas dopolnjuje, pridemo do problema, da v resnici nikoli ne vemo, kako velika je. Seveda si ne želimo vsakič znova popravljati formul v tabeli "Želimo". Kako izvemo, kako velika je tabela "Imamo"? Dokaj enostavno. Bom delal po korakih zaradi boljšega razumevanja:

1.) Koliko vrstic je v tabeli "Imamo"?
V celico G1 vpišimo "Vrstice:" v celico H1 pa formulo

=COUNTA(A:A)

2.) Kolikšen je obseg podatkov v tabeli "Imamo"?
V celico G2 vpišemo "Obseg A:" in v celico H2 vpišemo formulo

="$A$1:$A$" & H1

V celico G3 vpišemo "Obseg B:" in v celico H3 vpišemo formulo

="$B$1:$B$" & H1

Če sedaj dodamo v tabeli "Imamo" novo vrstico, vidimo, da se nam pravilno preračunava število vrstic in posledično tudi besedilo v celicah H2 in H3, ki predstavljata v bistvu "sklic"

3.) Kako sedaj ta sklic na stolpec uporabimo?
V celici D1 popravimo formulo, tako da povemo, da želimo območje obsega podatkov prebrati iz celice, torej

=SMALL(INDIRECT($H$3);ROW(1:1))

in to formulo razmnožimo po stolpcu navzdol.

4.) Kaj pa iskanje cilja?!?
Tukaj tudi zamenjamo "trde sklice" s preračunanimi in sicer tako, da našo formulo popravimo na sledeč način:

{=INDEX(INDIRECT($H$2);SMALL(IF(INDIRECT($H$3)=D1;ROW(INDIRECT($H$3)));COUNTIF($D$1:D1;D1)))}

OPOZORILO: Nikar ne spreglejte, da je to matrina formula, torej potrdite s kombinacijo tipk [CTRL]+[SHIFT]+[ENTER] (hkrati seveda). Ko vam bo uspelo, se bodo v formuli samodejno pojavili "zaviti oklepaji", ki jih ne vnašamo na roko, pač pa jih Excel naredi sam od sebe in pomenijo to, da gre za matrično formulo.

Ko končamo, zaključimo tako, da še to formulo razmnožimo po stolpcu navzdol.

Avtomatizacija
Takšno kopiranje formul navzdol zna biti v primeru nekaj deset tisoč vrstic "pain in the ass", zato si za ta namen posnamemo makro. Če želite, vam lahko napišem postopek.

Lahko pa organiziram tudi tečaj napredne rabe Excela v vašem podjetju, če imate interes.
Lep pozdrav,
Željko Petrušić
Microsoft Certified Trainer
---------------------------
Visual Basic Enterprise Developer
MS SQL Server DBA
MS Office Certified Professional

Ja, deluje, toda delno.
Pri dinamičnem obsegu podatkov:
Če vpišem formulo v H2, mi tam združi zapisa iz stolpca A in celico G1. Enaka formula v H3. Vsolpcu B so vrednosti različne od H2 ali H3, zato jih ne more primerjat. Vrnjeno dobim #ŠTEV!
Včeraj mi ni delovalo, ker si prvič zapisal naj pritisnem ctrl+enter, no danes si popravil na ctrl+shift+enter. Tako gre.

Druga stvar.
Pri dinamičnem vnosu, torej ko se število celic povečuje, in njihove pripadajoče vrednosti, ni rečeno da so vse te vrednosti že v zaporedju. Drugače povedano, v stolpcu B nimam padajoče zapisane vrednosti po vrst, ampak so različnih velikosti, naključno. Primer: B1=800, B2=500, B3=900, B4=200, B5=700 in tu se pojavi težava. Ker niso te vrednosti padajoče ali naraščajoče urejene, v stolpcu E ne velja formula z INDEX.
Torej:
Kako bo to delovalo, če pri vsakem vnosu novega imena z raličnio vrednostjo, dobim še vedno v stolpcih D in E urejene pripadajoče podatke?

Hvala.

OK.
Bom še enkrat preveril, uredil.
Hvala.
Lep vikend! Se slišiva naslednji teden.

P.S.: S katero literaturo si lahko pomagam o Excelu, take bolj zahtevnejše, kot je moj primer? Kje si preberem o pisanju makrov.
Namreč, sem samouk in se učim ravno na takšnih primerih. Zanima me, pač.

Zdravo.
Verjetno sva oba spregledala zapis 4. mar 2011, 17:53:34 2.) Kolikšen je obseg podatkov v tabeli "Imamo"? V celico G2 vpišemo "Obseg A:" in v celico H2 vpišemo formulo 1 ="$A$1:$A$" & H1 V celico G3 vpišemo "Obseg B:" in v celico H3 vpišemo formulo 1 ="$B$1:$B$" & H1 ,
kjer bi po današnji razlagi moralo pisati $H$1?
Preizkusim in se oglasim.
Hja, super, vsak dan nekaj novega.
LP

Še razlaga s sličicami ...

PS: (ZePe) Opažam, da imava zelo podoben način razlaganja stvari :) Verjetno so kriva leta