MS Excel

Ahoj, řeším následující problém.

V Buňce A1 je vzorec, který má spočítat řádky sloupce B které obsahují pouze text. Sloupec B ale kromě buněk s textem obsahuje také buňky s čísly a hlavně buňky vyplněné vzorcem jako "".

Dřív, když jsem ručně kopíroval data do tohoto sloupce z webu, tak fungoval jednoduchý vzorec =COUNTIF(B:B;"*")

Jenže nyní, když je sloupec B vyplňovaný vzorcem, tak argument "*" ve výše uvedeném vzorci vrací celkový počet řádků.

Zde je vzorec, který je roztažený na všechny řádky sloupce B:

=KDYŽ(Import!B1="";"";KDYŽ(JE.TEXT(Import!B1);Import!B1;HODNOTA(Import!B1)))

Jak je vidět, v bunkách může být text, číslo nebo "".

Poznámka: v listu Import se mi pomocí automatic query importují data z webu.

Můžete někdo poradit? Díky.

Zaslat odpověď >

Strana: 1 2 další »

Roman-Muller(4.12.2017 17:51)#038595

Tady je vzorek. Požadovaný vzorec je v buňce A1.

Všimnul jsem si zvláštního chování. Když jsem nechal vzorec vyplňující sloupec B v několika prvních řádcích, tak se choval správně. Vrátil mi číslo 2 (v jednom řádku je číslo a v jednom text). Když ale vzorec roztáhnu na spoustu řádků, tak začne ukazovat nesmysly...

http://leteckaposta.cz/497721019 citovat

AL(4.12.2017 18:09)#038596

Predpokladám, že miesto hodnoty 1048575 očakávaš výsledok 1, viď zadanie "který má spočítat řádky sloupce B které obsahují pouze text". V takom prípade napr. =ROWS(B:B)-COUNTBLANK(B:B)-COUNT(B:B), eventuálne =COUNTA(B:B)-COUNTIF(B:B; "")-COUNT(B:B)
V druhom poste ale píšeš, že výsledok má byť 2, tzn. do výsledku zrejme započítavaš bunky B2 a B3. V tom prípade: =ROWS(B:B)-COUNTBLANK(B:B), eventuálne =COUNTA(B:B)-COUNTIF(B:B; "")citovat

Roman-Muller(4.12.2017 20:43)#038604

Chtěl jsem to dle zadání, tzn výsledek měl být 1 (započítane buňky pouze s textem). Díky za řešení i s dalšími variantami.

Vůbec mě nenapadlo, že bych to mohl vyřešit přes další varianty funkce COUNT.

Myslím, že ale budu mít problém s implementací tohoto řešení do realného souboru... v rámci zjednodušení jsem totiž celý příklad zjedodušil. Ve skutečnosti je ale vzorec, který jsem ve vzorku uvedl jako =COUNTIFS(B:B;"*") COUNTIFS s cca několika podmínkami, příklad:

=COUNTIFS(B:B;"*";C:C;D1;E:E;F1;G:G;H1) a ještě několik dalších sloupců a buněk. V buňkách D1, F1, H1 atd si uživatel souboru může pomocí rozbalovacího seznamu vybírát různé hodnoty, funguje to jako filtr. Vzorec pak vrací hodnoty dle toho co uživatel navolí (např. zvolí že se mají počítat pouze zakázky z roku 2017, měsíc 12, pouze uzavřené zakázky a z konkrétního servisu).

Když od tohoto značně omezeného počtu odečtu celkový COUNTBLANK a COUNT tak dostanu pochopitelně zápornou hodnotu.

Nějaký nápad co s tím?citovat

marjankaj(4.12.2017 21:31)#038605

No ja by som ti poradil. Zaregistruj sa a potom tu môžeš priložiť súbor priamo, prípadne zazipovaný.citovat

AL(4.12.2017 21:47)#038606

Ako píše marjankaj, bodla by príloha so vzorom, ako by to cca malo vyzerať / čo by to malo počítať. Nejak sa mi nechce veštiť. Dosť ma nebaví niečo vymýšľať, keď OP následne príde s tým, že riešenie je síce fajn, ale reálna situácia, v ktorej sa to má aplikovať, je "trochu iná" :)

len poznámka ku countif: nie vždy funguje korektne, resp. občas sa mi stane, že mi nedá výsledok, ktorý si predstavujem. Pri viackriteriálnom sčítavaní by som doporučil zvážiť použitie maticových vzorcov, eventuálne skalárneho súčinu. Príklad použitia funkcie skalárny súčin, ktorý by mohol viesť ku kýženému výsledku (syntax je snáď jasná, takže bude potrebné upraviť akurát podmienky):=SUMPRODUCT((YEAR(D5:D9)=2017)*(MONTH(D5:D9)=12)*((E5:E9)<>"")*NOT(ISNUMBER(E5:E9)))citovat

Roman-Muller(4.12.2017 22:37)#038613

S tím nekompletním vzorkem to chápu, orininální soubor ale poskytnout nemůžu, protože obsahuje citlivá data klientů. Proto ty tajnosti. Skalární součin mě napadl taky, ale zatím sem s ním ještě nikdy nedělal. Něco sem si ale dneska nastudoval tak to zítra zkusím dát v kanclu do kupy. Když to nebude fungovat, dám sem vzorek s podmínkami přesně tak jak to má být.citovat

AL(4.12.2017 22:47)#038615

Ale čo sa týka vzorového súboru, tam je predsa dôležitá len jeho štruktúra. Stačí tým pádom iba pár riadkov, reálne údaje nahradiť fiktívnymi. To prepáč, ale argument s citlivými dátami v tomto prípade skutočne ťažko obstojí.
Pozri sa na ten príklad skalárneho súčinu, pri troche snahy si vzorec modifikovať dokážeš.citovat

Diphal(5.12.2017 12:19)#038631

Poznámka: já jsem OP.

A nezvládnul jsem to... ten skalární součin použitý jako filtr funguje pokud je obsahem vyhledávaných buňek konkrétní vyhledávaný text nebo číslo. Nefunguje to ale pro "*", což používám v případě pokud nechci daný sloupec filtrovat. U countifu to funguje, ale ve skalárním součinu ne.

V příloze je orignální soubor, osekaný o citlivá data. Architektura zůstala beze změny.

Jedná se vzorec v buňkách P51:P53 (původní countif verze) a ve sloupci Q je pak verze se skalárním součinem. Vzorec má spočítat výskyt textu ve sloupci 'Report Data'!AA:AA, přičemž má brát ohled na ostatní buňky ve vzorci (měsíc, rok a zvolená země, model a servis). Správný výsledek se vzorovými daty má být 1 (pouze jeden řádek obsahuje ve sloupci 'Report Data'!AA:AA text: "Nemá zájem").citovat

AL(5.12.2017 12:36)#038632

nahraj sem ten subor (musis zazipovat), potom sa na to snad pozriem. Zatial nemam dovod neverit, ze by skalarny sucin nefungoval..citovat

Diphal(5.12.2017 13:23)#038634

Pardon, nevšiml jsem si, že se nezazipovaná příloha nenahrála. Tak snad teď.

Jinak já netvrdím, že skalární součin nepůjde použít, ale že ten můj nefunguje

EDIT: Vzhledem k tomu, že přílohu pořád nevidím, tak dávám ještě link:

http://leteckaposta.cz/792640342 citovat

Strana: 1 2 další »

Téma: Countif text

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje