Lepljenje podatkov v že oblikovane celice

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.

Kako v Word hitro zapisati veliko besedila

Kadar oblikujete dokumente oz. predvsem kadar pripravljate predloge po katerih bo kdo drug urejal dokumente, potrebujete testno besedilo.

Dandanes je do testnega besedila priti dokaj preprosto. Odprete poljubno spletno stran in iz nje prekopirate besedilo v vaš dokument.

A gre še lažje, hitreje in z malce več kontrole. V Prano vrstico v Wordu vpišite =RAND() in pritisnite ENTER in …. Pojavilo se bo besedilo 🙂

Imate pa nad besedilo še malce več kontrole, saj lahko Wordu tudi poveste koliko odstavkov želite in koliko stavkov v vsakem odstavku želite. To zapišete kot parametra funkcije. Funkcija ‘=RAND(10, 4)’ bi izpisala 10 odstavkov s po štirimi stavki. 😉

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!

Edinstvene vrednosti v vrtilnih tabelah

V Excelu ni načina, ki bi nam omogočil iskanje edinstvenih vrednosti med podatki v vrtilni tabeli (to možnost ima dodatek PowerPivot v Excelu 2010, a o tem kdaj drugič).

Primer takšnega vprašanja, na katerega znotraj vrtilne tabele ni odgovora, je: »Koliko različnim strankam smo prodali določen izdelek?«.

Če imamo torej tabelo s podatki o prodaji izdelkov strankam, lahko v vrtilni tabeli preprosto in takoj vidimo koliko izdelkov smo prodali, koliko strankam smo jih prodali ipd… Ne moremo pa videti koliko različnim strankam smo jih prodali.

Continue reading Edinstvene vrednosti v vrtilnih tabelah

Seštevanje samo negativnih ~ pozitivnih vrednosti…

Rešitev ponuja funkcija SUMIF, ki jo lahko uporabite vedno, kadar želite seštevati glede na nek kriterij…

=SUMIF(območje1; kriterij; območje2)

Kot vidite sprejme funkcija tri parametre. Prvi parameter je območje nad katerim želite testirati kriterij, drugi parameter je kriterij, tretji parameter pa je seveda območje, ki ga želite sešteti.

PS: Bodite pozorni na tretji primer, kjer sta območji različni, saj Celje iščemo po prvem, seštevamo pa po drugem!