Archive for the ‘Excel’ Category

Lepljenje podatkov v že oblikovane celice

Četrtek, December 4th, 2014

Danes sem na spletišču Slo-tech zasledil vprašanje, ki je kar pogosto, pa se ga dosedaj še nisem dotaknil.

Vprašanje je, kako vnaprej formirati številke da imajo v celici nek predpisan izgled. Primer je npr. telefonska številka, za katero večinoma želimo, da je prikazana v obliki (031) 668 532, čeravno je sama številka pač 031668532.

Oblikovanje takšnega izgleda je trivialno in ga bralci mojega bloga že poznateVendar pa se pojavi problem, ko v tako oblikovano celico prekopirate podatek iz druge celice.

Kopiranje

Po privzetem Excel vedno kopira tako vsebino, kot obliko celice in v našem primeru pač prekopira tudi obliko izvorne celice in zato se oblika v celici poruši.

Moj odgovor na tovrstne zagate je vedno, da vsebino vnašajte na enem mestu, končno (pravo, lepo) obliko pa prikazujte v drugih celicah, ki se na prve samo skljucujejo. S tem lepo ločite vnos in prikaz.

Seveda pa to ne gre vedno in mnogokrat preprost želite vnašati v celice, ki jih navkljub vsemu želite imeti drugače oblikovane. V tem primeru imate dve možnosti:

  1. Vedno pazite da izberete opcijo ‘Prilepi vrednosti’ in v tem primeru pač ne morete uporabiti bljižnice CTRL+V
  2. Na bljižnico CTRL+V postavite makro, ki izvede lepljenje po vrednosti. V tem primeru je problem, ker morate makro vedno priložiti in tudi oseba, ki uporablja takšen zvezek mora dovoliti izvajanje makrov.

Makro za lepljenje vrednosti

Makro za lepljenje samo vrednosti je zelo preprost:

Sub PrilepiSamoVrednosti()
 Selection.PasteSpecial Paste:=xlPasteValues, _
                        Operation:=xlNone, _
                        SkipBlanks:=False, _
                        Transpose:=False
End Sub

Vse kar je potrebno še storiti je, da temu makru določite bljižnico CTRL+V in vedno kadar boste uporabili CTRL+V (lepljenje) bo Excel poklical ta makro in prilepil samo vrednosti, ne pa tudi oblike.

Določanje bljižnice makra:

  1. Pritisnite ALT+F8 – odpre se seznam vseh markov
  2. V seznamu izberite makro ‘ PrilepiSamoVrednosti’ in pritisnite gumb Možnosti
  3. V kvadratek ob ‘CTRL+’ vpišite V

To je to.

Iskanje celic glede na obliko oz. lastnosti

Torek, Februar 5th, 2013

Iskanje vrednosti v Excelovih tabelah je preprosto in ga takoj obvlada vsak uporabnik. Tako recimo vsi vemo, da lahko poiščemo vse celice, ki imajo vrednost 516 preprosto tako, da izberemo orodje za iskanje (bližnjica CTRL+F, Urejanje / Najdi (Excel pred 2007) oz Osnovno / Urejanje / Poišči in izberi (Excel 2007 in nadalnji)) ter vanj vpišemo vrednost 516 ter kliknemo ‘Nadaljuj iskanje’ ali pa ‘Najdi vse’


Iskanje posebnost

Kaj pa če želimo najti vse združene celice ali pa vse celice, katerih tekst je v rdeči barvi?

Tudi tukaj nam zna Excel priskočiti na pomoč, saj lahko v že omenjenem oknu, natančneje določimo kakšno obliko celice iščemo. Zgoraj desno imamo namreč na voljo gumb ‘Oblika’ s katero določimo obliko in Excel išče celice, ki imajo natanko določeno obliko.

Primer: iskanje združenih celic

Če želimo poiskati vse združene celice izberemo torej orodje za iskanje, iskalni pogoj pustimo prazen, saj nas vsebina ne zanima in izberemo gumb ‘Oblika’. Odpre se novo okno, v katerem določimo obliko celic, ki jih želimo najti. Ker nas zanimajo celice, ki so združene, se postavimo na zavihek ‘Poravnava’ ter označimo, da nas zanimajo združene celice

Ko vse potrdimo, nam Excel prikaže seznam združenih celic oz. nam pokaže posamezne združene celice:

Na enak način lahko recimo najdemo tudi vse celice, ki imajo rdečo barvo ali pa imajo rdečo barvo in so združene.

Če nas ob obliki zanima tudi vrednost pač v okno vnesemo tudi vrednost in tako najdemo vse celice, ki imajo npr. vrednost 516 in so obarvane rdeče.
Ne pozabite počistiti oblikovanja, ko iščete nekaj drugega!

Excel si namreč zapomni kakšno oblikovanje ste izbrali in ob naslednjem iskanju oblikovanje ohrani. Ko torej vi naslednji recimo vnesete iskalni pogoj ‘Ljubljana’ se lahko zgode, da bo Excel vstrajal da te besede ne najde, čeravno vi veste, da se v tabel zagotovo nahaja…

Problem je lahko v tem, da ste prej označili, da vas zanimajo celice z rdečo barvo in Ljubljane z rdečo barvo pa v tabeli recimo ni. Excel vam tudi kaže, kakšno obliko išče, zato bodite na to pozorni

Oblikovanje počistite v padajočem meniju

Zamenjaj/Najdi besedilo samo znotraj označenih celic

Sreda, Januar 2nd, 2013

V Excelu akcija zamenjevanja znakov ni tako pogosta kot v Wordu a se navkljub vsemu včasih le zgodi, da želite v kakšni veliki tabeli zamenjati neko besedo z drugo besedo. Recimo, da želite besedo ‘rdeč’ zamenjati z besedo ‘moder’.

Akcija je skrajno preprosta, saj samo izberete orodje za zamenjevanje in zaukažete, naj Excel pač besedico ‘rdeč’ zamenja z ‘moder’. To je vse.

Kaj pa če želite zamenjati besedici samo znotraj nekega manjšega področja in ne znotraj celotne tabele? Recimo samo znotraj enega stolpca?

V tem primeru pa se pojavi dilema, saj nikjer v oknu za zamenjavo znakov ni opcije ‘zamenjaj znotraj označenega obsega’. Določite lahko, naj Excel zamenjuje besede samo znotraj lista ali pa znotraj delovnega zvezka. Kaj pa znotraj označenega območja?

In izkaže se, da te opcije ni, ker je to pač privzet Excelov način delovanja. Če torej najprej izberete neko območje in potem izberete orodje za zamenjavo (ali iskanje), bo Excel zamenjal znake (iskal znake) samo znotraj označenega območje, ne da bi mu vi to morali posebej zaukazati!

Edinstvene vrednosti v Excelu III

Torek, November 6th, 2012

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

Torek, Oktober 16th, 2012

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

Petek, Oktober 5th, 2012

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’

Ponedeljek, September 24th, 2012

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’…

😉

Excelovi grafi so zares dinamični

Petek, Avgust 31st, 2012

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

Torek, Avgust 21st, 2012

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.

(more…)

Excel in delovne datoteke

Četrtek, Avgust 9th, 2012

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