Ich habe eine Reihe von Zahlen, die durch Kommas getrennt sind. Jede Reihe stellt eine Reihe eindeutiger Zahlen dar, die sich auf ein Projekt beziehen. Wie kann ich eine Formel schreiben, um ein Referenzblatt nach einer beliebigen Anzahl von Elementen in der Liste zu durchsuchen und einen fest codierten Wert zurückzugeben?
Ich werde unten für jedes Blatt Beispiele (anonymisiert) angeben (Tabellen 1-5) und die beabsichtigte/erwartete Ausgabe. Ich könnte mir vorstellen, dass es etwas mit SUM, FREQUENCY, INDEX oder einer anderen Array-Formel zu tun hat, aber ich komme nicht dahinter. Sie können meine Frage sehenHierauch, aber es enthält weitgehend die gleichen Informationen.
Ich habe "PArsch" "Fail" und "Missing", aber alles funktioniert.
Logik:
Die Formel sollte die folgenden Schritte ausführen.
- Extrahieren Sie die Zahlenliste aus jeder Zelle in Spalte C vonTabelle 4.
- SuchenTisch 3(Spalte D) für Instanzen der Nummern in der Liste.
2b. Wenn eine Nummer in der Liste erscheint, überprüfen Sie, ob das Audit fehlgeschlagen ist (gekennzeichnet durch den Text „fehlgeschlagen“ in Spalte B derTisch 3).
2c. Wenn ja, drucken SieF.
2d. Wenn nicht, drucken SieP. - Wenn keine der Nummern in der Liste erscheint, drucken SieM.
Tabellen:
Tabelle 1
Diese Tabelle ist das Hauptreferenzblatt. Sie listet jede UID mit Name, Server und Projekt auf.
Unique ID Name Server Address Project
10000 Company 1 Server 1 Project 1
10001 Company 2 Server 2 Project 2
10002 Company 3 Server 3 Project 3
10003 Company 3 Server 3 Project 4
10004 Company 3 Server 3 Project 5
10005 Company 3 Server 3 Project 6
10006 Company 4 Server 4 Project 7
10007 Company 4 Server 4 Project 7
10008 Company 4 Server 4 Project 7
10009 Company 5 Server 5 Project 8
10010 Company 6 Server 6 Project 9
10011 Company 7 Server 7 Project 10
10012 Company 8 Server 8 Project 11
10013 Company 8 Server 8 Project 11
10014 Company 8 Server 8 Project 11
10015 Company 8 Server 8 Project 11
10016 Company 8 Server 8 Project 11
10017 Company 8 Server 8 Project 11
10018 Company 8 Server 8 Project 11
10019 Company 8 Server 8 Project 11
10020 Company 8 Server 8 Project 11
10021 Company 9 Server 9 Project 12
10022 Company 10 Server 10 Project 13
10023 Company 11 Server 10 Project 14
10024 Company 12 Server 10 Project 15
10025 Company 13 Server 10 Project 16
10026 Company 14 Server 10 Project 17
10027 Company 15 Server 10 Project 18
10028 Company 16 Server 10 Project 19
10029 Company 17 Server 10 Project 20
10030 Company 18 Server 10 Project 21
10032 Company 19 Server 10 Project 23
10033 Company 19 Server 10 Project 24
Tabelle 2
Diese Tabelle enthält dieselben Informationen wieTabelle 1sondern gruppiert jede UID zu ihrem jeweiligen Projekt. Ich verwende es derzeit nicht, aber es könnte hilfreich sein. Wenn nicht, werde ich es löschen.
Company Trading Name Project UID UID UID UID
Company 1 Project 1 10000
Company 2 Project 2 10001
-----------------------------------------------------
Company 4 Project 7 10006 10007 10008
Tisch 3
In diese Tabelle werden die Prüfungsergebnisse eingefügt. Sie folgen genau diesem Layout, wobei die Spalte „Notizen“ das Wort „fehlgeschlagen“ enthält, wenn die Prüfung nicht bestanden wurde (die bedingte Formatierung hebt dies derzeit hervor) und die UID durch eine einfache LEFT-Funktion herausgezogen wird. Die Spalten „Name“ und „Firma“ verwenden dann eine SVERWEIS-Funktion für die UID inTabelle 1um den Namen/Server zu erfassen.
Notes Time Rec. UID Name Server
Audit for company passed:10001 auto 10001 Company 2 Server 2
Audit for company passed:10006 auto 10006 Company 4 Server 4
Audit for company failed:10007 auto 10007 Company 4 Server 4
Tabelle 4
Dies ist das Prüfblatt. Die Formel würde in Spalte D eingetragen und die Ausgabe jeden Tag als Werte kopiert/eingefügt. Ich habe hier einige Beispiele eingetragen, wie es danach aussehen sollte.
Server Name Project (UID) Today's Date
Server 1 Company 1 Project 1 (10000) P
Server 2 Company 2 Project 2 (10001) N
Server 3 Company 3 Project 3 (10002,10003,10004,10005) F
Server 4 Company 4 Project 4 (10006,10007,10008) P
-----------------------------------------------------
Server 10 Company 10 Project 13 (10022) P
Server 10 Company 11 Project 14 (10023) M
Server 10 Company 12 Project 15 (10024) P
Server 10 Company 13 Project 16 (10025) P
Server 10 Company 15 Project 18 (10027) F
Server 10 Company 16 Project 19 (10028) P
Server 10 Company 17 Project 20 (10029) M
Server 10 Company 18 Project 21 (10030) P
Server 10 Company 19 Project 22 (100310) P
Server 10 Company 19 Project 23 (10032) P
Server 10 Company 19 Project 24 (10033) M
Alternativ habe ich noch einen fünften Tisch,Tabelle 5, das automatisch nach fehlenden IDs sucht ausTisch 3mit der folgenden Formel:
=INDEX(Reference!$A$2:$A$160, SMALL(IF(ISERROR(MATCH(Reference!$A$2:$A$160, Check!$D$2:$D$350, 0)), (ROW(Reference!$A$2:$A$160)-MIN(ROW(Reference!$A$2:$A$160))+1), ""), ROWS($A$1:A1)))
WannTisch 3leer ist (es wird jeden Tag gelöscht), werden hier einfach alle Projekte aufgelistet. Wenn eine beliebige Anzahl von Audits inTisch 3Die Liste inTabelle 5wird aktualisiert, um anzuzeigen, welche fehlen. Vielleicht gibt es eine Möglichkeit, dies in eine endgültige Formel zu integrieren.
Ich habe eine anonymisierte Version, falls jemand damit arbeiten möchte.
Danke schön.
edit1: Jemand hat weitere Systemdetails angefordert. Dies geschieht in einer Windows 10-Umgebung mit Excel 365.
Antwort1
Ich habe versucht, einen Ansatz zusammenzustellen, der, wie ich zugeben muss, nicht sehr skalierbar ist und einige Hilfsspalten verwendet. Ich bin mir nicht sicher, ob es eine direkte Möglichkeit gibt, durch Kommas getrennte Werte auf einmal nachzuschlagen. Es könnte möglich sein, aber ich bin kein Excel-Experte.
Außerdem glaube ich, dass die Desktopversion von Office 365 VBA-Makros unterstützt. Wenn Sie die Desktopversion haben, können Sie sich für eine VBA-Makrolösung entscheiden, die möglicherweise optimierter ist. Ich glaube jedoch, dass Makros in der Onlineversion nicht unterstützt werden.
Siehe den Schnappschuss unten.
Es gibt 5 Hilfsspalten. H, M, N, O, P. H ist möglicherweise optional, aber ich bevorzuge die Verwendung einer einfachen numerischen Folge, da die letzten Buchstaben Ihrer Servernamen möglicherweise nicht aus Zahlen bestehen, wie z. B. Server 1, Server 2 in dieser Reihenfolge.
Geben Sie in M4 die folgende Formel ein und ziehen Sie sie nach unten.
=SUBSTITUTE(RIGHT(K4,LEN(K4)-FIND("(",K4)),")","")
Dadurch wird eine korrekte, durch Kommas getrennte Liste von UIDs erstellt.
Geben Sie in N4 die folgende Formel ein und ziehen Sie sie nach unten bis zur vorläufigen Anzahl von Zellen. Wenn nichts anzuzeigen ist, ist der Zelleninhalt leer, aber erweitern Sie ihn nach unten für eine spätere Bereitstellung, da die Anzahl der UIDs variieren kann.
=VALUE(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",TRUE,$M$4:$M$22),",",REPT(" ",99)),(ROW(1:1)-1)*99+1,99)))
Geben Sie in O4 die folgende Formel ein und ziehen Sie sie nach unten.
=IF(ISNUMBER(VALUE(N4)),MATCH("*"&N4&"*",$M$4:$M$22,0),"")
Hier sind die Positionen der später verwendeten UIDs aufgelistet.
Geben Sie in P4 die folgende Formel ein und ziehen Sie sie nach unten.
=IF(ISNUMBER(VALUE(N4)),IF(ISNUMBER(FIND("passed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"P",IF(ISNUMBER(FIND("failed",INDEX($B$4:$B$22,MATCH(VALUE(N4),$D$4:$D$22,0)))),"F","M")),"")
Dies entscheidet über den Status jeder UID aus Tabelle 4.
Nun die Ausgabespalte L in meinem Beispiel.
Geben Sie in L4 die folgende Formel ein und ziehen Sie sie nach unten.
=IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"M",IF(COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"P")+COUNTIFS($O$4:$O$22,H4,$P$4:$P$22,"M")=COUNTIF($O$4:$O$22,H4),"P","F"))
Dadurch wird die endgültige Logik angewendet, um den Prüfstatus „Bestanden, nicht bestanden“ oder „Fehlend“ basierend auf der transponierten Liste in Hilfsspalten zu bestimmen.
Sie können diese Datensätze in Excel-Tabellen konvertieren und Tabellen-[Kopfzeilen]-Referenzen verwenden, um sie dynamischer zu gestalten.
Probieren Sie es aus und entscheiden Sie, ob es für Sie nützlich sein könnte. Ich gebe zu, dass es nicht so toll ist, da es mehrere Hilfsspalten verwendet. Die Idee besteht darin, eine durch Kommas getrennte Liste in zusammenhängende Zellen zu transponieren und dann die Suche anzuwenden.