WVERWEIS mit SUMMEWENN

Forumthread: WVERWEIS mit SUMMEWENN

05.01.2020 11:06:48

Kevin

Guten Morgen liebe Excel-Experten!
Ich habe einen relativ komplexen Dienstplan, indem die Linien und Fahrzeuge vermerkt sind, welche ich gefahren habe. Außerdem werden verschiedene Zeiten ausgerechnet, was aber für meine Frage nebensächlich ist. Ich möchte nun in einer extra Tabelle auswerten wie viele Kilometer ich pro Linie bzw. pro Fahrzeug gefahren habe. Ich habe schon viel probiert und das halbe Internet durchsucht, allerdings keine funktionierende Formel gefunden, mit der man das so berechnen könnte wie es wöllte. Für mein Beispiel gehen wir nur von dem 01. und 02.01.2019 aus. Vorgestellt habe ich mir, dass man in 'Test-0119'!A6:BD6 mit WVERWEIS das Datum sucht, welches sich in 'Auswertung-0119'!B3:E3 befindet. Wenn dies gefunden wurde 3 Zeilen runter geht und in der gleiche Spalte wie das Datum mit SUMMEWENN die Linie sucht und dann die jeweiligen Kilometer in der Auswertung zusammen rechnet. Das gleiche würde ich dann gerne mit den Fahrzeugen haben.
Die Ergebnisse müssten dann wie folgt aussehen:
01.01.2019 Linie 10: 60,377
01.01.2019 Linie 12: 63,037
02.01.2019 Linie 6: 29,934
02.01.2019 Linie 7: 66,421
01.01.2019 Fzg 2601: 51,686
01.01.2019 Fzg 2536: 71,728
02.01.2019 Fzg 2602: 29,934
02.01.2019 Fzg 2801: 66,421
Hier die Tabellen zur Veranschaulichung:
Test-0119

	A	B	C	D	E	F	G	H	I	J	K	L	AC	AD	AE	AF
6	1	Di	Beginn	BGOR	06:11	auf 12/14.				BGOR-19084			2	Mi	Beginn	BGOR
7	1	Di	Ende	POS	15:12	auf 12/10.				BGOR-19084			2	Mi	Ende	GOR
8	Linie	Kurs	Fahr- zeug	Route	Anfang Ort	Anfang Zeit	Anfang Zeit IST	über	Ende Ort	Ende Zeit	Ende Zeit IST	KM	Linie	Kurs	Fahr- zeug	Route
9			VBZ		BGOR	06:11			BGOR	06:21					VBZ
10	12	14.	2601	207	BGOR	06:22			LTW	06:44		7,544	6	30.	2602	83
11	12	14.	2601	202	LTW	06:55		MAN	LHA	07:41		13,172	6	30.	2602	68
12	10	14.	2601	1	LHA	07:41			MEG	08:22		11,369	6	30.	2602	65
13	10	14.	2601	2	MEG	08:27			LHA	09:03		10,869	6	30.	2602	68POS
14	12	14.	2601	201POS	LHA	09:03			POS	09:32		8,732			NBZ
15			NBZ		POS	09:32			POS	09:35					WGZ
16			WGZ		POS	09:35			APOS	09:38					FLZ
17			FLZ		APOS	09:38			APOS	10:20					UP
18			WGZ		APOS	10:20			HBF	10:32					WGZ
19	10	10.	2536	HBF1	HBF	10:32			MEG	10:52		5,032	7	24.	2801	ALP151
20	10	10.	2536	2	MEG	10:57			LHA	11:33		10,869	7	24.	2801	152
21	12	10.	2536	201	LHA	11:33		MAN	LTW	12:24		11,685	7	24.	2801	151
22	12	10.	2536	202	LTW	12:35		MAN	LHA	13:21		13,172	7	24.	2801	152GOR
23	10	10.	2536	1	LHA	13:21			MEG	14:02		11,369
24	10	10.	2536	2	MEG	14:07			LHA	14:43		10,869
25	12	10.	2536	201POS	LHA	14:43			POS	15:12		8,732
26
27
28
29
30
31
32
33
34
35

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Auswertung-0119

	A	B	C	D	E
3		01.01.2019		02.01.2019
4		Anzahl	Kilometer	Anzahl	Kilometer
5	1
6	2
7	3
8	4
9	6
10	7
11	8
12	9
13	10
14	11
15	12
16	13
17	16
18	20
19	41
20	42
21	43
22	44
23	46
24	47
25	48
26	49
27	59
28
29	2501
30	2502
31	2503
32	2504

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Ich hoffe Ihr könnte mir helfen. Evtentuell kommen dann noch ein bis zwei andere Fragen zu dieser Tabelle, an denen ich auch schon gescheitert bin.
Viele Grüße,
Kevin Gellrich

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: WVERWEIS mit SUMMEWENN

05.01.2020 12:44:45

Günther

Moin Kevin,
mit dem Tabellen-Aufbau wirst du noch viel "Freude" haben. Das geht damit los, dass verbundene Zellen der schleichende Tod einer ordentlichen Auswertung sind und endet damit, dass eine ordentliche Tabelle genau 1 Überschrift-Zeile hat.
Ich habe einmal mit der Auswertung angefangen aber schnell aufgegeben, da ein sauberer Transfer von der Jeany-Tabelle in mein Excel nicht möglich ist und ich auch wegen der oben aufgeführten Hindernisse zu viel nacharbeiten müsste, um überhaupt mit der Arbeit (Auswertung) anzufangen.
Gruß
Günther

AW: 2 benannte Fml + 2 Fml +2 bed Form. ...

05.01.2020 13:35:07

neopa

Hallo Kevin,
... unter der Voraussetzung, das in A6 und M6 ... echte Datumswerte stehen, die nur im Zahlenformat T formatiert sind.
Folgende Auswertung Deiner Beispielangaben (km-Angaben für den 02.01.99 hattest du nicht mit angegeben) Die benannten Formeln sowie die bed. Formatierungen müssen bei aktivierter Zelle B5 definiert werden.
Formeln B5:C5 sind Matrixfunktion(alität)sformel die keines spez. Formelabschluss wie eine klassische Matrixformel benötigen, nach unten ziehend kopieren und dann blockweise nach rechts.

Arbeitsblatt mit dem Namen 'Auswertung-0119'
	B	C	D
3	01.01.2019		02.01.2019
4	Linie /Fzg	Kilometer	Anzahl
5	Linie 10	60,377	Linie 6
6	Linie 12	63,037	Linie 7
7
8	Fzg 2536	71,728	Fzg 2602
9	Fzg 2601	51,686	Fzg 2801
10

Name	Bezug
_Fzg	=WENNFEHLER(AGGREGAT(15;6;INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0)+2)/(INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0)+2)>"")/(ZÄHLENWENN('Auswertung-0119'!B$4:B2;INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0)+2))=0);1);"")
_Linie	=WENNFEHLER(AGGREGAT(15;6;INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0))/(INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0))>"")/(ZÄHLENWENN('Auswertung-0119'!B$4:B2;INDEX(Test_0119!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;Test_0119!$6:$6;0)))=0);1);"")

Zelle	Formel
B5	=WENN(ZÄHLENWENN(B$4:B4;"")=1;_Fzg;_Linie)
C5	=WENN(B5="";"";SUMMEWENN(INDEX(Test_0119!A$9:BD$99;;VERGLEICH(B$3;Test_0119!$6:$6;0)+2*(B5&/gt99));B5;INDEX(Test_0119!A$9:BD$29;;VERGLEICH(B$3;Test_0119!$6:$6;0)+11)))

Zelle	bedingte Formatierung...	Format
B5	1: B5&lt99	abc
B5	2: (B5&gt99)	abc

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Die bedingte Formatierung wird durch das Tool leider nicht korrekt angeben, deshalb hier:
für &gt99 Zahlenformat "Linie "0 und
für &gt99 Zahlenformat "Fzg "0 zuweisen
Gruß Werner
.. , - ...

AW: ergänzende Hinweise hierzu ...

05.01.2020 13:43:49

neopa

Hallo,
... die Zellfarbe der Zelle B5 hatte ich wohl aus Versehen nicht mit übernommen und ist somit nicht dargestellt. Diese ist aber für die Def. der benannten Formeln _Linie und _Fzg zwingend zu aktivieren.
Bei der bedingten Formatierung hab ich mich beim Vergleichszeichen verschrieben.
Es sollte richtig "für &lt"99 Zahlenformat "Linie "0 lauten.
Gruß Werner
.. , - ...

AW: ergänzende Hinweise hierzu ...

11.01.2020 11:26:45

Kevin

Hallo Werner,
ich danke dir sehr für die ausführliche Antwort. Entschuldige bitte meine recht späte Reaktion, aber ich kam bisher nicht dazu dies auszuprobieren.
Vorneweg: In A6 und AC6 steht ein echtes Datum, welches mit T formatiert wurde. Außerdem habe ich bei Linie /Fzg die bedingte Formatierung angewandt.
Leider hat bei mir die Auswertung allerdings nicht ganz funktioniert, wie es bei dir dargestellt und beschrieben wurde.
Dadurch, dass ich gerade auf dem Schlauch stand und nicht genau wusste was ich mit _Fzg und _Linie und den dazugehörigen Bezügen machen sollte bzw. wo ich die hinterlegen sollte habe ich diese in die Formel in B5 gesetzt. Ich hoffe, dass das richtig war, denn sonst könnte man den Fehler schon gefunden haben.
Außderdem musste ich ein bisschen was an der Formel anpassen, damit Excel diese auch damit arbeiten kann ;)
Mein Ergebnis sieht wie folgt aus:
Auswertung-0119

	B	C	D
3	01.01.2019		02.01.2019
4	Linie /Fzg	Kilometer	Anzahl
5	Linie 10	0	Linie 6
6	Linie 10	0	Linie 6
7	Linie 10	0	Linie 6
8	Linie 12	0	Linie 7
9	Linie 12	0	Linie 7
10	Linie 12	0	Linie 7
11
12	Fzg 2536	#BEZUG!	Fzg 2602
13	Fzg 2536	#BEZUG!	Fzg 2602
14	Fzg 2536	#BEZUG!	Fzg 2602
15	Fzg 2601	#BEZUG!	Fzg 2801
16	Fzg 2601	#BEZUG!	Fzg 2801
17	Fzg 2601	#BEZUG!	Fzg 2801

Formeln der Tabelle

Zelle	Formel
B5	=WENN(ZÄHLENWENN(B$4:B4;"")=1;WENNFEHLER(AGGREGAT(15;6;INDEX('Test-0119'!$A$9:$BD$35;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0)+2)/(INDEX('Test-0119'!$A$9:$BD$35;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0)+2)<>"")/(ZÄHLENWENN('Auswertung-0119'!B2:B$4;INDEX('Test-0119'!$A$9:$BD$99;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0)+2))=0); 1); ""); WENNFEHLER(AGGREGAT(15;6;INDEX('Test-0119'!$A$9:$BD$35;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0))/(INDEX('Test-0119'!$A$9:$BD$35;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0))<>"")/(ZÄHLENWENN('Auswertung-0119'!B2:B$4;INDEX('Test-0119'!$A$9:$BD$35;;VERGLEICH('Auswertung-0119'!B$3;'Test-0119'!$6:$6;0)))=0); 1); ""))
C5	=WENN(B5="";"";SUMMEWENN(INDEX('Test-0119'!A$9:BD$35;;VERGLEICH(B$3;'Test-0119'!$6:$6;0)+2*(B5&GT99)); B5;INDEX('Test-0119'!A$9:BD$35;;VERGLEICH(B$3;'Test-0119'!$6:$6;0)+11)))

Viele Grüße,
Kevin

AW: ein andereren Daten-Tabellenbalttnamen ...

12.01.2020 20:20:10

neopa

Hallo Kevin,
... hatte ich verwendet. Den Du mit "Test-0119" hatte ich mit "Test_0119" bezeichnet. Dadurch hat sich bei Dir wohl beim entsprechenden Anpassen der Formeln an Deinen Tabellenblattnamen Fehler eingeschlichen, denn die von mir angegebenen Formeln sind korrekt.
Uns bei "_Fzg" sowie "_Linie" handelt es sich um sogenannte benannte Formeln, die bei aktivierter Zelle B5 definiert werden müssen. Mehr dazu sieh z.B. mal her: https://www.online-excel.de/excel/singsel.php?f=60 und ff.
Benenne doch einfach in Deinem Originaldatei Dein Tabellenblattnamen "Test-0119" vorübergehend in "Test_0119" um und nutze dafür meine Formelvorgaben durch kopieren aus meinem thread-Beitrag. Wenn danach die Ergebnisse stimmen, kannst Du ja anschließend das Tabellenblatt wieder zurück benennen. Die Formeln passen sich dem automatisch an.
Solltest Du weitere Fragen hierzu haben, eröffne besser einen neuen thread und schreibe in dessen Betreff: "@ neopa Fortsetzungsthread... " Und im Text dazu gibst Du zu Deinen Fragen auch noch folgenden Link zu Deinem hiesigen thread an: https://www.herber.de/forum/archiv/1732to1736/t1732136.htm (damit ich nicht lange nach diesen thread wieder suchen muss).
Der neue thread ist notwendig, weil der alte nicht mehr in der Forumliste geführt wird und ich Deine evtl. Fragen dann leicht übersehen kann.
Gruß Werner
.. , - ...

Infobox / Tutorial

WVERWEIS und SUMMEWENN in Excel richtig anwenden

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Tabellenstruktur vorbereiten: Stelle sicher, dass deine Daten in der Tabelle Test-0119 gut strukturiert sind. Die erste Zeile sollte Datumseinträge enthalten, und in den folgenden Zeilen sollten die Linien und Fahrzeuge aufgeführt sein.
Formel für WVERWEIS erstellen: Verwende die folgende Formel, um den WVERWEIS zu implementieren:
```
=WVERWEIS(Datum; Test-0119!$A$6:$BD$99; Zeilenindex; FALSCH)
```
Ersetze Datum durch die Zelle, die das gesuchte Datum enthält, und Zeilenindex mit der Zeilennummer, die du anvisierst.
SUMMEWENN für Kilometer: Um die Kilometer für eine spezifische Linie zu summieren, nutze die SUMMEWENN-Funktion:
```
=SUMMEWENN(Test-0119!$C$6:$C$99; Linie; Test-0119!$N$6:$N$99)
```
Hierbei ersetzen Linie und die Bereiche die spezifischen Bezugnahmen in deiner Tabelle.
Formeln in die Auswertung übertragen: Trage die Formeln in die Tabelle Auswertung-0119 ein, um die Kilometer nach Datum und Linie/Fahrzeug zu aggregieren.

Häufige Fehler und Lösungen

Fehler: #BEZUG!
- Lösung: Überprüfe, ob du korrekte Zellreferenzen verwendet hast. Achte darauf, dass die Tabellenblattnamen korrekt sind.
Fehler: Ergebnisse sind nicht wie erwartet
- Lösung: Stelle sicher, dass deine Datumsangaben in der richtigen Formatierung (z.B. als echte Datumswerte) vorliegen.
Problem mit verbundenen Zellen
- Lösung: Vermeide verbundene Zellen, da sie die Nutzung von WVERWEIS und SUMMEWENN beeinträchtigen können.

Alternative Methoden

Pivot-Tabellen: Anstatt WVERWEIS und SUMMEWENN zu verwenden, kannst du auch eine Pivot-Tabelle erstellen, um die Kilometer nach Linie und Fahrzeug zu aggregieren. Dies kann eine einfachere und übersichtlichere Methode sein.
FILTER-Funktion (Excel 365): Wenn du Excel 365 nutzt, kannst du die FILTER-Funktion verwenden, um spezifische Daten zu extrahieren und so die Berechnungen zu vereinfachen.

Praktische Beispiele

Angenommen, du hast die folgende Tabelle in Test-0119:

Datum	Linie	Fahrzeug	Kilometer
01.01.2019	10	2601	60,377
01.01.2019	12	2536	63,037
02.01.2019	6	2602	29,934

In der Auswertung-0119-Tabelle könntest du die folgende Formel für die Kilometer von Linie 10 am 01.01.2019 verwenden:

=SUMMEWENN(Test-0119!$B$2:$B$100; "10"; Test-0119!$D$2:$D$100)

Tipps für Profis

Benannte Bereiche nutzen: Verwende benannte Bereiche für deine Daten, um die Formeln übersichtlicher zu gestalten und Fehlerquellen zu minimieren.
Bedingte Formatierung: Setze bedingte Formatierungen ein, um die Ergebnisse visuell hervorzuheben, z.B. wenn die Kilometer über einem bestimmten Wert liegen.
Fehlerüberprüfung: Nutze die Funktion WENNFEHLER, um Fehler in deinen Formeln abzufangen:
```
=WENNFEHLER(SUMMEWENN(...); "Keine Daten")
```

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen WVERWEIS und SVERWEIS? WVERWEIS sucht Werte in einer horizontalen Ausrichtung, während SVERWEIS vertikale Suchen durchführt.

2. Warum funktioniert meine Formel nicht? Überprüfe, ob alle Zellreferenzen korrekt sind und ob die Datumsformate einheitlich sind. Achte auch darauf, dass die Daten in den richtigen Spalten stehen.

3. Wie kann ich die Kilometer für mehrere Linien summieren? Verwende SUMMEWENN oder SUMMEWENNS, um mehrere Bedingungen zu berücksichtigen.

Beliebteste Forumthreads (12 Monate)

Forumthread: WVERWEIS mit SUMMEWENN

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

WVERWEIS und SUMMEWENN in Excel richtig anwenden

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread