Array/Matrix Formel aus Excel

[hlein]

Hallo,

Ich, Neuling in OpenOffice, habe in Excel folgendes:

Ein Tabellenblatt "FB_01" mit (auszugsweise) diesem Aufbau:
Spalte A enthält das Datum (von Zeile 5 bis 108)
Spalte H enthält zugehörige Werte.

In einer Zelle eines anderen Tabellenblatts (FB_01s) möchte ich dann die Summe für den Monat August. Dazu habe ich in Excel diese Formel (Eingabe mit Shift+Ctrl+Enter):

{=SUMME(WENN(MONAT(Dat01:INDIREKT(ADRESSE(endzeile01;SPALTE(Dat01);;FB_01)))=8;whgfakm01:INDIREKT(ADRESSE(endzeile01;SPALTE(whgfakm01);;FB_01));0))}

Die angesprochenen Variablen sind wie folgt definiert:
Dat01= $FB_01.$A$5
autodb01= $FB_01.$A$3:$S$108
endzeile01= ZEILE(autodb01)+ZEILEN(autodb01)-1
whgfakm01= $FB_01.$H$5


Das funktioniert in Excel ohne Probleme. In Calc bekomme ich da nur Err:502.

Ich habe schon einiges probiert, es aber nicht hinbekommen. Kann mir da wer helfen?

Vielen Dank
Helmut

[AhQ]

Hallo,

ich hab gerade nicht die Zeit, das genau anzusehen, aber wenn ich mich recht erinner, dann mag Calc nicht, wenn ein Parameter leer ist und danach kommt noch einer, so wie bei Deiner "ADRESSE(endzeile01;SPALTE(Dat01);;FB_01)"

Probier doch mal, ob es was bringt, als dritten Wert nen geeigneten Parameter zu setzen. Die OO-Hilfe meint bei ADRESSE dazu:

ADRESSE(Zeile; Spalte; ABS;Tabelle)
Zeile steht für die Zeilennummer der Zelle
Spalte steht für die Spaltennummer der Zelle (nicht den Buchstaben, sondern die Zahl)
ABS gibt die Art des Bezugs an:
1 oder leer: Absolut ($A$1)
2: Zeile absolut; Spalte relativ (A$1)
3: Zeile relativ; Spalte absolut ($A1)
4: Relativ (A1)
Tabelle steht für den Namen der Tabelle. Dieser muss in Anführungszeichen gesetzt werden.

Abgesehen davon, warum schreibst Du SPALTE(Dat01), wenn das auf eine absolute Adresse verweist? Geht das nicht einfacher mit einer 1 in dem Fall? Oder änderst Du gelegentlich Dat01 manuell?

Err. 502 heißt übrigens, daß irgendwas mit den Parametern nicht stimmt, aber das nur zu Deiner Information nebenbei.

Viele Grüße
AhQ

P.S. Und jetzt mal ganz grundsätzlich, geht das nicht mit Summenprodukt noch einfacher? Richt noch ne Hilfsspalte ein mit MONAT(SpalteMitDatum) und dann rechnets Du:

=Summenprodukt(Hilfsspalte=8;SpalteMitWerten)

[balu]

Hallo Helmut,

ergänzend zu dem was AhQ schon sagte, empfehle ich dir für ADRESSE in Excel ALLE 5 Parameter zu setzen, auch wenn Du dort das nicht brauchst. OOo-Calc kommt dann beim Importieren und konvertieren wegen fehlender Parameter nicht in's schleudern. Damit umgehst Du die Stolperfalle:
ADRESSE in Excel 5 Parameter ADRESSE in OOo-Calc 4 Parameter.

Hab da auch noch ne Frage.
Bist Du dir absolut sicher das deine Formel in Excel funktioniert?
Du hast da nämlich Doppelpunkte (:) drin, wo keine hingehören.
Oder ist das nur ein Dreckfehler?

Gruß
balu

[hlein]

Hallo AhQ,

Probier doch mal, ob es was bringt, als dritten Wert nen geeigneten Parameter zu setzen. Die OO-Hilfe meint bei ADRESSE dazu:

Das hat leider nichts gebracht.

Abgesehen davon, warum schreibst Du SPALTE(Dat01), wenn das auf eine absolute Adresse verweist? Geht das nicht einfacher mit einer 1 in dem Fall? Oder änderst Du gelegentlich Dat01 manuell?[

Einfacher wäre es. Aber ich habe lieber Namen, die mir etwas sagen.

[[uote]=Summenprodukt(Hilfsspalte=8;SpalteMitWerten)

Das sieht besser aus, obwohl ich Hilfsspalten (die zugegebenermaßen ausgeblendet werden können) eigentlich zu vermeiden suche.

Allerdings löst es das Problem noch nicht ganz, denn:
in Tabelle FB_01 ist eine "Datenbank", die sich ändert. D.h. es kommen Zeilen dazu, die natürlich in der Berechnung mit berücksichtigt werdedn sollen. Daher muss ich die Bereiche (SUMMENPRODUKT verlangt diese gleich groß) variabel definieren. Dazu habe ich die Variable endzeile01 die mit der Datenbank ihren Wert ändert (zumindest in den "aktuellen" Blättern für das laufende Jahr, wo autodbNN gekoppelt ist mit der Datenbank).

Wenn ich probiere, für den Bereich ADRESSE() einzuführen, kommt wieder der Fehler.

mfg
Helmut

[hlein]

Hallo balu,

Die Formel funktioniert in Excel seit 1994 in allen Versionen bis Office 2007.

Grüße
Helmut

[balu]

Hallo Helmut,

das deine Formel in Excel funktioniert bezweifle ich ja auch nicht. Nur wollte ich wissen ob da wirklich Doppelpunkte an den dementsprechenden stellen drin sind.

Jetzt wieder zum Thema ADRESSE.
Ich bin mir nicht sicher ob Du das richtig verstanden hast was ich in meiner ersten Antwort geschrieben habe. Deshalb will ich das jetzt noch mal etwas verdeutlichen.

In OOo-Calc werden maximal 4 (vier) Parameter für ADRESSE gesetzt.

In Excel werden jedoch maximal 5 (fünf) Parameter für ADRESSE gesetzt.

Wenn Du in Excel alle 5 Parameter für ADRESSE setzt, und diese Datei speicherst und anschließend in Calc öffnest, konvertiert Calc ADRESSE um.
Das bedeutet, das Calc dahergeht und den in Calc nicht benötigten 4. Parameter automatisch entfernt. Wenn Du aber diese Datei wieder im xls-Format speicherst, dann fügt Calc automtisch den 4. Parameter wieder ein. Und so kann dann Excel wieder mit der Datei arbeiten.

Wenn Du aber jedoch in Excel nicht alle 5 Parameter setzt, dann kann Calc beim öffnen der Datei ADRESSE nicht richtig konvertieren, oder nur fehlerhaft.

Mit anderen Worten:
Du musst in Excel den 3. Parameter auf 1 setzen, und den 4. Parameter auf WAHR.

Begründung:
Der 3. Parameter Liefert einen Bezug folgenden Typs: 1 oder nicht angegeben gleich Absolut adressierung.

Der 4. Parameter ist ein Wahrheitswert, der angibt, ob der jeweilige Bezug in der A1- oder der Z1S1-Schreibweise ausgegeben werden soll. Ist der 4. Parameter mit WAHR belegt oder nicht angegeben, liegt der von ADRESSE gelieferte Bezug in der A1-Schreibweise vor.

Ich hoffe das es jetzt verständlicher ist.


Wichtige Frage!!!!!!!!!!
Welche OOo Version hast Du?

Ich vermute nämlich das nicht nur ADRESSE, sondern auch INDIREKT problembehaftet ist.


Gruß
balu

[hlein]

Hallo balu,

das deine Formel in Excel funktioniert bezweifle ich ja auch nicht. Nur wollte ich wissen ob da wirklich Doppelpunkte an den dementsprechenden stellen drin sind.

Die Doppelpunkte gehören meiner Meinung auch hin, da es sich um Bereiche handelt (Zelle1:Zelle2), wobei "ZelleN" in einem Fall ein definierter Name ist und im anderen Fall aus INDIREKT und ADRESSE zusammengebastelt wird.

Du musst in Excel den 3. Parameter auf 1 setzen, und den 4. Parameter auf WAHR.

Hat leider auch nichts geändert.

Welche OOo Version hast Du?

3.0.0 Beta Build m14.


Grüße
Helmut

[AhQ]

Guten Morgen miteinander,

in Tabelle FB_01 ist eine "Datenbank", die sich ändert. D.h. es kommen Zeilen dazu, die natürlich in der Berechnung mit berücksichtigt werdedn sollen. Daher muss ich die Bereiche (SUMMENPRODUKT verlangt diese gleich groß) variabel definieren.

warum? Wenn Du einfach $FB_01.$A$2:$A$65000 nimmst und den Datumsbereich entsprechend ausdehnst? Summenprodukt sind leere Zellen doch ziemlich egal, wenn die die Bedingung nicht erfüllen, werden sie einfach ignoriert. Da kannst Du dann die volle Spaltenlänge eintragen, wenn Du der Meinung bist. Beim 3.0 sind das ja dann noch mehr.

Ich hab mal ein ganz einfaches Beispiel angehängt, in der der Bereich von Summenprodukt ein bißchen größer ist. Vielleicht verdeutlicht das dann, was ich meine.

Oder kannst Du Summenprodukt aus irgendwelchen sonstigen Gründen nicht nehmen?

Wie ist denn OOo 3.0? Ich bin schon sehr gespannt, will mir dann aber erst die richtige Version runterladen.

Viele Grüße
AhQ

@Balu: Ich wollt Dir eigentlich noch gestern noch schreiben, daß ich 3:38 Uhr für Deine erste Rückmeldung nach Deiner Abwesenheit ne ziemlich spaßige Zeit find. Andere Leute schlafen da...

[hlein]

Halo,

Wenn Du einfach $FB_01.$A$2:$A$65000 nimmst und den Datumsbereich entsprechend ausdehnst? Summenprodukt sind leere Zellen doch ziemlich egal, wenn die die Bedingung nicht erfüllen, werden sie einfach ignoriert.

Das werde ich wohl tun müssen.

@Balu: Ich wollt Dir eigentlich noch gestern noch schreiben, daß ich 3:38 Uhr für Deine erste Rückmeldung nach Deiner Abwesenheit ne ziemlich spaßige Zeit find. Andere Leute schlafen da...

Ich auch. Aber bei mir sehen Balu's Zeiten alle normal aus (Mittags bis Nachmittags) und ein :38 ist auch nicht dabei. Irgendwo eine Falsche Einstellung der Zeit/-zone ?

Grüße
Helmut

[balu]

Maaaaaaaaahlzeit

@Helmut,
die alternative von AhQ finde ich auch als sehr empfehlenswert. Getreu dem Motto : "Warum umständlich, wenn's auch einfach geht!?"
Und da Du ja gerne mit Namen arbeitest (genau so wie ich ), machst Du im Beispiel von AhQ folgendes:

Namen definieren:
Datum_01 = MONAT($A$2:$A$65000)
Wert_01 = B2:B65000

Und schon wird aus:
=SUMMENPRODUKT(MONAT($A$2:$A$65000)=8;B2:B65000)

die kürzeste und übersichtlichste Formel:
=SUMMENPRODUKT(Datum_01=8;Wert_01)



[OT]
Hey AhQ! Lebst Du hinter'm Mond
Um 3:38 Uhr sind sehr viele Leute am arbeiten, oder am baggern....(in der Disco ) Es schlafen halt nicht alle. Und ich hatte noch keinen Bock auf schlafen

Helmut Du brauchst dir keine gedanken machen, das hier was nicht stimmt Kommt auf den Betrachtungswinkel drauf an , AhQ hatte nur aus einem anderen Thread hier die Uhrzeit kundgetan.
[/OT]


Gruß
balu