Best of des formules !

Permet de sommer seulement les données répondant à un ou plusieurs critères (comme si on filtrait dans les colonnes).

Exemple 1 : (les chiffres sont faux)

j’ai une liste des personnes de l’équipage avec leur solde d’HS et je veux la somme des heures sup en cours par pôle :

=SOMMEPROD((plage service= « compta »)*(plage nb heures))

Exemple 2 : je veux mon coût total pour les articles vendus, seulement lorsque la quantité vendue dépasse les 100 pièces :

Attention : bien retenir la place des parenthèses, et le fait que chaque partie de formule est reliée à l’autre par le symbole * 

Fait à peu près la même chose que SOMMEPROD ! Lorsqu’il n’y a qu’une seule condition, utiliser SOMME.SI, et à partir de 2, utiliser SOMME.SI.ENS.

Exemple 1 : pareil que ci-dessus, je veux le sous-total des heures sup pour le pôle info :

=SOMME.SI(plage du critère;valeur recherchée;plage à sommer)

SOMME.SI.ENS a 2 critères au minimum, et jusqu’à 127 !

Exemple 2 : je veux la somme des heures sup pour le polak et seulement pour les personnes pour qui il y a plus de 40h :

=SOMME.SI.ENS(plage à sommer;plage du critère 1 ; critère 1 ; plage du critère 2 ; critère 2 ; plage du critère 3 ; critère 3 ; …)

NB : dans cet exemple, pour dire supérieur à 40, on écrit « >40 ». Si on avait été dans une sommeprod, on aurait écrit :

=SOMMEPROD((C3:C24)*(A3:A24= « polak »)*(C3:C24>40))

Utile pour créer des groupes dans lesquels on peut exécuter des actions telles que SOMME, NB, MOYENNE, PRODUIT, MAX etc.

Elle se compose comme suit : SOUS.TOTAL(n° de fonction;plage1;plage2 ;…)

Ci-dessous toutes les méthodes (n° de fonctions) possibles :

Le plus souvent, nous serons sur un sous-total de type SOMME, et donc avec le code méthode 9 (lignes masquées inclues) ou 109 (hors lignes masquées).

Ces 3 formules de recherche permettent de rapatrier le résultat d’une cellule à partir d’une valeur recherchée (recherchev ou rechercheh) ou sur une recherche multi-critères (combinaison de index + equiv).

• Recherchev est utilisé lorsque l’élément recherché est dans une colonne,

• Rechercheh est utilisé lorsque l’élément recherché est dans une ligne,

• et la combinaison Index Equiv est par exemple utilisé lorsque l’on recherche une cellule à partir d’un critère en ligne, et un autre en colonne.

Exemple 1 : je veux ramener le nombre d’heures sup d’Aurélie.

Mon identifiant commun à la base de donnée de gauche et à la recherche que j’effectue est Aurélie. Je cherche donc Aurélie dans la base de donnée (doit être en 1ère colonne de la plage sélectionnée) et je rapatrie le nombre d’heures (2è colonne à droite à partir du début de ma plage).

=RECHERCHEV(élément recherché;plage dans laquelle sont contenus ET l’identifiant (1ère colonne) ET le contenu à ramener ;n° de colonne dans laquelle se trouve l’élément à ramener;0).

Le « 0 » final signifie que l’on accepte pas de réponse proche, seulement le résultat exact.

Exemple 2 : pour un rechercheh => ramener la ville associée lorsque le dossier recherché est A-2

La combinaison INDEX + EQUIV

Si la fonction EQUIV renvoie le numéro de ligne :

=INDEX(tableau; EQUIV(valeur_recherchée; plage_de_recherche; 0); no_colonne)

Si la fonction EQUIV renvoie le numéro de colonne :

=INDEX(tableau; no_ligne; EQUIV(valeur_recherchée; plage_de_recherche; 0))

Exemple 3 : j’ai un tableau à double entrée avec les articles d’une part, et le prix selon plusieurs tarifications (listes de prix) d’autre part. Je veux pouvoir faire varier le prix de vente selon ces 2 critères (on peut imaginer des menus déroulants dans les cases B17 et B20). Ici par exemple, je veux remonter le prix de vente de la Bière Session IPA Bio

La formule se compose comme suit :

=INDEX(plage totale du tableau;EQUIV(valeur recherchée en ligne;en-tête de ligne où chercher;0);EQUIV(valeur recherchée en colonne;en-tête de colonne où chercher;0).

NB : le 0 signifie que l’on ne veut pas de résultat proche (idem recherchev), mais seulement le résultat exact.

Dans les plages d’en-tête ligne ou colonne, il faut inclure B1, sinon on se décale d’une case (il va ramener C11 au lieu de C12 par exemple).

Identifie la valeur maximale (MAX) ou minimale (MIN) dans une série.

S’écrit comme suit :

=MAX(plage de valeurs)

=MIN(plage de valeurs)

Exemple 1 : quelle sont les plus haute / faible valeurs d’heures sup dans l’équipe ? 

Cette fonction calcule une moyenne sur les cellules sélectionnées.

Cette fonction calcule la médiane sur les cellules sélectionnées.

Une formule pour se contrôler, rechercher des valeurs correctes / incorrectes, ou ramener un résultat différent selon un ou plusieurs critères.

La formule se construit comme suit :

=SI(argument;alors valeur si vrai;sinon valeur si faux).

La valeur pouvant être un texte (entre guillemets), la valeur d’une cellule, une somme, etc.

Dans le cas d’un cumul de conditions :

=SI(argument 1;alors valeur si vrai;SI(argument 2 ; alors valeur si vrai;SI(argument 3;alors valeur si vrai, sinon valeur si tout est faux)))

et on referme toutes les parenthèses à la fin.

Exemple 1 : vérifier que le prix Grande Distrib est bien inférieur au prix Petite Distrib :

Exemple 2 : classer les remises du prix associatif par montant selon qu’il n’y en a aucune, une supérieure à 0,5€ ou une inférieure à 0,50€ :

Exemple 3 : convertir un n° de jour de la semaine en libellé jour de la semaine 

La fonction Sierreur permet de dire quoi faire des arguments non vérifiés : une valeur non numérique qui empêche de faire une somme, un recherchev qui ne donne aucun résultat car la valeur recherchée n’est pas dans la base de donnée, etc.

On est souvent amené à faire un cumul de conditions avec des SIERREUR et des SI.

Exemple 1 : je cherche le prix associatif d’un article en 33cl alors que visiblement il n’existe qu’en 75cl dans le tableau : 

Parfois on a besoin de regrouper plusieurs cases en une seule : ça peut permettre de créer un identifiant unique (ref article + libellé + fournisseur) pour réaliser un RECHERCHEV par exemple. La formule CONCAT est faite pour ça !

Elle peut se remplacer par le symbole &.

NB : se pratique sur tous types de formats de cellule (texte, mais pas que !).

Exemple 1 : fusion de la référence de l’article et du libellé, en insérant un espace entre les 2 pour plus de lisibilité : 

Ces formules servent à casser un libellé pour ne garder que les 1^ers ou derniers caractères. Si l’on veut des caractères situés entre le début et la fin (mais toujours placé pareil), on peut cumuler les 2 :

Exemple 1 : récupérer les 4 derniers caractères des libellés des bières pour savoir s’il s’agit de 33 ou 75cl :

Exemple 2 : retirer « cl » pour convertir en nombre et pouvoir faire un calcul de prix de vente par litre par exemple : 

Renvoie la date du jour. S’écrit comme suit : =AUJOURDHUI()

Permet de faire des calculs de délais entre une date 1 dans votre tableur, et la date du jour par exemple (juste par soustraction, voir exemple ci-dessous).

Exemple 1 : mon relevé des heures à récupérer date du 31/03/2024, je veux calculer l’âge de ce relevé à l’ouverture du document, dans l’idée qu’au delà d’un certain nombre de jours, il devient désuet.

Permet de générer une date à partir d’une autre, en avançant / reculant de x mois 

Cette fonction amène le dernier jour du mois pour une date donnée. Là encore, on peut s’en servir pour calculer des délais entre 2 dates (exemple : aujourd’hui et la fin du mois), ou mettre des échéances (ex. la facture doit être payée avant la fin du mois) associées à des alerteurs (par exemple visuels, via la mise en forme conditionnelle) lorsque l’échéance est dépassée.

Exemple 1 : ramener le dernier jour du mois en cours : 

Exemple 2 : ramener le dernier jour du mois de « dans 8 mois » (qui serait par exemple la date limite pour récupérer) :

Permet de classer des résultats numériques (croissant ou décroissant). Pour faire par exemple un top ou flop articles ou autre.