Office: (Office 2013) Kombination Kgrößte, Index Vergleich mit Wenn-Bedingung

Moin liebe Excelliebhaber,

mir stellt sich folgende Herausforderung:

Die Verknüpfung von KGrößte bzw. KKleinste mit dem Index-Vergleich unter Berücksichtigung von WENN-Bedingungen.

In der angehängten Excel verzweifel ich an der Formel für Zelle D12-21 und C12-21. Absteigend sortiert sollen in Spalte D (D12= größter Wert, D13=2.größter Wert usw.) die Werte aus dem folgenden Tabellenblatt ("Daten15") dargestellt werden, aber nur wenn in Spalte B = Channel steht. Zudem Werte mehrfach vorkommen, diese sollen aber jeweils dargestellt werden.

Zu den jeweils ausgewiesenen Werten in D sollen in C, unter Berücksichtigung der Dopplungen, die Namen zu den Zahlen stehen.

Ist das Problem soweit verständlich? Bei mir klappte die Kombination von Kgrößte, der Wenn-Bedingung und dem Index-Vergleich nicht *stupid

Bin für jeden Hinweis dankbar!

:)

 

Hallo Name?,

anbei Deine Datei mit den gewünschten Formeln in Spalte C und D.

Achtung in Spalte D sind Matrixformeln!!! Geschweifte Klammern nicht mit eintippen, sondern durch STRG+SHIFT+ENTER erzeugen. Das gilt bei jedem reinklicken in die Zellformel!

LG Ingo

 

Wow, 1.000 Dank Ingo.

Es ist vlt. etwas aufwendig und nervig, aber die Funktion "ZEILE(Daten!$C$6:$C$73)/10^6);ZEILE(A6)))" verstehe ich leider nicht :-(.

Könntest du kurz erklären, wie die Funktionsweise der Befehle läuft?

Danke und lieben Gruß,

Tomasser

 

Hallo Tomasser,

damit ich die jeweiligen Reichweiten bei Gleichheit auch unterscheiden kann, ermittele ich mit "ZEILE(Daten!$C$6:$C$73)" lediglich die Zeilennummer in der die gesuchte Reichweite steht. Diese Zeilennummer addiere ich zur Reichweite hinzu. Damit die Zeilennummer aber nicht die eigentliche Reichweite maßgeblich verändert, nehme ich nur ein Millionstel der Zeilennummer ("/10^6"). Dadurch erhöht sich die Reichweite, die ich in Spalte D ausgebe um ein Millionstel der Zeilennummer in der die Reichweite in den Quelldaten steht. Optisch, da nur ganze Zahlen in Spalte D angezeigt werden, ändert sich nichts, aber genial wird es, wenn ich nun den passenden Sender rückwärts wieder suchen will (meine Formel in Spalte C). Nun schaue ich mir nur noch den Rest hinter dem Komma in Spalte D an. Mit "RUNDEN(REST(D12;1)*10^6" erhalte ich dann exakt die Zeilennummer in der die Reichweite aus Spalte D in den Quelldaten stehen soll, ohne sie noch suchen zu müssen. So habe ich kein Problem damit, wenn es die Reichweite mehrmals gibt. Und mit "INDEX(Daten!A:A;........)" lasse ich mir dann den Sendernamen ganz einfach dazu ausgeben.

LG Ingo

Edit: Ach, und das "ZEILE(A6)" gehört zu KGRÖSSTE. In diesem Fall das sechstgrößte. Da dies der Formelteil aus D17 ist und Du dort ja den Sender mit der sechstgrößten Reichweite suchst.

 

Hallo Ingo,

danke für die ausführliche Antwort, ist wirklich extrem ausführlich beschrieben von dir und auch sehr trickreich ;-)

Da es bisher nicht funktionierte bei mir, die hoffentlich letzte Frage zu diesem Thema: Die Ausweisung von KGrößte bezieht sich ja aktuell den Abgleich von Spalte B ("Channel") und Ausweisung des passenden Wertes aus Spalte C (Q4 2015) Angenommen ich möchte die Kgrößte aus Spalte D, sprich die Werte aus Q3 2015, reicht es scheinbar nicht, die Ursprungsformel

=KGRÖSSTE(WENN(Daten!$B$6:$B$73="Channel";Daten!$C$6:$C$73+ZEILE(Daten!$C$6:$C$73)/10^6);ZEILE(A1))

auf

=KGRÖSSTE(WENN(Daten!$B$6:$B$73="Channel";Daten!$D$6:$D$73+ZEILE(Daten!$D$6:$D$73)/10^6);ZEILE(A1))

anzupassen. Was muss ich bei der Bezugsänderung noch beachten? Scheinbar verstehe ich die Funktionsweise der Formel noch nicht ganz -.-

LG und vielen Dank,

Tomasser

 

Hallo Tomasser,

es regt mich immer wieder von Neuem auf, dass Threadstarter von mir meine doch ziemlich komplizierten Formeln erklärt haben wollen, und diese dann trotz ausführlicher Erklärung meinerseits, dann doch nicht verstehen. Dann war doch meine Mühe für die Tonne. *Seufz*
Aber Du scheinst nicht verstanden zu haben, dass Du es eigentlich (fast) verstanden hast.
Der Teil "ZEILE(Daten!$C$6:$C$73)" ist nur dafür da, die Trefferzeilennummer auszugeben. Da ich dafür irgendeine Spalte angeben musste, steht hier zufälligerweise C. Dies anzupassen, macht keinen Mehrwert. Hier könnte auch A oder Z stehen. D ist also auch nicht falsch, aber unnötig anzupassen.
Den ersten Teil auf "...el";Daten!$D$6:$D$73+ZEI...." anzupassen, reicht also völlig aus.

Warum nun diese Formel bei Dir nicht richtig funktioniert, da muss ich also nun raten. Ich vermute mal, dass Du meinen Hinweis in D25 zur Matrixformel nicht beachtet hast. Ansonsten hilft es, wenn Du die angepasste Datei hier wieder hochlädst. Dann werde ich den Fehler garantiert finden.

LG Ingo

P.S.: Sorry für den ersten Abschnitt, aber ich musste meinen Frust herunterschreiben. Du darfst gerne auch weiterfragen.

 

Hallo Superingo2,

als "Nicht-Autodidakt" bin ich leider auf Anhieb nicht durch deine Formel gestiegen und brauchte noch einmal klärende Worte. Danke für die Geduld.

Nun, das "Problem" liegt, wie so häufig in den Daten selbst. In der neuen Verweisspalte befinden sich leider auch nicht nummerische Werte ("-"), die über kgrößte nicht gedeutet werden können. Das war das Problem und mir leider nicht bekannt. Wenn ich die "-" durch 0en ersetze, funktioniert die angepasste Formel einwandfrei. In Zukunft werde ich mir die Daten vorher genauer anschauen und dann ggf. auf Sonderfälle gleich hinweisen. So bleibt allen viel Zeit ersparrt.

Immerhin lag es nicht an "STRG+SHIFT+ENTER" ;-)

Vielen Dank dir und BG,

Tomasser

 

Hallo Tomasser,

selbst ich kann meine eigenen Formeln nicht immer auf Anhieb verstehen. Daher ist das kein Makel (und ich möchte sogar behaupten, dass JEDER Excelprofi nicht alle Formeln auf Anhieb versteht). Toll ist es, wenn Du sie überhaupt irgendwann verstehst. Und zwar richtig verstehst, so dass Du sie anpassen oder sogar erweitern kannst.
Dein Problem mit dem "-" hätte ich ja selber sehen können, habe ich aber leider nicht ausprobiert. Sorry.
Dieses Problem kann man aber auch formeltechnisch abfangen (ohne die Daten anzupacken) mit dieser Formel in D12:

PHP:

         {=KGRÖSSTE(WENN((Daten!$B$6:$B$73="Channel")*(Daten!$D$6:$D$73<>"-");Daten!$D$6:$D$73+ZEILE(Daten!$C$6:$C$73)/10^6);ZEILE(A1))}


 

Hallo Ingo,

danke für die offenen Worte hinsichtlich des Verstehens ;-) und danke für den Abfangcode, klappt natürlich.

Lieben Gruß,

Tomasser

 

hllo, ich bekomme die selben (sichtbaren) ergebnisse ohne MatrixFormel-Abschluss mit AGGREGAT() ..:

Arbeitsblatt mit dem Namen 'Analyse'
*	B	C	D
10	Channel	*	*
11	#	Sendername	Höchste Reichweite in Q4 2015 (Werte)
12	1	ANTENNE BAYERN antenne (simulcast)	5909705
13	2	HIT RADIO FFH Simulcast	2477524
14	3	89.0 RTL Gesamt	1768385
15	4	bigFM Deutschlands biggste Beats	1429033
16	5	Klassik Radio	1290733
17	6	planet radio Simulcast	1098428
18	7	104.6 RTL Berlins Hit-Radio	1032459
19	8	JAM FM	815418
20	9	89.0 RTL	664253
21	10	antenne 1 simulcast	637106

Zelle	Formel
C12	=INDEX(Daten!$A:$A;AGGREGAT(15;6;ZEILE($A:$A8)/((Daten!$B:$B=$B)*(Daten!$C:$C=D12));1))
D12	=AGGREGAT(14;6;Daten!$C:$C/(Daten!$B:$B=$B);B12)

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

 

Moin Frank Furter,

vielen dank, habe deine Formel ausprobiert, klappt prinzipiell!

Das Problem ist allerdings der Umgang mit identischen Werten. Hier gibt deine Formel für Spalte C lediglich immer den ersten Treffer aus und und nicht etwa den 2. identischen Wert, 3. identischen Wert usw. aus.

Lässt sich die Formel entsprechend modifizieren?

BG,

Tomasser

 

hallo, deine frage versehe ich nicht, ich hstte die gleichen sichtbaren ergebnisse wie Ingo - nur meine waren echt...

 

Hallo Frank,

deine Formel war im Prinzip auch richtig, nur leider wird im Fall von mehrfach identischen Werten immer nur das erste Suchergebnis ausgewiesen und damit wie die angehängte Grafik zeigt, leider auch dreimal der selbe Sendername, nämlich der zuerste gefundene und eben nicht auch der 2. ...3. etc.

Klärt's das?

BG,

tomasser

 

hallo, ich habe da andere ergebnisse als auf deinem bild... welche erwartest du denn und sind Ingo's ergebnisse denn richtig..? dann nimm die formel...