Tabelle vervollständigen mithilfe von Matrixformel

[Blub]

Hallo zusammen,

Ich möchte, durch Auswahl eines Lieferanten (K2), alle Bestellungen der letzten zwei Jahre angezeigt bekommen. Dies habe ich mir mithilfe der Matrixformel bereits hergeleitet.
Ich leite mir durch die Bestimmung der relevanten Zeile die zugehörigen Werte anzeigen.

Doch leider kann ich nicht weiter als Zeile 650 in meiner Übersicht anzeigen lassen. Die gesamte Tabelle ist 3806 Zeilen lang und soll noch länger werden.

Auszug aus den Spalten:
I7:   {=WENNFEHLER(KKLEINSTE(WENN(($E$2:$E$650)=$K$2;ZEILE($B$2:$B$650)-1);ZEILE(B1));"")}
J7:   =WENNFEHLER(INDEX($B$2:$B$3806;$I7);"")
K7:   =WENNFEHLER(INDEX($C$2:$C$3806;$I7);"")
usw.

Wenn ich den Bereich der Matrixfunktion (I7) auf 700 erweitere, zeigt er mir ein leeres Feld an. Er findet also einen Fehler.
Was muss ich ändern, damit der Bereich bis Zeile 3806 angezeigt wird?

Ich könnte einen weiteren Filter (Quartal) einfügen, um die auszuwerfenden Zeilen auf 650 zu reduzieren, doch für das aktuelle Quartal würde das größentechnisch nicht hinhauen, da ich bereits 740 Positionen habe.

Danke für eure Hilfe!!

[steve1da]

Hola,

deine Anpassung funktioniert nicht, da in E695, E720 und E721 #WERT steht.

Ohne Abschluss als Matrixformel:

Code:

=WENNFEHLER(INDEX($A$2:$A$3806;AGGREGAT(15;6;ZEILE($A$2:$A$3806)-1/($E$2:$E$3806=$K$2);ZEILE(A1)));"")

Gruß,
steve1da

[Blub]

Hi steve1da,

vielen Dank für die schnelle Antwort. Ich wäre niemals darauf gekommen, dass der Fehler an einer leeren Zeile liegt.

Jetzt klappt es. Freut mich aber, dass die Formel im Grunde gestimmt hat :)



Danke und noch eine schönen Tag!

[steve1da]

Hola,

dass der Fehler an einer leeren Zeile liegt.

die Zellen sind nicht leer, da stehen Fehlerwerte drin.

Gruß,
steve1da

[Jockel]

Hallo {Matrix}formelfrei geht's mit Power Query...:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H
1	Kunde		Rec#	Datum	Rechnung	Artikel-Bezeichnung	Stück	Preis
2	BenData GmbH		219	01.03.2017	CE740A	HP 307A Schwarz Tonerkartusche für Color Laserjet CP5225, ca. 7.000 Seiten ANGEBOT Nr.: 8701374	1
3			220	01.03.2017	CE742A	HP 307A Gelb Tonerkartusche HP Color Laserjet CP5225, ca. 7.300 Seiten ANGEBOT Nr.: 8701374	1

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[Blub]

Hi Leute,

habe noch eine Folgefrage zu dem Thema: Ich habe die Formel auf drei Variablen erweitert, die ich entsprechend auswählen kann (Lieferant, Quartal, Jahr).
Ich möchte die Variable Quartal mit "Total" erweitern, sprich eine Gesamtauswahl des Jahres '17 oder '18 erhalten.

=WENNFEHLER(INDEX($A$2:$A$3806;AGGREGAT(15;6;ZEILE($A$2:$A$3806)-1/(($G$2:$G$3806=$N$2)*($B$2:$B$3806=$P$2)*($C$2:$C$3806=$Q$2));ZEILE(A1)));"")

 N2=Lieferant, P2=Quartal, Q2=Jahr

Alle Werte sind in der Ursprungstabelle vorhanden und können über die Auswahl der Spalte ausgewählt und zusammengeführt werden.
Wie umgehe ich nun den Quotienten (B2:B3806=P2) und erreiche, dass mir eine Jahresübersicht angezeigt wird? Ich habe eine Spalte (D) eingefügt, in der der Wert "total" steht und würde über eine Wenn/oder Funktion gehen:

WENN(ODER(P2="Q1";"Q2";"Q3";"Q4");B2:B3806=P2;D2:D3806=P2) 

d.h.

=WENNFEHLER(INDEX($A$2:$A$3806;AGGREGAT(15;6;ZEILE($A$2:$A$3806)-1/(($H$2:$H$3806=$N$2)*(WENN(ODER(O2="Q1";"Q2";"Q3";"Q4");($B$2:$B$3806=$P$2);$D$2:$D$3806=P2)*($C$2:$C$3806=$Q$2));ZEILE(A1)));"")

Die Zeile lässt sich jedoch nicht abschließen. Irgendwo ist ein Fehler.

Bin ich auf der richtigen Spur oder habt ihr eine andere Idee? Power Query ist für mich erst mal keine Option ;)

Vielen Dank
Blub

[steve1da]

Hola,

warum versuchst du mit ODER 4 Bedingungen zu definieren, wenn du doch eh alle Quartale abfragst?
Das mit dem "Total" hab ich nicht ganz verstanden. Abfragen auf Jahre würde man so machen:

Code:

=WENNFEHLER(INDEX($A$2:$A$3806;AGGREGAT(15;6;ZEILE($A$2:$A$3806)-1/(($G$2:$G$3806=$N$2)*(Jahr(BereichXY)=2018)*($B$2:$B$3806=$P$2)*($C$2:$C$3806=$Q$2));ZEILE(A1)));"")

Gruß,
steve1da

[Blub]

Hi steve1Da,

Ich suche eine Möglichkeit, die bisherige Bindung an alle drei Quotienten aufzubrechen, indem ich die Möglichkeit habe, nur nach "Lieferant" und "Jahr" zu suchen, sodass mir alle Werte des Jahren (z.B.) 2017 zu einem bestimmten Lieferanten angezeigt werden.

ich hatte mir überlegt, die Gleichung durch eine Bedingung des Quartals aufzubrechen, indem mir die Gleichung bei Auswahl Q1-Q4 das jeweilige Quartal anzeigt (Lieferant*Quartal*Jahr), bei der Auswahl "total", das gesamte Jahr (Lieferant*Jahr). Dazu muss das Quartal aus dem Quotienten rausfallen.

Alternativ war meine Idee, im Falle der Auswahl "total", die Formel des Quotienten mit "1" gleichzusetzen, aber dann sucht die Aggregatsfunktion nach der "1" in der Quartalsspalte und findet natürlich nichts.

Ist das klarer geworden?

ich kann die Liste leider nicht hochladen, da sie zu groß ist und ich sie, aus welchen Gründen auch immer, nicht komprimiert bekomme...

Grüße

[WillWissen]

Hallo,

ich kann die Liste leider nicht hochladen, da sie zu groß ist und ich sie, aus welchen Gründen auch immer, nicht komprimiert bekomme...

wie wär's, die Tabelle (natürlich eine Kopie davon) auf wenige Datensätze zu reduzieren? Und falls sensible Daten drin sind, diese zu anonymisieren?

[Blub]

Anbei die Tabelle mit reduzierter Anzahl an Werten.

Da klappt die Hilfe sicher besser =)

Grüße und Danke!