Comment utiliser les fonctions de référence dans Excel 2016 - dummies

La référence Excel 2016 fonctionne dans la liste déroulante du bouton de commande Recherche et référence dans les formules onglet du ruban sont conçus pour traiter spécifiquement différents aspects des références de cellules dans la feuille de calcul. Ce groupe de fonctions comprend:

ZONES pour renvoyer le nombre de zones dans une liste de valeurs (
zones sont définies comme une plage de cellules contiguës ou une seule cellule dans la référence de la cellule)

COLUMN pour renvoyer le nombre représentant la position de colonne d'une référence de cellule
COLUMNS pour renvoyer le nombre de colonnes dans une référence
FORMULATEXT pour renvoyer la formule référencée comme chaîne de texte > GETPIVOTDATA pour renvoyer les données stockées dans un tableau croisé dynamique
HYPERLINK pour créer un lien qui ouvre un autre document stocké sur votre ordinateur, réseau ou Internet (vous pouvez également le faire avec la commande Insérer → Hyperlien)
< ! --2 ->
INDIRECTE pour renvoyer une référence de cellule spécifiée par une chaîne de texte et amener le contenu de la cellule à laquelle elle se réfère à cette cellule
LOOKUP pour renvoyer une valeur d'un tableau
OFFSET à renvoie une référence à une plage de cellules spécifiée par le nombre de lignes et de colonnes d'une cellule ou d'une plage de cellules
ROW pour renvoyer le numéro de ligne d'une référence de cellule
ROWS pour renvoyer le nombre de lignes dans une plage de cellules ou tableau
RTD pour renvoyer des données en temps réel à partir d'un serveur exécutant un programme prenant en charge l'automatisation COM TRANSPOSE pour renvoyer un tableau vertical en tant que tableau horizontal et vice versa
Obtenez le skinny sur les colonnes et les lignes
Les fonctions COLUMNS et ROWS renvoient le nombre de colonnes et de lignes dans une plage de cellules ou un tableau particulier. Par exemple, si vous avez une plage de cellules dans la feuille de calcul nommée product_mix, vous pouvez connaître le nombre de colonnes qu'elle contient en entrant cette formule:

= COLUMNS (product_mix)

Si vous voulez connaître le nombre de lignes utilise, vous entrez alors cette formule:

= ROWS (product_mix)

Vous pouvez utiliser les fonctions COLUMNS et ROWS ensemble pour calculer le nombre total de cellules dans une plage particulière. Par exemple, si vous voulez connaître le nombre exact de cellules utilisées dans la plage de cellules product_mix, vous créez la formule de multiplication simple suivante à l'aide des fonctions COLUMNS et ROWS:

= COLUMNS (product_mix) * ROWS (product_mix) > Ne confondez pas la fonction COLUMNS (pluriel) avec la fonction COLUMN (singulier) et la fonction ROWS (pluriel) avec la fonction ROW (singulier).La fonction COLUMN renvoie le numéro de la colonne (comme si Excel utilisait le système de référence R1C1) pour la référence de cellule que vous spécifiez comme seul argument. De même, la fonction ROW renvoie le numéro de la ligne de la référence de cellule que vous spécifiez comme argument.

Transférer les gammes de cellules

La fonction TRANSPOSE vous permet de changer l'orientation d'une plage de cellules (ou d'une matrice). Vous pouvez utiliser cette fonction pour transposer une plage de cellules verticale dans laquelle les données parcourent les rangées de colonnes adjacentes à une autre où les données s'exécutent sur les colonnes des lignes adjacentes et inversement. Pour utiliser avec succès la fonction TRANSPOSE, non seulement vous devez sélectionner une plage qui a un nombre opposé de colonnes et de lignes, mais vous devez également l'entrer en tant que formule matricielle.

Par exemple, si vous utilisez la fonction TRANSPOSE pour transposer une plage de 2 x 5 cellules (c'est-à-dire une plage occupant deux rangées adjacentes et cinq colonnes adjacentes), vous devez sélectionner une plage vide de 5 x 2 (c'est-à-dire, une plage qui prend cinq lignes adjacentes et deux colonnes adjacentes) dans la feuille de calcul avant d'utiliser le bouton Insérer une fonction pour insérer la fonction TRANSPOSE dans la première cellule. Ensuite, après avoir sélectionné la plage 2 x 5 qui contient les données que vous souhaitez transposer dans la zone de texte Array de la boîte de dialogue Arguments de la fonction, vous devez appuyer sur Ctrl + Maj + Entrée pour fermer cette boîte de dialogue et entrer dans la fonction TRANSPOSE dans toute la plage de cellules sélectionnée sous la forme d'une formule matricielle (entourée d'accolades).

Supposons que vous souhaitiez transposer les données entrées dans la plage de cellules A10: C11 (un tableau 2 x 3) dans la plage de cellules vides E10: F12 (un tableau 3 x 2) de la feuille de calcul. Lorsque vous appuyez sur Ctrl + Maj + Entrée pour compléter la formule matricielle, après avoir sélectionné la plage de cellules A10: C11 comme argument

tableau, Excel place la formule matricielle suivante dans chaque cellule de la plage:

{ = TRANSPOSE (A10: C11)} La figure suivante illustre l'utilisation de la fonction TRANSPOSE. La plage de cellules B2: C4 contient un tableau 3 x 2. Pour convertir ce tableau 3 x 2 dans la plage de cellules B2: C4 en tableau 2 x 3 dans la plage B6: D7, procédez comme suit: Sélectionnez la plage de cellules vides B6: D7 dans la feuille de calcul.

Cliquez sur le bouton de commande Recherche et référence dans l'onglet Formules du ruban, puis choisissez l'option TRANSPOSE dans le menu déroulant du bouton.

Excel insère = TRANSPOSE () dans la barre de formule et ouvre la boîte de dialogue Arguments de la fonction dans laquelle la zone de texte Array est sélectionnée.

Glissez dans la plage de cellules B2: C4 dans la feuille de calcul pour que la zone de texte Argument de tableau contienne B2: C4 et que la formule dans la barre de formule indique maintenant = TRANSPOSE (B2: C4).
Appuyez sur Ctrl + Maj + Entrée pour fermer la boîte de dialogue Insérer des arguments (ne cliquez pas sur OK) et pour insérer la formule de tableau TRANSPOSE dans la plage de cellules B6: D7 comme indiqué ici.

Utiliser la fonction TRANSPOSE pour changer l'orientation d'un tableau simple.
Cliquez sur le bouton OK dans la boîte de dialogue Arguments de la fonction pour insérer la fonction TRANSPOSE dans la cellule active de la sélection de cellule en cours.Cela retourne le #VALUE! valeur d'erreur à la cellule. Vous devez vous rappeler d'appuyer sur Ctrl + Maj + Entrée pour à la fois fermer la boîte de dialogue et mettre la formule dans toute la plage de cellules.
Si tout ce que vous voulez faire est de transposer des en-têtes de lignes et de colonnes ou un simple tableau de données, vous n'avez pas besoin de passer par le rigmarole de créer une formule matricielle en utilisant la fonction TRANSPOSE. Copiez simplement la plage de cellules à transposer avec le bouton de commande Copier dans l'onglet Accueil du ruban. Placez le curseur de la cellule dans la première cellule vide où la plage transposée doit être collée avant de cliquer sur l'option Transposer dans le menu déroulant du bouton de commande Coller.