Le problème
Nous avons une liste de codes alphanumériques. Chaque code se compose d'une seule lettre (A, B, C, etc.) suivie d'un numéro à 3 chiffres. Ces codes doivent apparaître dans l'ordre alphabétique, mais parfois ils ne sont pas dans l'ordre. Nous voulons marquer les codes hors séquence.
Défi n ° 1
Quelle formule dans la colonne "Vérifier" placera un "x" à côté d'un code qui n'est pas dans l'ordre? Dans ce défi, nous vérifions uniquement que la partie * numérique * du code est hors séquence, et non que la lettre elle-même est hors séquence.
Défi # 2
Comment la formule ci-dessus peut-elle être étendue pour vérifier si la partie "alpha" du code (A, B, C, etc.) est hors séquence? Par exemple, nous devrions marquer un code qui commence par "A" s'il apparaît après un code qui commence par "C" ou "B".
Téléchargez la feuille de travail ci-dessous et relevez le défi!
Remarque: il y a 2 feuilles dans le cahier d'exercices, une pour le défi n ° 1, une pour le défi n ° 2.
Astuce - Cette vidéo montre quelques astuces pour résoudre un problème comme celui-ci.
Hypothèses
- Tous les codes contiennent toujours quatre caractères: 1 lettre majuscule + 3 chiffres.
- Le nombre de codes par lettre est aléatoire, mais il ne doit pas y avoir de lacunes dans les valeurs numériques.
- Il suffit de marquer le premier code avec une lettre dans le désordre, pas tous les codes suivants.
Voici quelques solutions de travail. Il est important de comprendre qu'il existe de très nombreuses façons de résoudre les problèmes courants dans Excel. Les réponses ci-dessous sont juste ma préférence personnelle. Dans toutes les formules ci-dessous, les noms de fonctions sont cliquables si vous souhaitez plus d'informations.
Défi n ° 1
Je suis allé à l'origine avec cette formule:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Remarque MID renvoie du texte. En ajoutant 1 et en ajoutant zéro, nous obtenons Excel pour forcer le texte en un nombre. La multiplication à l'intérieur du test logique dans IF utilise une logique booléenne pour éviter un autre IF imbriqué. Je ne sais pas pourquoi je n'ai pas utilisé RIGHT, ce qui fonctionnerait bien ici également.
Notez également que LEFT ne nécessite pas le nombre de caractères et renverra le premier caractère s'il n'est pas fourni.
Sur la base de certaines des réponses intelligentes ci-dessous, nous pouvons optimiser un peu plus:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Ici, l'opération mathématique de soustraction de MID de MID contraint automatiquement les valeurs de texte en nombres.
Défi # 2
Pour cette solution, j'ai utilisé plusieurs IF imbriqués (sauts de ligne ajoutés pour la lisibilité):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
J'ai fait cela parce que le premier test GAUCHE (B5) = GAUCHE (B6) détermine si nous vérifions des chiffres ou des lettres. Si le premier caractère est le même, nous vérifions les nombres comme ci-dessus. Sinon, nous vérifions uniquement la première lettre.
Notez que la fonction CODE renverra le numéro ascii du premier caractère si une chaîne de texte contient plus d'un caractère. Cela ressemble à un hack, et cela rend le code moins compréhensible peut-être, mais cela fonctionne :)
Si cela choque votre sensibilité, utilisez GAUCHE comme ci-dessus dans CODE pour ne livrer que le premier caractère.
