Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1616to1620
1616to1620
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formel für dynamischen Bereich

Forumthread: Formel für dynamischen Bereich

Formel für dynamischen Bereich
28.03.2018 11:51:45
xbit
Hallo zusammen,
ich möchte gerne eine Excel Formel ohne VBA nutzen.
Ich habe eine Spalte z.B. von A2:A10, dieser Bereich wäre fix.
Da aber die Anzahl der Spalten variabel ist suche ich eine Excel Formel die sich automatisch den letzen wert in der Spalte A sucht.
Des weiteren suche ich eine Formel die mir in der Spalte B einmal den kleinsten Wert und einmal den größten Wert in der Spalte B gibt. Spalte B muss ebenfalls dynamisch sein, da auch hier die Einträge variieren.
Die Werte für A sind ganzzahlig 1,2,3,4,5... Die Werte für B sind mit 2 Nachkommastellen. 1,1 1.2 1,3.....
Ich kann den kleinsten Wert mit KKleinst und den Größten mit KGrößter ermitteln aber leider nicht dynamisch.
Hat jemand ne idee?
Anzeige

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Formel für dynamischen Bereich
28.03.2018 13:05:15
xbit
Hi SF,
erstmal danke für die Antwort.
Leider gibt es bei der 2010 Version nicht die letzte Zelle, letzte Spalte -mmmmmhhhh- leider
Anzeige
AW: Formel für dynamischen Bereich
28.03.2018 13:09:30
SF
Hola,
zum Einen hast du OfficeXP angegeben, zum anderen verstehe ich nicht was dein 2. Satz bedeuten soll.
Die Formeln hast du dir aber schon angeschaut?
Gruß,
steve1da
AW: Angaben sind nicht eindeutig ...
28.03.2018 12:32:26
...
Hallo xbit,
... abgesehen davon, dass, dass Du in "Da aber die Anzahl der Spalten variabel ist..." wahrscheinliche "Zellen" meinst, ist für die Ermittlung des kleinsten und größten Wertes in Spalte B einfach MIN() und MAX() ausreichend bei Auswertung der gesamten Spalte, d.h. normalerweise ohne Notwendigkeit der Ermittlung eines "dynamischen" Datenbereiches.
Oder willst Du MIN/MAX in Spalte B in Abhängigkeit des letzten Wertes in Spalte A ermitteln?
Dann z.B. so: =MIN(B1:INDEX(B:B;VERWEIS(9;1/(A1:A999"");ZEILE(A1:A999)))) und analog für MAX.
Gruß Werner
.. , - ...
Anzeige
AW: Angaben sind nicht eindeutig ...
28.03.2018 13:20:58
xbit
Hallo Werner,
Danke für Deine info.
Ja Du hast recht, es handelt sich um Zellen
Ich möchte nur in 1 Spalte B Zelle2-Zelle? da variable Länge nicht bekannt, nach den letzen eingetragenen Wert suchen-wiedergeben, also letzte Zelle.
Und zusätzlich noch in der Variable Spaltenlänge-Zellenlänge A2-A? nach den kleinsten und größten Wert suchen. Ja ich kann mit min arbeiten aber wie mache ich das mit der variablen Zellenlänge A1-10 ein anderes mal A1-A34......etc. ?
Anzeige
AW: Angaben sind nicht eindeutig ...
28.03.2018 13:20:59
xbit
Hallo Werner,
Danke für Deine info.
Ja Du hast recht, es handelt sich um Zellen
Ich möchte nur in 1 Spalte B Zelle2-Zelle? da variable Länge nicht bekannt, nach den letzen eingetragenen Wert suchen-wiedergeben, also letzte Zelle.
Und zusätzlich noch in der Variable Spaltenlänge-Zellenlänge A2-A? nach den kleinsten und größten Wert suchen. Ja ich kann mit min arbeiten aber wie mache ich das mit der variablen Zellenlänge A1-10 ein anderes mal A1-A34......etc. ?
Anzeige
AW: nun die Spalten vertauscht? ...
28.03.2018 13:48:10
...
Hallo xbit,
... nun willst Du MAX und MIN nicht mehr in Spalte B sondern in Spalte A ermitteln? Dann eben hier =MAX(A:A) und MIN(A:A)
Der letzte (Zahlenwert!) in Spalte B z.B. so: =VERWEIS(9^99;B:B)
Gruß Werner
.. , - ...
AW: nun die Spalten vertauscht? ...
28.03.2018 15:01:38
xbit
Hallo Werner,
sorry das ich jetzt nochmal auf dich zukomme.
Hab jetzt erfahren das die dynamischen Werte Spalte B also letzen Werte die mal in der Zelle B10, B30, B50, B66...... etc. stehen können auch von einen anderen Tabellenblatt z.B. Tabelle1 stehen können. Ich hab jetzt schon versucht mit
=Verweis("Tabelle1!";9^99;B:B) kein Erfolg
=Verweis("Tabelle1!"9^99;B:B) kein Erfolg
=Verweis("Tabelle1!",9^99;B:B) kein Erfolg
=Verweis(Tabelle1! 9^99;B:B) kein Erfolg
Hättest du da noch einen Vorschlag?
Anzeige
AW: nun die Spalten vertauscht? ...
28.03.2018 15:01:40
xbit
Hallo Werner,
sorry das ich jetzt nochmal auf dich zukomme.
Hab jetzt erfahren das die dynamischen Werte Spalte B also letzen Werte die mal in der Zelle B10, B30, B50, B66...... etc. stehen können auch von einen anderen Tabellenblatt z.B. Tabelle1 stehen können. Ich hab jetzt schon versucht mit
=Verweis("Tabelle1!";9^99;B:B) kein Erfolg
=Verweis("Tabelle1!"9^99;B:B) kein Erfolg
=Verweis("Tabelle1!",9^99;B:B) kein Erfolg
=Verweis(Tabelle1! 9^99;B:B) kein Erfolg
Hättest du da noch einen Vorschlag?
Anzeige
AW: nun sind wir an einem Punkt angelangt, ...
28.03.2018 15:13:29
...
Hallo,
... wo Du mal eine kleine Beispieldatei einstellst und darin aufzeigst, was Du wo zu stehen hast, was Du versucht hast und was Du dafür als Ergebniswert(e) erwartest.
Gruß Werner
.. , - ...
;

Forumthreads zu verwandten Themen

Formel gesucht: Anzahl vesch. Werte in Zellbereich
Martin

Hallo liebe Excelianer,
gibt es eine Formel, die die Anzahl verschiedener Zelleninhalte für einen Bereich ermittelt? Zum Beispiel wenn ich 15 Zellen hätte, in denen 3mal die 5 steht und 12mal die 7 steht. Da ja nur 2 verschiedene Werte in den 15 Zellen stehen, müsste also 2 herauskommen. Nu...
19.09.2008 13:25:00
Thread-ID: 1011022
4 Beiträge
dynamischer Zellbezug in vba
Andi

Hallo Experten,
Ich wär euch sehr dankbar wenn ihr mir bei folgendem Problem weithelfen könnt. Ich versuch im beigelegten Dokument, Daten aus einer Pivottabelle über ein Makro darzustellen. Ich kann jedoch die Referenzzelle nicht gemäss der entsprechenden Zeile anpassen.
Bitte schauts...
09.01.2009 12:49:25
Thread-ID: 1038197
3 Beiträge
Anzeige
Änderung von Zellbezügen in einer Formel
Zellbezügen

Liebe Forumexperten,
ich habe eine Formel, die ich durch horizontales ziehen des Ausfüllkästchens automatisch kopieren möchte. Die Formel lautet:
=wenn(Register1!B3="";wiederholen(Register1!C3;1);weiderholen(Register1!B3))
ich möchte jetzt das die nächste Formel so aussieht:
=we...
18.01.2010 12:58:23
Thread-ID: 1130724
4 Beiträge
Variabler Bereich in Excel Formeln
valentin

Hallo
ICh hätte ein kleines problem:
ICh würde gerne in VBA die Formel "count if" für einen Bereich einsetzen, welcher von der Anzahl der ausgefüllten Zeilen abhängt... Sprich es sollen immer alle ausgefüllten Zeilen gezählt werden so soll der Bereich bei 5 Zeilen etwa A1:A5 betragen.
05.03.2010 10:40:42
Thread-ID: 1143585
7 Beiträge
Bereichsanpassung z. einfärben d. Formelergebnisse
sharky1000

Hallo,
ich möchte in einer Tabelle Formelergebnisse abhängig vom Ergebnis einfärben.
Zur automatischen Aktualisierung verwende ich die Funktion =HEUTE() in einer Zelle.
Wie passe ich das folgende, hier gefundene Makro an, dass es nur für den Zellbereich C4:N33 greift?
Option Exp...
29.12.2010 18:36:06
Thread-ID: 1193250
11 Beiträge
Anzeige
Dynamischer WErt einer Formel Kopieren
Christian

Hallo zusammen.
Ich habe mehrere Excel Tabellen welche alle ein Ergebniss ausgeben.
Dieses Ergebniss Kopiere ich über ein Makro in eine Ergebnisstabelle.
Mein Problem ist jetzt das die Formel kopiert wird und nicht der Wert.
Der Wert ändert sich täglich daher sollte wohl das Kop...
29.03.2011 11:31:47
Thread-ID: 1208064
5 Beiträge
Dynamischer Wert in Formel
Karl

Hallo Excel Profis,
ich stehe vor dem Problem, dass ich in einem Tabellenblatt auf eine Zelle in einer anderen Datei verknüpfe: z.B: ='C:\2011\[Monat_01.xls]Tabelle1'!$U$5
Nun möchte ich aber diese Verlinkung dynamisch halten, sprich der Teil der Formel "... Monat_01 ..." soll in in abh...
10.08.2011 14:38:13
Thread-ID: 1225483
2 Beiträge
Anzeige
in WENN-Formel Link 2 * einbinden mit Zellbezug
Holger

Hallo,
gegeben sei eine Exceltabelle, wo in Feld D8 ein Wert von 0 € steht, wenn ich für eine Dienstleistung meines Unternehmens keine Fahrtkosten berechnet habe. Je nach Anfahrtstrecke steht in Zelle D8 ein Wert wie etwa 3 €.
Ich möchte nach der Datenerfassung in der Kundenübersichts-T...
15.05.2012 20:24:18
Thread-ID: 1263604
5 Beiträge
Formeln in Zellbereich eintragen per VBA-Array
Herbert

Hallo,
eine Formel kann ich mit VBA in eine Zelle mit folgendem Code eintragen:
ActiveCell.FormulaR1C1 = "=IF(RC[-64]="""","""",maxBlock2(ADDRESS(ROW()-1,COLUMN()-63)&"":""&ADDRESS(ROW()-1,54)&"",""&ADDRESS(ROW(),7)&"":""&ADDRESS(ROW(),COLUMN()-64),1)/2)"
Da...
31.01.2013 16:48:57
Thread-ID: 1296973
3 Beiträge
Formel in variablen Bereich einfügen
Markus
Hallo zusammen,
per VBA stelle ich eine Formel ein.
Range("AJ2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-4]="""","""",VLOOKUP(RC[-3],Personalnummer!C[-35]:C[-34],2,0))"
Selection.AutoFill Destination:=Range("AJ2:AJ50"), Type:=xlFillDefault
Diese Formel ist, wie Ihr sehen...
15.06.2014 16:13:13
Thread-ID: 1365953
3 Beiträge
Wie kann man Formeln zu einem Bereich verknüpfen
Sebastian

Hallo,
vermutlich ist die Lösung ganz einfach - finde aber nichts dazu ...
Ich möchte mit der TREND-Funktion eine lineare Interpolation zwischen zwei Punkten durchführen. Nichts leichter als das: TREND(A1:A2;B1:B2;C1).
Nun sollen die Werte für x1, x2, y1 und y2 aber aus je einer For...
17.09.2014 12:31:50
Thread-ID: 1382063
8 Beiträge
Wie kann man Formeln zu einem Bereich verknüpfen
Sebastian

Hallo,
vermutlich ist die Lösung ganz einfach - finde aber nichts dazu ...
Ich möchte mit der TREND-Funktion eine lineare Interpolation zwischen zwei Punkten durchführen. Nichts leichter als das: TREND(A1:A2;B1:B2;C1).
Nun sollen die Werte für x1, x2, y1 und y2 aber aus je einer For...
17.09.2014 12:33:14
Thread-ID: 1382064
3 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Dynamischer Bereich in Excel: Formeln und Tipps

Schritt-für-Schritt-Anleitung

  1. Dynamischen Bereich erstellen: Um einen dynamischen Bereich in Excel zu erstellen, kannst Du die INDEX- und VERWEIS-Funktion nutzen. Angenommen, Deine Daten stehen in Spalte A (A2:A10). Verwende die folgende Formel, um den letzten Wert in dieser Spalte zu finden:

    =VERWEIS(9^99;A:A)

  2. Kleinster und größter Wert in Spalte B ermitteln: Für die Spalte B, in der die Werte variabel sind, kannst Du die MIN- und MAX-Funktionen zusammen mit INDEX verwenden. Beispiel:

    =MIN(B1:INDEX(B:B;VERWEIS(9;1/(A1:A999"");ZEILE(A1:A999)))

    und

    =MAX(B1:INDEX(B:B;VERWEIS(9;1/(A1:A999"");ZEILE(A1:A999)))

  3. Dynamischer Zellbezug über Tabellenblätter: Wenn Deine Daten in einem anderen Tabellenblatt sind, kannst Du die Formel anpassen, indem Du den Tabellennamen hinzufügst:

    =VERWEIS(9^99;Tabelle1!B:B)

Häufige Fehler und Lösungen

  • Falsche Bezugnahme auf Zellen: Achte darauf, dass Du die richtigen Zellbezüge verwendest. Wenn Du A1:A999 verwendest, stelle sicher, dass die Zellen tatsächlich in diesem Bereich liegen.

  • Dynamische Bereiche in älteren Excel-Versionen: In älteren Versionen (z.B. Excel 2010) kann es sein, dass einige Funktionen nicht verfügbar sind. Überprüfe die Kompatibilität der verwendeten Formeln.

  • Formel funktioniert nicht: Wenn die Formel nicht funktioniert, überprüfe, ob Du die korrekten Trennzeichen (z.B. ; oder ,) verwendest, je nach Deinen Excel-Einstellungen.

Alternative Methoden

  • Tabelle nutzen: Du kannst auch Excel-Tabellen verwenden, um dynamische Bereiche zu erstellen. Wenn Du eine Tabelle erstellst (Strg + T), wird der Bereich automatisch dynamisch.

  • Dynamische Namen definieren: Eine weitere Methode besteht darin, einen Namen für den dynamischen Bereich zu definieren. Gehe zu "Formeln" > "Namens-Manager" und erstelle einen neuen Namen mit einer Formel, die den Bereich dynamisch bestimmt.

Praktische Beispiele

  1. Dynamische Summe: Um die Summe eines dynamischen Bereichs zu berechnen, kannst Du SUMME mit INDEX kombinieren:

    =SUMME(B1:INDEX(B:B;VERWEIS(9;1/(A1:A999"");ZEILE(A1:A999)))

  2. Kleinster und größter Wert: Verwende die oben genannten Formeln, um den kleinsten und größten Wert in einer dynamischen Liste zu ermitteln.

Tipps für Profis

  • Verwende die OFFSET-Funktion: Diese Funktion kann ebenfalls zur Erstellung dynamischer Bereiche verwendet werden. Beispiel:

    =SUMME(OFFSET(A2;0;0;ANZAHL(A:A);1))

  • Datengültigkeit: Achte darauf, dass Deine Daten konsistent sind, um die Genauigkeit der Formeln sicherzustellen.

  • Dokumentiere Deine Formeln: Wenn Du komplexe Formeln verwendest, schreibe die Logik in die Kommentarfunktion von Excel, um später besser nachvollziehen zu können, was Du gemacht hast.

FAQ: Häufige Fragen

1. Was sind dynamische Bereiche in Excel?
Dynamische Bereiche sind Bereiche, die sich automatisch anpassen, wenn Daten hinzugefügt oder entfernt werden.

2. Wie kann ich einen dynamischen Bereich in einer Formel verwenden?
Du kannst Funktionen wie INDEX, VERWEIS, MIN und MAX nutzen, um dynamische Bereiche in Deinen Formeln zu integrieren.

3. Funktioniert das in allen Excel-Versionen?
Nicht alle Funktionen sind in älteren Excel-Versionen verfügbar. Die oben genannten Methoden sollten jedoch in den meisten aktuellen Versionen funktionieren.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige