HERBERS Excel-Forum - das Archiv
Bewerten Sie hier bitte das Excel-Portal
Matrixformel mit Indirekt() oder welche Alternativ
Roger

Hallo, ich habe folgendes Problem:
an einer Stelle in meinem Workbook habe ich Tagessätze stehen (~20 verschiedene). Jeder der Tagessatz hat einen Namen und normalerweise lassen sich so auch aus Tage * Indirekt(Name des Tagessatzes) einfach Kosten errechnen. Nun möchte ich das Ganze über mehrere Zeilen mit Hilfe der Matrixfunktion durchführen - doch ich bekomme immer den WERT! Fehler. Beispiel:
Zellen A1 bis A10 haben Namen von Tagessätzen. Zellen B1 bis B10 haben jeweils Tage drinstehen. Ich möchte nun die Summe über das Produkt der jeweiligen Zellen aus indirekt(A) * B bestimmen. Ein Ansatz via
{=SUMME((B1:B10)*INDIREKT(A1:A10)) } liefert bedauerlicherweise nur den genannten Fehler. Meine Vermutung: entweder ich mache was falsch oder der INDIREKT() Befehl ist so nicht anwendbar. Für Lösungsvorschläge (gerne auch Alternativen) bin ich sehr dankbar.
MfG Roger

=SUMME((B1:B10)*INDIREKT("A1:A10")) oT
Backowe

AW: =SUMME((B1:B10)*INDIREKT("A1:A10")) oT
Roger

Hi, erst einmal vielen Dank, doch das Ergebnis ist noch immer #WERT! - also nicht das gewünschte. Die Datentypen habe ich schon mal geprüft und auch sonst keine Auffälligkeit feststellen können.
Gäbe es noch eine andere elegante Möglichkeit dies vielleicht mit anderen Befehlen zu lösen? Bin für jede Anregung dankbar.
Gruß
Roger

mE geht das so nicht ...
neopa

Hallo Roger,
... ohne große Verenkungen zu machen, aber ich lass mich gern eines besseren belehren.
Wenn Du wirklich Deine definierten Namen verbal sehen möchtest, dann ermittele deren Inhalt in einer Hilfsspalte mit =INDIREKT(A1) usw. je Zeile und führe Deine Auswertung über diese Hilfsspalte, die Du natürlich ausblendest.
Gruß Werner
.. , - ...

AW: =SUMME((B1:B10)*INDIREKT("A1:A10")) oT
jockel

hi Roger, (d)eine (beipsiel)mappe wäre nicht schlecht... wie hast du denn die tage eingetragen (händisch oder via formel)?
cu jörg

AW: =SUMME((B1:B10)*INDIREKT("A1:A10")) oT
Roger

hi jörg, hi werner,
es handelt sich um ein sehr großes Excelsheet bei dem an dieser Stelle nur für eine bestimmte Kostenart für einen definierten Zeitraum eine Zwischensumme gebildet werden soll - diese wird später weiter verdichtet. Die Tage sind bereits das Ergebnis von entsprechenden Summenformeln und eine Hilfspalte (es würde in diesem Fall zu vielen Hlfsspalten führen) wollte ich gerade eben nicht einbauen.
Wenn ich eine Hilfsspalte verwende, dann funktioniert auch die Matrixformel - daher mein Verdacht das der INDIREKT() - Befehl so nicht in einer Matrixformel anwendbar ist.
Der Vorteil der Benamten Tagessätze ist nicht nur die Lesbarkeit, sondern eine Liste aller möflichen Tagessätze ist wiederum Benamt und darüber via Gültigkeit / Liste und Eingabe von =Bereichsname der Tagessätze in allen Worksheets des Workbooks auch sauber auswählbar. Nur wie komme ich nun zum gewünschten letzten Schritt?

MATRIXFormel mit INDIREKT() ...
neopa

Hallo Roger,
... habe in den letzten 15 Minuten auch keine befriedigende Lösung gefunden. Deshalb markiere ich den Pfad als offen.
Bilde mir ein, vor längerer Zeit mal diesbezgl. etwas gelesen zu haben (evtl. von {Boris}), weiß aber nicht mehr genau wo.
Gruß Werner
.. , - ...

Ich versteh das Problem nicht so ganz...
{Boris}

Hi Werner, Roger und @ all,
...daher wäre ein konkretes Beispiel in der Tat nicht schlecht.
Könnte daran liegen, dass die Namen in A1:A10 auch bereits dynamisch erzeugt wurden - und eine doppelte Dynamik geht nunmal nicht. Oder einfach mal die Funktion N() einsetzen:
{=SUMME(B1:B10)*N(INDIREKT(A1:A10))}
Aber wie gesagt: Alles nur Vermutungen. Eine kleine Beispieldatei würde hier sicher Wunder wirken.
Grüße Boris

AW: Matrixformel mit INDIREKT() und Namen
Erich

Hi Werner, Roger und Boris,
mit den Formeln in C1, C2 (und C4) geht das. (Die Spalten E und H sind überflüssig.)
Bei C6 müsste noch eine Klammer versetzt werden, C7 funzt nicht:
 ABCDEFGH
1elfe2120 11 33
2drei4120 3 55
3fuenf6  5 77
4sieben8120 7 1111
5        
6  220     
7  #WERT!     

Formeln der Tabelle
ZelleFormel
C1{=SUMME(B1:B4*N(INDIREKT(A1:A4)))}
E1=INDIREKT(A1)
H1=drei
C2{=SUMME((B1:B4)*N(INDIREKT(A1:A4)))}
E2=INDIREKT(A2)
H2=fuenf
E3=INDIREKT(A3)
H3=sieben
C4=SUMMENPRODUKT(B1:B4;E1:E4)
E4=INDIREKT(A4)
H4=elfe
C6{=SUMME(B1:B4)*N(INDIREKT(A1:A4))}
C7{=SUMME(B1:B4*INDIREKT(A1:A4))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen
Namen in Formeln
ZelleNameBezieht sich auf
H1drei=Tabelle1!$G$1
H2fuenf=Tabelle1!$G$2
H3sieben=Tabelle1!$G$3
H4elfe=Tabelle1!$G$4

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Matrixformel mit INDIREKT() und Namen
Roger

SUPER, vielen Dank. Wieder was gelernt. Das funzt jeztz :-)))
Mußte in meinem konkreten Fall zwar noch innerhalb der Indirektfunktion die Anführungszeichen setzen, doch es läuft nun wie gewünscht.
Nochmals herzlichen Dank an Alle.
Gruß
Roger

ich könnt mir gleich ...
neopa

Hallo Boris, Erich und @all,
... es ist kaum zu glauben, ich war gestern so nah dran und doch so weit entfernt :-(
Mein gestriger erster Versuch war die Formel in E1. Dieser vergebliche Versuch führte mich dann in immer abwegigere Lösungsversuche, denn ich war überzeugt, dass es eine Lösung gibt aber leider unfähig sie zu finden.
Danke, Boris.
Jetzt such ich nur noch nach der "perfekten" Erklärung, warum mein Versuch nicht das gewünschte Ergebnis bringen konnte. Vielleicht hilfst Du noch mal aus?
 ABCDEFGH
1TagessatzTag  #WERT!   
2zwei503  9900 eins50150
3fünfh3    zweih200
4zwei504    zwei50250
5tausend3    fünfh500
6eins506    tausend1000
7fünfh4      
8eins505      

Formeln der Tabelle
ZelleFormel
E1{=SUMME(--INDIREKT(A2:A8)*B2:B8)}
E2{=SUMME(N(INDIREKT(A2:A8))*B2:B8)}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

noch ein kleiner Nachtrag ...
neopa

Hallo Boris und @all,
... das mich verwirrende an der Angelegenheit ist vor allem, dass man durch Einsatz von [F9] die zwei verschiedenen Formeln E1 und E2 in Formeln überführen kann, die gleiche Ergebnisse liefern (allerdings eben nur in zwei Schritten). Dazu siehe Formeln in F1:G2:
 ABCDEFGHI
1TagessatzTag  #WERT!99009900  
2zwei503  990099009900eins50150
3fünfh3     zweih200
4zwei504     zwei50250
5tausend3     fünfh500
6eins506     tausend1000
7fünfh4       
8eins505       

Formeln der Tabelle
ZelleFormel
E1{=SUMME(--INDIREKT(A2:A8)*B2:B8)}
F1{=SUMME(--{250;500;250;1000;150;500;150}*B2:B8)}
G1{=SUMME({250;500;250;1000;150;500;150}*B2:B8)}
E2{=SUMME(N(INDIREKT(A2:A8))*B2:B8)}
F2{=SUMME(N({250;500;250;1000;150;500;150})*B2:B8)}
G2{=SUMME({250;500;250;1000;150;500;150}*B2:B8)}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: noch ein kleiner Nachtrag ...
Erich

Hi Werner,
erklären kann ich das nicht, aber vielleicht hilft dir das hier (von Boris) etwas weiter:
http://clever-forum.de/read.php?11,145847,145868#msg-145868
(Hier auch der Hinweis zu den nicht so ganz stimmigen F9-Anzeigen.)
Im Zauberbuch (S. 105) steht dazu:
Etwas aus der Reihe tanzen auch die Funktionen N und T,
die ... den INDIREKT-Ausdruck in ein funktionierendes Array umwandeln können.
Grüße von Erich aus Kamp-Lintfort

so wird ein Schuh draus ....
neopa

Hallo Erich,
... und jetzt weiß ich Dank Dir auch wieder, wo ich es schon mal gelesen hatte - nämlich im Zauberbuch. Aber leider ist eben lesen und sich später daran erinnern was und wo, bei mir nicht sehr ausgeprägt und wenn es dann darum geht, gelesenes jederzeit und korrekt anzuwenden, ist es bei mir fast duster.
Danke Dir nochmal. Du hast zumindest aufmerksamer gelesen als ich.
Gruß Werner
.. , - ...