Verweisfunktionen (3) ->

Über indirekten Verweis ermittelte Werte summieren (237)
Aufgabe
Gegeben ist eine Spalte A mit Namen und einem dazugehörigen Code (a-d) in Spalte B.
Ein Name kann mehrfach vorkommen und auch verschiedene Codes haben.

in Spalte D und E stehen die Codes von a-d in Spalte D und eine Zahl in Spalte E.
Nun sollen zu einem bestimmten Namen in Spalte A die den Codes aus Spalte B zugehörigen Zahlen aus Spalte E summiert werden.

Beispieltabelle:
       A               B               C               D               E       
1 Walter  a    a  5  
2 Andy  c    b  10  
3 Jens  b    c  15  
4 Walter  b    d  20  
5 Klaus  d       
6 Verena  c       
7 Klaus  b       
8 Jens  a       
9 Viktoria  d       
10 Raik  c       
11 Alexandra  d       
12 Walter  c       
13 Sven  d       
14 Klaus  c       
15 Andy  b       
16 Summe Walter  30       


Lösung
Lösung
Namen:A1:A20
zu summierender Name: "Walter"
Code:B1:B20
Suchmatrix D1:E4

{=SUMME(SUMME(INDIREKT("E"&WENN(ISTNV(VERGLEICH(WENN($A$1:$A$20="Walter";$B$1:$B$20);D1:D4;0));65536;VERGLEICH(WENN(A1:A20="Walter";$B$1:$B$20);D1:D4;0)))))}

verkürzt am 19.02.2002:
{=SUMME(WENN((A1:A20="Walter")*(B1:B20=MTRANS(D1:D4));MTRANS(E1:E4);0))}

Erläuterung
Variante 20.März 2011 von Helmut Raabe

Aus "verkürzt am 19.02.2002:"
{=SUMME(WENN((A1:A15="Walter")*(B1:B15=MTRANS(D1:D4));MTRANS(E1:E4);0))

Vorschlag März. 2011
{=SUMME(MMULT((A1:A15="Walter")*(B1:B15=MTRANS(D1:D4));E1:E4))}

Sie ist einerseits etwas kürzer und klarer.
Jede Datum kommt nur ein einziges mal in der Formel vor :-)
(Die bisherige Lösung hat am Ende eine Zusätzliche 0)

Weil es mir sehr viel Spass gemacht hat, beschreibe ich es gerne mal.

Erklärung der Terme T, Hinweis: Z=Zeile, S=Spalte
(T1) = (A1:A15="Walter") generiert eine boolsche Spalte (Z15 x S1) mit den Einträgen denen "Walter" gefunden wurde (=WAHR).
(T2) = (B1:B15=MTRANS(D1:D4)) generiert eine boolsche Matrix (Z15 x S4), wobei in jeder Zeile nur das Element WAHR ist,
wenn es dem Code entspricht: Also Code="a" -> S1=WAHR;...; Code="d" -> S4=WAHR
Hinweis: Damit es eine Matrix wird, muss MTRANS verwendet werden, da D1:D4 als Spalte vorliegt.

(T3)=(T1)*(T2) ergibt eine Matrix in dessen Elemente nur dann WAHR sind,
wenn in der A:A-Zeile "Walter" gefunden wurde UND auch dessen zugehöriger Code.

Bis hierhin entpricht es der bisherigen Lösung, nun die Änderung:
(T4)=MMULT(T3;E1:E4) Hier findet die Bewertung statt, netterweise ist bereits alles "mundgerecht" aubereitet:
über die Muliplikation, erhalten nur die "wahren" Einträge einen entsprechenden Wert von E1:E4.
PS: über die Matrixmulipliktaion (Zeile * Spalte) findet die Typwandlung von WAHR (Bool) zu 1 (Zahl) statt.
Wie gehabt:
(T5)=Summe(T4) Nur noch alles aufsummieren und fertig.


Die Lösung vom 19.02.2002 behandelt die Typen Bool und Zahl prinzipiell korrekt.
Allerdings findet durch Excel im (T3) eine (nicht notwendige) Typwandlung statt(!): BOOL -> Zahl,
die bei mir spätestens im (T4) auch durchgeführt werden würde.



zurück