Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.10.2025 11:36:58
verschachteltes Dropdown-Menü
17.10.2025 10:28:49
Call Anweisung
16.10.2025 18:38:17
Zusammenfassung nach Personalnummer
16.10.2025 17:40:39
Rabattstafel
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1980to1984
1980to1984
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Werte referenzieren ohne Identifikator

Forumthread: Werte referenzieren ohne Identifikator

Werte referenzieren ohne Identifikator
03.07.2024 01:01:55
tp.pl
Hallo,

ich bin am Ende meiner Kenntnisse angelangt mit folgendem Problem:
Ich möchte einer Liste von Rechnungen (Blatt2) eine Auszahlungsreferenz zuordnen, die sich im Tabellenblatt Transaktionen (Blatt1) befindet. In der Tabelle Transaktionen sind generierte Umsätze durch verschiedene Kunden und deren Auszahlung aufgelistet (Umsatz positiv, Auszahlung negativ). In der Tabelle Rechnungen sind die entsprechenden Rechnungen aufgelistet. Die Beträge von Umsatz und Rechnung gleichen sich jedoch nicht immer, da mehrere Umsätze in einer Rechnung zusammengefasst sein können. Ebenfalls können mehrere Rechnungen in einer Auszahlung überwiesen werden.
Mein Ziel ist, im Blatt 2 in Spalte F die passende Referenznummer der Auszahlung aus Blatt 1 Spalte E aufzulisten.

Beispieldatei anbei.

Ich bin überzeugt Ihr könnt mir weiterhelfen!

https://www.herber.de/bbs/user/170640.xlsx
Anzeige

19
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Werte referenzieren ohne Identifikator
03.07.2024 02:12:01
Onur
Das ist doch wie bei Milchkaffee die Milch und den Kaffe wieder zu trennen.
Wenn Rechnungen zusammengefasst wurden, wie willst du dann die Referenznummer finden? Ausserdem hättest du dann MEHRERE Referenznummern zu einer Rechnung.
Dann hast du auch noch zu wenig Datensätze (Acom +1300,00 € Rechnungen gegenüber +700,00€ Transaktionen).
Ausserdem - was ist mit den Lücken bei Spalte "Firma" ?
Anzeige
AW: Werte referenzieren ohne Identifikator
03.07.2024 14:01:37
tp.pl
Mehrere Referenznummern zu einer Rechnung ist eigentlich nicht möglich. In der zeitlichen Abfolge werden die Werte systemseitig wie folgt generiert:
  • Rechnung
  • Umsätze
  • Auszahlung


    • Und es stimmt, die Beispieldatei enthält nur einen Auszug, wobei es auch hier möglich ist, dass die Rechnung bereits erstellt, die Umsätze aber erst später generiert werden.

    Die Lücken in der Spalte Firma rühren daher, dass dies eine Auszahlung an meine Firma ist.
    Anzeige
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 14:05:11
    Onur
    "Mehrere Referenznummern zu einer Rechnung ist eigentlich nicht möglich" ?
    DU hast doch aber geschrieben: " da mehrere Umsätze in einer Rechnung zusammengefasst sein können"
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 16:58:06
    tp.pl
    Du meinst die Referenznr. der Umsätze. Da hättest du Recht.
    Ich meine allerdings die Referenznr. der Auszahlung.
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 17:16:15
    Onur
    Versuch doch mal anhand deiner geposteten Datei ein paar Rechnungen HÄNDISCH aufzubröseln.
    Dann wirst du sehen, dass das gar nicht möglich ist - wie soll es Excel dann automatisch machen?
    Anzeige
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 18:19:27
    tp.pl
    Vorab: ich habe nach berechtigtem Einwand das komplette Daten Set anonymisiert als Beispieldatei hochgeladen:
    https://www.herber.de/bbs/user/170661.xlsx

    Ich würde es so beschreiben:

  • Ich weiß, dass ungefähr zum Fälligkeitstermin der Rechnungen die Auszahlungen stattfinden (nicht immer der Fall)
  • z.B. im Blatt "Transactions", Spalte 4, Firma Romeo zeigt den Umsatz in Summe von 4.259,69
  • weiter oben sehe ich, dass die nächste Auszahlung -4.259,69 beträgt mit dem Datum 2024-06-04
  • im Blatt "Invoices" suche ich in Spalte E nach Zeiträumen um das Datum 2024-06-04 die den Rechnungsbetrag von 4.259,69 aufzeigen und den selben Firmennamen haben
  • hier würde ich dann in Spalte H die Auszahlungsreferenznr. aus Blatt "Transactions", Zeile E2 eintragen
    • Anzeige
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 08:17:30
    daniel
    HI
    und woran erkennst du, welche Referenznummer zu welchem Auftrag gehört?
    Beschreibe bitte mal, wie du die Aufgabe einem Mitarbeiter erklären würdest, wenn die beiden Listen in Papierform vorliegen würden und er die Zuordnung von Hand machen müsste. Woran würde der Mitarbeiter erkennen, welche Referenznummer er eintragen muss?
    könntest du für das Beispiel die gewünschte Lösung von Hand eintragen?

    Gruß Daniel
    Anzeige
    AW: da noch offen gestellt war und mich interessierte ...
    05.07.2024 11:52:30
    neopa C
    Hallo tp.pl,

    ... nachfolgend mal für Deine zuletzt eingestellte Beispieldatei (170661.xlsx) und gemäß (m)einer momentanen Interpretation Deiner Zielstellung meine Ergebniswerte (gefiltert für lediglich 3 Debitoren). Ermittelt habe ich meine Ergebniswerte mit einer Formel in einer älteren XL-Version als Du im Einsatz hast. Sollte also in Deiner Version eine etwas einfachere zumindest aber kürzere Formellösung möglich sein. Ermittelt habe ich zunächst immer nur die zuerst (zuoberst gelistete Ref.Nr. Sollen alle zutreffenden gelistet werden? Wäre auch möglich. Aber zunächst eben zu Deiner Prüfung nur eine (aus meiner Sicht) zutreffende.

    Arbeitsblatt mit dem Namen 'Invoices'
     ABEF
    1DebitorSummeFälligkeitsdatumReferenzNr.
    2Door90,002024-07-05noch offen
    15Sun423,002024-06-21noch offen
    20Door605,002024-06-04noch offen
    31Sun1.044,002024-05-21noch offen
    36Door925,002024-05-0527385306
    47Sun203,002024-04-2127127702
    53Door865,002024-04-0426840727
    71Sun380,002024-03-2126793146
    78Door1.675,002024-03-0626319264
    86Art3.885,002024-02-2126257371
    101Sun230,002024-02-2126189633
    108Door420,002024-02-08noch offen
    109Door1.225,002024-02-0425758292
    114Art2.560,002024-01-2125715387
    132Sun10,002024-01-2125513059
    137Door890,002024-01-08noch offen
    138Door480,002024-01-0425455440
    246    
    Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
    Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


    Prüfe Du nun mal, ob diese Deiner Ergebniszielstellung entsprechen. Wenn dies nicht der Fall sein sollte, müsstest Du für die von mir gelisteten Debitoren, die von Dir erwarteten Ergebniswerte gegenüberstellen.

    Auf Deine Antwort kann ich jedoch frühestens morgen reagieren.

    Gruß Werner
    .. , - ...
    Anzeige
    AW: da noch offen gestellt war und mich interessierte ...
    07.07.2024 17:19:18
    tp.pl
    Hallo Werner,

    Du referenzierst in deiner Lösung den aktuellsten Umsatz. Was ich jedoch benötige ist die Referenz der Auszahlung. Das sind immer die Zeilen ohne Firmennamen mit Negativbeträgen. Meine erwarteten Ergebnisse in der aktualisierten Beispieltabelle füge ich hinzu.

    https://www.herber.de/bbs/user/170752.xlsx

    Anzeige
    AW: wenn dem so ist, ist es zumindest nicht eindeutig ...
    08.07.2024 10:00:49
    neopa C
    Hallo ... (?),

    ... bzw. es fehlt dann zumindest eine von Dir noch vorzugebende weitere Bedingung. Oder es ist nicht eindeutig lösbar.
    Nach meinen neuen Ermittlungen käme ich für die bisher bekannten Bedingungen momentan zu folgenden Ergebniswerten in Spalte F, die jedoch teilweise im Widerspruch zu Deinen Zielvorgaben stehen.

    Arbeitsblatt mit dem Namen 'Invoices'
     ABEFG
    1DebitorSummeFälligkeitsdatumReferenzNr.Zielvorgabe
    2Door90,002024-07-0527414894noch offen
    15Sun423,002024-06-2127808035noch offen
    20Door605,002024-06-0427414894noch offen
    31Sun1.044,002024-05-2127717535noch offen
    36Door925,002024-05-052741489427414894
    47Sun203,002024-04-212717367427173674
    53Door865,002024-04-042685126626851266
    71Sun380,002024-03-212682683026796166
    78Door1.675,002024-03-062632499126324991
    101Sun230,002024-02-212620584026205840
    108Door420,002024-02-0825765722noch offen
    109Door1.225,002024-02-042576572225765722
    132Sun10,002024-01-212555294425552944
    137Door890,002024-01-0825467272noch offen
    138Door480,002024-01-042546727225467272
    246     
    Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
    Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


    Erkläre mir nun, warum meine gelb markierten Ergebniswerte anders ausgewiesen werden sollen bzw. welche Ergebniswerte da und warum ermittelt werden müssen.

    Gruß Werner
    .. , - ...
    Anzeige
    AW: wenn dem so ist, ist es zumindest nicht eindeutig ...
    08.07.2024 17:27:24
    tp.pl
    Hallo Werner,

  • die Referenz in Zeile 2 und 20 kann nicht stimmen, da hier eindeutig Umsätze in Höhe von 350 und 575 ausgezahlt werden (siehe Zeile 34 und 35 in Blatt "Transactions"
  • für Zeile 15 kann noch keine Referenznr. zugewiesen werden, da der entsprechende Umsatz im Blatt "Transactions" noch nicht erstellt wurde
  • Zeile 71 hast du Recht, da muss ich beim manuellen Zuordnen verrutscht sein
  • bei Zeile 108 kann ich ebenfalls keinen passenden Umsatz finden in Blatt1, am nähesten kommt da nur der Wert aus Zeile 178 i.H.v. 445
  • bei Zeile 137 hast du auch Recht, hier müsste 25467272 stehen, wobei es sich um einen Sonderfall einer Gutschrift handelt


    • Gruß

    Thomas

    Anzeige
    AW: dazu festgestellt ...
    08.07.2024 20:10:24
    neopa C
    Hallo Thomas,

    ... es kristallisiert sich immer mehr heraus, daß Deinerseits zwingend zumindest eine weitere Bedingung definiert werden sollte/muß. Z.B. daß die Zahlung innerhalb von z.B. max 14 (?) Tagen erfolgt(e). Dann lassen sich einige meiner zuletzt noch ermittelten "Ausreißer" z.B. zu Zeile 2 und 15 in "nicht erfolgt" wandeln. Allerdings bleiben auch dann möglicherweise noch Uneindeutigkeiten bestehen.

    Deine Aussagen zu meinem aufgezeigten Ergebnis in Zeile 108, kann ich nicht wirklich nachvollziehen. Du hattest doch gestern Nachmittag geschrieben: "Was ich jedoch benötige, ist die Referenz der Auszahlung. Das sind immer die Zeilen ohne Firmennamen mit Negativbeträgen". insofern verstehe ich Deine Aussagen hierzu momentan nicht.

    Gruß Werner
    .. , - ...
    Anzeige
    weitere Bedingung
    12.07.2024 00:21:57
    tp.pl
    Hallo Werner,

    ich hatte am Anfang meines Beitrags folgendes erwähnt:
    "Ich weiß, dass ungefähr zum Fälligkeitstermin der Rechnungen die Auszahlungen stattfinden (nicht immer der Fall)"
    Ungefähr bedeutet in dem Fall +/- 3 Tage. Gibt sicherlich Sonderfälle mit noch früheren oder späteren Auszahlungen, aber mit der zusätzlichen Bedingung sind 95% der Einträge automatisch zuzuordnen.

    Zur Zeile108: deine zugeordnete Referenznr. 25765722 verweist auf eine Auszahlung die aus den Umsätzen von Door i.H.v. 200, 445, und 580 besteht. Somit kanndas mit dem Umsatz i.H.v. 420 nicht passen.
    Die selbe Referenznr. 25765722 ist eine Zeile weiter unten 109 von uns beiden korrekt zugeordnet.

    Gruß

    Thomas
    Anzeige
    AW: Deine Angaben sind zumindest etwas widersprüchlich ...
    12.07.2024 19:42:08
    neopa C
    Hallo Thomas,

    ... denn wenn Deinen bisherigen Aussagen nach, die von mir ermittelten Werte für Zeile 71, 137 und 138 Deinen Zielvorgaben entsprechen, weichen dort die Zahlungen zum entsprechenden Fälligkeitstermin über 12, 9 und gar 13 Tage ab.
    (Deine Angabe: ".. zum Fälligkeitstermin der Rechnungen die Auszahlungen stattfinden (nicht immer der Fall) Ungefähr bedeutet in dem Fall +/- 3 Tage" konnte ich eben übrigens auch nicht in Deinen Beiträgen nachlesen.

    Wie auch immer, prüfe nun meine nachfolgenden Ergebniswerte, und zwar die neuen in Zeile 31 und 108. Diese Ergebnisse konnte ich wegen dem Spezifikum (welches im betrachteten Bereich ausschließlich in Zeile 108 vorliegt) in meiner älteren XL-Version nur mit Hilfe einer zusätzlichen Formelhilfsspalte (im Ergebnistabellenblatt) lösen.

    Arbeitsblatt mit dem Namen 'Invoices'
     ABEFG
    1DebitorSummeFälligkeitsdatumReferenzNr.Zielvorgabe
    2Door90,002024-07-05noch offennoch offen
    15Sun423,002024-06-21noch offennoch offen
    20Door605,002024-06-04noch offennoch offen
    31Sun1.044,002024-05-2127741092noch offen
    36Door925,002024-05-052741489427414894
    47Sun203,002024-04-212717367427173674
    53Door865,002024-04-042685126626851266
    71Sun380,002024-03-212682683026826830
    78Door1.675,002024-03-062632499126324991
    101Sun230,002024-02-212620584026205840
    108Door420,002024-02-0825791964noch offen
    109Door1.225,002024-02-042576572225765722
    132Sun10,002024-01-212555294425552944
    137Door890,002024-01-082546727225467272
    138Door480,002024-01-042546727225467272
    246     
    Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
    Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


    Gruß Werner
    .. , - ...
    Anzeige
    AW: Deine Angaben sind zumindest etwas widersprüchlich ...
    15.07.2024 01:03:22
    tp.pl
    Hallo Werner,

    sieht aus als wäre das die Lösung nach der ich gesucht habe! Könntest du bitte deine Beispieldatei hochladen?

    Gruß

    Thomas
    AW: wäre möglich, jedoch ...
    15.07.2024 15:40:02
    neopa C
    Hallo Thomas,

    ... würde ich Dir meine Lösung auch ohne diese darlegen.
    Zuerst aber weise ich Dich darauf hin, das ich noch eine ältere XL-Version im Einsatz habe. Mit den neueren Funktionen Deiner XL-Version lässt sich meine Formellösung sicher effektiveren. Ich hab zudem in meiner Lösung eine benannte (Teil-)Formel definiert. Diese von mir als _TDiff benannt, ist bei aktivierter Zelle F2 im Namensmanager zu hinterlegen. Die nachfolgend aufgezeigten Formeln H2 und F2 sind danach nach unten zu kopieren(natürlich bei nicht gefilterter Datentabelle). Meine Auswertung ist aktuell für max 998 Datensätze vorgenommen. Sollte eine Massendatenauswertung (viele tausende Datensätze) notwendig werden, dann ist (m)eine Formellösung nicht effektiv. Alternativ käme dann z.B. eine PQ-Lösung in Betracht.

    Arbeitsblatt mit dem Namen 'Invoices'
     ABEFGHI
    1DebitorSummeFälligkeitsdatumReferenzNr.ZielvorgabeHilfs-Sp 
    2Door90,002024-07-05noch offennoch offen- 
    15Sun423,002024-06-21noch offennoch offen- 
    20Door605,002024-06-04noch offennoch offen- 
    31Sun1.044,002024-05-2127741092noch offen17 
    36Door925,002024-05-05274148942741489434 
    47Sun203,002024-04-21271736742717367452 
    53Door865,002024-04-04268512662685126672 
    71Sun380,002024-03-21268268302682683080 
    78Door1.675,002024-03-062632499126324991119 
    101Sun230,002024-02-212620584026205840136 
    108Door420,002024-02-0825791964noch offen177 
    109Door1.225,002024-02-042576572225765722177 
    132Sun10,002024-01-212555294425552944209 
    137Door890,002024-01-082546727225467272215 
    138Door480,002024-01-042546727225467272215 
    247       

    NameBezug
    _TDiff=AGGREGAT(15;6;ABS(Transactions!$D$2:$D$999-Invoices!$E1)/(Transactions!$A$2:$A$999=Invoices!$A1);1)

    ZelleFormel
    F2=WENN(ANZAHL2(A2:B2;E2)3;"";WENNFEHLER(INDEX(Transactions!E:E;AGGREGAT(14;6;ZEILE(Transactions!A$2:A$999)/(MAX(B2;SUMMEWENNS(Transactions!B:B;Transactions!A:A;A2;Transactions!D:D;INDEX(Transactions!D:D;H2)))+SUMMEWENN(H3:H$999;H2;B3:B$999)=-(Transactions!B$2:B$999))/(ZEILE(Transactions!D$2:D$999)H2);1)/ISTZAHL(H2));"noch offen"))
    H2=WENN(ANZAHL2(A2:B2;E2)3;"";WENNFEHLER(AGGREGAT(15;6;ZEILE(Transactions!$E$2:$E$999)/(ABS(Transactions!$D$2:$D$999-$E2)/(Transactions!$A$2:$A$999=$A2)=_TDiff)/(_TDiff21)*(Transactions!$A$2:$A$999=$A2);1);"-"))
    Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
    Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


    Gruß Werner
    .. , - ...
    Anzeige
    AW: ergänzend hierzu ...
    17.07.2024 14:08:49
    neopa C
    Hallo Thomas,

    ... natürlich könnte ich auch die Beispieldatei dazu mit einstellen. Aber so wie von mir als Lösung aufgezeigt, ist es zwar für Dich ein wenig aufwendiger aber könnte/sollte für Dich ein wenig höheren Nutzwert haben.

    Auf jeden Fall interessiert mich nun natürlich, ob meine aufgezeigte Lösung Deiner Zielstellung entspricht?

    Gruß Werner
    .. , - ...
    Anzeige
    AW: nachgefragt ...
    11.07.2024 20:02:36
    neopa C
    Hallo Thomas,

    ... hast Du mittlerweile eine Lösung oder ist eine solche für Dich nicht mehr von Interesse?
    Mich interessiert eine solche noch immer, allerdings ohne Antworten auf meine zuvor gestellte Frage zu erhalten, sehe ich wenig Erfolgsaussicht.

    Gruß Werner
    .. , - ...
    Anzeige
    AW: Werte referenzieren ohne Identifikator
    03.07.2024 16:55:47
    tp.pl
    Vorab: ich habe nach berechtigtem Einwand das komplette Daten Set anonymisiert als Beispieldatei hochgeladen:
    https://www.herber.de/bbs/user/170661.xlsx

    Ich würde es so beschreiben:

  • Ich weiß, dass ungefähr zum Fälligkeitstermin der Rechnungen die Auszahlungen stattfinden (nicht immer der Fall)
  • z.B. im Blatt "Transactions", Spalte 4, Firma Romeo zeigt den Umsatz in Summe von 4.259,69
  • weiter oben sehe ich, dass die nächste Auszahlung -4.259,69 beträgt mit dem Datum 2024-06-04
  • im Blatt "Invoices" suche ich in Spalte E nach Zeiträumen um das Datum 2024-06-04 die den Rechnungsbetrag von 4.259,69 aufzeigen und den selben Firmennamen haben
  • hier würde ich dann in Spalte H die Auszahlungsreferenznr. aus Blatt "Transactions", Zeile E2 eintragen
    • Anzeige
    ;

    Beliebteste Forumthreads (12 Monate)

    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
    Anzeige
    Anzeige
    Entdecke mehr
    Finde genau, was du suchst

    Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

    Suche nach den besten Antworten
    Unsere beliebtesten Threads

    Entdecke unsere meistgeklickten Beiträge in der Google Suche

    Top 100 Threads jetzt ansehen
    Anzeige