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!

Excel in tipka ‘Scroll Lock’

Tipka ‘Scroll Lock’ je podobna tipkama ‘Num Lock’ in ‘Caps Lock’, saj jih druži že podobno ime. Lock pomeni zaklenjeno in ko stisnete katero izmed teh tipk, se funkcija aktivira. Deaktivirate jo seveda tako, da tipko kliknete še enkrat. Pomen tipk, pa je sledeč:

  • Caps Lock – ta je najbolj znana in jo zagotovo poznate vsi. Če jo vključite se tipkovnica »spremeni« tako, da tipkate samo velike črke.
  • Num Lock – tudi ta je dokaj znana in je večinoma vseskozi vključena. V kolikor jo vključite s tem »zaklete« numerično tipkovnico na desi strani, kar pomeni, da se tipke obnašajo kot številke in ne kot smerne tipke.
  • Scroll Lock pa je dokaj neznana in praktično je nikjer ne uporabljamo pomeni pa ‘zaklepanje premikanja’, kakorkoli se to čudno sliši.

In kaj je sedaj z Excelom?

Kot rečeno, se tipka ‘Scroll Lock’ ne uporablja veliko in v Excelu se mnogi uporabniki pravzaprav ustrašijo, da je nekaj narobe, če je ta tipka vklopljena.

Ko je ‘Scroll Lock’ namreč vklopljena, smerne tipke na tipkovnici ne premikajo več kazalca med celicami, temveč premikajo celotno tabelo in to je lahko zelo frustrirajoče, če ne veste kako je do tega prišlo oz. če tipko vključite po pomoti (kar se rado zgodi!).

NASVET: Če se vam torej v Excelu naekrat začne premikati celotna tabela in ne izbrana celica, potem imate vključeno tipko ‘Scroll Lock’…

😉

Tabulatorji II

Zadnjič smo spoznali osnovni namen tabulatorjev, danes pa si bomo ogledali še tri dodatne vrste tabulatorjev, ki vam v poslovnem svetu dostikrat lahko pomagajo.

Današnja tematika je tako praktična, da si bomo vse ogledali direktno na primerih. Izhajali bomo iz preprostega seznama živil s cenami. Med nazivom izdelka in ceno smo že vstavili tabulator in dobili sledeč izgled:

Če sedaj dvokliknete  po oznaki tabulatorja (obkrožen z rdečo), se nam odpre novo okno, kjer lahko tabulatorje natančno postavljamo:

Kot je vidno na sliki, imamo trenutno definiran en tabulator, ki se nahaja na 2,54 centimetrih (PS: to je ravno en inch, kar pa nima nobene zveze oz. je čisto nepomembno). To je levi tabulator in nima vodline črte.

Če namesto levega izberemo sredinski tabulator…

…se nam vse cene poravnajo po sredini (to seveda v tem konkretnem primeru nima nobenega smisla).

Če pa v oknu izberemo decimalno poravnavo pa je vse skupaj že precej bolj smiselno, saj so vse cene takoj lepo poravnane po decimalni vejici:

Če cene oblikujemo še »z manjkajočimi« ničlami, pa sploh dobilo lep izgled:

Lahko pa naredimo še nekaj boljšega in pri tabulatorju zaukažemo še vodilne pike, pa se nam skozi celoten prazen prostor (torej skozi prostor) tabulatorja lepo izrišejo pikice.

Še dve povsem praktični uporabi

Podpis v poslovnem pismu:

Večina poslovnih pisem se konča s krajem in datumom na levi strani, ter podpisnikom na desni strani. Večina ljudi na žalost ta problem spet rešuje z uporabo presledkov, nekateri »naprednejši« z uporabo tabel (tabela z dvema celicama, v levi sta kraj in datum, v desni pa podpisnik, ki je poravnan desno)..

Redko kdo pa uporabi najtrivijalnejšo, najhitrejši in zato seveda najboljšo pot. Med kraj in podpisnika vstavite desno poravnani tabulator in tabulator postavite na rob lista…. TO JE VSE!


Anketa:

Mnoge ankete so izdelane tako, da mora izpolnjevalec pač vpisati še nek tekst, kar stori z vpisom teksta na pripravljeno črto… In spet večina ljudi črte riše z vnosom 30 podčrtajev ali še kakšnimi bolj »domiselnimi« tehnikami; vse kar pa je potrebno storiti pa je vpisati vprašanje in pritisniti tabulator, pred katerim zaukažemo, naj se vidi črta:


Tabulatorji

V nekaj nadaljnjih prispevkih bom govoril o tabulatorjih, saj opažem, da se uporabniki sploh ne zavedate kako uporabni so tabulatorji in kako zelo preprosto jih je uporabljati.

Kaj so tabulatorji

Še v času elektronskih pisalnih strojev je na tipkovnici obstajala posebna tipka (tipka TAB, ki je seveda od tedaj dalje tudi na računalniških tipkovnicah), ki je list premaknila na naslednje mesto, katerega pozicija je bila deljiva z 8. Ok; tole je malo težko razumeti, a ideja je preprosto v tem da se je s tipko TAB uporabnik lahko premikal med sledečimi pozicijami 8, 16, 24… Kot lahko sklepate pa je bilo to zamišljeno za lažjo izdelavo tabel.

No; boljši stroji so imeli potem tudi nastavljiv razmik tabulatorjev, kar pa za nas sploh ni več pomembno. V času računalnikov pač seveda pričakujemo, da bo funkcionalnost ohranjena oz. celo nadgrajena… in res je tako!

Tako lahko v Wordu preprosto z miško določamo, kje se bodo nahajali tabulatorji in kar je še posebej uporabno(!), z miško jih lahko preprosto premikamo po ravnilu in s tem seveda ustrezno premikamo tudi besedilo.

Primer uporabe

V vseh sličicah spodaj je v Wordu izbran prikaz vseh znakov in zato so presledki videti kot majhne pikice, tabulatorji kot puščice in znak za novo vrstico kot narobe obrnjena črka P.

Za začetek torej vzemimo dokaj klasičen primer, kjer želimo zapisati osebe, ki so pri nekem poslu pač sodelovale. Izgled je pač klasičen, kjer je na levi strani zapisana funkcija, na desni strani pa oseba, ki to funkcijo opravlja.

Večina uporabnikov to težavo rešuje z uporabo presledkov, kar je seveda povsem napačno iz mnogih razlogov in če omenim vsaj dva:

  • črke so neproporcionalno široke in zato s presledki besedila nikoli ne morete lepo (čisto) poravnati
  • če je potrebno (zaradi prostora) vsa imena prestaviti bolj desno je potrebno v vsaki vrstico dodati nekaj presledkov

Naprednejši uporabniki ta problem rešujejo s tabelami, kjer v prvo kolono zapišejo funkcijo, v drugo kolono pa osebo. To je povsem pravilna rešitev, ki dajte tudi lepe in pravilne rezultate a tabele o v tem primeru povsem odveč in jih ne potrebujemo.

Primer oblike, kjer so uporabljeni presledki:

Zamenjajmo vse odvečne presledke s tabulatorjem in dobimo uporabnejšo rešitev, ki pa (samo za kratek čas!) izgleda neurejena, saj Wordu nismo povedali, kje želimo imeti tabulator postavljen:

Zatorej označimo celoten tekst in z miško enkrat kliknemo na ravnilo, ter s tem postavimo tabulator. Seveda se lepo in takoj poravna celoten tekst (OPOMBA: Tekst je očrnjen, ker je izbran v celoti in zato postavitev tabulatorja velja za VSE vrstice!):

S tem pa smo pridobili še eno veliko prednost, če namreč tabulator premaknemo, s tem premikamo tudi tekst.

Ob premiku tabulatorja pa velja opomnik, da morate označiti vse vrstice katerih tabulator prestavljate, saj sicer prestavite tabulator samo za vrstico v kateri se nahaja kazalec, kakor je vidno na spodnji sliki:

Naslednjič pa bom spregovoril še o drugih vrstah tabulatorjev, saj znajo tabulatorji poravnati tekst levo, desno, na sredino…. ali pa celo po decimalnih vejicah!

Excelovi grafi so zares dinamični

Današnji nasvet je zelo kratek a zanimiv. Mnogi uporabniki namreč ne veste, da lahko v Excelu tudi graf vpliva na podatke in ne samo obratno.

Torej vsem je jasno, da če v Excelu izdelate tabelo in na podlagi te tabele graf, se bo graf spremenil, če spremenite podatke. če torej prodajo v tabeli popravite iz 12.348,78EUR na 13.234,87EUR, se bo to takoj odražalo tudi na grafu.

Toda stvar deluje tudi obratno :)… Če namreč na grafu z miško povečate/pomanjšate (pač spremenite) en stolpec se bo zadaj v tabeli ustrezno popravil podatke, ki se na ta stolpec nanaša. (Seveda to deluje z vsemi oblikami grafov, ne samo stolpičastimi, a s slednjimi si je zadevo najlažje predstavljati)

Seznami opravil

Danes vam bom predstavil zanimivo in uporabno VBA proceduro, ki vam lahko v Excelu olajša delo s seznami opravil.

Ljudje si namreč večkrat izdelujemo sezname opravil in se jih potem bolj ali manj držimo :). A danes ne bom govoril o vztrajnosti, temveč o tem, kako nam lahko Excel pomaga hitro označiti katere naloge smo že opravili in katere ne.

Ideja je preprosto v tem, da si v Excelu naredimo nek seznam opravil (recimo v stolpcu B), potem pa želimo v stolpcu A kljukico, če smo neko opravilo že izvedli oz. prazno mesto, če ga še nismo izvedli.

Continue reading Seznami opravil

Iz PDF-a v MS Pisarno

Današnji prispevek bo kratek a uporaben. Mnogo uporabnikov me namreč sprašuje kako lahko določen PDF dokument pretvorijo v Word ali Excel oz. v Word-ovo ali Excel-ovo datoteko.

Sedaj je to preprosto: Free PDF Creation Online je namreč spletna stran, kamor pošljete PDF datoteko in po pošti vam vrnejo Excelovo ali Wordovo datoteko in to zastonj :)..

Seveda pa se morate zavedati določenih omejitev in problemov z obliko, slovenskimi znaki ipd… A nekaj je le 😉

Excel in delovne datoteke

V kolikor želite, da se vam od odpiranju Excela odprejo tudi določeni delovni zvezki s katerimi dnevno delate lahko to preprosto dosežete.

Ideja je namreč v tem, da na disku odprete neko mapo, kamor shranite vse delovne zvezke s katerimi vsak dan delate. Na ta način imate vse omenjene zvezke skupaj in tudi Excel jih lahko hitro in preprosto odpre.

Nastavite Excel tako, da vedno odpre vse delovne zvezke v izbrani mapi

Excel 2003 in nazaj: Orodja / Možnosti / (zavihek) Splošno / (opcija) Ob zagonu odpri vse datoteke v

Excel 2007 in naprej: Datoteka (ali office gumb v 2007) / Možnosti / (izbira) Dodatno / (skupina) Splošno / (opcija) Ob zagonu odpri vse datoteke v