Problemet
Vi har en liste over alfanumeriske koder. Hver kode består av en enkelt bokstav (A, B, C, etc.) etterfulgt av et tresifret nummer. Disse kodene skal vises i alfabetisk rekkefølge, men noen ganger er de utenfor rekkefølge. Vi ønsker å flagge koder utenfor sekvensen.
Utfordring nr. 1
Hvilken formel i "Sjekk" -kolonnen vil plassere et "x" ved siden av en kode som ikke er i rekkefølge? I denne utfordringen sjekker vi bare at den * numeriske * delen av koden er utenfor rekkefølgen, ikke at selve bokstaven er utenfor rekkefølgen.
Utfordring nr. 2
Hvordan kan formelen ovenfor utvides for å sjekke om "alfa" -del av koden (A, B, C, etc.) er ute av rekkefølge? For eksempel bør vi flagge en kode som begynner med "A" hvis den vises etter en kode som begynner med "C" eller "B".
Last ned regnearket nedenfor og ta utfordringen!
Merk: det er to ark i arbeidsboken, ett for utfordring nr. 1, ett for utfordring nr. 2.
Tips - Denne videoen viser noen tips for hvordan du kan løse et problem som dette.
Antagelser
- Alle kodene inneholder alltid fire tegn: 1 stor bokstav + 3 tall.
- Antall koder per bokstav er tilfeldig, men det skal ikke være noen hull i numeriske verdier.
- Det er bare nødvendig å merke den første koden med en bokstav utenfor rekkefølgen, ikke alle påfølgende koder.
Her er noen arbeidsløsninger. Det er viktig å forstå at det er mange, mange måter å løse vanlige problemer i Excel på. Svarene nedenfor er bare min personlige preferanse. I alle formlene nedenfor er funksjonsnavn klikkbare hvis du vil ha mer informasjon.
Utfordring nr. 1
Jeg gikk opprinnelig med denne formelen:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Merk MID returnerer tekst. Ved å legge til 1 og legge til null får vi Excel til å tvinge teksten til et tall. Multiplikasjonen inne i den logiske testen i IF bruker boolsk logikk for å unngå en annen nestet IF. Jeg er ikke sikker på hvorfor jeg ikke brukte RIGHT, noe som også ville fungere bra.
Vær også oppmerksom på at VENSTRE ikke krever antall tegn, og vil returnere det første tegnet hvis det ikke er oppgitt.
Basert på noen av de smarte svarene nedenfor, kan vi optimalisere litt mer:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Her tvinger matematikkoperasjonen til å trekke MID fra MID automatisk tekstverdiene til tall.
Utfordring nr. 2
For denne løsningen brukte jeg flere nestede IF-er (linjeskift lagt til for lesbarhet):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Jeg gjorde dette fordi den første testen VENSTRE (B5) = VENSTRE (B6) avgjør om vi sjekker tall eller bokstaver. Hvis det første tegnet er det samme, sjekker vi tallene som ovenfor. Hvis ikke, sjekker vi bare første bokstav.
Merk at KODE-funksjonen vil returnere ascii-nummeret til det første tegnet hvis en tekststreng inneholder mer enn 1 tegn. Dette føles som et hack, og det gjør koden kanskje mindre forståelig, men den fungerer :)
Hvis det fornærmer følsomhetene dine, kan du bruke VENSTRE som ovenfor inne i KODE for å levere bare det første tegnet.
