Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 23:30:16
Anzahl Jahre Zählen aus Datumreihe
16.04.2024 19:22:16
Von exceltabelle in word per steuerelement temarker
16.04.2024 18:50:53
Fehlermeldung bei Code - Excel speichern mit Bez. aus Zelle
16.04.2024 17:18:01
Projektplan-Tabelle oder Gantt ohne Balken
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1652to1656
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
1652to1656
1652to1656
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Problem mit Formel

Problem mit Formel
28.10.2018 08:33:54
Peter
Guten Morgen ihr Excelspezialisten,
könnt ihr mir bitte bei der Umsetzung der Formel behilflich sein.
Zelle K2==SUMMENPRODUKT((TEXT(Tabelle5!$A$2:$A$100;"MMMM")="Januar")*(TEXT(Tabelle5!$A$2:$A$100;"JJJ")="2009")*(Tabelle5!$B$2:$B$100))
Nun möchte ich gerne, dass das Jahr "2009" durch Zelle F2 aus Tabelle5 ersetzt wird und der Monat "Januar" durch die Zelle G2 aus Tabelle5.
Gleichzeitig hätte ich gerne die Bitte dies mittels VBA zu erstellen.
Besten Dank für eure Hilfe.
Gruss
Peter

31
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Problem mit Formel
28.10.2018 08:41:29
Hajo_Zi
Deine Formel Vergleicht Text, nicht die Zahl 2009!
F2 ist also Text ersetze "2009" durch F2 und "Januar" durch G2.
nur wenige schauen auf Deinen Rechner und sehen die Datei.
Ich möchte gerne den Fehler im Original sehen.
Ich baue keine Datei nach. Die Zeit hat schon jemand investiert.
Ein Nachbau sieht meist anders aus als das Original. Darum sollte das Original verlinkt werden.
Wenn du an Stelle einer Demomappe deine Originalmappe hochladen willst, diese aber sensible Daten enthält, kannst du diese Daten anonymisieren bzw. pseudonymisieren.
Benutze hier im Forum die Funktion zum hochladen. Falls Du die nicht benutzen möchtest beachte, von unsicheren Servern wie z.B. www.file-upload.net lade ich keine Datei runter. (lt. Einschätzung meines Virenprogramms)

Beiträge von Werner, Luc, robert, J.O.Maximo und folgende lese ich nicht.
Die Beiträge werden auch ignoriert, es erfolgt keine Antwort.
Anzeige
AW: Problem mit Formel
28.10.2018 09:04:20
Hajo_Zi
Hallo Peter,
ich kann Dir Leider nicht Helfen, was wohl daran liegt das ich die Datei auf dem Desktop nicht habe. Vielleicht ist Lupo seine Lösung die Lösung.

AW: Problem mit Formel
28.10.2018 09:06:45
Peter
Hallo Hajo,
habe die Datei doch hochgeladen.
Warum hast Du diese nicht?
Die Lösung von Lupo ergibt die Anzahl nicht die Summe.
Gruss
Peter
AW: Problem mit Formel
28.10.2018 09:12:14
Peter
Hallo Hajo,
ich habe jetzt die Formel:
=SUMMENPRODUKT((TEXT([124605_Zeilenumbruch_05.xlsm]Tabelle5!$A$2:$A$100;"MMMM")=E2) *(JAHR([124605_Zeilenumbruch_05.xlsm]Tabelle5!$A$2:$A$100)=D9) *([124605_Zeilenumbruch_05.xlsm]Tabelle5!$B$2:$B$100))
Kannst Du mir diese Bitte als VBA umwandeln?
Besten Dank
Gruss
Peter
Anzeige
AW: Problem mit Formel
28.10.2018 09:35:09
Hajo_Zi
Halo Peter,
das kannst Du selber sehr einfach,
Register, Einfügen, Internationale Makrovorlage und die Formel in diese kopieren.
Gruß Hajo
AW: Problem mit Formel
28.10.2018 10:02:04
Peter
Hallo Hajo,
besten Dank für Deine Hilfe.
Gruss
Peter
AW: Problem mit Formel
28.10.2018 09:41:13
Werner
Hallo Peter,
dann einfach die Formel per VBA in die Zelle schreiben: 
Range("K2").FormulaLocal = _
"=SUMMENPRODUKT((TEXT([124605_Zeilenumbruch_05.xlsm]Tabelle5!$A$2:$A$100;""MMMM"")=E2)" _
& "*(JAHR([124605_Zeilenumbruch_05.xlsm]Tabelle5!$A$2:$A$100)=D9)" _
& "*([124605_Zeilenumbruch_05.xlsm]Tabelle5!$B$2:$B$100))"
'und wenn die Formel dann durch den Wert ersetzt werden soll
Range("K2").Value = Range("K2").Value
Gruß Werner
Anzeige
AW: Problem mit Formel
28.10.2018 10:14:19
Peter
Hallo Werner,
besten Dank für Deine Hilfe.
Ich habe eine Datei hochgeladen. Wie Du hier erkennen kannst, habe ich eine Tabelle, die Jahre von 2001 bis 2050 in Spalte G aufgelistet und die Monate hierzu in den Spalten H bis S.
Ich möchte eigentlich nicht manuell für jedes Jahr und jeden Monat die Formel ändern und eintragen.
Gibt es Variable für den Ersatz der Bezüge E2 bzw. D9?
Ich hoffe Du verstehst wie ich das meine.
Die betreffende Datei: https://www.herber.de/bbs/user/124956.xlsm
Besten Dank
Gruss
Peter
Anzeige
AW: Problem mit Formel
28.10.2018 10:18:23
Werner
Hallo Peter,
kann mir die Datei Momentan nicht herunterladen, da ich hier keine .xlsm herunterladen kann. Stell die Datei doch nochmal ohne Makros, als normale .xlsx ein.
Gruß Werner
AW: Problem mit Formel
28.10.2018 11:27:01
Daniel
Hi
du musst in den Bezügen auf Jahr und Monat mit absoluten und relativen Zellbezügen richtig arbeiten.
mit dem Monatsbezug H$1 und dem Jahrsbezug $G2 kannst du die Formel in die Zelle H2 schreiben und von dort in die anderen Zellen kopieren.
im Code kann man die Formel in alle Zellen gleichzeitg schreiben:
Range("H2:S51"):FormulaLocoal = "=SUMMENPRODUKT((TEXT('C:\Users\Peter\Desktop\TestKonto 2018_10_27\[124605_Zeilenumbruch_05.xlsm]Tabelle5'!$A$2:$A$100;""MMMM"")=H$1)*(TEXT('C:\Users\Peter\Desktop\TestKonto 2018_10_27\[124605_Zeilenumbruch_05.xlsm]Tabelle5'!$A$2:$A$100;""JJJ"")=$G2)*('C:\Users\Peter\Desktop\TestKonto 2018_10_27\[124605_Zeilenumbruch_05.xlsm]Tabelle5'!$B$2:$B$100))"
die Formle ist jetzt passend für deine Beipieldatei.
wobei du dir auch nochmal den Vorschlag von Lupo anschauen solltet, denn seine Lösung kombniniert die Prüfung auf Monat und Jahr in eine Prüfung und damit wird die Formel etwas einfacher.
Gruß Daniel
Anzeige
AW: Problem mit Formel
28.10.2018 11:48:09
Werner
Hallo Peter,
und hier mal als Makro.
Wozu du allerdings die Monate in Spalte E schreibst ist mir unklar. Schmeiß die Monate in Spalte E mal raus. Das Jahre die du auswerten willst wählst du aus, indem du in Spalte E ein X neben dem Jahr setzt. 
Public Sub sss()
Dim loLetzte As Long
Application.ScreenUpdating = False
With Worksheets("Tabelle1")
If WorksheetFunction.CountIf(.Columns(5), "x") > 0 Then
loLetzte = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range(Cells(1, 5), .Cells(loLetzte, 19)).AutoFilter field:=1, Criteria1:="x"
With .AutoFilter.Range.Offset(1, 3).Resize(.AutoFilter.Range.Rows.Count - 1, _
.AutoFilter.Range.Columns.Count - 3).SpecialCells(xlCellTypeVisible)
.FormulaLocal = _
"=SUMMENPRODUKT((TEXT([124605_Zeilenumbruch_05.xlsm]Tabelle5!$A$2:$A$100" _
& ";""MMMM"")=H$1)*(JAHR([124605_Zeilenumbruch_05.xlsm]Tabelle5!" _
& "$A$2:$A$100)=$G2)*([124605_Zeilenumbruch_05.xlsm]Tabelle5!$B$2:$B$100))"
'wenn Formeln durch die Werte ersetzt werden sollen
'.Value = .Value
End With
.AutoFilterMode = False
Else
MsgBox "Es ist kein Jahr ausgewählt."
End If
End With
End Sub
Gruß Werner
Anzeige
AW: Problem mit Formel
28.10.2018 12:14:27
Peter
Hallo Werner,
besten Dank für das Makro. Leider geht es nicht wie gewünscht.
Wenn ich neben das Jahr 2008 "x" einfüge, werden in der gleichen Zeile in H9 folgend bis S9 die Formel eingefügt, jedoch ist die Formel falsch. Für das Jahr 2008 wird $G2 eingefügt statt $G9.
Das gleiche bei 2011 "x" jedoch $G5 statt G12.
Leider finde ich nicht, was geändert werden muss.
Gruss
Peter
AW: Problem mit Formel
28.10.2018 14:06:11
Daniel
HI
formuliere die Formel in Z1S1-Schreibweise.
dann ist die Formel immer gleich, egal in welche Zeile du die Formel schreibst.
die englische Version der Z1S1-Schreibweise zeichnet übrigens der Recorder auf, dh du kannst den vom Recorder aufzeichneten Code zum Einfügen der Formel in dein Makro übernehmen.
Gruß Daniel
Anzeige
AW: Problem mit Formel gelöst
28.10.2018 16:22:26
Peter
Hallo Werner,
der Code hat leider nicht so funktioniert wie ich dies benötige.
Habe aber Deinen Code mit meiner Formel abgeändert eingefügt und es funktioniert einwandfrei.
Sub sss3()
Dim loLetzte As Long
Application.ScreenUpdating = False
With Worksheets("Tabelle1")
If WorksheetFunction.CountIf(.Columns(5), "x") > 0 Then
loLetzte = .Cells(.Rows.Count, 5).End(xlUp).Row
.Range(Cells(1, 5), .Cells(loLetzte, 19)).AutoFilter field:=1, Criteria1:="x"
With .AutoFilter.Range.Offset(1, 3).Resize(.AutoFilter.Range.Rows.Count - 1, _
.AutoFilter.Range.Columns.Count - 3).SpecialCells(xlCellTypeVisible)
.FormulaR1C1 = _
"=SUMPRODUCT((TEXT(Tabelle1!R2C1:R100C1,""MMMM"")=R1C)*(YEAR(Tabelle1!R2C1:R100C1)=RC7)* _
(Tabelle1!R2C2:R100C2))"
'wenn Formeln durch die Werte ersetzt werden sollen
'.Value = .Value
End With
.AutoFilterMode = False
Else
MsgBox "Es ist kein Jahr ausgewählt."
End If
End With
End Sub

Das einzige was nicht geht warum auch immer das Makro wird in Entwicklertools - Makro zwar erkannt aber nicht ausführbar.
Besten Dank für Deine Hilfe.
Gruss
Peter
Anzeige
AW: Problem mit Formel gelöst
28.10.2018 16:38:32
Peter
Hallo Werner,
nochmals besten Dank. Habe Name von Makro geändert - jetzt funktioniert alles.
Gruss
Peter
Gerne u. Danke für die Rückmeldung. o.w.T.
28.10.2018 19:28:44
Werner
verkürzt
28.10.2018 08:58:56
lupo1
K2: =SUMMENPRODUKT(--(TEXT(Tabelle5!$A$2:$A$100;"JJJJMMMM")=F2&G2))
AW: verkürzt
28.10.2018 09:03:00
Peter
Hallo Lupo
das Ergebnis ist 3 = wohl Anzahl der Zellen nicht die Summe.
Gruss
Peter
Du erkennst aber selbst, was ich vergessen habe.
28.10.2018 09:16:37
lupo1
Problem ohne Formel lösen mit...
28.10.2018 13:49:13
Frank
Hallo Peter, z. B. mit Power Query... das Ergebnis sähe so aus..:
Arbeitsblatt mit dem Namen 'Tabelle2'
 ABCDEFGHIJ
1JahrJanFebMrzAprMaiJunJulOktNov
22008121,1935,238,5581,053,1338,9992,68116,4548,56
32009121,1935,238,5581,053,1338,9992,68116,4548,56
Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Ist dynamisch...
Anzeige
AW: Problem ohne Formel lösen mit...
28.10.2018 14:09:04
Daniel
naja, wie das Ergebnis aussehen soll, wissen wir eigentlich.
interessanter ist der Weg, wie man dahin kommt.
Gruß Daniel
...
28.10.2018 16:18:47
Frank
Hallo Daniel, ich lasse mich nicht mehr von dir provozieren. Mein Ergebnis zeige ich nicht ohne Grund, das hatten wir schon.
AW: Problem ohne Formel lösen mit...
28.10.2018 14:58:01
Peter
Hallo Frank,
liest sich intessant. Habe aber Power Query noch nicht gearbeitet.
Erkläre mir doch bitte den Weg. Wie Daniel geschrieben hat - das Ergebnis kennen wir - aber nicht den Weg dort hin.
Gruss
Peter
AW: Problem ohne Formel lösen mit...
28.10.2018 16:13:47
Frank
Hallo Peter, Power Query gibt es ab XL2010 bis XL2013 als kostenfreies Add-In - zum Herunterladen Wenn du Power Query benutzen darfst/kannst, dann klickst du in deine Datentabelle und machst daraus eine intelligente Tabelle (z. B. mit Strg und T). Danach auf Ribbon Power Query --> Aus Tabelle/Bereich dann öffnet sich ein neues Fenster -> das Bearbeitungsfenster für PQ. Folgende Punkte <b>sind nur Klicks</b> - bis auf eine Kleinigkeit, das Monatsformat, aber dazu mehr wenn es für dich wirklich nötig wäre - sonst werden die Monatsnamen ausgeschrieben). Der Text hier zu schreiben oder zu lesen dauert länger als die Lösung nachher umzu setzen... Also Weiter:
Das dort angezeigte Datum 29.07.2009 00:00 in ein (reines Datum umzuwandeln). Danach eine Spalte einfügen --> Spalte hinzufügen --> Datum -> Jahr. Neue Spalte hinzufügen --> Wieder Datum -> Monat -> Monatsname. Datumsspalte -> sortieren. Spalte Datum löschen! Jetzt benutzerdefinierte Spalte in Monat umbenennen. Wir sind gleich fertig..! Die Spalten so anordnen, dass die Reihenfolge so aussieht. Jahr; Monat; Betrag. Spalten Jahr und Monat markieren. Nun Gruppieren nach: --> Jahr und Monat Vorgang Summe Spalte Betrag. Und dann noch das Ergebnis Pivotieren. Fertig... Sollte ich die Datei zur Ansicht für dich Peter hochladen..? Es sieht schwieriger aus als es ist...
AW: Problem ohne Formel lösen mit...
28.10.2018 16:18:12
Peter
Hallo Frank,
besten Dank für Deine Information. Ich habe mir zwischenzeitlich ein Video zu diesem Thema angesehen.
So leicht wie Du dies beschreibst ist es auch wieder nicht.
Ich habe den Code von Werner überarbeitet. Dieser funktioniert jetzt einwandfrei.
Habe alle Formeln ordnungsgemäss in der Tabelle eingefügt. Die Daten werden automatisch aktualisiert bei Änderung.
Gruss
Peter
AW: Problem ohne Formel lösen mit...
28.10.2018 16:23:19
Frank
Nun ich kann nicht so gut beschreiben, aber es ist - wenn man Power anwenden darf/kann also es zur Verfügung steht super einfach - auch für den Einstieg, man kommt in der Zukunft eh nicht daran vorbei. Aber ich kenne natürlich die Zweifel. Und wenn dir eine andere Variante besser gefällt, alles easy. Es ist deine Anfrage. Ein gute Zeit noch.
AW: Problem ohne Formel lösen mit...
28.10.2018 16:26:06
Peter
Hallo Frank,
Du hast mich irgendwie neugierig gemacht.
Ist diese PowerQuery auf der CD?
Wo ist diese zu erhalten?
Gruss
Peter
http://lmgtfy.com/?q=power+query
28.10.2018 16:33:21
lupo1
AW: http://lmgtfy.com/?q=power+query
28.10.2018 16:36:39
Peter
Danke
Gruss
Peter

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige