Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Formel soll Filter beachten





Frage

Hallo liebes Forum, wie kann ich Excel dazu kriegen, dass er einer Verkettungsformel, die sich auf ein anderes Tabellblatt bezieht, nur jeweils die Zeilen berücksichtigt die im anderen Tabellenblatt gerade durch die Filter sichtbar sind. Brauche ich dazu ein Makro oder kann ich was vor meine Funtkion schreiben? Danke.

Antwort 1 von JoeKe

Hallo,

vielleicht hilft dir die Funktion "Teilergebnis" weiter.

=TEILERGEBNIS(9;Tabelle2!A2:A11)

liefert die Summe der Zahlen in Tabelle2 im Bereich A2:A11 die nicht durch den Filter ausgeblendet sind.

MfG

JöKe

Antwort 2 von JörnRosenlund

Leider geht das gar nicht.. ich will ja nicht wissen, wie viele Zahlen es sind oder deren Summe oder Produkte haben. Ich will das er nur diese Zeilen die gefiltert sind für eine neue komplizierte Formel verwendet, die mit allen Werten aus dem Blatt 1 rechnet und sie verkettet...
Gibt es noch einen anderen Vorschlag?

Antwort 3 von JoeKe

Hallo,

dann fällt mir nur eine VBA-Lösung ein.
Folgender Code kopiert die gefilterten Werte auf ein Hilfsblatt. Deine Formeln müssen dann auf diese Werte zugreifen.

Der Code gehört in das VBA-Projekt des Tabellenblattes auf dem die Daten gefiltert werden.

Option Explicit
Private Sub Worksheet_Deactivate()
Worksheets("Tabelle1").Columns("A:A").ClearContents
Worksheets("Tabelle2").Range("A1:A20").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Tabelle1").Range("A1")
End Sub


Die Tabellennamen sowie die Bereiche müssen angepasst werden.

MfG

JöKe

Antwort 4 von JörnRosenlund

Hmmm... so richtig glücklich macht mich das noch nicht... es will nicht so recht... ich kriege die Daten nicht rüber in die Hilfsspalte.
Stellte sich aber abgesehen davon dann nicht wieder das Problem das die eigentliche Berechnung sich ja dann an die Hilfstabelle anpassen müsste, d.h. die Bezüge bezüglich der Anzahl der Zeilen (im vollständigen Blatt wären das BU7:BU677) ändern müssten?

Gruß
Jörn

Antwort 5 von JoeKe

Hallo Jörn,

weshalb du die Daten nicht übertragen bekommst, kann ich leider nicht sagen. In meiner Testdatei läuft es reibungslos.
Hast du die Bezüge und die Blattnamen angepasst?

Mit den Berechnungen hast du Recht, dass sie sich auf das Hilfsblatt beziehen müssen. Ob allerdings die Formeln bezüglich der Zeilenzahl angepasst werden müssen, hängt aber von den Formeln selbst ab. Wenn die Zeilenzahl bei der Berechnug keine Einfluss hat, kann der Rest der Formel wie bisher bleiben.

Denkbar wäre auch eine komplette VBA-Lösung. Für eine solche sind deine Angaben aber zu dürftig.

MfG

JöKe

Antwort 6 von JörnRosenlund

Also die Formel um die es in dem Blatt "Performance Cluster" geht ist:

=VerkettenWenn(´Super Excel Sheet´!E7:E677;´Super Excel Sheet´!BW7:BW677;B3;ZEICHEN(10))

Dazu gibt es den VBA:

Option Explicit

Function VerkettenWenn(Bereich As Range, KriterienBereich As Range, Suchkriterium As String, Optional Trenner As String = "") As Variant
Dim strWerte As String, lngZaehler As Long

If Bereich.Cells.Count <> KriterienBereich.Cells.Count Then
VerkettenWenn = CVErr(2042)
Exit Function
End If

strWerte = ""
For lngZaehler = 1 To Bereich.Cells.Count
If KriterienBereich.Cells(lngZaehler) Like Suchkriterium Then
If strWerte = "" Then
strWerte = Bereich.Cells(lngZaehler)
Else
strWerte = strWerte & Trenner & Bereich.Cells(lngZaehler)
End If
End If
Next
VerkettenWenn = strWerte
End Function


Die Formel bezieht sich also leider auf die Zeilen :( Denke, dass ich die Bezüge angepasst habe... Wobei ich diesbezüglich sicher alles andere als erfahren bin - das ist alles noch recht neu für mich.

Zum Hintergrund: im Blatt "Performance Cluster" steht oben genannte Formel in C3, C4, C5, C6 & C7. Das 3. Argument (oben ist das B3, passt sich an B4, B5, B6 & B7 an). Am Schluß habe ich dann abhängig von den Einträgen in BW aus dem "Super Excel Sheet" ein Cluster, dass mir alle Mitarbeiter in eine Zelle sortiert, die in BW ein Kriterium (B3, B4, B5, B6 oder B7 erfüllen).

Hilft das weiter?

Antwort 7 von JoeKe

Hallo Jörn,

ich halte immer noch an meiner ersten VBA-Lösung fest. ;-)
Folgenden Code in das VBA-Project des Tabellenblattes "Super Excel Sheet":

Option Explicit
Private Sub Worksheet_Deactivate()
Worksheets("Kopie").Cells.ClearContents
Worksheets("Super Excel Sheet").Range("BW7:BW677").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Kopie").Range("BW7")
Worksheets("Super Excel Sheet").Range("E7:E677").SpecialCells(xlCellTypeVisible).Copy _
Destination:=Worksheets("Kopie").Range("E7")
End Sub


Die von dir gefilterten Daten werden beim deaktivieren des "Super Excel Sheet" auf das zuvor anzulegene Blatt "Kopie" übertragen. (Dieses kann ausgeblendet werden.)

Deine Formel musst du dann so abändern:

=VerkettenWenn(Kopie!$E$7:$E$677;Kopie!$BW$7:$BW$677;B3;ZEICHEN(10))

Zum besseren Verständnis, habe ich mal meine Beispieldatei gefilterte Daten hochgeladen.

Mfg

JöKe

Antwort 8 von JörnRosenlund

Hallo JöKe,

klappt jetzt super... Ich danke Dir. Wäre ohne die Hilfe echt aufgeschmissen gewesen. Danke.

Jörn

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: