Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 21:51:33
Idee für Grafik
26.04.2024 21:29:07
PQ zufällige Reihenfolge
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1796to1800
1796to1800
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Funktion INDIREKT für berechnete Namen

Funktion INDIREKT für berechnete Namen
14.12.2020 09:35:51
JG
Hallo zusammen,
die Funktion INDIREKT liefert den korrekten Wert eines definierten Namensfeldes, das sich auf eine Zelle bezieht, z.B. =INDIREKT("Kauf" & B5) ergibt 10.000,00 €, wenn in B5 "Maschine1" steht und der Name KaufMaschine1 auf Zelle X5 referenziert, in der der Wert 10.000 steht (s. Datei, Tabellenblatt Korrekt).
Wenn der Name KaufMaschine1 aber auf 2 nicht zusammenhängende Zellen referenziert z.B. K5 und K20 [=$K$5; $K$20] oder auf die Summenformel hierzu [=SUMME($K$5; $K$20) oder =$K$5+$K$20], ergibt jeder Versuch mit INDIREKT einen Fehler (bei mir). Natürlich ergibt die Formel "=KaufMaschine1" den korrekten Wert von 11.000,00 € (in K20 steht 1.000); aber dann verliert die Abfrage ihre Variabilität.
Liegt der Fehler bei mir oder kann INDIREKT das nicht?
https://www.herber.de/bbs/user/142299.xlsx
Vielen Dang im Voraus
JG

23
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 11:46:29
onur
INDIREKT kann NUR mit STRINGS arbeiten, die zusammengenommen eionen gültigen BEREICH ergeben.
Siehe Microsoft:
"Bezug - Erforderlich. Der Bezug auf eine Zelle, die einen Bezug in der A1-Schreibweise, einen Bezug in der Z1S1-Schreibweise, einen definierten Namen als Bezug oder einen Zellbezug als Zeichenfolge enthält. Gibt "Bezug" einen unzulässigen Zellbezug an, gibt INDIREKT den Fehlerwert #BEZUG! zurück."
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 12:52:16
JG
Hallo onur,
das ist schade, dann muss ich mir was Anderes ausdenken.
Vielen Dank
JG
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 12:55:56
onur
Nimm doch 
WennFehler(Indirekt(......

Anzeige
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 14:23:20
JG
Hallo onur,
da fast alle dieser Namen "KaufMaschine…" auf nicht zusammenhängende Zellen referenzieren, tritt auch der Fehler fast immer auf; daher verstehe ich den Sinn/Vorteil von WennFehler nicht. Was sollte denn dann hinter dem Semikolon stehen?
Plan A:
Ich werde es zunächst mit Summenprodukt versuchen - der gesamte Bereich wird nur ca. 20 Spalten und 300 Zeilen umfassen, sodass ich keine Performanceprobleme erwarte.
Plan B:
Ich vergebe bei der Namensvergabe noch ein Suffix, also KaufMachine1_1,KaufMachine1_2 etc. und ermittle dann die Summe aller Namen, die mit KaufMaschine1 beginnen in einer eigenen, noch zu erstellenden Funktion.
Beste Grüße
JG
Anzeige
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 16:48:51
Herbert_Grom
Hallo,
bestehen die Bereichsnamen immer nur aus 2 Zellen?
Servus
AW: Funktion INDIREKT für berechnete Namen
14.12.2020 17:57:18
JG
Hallo Herbert,
das werden in der Regel mehr, ca. 4-6.
Was ist denn die Idee bei 2 Zellen?
Beste Grüße
JG
AW: Funktion INDIREKT für berechnete Namen
15.12.2020 09:22:20
Herbert_Grom
Hallo,
schau dir mal meinen Vorschlag an. In B5 steht der variable Teil des Bereichsnamens. In A5 kommt das Ergebnis, nachdem du auf die SF geklickt hast. Zur Kontrolle habe ich es in K23 auch noch mal manuell summiert.
https://www.herber.de/bbs/user/142327.xlsm
Servus
INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 03:33:14
Luc:?
…(vgl Onurs AW) als bspw INDEX, JG;
INDEX kann wie viele Ur-Xl-Fktt auch diskontinuierliche Bereiche verarbeiten (in Extra-Klammern), INDIREKT wie erst später hinzugefügte Fktt (deshalb wohl auch kein Zugriff auf geschlossene Dateien) aber nicht. Dazu benötigst du bspw zusätzlich eine benannte Fml mit der alten XLM-Fkt AUSWERTEN:
ZellFml: =KaufsummeMaschine1
benFml KaufsummeMaschine1: =AUSWERTEN("summe(KaufMaschine1)")
Die Verwendung einer alten Xl4-MakroFkt erfordert allerdings das automatische Beifügen der entsprd Module des alten MakroCodes, weshalb die Datei dann zwingend als .xlsm oder .xlsb gespeichert wdn muss.
Morhn, Luc :-?
„Die universelle Befähigung zur Unfähigkeit macht jede menschliche Leistung zu einem unglaublichen Wunder.“ Stapps ironisches Paradoxon
Anzeige
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 09:22:49
JG
Moin Luc,
das wäre eine Möglichkeit, aber …
-- Die Variabilität einer Formel mit Copy&Paste in Excel-Zellen geht auch verloren,
da die Formel AUSWERTEN nicht in einer Zelle sondern im Namen anzupassen wäre
-- Vermutlich müssten alle User der Datei auf ihren Rechnern Excel4-Makros zulassen
Vielen Dank
JG
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 09:45:57
Daniel
Hi
Das Hauptproblem beim Auswerten ist, dass du die Zellbezüge und Parameter im Namen selbst festlegen muss.
Man kann zwar im Namen auch mit relativen Zellbezügen arbeiten, so dass für alle Zellen, zwischen denen sich die Formel mit Copy-Paste über tragen ließe, der selbe Name verwendet werden kann, aber sobald es eine Variation gibt, brauchst du einen neuen Namen.
Allerdings sehe für das Problem mit Namen, die einen Bzug auf zwei oder mehr getrennte Zellen enthalten, keinen anderen Weg als über Auswerten
Das Problem mit Namen, die nicht auf einen Zellbereich referenzieren sondern einen Wert als Ergebnis haben, kannst mit einer UDF lösen:
Funktion Indirekt2(txt AS String)
Application.Volatile
Indirekt2 = Evaluate(txt)
End Function
Das funktioniert wie das normale Indirekt und kann zusätzlich Namen mit Wert als Ergebnis ausgeben.
Bei Namen mit Bezug auf mehrere getrennte Zellen versagt es aber und gibt nur die erste Zelle zurück.
Gruß Daniel
Anzeige
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 12:39:56
JG
Hallo Daniel,
deine Function ist die Lösung.
"Bei Namen mit Bezug auf mehrere getrennte Zellen versagt es aber und gibt nur die erste Zelle zurück." → Das stimmt zwar (s. neue Datei KaufMaschine8), aber wenn ich die getrennten Zellen
nicht nur mit ";" aufzähle, sondern mit einem Operator (hier "+") verbinde bzw.
in einer Funktion (hier "Summe") funktioniert alles.
Vielen Dank‼
JG
Ups: Upload-Fehler → Datei kommt noch
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 13:51:11
Herbert_Grom
Hallo,
hast du dir meinen Vorschlag schon mal angesehen?
Servus
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 15:58:28
JG
Hallo Herbert,
hat etwas länger gedauert, alles zu verstehen und geringfügig anzupassen.
Eine hervorragende Lösung!!
Vielen Dank
JG
Ich lade die Datei mit meiner Anpassung nochmals hoch (ich bin allerdings heute nicht mehr online)
Anzeige
AW: INDIREKT definiert 'gültiger Bezug' anders ...
15.12.2020 16:13:57
Herbert_Grom
Hallo,
Ich lade die Datei mit meiner Anpassung nochmals hoch
Prima, ich bin schon gespannt! Das war eine ganz schön knifflige Aufgabe!
Servus
AW: INDIREKT definiert 'gültiger Bezug' anders ...
16.12.2020 08:42:52
JG
Servus Herbert,
ich hatte deine leicht angepasste Datei schon zweimal hochgeladen, kann sie aber in der Beiträgen nicht finden, obwohl der Herber-Server eine Nummer vergeben hatte. Daher zum Dritten ....
https://www.herber.de/bbs/user/142359.xlsm
Ist im ersten Step nur eine übergeordnete Schleife dazugekommen, damit das Ganze für alle Maschinen ermittelt wird; dadurch änderten sich natürlich auch Einlese- u. Ausgabe-Adresse.
Beste Grüße
Jürgen
Anzeige
AW: INDIREKT definiert 'gültiger Bezug' anders ...
16.12.2020 08:58:10
Herbert_Grom
Hallo Jürgen,
prima. So etwas habe ich auch noch nie gemacht. Da bin ich schon ein klein wenig Stolz darauf, wie ich das zusammengeschustert habe, mit den Zellangaben aus dem Namensstring und mit diesen dann die Beträge der einzelnen Adressen summiert. Aber so lernt man, auch nach 26 Jahren mit Excel, immer wieder noch etwas dazu und das macht das Ganze so spannend und jeden Tag wieder interessant!
Servus
AW: INDIREKT definiert 'gültiger Bezug' anders ...
16.12.2020 08:41:42
Daniel
naja wenn du das "+" dazwischen schreibst, ist das Ergebnis des Namens ja kein Zellbezug mehr, sondern eine einfache Zahl, für die das Evaluate funktioniert.
Problematisch wird's, wenn das Ergebnis des Namens ein Bezug sein soll, der auf mehrere nicht zusammenhängende Zellen verweist.
Gruß Daniel
Anzeige
Welche Variabilität soll da verlorengehen, ...
16.12.2020 01:53:07
Luc:?
…JG?
Die deinen Angaben zugrunde liegende Fml ist doch nur benannt worden, wobei auch noch deine Namen, auch die für die diskontinuierlichen Bereiche, verwendet wurden. Würdest du relative Adressen verwenden, würden die Ergebnisse auch an den Standort der ZellFml angepasst wdn. Gib es zu, du hast wenig Erfahrung mit definierten Namen, höchstens mit benannten Bereichen, aber weder mit benannten Fmln noch ebensolchen Konstanten, die es auch noch gibt!
Das Problem mit der Speicherung als .xlsm/b hast du auch mit Daniels UDF, es sei denn, du steckst sie in ein xlVBA-AddIn, aber über das muss dann auch jeder Nutzer verfügen. Und bei Herberts Lösung ist es dasselbe.
Ich wollte dir zeigen, wie es auch ohne VBA, aber mit deiner Vorarbeit fktionieren kann. Denn UDFs zur Anwendung in einer ZellFml hätte ich dir auch anbieten können, von trivial bis komplex. Aber das war die einfachste Lösung.
Nebenbei, Daniel verwendet ebenfalls die vbFkt Evaluate, die US-Original-Notation ihres FmlTextArguments verlangt. Das musst du beachten, wenn du bspw beliebige ganze Fktsnotationen auswerten willst.
Luc :-?
Anzeige
AW: Welche Variabilität soll da verlorengehen, ...
16.12.2020 08:24:17
JG
Hallo Luc,
da liegst du richtig, dass ich vorher noch nicht mit benannten Fmln gearbeitet habe; deshalb bin ich auch in diesem Forum angekommen.
Die Variabilität liegt für mich darin, dass ich in in jeder Zeile einer Ausgabe-Tabelle eine einheitliche Formel durch Copy&Paste habe, die wie im Beispiel die aktuellen Anschaffungskosten der ausgewählten Maschine anzeigt bzw. damit weiterrechnen kann. Das Problem liegt sicherlich eher in der ungünstig angelegten Erfassungstabelle, deren Zeilen einfach chronologisch fortgeschrieben werden (aber man kann nicht alles ändern), sodass eben die für Maschine1 relevanten Daten nicht in einem zusammenhängenden Bereich stehen, sondern z.B. in den Zeilen 1, 45 und 280.
Beste Grüße
Jürgen
Anzeige
AW: Welche Variabilität soll da verlorengehen, ...
16.12.2020 08:43:14
Herbert_Grom
Hallo Jürgen,
vergiss bitte nicht, deine geänderte Datei hoch zu laden! Falls du sie nicht breit veröffentlichen willst, nimm meine eMail-Adr. hier aus den Profilen.
Servus
AW: Welche Variabilität soll da verlorengehen, ...
19.12.2020 11:26:57
Herbert_Grom
Hallo Jürgen,
du wolltest doch die geänderte Datei hochladen! Mach es doch bitte noch!
Servus
AW: Welche Variabilität soll da verlorengehen, ...
19.12.2020 12:34:43
JG
Servus Herbert,
das hatte ich gemacht (steht in meiner Antwort v. 16.12.2020 08:42:52) mit folgender Speicheradresse
https://www.herber.de/bbs/user/142359.xlsm
Wie schon gesagt, ich habe da nur noch eine "übergeordnete Schleife" eingebaut(hier nur mit 2 Durchläufen) samt Anpassung der Ein- u. Ausgabezelle. Auf diese Weise kann dann das Makro die Ergebnisse für alle Maschinen ausgeben.
Servus
Jürgen
AW: Welche Variabilität soll da verlorengehen, ...
19.12.2020 12:54:14
Herbert_Grom
Hi Jürgen,
sorry, aber ich glaube ich werde jetzt doch alt, resp. bin ich ja schon (70)! ;o)=)
Du hast natürlich recht, aber das hatte ich schon wieder vergessen!
Servus

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige