Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1676to1680
1676to1680
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

komplexe(re) Formel gesucht

Forumthread: komplexe(re) Formel gesucht

komplexe(re) Formel gesucht
17.02.2019 21:11:29
Günther
Hallo,
es geht darum in einem Bereich mit Zahlen (z.B. Spalte A) abzuzählen, wie oft eine Reihe von bestimmten Zahlen (z.B. angeführt in Spalte C) vorkommt.
Die klassische Lösung dazu lautet:
=ZÄHLENWENN(A:A;C1)+ZÄHLENWENN(A:A;C2)+ZÄHLENWENN(A:A;C3)+ ...
Suchen würde ich eine Formel, die nur auf die beiden Bereich A:A und C1:Cx zugreift. Es ist wichtig, dass dabei keine zusätzlichen Hilfsspalten o.ä. benötigt werden.
Danke in voraus für alle nützlichen Tipps!
Grüße
Günther
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: komplexe(re) Formel gesucht
17.02.2019 21:22:21
Sepp
Hallo Günther,
so?
Tabelle2

 ABCDE
19 3 14
27 4  
34 5  
48    
57    
68    
76    
89    
96    
108    
113    
125    
132    
146    
153    
165    
171    
189    
195    
202    
214    
221    
239    
243    
255    
265    
275    
286    
295    
304    
313    

Formeln der Tabelle
ZelleFormel
E1{=SUMME(ZÄHLENWENN(A1:A31;C1:C3))}
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.8
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: komplexe(re) Formel gesucht
17.02.2019 21:37:33
Günther
Ja Danke, genau das!
Grüße
Günther
AW: komplexe(re) Formel gesucht
17.02.2019 22:29:25
Günther
Hallo Sepp,
leider doch noch nicht gelöst - manchmal "nervt" Excel:
Meine Beschreibung der Aufgabenstellung war zu stark vereinfacht:
Damit die Formel ausfüllbar wird, werden bei mir die beiden Bereiche mit Bereich.verschieben oder auch Indirekt berechnet.
Dies klappt im ersten Zählenwenn-Argument ohne Probleme (auch wenn die Formelauswertung #Wert! anzeigt).
Probleme macht aber das zweite Zählenwenn-Argument!
Dies scheint mit Bereich.verschieben gar nicht und mit Indirekt nur zu funktionieren, solange keine Funktion zum Einsatz kommt - siehe folgende Beispiele:
Folgende Varianten gehen:
{=SUMME(ZÄHLENWENN(INDIREKT("$A$1:$A$31");INDIREKT("$C$1:$C$3")))}
{=SUMME(ZÄHLENWENN(INDIREKT("Z1S1:Z31S1";0);INDIREKT("Z1S3:Z3S3";0)))}
{=SUMME(ZÄHLENWENN(INDIREKT("Z1S"&1&":Z31S"&1;0);INDIREKT("Z1S"&3&":Z3S"&3;0)))}
{=SUMME(ZÄHLENWENN(INDIREKT("Z1S"&ZEILE(A1)&":Z31S"&1;0);INDIREKT("Z1S"&3&":Z3S"&F14;0)))}
Das was ich gerne hätte, geht aber nicht mehr:
{=SUMME(ZÄHLENWENN(INDIREKT("Z1S"&1&":Z31S"&1;0);INDIREKT("Z1S"&ZEILE(A3)&":Z3S"&3;0)))}
Warum? Bzw. viel wesentlicher:
Gibt es eine Möglichkeit, dass doch hinzubekommen?
Grüße
Günther
Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 05:51:22
Sepp
Hallo Günther,
du solltet eine Beispieldatei mit Beschreibung hochladen.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 10:47:00
Günther
Hallo Sepp,
gerne - anbei eine Beispieldatei.
https://www.herber.de/bbs/user/127713.xlsb
Es scheint so zu sein, dass es dann nicht funktioniert, wenn man die Funktionen Zeile oder Spalte im zweiten Zählenwenn-Argument verwendet. Alternative Lösungen mit anderen Funktionen gehen.
Falls Du dafür eine logische Erklärung hast oder einen Weg kennst, es doch mit diesen beiden Funktionen zum Laufen zu bekommen, freue ich mich über eine Info.
Ansonsten ist es die Anfrage erledigt.
Grüße und Danke
Günther
Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 18:06:31
Sepp
Hallo Günther,
könntest du mir den Sinn hinter INDIREKT() bzw. BEREICH.VERSCHIEBEN() erklärten oder was du damit genau erreichen willst.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 19:24:16
Günther
Hallo Sepp,
der Sinn liegt darin, dass der Bezug beim flächendeckenden Ausfüllen der Formeln anders "mitwandern" soll, als es Excel mit relativen/absoluten/gemischten Bezügen direkt unterstützt.
Konkret lautet die Anforderung:
1. Argument:
Formel 1x nach rechts ausfüllen: Bezug bleibt ident
Formel 1x nach unten ausfüllen: Bezug wandert eine Spalte nach rechts
2. Argument:
Formel 1x nach rechts ausfüllen: Bezug wandert zwei Spalten nach rechts
Formel 1x nach unten ausfüllen: Bezug bleibt ident
Falls Du andere Möglichkeiten kennst, dies umzusetzen, lerne ich gerne dazu ...
Nachfolgend meine funktionierenden Varianten.
Ich kenne als Alternative nur, auf das Ausfüllen der Formel zu verzichten und stattdessen die in diesem Fall ca. 3.500 Formeln per Makro zu erstellen.
Grüße
Günther
Eine funktionierende Matrix-Lösung (ohne Hilfszeile) lautet:
=SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$2;2;VERGLEICH(WECHSELN(B$151;".";"");'1-12 Grid'!$2:$2;0)-2;11;1)) )
Realisiert habe ich derzeit mit einer kleinen Hilfszeile:
=SUMME(ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$2;2;B$149-2;11;1)))
Die funktionierende Nicht-Matrix-Variante:
=ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$4;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$5;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$6;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$7;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$8;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$9;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$10;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$11;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$12;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$13;0;(SPALTE(B152)-SPALTE($B152))*2)) +ZÄHLENWENN(BEREICH.VERSCHIEBEN($A$11;1;ZEILE(B152)-ZEILE(B$152);AnzFragen;1); BEREICH.VERSCHIEBEN('1-12 Grid'!$B$14;0;(SPALTE(B152)-SPALTE($B152))*2))
Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 19:43:29
Sepp
Hallo Günther,
dann ist aber deine Beispieldatei nutzlos!
Ist es so schwer eine aussagekräftige Datei evtl. mit Beschreibung wie das gewünschte Ergebnis aussehen soll zu erstellen?
 ABCDEF
1Gruß Sepp
2
3

Anzeige
AW: komplexe(re) Formel gesucht
18.02.2019 23:36:22
Günther
Hallo Sepp,
die Beispieldatei ist nicht nutzlos, sondern zeigt die Essenz dessen, worum es geht, ohne zusätzlichen Ballast, der für meine Frage irrelevant ist. Wenn das für Dich nicht klar ersichtlich ist, bist Du nicht der richtige Ansprechpartner.
Grüße
Günther
AW: komplexe(re) Formel gesucht
19.02.2019 05:55:23
Sepp
Hallo Günther,
OK, ich will vor einer evtl. Lösung nur gerne Wissen um was es konkret geht, vielleicht hast du dich mit deinen Konstrukten verrannt und es gibt einen leichteren Weg.
INDIREKT() und BEREICH.VERSCHIEBEN() sind Performance-Killer.
 ABCDEF
1Gruß Sepp
2
3

Anzeige
;

Beliebteste Forumthreads (12 Monate)

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