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

Formel über gefilterte Zeilen

Forumthread: Formel über gefilterte Zeilen

Formel über gefilterte Zeilen
23.11.2022 15:19:11
Angelika
Hallo VBA Profis,
ich hab noch ne Frage, ich habe eine größere Tabelle die ich mit einem Autofilter teilweise gesteuert wird,
ich möchte jetzt über den VK, aber nur für die gefilterten Daten eine Formel drüber laufen lassen, die
nicht sichtbaren Zellen sollen davon unberührt bleiben. In einer normalen Tabelle funktioniert es,
aber in einer Intelligenten-Tabelle leider nicht, berechnet somit auch alle ausgeblendeten Zellen.
Wie kann ich dies mit VBA unterbinden---- ( ICH MÖCHTE NUR DIE ZELLEN BERECHNEN DIE GEFILTERT SIND)
Ich hab dieser Frage mal eine kleine Testdatei beigefügt.
Vielleicht kann mir jemand helfen, besten Dank im Voraus
Grüße
Angelika
https://www.herber.de/bbs/user/156360.xlsm
Kunde Lieferant Rabattgruppe Nettopreis Aufschlag VK Spalte1 Spalte2
10500 Gira A500 5 1,05 5,25 -94,75
10500 Gira A500 5 1,05 5,25 -94,75
10500 Gira A500 5 1,05 5,25 -94,75
10500 Jung A600 6 1,1 6,6 -93,4
10500 Jung A600 6 1,1 6,6 -93,4
10500 Jung A600 6 1,1 6,6 -93,4
10500 Busch A700 7 1,7 11,9 -88,1
10500 Busch A700 7 1,7 11,9 -88,1
10500 Busch A700 7 1,7 11,9 -88,1
10500 Berker A900 8 1,2 9,6 -90,4
10500 Berker A900 8 1,2 9,6 -90,4
10500 Berker A900 8 1,2 9,6 -90,4
Anzeige

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:27:18
Daniel
Hi
es wäre natürlich hilfreich, wenn du genauer beschreiben könntest, was du machen willst.
in VBA kann man beispielsweise mit Range(...).SpecialCells(xlcelltypevisible) die angegebene Range auf die sichtbaren Zellen einschränken.
Für Formeln gibt es die übergeordneten Funktionen Teilergebnis und Aggregat, bei denen man angeben kann, dass nur die sichtbaren Zellen für die Berechnung verwendet werden sollen.
Gruß Daniel
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:40:16
Angelika
Hallo,
entschuldigung, wenn ich mich nicht richtig ausgedrückt habe. Ja ich ich möchte die Range auf den sichtbaren Zeilen in der intelligenten Tabelle beschränken.
Die Berechnung sollte dann auch nur auf den ausgewählten Bereich ausgeführt werden, in dem Beispiel ist es ja nur eine Mulitiplikation von eine Zelle die dann
ausgegeben sollte.
Grüße
Angelika
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:43:52
onur
Diese Beschreibung ist auch nicht genauer. Erst schreibst du "Range", dann sprichst du von EINER Zelle.
Wie wäre es mal mit einem Beispiel?
AW: Formel über gefilterte Zeilen
23.11.2022 15:30:57
onur
" eine Formel drüber laufen lassen" - Was für Eine?
AW: Formel über gefilterte Zeilen
23.11.2022 15:45:43
Angelika
ich hab eine Testdatei hochgeladen.... es soll nur ein Beispiel sein... in meiner richtigen Tabelle sind einfache Aufschläge und Abschläge einer Zeile / Zelle zu berechnen.
Mein Problem ist, obwohl ich Zeilen gefiltert habe, werden in der intelligenten Tabelle alle Zeilen berechnet und nicht nur die Sichtbaren.
Grüße
Angelika
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:49:03
onur
Eine "Testdatei", die nicht viel mit dem Problem gemeinsam hat, bringt auch nix.
Wo sind denn da irgendwelche Berechnungen?
AW: Formel über gefilterte Zeilen
23.11.2022 15:56:58
Angelika
Die Berechnung ist in Spalte H..... wenn man einen Filter setzt auf den Nettopreis (Filter = 5 und 8), und ich löse die 

Sub Formel aus, dann berechnet es mir alle
Zeilen und nicht nur die gefilterten der Spalte H.


Sub Test_Filter_Formel()
With ThisWorkbook.Worksheets("Tabelle1")
.Range("A1").AutoFilter
.Range("D1").AutoFilter 4, Array("5", "6"), xlFilterValues
End With
End Sub




Sub Formel()
Dim iRow As Integer
iRow = ThisWorkbook.Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
With ThisWorkbook.Worksheets("Tabelle1")
.Range("H2").Formula = "=RC[-2]-100"
.Range("H2:H" & iRow).FillDown
End With
End Sub
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:53:50
Daniel
Hi
in einer intelligenten Tabelle ist es sinnvoll, wenn alle Zellen einer Spalte mit Formel die gleiche Formel enthalten.
wenn du willst, dass in Ausgeblendeten Zellen die Werte nicht berechnet werden, müsstest du das in der Formel darstellen.
also nicht: =D2*E2
sondern: =Wenn(Teilergebnis(3;D2);D2*E2;"")
mit dieser Formel wird dann, wenn die Zelle ausgeblendet ist, nichts gerechnet sondern nur der Text "" ausgegeben.
Gruß Daniel
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 15:56:28
Daniel
andererseits bekommst du, wenn du eine Auswertung wie =Summe(F:F) durch =Aggregat(9;5;F:F) ersetzt, die Summe über die sichtbaren Zellen der Spalte F.
Gruß Daniel
AW: Formel über gefilterte Zeilen
23.11.2022 16:25:22
ChrisL
Hi
Etwas verwirrend. Eine mögliche Interpretation:

Sub t()
With Worksheets("Tabelle1").ListObjects("Tabelle1")
.Range.AutoFilter
.ListColumns("VK").DataBodyRange.ClearContents
.ListColumns("Nettopreis").Range.AutoFilter 4, Array("5", "6"), xlFilterValues
.ListColumns("VK").DataBodyRange.Formula = "=[@Nettopreis]*[@Aufschlag]"
End With
End Sub
cu
Chris
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 16:51:10
Herbert_Grom
Hallo Angelika,
mit dieser Formel sollte es doch klappen:

=AGGREGAT(9;3;F:F)
9 = für Summe
3 = für Ausgeblendete Zeilen ignorieren
Servus
AW: Formel über gefilterte Zeilen
23.11.2022 17:17:00
Angelika
Vielen lieben Dank an alle.... ich bin begeistert von dieser Hilfsbereitschaft.
Vielen Dank, Grüße Angelika
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 17:21:32
Herbert_Grom
Jetzt weiß ich aber immer noch nicht, ob es dir geholfen hat!
AW: Formel über gefilterte Zeilen
23.11.2022 17:41:09
onur
"Vielen lieben Dank an alle" ist definitiv KEIN Feedback - klingt eher nach auf den Boden gestreute Almosen.
Wenn mehrere Leute versucht haben, dir zu helfen, solltest du auch mal dir die Mühe machen, dich zu den einzelnen Beiträge zu äussern.
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 19:10:37
JoWE
Ich denke das "ich bin begeistert von dieser Hilfsbereitschaft" schon recht deutlich zum Ausdruck bringt, dass zum einen zumindest eine der Lösungen funktioniert und zum anderen damit alle die hier zur Frage gepostet hatten damit ausreichend lobend angesprochen sind.
Wäre Angelika sonst begeistert?
Seid doch bitte nicht so empfindlich.
Gruß
Jichen
Anzeige
AW: Formel über gefilterte Zeilen
23.11.2022 19:41:23
Christian
Hallo zusammen, ja ich muss noch testen, hab heute noch einen anderen Termin, sobald ich getestet habe und es funktioniert gebe ich natürlich Bescheid… Vielen Dank
Angelika
andere Konzept...
23.11.2022 17:34:45
Yal
Hallo Angelika,
mit einer User Defined Function kann man den Zustand der Zeile ausgeben (muss in einem allgemeine Modul abgelegt werden):

Public Function IstZeileSichtbar(Optional Target As Range) As Boolean
If Target Is Nothing Then Set Target = Application.Caller
IstZeileSichtbar = Not Target.EntireRow.Hidden
End Function
= IstZeileSichtbar()
ergibt dann WAHR oder FALSCH je nach dem, ob die Zeile gefiltert ist oder nicht.
Man sieht aber nur WAHR.
Mit
= IstZeileSichtbar() * 1
sieht man zwar nur einser, aber eine Summe über die ganze Spalte zeigt, dass alle nicht sichtbare Zelle null sind.
Man kann entweder eine Wenn auf dem Wahr/Falsch oder eine Multiplikation auf dem 1/0 vornehmen, um das gewünschte Ergebnis zu erreichen.
VG
Yal
Anzeige
;

Forumthreads zu verwandten Themen

Gefilterte Zeilen/ Spalten Kopieren
Falk

Hallo zusammen,
folgendes Problem für mich:
Ich filtere den Bereich von A1:CD1000. Der Filter wird je nach Anforderung auf verschiedene Spalten gesetzt.
In den Zellen von Spalte A befindet sich eine Formel, die den Blattnamen anzeigt wenn in Spalte D und F etwas eingetragen ist anso...
11.11.2008 10:13:33
Thread-ID: 1023442
3 Beiträge
Zeilen variabel oft kopieren und einfügen
Bernd

Hallo Forum,
ich tüftle seit einiger Zeit vergebens an folgender Aufgabe und bitte um eure Hilfe:
Ich habe in einer Mappe zwei Sheets: Tab1 und Tab2
In Tab1 importiere ich Daten mit Hilfe des Textkonvertierungsassistenten aus einer txt-Datei.
Die txt-Datei kann unterschiedlich v...
03.09.2009 17:41:35
Thread-ID: 1100389
9 Beiträge
Anzeige
bestimmte gefilterte Zeilen kopieren
kiki

Hallo,
leider habe ich auf meinen letzten Beitrag keine Antwort bekommen, daher versuche ich es nochmal unter anderem Betreff.
ich bin dabei ein Makro zu schreiben, bei dem ich unter anderem gefilterte Daten kopieren muss und komme nicht weiter.
Ich habe eine Beispielmappe erstellt,...
26.06.2011 09:08:17
Thread-ID: 1219714
3 Beiträge
Zeilen individuelle Kopieren und einfügen
Martin

Hallo zusammen,
ich habe folgende - eigentlich recht einfaches - Problem, das ich aber nicht lösen kann.
Ich habe eine Excel-Tabelle die wie folgt aussieht:
aaa 5
bbb 6
ccc 2
Die letzte zeile zeigt an, wie oft die Zeile benötigt wird. Ausgegeben werden soll nach...
22.10.2011 12:08:19
Thread-ID: 1234979
8 Beiträge
Makrocode: Zeilen kopieren und einfügen
David

Hallo,
ich bräuchte einen Makro Code der folgendes macht:
1) bestimme die unterste Zeile (bezogen auf Spalte A) in dem aktiven Tabellenblatt
2) kopiere diese Zeile + die nächst oberen 9 Zeilen
3) füge diese kopierten Zeilen unter die unterste Zeile aus Schritt1 ein und mache daz...
14.11.2012 14:46:17
Thread-ID: 1286631
7 Beiträge
Nur sichtbare Zeilen kopieren und einfügen
Tim

Hallo zusammen,
ich habe eine Tabelle mit bereits mehreren Makros (daher auch ausgeblendete Zeilen).
Nun möchte ich der Übersicht halber nur die sichtbaren Zeilen:
- ab Zeile 12 bis zum Ende des Worksheets (das Ende variiert)
- und nur Spalte A bis Spalte D
kopieren und in...
23.09.2013 11:16:11
Thread-ID: 1331885
6 Beiträge
Zeilen kopieren und einfügen, aber nicht am Ende
Huffi

Guten Morgen,
ich habe eine Tabelle erstellt, die aus unterschiedlichen Kostenstellen besteht.
Mittels Makro kann ich eine zusätzliche makierte Zeile einfügen, bzw. löschen.
Die einzelnen Kostenstellen haben eine Zwischensumme.
Am Ende der Tabelle werden die Summen der einzelne...
07.11.2013 09:19:57
Thread-ID: 1337444
4 Beiträge
Makro gefilterte Zeilen in neue Mappe kopieren
Susann

Hallo Herber-Team,
trotz ausgiebiger Internet-Recherche habe ich das benötigte Makro leider nicht komplett hinbekommen, bitte um Eure Hilfe:
Worum es geht:
habe eine Excel-Arbeitsmappe Name "Gesamtdaten.xlsm", diese soll nach diversen Kriterien gefiltert werden (Makro dafür konnte i...
02.04.2014 16:31:42
Thread-ID: 1355908
5 Beiträge
bestimmte Anzahl an Zeilen kopieren und einfügen
Hans

Hallo zusammen,
habe ein Excel-Sheet mit ca. 1.800 Zeilen und 32 Spalten.
Nun möchte ich, dass ein Makro die Daten aus Zeile H6:H37 kopiert und in G38:G69 einfügt. Anschließend soll Zeile I6:I37 kopiert werden und in Zeile G70:G81 kopiert werden, usw. bis zur letzten Spalte.
Kann mi...
17.09.2014 10:35:19
Thread-ID: 1382027
2 Beiträge
Tabellenzeilen suchen, kopieren und einfügen
Chris

Hallo liebe Forumsmitglieder,
ich möchte in ein Excel Tool ein Makro einbauen, das folgende Funktionen hat:
1) Ich kann einen Dateipfad für eine Quellexcel auswählen, aus dem ich Daten Importieren möchte. (bereits erledigt)
2) Das Makro soll beim Aktivieren die Quellexcel öffnen (be...
24.09.2014 18:59:09
Thread-ID: 1383269
5 Beiträge
Anzeige
VBA Tab1 Zeilen kopieren und in Tab2 einfügen
mehmet
Hallo Forum,
ich komme einfach nicht weiter.
Es sollen die Inhalte (Value) von Tab1 N110:U130 in Tab2 Spalte AA..:AN.. (Zeilen in gelb) eingefügt werden.
Es soll also in Tab1 Zelle N110 und Zelle O110 geguckt werden ob Zellen belegt sind.
Wenn beide Zellen belegt, dann soll Tab1 Zei...
24.10.2014 21:12:24
Thread-ID: 1388524
14 Beiträge
vba gefilterte Zeilen einzeln kopieren
Bernhard

Hallo,
ich habe eine Excel Datei mit u.a. 2 Blättern, "Isogonen" und "gefiltert".
Gefilterte Daten aus "Isogonen" sollen per vba in "gefiltert" kopiert werden für eine Grafik, und zwar sequentiell, Zeile für Zeile... sodass sich die Kurve langsam aufbaut.
In "Isogonen" nimmt das vba...
13.05.2016 17:13:39
Thread-ID: 1493240
20 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Excel-Formel über gefilterte Zeilen anwenden

Schritt-für-Schritt-Anleitung

  1. Setze den Autofilter:

    • Markiere den Bereich deiner Tabelle und aktiviere den Autofilter. Dies kannst du über das Menü "Daten" > "Filter" erreichen.

  2. Berechne die Werte nur in sichtbaren Zellen:

    • Um eine Excel-Formel auf gefilterten Bereich anzuwenden, kannst du die Funktion Teilergebnis oder AGGREGAT verwenden. Zum Beispiel: 
      =WENN(TEILERGEBNIS(3;D2);D2*E2;"")
    • Diese Formel multipliziert die Werte in den Zellen D2 und E2 nur, wenn die Zeile sichtbar ist.

  3. Anwenden der Formel auf die gesamte Spalte:

    • Um die Formel auf alle gefilterten Zellen anzuwenden, ziehe das Ausfüllkästchen in der unteren rechten Ecke der Zelle nach unten.

  4. VBA-Makro für komplexe Berechnungen:

    • Wenn du VBA verwenden möchtest, kannst du folgendes Makro erstellen: 
      Sub Formel()
 Dim iRow As Integer
 iRow = ThisWorkbook.Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Row
 With ThisWorkbook.Worksheets("Tabelle1")
     .Range("H2").Formula = "=WENN(TEILERGEBNIS(3;D2);D2*E2;"""")"
     .Range("H2:H" & iRow).FillDown
 End With
End Sub

Häufige Fehler und Lösungen

  • Problem: Die Formel berechnet auch ausgeblendete Zellen.

    • Lösung: Stelle sicher, dass du die Teilergebnis- oder AGGREGAT-Funktion verwendest, um nur auf gefilterte Daten zuzugreifen.

  • Problem: Die Formel wird nicht auf alle gefilterten Daten angewendet.

    • Lösung: Überprüfe, ob die Formel korrekt in der ersten Zelle eingegeben wurde und dass das Ausfüllkästchen verwendet wurde, um die Formel auf die anderen Zellen zu übertragen.

Alternative Methoden

  • Nutzung von SpecialCells:

    • In VBA kannst du auch Range(...).SpecialCells(xlCellTypeVisible) verwenden, um nur auf sichtbare Zellen zuzugreifen. Beispiel: 
      Dim visibleRange As Range
Set visibleRange = ThisWorkbook.Worksheets("Tabelle1").Range("A1:A10").SpecialCells(xlCellTypeVisible)

  • User Defined Function (UDF):

    • Du kannst eine benutzerdefinierte Funktion erstellen, die überprüft, ob eine Zeile sichtbar ist: 
      Public Function IstZeileSichtbar(Optional Target As Range) As Boolean
If Target Is Nothing Then Set Target = Application.Caller
IstZeileSichtbar = Not Target.EntireRow.Hidden
End Function
    • Verwende dann =IstZeileSichtbar() in deiner Tabelle.

Praktische Beispiele

  • Berechnung von VK:

    • Wenn du den Verkaufspreis (VK) auf Basis des Nettopreises und des Aufschlags berechnen möchtest, kannst du die folgende Formel verwenden: 
      =WENN(TEILERGEBNIS(3;[Nettopreis]);[@Nettopreis]*[@Aufschlag];"")

  • Summe über gefilterte Werte:

    • Um die Summe der gefilterten Werte in einer Spalte zu berechnen, nutze: 
      =AGGREGAT(9;3;F:F)

Tipps für Profis

  • Verwende AGGREGAT: Es ist flexibler als TEILERGEBNIS, da du mehrere Funktionen kombinieren kannst.
  • Beachte die Datenstruktur: Stelle sicher, dass deine Daten sauber formatiert sind, um Fehler bei der Berechnung zu vermeiden.
  • Testen: Teste deine Formeln mit verschiedenen Filtereinstellungen, um sicherzustellen, dass sie korrekt funktionieren.

FAQ: Häufige Fragen

1. Kann ich VBA verwenden, um nur gefilterte Zellen zu bearbeiten? Ja, du kannst SpecialCells(xlCellTypeVisible) in VBA verwenden, um nur mit sichtbaren Zellen zu arbeiten.

2. Was ist der Unterschied zwischen Teilergebnis und AGGREGAT? Teilergebnis ist einfacher, während AGGREGAT mehr Funktionen unterstützt und mehr Kontrolle über die Berechnung bietet, insbesondere bei gefilterten Daten.

3. Wie kann ich eine Formel nur in gefilterte Zellen einfügen? Verwende die Teilergebnis- oder AGGREGAT-Funktion in deiner Formel, um sicherzustellen, dass nur die sichtbaren Zellen berechnet werden.

Beliebteste Forumthreads (12 Monate)

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