Formule de tableau PRODUIT pour les produits par ligne dans Google Sheets

L’utilisation de la fonction PRODUIT n’est pas possible en tant que formule de tableau pour calculer les produits par ligne dans Google Sheets. Cette fonction prend des tableaux (plages de cellules) et renvoie un résultat sous forme de cellule unique.

Dans le cas où vous souhaitez multiplier une série de nombres ensemble par ligne, vous pouvez utiliser soit une formule PRODUIT que vous faites glisser et déposez, soit une formule de tableau alternative.

L’alternative à la formule de tableau PRODUIT peut être une formule dynamique utilisant une combinaison de la fonction QUERY ou une formule non dynamique utilisant l’opérateur astérisque (*).

Pour mieux comprendre pourquoi la formule QUERY ou l’astérisque est nécessaire pour calculer les produits par ligne, veuillez consulter l’exemple ci-dessous :

Dans l’exemple ci-dessus, j’ai utilisé la formule PRODUIT dans la cellule G2 et l’ai fait glisser vers le bas pour couvrir toutes les lignes.

Dans ce tutoriel, je vais vous parler de trois alternatives de formule de tableau pour les produits par ligne dans Google Sheets. Parmi ces trois solutions, la plus simple consiste à utiliser l’astérisque et la fonction BYROW. Cependant, la première n’est pas une formule dynamique.

Produits par ligne en utilisant l’astérisque dans Google Sheets (non dynamique)

Si le nombre de colonnes est limité comme dans l’exemple ci-dessus, vous pouvez utiliser l’opérateur astérisque comme suit pour les produits par ligne dans Google Sheets.

Certains d’entre vous pensent peut-être que vous pouvez utiliser la formule basée sur l’opérateur astérisque comme suit :

=FormuleTableau(B2:B*C2:C*D2:D*E2:E)

Mais ce n’est pas correct !

Cela renverrait 0 dans certaines cellules de la colonne de résultat. De plus, vous pouvez voir que plusieurs 0 sont présents comme une traînée dans toute la colonne après la dernière ligne (ligne n°10).

Lorsque vous multipliez un nombre supérieur à 0 par zéro ou zéro par un nombre supérieur à 0, le résultat est 0.

Étant donné que la formule traite les valeurs dans les cellules vides comme des zéros, le problème mentionné ci-dessus se pose.

Solutions pour corriger l’erreur

Pour que cette formule fonctionne correctement, nous devons spécifier deux choses dans la formule. Dans les étapes ci-dessous, je vais vous les expliquer.

1. Remplacez les cellules vides dans la plage par le nombre 1 dans l’expression de la formule.

2. Limitez la formule pour qu’elle ne s’étende qu’à la dernière cellule non vide de la colonne A.

Comment mettre en œuvre ces deux points dans la formule et renvoyer les produits par ligne corrects en utilisant une formule de tableau PRODUIT alternative dans Google Sheets ?

Nous pouvons utiliser une série de tests logiques SI avec chaque colonne comme suit :

=FormuleTableau( si(B2:B="",1,B2:B)* si(C2:C="",1,C2:C)* si(D2:D="",1,D2:D)* si(E2:E="",1,E2:E) )

La formule ci-dessus dans la cellule G2 renverrait les produits par ligne corrects dans Google Sheets, mais elle laisserait le nombre 1, au lieu de 0, dans les lignes vides.

Maintenant, nous devons supprimer le #1 en limitant la formule pour qu’elle s’étende jusqu’à la dernière ligne avec une valeur.

Voici cette formule :

=FormuleTableau( si( A2:A="",, si(B2:B="",1,B2:B)* si(C2:C="",1,C2:C)* si(D2:D="",1,D2:D)* si(E2:E="",1,E2:E) ) )

La formule ci-dessus est l’alternative à la formule de tableau PRODUIT dans Google Sheets.

Je ne recommande pas la formule ci-dessus dans les deux scénarios suivants :

1. Si vous avez plusieurs colonnes, écrire la formule sans erreurs peut être une tâche difficile.

2. Lorsque vous insérez de nouvelles colonnes entre les colonnes existantes, vous devrez peut-être modifier la formule manuellement. Cela prend donc du temps et peut entraîner des erreurs.

Alternative de formule de tableau PRODUIT en utilisant une combinaison de la fonction QUERY (dynamique)

La formule que vous allez découvrir ici vous aidera à surmonter les inconvénients mentionnés ci-dessus.

Tout d’abord, voyons comment écrire une formule pour remplacer les formules #1 et #2 ci-dessus.

La requête suivante dans la cellule G1 remplacera la formule #1, et la requête et la combinaison SI remplaceront la formule #2.

=FormuleTableau(requete({si(B2:E="",1,B2:E)},"Select Col1*Col2*Col3*Col4",0))

=FormuleTableau(si(long(A1:A),requete({si(B2:E="",1,B2:E)},"Select Col1*Col2*Col3*Col4",0),))

REMARQUE : Veuillez insérer les formules ci-dessus (l’une des deux) dans la cellule G1 car elle renvoie un en-tête autre que les produits.

Les formules ci-dessus, la formule #1 et la formule #3, sont les mauvaises formules, tandis que la formule #2 et la formule #4 sont les formules non dynamiques correctes pour renvoyer les produits par ligne dans Google Sheets.

Nous pouvons modifier la formule #4 comme alternative à la formule de tableau de produit dynamique pour les produits par ligne dans Google Sheets.

=FormuleTableau({"Produit";ArrayFormula( si( long(A2:A), Requete( Requete( ({si(B2:E100="",1,B2:E100)}),"Select "&"Col"&TEXTJOIN("*Col",1,COLUMN(B1:E1)-1) ), "offset 1",0 ), ) )})

Explication de la formule :

D’abord, examinons la Query interne (qui est une version modifiée de la formule #4) et apprenons-la d’abord.

=FormuleTableau(requete({si(B2:E100="",1,B2:E100)},"Select "&"Col"&TEXTJOIN("*Col",1,COLUMN(B1:E1)-1)))

Dans cette formule, la clause SELECT de la formule #4, c’est-à-dire Select Col1Col2Col3Col4, est remplacée par une référence de colonne dynamique, c’est-à-dire Select « & »Col »&TEXTJOIN(« Col »,1,COLUMN(B1:E1)-1).

Je vais l’expliquer. Avant cela, veuillez consulter le résultat renvoyé par la formule ci-dessus.

Maintenant, pour l’explication.

La formule COLUMN(B1:E1)-1 renvoie les nombres 1, 2, 3 et 4.

Les numéros de colonne de la plage B1:E1 (les colonnes impliquées dans le calcul du produit) sont 2, 3, 4 et 5. Pour obtenir 1, 2, 3 et 4, j’ai utilisé -1.

La fonction TEXTJOIN convertit les nombres en Col1Col2Col3*Col4 (syntaxe de la requête).

C’est dynamique car lorsque vous insérez de nouvelles colonnes dans la plage B2:E, la référence de colonne à l’intérieur de la fonction COLUMN sera ajustée en conséquence.

Quel est le rôle de la requête externe dans la formule finale des produits par ligne ?

Elle permet en fait de décaler d’une ligne (voir G1 dans l’image ci-dessus) pour supprimer l’en-tête.

Après avoir supprimé l’en-tête, nous avons ajouté un en-tête personnalisé en utilisant des accolades comme indiqué dans la syntaxe ci-dessous.

{« Product »; Formule_Requete}

Vous pouvez changer le texte selon vos préférences.

Formule de produit de tableau (renversement) utilisant BYROW – Nouveau

J’ai utilisé la colonne A (qui contient les dates) pour limiter l’expansion du tableau dans les lignes vides dans les deux formules de produit de tableau mentionnées ci-dessus (astérisque et requête).

Cependant, si vous utilisez le produit suivant et le BYROW dans la cellule G1, vous pouvez éviter d’utiliser cette colonne.

=FormuleTableau({"Product (Byrow)";byrow(B2:E,lambda(row,if(count(row)=0,,product(row))))})

Parce que la formule compte le nombre de valeurs dans chaque ligne et, s’il est égal à 0, elle renvoie une cellule vide, sinon le produit.

J’ai ajouté la formule ci-dessus dans la cellule H1 dans la feuille d’exemple ci-dessous.

Voilà tout ce qu’il y a à savoir sur l’alternative à la formule de tableau PRODUIT pour les produits par ligne dans Google Sheets.

Merci de m’avoir suivi. Profitez-en !

Source