Live-Forum - Die aktuellen Beiträge
Datum
Titel
27.04.2024 20:04:34
Excel in Formen z.b Rechteck, eine Tabelle einfügen?
27.04.2024 19:23:05
Passwortabfrage für verschiedene tabellenblätter
27.04.2024 16:48:06
Formel gibt Falsch aus und ich weiß nicht warum - VBA
27.04.2024 14:45:54
Verknüpfung Tabelleneinträge mit Datumsspalte
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1892to1896
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
1892to1896
1892to1896
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Matrixformel nach Spalte sortieren

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

211 Forumthreads zu ähnlichen Themen

Tabelle nach Anzahl sortieren
Pascal

Hallo,
wie kann ich eine Tablle nach der Anzahl der Wörter in einer Spalte sortieren?
Die Tabelle sieht etwa so aus:
Spalte1 | Spalte2 | Spalte 3
1 | der die | ...
2 | der | ...
3 | der die das | ...
und aussehen soll...
25.09.2008 11:50:39
Thread-ID: 1012284
6 Beiträge
Anzeige
Daten nach Kriterien in Spalten umsortieren
hans

Hallo zusammen.
habe mir bereits einige Stunden den Kopf zerbrochen, ohne jedoch weitergekommen zu sein. Mein Problem ist folgendes:
Ich habe eine Tabelle (kleiner Auszug siehe unten), die Daten enthält, die jeweils entweder mit den Buchstaben A, B oder C enden. Ich möchte nun diese Dat...
14.06.2009 02:13:12
Thread-ID: 1081347
6 Beiträge
Spalte nach Datum sortieren per Makro
Larissa

Hallo,
ich möchte mit einem Makro eine Tabelle nach Datum in Spalte G sortieren.
Wenn ich das Makro aufzeichne, fragt er mich nach: Alles was wie eine Zahl aussieht....
Dann funktioniert die Sortierung, aber nicht, wenn ich die Aufzeichnung abspiele.
Weiß jemand, woran das liege...
15.10.2009 11:09:27
Thread-ID: 1109788
5 Beiträge
Anzeige
Sortieren nach Spalten
chris

Hallo VBA experten,
ich brauche mal wieder einen Tipp.
Ich habe ab Zelle X1 bis Zelle IV1 einen Bereichs Namen vergeben.
Dieser heißt z.b Vornamen
Darunter in Zeile 2 bis z.b 500 stehen vornamen.
Jezt will ich aber nicht nach Zeile sondern nach der Spalte sortieren.
Also...
17.06.2010 08:40:19
Thread-ID: 1162370
2 Beiträge
Tabelle nach mehr als 3 Spalten sortieren
manasse

Hallo zusammen,
ich möchte eine große Tabelle nach mehr als drei Spalten sortieren. Standardmäßig geht das ja nur mit maximal drei Spalten. Kennt jemand dazu eine Lösung?
Für jegliche Hilfe bin ich dankbar.
Dank und Gruß
Manasse
04.11.2010 11:49:12
Thread-ID: 1185146
3 Beiträge
Datums-Spalte nach TAG sortieren
Bruno

Hallo Leute,
ich versuche, leider ohne Erfolg eine Liste zu sortieren :
Datumszeile nach Geburtstag !!!! nicht nach dem Geburtsjahr sondern vorrangig eben nach dem Geburts-TAG. 01. , 02. , 03. ,
Wer kann mir helfen ?
gruss
Bruno
19.11.2010 19:18:47
Thread-ID: 1187861
3 Beiträge
Pivot nach bestimmter Spalte sortieren
MikeM

Hallo zusammen,
ich möchte eine Kunden-Umsatz-Pivot-Datei nach einer bestimmten Spalte sortieren. Leider funktioniert das nicht. Ich habe eine kurze Datei mit angehängt inkl. kurzer Beschreibung.
https://www.herber.de/bbs/user/74121.xlsx
Über Hilfe wäre ich sehr dankbar.
Herzli...
30.03.2011 22:05:19
Thread-ID: 1207581
2 Beiträge
Zeilen sortieren nach einer Spalte ausserhalb des
Wenger

Hi all!
Ist denn mein Problem so schwierig? Immer ernte ich nur Kopfschütteln.
Erklärung:
Die Rennleitung hat eine Veranstaltersoftware. Diese exportiert mir ein Excel-Sheet mit den Spalten A, B und C. Diese Spalten dürfen keinesfalls verändert werden!!
In meinem Beispiel start...
07.07.2011 20:28:17
Thread-ID: 1221296
3 Beiträge
Anzeige

Links zu Excel-Dialogen

ToggleButton

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
5
14
5
4
3
1
2
2
1
2
17
3
2
3
4
7
2
1
8
5
6
4
7
2
7