Office: (Office 365) XVERWEIS - Suche bei verbundenen Feldern

Guten Abend zusammen,

ENDLICH wurde bei uns Office 365 installiert und die Funktion XVERWEIS stand zur Verfügung. Ende mit der ständigen Anpassung der Tabelle beim Hinzufügen neuer Kostenstellen. Aber....klappt noch nicht ganz.

Zur Veranschaulichung habe ich die alte Tabelle eingefügt (ORIGINAL) und die neue Tabelle (ORIGINAL_Kopie) hinzugefügt.

Was sieht man da? Mit Makro wird eine Tabelle aus SAP exportiert (1:1 Kopie). DIese erscheint unter Reiter "import_ZPS_CO" (Daten anonymisiert). Da diese mehr Daten als nötig beinhaltet und relativ unübersichtlich ist, werden die benötigten Daten in eine Art Auswertetabelle (Reiter: Folgeebene) übertragen und mit div. Funktionen erweitert / veranschaulicht.

Die Datenübernahme erfolgt aktuell per fester Zuweisung. Problem ist: verändert sich die Reihenfolge in der Import-Datei oder komme eine neue Kostenstelle hinzu - beginne ich die Zuordnung von vorne. Die Datei zeigt jetzt nur einen kurzen Ausschnitt und ist real viiiiiel länger.

Bei der Datei KOPIE habe ich bei der KST 661 (Kostenstelle 661) die IST_KST (Ist-Kosten) mit XVERWEIS realisiert. Problem ist, dass in der Import-Datei jede Kostenstelle als verbundenes Feld 2 Spalten in sich trägt: IST_KST (Ist-Kosten) und PLAN_KST (Plan-Kosten). Nun komme ich nicht wirklich drauf, wie ich die PLAN_KST anzeigen kann (diese müssten nun in der gelben Spalte D erscheinen).

Faktisch müsste ich irgendwie noch eine 2-te Suche Starten, die bei der gefundenen KST 661 im Fall A nach IST_KST und im Fall B nach PLAN_KST sucht....

Bitte um HIlfe...

 

Hallo,

in der beiliegenden Datei habe ich dir einige Änderungen eingebaut:
Ich bin davon ausgegangen, dass du in Zelle C2 bzw. F2 deine Kostenstellen eingibst: KST 661 bzw. KST 664 (habe diese rot eingefärbt).

Die Zellen mit blauer Schrift enthalten Formeln, die von diesen beiden Zellen abhängen:
Formel in Zelle C1: =XVERGLEICH(C2;import_ZPS_CO!$3:$3;0;1)
Formel in Zelle F1: =XVERGLEICH(F2;import_ZPS_CO!$3:$3;0;1)
Diese beiden Formeln ermitteln die SpaltenNr., ab der die Kostenstellen im Import-Blatt enthalten sind.

Formel in Zelle A4: =ANZAHL2(import_ZPS_CO!$A$6:$A$999)
Diese Formel ermittelt die Anzahl der Maschinenzeilen. Die Zellangabe $A$999 müsstest du an deine maximal vorkommenden Maschinenzeilen anpassen.

Formeln im Zellbereich C3:D5 bzw. Zellbereich F3:G5 ermitteln die zugehörigen Kostenstellenbeschriftungen.

Formel in C6: =INDEX(import_ZPS_CO!$A$6:$ZZ$100;SEQUENZ($A$4;1;6);SEQUENZ(1;2;C1))
Formel in F6: =INDEX(import_ZPS_CO!$A$6:$ZZ$100;SEQUENZ($A$4;1;6);SEQUENZ(1;2;F1))
Diese beiden Array-Formeln ermitteln die IST/PLAN-Kostenstellenwerte. Sie sind nur in diesen beiden Zellen enthalten und blenden (in diesem Beispiel) alle Zahlen im Bereich C6:D37 bzw. F6:G37 ein. In diesen Einblendebereichen dürfen sonst keine Zahlen oder Formeln enthalten sein - sonst wird der Fehler #ÜBERLAUF! angezeigt. Die Zellangabe $ZZ$100 müsstest du wiederum an deine Maximal-Verhältnisse im Import-Blatt anpassen.

Ich habe dir ebenfalls die Formeln im Bereich E6:E37 bzw. H6:H37 simplifiziert. Die waren unnötig kompliziert abgefasst.

Die Informationen, die du nicht angezeigt bekommen möchtest zB. in C1, F1 oder A4, kannst du ja mit gleicher Farbe wie der Zellhintergrund quasi unsichtbar machen.

 

Hallo,

habe gerade bemerkt, dass die beiden Formeln in C6 bzw. F6 einen Fehler enthielten.
Korrekte Formel in C6: =INDEX(import_ZPS_CO!$A$6:$ZZ$100;SEQUENZ($A$4;1;1);SEQUENZ(1;2;C1))
Korrekte Formel in F6: =INDEX(import_ZPS_CO!$A$6:$ZZ$100;SEQUENZ($A$4;1;1);SEQUENZ(1;2;F1))

Korrigierte Datei liegt bei.

 

top! DANKE!!!! Nach 5 Std. Bastelarbeit ist nun die komplette Tabelle (VOllversion) umgebaut! SCheint zu funktionieren!


Frage: besteht die Möglichkeit alle Zeiten > 32 (dyn. ermittelt) & < 120 (feste FElder in der Vorlage) automatisch (abhängig von der ermittelten Anzahl der Zeilen) auszublenden??? (danach kommt die Summenbildung...bei großen Projekten kein Problem, aber bei kleineren müsste man immer scrollen).

 

Hallo,

du möchtest also Zellen, die bei den IST/PLAN-Stunden im Intervall (32..120) exklusive liegen, ausblenden.
Probleme:
1) Das geht natürlich nur mit ganzen Zeilen. Was ist aber, wenn in einer anderen Spalte derselben Zeile ein h-Wert außerhalb dieses Intervalls steht?
2) Ein weiteres Problem ist, dass das Ausblenden nur mittels VBA-Makro geschehen kann (oder eben händisch, was du ja eh bereits kannst).

Oder meinst du mit "ausblenden" irgendetwas anderes?

 

Hi,

zu 1) wird es nicht geben. Die Anzahl der Zellen legt die Anzahl der Maschinen im Projekt fest. Somit immer im Projekt fest (max. aber 120 bei größeren Projekten)

zu 2) genau, mit Makro hätte ich das auch versucht zu basteln. Es sei denn es gibt einen einfacheren Weg.
Wenn Du eine Idee zum Makro hättest, wäre es geil (kostet mir bestimmt 1-2 Tage Bastelarbeit :) )

 

Hallo,

ich gehe in den folgenden beiden Makros vom Datenbestand, wie er sich in obigen Postings zeigt, aus, wobei das Arbeitsblatt "Folgenebene" das aktive Arbeitsblatt sein muss.
Die Maschenanzahl befinde sich aufgrund meiner Formel in Zelle A4.
Falls dort 32 enthalten ist, reichen die Maschinenzeilen bis Zeile 37 inklusive, was bedeutet, dass die Zeilen 38-120 auszublenden sind.

Code:

Public Sub ZeilenAusblenden()
   Dim Ws As Worksheet
   Dim AnzZeilen As Long, rng As Range
  
   Set Ws = ActiveSheet
   AnzZeilen = Ws.Range("A4").Value     '<=== Zelle A4 enthält die Maschinenanzahl
  
   With Ws.Cells(AnzZeilen + 6, 1)
     .Resize(120 - .Row + 1).EntireRow.Hidden = True
   End With
  
End Sub
Public Sub ZeilenEinblenden()
   Dim Ws As Worksheet
   Dim AnzZeilen As Long, rng As Range
  
   Set Ws = ActiveSheet
   AnzZeilen = Ws.Range("A4").Value     '<=== Zelle A4 enthält die Maschinenanzahl
  
   With Ws.Cells(AnzZeilen + 6, 1)
     .Resize(120 - .Row + 1).EntireRow.Hidden = False
   End With
  
End Sub

Diese beiden SUBs sind in ein allgemeines VBA-Codemodul zu speichern, wobei die Namen der SUBs andeuten, wozu sie gut sind. Sie können im Arbeitsblatt mit einem Button verknüpft werden, um sie bequem aufrufen zu können. Ohne Button geht es auch über die Tastenkombination Alt+F8.