MS Excel

Dobry den,

pro vysvetleni zjednoduseny priklad:

Mam dva listy.

V jednom (List1) jsou definovany skupiny a jejich clenove: napr. jednotlive domy a jejich najemnici:

Dum 1:
Novak (A3)
Svoboda (A4)
Dvorak (A5)

Dum 2:
Pokorny (A7)
Kavan (A8)
Pavlicek (A9)

Ve druhem listu (List2) je velky datovy seznam o nekolika sloupcich: napr. datum, jmeno, rodne cislo, telefon, obdrzena platba, atd.

A v novem liste chci udelat statistiky plateb od jednotlivych obyvatel dle domu.

Tedy bych chtel:

Dum 1: Suma vsech plateb od Novak, Svoboda, Dvorak.

Az potud by to bylo jednoduche, protoze by stacilo dat: =SUM(SUMIF(List2!$B$6:$B$13, List1!$A$3:$A$5, List2!$C$6:$C$13))
Kde List2!B jsou jmena a List2!C platby a List1!A3:A5 je preddefinovany seznam najemniku domu 1. Pokud tohle zadam jako maticovy vzorec (CTRL+SHIFT+ENTER), tak to funguje dobre.

Stejne by bez maticoveho vzorce fungovalo =SUM(SUMIF(List2!$B$6:$B$13, {"Novak", "Svoboda", "Dvorak"}, List2!$C$6:$C$13)). Toto reseni bych preferoval, tedy bez maticove funkce s vypisem jednotlivych podminek pro scitani.

Problem nastava, ze v nekolika pripadech, napr. u Domu 3 z nejakeho duvodu potrebuji pripocitat poplatky od nekoho, kdo uz v listu A neni, byl ze seznamu odstranen, treba "Novotny".

Tedy neco ve smyslu: =SUM(SUMIF(List2!$B$6:$B$13, List1!$A$3:$A$5 + "Novotny", List2!$C$6:$C$13)), abych v temer vsech pripadech pouzil scitani dle definovanych obyvatel pro jednotlive domy v Listu 1, ale v par vyjimkach pridal jmeno, ktere tam uz neni.

Rikal jsem si, ze pokud by bylo soucasti vzorce {"Novak", "Svoboda", "Dvorak", "Novotny"}, tak by to fungovalo spravne, a tedy jsem se snazil vygenerovat dany textovy retezec pomoci funkce,

=CONCATENATE("{",CHAR(34),TEXTJOIN(""",""",TRUE,List1!$A$3:$A$5), CHAR(34), ",", """Novotny""", "}")

ktera mi da pozadovany retezec, tedy '{"Novak", "Svoboda", "Dvorak", "Novotny"}' s tim, ze pouziji definovany seznam najemniku v Listu 1 a pouze na konci pridam potrebne jmeno.

Pokud by tato funkce sla nejak vlozit do vzorce

=SUM(SUMIF(List2!$B$6:$B$13, {"Novak", "Svoboda", "Dvorak", "Novotny"}, List2!$C$6:$C$13))

a nahradit samotny vycet tedy cast "{ ... }", bylo by to presne to, co potrebuji.

Neco ve smyslu:
=SUM(SUMIF(List2!$B$6:$B$13, CONCATENATE("{",CHAR(34),TEXTJOIN(""",""",TRUE,List1!$A$3:$A$5), CHAR(34), ",", """Novotny""", "}"), List2!$C$6:$C$13))

Jen tu generovanou cast nahrazujici seznam v "{ ... }" mi to nebere. To je to oc mi jde, jak nahradit tu cast vzorce textovym vyctem generovanym jinym vzorcem.

Snad uz je to pochopitelnejsi.

Dekuji za odpoved.citovat

Obecne by slo asi i dotaz formulovat, zda-li existuje nejaka moznost, jak nahradit libovolny parametr kterekoliv funkce textem umistenym v jine bunce.

S INDIRECT to nefunguje.citovat

=SUMA(SUMIF($B$6:$B$13; E6:E13; $C$6:$C$13))

V oblasti E6:E13 si vyplňte písmenka.
Vzorec uložte maticově (CSE).

Totéž bez matice:
=SOUČIN.SKALÁRNÍ(SUMIF($B$6:$B$13; E6:E13; $C$6:$C$13))

Tj. výčet se definuje operátorem ":"

Všimněte si, že oblast nemusí být vyplněná.
Pokud potřebujete proměnnou oblast, tak si ji spočtěte vzorcem nebo prostě pro tato data vytvořte tabulku a odkazujte se na sloupec tabulky.

Protože toto řešení uvádíte, nechápu o jaký problém jde. Pokud to chápu potřebujete na volné místo seznamu nebo dynamicky do tabulky připsat dočasně další jméno?citovat

Sice nechápu smysl, ala maticově to jde takto:

=SUMA(SUMIF($B$6:$B$13; ZVOLIT({1\2};$E$9:$E$11;$G$9:$G$10); $C$6:$C$13))

V $E$9:$E$11 je první seznam, v $G$9:$G$10 druhý.

Případně

=SUMA(SUMIF($B$6:$B$13; $E$9:$F$11; $C$6:$C$13))
Ve sloupci E je původní seznam, ve sloupci F doplňující.citovat