Expertenwissen
Von: , 16.03.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.

23 Antworten zu dieser Frage
  1. Antwort von nach 9 Minuten 1 hilfreich
    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
    • Antwort von nach einem Tag 1 hilfreich
      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
      • Antwort von nach 20 Minuten 0 hilfreich
        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
    • Antwort von nach einem Tag 1 hilfreich
      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
  2. Antwort von nach 22 Minuten 1 hilfreich
    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
    • Antwort von nach einem Tag 0 hilfreich
      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
    • Antwort von nach 3 Stunden 0 hilfreich
      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).
      • Antwort von nach einem Tag 0 hilfreich
        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
    • Antwort von nach 6 Stunden 1 hilfreich
      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
      • Antwort von nach einem Tag 1 hilfreich
        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
    • Antwort von nach 16 Stunden 0 hilfreich
      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
    • Antwort von nach einem Tag 0 hilfreich
      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
  3. Antwort von nach 17 Stunden 0 hilfreich
    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.
  4. Antwort von nach einem Tag 0 hilfreich
    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
  5. Antwort von nach 19 Stunden 1 hilfreich
    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
  6. Antwort von nach einem Tag 0 hilfreich
    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
    • Antwort von nach einem Tag 0 hilfreich
      Re^3: Excel 2010 Grosse Tabelle automatisch gruppieren
      Hi zenogra,

      ich freue mich, dass ich dir helfen konnte.

      Ciao, Kai
  7. Antwort von nach 22 Stunden 0 hilfreich
    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.
    • Antwort von nach einem Tag 0 hilfreich
      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.
      • Antwort von nach einem Tag 0 hilfreich
        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
        • Antwort von nach 2 Tagen 1 hilfreich
          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
      • Antwort von nach einem Tag 0 hilfreich
        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 nach 4 Tagen 0 hilfreich
        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.
        Jetzt auf diese Frage antworten!