Live-Forum - Die aktuellen Beiträge
Datum
Titel
16.04.2024 15:29:29
Notizen einheitliche Position, Formatierung per Bezugsspalte
16.04.2024 15:16:53
Summen mit verschiedenen Variablen
16.04.2024 14:29:30
Matrixkonstanten als Quelle für Gültigkeit und Steuerelement
16.04.2024 13:54:06
Multiplikation
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1536to1540
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
1536to1540
1536to1540
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

KGrösste mit best. Bereich und mehr

KGrösste mit best. Bereich und mehr
20.01.2017 11:34:01
Michael
Hallo,
folgendes Problem bekomme ich einfach nicht hin.
Ich habe ein Blatt, in dem verschiedene Accounts aufgelistet sind (nicht direkt nacheinander, sondern auch mit Leerzeilen, etc.).
Daneben steht jeweils ein Wert.
Beispiel:
Spalte A
Kategorie
60
60
61
Spalte B
Account
60100
60210
61100
Spalte C
Wert
10
20
30
Nun suche ich folgendes:
In einem anderen Blatt sollen aus jeder Kategorie (Spalte A), die vier grössten absoluten Werte (Absolut = Vorzeichen gelten nicht) untereinander angezeigt werden.
Beisp.
Account Wert
60210 20
Damit brauche ich also für den wert eine KGrösste die nur nach Bereich (hier Kategorie) auswählt UND für den Account sowas wie Sverweis (aber was wenn ein Wert doppelt vorkommt ?).
Könnt ihr mir helfen?
Ich sitze schon ewig an diesem Problem.
Danke

18
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B mit AGGREGAT() und ZÄHLENWENN() ...
20.01.2017 19:33:07
...
Hallo Michael,
... dann noch REST(), WENN() und WENNFEHLER() wenn ich Deine sehr minimalistischen und teils auch nicht ganz eindeutigen Angaben richtig deute.
In meiner Interpretation hab ich zunächst ein par weitere Beispieldaten ergänzt und dafür entsprechend Deiner Sortiervorschrift nicht die vorhandenen Werte sondern lediglich deren absoluten Werte gelistet.
Nachfolgende Formeln werten bis zur Datenzeile 99 aus. Wenn Du mehr hast, ändere die 99 konsequent in allen Formeln auf die max. bei Dir erforderliche (nicht die max. mögliche) Zeilenzahl ab.
Die Formeln B2 und B3 nach rechts kopieren und die Formeln B4:C4 sind nach unten zu kopieren und dann im Block nach rechts nach D:E, F:G ...
Auswert

 ABCDEF
1     
2Kategorie60 61  
3 AccountWertAccountWert 
4 60210206121055 
5 60211206113040 
6 60100106114040 
7   6110030 
8      

Formeln der Tabelle
ZelleFormel
B2=WENN(REST(SPALTE(); 2)=0;WENNFEHLER(AGGREGAT(15;6;Dat!$A$2:$A$99/(Dat!$A$2:$A$99>0)/(ZÄHLENWENN($A2:A2;Dat!$A2:$A99)=0); 1); ""); "")
B3=WENN(B2<>"";Dat!$B1;WENN((A2<>"")*(A3<>""); Dat!$C1;""))
B4=WENN(C4="";"";AGGREGAT(15;6;Dat!$B$2:$B$99/(ABS(Dat!$C$2:$C$99)=C4)/(Dat!$A$2:$A$99=B$2); ZÄHLENWENN(C$4:C4;C4)))
C4=WENN(ZEILE(A2)>4;"";WENNFEHLER(AGGREGAT(14;6;ABS(Dat!$C$2:$C$99)/(Dat!$A$2:$A$99=B$2); ZEILE(A2)); ""))


Dat

 ABC
1KategorieAccountWert
2606010010
3606021020
4616110030
5   
6616114040
76060211-20
86161130-40
9   
10616113025
11   
12   
13616113025
146161130-25
15616111015
16   
17616121055
18   

Gruß Werner
.. , - ...
Anzeige
AW: z.B mit AGGREGAT() und ZÄHLENWENN() ...
22.01.2017 12:52:44
Michael
Hallo Werner,
du hast exakt die Idee dahinter erfasst.
Ich aber leider nicht die Funktionsweise der Formel, bekomme sie nicht umgestellt.
Kannst du mir hier bitte nochmal nachhelfen.
Userbild
Userbild
Ziel ist es, Zelle E10 aus der Ergebnisdatei zu füllen, mit Werten aus der Datengrundlage Spalte R.
Danke vorab.
Mit freundlichen Grüßen
Michael
AW: sicher kann ich auch da helfen, aber ...
22.01.2017 14:54:07
...
Hallo Michael,
... Deine beiden Bildausschnitte müsste ich jetzt relativ aufwendig nachstellen und dazu hab ich momentan wenig Lust. Lade doch mal eine Excel-Datei, die genau die zwei maßgeblichen Tabellenausschnitten beinhalten, hier hoch. Schreibe noch dazu, welche Daten die "Kategorie", welche "Account" und welche "Wert" sind. Dann wird es für mich einfacher, Deinem Wunsch nachzukommen.
Gruß Werner
.. , - ...
Anzeige
AW: sicher kann ich auch da helfen, aber ...
22.01.2017 15:39:16
Michael
Hallo Werner,
vielen, vielen Dank.
https://www.herber.de/bbs/user/110800.xlsx
Die jeweilige Kategorie befindet sich im Blatt Caclulation comments in Spalte B.
Die Accounts sind in diesen Fall nicht die Nummern, sondern die Namen aus Spalte M.
Zu dieser sollen die höchsten Werte gefunden werden.
BDG YTD V
ACt YTD X
BDG FC AB
RR FY AC
FC P04 AE
Ich hoffe das hilft weiter.
Danke,
Michael
AW: sicher kann ich auch da helfen, aber ...
22.01.2017 18:05:34
Michael
Hallo,
hatte vergessen das Kontrollkästchen zu aktivieren.
Hiermit nachgeholt.
Anzeige
AW: dann wird es komplexer ...
22.01.2017 20:16:38
...
Hallo Michael,
... wenn man mit möglichst wenig verschiedenen Formeln auskommen will.
Nachfolgende Formelauswertung setzt voraus, dass Deine auszuwertenden Werte entsprechend wie in V1 und X1 des Tabellenblattes Calculation Comments beschriftet sind und im Auswertungstabellenblatt Spalte A einkopiert werden.
Formeln B2:B3 nach rechts kopieren; B4:C4 nach unten (max bis Zeile 8); dann B4:C8 als Bereich entsprechend weit nach rechts kopieren. Danach Zeile 3 bis 8 markieren, kopieren und nach Zeile 9:13 kopieren und dazu abschließend in A9 den neuen "Kategorie-Text" einschreiben und für die weiteren Kategorien analog weiter nach unten kopieren.
Auswert

 ABCDEFG
1       
2Group 1:60 61 62 
3BDG YTDShort TextBDG YTDShort TextBDG YTDShort TextBDG YTD
4 Office supplies200I&C low-value asset7.000Salaries/ excl. direct salaries1.721.850
5   internal software4.000Accrual for vacation pay #10 - reversal.46.166
6   Vehicle running/main400var. bonus payments15.454
7     Company contribution6.000
8       
9Act. YTDShort TextAct. YTDShort TextAct. YTDShort Text 
10 Office supplies122I&C low-value asset9.300Salaries/ excl. direct salaries1.549.235
11   Investment low value4.295Accrual for vacation pay #10 - reversal.71.445
12   internal software1.000Exp. share-based pay39.974
13   Vehicle running/main400Allow.f.public holid10.951
14       

Formeln der Tabelle
ZelleFormel
B2=WENN(REST(SPALTE(); 2)=0;WENNFEHLER(AGGREGAT(15;6;'Calculation Comments'!$B$1:$B$399/('Calculation Comments'!$B$1:$B$399>0)/(ZÄHLENWENN($A2:A2;'Calculation Comments'!$B1:$B399)=0); 1); ""); "")
C2=WENN(REST(SPALTE(); 2)=0;WENNFEHLER(AGGREGAT(15;6;'Calculation Comments'!$B$1:$B$399/('Calculation Comments'!$B$1:$B$399>0)/(ZÄHLENWENN($A2:B2;'Calculation Comments'!$B1:$B399)=0); 1); ""); "")
B3=WENN(B$2<>"";'Calculation Comments'!$M$6;WENN((A$2<>"")*(A3<>""); $A3;""))
B4=WENN(C4="";"";INDEX('Calculation Comments'!$M:$M;AGGREGAT(15;6;ZEILE('Calculation Comments'!$M$1:$M$399)/(ABS(INDEX('Calculation Comments'!$A$1:$AZ$399;;VERGLEICH(VERWEIS(9;1/($A$1:$A4<>""); $A:$A); 'Calculation Comments'!$1:$1;)))=C4)/('Calculation Comments'!$B$1:$B$399=B$2); ZÄHLENWENN(INDEX(C:C;VERWEIS(9;1/($A$1:$A4<>""); ZEILE(A$1:A4))):C4;C4))))
C4=WENN(ZEILE()-VERWEIS(9;1/($A$1:$A4<>""); ZEILE(A$1:A4))>4;"";WENNFEHLER(AGGREGAT(14;6;ABS(INDEX('Calculation Comments'!$A$1:$AZ$399;;VERGLEICH(VERWEIS(9;1/($A$1:$A4<>""); $A:$A); 'Calculation Comments'!$1:$1;)))/('Calculation Comments'!$B$1:$B$399=B$2)/(INDEX('Calculation Comments'!$A$1:$AZ$399;;VERGLEICH(VERWEIS(9;1/($A$1:$A4<>""); $A:$A); 'Calculation Comments'!$1:$1;))<>0); ZEILE()-VERWEIS(9;1/($A$1:$A4<>""); ZEILE(A$1:A4))); ""))

Gruß Werner
.. , - ...
Anzeige
AW: dann wird es komplexer ...
23.01.2017 12:10:14
Michael
Hallo Werner,
leider scheint meine Nachricht nicht gepostet zu sein.
Danke für deine Zeit und deine Hilfe.
Leider bekomme ich die Formel nicht auf mein Sheet angewandt (in der Datei, Blatt "Comments for CCR".
Hier muss sich die Spalten E, O und Y auf die höchsten Werte beziehen.
Bsp. Es wird erkannt, dass die Account "Software" am meisten erbracht hat. Die anderen Spalten ziehen sich dann fast wie mit SVerweis nach.
Ich kann leider deine Formel nicht nachvollziehen, muss mir dafür mal richtig Zeit nehmen. Hut ab!
Kannst du dir bitte mal das Sheet anschauen deine Formel anpassen.
Danke vielmals
Anzeige
AW: dann wird es komplexer ...
23.01.2017 13:49:34
Michael
Hallo!
Ok, folgende Probleme habe ich rausgefunden.
Die Kategorie erkennt nur Zahlen. Sobal ein Text vorkommt wie "S66" oder "60 / 61" funktioniert es nicht. Ich glaube aber, dass ich es schlecht schlecht erklärt habe.
Anbei nochmal eine genauere Erklärung.
Userbild
Danke
AW: wenn Deine Kategorien keine Zahlen sind ...
23.01.2017 18:54:29
...
Hallo Michael,
... dann muss meine gestrige Formel in B2 nur etwas umgeschrieben werden:
So:
=WENN(REST(SPALTE();2)=0;WENNFEHLER(
INDEX('Calculation Comments'!$B:$B;AGGREGAT(15;6;ZEILE('Calculation Comments'!$B$7:$B$399)/
('Calculation Comments'!$B$7:$B$399>0)/(ZÄHLENWENN($A2:A2;'Calculation Comments'!$B7:$B399)=0)
;1));"");"")
Gruß Werner
.. , - ...
Anzeige
AW: Deine bisherigen Angaben lauteten anders ...
23.01.2017 18:46:19
...
Hallo Michael,
... gestern 15:39 schriebst Du:
Die jeweilige Kategorie befindet sich im Blatt Caclulation comments in Spalte B.
Die Accounts sind in diesen Fall nicht die Nummern, sondern die Namen aus Spalte M.
Dafür hatte ich ein separates neues Auswertungstabellenblatt (welches ich "Auswertung" genannt habe) mit den von mir aufgezeigten Formeln (für die jeweils bis zu 4 größten Werte) erstellt. Natürlich kann man die Formeln auch so umstellen, dass Sie in Zellen Deines Tabellenblattes "Comments for CCR" zugeordnet werden, wenn Du zu diesem für mich nachvollziehbare eindeutige Angaben vornimmst.
Gruß Werner
.. , - ...
Anzeige
besser erklärt
24.01.2017 11:28:06
Michael
Hallo Werner,
es tut mit leid, wenn ich mich da unklar ausdrücke.
Ich versuche es daher anders:
Das Blatt "Comments for CCR" soll ausgefüllt werden. Datenquelle ist "Calculation foc Comments".
In "Comments for CCR" sind mehrere Bereiche zu sehen, eines für jede Kategorie die ich benötige.
Verbleiben wir bei dem Bereich der Kategorie "60 / 61".
Schritt 1:
Für diese Kategorie (B8) möchte ich in Spalte E10:E13 die vier höchsten Werte absteigend sortiert haben.
Die Kategorie befindet sich im Blatt "Calculation for Comments" in C8:C400.
Der Datenwert, aus dem gesucht werden muss, in X8:x400.
Schritt 2:
Nachdem die grössten Werte nun vorhanden sind, möchte ich in C10:C13 den jeweiligen Account sehen, damit ich weiß, wozu der Wert aus E10:E13 gehört.
Der Account ist im Blatt "Calculation for Comments" in M8:M400.
Schritt 3 bis 6:
Nun sind die höchsten Werte bekannt und der dazu passende Account.
Ich benötige nun die Vergleichszahlen, damit ich diesen Wert deuten kann.
"Comments for CCR" V10:V13 zu finden in "Calculation for Comments" V8:V400
"Comments for CCR" G10:G13 zu finden in "Calculation for Comments" AB8:AB400
"Comments for CCR" H10:H13 zu finden in "Calculation for Comments" AC8:AC400
"Comments for CCR" I10:I13 zu finden in "Calculation for Comments" AE8:AE400
Meiner Meinung nach gleich Formel wie Schritt 2, nur mit anderer Spaltensuche.
Genauer kann ich es leider nicht beschreiben.
Danke für deine Geduld
FG,
Michael
Anzeige
AW: besser vielleicht als Infos in der Datei ...
24.01.2017 16:24:04
...
Hallo Michael,
... momentan hab ich wenig Zeit.
Wenn Du magst kannst Du mir aber eine Mail mit der Datei und darin entsprechenden Infos senden, falls der thread zwischenzeitlich deaktiviert im Archiv landet.
Meine Mailadresse bekommst Du, wenn Du in eine leere Standardexcelzelle folgende Formel schreibst: =WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.")
Solltest Du mir schreiben, dann schreibe in den Betreff bitte: Herbers Excel Forum, thread "KGrösste mit best. Bereich und mehr ..." und schreibe hier im thread, wenn Du die Mail abgesendet hast.
Und kopiere in den Text folgenden Link: https://www.herber.de/forum/archiv/1536to1540/t1536289.htm
Gruß Werner
.. , - ...
Anzeige
AW: besser vielleicht als Infos in der Datei ...
24.01.2017 17:05:34
Michael
Hallo Werner,
Mail ist abgesendet.
Schade, war für heute meine letzte Hoffnung, morgen muss ich präsentieren.
Aber für nächsten Monat kann ich es dann gut gebrauchen.
Nun erstmal Handarbeit.
Danke!
AW: Mail ist zwar angekommen, aber ...
24.01.2017 19:18:38
...
Hallo Michael,
... die Mail beinhaltet nur den Link den ich Dir angegeben habe aber keine an gehangene Datei.
Gruß Werner
.. , - ...
AW: Mail ist zwar angekommen, aber ...
24.01.2017 19:21:21
Michael
Hallo Werner,
ist nochmals unterwegs.
Cooler Trick übrigens mit der Email-Adresse.
AW: nun ist es für mich wieder relativ einfach ...
25.01.2017 19:20:15
...
Hallo Michael,
... nachdem ich eben Deine Mail mit der Datei und der darin enthaltenen Information gelesen habe. Danach gilt Deine Aussage vom 22.01. "Die jeweilige Kategorie befindet sich im Blatt Caclulation comments in Spalte B" nicht, sondern nach Deiner neue Aussage gelten die Kategorien in Spalte C.
Damit müssen jetzt lediglich meine bereits eingestellten früheren Formeln angepasst werden.
In E10: 
=WENN(ZEILE(A1)>4;"";
WENNFEHLER(AGGREGAT(14;6;ABS('Calculation Comments'!$X$2:$X$399)/
('Calculation Comments'!$C$2:$C$399=$B$8)/('Calculation Comments'!$X$2:$X$399>0);ZEILE(A1));""))
in C10:
=WENN(E10="";"";INDEX('Calculation Comments'!M:M;AGGREGAT(15;6;
ZEILE('Calculation Comments'!$C$2:$C$399)/(ABS('Calculation Comments'!$X$2:$X$399)=E10)/
('Calculation Comments'!$C$2:$C$399=$B$8);ZÄHLENWENN(E$10:E10;E10))))
Die Formeln in G10:I10 und D10 analog wie in E10 anpassen (also nur 'Calculation Comments'!$X$2:$X$399 auf die entsprechenden Spaltenbereiche abändern).
Gruß Werner
.. , - ...
AW: nun ist es für mich wieder relativ einfach ...
26.01.2017 10:09:20
Michael
Hallo Werner,
danke, deine Formel macht das schon gut.
ABER...die Kategorie "S66" wird nicht erkannt, bzw. die Werte nicht berechnet.
Eine andere Sache, welche ich ausführlicher hätte erklären müssen:
Der Sinn nach der Suche nach absoluten Zahlen ist der, dass die größten Einflüsse unabhängig der Vorzeichens absteigend sortiert werden sollen. Aber der Wert welcher wiedergegeben werden soll, ist natürlich der Richtige.
Bsp:
+1000
-1200
+ 700
Sortiert werden würde:
+1000
+ 700
-1200
Sortiert werden soll:
1200
1000
700
Gezeigt werden soll:
-1200
+1000
+ 700
Wenn ich eine Kategorie "Total" hätte, also die größten Werte aus jeder Kategorie zeigen wollen würde,könnte ich dann einfach "größer als gleich "" " eintragen?
Danke und Gruß
Michael
AW: dann wird es wieder komplexer, trotzdem ...
26.01.2017 20:10:03
...
Hallo Michael,
... kann man auch dies mit {}-freien Formeln realisieren. Damit wird sowohl die Kategorie S66 erkannt als auch die Werte absolut sortiert aber mit Vorzeichen gelistet und wenn Du in B8 "total" schreist werden alle Kategorien ausgewertet.
Folgende Formel in E10:
=WENN(ZEILE(A1)>4;"";
WENNFEHLER(INDEX('Calculation Comments'!X:X;AGGREGAT(15;6;ZEILE($E$2:$E$339)
/('Calculation Comments'!$C$2:$C$339=WENN(B$8="total";'Calculation Comments'!$C$2:$C$339;B$8))
/(ABS('Calculation Comments'!$X$2:$X$1339)+ZEILE(E$2:E$339)%%
=AGGREGAT(14;6;(ZEILE(E$2:E$339)%%+ABS('Calculation Comments'!$X$2:$X$339))
/('Calculation Comments'!$C$2:$C$339=WENN(B$8="total";'Calculation Comments'!$C$2:$C$339;B$8))
/(ABS('Calculation Comments'!$X$2:$X$339)0);ZEILE(A1)));1));""))
und in C10:
=WENN(E10="";"";INDEX('Calculation Comments'!M:M;AGGREGAT(15;6;ZEILE($C$2:$C$399)
/('Calculation Comments'!$X$2:$X$399=E10)/('Calculation Comments'!$C$2:$C$399=WENN(B$8="total";
'Calculation Comments'!$C$2:$C$339;B$8));ZÄHLENWENN(E$10:E10;E10))))

Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige