Nepřímý odkaz a vyhledávací funkce

[oco1]

Dobrý den,

obracím se na Vás s prosbou o radu ohledně funkce nepřímý odkaz a... o co mi jde

Mám v reportu pole se seznamem, kde si vybírám měsíc a podle toho jaký měsíc si vyberu, tak potřebuji do jednotlivých řádků vyjet hodnoty vybraných účtů.

Příklad - beru jen typ hodnoty PLÁN a řádek Osobní náklady celkem
Měsíční hodnoty

Najdi na listu (Plán NEBO Tranfor.tab) účty např. 521,522,523,524,525,527,528 a podle toho co je vybrán za měsíc v rozbalovacím seznamu, tak vyjeď data.
Problém je, že nevím jak tam zakomponovat do funkce, aby to najednou bralo více účtů (nechci tam funkci kopírovat 2x a více krát jak to mám teď)
Buď by se to do funkce natvrdo nadefinovaly účty, např. 521,522, atd. nebo bych udělal pomocný list, kde by to bylo pro každý řádek definováno, např. jak to mám na listu Transfor.tab. H1-H8 a z těchto oblastí by se to bralo.

Další věc jsou kumulativní hodnoty, které jsem zkoušel dělat pomocí funkce SUMA+NEPŘÍMÝ ODKAZ

Jde o podobnou věc, ale aby to vyjelo za období 1-X např. 1-3, prostě podle toho jaký je vybraný měsíc v seznamu.
Zkoušel jsem to udělat pomocí nepřímého odkazu, ale nevím jak toto řešení zprovoznit, když jsou data na jiném listu.
Když jsou data na stejné listu, tak mi to funguje, ale na jiný list již nikoliv…asi tam mám něco špatně… a taky tam bohužel neumím zakomponovat, aby mi to bralo více účtů, v mém pokusu to mám uděláno zase tak, že jsem tam ty funkce nakopíroval vícekrát…

Ale cítím, že toto řešení není moc systémové a je to i pracné.
Každopádně to nemůžu naodkazovat napřímo, jelikož se může stát, že se někdy může pořadí účtů prohodit a už by to nefungovalo.

Předem Vám děkuji za jakoukoliv radu nebo nasměrování jak toto dodělat.

Kuba

[Reklama]

[eLCHa]

Sorry, je to hrozně moc písmen a já už se při pátku úplně nesoustředím, takže si nejsem jistý, jestli správně chápu.

Najdi na listu (Plán NEBO Tranfor.tab) účty např. 521,522,523,524,525,527,528
a podle toho co je vybrán za měsíc v rozbalovacím seznamu, tak vyjeď data.
takže např. za březen by měla vyjet celková hodnota 2 967 455

Kód: Vybrat vše

=SUMIF(Plán!$D$2:$D$31;"52?";POSUN(Plán!$E$2:$E$31;0;$F$5))

Vykašlete se na kombinaci SUMA+NEPŘÍMÝ ODKAZ a používejte funkci POSUN (není složitá - koukněte do nápovědy). Tedy SUMA+POSUN. Jestli Vám někdo tvrdí, že je to volatilní funkce a tak je lepší se jí vyhnout, tak to ignorujte. Ten soubor je tak malý, že je to jedno.

[oco1]

Super, díky funguje to, tak rychlou odpověď jsem ani nečekal!

Ještě se chci ještě zeptat, v jakém formátu mám zapsat, když ve funkci SUMIF definuji kritéria, aby mi to bralo více účtů?
Jelikož vy jste tam napsal "52?", takže to bere všechny účty "52X", ale když bych chtěl třeba 2 a více různých účtů? Např. 511 a 518? Zkoušel jsem hodnoty oddělit středníkem, ale to nefunguje...

[guest]

Četl jsem to na půl oka.. co místo otazníku hvězdička?

[eLCHa]

@xlnc
na půl oka to nestačí - ono to má délku 3 znaky, takže v tomto případě je jedno jestli * nebo ? ;)

@oco1
já to dělám tak, že mám skrytý sloupec a v něm mám účty, které chci sčítat označené stejným řetězcem (slovem). Podle tohoto slopce pak používám SUMIF

[oco1]

Dobrý den,

dostal jsem se k tomu až dnes a je opravdu jedno, jestli tam je napsáno "52?" nebo "52*" ... budu nad tím ještě trochu popřemýšlet.
Prostě bych potřeboval, jestli to nejde napsat tak, jak to mám na obrázku vpravo, jestli ty jednotlivé účty nejdou něčím oddělit - ale nepřišel jsem na to jak...


@eLCHa
Vaše řešení zní zajímavě, ale přesně nevím jak to myslíte - máte 1 sloupec a v něm na každém řádku jeden účet?

[lubo.]

Jde to:

Kód: Vybrat vše

=SUMIF(Plán!$D$2:$D$31;511;POSUN(Plán!$E$2:$E$31;0;$F$5))+SUMIF(Plán!$D$2:$D$31;518;POSUN(Plán!$E$2:$E$31;0;$F$5))

nebo

Kód: Vybrat vše

=SUMA(SUMIF(Plán!$D$2:$D$31;{511;518};POSUN(Plán!$E$2:$E$31;0;$F$5)))

(maticově)

nebo

Kód: Vybrat vše

=SOUČIN.SKALÁRNÍ(SUMIF(Plán!$D$2:$D$31;{511;518};POSUN(Plán!$E$2:$E$31;0;$F$5)))

Zřejmě nejvhodnější je první vzorec

[oco1]

Ideální, díky
Na to první řešení jsem přišel taky, ale spíš mi šlo o to, to udělat tak jak to máte ve druhém a třetím případě.

[eLCHa]

na údržbu nejsnazší je

[oco1]

Sorry, je to hrozně moc písmen a já už se při pátku úplně nesoustředím, takže si nejsem jistý, jestli správně chápu.

Najdi na listu (Plán NEBO Tranfor.tab) účty např. 521,522,523,524,525,527,528
a podle toho co je vybrán za měsíc v rozbalovacím seznamu, tak vyjeď data.
takže např. za březen by měla vyjet celková hodnota 2 967 455

Kód: Vybrat vše

=SUMIF(Plán!$D$2:$D$31;"52?";POSUN(Plán!$E$2:$E$31;0;$F$5))

Vykašlete se na kombinaci SUMA+NEPŘÍMÝ ODKAZ a používejte funkci POSUN (není složitá - koukněte do nápovědy). Tedy SUMA+POSUN. Jestli Vám někdo tvrdí, že je to volatilní funkce a tak je lepší se jí vyhnout, tak to ignorujte. Ten soubor je tak malý, že je to jedno.

A jde ještě nakombinovat tuto funkci tak, aby nevracela hodnoty pouze za 1 vybraný měsíc, ale aby vrátila hodnoty za období Leden - vybraný měsíc?

=SUMA(SUMIF(Plán!$D$2:$D$31;{521;522;523;524;525;526;527;528};POSUN(Plán!$E$2:$E$31;0;$F$5)))

Tzn. že za březen to správně vrátí hodnotu 2 967 455, ale za Leden - Březen by měla vrátit hodnotu 8 895 250.

[eLCHa]

Na Vašem místě bych si nedělal problémy se složitými vzorci, viz ukázka

[oco1]

No ono to právě není tak jednoduché... Vaše řešení funguje, ale není to "interaktivní". Prostě ten soubor bude sloužit k tomu, že si tam uživatel bude moc přepínat období (měsíce) pomocí toho rozbalovacího seznamu a podle toho mu vždy v jednom sloupci vyjedou data za daný měsíc a v dalším sloupci data v kumulaci Leden až vybraný měsíc...