Excel Wert nach Datum und Begriff übernehmen

Computer: Software & Programmierung Office Software
1

Hallo,

ich bräuchte etwas hilfe für eine Formel, die mich zum Verzweifeln bringt.
Ich habe 2 Tabellenblätter
Blatt 1 Auswertung
Blatt 2 Zusammenfassung

Blatt 1
Spalte A sind alle 16-20 Zeilen ein Datum
Spalte B Begriffe wie z.B. Wareneingang; Ausgang; Stunden usw.
Spalte C die Zahlen zu den einzelnen Begriffe und unterhalb eine Zusammenrechnung

also. z.B. A1-Datum, B5-Text(Warenausgang) C5-Wert

Blatt 2 - Zusammenfassung

Spalte A Datum fortlaufend jede Zeile
B1 - Wareneingang
C1 - Warenausgang
D1 - Stunden usw.

nun brauche ich eine Formel für Blatt 2 (B2) die die Zusammenrechnung aus Blatt 1 übernimmt

Hoffe es ist verständlich und mir kann einer helfen.

2

Hi,

also ich fasse mal kurz zusammen, was ich verstanden habe:
Du hast Rohdaten der Form
Datum | Typ | Wert

und möchtest diese als Auswertung aufbereiten in der Form
Datum | Typ1 | Typ2 | Typ3 …

Frage dazu:

  1. Es gibt in Rohdaten mehrfach Einträge zum gleichen Datum, die in Auswertung in einer Zeile zusammengefasst werden sollen?
3

Leider ein bißchen unverständlich. Könnte mit einer Beispieltabelle mehr anfangen.
Kp

4

falls es so ist, wie ich vermute, sieht eine mögliche Lösung folgendermaßen aus:

Name Tabellenblatt1 = Rohdaten
Name Tabellenblatt2 = Auswertung

Tabellenblatt 1 (Aufbau)
A1 = „Datum“
B1 = „Typ“
C1 = „Wert“

Tabellenblatt 2 (Aufbau)
A1 = „Datum“ (fortlaufend, Beginndatum schreiben und einfach rechts im Eck der Zelle runterziehen…

B1 =
hier ist die Schreibweise exakt einzuhalten

C1 =
etc…

dann in Excel VBA wechseln ( + ) und dort links in der Liste auf „Diese Arbeitsmappe“ klicken.
Mit Rechtsklick den Menübefehl „Einfügen“ auswählen und hier „Modul“ anklicken.

den folgenden Quelltext in das sich öffnende Fenster übernehmen:

Option Explicit
Function date_sum(datum As Date, suchwort As String) As Integer
Dim letzte_zeile, i, ergebnis As Integer
Application.Volatile True
letzte_zeile = Worksheets(„Rohdaten“).Cells(Rows.Count, 1).End(xlUp).Row
ergebnis = 0
For i = 2 To letzte_zeile
If (Worksheets(„Rohdaten“).Cells(i, 1) = datum) Then
If (Worksheets(„Rohdaten“).Cells(i, 2) = suchwort) _
Then ergebnis = ergebnis + Worksheets(„Rohdaten“).Cells(i, 3)
End If
Next i
date_sum = ergebnis
End Function

___________________________________________
anschließend in Tabellenblatt 2 in die Zelle
B2 folgende Formel eintragen: =date_sum($A2;B$1)
wieder mit Klick ins rechte untere Eck der Zelle die Formel auf den gewünschten Bereich (also alle Typen nach rechts und alle Daten nach unten übertragen.

Damit wären wir auch schon fertig :smile:

5

Hi Steve85,

Dein Kernproblem ist also den Wert einer Zelle in einer Zelle auf einem anderen Tabellenblatt anzuzeigen, richtig? (Den Anfang Deiner Ausführung habe nicht ganz verstanden…)
-Gehe in die Zielzelle, also B2 auf Blatt 2
-tippe „=“ (ohne ") und
-Gehe auf Baltt 2 und
-klicke auf die zu übernehmende Zelle
-Enter
fertig.

Alternativ kannst Du die „Formel“ =Baltt2!A1
wobei Blatt2 der Name des Tabellenblattes ist und
A1 die zu übernehmende Zelle.

hth
Gruß
Klaus

auf Reiter „Blatt 2“

6

Hallo
Irgendwie wird mir nicht klar, was du in dem Blatt 2 möchtest. Ich sehe es so, das du alle Wareneingänge, alle Warenausgänge und alle Stunden zusammenfassen möchtest! Wenn dem so sein sollte, dann kannst du einfach die Formel „wenn“ benutzen. BSP: Wenn C1 = Wareneingang; dann D1; sonst leer (""). Danach alle Felder zusammenrechnen und du hast dein Ergebnis.

7

Hallo loco_uno,

ja so ungefähr haut das schon hin. Muss ich eine VBA einfügen?
bin leider nicht mehr auf arbeit um das zu testen, also Montag wieder.

eine Beispieltabelle

__|___A___|____B____|___C__|
1_|_18.08_|__________|______|
2_|_______|WE Schokol|__238_|
3_|_______|WE Cafe___|_1421_|
4_|_______|WE Cereal_|__179_|
5_|_______|Summe WE_|_1838_|
6_|_______|WA Schokol|__456_|
7_|_______|WA Cafe___|_1224_|
8_|_______|WA Cere___|___79_|
9_|_______|Summe WA_|_1759_|
10|_______|produktiv___|__142_|
11|_______|indirekt____|___32_|
12|_______|prod zu indir|_22,5%|
usw.
17|_20.08_|__________|______|
18|_______|WE Schokol|_2198_|

Tabelle 2

__|___A___|___B___|___C___|___D___|
1_|_Datum_|__WE___|__WA___|__std__|
2_|18.08___|___?___|___?____|___?___|
3_|20.08___|

? = gesamt Summe WE ; B2=1838(gesuchte formel)

8

Hi Klaus,
ich will es noch einfacher haben, tabelle 2 soll sich von selber aktualisieren.
siehe antwort bei loco_uno, da habe ich eine tabelle gemacht.

mfg

9

Hallo Andreas,

siehe tabelle bei Antwort von loco_uno.

mfg

10

Mir ist nicht ganz klar wo das Problem ist , mach doch einfach eine Schleife als Makro, durch Blatt 1läuft und mit folgender Formale addiert: =Wenn(B5="Warenausgang), Blatt2!B2+.

Gruß
Armin

11

Kann leider nicht weiterhelfen

12

Hi,

hm, okay, Deine ursprüngliche Beschreibung klang deutlich komplexer, als es jetzt detaillierter beschrieben aussieht :smile:

Vorausgesetzt die Struktur der Daten ist in Tabelle 1 immer gleich, könnte eine stark vereinfachte Variante so aussehen:
Tabelle2:
B2=Tabelle1!C5
C2=Tabelle1!C9
D2=Summe(Tabelle1!C10:Tabelle1!C11)

falls die Struktur nicht immer gleich ist, bietet sich die Lösung mit dem VBA-Skript deshalb an, weil Du von nicht weisst, welches die letzte Zeile in Tabelle1 ist, also auch keinen festen Bereich (ausser natürlich gleich irrsinnig groß) definieren kannst.

das VBA-Skript müsste jedoch angepasst werden:
Option Explicit
Function date_sum(datum As Date, suchwort As String) As Integer
Dim letzte_zeile, i, ergebnis As Integer
Application.Volatile True
letzte_zeile = Worksheets(„Rohdaten“).Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To letzte_zeile
If (Worksheets(„Rohdaten“).Cells(i, 1) = datum) Then
If (Worksheets(„Rohdaten“).Cells(i, 2) = suchwort) _
Then ergebnis = Worksheets(„Rohdaten“).Cells(i, 3)
End If
Next i
date_sum = ergebnis
End Function

dann in Tabelle2 in Zelle B2 folgendes schreiben:
=date_sum($A2; „Summe WE“)
falls Du Dir die Mühe machen möchtest, Deine Überschriften in B1, C1, D1 anzupassen zu „Summe WE“
„Summe WA“, „Summe Std“ kannst Du die Formel dann auch einfach rüber- und runterziehen für den gewünschten Bereich.

Damit das dann aber funktioniert müsstest Du in Tabelle 1 noch die „Summe Std“ tatsächlich bilden lassen, damit diese dann beim Suchlauf durch das VBA-Skript gefunden werden kann.

Zur Eingabe der Daten bietet sich in Excel-VBA noch die Möglichkeit, über einen Button einen eigenen Dialog einzublenden, in dem dann Datum, Typ (WE, WA, Std), sowie der jeweilige Wert abgefragt und an die richtige Position in der Excel-Datei geschrieben werden, aber das war jetzt beim Lesen nur so ein Gedanke.

Es kommt natürlich unter anderem darauf an, wer mit der Excel-Datei arbeiten soll / muss :smile:

13

da hatte sich doch glatt noch ein Flüchtigkeitsfehler eingeschlichen:
Da Du in A nur einmal das Datum hast und dann erst wieder beim nächsten Eintrag, muss das VBA-Skript natürlich auch nur dann einen neuen Datumswert holen, wenn tatsächlich in der Zelle einer steht.
Die folgende Variante ist getestet und liefert die entsprechenden Tagessummen zurück:

Option Explicit
Function date_sum(datum As Date, suchwort As String) As Integer
Dim letzte_zeile, i, ergebnis As Integer
Dim Eintragsdatum As Date

Application.Volatile True
letzte_zeile = Worksheets(„Rohdaten“).Cells(Rows.Count, 1).End(xlUp).Row
ergebnis = 0
For i = 2 To letzte_zeile
If (Worksheets(„Rohdaten“).Cells(i, 1) „“) Then
Eintragsdatum = Worksheets(„Rohdaten“).Cells(i, 1)
End If
If (Eintragsdatum = datum) Then
If (Worksheets(„Rohdaten“).Cells(i, 2) = suchwort) _
Then ergebnis = ergebnis + Worksheets(„Rohdaten“).Cells(i, 3)
End If
Next i
date_sum = ergebnis
End Function

14

aus Gründen der Übersicht in der Excel-Tabelle würde ich einen der beiden folgenden Wege empfehlen:

Falls die Anzahl der Positionen immer gleich ist:
Datum | WE Schokolade | WE Cafe | WE Cereal | Summe WE | WA Schokolade | WA Cafe | WA Cereal | Summe WA | produktiv | indirekt | Summe Std | 'produktiv:indirekt

und ab Zeile 2 dann die Tagesbuchung

Falls die Liste der Artikel und Positionen wachsen kann:

Position | 01. | 02. | 03. | … --> Monatsletzter
und dann für jeden Monat des Jahres ein eigenes Tabellenblatt, dann könnte man eine schöne Jahresauswertung mit Monatsanalyse machen mit allen Statistik-Funktionen, die Excel so hergibt.

15

Hallo Steve,

Ich versuche mich auch noch einmal an einem Lösungsvorschlag, da Du evtl ohne Makros und VBA arbeiten möchtest und andere Lösungsvorschläge der anderen hilfsbereiten hier auf Wer Weiss Was evtl zu manuell ist.

Da Excel ein wenig „simple“ ist und nicht von selbst unsere Tabellenstrukturen erkennt, braucht es etwas Vorarbeit. Aufgrund Deines Beispiels nehme ich an, das Du mit Daten versorgt wirst, die jemand oder Du selbst aus einer Pivottabelle nimmt. Wenn Du diese Pivotabelle im Original hast, ist es einiges einfacher weiterzuarbeiten. Daher hier erst einmal bitte um Info.

Falls nicht, kann ich Dir im Detail folgende Schritte beschreiben, die zuerst die Rohdaten füllen -indem man die leeren Zellen mit dem gleichen Wert oberhalb befüllt.
Dann schlage ich vor die nächste Tabelle über Verweise zu füllen, die Datum und Typ abgleichen und dann ausgeben. Dafür bräuchte man einfacherhalber eine Hilfsspalte vor der Tabelle mit den Rohdaten. Voraussetzung hierfür ist, das jeder Typ an einem Datum nur ein eiziges mal auftaucht.
Falls das nicht der Fall ist, müsste ich wissen was passieren soll, falls mehr als ein Wert vorkommt. Z.B. Summe bilden o.ä.

Hoffe es ist für Dich verständlich und es ist das, was Du Dir als Ergebnis vorstellst.

Gruss
Thomas

16

Hallo Steve85,
ich weiß nicht genau, ob ich das richtig nachvollziehen kann. Wo kommen die Daten her und wofür benötigst du die 2. Tabelle. Soll alles automatisch berechnet werden? Wenn ja, wäre da eine Matrix nicht sinnvoll?

LG
PW2

17

Hallo,

so ganz verstanden habe ich es nicht. Du müsstest es mir etwas genauer aufschlüsseln. Vielleicht kann ich dann helfen.

Grüße,
Daniel

18

Hallo loco_uno

danke für die Zeit die du dir genommen hast.
Dein Vorschlag habe ich ausprobiert und es hat funktioniert, zum Teil. Die Tabelle ist leider doch ein bisschen komplexer wie im Beispiel zusammengefasst. Hatte mir den Rest irgendwie leichter vorgestellt. Würde dir die Tabelle gerne mal zeigen, aber wüsste jetzt nicht wie.
Ich werde eine Änderung in der ersten Tabelle einfügen und dann mit VERWEIS die Daten ziehen.

trotzdem ein Danke Schön

19

Hallo Thomas,

es wäre schön wenn die Tabelle ohne Makros und VBA arbeitet, weil andere Kollegen damit überhaupt nicht zurecht kommen. Die Tabelle1 (Rohdaten) bezieht ihre Daten von anderen Exceltabellen und wird seit April gepflegt und täglich aktualisiert, somit schon über 1800 Zeilen lang. Nun möchte die Geschäftsleitung eine Zusammenfassung mit Auswertung jeden Monats. Da ich nicht alle Daten manuell auf das andere Tabellenblatt übertragen wollte, weil das zu viel Zeit in Anspruch nimmt, habe ich mich nach eine vereinfachten alternative umgeschaut und euch um Hilfe gebeten. Leider sind die Begriffe wie WE/WA mehrmals jeden Tag vertreten und selbst eine VBA (Vorschlag von loco_uno) sucht sich schwer.
Ich würde euch die Tabelle gerne mal zeigen, aber ich weiss nicht wie.
Ich denke mal meine einzige Möglichkeit besteht nur noch die Tabelle1 aufbaulich zu ändern und dann mit SVERWEIS die Daten zu übernehmen.

Viele Grüße

20

Hallo Steve,
kannst Du Dich ein bisschen besser ausdrücken.
Verstehe Dein Problem nicht ganz.
Vielleicht kannst Du es anhand eines Beispieles aufzeichnen.
Gruß
Udo