Excel - odkaz na buňky, když...
Zdravím,
rád bych bez použití maker vyřešil následující excelovský problém.
Na listu ABC jsou jakási zdrojová data (cca 2 tisíce řádků a 20 sloupců) - pro příklad přikládám výřez obrázku. Potřebuju zkopírovat odpovídající číslo (např. ve formě odkazu) ze všech příslušných buňěk sloupců P a R, když je ve sloupci L např. nula.
Např. když je v L225 nula, potřebuju hodnoty P225 a R225.
Používám funkci když pro každou hodnotu, kterou chci zkopírovat:
=KDYŽ(ABC!L225=0;ABC!L225;"")
Ale pak získám tabulku kde je spousta prázdných buněk.
Snad se mi to alespoň trošku povedlo vysvětlit.
Tak přikládám excelovský sešitna na ukázku.
List Data obsahuje veškeré data
Druhý list už obsahuje separovaná data.
Ve skutečnosti je v jednom sešitě mnohem více dat. Po separaci dle hodnoty (ve sloupci B v listu Data) ale zůstávají v tabulkách po separaci volné buňky - vím, proč tam zůstávají, ale nejsem schopen přijít na to, jak to obejít. Kdyby to bylo jen pár prázdných buňěk, ale jsou (budou) jich stovky - a já bych rád měl všechny hodnoty ve všech tabulkách vedle sebe, abych nemusel rolovat jak blázen. Viz. sešit.
Děkuju
No, tak pokud jsem to pochopil dobře, tak:
1. máš zdrojová data (list "Data"), která potřebuješ roztřídit do oblastí dle hodnoty ve sloupci "B" (0, 1, 2, ...)
2. Tyto oblasti se nacházejí na listu "Separovaná data"; pokud je na listu Data ve sloupci B hodnota 0, tak cílové sloupce jsou B až D, při hodnotě 1 to jsou sloupce F až H, atd.
Pokud je to tedy pravda, tak bych na to použil rozšířený filtr - tady máš povídání přímo od MS a tady máš návod z webu Lasakovi.com.
Na listu data pro každý řádek vytvořit unikátní hodnotu indexu složeného z hodnoty sloupce B a pořadového čísla pro každý prvek sloupce B (lze to spojit s podmínkami, za jakých prvek vstupuje do výběru, to co nemá vstoupit prostě pořadí nedostane). Základní předpoklad je, že hodnoty ve sloupci B jsou seřazeny, nejsou různě promíchané. Pak by to nefungovalo. Ten index by vypadal třeba takto "1_1", "1_2"... až "1_n", "2_1", "2_2"... až "2_n"...
Separovaná data mají číslo pořadí určeno číslem řádku, někde začne řádek s číslem pořadí 1. třeba ve sloupci A. Ty další sloupce jsou už konkrétní hodnoty dodané funkcí "svyhledat" ta funkce má první hodnotu určenou pro index a ten vytvořím jak je uvedeno výše hodnotou prvku + pořadí", druhá hodnota funkce je zdrojová tabulka data, třetí hodnota je číslo sloupce té tabulky ve které jsou ty hodnoty, čtvrtá hodnota je nula "0" neboli nepravda.
Na spojení dvou hodnot a přidání "_", slouží funkce CONCATENATE(1;"_";2) vytvoří textový řetězec "1_2".
Teď se nemůžu hrabat přímo v excelu, kdyžtak později, pokud nikdo nepřispěje s jiným a lepším řešením.
MachR55: Ano, pochopil jsi to správně. Jen bych rád zdůraznil, to co jsem ještě zatajil - a to, že těch soborů s čísly je skoro 200 a stále přibývají. Ty soubory vkládám do jakéhosi excelovského mustru- ten mi data vyhodnotí. Proto je třeba, aby vše probíhalo automaticky, třeba ve formě odkazů na buňky. Filtry asi takto fungovat nebudou. Každopádně děkuju za vstřícnost.
TNT: To zní dost fundovaně a tady je možná ten klíč k mému problému. S těmito funkcemi jsemse ale doposud neshledal a ani po x-tém čtení mi to není jasné :(
V tom případě je řešení to, co psal TNT - tj. pro každý řádek v Datech vytvořit jednoznačný index a použít vyhledávací funkci SVYHLEDAT. Pokud tuto funkci neznáš, můžeš se sní seznámit třeba tady.
Tak sem se o něco pokusil. Doufám, že sem pochopil zadání jinak se z toho ...
Vše je řešeno pomocí maticových vzorců.
Siki, výborná práce, moc děkuju!!
Zrovna to aplikuju na svá data, ale chvíli to potrvá, než se zorientuju. Jen se chci optat, zda nebude problém, když budou mít ty sloupce záhlaví, nebo nad tou tabulkou která je na listu Data bude text? Tzn. i v sloupci "B"?
Se záhlavím nebude problém, stačí si jen nastavit rozsah "prohledávané oblasti". Ve vzorovém sešitu začínají data na řádku 7. Pokud je záhlaví sloupce na řádku 6 nebo nižším nemá to na vzorec vliv.
Pokud se Ti nebude dařit přenést vzorce do ostré verze sešitu, napiš rozsahy buněk a vzorce upravím.
PS: sem zvědaví, jestli někdo přijde s jiným řešením. Nic jednoduššího mě totiž nenapadlo.
Hmmm, zírám
Zrovna jsem chtěl doporučit to samý, co TNT, ake tohle je fakt parádní řešení, to by mě nenapadlo. No, ukazuje se, že Excel umí opravdu vše - tedy kromě vaření a luxování
Tak funguje to náramně
Ještě jedna věc, která je naznačena v přiloženém excelovském souboru. Jde o nalezení odpovídající hodnoty k buňce, která má z množiny hodnot vejvyšší číselnou hodnotu. Dá se to lehce pochopit na přiloženém příkladu.
Moc Vám všem děkuji.
Problém jsou ty hodnoty #N/A. Takže:
1. vytvoř si pomocnou buňku - např. G1 - v níž bude maticový vzorec:
(maticový vozrec z toho dostaneš tak, že po vložení nestikneš samotný ENTER, ale kombinaci CTRL+SHIFT+ENTER).
2. Do buňky G2 napiš vozrec:
No a tam se Ti bude načítat hodnota ze sloupce D, odpovídající nejvyšší hodnotě ve sloupci C.
Tak mě to píše, že ve vzorci je cyklický odkaz, který excel neumí vypsat (v reálných datech). Já jsem z toho jelen.
Neexistuje ještě nějaké jiné řešení?
Co třeba takto:
Opět maticový vzorec