Het probleem
We hebben een lijst met alfanumerieke codes. Elke code bestaat uit een enkele letter (A, B, C, etc.) gevolgd door een 3-cijferig nummer. Deze codes moeten in alfabetische volgorde worden weergegeven, maar soms zijn ze niet op volgorde. We willen codes die niet in de juiste volgorde staan, markeren.
Uitdaging # 1
Welke formule in de kolom "Controleren" plaatst een "x" naast een code die niet in de juiste volgorde staat? In deze uitdaging controleren we alleen of het * numerieke * gedeelte van de code niet in de juiste volgorde staat, niet dat de letter zelf niet in de juiste volgorde staat.
Uitdaging # 2
Hoe kan de bovenstaande formule worden uitgebreid om te controleren of het "alfa" -gedeelte van de code (A, B, C, enz.) Niet in de juiste volgorde staat? We moeten bijvoorbeeld een code markeren die begint met "A" als deze verschijnt na een code die begint met "C" of "B".
Download het onderstaande werkblad en ga de uitdaging aan!
Opmerking: er zijn 2 bladen in het werkboek, een voor uitdaging # 1 en een voor uitdaging # 2.
Hint - Deze video toont enkele tips om een probleem als dit op te lossen.
Veronderstellingen
- Alle codes bevatten altijd vier tekens: 1 hoofdletter + 3 cijfers.
- Het aantal codes per letter is willekeurig, maar er mogen geen hiaten in numerieke waarden zijn.
- Het is alleen nodig om de eerste code te markeren met een letter die niet in de juiste volgorde staat, niet alle volgende codes.
Hier zijn enkele werkende oplossingen. Het is belangrijk om te begrijpen dat er veel, veel manieren zijn om veelvoorkomende problemen in Excel op te lossen. De onderstaande antwoorden zijn slechts mijn persoonlijke voorkeur. In alle onderstaande formules zijn functienamen aanklikbaar als u meer informatie wilt.
Uitdaging # 1
Ik ging oorspronkelijk met deze formule:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Opmerking MID geeft tekst terug. Door 1 toe te voegen en nul toe te voegen, zorgen we ervoor dat Excel de tekst in een getal dwingt. De vermenigvuldiging binnen de logische test binnen IF gebruikt booleaanse logica om een andere geneste IF te vermijden. Ik weet niet zeker waarom ik RIGHT niet heb gebruikt, wat hier ook prima zou werken.
Merk ook op dat LINKS het aantal tekens niet vereist en het eerste teken zal retourneren als dit niet wordt opgegeven.
Op basis van enkele van de onderstaande slimme reacties kunnen we nog wat optimaliseren:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Hier dwingt de wiskundige bewerking van het aftrekken van MID van MID automatisch de tekstwaarden naar getallen.
Uitdaging # 2
Voor deze oplossing heb ik verschillende geneste IF's gebruikt (regeleinden toegevoegd voor leesbaarheid):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Ik deed dit omdat de eerste test LINKS (B5) = LINKS (B6) bepaalt of we cijfers of letters controleren. Als het eerste teken hetzelfde is, controleren we de getallen zoals hierboven. Zo niet, dan controleren we alleen de eerste letter.
Merk op dat de CODE-functie het ascii-nummer van het eerste teken retourneert als een tekstreeks meer dan 1 teken bevat. Dit voelt als een hack, en het maakt de code misschien minder begrijpelijk, maar het werkt :)
Als dat je gevoeligheden beledigt, gebruik dan LINKS zoals hierboven in CODE om alleen het eerste teken weer te geven.
