Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 10:08:11
Summieren von Zellen mit bedingter Formatierung
26.04.2024 09:08:00
Export alle Tabellenblätter als csv - in bestimmten Ordner
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1328to1332
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
1328to1332
1328to1332
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

30 Bestimmte Zellen per Formel ausgeben

30 Bestimmte Zellen per Formel ausgeben
24.08.2013 18:28:33
Christian
Hallo an alle,
würde mich sehr über eine Formel freuen, bitte helft mir.
Habe eine Tabelle, 5189 Zeilen. Suche eine Formel, die die letzten 30 Texte aus Spalte A ausgibt (durch kopieren in 30 Zeilen), bei denen in derselben Zeile in Spalte M eine Zahl kleiner gleich 30 steht. Reihenfolge egal.
Was noch super wäre, wenn das möglich ist, dass es 30 unterschiedliche Texte sind, also das wenn bei den 30 letzten Texten einer doppelt vorkommt, dass der doppelte dann übergangen wird und der Text somit nur einmal erscheint, selbe bei 3x, 4x usw.
Tabelle ist so sortiert, das gleiche Texte in Spalte A untereinander stehen.
Kann mir da jemand helfen?
Gruß und vielen Dank
Christian

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 20:26:33
Raphael
Hallo Christian,
ohne Beispiel ist das immer etwas schwierig, ich habe dir hier mal was gezimmert oder geschustert was nichts doppeltes auflistet und die Liste von unten nach oben zieht.
Es gibt sicher noch einige die meine Formel vereinfachen können, aber so auf die schnelle wird's schon gehen (bis ich weiss ob das etwa dem entspricht was du suchst)
Tabelle1

 AB
1af
2ba
3bs
4bd
5bc
6cb
7c#NV
8d#NV
9s#NV
10a#NV
11f#NV

Formeln der Tabelle
ZelleFormel
B1=INDIREKT("A"&ANZAHL2(A:A))
B2{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 1)); (ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 0))}
B3{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 1)); (ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 0))}
B4{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 1)); (ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 0))}
B5{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 1)); (ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 0))}
B6{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 1)); (ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 0))}
B7{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 1)); (ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 0))}
B8{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 1)); (ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 0))}
B9{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 1)); (ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 0))}
B10{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 1)); (ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 0))}
B11{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 1)); (ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 0))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruess
Raphael

Anzeige
AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 20:53:39
Christian
Hallo Raphael, erstmal vielen Dank
sorry hänge nun eine Datei an.
In Spalte N dein Formelvorschlag, der leider nicht im Entferntesten dem was ich suche entspricht. Aber das nehme ich auf meine Kappe, das mit der Bsp-Datei hätte ich von selbst drauf kommen müssen.
Schau mal bitte in der Datei in den Bereich A5170:A5198 dort habe ich die letzten 5 Texte die ausgegeben werden sollen rot markiert (denke ja mal das nachher auf 30 auszuweiten sollte ja kein Problem darstellen).
Dies sind die letzten 5 Zeilen, in denen in Spalte M ein Wert kleiner gleich 30 steht. Eine Ausnahme bilden die Zellen A5176 und A5177. Hier ist nur eine Zelle rot markiert, obwohl beide Zeilen die Vorraussetzung kleiner gleich 30 erfüllen, da beide denselben Text in Spalte A haben.
Hoffe jetzt war es verständlicher
Christian
https://www.herber.de/bbs/user/87008.xlsx

Anzeige
AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 21:04:04
Raphael
Nabend,
habe dir die Formel (mein zweiter Ansatz) direkt in deine Datei eingebaut, sieht für mich ok aus.
https://www.herber.de/bbs/user/87009.xlsx
Gruess
Raphael

AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 21:09:27
Christian
Hallo Raphael,
sorry hab den zweiten Ansatz jetzt erst gesehen.
Ja dass das ok aussieht stimme ich dir zu, noch ein schönes Wochenende wünsche ich dir.
Nur noch eine kleine Frage am Rande, würde die Formel auch funktionieren, wenn Spalte A nicht so schön sortiert wäre? Ist nur ne kleine Interessensfrage, brauchst die Formel nicht anpassen
Christian

Anzeige
AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 21:12:34
Raphael
Ja, spielt keine rolle ob es sortiert ist oder nicht. Er geht von unten nach oben durch und lässt die doppelten einfach aus.
Schönes WE

AW: 30 Bestimmte Zellen per Formel ausgeben
24.08.2013 20:26:57
Raphael
Hallo Christian,
ohne Beispiel ist das immer etwas schwierig, ich habe dir hier mal was gezimmert oder geschustert was nichts doppeltes auflistet und die Liste von unten nach oben zieht.
Es gibt sicher noch einige die meine Formel vereinfachen können, aber so auf die schnelle wird's schon gehen (bis ich weiss ob das etwa dem entspricht was du suchst)
Tabelle1

 AB
1af
2ba
3bs
4bd
5bc
6cb
7c#NV
8d#NV
9s#NV
10a#NV
11f#NV

Formeln der Tabelle
ZelleFormel
B1=INDIREKT("A"&ANZAHL2(A:A))
B2{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 1)); (ZÄHLENWENN(B$1:B1;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(1:99); 0))}
B3{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 1)); (ZÄHLENWENN(B$1:B2;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(2:100); 0))}
B4{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 1)); (ZÄHLENWENN(B$1:B3;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(3:101); 0))}
B5{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 1)); (ZÄHLENWENN(B$1:B4;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(4:102); 0))}
B6{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 1)); (ZÄHLENWENN(B$1:B5;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(5:103); 0))}
B7{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 1)); (ZÄHLENWENN(B$1:B6;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(6:104); 0))}
B8{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 1)); (ZÄHLENWENN(B$1:B7;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(7:105); 0))}
B9{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 1)); (ZÄHLENWENN(B$1:B8;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(8:106); 0))}
B10{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 1)); (ZÄHLENWENN(B$1:B9;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(9:107); 0))}
B11{=INDEX(A:A;VERGLEICH(WENN(KGRÖSSTE((ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 1)=0;1;KGRÖSSTE((ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 1)); (ZÄHLENWENN(B$1:B10;A$1:A$99)=0)*(A$1:A$99<>"")*ZEILE(10:108); 0))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruess
Raphael

Anzeige
Die Version passt wahrscheinlich eher
24.08.2013 20:52:39
Raphael
Hallo Christian,
ich habe deinen Beschrieb nochmals gelesen, ich denke diese Version trifft es besser
Tabelle1

 ABCD
1a25 f
2b26 s
3b30 d
4b50 c
5b50 b
6c10 a
7q500 a
8d30 a
9s29 a
10a250 a
11f250 a

Formeln der Tabelle
ZelleFormel
D1{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*($B$1:$B$15>=30)*ZEILE($1:$15); 1))}
D2{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D1;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D3{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D2;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D4{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D3;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D5{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D4;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D6{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D5;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D7{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D6;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D8{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D7;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D9{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D8;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D10{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D9;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
D11{=INDEX(A:A;KGRÖSSTE(($A$1:$A$15<>"")*(ZÄHLENWENN($D$1:D10;$A$1:$A$15)=0)*($B$1:$B$15<=30)*ZEILE($1:$15); 1))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruess
Raphael
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige