Si vous avez besoin d’insérer un certain nombre de lignes vides dans vos données, vous pouvez le faire à l’aide d’une formule dans Google Sheets.
Il existe plusieurs raisons réelles pour lesquelles vous pourriez vouloir le faire. Par exemple, vous pourriez insérer des lignes vides dans chaque autre ligne pour faciliter la lecture de vos données. Cela est également utile lorsque vous souhaitez insérer une formule matricielle dans une plage de cellules fusionnées de manière unique, car cela empêchera la formule de masquer les valeurs.
Dans ce tutoriel, je vais vous montrer comment insérer 2 lignes vides après chaque ligne. Je vais également expliquer comment modifier le nombre de lignes vides.
Il existe plusieurs façons de le faire. Ma méthode préférée consiste à utiliser la formule REDUCE. Nous couvrirons cette formule en détail dans la dernière partie du tutoriel. Mais d’abord, examinons une autre méthode utilisant VLOOKUP.
Utilisation de la formule VLOOKUP pour insérer des lignes vides dans Google Sheets
Permettez-moi d’abord de vous présenter la formule VLOOKUP que j’ai utilisée dans la cellule F2. Ensuite, je vous expliquerai les modifications que vous devrez peut-être apporter à la formule pour l’utiliser dans votre ensemble de données. Enfin, je vous donnerai une explication brève de la formule.
=ARRAYFORMULA(
LET(
range, "A2:B",
first_col, A1:A,
n, 2,
test, XMATCH("?*",TO_TEXT(first_col),2,-1),
IFNA(
VLOOKUP(
SEQUENCE(test*(n+1),1,0)/(n+1)+1,
HSTACK(
SEQUENCE(test),
INDIRECT(range&test)
),
{2,3},
0
)
)
)
)La formule VLOOKUP ci-dessus insère deux lignes vides après chaque autre ligne.
Comment ajuster cette formule en fonction de votre plage de données ?
Je sais que votre plage de données peut être totalement différente. Pour vous permettre d’utiliser cette formule dans une plage/des données différentes, je dois expliquer les modifications nécessaires dans la formule ci-dessus.
1. Modification de la plage de données dans la formule
Supposons que vous souhaitez insérer deux lignes vides après chaque autre ligne dans la plage B5:C. Vous devez apporter les modifications suivantes à ma formule :
- Remplacez la plage dans la formule de « A2:B » à « B5:C ».
- Remplacez first_col dans la formule de A1:A à B1:B. C’est important. Vous devez conserver la plage de la première colonne (first_col) à partir de la première ligne, et non à partir de la 5e ligne.
La formule ci-dessus insère 2 lignes après chaque ligne. Pour changer le nombre de lignes vides, vous devez changer la valeur de l’argument n.
2. Modification du nombre de lignes à insérer
Voyez comme il est simple d’augmenter le nombre de lignes vides. Remplacez la valeur de n dans la formule de 2 par le nombre souhaité. Dans l’exemple suivant, je vais le définir sur 3.
La formule ci-dessous insère 3 lignes vides après chaque autre ligne dans la plage B5:C.
=ARRAYFORMULA(
LET(
range, "B5:C",
first_col, B1:B,
n, 3,
test, XMATCH("?*",TO_TEXT(first_col),2,-1),
IFNA(
VLOOKUP(
SEQUENCE(test*(n+1),1,0)/(n+1)+1,
HSTACK(
SEQUENCE(test),
INDIRECT(range&test)
),
{2,3},
0
)
)
)
)3. Modification du nombre de colonnes
Dans mes exemples précédents, j’ai utilisé un jeu de données à deux colonnes.
Si vous avez un nombre différent de colonnes, telles que A2:C, alors la partie de la formule {2,3} doit être modifiée en {2,3,4}. De même, si le nombre de colonnes est de 4, alors ce sera {2, 3, 4, 5}.
Si vous souhaitez en savoir plus sur cette fonctionnalité de tableau de recherche VLOOKUP, consultez ce tutoriel : Multiple Values Using Vlookup in Google Sheets is Possible [How to]
La formule suivante (affichée dans la capture d’écran) insère 1 ligne vide après chaque autre ligne dans la plage A2:D.
Anatomie de la formule VLOOKUP pour insérer des lignes vides dans Google Sheets
Voici l’essentiel de la formule :
J’ai utilisé deux formules SEQUENCE. L’une a été ajoutée à la plage, et l’autre a été utilisée comme clé de recherche pour VLOOKUP.
Syntaxe de la fonction VLOOKUP dans Google Sheets :
VLOOKUP(clé_de_recherche, plage, index, [est_trié])Supposons que la plage de données soit A1:B6. Cela signifie qu’il y a 6 enregistrements dans le tableau.
La formule =SEQUENCE(6) renverra les chiffres de 1 à 6, qui seront ensuite empilés sur la plage de données comme suit (veuillez vous référer à F1:H6 dans l’image ci-dessous) :
=HSTACK(SEQUENCE(6),A1:B6)C’est la plage dans VLOOKUP.
Maintenant, la clé de recherche est la formule de nombres de séquence * n / (n + 1). Supposons que n soit de 3, alors ce sera :
=ArrayFormula(SEQUENCE(6 * 3, 1, 0) / 3 + 1)Veuillez vous référer à D1:D18 dans l’image ci-dessous.
Grâce à VLOOKUP, nous pouvons maintenant insérer facilement n lignes vides en dessous de chaque autre ligne. Veuillez voir l’illustration ci-dessous.
Formule REDUCE pour insérer des lignes vides dans Google Sheets
La formule VLOOKUP ci-dessus est une formule dynamique qui fonctionne avec des plages ouvertes et fermées. Nous pouvons utiliser la fonction REDUCE pour coder une formule dynamique similaire à l’insertion de lignes vides en dessous de chaque autre ligne.
Cependant, pour simplifier les choses, je choisirai de l’utiliser sur des plages limitées.
Nous pouvons utiliser la formule REDUCE suivante pour insérer une ligne vide en dessous de chaque autre ligne dans la plage fermée B3:E8 :
=REDUCE(,B3:B8,LAMBDA(a,v,IFNA(VSTACK(a,FILTER(B3:E8,ROW(B3:B8)=ROW(v)),))))Ici,
- B3:B8 est la plage de la première colonne dans les données source.
- B3:E8 est la plage de données source.
Pour insérer deux lignes vides, ajoutez une virgule juste après la dernière virgule de la formule.
Cette formule a un problème. Elle insérera une ligne vide en haut, mais c’est la meilleure formule pour insérer des lignes vides dans Google Sheets, en ignorant le problème de la plage fermée.
Logique de la formule et explication
La fonction FILTER filtre la première ligne de la plage.
FILTER(B3:E8,ROW(B3:B8)=ROW(v))où v est égal à B3.
VSTACK empile la valeur de l’accumulateur (a), qui est vide à la première étape, avec la ligne filtrée ci-dessus, puis avec une seule cellule vide.
VSTACK(a,FILTER (B3:E8,ROW(B3:B8)=ROW(v)),)À l’étape suivante, dans VSTACK, la valeur de l’accumulateur sera le résultat ci-dessus. v dans FILTER(B3:E8, ROW(B3:B8) = ROW(v)) devient B4.
Cela se répète jusqu’à la dernière ligne de la plage.
C’est la logique de la formule REDUCE qui insère des lignes vides en dessous de chaque autre ligne dans Google Sheets.
Révéler les valeurs masquées ou tronquées dans les cellules fusionnées dans Google Sheets
Avez-vous déjà essayé d’appliquer une formule de tableau à la cellule du haut d’une plage de cellules fusionnées ? Si oui, est-ce que cela affiche correctement toutes les valeurs ?
Lorsque vous utilisez une formule de tableau dans une plage de cellules fusionnées, cela masque un certain nombre de valeurs, en fonction du nombre de lignes fusionnées.
Veuillez consulter la Figure 6 ci-dessous pour un exemple.
=SEQUENCE(10)
Vous pouvez voir que la formule SEQUENCE dans la cellule A2 ne renvoie pas tous les nombres de 1 à 10. Certaines valeurs sont masquées à cause des cellules fusionnées.
Vous avez déjà appris comment insérer des lignes vides à l’aide d’une formule dans Google Sheets. Il est temps de le tester dans un scénario réel.
Vous pouvez choisir la formule basée sur REDUCE. J’évite la formule basée sur VLOOKUP car elle utilise une plage en tant que chaîne de texte (entre guillemets).
=REDUCE(
,A1:A10,
LAMBDA(a,v,IFNA(VSTACK(a,FILTER(<strong>SEQUENCE(10)</strong>,ROW(A1:A10)=ROW(v)),))))Cette formule en E1 renverra correctement toutes les valeurs. Cependant, nous devons entrer la formule dans une cellule non fusionnée car elle renvoie une cellule vide en haut.
La formule SEQUENCE renvoie 10 nombres. Nous devons donc spécifier A1:A10 dans la formule. Si la formule renvoie 100 nombres, vous devez spécifier A1:A100.
Si vous utilisez une autre formule au lieu de SEQUENCE, par exemple une QUERY, vous devez d’abord vérifier le nombre de lignes que la QUERY renvoie et spécifier une plage en conséquence, au lieu de A1:A100.
Note : La formule ci-dessus nécessite des cellules fusionnées de manière unique pour fonctionner correctement.
Lié :
- Comment insérer automatiquement une ligne vide en dessous de chaque groupe dans Google Sheets.
- Insérer des lignes vides pour séparer les débuts/fins de semaine dans Google Sheets.
