EQUIV et INDEX : le duo de choc !

Au moment de construire des simulations, des études et autres travaux nécessitant des calculs fondés sur des hypothèses variables, vous avez certainement ressenti le besoin de balayer des tableaux en fonction d'un ou plusieurs choix.

Pour que la démonstration soit parlante à tout un chacun, et colle quelque peu à l'actualité, cet article recourt à l'exemple de la TVA.

L'auteur tient le classeur Excel correspondant à votre disposition, sur simple demande (utilisez la rubrique Contact du site www.ao-editions ou du site jeanluctafforeau.com).

Imaginez donc que vous bâtissiez un tableau de calcul dans lequel, pour obtenir un taux de TVA, vous devez préciser deux critères :

• Le pays européen concerné ;
  
• Le type de taux de TVA : normal, réduit, super réduit.

Au moment de calculer un montant TTC, votre formule de calcul devra être capable de trouver le taux requis en balayant un tableau ressemblant à celui-ci :



La source du tableau se trouve à ce lien. Des simplifications ont été ménagées pour ne pas compliquer inutilement l'exposé. C'est ainsi que, dans les pays qui disposent de deux taux 'réduits' (Finlande, Portugal notamment), le taux le plus bas a été considéré comme un taux 'super-réduit'. En outre, la notion de 'taux parking', quoique amusante dans son libellé, a été également exclue.

Quel est l'objectif de la formule à rédiger ?
À partir de deux choix, trouver la valeur ad hoc dans le tableau. Par exemple : quel est le taux réduit de la Slovénie ?

De quoi avez-vous besoin pour cela, outre les habituelles cellules des montants HT et TTC ?

• D'une cellule pour indiquer le pays ;
  
• D'une cellule pour indiquer le type de taux ;
  
• D'une cellule pour afficher le taux de TVA qui en résulte.

L'un des conseils cruciaux du livre est de nommer les plages destinées à être impliquées dans des formules. Pour ce cas pratique, il convient donc :

1. De nommer le tableau des taux de TVA
2. De nommer les critères de choix (pays et type de taux)

Voici des propositions de nommage :

• La colonne des pays, à gauche, est baptisée tout simplement Pays ;
• La ligne des types de taux (3 cellules) est nommée TypesTaux ;
• Le tableau des taux proprement dits reçoit la dénomination tout aussi simple de Tableau.

Pour nommer des plages, lisez les nombreux conseils du livre qui traitent en détails des techniques possibles (l'une des façons de procéder consiste à sélectionner la plage puis à saisir le nom dans la zone des noms, à gauche de la barre des formules).

Concernant les cellules destinées à gérer les choix et à calculer un montant TTC en conséquence, il vous est proposé d'ajouter le pronom possessif 'mon' pour indiquer vos options :

• monPays pour choisir le pays ;
  
• monTypeTaux pour sélectionner l'une des trois catégories de taux ;
  
• monTaux pour afficher le taux de TVA correspondant.

Afin de faciliter et de contrôler la saisie du pays et du type de taux, insérez des listes de validation dans les deux cellules, en actionnant la commande de menu Données > Validation :



Ce contrôle de validation consiste à restreindre la saisie aux seules valeurs de la plage nommée TypesTaux, indiquée dans le champ Source, précédée du signe =. En décochant l'option Ignorer si vide, vous interdisez que la cellule ne comporte aucun choix (quoique facultative, cette précaution vous évitera d'afficher des erreurs dans les formules d'INDEX et EQUIV).

Le résultat donne ceci lorsque vous accédez à la cellule :



Le choix est effectué dans une liste déroulante. Cette facilité ergonomique est à la fois élégante et sûre (la saisie manuelle des mots 'super' et 'réduit' ne se justifiant pas, ne serait-ce qu'en raison des risques d'erreurs orthographiques).

Les préparatifs étant achevés, c'est le moment d'entrer dans le vif du sujet.

Pour trouver des valeurs dans un tableau, Excel propose toute une batterie de fonctions.

Notre thèse est que la combinaison d'INDEX et d'EQUIV est plus aisée à comprendre que les paramètres d'autres fonctions comme RECHERCHEV par exemple. Pourquoi ? Parce que l'on sépare bien les rôles : EQUIV cherche tandis qu'INDEX trouve (localise) la valeur cherchée dans une plage. En outre, une formulation combinant ces deux fonctions permet de gérer des plages qui ne voisinent pas forcément dans une feuille de calcul.

Initiation à la fonction EQUIV


Dans cet exemple simplifié, nous sommes un peu chauvins en cherchant où se situe la cellule France (argument 1) dans le petit tableau de 10 lignes situé en C5:C14 (argument 2) et en demandant à Excel d'être exact et strict (argument 3, chiffre zéro).

La fonction EQUIV vous répond 8 car la France est située à la huitième ligne de la plage.

Initiation à la fonction INDEX



La fonction INDEX se contente pour sa part de pointer sur l'élément demandé.
Dans cet exemple, également simplifié, la formule affiche, dans la plage C5:C14 (argument 1) la huitième ligne (argument 2), soit… la France.

Mais revenez aux taux de TVA.

En combinant EQUIV et INDEX, vous allez être en mesure de balayer le tableau des taux de TVA grâce à deux EQUIV (le pays et le type de taux) associés à une fonction INDEX plus sophistiquée incluant à la fois un indice de ligne et un indice de colonne.



Voici les commentaires détaillés de sa syntaxe :

INDEX comporte trois principaux arguments :

1. La plage concernée par la recherche, soit dans l'exemple la plage nommée Tableau ;
2. Un indice de ligne (le pays) ;
3. Un indice de colonne (le type de taux).

=INDEX( Tableau; EQUIV(monPays;Pays;0); EQUIV(monTypeTaux;TypesTaux;0) )

• La ligne du pays est fournie par l'équivalence (EQUIV) de monPays (France) dans la plage nommée Pays ;
• La colonne du type de taux est fournie par l'équivalence (EQUIV) de monTypeTaux (réduit) dans la plage nommée TypesTaux.

Le résultat intermédiaire de la fonction est le suivant :

INDEX(Tableau;8;2), c'est-à-dire la ligne 8, colonne 2 du tableau, où se trouve bien 5,5%, notre taux réduit.



Si vous changez de pays et de type de taux, le taux de TVA est immédiatement mis à jour.
Vous remarquerez que cette formulation atteint son objectif alors même que la colonne des pays est mal triée, et fonctionne correctement même si les plages concernées ne sont pas adjacentes.



Enfin, pour peu que vous nommiez les différentes cellules comme dans l'illustration ci-dessus, les formules de calculs seront très lisibles :

• Montant de la TVA : =montantHT*monTaux
• Montant TTC : =montantHT+montantTVA

Remarque : en toute rigueur, vous devrez tester que le taux de TVA existe bien avant d'effectuer le calcul, notamment lorsqu'un taux super réduit est demandé et que le pays n'en pratique aucun. De même, l'arrondi au centime d'euro doit être appliqué pour être conforme aux règles monétaires.

Pour terminer, triez le tableau, tout d'abord par ordre alphabétique de pays, ce qui facilitera la saisie dans la liste déroulante des pays :



…puis par taux normal, afin de nous situer dans la hiérarchie des taux de TVA européens (avant que la TVA n'augmente, diraient les mauvaises langues) :



Ce qui permet de constater qu'avec notre taux bizarroïde qui ne tombe pas juste (19,6% *), nous figurons en milieu de tableau (13è sur 27), grâce à un important bataillon de taux à 20% tout rond. Chypre et le Luxembourg sont au plancher communautaire (15%) tandis que l'Espagne et le Royaume-Uni les suivent de près.

Quant à la TVA sociale… c'est une autre histoire !

(*) Annexe : comment a évolué le taux de TVA dit 'normal' ?



Se situant à 16,66% en 1968, il grimpe à 23% puis reste presque six ans à 20%. C'est Raymond Barre qui le ramène à 17,6% (merci pour l'exercice de calcul mental, cher professeur !). Pour réduire le déficit budgétaire, le gouvernement socialiste est contraint de l'augmenter d'un point dès 1982. Dix ans plus tard, en 1992, le taux majoré de 33% est supprimé, ce qui constitue une forte réduction de fait de la TVA. Après une longue période de stabilité, le taux normal passe à 20,6% sous le gouvernement d'Alain Juppé, afin de satisfaire aux critères d'entrée dans l'Euro. Le gouvernement Jospin parvient cependant à le réduire d'un point en 1997. Depuis dix ans, donc, nous pratiquons ce taux de 19,6%.