Live-Forum - Die aktuellen Beiträge
Datum
Titel
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
23.04.2024 17:41:19
Link erstellen mit Formel oder VBA?
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1556to1560
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1556to1560
1556to1560
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVerweis mit Sortierung

SVerweis mit Sortierung
15.05.2017 11:07:57
stormlamp
Hallo Zusammen,
ich habe eine Exceltabelle, in der in jeder Zeile eine Bestellung steht.
In Spalte A immer die Bestellnummer. Dahinter in den Spalten die bestellten Artikel und jeweils dahinter die Menge. Die Artikel stehen jedoch nicht in der gleichen Reihenfolge. Beispiel: 
Bestellung
4711	Äpfel	100	Bananen	30	Birnen	300
4712	Birnen	30	Äpfel	70	Bananen	30
4713	Äpfel	200	Birnen	50	Bananen	50
4714	Bananen	150	Äpfel	100	Birnen	80
4715	Birnen	60	Äpfel	200	Bananen	100

Nun möchte ich die Daten in eine neue Tabelle bringen und die daten mit SVerweis übertragen. llerdings soll dabei gleichzeitig sortiert werden.
Das Ergebnis sollte dann so aussehen: 
Bestellung	Äpfel	Birnen	Bananen
4711	        100	300	30
4712	        ?	?	?
4713	        ?	?	?
4714	        ?	?	?
4715	        ?	?	?

Wie müssen die Formeln dazu aussehen?
Freundlichen Gruß
Hans

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: SVerweis mit Sortierung
15.05.2017 11:16:18
ChrisL
Hi Hans
https://www.herber.de/bbs/user/113587.xlsx
=SUMMEWENNS($C:$C;$A:$A;$I2;$B:$B;J$1)+SUMMEWENNS($E:$E;$A:$A;$I2;$D:$D;J$1)+SUMMEWENNS($G:$G;$A:$A; $I2;$F:$F;J$1)
cu
Chris
AW: SVerweis mit Sortierung
15.05.2017 11:24:20
stormlamp
Hallo Chris,
danke für den Vorschlag. Es sind ca. 30-40 verschiedene Artikel. Dann wird das vermutlich mit der Formel nicht machbar sein. Es wäre prima, wenn es eine allgemein gültige Formel gäbe, die ich in alle Zellen kopieren kann und die den Wert aus der ersten Zeile in der Suche berücksichtigt.
Gruß
Hans
AW: SVerweis mit Sortierung
15.05.2017 11:30:08
stormlamp
Hallo Bernd,
das passte noch nicht ganz, da ich in Deinem Vorschlag jeweils die Zeilennummer manuell eintragen muss.
Die sollte jedoch mittels SVerweis aufgrund des Eintrags in Spalte A gezogen werden.
Gruß
Hans
gelöst
15.05.2017 11:54:33
stormlamp
Hallo Zusammen,
hier die Lösung, indem ich Daniel Vorschlag erweitert habe:
=WENNFEHLER(INDEX(Tabelle1!$A:$G;VERGLEICH($A2;Tabelle1!$A:$A);VERGLEICH(B$1;INDEX(Tabelle1!$A:$G; VERGLEICH($A2;Tabelle1!$A:$A);0);0)+1);0)
Gruß
Hans
Anzeige
AW: warum hier überhaupt die Hilfsspalte? ...
15.05.2017 19:16:05
...
Hallo Daniel,
... die Formel =VERGLEICH($K2;$A:$A;0) hätte man doch auch in die Ergebnisformel noch einsetzen können, selbst bei 40 Artikeln und 100 Bestellungen sollte da die Geschwindigkeitseinbuße kaum messbar sein.
Und so wie das die Fragestellung aussah, hätte gar als Ergebnisformel
=WENN($K2="";"";INDEX($A:$G;;VERGLEICH(L$1;2:2;)+1)) ausgereicht.
Gruß Werner
.. , - ...
Besser ist das
16.05.2017 07:43:36
Daniel
Weil das Formelwerk kürzer und einfacher wird, wenn man Teilformeln, die mehrfach mit gleichem Ergebnis vorkommen, in eine Hilfsspalte oder -Zeile aus lagert.
In der Programmierung würde man dann ja auch den berechneten Wert in eine Variable schreiben anstatt ihn jedesmal neu zu berechnen.
Wer die Hilfsspalte nicht mag, kann ja einfach den Zellbezug auf die Hilfszelle durch die Formel in dieser Zelle ersetzen.
Gruß Daniel
Anzeige
Das solltest du aber seit der WENN-Diskussion ...
16.05.2017 14:34:31
Luc:-?
…wissen, Daniel,
dass hier höchstwahrscheinlich die in der Xl-Hilfe erwähnte Fml-Optimierung zuschlägt und diese Fml-Teile auch nur 1× berechnet. Man spart also eigentlich nur etwas SchreibAufwand, den man aber auch per benannter TeilFml (oder Merk-UDF) einsparen könnte, wodurch auch eine komplexere Fml mit mehrfach verwendeten TeilFmln übersichtlicher wird — ein in der Tat nicht zu unter­schät­zen­des Kriterium. Damit wird ein solches Konstrukt einer HilfsspaltenLösung zumindest gleichwertig.
Gruß, Luc :-?
Besser informiert mit …
Anzeige
Das WENN ist was anderes.
16.05.2017 16:13:43
Daniel
über den Rechenaufwand habe ich auch nichts geschrieben.
was konkret meinst du mit Fml-Optimierung?
würde das bedeuten, dass die Formel
  • =VERGLEICH("x";A:A;0)/VERGLEICH("x";A:A;0)

    • die gleiche Rechenzeit erfordert wie die Formel:
  • =VERGLEICH("x";A:A;0)/1

    • weil sich die Formeloptimierung das Ergebnis der Teilfunktion VERGLEICH("x";A:A;0) merkt und daher nicht neu berechnet?
    bei mir braucht die erste Formel genau doppelt so lang wie die zweite, was eigentlich gegen deine Vermutung sprechen würde. (zum testen das "x" möglichst weit unten platzieren, um messbare Rechenzeiten zu erzeugen)
    außerdem steigt die Rechenzeit proportional mit der Anzahl der Zellen, in die diese Formel kopiert wird.
    All das lässt vermuten, dass du mit deiner Theorie höchstwahrscheinlich falsch liegst.
    Gruß Daniel
    Anzeige
    Das kann durchaus sein, ...
    16.05.2017 18:37:46
    Luc:-?
    …denn auch bei WENN gab's ja Ausnahmen, Daniel;
    ich gehe deshalb mittlerweile davon aus, dass die Fmln zwar optimiert wdn, das aber Grenzen hat. Früher hatte ich die gleiche Auffassung wie du, aber die WENN-Erfahrung hat mich dazu gebracht, auch eine Opti­mierung über die nachweisliche Matrix­Ele­ment-Va­riation bei skalaren Argumenten hinaus anzunehmen. Aber alles hat seine Grenzen und mit mitlfd Variablen und FmlTextVgl scheint die Xl-Optimierung nicht grundsätzlich zu arbeiten, obwohl das ja möglich wäre. Aber evtl verschuldet auch genau das die zusätzliche Rechenzeit. Andererseits konnte ich an UDFs beobachten, dass Xl mitunter erst eine „LeerRechnung“ durchführt, also in solchen Fällen ohnehin 2× rechnet. Das könnte bei komplexeren Fmln ja auch immer der Fall sein, so dass man ggf mehr sagen könnte, wenn dieselbe TeilFml wenigstens 3× in der einen und 2× in einer anderen Fml verwendet wird.
    Luc :-?
    Anzeige

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

    Beliebteste Forumthreads (12 Monate)

    Anzeige
    Anzeige
    Anzeige