Abrechnungsdatei anhand Matrixsuche - Hilfe! :)

[SBieteh]

Hallo liebe Excel-Community,

ich versuche aktuell eine Abrechnungsdatei für unsere Kunden, basierend auf einem Report zu erstellen.

Meine Beispieldatei ist wie folgt aufgebaut:

In der Arbeitsmappe "Tabelle 1" habe ich drei verschiebe Matrixtabellen.

In Zeile 1 stehen die Kunden "S1", "S2" und "S3"

In Zeile 2 steht der Wert, der für für den jeweiligen Kunden in diesem Monat abgerechnet wird und dann anhand der Tabelle abgelesen werden muss.

In Zeile 3 haben wir dann der Servicetyp, welcher bei jedem Kunden entweder "ja" oder "nein" sein kann. Je nach Servicetyp gibt es auch noch einen anderen Typ. Dieser ist in Zeile 4 zu sehen.

In Zeile 4 Wie oben schon gesagt, gibt es noch drei "Untertypen" mit "T1", "T2" und "T3".

In der Arbeitsmappe "Tabelle 2" habe ich dann meine monatlichen Daten. Hier möchte ich mit einer Formel oder wie auch immer (ich habe leider überhaupt keine Idee), dass in Spalte E, die jeweiligen Kosten automatisch hinterlegt werden. Die Basis der Kosten bildet der Wert, welcher in Zeile 2 steht. Wird dieser geändert, so sollen die Preise anhand der Matrixtabelle automatisch angepasst werden.

Es wird also immer unterschieden zwischen: Welcher Kunde (S1,S2,S3)?, handelt es sich hier um Servicetyp "ja" oder "nein" ?, welcher Untertyp (T1,T2,T3) ist eingetragen?

Ich hoffe ihr könnt mir helfen

1000 Dank schon mal im Vorraus!

[Storax]

... Meine Beispieldatei ist wie folgt aufgebaut: ...

Welche Beispieldatei?

[MisterBurns]

Anhand der Beschreibung fällt mir der WVERWEIS ein - mehr ist ohne Beispielmappe nicht zu sagen.

[SBieteh]

Anbei die Datei. Hab hier wohl nicht auf hinzufügen gedrückt beim ersten Mal

[WillWissen]

Hi,

deine Tabelle ist mehr als suboptimal aufgebaut. Schreibe alle Daten in eine Liste, dann ist das Auswerten relativ einfach. In eine Liste gehören auch keine verbundene Zellen. Hier mal zu den beiden angesprochenen Fehlern etwas Lesestoff:

Listen: https://www.online-excel.de/excel/singsel.php?f=52
Verb. Zellen: https://www.clever-excel-forum.de/Thread...oder-Segen

[MisterBurns]

Abgesehen vom bereits erwähnten ganz ganz ganz schlechten Tabellenaufbau... ich verstehe die Anforderung nicht ganz. In Zeile 2 steht ein Wert. Und welche Kosten sollen nun abgefragt werden? Was wäre für das Beispiel das richtige Ergebnis?

[SBieteh]

Also die zwei Links zu den vermeintlichen Fehlern kann ich mir morgen erst anschauen.

Zu dem Tabellenaufbau.

Vornweg: Das mit den verbundenen Zellen dachte ich mir auch, aber als ich dann ein Beispiel durchgerechnet habe mit der verbundenen Zelle und dies funktioniert, hab ich es so gelassen.

Im zweiten Reiter "Tabelle2" stehen meine Abrechnungsdaten. In Spalte E sollen die jeweiligen Kosten berechnet werden.

Ich nehme jetzt mal als Beispiel die Zeile 2.

ID        Kunde   Service     Typ    Kosten
ID100   S1         nein          T3     55,00 €

Anhang der Preismatrix für den Kunden S1, welcher bei einem aktuellen Wert von 75 abgrechnet wird, entstehen Kosten in Höhe von 55,00 €

ein weiteres Beispiel zur Veranschaulichung

ID        Kunde   Service     Typ    Kosten
ID700   S2         ja            T1      220,00 €


Der Kunde S2 wird aktuell mit einem Wert von 80 abgerechnet, somit wären die Kosten laut Preismatrix 220,00 €.

Jetzt kann es sein, dass der Kunde S2 im nächsten Monat nicht mehr mit dem Wert 80 abgerechnet wird, sondern mit dem Wert 90.

Demzufolge lägen die Kosten bei dem gleichen Beispiel bei 140,00 €

[shift-del]

Jetzt kann es sein, dass der Kunde S2 im nächsten Monat nicht mehr mit dem Wert 80 abgerechnet wird, sondern mit dem Wert 90.

Warum steht der Abrechungswert nicht in Tabelle2 mit den anderen monatlichen Daten? Aber du wirst schon wissen was du tust.
Wenn der Wert auf Zeile 7 oder Zeile 8 fällt ist das Ergebnis immer 0. Aber du weißt ja was du tust.

Tabelle2

	A	B	C	D	E	F	G	H	I	J	K	L
1	ID	Kunde	Service	Typ	Kosten		Pos. Kunde	Pos. Service	Pos .Typ	Spalte	Abrechnungswert	Zeile
2	ID200	S1	nein	T3	55		1	1	3	5	75	9
3	ID200	S2	nein	T2	230		10	1	2	13	80	10
4	ID300	S3	ja	T3	1200		19	4	3	26	85	11
5	ID400	S2	nein	T1	165		10	1	1	12	80	10
6	ID500	S2	ja	T1	220		10	4	1	15	80	10
7	ID600	S1	ja	T2	90		1	4	2	7	75	9
8	ID700	S2	ja	T1	220		10	4	1	15	80	10
9	ID800	S3	nein	T3	660		19	1	3	23	85	11
10	ID900	S3	ja	T2	340		19	4	2	25	85	11
11	ID1000	S1	nein	T1	170		1	1	1	3	75	9

Formeln der Tabelle

Zelle Formel E2 =INDEX(Tabelle1!$6:$19;L2;J2) G2 =VERGLEICH(B2;Tabelle1!$1:$1;0) H2 =VERGLEICH(C2;Tabelle1!$C$3:$H$3;0) I2 =VERGLEICH(D2;Tabelle1!$C$4:$E$4;0) J2 =G2+H2+I2 K2 =WVERWEIS(B2;Tabelle1!$1:$2;2;FALSCH) L2 =VERGLEICH(K2;Tabelle1!$A$6:$A$19;1)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[SBieteh]

Hallo shift-del,

vielen Dank für Deine Hilfe. Ich weiß leider nicht was zu tun ist, deswegen bin ich doch hier
Ich kenn mich leider nicht so gut aus in Excel und wäre auch nie auf Deine Lösung gekommen.

Gibt es auch eine Möglichkeit das ohne Hilfsspalten zu lösen oder würde das die Formel sprengen?

Nochmal Danke!

Gruß
SBieteh

[shift-del]

Die Hilfsspalten sind dazu da um die Formel leichter zu verstehen.
Du kannst selbstverständlich die Formel ineinander verschachteln.

Im übrigen liegst die Komplexität der Formel - wie bereits angemerkt -  an dem Tabellenaufbau.