Edinstvene vrednosti v Excelu III

http://www.matjazev.net/blog/2012/10/16/edinstvene-vrednosti-v-excelu-ii/ kako prešteti edinstvene vrednosti znotraj nekega Excelovega območja in prišli do spoznanja, da omenjena formula deluje samo nad številskimi podatki.

V kolikor pa se med vrednostmi nahajajo tudi nizi znakov, datumi ali kaj tretjega pa opisana metoda odpove.

Poiskati je torej potrebno način, ki bo preštel različne vrednosti neglede na njihovo vsebino. Iščemo torej bolj splošno formulo, ki jo bomo lahko uporabili vedno.

Seveda se sprašujete zakaj nisem te formule zapisal že takoj na začetku saj bo očitno delovala nad vsemi tipi podatkov in je zatorej boljša kot tista, ki sem vam jo predstavil zadnji. Odgovor je seveda v zahtevnosti.

Današnja formula je celo krajša in lepša od FREQUENCY, a je težje razumljiva oz. vem priporočam, da se poglobite v razumevanje zakaj deluje oz. zakaj vrača pravilen rezultat.

Uporabljene formule

Za končni rezultat bomo potrebovali dve vgrajeni Excelovi funkciji SUMPRODUCT in COUNTIF. Funkcijo COUNTIF sem v blogu že opisoval, SUMPRODUCT pa še nisem omenjal.

COUNTIF funkcije preprosto prešteje število celic, ki ustrezajo nekemu pogoju, funkcija SUMPRODUCT pa je bolj podobna mrežnim funkcijam in medsebojno sešteva medsebojne rezultate.

IDEJA REŠITVE

Za vsako celico bomo ugotovili kolikokrat se pojavi na celotnem območju (temu bo služila funkcija COUNTIF).

Sedaj je potrebno te podatke normalizirati, kar pomeni, da če se neka vrednost pojavi N krat, jo bomo mi upoštevali samo 1/N krat… Z drugimi besedami – vse rezultate je potrebno potencirati na -1 oz. 1 deliti z njimi:

Kot vidite lahko normalizirane vrednosti preprosto seštejemo (zato smo jih normalizirali!) dobimo iskani rezultat – število edinstvenih vrednosti ne nekem območju.

In če sedaj opisano idejo zapišemo z eno formulo dobimo sledeč rezultat:

=SUMPRODUCT((A3:A15<>"")/COUNTIF(A3:A15;A3:A15&""))

In to je splošna formula štetja edinstvenih vrednosti v Excelu!

Edinstvene vrednosti v Excelu II

Zadnjič sem prikazal uporabo funkcije FREQUENCY, saj jo bomo potrebovali za štetje edinstvenih vrednosti.

Imamo torej funkcijo, ki nam zna povedati, koliko števil pade v neko območje. Kako lahko to uporabimo za štetje ednistvenih vrednost?

Najprej pomislimo, kaj dobimo, če funkciji FREQUENCY posredujemo isti niz števil v obeh primerih… V tem primeru dobimo za vsako število zapisano kolikokrat se pojavi; toda če se isto število pojavi večkrat, funkcija FREQUENCY za vse ponovitve vrne vrednot 0.

Slednje je zelo pomembno, saj je to ključ naše rešitve, katere ideja je preprosta: »Seštej vse števila, ki jih vrne funkcija FREQUENCY in pri tem vsako število, ki je več kot 1, preprosto upoštevaj kot 1«

Rešitev:

=SUM(IF(FREQUENCY(A1:A100;A1:A100)>0;1))

In to je vse! Ta funkcija prešteje vse edinstvene vrednosti na območju A1 do A100. Ne pozabite pa, da je to matrična funkcija, kar pomeni, da jo morate potrditi s kombinacijo CTRL+SHIFT+ENTER.

Ima pa omenjena funkcija oz. omenjena rešitev manjšo pomanjklivost. Deluje namreč samo in izključno nad številkami. Če želimo prešteti vse edinstvene vrednosti, ki vsebujejo besedilo (recimo edinstvena imena), pa nam omenjena funkcija ne bo mogla prav nič pomagati.

V tem primeru bo potrebno poseči po močnejšem orožju, ki ga bom prikazal naslednjič.

Edinstvene vrednosti v Excelu I – funkcija FREQUENCY

V enem izmed prejšnjih prispevkov sem prikazal, kaj je potrebno storiti, da bi lahko v vrtilnih tabelah šteli edinstvene vrednosti in to je splošna rešitev problema edinstvenih vrednosti a njena pomanjklivost je v dejstvu, da potrebujemo dodatno kolono, ki označuje edinstvene vrednosti.

Včasih pa se soočimo s problemom, ko želimo preprosto ugotoviti koliko je edinstvenih vrednosti na nekem območju in potrebujemo funkcijo, ki bi nam to znala povedati.

A preden se spustimo v iskanje te rešitve, si oglejmo uporabo vgrajene funkcije FREQUENCY. Funkcija je specifična, saj vrača več vrednosti in je torej matrična funkcija!

Njen namen je razporediti vrednosti iz nekega območja v posamezna območja. Recimo ljudi v starostna območja ali artikle v cenovne razrede in podobno.

Primer uporabe funkcije FREQUENCY

Zamislimo si seznam velikosti otrok, ki jih želimo razporediti v velikostne razrede. Ugotoviti torej želimo, koliko otrok je manjših od 140cm, koliko jih je med 140cm in 150cm, 150cm in 160cm…

Imamo torej tabelo, kot je prikazana na levi strani, želimo pa rezultat, kot je prikazan na desni strani, kjer vidimo točno koliko otrok spada v kateri velikosti razred.

Postopek:

  1. Označimo celice E2:E7
  2. Vpišemo formulo
    =FREQUENCY(B2:B18;D2:D7)
  3. Pritisnemo kombinacijo SHIFT+CTRL+ENTER (ne pozabite to je mrežna funkcija!)
  4. S tem smo rezultat funkcije dobili v 6 celic hkrati.

Pomen prvega parametra je torej jasen – to je seznam vrednosti, ki jih želimo razporediti.

Pomen drugega parametra pa mogoče ni tako samoumeven. V drugem parametru namreč postavljamo gornje meje razredov in Excel te meje vedno sortira v naraščujočem vrstnem redu ter potem vrednosti druži v tako pridobljene razrede. V celico, ki ima prazen razred (v našem primeru celica E7) pa vstavi število, ki presega gornjo zapisno mejo.

V našem primeru smo torej otroke razporedili v razrede (0–130, 131–140, 141–150, 151–160, 161–170, večji). Seveda ni nujno, da so razredi enako veliki – Excelu je popolnoma vseeno.

Če bi torej napisali sledeče številke (122, 130, 145, 155, 170), bi Excel izračunal število otrok v velikostnih razredih (0–122, 123–130, 131–145, 146–155, 156–170, večji).

Naslednjič pa bom pokazal kako nam lahko ta funkcija pomaga pri štetju edinstvenih vrednosti!

Loto in naključna števila

Zadnič se je na forumu pojavilo zanimivo vprašanje: »Recimo da rabim “spisek” števil od 1 do 1000. Znotraj tega pa bi rad, da so števila v blokih po sto (1..100, 101…200, 201..300) z naključnim vrstnim redom.«

In ker te dni v Sloveniji potka nora gonija za loto dobitkom, je izziv kot vzet iz omenjenega konteksta.

Kako torej izbrati naključna števila za loto napoved?

Continue reading Loto in naključna števila

Pogojno oblikovanje celic glede na preostale celice

Zadnjič se je na forumu pojavilo zanimivo vprašanje, s katerim se trudi mnogo uporabnikov. Namreč pogojno oblikovati neko celico, ki ima fiksno vrednost je dokaj preprosto, kaj pa če želimo v v neki tabeli obarvati rdeče celico z najnižjo vrednostjo?

Najnižje vrednosti v tabeli ni težko poiskati:

=MIN([področje tabele])

Če pa hočemo obarvati celico, ki ima to vrednost, pa je potrebno v okno pogojnega oblikovanja pač vpisati formulo, ki pravi:

Če je vrednot trenutne celice enaka minimalni vrednosti celotne tabele, potem jo obarvaj.

Če bi šli vpisovat pogojno oblikovanje v vsako celico tabele bi bilo to seveda skrajno zamudno in tudi dokaj nesmiselno. Kaj pa če se pogoj malce spremeni ali bomo šli zopet popravljat 5000 celic? Seveda ne!

Pogoj za obarvanje zatorej določimo za celotno tabelo naenkrat, pri tem pa se moramo zavedati, da bo Excel formulo razumel natanko tako, kot razume vse formule, ki jih vnašamo na večje območje celic hkrati.

Naša formula se bo vedno sklicevala na zgornjo desno celico tabele, Excel pa jo bo ustrezno priredil za vse ostale celice na celotnem področju.

Iskanje najmanjše celice

Predpostavimo torej da se naša tabela nahaja na območju F10:P42 in da želimo z rdečo obarvati celico, ki je na tem območju najmanjša:

  1. Označimo celotno področje
  2. V Excelu 2003 izberemo opcijo Oblika / Pogojno oblikovanje … in odpre se novo okno, v katerem:
    • na desni strani izberemo opcijo ‘Formula je’
    • vpišemo formulo: =F10=MIN($F$10:$P$42)
    • in izberemo rdečo podlogo
  3. V Excelu 2007/2010 pa izberemo opcijo Pogojno oblikovanje, ki se nahaja na traku Osnovno.
    • Odpre se nov podmeni, na katerem izberemo opcijo Novo pravilo
    • Odpre se novo okno v katerem izberemo opcijo Uporabi formulo za določanje celic za obilovanje
    • Spodaj vpišemo formulo: =F10=MIN($F$10:$P$42)
    • in izberemo rdečo podlogo

V vsakem primeru se nam v tabeli RDEČE obarva celica, ki nosi najmanjšo vrednost.

Kako deluje ta formula?

V pogojno oblikovanje smo vpisali formulo:

=F10=MIN($F$10:$P$42)

In s to formulo smo Excelu zaukazali: “V kolikor je vrednost celice F10 enaka minimalni vrednosti v celotni tabeli MIN($F$10:$P$42), potem celico obarvaj!”.

Ob tem vas še enkrat opozarjam, da smo v formulo vpisali celico F10, saj je to zgornja desna celica našega območja in Excel jo bo ustrezno zamenjal v vsaki celici. V celici K27 bo Excel zatorej videl sledečo formulo:

=F27=MIN($F$10:$P$42)

V funkcijo MIN pa smo vnesli absolutni naslov, saj ne želimo, da bi Excel spreminjal območje. Več o absolutnem ~ relativnem naslavljanju si lahko preberete med nasveti.

Še par zanimivih možnosti

maksimalna vredost

=F10=MAX($F$10:$P$42)

vse vrednosti, ki so manjše od povprečja
=F10<AVARAGE($F$10:$P$42)

Obarvanje vseh podvojenih vrednosti

Mnogokrat je želja v neki veliki tabeli najti vse podvojene vrednosti:

=COUNTIF($F$10:$P$42;F10)>1

🙂

Manjka podatek?

Včasih se zgodi, da v večji Excelovi tabeli ugotovimo, da nam nek podatek manjka. Največkrat to ugotovimo, ker ima tabela premalo vrstic.

Večina tabel je namreč zgrajenih tako, da imajo v nekem stolpcu (ponavadi na levi strani) pač nek urejen podatke, ki mu na desni strani sledijo podatki. Ta urejen podatek na levi strani je lahko številka, datum, ura…

Problem je torej sledeč: »Kako najti, kateri podatek nam manjka?«

Za lažjo predstavo vzemimo konkreten problem, kjer imate tabelo v katero vsak delovnik vpišete podatke o prodaji. Seveda je to popolnoma enakovredno primeru, ko dobite neke podatke iz centralnega računalnika na osnovi delovnih dni. Ali pa podatke o urah…

Vsem tem tabelam je torej skupno, da imajo nek stolpec, v katerem bi se morali nahajati naraščujoči podatki a eden (ali več) manjka. Da jih nekaj manjka preprosto ugotovite, tako da pogledate velikost tabele. Če bi morali biti v tabeli podatki za delovne dni potem bi v tej tabeli moralo biti toliko vrstic kolikor je delovnih dni.. pa jih ni ?… Kateri manjka?

Dobro, tabelo lahko pregledujete ročno in iščete datum ki manjka a to je zamudno in dokaj vrjetno, da se boste zmotili oz. težko našli vse datume. Zaposliti je torej potrebno računalnik!

Delovni dnevi

Zanalašč sem vzel težji primer, saj vam bom tako prikazal rešitev več problemov hkrati. Najprej moramo najti delovne dneve!

Vpišite torej prvi datum. Z miško razpotegnite datume navzdol in Excel bo v vsako naslednjo celico zapisal nov (naslednji) datum. S tem ste dobili stolpec z naraščujočimi datumi.


Sedaj je potrebno še ugotoviti kateri dnevi so delovni (če bi imeli kje seznam praznikov bi vpletli še te, a danes pač predpostavimo, da so delovni dnevi vsi dnevi razen sobote in nedelje. Dan v tednu nam vrne funkcija WEEKDAY.

Zatorej v sosednji stolpec zapišemo funkcijo =WEEKDAY(<celica z datumom>;2) in razpotegnemo navzdol:

Pridružimo dneve iz naše originalne tabele

S pomočjo dodatka MExcel tem datumom pridružimo datume iz naše tabele. S tem dobimo še eno vrstico z datumi.

Kdo manjka?

Sedaj imamo seznam datumov, oznako za kateri dan v tedni gre in zraven še seznam datumov iz naše originalne tabele. Tukaj lažje že na oko vidimo kateri datum manjka… seveda pa je še mnogo bolje zaposliti Excel in mu reči:

Če je oznaka dneva manjša od 6 (torej ponedeljek – petek) in je sosedna celica prazna (ni datuma) potem je to napaka, sicer je vse OK:

=IF(AND(Q5<6;ISBLANK(R5));"MANJKA";".")

Pa jih imamo – vse packe 😉

Koliko popusta mi pripada za moj nakup?

Pogosta želja uporabnikov v Excelu je pridobiti procent popusta glede na vrednost nakupa. Pač nekaj v stilu: »V kolikor kupite blaga za 100€ ali več, vam pripada 5% popust. V kolikor kupite za 300€ ali več, vam pripada 10% popusta. V kolikor pa kupite za 1000€ ali več, pa vam pripada kar 20% popusta…«

Recimo sedaj, da imate vrednosti nakupov v stolpcu K, v stolpcu L pa želite izračunati vrednot popusta in nato v stolpcu M končno vrednost (torej vrednost z že upoštevanim popustom).

Ker je pogojev malo, lahko problem rešite s preprosto IF funkcijo, ki točno sledi gornjemu stavku in pravi: »V kolikor je vrednost manjša od 100 potem je popust 0%, sicer, če je manjša od 300€ je popust 5%, sicer, če je manjša od 1000€ je popust 10%, sicer 20%«.

V Excelu to seveda zapišemo takole:

=IF(K1<100;0%;IF(K1<300;5%;IF(K1<1000;10%;20%)))

Kaj pa če je teh pogojev, teh mej, več?

V tem primeru se funkcija grdo poveča in postane povsem nepregledna! V tem primeru si raje naredite tabelco, v katero vpišete zahtevane meje in pogoje in potem Excelu zaukažete, da naj išče po tej tabelci.

Za naš primer izgleda tabelca takole:

In iskanje ustreznega procenta v takšni tabeli je otročje lahko. Uporabiti je potrebno funkcijo VLOOKUP, o kateri si lahko več preberete med nasveti. V prikazanem nasvetu je funkcija VLOOKUP uporabljena v primeru, ko želite v neki tabeli najti točno konkretno vrednost in tedaj je zadnji parameter funkcije VLOOKUP false.

V našem primeru, pa moramo Excelu zaukazati, da naj išče točno želeno vrednost ali manjšo in v tem primeru je zadnji parameter funkcije vlookup true.

Iskana funkcija pa je torej takšna:

=VLOOKUP(K3;$A$2:$B$5;2;TRUE)

To je vse!  🙂 A lepota te rešitve je v dejstvu, da lahko imamo mnogo več razredov, recimo:

Pa je funkcija še vedno skrajno preprosta in jasna, le območje iskanja ja malce večje (namesto B5 pač B12):

=VLOOKUP(K3;$A$2:$B$12;2;TRUE)

POZOR: Za uporabo te rešitve morajo biti vrednoti v prvem stolpcu v naraščujočem vrstnem redu! Obvezno!

Razvrščanje podatkov v vrstici (od leve proti desni)

Zagotovo vsi poznate razvrščanje podatkov v Excelu in prav tako skoraj zagotovo ob razvrščanju tudi vsi takoj pomislite na razvrščanje podatkov po stolpcu. Kaj pa razvrščanje po vrstici? Kako razvrstiti podatke od leve proti desni?

Excel 2003

V Excelu 2003 je postopek razvrščanja podatkov od leve proti desni sledeč.

  1. Izberete meni Podatki/Razvrsti…
  2. Odpre se novo okno, kjer kliknete gumb ‘Možnosti’
  3. Spet se odpre novo okno, kjer izberete opcijo ‘Razvrsti od leve proti desni’
  4. Potrdite

Excel 2007/2010

  1. Izberete zavihek ‘Osnovno’ in v njem opcijo ‘Razvrsti in filtriraj’
  2. V hitrem meniju izberete opcijo ‘Razvrščanje po meri’
  3. Odpre se novo okno, kjer kliknete gumb ‘Možnosti’
  4. Spet se odpre manjše okno, kjer izberete opcijo ‘Razvrsti od leve proti desni’ ter potrdite.
  5. V osnovnem oknu izberete še vrstico za razvrščanje in to je to 😉