Kreuztabelle "Spezial"

[GMG-CC]

Moin Moin liebe Herler-Kollegen,

jetzt stehe ich einmal etwas auf dem Schlauch und kann mich dank reichlich Paracetamol (wegen eines fiebrigen grippalen Infekts) einfach nicht konzentrieren und finde auch nicht den richtigen Einstieg, bei Mr. Goo oder hier im Forum den optimalen Ansatz zu finden. Dazu kommt, dass ich ja nun nicht wirklich besonders Formel-affin bin ...  .

Für einen Bekannten soll ich da etwas "zaubern" (Zitat: 'Excel-ist-sexy ist doch deine Seite, also kannst du das auch'), was mir zwar mit VBA durchaus schon gelungen ist; aber da es sich um sehr große Datenmengen handelt, gibt es trotz fast reinen Array-Einsatzes (VBA) erhebliche Geschwindigkeits-Probleme. Erstens sind es reichlich mehr Tabellen(blätter) und die Einzeltabellen sind auch noch um einiges größer. Ach ja, und es sollen "natürlich" keine Makros sein.  :s Als Annahme sage ich einmal, dass es sich bei den Daten (Kreuztabelle.xlsx) um unterschiedliche Kurier-Unternehmen handelt.

Weiterhin kommen noch einige Besonderheiten hinzu:

• Es sind etwa 5 unterschiedliche Anbieter, deren Daten alle in solch einer Kreuztabelle aufbereitet sind.
  
• Die Preise der Anbieter unterscheiden sich NICHT linear; für weitere Zielorte kann mitunter ein günstigerer Preis angeboten werden, weil (beispielsweise) der Kurierdienst sowieso dort jeden tag hinfährt.
  
• Die Städte sind prinzipiell gleich, aber nicht jeder Kurierdienst bietet alle Städte als Ziel an. In der Realität sind es erheblich mehr Staädte. Bietet der Dienst diese Stadt nicht an, dann Rückgabe 0 oder "Text"
  
• Die Gewichtsangaben sind immer als "bis zu", es soll also stets der erste >=-Wert gefunden werden. Wird die maximale Gewichtsgrenze des Anbieters überschritten, dann Rückgabe 0 oder "Text"
  
• Die einzelnen Tabellen/Listen liegen in unterschiedlichen Blättern, ich habe sie aber auch in 1 Liste zusammengefasst (File: 1 Liste); hier natürlich nur die 1 Liste entsprechend aufbereitet. In Natura sind das >13400 Zeilen.
  
• Das Ziel: Eine Aufstellung von mehreren tausend Einzelfahrten zu oft den gleichen Einzel-Zielen soll (mit Excel, nicht mit einer DB) ausgewertet werden und entweder soll ein Kurierdienst ausgewählt werden, der für die Gesamtheit der Fahrten am preiswertesten ist oder es werden bestimmte Zielorte ausgeeinzelt oder zusammengefasst.
  

Und warum bin ich ausgesprochen schläfrig aber dennoch wach? Ich weiß es nicht ...
Jetzt schon einmal mein "Dankeschön" an alle Leser, Helfer, Willige, ...

[BoskoBiati]

Hi Günter,

hier kommt eine unangenehme Eigenschaft der intelligenten Tabellen zur Geltung: Spaltenköpfe sind keine Zahlen! Ich habe die Tabelle aufgelöst. dann ginge das folgendermaßen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1	Ziel	Gewicht	Preis1	Preis2	Preis3	Preis4
2	HH	100	25
3	F	100	kein Ziel
4	HH	1100	zu schwer

Zelle	Formel
C2	=WENN(ZÄHLENWENN(Kreuztabelle!B:B;A2)=0;"kein Ziel";WENN(B2>MAX(Kreuztabelle!1:1);"zu schwer";INDEX(Kreuztabelle!$C$2:$J$11;VERGLEICH(A2;Kreuztabelle!$B$2:$B$11;0);WENNFEHLER(VERGLEICH(B2-1%;Kreuztabelle!C1:J1;1);0)+1)))

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

Das könnte man für alle Sendungen und Anbieter als Kreuztabelle erstellen und mit Pivot oder evtl. PowerQuery (kenne ich noch nicht) auswerten

[Fennek]

Hallo,

wenn das Ziel (Zitat)

Code:

Das Ziel: Eine Aufstellung von mehreren tausend Einzelfahrten zu oft den gleichen Einzel-Zielen soll
(mit Excel, nicht mit einer DB) ausgewertet werden und entweder soll ein Kurierdienst ausgewählt werden,
der für die Gesamtheit der Fahrten am preiswertesten ist...

bräuchte man da nicht Daten von mindestens 2 Anbietern?

mfg

[Der Steuerfuzzi]

Hallo Günther,

ich würde eine Tabelle mit den auszuwertenden Sendungen ertsellen. Diese müsste auch die jeweilige Gewichtskategorie enthalten. Dann über Power Pivot die entpivotisierte Tabelle mit der Sendungstabelle per join anhand Ort und Gewicht (als Primärschlüssel) verknüpfen. Diese Tabelle kannst Du dann per Pivot auswerten. Ich habe Dir mal eine Datei beigefügt.

[neopa]

Hallo Günther,

PoverPivot habe ich noch nicht im Einsatz.

Aber man kann Dein Anliegen auch nur mittels Formel(n) lösen. Dazu bedürfte es auch keiner Entpivotierung. Wenn Du eine solche zur Zusammenfassung aller Anbieter-Kreuztabellen jedoch vornehmen willst, dann kannst Du z.B. direkt in diesem Tabellenblatt auch folgende Formellösung einsetzen (Formel nach rechts und unten kopieren):

Entpivotiert

	F	G	H	I	J
1	Ziel	Gewicht	Anbieter 1	Anbieter 2	Anbieter 3
2	HH	100 kg	25,00 €	kein Ziel	kein Ziel
3	FL	105 kg	82,50 €	kein Ziel	kein Ziel
4	HH	995 kg	70,00 €	kein Ziel	kein Ziel
5

Formeln der Tabelle

Zelle Formel H2 =WENN(ZÄHLENWENNS($A:$A;H$1;$B:$B;$F2)=0;"kein Ziel";WENN($G2>AGGREGAT(14;6;$C$2:$C$999/($A$2:$A$999=H$1)/($B$2:$B$999=$F2);1);"zu schwer";INDEX($D:$D;AGGREGAT(15;6;ZEILE(A$2:A$39)/($A$2:$A$39=H$1)/($B$2:$B$39=$F2)/($C$2:$C$39>=$G2);1))))

Die Gesamtauswertung dieser Ergebnisse ist dann sicher kein Problem mehr.

Wenn Du die Original-Kreuztabelle(n) jedoch beibehalten willst, bedarf es der von Edgar vorgeschlagenen Auflösung der intelligenten Tabellen nicht, denn dies könnte man auch mit der Formellösung abfangen:

Tabelle1

	A	B	C	D
1	Ziel	Gewicht	Anbieter 1	Anbieter 2
2	HH	50	22,50 €	kein Ziel
3	FL	100	70,00 €	kein Ziel
4	HH	1100	zu schwer	kein Ziel
5

Formeln der Tabelle

Zelle Formel C2 =WENN(ZÄHLENWENNS(Kreuztabelle!$A:$A;C$1;Kreuztabelle!$B:$B;A2)=0;"kein Ziel";WENN(B2>AGGREGAT(14;6;--WECHSELN(Kreuztabelle!A$1:J$1;"kg";"");1);"zu schwer";INDEX(Kreuztabelle!$A:$J;VERGLEICH(A2;Kreuztabelle!$B:$B;);AGGREGAT(15;6;SPALTE($C1:$J1)/(--WECHSELN(Kreuztabelle!$C$1:$J$1;"kg";"")>=B2);1))))

[GMG-CC]

Moin Moin,

ich möchte mich nur ganz kurz als "immer noch unter den lebenden" melden. 
Ich liege zwar noch stramm im Bett und kuriere mich aus - hoffe aber, dass ich heute Abend so weit wieder auf den Beinen bin, dass ich mir das Ganze einmal zu Gemüte führen kann. 
Beim Überfliegen fiel mir auf: Ja es sind mehrere Anbieter, ich hatte nur einen als Muster eingestellt. Mal sehen, dass ich im Laufe des Tages ein, zwei weitere Anbieter mit zufallsgenerierten Zahlen erstelle. Das ist ja nicht der Riesenaufwand.
Jetzt schon einmal Dankeschön, und bis denne ...

[snb]

Oder array Formula

PHP-Code:

=SMALL(($B$2:$B$21=$A25)*INDEX(A2:J21;0;MATCH($B25;$A$1:$J$1;1)+1);SUM(N($B$2:$B$21<>$A25))+1) 


[neopa]

Hallo snb,

offensichtlich versuchst Du die Aufgabe so zu interpretieren, nur den billigsten Preis je "Produkt" zu ermitteln.
Meist interessiert aber mE der Anbieter mit nachvollziehbar ausgewiesenen Einzelpreisen und davon dann preiswertesten Angebotssumme für die komplette "Produktpalette".

Der billigste Einzelpreis ist jedoch gemäß Tabelle für HL 55€ und nicht 95€. Auch wird mit Deiner Formel für HH und 100 kg schon 26,5€ ausgewiesen. Die  Angabe von Günther:

Die Gewichtsangaben sind immer als "bis zu", es soll also stets der erste >=-Wert gefunden werden.

kann man so interpretieren. Ich interpretierte sie und momentan auch noch anders. Aber egal ob eine entsprechende Anpassung in Deiner oder z.B. in meiner Formel notwendig wird, diese wäre einfach möglich.
Vorgenanntes jedoch bedarf einer entsprechenden Aussage von Günther bzw. dessen "AG".

[snb]

Dann könnte es so gehen:

PHP-Code:

=SUMPRODUCT((Ziel_2=$A25)*(COLUMN(gewicht)-2=MATCH($B25;gewicht;1))*INDIRECT(D$23)) 


[Der Steuerfuzzi]

Dann über Power Pivot ...

Sorry, vertippt, ich meinte natürlich Power Query. Auswertung erfolgt dann über (einfache) Pivot.