Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Extrem große Datenmengen verarbeiten und zuordnen

Betrifft: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 17.11.2014 17:42:26

Hallo,
ich habe folgende Aufgabe: Herunterbrechen von sehr großen Sammelrollen in kleinere funktionelle Einheiten (mehrere Einzelrollen), Dazu muss ich für die Sammelrollen herausfinden, welche TA sie enthalten und in welchen anderen Rollen diese TA enthalten sind.
Ziel: E:DE:SD_CAM MANAGER FOR DE enthält als einzige Rolle TA Z4D1 behalten
TA Z4D1 auch in Einzelrollen E:DE:Create Sales Order, etc. enthalten, nicht behalten
Datenmaterial: Abruf aus SAP R/3
Also Rolle E:DE:SD_CAM MANAGER FOR DE - TA Z4D1 - in E:DE:Create Sales Order, E:DE:Change Sales Order, etc.
Problem: 135.000 Zeilen, 230 Spalten, 1.215.000 Zellen
Auch eine Darstellung als Pivottabelle ist absolut nicht hilfreich, da es sich um extrem viele Daten handelt, die so nicht übersichtlich dargestellt werden können.

  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 17.11.2014 17:44:15

https://www.herber.de/bbs/user/93833.xlsx
Da ist die Datei (ist nur eine Musterdatei, nicht die tatsächliche, die ist einfach zu groß), ich weiß aber nicht wie man sie hier reinstellt


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 17.11.2014 17:45:42

Für Eure Hilfe wäre ich sehr dankbar


  

Betrifft: Extrem große Datenmengen verarbeiten und zuordnen von: Erwin
Geschrieben am: 17.11.2014 20:36:42

Hallo Barry,
1. wenn unerledigt, dann Frage auf offen stellen (also Haken rein in Antwortformular)
2. für Excel ist das nicht wenig, vor allem bei den vielen Spalten; überleg mal ob das evtl besser in Access zu machen ist
3. ich habe keine Lösung
Grüße - Erwin


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 18.11.2014 04:53:22

Hallo Erwin,
Danke für Deine Antwort.

Bei der Spaltenzahl habe ich mich geirrt, so viele sind es gar nicht.
In der Mustertabelle in Tab Daten
Bereich - User ID - Rollenname - Role Type - TA -TA Beschreibung - CLUSTER - Lager - Sales Org
Wir sortieren nur nach Rollenname, Rollentyp (Sammel- oder Einzelrolle) und TA.
Es sind allerdings in Wirklichkeit tatsächlich 135.000 Zeilen.

Ich dachte, für Excel-Füchse sei das kein Problem. Vielleicht sind es ja auch drei Arbeitsschritte.
Tab Daten
Spalte C - Rollenname
Spalte D - Rollentyp (Sammel- oder Einzelrolle)
Spalte E - TA

Tab Ergebnis
Suche Sammelrolle
Sammelrolle A
Spalte F - alle TAs zu Sammelrolle A
Sammelrolle A - TA 1
Sammelrolle A - TA 2
Sammelrolle A - TA 3
(das lässt sich mit SVWEIS noch relativ einfach machen)

Jetzt kommt es:
Nimm TA 1 aus Sammelrolle A und suche aus Tab Daten alle Rollennamen, die diese TA enthalten.
Auswertung:
ROT: TA 1 kommt in Sammelrolle A, Sammelrolle B, Einzelrolle X vor, Konsequenz: LÖSCHEN
GRÜN: TA 3 nur und ausschließlich in Sammelrolle A enthalten, Konsequenz: BEHALTEN

Ziel:
Identifizierung von TA, die nur und ausschließlich in der Sammelrolle enthalten sind.
Herauslöschen von TA, die in der Sammelrolle enthalten sind.

Hört sich doch eigentlich gar nicht so schwer an, nur ich weiß nicht, wie ich dahinkomme.

Vielen Dank und Gruss
Gruss


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: fcs
Geschrieben am: 18.11.2014 16:53:38

Hallo Barry,

hier mal ein Ansatz. Ich bin mir aber aber nicht sicher, ob ich die Verarbeitung der Einzelrollen korrekt verstanden hab. Im Moment werden sie ignoriert.

Gruß
Franz

'Erstellt unter Excel 2010
'Code in einem allgemeinen Modul
Sub TA_Sortieren_extrahieren()
'
' Sortieren Makro
'
  Dim wksData As Worksheet, wksErgebnis As Worksheet
  Dim Zeile_D As Long, Zeile_D1 As Long, Zeile_DL As Long, Spalte_DL As Long
  Dim arrData, arrErgebnis, iCount As Integer, iCount2 As Long, Spalte
  Dim strRolle As String, strTA As String
'
  Set wksData = ActiveWorkbook.Worksheets("Daten")
  Set wksErgebnis = ActiveWorkbook.Worksheets("Ergebnis")
  
  With wksData
    'Daten sortieren nach Role Type / Tellenname / TA
      If .AutoFilterMode = True Then
        If .FilterMode = True Then .ShowAllData
      Else
        .UsedRange.AutoFilter
      End If
      
      .AutoFilter.Sort.SortFields.Clear
      .AutoFilter.Sort.SortFields.Add Key:=.Range("D2"), _
          SortOn:=xlSortOnValues, Order:=xlDescending, _
          DataOption:=xlSortNormal
      .AutoFilter.Sort.SortFields.Add Key:=.Range("C2"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, _
          DataOption:=xlSortNormal
      .AutoFilter.Sort.SortFields.Add Key:=.Range("E2"), _
          SortOn:=xlSortOnValues, Order:=xlAscending, _
          DataOption:=xlSortNormal
      With .AutoFilter.Sort
          .Header = xlYes
          .MatchCase = False
          .Orientation = xlTopToBottom
          .SortMethod = xlPinYin
          .Apply
      End With
      'letzte Daten-Zeile ermitteln
      Zeile_DL = .Cells(.Rows.Count, 1).End(xlUp).Row
      'letzte Daten-Spalte setzen/berechnen
      Spalte_DL = 9 ' .Cells(1, .Columns.Count).End(xlToLeft).Column
      'Daten in Array einlesen
      arrData = .Range(.Cells(1, 1), .Cells(Zeile_DL, Spalte_DL + 1))
      'Titelzeile als zu übernehmen kennzeichnen
      arrData(1, Spalte_DL + 1) = True: iCount2 = 1
      
      For Zeile_D = 2 To Zeile_DL
        'Prüfen, ob Zeile schon mit True oder False gekennzeichnet wurde
        If IsEmpty(arrData(Zeile_D, Spalte_DL + 1)) Then
          'Rollenname in Variable einlesen, wenn er wechselt
          If strRolle <> arrData(Zeile_D, 3) Then
            strRolle = arrData(Zeile_D, 3)
          End If
          'Prüfen, ob Sammelrolle
          If arrData(Zeile_D, 4) = "Sammelrolle" Then
              'TA in Variable einlesen
              strTA = arrData(Zeile_D, 5)
              'Zähler auf 1 setzen
              iCount = 1
              'Liste bis zum Ende abarbeiten
              For Zeile_D1 = Zeile_D + 1 To Zeile_DL
                'Prüfen, ob TA übereinstimmt
                If arrData(Zeile_D1, 5) = strTA Then
                'Prüfen, ob Sammelrolle
                If arrData(Zeile_D1, 4) = "Sammelrolle" Then
                'Prüfen, ob Rollenname verschieden
                If arrData(Zeile_D1, 3) <> strRolle Then
                  iCount = iCount + 1
                  arrData(Zeile_D, Spalte_DL + 1) = False
                End If
                End If
                End If
              Next Zeile_D1
              If iCount = 1 Then
                'TA kommt nur einmal vor
                arrData(Zeile_D, Spalte_DL + 1) = True: iCount2 = iCount2 + 1
              Else
                arrData(Zeile_D, Spalte_DL + 1) = False
              End If
              iCount = 0
          Else
            'Einzelrollen
            arrData(Zeile_D, Spalte_DL + 1) = False ': iCount2 = iCount2 + 1
          End If
        End If
      Next Zeile_D
  End With 'wksData
  
  'Ergebnis-Array dimensionieren
  ReDim arrErgebnis(1 To iCount2, 1 To Spalte_DL)
  iCount2 = 0
  
  'Alle mit True markierten Zeilen ins ErgebnisArray übernehmen
  For Zeile_D = 1 To Zeile_DL
    If arrData(Zeile_D, Spalte_DL + 1) = True Then
      iCount2 = iCount2 + 1
      For Spalte = 1 To Spalte_DL
        arrErgebnis(iCount2, Spalte) = arrData(Zeile_D, Spalte)
      Next
    End If
  Next Zeile_D
  
  Application.ScreenUpdating = False
  With wksErgebnis
    'Inhalte löschen
    .UsedRange.ClearContents
    'Ergebnis-Array eintragen
    .Cells(1, 1).Resize(iCount2, Spalte_DL) = arrErgebnis
  End With
  Application.ScreenUpdating = True
  
  Erase arrErgebnis, arrData
End Sub



  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 19.11.2014 05:04:31

Guten Morgen Franz,
großartig. Wo hast Du das denn so schnell hergezaubert?
Ich habe noch nie mit VBA-Scripts gearbeitet, höchstens bereits erstellte benutzt. Also ich gehe in die "Entwickleransicht", kopiere Dein Script und aktiviere ein Kontrollkästchen. Makro ausführen und dann sehe ich ob es läuft. Bei Fehlermeldung "Debug"....ich werde es einfach probieren und mich heute abend oder morgen wieder melden.
Bis dahin erst einmaml ganz herzlichen Dank an Dich, Franz.
Gruss, Barry


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: fcs
Geschrieben am: 19.11.2014 10:54:15

Hallo Barry,

im Makro ist noch ein kleiner aber folgenschwerer Fehler enthalten, den ich beim Testen übersehen hab.
Es muss einmal Zeile_D1 statt Zeile_D in der inneren For-Next-Schleife sein
Gruß
Franz

                'Prüfen, ob Rollenname verschieden
                If arrData(Zeile_D1, 3) <> strRolle Then
                  iCount = iCount + 1
                  arrData(Zeile_D1, Spalte_DL + 1) = False
                End If



  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 19.11.2014 17:40:37

Hallo Franz,
ich habe Dein Makro getestet. Erst gab es Probleme wie "Index außerhalb des gültigen Bereichs" und "Typen unverträglich", doch dann funktionierte es einwandfrei.
Ich habe einen Kollegen zu Rate gezogen. Seine Überlegungen sind:
1. Prüfe Sammelrolle
2. wenn ja, prüfe 1. Zeile gleiche Rolle bis n (n also letzte Zeile)
3. wenn nein TA - Sammelrolle = TA Prüfrolle
4. wenn ja Ergebnisfeld: Sammelrolle, TA, Rollenname
Also das Ergebnisfeld ist das Entscheidende und das muss hinreichend definiert sein.
Einzel- oder Sammelrolle ist eigentlich nicht das Entscheidende.
Sondern alle TAs zur gegebenen Sammelrolle, welche dieser TAs sind in welchen anderen Rollen enthalten? Also im Ergebnisfeld soll stehen: Sammelrolle 1 - alle zugehörigen TAs (TA1 - TA1000) und alle anderen Rollen, welche die TAs von Sammelrolle 1 enthalten.

Das Ergebnis soll damit eine Entscheidungsbasis liefern: TA 1 nur in Sammelrolle 1 enthalten -BEHALTEN
oder TA1 auch in anderen Rollen (egal ob andere Sammel- oder Einzelrollen) enthalten - LÖSCHEN

Ist vielleicht auch zu aufwendig?
Gruss, Barry


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: fcs
Geschrieben am: 20.11.2014 12:24:13

Hallo Barry,

ich hab mal versucht das Makro in die Richtung umzuschreiben.
Im Ergebnisblatt werden im Moment alle Zeilen angezeigt. Diese kann man jetzt per Autofilter oder auch Pivot-Bericht zeilenmäßig komprimieren.

Das sollte zumindest für weitere Modifikationen des Makros/der Prüfungen im Makro eine Basis sein.

Gruß
Franz

Deine Beispieldatei mit Makr (alt und neu)
https://www.herber.de/bbs/user/93907.xlsm


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 21.11.2014 04:43:05

Guten Morgen Franz,
das ist genau das, was ich gesucht habe. Großartig, klasse!! Ich muss anhand der Datengröße das Ergebnisblatt so einstellen, dass immer nur eine Rolle mit ihren zugehörigen TAs dargestellt wird. Franz, ein ganz großes Dankeschön an Dich und die Mühe, die Du Dir gemacht hast. Vielen, vielen Dank!! Gruss, Barry


  

Betrifft: AW: Extrem große Datenmengen verarbeiten und zuordnen von: Barry White
Geschrieben am: 20.11.2014 05:01:40

Guten Morgen Franz,
D1 habe ich korrigiert aber das ist es nicht.
Das Geheimnis liegt in der richtigen Definition des Ergebnisfeldes.

Anderer Ansatz:
neuer Tab: Eingabe einer beliebigen Rolle, Aktivierung Makro und als Ergebnis alle TAs zu dieser Rolle und alle Rollen zu diesen TAs.
Ich kann mich da leider so schlecht artikulieren, worauf ich eigentlich hinaus will.

Gruss,
Barry


 

Beiträge aus den Excel-Beispielen zum Thema "Extrem große Datenmengen verarbeiten und zuordnen"

Einen Wert in einem zweiten Blatt einer Kalenderwoche zuordnen
Adresse bei mehrfach vorkommenden Namen zuordnen
Telefonnummern den Telefonierern zuordnen