Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 10:08:11
Summieren von Zellen mit bedingter Formatierung
26.04.2024 09:08:00
Export alle Tabellenblätter als csv - in bestimmten Ordner
26.04.2024 08:56:40
Über alle Spalten nur €-Werte zählen
26.04.2024 08:08:24
Pivot Vormonatsveränderung
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1732to1736
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
1732to1736
1732to1736
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

WVERWEIS mit SUMMEWENN

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

 ABCDEFGHIJKLACADAEAF
61DiBeginnBGOR06:11auf 12/14.   BGOR-19084 2MiBeginnBGOR
7EndePOS15:12auf 12/10.    EndeGOR
8LinieKursFahr- zeugRouteAnfang OrtAnfang ZeitAnfang Zeit ISTüberEnde OrtEnde ZeitEnde Zeit ISTKMLinieKursFahr- zeugRoute
9  VBZ BGOR06:11  BGOR06:21    VBZ 
101214.2601207BGOR06:22  LTW06:44 7,544630.260283
111214.2601202LTW06:55 MANLHA07:41 13,172630.260268
121014.26011LHA07:41  MEG08:22 11,369630.260265
131014.26012MEG08:27  LHA09:03 10,869630.260268POS
141214.2601201POSLHA09:03  POS09:32 8,732  NBZ 
15  NBZ POS09:32  POS09:35    WGZ 
16  WGZ POS09:35  APOS09:38    FLZ 
17  FLZ APOS09:38  APOS10:20    UP 
18  WGZ APOS10:20  HBF10:32    WGZ 
191010.2536HBF1HBF10:32  MEG10:52 5,032724.2801ALP151
201010.25362MEG10:57  LHA11:33 10,869724.2801152
211210.2536201LHA11:33 MANLTW12:24 11,685724.2801151
221210.2536202LTW12:35 MANLHA13:21 13,172724.2801152GOR
231010.25361LHA13:21  MEG14:02 11,369    
241010.25362MEG14:07  LHA14:43 10,869    
251210.2536201POSLHA14:43  POS15: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

 ABCDE
3 01.01.2019 02.01.2019 
4 AnzahlKilometerAnzahlKilometer
51    
62    
73    
84    
96    
107    
118    
129    
1310    
1411    
1512    
1613    
1716    
1820    
1941    
2042    
2143    
2244    
2346    
2447    
2548    
2649    
2759    
28     
292501    
302502    
312503    
322504    


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

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

Betreff
Datum
Anwender
Anzeige
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
Anzeige
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'
 BCD
301.01.2019 02.01.2019
4Linie /FzgKilometerAnzahl
5Linie 1060,377Linie 6
6Linie 1263,037Linie 7
7   
8Fzg 253671,728Fzg 2602
9Fzg 260151,686Fzg 2801
10   

NameBezug
_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);"")

ZelleFormel
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)))

Zellebedingte Formatierung...Format
B51: B5&lt99abc
B52: (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
.. , - ...
Anzeige
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
 BCD
301.01.2019 02.01.2019
4 Linie /FzgKilometerAnzahl
5Linie 100Linie 6
6Linie 100Linie 6
7Linie 100Linie 6
8Linie 120Linie 7
9Linie 120Linie 7
10Linie 120Linie 7
11   
12Fzg 2536#BEZUG!Fzg 2602
13Fzg 2536#BEZUG!Fzg 2602
14Fzg 2536#BEZUG!Fzg 2602
15Fzg 2601#BEZUG!Fzg 2801
16Fzg 2601#BEZUG!Fzg 2801
17Fzg 2601#BEZUG!Fzg 2801

Formeln der Tabelle
ZelleFormel
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
Anzeige
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
.. , - ...
Anzeige

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige