Banner

pondelok 27. októbra 2014

Ako spočítať unikátne názvy v bunkách alebo počet hodnôt bez duplicít


Po niekoľkých minútach hľadania na internete sa dostanete na stránky MS OFFICE, kde majú napísaný základný prehľad vzorcov. Určite mi dáte za pravdu, že nevyzerajú vôbec prívetivo a v prípade, že by sme chceli rozšíriť danú podmienku bez duplicít, tak to nemusí byť záležitosť 5 minút (aspoň pre mňa nebola).

Prejdime k príkladu, kde mám vygenerovaný zoznam 57 mien, z ktorých je presne 12 jedinečných, teda neopakujúcich sa.

image

V tomto prípade bol použitý nasledujúci vzorec:

{=SUM(IF(FREQUENCY(IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""); IF(LEN(G3:G59)>0;MATCH(G3:G59;G3:G59;0);""))>0;1))}

Špeciálne zátvorky znamenajú. že ide o formu maticového počtu (napísaný vzorec aktivujeme pomocou Ctrl + Shift + Enter). Prejdime však na rozbor tohto vzorca:

image

IF(LEN($G$3:$G$59)>0;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Prvý stĺpec analyzuje bunky v stĺpci takto: Ak je dĺžka textového reťazca nenulová (t.z., že sa v bunke nachádza text), tak vráti pozíciu textu v stĺpci (Keďže meno Bernard sa nachádza ako 2. v poradí v tabuľke, tak funkcia MATCH() vráti číslo dva v oboch, oranžovo označených prípadoch). Ak by bunka bola prázdna, tak funkcia vráti prázdnu hodnotu “”.

{=FREQUENCY($H$3:$H$59;$H$3:$H$59)}

Ďalším kľúčovým prvkom je funkcia FREQUENCY(), ktorá funguje maticovým spôsobom. V nej uvedieme, že chceme pre každú hodnotu vo vypočítanom stĺpci nájsť, koľkokrát sa  v rámci stĺpca nachádza.

{=IF(I3:I59>0;1;0)}

Posledná podmienka už len vyhodnotí výsledok vzorca FREQUENCY() v zmysle, že ak je frekvencia daného čísla v tabuľke nenulová, tak vráti 1 ako jedinečná, inak 0 ako duplicita.
Posledná sčítacia funkcia už len spočíta nenulové hodnoty v predchádzajúcom stĺpci a tým pádom vráti počet mien bez opakovania.

Pokúsme sa pridať ďalšiu obmedzujúcu podmienku. Chceme vedieť počet špecifických mien, ku ktorým prináleží vek nad 30 rokov.

Ak si vyfiltrujeme všetky mená, ktoré majú vek nad 30 rokov a použijeme predchádzajúcu funkciu, zistíme, že máme už len 11 mien (viď template). Avšak my nechceme zbytočne filtrovať a upravovať rozsahy vo vzorci. Chceme pridať alebo pozmeniť len časť predchádzajúceho vzorca.

Pozmeníme preto hneď prvú časť vzorčeka takto:

         Definujeme si podmienku vek nad 30 rokov - IF($F$3:$F$59>30;1;0)
         Definujeme si podmienku neprázdna bunka - IF(LEN($G$3:$G$59)>0;1;0)

Ak obe podmienky platia, ich súčet musí dať dohromady číslo 2. Takže platí:

=IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);"")

Červenou farbou je zvýraznená zmena oproti pôvodnej podmienke. Ostatné vzorčeky sa nemenia, avšak aby sme obišli problém s výsledkom #N/A pri niektorých neplatných hodnotách, prerobíme poslednú sčítaciu funkciu na:

SUMIFS($R$3:$R$59;$R$3:$R$59;">0")

Takto spojené podmienky a funkcie budú vyzerať nasledujúco (pri spájaní do jednej funkcie použijeme len SUM() a nie SUMIFS(), lebo sa jedná o maticový počet):

=SUM(IF(FREQUENCY(IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""); 
IF(IF($F$3:$F$59>30;1;0)+IF(LEN($G$3:$G$59)>0;1;0)=2;MATCH($G$3:$G$59;$G$3:$G$59;0);""))>0;1))

Na základe použitého princípu môžeme pridať oveľa väčší rozsah podmienok. Stačí ak hodnoty, vstupujúce do FREQUENCY() sú založené na báze Ak{ (Platí Podmienka 1 = 1 inak 0) + (Platí Podmienka 2 = 1 inak 0) + (Platí Podmienka 3 = 1 inak 0) + … + (Platí Podmienka n = 1 inak 0) } = n tak nájdi pozíciu textu v rámci tabuľky, inak vráť prázdnu hodnotu.



Jedným z ďalších riešení je použiť VBA a naprogramovať si niečo ako vlastný COUNTIFS() bez duplicít. Čo môže byť obsahom nasledujúceho článku. Každopádne template na stiahnutie spolu s odkazom na web a facebook je k dispozícii kliknutím na nasledujúce obrázky:
downloads_normal

pondelok 6. októbra 2014

Ako Excel upraví Word a uloží do PDF: VBA automatizácia


Máte štandardné wordovské dokumenty ako zmluvy, vyúčtovacie formuláre, prihlášky atď.. V nich potrebujete zmeniť zopár hodnôt ako dátum, meno, priezvisko, vek atď.. Tieto dokumenty sa v prevažnej miere ukladajú ako historické podklady, tlačia a posielajú koncovým adresátom.
Problém je však v tom, že tie hodnoty sa nachádzajú v Excel-i a my ich potrebujeme dostať do Word-u.
Tí z Vás, ktorí tieto veci robia ručne, tak pozor! … Existuje na to makro Winking smile.
 
Príklad: Pripravme si najprv Word dokument s názvom Dokument.docx, ktorý bude vyzerať takto:
 
 
image
 
Na obrázku vidíme, že hodnotu, ktorú plánujem meniť podľa zoznamu v Exceli je Zmena dňa:. Tým pádom si vytvorím záložku (Bookmark) Označím oblasť vo Worde – Insert Bookmark:

 

image

Túto oblasť pomenujem ako DEN a potvrdím voľbu kliknutím na Add:
 

image
Tým pádom som si pripravil predlohu vo Worde, ktorú potrebujem obmieňať o špecifický zoznam z Excelu. Ten bude jednoduchý:


image

Máme pripravený Word a Excel. Teraz nám stačí vytvoriť si VBA procedúru tak, aby po kliknutí na tlačítko sa otvoril Word, vložili doň hodnoty a povedzme aby sa dokument uložil (aj vytlačil, záleží od toho čo potrebujeme) pod špecifickým názvom ako PDF, v našom prípade “konkrétny deň.pdf”.
 

Sub PDF_word()

Dim Meno As String
Dim WAPP As ObjectDim WDOC As Object
Dim EWB As Workbook: Set EWB = ThisWorkbook
Meno = "Dokument.docx"

'=================Aktivujeme Word=======================================
'======================================================================
  
Set WAPP = CreateObject("Word.Application")
WAPP.Visible = True
Set WDOC = WAPP.Documents.Open(ThisWorkbook.Path & "\" & Meno)
     
'======================================================================
'======================================================================

For i = 3 To 9
        Dim denRNG: Set denRNG = WDOC.Bookmarks("DEN").Range
            denRNG.text = EWB.Sheets("ZOZNAM").Cells(i, 2).Value
        WDOC.Bookmarks.Add "DEN", denRNG
        WAPP.ActiveDocument.SaveAs2 ThisWorkbook.Path & "\" & Cells(i, 2).Value & ".pdf", 17
Next i

WAPP.Quit savechanges:=wdDoNotSaveChanges

End Sub

Výsledok vyzerá nasledovne:

image

V referenčnom adresári sú okrem vzorového wordu a excelu s markom uložené kópie dokumentov podľa dňa v týždni.

Cvičný súbor si môžete stiahnuť kliknutím na nasledujúci odkaz. V prípade dotazov, alebo ak by ste mali záujem o takéto riešenie na mieru:


downloads_normal

streda 1. októbra 2014

Kontingenčná tabuľka a funkcia získatkontdata resp. GETPIVOTDATA

 

Začnem otázkou: Prečo táto kombinácia?

1) Rieši komplikované delenie faktov podľa dimenzií (tržieb podľa krajov, predajcov apod.) – nemusíme používať databázové funkcie ani zložíté makra

2) Rozdelenie výpočtového výkonu na časť kontingenčnej tabuľky a funkcie           (V konečnom dôsledku Excel bude počítať len ako dostať hodnoty z kontingenčnej tabuľky a nie celkový prepočet, čo značne uľahčuje manipuláciu so súborom s veľmi veľkým množstvom dát a analýz)

Pokračujem otázkou: Prečo túto kombináciu nie?

1) Veľmi citlivé na zmenu kontingenčnej tabuľky – ak zmeníte usporiadanie stĺpcov, poprípade pridáte/odstánite ďalšie dimenzie tak vzorčeky Vám vyhodia chybu.

Princíp: Data – Kontingenčná tabuľka ako výpočtový most (Štandardne nastavené dimenzie a fakta, ktorých hodnoty sa menia v čase) – Summary, ktorý vzniká kombináciou funkcií IFERROR a GETPIVOTDATA (ZÍSKATKONTDATA).

Príklad: Dataset, pozostávajúci zo štatistík predajov (Predajca, Množstvo, Cena, Náklady na predaj, Kraj, Mena, Mesiac, Rok … atď.) – Kontingenčná tabuľka (Stĺpce – Mesiace a Roky, Riadky – Kraje, Výpočet – Súčet tržieb) – Summary (Prehľad tržieb podľa mesiacov v konkrétnom roku alebo posledných troch rokoch po kvartáloch).

Aktualizácia reportu: Doplníte údaje (ďalšie riadky) do listu Dataset a aktualizujete kontingenčnú tabuľku, ktorá má Zdroj Dat definovaný ako samotné stĺpce (napr. stĺpce “A:Z). Po aktualizácii tabuľky sa automaticky prepočítajú vzorce IFERROR(GETPIVOTDATA()).

Majme tabuľku:

image

Z nej si pripravíme nasledujúcu kontingenčnú tabuľku (Všimnite si Table/Range: sú samotné stĺpce):

image

Štruktúra bude vyzerať takto:

image

A taktiež vymažeme Total podľa rokov a Grand Total. Výsledná tabuľka bude vyzerať takto:

image

Urobíme si nový list s názvom Summary a pripravíme napr. kvartálny prehľad tržieb podľa rokov a konkrétnych štátov:

image

Použijeme univerzálny GETPIVOTDATA (ZÍSKATKONTDATA) vzorec tak, že v tomto liste sa odkážeme na hodnotu vo vnútri kontingenčnej tabuľky. Všimnite si, že namiesto odkazu typu =’PIVOT’!C6 nám Excel vrátil nasledujúci vzorec:

image

V ňom potrebujeme v poradí určiť: Názov stĺpca, ktorý pivotka počíta v tabuľke; Akákoľvek bunka vrámci kontingenčnej tabuľky; Filtračný stĺpec; Konkrétna hodnota; ….. Filtračný stĺpec; Konkrétna hodnota)

Upravme a aplikujme vzorček podľa obrázka:

image

Teda v počiatku tabuľky bude vzorec vyzerať takto (nahradili sme konkrétne hodnoty odkazmi na ukotvené bunky):

=GETPIVOTDATA("Čiastka zostatok";PIVOT!$B$3;"Rok";$E9;"Měsíc";F$4;"Stát";$D$8)

Avšak pre univerzálnosť vzorček doplníme o funkciu IFERROR() tak,aby v prípade, že ak niektoré hodnoty zatiaľ neexistujú (napríklad 4. kvartál, pozostávajúci z mesiacov 10 11 a 12), tak funkcia vráti 0.

=IFERROR(GETPIVOTDATA("Čiastka zostatok";PIVOT!$B$3;"Rok";$E9;"Měsíc";F$4;"Stát";$D$8);0)

Skopírujeme vzorček a upravíme odkazy na bunky tak, aby ukazovali relevantné hodnoty. List s pivotkou zamkneme, aby s ním nemohol nikto manipulovať a farbu písma mesiacov v tabuľke nad summary zmeníme na bielu. Finálny report vyzerá takto:

image

V prípade, že chceme vytvoriť plnú automatizáciu, pripravíme si makra na vloženie dát a automatický refresh pivotky, ktorý som vysvetľoval v predchádzajúcom článku. Na záver podotknem, že vypracovaný súbor sa stiahne kliknutím na download a dotazy/like čakám kliknutím na facebook logo Winking smile

 

                downloads_normal