» »

excell razvrščanje

excell razvrščanje

rferrari ::

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č.

h1427096 ::

data -> sort
Izbereš naj sortira po vrednosti padajoče

rferrari ::

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

prtenjam ::

rferrari je izjavil:


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.

To ne pije vode. Če vi vpišete ime ste ga s tem že določili, kaj vam lahko Excel izpisuje padajoče?? Mogoče edino, če pričakujete, da go boste v D4 vpisali robi bi zraven zapisal njegovo največjo vrednost. Ko drugič zapišete robi želite ob njem njegovo drugo največjo vrednost... Nikakor ne vidim logike??? Sortirajte in to je to?

rferrari je izjavil:


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.

Tu pa ste me izgubili :(
Matjaž Prtenjak
https://mnet.si

h1427096 ::

č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.

rferrari ::

Ja, to bo to.

Vlookup funkcija najde vrednost, vedno prvo in jo ponavlja pri naslednjih celicah.
Rad bi pa, da mi številke ostanejo pri imenih.
V novem stolpcu bi si morale celice slediti (že urejene):
franci 800
tone 400
janez 200
marko 100
robi 100
Kaj bi moral vpisati v celico oz. celice, da dobim to "tabelco"? Z makri pa sem .... Kako naj jih sploh začnem uporabljati ali se učiti o njihovi uporabi?

hvala

ZePe ::

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.
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

rferrari ::

ZePe hval za tako razlago, ampak to ni moja težava.
zdenka17 :: danes, 09:18:57 je opisala in razume, kaj sem spraševal. Pri meni morajo imena imeti iste vrednosti, le da so v naslednjem stolpcu te vrednosti zapisane padajoče in k njim pripisana imena. Imena morajo imeti vsako svojo vrednost. Težava je, ker lookup funkcija poišče prvo vrednost, ki je enaka in njej določi imena enakih vrednosti. Temu se hočem izogniti.
Kako naj prilepim sliko sem, da boste bolje razumeli mojo težavo?

ZePe ::

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.

rferrari ::

OHO, ZePe!
To pa je že nekaj. Preprosta stvar je za takšne, ki znate razmišljat s svojo glavo. Ostali pa pač prosimo.
No, 1 =SMALL($B$1:$B$4;ROW(1:1)) tu bi verjetno moralo pisat $B$5, ker drugače ne vpiše zadnje vrednosti.
Ko pa vpišem v D1 formulo z INDEX, ne dobim pravilne razvrstitve ampak:
franci
#ŠTEV!
franci
marko
robi
A so tudi tukaj katere številke mal zamenjane?

Kje imaš ZS, da ti pošljem odškodnino za tvojo pomoč?
hvala

ZePe ::

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

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
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

rferrari ::

Zdravo

Lepo.
Kar si napisal, sem prebral, sem premislil, sedaj bom to pretopil v mojo tabelo.
Še se slišiva. HVALA.

rferrari ::

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.

ZePe ::

Ne vem, če pravilno razumem. Formula v H2 mora kot rezultat izpisati nekaj v smislu $A$1:$A$5, oziroma $A$1:$A$n, pri čemer je n število celic v stolpcu A, ki niso prazne - torej izpolnjenih vrstic. Število celic, ki vsebujejo besedilo vrne funkcija COUNTA(A:A), ki smo jo postavili v H1.

Enako velja za H3. Le da ta pač sestavi niz za stolpec B, torej nekaj v smislu $B$1:$B$n, pri čemer je n enak, kakor v zgornjem primeru.

Funkcija INDIRECT iz besedila izlušči, kateri obseg naj bi to bil. Torej če napišemo v neko celico =A1, je to enako, kot če bi napisali =INDIRECT("A1"). V našem primeru torej INDIRECT(H2) pomeni nekaj v smislu INDIRECT("$A$1:$A$n"), pri čemer n zavzema konkretno vrednost, ki ga vrne funkcija COUNTA v celici H1.

Opozorilo: upoštevaj uporabo narekovajev. Napisani so z razlogom.

Funkcija INDIRECT nam torej vrne obseg podatkov, nad katerim se izvede operacija. INDIRECT(H2) torej pomeni: V stolpcu A, vendar samo toliko vrstic, kolikor je dejanskih vnosov v stolpcu A.

Ko pokličemo funkcijo SMALL(INDIRECT($H$2);ROW(1:1)) pa to pomeni prvi (1.), torej najmanjši člen iz tega stolpca. Ko formulo potegnemo navzdol, se številke spreminjajo, tako je v stoti vrstici formula dejansko SMALL(INDIRECT($H$2);ROW(99:99)), kar pomeni 99.-i člen iz obsega, opisanega v H2.

Zaporedje torej tu nima vpliva, saj mi sproti luščimo posamezne člene verige, oz. ustvarjamo zaporedje...

Prosim za bolj natančno opredelitev problema. Meni zadeva deluje na primeru cca. 34 tisoč vrstic (letna meritev temperature za leto 2010 z lokacijo meritve na eno uro) bres težav.
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

rferrari ::

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č.

ZePe ::

Zdravo.

Glede literature ti težko svetujem. Za predavanja, ki jih izvajam, sem sestavil lastno skripto, oz. več njih, če smo natančni, so pa zgolj variacije usmerjene v posamezno tematiko.

Glede VBA-ja in makrojev pa je stvar takšna, da je treba najprej poznati Visual Basic, nato pa je zadeva dokaj preprosta, saj je Office Avtomatizacija dokaj dobro podprta z literaturo in tudi na spletu je mogoče najti dosti dobrih primerov.

Eden pionirjev Office avtomatizacije, kjer je mogoče najti precej luštnih rešitev in je v slovenskem jeziku je Matjaž, tudi sam na mojem blogu se lotevam podobne tematike, a z drugega zornega kota (problem - rešitev).

Lahko pa se kadarkoli obrneš tudi name, saj rad pomagam, če mi le čas dopušča.

Lep dan želim,
P.J.
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

rferrari ::

Lep norčav pozdrav.
Preizkusil sem tvoje predloge, oz. vpisal sem formulo v celico in deluje. Vnesel sem tudi Large namesto Small. Tudi gre. Hvala.
Me pa nekaj zanima in nevem kje grešim.
Če ne začnem v prvi vratica, torej row1, ne dobim pravilnih rezultatov. Ne znam definirati ukaza ROW. Kadar pa sem začel v A1, B1 ali katerem drugem stolpcu samo, da je bila prva vrstica, pa je šlo. Kaj moram nastaviti, če bi imel na delovnem listu matriko (zapisane podatke) nekje stran od robov.
Dinamični obsegi podatkov pa še ne gredo.
Če vpišem "$A$1:$A$" se to zapiše kot besedilo. Tukaj obstanem.
No, do naslednjič, ker sem trenutno malce več zadržan z drugim delom in družino in z ženinim praznikom, bom kaj več naštudiral pa se oglasim.
LP.

ZePe ::

rferrari je izjavil:


Če vpišem "$A$1:$A$" se to zapiše kot besedilo. Tukaj obstanem.


To je popolnoma pravilno. In ko zapišeš
="$A$1:$A$" & $H$1

bi to prevedel v človeški jezik nekako tako: Dobesedno navedenemu besedilu prilepi na konec vrednost, ki je v celici H1.

Ker funkcija INDIRECT iz besedila poskusi izluščiti sklic na obseg podatkov. V našem primeru je v H1 funkcija COUNTA(A:A), ki prevedeno pomeni Preštej vse celice, v katerih je kaj napisanega v stolpcu A. Če je torej v 100 vrsticah v stolpcu A nekaj napisano, bo celica H1 prikazovala vrednost 100, v celici H2 bo pa besedilo $A$1:$A$100.

In funkcija INDIRECT("$A$1:$A$100") - no v resnici je INDIRECT($H$2) in operira nad besedilom, ki pač je v H2 - bo vrnila $A$1:$A$100 v smislu obsega podatkov. Upam da je sedaj bolj jasno o čem govorim. In če je... potem se lahko vrneva k prvem delu tvojega komentarja... Odgovor je tukaj preprost:

Kot vidiš, sem definiral dinamični obseg od A1 do An in samo n izračunavam. Poanta je v A1. Če želiš, da začne v tretji (3) vrstici, pač deklariraj:
="$A$3:$A$" & $H$1

in je stvar rešena, kajne?

;)
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

Zgodovina sprememb…

  • spremenil: ZePe ()

rferrari ::

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

ZePe ::

Kaj pomeni $?

Če napišemo formulo
=A1
v celico B1, se bo ob kopiranju FORMULE iz B1 v C1 se bo v slednji formula glasila:
=B1

če pa formulo iz B1 kopiramo v B2 se bo glasila:
=A2


Excel torej pri kopiranju FORMUL sam skrbi za to, da so sklici RELATIVNI. Včasih pa želimo, da so sklici ABSOLUTNI, torej da Excel pri kopiranju formule obdrži logiko, ki jo mi določimo. Tu je možnih več kombinacij. Če npr. b B1 formulo zapišemo:
=$A$1

bo po kopiranju formule horizontalno ali vertikalno Excel obdržal absolutni sklic. Ob kopiranju FORMULE iz B1 v C1 se bo sedaj v slednji formula glasila:
=$A$1

in če formulo iz B1 kopiramo v B2 se bo tudi glasila:
=$A$1


Lahko pa fiksiramo tudi samo stolpec ali samo vrstico. Če je torej naša formula vb B1 napisana tako:
=$A1

bo po kopiranju formule horizontalno Excel obdržal absolutni sklic. Ob kopiranju FORMULE iz B1 v C1 se bo sedaj v slednji formula glasila:
=$A1

in če formulo iz B1 kopiramo v B2 se bo tudi glasila:
=$A2


Velja tudi obratno... Če je torej naša formula vb B1 napisana tako:
=A$1

bo po kopiranju formule horizontalno Excel obdržal absolutni sklic. Ob kopiranju FORMULE iz B1 v C1 se bo sedaj v slednji formula glasila:
=B$1

in če formulo iz B1 kopiramo v B2 se bo tudi glasila:
=A$1


Upam da je ta del razjasnjen. ;)
Moj blog: http://office.blog.siol.net in http://www.finance.si/blog.php?id=25

prtenjam ::

Še razlaga s sličicami ...

PS: (ZePe) Opažam, da imava zelo podoben način razlaganja stvari :) Verjetno so kriva leta
Matjaž Prtenjak
https://mnet.si

rferrari ::

Pozdravljeni.
Preizkusil sem, pravilno vpisal formule in ..... deluje.
Ja. Res, če dodajam v stolpec ime in poleg vrednost, se v D in E stolpcih urejajo vrednosti, kot morajo. Razumem, kaj so sklici na celice, moral pa sem si pojasniti ukaz INDIRECT. Kolker sem si pač lahko.
Sedaj pa imam vprašanja:
1. Najprej vnesem vrednosti v prva stolpca in v naslednjih stolpcih dobim urejeno, kot v dosedanjem primeru. Ko pa želim vrstice premakniti dol, da bi nad stolpce vpisal njihova imena (Naziv, Vrednost, ...), se mi v rezultatih pojavijo napake. Nakje imam narobe definirano, od kod naj začne brati podatke, kljub vnosu praznih vrstic pred prvim zapisom. Sem razumljiv? Bolj pišem, bolj kompl.....
2. Ko sem vpisal $D$1:D1;D1, se mi je ta del $D$1:D1 ob pritisku na : spremenil v D1:D1. Ha? Vsakič sem moral tipko po tipko vpisati ta ukaz.

LP ZePe in tudi tebi prtenjam!
Hvala.


Vredno ogleda ...

TemaSporočilaOglediZadnje sporočilo
TemaSporočilaOglediZadnje sporočilo
»

pomoč excel

Oddelek: Pomoč in nasveti
382178 (902) sax8er
»

EXCEL vgnezdenje dveh vlookup funkcij?

Oddelek: Programska oprema
10979 (678) prtenjam
»

Excel Težava - fukncija

Oddelek: Pomoč in nasveti
201621 (1314) smetko
»

excell formule

Oddelek: Programska oprema
181353 (1055) ZePe
»

Povprecja v Excelu

Oddelek: Programska oprema
161592 (1137) prtenjam

Več podobnih tem