Microsoft Excel

Herbers Excel/VBA-Archiv

Wert aus Zeile mit aktuellestem Datum ziehen

Betrifft: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 26.04.2014 11:50:15

Hallo,

ich habe folgendes Problem: Aus der Ausgangstabelle von unten soll für jede Firma für das jeweilige Jahr die letzte Prüfungsnote gezogen warden. Beispielsweise hat Firma a für das Jahr 2012 zwei Prüfungsnoten, eine aus Januar und eine aus Dezember. In der Zieltabelle soll in diesem Fall der aktuellere Wert stehen, das heißt der aus Dezember. Da ich mit VBA noch sehr sehr wenig Erfahrung habe ware es super nett wenn mir hierbei jemand helfen könnte.

Vielen Dank schonmal!

AUSGANGSTABELLE:

Firma; Bundesland; Datum der Prüfung; Prüfungsnote
a; Niedersachsen; 20/01/2012; 1,1
a; Niedersachsen; 25/12/2012; 1,2
a; Niedersachsen; 26/07/2013; 1,7
b; Bayern; 11/12/2009; 1,4
b; Bayern; 11/01/2011; 1,2
b; Bayern; 07/12/2011; 1,3
b; Bayern; 18/01/2012; 1,1
b; Bayern; 08/11/2012; 1,1
c; Berlin; 16/02/2010; 1,9
c; Berlin; 10/01/2012; 1,8
d; Thüringen; 15/10/2009; 1,4
d; Thüringen; 19/07/2011; 1,3
d; Thüringen; 04/12/2012; 1,2

ZIELTABELLE:

Firma 2009 2010 2011 2012 2013
a
b
c
d

  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 12:15:26


Tabelle2

 ABCDEFGHIJK
1Firma;Bundesland;DatumPrüfungsnote Firma20092010201120122013
2aNiedersachsen;20.01.20121,1 a   1,21,7
3aNiedersachsen;25.12.20121,2 b1,4 1,31,1 
4aNiedersachsen;26.07.20131,7 c 1,9 1,8 
5bBayern;11.12.20091,4 d1,4 1,31,2 
6bBayern;11.01.20111,2       
7bBayern;07.12.20111,3       
8bBayern;18.01.20121,1       
9bBayern;08.11.20121,1       
10cBerlin;16.02.20101,9       
11cBerlin;10.01.20121,8       
12dThüringen;15.10.20091,4       
13dThüringen;19.07.20111,3       
14dThüringen;04.12.20121,2       

verwendete Formeln
Zelle Formel Bereich N/A
G2:K5{=WENN(MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=G$1); )=2;$C$2:$C$14); )=0;"";SUMMENPRODUKT(($C$2:$C$14=MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=G$1); )=2;$C$2:$C$14); ))*$D$2:$D$14); )}$G$2 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!


Zahlenformate
Zelle Format Wert
C2 'TT.MM.JJJJ 40928
C3 'TT.MM.JJJJ 41268
C4 'TT.MM.JJJJ 41481
C5 'TT.MM.JJJJ 40158
C6 'TT.MM.JJJJ 40554
C7 'TT.MM.JJJJ 40884
C8 'TT.MM.JJJJ 40926
C9 'TT.MM.JJJJ 41221
C10 'TT.MM.JJJJ 40225
C11 'TT.MM.JJJJ 40918
C12 'TT.MM.JJJJ 40101
C13 'TT.MM.JJJJ 40743
C14 'TT.MM.JJJJ 41247
G2, G4, H2:H3, H5:K5, I2, I4 '0,0  
G3, G5 '0,0 1,4
H4 '0,0 1,9
I3, I5 '0,0 1,3
J2, J5 '0,0 1,2
J3 '0,0 1,1
J4 '0,0 1,8
K2 '0,0 1,7
Zellen mit Format Standard werden nicht dargestellt
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 17.11 einschl. 64 Bit



GrußformelHomepage


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: =Peter=
Geschrieben am: 26.04.2014 12:31:30

Hallo

die Darstellung der von Hajo_Zi geposteten Formel sieht seltsam aus. Diese Strichpunkte und Leerzeichen zwischen den Klammern dürften da eigentlich nicht stehen.

=WENN(MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=G$1); )=2;$C$2:$C$14); )=0;""; SUMMENPRODUKT(($C$2:$C$14=MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=G$1); )=2;$C$2:$C$14); )) *$D$2:$D$14); )


Die Formel müsste so aussehen:

=WENN(MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=I$1))=2;$C$2:$C$14))=0;""; SUMMENPRODUKT(($C$2:$C$14=MAX(WENN((($A$2:$A$14=$F2)+(JAHR($C$2:$C$14)=I$1))=2;$C$2:$C$14))) *$D$2:$D$14))

Da scheint das Addin noch ein kleines Problem zu haben.


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 12:35:55

welche Strichpunkte; Leerstelle sind der Formel egal

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: =Peter=
Geschrieben am: 26.04.2014 12:42:32

Hallo,

z.B. hier =G$1); )=2 oder hier $C$14); )=0


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 12:46:03

Strich Punkt ist für mich ein Strich und ein Punkt, meinst Du Semikolon?
Die Formel ist korrekt, Sie liefert ja´das richtige Ergebnis. Ich schreibe in einem Forum .DE also gehe ich von einer Version Deutschland aus.

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: =Peter=
Geschrieben am: 26.04.2014 12:53:13

Hallo,

wenn dir der Begriff Semikolon lieber ist - meinetwegen.

Ich hatte nicht geschrieben, dass die Formel falsch ist - ich hatte geschrieben, dass die Darstellung der Formel falsch ist und das auch an einem Beispiel dargestellt.

Und das hat nichts mit einer "Version Deutschland" zu tun.

Deine Antworten zeigen mir, dass du dich überhaupt nicht mit meinem Beitrag auseinandergesetzt hast.

Damit ist für mich das Thema erledigt.


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 26.04.2014 12:54:47

Super, danke schonmal für die fixe Hilfe!!!
Habe eine englische Version, denke aber dass ich das hinbekomme. Könnte man das ganze auch in VBA lösen; würde mich einfach interessieren wie ein möglicher Lösungsweg aussehen würde.

VG


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 13:24:59

von englisch Stand nicchts im Beitrag.
ich vermute folgende Array Formel.
=IF(MAX(IF((($A$2:$A$14=$F2)+(YEAR($C$2:$C$14)=G$1))=2,$C$2:$C$14)) =0,"",SUMPRODUCT(($C$2:$C$14=MAX(IF((($A$2:$A$14=$F2)+(YEAR($C$2:$C$14)=G$1))=2,$C$2:$C$14))) *$D$2:$D$14))

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 13:21:31

ich hatte nun nicht vor zwei Formeln mit ca. 200 Zeichen zu vergleichen um den Unterschied festzustellen den Du schon festgestellt hast. Aber ich bin zufällig drauf gestossen.
Das muss ich jetzt aber erstmal vertiefen, warum Excel eine geänderte Formel ausliest.

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 14:16:36

Hallo Peter,

der Fehler ist bei Version 17.12 beseitigt. Wir hatten einigen Code zu Smileys Vermeidung geschrieben und da hat sich dieser Fehler eingeschlichen.

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 26.04.2014 19:20:41

Hi,

ich habe mein sheet mal angehängt, leider funktioniert die Formel manchmal nicht. Für Augsburg wird beispielweise in 2011 5.5 gezogen und nicht der korrekt Wert. Leider war meine Fehlersuche bisher nicht erfolgreich, vl könnt ihr mir nochmal helfen.

Danke nochmals!


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 26.04.2014 19:21:39

https://www.herber.de/bbs/user/90357.xlsx


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 19:36:09

das ist der Vorteuiil von umfangreichen Werten, Fehler fallen auf.
Nun auch in Englisch ausgelesen.

Sheet1

 IJKLM
2  1,31,3 
3 1,3 1,41
42,1 1,61,71,8
5  1,41,21,2

verwendete Formeln Englisch
Zelle Formel Bereich N/A
I2:M5{=IF(MAX(IF((($A$2:$A$229=$G2)+(YEAR($C$2:$C$229)=I$1) )=2,$C$2:$C$229) ) =0,"",SUMPRODUCT(($A$2:$A$229=$G2)*($C$2:$C$229=MAX(IF((($A$2:$A$229=$G2)+(YEAR($C$2:$C$229)=I$1) )=2,$C$2:$C$229) )) *$D$2:$D$229) )}$I$2 
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!

Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 17.12 einschl. 64 Bit



Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 27.04.2014 15:17:19

Hey Hajo,

Danke, die Formel funktioniert jetzt einwandfrei. Hast du einen Tipp wie sich die Syntax solcher Arrayfunktionen am besten erlenen lässt, gibt es da irgendwelche tollen Quellen/Internetseiten? Die Darstellung ist ja generell sehr unintuitiv (zumindestens für mich.

VG


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 27.04.2014 15:45:25

bei mir ist es auch nicht anders, vielle Array Formeln findest du auf Excelformeln.DE

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Hajo_Zi
Geschrieben am: 26.04.2014 19:23:17

ich sehe die Datei nicht. Im ersten Beitrag gab es keine Firma Augsburg.

Gruß Hajo


  

Betrifft: AW: Wert aus Zeile mit aktuellestem Datum ziehen von: Armin
Geschrieben am: 26.04.2014 19:30:33

Funktioniert der Link oben nicht? In der Datei siehst du Augsburg.


 

Beiträge aus den Excel-Beispielen zum Thema "Wert aus Zeile mit aktuellestem Datum ziehen"