Microsoft Excel

Herbers Excel/VBA-Archiv

Array-Formel mit Indirect

Betrifft: Array-Formel mit Indirect von: Ralph
Geschrieben am: 03.10.2014 01:35:15

Hallo,
ich habe 3 Dateien mit einem ähnlichen Bericht für 3 Filialen. Nun möchte ich einen Summenbericht aus den 3 Dateien machen.

Dabei dachte ich, es über eine Array-Formel zu machen, die sich aus einem Bereich die Dateinamen holt und dann mit INDIRECT sich die zusammenbaut.

Zum Testen wollte ich das mit Arbeitsblättern (statt Dateien) probieren:

Es gibt also 3 Arbeitsblätter DE01,DE02,DE03 wo in Zellen B2:B4 jeweils Zahlen stehen. Nun dachte ich, wenn H1:H3 DE01, DE02, DE03 enthalten, könnte ich dann so für z. B. für die _
Werte in B2 summieren:

{=SUMME(INDIREKT(H1:H3&"!B2"))} 
aber das klappt nicht. Ich habe auch
{=SUMME(INDIREKT("H"&ZEILE("1:3")&"!B2"))}
probiert, das klappte auch nicht.
Wo liegt mein Denkfehler?
Ich will eigentlich das

=INDIRECT($H$1&"!B2")+INDIRECT($H$2&"!B2")+INDIRECT($H$3&"!B2") 
nachbauen (sorry, mein excel ist Englisch) ...
Ich danke schon mal
Gruß
Ralph

  

Betrifft: N() von: {Boris}
Geschrieben am: 03.10.2014 07:41:25

Hi Ralph,

...ist die Zauberfunktion, die das indirekte Zahlenarray zum Leben erweckt.

=SUMMENPRODUKT(N(INDIREKT("'"&H1:H3&"'!B2")))

VG, Boris


  

Betrifft: Morrn, Boris; da hab' ich wohl doch zu lange... von: Luc:-?
Geschrieben am: 03.10.2014 08:35:24

…geschrieben! Hatte gerade heute keine FrühArbeiter vermutet… ;-)
Gruß, Luc :-?


  

Betrifft: Alles gut ;-) von: {Boris}
Geschrieben am: 03.10.2014 08:38:21

Moin Luc,

...Deine Ausführungen sind ja nun auch wesentlich umfangreicher. Da gibt`s für Ralph sicher noch ein paar Mehrwerte draus.

VG, Boris


  

Betrifft: oT mir stellt sich die Frage ... von: neopa C (paneo)
Geschrieben am: 03.10.2014 08:50:49

Hallo Luc,

... bist Du schon oder noch auf ;-)

Gruß Werner
.. , - ...


  

Betrifft: Noch Einer! Beides! ;-) Gruß owT von: Luc:-?
Geschrieben am: 03.10.2014 09:07:02

:-?


  

Betrifft: AW: N() von: Ralph
Geschrieben am: 06.10.2014 10:04:18

Danke auch Dir, jetzt muß ich das nur noch auf Dateien umbasteln, aber das dürfte ja kein Problem sein!


  

Betrifft: Das geht so mit Indirect nicht, ... von: Luc:-?
Geschrieben am: 03.10.2014 08:32:36

…Ralph,
und auch mit keiner anderen datenbereitstellenden xlFkt, denn außer INDIREKT kann keine Daten­felder über mehrere Blätter aufbauen, was wg der indirekten Angabe der Blatt­namen hier erforderlich wäre. So etwas können sonst nur etliche end­verarbeitende xlFktt wie zB SUMME (Sum) intern bei Ermittlung ihres Ergebnisses.
INDIRECT baut ein solches Datenfeld aber in nicht direkt darstell­barer Form auf, weshalb du eine HilfsFkt benötigst, die das bewerk­stelligt. Das leistet im Folgenden N:
Als MatrixFml: {=SUM(N(INDIRECT(H1:H3&"!B2")))}
oder normal: =SUMPRODUCT(N(INDIRECT(H1:H3&"!B2")))
Ansonsten kannst du natürlich auch direkt =SUM(DE01:DE03!B2) oder indirekt =SUM(INDIRECT(H1&"!B2");INDIRECT(H2&"!B2");INDIRECT(H3&"!B2")) schreiben oder neben Einsatz einer VBA-basierten UDF wie bspw diese: =TransFor("sum("&H1&":"&H3&"!B2)")
noch die alte XLM-Fkt AUSWERTEN (Evaluate) anwenden, die allerdings nur in benannten Fmln verwendet wdn kann und außerdem die Speicherung der Datei als .xlsm/b erfordert. In diesem Fall musst du einen Namen definieren, zB Sum3D, und in seinem Bezug =EVALUATE("sum("&H1&":"&H3&"!B2)") angeben. Dabei sollte im Blatt die Zelle ausgewählt sein, in die du anschließend =Sum3D einträgst.
Die XLM-Fkt reagiert aber nicht oW auf Änderungen, weshalb man ihr eine volatile Fkt wie bspw JETZT() (Now), die das tut, beigeben müsste: =1^NOW()*EVALUATE("summe("&H1&":"&H3&"!B2)")
(Gleiches gilt leider auch für die gezeigte UDF.)
Morrn, Luc :-?


  

Betrifft: AW: Das geht so mit Indirect nicht, ... von: Ewald
Geschrieben am: 03.10.2014 15:25:21

Hallo,

wenn ich das richtig verstanden habe,

schon mal so probiert

 EFG
16   
17Deko1Deko2Deko3
18   
19   
20350  
21   
22350  

ZelleFormel
E20=SUMME(Deko1;Deko2;Deko3)
E22=SUMME(INDIREKT(E17);INDIREKT(F17);INDIREKT(G17))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Deko 1-3 sind Bereichnamen auf drei Tabellen

Gruß Ewald


  

Betrifft: AW: Das geht so mit Indirect nicht, ... von: Ralph
Geschrieben am: 06.10.2014 17:39:21

Moin Ewald,
das Prinzip hatte ich kurz überlegt, es aber dann nicht angewendet. In echt geht es um mehr als 10 Filialen mit häufigen Änderungen, somit wird die Handeingabe zu aufwändig.
Gruß
Ralph


  

Betrifft: Alternativ zur Fkt N kannst du auch einen ... von: Luc:-?
Geschrieben am: 03.10.2014 20:05:16

…Namen definieren, Ralph,
und dem die INDIRECT-Formel zuordnen, wodurch du alle Werte (vertikal!) im Blatt darstellen kannst, wofür dann die Xl-Steuerung sorgt. Ohne N lässt sich aber so auch keine Summe direkt bilden (das betrifft nicht Ewalds Vorschlag, der hat mich nur zu dieser Ergänzung inspiriert!).
Mit =N(INDIRECT(H1:H3&"!B2")) gelingt diese Darstellung aber auch ohne Namen und damit unabhängig von der Xl-Steuerung für Zeilen/Spalten-Variation in mehrzelligen (Matrix-)Fmln. Das dürfte auch ein Grund dafür sein, dass so etwas mit INDEX nicht fktioniert, da es sich weder um variierende Zeilen/Spalten noch TeilBereiche von MehrfachAuswahlen (unzusammen­hängende Bereiche, die ebenfalls als Tensoren interpretiert wdn könnten) im „klassischen“ Sinne handelt, sondern um einen 3dTensor mit den Dimensionen [1:1,1:1,1:3] (insgesamt 3 Dimensionen L/B/T) handelt.
Interessant hierbei ist, dass die klassische FmlForm =INDIRECT(H1&":"&H3&"!B2") weder ohne noch mit N fktioniert, weil hier eben (in-)direkt versucht wird, einen 3dTensor zu bilden, was keine Daten­bereit­stellungs­Fkt in Xl kann. Die gezeigte fktionierende Form spricht dagg alle Einzelwerte separat an und setzt dann daraus ein irreguläres Datenfeld zusammen.
Die Fkt N ist ebenso wie ihr Pendant für Texte, T, eigentlich nicht primär für so etwas gedacht. Beide sind aber offensichtlich so pgmiert worden (bzw wdn in entsprd Umgebung so genutzt), dass sie irreguläre Datenfelder bedingt normieren können, sofern eine 2dimensionale, xl-gerechte Form möglich ist. Da aber in der sonst ggüber INDIRECT zu bevorzugenden Alternative INDEX die Variation als Matrix angegebener, aber skalar verlangter Argumente von der Xl-Steuerung extern besorgt wird, sind N&T hierbei machtlos, denn sie bekommen so ja nur jeweils einen Wert pro Zelle geliefert. INDIRECT liefert aber alle Werte auf 1×, nur nicht in auf Xl-Blätter abbildbarer (und somit auch nicht summier­barer) Form.
Man kann so etwas auch selber pgmieren, denn eine Normierungsfkt, die das für jede Art von Werten in Datenfeldern besorgt, fehlt in Xl. Schon daran kann man erkennen, dass das nicht der HptZweck von N&T ist, und natürlich daran, dass keine Matrix für ihr Argument im FmlAssi angezeigt wird (nur ihr 1.Element) und sie, auf einen normalen ZellBereich angewendet, auch stets nur das 1.Element liefern, also nicht aktiv matrixfml-fähig sind (nur passiv). Man kann sie deshalb in Analogie zur Chemie auch als eine Art Katalysator betrachten… ;-)
Luc :-?


  

Betrifft: AW: Alternativ zur Fkt N kannst du auch einen ... von: Ralph
Geschrieben am: 06.10.2014 10:01:36

Moin!
Tut mir leid, daß ich erst jetzt reagiere - ich hatte vom letzten Forum her erwartet, eine Nachricht zu bekommen, wenn geantwortet wird, aber entweder ich hatte das nicht gesetzt oder es passiert nicht.

Egal, die Ausführungen haben mir sehr geholfen und daß N() das so kann - sprich das "irreguläre" Feld aufbauen, daß ist mir neu. Danke für die Erkenntnis und die schnelle Hilfe.
Gruß
Ralph


  

Betrifft: Nee, das "irreguläre" Feld normieren! ;-) owT von: Luc:-?
Geschrieben am: 06.10.2014 18:24:45

:-?


  

Betrifft: AW: Nee, das "irreguläre" Feld normieren! ;-) owT von: Ralph
Geschrieben am: 06.10.2014 21:29:05

Moin!
Jetzt habe ich versucht, das auf den Fall mit Dateien umzubauen - er steigt mir aber schon mit Bezugsfehler (#REF!) aus, wenn ich versuche,

{=SUM(N(INDIRECT("'V:\Summieren_über_Dateien\["&H2:H4&".xlsx]Daten'!$B$2")))}
wobei in H2:H4 die Filialen stehen. Die Dateien sind Sammel.xlsx, wo die Formel drin steht und DE01.xlsx,DE05.xlsx,DE06.xlsx im gleichen Verzeichnis. H2:H4 enthalten "DE01", "DE05", "DE06".

Habe ich etwas falsch zusammengebastelt?

Danke im Voraus


  

Betrifft: Sicher nicht, aber das ist ohnehin ohne ... von: Luc:-?
Geschrieben am: 07.10.2014 01:33:24

…Relevanz, falls die anderen Dateien geschlossen sind, Ralph,
denn INDIRECT kann nicht auf geschlossene Dateien zugreifen und mit der XLM-Fkt EVALUATE ist das ebenso. Gleiches gilt für jede (selbstgeschriebene) UDF, die in einer ZellFml angewendet wdn soll. Falls man diese die beteiligten Dateien öffnen lassen will, gelingt das nicht, weil derartige Aktionen einer ZellFmlFkt verboten sind und deshalb in dieser Umgebung nicht ausgeführt wdn. Dazu müsste man in der UDF entweder eine WinAPI-Fkt bemühen (vorangestelltes Declare … für diese mit Angabe der zuständigen DLL) oder UDF und DateiÖffnen physisch entkoppeln (Archiv-RECHERCHE-Stichwort), wobei das (zumindest Letzteres) 1.heikel (nur für Einzelfälle geeignet!) und 2.kompliziert ist, weshalb es besser sein dürfte, hier gleich auf eine VBA-SubProzedur auszuweichen.
Bei geöffneten QuellDateien fktioniert die Fml aber prinzipiell!
Gruß, Luc :-?


  

Betrifft: AW: Sicher nicht, aber das ist ohnehin ohne ... von: Ralph
Geschrieben am: 07.10.2014 13:21:02

Danke, habe das Physisch entkoppeln gesucht und ich glaube, das ist dann für den vorliegenden Fall zu aufwändig. Wenn ich der einzige bin, der weiß, wie dieses Makro funktioniert, dann ist das eventuell nicht so stabil.

Wahrscheinlich ist es in diesem Fall einfacher, die Formel ein Mal einzutragen und dann per Makro die Formel entsprechend auf alle Filialen auszuweiten.

Danke noch mal, Luc


  

Betrifft: Bitte sehr, gern geschehen! Gruß owT von: Luc:-?
Geschrieben am: 07.10.2014 14:52:46

:-?


 

Beiträge aus den Excel-Beispielen zum Thema "Array-Formel mit Indirect"