Summenformel mit Spaltennummer var. Bezug

Forumthread: Summenformel mit Spaltennummer var. Bezug

14.11.2017 22:29:51

SteffenS

Hallo Zusammen,
ich möchte gern eine Formeln mit variablen Bezug verwenden.
Leider schaffe ich es nicht die ganze Spalte als variablen Bezug anzugeben.
Meine Formel lautet wie folgt:
D3=Blattname
E3=Spaltennummer
=SUMMEWENNS(INDIREKT("'" & $D$3&"'!"&ADRESSE(1;$E$3+0)&":"&ADRESSE(100000;$E$3+0));Tabelle1'!$I:$I; $A11;'Tabelle1'!$B:$B;$B11;'Tabelle1'!$G:$G;$E11)
Was muss ich tun, damit meine Formel funktioniert?
Aktuell erscheint nur #Wert!
Danke Euch schon mal.
VG Steffen Schmerler

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Summenformel mit Spaltennummer var. Bezug

14.11.2017 22:48:25

Daniel

Hi
Hast du dir schonmal den erzeugten Text angeschaut?
Entspricht dieser einem regulären Zellbezug?
Beachte weiterhin, dass im SummeWenns alle Zellbezüge auf die Bereiche die gleiche Anzahl von Zellen haben müssen.
Dh. Wenn du in den Bedingungen ganze Spalten verwendest, musst du auch in der Summe eine ganze Spalte angeben.
Noch ein Tip:
Wenn die Spalte als Nummer vorliegt, solltest du im Indirekt die Z1S1-Bezugsart einsetzen, dann entfällt das Umrechnen der Nummer in den Buchstaben, weil du die Spaltennummer angeben kannst.
(Das gilt nicht, wenn die Datei auf Rechnern mit verschiedenen Sprachen laufen muss, dann musst du die A1-Bezugsart einsetzen)
Gruß Daniel

AW: Summenformel mit Spaltennummer var. Bezug

15.11.2017 06:55:41

SteffenS

Hallo,
danke für den Tipp. Könnt ihr mir nochmal helfen, wie ich den ersten Teil der Formel so gestalte das die gesamte Spalte und nicht nur die ersten 100.000 Zeilen verwendet werden:
INDIREKT("'" & $D$3&"'!"&ADRESSE(1;$E$3+0)&":"&ADRESSE(100000;$E$3+0))
Vielen lieben Dank nochmal
VG SteffenS

AW: Summenformel mit Spaltennummer var. Bezug

15.11.2017 09:03:13

{Boris}

Hi,
beherzige Daniels Tipp und schau Dir mal ALLE Parmaeter der INDIREKT-Funktion an.
=INDIREKT(Bezug;A1)
Dazu aus der Onlinehilfe:
##Ist "A1" gleich WAHR oder nicht angegeben, wird "Bezug" als ein Bezug interpretiert, der in der A1-Schreibweise vorliegt.
##Ist "A1" gleich FALSCH, wird "Bezug" als ein Bezug interpretiert, der in der Z1S1-Schreibweise vorliegt
Damit kann man also auch in der Z1S1-Schreibweise referenzieren - das hat in Deinem Fall den Vorteil, dass Du die in E3 stehende SpaltenNUMMER nicht in den Spaltenbuchstaben umwandeln musst.
VG, Boris

AW: Summenformel mit Spaltennummer var. Bezug

15.11.2017 09:08:53

Daniel

Hi
für einen Bezug auf die ganze Spalte müsstest du noch die Zeilennummern aus dem Adressstring entfernen, denn ein Bezug auf eine ganze Spalte sieht ja so aus: "$A:$A"

INDIREKT("'"&$D$3&"'!"&Wechseln(ADRESSE(1;$E$3+0)&":"&ADRESSE(1;$E$3+0);"$1";""))

oder eben mit Z1S1, wenn die Spaltennummer in E3 steht:

INDIREKT("'"&$D$36&"'!S"&$E$3;falsch)

Gruß Daniel

AW: Summenformel mit Spaltennummer var. Bezug

17.11.2017 07:27:42

SteffenS

Hallo,
danke für die Tipps. Hatte es erstmal mit einem begrenzten Bereich probiert aber teste das dann gleich mal :-)
VG Steffen

Infobox / Tutorial

Summenformel mit variabler Spaltennummer in Excel

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um eine Summenformel mit einer variablen Spaltennummer in Excel zu erstellen, folge diesen Schritten:

Daten vorbereiten: Stelle sicher, dass du die Blattnamen in einer Zelle (z.B. D3) und die Spaltennummer in einer anderen Zelle (z.B. E3) hast.

Formel eingeben: Verwende die SUMMEWENNS-Funktion zusammen mit INDIREKT. Deine Grundformel sollte wie folgt aussehen:

=SUMMEWENNS(INDIREKT("'" & $D$3 & "'!" & ADRESSE(1; $E$3) & ":" & ADRESSE(100000; $E$3)); Tabelle1!$I:$I; $A11; Tabelle1!$B:$B; $B11; Tabelle1!$G:$G; $E11)

Anpassung für ganze Spalten: Um die gesamte Spalte zu referenzieren, passe die Formel an, indem du die Zeilennummern entfernst:

=SUMMEWENNS(INDIREKT("'" & $D$3 & "'!" & WECHSELN(ADRESSE(1; $E$3) & ":" & ADRESSE(1; $E$3); "$1"; "")); Tabelle1!$I:$I; $A11; Tabelle1!$B:$B; $B11; Tabelle1!$G:$G; $E11)

Häufige Fehler und Lösungen

#WERT! Fehler: Dieser Fehler tritt auf, wenn der erzeugte Text aus der INDIREKT-Funktion nicht korrekt ist. Überprüfe, ob der Bezug zu den Zellen und der Spaltennummer richtig ist.
Falsche Anzahl an Zellen: Stelle sicher, dass alle Zellbezüge in SUMMEWENNS die gleiche Anzahl von Zellen haben. Wenn du ganze Spalten verwendest, sollten alle Bereiche in der Formel ebenfalls auf ganze Spalten verweisen.

Alternative Methoden

Eine alternative Methode zur Verwendung der Spaltennummer in der Formel ist die Nutzung der Z1S1-Bezugsart. Hier ist ein Beispiel:

=INDIREKT("'" & $D$3 & "'!S" & $E$3; FALSCH)

Diese Methode erlaubt es dir, die Spaltennummer direkt anzugeben, ohne sie in Buchstaben umwandeln zu müssen.

Praktische Beispiele

Summe einer ganzen Spalte: Angenommen, du möchtest die Summe der gesamten Spalte B auf einem anderen Blatt berechnen:
```
=SUMME(INDIREKT("'" & $D$3 & "'!B:B"))
```
Kombinierte Bedingungen: Um die Summe auf Basis mehrerer Kriterien zu berechnen, kannst du die SUMMEWENNS-Funktion wie oben beschrieben mit einer variablen Spaltennummer nutzen.

Tipps für Profis

Z1S1-Bezugsart verwenden: Wenn du die Spaltennummer in der Formel verwenden möchtest, setze auf die Z1S1-Bezugsart, um die Umwandlung in Buchstaben zu vermeiden.
Zellenformatierung: Achte darauf, dass die Zellen, die die Spaltennummer enthalten, korrekt formatiert sind (z.B. als Zahl).
Fehlerüberprüfung: Nutze die Excel-Funktion „Formel überprüfen“, um sicherzustellen, dass deine Formeln korrekt sind.

FAQ: Häufige Fragen

1. Wie kann ich die Spaltennummer dynamisch ändern?
Du kannst die Spaltennummer in einer Zelle speichern (z.B. E3) und dann in deiner Formel auf diese Zelle verweisen.

2. Funktioniert das in allen Excel-Versionen?
Ja, die beschriebenen Methoden funktionieren in Excel 2010 und neueren Versionen. Stelle sicher, dass die verwendeten Funktionen in deiner Excel-Version verfügbar sind.

Beliebteste Forumthreads (12 Monate)

Summenformel mit Spaltennummer var. Bezug

Forumthread: Summenformel mit Spaltennummer var. Bezug

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Summenformel mit variabler Spaltennummer in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Summenformel mit Spaltennummer var. Bezug

Forumthread: Summenformel mit Spaltennummer var. Bezug

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Summenformel mit variabler Spaltennummer in Excel

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread