Kostenstellen neu verteilen

[coemm]

Hallo @all,

ich bin um Hilfe gebeten worden, die ich leider nicht leisten kann.

Die Daten der alten Kostenstellen sollen nach einem bestimmtem Verteilungsschlüssel neu verteilt werden.

Die Ausgangstabelle:

	A	B	C	D	E	F
2	Kostenstelle alt	Konto	Jan	Feb	Mar	Apr
3	A	10	100	0	-200	0
4	A	20	0	50	0	0
5	B	30	0	0	0	0
6	B	40	0	0	0	234
7	C	50	234	0	234	0

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

Der Verteilungsschlüssel:

	B	C	D
2	KSt (alt)	KSt (neu)	Aufteilung
3	A	E	50%
4	A	F	30%
5	A	G	20%
6	B	W	10%
7	B	X	80%
8	B	Y	5%
9	B	Z	5%
10	C	F	100%

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

So soll die Zieltabelle aussehen:

	A	B	C	D	E	F
2	Kostenstelle neu	Konto	Jan	Feb	Mar	Apr
3	E	10	50	0	-100	0
4	F	10	30	0	-60	0
5	G	10	20	0	-40	0
6	E	20	0	25	0	0
7	F	20	0	15	0	0
8	G	20	0	10	0	0
9	W	30	0	0	0	0
10	X	30	0	0	0	0
11	Y	30	0	0	0	0
12	Z	30	0	0	0	0
13	W	40	0	0	0	23,4
14	X	40	0	0	0	187,2
15	Y	40	0	0	0	11,7
16	Z	40	0	0	0	11,7
17	F	50	234	0	234	0

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

Wie ist das zu lösen?
Geht das mit Hilfe von Formeln oder nur mit Makro?

[Max]

Hallo Conny,

ich glaube nicht, dass das so mit Formel oder VBA lösbar ist!
Wobei ich denke, mit den entsprechenden Informationen kann man das sicherlich sowohl mit Formeln wie auch VBA lösen.
Wie soll denn die Entscheidung getroffen werden, "A" wird "E"," F" oder "G"?
Solange Du darüber keine genaueren Angaben machen kannst, wird Dir hier sicherlich keiner helfen können.

In diesem Sinne
Max

[atilla]

Hallo Conny,

unten stehender Code müsste Deinen Wünschen entsprechen:

Code:

Option Explicit

Sub neu_verteilen()

Dim i As Long, j As Long, k As Long, n As Long, p As Long
Dim lngZA, lngZS, lngZN As Long

Dim wksAlt As Worksheet
Dim wksSchl As Worksheet
Dim wksNeu As Worksheet
Set wksAlt = Worksheets("Kostenstelle alt")
Set wksSchl = Worksheets("Verteilungsschlüssel")
Set wksNeu = Worksheets("Kostenstelle neu")

Application.ScreenUpdating = False

With wksNeu
   lngZN = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
   .Range(.Cells(2, 1), .Cells(lngZN, 6)).ClearContents
End With

With wksAlt
   lngZA = .Cells(.Rows.Count, 1).End(xlUp).Row
End With

With wksSchl
   lngZS = .Cells(.Rows.Count, 2).End(xlUp).Row
      For i = 3 To lngZA
         k = 0
            n = .Range("B1:B" & lngZS).Find(wksAlt.Cells(i, 1)).Row
         Do While .Cells(n + k, 2) = wksAlt.Cells(i, 1)
            wksNeu.Cells(j + 2, 1) = .Cells(n + k, 3)
            wksNeu.Cells(j + 2, 2) = wksAlt.Cells(i, 2)
            For p = 1 To 4
               wksNeu.Cells(j + 2, p + 2) = wksAlt.Cells(i, p + 2) * .Cells(n + k, 4)
            Next p
            k = k + 1
            j = j + 1
         Loop
      Next i
End With

Application.ScreenUpdating = True
End Sub

Ich gehe davon aus, dass es in der Datei 3 Tabellen gibt.
1. Tabelle "Kostenstelle alt" die Daten beginnen ab A2 und gehen bis Spalte F
2. Tabelle "Verteilungsschlüssel" die Daten beginnen ab B2 und gehen bis Spalte D
3. Tabelle "Kostenstelle neu" Die Daten werden ab A2 in die Tabelle geschriben

[Rabe]

Wie ist das zu lösen?
Geht das mit Hilfe von Formeln oder nur mit Makro?

so?

Ursprung

	A	B	C	D	E	F	G	H	I	J	K	L
2	Kostenstelle alt	Konto	Jan	Feb	Mar	Apr				KSt (alt)	KSt (neu)	Aufteilung
3	A	10	100	0	-200	0				A	E	0,5
4	A	20	0	50	0	0				A	F	0,3
5	B	30	0	0	0	0				A	G	0,2
6	B	40	0	0	0	234				B	W	0,1
7	C	50	234	0	234	0				B	X	0,8
8										B	Y	0,05
9										B	Z	0,05
10										C	F	1

Ziel

	A	B	C	D	E	F
2	Kostenstelle neu	Konto	Jan	Feb	Mar	Apr
3	E	10	50	0	-100	0
4	F	10	30	0	-60	0
5	G	10	20	0	-40	0
6	E	20	0	25	0	0
7	F	20	0	15	0	0
8	G	20	0	10	0	0
9	W	30	0	0	0	0
10	X	30	0	0	0	0
11	Y	30	0	0	0	0
12	Z	30	0	0	0	0
13	W	40	0	0	0	23,4
14	X	40	0	0	0	187,2
15	Y	40	0	0	0	11,7
16	Z	40	0	0	0	11,7
17	F	50	70,2	0	70,2	0

verwendete Formeln
Zelle	Formel	Bereich	N/A
C3:F17	=SVERWEIS($A3;Ursprung!$K$2:$L$10;2;0)*SVERWEIS($B3;Ursprung!$B$2:$F$7;SPALTE(B$1);0)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 18.19 einschl. 64 Bit

[atilla]

Hallo Ralf,

ich denke, die Spalten A und B stehen so nicht da. Die müssen auch erst nach Vorgaben der Schlüssel so verteilt werden.

[coemm]

@Atilla,

da hast du recht.

@Ralf,

in Zeile 17 müsste nach dem Verteilungsschlüssel nicht 70,2 sondern 234 stehen. Wo holt sich die Formel diese 30% her?

[Rabe]

Hi Conny,

in Zeile 17 müsste nach dem Verteilungsschlüssel nicht 70,2 sondern 234 stehen. Wo holt sich die Formel diese 30% her?

keine Ahnung, eigentlich dürfte das nicht sein. Das ist mir nachträglich auch aufgefallen, aber ich dachte, ich lasse es drin, da kann jemand mit mehr Ahnung vielleicht etwas dazu sagen.

[schauan]

Hallöchen,

wenn F zweimal definiert ist, dann holt sich der SVERWEIS den ersten Treffer, also die 0,3 und nicht die 1. Du müsstest zum Bleistift ggf. in der Zieltabelle zumindest zum berechnen noch die alte Kostenstelle davor schreiben und dann nach der Kombi ALt/Neu verweisen.

[coemm]

Danke @all,

z.Zt. wird versucht, das Makro von Atilla entsprechend der Originaltabelle anzupassen.

Falls noch Hilfe benötigt wird, werde ich mich melden.

[Rabe]

Hi André,

wenn F zweimal definiert ist, dann holt sich der SVERWEIS den ersten Treffer, also die 0,3 und nicht die 1. Du müsstest zum Bleistift ggf. in der Zieltabelle zumindest zum berechnen noch die alte Kostenstelle davor schreiben und dann nach der Kombi ALt/Neu verweisen.

ne, am besten ist es die Kombination aus "Kostenstelle neu" und "Konto".