Comment peut-on obliger un utilisateur à entrer des valeurs à partir d’une liste dans l’ordre de la liste dans Google Sheets ? La validation des données nous permet de contrôler la manière dont les données sont saisies dans une cellule ou une plage de cellules. Ainsi, en utilisant la validation des données, en particulier en utilisant la liste à partir d’une plage, nous pouvons atteindre l’exigence susmentionnée.
Cela signifie que nous pouvons nous assurer qu’un utilisateur n’entre que des données à partir d’une liste donnée dans Google Sheets et que ces entrées respectent les exigences suivantes :
- Les valeurs saisies conservent leur ordre dans la liste.
- Les valeurs peuvent se répéter plusieurs fois.
Pour répondre à ces exigences, nous pouvons utiliser la fonctionnalité de validation des données « Liste à partir d’une plage » (liste déroulante). Comment ? Dans ce tutoriel Google Sheets, vous apprendrez cette astuce incroyable, étape par étape.
Exemple d’entrée des valeurs à partir d’une liste dans l’ordre de la liste dans Google Sheets
Voici un exemple de la méthode de validation des données typique décrite ci-dessus.
Dans Google Sheets, ma liste se trouve dans les cellules D2:D11 et contient des noms de fichiers de 1 à 10 (veuillez faire défiler vers le bas et voir la première image).
La liste peut contenir n’importe quelles valeurs, les noms de fichiers ne sont donnés qu’à titre d’exemple. Il peut s’agir de noms de produits, de noms de livres, de noms de pays, de codes d’articles, de numéros de vols, de codes de stations, etc.
Je veux obliger un utilisateur ou me restreindre à entrer uniquement les noms de fichiers dans la plage A2:A à partir de la liste dans D2:D11 et cela dans l’ordre des noms de fichiers de la liste dans D2:D11.
Mais veuillez noter que l’utilisateur est libre d’entrer les noms de fichiers plusieurs fois tout en respectant la condition susmentionnée.
Si vous examinez la colonne « entrée de données incorrecte », vous pouvez comprendre pourquoi les entrées de données sont incorrectes. « Fichier 4 » vient avant « Fichier 3 », ce qui viole l’exigence.
Je n’ai pas pensé à un tel scénario de validation des données jusqu’à ce qu’un de mes lecteurs en parle dans l’un de mes tutoriels liés à la validation des données ici – Les meilleurs exemples de validation des données dans Google Sheets.
Comment est-il possible d’utiliser la liste à partir d’une plage ?
Nous allons utiliser la liste déroulante de la validation des données, également appelée « Liste à partir d’une plage », pour obliger l’utilisateur à saisir correctement les valeurs dans la plage A2:A.
Dans cette liste déroulante, dans chaque ligne, il n’y aura que deux valeurs disponibles à sélectionner : une est la valeur juste au-dessus de la cellule active, et l’autre valeur est la valeur suivante à celle-ci dans la liste D2:D11.
Pour cela, nous avons besoin de deux cellules d’aide contenant deux formules uniques. Ainsi, nous pouvons utiliser ces cellules d’aide au lieu de la liste d’origine dans D2:D11 pour créer la liste déroulante.
Cela signifie qu’en trois étapes, nous pouvons obliger un utilisateur à saisir des valeurs à partir d’une liste dans l’ordre de la liste dans Google Sheets. Voici ces étapes sous trois titres.
Formule 1 – Dernière valeur de la colonne A ou première valeur de la liste
Nous allons utiliser les cellules F2 et F3 pour générer les deux valeurs requises pour créer la liste déroulante à partir de la plage. Dans ces deux cellules, notre première formule ira dans la cellule F2.
Voici cette formule :
=ifna( indirect( ArrayFormula("A"&MATCH(2,1/(A:A<>""),1)) ), D2 )
Nous devons comprendre cette formule clé qui nous aide à entrer des valeurs à partir d’une liste dans leur ordre dans la liste.
Ici, ArrayFormula(« A »&MATCH(2,1/(A:A<> » »),1)) renverra l’ID de la dernière cellule non vide (le cas échéant) dans la colonne A.
Remarque : Voici l’explication de la formule – Adresse de la dernière cellule non vide en ignorant les cellules vides dans une colonne (fonctionne dans Sheets et Excel).
Indirect utilisera ensuite cet ID pour renvoyer la valeur de la dernière cellule non vide (le cas échéant).
S’il n’y a pas de valeurs dans la colonne A, la formule renverra #N/A!. Dans ce cas, IFNA renverra la valeur de la cellule D2, qui est la première valeur de la liste.
Veuillez observer de près l’image ci-dessous pour comprendre ce qui se passe dans la cellule F2.
Formule 2 – La « Valeur suivante » dans la liste
Le rôle de cette deuxième formule est de renvoyer la valeur suivante à celle de la première formule dans la liste. Veuillez insérer cette formule dans la cellule F3 :
=iferror( if( counta(A:A)=0, "", index(D2:D11, match(F2,D2:D11,0)+1 ) ) )
Voulez-vous aussi une explication pour cette formule ?
La fonction SI testera s’il y a des valeurs dans la colonne A. L’expression ici dans SI est la fonction COUNTA, qui est la suivante :
if(counta(A:A)=0
Si le nombre de valeurs dans la colonne A est 0, le SI renverra une cellule vide (« »). Sinon, le SI exécutera cette partie de la formule – Index(D2:D11,match(F2,D2:D11,0)+1).
Il est maintenant essentiel de comprendre la combinaison Index-Correspondance ci-dessus, n’est-ce pas ?
L’INDEX décale les lignes dans la liste D2:D11 en fonction du numéro de sortie de la fonction MATCH plus 1.
MATCH renvoie la position relative de la valeur de la première formule (F2) dans la liste. Le +1 permet d’obtenir la position relative de la valeur suivante dans la liste. Comme indiqué ci-dessus, INDEX décale ce nombre.
Maintenant, nous avons deux formules – une dans la cellule F2 et l’autre dans la cellule F3.
Il est temps de créer une liste déroulante de validation des données pour entrer des valeurs à partir d’une liste dans l’ordre de la liste dans Google Sheets.
Veuillez noter une fois de plus que nous n’utiliserons pas la liste d’origine pour créer la liste déroulante, nous utiliserons plutôt la plage F2:F3.
Liste à partir d’une plage pour entrer des valeurs à partir d’une liste dans l’ordre de la liste
Veuillez suivre la dernière étape ci-dessous.
Sélectionnez A2:A20 ou la plage dans laquelle vous souhaitez que la liste déroulante apparaisse.
Ensuite, cliquez sur le menu Données > Validation des données et suivez les paramètres de la « Liste à partir d’une plage » ci-dessous.
Maintenant, l’utilisateur ne peut entrer/sélectionner des données que dans A2:A20 (ou la plage dans laquelle vous avez créé la liste déroulante) à partir d’une liste dans l’ordre de la liste dans Google Sheets.
C’est tout. Profitez-en !
Sample_Sheet_11920
