neděle 28. září 2014

Pokladna v Excelu

Příklad

Na kurzech pro začátečníky občas řešíme, jak v Excelu vytvořit něco jako pokladní knihu, přehled položek na účtu, skladovou evidenci... zkrátka tabulku s příjmy, výdaji a automaticky spočteným aktuálním stavem.

Návod 

Není nic snadnějšího. Data, příjmy a výdaje se ručně vypisují, úkolem který řešíme je výpočet stavu. Do první buňky stavu napíšu:
  • =B2
    protože první řádek stavu je vlastně počáteční stav.

Do další buňky pak napíšu a dolů roztáhnu:
  • =D2+B3-C3
    protože každý další řádek se spočte tak, že se k řádku nad buňkou připočte příjem a odečte se výdej. Protože v praxi bude vždy v řádku zapsaný jen výdej nebo příjem, provede se buď přičtení nebo odečtení.



čtvrtek 25. září 2014

Zajímavost s porovnáváním číselných kódů

V tabulce máte tato dvě čísla naformátovaná jako text:
  • 1111111111111119999
  • 1111111111111110000
Myslíte, že tato čísla nejsou stejná? Excel je jiného názoru.


Vida, Excel obě čísla vyhodnotil jako duplicitní.
Stejně se zachová třeba funkce COUNTIF (nebo SUMIF) - tvrdí, že první číslo je v tabulce použité dvakrát.


To už nám může způsobit docela problém - protože funkce v našem případě bude házet naprosto nesmyslné hodnoty a je těžké si toho všimnou.
Je zajímavé, že např. funkce SVYHLEDAT/VLOOKUP nebo POZVYHLEDAT/MATCH fungují správně (na obrázku MATCH ukazuje, že hledané je na druhé pozici)....


...a současně přímé porovnání výsledků také správně ukáže, že hodnoty jsou odlišné.


Pro COUNTIF Excel očividně sleduje jen prvních 15 znaků (pokud se čísla liší na prvních 15 znacích, tak se problém neprojeví). 
Uvedený problém vypadá jako maličkost, ale např. u kódů zboží, kde se běžně jedná o dlouhé číselné řetězce, může vést k naprosto nemyslným výsledkům - o to zákeřnějším, že se projeví jen na některých položkách.
Náprava je přitom snadná - stačí vytvořit a pro porovnání použít (třeba funkcí CONCATENATE) pomocný sloupec, kde budou čísla změněna na:
  • 1111111111111119999x
  • 1111111111111110000x
a vše funguje tak jak má.