Problem
Mamy listę kodów alfanumerycznych. Każdy kod składa się z jednej litery (A, B, C itd.), Po której następuje 3-cyfrowy numer. Te kody powinny pojawiać się w kolejności alfabetycznej, ale czasami nie są w kolejności. Chcemy oznaczać kody spoza sekwencji.
Wyzwanie nr 1
Jaka formuła w kolumnie „Sprawdź” spowoduje umieszczenie znaku „x” obok kodu, który nie jest w kolejności? W tym wyzwaniu sprawdzamy tylko, czy * numeryczna * część kodu jest poza kolejnością, a nie, że sama litera jest poza kolejnością.
Wyzwanie nr 2
W jaki sposób można rozszerzyć powyższy wzór, aby sprawdzić, czy część „alfa” kodu (A, B, C itd.) Jest poza kolejnością? Na przykład powinniśmy oflagować kod zaczynający się od „A”, jeśli występuje po kodzie rozpoczynającym się od „C” lub „B”.
Pobierz poniższy arkusz roboczy i podejmij wyzwanie!
Uwaga: w zeszycie ćwiczeń znajdują się 2 arkusze, jeden dla Wyzwania nr 1, jeden dla Wyzwania nr 2.
Wskazówka - ten film przedstawia kilka wskazówek, jak rozwiązać taki problem.
Założenia
- Wszystkie kody zawsze zawierają cztery znaki: 1 duża litera + 3 cyfry.
- Liczba kodów przypadających na jedną literę jest losowa, ale nie powinno być luk w wartościach liczbowych.
- Konieczne jest tylko oznaczenie pierwszego kodu literą spoza kolejności, a nie wszystkie kolejne kody.
Oto kilka działających rozwiązań. Ważne jest, aby zrozumieć, że istnieje wiele, wiele sposobów rozwiązywania typowych problemów w programie Excel. Poniższe odpowiedzi to tylko moje osobiste preferencje. We wszystkich poniższych formułach nazwy funkcji można kliknąć, jeśli chcesz uzyskać więcej informacji.
Wyzwanie nr 1
Pierwotnie poszedłem z tą formułą:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Uwaga MID zwraca tekst. Dodając 1 i dodając zero, otrzymujemy Excel do przekształcenia tekstu w liczbę. Mnożenie wewnątrz testu logicznego wewnątrz funkcji JEŻELI używa logiki boolowskiej, aby uniknąć kolejnego zagnieżdżonego JEŻELI. Nie jestem pewien, dlaczego nie użyłem opcji RIGHT, co również by tu działało.
Zwróć również uwagę, że LEWY nie wymaga liczby znaków i zwróci pierwszy znak, jeśli nie zostanie podany.
Opierając się na niektórych sprytnych odpowiedziach poniżej, możemy nieco bardziej zoptymalizować:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
W tym przypadku operacja matematyczna polegająca na odejmowaniu MID od MID automatycznie przekształca wartości tekstowe na liczby.
Wyzwanie nr 2
W tym rozwiązaniu użyłem kilku zagnieżdżonych IF (dla czytelności dodano podziały wierszy):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Zrobiłem to, ponieważ pierwszy test LEWY (B5) = LEWY (B6) określa, czy sprawdzamy cyfry czy litery. Jeśli pierwszy znak jest taki sam, sprawdzamy liczby jak powyżej. Jeśli nie, sprawdzamy tylko pierwszą literę.
Zwróć uwagę, że funkcja CODE zwróci liczbę ascii pierwszego znaku, jeśli ciąg tekstowy zawiera więcej niż 1 znak. To wygląda na włamanie i być może sprawia, że kod jest mniej zrozumiały, ale działa :)
Jeśli to obraża twoją wrażliwość, użyj LEWEGO jak powyżej wewnątrz KODU, aby dostarczyć tylko pierwszą postać.
