Das Problem
Wir haben eine Liste von alphanumerischen Codes. Jeder Code besteht aus einem einzelnen Buchstaben (A, B, C usw.), gefolgt von einer dreistelligen Zahl. Diese Codes sollten in alphabetischer Reihenfolge angezeigt werden, sind jedoch manchmal nicht in der richtigen Reihenfolge. Wir möchten Codes außerhalb der Reihenfolge kennzeichnen.
Herausforderung Nr. 1
Welche Formel in der Spalte "Prüfen" setzt ein "x" neben einen Code, der nicht in der richtigen Reihenfolge ist? Bei dieser Herausforderung überprüfen wir nur, ob der * numerische * Teil des Codes nicht in der richtigen Reihenfolge ist, nicht, dass der Buchstabe selbst nicht in der richtigen Reihenfolge ist.
Herausforderung Nr. 2
Wie kann die obige Formel erweitert werden, um zu überprüfen, ob der "Alpha" -Teil des Codes (A, B, C usw.) nicht in der richtigen Reihenfolge ist? Zum Beispiel sollten wir einen Code markieren, der mit "A" beginnt, wenn er nach einem Code erscheint, der mit "C" oder "B" beginnt.
Laden Sie das Arbeitsblatt unten herunter und nehmen Sie die Herausforderung an!
Hinweis: Die Arbeitsmappe enthält zwei Blätter, eines für Herausforderung Nr. 1 und eines für Herausforderung Nr. 2.
Tipp - Dieses Video zeigt einige Tipps zur Lösung eines solchen Problems.
Annahmen
- Alle Codes enthalten immer vier Zeichen: 1 Großbuchstabe + 3 Zahlen.
- Die Anzahl der Codes pro Buchstabe ist zufällig, es sollten jedoch keine Lücken in den numerischen Werten vorhanden sein.
- Es ist nur erforderlich, den ersten Code mit einem Buchstaben außerhalb der Reihenfolge zu markieren, nicht alle nachfolgenden Codes.
Hier sind einige funktionierende Lösungen. Es ist wichtig zu verstehen, dass es viele, viele Möglichkeiten gibt, häufig auftretende Probleme in Excel zu lösen. Die folgenden Antworten sind nur meine persönliche Präferenz. In allen folgenden Formeln können Funktionsnamen angeklickt werden, wenn Sie weitere Informationen wünschen.
Herausforderung Nr. 1
Ich ging ursprünglich mit dieser Formel:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Hinweis MID gibt Text zurück. Durch Hinzufügen von 1 und Null erhalten wir Excel, um den Text in eine Zahl zu zwingen. Die Multiplikation innerhalb des logischen Tests innerhalb der IF verwendet eine boolesche Logik, um eine weitere verschachtelte IF zu vermeiden. Ich bin mir nicht sicher, warum ich RIGHT nicht verwendet habe, was auch hier gut funktionieren würde.
Beachten Sie auch, dass LINKS nicht die Anzahl der Zeichen erfordert und das erste Zeichen zurückgibt, wenn es nicht angegeben wird.
Basierend auf einigen der folgenden cleveren Antworten können wir ein bisschen mehr optimieren:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Hier zwingt die mathematische Operation des Subtrahierens von MID von MID die Textwerte automatisch zu Zahlen.
Herausforderung Nr. 2
Für diese Lösung habe ich mehrere verschachtelte IFs verwendet (Zeilenumbrüche zur besseren Lesbarkeit hinzugefügt):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Ich habe dies getan, weil der erste Test LINKS (B5) = LINKS (B6) bestimmt, ob wir Zahlen oder Buchstaben prüfen. Wenn das erste Zeichen dasselbe ist, überprüfen wir die Zahlen wie oben. Wenn nicht, überprüfen wir nur den ersten Buchstaben.
Beachten Sie, dass die CODE-Funktion die ASCII-Nummer des ersten Zeichens zurückgibt, wenn eine Textzeichenfolge mehr als 1 Zeichen enthält. Das fühlt sich wie ein Hack an und macht den Code vielleicht weniger verständlich, aber es funktioniert :)
Wenn dies Ihre Sensibilität verletzt, verwenden Sie LINKS wie oben in CODE, um nur das erste Zeichen zu liefern.
