Tabellenkalkulation
Von: zеnоgrа, 16.3.2011 16:40 Uhr
Liebe/-r Experte/-in,

Diese Anfrage habe ich schon mal an die Experten im Forum gerichtet. Leider gab es keine Lösung. Deshalb ein neuer Versuch.
Hier mein Problem: Ich habe im Excel eine Tabelle mit 154000 Zeilen und 20 Spalten.
Jede Zeile repräsentiert ein Messergebnis.
4 Zeilen entsprechen 1 Minute Messdauer
240 Zeilen entsprechen 1 Stunde Messzeit

Ich möchte nun jeweils 240 Zeilen (1Stunde) gruppieren, aus den Werten den Mittelwert berechnen, und die Stundenwerte später in eine Grafik überführen.

Mit den normalen Bordmitteln von Excel 2010 funktioniert das nicht. VBA Programmierung ist nicht meine Welt.

Deshalb nun meine Anfrage an das Forum hier.

Auf jeden Fall mein Dank im voraus an alle.



  1. Antwort von mаrkuѕ 1
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hy zenogra,
    du kannst dir pro Zeile einen gleichen Wert errechnen (STUNDE, auf eindeutigkeit achten) und dann nach diesem Wert die TEILERGEBNISSE einschalten. (unter Funktionsleite DATEN)
    Dies sollte dich deutlich weiter nach vorn bringen.

    Alternativ die gesamte Tabelle markieren (mit der neuen Spalte STUNDE) und dann Einfügen PivotTable.

    dann kannst du dort alle Werte analysieren

    gruss Markus
    3 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo Markus

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  2. Antwort von ΤоѕһіΜ 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo,

    folgender Vorschlag:

    1.
    z.B. in Spalte A - 2 neue Spalte einfügen.

    2. Durchnummiereren lassen also mit 1.2.3. ....

    in Spalte B (oder nach der nummerierung) Formel einfügen: =REST(A1;240) und ebenfalls runterziehen.

    es müssten nun überall NULLer erscheinen wo die 240 vorhanden sind.

    Ich weiss jetzt nicht, wie die Tabelle aufgebaut ist, aber mit der Funktion

    =SUMMEWENN(B16:C19;1;C16:C19)

    sollten die Werte vorkommen.


    Good Luck...

    ----------------------------------------------
    www.stallwanger.net | process and controlling
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo ToshiM
      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  3. Antwort von Αnnе 1
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo zenogra

    Also ehrlich gesagt verstehe ich dein Problem nicht so wirklich. Was hindert dich daran am Ende der 240 Zeilen den Mittelwert auszurechnen?
    Den Trick mit dem Doppelklick auf das schwarze Quadrat rechts unten an der Zelle kennst du, oder?
    Das mit der Grafik würde ich so lösen, dass ich ein extra Arbeitsblatt nehme, auf die Zahlen aus dem anderen Arbeitsblatt verweise und daraus dann die Grafik erstelle.
    Damit du die Verweise alle 240 Zeilen hinbekommst kann man Exel austricksen.
    Du verweist auf das erste Ergebnis (also Zeile 240), dann in den nächsten Zeile auf das zweite(also Zeile 480). Wenn du jetzt die Formel automatisch weiterziehst klappt das nicht.
    Wenn du aber das Gleichheitszeichen durch Leerzeichen Gleichheitszeichen ersetzt, hast du die Formel in der Zelle stehen, ohne dass sie berechnet wird.
    Diese beiden (also 240 und 480) markieren, unten rechts an der Zelle anfassen (der Cursor wird zu einem schwarzen Kreuz) und einfach runter ziehen. Dann nur noch " =" wieder durch "=" ersetzten und die Ergebnisse sind da.

    Falls das irgendwie verwirrend klingt (besser kann ich es im Moment nicht beschreiben) frag nochmal konkret nach.

    Erstmal viel Erfolg
    Gruß
    Anne
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo Anne

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  4. Antwort von аlеx2k7 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo,

    leider kann ich mir die Tabelle noch nicht ganz vorstellen. Ich würde versuchen über gemeinsame Kriterien die gesuchten Summen zu bilden und im Anscluss halt den entsprechenden Mittelwert bilden.

    Leider ist eine genauere Antwort aus meiner Sicht nur mit einer genaueren Beschreibung der Tabelle möglich (am besten Anhand einer Beispiel-Datei).
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo alex2k7

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  5. Antwort von Κеνіn 1
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo,
    ohne VBA wirst du wohl keine saubere Lösung hinbekommen. Versuch aber mal folgendes:

    Zunächst eine Spalte A mit den Zeilennummern anfügen. Dann schreibst du in B240:

    =WENN(REST(A240/240)=0;MITTELWERT(C1:C240);"")

    Das ziehst du dann nach unten.
    Vielleicht kannst du das schon direkt als Graph darstellen. Falls nicht wird das ohne VBA etwas schwieriger.
    In Spalte Z schreibst du:

    =WENN(REST(A240/240)=0;A240/240;"")

    Die ganze Tabelle kopieren und in eine neue Tabelle "Inhalte einfügen --> Werte". Dann kannst du Spalte C löschen und nach Z sortieren ... fertig.

    Gruß,
    Kevin
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo Kevin

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  6. Antwort von bесkеr 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo Zenogra,

    ich wundere mich, dass du bei deiner ersten Anfrage keine Hilfe bekommen hast. Wenn ich dein Problem richtig verstanden habe, dann kommst du OHNE VBA aus.

    Dein Problem ist das Gruppieren in 240er Päckchen. Ich denke, wenn du dann eine Summe pro Päckchen bilden kannst, dann ist der Rest einfach (Mittelwerte, Diagramme, etc)

    1. Füge neben deiner Tabelle noch eine weitere Spalte direkt anschließend ein. In dieser neuen Spalte bilden wir eine Zahl, die pro 240er Päckchen gebildet wird. Das Ergebnis wäre also von Zeile 3 bis 242 eine 1 und von Zeile 243 bis 482 eine 2, usw.
    Das geht mit folgendem Ungetüm:
    =AUFRUNDEN((ZEILE(A3)-2)/240;0)

    Wie funktioniert das: du nimmst die Zeilennummer als Hilfsnummerierung und ziehst den "irgendwo" Beginn ab, so dass dein erster Eintrag auch eine 1 ergibt. Beispiel:
    - Dein erster Eintrag ist in Zeile 3 - dann ziehst du 2 ab, ergo ergibt sich eine 1 für deinen ersten Eintrag. Die nächste Zeile ergäbe dann eine 2, usw.
    - Das jeweilige Ergebnis (DEINE Zeilennummer) teilst du durch 240. Das Ergebnis wäre eine krumme Zahl.
    - Die krumme Zahl rundest du auf die nächste Zahl auf.

    Nachdem du die Formel eingetragen hast, stehen in den ersten 240 Zeilen jeweils eine 1 und in den nächsten 240 Zeilen jeweils eine 2, usw.

    Jetzt kommt dein nächstes Problem:
    Du musst Ergebnisse zusammenfassen/errechnen und zwar für die jeweiligen Päckchen. Das machst du mit einem weiteren Bereich neben deiner Riesentabelle.
    In dem Bereich trägst du in der ersten Spalte (bei mir ist es die Spalte H) die Zahlen 1, 2, 3 bis (154000/240) ein. Dabei hilft dir der Ausfüll-Assistent. Neben der ersten Spalte kommen dann deine Auswertungsformeln.
    In meinem Beispiel:
    =SUMMEWENN($F$3:$F$15403;H3;$A$3:$A$15403)

    Was macht die Formel (du solltest evtl. nachlesen!)
    - Die Spalte F (du solltest dort 154003 anstatt 15403 eintragen) sucht eine Päckchennummer - und zwar die Nummer, die in Zelle H3 steht. Wann immer dort diese Zahl gefunden wird, werden die Inhalte der Spalte A addiert.

    Wenn du mehr Spalten auswerten willst, dann musst du eben eine dritte, vierte, ... Spalte mit der Summewenn-Formel füllen.

    Alles klar?
    Zögere nicht, nachzufragen.

    Viel Erfolg
    Harald
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo becker

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  7. Antwort von роwеr_bluе 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo,

    gibt es einen Index oder Zeitstempel oder sowas je Zeile?

    Dann kannst du mit Summewenn() die Werte je Zeitintervall berechnen und durch 240 teilen um auf den Mittelwert zu kommen.
    1 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo power_blue

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
  8. Antwort von Κаі 1
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo zenogra,

    hier ein Vorschlag ohne VBA.

    1. Annahmen:
    Deine Daten stehen in einer Tabelle "Daten". Beginnend in Zelle A1 bis ...19 Spalten nach rechts, 153.999 Zeilen nach unten.

    2. Lösungsweg:
    Du trägst in Tabelle "Auswertung" folgende Formel in Zelle A1 ein

    =MITTELWERT(BEREICH.VERSCHIEBEN('Daten'!A$1;(ZEILE(A1)-1)*240;):BEREICH.VERSCHIEBEN('Daten'!A$240;(ZEILE(A1)-1)*240;0))

    Diese Formel bildet den Mittelwert der ersten 240 Werte in der Spalte A der Daten-Tabelle. wenn deine Werte erst in der 5. Zeile beginnen, so ändere den Bezug von A$1 auf A$5 sowie A$240 auf A$245.

    Diese Formel kannst du für alle Spalten einfach nach unten bzw. nach rechts kopieren, so dass du in der Tab. "Auswertung" die von dir gesuchten Werte zur Weiterverarbeitung für die Grafik hast.
    Die Grafik zu erstellen dürfte dann eigentlich kein problem mehr sein.
    viele Grüße
    Kai
    2 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo Kai

      Der Tip war hervorragend. Ich konnte ihn sofort umsetzen. Alles ist fertig als Diagramm bearbeitet.
      Danke für Deine schnelle Hilfe.
      Gruß
      zenogra
    • von Κаі 0
      Re^3: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hi zenogra,

      ich freue mich, dass ich dir helfen konnte.

      Ciao, Kai
  9. Antwort von ЈоѕсһіΗ 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    hallo,
    vielleicht funktioniert es mit der wenn Funktion.Wenn 4 Zeilen =1Minute dann240=1Stunde anstonsten "...".Und dann Formel Mittelwert.
    3 Kommentare
    • Re^2: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo JoschiH

      Vielen Dank für Deine Hilfe.
      Eine für mich praktikable Lösung kam von Kai.
      Danke
      Gruß
      zenogra
      hallo,
      vielleicht funktioniert es mit der wenn Funktion.Wenn 4 Zeilen
      =1Minute dann240=1Stunde anstonsten "...".Und dann Formel
      Mittelwert.
    • Re^3: Excel 2010 Grosse Tabelle automatisch gruppieren
      hallo,
      die lösung würde ich gerne wissen.man lernt ja nie aus.wäre net wenn sie mir die auch scheiben könnten.danke
    • Re^4: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo JoshiH

      Hier die Lösung von Kai
      1. Annahmen:
      Deine Daten stehen in einer Tabelle "Daten". Beginnend in Zelle A1 bis ...19 Spalten nach rechts, 153.999 Zeilen nach unten.

      2. Lösungsweg:
      Du trägst in Tabelle "Auswertung" folgende Formel in Zelle A1 ein

      =MITTELWERT(BEREICH.VERSCHIEBEN('Daten'!A$1;(ZEILE(A1)-1)*240;):BEREICH.VERSCHIEBEN('Daten'!A$240;(ZEILE(A1)-1)*240;0))

      Diese Formel bildet den Mittelwert der ersten 240 Werte in der Spalte A der Daten-Tabelle. wenn deine Werte erst in der 5. Zeile beginnen, so ändere den Bezug von A$1 auf A$5 sowie A$240 auf A$245.

      Diese Formel kannst du für alle Spalten einfach nach unten bzw. nach rechts kopieren, so dass du in der Tab. "Auswertung" die von dir gesuchten Werte zur Weiterverarbeitung für die Grafik hast.

      Gruß
      zenogra
      hallo,
      die lösung würde ich gerne wissen.man lernt ja nie aus.wäre
      net wenn sie mir die auch scheiben könnten.danke
  10. Antwort von Ѕtеfаn Rоtһ 3 0
    Re: Excel 2010 Grosse Tabelle automatisch gruppieren
    Hallo

    Wenn Du Tabellen mit 154'000 Zeilen pflegst, wirst vor ein wenig Formeln und Handarbeit nicht zurückschrecken. Ich habe ein kleines Beispiel zusammengeschuster, vielleicht trifft es ja halbwegs Dein Problem oder hilft Dir zumindest weiter. Mein Muster findest Du hier:

    http://www.redh.ch/Muster.xlsx

    Das ist eine Excel-Tabelle mit zwei Blättern. "Daten" entspricht quasi Deinem Blatt mit 154000 Zeilen, "Tabelle1" ist mein Vorschlag, wie man Deine Daten zusammenfassen könnte.

    Dein Datenblatt lassen wir völlig unangetastet, alles spielt sich nur in "Tabelle1" ab.

    Es gibt in Excel die Möglichkeit, Zellbezüge abzuleiten und das machen wir hier. Ziel ist es einen Mittelwert der Zellen 1-240, 241-480, usw. in Spalte 2 im Blatt "Daten" zu erhalten. Diese Mittelwerte sollen in Spalte H stehen. Die Spalten A bis G dienen als Zwischenschritte, um den Zellbezug für die in Spalte H befindliche Funktion MITTELWERT() zu berechnen. Dabei müssen lediglich die gelb hinterlegten Zellen erfasst werden, alles andere wird abgeleitet.

    Spalte A: Name des Blatts mit den Daten
    Spalte B: Spalte, wo sich die zu mittelnden Werte befinden
    Spalte C: Erhöhung für die Gruppierung (240 in Deinem Fall, 2 in meinem Beispiel)
    Spalte D: Startzeile
    Spalte E: Endzeile
    Spalte F: Beginnadresse für Zellbezug in MITTELWERT()
    Spalte G: Endeadresse für Zellbezug in MITTELWERT()
    Spalte H: Der Mittelwert.

    Das ganze wäre auch mit weniger Spalten gegangen aber es sollen ja die Zwischenschritte deutlich werden. Wenn Du Dir die Formel in Spalte H ankuckst, dort steht in der Funktion MITTELWERT() kein direkter Zellbezug, sondern es liest indirekt die generierten Start/Endbezüge mit einem : zwischendrin.

    Kopier das Blatt "Tabelle1" in Deine Datei, passe die gelben Zeilen an und vielleicht ergibt sich bereits ein Resultat. Dann musst Du lediglich die Zeilen so oft kopieren wie nötig.

    Hilft das?

    Gruss
    Stefan
    • Antwort von Ηіеr іѕt (abgemeldet) 0
      Re: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hallo, eine Gruppierung geht nur mit Bildern.
      Die 240 Zeilen makieren und den Mittelwert berechnen und dann in die Grafik einfügen.
      Aber das ist bestimmt schon bekannt.
      Viel Erfolg:
      Diese Anfrage habe ich schon mal an die Experten im Forum
      gerichtet. Leider gab es keine Lösung. Deshalb ein neuer
      Versuch.
      Hier mein Problem: Ich habe im Excel eine Tabelle mit 154000
      Zeilen und 20 Spalten.
      Jede Zeile repräsentiert ein Messergebnis.
      4 Zeilen entsprechen 1 Minute Messdauer
      240 Zeilen entsprechen 1 Stunde Messzeit

      Ich möchte nun jeweils 240 Zeilen (1Stunde) gruppieren, aus
      den Werten den Mittelwert berechnen, und die Stundenwerte
      später in eine Grafik überführen.

      Mit den normalen Bordmitteln von Excel 2010 funktioniert das
      nicht. VBA Programmierung ist nicht meine Welt.

      Deshalb nun meine Anfrage an das Forum hier.

      Auf jeden Fall mein Dank im voraus an alle.