Hallo,
ich habe einmal wieder ein kleines Problem und hoffe, dass mir hier jemand helfen kann.
Ich habe mehrere Tabellen in denen in jeweils einer Spalte nur "1" oder nichts steht. Die Einzelnen Tabellen haben über 7000 Spalten.
Nun würde ich gern die Anzahl der Einträge mit "1" in der Spalte wissen. Soweit erst einmal nicht weiter schwer.
Ich möchte allerdings nur die Einträge mit "1" zählen die in Blöcken von >2 stehen. Einzelne "1", ohne das davor oder danach eine "1" steht möchte ich also nicht mitzählen. Das selbe gilt für Blöcke aus nur 2 aufeinander folgenden "1".
Ich hoffe das war verständlich.
Hat jemand eine Idee? Ich hab da leider eine völlige Blockade.
Hi
Für mich hätte ich das mit VBA gelöst. (Bei Interesse reiche ich dir das nach.)
Mit Formel sollte es so klappen.
____|__A_|__B_|__C_|__D_|
1| 1| | | 3|
2| 1| | | |
3| | | | |
4| 1| | | |
5| 1| | | |
6| 1| | | |
7| | | | |
8| 1| | | |
9| | | | |
10| 1| | | |
11| 1| | | |
12| | | | |
13| 1| | | |
14| 1| | | |
15| 1| | | |
16| 1| | | |
17| | | | |
18| | | | |
19| | | | |
20| 1| | | |
21| 1| | | |
22| | | | |
23| 1| | | |
24| 1| | | |
25| 1| | | |
26| | | | |
27| | | | |
Achtung! Matrixformeln mit shift+strg+Enter abschließen und die letzte Zelle im Bezug muss leer sein. Also A27.
Code:
D1 =SUMMENPRODUKT(((KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A1:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27););)))-WENNFEHLER(KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A2:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27)+1;);)));0))>3)*1)
Gruß Elex
Hallo,
danke für die Hilfe. So hatte ich das aber nicht gemeint. Gezählt werden sollen die Einsen die in Blöcken größer 2 stehen. In deinem Beispiel sollte das Ergebnis also 10 sein.
Grüße
Die Zahlen beginnen ab A3 (auf keinen Fall darüber; dort leere Zellen). Dann:
B3: =(PRODUKT(--A1:A3)+PRODUKT(--A2:A4)+PRODUKT(--A3:A5)>0)+B2
runterkopieren (Hilfsspalte)
nicht sonderlich schön aber funktioniert... mit Hilfsspalte...
| Arbeitsblatt mit dem Namen 'Tabelle1' |
| E | F | G | H |
| 1 | | | Hilfsspalte | |
| 2 | 1 | 1 | | 10 |
| 3 | 2 | 1 | | |
| Zelle | Formel |
| G2 | =WENN(UND(F2=1;F3=1;F4=1);F2;"") |
| H2 | =SUMME(G:G) |
| G3 | =WENN(ODER(UND(F3=1;F2=1;F1=1);UND(F2=1;F3=1;F4=1);UND(F3=1;F4=1;F5=1));F3;"") |
| Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016 |
| Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg |
Hi
Dann so oder mit Hilfsspalten.
Code:
=SUMMENPRODUKT(WENNFEHLER(EXP(LN((KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A1:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27););)))-WENNFEHLER(KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A2:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27)+1;);)));0))-3));-2)+2)
strg+shift+Enter und A27 = ""
Gruß Elex
Hallo
anbei eine Beispieldatei mit 2 Makros. Das 1. Makro zaehlt in der markierten Spalte, die kann beliebig sein. Beim 2. Makro ist eine bestimmte Spalte in der Const Anweisung fest vorgeben. Gezaehlt wird ab Zeile 2, das Ergebnis wird per MsgBox angezeigt
Würde mich freuen wenn diese Idee mit zur Lösung beitraegt.
mfg Gast 123
Hallo, die Lösung von Frogger hat super funktioniert. Die von LCohen habe ich leider nicht zum laufen bekommen. Die zweite Matrix von Elex hat zwar grundsätzlich funktioniert (in dem Beispiel A1:A27) und wäre vielleicht auch die elegantere Lösung gewesen, aber in meinem Anwendungsfall habe ich sie irgendwie nicht funktionstüchtig bekommen.
Danke für die Mühe. Ich finde es ja immer wieder klasse wie schnell und kompetent einem hier geholfen wird.
Grüße
Die Lösung von Elex ist ein Sahnehäubchen. Endlich verwendet mal jmd.
WENNFEHLER(EXP(LN(, also das
Array-MAX(;...). Der Bereich ist von A1:A27. Du musst nun selbst die Zelle A27 weiter nach unten verschieben, wenn Du mehr Daten hast. Eine automatisierte Datenzählung würde die Formel unlesbar machen.
Ich habe sie einmal so umbrochen, dass man gleiche (bzw. hier: ähnliche!) Bestandteile sieht:
=SUMMENPRODUKT(WENNFEHLER(EXP(LN((
KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A1:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27););)))-WENNFEHLER(
KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(INDEX(A2:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27)+1;);)));0))-3));-2)+2)
oder auch
{
=SUMME(WENNFEHLER(EXP(LN((
KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(A1:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27))))-WENNFEHLER(
KGRÖSSTE(WENN(A1:A27="";ZEILE(A1:A27));ZEILE(A2:INDEX(A:A;ANZAHLLEEREZELLEN(A1:A27)+1)));0))-3));-2)+2)}
Vielleicht erklärt elex ja, wie er vorgegangen ist. Ich gebe zu, dass ich sie beim schnellen Gucken noch nicht kapiere. Das ist aber fast immer so bei komplexen Formeln anderer - und auch bei eigenen komplexen Formeln, die schon etwas älter sind.
Zu meiner kleinen PRODUKT-Hilfsspaltenformel kann ich nichts hinzufügen. Alles gesagt.
Hi
Zitat:Endlich verwendet mal jmd. WENNFEHLER(EXP(LN(
Will mir einfach nicht einfallen wo ich das her hatte. :91: Naja bei den vielen Namen. :21:
Zitat:Vielleicht erklärt elex ja, wie er vorgegangen ist.
Vieleicht brauch ich das nicht mehr und es wird nachvollziehbar wenn Interpretation des Wunschergebnis passt.
Soll = 10
____|__A_|__B__|__C__|
1| | | |
2| | | |
3| | 0| 0|
4| 1| 1| 1|
5| 1| 2| 2|
6| 1| 3| 3|
7| | 3| 3|
8| 1| 3| 4|
9| | 3| 4|
10| 1| 3| 5|
11| 1| 3| 6|
12| | 3| 6|
13| 1| 4| 7|
14| 1| 5| 8|
15| 1| 6| 9|
16| 1| 7| 10|
17| | 7| 10|
18| | 7| 10|
19| | 7| 10|
20| 1| 7| 11|
21| 1| 7| 12|
22| | 7| 12|
23| 1| 8| 13|
24| 1| 9| 14|
25| 1| 10| 15|
26| | 10| 15|
27| | | |
B3 =WENN((SUMME(A1:A3)=3)+(SUMME(A3:A5)=3)+(SUMME(A2:A4)=3);B2+1;B2)
C3 =(PRODUKT(--A1:A3)+PRODUKT(--A2:A4)+PRODUKT(--A3:A5)>0)+C2
Gruß Elex