Excel: vyhledání hodnoty
Zdravím a prosím o radu,
marně si lámu hlavu, jak v excelu zpracovat zdrojová data (také excel), konkrétně potřebuji vyhledat určitou hodnotu. V řádku 1 jsou vždy popisy sloupců, pro zjednodušení: Značka Jméno Druh Částka a já potřebuji vyhledat Částku určitého Jména. Problém je v tom, že nedokážu zajistit, aby bylo Jméno vždy ve sloupci 2 a Částka ve sloupci 4 (tabulka je rozsáhlá a závislá na informačním systému, mění se pořadí sloupců).
Na co jsem přišel:
za pomoci POZVYHLEDAT si umím najít příslušný sloupec, případně by se dalo použít VVYHLEDAT, ale pak narážím na to, že nevím, jak vnutit tento sloupec do rovnice, aby SVYHLEDAT použilo tento sloupec jako první, případně aby mi POZVYHLEDAT prohledalo tento sloupec a dalo mi hodnotu řádku pro VVYHLEDAT.
Nenapadá vás jak na to?
Díky za každý nápad!
nofu
Chtělo by to vidět strukturu dat - vzorový soubor.
Ahoj,
díky za včerejšek! Přikládám vzorový zdrojový soubor. Jméno i Zisk P může být sloupec A až do DB, záleží, jak si to uživatel přeháže.
Už jsem se dostal až tam, že dokážu zjistit a ve dvou buňkách vypsat začátek a konec sloupce Zisk P, v tomto případě tedy v jedné buňce $N$2 a v druhé $N$5, ale zaboha nemůžu přijít na to, jak to dostat do fce, aby byl výsledný vzorec např. =SUMA($N$2:$N$5), pak už bych si s tím asi dokázal poradit.
Promiň, sem dneska asi natvrdlej. Co je cílem. Proč nyní mluvíš o SUMĚ a předtím o SVYHLEDAT? Proč si chtěl vyhledávat i sloupec Jméno, když použiješ SUMU?
Hoď do příkladu i ty vzorce o kterých mluvíš na začátku ať sem alespoň trochu v obraze.
Já se omlouvám, v tom dělám mrdník. SUMA byla jen pro příklad, že umím udělat konkrétní odkaz z čísla sloupce, ale už jej nedokáži dál použít ve vzorečku. Přikládám tabulku se vzorci, jak si to představuji, že by to mohlo jít, ale taky nemusí.
Jde mi o to, zjistit hodnotu Zisk P pro jednotlivé osoby. Představa byla taková, možná od začátku chybná, že nakonci využiji fce INDEX či SVYHLEDAT, tudíž jsem zjistil pořadí sloupce Jméno (buňka B20): =POZVYHLEDAT("Jméno";A1:X1;0), poté dokážu vygenerovat do buňky odkaz na začátek a konec oblasti (buňky B24 a B25), ale tyto už dál nedokáži použít. Nyní bych potřeboval vložit to $D$2 a $D$1000 do vzorce, buď do POZVYHLEDAT(A15;$D$2:$D$1000;0) nebo třeba do SVYHLEDAT (A15;$D$2:$DB$1000;10;NEPRAVDA), kde bych ale ještě musel upravit číslo sloupce, kde končí tabulka a dopočítat pořadí sloupce, kde se nachází Zisk P. Problém ale je, že mám odkazy a neumím je použít = nevím, jak je vložit do vzorce.
A možná na to jdu od začátku špatně...
Sorry, ale tady asi neporadím. Netuším či je to tou pozdní hodinou, ale opravdu nevím o co se pokoušíš a ani ten vzorový soubor mi v tom neudělal jasno. Celé to zadání je nějaký zmatený. Když odstraním apostrof ze vzorců, dostanu hodnoty Pavel: 3000, což je špatně. V prvním vzorci je odkaz na sloupec zadán buňkou B20 (hodnota 4) + 10 - což je fixní hodnota. Původně si tvrdil, že slupce jsou "plovoucí".
Proč chceš vlastně zjišťovat čísla řádků?
Jak říkám zde nedokážu poradit neb mi uniká smysl zadání. Snad se chytne někdo jiný.
_____________________________________________________________________________________
Jen k tebou udávané oblasti $D$2:$D$1000
=ODKAZ(2;B20;1) => $D$2
=ODKAZ(1000;B20;1) =>$D$1000
Tedy:
=ODKAZ(2;B20;1)&":"&ODKAZ(1000;B20;1) => $D$2:$D$1000
Aby bylo možné takto zvolenou oblast použít ve vzorci je potřeba ji vložit do fce NEPŘÍMÝ.ODKAZ =>
NEPŘÍMÝ.ODKAZ(ODKAZ(2;B20;1)&":"&ODKAZ(1000;B20;1))
Tedy vzorec POZVYHLEDAT(A15;$D$2:$D$1000;0) bude vypadat takto =>
=POZVYHLEDAT(A15;NEPŘÍMÝ.ODKAZ(ODKAZ(2;B20;1)&":"&ODKAZ(1000;B20;1));0)
Tj přesně ono, co neumím zapsat a ani teď, když vím, jak se to dělá, tak to nedokážu vygooglit. Neporadil bys mi pls, co hledat? Jedná se mi o ten zápis s "&", abych se to doučil
Za chyby ve vzorcích se omlouvám, už sem byl mega unavenej. Výsledek jsem si představoval viz přiložená tabulka. Lze kamkoliv přidat sloupec a výsledek je stejný. Nelze toho dosáhnout nějak jednodušeji, nekomplikuju si zbytečně život?
Mnohý dík Ti!
Neponoril som sa hlbšie do obsahu vašej komunikácie, stačila mi informácia, že rôzne zdrojové dáta sú stĺpcovo rozhádzané, podľa systému, z ktorého pochádzajú.
Pre spracovávanie databázy považujem za prvoradú nutnosť data usporiadať do zjednotenej formy. Pokiaľ sú identifikátory (názvy stĺpcov) totožné, nemal by byť problém urobiť makro, ktoré vstupné súbory usporiada a zlúči do jednej databázy, kde by už nemal byť problém ďalej spracovávať podľa svojich potrieb.
...ak som to dobre pochopil...
Doučovat se doporučuji zde: https://office.lasakovi.com/excel/.
Operátor "&" je spojení řetězců, dělá v podstatě to samé, co funkce CONCATENATE.
A tad máš ty vnořené funkce NEPŘÍMÝ.ODKAZ a ODKAZ.
S Nepřímým odkazem a Concatenate jsem laboroval, ale nedokázal jsem dostat kýžený výsledek, neznal jsem & (ampersand). Teď už je to jasné.
Díky!
Jó ták. Už je mi to jasný!
Co se týče ampersandu (&): viz Zdenál nebo
https://lorenc.info/3MA381/funkce-concatenate.htm
https://support.office.com/cs-cz/article/concatenate-funkce-8f8ae884-2ca8-4f7a-b093-75d702bea31d
https://support.office.com/cs-cz/article/zahrnut%C3%AD-textu-do-vzorc%C5%AF-9318c2a2-bd28-4b86-a86c-f9f1be96304d
V podstatě je to furt stejný.
Co se týče zpracování dat, základem úspěchu, jak píše Robert je zachovat strukturu dat! Všechno ostatní je jen rovnák na vohejbák. Obávám se, že zjednodušit vzorce nelze, pokud to má být flexibilní. A i tak se vystavuješ riziku, že vzorce můžou vracet neočekávané (špatné) výsledky, které se budou v rozsáhlých tabulkách špatně odhalovat.
Jediné co by jsi mohl udělat pro lepší čitelnost vzorců je použít správce názvů.
=POZVYHLEDAT(A15;NEPŘÍMÝ.ODKAZ(ODKAZ(2;B20;1)&":"&ODKAZ(1000;B20;1));0)
Tučně zvýrazněnou oblast vzorce by jsi umístil do správce názvů s názvem např OblastJmeno
=POZVYHLEDAT(A15;OblastJmeno;0)
Změní se tím jen čitelnost vzorců na listě, nikoliv jejich celková složitost.
Zachovat strukturu dat je samozřejmě také řešení, bohužel toto je export z informačního systému firmy, který je závislý na aktuálním zobrazení a i já si občas nějaký ten sloupec odeberu či přidám.
Správce názvů se zdá jako velmi šikovná fce, tím se mi velmi zpřehlední práce, neznal jsem.
Obrovský DÍK!
Napadla mě taková věc. Neznám ten svůj systém, tak to ber s rezervou.
Pokud je sloupec se jmény stále na stejném místě a sloupec Zisk je "plovoucí" podle nastavení zobrazení a zároveň by se nedostal před sloupec se jmény, šla by použít fce SVYHLEDAT. Jako oblast (tabulka) by se nastavila statická oblast (celkový počet sloupců, který generuje inf. systém) dejme tomu, jak už tu padlo - D2:DB1000 a číslo sloupce (zisk) by se zjišťoval přes fci POZVYHLEDAT.
=SVYHLEDAT(Jméno;D2:DB1000;POZVYHLEDAT("zisk";A1:DB1;0);NEPRAVDA)
=>číslo sloupce
???????
Tohle je mi jasný, bohužel i Jméno může být jinde. Ale už na tom makám, optimalizuji to a budu testovat, aby to makalo vždy správně. A navymejšlel sem si ještě další fičury, ale snad už otravovat nebudu![]:)](https://static.poradna.net/images/smiley/evilsmile.gif)
Ještě jednou díky!