Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Matrixformel nach Spalte sortieren
19.08.2022 18:33:05
Christian
Hallo
ich lese aus einem Datenblatt mit der Matrixformel
{=WENNFEHLER(INDEX(Zwischenergebnis!$A$2:$A$3001;KKLEINSTE(WENN(Zwischenergebnis!$A$2:$A$3001""; ZEILE($1:$3000);"");ZEILE(A1)));"") }
aus um alle Leerzeilen raus zu fischen. Kann ich die auch gleichzeitig noch nach dem Wert in Spalte F im Tabellenblatt Zwischenergebnis sortieren lassen? Steh irgendwie auf dem Schlauch :-).
Danke schon mal und Grüße
Christian

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: sicherlich ...
19.08.2022 18:52:45
neopa
Hallo Christian,
... doch (D)eine kleine Beispieldatei mit ein paar Daten in Spalte A und F und den von Dir angestrebten Ergebnis wäre angebracht.
Gruß Werner
.. , - ...
AW: sicherlich ...
19.08.2022 19:16:02
Christian
Hallo. Ich hab die Datei mal klein gemacht und hochgeladen. Im Blatt Wiedervorlage soll dann alles nach PLZ sortiert erscheinen.
https://www.herber.de/bbs/user/154730.xlsx
Danke und Grüße schon mal vorab :-)
AW: mit INDEX(), AGGREGATund ...
19.08.2022 20:12:25
neopa
Hallo Christian,
... Du wolltest nach PLZ sortieren, deshalb unten stehend zuerst die Formel in D3 einsetzen, dann die Formel in A3 und danach die Formel in B3 und diese nach C3 kopieren.
Ich sehe gerade, man könnte die Ergebnisse auch ohne das Hilfstabellenblatt Zwischenergebnis ermitteln. Dazu müsste lediglich die Auswertungsformeln in D3 und A3 erweitert werden. Aber das schaffe ich zeitlich nicht mehr. Bin sofort offline und erst übernächste Woche wieder online. Bei Rückfragen helfen Dir sicherlich andere hier weiter oder Du müsstes dann einen neuen thread aufmachen.
Arbeitsblatt mit dem Namen 'ErgebnisWiedervorlage'
 ABCD
2ErgNrName InstitutAnsprechpartnerPLZ
35Test 24Frau 2422175
46Test 9 52345
59Test 13Herr 1356463
68Test 14Herr 1463548
77Test 15Herr 15CH-78654
8    

ZelleFormel
A3=WENNFEHLER(AGGREGAT(15;6;Tabelle3[ZENR]/(Tabelle3[ZENR]>"")/(Tabelle3[PLZ]&""=[@PLZ]);ZÄHLENWENN(D$3:D3;D3));"")
B3=WENNFEHLER(SVERWEIS($A3;Tabelle1[[Nr]:[Ansprechpartner]];SPALTE();0)&"";"")
C3=WENNFEHLER(SVERWEIS($A3;Tabelle1[[Nr]:[Ansprechpartner]];SPALTE();0)&"";"")
D3=WENNFEHLER(WECHSELN(AGGREGAT(15;6;WECHSELN(Tabelle3[PLZ];"CH-";999999)/(Tabelle3[ZENR]>"");ZEILE(A1));999999;"CH-");"")
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: zur Information ...
20.08.2022 07:07:11
neopa
Hallo Christian,
... bin nun außerplanmäßig doch nicht offline.
Gruß Werner
.. , - ...
AW: zur Information ...
20.08.2022 09:53:16
Christian
Hallo Werner,
super, danke Dir! Hat schon mal 1a funktioniert. Ich hoffe Dir ist nicht der Urlaub geplatzt :-)?
Ein Problem hab ich noch. Die PLZ ist im Eingabeblatt, Zwischenergebnis und WiedervorlageErgebnis Blatt als Text formatiert. Da ich deutsche, österreichische und schweizer Postleitzahlen brauche kann ich, glaube ich, kein anderes Format nehmen. Wenn ich eine PLZ aus dem deutschen PLZ-Bereich 0XXXX nehme ist die führende 0 im Eingabeblatt und auch im Zwischenergebnis da, im Blatt Wiedervorlage ist die allerdings verschwunden und die PLZ ist nur noch 4-stellig.
Hast Du dazu auch noch eine Lösung im Köcher :-)?
Grüße
Christian
Anzeige
AW: textformtierte Zellen vermeiden ...
20.08.2022 11:04:42
neopa
Hallo Christian,
... diese sollten stets die absolute Ausnahme sein, denn eine solche ist fast nie wirklich notwendig und verursacht lediglich Probleme.
Warum nutzt Du nicht das Standardzahlenformat und weist jeder PLZ die Länderkennung zu? Wenn Du es für die deutschen PLZ ohne dieses tun willst, bietet sich das z.B. benutzerdefinierte Zahlenformat 00000 an.
Gruß Werner
.. , - ...
AW: textformtierte Zellen vermeiden ...
20.08.2022 12:53:23
Christian
Hallo Werner. Der nervige Typ noch mal :-)
Ich habe jetzt in der Eingabe noch ein Feld "Land" hinzugefügt. Im Zwischenergebnis das Land als Spalte G dazu gepackt (D,CH oder A). Jetzt müsste nur die Wiedervorlage erst nach Land und dann nach PLZ sortiert werden. Geht das auch? 2 Sortierkriterien in der Rangfolge? Sorry, aber Aggregat Funktion ist mir immer noch ein kleines Rätsel.
In Eingabe und Ergebnis als Zahl formatiert mit 0 Kommastellen und bedingte Formatierung WENN LAND = D dann 00000. So füllt er für Deutschland die Postleitzahlen alle auf 5 Stellen auf und lässt die für CH und A 4-stellig.
LG
Christian
Anzeige
AW: (D)eine neue Beispieldatei wäre hilfreich owT
20.08.2022 14:11:53
neopa
Gruß Werner
.. , - ...
AW: (D)eine neue Beispieldatei wäre hilfreich owT
20.08.2022 16:09:21
Christian
Hallo Werner,
die Datei hab ich hochgeladen
https://www.herber.de/bbs/user/154743.xlsx
Im Blatt Wiedervorlage soll jetzt die Sortierung zuerst nach Land und dann nach PLZ erfolgen. Ich bin überfordert :-)
Danke und Grüße
Christian
AW: als Formellösung ...
20.08.2022 17:28:22
neopa
Hallo Christian,
... ist die Realisierung auch nicht ganz ohne. Einfacher ist es, so behaupte ich jetzt mal (ohne erfolgten Nachweis) ganz ohne Formeln mit der Power Query Funktionalität von Excel. Allerdings hat mich gerade die Formellösung gereizt. Um die Formel ein wenig kürzer zu halten habe ich als erstes die Überschrift in Eingabe!G1 gekürzt zu nur "Datum VW" und den Text "(nur Datum oder immer)" in ein Textfeld mit gleicher Hintergrundfarbe und ohne Randlinie eingefügt.
Dann zuerst folgende Formel in D3:
=WENNFEHLER(WECHSELN(ZEICHEN(AGGREGAT(15;6;CODE(Tabelle1[Land])/((Tabelle1[Datum WV]&gt=Eingabe!$C$1)*(Tabelle1[Datum WV]&lt=Eingabe!$F$1)+(Tabelle1[Datum WV]="immer"));ZEILE(D1)));"C";"CH");"")
Danach in E3:
=WENN([@Land]="";"";AGGREGAT(15;6;Tabelle1[PLZ]/(Tabelle1[Land]=[@Land])/((Tabelle1[Datum WV]&gt=Eingabe!$C$1)*(Tabelle1[Datum WV]&lt=Eingabe!$F$1)+(Tabelle1[Datum WV]="immer"));ZÄHLENWENN(D$3:D3;D3)))
mit dem benutzerdefinierten Zahlenformat Postleitzahl in der bedingten Formatierung für [@Land])="D"
und in A3:
=WENN([@Land]="";"";AGGREGAT(15;6;Tabelle1[Nr]/(Tabelle1[Land]=[@Land])/(Tabelle1[PLZ]=[@PLZ]);ZÄHLENWENNS(D$3:D3;D3;E$3:E3;E3)))
in alle anderen Spalten der Zeile 3 folgende Formel:
=WENNFEHLER(SVERWEIS($A3;Tabelle1;SPALTE();0);"") mit benutzerdefinierten Zahlenformat: 0;; bzw. TT.MM.JJJJ;;
Gruß Werner
.. , - ...
Anzeige
AW: als Formellösung ...
21.08.2022 14:50:22
Christian
Super! Funktioniert 1a. Ich hab beim Land noch die Aggregat Option 15 gegen 14 getauscht. So steht Deutschland auch oben (sind die meisten Kunden und werden am häufigsten gesucht). Bei dem Formelansatz wäre ich komplett gescheitert. Jetzt mal sehen wie die Geschwindigkeit bei 3000 Datensätzen ist. Vielleicht muss ich nen "Aktualisieren" Button einfügen damit nicht bei jeder Eingabe alles neu berechnet wird. Ganz herzlichen Dank nochmal und Grüße
Christian
AW: bitteschön und ...
21.08.2022 19:50:50
neopa
Hallo Christian,
... Deinen Erfahrung bzgl. Performance mit 3000 Datensätzen würden mich schon interessieren.
Sollte es da Probleme geben, bliebe als Alternative ja immer noch die PQ-Lösung. Deren aktuelles Ergebnis wird bei Änderung/Erweiterung sowieso nur nach Betätigung eines Aktualisierungsbuttons (oder durch eine Tastenkombination) erst entsprechend ausgegeben.
Gruß Werner
.. , - ...
Anzeige
AW: bitteschön und ...
22.08.2022 18:16:34
Christian
Hallo Werner. Ich habe das ganze jetzt mal auf 3000 Datensätze erweitert und die Abfrage noch mal auf 4 Datenblätter erweitert auf denen ich dann die gleiche Abfrage mache nur nach letzter Kontakt, Web fertig und Bera fertig. Läuft noch sehr rund. Das Gute ist dass ich ja bei Deiner Lösung das Zwischenergebnis nicht mehr brauche. Das schien mehr Zeit zu fressen als die 4 Ergebnisblätter jetzt.
Danke und Grüße noch mal
Christian
AW: freut mich, danke für Deine Rückmeldung owT
22.08.2022 19:37:03
neopa
Gruß Werner
.. , - ...
AW: freut mich, danke für Deine Rückmeldung owT
01.09.2022 09:41:15
Christian
Hallo Werner. Jetzt hab ich doch noch ein Problem. Sind jetzt auf 4000 Datensätzen und während der Eingabe ist das ganze nun doch ein wenig langsam. Wenn ich die automatische Berechnung der Formeln abschalte und über

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.Cursor = xlWait
Application.Calculate
Application.Cursor = xlDefault
End Sub
nur beim Wechseln des Arbeitsblatts aktualisiere geht's super. Beim Umschalten auf eins der Ergebnisblätter braucht Excel dann ca 4 Sekunden um die Ergebnisse zu aktualisieren.
Ich hab dann beim öffnen der Datei VBA
Application.Calculation = xlCalculationManual
und beim Schließen
Application.Calculation = xlCalculationAutomatic
eingeben. Aber leider wirkt sich das ja auch auf alle anderen zu der Zeit geöffneten Tabellen aus.
Gibt es eine Möglichkeit nur die eine Tabelle auf manuelle Berechnung zu setzen und alle anderen zu der Zeit offenen Tabellen im automatischen Berechnungsmodus zu lassen? Ich find da leider keinen Weg. Vielleicht kannst Du mir noch mal helfen?
Grüße und Danke schon mal im Voraus
Christian
Anzeige
AW: freut mich, danke für Deine Rückmeldung owT
01.09.2022 11:58:12
Pierre
Hallo Christian,
du könntest es folgendermaßen versuchen:
Im VBA-Editor musst du im Eigenschaftenfenster deines Blattes, auf dem die automatische Berechnung nicht gewünscht ist, diese ausschalten.
Es ist der Punkt "EnableCalculation". Diesen auf "False".
Dann musst du in deinem Code aber das Application.Calculation = xlCalculationManual wieder löschen (beides).
Dann in einem allg. Modul folgendes:

Sub berechnen()
ActiveSheet.EnableCalculation = True
End Sub
Dazu z. B. einfach einen Button aufs Blatt legen.
Schöner (finde ich persönlich), wäre ein ToggleButton, dazu dieser Code (der muss allerdings ins Modul des entsprechenden Blattes):

Private Sub ToggleButton1_Click()
If ToggleButton1 = True Then
ActiveSheet.EnableCalculation = True
Else: ActiveSheet.EnableCalculation = False
End If
End Sub
Übrigens: Application.Calculate betrifft Excel selbst. ActiveSheet.Calculate nur das aktive Blatt. Sagt ja schon der Name ;-)
Gruß Pierre
Anzeige
AW: freut mich, danke für Deine Rückmeldung owT
03.09.2022 16:18:31
Christian
Hallo Pierre
danke Dir für die Antwort. Jetzt hab ich das Problem dass das ganze läuft mit alle zu berechnenden Worksheets auf "Enable Calculation" auf false zu setzen und dann mit

Sub berechnen()
ActiveSheet.EnableCalculation = True
End Sub
alles zu berechnen. Wenn ich die Datei aber schließe und dann wieder öffne stehen alle Tabellenblätter wieder auf "Enable Calculation" true.
Was ist das denn für ein Sch... :-). Dachte beim ersten mal ich hätte nicht gespeichert aber auch nach 5 mal probiren immer wieder alle Datenblätter auf Enable Calculation" = true.
Was mach ich denn jetzt falsch? Dachte zum speichern bin ich nicht zu blöd :-).
Gruß
Christian
Anzeige
AW: und als PQ-Lösung ...
21.08.2022 14:31:23
neopa
Hallo Christian.
... ist die Erstellung einfacher und auch leichter nachvollziehbar, wenn man sich schon mal mit PQ etwas beschäftigt hat. Dazu sieh z.B. mal hier: https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/ oder auch hier: http://www.excel-ist-sexy.de/
In Deinem Beispiel müsste dazu die Daten aus Eingabe!C1:F1 zu einer eigenständigen "intelligente" Tabelle formatiert werden, damit die PQ-Auswertung deren Daten auch dynamisch flexibel auswerten kann. Die Auswertung kann mit einer Ausnahme interaktiv (durch Mausklicks) zusammengestellt werden. Die Ausnahme: eine if() then 1 else 0 Formelansatz mit einer einfachen and() und or() Verknüpfung im if()-Formelteil.
Die so erstellte PQ-Auswertung wertet dann bei Datenerweiterung oder anderen von bis Daten nach der Aktivierung eines Aktualisierungsbuttons diese dann aus.
Gruß Werner
.. , - ...
Anzeige

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige