Clever-Excel-Forum

Hallo,

ich tu mich gerade mit einem Problem sehr schwer (leider schon einige Stunden) und vielleicht kann mir jemand sagen wo mein Denkfehler ist.
Auch wenn die eigentliche Formel schon "abartig" lang ist, breche ich mein Problem mal auf den kleinsten Nenner runter und hänge nur ein Minimalbeispiel an.

Problem:
In Zelle B1 steht der Name des Tabellenblattes und a1 ist die Zielzelle auf diesem Blatt. Das funktioniert, es wird der Inhalt von Zelle a1 ausgegeben.

Nun habe ich aber nicht einfach B1 mit dem Namen des Tabellenblattes, sondern einen "definierten Namen" für die Zelle in der der Name des Tabellenblattes steht. Die Formel
gibt mir die korrekte Addresse aus --> B1

So und nun möchte ich das kombinieren:


nur funktioniert das irgendwie nicht. Die Kombination aus &"! will mir einfach nicht gelingen. Egal was ich versucht habe, ich habe entweder 2 x " hintereinander oder gar keins und somit funktioniert die Formal auch nicht. Selbst mit Verketten war nichts zu machen. Hat jemand einen Tip für mich? Anbei das Excel zum ausprobieren.

Danke schonmal!

Schöne Grüße
Mark

Hallo, was um Gottes willen hast du denn vor..?

Hi, moin. Was heißt das im Betreff... "maskieren von Sonderzeichen"..? Meinst du so..?

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C
1	tollesBlatt	Geschafft!

Name	Bezug
tollesBlatt	=Tabelle1!$B$1

Zelle	Formel
C1	=INDIREKT(B1&"!A1")

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Ok ich versuche mal zu beschreiben was ich eigentlich vor habe. Leichter verständlich wird es denke ich durch den Anhang. Also ich habe eine größere Tabelle mit Werten und die Spaltenüberschriften sind als einzelne Tabellenblätter vorhanden (identischer Name). In meiner Tabelle möchte ich nun Zeilenweise addieren, sofern auf den zugehörigen Tabellenblättern 2 Bedingungen erfüllt sind. Dafür sollte sich die Funktion "SUMMEWENNS" eignen. Der Summenbereich ist ja relativ leicht zu definieren (entsprechende Zeile), aber beim Kriterienbereich benötige ich INDIREKT um über den Spaltennamen der Tabelle auf die jeweiligen Blätter zu gelangen und dort meine Kriterien zu prüfen. Genau da hänge ich nun dran.

Hallo k., ich verstehe es (noch) nicht. Erstens: was maskieren von Sonderzeichen bedeutet, Zweitens: was bedeuten die Zahlen - wo kommen die her...?

Arbeitsblatt mit dem Namen 'Tabelle1'
	B	C	D	E	F	G
1	Bereichsanfang	Tabelle10	Tabelle20	Tabelle30	Tabelle40	Bereichsende
2
3
4
5		5	3	1	2
6		6	2	8	9
7		1	4	4	2

Name	Bezug
Bereichsanfang	=Tabelle1!$B$1
Bereichsende	=Tabelle1!$G$1

Verwendete Systemkomponenten: [Windows (32-bit) NT :.00] MS Excel 2013

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Und Drittens: Ich habe mir die Formeln angesehen, (da ich beim besten Willen - aber es ist schon früh - nicht nachvollziehen kann, was du vorhast) und hier ..:

=INDIREKT(ADRESSE(ZEILE();SPALTE(Bereichsanfang))):INDIREKT(ADRESSE(ZEILE();SPALTE(Bereichsende))) steht eigentlich =$B$21:$G$21 (in Zeile21 eingefügt) - was soll das..?

Hallo Jockel,

ok dann fange ich mal mit deinen Rückfragen an:
1. "maskieren von Sonderzeichen"
Wenn ich diese beiden Formeln verschachtele, muss ich sicherstellen das z.B. das &-Zeichen und das "-Zeichen nicht interpretiert werden sondern als "String" gesehen werden. Das meine ich mit "Maskieren".
- diese Formel liefert B1 als Ergebnis --> =ADRESSE(1;SPALTE(tollesBlatt);4)
- diese Formel funktioniert ebenso --> =INDIREKT(B1&"!a1")
- wenn ich aber die erste Formel stellvertretend für B1 in die zweite Formel einsetze funktioniert es nicht.

2. "was bedeuten die Zahlen"
Das sind in diesem Fall nur "Dummy"-Werte ohne weitere Bedeutung. Im echten Excel sind es Umsatzzahlen, jede Zeile entspricht einem Monat. Die Zeilen sollen entsprechend den Kriterien addiert werden, die auf den jeweiligen Blättern zu finden sind. Also Beispielsweise Zeile 5 addieren wenn die Bedingung "ja" und "wichtig" erfüllt ist. Ergebnis wäre --> 5. Denn nur auf Tabelle20 und Tabelle40 sind jeweil beide Kriterien erfüllt.

3. "was soll die Formel bezwecken?"
Die Formel =INDIREKT(ADRESSE(ZEILE();SPALTE(Bereichsanfang))):INDIREKT(ADRESSE(ZEILE();SPALTE(Bereichsende))) gibt einen Bereich relativ dazu aus wo ich sie einfüge. Wenn ich sie also neben die Tabelle in die gleiche Zeile setzte die ich addieren möchte, gibt sie mir den Bereich meiner Werte aus, also z.B. B5:G5. Damit habe ich dann den Summe_Bereich der SUMMEWENNS-Funktion bereits abgedeckt.

(20.03.2018, 01:31)kieselbert schrieb: [ -> ]Hallo,

...
- diese Formel liefert B1 als Ergebnis --> =ADRESSE(1;SPALTE(tollesBlatt);4)
- diese Formel funktioniert ebenso --> =INDIREKT(B1&"!a1")
- wenn ich aber die erste Formel stellvertretend für B1 in die zweite Formel einsetze funktioniert es nicht ...

Hallo, da ich nicht verstehe, warum du nicht verstehst, warum das nicht funktionieren kann - maskiere doch mal den Formelassitenten..!!! Ich bin für heute Nacht raus... Hast du mal den Formelassistenten bemüht..? Eventuell meinst du ja sowas..:


Aber das ist mir mehr als suspekt...

Hallo Mark,

ich bin mir nicht sicher ob das was du vorhast überhaupt möglich ist - vor allem weil SUMMEWENNS() eine andere Struktur benötigt.

Mache dir das leben leichter:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
14	Tabelle	Krit1	Krit2	Wert1	Wert2	Wert3
15	Tabelle10	ja	unwichtig	5	6	1
16	Tabelle20	ja	wichtig	3	2	4
17	Tabelle30	nein	wichtig	1	8	4
18	Tabelle40	ja	wichtig	2	9	2
19	Ergebnis			5	11	6

Zelle	Formel
D19	=SUMMEWENNS([Wert1];[Krit1];"ja";[Krit2];"wichtig")
E19	=SUMMEWENNS([Wert2];[Krit1];"ja";[Krit2];"wichtig")
F19	=SUMMEWENNS([Wert3];[Krit1];"ja";[Krit2];"wichtig")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Ok, ich habe nun herausgefunden das Excel keine Bereiche über mehrere Blätter bilden kann. Ich hatte das nur angenommen da man z.B. mit einer Funktion wie SUMME(A_all:E_all!A1) mal eben über alle Blätter hinweg eine Zelle aufsummieren kann. Einen Bereich aus lauter A1 Feldern über mehrere Blätter hinweg kann Excel schlichtweg nicht.

Ich habe es nun so gelöst, dass ich die Bedingungen der Einzelblätter in einer Zeile des Übersichtsblatts überführe und darüber dann meine SummeWenns-Funktion laufen lasse. Diesen Zwischenschritt kann ich scheinbar nicht vermeiden. Danke für eure Hilfe :19: . Ich geh dann mal zum nächsten Thema über.

Grüße
Mark

Hallo, wenn ich das jetzt so lese --> Funktion wie SUMME(A_all:E_all!A1) ist doch das hier genau das, was du beschreibst, was du suchst... Ohne maskieren von Sonderzeichen...

http://www.herber.de/excelformeln und bitte suchen .../formeln.html?welcher=206

--> eben über alle Blätter hinweg eine Zelle aufsummieren