Problemet
Vi har en lista med alfanumeriska koder. Varje kod består av en bokstav (A, B, C, etc.) följt av ett tresiffrigt nummer. Dessa koder ska visas i alfabetisk ordning, men ibland är de inte i ordning. Vi vill flagga koder utanför sekvensen.
Utmaning nr 1
Vilken formel i kolumnen "Kontrollera" placerar ett "x" bredvid en kod som är ur sekvens? I den här utmaningen kontrollerar vi bara att den * numeriska * delen av koden är ur sekvens, inte att bokstaven i sig är utanför sekvensen.
Utmaning 2
Hur kan formeln ovan utvidgas för att kontrollera om "alfa" -del av koden (A, B, C, etc.) är ur sekvens? Till exempel bör vi flagga en kod som börjar med "A" om den visas efter en kod som börjar med "C" eller "B".
Ladda ner kalkylbladet nedan och ta utmaningen!
Obs! Det finns två ark i arbetsboken, ett för utmaning 1, ett för utmaning 2.
Tips - Den här videon visar några tips för hur man löser ett sådant problem.
Antaganden
- Alla koder innehåller alltid fyra tecken: 1 stor bokstav + 3 siffror.
- Antalet koder per bokstav är slumpmässigt, men det bör inte finnas några luckor i numeriska värden.
- Det är bara nödvändigt att markera den första koden med en bokstav utanför sekvensen, inte alla efterföljande koder.
Här är några arbetslösningar. Det är viktigt att förstå att det finns många, många sätt att lösa vanliga problem i Excel. Svaren nedan är bara mina personliga preferenser. I alla formlerna nedan är funktionsnamnen klickbara om du vill ha mer information.
Utmaning nr 1
Jag gick ursprungligen med den här formeln:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Obs! MID returnerar text. Genom att lägga till 1 och lägga till noll får vi Excel att tvinga texten till ett tal. Multiplikationen i det logiska testet inuti IF använder boolesk logik för att undvika en annan kapslad IF. Jag är inte säker på varför jag inte använde RIGHT, vilket också skulle fungera bra.
Observera också att VÄNSTER inte kräver antalet tecken och kommer att returnera det första tecknet om det inte tillhandahålls.
Baserat på några av de smarta svaren nedan kan vi optimera lite mer:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Här tvingar matematiken att subtrahera MID från MID automatiskt textvärdena till siffror.
Utmaning 2
För den här lösningen använde jag flera kapslade IF: er (radbrytningar har lagts till för läsbarhet):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Jag gjorde det eftersom det första testet VÄNSTER (B5) = VÄNSTER (B6) avgör om vi kontrollerar siffror eller bokstäver. Om det första tecknet är detsamma kontrollerar vi siffrorna enligt ovan. Om inte, kontrollerar vi bara första bokstaven.
Observera att KOD-funktionen returnerar det första tecknets ascii-nummer om en textsträng innehåller mer än 1 tecken. Det här känns som ett hack, och det gör koden kanske mindre förståelig, men det fungerar :)
Om det stötar dina känslor, använd VÄNSTER som ovan i CODE för att leverera bara det första tecknet.
