Office: zahlen probleme

Hallo,

ich habe folgendes Problem:

in A1 stehen untereinander Werte die folgendermaßen aussehen:
A1 = 312-314
A2 = 23,24
A3 = 26-28
A4 = 30-32
A5 = 212,213

etc.

Nun möchte ich, wenn Möglich die Zahlen 312 313 314 23 24 26 27 28 30 31 32 212 213 in Spalte B darstellen.

Ist dies vielleicht mit einer Formel machbar?

Vielen Dank im Voraus für eure Hilfe.


Gruß

Manzur

 

Hallo Manzur,

ich habe folgende VBA-Funktion gebastelt, die das bewerkstelligt:

Code:

Public Function ZahlenListe(ListRg As Range) As Long()
  Dim Zelle As Range
  Dim Z As New Collection, Zvb$(), Za&()
  Dim I&, IstSpalte As Boolean, F$
' ---- Ermittle alle Collection-Einträge
  For Each Zelle In ListRg.Cells
    If InStr(Zelle, "-") Then
      Zvb$ = Split(Zelle, "-")
      For I& = Zvb(0) To Zvb(1)
        Z.Add I&
      Next I&
    ElseIf InStr(Zelle, ",") Then
      Zvb$ = Split(Zelle, ",")
      For I& = 0 To UBound(Zvb$)
        Z.Add CLng(Zvb$(I&))
      Next I&
    Else
      Z.Add Zelle.Value
    End If
  Next Zelle
' ---- Ermittle ob Spalten- oder Zeilen-Array (CurrentArray funkt.nicht)
  With Application.ThisCell
    F$ = .FormulaArray
    With .Offset(1, 0)
      IstSpalte = .HasArray And .FormulaArray = F$
    End With
  End With
' ---- Erstelle Rückgabe-Array
  If IstSpalte Then ReDim Za&(1 To Z.Count, 0) Else ReDim Za&(0, 1 To Z.Count)
  For I& = 1 To Z.Count
    If IstSpalte Then Za&(I&, 0) = Z(I&) Else Za&(0, I&) = Z(I&)
  Next I&
  ZahlenListe& = Za&
End Function

Es ist eine Array-Funktion. Installiert wird sie folgendermaßen:
1) Markiere obigen VBA-Code mit der Maus; drücke die Tastenkombination Strg+C, um ihn in die Zwischenablage zu kopieren.
2) Wechsle zurück in Dein Excel-Arbeitsblatt, drücke dort die Tastenkombination Alt+F11, um in den VBA-Editor zu gelangen.
3) Im VBA-Editor öffne im Menü 'Einfügen > Codemodul' ein allg. Codemodul; drücke dort die Tastenkombination Strg+V, um den VBA-Code ins Codemodul einzufügen.
4) Gehe wieder zurück in Dein Excel-Arbeitsblatt. Damit ist die Funktion installiert.

5) Um die Array-Funktion im Arbeitsblatt anzuwenden, mache Folgendes:
a) Angenommen, Deine Vorgabeliste "312-314 | 23,24 | ... | 212,213" befinde sich im Bereich A1:A5
b) Markiere den Zellbereich (Spaltenbereich oder Zeilenbereich), der die zu ermittelnde Zahlenliste aufnehmen soll.
c) Gib in die Formeleditierzeile folgende Formel ein: =ZahlenListe(A1:A5)
d) Beende die Formeleingabe nicht mit der normalen Eingabe-Taste, sondern mit der Tastenkombination Strg+Umschalt+Eingabe.
e) Daraufhin müsste die eingegebene Formel in geschweiften Klammern eingeschlossen erscheinen und andererseits müsste im im Punkt 5b) markierten Bereich die gesuchte Zahlenliste enthalten sein.
f) Ist der in 5b) markierte Bereich größer als vorhandene Zahlen in der Liste, werden die überzähligen Zellen mit '#NV' ausgefüllt, was eine Kennung darstellt, dass für diese Zellen keine Zahlen verfügbar sind.

 

Danksagung

Hallo,

Danke für deine schnelle Hilfe.

Allerdings habe ich mit VBA noch nie etwas zu tun gehabt. Daher klappt es wohl nicht bei mir.

Vielleicht geht es auch mit Hilfsspalten und Formeln??

Danke nochmals.


Gruß


Manzur

 

Hallo!

Letztenendes geht es auch mit Formeln, aber die gezeigte VBA-Variante ist 1000mal komfortabler.
Alle Formeln sind nach unten ausfüllbar, die Formel aus F1 zusätzlich auch nach rechts (bis Spalte Y).
In Spalte C werden die in Spalten D:Y ermittelten Zahlen untereinander geschrieben.
In Spalte B werden die Leerzeilen aus Spalte C eliminiert.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	312-316	312	312	312	313	314	315	316																		5
2	23,24	313	313	23	24																					2
3	26-28	314	314	26	27	28																				3
4	30-32	315	315	30	31	32																				3
5	212,213	316	316	212	213																					2
6	99	23	23	99																						1
7		24	24																							0
8		26																								0
9		27																								0
10		28
11		30	26
12		31	27
13		32	28
14		212
15		213
16		99	30
17			31
18			32
19
20
21			212
22			213
23
24
25
26			99
27
28
29
30
31
32			0
33			0
34

Zelle	Formel
B1	{=WENN(ZEILE()>SUMME(Z:Z);"";INDEX(C:C;KKLEINSTE(WENN(C$1:C$999<>"";ZEILE($1:$999));ZEILE())))}
C1	=INDEX(BEREICH.VERSCHIEBEN(D:D;;;;MAX(Z:Z));(ZEILE()-1)/MAX(Z:Z)+1;REST(ZEILE()-1;MAX(Z:Z))+1)
D1	=WENN(A1="";"";WENN(ISTZAHL(FINDEN("-";A1));1*LINKS(A1;FINDEN("-";A1)-1);WENN(ISTZAHL(FINDEN(",";A1));LINKS(A1;FINDEN(",";A1)-1)*1;A1)))
E1	=WENN(D1="";"";WENN(ISTZAHL(FINDEN("-";A1));WENN(D1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);D1+1;"");WENN(ISTZAHL(FINDEN(",";A1));TEIL(A1;FINDEN(",";A1)+1;99)*1;"")))
F1	=WENN(E1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(E1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);E1+1;"");""))
G1	=WENN(F1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(F1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);F1+1;"");""))
H1	=WENN(G1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(G1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);G1+1;"");""))
I1	=WENN(H1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(H1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);H1+1;"");""))
J1	=WENN(I1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(I1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);I1+1;"");""))
K1	=WENN(J1="";"";WENN(ISTZAHL(FINDEN("-";$A1));WENN(J1+1<=1*TEIL($A1;FINDEN("-";$A1)+1;99);J1+1;"");""))
Z1	=SUMMENPRODUKT(N(D1:Y1<>""))

<table><tr><td>Achtung, Matrixformel enthalten!</td></tr><tr><td><span>Die geschweiften Klammern{} werden </span><span>nicht</span><span> eingegeben.</span></td></tr><tr><td><span>Verlassen Sie den Zelleneditor mit </span><span>Strg+Shift + Enter</span><span>, statt Enter alleine.</span></td></tr></table>[/parsehtml]