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

Zellen sortieren, Formel mit Zellenbezug anpassen

Forumthread: Zellen sortieren, Formel mit Zellenbezug anpassen

Zellen sortieren, Formel mit Zellenbezug anpassen
17.01.2017 18:18:01
Robin
Hallo,
ich habe mir eine Tabelle angefertigt, die ich mit einem Filter sortiert habe und nach mehreren Kriterien beliebig sortieren kann. Nur leider verliert dann eine Summenformel die außerhalb der Tabelle steht, den Bezug, wenn ich filtere, sodass ich immer extra zum ursprünglichen Stand zurückkehren muss.
Gibt es da eine Möglichkeit, dass sich die Zellbezüge in der Formel auch mit ändern, wenn die Zeilen in der Tabelle durch das Filtern neu angeordnet wurden?
Ich hoffe ihr könnt mir da weiterhelfen!
Danke
Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zellen sortieren, Formel mit Zellenbezug anpassen
17.01.2017 18:21:22
Hajo_Zi
warum muss die Formel geändertrt werden. es wird doch nur sortiert.
Ich würde vor der Summenzeile eine Leerzeile machen, die kannst Du ja ausblenden, dann wird die Zeil nicht mit sortiert.

AW: Zellen sortieren, Formel mit Zellenbezug anpassen
17.01.2017 18:53:42
Robin
Nein da hast du mich wohl ein bisschen falsch verstanden. Wenn ich ja meine Tabelle neu filtere dann stehen z.B. die Werte von B2-B5 an der Stelle B3;B5;B7;B8 und meine Summenformel die sich auf die Tabelle bezieht, aber dort natürlich nicht mitsortiert wird zeigt wie vorher die Summe von B2-B5 an, richtig wäre dann aber die Summe aus den Werten B3;B5;B7;B8
Anzeige
AW: Zellen sortieren, Formel mit Zellenbezug anpassen
21.01.2017 12:08:25
Peter
Hallo Robin,
bei der einfachen Filterfunktion in Excel werden lediglich die Zeilen, die nicht der Filterung entsprechen, ausgeblendet. Eine Summenbildung (Formel), die den ausgeblendeten Bereich nicht berücksichtigt, ist mir nicht bekannt. Ich empfehle, in Deinem Fall den Spezialfilter ("Filtern" u. "Erweitert") zu verwenden. Ein Beispiel mit nur einer Spalte, die gefiltert wird, lege ich bei. Es sind auch mehrere zu filternde Spalten und mehrere Kriterien- und Ausgabespalten möglich. Lese hierzu die entsprechende Anleitung in der Excel-Hilfe (Suche nach "Spezialfilter").
https://www.herber.de/bbs/user/110772.xlsm
Das Makro1 steuert diesen Spezialfilter und wird ausgelöst durch das Ereignis-Makro zur Tabelle1.
Bitte mal ausprobieren, FeedBack wäre schön.
Mit freundlichem Gruß
Peter Kloßek
Anzeige
AW: Zellen sortieren, Formel mit Zellenbezug anpassen
21.01.2017 13:57:41
Robin
Hallo Peter,
erstmal vielen Dank für deinen Lösungsvorschlag.
Zur Veranschaulichung so sieht in etwa meine Tabelle aus:
Spielername | Spielnr | Punkte
Spieler 1 1 10
Spieler 1 2 3
Spieler 2 1 7
Spieler 2 2 12
Spieler 3 1 5
Spieler 3 2 1
Unter der Tabelle hatte ich dann die Summenformel von Spieler 1 in Punkten usw.
Mein Problem war nämlich jetzt, dass wenn ich dort die Summe gezogen habe und nach Punkten sortiert habe, für Spieler 1 nicht mehr der korrekte Wert von 13 Punkten angezeigt wurde, sondern 22 Punkte (absteigend sortiert). Da bin ich aber jetzt selber auf die Lösung gekommen, habe einfach die SUMMEWENN-Formel benutzt; tja so einfach kann es manchmal sein :D
Aber trotzdem noch einmal vielen Dank!
Anzeige
;

Forumthreads zu verwandten Themen

Sortieren mit Formel
Mike


Hey Cracks,
wieso werden in der nachfolgenden Formel in Spalte K die Einträge mehrmals
angzeigt? Weil es auch nummerische Einträge hat?
Eingabe Ausgabe

 IJK24  http://www.excelformeln.de/formeln.html?welcher=23625KST 134 AB 11026KST 135 AB 11027...
26.03.2009 12:49:14
Thread-ID: 1064208
5 Beiträge
Anzeige
Sortieren mit Formel
Heinz

Hallo Leute
Ich hätte gerne A3:IV 154 gerne nach Spalte B aufsteigend sortiert.
Nur mit unteren Code funktioniert es leider nicht.
Da in der Formel Bezüge auf andere Sheets enthalten sind.
Private Sub Worksheet_Activate() ActiveSheet.Unprotect Password:="vetro" Range("A3...
07.01.2012 17:19:39
Thread-ID: 1245086
10 Beiträge
Summe bei gefilterten Daten
nick28

Guten Tag,
ich habe eine Statitstiktabelle, in der auf einem weiteren Blatt die Daten gespiegelt sind und in den Spaltenköpfen Filter gesetzt sind. Diese Filter sollen z.B. die geleisteten Nachtdienststunden summieren. Das klappt soweit mit dem Filtern auch, aber die Summenfunktion addiert...
03.01.2016 10:01:24
Thread-ID: 1466788
3 Beiträge
Anzeige
AlphabetischesSortieren einer Tabelle mit Formeln
Andreas

Hallo alle zusammen,
ich weiß, es ist wahrscheinlich sehr einfach, allerdings habe ich es immer noch nicht geschafft, deshalb muss ich hier so naiv fragen.
Ich möchte mit einer Formel eine bestehende Tabelle in einem neuen Arbeitsblatt alphabetisch Sortiert ausgeben. Dies soll mit Form...
13.04.2016 22:33:43
Thread-ID: 1487892
2 Beiträge
Summewenn() bei gefiltert
Fred

Hallo,
eine Frage zur Formel Summewenn() bzw. dessen Ergebnis.
Ich habe die Formel =SUMMEWENN($L$7:$L$3350;0;M$7:M$3350) welche prüft, ob in Spalte „L“ (ab Zeile 7) der Wert „0“ steht und wenn ja, mir die entsprechende Summe aus Spalte „M“ (ab Zeile 7) ausgibt.
Bei gefilterte...
12.11.2016 15:01:37
Thread-ID: 1524484
4 Beiträge
Formel mit Kalender zum Sortieren von Daten
Alice

Ich brauche eine Formel zum sortieren von Daten, die an einen Kalender gekoppelt ist, denn ich möchte nur die Termine haben, die vom Datum her noch nicht abgelaufen sind und das soll der Rechner selbst erkennen aber ich bin in Excel nicht so fit
04.09.2002 19:56:34
Thread-ID: 156618
2 Beiträge
Summe mit Kriterien bei Autofilter
user23

Hallo,
ich habe folgende Excel-Datei:
https://www.herber.de/bbs/user/119493.xlsx
Die Tabelle generiere ich jeden Tag aus meinem ERP-System. Die Daten unter den Überschriften sind dynamisch.
Die Tabelle wird wie folgt genutzt: Es wird der Filter entweder in Spalte A oder Spalt...
02.02.2018 13:37:47
Thread-ID: 1606563
5 Beiträge
pivot summe bei einsatz v. filter
Kathrin

Hallo zusammen,
ich habe eine Pivottabelle, bei der im Bereich "Spalten" Mengenwerte f. versch. Produktkategorien nach Monaten dargestellt werden. Nun habe ich aus allen in der Datengrundtabelle vorhandenen Produktkategorien per Filter in der Pivot nur zwei Kategorien ausgewählt. Am Ende mö...
11.10.2018 08:39:46
Thread-ID: 1650640
4 Beiträge
Tabellen umsortieren per Formel oder Makro
Ralf

Hallo zusammen,
ich verzweifle mal wieder an etwas vermeintlich einfachem:
Ich habe CSV Files für drei Marken mit dementsprechenden Einträgen, die jeweils für drei Filialen gelistet sind.
Diese möchte ich mittels Formel oder Makro umsortieren, so dass ich drei Filialen mit jeweils d...
12.03.2019 21:46:30
Thread-ID: 1680914
3 Beiträge
Sortieren mit Formeln
Joerg

Ich möchte den Bereich B5 bis I11 sortieren lassen, und zwar nach den aufsteigenden Werten in Spalte G.
Ich bekomme aber dauernd Fehlermeldungen "Bezug#" in den Formelspalten am Ende der Sortierung.
Wie lässt sich das verhindern ?

https://www.herber.de/bbs/user/252.xls

Dan...
14.07.2003 22:05:58
Thread-ID: 280986
7 Beiträge
Summe bei gefilterten Daten
Albatros

Ich habe ca. 1500 Daten in meiner Excel-Tabelle stehen. Diese möchte ich nun über den Autofilter filter und die Summe nur für die gefilterten Daten ausgeben.
Wenn ich alles über Summe laufen lasse, bekomm ich die Summe aller Daten.
Wie kann ich das verhindern?


Gruß
Oliver<...
19.08.2003 14:38:48
Thread-ID: 296946
4 Beiträge
Sortieren einer Spalte mit Formel
Hans
Hallo.
Mein zweiter Versuch.Hoffe jetzt etwas verständlicher.

Ich habe in einer Spalte(C) mit 5 Zeilen eine Rangliste ermittelt,deren
Bezug aus der Punktanzahl einer anderen Spalte (A)mit 5 Zeilen ermittelt wurde.
In der Spalte B sind die dazugehörigen Namen eingetragen.
Ich...
07.12.2003 18:37:56
Thread-ID: 348577
7 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Zellen sortieren und Formeln mit Zellenbezug anpassen

Schritt-für-Schritt-Anleitung

  1. Tabelle erstellen: Erstelle eine Tabelle mit den benötigten Daten. Zum Beispiel: Spielername, Spielnr und Punkte.

  2. Formel einfügen: Platziere eine Summenformel unter deiner Tabelle, um die Punkte zu summieren. Diese Formel könnte so aussehen:

    =SUMME(B2:B6)

  3. Filter anwenden: Wende den Filter an, um deine Daten zu sortieren. Gehe dazu auf die Registerkarte "Daten" und klicke auf "Filtern".

  4. Sortierung durchführen: Sortiere die Tabelle nach deinen Kriterien (z.B. nach Punkten). Beachte, dass die Summenformel möglicherweise nicht mehr den korrekten Bezug hat.

  5. Zellenverknüpfung anpassen: Um sicherzustellen, dass deine Formel den richtigen Bezug behält, kannst du die SUMMEWENN-Funktion nutzen. Zum Beispiel:

    =SUMMEWENN(A2:A6, "Spieler 1", B2:B6)

Häufige Fehler und Lösungen

  • Falscher Zellbezug nach Sortierung: Wenn du deine Tabelle sortierst und die Summenformel nicht den korrekten Bezug anzeigt, liegt das daran, dass die Formel nicht dynamisch ist. Verwende die SUMMEWENN-Formel, um den Bezug beizubehalten.

  • Formel ändert sich nicht: Wenn du die Tabelle filterst und die Formel nicht die erwarteten Werte anzeigt, stelle sicher, dass die Formel richtig auf die gefilterten Zellen verweist.

Alternative Methoden

  • Spezialfilter nutzen: Eine effektive Methode, um die Daten zu sortieren, ohne die Formeln zu verlieren, ist die Verwendung des Spezialfilters. Dies ermöglicht es, die Daten nach mehreren Kriterien zu filtern und gleichzeitig die Summenformeln korrekt zu halten.

  • Pivot-Tabellen: Eine weitere Möglichkeit, um Daten zu sortieren und zu analysieren, ist die Verwendung von Pivot-Tabellen. Diese können dir helfen, die Summen schnell zu berechnen, ohne die Bezüge manuell anpassen zu müssen.

Praktische Beispiele

  • Beispiel für die Anwendung von SUMMEWENN: Angenommen, du hast die folgende Tabelle:

    Spielername Punkte
    Spieler 1 10
    Spieler 2 12
    Spieler 1 3
    Spieler 2 7

    Um die Gesamtpunkte für Spieler 1 zu berechnen, verwende:

    =SUMMEWENN(A2:A5, "Spieler 1", B2:B5)

  • Filter und Sortierung: Wenn du die Punkte nach absteigender Reihenfolge sortierst und die Summe für Spieler 1 berechnest, kannst du sicherstellen, dass die Formel immer den richtigen Bezug anzeigt.

Tipps für Profis

  • Verwende absolute Zellbezüge: Um Zellbezüge beim Sortieren beizubehalten, kannst du absolute Zellbezüge nutzen (z.B. $B$2:$B$6), um sicherzustellen, dass die Formel nicht leicht verändert wird.

  • Formeln kombinieren: Du kannst mehrere Formeln in einer Zelle kombinieren, um komplexere Berechnungen durchzuführen, z.B. SUMME mit WENN.

  • Datenvalidierung: Implementiere Datenvalidierungsregeln, um sicherzustellen, dass nur die richtigen Daten in die Tabellen eingegeben werden. Dies kann helfen, Fehler bei der Sortierung zu minimieren.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass meine Formeln bei einer Sortierung nicht den Bezug verlieren?
Verwende die SUMMEWENN-Funktion oder absolute Zellbezüge, um sicherzustellen, dass die Formeln die richtigen Werte anzeigen, auch wenn die Daten sortiert werden.

2. Was kann ich tun, wenn meine Summenformel falsche Werte anzeigt?
Überprüfe die Zellbezüge in deiner Formel. Stelle sicher, dass du die richtigen Zellen für die Berechnung verwendest und ziehe in Betracht, die SUMMEWENN-Funktion zu nutzen, um spezifische Kriterien anzuwenden.

Beliebteste Forumthreads (12 Monate)

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