Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1700to1704
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

Zählen, wenn Gruppe offen.

Zählen, wenn Gruppe offen.
14.07.2019 11:18:15
Vanadium
Hallo,
ich habe eine Frage zu Gruppierungen und daher eine vereinfachte Testdatei hochgeladen:
https://www.herber.de/bbs/user/130893.xlsx
In der Datei sind verschiedene Spalten mit Kürzel (z.B. LiMa, BuJo, etc...) dargestellt. Diese Spalten sind gruppiert, können also ein- bzw. ausgeblendet werden.
Daneben befindet sich eine Tabelle, welche für jedes Kürzel die Häufigkeit pro Zeile zählt.
Ich möchte, dass die Häufigkeit der einzelnen Kürzel nur von Spalten gezählt werden, die auch eingeblendet sind. Wird die Spalte ausgeblendet (über das "-" der Gruppierung), sollen die eingetragenen Werte unberücksichtigt bleiben.
Vielen lieben Dank für die Unterstützung.
VG

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 11:51:19
neopa
Hallo,
... die horizontal ausgeblendete Spalten erkennt, jedenfalls konnte ich momentan noch immer keine bis einschließlich XL2016 finden.
Da würde nur eine VBA-Lösung helfen oder Du änderst Deine Datenstruktur ("drehst" diese um 90°), so dass Du Zeilen gruppieren kannst. Diese sind dann standardmäßig mit Hilfe von TEILERGEBNIS() oder AGGREGAT() auf jeden Fall auswertbar.
Evtl. gibt es alternativ zu VBA ja eine Lösung über PowerQuery. Wüsste jetzt aber auch nicht wie. Ich stell den thread deswegen wieder offen.
Gruß Werner
.. , - ...
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 12:57:26
Rainer
Hallo Vanadium,
Hier eine Lösung mit VBA: https://www.herber.de/bbs/user/130895.xlsb
Es gibt 2 "User defined Function", welche prüfen ob die Spalte oder Zeile sichtbar ist.
Diese Funktionen sind volatil, aktualisieren also bei jeder Änderung am Worksheet. Bei zu häufigem Einsatz reduziert das die Performance.
GRuß, Rainer
Anzeige
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 13:37:55
Vanadium
Hallo Werner, Hallo Rainer,
viele Dank für euere Bemühungen.
Die Lösung von Rainer funktioniert erstklassig!
Vielen lieben Dank!
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 13:48:11
Vanadium
Hallo Rainer,
wie du bereits sagtest, sinkt die Performance extrem.
Besteht hier vll. die Möglichkeit, die Berechnung auf Knopfdruck starten zu lassen?
VG
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 14:17:49
Daniel
HI
ja, mit nem kleinen Trick.
ändere die Formel der Funktion so ab, das sie nicht mehr volatil ist.
Um die Neuberechnung einfach auslösen, nehmen wir einen zweiten Paramter (auch wieder eine Zelle) in die Paramterliste mit auf:
Function SpalteSichtbar(cell As Range, Zelle_Zum_Berechnung_Starten)
SpalteSichtbar = Not cell.EntireColumn.Hidden
End Function
ändere die Formeln, welche dir anzeigen ob die Spalte sichtbar ist so ab:
=SpalteSichtbar(B1;$A$1)

Das $A$1 bleibt für alle Zellen gleich.
Um jetzt die Neuberechnung auszulösen, schreibst du einen anderen Wert in die Zelle A1
Gruß Daniel
Anzeige
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 16:19:02
Vanadium
Hallo Daniel,
es klappt einwandfrei!
Vielen herzlichen Dank!
VG
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 20:58:27
Rainer
Hallo Vanadium,
unabhängig von Daniels Modification hätte ich es so gemacht:

Sub UpdateMyFunctions()
Dim myRange As Range
Dim rng As Range
'Bereich zum Aktualisieren wählen
Set myRange = ActiveSheet.Range("B1:B10")
For Each rng In myRange
rng.Formula = rng.Formula
Next
End Sub
Function SpalteSichtbar(cell As Range)
SpalteSichtbar = Not cell.EntireColumn.Hidden
End Function
Function ZeileSichtbar(cell As Range)
ZeileSichtbar = Not cell.EntireColumn.Hidden
End Function
oder noch viel einfacher über das Ein/Ausschalten der Automatischen Formel-Berechnung.
Gruß, Rainer
Anzeige
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 21:04:23
Daniel
Hi Rainer
ein eigenständiges Makro hat den Nachteil, dass der Zellbereich, in welchem die neu zu berechnenden Formeln stehen, im Makro fest hinterlegt ist und immer mit aktualisiert werden muss, wenn sich Zellbereiche verschieben oder Spalten mit dieser Funktion hinzukommen.
btw "ZeileSichtbar" braucht man doch nicht dass kann man mit Teilergebnis oder Aggregat ja darstellen.
Gruß Daniel
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 21:13:54
Rainer
Hallo Daniel,
ich habe nicht gesagt das deine Lösung schlechter (oder besser) ist und habe auch keine Lust mich zu rechtfertigen.
Wenn die Beispieltabelle transponiert sein sollte, dann finde ich "ZeileSichtbar" ganz brauchbar.
Aber keine Lust auf Streit, Pommes rot oder weiß oder mit Couscous-Joghurt-Nutella-Oregano-Soße, es muss ja nicht mir schmecken, sondern auch dem Fragesteller.
Schönen Abend noch, Rainer
Anzeige
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 23:44:05
Daniel
naja, ich habe auch nicht angenommen, dass du gesagt hättest, dass meine Lösung schlechter wäre.
daher musst du dich auch nicht für irgendetwas rechtfertigen, dafür besteht keinerlei anlass.
Ich habe nur den Grund genannt, warum ich mich dafür entschieden habe das neuberechnen der Formeln nicht über ein Makro, sondern über die Formeln selbst auszulösen.
Warum spricht du hier von "Streit"?
sorry, aber kann es sein, dass du etwa überreagierst?
Gruß Daniel
AW: mW gibt es keine standardmäßige Funktion ...
15.07.2019 18:03:12
Rainer
Hallo Daniel,
dH = 0
Gruß, Rainer
AW: mW gibt es keine standardmäßige Funktion ...
14.07.2019 13:48:36
Hajo_Zi
offen bedeutet es soll noch eine Antwort kommen.
Warum ist dein Beitrag Offen.
Du willst doch was machen. Soll jemand vorbei kommen?

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
Anzeige
Aber es gibt die UDF NoErrRange im Archiv, ...
19.07.2019 02:56:03
Luc:-?
…die so etwas berücksichtigen kann, (Werner &) Vanadium;
hier als singulare Matrix- bzw Normal-Fml (mit TextVerkettung):
J5[:N8]: {=ANZAHL(--LINKS(1&DataSet(NoErrRange($B5:$H5;Neu;$B5:$H5=J$4);#NV;2;;1)))}
Oder J5[:N8]:=CountOn(MxJoin(NoErrRange($B5:$H5;Neu;$B5:$H5=J$4));J$4)
Die benannte Konstante des Blattes, Neu (=WAHR oder =FALSCH → muss zuvor angelegt wdn!), sorgt hier für Ein-/Ausschaltung der Vola­tilität von NoErrRange. Sie wird umgeschaltet, indem man auf eine Zelle des ErgebnisBereichs klickt, was durch folgd Ereignis­Pro­ze­dur des Blattes erreicht wird:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const adStrBer$ = "J4:N8", naStrKonst$ = "Neu"
If Not Intersect(Target, Me.Range(adStrBer)) Is Nothing Then
With Application
.EnableEvents = False
Me.Names(naStrKonst).RefersTo = Not Me.Evaluate(Me.Names(naStrKonst).RefersTo)
.EnableEvents = True
End With
End If
End Sub
Falls das doch noch zu zeitaufwendig ist oder auf Grund größerer Datenmenge nicht zuverlässig fktioniert, könnte auch die bisherige Variante (mit Daniels Ergänzung!) teil­auto­mati­siert wdn, indem nur noch auf die die WahrheitswerteBerechnung auslösende Zelle geklickt wdn muss, um deren Änderung auszulösen. Die Ereignisprozedur sähe dann für A2 so aus:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const adRelBer$ = "B2,D2,F2,H2", adStrZelle$ = "A2"
Dim ix As Long, strIx As Long, zelle As Range
If Target.Address(0, 0) = adStrZelle Then
With Application
.EnableEvents = False
For Each zelle In Me.Range(adRelBer)
ix = ix + 1
strIx = strIx & ix * Abs(zelle.EntireColumn.Hidden)
Next zelle
If Target  strIx Then Target = strIx 'Else Target = 0
Target.Offset(-1, 0).Select
.EnableEvents = True
End With
End If
End Sub
Falls A2 bei keinerlei Änderung, aber trotzdem erfolgten Klick stets auf 0 gesetzt wdn soll, muss der ' vor dem kursiven PgmTeil entfernt wdn.
UDF-Links:
CountOn (Vs1.1) https://www.herber.de/forum/archiv/732to736/732035_Zaehlennwenn_mit_Zahlenkombinationen.html#734566
DataSet (Vs1.3) https://www.herber.de/forum/archiv/1504to1508/t1507939.htm#1508954
MxJoin (Vs1.1) https://www.herber.de/forum/archiv/1364to1368/1366681_Verketten_anhand_von_Kriterien.html#1366803
NoErrRange (Vs1.3) https://www.herber.de/forum/archiv/1508to1512/t1508209.htm#1508215
Morhn, Luc :-?
„Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen.“ H.Lesch, 2018, Sonneberg
Deshalb Intelligenz steigern mit …

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige