=DÉLKA(A1)-DÉLKA(DOSADIT(A1;",";""))+1citovat
excel, návod, tipy, funkce, makra, vba, tabulky, šablony
Váš průvodce programem Microsoft Excel a programováním maker ve VBA
< návrat zpět
Téma: Počet hodnot v buňce mezi čárkami
MS Excel
Téma: Počet hodnot v buňce mezi čárkami 
Zaslal/a martinek.v 7.2.2016 20:15
Zdravím vás po delší době. Prosím vás, poradili byste mi vzorec, který by spočítal počet hodnot v buňce, které jsou odděleny nějakým znakem, např. čárkou?
Př. V buňce A1 je: aaa, bbb, ccc a v buňce B1 by mělo být číslo 3. V buňce A2 je: x,jj-y,z,g,hu a v buňce B2 je číslo 5.
Doufám, že jsem to napsal srozumitelně, děkuji za jakoukoli radu, Marťas
Jen tak z legrace.
Vzorec výše neřeší následující stavy:
a,b,c,d - vrací 4
a,b,,,c,d,e - vrací 7
a,b,,c, - vrací 5
a,b,c,d, - vrací 5
,a,b,c,d,,,e - vrací 8
tedy bere prázdnou hodnotu jako hodnotu. Pokud to nechcete (a mně to zajímalo), mělo by fungovat:
B - pomocný sloupec=KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"")C - výsledek=DÉLKA(B1)-DÉLKA(DOSADIT(B1;",";""))+1-((DÉLKA(B1)-DÉLKA(DOSADIT(B1;"#NULL#";"")))/6)
Popř. bez pomocného sloupce:=DÉLKA(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";""))-DÉLKA(DOSADIT(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"");",";""))+1-((DÉLKA(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";""))-DÉLKA(DOSADIT(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"");"#NULL#";"")))/6)
Vrací:
4
5
3
4
5citovat
Vzorec výše neřeší následující stavy:
a,b,c,d - vrací 4
a,b,,,c,d,e - vrací 7
a,b,,c, - vrací 5
a,b,c,d, - vrací 5
,a,b,c,d,,,e - vrací 8
tedy bere prázdnou hodnotu jako hodnotu. Pokud to nechcete (a mně to zajímalo), mělo by fungovat:
B - pomocný sloupec=KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"")C - výsledek=DÉLKA(B1)-DÉLKA(DOSADIT(B1;",";""))+1-((DÉLKA(B1)-DÉLKA(DOSADIT(B1;"#NULL#";"")))/6)
Popř. bez pomocného sloupce:=DÉLKA(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";""))-DÉLKA(DOSADIT(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"");",";""))+1-((DÉLKA(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";""))-DÉLKA(DOSADIT(KDYŽ(ZLEVA(A1;1)=",";"#NULL#";"")&DOSADIT(DOSADIT(A1;",,";",#NULL#,");",,";",#NULL#,")&KDYŽ(ZPRAVA(A1;1)=",";"#NULL#";"");"#NULL#";"")))/6)
Vrací:
4
5
3
4
5citovat
Vyzkoušel jsem tu první možnost a úplně postačí, za ty další možnosti MOC DĚKUJI, budou se hodit také. Je neuvěřitelné, jakými způsoby to lze udělat!!! Velký obdiv, Martincitovat
Je samozřejmě věcí uživatele, jak chce, aby byla chápána přípustnost a role "nadbytečných" oddělovačů. Po mém soudu by bylo dobré držet se toho, jak jim standardně rozumí Excel. Toto chování si lze snadno ověřit pomocí služby Data / Text do sloupců. Z něj jednoznačně plyne, že počet prvků, do kterých Excel rozdělí text s oddělovači, přesně odpovídá úvodnímu vzorci vlákna, tedy o jeden víc, než je počet oddělovačů. Prvky, vytvořené v rámci této služby "nadbytečnými" oddělovači, vzniknou, jen mají nulovou délku.
Úplně stejně se vůči oddělovačům chová VBA ve své funkci Split, která ze stringu s oddělovači vytváří pole. Dalším příkladem analogického chování je vynechání parametrů v seznamu parametrů pomocí opakování oddělovače.
Odlišné vyhodnocení oddělovačů pro určení počtu prvků je sice možné, bez skutečně vážného důvodu bych ho ale nedoporučoval.
Edit: služba Data / Text do sloupců sice umožňuje volbu "posloupnost oddělovačů jako jeden", jde ale o nastavení, které si musíme vyžádat a které se tím liší od standardu pro vyhodnocení oddělovačů. Aniž bych zkoumal složitější vzorce od elCHa, předpokládám, že směřují k tomuto způsobu vyhodnocení oddělovačů.citovat
Úplně stejně se vůči oddělovačům chová VBA ve své funkci Split, která ze stringu s oddělovači vytváří pole. Dalším příkladem analogického chování je vynechání parametrů v seznamu parametrů pomocí opakování oddělovače.
Odlišné vyhodnocení oddělovačů pro určení počtu prvků je sice možné, bez skutečně vážného důvodu bych ho ale nedoporučoval.
Edit: služba Data / Text do sloupců sice umožňuje volbu "posloupnost oddělovačů jako jeden", jde ale o nastavení, které si musíme vyžádat a které se tím liší od standardu pro vyhodnocení oddělovačů. Aniž bych zkoumal složitější vzorce od elCHa, předpokládám, že směřují k tomuto způsobu vyhodnocení oddělovačů.citovat
Ahoj! Vzorec poloviční velikosti.=(DÉLKA(DOSADIT(DOSADIT(A1;",";"|,|");",||";""))-DÉLKA(DOSADIT(DOSADIT(DOSADIT(A1;",";"|,|");",||";"");"|,|";"")))/3-(ZLEVA(DOSADIT(DOSADIT(A1;",";"|,|");",||";"");3)="|,|")-(ZPRAVA(DOSADIT(DOSADIT(A1;",";"|,|");",||";"");3)="|,|")+1citovat
Moc za vše děkuji, nešlo by, když už mám počet hodnot mezi čárkami, zobrazit určitou hodnotu ve vedlejší buňce, např. třetí hodnotu?
Př. A1 = "Ses,des,res,res,pes" a ve vedlejší buňce vrací "res" či pátou hodnotu - vrací "pes".
Půjde to udělat vzorcem?
Děkuji moc, M.citovat
Př. A1 = "Ses,des,res,res,pes" a ve vedlejší buňce vrací "res" či pátou hodnotu - vrací "pes".
Půjde to udělat vzorcem?
Děkuji moc, M.citovat
Sám vidíte, že vzorce mohou být dost košaté. Pokud byste chtěl prvek ze seznamu s konstantním pořadovým číslem, asi by vzorec ještě ušel, kdybyste měl na mysli proměnnou polohu prvku, tak do toho bych se vzorem asi nešel. To už je na můj vkus úloha pro uživatelskou funkci, napsanou ve VBA. Taková UDF by byla prostičká, akorát nevím, jestli vám řešení VBA vyhovuje.
UDF může vypadat např. takto:
Function Prvek(Kec As String, Sep As String, Poradi As Integer) As String
Dim Pole
Poradi = Poradi - 1
Pole = Split(Kec, Sep)
If IsArray(Pole) Then
If Poradi > UBound(Pole) Then Exit Function
Prvek = Pole(Poradi)
End If
End Function
citovat
UDF může vypadat např. takto:
Function Prvek(Kec As String, Sep As String, Poradi As Integer) As String
Dim Pole
Poradi = Poradi - 1
Pole = Split(Kec, Sep)
If IsArray(Pole) Then
If Poradi > UBound(Pole) Then Exit Function
Prvek = Pole(Poradi)
End If
End Function
citovat
@martinek.v
Ano lze to vzorcem. Např. nějak takto. V C1 je pořadí hodnoty, kterou hledáte.=ČÁST(A1;IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1-1));0)+1;IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1))-IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1-1));0)-1;1000))citovat
Ano lze to vzorcem. Např. nějak takto. V C1 je pořadí hodnoty, kterou hledáte.=ČÁST(A1;IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1-1));0)+1;IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1))-IFERROR(NAJÍT("##";DOSADIT(A1;",";"##";C1-1));0)-1;1000))citovat
Děkuji mockrát, funguje :) Jste neskuteční, proč já to nechápu a nedokážu sestavit? Mám se co učit...M.citovat
Nedávno som posielal na iné fórum zaujímavý variant pre N-té slovo.
=TRIM(RIGHT(LEFT(SUBSTITUTE(B1;B2;REPT(" ";LEN(B1)));B3*LEN(B1));LEN(B1)))
=PROČISTIT(ZPRAVA(ZLEVA(DOSADIT(B1;B2;OPAKOVAT(" ";DÉLKA(B1)));B3*DÉLKA(B1));DÉLKA(B1)))
Od eLCHa samozrejme funguje tiež. Prihodil som ho do súboru s úpravou voliteľného oddeľovača.
=TRIM(RIGHT(LEFT(SUBSTITUTE(B1;B2;REPT(" ";LEN(B1)));B3*LEN(B1));LEN(B1)))
=PROČISTIT(ZPRAVA(ZLEVA(DOSADIT(B1;B2;OPAKOVAT(" ";DÉLKA(B1)));B3*DÉLKA(B1));DÉLKA(B1)))
Od eLCHa samozrejme funguje tiež. Prihodil som ho do súboru s úpravou voliteľného oddeľovača.
Příloha: 
30084_n-te-slovo-v-poradi.zip (7kB, staženo 33x)
citovat30084_n-te-slovo-v-poradi.zip (7kB, staženo 33x)Uživatelské menu
Nejste přihlášen(a)
\n
\nMenu
Formulář Faktura
Formulář Faktura IV
Oblíbený formulář Faktura byl vylepšen a rozšířen.
Více se dočtete zde.
Helios iNuvio
Používáte podnikový systém Helios iNuvio? Potřebujete pomoci se správou nebo vyvinout SQL proceduru? Více informací naleznete na stránce Helios iNuvio.
RSS nejnovější články