Das letzte Clever-Excel-Forum.de - Treffen
fand vom 15. - 17. September 2017 in Friedrichroda /
Thüringen / Region Großer Inselsberg statt.

Kostenstellen neu verteilen
#1
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:

 ABCDEF
2Kostenstelle altKontoJanFebMarApr
3A101000-2000
4A2005000
5B300000
6B40000234
7C5023402340
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


Der Verteilungsschlüssel:

 BCD
2KSt (alt)KSt (neu)Aufteilung
3AE50%
4AF30%
5AG20%
6BW10%
7BX80%
8BY5%
9BZ5%
10CF100%
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg


So soll die Zieltabelle aussehen:

 ABCDEF
2Kostenstelle neuKontoJanFebMarApr
3E10500-1000
4F10300-600
5G10200-400
6E2002500
7F2001500
8G2001000
9W300000
10X300000
11Y300000
12Z300000
13W4000023,4
14X40000187,2
15Y4000011,7
16Z4000011,7
17F5023402340
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?
Gruß Conny Smile
_______________________________________________________________

Ich habe keine Lösung, aber ich bewundere dein Problem !
to top
#2
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
to top
#3
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
Gruß Atilla
to top
#4
(08.12.2014, 21:10)coemm schrieb: Wie ist das zu lösen?
Geht das mit Hilfe von Formeln oder nur mit Makro?

so?

Ursprung
ABCDEFGHIJKL
2Kostenstelle altKontoJanFebMarAprKSt (alt)KSt (neu)Aufteilung
3A101000-2000AE0,5
4A2005000AF0,3
5B300000AG0,2
6B40000234BW0,1
7C5023402340BX0,8
8BY0,05
9BZ0,05
10CF1


Ziel
ABCDEF
2Kostenstelle neuKontoJanFebMarApr
3E10500-1000
4F10300-600
5G10200-400
6E2002500
7F2001500
8G2001000
9W300000
10X300000
11Y300000
12Z300000
13W4000023,4
14X40000187,2
15Y4000011,7
16Z4000011,7
17F5070,2070,20

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

Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#5
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.
Gruß Atilla
to top
#6
@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?
Gruß Conny Smile
_______________________________________________________________

Ich habe keine Lösung, aber ich bewundere dein Problem !
to top
#7
Hi Conny,

(09.12.2014, 14:35)coemm schrieb: 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.
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top
#8
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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top
#9
Danke @all,

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

Falls noch Hilfe benötigt wird, werde ich mich melden.
Gruß Conny Smile
_______________________________________________________________

Ich habe keine Lösung, aber ich bewundere dein Problem !
to top
#10
Hi André,

(09.12.2014, 22:08)schauan schrieb: 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".
Gruß Ralf

?mage

Die deutsche Rechtschreibung ist Freeware, d.h. du kannst sie kostenlos nutzen.
Allerdings ist sie nicht Open Source, deswegen darfst du sie nicht verändern oder in veränderter Form veröffentlichen.
to top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste