Excel 2007 - Variable in Feldbereichsangaben

Computer: Software & Programmierung Office Software
1

Eine Funktion mit Feldbereichsangabe sieht ja normalerweise so aus: =min(a100:a110)
Kann ich die „100“ und die „110“ auch durch weitere Variablen ersetzen?
Ich denke dabei an zwei weitere Zellen des Arbeitsblattes, in denen dann z.B. „105“ und „115“ steht.
Läßt sich das noch erweitern und z.B. nach der letzten NICHT leeren Zeile in Spalte a suchen und die Nummer dieser Zeile als Feldbereichsangabe einsetzen?

Mein Problem: Ich habe eine sich ständig vergrößernde Menge an Werten. Nach Eingabe beliebiger Werte muss ich die Feldbereiche für Min, Max und Durchschnitt und für ein Diagramm nacheinander von Hand auf die letzten 100 Messwerte aktualisieren. Das möcht ich gerne automatisch erledigen lassen. Geht das?
Sind in Excel überhaupt Variable in Felbereichsangaben möglich?
Wenn ja, wie heißen die Funktionen und wie sieht so eine Zeile dann aus?

2

Moin,
meinst Du INDIREKT()?
=MIN(INDIREKT(B1):INDIREKT(B2))
Freundliche Grüße
Thomas

3

Yep! Prima! Danke sehr!
Das funktioniert schon mal wie von mir gedacht für
MIN, MAX und MITTELWERT.
Ich bekomme noch eine Fehlermeldung beim Diagramm.
Ich arbeite mit 2 Arbeitsblättern:
„Tabelle“ für alle Daten und Rechenergebnisse
„Diagramm“ eben für ein ganzseitiges Diagramm
Die Reihenwerte sehen so aus:
=Tabelle!$B$349:blush:B$449
Die Achsenbeschriftung so:
=Tabelle!$A$349:blush:A$449
Mein Versuch für die Reihenwerte
=tabelle!indirekt(e1):indirekt(f1)
oder
=tabelle!(indirekt(e1):indirekt(f1))
funktioniert hier leider nicht.
Was mache ich da noch falsch?

4

Nachfrage

„Diagramm“ eben für ein ganzseitiges Diagramm
Die Reihenwerte sehen so aus:
=Tabelle!$B$349:blush:B$449
=tabelle!indirekt(e1):indirekt(f1)
oder
=tabelle!(indirekt(e1):indirekt(f1))
funktioniert hier leider nicht.

Hallo DK,

Deine Reihenwerte stehen in Spalte B. Im Diagramm willst du dynamisch immer die letzten (untersten) 100 Werte angezeigt haben?

Was steht denn in E1 und F1?
Wenn Formel bitte die Formel und die Ergebnisanzeige in E1,F1.

Speicher dir diesen Link von Karins Homepage als Lesezeichen ab, sehr hilfreich grad bei Diagrammen u.v.m.
Klicke dort links auf „Diagramme“, dann suche nach den zwei oder mehr Beispielmappen mit dynamischen Diagrammen.

http://beverly.excelhost.de/

Gruß
Reinhard

5

In e1 und f1 stehen die Zellenangaben für die letzten 100 Messwerte.
Also wie darüber in meinem nicht dynamischen Beispiel „b349“ bzw. „b449“ als Text. Ich passe nach Ergänzung mit neuen Werten die Einträge von Hand an.
Wie sich auch das automatisieren läßt, also nach der letzten gefüllten Zelle in Spalte b suchen und diese Zeilennummer (und die um 100 kleinere Startzeile) dann automatisch am meine drei Formeln und das Diagramm übergeben werden kann, nehme ich in Angriff, wenn das manuell funktioniert und ich alles verstanden habe.

6

dynamische Datenbereiche
Hallo DK,

schau wie gesagt zu den Diagrammen von karin.

Zum Verstehen lernen, schau mal dieses an, es füllt E1 und F1 automatisch, deinm anuelles Abändern entfällt.
In E2 siehst du wie man damit weiterarbeiten kann am Beispiel Summe.

Tabellenblatt: [Mappe1]!Tabelle1
 │ E │ F │
──┼────────┼────────┤
1 │ $B$350 │ $B$251 │
──┼────────┼────────┤
2 │ 30050 │ │
──┴────────┴────────┘
Benutzte Formeln:
E1: =ADRESSE(ANZAHL2(B:B);2)
E2: =SUMME(INDIREKT(Tabelle1!E1):INDIREKT(Tabelle1!F1))
F1: =ADRESSE(ZEILE(INDIREKT(E1))-99;2)

E1:F2
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363
Gruß
Reinhard

1 „Gefällt mir“
7

Auch das hat mir bei der Vereinfachung der Tabelle weitergeholfen. Danke!
Ich verwende jetzt aber nicht ANZAHL2() sondern ANZAHL().
Meine Tabelle enthält Überschriften und der Wertebereich beginnt erst in Zeile 5. Meine beiden Formeln sehen jetzt so aus:

=ADRESSE(ANZAHL(B5:B1000)-96;2)
=ADRESSE(ANZAHL(B5:B1000)+4;2)

Kann ich den absolut angegebenen Feldbereich auch umstellen auf
„gesamte Spalte B ab Zeile 5“. So wie oben ist der Bereich größer als der von mir zur Zeit farblich formatierte und wird durch Einfügen neuer Zeilen weiter vergrößert, was zukünftige Fehler verhindern sollte. So elegant wie jetzt die automatische Anpassung an die letzten 100 Werte ist das aber noch nicht.

Tja, am Diagramm muss ich noch arbeiten. Da hab ich auch mit Beverlys Beispielen noch Probleme…
Ich habe auch gerade neue Werte ergänzt. Jetzt funktioniert in der Tabelle alles so, wie ich mir das gedacht habe.
Danke!

8

Hallo DK,

Auch das hat mir bei der Vereinfachung der Tabelle
weitergeholfen. Danke!

gerne :smile:

Ich verwende jetzt aber nicht ANZAHL2() sondern ANZAHL().

Das ist prinzipiell egal aber wichtig ist daß du den Unterschied genau kennst.

Oder annerster formuliert so wie ich das für mich mache.
Ich „arbeite“ viel zu wenig mit beiden als daß ich das Bedürfnis hätte mir da die Feinheiten zu merken.

Ich weiß aber daß da einer ist und wenn ich jetzt das mal brauche so teste ich das fix mit Anzahl, wenn das klappt isses gut, wenn nicht nehm ich Anzahl2. Wenn das auch nicht klappt nehm ich was anderes :smile:

Meine Tabelle enthält Überschriften und der Wertebereich
beginnt erst in Zeile 5. Meine beiden Formeln sehen jetzt so
aus:

=ADRESSE(ANZAHL(B5:B1000)-96;2)
=ADRESSE(ANZAHL(B5:B1000)+4;2)

Ist okay, sieht gut aus, du hast das mit der Rechnerei mit -99 usw. verstanden.

Wenn in B1:B4 Werte stehen ist es unnötig erst ab B5 zu rechnen.
Excel (Anzahl) ist es wurscht ob da Überschriften oder Daten stehen.
Es schaut nur ob da was drin steht, eine Formel drinsteht, eine Fehlermeldung, sonstwas.

Kann ich den absolut angegebenen Feldbereich auch umstellen
auf „gesamte Spalte B ab Zeile 5“. So wie oben ist der Bereich
größer als der von mir zur Zeit farblich formatierte und wird
durch Einfügen neuer Zeilen weiter vergrößert, was zukünftige
Fehler verhindern sollte.

Wenn wie oben gesagt in B1:b4 was steht schreib doch als Bereich B.B und fertig.

Oder erweiter auf B5:B10000 oder B5:B20000 o.ä.

Oder schau auf http://www.excelformeln.de wie man die unterste Zelle einer Spalte ermittelt ohne Benutzung von Anzahl2 oder Anzahl.
Tipp, klicke dort auf Formeln dann siehst du "Suchfunktion, klick das an, dann suche nach
letzte zelle

So elegant wie jetzt die
automatische Anpassung an die letzten 100 Werte ist das aber
noch nicht.

? Verstehe ich leider nicht :frowning:

Tja, am Diagramm muss ich noch arbeiten. Da hab ich auch mit
Beverlys Beispielen noch Probleme…

Ich mutmaße das sind deine persönlichen Umsetzungsprobleme.
Und logisch kann man eine Lösung andernorts für andere daten erst dann für sich auf eigene Datenbereiche umsetzen wenn man das Grundprinzip verstanden hat.

Probiere es weiter. Wenn du nicht klarkommst lade mal eine Beispielmappe hoch mit file-upload o.ä., s. FAQ:2606

Ich habe auch gerade neue Werte ergänzt. Jetzt funktioniert in
der Tabelle alles so, wie ich mir das gedacht habe.

Aha, schön, ich weiß jetzt nicht genau was da klappt, hast du unten in B Werte angehängt und die Formeln haben sich angepasst?

Gruß
Reinhard

1 „Gefällt mir“
9

Also! Nochmal zum Diagramm.
In Beverlys Dateien sind in den Erläuterungen ein paar Fehler!
Beim Beispiel „feste_spanne“ stimmt die Textwiedergabe (’=BER…) ihrer Formel nicht mit der im Namensmanager eingetragenen überein. Das verwirrt. Auch stimmen einige Feldverweise nicht mit dem Beispiel darunter überein.
Im Prinzip vestehe ich BEREICH.VERSCHIEBEN jetzt, in der Praxis ist mein Ergebnis beim Test aber immer #WERT! Aus anderer Quelle habe ich diese Formel:

=BEREICH.VERSCHIEBEN(Tabelle!$A$1;VERGLEICH(0;Tabelle!$B:blush:B;-1)-100;1;100:wink:

Eingetragen in eine Zelle erhalte ich die Fehlermeldung #WERT!
Benutze ich meinen Feldbereichsnamen in der Tabelle, erhalte ich den allgemeinen Hinweis auf einen Formelfehler.

Ich hatte Zeit zum Nachdenken:
Kann ich die letzten 100 Werte nicht in einen anderen festen Bereich kopieren, natürlich automatisch und das Diagramm so simpel und undynamisch lassen, wie es ist und nur auf den neuen festen Bereich abändern?

10

Kann ich die letzten 100 Werte nicht in einen anderen festen
Bereich kopieren, natürlich automatisch und das Diagramm so
simpel und undynamisch lassen, wie es ist und nur auf den
neuen festen Bereich abändern?

Hallo DK,

schreib in z.B. E1:

=BEREICH.VERSCHIEBEN(B1;ANZAHL2(B:B)-100;0)

und kopiere es bis E100

Gruß
Reinhard

1 „Gefällt mir“