Tag: Excel

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!

Bančno zaokroževanje ali Gaussovo zaokroževanje ali parno zaokroževanje ali …

Pa sem spet pri zaokroževanju 🙂 (zaokroževanje števil, zaokroževanje časovnih elementov), a slednje je res tako pomembno da se ga je potrebno večkrat zavedati.

In spet sem naletel na zanko. Že v prvem blogu o zaokroževanju sem opozoril, da se resen problem zaokroževanja pojavi samo takrat, ko se neko število nahaja natančno na sredini. Tedaj je vprašanje kam ga zaokrožiti – ali navzdol ali navzgor.

In v bančnem oz. finančnem sektorju v tem primeru zaokrožujejo – pazite sedaj (!) – na najbližje SODO število. Na prvi pogled se zdi to nesmiselno, celo čudno, a ima namen. Ideja je namreč v tem, da se statistično gledano, število včasih zaokroži navzgor (kadar je prvo sodo število večje), včasih pa navzdol (kadar je sodo število pač manjše).

In ravno to zagotavlja neko mero »poštenosti«, saj banka včasih zaokroži v svojo škodo, včasih pa v svojo korist in v splošnem se to izravna. Ravno zaradi tega se ta način zaokroževanja včasih imenuje tudi Gaussovo zaokroževanje.

Dobro, tu je vse ok in jasno, vprašanje pa je zakaj to omenjam. Odgovor pa je preprost in zanimiv. To omenjam zato, ker, čeravno je Excel prvenstveno namenjen uporabi v finančnih institucijah, ne zaokrožuje na opisan način :(.

No resnica je še bolj zmedena, saj Excel v tabelah zaokrožuje na en način, pri uporabi VBA procedure pa na drugega!!

Torej funkcija Round v Excelu v primeru števila, ki je točno na sredini le tega zaokroži navzgor, funkcija Round v VBA-ju pa število, ki je točno na sredini zaokroži na najbližje sodo število.

Razlika je preprosto vidna. Že si zapišete sledečo VBA funkcijo:

Function ZaokroziKotBanka(stevilo, pozicija)
   ZaokroziKotBanka = Round(stevilo, pozicija)
End Function

In potem v Excelovi tabel zapišete:
=ROUND(10,5;0)            -- rezultat 11
=ZaokroziKotBanka(10,5;0) -- rezultat 10

UPSI!

Dobro, res da je razlika zelo minorna in specifična, saj se pojavi samo in izključno tedaj, ko je neko število točno na sredini… a dobro je da se tega zavedate.

Kako torej Excel prepričati da zaokrožuje po »bančnih« standardih?

  1. Najlažje z uporabo zapisane VBA funkcije.
  2. Če VBA-ja ne morete/smete uporabiti pa je potrebno zapisati malce kompleksnejšo funkcijo zaokroževanja…
    =IF(MOD(100*B2;1)=0,5;IF(ISODD(INT(100*B2));ROUNDUP(B2;2);ROUNDDOWN(B2;2));ROUND(B2;2))

Zanimivo kajneda ;)…

MExcel.biz

Vabljeni na premierno otvoritev nove blagovne znamke MExcel, ki se skriva na spletišču www.mexcel.biz. Gre za Excelovega pomočnika, čarovnika, ko vam bo skrajno poenostavil združevanje tabel v Excelu.

Programski dodatek MExcel bo v prihodnosti rastel in pridobival več orodij in čarovnikov a že v prvi izdaji ponuja izjemno orodje za združevanje tabel, s pomočjo katerega lahko podatke združite do 10x hitreje kot to sicer omogočajo vgrajene Excelove tabele.

Prednosti

  • Združevanje po več ključnih podatkih hkrati
  • Združevanje podatkov tako po horizontali kot vertikali
  • Dodajanje nezdruženih podatkov v rezultat
  • Neobčutljivost na format ključnih podatkov
  • Preprostost uporabe – ne potrebujete znanja Excela, saj lahko s pomočjo čarovnika tabeli združi vsak uporabnik
  • Nad veliko množico podatkov (tabele večje od 500.000 zapisov) je združevanje do 10x hitrejše kot v kombinaciji funkcij VLOOKUP ali INDEX in MATCH!

Slabosti

  • “Slabost” čarovnika je samo v dejstvu, da ga Excel ne pozna in ga morate torej naknadno doinstalirati… A instalacija je skrajno preprosta in intuitivna. 🙂

Vabilo

Vabim vas torej, da si orodje naložite, ga preizkusite in napišete svoje mnenje…

Excel in datoteke z veliko listi

Kadar imamo v Excelu veliko listov se lahko med njimi kar hitro izgubimo, še težje pa se med njimi sprehajamo. A orodje, ki je namenjeno sprehodu med listi je na voljo že mnogo časa, le malo uporabnikov ve zanj.

Na dnu Excelovega okna se, skrajno levo, nahaja majhen trak oz. skupek štirih gumbov, ki jih uporabite za premik na prvi list, prejšnji list, naslednji list ali pa zadnji list v zvezku.

A to ni vse; če na omenjene gumbke z miško desno-kliknete se vam odpre seznam, kjer so izpisana imena vseh listov in tako se lahko na poljuben list prestavite še hitreje in lažje…

Zaokroževanje časa

Zaokroževanje različnih elementov je v Excelu pogosta naloga in tudi vprašanja s to tematiko se zatorej večkrat pojavljajo. Zadnjič se je na forumu pojavilo vprašanje, kako zaokrožiti čas na polovico ure.

O zaokroževanju sem v blogu že pisal in tedaj tudi dokaj podrobno zapisal kako lahko neko število zaokrožimo na poljuben večkratnik drugega števila. Pokazal sem torej kako neko število zaokrožiti na npr. večkratnik števila 3. Glede na to znanje je rešitev preprosta. Čas torej moramo zaokrožiti na najbližjo polovico ure.

Toda čas ni število… Ali pač? No; kot sem v blogu že dokazal, je tudi čas v Excelu navadno število. Iz obeh člankov v blogu lahko zatorej izluščimo, da je čas v Excelu prikazan kot število in števila znamo zaokroževati!

Splošna formula, ki izhaja iz bloga o zaokroževanju je torej sledeča:

=Round(čas / pol_ure; 0) * pol_ure

Vprašanje torej ostaja samo koliko je pol_ure? Spet glede na zapis v blogu vemo, da je en dan predstavljen kot število 1. Ker ima dan 24 ur je torej ena ura predstavljena kot 1/24 in ker ima en dan 48 »pol ur«, torej pol ure predstavljeno kot 1/48.

Glede na zapisano torej že imamo pravilno formulo:

=Round(čas / (1 / 48); 0) * (1 / 48)

Seveda pa z malo znanja osnovnošolske matematike oz. računanja z ulomki vemo da A * (1 / B) == A/B in A / (1 / B) == A*B, zatorej lahko končno formulo zapišemo lepše:
=Round(čas * 48; 0) / 48

Glede na zapisanoformulo je torej zaokroževanje po času skrajno preprosto:
Na 5  minut:  =Round(čas * 288; 0) / 288
Na 10 minut:  =Round(čas * 144; 0) / 144
Na 15 minut:  =Round(čas * 96 ; 0) / 96
Na 30 minut:  =Round(čas * 48 ; 0) / 48
Na 1    uro:  =Round(čas * 24 ; 0) / 24
Na 2    uri:  =Round(čas * 12 ; 0) / 12

😉

DropBox ali »shrani moje podatke na mrežni disk«

V zadnjem času je popularen izraz »računalništvo v oblaku«. Izraz je lep in pritegne ljudi, pomeni pa, da se počasi vračamo k osnovam oz. v 60-ta leta prejšnjega stoletja. Tedaj smo namreč imeli velike centralne računalnike in na njih so bili priključeno ‘terminali’, ki pa niso imeli lastne pameti temveč samo tipkovnico in zaslon. Vse se je odvijalo na centralnem računalniku…

No »računalništvo v oblaku« pa je nekaj podobnega, le da današnji terminali (računalniki, dlančniki, mobilni telefoni) niso povsem neumni, temveč premorejo tudi nekaj lastne »pameti«. Kot bi torej rekel pesnik: »Sve su ostalo nijanse…«

A namen današnjega bloga ni v dilemi računalništvo v oblaku da ali ne, temveč v predstavitvi ene izmed aplikacij, ki so posledica te nove paradigme. Gre za aplikacijo DropBox, ki vam omogoča, da vaše podatke shranite v »oblaku«, torej na internetu in do njih dostopate od koderkoli in seveda kadarkoli.

DropBox

DropBox nikakor ni edina takšna aplikacija, saj podobnih obstaja kar nekaj a nekako je DropBox dokaj popularen tudi v Sloveniji, zato ga bom malce podrobneje opisal. MS recimo ponuja aplikacijo SkyDrive.

Pred uporabo DropBox aplikacije, morate najprej odpreti račun in s tem pridobiti prostor na mrežnem disku. Količina prostora je odvisna od tega ali boste aplikacijo uporabljali povsem zastonj ali ste pripravljeni zanjo kaj plačati. V kolikor boste stvar uporabljali zastonj, vam pripada 2GB prostora, v kolikor pa ste pripravljeni nekaj plačati, pa ste omejeni samo z debelino vaše denarnice.

Ko se torej registrirate dobite prostor in vaš novi disk lahko začnete uporabljati takoj. Dostop do njega imate urejen preko spletnega obrazca, še bolje pa je, da si na vaš računalnik naložite DropBox odjemalca.

DropBox odjemalec

Vse skupaj postane zanimivo, uporabno in simpatično šele z uporabo odjemalca, ki je majhen programček in teče v ozadju vašega operacijskega sistema. Ob instalaciji se samo odločite katara mapa na VAŠEM računalniku bo služila kot slika vašega novega mrežnega diska in to je vse.

Od tega trenutka dalje bo DropBox odjemalec poskrbel, da karkoli prenesete v mapo na VAŠEM disku, se bo avtomatično prepisalo na mrežni disk in seveda tudi obratno. Karkoli posnamete na mrežni disk se bo takoj preslikalo v vašo mapo na VAŠEM disku.

Kot je vidno že iz slike je DropBox kompatibilen tako z osebnimi računalniki kot seveda prenosniki, dlančniki, pametnimi telefoni in kar je še podobne »šare«… Prav tako je tudi možnih scenarijev uporabe kar nekaj in v nadaljevanju vam predstavljam dva.

Scenarij uporabe 1 – sinhronizacija med različnimi napravami

V kolikor uporabljate več računalnikov oz. podobnih naprav, lahko odprete račun na DropBox-u in odjemalca instalirate na vsako izmed uporabljenih naprav. Od tega trenutka dalje lahko na katerikoli napravi določen dokument postavite v DropBox mapo in v nekaj minutah (odvisno od povezav in velikosti datoteke) se bo slednja najprej prepisala na mrežni disk in od tam jo bodo posamezni odjemalci prepisali na vse vaše preostale naprave.

S tem ste torej dosegli sinhronizacijo dokumenta med vsemi vašimi napravami.

Scenarij uporabe 2 – varnostne kopije

DropBox mrežni disk si lahko predstavljate tudi kot varnostno kopijo vaših podatkov. Zatorej četudi uporabljate en sam računalnik lahko z uporabo takšne rešitve izdelujete varnostno kopijo vaših podatkov.

Ob tem vas opozarjam, na dejstvo, da vam povsem 100% varnega shranjevanja ne zagotavlja nihče… je pa majhna verjetnost, da se bodo podatki na mrežnem disku izgubili. Da bi pa hkrati izgubili podatke na vašem računalniku in mrežnem disku je pa že hudo hudo neverjetno…

mRacuni – Varna kopija podatkov o izdanih računih

Scenarij 2 lahko zatorej uporabite tudi v programskem paketu mRacuni, saj lahko s pomočjo mrežnega diska preprosto dosežete, da so dnevne kopije vseh vaših računov varno shranjene v oblaku.

V nastavitvah mRacunov imate možnost vpisati pot do mape, kamor se kopije shranjujejo in v kolikor na to mesto vpišete vašo DropBox mapo ste s tem dosegli iskani rezultat.

Kako do DropBox-a?

Kot rečeno je stvar popolnoma zastonj in dosegljiva na temle naslovu.

Razvoj Excelovih dodatkov v .NET okolju

Zadnjič smo si ogledali nekatere izmed možnosti razvoja Excelovih dodatkov, danes pa se bom omejil samo na razvoj v .NET okolju in podrobneje predstavil opcije na tem področju.

Seveda ne bom opisal vseh možnosti saj to niti ni mogoče. Pa tudi sicer tale zapis ni neka poglobljena študija temveč bolj moj osebni vpogled v tehnologije, ki sem jih preizkusil.

VSTO

VSTO (Visual Studio Tools for Office) je MS tehnologija za razvoj Office dodatkov. Preko VSTO lahko razvijate tako dodatke za Excel kakor tudi za vse preostale produkte za Office.

Prednosti

  • Podprta s strani MS
 Slabosti

  • Za razvoj potrebujete Visual Studio 20xx Professional ali še višjega
  • Za vsako aplikacijo (Excel, Word…) potrebujete lasten projekt
  • Težko razvijate dodatek ki bi tekel na različnih verzijah Excela (vsaka verzija svoj dodatek)

Excel-DNA

Excel DNA je čudovito orodje in ga priporočam vsakemu, ki želi razvijati dodatke za Excel.

Prednosti

  • Zastonj
  • Ne potrebujete Visual Studia za razvoj, dovolj je .NET prevajalnik
 Slabosti

  • Težko razvijate dodatek ki bi tekel na različnih verzijah Excela (vsaka verzija svoj dodatek)
  • Primeren izključno samo za razvoj Excelovih dodatkov, na pa tudidodatkov za druge Office programe

Add-in Express

Add-in Express ima glede na Excel-DNA eno veliko prednost in eno manjšo pomanjkljivost. Njegova velika prednost je zmožnost razvoje ene aplikacije za VSE verzije Excela hkrati, pomanjkljivost pa je da (za razliko od Excel-DNA) ni zastonj.

Prednosti

  • En projekt za vse verzije Excela hkrati
  • Preprost za uporabo
 Slabosti

  • Ni zastonj

Primerjava

Ko primerjate omenjene produkte je potrebno vedeti, za katero verzijo Excela boste dodatek razvijali in katero verzijo Visual Studia uporabljate.


Kako brati sliko?

Vsak oblaček prikazuje ali lahko v izbranem okolju razvijate dodatek. Ker rdečega krogca (VSTO) ni narisanega pod Visual Studio Express to pomeni, da s to različico v okolju VSTO ne morete razvijati. Velikost oblačka pa prikazuje čez koliko verzij Excela lahko greste brez spremembe kode.

Glede na prikaz v sliki se najslabše odreže VSTO. Da bi lahko razvijali s slednjim morate imeti ustrezno verzijo Visual Studia. To je torej najslabša izbira.

Če izberete Excel-DNA pomeni, da lahko razvijate v poljubni različici Visual Studia, vendar morate imeti za vsako verzije Excela lasten projekt. Tu moram opozoriti, da lahko tudi v okoljih VSTO in Excel DNA razvijate dodatek, ki teče tako na verziji 2003 kot na 2007, vendar je potrebno vložiti kar nekaj truda.

Na drugi strani pa je Add-in Express, kjer pa lahko v enem projektu razvijate za vse verzije Excela hkrati.

Kaj torej izbrati

Po mojem mnenju je odločitev dokaj preprosta. VSTO definitivno odpade, ker nima nobene prednosti. V kolikor nimate potrebe po razvoju za več verzij Excela potem izberite Excel-DNA, ki je super produkt in povsem zastonj.

V kolikor razvijate za več verzij Excela hkrati pa se lahko potrudite in rešitev vseeno razvijate s pomočjo Excel-DNA ali pa vložite nekaj denarja in izberete produkt Add-in Express, saj z nakupom slednjega dobite tudi možnost razvoja dodatkov za druge programe v okolju Office (Word, Excel…)

Razvoj dodatkov za Excel

Ko razvijamo dodatke za Excel se prej ali slej srečamo s problemom vzdrževanja programske kode. Na izbiro imamo namreč kar nekaj različnih načinov in okolij za razvoj dodatkov.

Na eni strani imamo VBA, ki je vgrajen v večino Office produktov, na drugi strani imamo programski jezik C in WIN API ter COM, na tretji pa .NET tehnologijo in COM.

VBA

Vsekakor je se potrebno pred kakršnimkoli resnim razvojem za MS Office dodobra seznaniti z programskim jezikom VBA. Razvoj v VBA-ju je nekako najlažji izmed vseh načinov in ima mnogo prednosti:

VBA Prednosti

  • VBA bazira na programskem jeziku BASIC, ki je zelo preprost jezik
  • Zelo dobra podpora znotraj vseh Office programov
  • Vvečino problemov nam reši vgrajeno snemanje makrov – če želite kaj postoriti, začnete s snemanjem makra in ga nato razvijate dalje
  • VBA urejevalnik nam preko vgrajenega ‘InteliSence’-a aktivno in obilno pomaga pri programiranju.
  • Programska koda se ne prevaja, temveč interpretira, kar je za razvoj dobrodošlo, saj lahko programsko kod spreminjamo med razhroščevanjem programa (glej spodaj)

VBA Slabosti

  • Programska koda je zapakirana v posamezen dokument (Excelov XLS ali XLSM oz. XLA in XLAM) kar pomeni slab nadzor nad programsko kodo
  • Programske kode ni možno dajati v repozitorije programske kode
  • Otežena je souporaba kode v več projektih saj VBA ne pozna koncepta knjižnic
  • Programska koda se ne prevaja temveč interpretira (glej zgoraj), kar je lahko slabost, saj se ob prevajanju odkrijejo mnoge napake.
  • Preko VBA ne moremo storiti vsega – določene stvari lahko spreminjamo, popravljamo, dograjujemo samo preko knjižnic v C oz. C++ jezikih.

C / C++

Izmed vseh izbir je to najtežja pot, ki ima glede na naslednjo možnost, torej razvoj v .NET-u eno slabost in eno prednost

C / C++ prednosti

  • Najhitrejša koda in največja možnost nadzora nad načinom izvajanja

C / C++ slabosti

  • Zelo zapleten in dolgotrajen razvoj, slaba (zelo slaba!) literatura

.NET

Za razvoj v .NET-u pa imamo več možnosti, ki so prikazane spodaj.

.NET Prednosti

  • Možna uporaba knjižnic
  • Možna uporaba vseh znanj pridobljenih na področju –NET razvoja vključno z tisiči in tisoči objektov, ki so napisani zanj
  • Glede na prejšnjo točko seveda tudi preprosta možnost povezav do podatkovnih baz, spletnih storitev…
  • Popoln nadzor nad programsko kodo in možnost uporabe repozitorijev programske kode

.NET Slabosti

  • Neintuitivni razvoj
  • Težak dostop do ustrezne literature za dostop do Office automatization objektov

Oblikovanje celic v Excelu – kode za datum in čas

V enem izmed prejšnjih prispevkov smo si ogledali kako lahko v Excelu definirate lasten izgled podatkov v celicah. V omenjenem prispevku pa smo se omejili samo na določanje oblike numeričnih podatkov.

Seveda pa pozna Excel tudi posebne kode za oblikovanje datumov in časov in v tem prispevku si bomo zatorej ogledali slednje.

Oblikovni znaki za datume in čase

Tabela oblikovnih znakov za datume:

Znak Pomen
m Izpiše mesec brez vodilne ničle, torej 1 do 12
mm Izpiše mesec z vodilno ničlo, torej 01 do 12
mmm Izpiše tričrkovno okrajšavo za mesec, torej jan do dec
mmmm
 Izpiše ime meseca januar do december
d
 Izpiše dan v mesecu brez vodilne ničle, torej 1 to 31
dd
 Izpiše dan v mesecu z vodilno ničlo, torej 01 to 31
ddd
 Izpiše tričrkovno okrajšavo za dan v tednu(!), torej pon do ned
dddd
 Izpiše dan v tednu(!), torej ponedeljek do nedelja
yy
 Izpiše dvomestno leto (torej brez 20 iz 19 spredaj)
yyyy
 Izpiše vse 4 cifre v letu

Tabela oblikovnih znakov za čase:

Znak Pomen
s Sekunde brez vodilnih ničel 0 do 59
ss Sekunde z vodilnimi ničlami 00 do 59
m Minute brez vodilnih ničel 0 do 59
mm
 Minute z vodilnimi ničlami 00 do 59
h
 Ure brez vodilnih ničel 0 do 23
hh
 Ure z vodilnimi ničlami 00 do 23

Primeri

Ker je razlaga suhoparna sem vam spet pripravil datoteko, kjer se lahko po mili volji igrate z nastavitvami … 🙂

<datoteka s primeri>

Oblikovanje celic v Excelu – kode za tekst

V enem izmed prejšnjih prispevkov smo si ogledali kako lahko v Excelu definirate lasten izgled podatkov v celicah. V omenjenem prispevku pa smo se omejili samo na določanje oblike numeričnih podatkov.

Seveda pa pozna Excel tudi posebne kode za oblikovanje teksta in v tem prispevku si bomo zatorej ogledali slednje.

Oblikovni znaki za besedilo

Tabela znakov za oblikovanje besedila:

Znak Pomen
$ + / – ( ) : presledek Ti znaki se izpišejo na mestu, kjer so zapisani
\<znak> Vsem preostalim znakom (torej znakom, ki niso napisani zgoraj) je potrebno dodati predpono \ ali pa jih zapreti med dvojna narekovaja, kot je prikazano spodaj
“besedilo” Izpiše se besedilo
*
 Ta koda zapiše znak, ki ji sledi tolikokrat, da zapolni celotno širino celice (zelo uporabno!)
@ Izpiše vsebino kot tekst

Primeri

Ker je razlaga suhoparna sem vam spet pripravil datoteko, kjer se lahko po mili volji igrate z nastavitvami … 🙂

<datoteka s primeri>