Erkennen einer bestimmten Anzahl an Zeilen zwischen zwei Textzeilen

[moonchild]

Hi!

Ich habe folgendes Thema: und zwar ziehe ich mir mittels einer Power Query Daten aus einem Excel File im Sharepoint. Diese Daten aktualisieren sich täglich. Und daraus möchte ich eine Auswertung machen. 
Ich habe hierbei immer eine Zeile mit "KW01" und dann direkt darunter mehrere Zeilen (zufällige Anzahl) mit Werten. Dann geht es weiter mit "KW02" und wieder eine zufällige Anzahl an Werten. Ich möchte nun aus diesen Werten für jede "KW" den Mittelwert errechnen. Und ich möchte, dass sich diese Formel dann auch weiter, mit kontinuierlich hinzukommenden Daten, automatisch weiter generiert. Mein Problem ist, dass die Anzahl an Zeilen immer zufällig ist. Man bräuchte also irgend eine Formel die die Werte zwischen den KWs erkennt. Ich hoffe ihr könnt mir weiterhelfen.



LG, Moonchild

[steve1da]

Hola,

mit Vergleich() kann man die Position eines Textes in einer Spalte ermitteln. Dies kann dann in Mittelwert() zusammen mit Index() verbastelt werden.
Mehr kann man aufgrund des Bildchens nicht sagen.

Gruß,
steve1da

[moonchild]

Hi!

Danke für das schnelle Input. Ich habe versucht mit diesen Funktionen zu basteln, komme aber leider nicht auf das gewünschte Ergebnis. Ich versuche es nochmal mit einem Bild, vielleicht erklärt es das besser
Ich möchte die Summen (also doch nicht die Mittelwerte - da habe ich mich vertan) nicht jedes Mal händisch berechnen müssen, sondern hätte gerne eine Formel die automatisch erkennt, von wo bis wo sie mir eine Summe berechnen muss (nämlich für die jeweilige Kalenderwoche). Die Anzahl an Zeilen ist jedes mal unterschiedlich. Als Anhaltepunkt habe ich nur eine eigene Spalte mit den Kalenderwochen.

[steve1da]

Lad doch bitte eine Exceldatei hoch - man kann leider nicht erkennen wie die KW-Angaben genau erzeugt werden.

[moonchild]

Hi!

Hier die Datei. Momentan berechne ich die Spalte K mit Summewenns. Das muss ich aber für jede hinzukommende KW händisch hinzufügen. Das hätte ich gerne automatisiert. Und dann hätte ich das natürlich auch noch gerne im Diagramm so ausgespuckt. Das kopiere ich momentan auch noch händisch zusammen...

LG, Katja

[steve1da]

Hola,

ok, es gibt einiges zu tun
Als erstes musst du die Bezeichnungen der KW angleichen. Im Blatt x3x stehen Sie mit Leerzeichen in Spalte A, beim Diagramm ohne. Ich habe für mein Beispiel die Leerzeichen im Blatt x3x entfernt. Dann musst du die #NV aus den Spalte bekommen. Dazu packst du Wennfehler() um die Formeln, also:

Code:

=WENNFEHLER(F2+G2+H2+I2;"")

Wenn noch andere Spalten summiert werden sollen, musst du das in jeder Spalte machen, ansonsten reicht Spalte J.
Dann kommt in K6 folgende Formel:

Code:

=WENNFEHLER(WENN(A6<>"";SUMME(INDEX(J:J;VERGLEICH(A6;A:A;0)):INDEX(J:J;VERGLEICH("KW"&TEXT(RECHTS(A6;2)+1;"00");A:A;0)));"");SUMME(INDEX(J:J;VERGLEICH(A6;A:A;0)):INDEX(J:J;9999)))

Die ziehst du nach unten.
Wenn der Aufbau in jeder der x1x, x2x, x3x.....Tabellen gleich ist, das Ergebnis also immer in Spalte K steht, kommt folgende Formel in Diagramme!B3:

Code:

=SVERWEIS($A3;INDIREKT(B$2&"!A:K");11;0)

Die ziehst du nach rechts und nach unten.

Gruß,
steve1da

[moonchild]

Hi!

Riesen Dank schonmal für die Hilfe. Es geht schonmal in die richtige Richtung. 

Die zweite Formel übersteigt definitiv meine Excel Kenntnisse. Sehr kompliziert....

Ich habe es ausprobiert. Allerdings kommt jetzt im Feld K nicht die richtige Summe raus. Die richtige Summe hab ich hier in Spalte N hinterlegt. Nochmal im Anhang. Könntest du mir sagen woran es liegt?

LG, Katja

[steve1da]

Hola,

Könntest du mir sagen woran es liegt?

klar.

Ich habe für mein Beispiel die Leerzeichen im Blatt x3x entfernt.

Das hast du nicht gemacht.

Gruß,
steve1da

[moonchild]

Hallo steve1da,


danke für dein Input! Es funktioniert!


Leider ist das Excel zusätzlich noch ein wenig komplizierter, weil zb x1x, x2x; x3x zusammengefasst sind. Deine Formel funktioniert, wenn tatsächlich jedes xXx ein eigenes Tabellenblatt hat. Ist leider aber nicht der Fall.

Weil da allerdings auch welche zusammengefasst sind, kommen auch mehrere KWs vor und somit passt es dann nicht mehr zusammen.  
Gibt es denn dafür noch eine Lösung? Ich beziehe meine Daten über PowerQuery aus einem Excel File, wo ich leider die Struktur nicht ändern kann...


LG, Katja

[GMG-CC]

Ich beziehe meine Daten über PowerQuery aus einem Excel File

… und leider ist davon in deiner Muster-xlsx nichts zu erkennen. Power Query kann viel, viel mehr als Daten zu importieren! Und eventuell ist deine Auswertung auch damit machbar…