Microsoft Excel

Herbers Excel/VBA-Archiv

nur den grössten Wert einer Bedingung finden


Betrifft: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 11:40:21

Moin,

ich habe eine Tabelle, wo ich in Spalte A die Bestellnummern habe
und in Spalte B die einzelnen Werte (Preise in dem Fall)

Ich benötige nun den grössten Wert einer Bestellnummer, manchmal gibt es 3 Datensätze zu einer Bestellung und manchmal auch nur 2...
Am liebsten sollte der Grösste in Spalte C geschrieben werden gleich neben dem richtigen Wert und die anderen bleiben leer...

Eine Beispieldatei ist angefügt. Dies ist nur ein Auszug, denn meine Datentabelle hat eigentlich 31 Spalten und ist ein Abfragetabelle.

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

Oder hat jemand eine andere Lösung?

  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Oberschlumpf
Geschrieben am: 25.09.2019 11:48:18

Hi Sue

versuch es doch so:

1.0 die ganze Tabelle markieren
2.0 Sortieren anklicken
2.1 1. Sortierparameter = Spalte A, aufsteigend
2.2 2. Sortierparameter = Spalte B, absteigend, also größter Wert zuerst

Wenn ich nix übersehen habe, wird mit diesem Lösungsansatz deine Frage beantwortet.

Hilfts?

Ciao
Thorsten


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 11:53:04

Hi Oberschlumpf (geiler Name),

grundsätzlich super Lösung (DANKE!) , nur leider nicht wenn ich die Daten in eine Pivot nehme, Deswegen dachte ich könnte quasi nur die Grössten Werte per Formel ermitteln, in die jeweilige Zelle schreiben und die Filter in der Pivot aus nicht leer setzen....

Lieben Gruß


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Oberschlumpf
Geschrieben am: 25.09.2019 12:12:33

Hi Sue,

dann hier noch mal...mit Sortieren (per Hand) und einem Makro:
https://www.herber.de/bbs/user/132192.xlsm

Beschreibung:
1. Zuerst per Hand die ganze Tabelle sortieren wie gewünscht
2. Öffne den VBE (rechte MT auf ein Tabellenblattname und Code anzeigen auswählen
3. Nun das Makro sbGroesster im allgemeinen Modul starten

Das Makro beginnt mit einer For/Next-Schleife in der letzten, genutzten Zeile in Spalte A
Wenn Wert in A aus For/Next anders ist als Wert in A aus For/Next-1, dann wird in Spalte C in Zeile A aus For/Next der Wert aus Spalte B eingetragen.

So werden immer nur die jeweils ersten Zeilen mit gleicher Bestellnr in A berücksichtigt und somit wird immer - nur - der größte Wert aus B in C eingetragen.

Jetzt ist es noch etwas umständlich mit dem Ausführen, aber man kann das Ganze ja noch mit nem Button versehen, der a) automatisch sortiert und b) automatisch das Makro startet.

Hilfts?

Ciao
Thorsten


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Oberschlumpf
Geschrieben am: 25.09.2019 12:14:10

da hat Mathias allerdings recht.
Die Werte in B sind negative Zahlen, da sind wirklich die kleinen Zahlen die größeren Werte^^ :-)
Und nun?


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Matthias L
Geschrieben am: 25.09.2019 11:50:17

Hi

Tabelle1

 AB
1Bestellnr - Kopie.1 Preis USD
2A18016954-                             264,39
3A18019302-                               15,08
4A18021076-                             272,26
5A18021096-                             140,56
6A18021989-                                  8,01
7A18022424-                                  5,00
8A18022593-                             150,04
9A18022593-                             100,17
10A18022593-                               50,70
11A18022700-                               51,68
12A18022849-                             665,87
13A19000768-                               23,14
14A19000821-                       19.039,02
15A19000821-                               48,93
16A19001267-                               96,00
17A19001267-                               67,28
18A19001587-                               26,80
19A19001587-                             180,17
20A19001746-                               51,75
21A19001764-                               10,38
22A19001804-                               46,97
23A19001810-                         8.518,61
24A19001893-                               55,53
25  
26  
27A18016954-                             264,39
28A18019302-                               15,08
29A18021076-                             272,26
30A18021096-                             140,56
31A18021989-                                  8,01
32A18022424-                                  5,00
33A18022593-                               50,70
34A18022700-                               51,68
35A18022849-                             665,87
36A19000768-                               23,14
37A19000821-                               48,93
38A19001267-                               67,28
39A19001587-                               26,80
40A19001746-                               51,75
41A19001764-                               10,38
42A19001804-                               46,97
43A19001810-                         8.518,61
44A19001893-                               55,53

Formeln der Tabelle
ZelleFormel
B27{=MAX(WENN($A$2:$A$24=A27;$B$2:$B$24))}
B28{=MAX(WENN($A$2:$A$24=A28;$B$2:$B$24))}
B29{=MAX(WENN($A$2:$A$24=A29;$B$2:$B$24))}
B30{=MAX(WENN($A$2:$A$24=A30;$B$2:$B$24))}
B31{=MAX(WENN($A$2:$A$24=A31;$B$2:$B$24))}
B32{=MAX(WENN($A$2:$A$24=A32;$B$2:$B$24))}
B33{=MAX(WENN($A$2:$A$24=A33;$B$2:$B$24))}
B34{=MAX(WENN($A$2:$A$24=A34;$B$2:$B$24))}
B35{=MAX(WENN($A$2:$A$24=A35;$B$2:$B$24))}
B36{=MAX(WENN($A$2:$A$24=A36;$B$2:$B$24))}
B37{=MAX(WENN($A$2:$A$24=A37;$B$2:$B$24))}
B38{=MAX(WENN($A$2:$A$24=A38;$B$2:$B$24))}
B39{=MAX(WENN($A$2:$A$24=A39;$B$2:$B$24))}
B40{=MAX(WENN($A$2:$A$24=A40;$B$2:$B$24))}
B41{=MAX(WENN($A$2:$A$24=A41;$B$2:$B$24))}
B42{=MAX(WENN($A$2:$A$24=A42;$B$2:$B$24))}
B43{=MAX(WENN($A$2:$A$24=A43;$B$2:$B$24))}
B44{=MAX(WENN($A$2:$A$24=A44;$B$2:$B$24))}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Matthias


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 12:00:57

Hallo Matthias,

auch Dir lieben Dank für deine Mühe. Die Lösung passt allerdings nicht, denn ich kann keine Daten unter die Tabelle setzen, da sich diese noch erweitert nach dem Aktualisieren (ist eine Abfragedatei aus einer anderen Quelle)

Leider stimmen dennoch deine Ergebnisse nicht, z.B. in A8-10 sind die gleichen Bestellnummern und das Ergebnis sollte eigentlich 150,04 sein, bei dir kommt 50,70 das ist der Kleinste von den 3en...


  

Betrifft: siehe mein 2. Beitrag, & was ist wohl größer ;-) von: Matthias L
Geschrieben am: 25.09.2019 12:08:28

Hi

was ist größer?
-2 oder -10
Du hast negative Werte in den Zellen!
Willst Du den kleinsten Wert zeigen ( das wäre hier -10! )
musst Du Formel einfach von Max auf Min umstellen
und nicht vergessen als Matrixformel abschließen.

Gruß Matthias


  

Betrifft: oder so nur 1x ... von: Matthias L
Geschrieben am: 25.09.2019 11:56:43

Hi

Tabelle1

 ABC
1Bestellnr - Kopie.1 Preis USD Grösster Wert  
2A18016954-        264,39 -                  264,39
3A18019302-          15,08 -                     15,08
4A18021076-        272,26 -                  272,26
5A18021096-        140,56 -                  140,56
6A18021989-             8,01 -                       8,01
7A18022424-             5,00 -                       5,00
8A18022593-        150,04 -                     50,70
9A18022593-        100,17  
10A18022593-          50,70  
11A18022700-          51,68 -                     51,68
12A18022849-        665,87 -                  665,87
13A19000768-          23,14 -                     23,14
14A19000821-  19.039,02 -                     48,93
15A19000821-          48,93  
16A19001267-          96,00 -                     67,28
17A19001267-          67,28  
18A19001587-          26,80 -                     26,80
19A19001587-        180,17  
20A19001746-          51,75 -                     51,75
21A19001764-          10,38 -                     10,38
22A19001804-          46,97 -                     46,97
23A19001810-    8.518,61 -               8.518,61
24A19001893-          55,53 -                     55,53

Formeln der Tabelle
ZelleFormel
C2{=WENN(ZÄHLENWENN($A$2:A2;A2)=1;MAX(WENN($A$2:$A$24=A2;$B$2:$B$24));"")}
C3{=WENN(ZÄHLENWENN($A$2:A3;A3)=1;MAX(WENN($A$2:$A$24=A3;$B$2:$B$24));"")}
C4{=WENN(ZÄHLENWENN($A$2:A4;A4)=1;MAX(WENN($A$2:$A$24=A4;$B$2:$B$24));"")}
C5{=WENN(ZÄHLENWENN($A$2:A5;A5)=1;MAX(WENN($A$2:$A$24=A5;$B$2:$B$24));"")}
C6{=WENN(ZÄHLENWENN($A$2:A6;A6)=1;MAX(WENN($A$2:$A$24=A6;$B$2:$B$24));"")}
C7{=WENN(ZÄHLENWENN($A$2:A7;A7)=1;MAX(WENN($A$2:$A$24=A7;$B$2:$B$24));"")}
C8{=WENN(ZÄHLENWENN($A$2:A8;A8)=1;MAX(WENN($A$2:$A$24=A8;$B$2:$B$24));"")}
C9{=WENN(ZÄHLENWENN($A$2:A9;A9)=1;MAX(WENN($A$2:$A$24=A9;$B$2:$B$24));"")}
C10{=WENN(ZÄHLENWENN($A$2:A10;A10)=1;MAX(WENN($A$2:$A$24=A10;$B$2:$B$24));"")}
C11{=WENN(ZÄHLENWENN($A$2:A11;A11)=1;MAX(WENN($A$2:$A$24=A11;$B$2:$B$24));"")}
C12{=WENN(ZÄHLENWENN($A$2:A12;A12)=1;MAX(WENN($A$2:$A$24=A12;$B$2:$B$24));"")}
C13{=WENN(ZÄHLENWENN($A$2:A13;A13)=1;MAX(WENN($A$2:$A$24=A13;$B$2:$B$24));"")}
C14{=WENN(ZÄHLENWENN($A$2:A14;A14)=1;MAX(WENN($A$2:$A$24=A14;$B$2:$B$24));"")}
C15{=WENN(ZÄHLENWENN($A$2:A15;A15)=1;MAX(WENN($A$2:$A$24=A15;$B$2:$B$24));"")}
C16{=WENN(ZÄHLENWENN($A$2:A16;A16)=1;MAX(WENN($A$2:$A$24=A16;$B$2:$B$24));"")}
C17{=WENN(ZÄHLENWENN($A$2:A17;A17)=1;MAX(WENN($A$2:$A$24=A17;$B$2:$B$24));"")}
C18{=WENN(ZÄHLENWENN($A$2:A18;A18)=1;MAX(WENN($A$2:$A$24=A18;$B$2:$B$24));"")}
C19{=WENN(ZÄHLENWENN($A$2:A19;A19)=1;MAX(WENN($A$2:$A$24=A19;$B$2:$B$24));"")}
C20{=WENN(ZÄHLENWENN($A$2:A20;A20)=1;MAX(WENN($A$2:$A$24=A20;$B$2:$B$24));"")}
C21{=WENN(ZÄHLENWENN($A$2:A21;A21)=1;MAX(WENN($A$2:$A$24=A21;$B$2:$B$24));"")}
C22{=WENN(ZÄHLENWENN($A$2:A22;A22)=1;MAX(WENN($A$2:$A$24=A22;$B$2:$B$24));"")}
C23{=WENN(ZÄHLENWENN($A$2:A23;A23)=1;MAX(WENN($A$2:$A$24=A23;$B$2:$B$24));"")}
C24{=WENN(ZÄHLENWENN($A$2:A24;A24)=1;MAX(WENN($A$2:$A$24=A24;$B$2:$B$24));"")}
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Gruß Matthias


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 13:08:18

Hi meine 2 lieben Helfer,

1. ihr habt recht, mir ist vorher nicht aufgefallen, dass meine Zahlen negativ sind, 'tschuldigung!
Nun, die Formel ist toll und funzt auch, wenn ich die Daten anpasse...

Dennoch kann ich drunter keine Daten setzen, da es ne Abfrage ist und Daten jedesmal dazukommen

2. @ Oberschlumpf: Deine Makro bzw. der Code wird mir gar nicht erst angezeigt. Wärst Du so lieb und kopierst in mal in einen Beitrag?

DANKE!


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Oberschlumpf
Geschrieben am: 25.09.2019 13:45:17

Hi Sue

der Code sieht so aus:

Option Explicit

Sub sbGroesster()

    Dim lloRow As Long
    
        For lloRow = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
            If Range("A" & lloRow).Value <> Range("A" & lloRow - 1).Value Then
                Range("C" & lloRow).Value = Range("B" & lloRow).Value
            End If
        Next
        
End Sub
Ciao
Thorsten


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 14:24:15

Whoop Whoop es funktioniert prima, hab vielen Dank!


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Günther
Geschrieben am: 25.09.2019 14:23:22

Moin Sue,
wenn du Power Query als Add-In installierst, hast du es gewiss leichter und transparenter ...
=> https://www.herber.de/bbs/user/132202.xlsx

Gruß
Günther


  

Betrifft: AW: nur den grössten Wert einer Bedingung finden von: Sue
Geschrieben am: 25.09.2019 15:16:32

die Lösung find ich auch super.... Danke Günther!


Beiträge aus dem Excel-Forum zum Thema "nur den grössten Wert einer Bedingung finden "