Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1536to1540
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
Inhaltsverzeichnis

Formelmonster ZÄHLENWENN optimieren

Formelmonster ZÄHLENWENN optimieren
28.01.2017 11:07:00
erichm
Hallo,
ich habe bereits eine Formel mit ZÄHLENWENN die funktioniert.
Allerdings ist diese sehr umfangreich und ich suche nach einer Verkürzung bzw. Optimierung (hier allerdings keine VBA-Lösung möglich).
Es geht darum, dass in Zeile 2 eine Zahl steht und diese kann sich in den Zeilen nach unten wiederholen (maximal 14x), oder auch nicht. Diese Wiederholungen sind in jeder Spalte separat zu prüfen. In meinem Beispiel stehen die Zahlen in A bis J; die Lösungen in L bis U.
Meine Lösung sieht jetzt so aus:
identisch

 ABCDEFGHIJKLMNOPQRSTU
1Test1Test2Test3Test4Test5Test6Test7Test8Test9Test10 Test1Test2Test3Test4Test5Test6Test7Test8Test9Test10
2107107107107107107107107107107 125381214312
3251107107107107107107107251107           
4251251107107107107107107251251           
5251251107251107107107251251251           
6107107107107107107107107107107           
72222107107107222           
856565656107107107565656           
956565656107107107565656           
105656565656107107565656           
115656565656107107565656           
125656565656107107565656           
135656565656107107565656           
14565656565656107565656           
15565656565656107565656           

Formeln der Tabelle
ZelleFormel
L2=WENN(ZÄHLENWENN(A2:A15;A2)>13;14;WENN(ZÄHLENWENN(A2:A14;A2)>12;13;WENN(ZÄHLENWENN(A2:A13;A2)>11;12;WENN(ZÄHLENWENN(A2:A12;A2)>10;11;WENN(ZÄHLENWENN(A2:A11;A2)>9;10;WENN(ZÄHLENWENN(A2:A10;A2)>8;9;WENN(ZÄHLENWENN(A2:A9;A2)>7;8;WENN(ZÄHLENWENN(A2:A8;A2)>6;7;WENN(ZÄHLENWENN(A2:A7;A2)>5;6;WENN(ZÄHLENWENN(A2:A6;A2)>4;5;WENN(ZÄHLENWENN(A2:A5;A2)>3;4;WENN(ZÄHLENWENN(A2:A4;A2)>2;3;WENN(ZÄHLENWENN(A2:A3;A2)>1;2;1)))))))))))))
M2=WENN(ZÄHLENWENN(B2:B15;B2)>13;14;WENN(ZÄHLENWENN(B2:B14;B2)>12;13;WENN(ZÄHLENWENN(B2:B13;B2)>11;12;WENN(ZÄHLENWENN(B2:B12;B2)>10;11;WENN(ZÄHLENWENN(B2:B11;B2)>9;10;WENN(ZÄHLENWENN(B2:B10;B2)>8;9;WENN(ZÄHLENWENN(B2:B9;B2)>7;8;WENN(ZÄHLENWENN(B2:B8;B2)>6;7;WENN(ZÄHLENWENN(B2:B7;B2)>5;6;WENN(ZÄHLENWENN(B2:B6;B2)>4;5;WENN(ZÄHLENWENN(B2:B5;B2)>3;4;WENN(ZÄHLENWENN(B2:B4;B2)>2;3;WENN(ZÄHLENWENN(B2:B3;B2)>1;2;1)))))))))))))
N2=WENN(ZÄHLENWENN(C2:C15;C2)>13;14;WENN(ZÄHLENWENN(C2:C14;C2)>12;13;WENN(ZÄHLENWENN(C2:C13;C2)>11;12;WENN(ZÄHLENWENN(C2:C12;C2)>10;11;WENN(ZÄHLENWENN(C2:C11;C2)>9;10;WENN(ZÄHLENWENN(C2:C10;C2)>8;9;WENN(ZÄHLENWENN(C2:C9;C2)>7;8;WENN(ZÄHLENWENN(C2:C8;C2)>6;7;WENN(ZÄHLENWENN(C2:C7;C2)>5;6;WENN(ZÄHLENWENN(C2:C6;C2)>4;5;WENN(ZÄHLENWENN(C2:C5;C2)>3;4;WENN(ZÄHLENWENN(C2:C4;C2)>2;3;WENN(ZÄHLENWENN(C2:C3;C2)>1;2;1)))))))))))))
O2=WENN(ZÄHLENWENN(D2:D15;D2)>13;14;WENN(ZÄHLENWENN(D2:D14;D2)>12;13;WENN(ZÄHLENWENN(D2:D13;D2)>11;12;WENN(ZÄHLENWENN(D2:D12;D2)>10;11;WENN(ZÄHLENWENN(D2:D11;D2)>9;10;WENN(ZÄHLENWENN(D2:D10;D2)>8;9;WENN(ZÄHLENWENN(D2:D9;D2)>7;8;WENN(ZÄHLENWENN(D2:D8;D2)>6;7;WENN(ZÄHLENWENN(D2:D7;D2)>5;6;WENN(ZÄHLENWENN(D2:D6;D2)>4;5;WENN(ZÄHLENWENN(D2:D5;D2)>3;4;WENN(ZÄHLENWENN(D2:D4;D2)>2;3;WENN(ZÄHLENWENN(D2:D3;D2)>1;2;1)))))))))))))
P2=WENN(ZÄHLENWENN(E2:E15;E2)>13;14;WENN(ZÄHLENWENN(E2:E14;E2)>12;13;WENN(ZÄHLENWENN(E2:E13;E2)>11;12;WENN(ZÄHLENWENN(E2:E12;E2)>10;11;WENN(ZÄHLENWENN(E2:E11;E2)>9;10;WENN(ZÄHLENWENN(E2:E10;E2)>8;9;WENN(ZÄHLENWENN(E2:E9;E2)>7;8;WENN(ZÄHLENWENN(E2:E8;E2)>6;7;WENN(ZÄHLENWENN(E2:E7;E2)>5;6;WENN(ZÄHLENWENN(E2:E6;E2)>4;5;WENN(ZÄHLENWENN(E2:E5;E2)>3;4;WENN(ZÄHLENWENN(E2:E4;E2)>2;3;WENN(ZÄHLENWENN(E2:E3;E2)>1;2;1)))))))))))))
Q2=WENN(ZÄHLENWENN(F2:F15;F2)>13;14;WENN(ZÄHLENWENN(F2:F14;F2)>12;13;WENN(ZÄHLENWENN(F2:F13;F2)>11;12;WENN(ZÄHLENWENN(F2:F12;F2)>10;11;WENN(ZÄHLENWENN(F2:F11;F2)>9;10;WENN(ZÄHLENWENN(F2:F10;F2)>8;9;WENN(ZÄHLENWENN(F2:F9;F2)>7;8;WENN(ZÄHLENWENN(F2:F8;F2)>6;7;WENN(ZÄHLENWENN(F2:F7;F2)>5;6;WENN(ZÄHLENWENN(F2:F6;F2)>4;5;WENN(ZÄHLENWENN(F2:F5;F2)>3;4;WENN(ZÄHLENWENN(F2:F4;F2)>2;3;WENN(ZÄHLENWENN(F2:F3;F2)>1;2;1)))))))))))))
R2=WENN(ZÄHLENWENN(G2:G15;G2)>13;14;WENN(ZÄHLENWENN(G2:G14;G2)>12;13;WENN(ZÄHLENWENN(G2:G13;G2)>11;12;WENN(ZÄHLENWENN(G2:G12;G2)>10;11;WENN(ZÄHLENWENN(G2:G11;G2)>9;10;WENN(ZÄHLENWENN(G2:G10;G2)>8;9;WENN(ZÄHLENWENN(G2:G9;G2)>7;8;WENN(ZÄHLENWENN(G2:G8;G2)>6;7;WENN(ZÄHLENWENN(G2:G7;G2)>5;6;WENN(ZÄHLENWENN(G2:G6;G2)>4;5;WENN(ZÄHLENWENN(G2:G5;G2)>3;4;WENN(ZÄHLENWENN(G2:G4;G2)>2;3;WENN(ZÄHLENWENN(G2:G3;G2)>1;2;1)))))))))))))
S2=WENN(ZÄHLENWENN(H2:H15;H2)>13;14;WENN(ZÄHLENWENN(H2:H14;H2)>12;13;WENN(ZÄHLENWENN(H2:H13;H2)>11;12;WENN(ZÄHLENWENN(H2:H12;H2)>10;11;WENN(ZÄHLENWENN(H2:H11;H2)>9;10;WENN(ZÄHLENWENN(H2:H10;H2)>8;9;WENN(ZÄHLENWENN(H2:H9;H2)>7;8;WENN(ZÄHLENWENN(H2:H8;H2)>6;7;WENN(ZÄHLENWENN(H2:H7;H2)>5;6;WENN(ZÄHLENWENN(H2:H6;H2)>4;5;WENN(ZÄHLENWENN(H2:H5;H2)>3;4;WENN(ZÄHLENWENN(H2:H4;H2)>2;3;WENN(ZÄHLENWENN(H2:H3;H2)>1;2;1)))))))))))))
T2=WENN(ZÄHLENWENN(I2:I15;I2)>13;14;WENN(ZÄHLENWENN(I2:I14;I2)>12;13;WENN(ZÄHLENWENN(I2:I13;I2)>11;12;WENN(ZÄHLENWENN(I2:I12;I2)>10;11;WENN(ZÄHLENWENN(I2:I11;I2)>9;10;WENN(ZÄHLENWENN(I2:I10;I2)>8;9;WENN(ZÄHLENWENN(I2:I9;I2)>7;8;WENN(ZÄHLENWENN(I2:I8;I2)>6;7;WENN(ZÄHLENWENN(I2:I7;I2)>5;6;WENN(ZÄHLENWENN(I2:I6;I2)>4;5;WENN(ZÄHLENWENN(I2:I5;I2)>3;4;WENN(ZÄHLENWENN(I2:I4;I2)>2;3;WENN(ZÄHLENWENN(I2:I3;I2)>1;2;1)))))))))))))
U2=WENN(ZÄHLENWENN(J2:J15;J2)>13;14;WENN(ZÄHLENWENN(J2:J14;J2)>12;13;WENN(ZÄHLENWENN(J2:J13;J2)>11;12;WENN(ZÄHLENWENN(J2:J12;J2)>10;11;WENN(ZÄHLENWENN(J2:J11;J2)>9;10;WENN(ZÄHLENWENN(J2:J10;J2)>8;9;WENN(ZÄHLENWENN(J2:J9;J2)>7;8;WENN(ZÄHLENWENN(J2:J8;J2)>6;7;WENN(ZÄHLENWENN(J2:J7;J2)>5;6;WENN(ZÄHLENWENN(J2:J6;J2)>4;5;WENN(ZÄHLENWENN(J2:J5;J2)>3;4;WENN(ZÄHLENWENN(J2:J4;J2)>2;3;WENN(ZÄHLENWENN(J2:J3;J2)>1;2;1)))))))))))))


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Wie könnte ich die Formel kürzer machen; ggfls. auch mit einer anderen Funktion.
Besten Dank!
mfg

15
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Mit Median
28.01.2017 11:50:15
lupo1
L2: =MEDIAN(1;ZÄHLENWENN(A2:A15;A2);14)
AW: Mit Median
28.01.2017 12:04:31
erichm
Hallo lupo1,
danke, aber das klappt leider nicht ganz.
Es geht nur um die Wiederholungen, nicht um die gesamte Anzahl der gleichen Zahl.
Siehe mein Beispiel:
Spalte B mit Lösung Spalte M
oder
Spalte H mit Lösung Spalte S
mfg
Hab mich verguckt beim Bereich
28.01.2017 13:25:22
lupo1
{=MEDIAN(1;14;MIN(WENN(A2:A16A2;ZEILE(A1:A15)-1)))}
Die {} sind Dir bekannt!
DANKE - funktioniert perfekt!! o.w.T.
28.01.2017 18:06:32
erichm
...
AW: Formelmonster ZÄHLENWENN optimieren
28.01.2017 12:01:17
JoWE
Hallo Erich (?),
in Deiner Lösung wird der Wert der Zeile 2 in den Spalten L und N nicht mitgezählt, in den Folgespalten jedoch schon. Absicht?
Vielleicht hab' ich Deine Frage falsch verstanden, aber ginge es nicht einfach auch so?
in L2 =ZÄHLENWENN(A3:A15;A2) wenn die erste "107" nicht mitzählen soll
oder wenn die erste "107" doch mitzählen soll: =ZÄHLENWENN(A2:A15;A2).
Die Formel nach rechts kopieren.

so wenn das Vorkommen in Zeile zwei mitgezählt wird
Test1 Test2 Test3 Test4 Test5 Test6 Test7 Test8 Test9 Test10
2 3 5 4 8 12 14 4 2 3
so wenn das Vorkommen in Zeile zwei nicht mitgezählt wird
Test1 Test2 Test3 Test4 Test5 Test6 Test7 Test8 Test9 Test10
1 2 4 3 7 11 13 3 1 2

Gruß
Jochen
Anzeige
AW: Formelmonster ZÄHLENWENN optimieren
28.01.2017 12:07:50
erichm
Danke - die Klarstellung habe ich in meiner Antwort soeben formuliert; im ersten Beitrag war dies nicht 100%-ig aus der Beschreibung ersichtlich, sondern nur direkt in den Beispielen.
mfg
AW: Formelmonster ZÄHLENWENN optimieren
28.01.2017 12:25:00
hary
Moin
Mal ausser Konkurrenz da mit UDF(VBA). Daher Frage offen.
Soll eher so aussehen!?



Tabelle1
 ABCDE
1test1test2 test1test2
2107107 12
3251107 31
4251251 22
5251251 11
6107107 11
722 11
85656 88
95656 77
105656 66
115656 55
125656 44
135656 33
145656 22
155656 11

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D2:E15=zaehlen(A2:A$15;A2)  =zaehlen(RC[-3]:R15C[-3],RC[-3])

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.02 einschl 64 Bit

gruss hary
Anzeige
AW: Formelmonster ZÄHLENWENN optimieren
28.01.2017 18:07:51
erichm
DANKE - das muss ich mir erst näher ansehen: das Ergebnis kann ich als Alternative evtl. gut gebrauchen!!
mfg
mit VERGLEICH
28.01.2017 15:20:55
Josef
Hallo
In L2:
=VERGLEICH(1;INDEX(--(A3:A16A2);0);0)
und nach rechts kopieren
Gruss Sepp
Sehr schick ...
28.01.2017 17:14:33
lupo1
... der INDEX-Bereich als Wahrheitswerte.
Ohne Umwandlung derer spart man in diesem Falle noch ein Zeichen:
=VERGLEICH(1;INDEX(--(A3:A16A2););) =VERGLEICH(WAHR;INDEX(A3:A16A2;);)
AW: Sehr schick ...
28.01.2017 18:08:40
erichm
VIELEN DANK - ich glaube das ist jetzt die optimale Lösung. Funktioniert ebenfalls perfekt!
mfg
AW: villeicht noch etwas konsequenter Ver=en ...
28.01.2017 18:18:33
...
Hallo Sepp,
... wäre: in Deinem Vorschlag durch = zu ersetzen, die doppelte Negation aufzuheben und mit 0 VERGLEICH()en.
Und wer es ganz kurz mag, kann ja dann auch noch alle 0en in den Skat drücken:
=VERGLEICH(;INDEX(-(A3:A16=A2););)
Bei uns war heute schönste Wintersonnenwetter. Bei Euch wohl nicht, wenn Du schon 15:20 am PC gesessen hast?
Gruß Werner
.. , - ...
Anzeige
Sehr nett!
28.01.2017 18:52:21
lupo1
... auch wenn man jetzt schon etwas länger grübeln muss. Aber das sei keine Schande.
Winterwetter
29.01.2017 17:19:54
Josef
Hallo Werner
Da liegst du aber falsch, auch bei uns war endlich auch in tieferen Lagen herrliches Winterwetter.
Auf den Bergen hatten wir Das schon seit 2 Wochen.
Aber, allzu viel ist ungesund. ;-)
Gruss Sepp
AW: heute hast Du es wieder genutzt ...
29.01.2017 17:59:50
...
Hallo Sepp,
... ja, mit Aber, "allzu viel ist ungesund" hast Du auch wieder Recht. Aber nach der teils eisigen Kälte bei uns die letzten 10 Tage hab ich ich die Sonne gestern und Heute schon gern genossen.
Wünsche Einen guten Start Euch in die neue Woche.
Gruß Werner
.. , - ...
Anzeige

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige