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

Teilergebnis mit Autofilter incl. Indirekt

Forumthread: Teilergebnis mit Autofilter incl. Indirekt

Teilergebnis mit Autofilter incl. Indirekt
22.02.2008 12:20:00
manasse
Hallo,
ich möchte aus einer Tabelle mit Autofilter die Anzahl an Zeilen ermitteln, die einem gewissen Kriterium entsprechen. Dazu nutzte ich z.B. die Formel
=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(2:100)))*(B2:B100="m"))
Da sich die Anzahl der Zeilen verändert möchte ich in der Formel mit INDIREKT angeben, von welcher bis zu welcher Zeile die Tabelle geht. Für den zweiten Teil ist das recht einfach wenn ich in die Felder A1 und B1 angebe von wo bis wo die Tabelle geht.
INDIREKT("n"&A1):INDIREKT("n"&B1)
Nur in dem Bereich ("B"&ZEILE(2:100)) gibt es da Probleme.
Hat dazu jemand eine Lösung?
Für jede Info bin ich dankbar.
Gruß
Manasse

Anzeige

1
Beitrag zum Forumthread
Beitrag zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Teilergebnis mit Autofilter incl. Indirekt
23.02.2008 12:01:49
fcs
Hallo Manasse,
du kannst für ZEILE(2:100) in gleicher Weise per INDIREKT den Zellenbereich festlegen. ZEILE erfordert nicht unbedingt die Angabe der Zeilen als 2:100, genauso funktioniert "B2:B100")
Falls du eine Spalte hast, in der für alle Listen-Zeilen ein Eintrag vorhanden ist, dann kannst du die letzte Datenzeile auch per Formel berechnen.
Damit geht folgendes:

A         B         C         D
1        3         8         2
2  Feld 01   Feld 02   Feld 03   Feld 04
3        1         x         A
4        2                   A         C
5        3         m         A         C
6        4         m         B         C
7        5                   B         C
8        6         x         B
Benutzte Formeln:
A1:  =ZEILE(A2)+1
B1:  =ZEILE(A2)+ANZAHL2(A2:A10000)-1
C1:  =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(INDIREKT("B" & A1 & ":" & "B" &
B1))))*(INDIREKT("B" & A1 & ":" & "B" & B1)="m"))


Die Formeln in A1 und B1 könnte man natürlich auch direkt in die Formel in C1 einbauen
Gruß
Franz

Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Teilergebnis mit Autofilter und Indirekt in Excel

Schritt-für-Schritt-Anleitung

  1. Daten eingeben: Erstelle eine Tabelle in Excel mit den Daten, die du filtern möchtest. Achte darauf, dass die Spaltenüberschriften vorhanden sind.

  2. Autofilter aktivieren: Wähle deine Tabelle aus und aktiviere den Autofilter über Daten > Filter > Autofilter.

  3. Zellen für den Bereich festlegen: In den Zellen A1 und B1 kannst du den Bereich definieren, den du filtern möchtest. Zum Beispiel:

    • A1: =ZEILE(A2)+1
    • B1: =ZEILE(A2)+ANZAHL2(A2:A10000)-1

  4. Teilergebnis berechnen: Nutze die folgende Formel in einer Zelle, um die Anzahl der gefilterten Zeilen zu ermitteln:

    =SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(INDIREKT("B" & A1 & ":" & "B" & B1))))*(INDIREKT("B" & A1 & ":" & "B" & B1)="m"))

    Diese Formel zählt die Zeilen, die dem Kriterium „m“ entsprechen.

  5. Ergebnisse überprüfen: Filtere deine Tabelle nach dem gewünschten Kriterium und beobachte, wie sich das Teilergebnis automatisch ändert.

Häufige Fehler und Lösungen

  • Fehler bei der Formel: Wenn die Formel nicht funktioniert, überprüfe, ob die Zellbezüge korrekt sind. Achte darauf, dass die Bereiche in deiner Formel mit den tatsächlichen Daten übereinstimmen.

  • Indirekt nicht richtig: Stelle sicher, dass die INDIREKT-Funktion korrekt eingesetzt ist. Der Bezug sollte immer innerhalb der Anführungszeichen stehen, z.B. INDIREKT("B"&A1).

  • Autofilter funktioniert nicht: Vergewissere dich, dass der Autofilter korrekt aktiviert ist. Manchmal kann ein einfacher Neustart von Excel helfen.

Alternative Methoden

  • Pivot-Tabellen: Eine Pivot-Tabelle kann eine schnelle Möglichkeit sein, um Teilergebnisse zu erstellen. Du kannst die Daten nach verschiedenen Kriterien gruppieren und analysieren.

  • SUMMEWENN-Funktion: Anstelle von Teilergebnis kannst du auch die SUMMEWENN-Funktion verwenden, um die Summe der Werte in einem bestimmten Bereich zu berechnen, die einem bestimmten Kriterium entsprechen.

Praktische Beispiele

Stell dir vor, du hast eine Verkaufsdaten-Tabelle mit den Spalten „Produkt“, „Verkäufe“ und „Region“. Um die Anzahl der Verkäufe eines bestimmten Produkts in einer bestimmten Region zu ermitteln, kannst du die oben genannten Schritte ausführen und die entsprechende Filterung anwenden.

Hier ist eine Beispiel-Formel für die Verkäufe eines Produkts „A“ in der Region „Nord“:

=SUMMENPRODUKT(TEILERGEBNIS(3;INDIREKT("B"&ZEILE(INDIREKT("B" & A1 & ":" & "B" & B1))))*(INDIREKT("B" & A1 & ":" & "B" & B1)="A")*(INDIREKT("C" & A1 & ":" & "C" & B1)="Nord"))

Tipps für Profis

  • Dynamische Bereiche: Nutze dynamische Bereichsnamen, um deine Formeln flexibler zu gestalten. Du kannst dafür die Funktion „Namensmanager“ in Excel verwenden.

  • Formatierung: Achte darauf, dass deine Daten richtig formatiert sind. Zum Beispiel sollten Zahlen nicht als Text formatiert sein, da dies zu falschen Ergebnissen führen kann.

  • Verwendung von Tastenbefehlen: Lerne die häufigsten Tastenkombinationen in Excel, um deine Arbeit effizienter zu gestalten.

FAQ: Häufige Fragen

1. Kann ich die Teilergebnisse auch in einer anderen Zelle anzeigen lassen?
Ja, du kannst die Formel in jede Zelle eingeben, die du möchtest. Achte darauf, dass die Zellbezüge in der Formel an die neue Position angepasst werden.

2. Was ist der Unterschied zwischen SUMMENPRODUKT und SUMMEWENN?
SUMMENPRODUKT kann mehrere Bedingungen berücksichtigen und ist flexibler, während SUMMEWENN einfacher ist und nur eine Bedingung prüft.

Beliebteste Forumthreads (12 Monate)

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