< návrat zpět

MS Excel


Téma: výběr hodnot z řádku do jedné buňky rss

Zaslal/a 16.9.2020 12:35

Dobrý den,
dovolím si požádat o radu, jak najít hodnotu v řádku a na jejím základě zobrazit příslušný výsledek.
Mám soubor, viz příloha.
Pokud je v BC nebo BE nebo BG nebo BI nebo BL nějaká hodnota, která začíná "NAZ PŮVODNÍ", pak se musí zapsat zbývající text za "NAZ PŮVODNÍ***"

př.: BC4 obsahuje "NAZ PŮVODNÍ***BCCCCC", tak do buňky W4 se napíše "BCCCCC"
př.: BG16 obsahuje "NAZ PŮVODNÍ***PUMPIČKA", tak do W16 se zapíše "PUMPIČKA"

V sloupci W je zatím zkusmo vzorec, který mi řekne, že v daném řádku v některém ze sloupců BC nebo BE nebo BG nebo BI nebo BL je zápis začínající "NAZ PŮVODNÍ" a vrátí zápisem "je hodnota".
Rozepsání řetezce za NAZ PŮVODNÍ mi funguje vzorcem, ale pouze pro BC
KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ"; BC4));(ČÁST(BC4;15;200));"")
Potřeboval bych tento vzorec rozšířit i na zbývající sloupce BE, BG, BI a BL
Pokud je to možné, uvítal bych řešení ve VBA (nevyznám se a data budou v tabulce přibývat), event. vzorcem v buňce. Poradí, prosím někdo? 4
Zkoušel jsem různě kombinovat funkci A a funkci NEBO ale stále mi to nejde.

Příloha: xlsx48019_vyber-hodnot-z-radku-do-jedne-bunky.xlsx (17kB, staženo 8x)
Jméno
Kontrola
Text
  b i u s img code url hr   1 2 3 4 5 6 7 8 9 10

Strana:  1 2   další »
#048020
avatar
Do bunky w4 napíš:
=IF(OR(V4="";AND(BC4="";BE4="";BG4=""));"";RIGHT(OFFSET(BC4;0;(LEFT(BC4;11)=$W$1)*0+(LEFT(BE4;11)=$W$1)*2+(LEFT(BG4;11)=$W$1)*4);LEN(OFFSET(BC4;0;(LEFT(BC4;11)=$W$1)*0+(LEFT(BE4;11)=$W$1)*2+(LEFT(BG4;11)=$W$1)*4))-14))citovat
#048021
avatar
Sorry, oprava, nevšimol som si že tam sú ďalšie stĺpce, takže správne :
Do W4 zadaj:
=IF(OR(V4="";AND(BC4="";BE4="";BG4="";BI4="";BK4=""));"";RIGHT(OFFSET(BC4;0;(LEFT(BC4;11)=$W$1)*0+(LEFT(BE4;11)=$W$1)*2+(LEFT(BG4;11)=$W$1)*4+(LEFT(BI4;11)=$W$1)*6+(LEFT(BK4;11)=$W$1)*8);LEN(OFFSET(BC4;0;(LEFT(BC4;11)=$W$1)*0+(LEFT(BE4;11)=$W$1)*2+(LEFT(BG4;11)=$W$1)*4+(LEFT(BI4;11)=$W$1)*6+(LEFT(BK4;11)=$W$1)*8))-14))
a vyplň nadol.
Pre ostatné stĺpce si to snáď vydedukuješ (možno si to vieš zjednodušiť správnym rozhodením absolútnych a relatívnych odkazov.
Všimni si, že neporovnávam voči "tvrdej hodnote" ale voči obsahu bunky, kde sa daný hľadaný výraz nachádzacitovat
#048022
avatar
V českej verzii:

=KDYŽ(NEBO(V4="";A(BC4="";BE4="";BG4="";BI4="";BK4=""));"";ZPRAVA(POSUN(BC4;0;(ZLEVA(BC4;11)=$W$1)*0+(ZLEVA(BE4;11)=$W$1)*2+(ZLEVA(BG4;11)=$W$1)*4+(ZLEVA(BI4;11)=$W$1)*6+(ZLEVA(BK4;11)=$W$1)*8);DÉLKA(POSUN(BC4;0;(ZLEVA(BC4;11)=$W$1)*0+(ZLEVA(BE4;11)=$W$1)*2+(ZLEVA(BG4;11)=$W$1)*4+(ZLEVA(BI4;11)=$W$1)*6+(ZLEVA(BK4;11)=$W$1)*8))-14))citovat
#048023
avatar
No a z porovnávania je zrejmé, že hľadaný výraz musí byť totožný s obsahom príslušnej bunky (u nás obsah W1), ideálne Case Sensitive, t.j. zhoda i vo veci veľkosti písmencitovat
#048024
avatar
Děkuji za odpověď, robert13, ale není to ono. Kalkuluješ s hodnotou v sloupci V. tam bude úplně něco jiného. Do V jsem zapsal pouze příklad toho, co se má zobrazit ve W. Navíc kalkuluješ s pevěn danou hodnotou, ale ty hodnoty v BC:BL mohou být např. NAZ PŮVODNÍ***ABC, NAZ PŮVODNÍ***123, NAZ PŮVODNÍ***BLABLABLA, atd. Do W se tedy musí zapsat ta konkrétní hodnota, která bude v některém z těch sloupců BC:BL. Pokud to pomůže, tak počítám s tím, že "NAZ PŮVODNÍ" bude zapsán v tom řádku jen 1x.

robert13 napsal/a:

V českej verzii:

=KDYŽ(NEBO(V4="";A(BC4="";BE4="";BG4="";BI4="";BK4=""));"";ZPRAVA(POSUN(BC4;0;(ZLEVA(BC4;11)=$W$1)*0+(ZLEVA(BE4;11)=$W$1)*2+(ZLEVA(BG4;11)=$W$1)*4+(ZLEVA(BI4;11)=$W$1)*6+(ZLEVA(BK4;11)=$W$1)*8);DÉLKA(POSUN(BC4;0;(ZLEVA(BC4;11)=$W$1)*0+(ZLEVA(BE4;11)=$W$1)*2+(ZLEVA(BG4;11)=$W$1)*4+(ZLEVA(BI4;11)=$W$1)*6+(ZLEVA(BK4;11)=$W$1)*8))-14))
citovat
#048025
avatar
Mimochodem, míst BK má být BL.

Já na to šel od lesa a i když tu jedno řešení je, přidám další:
=KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ";BC4;1));NAHRADIT(BC4;1;14;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ";BE4;1));NAHRADIT(BE4;1;14;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ";BG4;1));NAHRADIT(BG4;1;14;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ";BI4;1));NAHRADIT(BI4;1;14;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ PŮVODNÍ";BK4;1));NAHRADIT(BK4;1;14;"");"")))))


Ještě je otázka, jestli NAZ.PŮVODNÍ je na řádku jen jednou. Co když tam bude víckrát?citovat
#048027
avatar
Jiří497, to je přesně ono, co jsem potřeboval. Ověřil jsem to i navedlejší sloupec s "novými hodnotami a také to funguje.

=KDYŽ(JE.ČISLO(NAJÍT("NAZ NOVÉ";BD4;1));NAHRADIT(BD4;1;11;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ NOVÉ";BF4;1));NAHRADIT(BF4;1;11;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ NOVÉ";BH4;1));NAHRADIT(BH4;1;11;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ NOVÉ";BJ4;1));NAHRADIT(BJ4;1;11;"");KDYŽ(JE.ČISLO(NAJÍT("NAZ NOVÉ";BL4;1));NAHRADIT(BL4;1;11;"");"")))))

Jen ten soubor nejde stáhnout, píše mi to chybu. Ale to nevadí, vzorec je tu, pro případné zájemce s podobným problémem.
Díky moc!!! 9citovat
#048028
avatar
Mila, jasne, to s tou bunkou V je blbosť stačí vynechať funkciu NEBO s bunkou V4.
Ale ostatné sa mi zdá, že by malo byť v poriadku alebo som to zle pochopil.
Inak, ako píše Jiří, tiež som sa chcel spýtať, že čo ak bude rovnaká hodnota vo viacerých stĺpcoch ale ako som si všimol v príkladoch to bolo vždy len v jednom stĺpcicitovat
#048029
avatar
Co třeba:
=ČÁST(IFNA(VVYHLEDAT(W$1&"*";$BC4:$BL4;1;0);"");DÉLKA(W$1)+4;100000)

(Za předpokladu, že soubor udrží rozumnou strukturu.)citovat
#048030
avatar
lubo, to teda fakt zírám. Super. také jsem ověřil pro další sloupec. Jen mám chybu v záhlaví a musí tam být NAZ NOVÉ místo NAZ NOVÝ.
Jen jsem ještě ten úžasně krátký vzorec nepochopil, jak funguje, ale je to super. Pokud blíže vysvětlíš, budu rád. Nechápu, jak přesně najde v buňce místo, kde to má rozseknout a zobrazit zbývající hodnotu v buňce Také díky.

Pro řešitele:
Hodnoty NAZ PŮVODNÍ, NAZ NOVÉ, DOP PŮVODNÍ, DOP NOVÉ, apod by měly být vždy jen 1x v některém z těch sloupců BC:BL. neměly by se vícekrát opakovat. 1citovat

Strana:  1 2   další »

Uživatelské menu

Nejste přihlášen(a)
avatar\n

Menu

On-line nástroje

Formulář Faktura

Formulář Faktura III

Oblíbený formulář Faktura byl vylepšen a rozšířen. Formulář faktura III
Více se dočtete zde.

Aktivní diskuse

Zaloha zošitov okrem hdd a nas

milada • 23.9. 22:03

VLOOKUP jen pro poslední přidané řádky

Jiří497 • 23.9. 21:59

Zaloha zošitov okrem hdd a nas

lugr • 23.9. 21:47

Zaloha zošitov okrem hdd a nas

Milada • 23.9. 21:28

zlucenie buniek do jedneho

pavol14pavol • 23.9. 21:11

zlucenie buniek do jedneho

robert13 • 23.9. 19:23

vyzisti pocet dni s datumu

robo • 23.9. 19:01