Top 5 Statistik mit Verweis

[m6joe]

Hallo zusammen!

Ich erfasse in Excel eine Jahresstatistik für Umsätze, Auslastung usw.
Pro Jahr wird ein eigenes Tabellenblatt verwendet, die Blätter sind nach Jahreszahl benannt.

In Zelle B1 hole ich mir den Namen der jew. Tabelle als Überschrift.
Zelle E21 steht der Gesamt-Jahres-Umsatz.

In einem anderen Blatt erhalte ich mit =KGRÖSSTE('2006:2017'!$E$21;1) den Top-Wert der letzten Jahre, in diesem Fall fällt dieser ins Jahr 2014 und nun sollte hier noch ein Verweis auf das dazugehörige Jahr hin.


Habe hier schon diverse Formeln probiert, bekomme es nicht und nicht hin, oder es wird mir persönlich schlichtweg zu komplet (INDEX(), MAX().
Auch diverse Vorlagen zum Download helfen mir beim Verstehen nicht weiter...


Vielleicht kann mir hier von Euch jemand unter die Arme greifen?


Danke schon mal, Grüße Joe

[steve1da]

Hola,

Pro Jahr wird ein eigenes Tabellenblatt verwendet

warum? Gleichartige Daten gehören auf ein Blatt - dann klappts auch mit den Auswertungen

Gruß,
steve1da

[m6joe]

Ich glaube, dass das nicht die vorgeschobene Lösung meiner Frage ist, trotzdem Danke für Deinen Hinweis!

Meine Statistik umfasst hier schon noch einige weitere Details, das ist ein Konstrukt basierend auf alle möglichen Zahlen eines ganzen Geschäftsjahres und hier wird innerhalb des Jahres ja schon auch noch nach Monaten sortiert...

Es ist augenscheinlich, dass derartiges auf einem einzigen Tabellenblatt schon nach wenigen Jahren extrem aufgebläht wird.

Und eine funktionierende Formel wird´s wohl geben...

[BoskoBiati]

Hallo,

bei ca. 1Million Zeilen in einer Tabelle dürfte es wohl das geringere Übel sein, alles in ein Blatt zu machen. Wie Du ja schon gemerkt hast, werden die Formeln sehr komplex und irgendwann nicht mehr nachvollziehbar, zudem werden mit Sicherheit dann Matrix- oder voltile Funktionen zum Einsatz kommen, die dann die Datei richtig aufblähen und verlangsamen!

Und eine funktionierende Formel wird´s wohl geben...

Vielleicht!

[m6joe]

Danke, das leuchtet schon auch ein!

Wie sieht es damit aus die benötigten Daten einfach in ein neues Tabellenblatt zu kopieren und dort auszuwerten?

[WillWissen]

Hi Joe,

stell doch bitte (d)eine (Beispiel)Datei vor. Etwa 30 relevante Datensätze dürften ausreichend sein, sensible Daten bitte anonymisieren. Hier kannst du nachlesen, wie das Hochladen geht:  http://www.clever-excel-forum.de/forum-2.html

[m6joe]

Danke an die Beitragskommentierer!

Hier wie gewünscht zur Ansicht meine Datei - verkleinert, aber soweit betriebsfähig.

Wie gesagt: im Tab "Info" möchte ich gerne ein paar Dinge auf einer Ansicht platzieren - in diesem Fall nun eine Top-3-Liste
Mit KGRÖSSTE bekomme ich ja schon mal die Zahlen in geordneter Reihenfolge, jetzt soll rechts davon noch das dazugehörige Jahr drin stehen.

Am besten Ihr seht Euch das selbst an, ich scheitere hier an INDEX, VERGLEICH, MAX und SVERWEIS.

Vielleicht kanns ja einer besser als ich

[Jockel]

Hallo, zunächst solltest du in den Tabellenblättern (2005, 2006 und 2007) aus B1 durch Mulpikation mit 1 Zahlen machen...

also so..:

Code:

=TEIL(ZELLE("Dateiname";$B$1);FINDEN("]";ZELLE("Dateiname";$B$1))+1;4)*1

 
Dann geht folgende Formel (für dieses Beispiel )..:

Arbeitsblatt mit dem Namen 'INFO'
	B	C	D
3	Top 3 Nächtigungszahlen im Jahr
4
5	5286	2007
6	5056	2005
7	4088	2006

Zelle	Formel
B5	=KGRÖSSTE('2005:2007'!$E$21;ZEILE(A1))
C5	{=SUMME(SUMMEWENN(INDIREKT("200"&{5;6;7}&"!e21");B5;INDIREKT("200"&{5;6;7}&"!B1")))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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

[m6joe]

Gleich noch eine Frage:

Mit dem Namensmanager kann ich den Zeitraum für KGRÖSSTE globalisiert definieren, zb: ='2005:2017'!$E$21.
Wäre es auch möglich die o.g. INDIREKT Sequenz in so etwas zu verpacken?

Dies wäre für das 1. Kürzel:
INDIREKT("20"&{"05";"06";"07";"08";"09";"10";"11";"12";"13";"14";"15";"16";"17"}&"!e21"), das

für das 2. Kürzel:
INDIREKT("20"&{"05";"06";"07";"08";"09";"10";"11";"12";"13";"14";"15";"16";"17"}&"!B1")

DAS nämlich lässt mich Excel nicht machen...

[Jockel]

Hallo, hattest du meinen Hinweis in den Tabellen Blättern 2005 bis 2007 beachtet... Ich würde die Tabellennamen in extra Zellen schreiben und dann geht (auch)..:

Arbeitsblatt mit dem Namen 'INFO'
	B	C	D	E	F	G	H
3	Top 3 Nächtigungszahlen im Jahr
4							2005
5	5286	2007	2007				2006
6	5056	2005	2005				2007
7	4088	2006	2006
8
9

Zelle	Formel
C5	{=SUMME(SUMMEWENN(INDIREKT("20"&{"05";"06";"07"}&"!E21");B5;INDIREKT("20"&{"05";"06";"07"}&"!B1")))}
D5	{=SUMME(SUMMEWENN(INDIREKT(MTRANS($H$4:$H$6)&"!E21");B5;INDIREKT(MTRANS($H$4:$H$6)&"!B1")))}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

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