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

Helfe beim Thema XVERWEIS - Suche bei verbundenen Feldern in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; 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... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von elta_gw, 12. Oktober 2022.

  1. elta_gw Neuer User

    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...
     
    elta_gw, 12. Oktober 2022
    #1
  2. Exl121150 Erfahrener User
    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.
     
    Exl121150, 13. Oktober 2022
    #2
  3. Exl121150 Erfahrener User
    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.
     
    Exl121150, 13. Oktober 2022
    #3
  4. elta_gw Neuer User

    XVERWEIS - Suche bei verbundenen Feldern

    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).
     
    elta_gw, 14. Oktober 2022
    #4
  5. Exl121150 Erfahrener User
    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?
     
    Exl121150, 14. Oktober 2022
    #5
  6. elta_gw Neuer User
    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 :) )
     
    elta_gw, 14. Oktober 2022
    #6
  7. Exl121150 Erfahrener User
    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.
     
    Exl121150, 14. Oktober 2022
    #7
Thema:

XVERWEIS - Suche bei verbundenen Feldern

Die Seite wird geladen...
  1. XVERWEIS - Suche bei verbundenen Feldern - Similar Threads - XVERWEIS Suche verbundenen

  2. XVERWEIS funktioniert nicht

    in Microsoft Excel Hilfe
    XVERWEIS funktioniert nicht: Hallo, ich bin da ein wenig verwirrt und finde den Fehler nicht. Ich möchte gerne eine Artikelnummer eingeben und Excel soll aus einer Artikelliste die korrekte Bezeichnung heraussuchen. Sollte...
  3. XVERWEIS mit "Versatz" suchen

    in Microsoft Excel Hilfe
    XVERWEIS mit "Versatz" suchen: Servus Office-Gemeinde, ich glaube ich treibe Xverweis gerade in die Superlative (für mein Excel-Verständnis) - zumindest hoffe ich, dass es per Xverweis geht und ich nicht auf dem Holzweg bin....
  4. XVERWEIS und Textverketten

    in Microsoft Excel Hilfe
    XVERWEIS und Textverketten: Hallo zusammen, Ich habe mir einen Kalender in Excel gebaut, in dem ich nur das Jahr ändern muss, und alle Feiertage und Geburtstage werden dann automatisch in die entsprechenden Zellen...
  5. XVERWEIS und Hyperlink

    in Microsoft Excel Hilfe
    XVERWEIS und Hyperlink: Hallo zusammen Ich hab in einem Excel zwei Tabellenbläter. In dem einen werden die ganzen Daten in einer Tabelle erfasst und das zweite dient zur Übersicht/Zusammenzug. Dort arbeite ich mit der...
  6. Daten mit sVerweis oder xverweis raussuchen

    in Microsoft Excel Hilfe
    Daten mit sVerweis oder xverweis raussuchen: Hallo zusammen, Ich bin mir nicht sicher ob mein Problem mit sverweis oder xverweis gelöst werden kann, aber ich denke das es eine der Funktionen ist. Zu meinem Problem: Ich habe mehrere...
  7. XVERWEIS auf Datenimport

    in Microsoft Excel Hilfe
    XVERWEIS auf Datenimport: Hallo, folgendes Problem mit dem XVERWEIS - dieser bezieht sich auf ein zweites Tabellenblatt, das importierte Daten aus PowerQuery enthält. Wenn in der Ursprungsdatei Spalten hinzukommen, ist...
  8. Datenüberprüfung mit XVERWEIS

    in Microsoft Excel Hilfe
    Datenüberprüfung mit XVERWEIS: Hallo, ich habe eine Tabelle mit Anwesenheitslisten. Wer Anwesend ist soll ein x eintragen. Zudem wird in Blatt2 die Anzahl x unterteilt in mehrere Gruppen gezählt und bei Überschreitung von z.B....
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden