May6 vous propose de partir à la découverte d’une fonction méconnue mais tellement utile : SEQUENCE. Avec cette fonction, vous allez gagner du temps dans la saisie d’informations dans certaines situations.
Syntaxe
=SEQUENCE(lignes;[colonnes];[début];[étape])
avec :
- lignes : Nombre de lignes à renvoyer
- [colonnes] : Nombre de colonnes à renvoyer
- [début] : Premier nombre de la séquence
- [étape] : Montant à appliquer pour incrémenter chaque valeur suivante dans le tableau
Fonctionnement
La fonction SEQUENCE permet de créer une suite de valeurs dans votre feuille Excel. Elle fait partie des fonctions dynamic arrays.
Lignes
L’argument permet de choisir le nombre de lignes pour lequel nous allons afficher une suite de valeurs. Imaginons que nous voulons créer une suite sur 10 lignes.
La formule est :
=SEQUENCE(10)
Colonnes
Vous pouvez créer une suite en lignes mais aussi dans le sens des colonnes. Cette fois-ci, je veux créer une suite de 3 colonnes.
Cela donne :
=SEQUENCE(;3) ou =SEQUENCE(1;3)
Or, on peut créer un tableau en définissant un nombre de lignes et de colonnes. Je veux une suite sur 4 lignes et 3 colonnes.
=SEQUENCE(4;3)
Début
Par défaut, on commence la suite à 1. Or, on peut choisir la valeur de départ avec cet argument. Faisons une suite sur 3 colonnes en commençant à 10.
=SEQUENCE(1;3;10)
Étape
Cet argument sert à définir l’écart entre chaque valeur. Si vos souvenirs de l’école sont encore clairs, c’est ce qu’on appelait le pas dans les cours de mathématiques et plus précisément les suites arithmétiques.
Essayons une suite avec un pas de 100 entre les valeurs de la suite.
=SEQUENCE(1;3;1;100)
Mais je peux faire l’inverse. J’ai envie de faire un TOP10 mais pour le suspense, je commence par la 10e place pour finir par la 1ère.
=SEQUENCE(10;1;10;-1)
Cas pratiques
Avec les exemples précédents, vous ne voyez peut-être pas les situations dans lesquelles vous pouvez utiliser la fonction SEQUENCE. Je vais vous présenter quelques exemples.
Index dynamique
=SEQUENCE(16)
Vous allez me dire que ce n’est pas pratique car à chaque produit, il faudra que je modifie la formule. Oui, c’est vrai, mais on peut rendre cette variable dynamique.
Nous avons une colonne avec des montants, donc des valeurs numériques. On va utiliser la fonction NB pour compter le nombre de cellules remplies avec un nombre dans la colonne D.
=NB(D:D)
Maintenant, on peut mettre cette formule dans notre argument lignes dans la fonction SEQUENCE.
=SEQUENCE(NB(D:D))
À partir du moment qu’on complètera la colonne PU, la colonne Référence rajoutera un identifiant.
Mais si nous n’avons pas de colonne contenant un nombre, comment fait-on ?
Ce n’est pas grave, on va dans ce cas utiliser la fonction NBVAL qui va compter le nombre de cellules remplies. Je n’oublie pas de soustraire 1 pour enlever l’en-tête.
=SEQUENCE(NBVAL(G:G)-1)
Vous trouvez qu’un identifiant avec qu’un seul chiffre est trop simpliste ? On peut concaténer avec autre chose comme du texte.
="PROD-"&SEQUENCE(NBVAL(L:L)-1)
Dates d'échéance d'emprunt
Grâce à la fonction SEQUENCE, nous pouvons lister les dates des remboursements d’un emprunt.
Nous avons :
- en D1, le nombre de remboursements, soit le nombre de lignes pour notre séquence.
- en B2, notre date de départ soit le début de la séquence. Pour récupérer uniquement le mois, on va utiliser la fonction MOIS.
cela donne :
SEQUENCE(D1;;MOIS(B2)+1)
Or, le résultat est un chiffre qui correspond au mois de l’année (1 pour janvier, 2 pour février… 12 pour décembre)
On va utiliser la fonction DATE pour afficher le jour exact.
=DATE(ANNEE(B2);SEQUENCE(D1;;MOIS(B2)+1);JOUR(B2))
Planning mensuel
Autre cas : réaliser un tableau mensuel pour la saisie de montants pour chaque journée. Cela peut concerner le montant des ventes, le solde en trésorerie…
Tout d’abord, nous avons deux cellules à saisir manuellement : l’année et le mois respectivement en A2 et B2.
Calculons le 1er jour et dernier jour du mois saisi.
En C2, on obtient la formule :
=DATE(A2;B2;1)
et D2, on va utiliser une fonction qui permet de déterminer le dernier jour du mois, FIN.MOIS :
=FIN.MOIS(C2;0)
Étape suivante : pour connaître le nombre de lignes à créer pour notre suite, nous devons connaître le nombre de jours pour le mois sélectionné. En E2, nous faisons la différence entre D2 et C2 en y ajoutant 1 pour inclure le dernier jour du mois.
=D2-C2+1
On a maintenant toutes les informations pour créer notre séquence :
=SEQUENCE(E2;;C2)
Planning annuel
=DATE(2022;SEQUENCE(1;12);1)Cela affiche le 1er jour des 12 mois de l’année.
Pour afficher le nom du mois, on va utiliser la fonction TEXTE. Dans le deuxième argument, on choisit le format mensuel avec 2 possibilités :
- mmm : nom du mois abrégé
- mmmm :nom du mois complet
=TEXTE(DATE(2022;SEQUENCE(1;12);1);"mmmm")
Plutôt que la fonction TEXTE, essayez de changer le format de cellule.
Via le menu, dans l’onglet Accueil, sélectionnez Autres formats numériques… dans la liste déroulante qui permet de choisir son format de cellule.
Allez dans la partie Personnalisée et cherchez mmmm. S’il n’existe pas créez-le dans le champ au-dessus la liste.
Ainsi, vous pourrez encore utiliser la date, qui est la valeur de cellule, pour d’autres formules de calcul.
