9 nouvelles formules Excel à connaître en 2024 pour doubler votre productivité !

Quoi ? Des nouvelles formules Excel pour 2024 ?

Oui [et non. . . ]

Pour être 100% transparent, ces nouvelles formules ont été intégrées dans Excel en 2023. Mais très peu de personnes ne le savent, et encore moins vont les utiliser en 2024.

C’est pour ça que je vous en parle, car en connaissant ces nouvelles formules Excel et en les utilisant judicieusement vous pourrez améliorer vos tableaux et votre maîtrise d’Excel.

Si vous souhaitez découvrir ces nouvelles fonctions Excel en vidéo, vous pouvez cliquer sur l’image ci-dessous pour découvrir ma vidéo sur le sujet 😉

Allez, passons directement au vif du sujet !

1. =TRIERPAR

TRIERPAR fait partie des nouvelles formules Excel. Elle permet de réaliser un tri de tableau directement avec une formule, en spécifiant des critères de tri :

=TRIERPAR(tableau; par_tableau1; [ordre_tri1]; …)

Voici les paramètres de cette fonction : (pour rappel, les arguments entre [ ] sont facultatif, la formule fonctionne aussi sans)

• tableau = sélectionnez la matrice ou la plage de cellule que vous souhaitez trier.
• par_tableau1 = définissez la plage ou matrice selon laquelle vous souhaitez réaliser votre tri.
• [ordre_trie1] = précisez l’ordre avec lequel vous souhaitez réaliser votre tir :
  • 1 = trier dans l’ordre croissant
  • -1 = trier dans l’ordre décroissant
• . . . = vous pouvez répéter autant de fois que nécessaire pour d’autres tableaux avec leur ordre de tri.

Exemple d’application :

AVANT	APRES

2. =DANSCOL ou = DANSLIGNE

Ces deux nouvelles formules Excel permettent de regrouper les données d’une plage dans une seule colonne (pour =DANSCOL) ou dans une seule ligne (pour =DANSLIGNE) :

=DANSCOL(array; [ignore]; [scan_by_column])
=DANSLIGNE(array; [ignore]; [scan_by_column])

=DANSCOL	=DANSLIGNE

Voici les paramètres de cette fonction : (pour rappel, les arguments entre [ ] sont facultatif, la formule fonctionne aussi sans)

• array = matrice = correspond à la plage de données ou matrice que vous souhaitez regrouper dans une seule colonne ou ligne.
• [ignore] = permet de définir certain filtres :
  • 0 = critère par défaut, permet de conserver toutes les valeurs de la plage
  • 1 = ignorer les espaces
  • 2 = ignorer les erreurs
  • 3 = ignorer les espaces et les erreurs
• [scan_by_column] = permet d’inverser le traitement des données de la plage. Par défaut, l’importation des données se fait via un scan par ligne, et en activant ce paramètre, la numérisation des données se fera désormais par colonne.

Exemple d’application :

Exemple avec la formule =DANSCOL()

AVANT	APRES

3. =ASSEMBL.V ou =ASSEMBL.H

Comme leur nom le précise, ces deux nouvelles formules Excel permettent d’assembler différentes plages en les cumulant les unes après les autres, dans le sens vertical pour ASSEMBL.V et dans le sens horizontal pour ASSEMBL.H :

=ASSEMB.V(array1; [array2]; …)
=ASSEMB.H(array1; [array2]; …)

=ASSEMBL..V	=ASSEMBL.H

Voici les paramètres de cette fonction :

Cette fonction est uniquement composée de paramètres “array” ou “matrice” en français, qui correspondent aux plages que vous souhaitez assembler. Il vous suffira de sélectionner les plages en les séparant à chaque fois pas un “;” .

Exemple d’application :

AVANT	APRES

4. =TEXTE.AVANT ou =TEXTE.APRES

Ces deux nouvelles formules Excel sont utiles pour extraire du texte ou une chaine de caractères dans une cellule. La première, TEXTE.AVANT, vous permet de récupérer tout le texte présent avant un ou plusieurs caractères ou symboles que vous précisez. Le seconde, TEXTE.APRES fait de même, mais en faisant l’extraction du texte après la chaine spécifiée dans la formule :

=TEXTE.AVANT(texte; délimiteur; [instance_num]; [match_mode]; [match_end]; [if_not_found])
=TEXTE.APRES(texte; délimiteur; [instance_num]; [match_mode]; [match_end]; [if_not_found])

=TEXTE.AVANT	=TEXTE.APRES

Voici les paramètres de cette fonction :

• texte = correspond au texte dans lequel vous voulez extraire les données, ou directement la référence d’une cellule contenant du texte.
• délimiteur = texte qui délimite la zone à partie de laquelle vous souhaitez extraire le texte, tout ce qui est avant ou après sera alors ressorti en résultat.
• [instance_num] = valeur numérique à renseigner pour informer à partir de quel caractère vous souhaitez extraire le texte. Un nombre négatif permet de commencer la recherche à partir de la fin du texte. (par défaut, instance_num = 1)
• [match_mode] = permet de préciser si vous souhaitez conserver la casse (majuscule/minuscule) dans la recherche de votre texte. Par défaut, la valeur est 0 qui correspond à conserver la casse, si vous renseignez 1 vous souhaitez une recherche qui ne respecte pas la casse.
• [match_end] = [fin_correspondance] = traite la fin du texte comme un délimiteur. Par défaut la valeur est 1 et le texte correspond exactement, renseignez 0 si vous ne souhaitez pas faire correspondre le délimiteur à la fin du texte.
• [if_no_found] = [si_non_trouvé] = renseignez la valeur que vous souhaitez renvoyer si la valeur de recherche n’est pas trouvée. Par défaut c’est l’erreur #N/A qui sera renvoyée

Exemple d’application :

Exemple : =TEXTE.AVANT

Exemple : =TEXTE.APRES

Résultats :

5. =FRACTIONNERTEXTE

Cette nouvelle formule permet de fractionner le texte que vous désirez en précisant les paramètres de fractionnement que vous souhaitez :

=FRACTIONNER.TEXTE(texte; col_delimiter; [row.delimiter]; [ignore_empty]; [match_mode]; [pad_with])

Voici les paramètres de cette fonction :

• texte = texte ou contenu texte de la cellule que vous souhaitez fractionner.
• col_delimiteur = texte qui délimite la zone à partie de laquelle vous souhaitez fractionner le texte, tout ce qui est après sera reporté dans les cellules/colonnes à droite, dans la même ligne.
• [row_delimiteur] = texte qui délimite la zone à partie de laquelle vous souhaitez fractionner le texte, tout ce qui est après sera reporté dans les lignes/cellules en dessus, dans la même colonne.
• [ignore_empty] = permet d’ignorer ou non les délimiteurs consécutifs. La valeur par défaut est FAUX, qui prend en compte les délimiteurs consécutifs. Renseignez VRAI si vous souhaitez ignorer les délimiteurs consécutifs.
• [match_mode] = permet de préciser si vous souhaitez conserver la casse (majuscule/minuscule) dans la recherche de votre texte. Par défaut, la valeur est 0 qui correspond à conserver la casse, si vous renseignez 1 vous souhaitez une recherche qui ne respecte pas la casse.
• [pad_with] = renseignez la valeur que vous souhaitez renvoyer si la valeur de recherche n’est pas trouvée. Par défaut c’est l’erreur #N/A qui sera renvoyée.

Exemple d’application :

Exemple =FRACTIONNER.TEXTE en colonne

AVANT	APRES

Exemple =FRACTIONNER.TEXTE en ligne

AVANT	APRES

6. =RECHERCHEX (remplace =RECHERCHEV)

Parmi les nouvelles formules Excel, on retrouve la fonction RECHERCHEX. C’est une des fonctions les plus importantes d’Excel. Elle remplace la fonction RECHERCHEV ou RECHERCHEX et permet de faire le même travail, mais beaucoup plus simplement en limitant le risque d’erreur :

=RECHERCHEX(valeur_cherchée; tableau_recherche; tableau_renvoyé; [si_non_trouvé]; [mode_correspondance]; [mode recherche])

Voici les paramètres de cette fonction :

• valeur_cherchée = valeur à rechercher, valeur de recherche, valeur cible : correspond à l’élément que vous cherchez pour laquelle vous souhaitez retrouver le résultat.
• tableau_recherche = tableau de recherche, plage de cellule de recherche : correspond au terrain de chasse où Excel doit fouiller. Contrairement aux fonctions RECHERCHEV ou RECHERCHEH, vous pouvez spécifier uniquement la colonne où se trouve la valeur cherchée.
• tableau_renvoyé = tableau ou plage à renvoyer : correspond à la zone dont vous souhaitez renvoyer le résultat.
• [si_non_trouvé] = renseignez la valeur que vous souhaitez renvoyer si la valeur de recherche n’est pas trouvée. Par défaut c’est l’erreur #N/A qui sera renvoyée.
• [mode_correspondance] = permet de préciser le mode mode de correspondance souhaité :
  • 0 = correspondance exacte = valeur par défaut qui permet de spécifier que vous recherchez exactement la mention précisée en valeur_cherché. si aucun élément n’est trouvé, la valeur #N/A est renvoyée.
  • 1 = correspondance semi-exacte = réalise la même recherche que pour 0, mais si aucun n’est trouvé alors la formule renverra l’élément suivant le plus grand.
  • -1 = correspondance semi-exacte = réalise la même recherche que pour 0, mais si aucun n’est trouvé alors la formule renverra l’élément suivant le plus petit.
  • 2 = correspondance générique ou les caractères ? , * et ~ permette d’agrandir la recherche :
    • ? = permet de remplacer par n’importe quel caractère dans votre recherche. Par exemple : la recherche “?ugir” renverra “rugir” et “mugir”.
    • * = permet de remplacer par n’importe quel nombre de caractères dans votre recherche. Par exemple : la recherche “*Ouest” renverra “Nord-Ouest” et “Sud-Ouest”.
    • ~ = permet de préciser une recherche pour prendre en compte le ? ou l’astérisque *. Par exemple : la rehcerche “Age~ ?” renverra “Age ?”.
• [mode_recherche] = permet de préciser le mode de recherche à utiliser :
  • 1 = (valeur par défaut) permet d’effectuer une recherche en commentant par le premier élément.
  • -1 = effectuer une recherche inversée en commentant par la fin.
  • 2 = effectuer une recherche binaire qui repose sur le tri par préalable par ordre croissant de la plage précisé en tableau_recherche. Il renverra la première valeur trouvée dans cet ordre de tri.
  • -2 = effectuer une recherche binaire qui repose sur le tri par préalable par ordre décroissant de la plage précisé en tableau_recherche. Il renverra la première valeur trouvée dans cet ordre de tri.

Pour plus de détail pour bien comprendre et maîtriser cette fonction indispensable, je vous invite à lire l’article dédié à la fonction RECHERCHEX (à venir)

Exemple d’application :

AVANT	APRES

7. =IMAGE

La fonction IMAGE vous permet d’importer et d’intégrer des images dans votre fichier Excel directement grâce à une formule :

=IMAGE(source; [alt_texte]; [dimensionnement]; [hauteur]; [largeur])

Voici les paramètres de cette fonction :

• array = tableau = tableau ou plage de cellule à partir duquel vous souhaitez extraire des lignes ou de colonnes.
• rows = ligne = renseignez le nombre de lignes que vous souhaitez extraire. Une valeur négative vous permet de commencer la récupération par la fin du tableau précisé.
• [columns] = [colonnes] = renseignez le nombre de colonnes que vous souhaitez extraire. Une valeur négative vous permet de commencer la récupération par la fin du tableau (array) précisé. Vous devez laisser le champ rows vide.

Exemple d’application :

Exemple avec source brute	Exemple avec source dans une cellule

Résultats :

8. =PRENDRE

Cette nouvelle fonction permet de récupérer un tableau ou partie précise d’un tableau en y faisant référence directement :

=PRENDRE(array; row; [columns])

Voici les paramètres de cette fonction :

• source = adresse URL ou chemin d’accès à l’URL de l’image que vous souhaitez importer, à intégrer entre guillemets ” “.
• [alt_text] = permet de préciser le texte de remplacement qui décrit l’image pour l’accessibilité, à intégrer entre guillemets ” “.
• [dimensionnement] = spécifier les dimensions que vous souhaitez pour l’image :
  • 0 = ajuster l’image dans la cellule et conserver ses proportions
  • 1 = remplir la cellule avec l’image et ignorer ses proportions d’origine
  • 2 = conserver la taille d’origine de l’image, qui peut déborder de la taille de la cellule
  • 3 = personnaliser la taille de l’image à l’aide des arguments suivants (hauteur et largeur)
• [hauteur] = précisez en nombre de pixels la hauteur souhaitée pour l’image.
• [largeur] = précisez en nombre de pixels la largeur souhaitée pour l’image.

Exemple d’application :

AVANT	APRES

9. =ETENDRE

Cette formule permet de récupérer une plage de données ou un tableau et d’étendre la sélection pour prendre en compte les données à venir, qui pourraient êtres saisies dans un future proche :

=ETENDRE(array; row; [columns]; [pad_with])

Voici les paramètres de cette fonction :

• array = tableau = tableau ou plage de cellule que vous souhaitez étendre.
• rows = ligne = renseignez le nombre de lignes que vous souhaitez développer.
• [columns] = [colonnes] = renseignez le nombre de colonnes que vous souhaitez développer.
• [pad_with] = renseignez la valeur que vous souhaitez renvoyer si la valeur de recherche n’est pas trouvée. Par défaut c’est l’erreur #N/A qui sera renvoyée.

Exemple d’application :

AVANT	APRES

10. Conclusion

Je vous ai présenté les 9 nouvelles formules Excel à connaître en 2024 pour développer vos compétences et booster votre carrière.

Si vous souhaitez voir plus en détail comment je mets en application ces formules, je détaille pas à pas chaque exemple de cet article dans ma vidéo sur les 9 nouvelles formules Excel pour 2024.

Pas besoin d’en dire plus, maintenant c’est à vous de vous familiariser avec ces nouvelles fonctions Excel et de les utiliser.

Bonne application !

11. Ressources supplémentaires

Pour compléter vos compétences Excel autour du sujet de cet article, vous pouvez consulter les articles suivants :

• Comprendre la fonction RECHERCHEV dans Excel : tuto simple
• La fonction RECHERCHEX (à venir)
• Fonction SI Excel : guide complet pour la maîtriser