Vous devez savoir ce que fait l'opérateur double unaire (–) dans Excel

J'avais l'habitude de voir le double tiret (–) dans les feuilles de calcul Excel et je me demandais ce qu'il faisait. Il s'agit d'un raccourci essentiel pour forcer Excel à traiter VRAI et FAUX comme les nombres 1 et 0, ce qui signifie que vous pouvez facilement additionner ou compter les résultats d'un test logique sur une plage. Maintenant, je sais et je veux partager cette astuce puissante avec vous.

La fonction principale : la coercition booléenne vers numérique

Le double-unaire force une valeur d'un type de données (booléen) à un autre (numérique) — un processus appelé type de coercition. Laissez-moi vous montrer un exemple simple.

Cette formule renvoie VRAI car la valeur dans la cellule A1 est supérieure à 10 :

=A1>10

Un seul signe moins dans la formule convertit VRAI en son équivalent numérique négatif (-1) :

=-(A1>10)

L'ajout d'un deuxième moins (donc le signe double-unaire) inverse le nombre à 1.

=--(A1>10)

Si la valeur de la cellule A1 ne répond pas aux critères booléens, le double-unaire renvoie 0.

Le problème : pourquoi les tableaux logiques échouent avec les sommes simples

Lorsque vous créez un tableau de résultats logiques, la fonction SOMME traite VRAI et FAUX comme non numériques et les ignore, ce qui donne 0.

Dans cette feuille de calcul, supposons que vous surveilliez les totaux des ventes hebdomadaires et que vous vérifiiez si elles atteignent l'objectif de 500 $. La formule de la cellule C2 renvoie un tableau renvoyant VRAI et FAUX selon que les valeurs correspondantes dans la colonne B répondent aux critères :

=B2:B6>=F2

Désormais, plutôt que de vous dire si vous avez atteint l'objectif pour chaque semaine individuellement, vous souhaitez qu'Excel compte le nombre total de ventes qui ont atteint l'objectif global. Cependant, si vous enveloppez la formule ci-dessus dans SUM, elle renvoie zéro car les valeurs booléennes ne sont pas numériques :

=SUM(B2:B6>=F2)

Dans ce cas précis, vous pourrait utilisez COUNTIF. Cependant, j'utilise la fonction SUM uniquement pour démontrer comment l'opérateur double-unaire force la conversion numérique du tableau sous-jacent.

Le double-unaire convertit les éléments du tableau, ce qui signifie que la fonction SUM peut les traiter :

=SUM(--(B2:B6>=F2))

Ici, en coulisses, Excel fait passer le tableau de {TRUE, FALSE, TRUE, FALSE, FALSE} à {1, 0, 1, 0, 0}, c'est pourquoi nous obtenons le nombre correct de 2.

Le cas d'utilisation de l'alimentation : compter les cas d'échec de COUNTIFS

L'opérateur double-unaire brille vraiment lorsque vous devez exécuter une logique de tableau complexe que les fonctions intégrées telles que COUNTIFS ne peuvent pas exécuter. La principale limitation de COUNTIFS est que ses critères sont statiques : il ne peut pas comparer dynamiquement les plages ligne par ligne.

Imaginez que vous deviez compter combien de vendeurs ont dépassé leurs objectifs individuels dans cette feuille de calcul.

Pour le faire correctement, vous devez comparer chaque cellule de la colonne Ventes à sa voisine dans la colonne Objectif. Si vous essayez d'utiliser COUNTIFS pour ce faire, cela produit un résultat inattendu :

=COUNTIFS(B2:B6,">"&C2:C6)

En effet, au lieu de comparer B2 à C2, B3 à C3, etc., Excel traite la plage entière (C2 : C6) comme un tableau de plusieurs critères à appliquer à la même plage (B2 : B6). Cela renvoie un tableau de résultats incorrects ({3, 1, 3, 2, 3}), car il compte le nombre de ventes supérieures à chaque valeur d'objectif individuel dans l'ensemble de la liste. En d’autres termes, trois totaux de ventes sont supérieurs à 81, un est supérieur à 97, trois sont supérieurs à 71, et ainsi de suite.

Au lieu de cela, vous utilisez SUMPRODUCT avec l'opérateur double-unaire. Cela oblige Excel à évaluer le test logique ligne par ligne :

=SUMPRODUCT(--(B2:B6>C2:C6))

Voici comment cela fonctionne :

1. Test logique : La comparaison (B2:B6>C2:C6) renvoie le tableau correct de valeurs booléennes : {FALSE, FALSE, TRUE, FALSE, TRUE}.
2. Coercition de type : L'opérateur double-unaire (–) convertit ce tableau booléen en tableau numérique : {0, 0, 1, 0, 1}.
3. Somme finale : La fonction SUMPRODUCT ajoute les éléments : 0+0+1+0+1 pour renvoyer 2, ce qui est le nombre correct.

Comme COUNTIFS, SUMIFS se limite également à vérifier des critères statiques. Ainsi, par exemple, si vous souhaitez additionner les ventes uniquement lorsqu'elles dépassent la vente moyenne, vous devez également utiliser la fonction SUMPRODUCT avec l'opérateur double-unaire.

Pourquoi utiliser SUMPRODUCT, pas SUM ?

Dans cet exemple, vous pouvez utiliser SUM au lieu de SUMPRODUCT. Cependant, la différence entre les deux concerne la compatibilité. SUMPRODUCT gère les tableaux de manière native, en prenant automatiquement le tableau numérique généré par l'opérateur double-unaire et en additionnant les éléments. Dans Excel pour Microsoft 365 et les versions perpétuelles publiées en 2021 ou ultérieurement, SUM peut le faire automatiquement, bien que dans les anciennes versions, vous devez appuyer sur Ctrl+Shift+Entrée pour valider la formule, qui l'enveloppe entre accolades :

{=SUM(--(B2:B6>C2:C6))}

Dans les anciennes versions d'Excel, si vous validez cette formule avec une simple Entrée, elle renverra soit une erreur, soit une valeur incorrecte. Ainsi, SUMPRODUCT est plus simple, plus sûr et rétrocompatible.

Et pourquoi pas des méthodes alternatives ?

Étant donné qu'Excel convertit VRAI et FAUX en 1 ou 0 s'il voit un symbole mathématique juste devant eux, il est juste de se demander si l'opérateur double-unaire est vraiment nécessaire. Puisque toute opération arithmétique effectue ce type de coercition, il existe en effet des formules alternatives qui aboutissent au même résultat :

Vous pouvez multiplier l'ensemble du tableau logique par 1 :

=SUMPRODUCT((B2:B6>=F2)*1)

Vous pouvez également ajouter 0 au tableau :

=SUMPRODUCT((B2:B6>=F2)+0)

Ces méthodes réussissent à convertir les valeurs booléennes en nombres, ce qui signifie que le tableau devient dénombrable. Cependant, je préfère toujours utiliser l'opérateur double-unaire pour ces raisons :

• Cela signale clairement l’intention : L'opérateur double-unaire est la méthode de coercition booléenne-numérique universellement acceptée. Son seul objectif est la conversion du type de données, et le voir indique immédiatement à tous ceux qui voient la feuille de calcul exactement ce que fait la formule. L'utilisation de *1 ou +0 est moins explicite et peut être confondue avec un ajustement arithmétique distinct du résultat.
• C'est plus propre et moins encombré : L'opérateur double-unaire est la manière la plus concise d'appliquer une opération de coercition mathématique à un tableau. Par exemple, si vous disposez d’une formule matricielle imbriquée complexe, deux tirets simples permettent de conserver une logique globale simple.
• C'est légèrement plus efficace : Bien que la différence soit négligeable, l’opérateur double-unaire peut être plus efficace dans des classeurs extrêmement volumineux. Lorsque vous utilisez *1 ou +0, Excel doit traiter cela comme une opération distincte sur chaque cellule ; cependant, l'opérateur double-unaire convertit simplement le type de données des valeurs du tableau.

Ainsi, bien que les alternatives soient parfaitement fonctionnelles, l’opérateur double-unaire est le moyen le plus propre, le plus efficace et le plus professionnel d’effectuer la coercition booléenne vers numérique.

---

Le double tiret n’est pas le seul symbole Excel capable de débloquer un contrôle avancé des feuilles de calcul et des formules. Par exemple, le symbole # est essentiel dans les versions modernes du programme pour inclure tous les résultats d'un tableau dynamique dans une formule, et le signe @ renvoie une valeur unique d'une plage ou d'un tableau.