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!!
Du hast nicht beschrieben, sondern nur im Beispiel dargestellt, dass 77 wegen 73 "untergeht". Außerdem: Was soll mit n und -MK (?)
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
In meiner kurzen Nachfrage gab es (implizit und ausdrücklich) noch zwei Fragen (Doppler und "n").
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
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
[
attachment=48020]
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.
Die Lösung von Fred11 ist echt Super VIELEN DANK !!!!