MS Excel

Helou,

potreboval by som vybrať náhodné číslo z množiny buniek v jednom stĺpci. V tom stĺpci sa nachádzajú aj 0 ktoré potrebujem ignorovať. Tzn. ak mám v stĺpci čísla 1,15,0,0,0,6 tak chcem aby vybralo náhodné číslo ale okrem 0. Skúšal som RAND, RANDBETWEEN, INDEX, ale už som v koncoch. Ďakujem za pomoc

Zaslat odpověď >

elninoslov(15.11.2022 7:55)#053851

Jednoduchší vzorec ma zatiaľ nenapadol, tak snáď niekto iný:
(maticový vzorec Ctrl+Shift+Enter)
=INDEX(A1:A6;INDEX(SMALL(IF(A1:A6<>0;ROW(A1:A6);FALSE);ROW(OFFSET(A1;;;COUNTIF(A1:A6;"<>"&0))));RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0))))
=INDEX(A1:A6;INDEX(SMALL(KDYŽ(A1:A6<>0;ŘÁDEK(A1:A6);NEPRAVDA);ŘÁDEK(POSUN(A1;;;COUNTIF(A1:A6;"<>"&0))));RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0))))

kratší variant, ale musia to byť čísla
=SMALL(IF(A1:A6<>0;A1:A6;FALSE);RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0)))
=SMALL(KDYŽ(A1:A6<>0;A1:A6;NEPRAVDA);RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0)))citovat

lubo(15.11.2022 14:45)#053856

Pokud máte novější verzi excelu, pak
=LET(tab;FILTER($A$1:$A$6;$A$1:$A$6<>0);INDEX(tab;RANDBETWEEN(1;POČET(tab))))citovat

Mysiak(16.11.2022 11:23)#053863

elninoslov napsal/a:

Jednoduchší vzorec ma zatiaľ nenapadol, tak snáď niekto iný:
(maticový vzorec Ctrl+Shift+Enter)
=INDEX(A1:A6;INDEX(SMALL(IF(A1:A6<>0;ROW(A1:A6);FALSE);ROW(OFFSET(A1;;;COUNTIF(A1:A6;"<>"&0))));RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0))))
=INDEX(A1:A6;INDEX(SMALL(KDYŽ(A1:A6<>0;ŘÁDEK(A1:A6);NEPRAVDA);ŘÁDEK(POSUN(A1;;;COUNTIF(A1:A6;"<>"&0))));RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0))))

kratší variant, ale musia to byť čísla
=SMALL(IF(A1:A6<>0;A1:A6;FALSE);RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0)))
=SMALL(KDYŽ(A1:A6<>0;A1:A6;NEPRAVDA);RANDBETWEEN(1;COUNTIF(A1:A6;"<>"&0)))

Ahoj díky.
Testoval som (príloha). V prvom vzorci mi tam hádže aj 0. Ten druhý funguje ale pri refreshi vzorca mi vyhodí niekedy chybu #ČÍSLO! (OFFICE 2019). Nevieš mi prosím pomôcť, kde robím chybu?

Příloha: xlsx

53863_test.xlsx (10kB, staženo 5x)

citovat

Mysiak(16.11.2022 11:26)#053864

lubo napsal/a:

Pokud máte novější verzi excelu, pak
=LET(tab;FILTER($A$1:$A$6;$A$1:$A$6<>0);INDEX(tab;RANDBETWEEN(1;POČET(tab))))

Díky skúšal som aj na tablete kde mám nový office 365 a hádzalo mi tam aj 0. Nevedel som nájsť kde robím chybu.citovat

elninoslov(16.11.2022 12:52)#053865

A Vám nevadí, že Vám Excel jasne hovorí (zelený trojuholník), že tie "0" nie sú 0? Veď to tam jasne píše, že máte tie "čísla" uložené ako text. Použite normálne čísla a ten kratší vzorec. O tom, že to musí byť číslo, som písal aj ja.

EDIT:
A u dlhšieho vzorca je problém okrem textočísel aj v tom, že neviete ako funguje, a nesprávne ste ho upravil. Tučným som zvýraznil Vaše chyby. To A1 je tam na získanie číselného radu (indexu) začínajúceho 1 a končiaceho počtom <>0, to A1 bude vždy A1 bezohľadu akú oblasť chcete skúmať. A to -2 tam musíte dať z rovnakého dôvodu, aby ste získali číselný rad podľa počtu skúmaných buniek ale tiež začínajúcim od 1. Preto ak ste na riadku V3 tak -2.
=INDEX(V3:V45;INDEX(SMALL(IF(V3:V45<>0;ROW(V3:V45)-2;FALSE);ROW(OFFSET(A1;;;COUNTIF(V3:V45;"<>"&0))));RANDBETWEEN(1;COUNTIF(V3:V45;"<>"&0))))
=INDEX(V3:V45;INDEX(SMALL(KDYŽ(V3:V45<>0;ŘÁDEK(V3:V45)-2;NEPRAVDA);ŘÁDEK(POSUN(A1;;;COUNTIF(V3:V45;"<>"&0))));RANDBETWEEN(1;COUNTIF(V3:V45;"<>"&0))))citovat

Mysiak(16.11.2022 14:44)#053868

elninoslov napsal/a:

A Vám nevadí, že Vám Excel jasne hovorí (zelený trojuholník), že tie "0" nie sú 0? Veď to tam jasne píše, že máte tie "čísla" uložené ako text. Použite normálne čísla a ten kratší vzorec. O tom, že to musí byť číslo, som písal aj ja.

EDIT:
A u dlhšieho vzorca je problém okrem textočísel aj v tom, že neviete ako funguje, a nesprávne ste ho upravil. Tučným som zvýraznil Vaše chyby. To A1 je tam na získanie číselného radu (indexu) začínajúceho 1 a končiaceho počtom <>0, to A1 bude vždy A1 bezohľadu akú oblasť chcete skúmať. A to -2 tam musíte dať z rovnakého dôvodu, aby ste získali číselný rad podľa počtu skúmaných buniek ale tiež začínajúcim od 1. Preto ak ste na riadku V3 tak -2.
=INDEX(V3:V45;INDEX(SMALL(IF(V3:V45<>0;ROW(V3:V45)-2;FALSE);ROW(OFFSET(A1;;;COUNTIF(V3:V45;"<>"&0))));RANDBETWEEN(1;COUNTIF(V3:V45;"<>"&0))))
=INDEX(V3:V45;INDEX(SMALL(KDYŽ(V3:V45<>0;ŘÁDEK(V3:V45)-2;NEPRAVDA);ŘÁDEK(POSUN(A1;;;COUNTIF(V3:V45;"<>"&0))));RANDBETWEEN(1;COUNTIF(V3:V45;"<>"&0))))

Díky. Nechcel som ťa rozčúliť, stále sa učím

Bohužiaľ aby som z pôvodného stĺpca spravil číslo tak som to zatiaľ dokázal spraviť len tým, že som to do nového stĺpca prenásobil *1 a vzorec upravil tak aby sa týkal tohto stĺpca. Následne to funguje s INDEXovou funkciou :) Díkycitovat

elninoslov(16.11.2022 16:15)#053871

Obe verzie prerobené tak, že je jedno, či je to číslo alebo textočíslo. Každopádne to musí byť prevediteľné na číslo.
Ale ja by som si to určo previedol na čísla. Na to môžu nadväzovať ďalšie problémy ako filtre (časté aj pri dátum vs. textodátum) a pod. Jednorázové prevádzanie sa robí tak, že si niekde do bunky dočasne napíšete 1, tú bunku dáte Ctrl+C, označíte textočísla, pravý klik, Prilepiť špeciálne, Hodnoty a Násobenie, bunku s 1 zmažete. Ak to je často menené, napr. nejaký import, tak priamo v importe by to malo ísť, alebo pomocným stĺpcom so vzorcami ako to máte teraz. A možnosť je samozrejme aj takýto upravený vzorec čo Vám sem teraz dávam, ktorému je to jedno :)
=INDEX(V3:V45;INDEX(SMALL(IF(V3:V45*1<>0;ROW(V3:V45)-2;FALSE);ROW(OFFSET(A1;;;SUM(--(V3:V45*1<>0)))));RANDBETWEEN(1;SUM(--(V3:V45*1<>0)))))
=INDEX(V3:V45;INDEX(SMALL(KDYŽ(V3:V45*1<>0;ŘÁDEK(V3:V45)-2;NEPRAVDA);ŘÁDEK(POSUN(A1;;;SUMA(--(V3:V45*1<>0)))));RANDBETWEEN(1;SUMA(--(V3:V45*1<>0)))))
=SMALL(IF(V3:V45*1<>0;V3:V45*1;FALSE);RANDBETWEEN(1;SUM(--(V3:V45*1<>0))))
=SMALL(KDYŽ(V3:V45*1<>0;V3:V45*1;NEPRAVDA);RANDBETWEEN(1;SUMA(--(V3:V45*1<>0))))citovat

Mysiak(18.11.2022 11:35)#053903

elninoslov napsal/a:

Obe verzie prerobené tak, že je jedno, či je to číslo alebo textočíslo. Každopádne to musí byť prevediteľné na číslo.
Ale ja by som si to určo previedol na čísla. Na to môžu nadväzovať ďalšie problémy ako filtre (časté aj pri dátum vs. textodátum) a pod. Jednorázové prevádzanie sa robí tak, že si niekde do bunky dočasne napíšete 1, tú bunku dáte Ctrl+C, označíte textočísla, pravý klik, Prilepiť špeciálne, Hodnoty a Násobenie, bunku s 1 zmažete. Ak to je často menené, napr. nejaký import, tak priamo v importe by to malo ísť, alebo pomocným stĺpcom so vzorcami ako to máte teraz. A možnosť je samozrejme aj takýto upravený vzorec čo Vám sem teraz dávam, ktorému je to jedno :)

Funguje to skvele. Díky za tvoj čas a inštrukcie!citovat

Téma: Náhodné číslo z množiny buniek okrem 0

Uživatelské menu

Menu

Formulář Faktura

Formulář Faktura IV

Helios iNuvio

On-line nástroje