Summenprodukt Problem

[clag]

Hi Leutz

in einer größeren Tabelle habe ich mit der Formel

Code: Alles auswählen

=SUMMENPRODUKT(Daten.$U$11:Daten.$U$65536=$B5;Daten.$W$11:Daten.$W$65536=C$4)

zwei Spalten nach Wochentagen "C$4" und Jahr "$B5" abgefragt, hat auch funktioniert
aber neuerdings nicht mehr.

Die Formel hat jetzt ein Problem mit den Jahreszahlen die als Text mit Hochkomma '2007 in den Zellen stehen und zeigt #NAME? an

auch ZÄHLENWENN funktioniert mit den Jahreszahlen nicht mit den Wochentagen aber sehr wohl?

[Karolus]

Hallo
Warum stellst du den Jahreszahlen Hochkommas voran?

SUMMENPRODUKT kann auch direkt aus einer Datumsspalte via ...JAHR(datumsspalte)=B$4;... gefüttert werden, desgleichen mit ...WOCHENTAG(datumsspalte)=wochentagsziffer...

Gruß Karo

[clag]

Hi Karolus,

die Frage warum das Hochkomma musste ich erst drüber nachdenken wieso

in der Tabelle enthält die Spalte F "Datum Uhrzeit"
in drei anderen Spalten wird | Jahr | Monat | Wochentag | angezeigt
mit jeweils =TEXT(F3544;"JJJJ") | =TEXT(F3544;"MMMM") | =TEXT(F3544;"TTTT")
um es für Diagramme auswerten zu können zB wie oft 2007 Montags oder so

das ist der Grund für das Text Jahr '2007

das hat auch sonst funktioniert,
habe aber schon einige Zeit nicht mehr ausgewertet und kann nun leider nicht nachvollziehen ob es mit einem update zu tun hat?

[Karolus]

Hallo
Wie ich bereits andeutete brauchst du die drei Hilfsspalten nicht unbedingt, die Summenproduktformel kannst du direkt auf die Datumsspalte F umschreiben:

Code: Alles auswählen

=SUMMENPRODUKT(JAHR(Daten.$F$11:$F$65536)=$B5;WOCHENTAG(Daten.$F$11:$F$65536)=C$4)

in B5 steht die 4.stellige Jahreszahl, in C4 eine Zahl von 1 bis 7 für Sonntag bis Samstag.

__
Es scheint so als ob das ganze vielleicht besser per -->Datenpilot zu realisieren wäre?

Gruß Karo

[clag]

Hi Leutz

ich stoß mal wieder an meine OOo Formelverstehgrenzen und brauche eure Hiiiilfe oder auch leichte Schläge auf den Hinterkopf

@Karolus
mit deiner Formel bekomme ich jetzt wieder Ergebnisse aus der Spalte Datum TT.MM.JJ HH:MM

Code: Alles auswählen

=SUMMENPRODUKT(JAHR(Daten.$F$11:$F$65536)=$B25;WOCHENTAG(Daten.$F$11:$F$65536)=C$23)

meine Versuch das Prinzip nun auch auf ZÄHLENWENN anwenden zu können geht schief

Code: Alles auswählen

=ZÄHLENWENN(JAHR(Daten.$F$11:$F$65536)=$B25)

= Err:511 (B25:B29 = Zahlen 2009-2005)
bzw

Code: Alles auswählen

=ZÄHLENWENN(WOCHENTAG(Daten.$F$11:$F$65536)=C$23)

= Err:511 (C23-I23 = Zahlen 1-7)

dagegen funktioniert das Zählen in den Text Spalte U =TEXT(F3544;"JJJJ")

Code: Alles auswählen

=ZÄHLENWENN(Daten.$U$11:Daten.$U$65536;$B5)

B5-B9 = Text '2009-'2005
und auch Text Spalte W =TEXT(F3544;"TTTT")

Code: Alles auswählen

=ZÄHLENWENN(Daten.$W$11:Daten.$W$65536;C$4)

C4-I4 = Text Montag- Sonntag

was mach oder versteh ich falsch ??

[Karolus]

Hallo
a) Zählenwenn hat die Syntax: ZÄHLENWENN(bereich;kriterium)
b) zählenwenn bietet keine Möglichkeit in 'bereich' eine Unterfunktion zu benutzen.
aber wenn du unbedingt mit Zählenwenn arbeiten willst -
c) Zählenwenn kann mit regulären Ausdrücken*** arbeiten (das kann SUMMENPRODUKT nicht)
z.B.

Code: Alles auswählen

ZÄHLENWENN(datumsspalte;".*2007$")

oder

Code: Alles auswählen

ZÄHLENWENN(datumsspalte;".*"&B4&"$")

***evtl. unter-->Extras->Optionen->OOocalc-->berechnen die Option [x]Reg-ex-erlauben !

Gruß Karo

[clag]

Hi Leutz

OOo311summenprodukt-fehler.png (17.3 KiB) 999 mal betrachtet

so sieht es zurzeit aus,
versteh ich nicht wirklich vor allem wieso die alte Formel nicht mehr funktioniert
kann dann nur noch an OOo3.1.1 RC1 liegen ?
habe leider nur das eine aktive

[Karolus]

Hallo
Die Formel in J25 hat die falsche Syntax (siehe letzte Antwort),
Was bei der "alten" Summenproduktformel schiefläuft kann ich aus dem Snapshot nicht erkennen, schau doch mal im Tabellenblatt 'Daten' in die Spalten U und W -- wird 'err 508' von da geerbt?

Gruß karo

[clag]

Hallo Karolus
erst einmal Danke für deine Geduld

habe jetzt diese Formel von dir angewendet =ZÄHLENWENN(datumsspalte;".*"&B4&"$")
meintest du mit ZÄHLENWENN(datumsspalte;".*"&B4&"$") die Spalte F mit TT.MM.JJ HH:MM
oder die Spalte U =TEXT(F3544;"JJJJ")

mit Bezug auf Spalte F TT.MM.JJ HH:MM als Ziel funktioniert die Formel bei mir nicht,
egal ob nach Zahl 2007 oder Text '2007 gesucht wird

auf Spalte U =TEXT(F3544;"JJJJ") bezogen werden die Werte geliefert
und es ist aber ebenfalls ist es egal ob B4 Text oder Zahl ist.

=ZÄHLENWENN(datumsspalte;".*"&B4&"$") könntest du mir den Teil kurz erklären

das der Fehler geerbt wird kann doch eigentlich nicht sein,
denn deine neue Formel und die alte Formel greifen ja auf die gleichen Bereiche zu !?

in der Fußzeile wird bei den Zellen mit Err:508 (Fehler in der Klammerung) angezeigt ?

vielleicht ist es besser jetzt noch auf das endgültige OOo3.1.1 zu warten..

ein verwirrter clag

[Karolus]

Hallo
Entschuldige ich vergas den Zeitanteil in der Datumsspalte F, die Regex muss dann lauten: ".*"&B4&".*"
Der . steht sinngemäss für 'ein einzelnes beliebiges Zeichen' der * steht für 'das/die voranstehende Zeichen(gruppe) beliebig oft),
Zusammengenommen also: 'irgendein Zeichen beliebig oft hintereinander'

das der Fehler geerbt wird kann doch eigentlich nicht sein,
denn deine neue Formel und die alte Formel greifen ja auf die gleichen Bereiche zu !?

Die neue Formel greift direkt auf die Datums/zeitspalte zu, die alte greift auf die Hilfsspalten U und W zu, deren Inhalte du mal überprüfen solltest.

Gruß Karo

[clag]

Hi Karolus

heute hast du's aber auch schwer mit mir, und ich erst mit mir

habe jetzt die Formel abgewandelt ".*"&B4&".*"

jetzt bringt sie Werte aus der Spalte F aber etwas mehr wie erwartet
auf Spalt U bezogen die erwarteten Werte

was könnte der Grund für die Differenz sein ?

OOo311summenprodukt-fehler-2a.png (17.1 KiB) 944 mal betrachtet

(für heute muss ich mich vom Thema trennen)

[balu]

Hallo clag,

es ist nicht grad leicht da durchzublicken.

1.)
Warum setzt Du den Datenbereich in der ZÄHLENWENN in Klammern?
(Daten.$F$11:$F$65536)
Das ist flüßiger als Wasser, nämlich Überflüßig. Also weg damit, die Klammern!

2.)
Warum nimmst Du einen so großen Datenbereich in der ZÄHLENWENN?
Bis zur Zeile 65536. Muss das wirklich sein? Bist Du dir da ganz sicher das das so sein muss?
Es kann nämlich durchaus sein, dass in den anderen Zeilen, ie Du nicht sofort siehst, Werte drin stehen die ZÄHLENWENN mit aufaddiert. Und schon hast Du das Dilemma.

Also auf den Bereich reduzieren den Du wirklich brauchst. Und achte darauf das in der Spalte nix anderes drin steht, was nicht dazugehört.

das hat auch sonst funktioniert,
habe aber schon einige Zeit nicht mehr ausgewertet und kann nun leider nicht nachvollziehen ob es mit einem update zu tun hat?

Unter welcher OOo-Version hattest Du die Datei erstellt. Oder war das vielleicht sogar unter Excel gewesen? Was ich doch vermute.

in der Tabelle enthält die Spalte F "Datum Uhrzeit"
in drei anderen Spalten wird | Jahr | Monat | Wochentag | angezeigt
mit jeweils =TEXT(F3544;"JJJJ") | =TEXT(F3544;"MMMM") | =TEXT(F3544;"TTTT")
um es für Diagramme auswerten zu können zB wie oft 2007 Montags oder so

Warum so umständlich?
Wenn Du wirklich eine Datum- und Zeitkombination in einer Zelle hast, also nach dem Muster TT.MM.JJ HH:MM, dann ist es doch viel kürzer und einfacher das mit JAHR(), MONAT() und TAG() zu realisieren. Und außerdem braucht dann auch nix mehr mit WERT() umgewandelt zu werden. Beispiel:

Code: Alles auswählen

=JAHR(F3544) =MONAT(F3544) =TAG(F3544)

Vorausgesetzt, dass das Datum nicht mit einem Hochkomma versehen ist.

Ich hab da mal eben in ein leeres Blatt in A2 =JETZT() eingegeben. Und das Ergebnis in dieser Zelle war:
18.08.09 15:15
Dann hab ich die drei eben genannten Funktionen nebeneinander in seperaten Zellen eingegeben, und dabei bekam ich das raus:
2009 8 18

Und mit STUNDE() und MINUTE() könnte man dann sogar noch die Zeit aufgesplittet rausholen.

Wenn Du aber den Monat und den Wochentag ausgeschrieben als Textform "sehen" willst, dann setzt Du folgende Formel ein

Code: Alles auswählen

=DATUM(JAHR(F3544);MONAT(F3544);TAG(F3544))

und formatierst die dementsprechenden Zellen mi dem Format-Code MMMM, beziehungsweise TTTT.

Vorteile.
Einfacheres suchen, und auch die SUMMENPRODUKT() kann dann wieder gefahrlos eingesetzt werden, so wie schon vorgeschlagen wurde.


Machs gut, bis morgen
Gruß
balu

[clag]

Hallo Leutz

das Ursprungsproblem ist gelöst und damit auch die daraus resultierenden,

und Karolus hatte recht es war ein geerbtes Problem, sogar im doppelten Sinn,
angefangen hat mit dem update von 3.0x auf 3.10 und der daraus entstandenen CALC Unfähigkeit Tabellen zu sortieren mit Zellen die Verknüpfungen enthalten !
bei meinen dann folgenden Reparaturversuchen sind ein paar der produzierten Fehler durchgerutscht und haben dann die Berechnung für die Diagrammwerte mit der Summenprodukt Formel blockiert, was mir aber erst ein paar Wochen später aufgefallen ist
nachdem ich gestern und heute nochmal gut 15.000 Datensätze nacheinander überprüft hatte habe ich die letzten Fehler beseitigt und jetzt liefern auch alle Formeln sinnige Werte
das war aber ein echter Schei.dreck Job ........ darauf hätte ich gut verzichten können.

nun ja, so sind ein paar neue Formelvarianten bei herausgekommen

Hi balu
mit deiner verkürzten Variante für Jahr Monat Wochentag klemmt irgend wie
=JAHR(F5); =MONAT(F5); =TAG(F5) dann werden die richtigen Zahlen geliefert (JETZT) 2009 8 19
aber wenn ich dann die Zelle mit MMMM bzw TTTT formatiere wird 8 zu Januar 19 zu Donnerstag müsste aber August und Mittwoch werden ??

die restlichen Formeln habe ich natürlich von den Klammern befreit, und sie funktionieren trotzdem immer noch

ansonsten mal wieder ein FETTES DANKE an die, AufdenHinterkopfschläger, Helfer und Ideengeber