Zahlen in einen bestimmten Bereich ausgeben

[karl1965]

Hallo,

ich habe folgende Frage Problem wie kann ich aus einer Tabelle einen definierten Zahlenbereich "von bis" zerlegen und in einer neuen Spalte ausgeben auch die dazwischen liegenden Werte anzeigen lassen.

Ist das überhaupt mit Excel 2016 möglich. (PC in der Arbeit)

Zuhause habe ich 365

Beispiel als Anlage

DANKE SCHON MAL INS FORUM!!

[LCohen]

Du hast nicht beschrieben, sondern nur im Beispiel dargestellt, dass 77 wegen 73 "untergeht". Außerdem: Was soll mit n und -MK (?)

[karl1965]

Hallo,

Sorry für die unklare Erklärung

Es sollte jeder Zahlenbereich dargestellt werden

A3110-A3115
sollte in der Spalte stehen
A3110
A3111
A3112
A3113
A3114
A3115

A3184 MK-A3185 MK
sollte in der Spalte stehen
A3184 MK
A3185 MK

Die Ergebnisse aller Zahlenbereiche in einer Spalte

[LCohen]

In meiner kurzen Nachfrage gab es (implizit und ausdrücklich) noch zwei Fragen (Doppler und "n").

[HKindler]

Hi,

darf es VBA sein? Dann nimm folgende Funktion. Bei aktuellem Excel reicht ein =Nummern(Tabelle1[Nummernbereich]), du musst nur darauf achten, dass unterhalb der Zelle mit dieser Formel genügend freie leere Zellen sind.
Bei älterem Excel musst du genügend Zellen markieren und dann in der ersten Zelle diese Formel als Matrix-Formel abschließen.

Code:

Function Nummern(Bereich As Range) As Variant
Dim Zelle As Range
Dim StartEnde As Variant
Dim Start As Variant, Ende As Variant
Dim i As Long
Dim k As Long
Dim tmp As Variant
Dim Vor As String, Nach As String
ReDim tmp(1 To 1)
For Each Zelle In Bereich
    StartEnde = Split(Zelle, "-")
    Start = Zerlegen(StartEnde(0))
    Ende = Zerlegen(StartEnde(1))
    Vor = Start(1)
    Nach = Start(3)
    For i = Start(2) To Ende(2)
        k = k + 1
        ReDim Preserve tmp(1 To k)
        tmp(k) = Vor & i & Nach
    Next i
Next Zelle
Nummern = Application.Transpose(tmp)
End Function

Function Zerlegen(ByVal Text As String) As Variant
'Zerlegt einen String in Text, Zahl, Text
Dim i As Integer
Dim t(1 To 3) As Variant
For i = 1 To Len(Text)
    If IsNumeric(Mid(Text, i, 1)) Then  'hier geht die Zahl los
        t(1) = Left(Text, i - 1)
        t(2) = Val(Mid(Text, i))
        t(3) = Mid(Text, i + Len(CStr(t(2))))
        Zerlegen = t
        Exit Function
    End If
Next i
End Function

[Fred11]

Hallo Karl,

basierend auf deiner Beispieldatei anbei ein Vorschlag für eine Formellösung mit (mehreren) Hilfsspalten., die die einzelnen Werte nicht in einer Spalte untereinander sondern in einer Zeile, jeweils hinter der Bereichsangabe auflistet.
(Die Hilfsspalten B:J sind ausgeblendet)

Da deine "Zahlenbereiche" keine rein numerischen Werte sind, sondern mit Buchstaben vermixt sind, und dadurch, dass von und bis zusammen in einer Zelle stehen (besser wäre, wenn gleich von vornherein der Anfangs- und Endwert in separaten Zellen stünden), muss das ganze eben erst mühsam aufgedröselt werden, um die reinen Anfangs- und Endwerte zu erhalten.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	K	L	M	N	O	P	Q	R	S
1	Nummernbereich
2	A111-A119	A111	A112	A113	A114	A115	A116	A117	A118	A119
3	A1111-A1117	A1111	A1112	A1113	A1114	A1115	A1116	A1117
4	A1112-A1112	A1112
5	A1113-A1115	A1113	A1114	A1115
6	A1114-A1114	A1114
7	A1115-A1115	A1115
8	A1115-A1115	A1115
9	A1115-A1115	A1115
10	A1115-A1115	A1115
11	A1115-A1115	A1115

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

Diese Tabelle wurde mit Tab2Html (v2.7.1) erstellt. ©Gerd alias Bamberg

Den Hinweisen/Fragen von LCohen schliesse ich mich an:
Für die Erzeugung der Zahlenreihen kommt erschwerend hinzu, dass manche Zahlenbereiche noch einen Zusatz (n, MK) haben.
Muss das so sein ? (oder ist das ein Versehen... ?)
Gibt es da noch weitere "Sonderlinge" ?

Und es gibt in deinen Zahlenbereichen in Spalte A sehr viele doppelte / identische Werte (Bereiche).
Was macht das für einen Sinn ?

Gruß
Fred

[LCohen]

  Mappe1 (16).xlsx (Größe: 53,41 KB / Downloads: 4)

bitte nur in Excel 365 oder in Excel Online öffnen!

Ich habe die Einzelschritte dort veranschaulichkeitshalber nicht in eine Formel zusammengeschoben (daher ist die Datei für meine Verhältnisse so groß). Wollte man das, käme (sicher noch optimierbar, vielleicht sogar erheblich!) raus:

O1#:

=LET(x;A2:A211;
d;LAMBDA(x;t;LET(y;t&x&t;m;LÄNGE(t);
   n;MAX(NACHZEILE(y;LAMBDA(a;(LÄNGE(a)-LÄNGE(WECHSELN(a;t;))-m)/m)));
   WENNFEHLER(TEXTNACH(TEXTVOR(y;t;SEQUENZ(;n;2));t;SEQUENZ(;n;1));"")))(x;"-");
f;WECHSELN(WECHSELN(WECHSELN(WECHSELN(d;"A";);"n";);" Ufa";);" MK";);
h;INDEX(f;;2)-INDEX(f;;1)+1;
i;SCAN(;h;LAMBDA(a;c;a+c))-h+1;
j;LET(x;HSTAPELN(INDEX(f;;1);i);n;h;INDEX(x;VERGLEICH(SEQUENZ(SUMME(n));SCAN(;n;LAMBDA(a;c;a+c))-n+1);SEQUENZ(;SPALTEN(x))));
l;SEQUENZ(ZEILEN(j))<>INDEX(j;;2);
m;SCAN(;--l;LAMBDA(a;c;a*(c>0)+c));
"A"&(INDEX(j;;1)+m))

Dies kann man nun natürlich noch verEINDEUTIGen und SORTIEREN. Habe ich nicht gemacht.

Der gute Drogist aka GMG-CC hätte das in PQ vermutlich eleganter hinbekommen.

Hinweis: Hat man in einer Tabelle ab Zeile 1 ZEILE(Kolonne) formuliert, muss man das in einem LET immer allgemeingültig in SEQUENZ(Zeilen(Kolonne)) umwandeln, damit die LET-Formel lageneutral wird.

[karl1965]

Die Lösung von Fred11 ist echt Super VIELEN DANK !!!!