Comment utiliser la formule COLONNE dans Google Sheets ?

Le COLUMN formula is one of the lookup functions available within Google Sheets. It gives us the column number where the specified cell or a range of cells are located.

Syntaxe

COLUMN([cell_reference])

référence_cellule – is the address reference to the cell whose column number we need. This is an optional parameter. So, if not specified, the COLUMN formula considers the address of the cell where we keyed in the formula.

Veuillez noter que si le référence_cellule parameter is not a single cell, but is a range of multiple cells, the formula returns only the column number of the first column within the référence_cellule.

Usage: COLUMN Formula

Rendons notre compréhension de la formule plus concrète à l'aide de quelques exemples. Veuillez consulter l'instantané ci-dessous.

The first example took B1 for référence_cellule and accordingly returned 2 as output. Similarly, for F1, it returned 6 as output for the second example. The third case doesn’t have any input for the référence_cellule, hence it returned the column number of the cell in which we keyed in the formula.

In the fourth example, we provided an address to a range of cells as opposed to a single cell. And, it returned the number of the first column within the range B1:F1, which is 2.

Il est intéressant de noter que nous avons fourni une référence de plage nommée en entrée pour le référence_cellule parameter in the last example case. As shown on the extreme right of the above image, the “COLUMN1!Ages” range spans from A2 to F2. Therefore, the result is the first column number of range A1:F2.

Cas d'utilisation : Remplissage automatique des numéros

One of the useful ways we can use the COLUMN formula is when we use it in conjunction with the ARRAYFORMULA. Nous utilisons la méthode suivante pour que Google Sheets remplisse des séries de nombres pour nous sans que nous ayons à les saisir nous-mêmes.

This utility of the above formula is very handy indeed. Even if we try and insert a column anywhere between the existing columns, the numbering is taken care of automatically. To illustrate this, we will insert a column between columns F and G as shown below. And we will notice we don’t have to do anything to adjust the numbering – Google Sheets does that for us automatically.

But then, there is a small problem. What if we try inserting a column above the before cell A1? Doing so would shift the cell right and accordingly adjusts the formula as shown below, which is probably undesirable.

Nous pouvons résoudre ce problème en utilisant le INDIRECT formula as shown below. Even if we insert a column before, it doesn’t affect the numbering. However, the address reference A:L has now become static. So, inserting columns between A through L will not adjust the reference to A:M anymore (like it did in the third snapshot).