Filtrace sčítaných položek

rejze · 8. 7. 2014 08:54:39

Zdravím. Řeším výpis prodeje druhů zboží kde je v tabulce kompletní seznam zboží a u něho se sčítá počet kusů. Data si to bere z jiného listu,kde je seznam faktur v řádcích. Každý řádek obsahuje komplet informace o dané faktuře. Pokud bych nechal kompletní součet ze všech faktur,stačí mi na to funkce SUMIF ,která sečte ze sloupce s počty kusů jen to s odpovídajícím názvem.Těch sloupců s počty kusů je 40 jako řádků na faktuře. Když chci ale filtrovat ještě podle data vystavení,funkce SUMIF už mi na to nestačí,nebo nevím jak to napsat,aby sčítala podle dvou oblastí a dvou kritérií.Vzorec pouze podle názvu zboží =SUMIF(data.O$2:O$500;$A5;data.P$2:P$500)To je rozkopírováno v oblasti a nakonci se jednotlivé řádky sečtou ke každému zboží. Nakonec jsem použil maticový vzorec =SUM((data.$G$2:$G$500>=$FV$1)*(data.$G$2:$G$500<=$FV$2)*(data.O$2:O$500=$A1)*data.P$2:P$500),který dělá přesně to co potřebuju,ale tabulka se dlouho načítá,protože ji volám makrem,které mi aktualizuje odkazy na externí data. Pokud někoho napadá řešení,které tolik nezatěžuje jako matice budu vděčen.
Díky.

neutr · 8. 7. 2014 09:12:10

Tohle řeší funkce SUMIFS. Zadávají se dva úseky pro vyhodnocení, ale myslím že by šlo i více.
Jiným řešením by byl pomocný sloupec a v tom jakákoliv kombinace dotazu například zdvojené IF, VLOOKUP, ale i LOOKUP a další.
Také by to bezpečně uměly maticové vzorce. To bych ale musel najít. Možná že to napíše lp., Který s nimi dělá nejvíc. Když byste měl zájem - najdu to. Ale je to tady někdy ve starších příspěvcích.

Editoval neutr (8. 7. 2014 09:13:35)

rejze · 8. 7. 2014 13:22:42

Maticový vzorec jsem právě použil a funguje,ale jak jsem psal je to asi moc náročný když se při otvírání aktualizují makrem linky a k tomu se propočítává matice. Okno dokonce chvíli neodpovídá a i když to funguje je to nepříjemný. SUMIFS zkusím to by mohlo být ono.

neutr · 8. 7. 2014 13:48:02

Já sice vím, že jste to psal o těch maticových vzorcích se základem SUM, ale SUMIF má také verzi jako maticový. Bohu žel pokud tam máte hodně načítání je možné že se to 's maticemi hádá'. A možná nejen s maticemi.
Matice (obecně vzorec) se provede okamžitě. Problém je možná v tom, že se to spočte a pak dojde k aktualizaci - a přepočítává se to a nanovo dokud všechny aktualizace neproběhnou. Tedy vždy po aktualizaci. Takže nedostatek je možná v koncepci.
Pokud používáte makro doporučuji spouštět nejdříve aktualizace a pak makrem vložit vzorec. To by se sekat nemělo. ale bude to chvilku trvat podle přístupnosti zdrojů.

ludviktrnka · 8. 7. 2014 14:10:34

Nevím zda to bude mít nějaký efekt, ale můžete zkusit sumproduct, třeba se to ve vztahu k aktualizaci bude chovat jinak. =SUMPRODUCT(data.$G$2:$G$500>=$FV$1;data.$G$2:$G$500<=$FV$2;data.O$2:O$500=$A1;data.P$2:P$500)

rejze · 8. 7. 2014 14:21:42

To by snad taky šlo. Jen je otázka jak se zachová makrem vložený vzorec v celé oblasti. Když ho napíšu do levé horní buňky a pak rozkopíruju,počítám s tim,že nedolarovaný buňky se mi o sloupec či řádek upraví. Stane se to i když to udělám makrem? Zkusím i návrh p."ludviktrnka" Dám vědět co to udělalo. Zatim děkuju.

Editoval rejze (8. 7. 2014 14:25:11)

neutr · 8. 7. 2014 14:41:38

Tady jsou základy pro makra se vzorecem (obyčejný) a pak také matice. Já je používám jak start v buňce, kde zrovna stojí kurzor, ale napsat 'natvrdo' start třeba v A1 je snadnější. Já je mám originál ve formátu funkce která se dá zavolat ze sešitu například '=DejVzorec().'
Nevím sice jak voláte aktualizaci (může to být makro, ale může to být i automat aktualizující DDE [OLE]). Pokud tedy opravdu spouštíte makra, zadejte je na událost před, nebo pří otevření sešitu. Upravené makro se vzorcem spusťte až v následující události.
Ještě doporučuji vzorec (matici) hned našíst a vložit zpět jen jako hodnotu - to jde také makrem. Takže takhle bych to doporučil.

Sub DejVzorec
Dim oCell As Object 
oCell = ThisComponent.CurrentController.getSelection() 
With oCell.RangeAddress 
SH = .Sheet 
SC = .StartColumn 
SR = .StartRow
End With 
oSheet = ThisComponent.Sheets(SH) 
oCell = oSheet.GetCellbyPosition(SC, SR)
oCell.Formula = "=Address(ROW();COLUMN();4)"
End Sub

Sub DejMatici
Dim oCell As Object 
oCell = ThisComponent.CurrentController.getSelection() 
With oCell.RangeAddress 
SH = .Sheet 
SC = .StartColumn 
SR = .StartRow
End With 
oSheet = ThisComponent.Sheets(SH)
Dim sText as string
sText =  "=SUM(G2:G500*H2:H500)"
oRange = oSheet.getCellRangeByPosition(SC, SR, SC, SR)
oRange.setArrayFormula(sText) 
End Sub

lp. · 8. 7. 2014 21:58:15

Na podobné úlohy existuje kontingenční tabulka. Co na ní nevyhovuje?

neutr · 9. 7. 2014 07:46:29

Když budete dávat vzorec makrem které jsem uvedl výše, tak úseky dolarovat musíte. Existuje ale řešení právě pro tyto má makra, že se načte do kurzoru úsek (a nikoliv jen jediná buňka) a tam se to vykopíruje naráz - sice jednotlivě pro každou buňku, ale dolarovat se vzorec nemusí.
Přepíše se jednoduše buď přímo v makru pro vzorec (matici) kód, nebo se primárně spustí jiné makro, které bude iterovat buňky v úseku a do nich vkládat vzorec voláním toho originál vzorce.

Také mne napadlo, že si možná neuvědomujete celý potenciál vzorců typu SUMIF, nebo SUMIFS a podobně. Představte si, že to děláte právě proto, že existuje více řádků splňujících stejná kriteria. Vzorec se nemusí použít na všechny takto 'shodné' případy.
Stačí, aby byl výsledek sečten jen pro jeden každý kvalitativní druh podmínky. Při tom může existovat problém neprázdného průniku množin s různými podmínkami.
Pokud se tedy podmínky nepřekrývají (nemají společný -správně-nemají-neprázdný-, nebo mají-jen-prázdný průnik), lze vybrat tak, aby všechny různé dávaly součet za celý soubor 1:1. Tedy součet za soubor se 'rozpadá' na jednotlivé podmínky asi jako když vytvoříme kontingenční tabulku. To je řekněme ideální stav.
Existují ale chyby, nebo i potřeby kdy se kriteria překrývají potom součet různých podmínek, je větší, nežli prostý součet položek seznamu. Pokud je součet menší, tak je to evidentně jen chyba.

Píšete že systém je používán nad fakturami. Tam se může každá maličkost vymstít. Když do každého řádku vložíte vzorec na skupinový součet a tento sloupec sečtete, tak to bude fungovat jen jako detekce chyby. Konkrétně pokud součet skupinových součtů je větší nežli prostý součet, je mezi položkami nejspíš duplicita - chyba a podobně. Tohle by potřeboval analytik FÚ.
Jestli to chcete použít k jiným účelům tak se bez kontroly konzistence neobejdete. Lze to ale udělat poměrně snadno. Spustíte nejprve COUNTIF na podmínky - zjistíte kolik je různých, nebo otestujete pomocí LOOKUP, VLOOKUP, zdvojených dotazů typu IF ap. Tak můžete získat odkazy na první stejnou kvalitativní položku. Do té pak vložíte ten SUMIFS a podobně. Pak už prostý součet souhlasí s tím součtem kvalitativních kategorií.

Pravdu má také 'lp.'. Jenomže aby toto kontingenční tabulka splnila stejně se jí musí zadat kriteria s podobou nějakého vzorce, který to vyhodnotí. Na to se užívá pomocný sloupec. Takže je to zřejmě dobré řešení, ale nedá se dobře automatizovat, respektive jakmile to zautomatizujeme, tak vlastně Kontingenční tabulku nepotřebujeme.

Překontrolovat bychom měli vždy když nás nezajímá 'jenom některá položka'. Pokud se jedná o podvojné účetnictví a neděláte kontrolu na obsahy - nazdar hodiny. Ona ale stačí i nedostatečná kontrola pro jednoduché účetnictví. Tedy faktem je, že si účetní musí poradit vždy a tak informativní kontrola letmým způsobem (nezávazně) takto dělána být samozřejmě může.

Editoval neutr (9. 7. 2014 07:57:54)

lp. · 10. 7. 2014 06:02:40

Maticový vzorec

=SUM((data.$G$2:$G$500>=$FV$1)*(data.$G$2:$G$500<=$FV$2)*(data.O$2:O$500=$A1)*data.P$2:P$500)

lze přepsat na rychlejší

=SUMIFS(data.P$2:P$500;data.$G$2:$G$500;">="&$FV$1;data.$G$2:$G$500;"<="&$FV$2;data.O$2:O$500;$A1)

ale kt je ještě o dost rychlejší.

Dále:

Přepisy maticových vzorců pomocí SUMPRODUCT jsou skoro stejne pomalé jako původní maticový vzorec.

Pokud používáš větší úpravu sešitu makrem, tak zakaž automatickou aktualizaci, případně zakaž aktualizaci obrazovky. Např.:

myDoc = ThisComponent
myDoc.lockControllers()              ' zablokuje aktualizaci obrazovky
myDoc.addActionLock()                ' zablokuje aktualizaci interních objektů
' kód
myDoc.removeActionLock()
myDoc.unlockControllers()

případně jen přepočet se nastavuje

Dim bNastaveniPrepoctu as boolean
bNastaveniPrepoctu = thisComponent.IsAutomaticCalculationEnabled
thisComponent.enableAutomaticCalculation(False)
' kód
thisComponent.enableAutomaticCalculation(bNastaveniPrepoctu)       ' obnové původní nastavení přepočtu

Nakonec ještě jeden tip mimo dotaz, mohl by se hodit:
Pokud máš kt s datumem v poli řádků, vyber (v kt) některý datum a aplikuj data/seskupení souhrn/seskupit.

@neutr - poznámky k použití kt vycházejí pro mne poněkud netypického způsobu použití. Lze i
jinak. Tvrzení že po automatizaci kt nepotřebujeme je pravdivé jen pokud nám nevadí řádově delší odezva při použití vzorců. Automatizace kt makrem je možná.

Editoval lp. (10. 7. 2014 06:33:54)

rejze · 17. 7. 2014 23:11:47

Dik za namety. Ted jsem pres leto u PC pomerne vzacne,takze az k tomu vlezu vyzkousim co pujde. Kazdopadne kontingencni tabulka na to nesla sestavit. Tu jsem zkousel jako prvni. Problem je ten,ze potrebuju udelat soucet vsech druhu zbozi za dane obdobi,ale data si to bere ze seznamu faktur kde je pod polozkou kupr. zbozi1 pokazde neco jineho,protoze kazda faktura je jina. Proto si to scitam funkci SUMIF a dalsi co pak potrebuju,aby to secetlo prave jen z faktur za dane obdobi. Na to jsem KT proste nedokazal vymyslet.
EDIT: Jen pro informaci. Není to účetní program. Jen tabulky pro fakturaci a s odpočtem skladu a to co řeším teď jsou informativní statistiky prodeje jednotlivých druhů zboží za dané období. Oficiální účetní software dostává data na základě tištěných faktur a tam se případná chyba nepromítne. Každopádně dík za upozornění. Zrovna u toho sedím a testuji Vaše náměty.

Editoval rejze (21. 7. 2014 15:28:21)

rejze · 22. 7. 2014 07:52:29

Zdravim. Zatím jsem zkusil pouze možnost p."ludviktrnka" Je to SUMPRODUCT bez matice a je pomalejší než původní SUM s maticí. Nicméně ukázalo mi to,že tudy cesta nevede. Budu se jak psal p."neutr" muset vyhnout aktualizaci a současnému přepočítávání. Aktualizaci provádím makrem Linkaktu,které je řešeno zde

http://forum.openoffice.cz/viewtopic.php?id=3669

Jiným způsobem mi aktualizace linků nefungovaly pokud jsem otvíral sešity makrem. Pochopitelně v sešitě se statistikou prodejů se sbíhají tři cesty a tudíž je makro voláno třikrát. Nejschůdnější se mi tedy jeví zakázání přepočtu během aktualizace.

neutr · 22. 7. 2014 08:48:28

Možná by nebylo od věci načíst všechny tři druhy někam do pomocného listu a pak je makrem uspořádat na stejný 'formát' - stejné kolonky pod sebe. přidat jen možná sloupec s poznámkou jaký typ faktury to byl - respektive identifikace zdroje faktury.
Následně vytvořit kontingenční tabulku, nebo vykuchat makry ap.

rejze · 26. 7. 2014 08:47:51

Zdravim. Zkoušel jsem podle p."Ip" vložit do makra ten kus kódu který zakazuje přepočítávat,ale nic se nezmění.Otázka tedy je jestli jsem to použil správně,nebo jestli ten problém způsobují samotné vzorce nezávisle na aktualizaci linků. Celý kus kódu do kterého jsem to dával.

Sub Statistiky 'otevřít statistiky

dim document as object, dokument as object, list as object
dim bunka_slozka as object, bunka_soubor as object
dim Nastav_Prepocet as boolean
passw
    if Cerbpas=Cerbpasin then
    'panfce="faktura"
    'endif
    'if panfce="faktura" then
'passw
'passctrl  
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dokument=stardesktop.currentcomponent
list=dokument.sheets(0)
bunka_slozka=list.GetCellByPosition(8,35)'I36
Dcesta=trim(bunka_slozka.string)
bunka_soubor=list.GetCellByPosition(9,2)'J3
Dsoubor=trim(bunka_soubor.string)
ovladani.otevri (""+Dcesta+""+Dsoubor+"")
Nastav_Prepocet = thisComponent.IsAutomaticCalculationEnabled
thisComponent.enableAutomaticCalculation(False)
ovladani.LinkAktu (Dcesta+"databáze/kontakty.ods","odběratelé","odběratelé")
ovladani.LinkAktu (Dcesta+"databáze/zboží.ods","zboží","data")
ovladani.LinkAktu (Dcesta+"databáze/faktury.ods","data","data")
'wait 2000
thisComponent.enableAutomaticCalculation(Nastav_Prepocet)
ovladani.vyberlb ("data","B2")
goto konec
end if
msgbox("Zadejte heslo pro "+Fakt,0,"CHYBNÉ PŘIHLÁŠENÍ")
konec:
End Sub

neutr · 26. 7. 2014 09:44:17

Netestoval jsem to - musel bych si něco setrojit pro otestování. Ale podle toho, co píše 'lp.' tak by to mělo být zřejmě buď před deklarací document, nebo těsně za ní. Nejméně asi před deklarací listu, ale tahle vlastnost se nastavuje skutečně pro celý dokument - takže to možné chce jinak (možná i další) deklaraci.
Nejspíš ty dva řádky co uvádí 'lp.' musí být za sebou a pak teprve se to uzamkne (pokud to funguje). Vy to zamykáte až po akci.
Ten kód se má také provádět v podstatě pod dotazem - je - li odemčen přepočet - tak zamknout. Mělo by tam být něco jako IF.
Další věcí je, že by se to pak mělo zase povolit - místo 'false' dát 'true'. Otestoval bych to, ale vytvářet adresáře a složky jen pro test - to se mi nechce.
Mohu otestovat jen kousek kódu podle lp. Takže když to nebude ono mrknu na to.

neutr · 26. 7. 2014 09:57:56

Oprava : Udělal jsem ořezanou verzi jen na test zastavení přepočtu a funguje to :

Sub Statistiky 'otevřít statistiky
dim document as object, dokument as object, list as object
dim bunka_slozka as object, bunka_soubor as object
dim Nastav_Prepocet as boolean
document= ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dokument=stardesktop.currentcomponent
list=dokument.sheets(0)
bunka1=list.GetCellByPosition(0,0)'I36
bunka2=list.GetCellByPosition(0,1)'J3
Nastav_Prepocet = thisComponent.IsAutomaticCalculationEnabled
thisComponent.enableAutomaticCalculation(True)
thisComponent.enableAutomaticCalculation(Nastav_Prepocet)
bunka2.Value = bunka1.Value*4
print "konec"
End Sub

problém je v té deklarované hodnotě - false nezakazuje - ale povoluje :-)
Správně je to takto :
thisComponent.enableAutomaticCalculation(True)

PS Na konci byste měl přepočet zase povolit například takto :
deklarace ZASTAVIT PŘEPOČET - do prostoru deklarací - na začátek
Zastav_Prepocet = thisComponent.IsAutomaticCalculationEnabled
thisComponent.enableAutomaticCalculation(True)
deklarace ZASTAVIT PŘEPOČET - konec deklarace
thisComponent.enableAutomaticCalculation(Zastav_Prepocet) 'Tohle do kódu
....je zasteveno přepočítávání
kód...
....povolit přepočítávání
deklarace POVOLIT PŘEPOČET - do prostoru deklarací - na začátek
Povolit_Prepocet = thisComponent.IsAutomaticCalculationEnabled 'Tady možná patří Disabled
thisComponent.enableAutomaticCalculation(False)
deklarace POVOLIT PŘEPOČET - konec deklarace
thisComponent.enableAutomaticCalculation(Povolit_Prepocet) 'tohle do kódu

Editoval neutr (26. 7. 2014 10:18:40)

neutr · 26. 7. 2014 10:29:46

Tak mi napadlo, že to není úplně správné - dávat zákazy do kódu výkonného makra. Lépe je postavit specializované makro, nebo funkci 'ZakazatPrepocet' a také 'PovolitPrepocet'. Měla by stačit i jediné - kterému se předává parametr True/False, ale když uděláte dvě specializované, tak víte okamžitě co děláte - jestli zakazujete, nebo povolujete a parametr nepotřebují.

rejze · 26. 7. 2014 17:54:41

Ok zkusim dát true,nejspiš i udělám to makro zvlášť pro možnost volat ho i odjinud. Nicméně to umístění jsem zvolil dobře. Takhle by mi to zakázalo přepočítávat ovládací panel a sešit statistiky,který z toho panelu otvírám tlačítkem by to neovlivnilo. Ještě si s tim pohraju. Dík za nasměrování.

OpenOffice.cz - web věnovaný kancelářským balíkům OpenOffice.org

#1 8. 7. 2014 08:54:39

Filtrace sčítaných položek

#2 8. 7. 2014 09:12:10

Re: Filtrace sčítaných položek

#3 8. 7. 2014 13:22:42

Re: Filtrace sčítaných položek

#4 8. 7. 2014 13:48:02

Re: Filtrace sčítaných položek

#5 8. 7. 2014 14:10:34

Re: Filtrace sčítaných položek

#6 8. 7. 2014 14:21:42

Re: Filtrace sčítaných položek

#7 8. 7. 2014 14:41:38

Re: Filtrace sčítaných položek

#8 8. 7. 2014 21:58:15

Re: Filtrace sčítaných položek

#9 9. 7. 2014 07:46:29

Re: Filtrace sčítaných položek

#10 10. 7. 2014 06:02:40

Re: Filtrace sčítaných položek

#11 17. 7. 2014 23:11:47

Re: Filtrace sčítaných položek

#12 22. 7. 2014 07:52:29

Re: Filtrace sčítaných položek

#13 22. 7. 2014 08:48:28

Re: Filtrace sčítaných položek

#14 26. 7. 2014 08:47:51

Re: Filtrace sčítaných položek

#15 26. 7. 2014 09:44:17

Re: Filtrace sčítaných položek

#16 26. 7. 2014 09:57:56

Re: Filtrace sčítaných položek

#17 26. 7. 2014 10:29:46

Re: Filtrace sčítaných položek

#18 26. 7. 2014 17:54:41

Re: Filtrace sčítaných položek

Zápatí