< návrat zpět

MS Excel

Téma: Zobrazení hodnoty z jiné buňky rss

Zaslal/a buger 29.1.2016 16:35

Zdravím,

potřeboval bych poradit se zobrazením dané buňky. Mám sloupce A, B, C, D se vstupními daty. Dále mám dva závislé seznamy, v jednom volím výběr mezi sloupcem B a D a v druhém se mi zobrazí vybraný sloupec (B nebo D). Potřeboval bych, aby se mi po vybrání hodnoty ze druhého seznamu zobrazila na daném místě hodnota ze sloupce A nebo C ve stejném řádku.
Ještě bych poprosil, pokud by funkce šla ošetřit, aby se nezobrazila hodnota N/A.

Pozn.: Zkoušel jsem funkce POSUN, INDEX, POZVYHLEDAT, KDYŽ a JE.CHYBHODN, ale přišlo mi to až moc složité.

Všem předem děkuji za odpovědi.

Zaslat odpověď >
elninoslov(29.1.2016 17:16)#029689
elninoslov
To môže byť veľa riešení. Tu som si napr. pomohol CHOOSE, MATCH, OFFSET, IFERROR (ZVOLIT, POZVYHLEDAT, POSUN, CHYBHODN) ... a podmienené formátovanie
Příloha: zip29689_priklad-offset.zip (7kB, staženo 17x)
citovat
buger(29.1.2016 20:08)#029691
avatar
Já to mám následovně...
=KDYŽ(JE.CHYBHODN(POSUN(INDEX(NEPŘÍMÝ.ODKAZ($F$1);POZVYHLEDAT($G1;NEPŘÍMÝ.ODKAZ($F$1);0));;-1));0;POSUN(INDEX(NEPŘÍMÝ.ODKAZ($F$1);POZVYHLEDAT($G1;NEPŘÍMÝ.ODKAZ($F$1);0));;-1))

Aplikováno na Tvuj příklad. 1

Říkal jsem si, že by se to dalo nějak zjednodušit. 7citovat
elninoslov(30.1.2016 9:41)#029699
elninoslov
Ak by išlo o to, aby ten vzorec nepôsobil dlhý, zložitý, mätúci, tak si výber oblasti (v mojom prípade) urobte pomocou Definovanej oblasti.
CHOOSE/ZVOLIT namiesto INDIRECT/NEPŘÍMÝ.ODKAZ Vám umožňuje mať v bunke s prvým výberom slová oddelené medzerami.
Dá sa tam prirobiť viac vecí, napr. rozsahy môžu byť dynamické, alebo to môže byť urobené ako kopírovateľný vzorec...
Příloha: zip29699_priklad-offset.zip (7kB, staženo 15x)
citovat
vovka.h(30.1.2016 14:23)#029703
avatar
Použití podmíněného formátu jako zpětnovazební informace pro hodnoty, vybrané pomocí ověření, je vynikající nápad. Opticky řeší "neexistenci pudu sebezáchovy", jak kdysi někdo nazval největší slabinu techniky ověření dat. Existuje několik postupů, jak ověření dat přelstít. Případ, kdy se změní nadřazený výběrový seznam, je jenom jeden z nich. Podmíněné formátování, které použil elninoslov, zabere i v řadě dalších způsobů, jimiž se ověřovací podmínky naruší. Zcela jistě ten nápad využiji.
Daleko míň jsem nadšený složitostí použitých vzorců. Nedávno mi právem Hav-Ran vyčetl, že na fórum posílám příliš hotová řešení, do kterých už tazatel těžko bude zasahovat. Jsem si téměř jistý, že do dlouhých megavzorců, které elninoslov použil, běžný uživatel zasáhnout ani nemůže, aniž by je pokazil.citovat
buger(31.1.2016 8:36)#029724
avatar
To elninoslov: Máš pravdu... V mém řešení není možné slova v seznamu oddělit mezerami. Ještě jsem řešil jednu věc: Když se změní hodnota v prvním seznamu, aby se smazala hodnota v druhém seznamu. Nepřišel jsem na to, jak to vyřešit pomocí funkce, tak jsem použil makro, které jsem vložil do modulu listu, viz níže.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$1" Then
Range("$G$1").ClearContents
End If
End Subcitovat
elninoslov(31.1.2016 14:24)#029737
elninoslov
@vovka.h:
Dlhé ?
Toto sú všetky vzorce, ktoré som použil, teda bunky aj Definované oblasti aj PF:
SK
=IFERROR(OFFSET(Vyber;MATCH($G$1;Vyber;0)-1;-1;1;);"")
=CHOOSE(IF(Hárok1!$F$1="Zoznam B";1;2);ZoznamB;ZoznamD)
=Hárok1!$B$2:$B$4
=Hárok1!$D$2:$D$4
=ISERROR(MATCH($G$1;Vyber;0))

CZ
=CHYBHODN(POSUN(Vyber;POZVYHLEDAT($G$1;Vyber;0)-1;-1;1;);"")
=ZVOLIT(KDYŽ(Hárok1!$F$1="Zoznam B";1;2);ZoznamB;ZoznamD)
=Hárok1!$B$2:$B$4
=Hárok1!$D$2:$D$4
=JE.CHYBHODN(POZVYHLEDAT($G$1;Vyber;0))
Pozrite iba to moje riešenie v F1 a G1. Mne to nepripadá dlhé, robím často (zbytočne) oveľa dlhšie...
V F2 a G2 je len na porovnanie aplikované riešenie od kolegu "buger".

@buger:
Toto sa dá riešiť jedine makrom, lebo Overenie nemá takú funkciu, aby odstránilo hodnotu, ktorá v bunke je. To PF som Vám tak pridal presne preto, ak by ste nechceli/nemohli použiť makro, tak aby Vám do očí udrelo, že hodnotu treba zmeniť.
To Vaše makro môže byť. Volá sa ale 2x. Lebo ClearContents vyvolá ďalšie spustenie OnChange. Buď to obalíte do
Application.EnableEvents = false
...
Application.EnableEvents = true
alebo to necháte tak. Neviem čo bude rýchlejšie.

Ďalej ak by mohol nastať prípad, že sa zmení bunka s hlavným zoznamom spoločne s inými bunkami naraz (mazanie, kopírovanie,...) tak to treba ošetriť v OnChange nie porovnaním priamej adresy, ale cez Intersect...

Proste záleží na tom, ako sa to bude používať. Použitím makra v OnChange, prichádzate o Undo.citovat
vovka.h(31.1.2016 15:44)#029740
avatar
Vzorce, které tady uvádíte, jsou opravdu stručné a čitelné. V sešitě jsem ale našel taky tohle:
=KDYŽ(JE.CHYBHODN(POSUN(INDEX(NEPŘÍMÝ.ODKAZ($F$2);POZVYHLEDAT($G2;NEPŘÍMÝ.ODKAZ($F$2);0));;-1));0;POSUN(INDEX(NEPŘÍMÝ.ODKAZ($F$2);POZVYHLEDAT($G2;NEPŘÍMÝ.ODKAZ($F$2);0));;-1))O tomto vzorci jsem psal 1 Teď jsem se dočetl, že je to tam jen pro srovnání. Tím se mi vysvětluje, proč jsem já byl nespokojený a vy zaskočený.citovat
elninoslov(31.1.2016 15:54)#029741
elninoslov
Presne tak. tento vzorec, ktorý spomínate, uviedol
buger(29.1.2016 20:08)
a ja som ho pridal k môjmu iba pre porovnanie.
Teda moje riešenie je v F1:H1
a to od "buger", pre porovnanie v F2:H2.

Ja neviem, možno by to šlo inak, takto ma to napadlo, zdalo sa mi to funkčné, tak som to poslal. Ale máte pravdu, že často robím veci príííliš zložito, ale zrovna teraz to nieje až také blbé, snáď 1

EDIT:
Ešte ma napadlo a pár znakov skrátiť toto
=CHOOSE(IF(Hárok1!$F$1="Zoznam B";1;2);ZoznamB;ZoznamD)
na toto
=CHOOSE((Hárok1!$F$1="Zoznam B")+1;ZoznamD;ZoznamB)citovat

Uživatelské menu

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

Menu

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.

On-line nástroje