Excel est une tante stricte. D'une part, c'est un outil indispensable pour créer des rapports, des listes et des analyses. D'autre part, vous ne pouvez extraire les informations que vous souhaitez de la feuille de calcul que si vous maîtrisez le langage Excel typique. Ces formules Excel associent toutes sortes de relations aux cellules pour renvoyer des informations ciblées. Voici 15 fonctionnalités qui peuvent vous faire gagner du temps.
Manuel ou assistant de formule?
Nous supposons qu'entre-temps vous maîtrisez les formules de base pour appliquer les opérations principales. Sans tomber dans le hocus pocus des spécialistes, nous montrons à quel point des formules utiles sont assemblées. Vous pouvez les saisir manuellement, mais vous pouvez également utiliser le bouton fx dans la barre de formule: l'assistant de formule. Il vous prendra par la main pour construire la formule étape par étape.
01 Heure actuelle
Êtes-vous quelqu'un qui oublie régulièrement de dater correctement son travail? La formule AUJOURD'HUI remplit automatiquement le jour, le mois et l'année, tandis que la fonction MAINTENANT ajoute même l'heure à la minute. Vous tapez ensuite = AUJOURD'HUI () ou = MAINTENANT () . Ces fonctions sont également utiles dans une feuille de calcul où vous souhaitez calculer une valeur basée sur le jour et l'heure actuels. Avec un clic droit et le choix des propriétés de la cellule, vous pouvez ensuite ajuster l'affichage de la date et de l'heure. Pour mettre à jour ces informations de temps dans la feuille de calcul active, appuyez sur Maj + F9; utilisez F9 pour mettre à jour tout le classeur.
02 Compter les cellules remplies
Si vous avez un groupe de cellules contenant à la fois du texte et des nombres et que vous voulez savoir combien de nombres il y a dans une sélection, utilisez la fonction COUNT . La structure de la formule ressemble alors à ceci: = COUNT (zone de recherche) . La zone dans laquelle Excel doit rechercher apparaît entre les crochets. Il peut s'agir de cellules situées en dessous ou l'une à côté de l'autre, mais il peut également s'agir d'une sélection rectangulaire de cellules. S'il y a des mots dans la sélection, ceux avec la fonction COUNT ne sont pas comptés. Si vous souhaitez simplement compter toutes les cellules où quelque chose est écrit, utilisez la fonction = COUNTA (sans point).
03 À quelle fréquence?
Utilisez la fonction COUNTIF pour compter des données spécifiques . Supposons que vous ayez dessiné une grille dans laquelle quatre personnes apparaissent, alors vous pouvez voir à quelle fréquence le nom Herman apparaît avec = NANTAL.IF (zone de recherche; «Herman») . Entrez la plage de recherche entre les crochets et placez les critères de recherche entre guillemets.
04 Ajout sélectif
La fonction SUM pour additionner les cellules est largement utilisée. Une variante plus intelligente est SOM.IF () . Spécifiez d'abord la zone dans laquelle Excel doit rechercher entre les crochets. La plage de recherche doit être une série de cellules contiguës. Après le point-virgule, vous déterminez ce qui doit être ajouté. Ceux-ci peuvent être des nombres ou une référence. S'il s'agit d'une équation, vous devez la mettre entre guillemets. Par exemple, = SUMIF (B20: B40, "> 50") fait la somme de toutes les cellules de cette plage qui sont supérieures à 50.
05 Ajout sous condition
Vous pouvez étendre encore plus la condition d'ajout en utilisant les informations d'une autre colonne. Un exemple le rend clair. Supposons que vous ayez des chiffres relatifs à trois villes: Amsterdam, Rotterdam et Eindhoven. Ensuite, vous ne pouvez ajouter les nombres d'Amsterdam qu'avec = SUMIF (intervalle; «Amsterdam»; intervalle d'addition) . Dans ce cas, la formule devient = SUMIF (C48: C54; «Amsterdam»; B48: B54) . En langage clair: lorsque le mot Amsterdam est dans la plage C48 à C54, Excel doit additionner la valeur correspondante de la cellule à côté dans la plage B48 à B54.
06 Fusionner
Avec la fonction CONCATENER vous fusionnez les données de différentes cellules. Par exemple, les cellules des noms et prénoms avec quelque chose comme = CONCATENER TEXTE (E34, ”“; F34) . Les guillemets doubles avec l'espace garantissent qu'il y a un espace entre le prénom et le nom. De la même manière, il est possible de fusionner le texte avec la devise. Par exemple, pour ajouter la devise euro, vous devez la saisir sous la forme d'une fonction telle que = CONCATENER TEXTE (A1, ”“; B1; ”“ EURO (C1)) . Vous pouvez lire ceci comme «fusionner les cellules A1, B1 et C1 avec des espaces entre elles et placer le signe euro avant le troisième élément de la fusion».
07 Conclusion
Excel propose plusieurs options d'arrondi. L'arrondi par défaut ressemble à = ROUND (nombre, nombre de décimales) . Ainsi, la formule = ROUND (12,5624, 1) renvoie 12,6 . Après tout, vous demandez d'arrondir à un nombre après la virgule décimale. Excel arrondira également au nombre de décimales que vous spécifiez avec les fonctions ROUND.TO.ABOVEN et ROUND.BOTH . = ROUNDUP (12,5624; 2) donne donc le résultat 12,57 et = ROUNDDOWN (12,5624; 2), ce qui donne 12,56 . La fonction INTEGER est en fait aussi une fonction d'arrondi, mais avec cela Excel arrondit au nombre entier le plus proche.
08 Lettres majuscules - lettres minuscules
Pour vous assurer que tout apparaît en majuscules dans une colonne, utilisez la fonction MAJUSCULES . La formule SMALL.LETTERS fait le contraire. Et si vous voulez que chaque mot commence par une majuscule suivie de minuscules, vous utilisez la fonction LETTRES INITIALES . La formule = PETITES LETTRES (B4) affiche le contenu de la cellule B4, mais en minuscules.
09 Sous condition
Lorsqu'un calcul dépend de certaines conditions, vous utilisez la fonction IF . Le principe de cette fonction est: = IF (condition, calcul si la condition est remplie, autres cas) . Pour formuler la condition, utilisez les signes: = égal à,différent de, > supérieur à, < inférieur à, > = supérieur ou égal à, < = inférieur ou égal à. Supposons que dans une organisation, tout le monde reçoive un bonus qui s'est vendu pour 25 000 euros ou plus. Si vous recevez un bonus, le mot «Hourra» apparaîtra automatiquement à côté de son nom, sinon, le mot «Malheureusement» apparaîtra. La formule dont vous avez besoin est = IF (B2> = 2500; "Hourra"; "Malheureusement") .
10 plus grand - plus petit
Pour trouver rapidement la valeur la plus élevée et la plus basse, il existe les fonctions MAX et MIN . Avec = MAX (B2: B37), vous obtenez la valeur la plus élevée de ces cellules, et avec = MIN (B2: B37), vous obtenez la valeur la plus basse de la plage. Les fonctions PLUS IMPORTANTS et MOINDRES sont plus subtiles: vous pouvez également demander au troisième ou deuxième plus petit, par exemple. Le plus grand peut être trouvé avec = LARGE (B2: B37; 1); le chiffre 1 indique le plus grand. Avec = LARGE (B2: B37,2), vous obtenez le deuxième plus grand et ainsi de suite. De cette façon, vous pouvez facilement compiler un top 3 ou un top 10.
11 Recherche verticale
Supposons que vous ayez deux feuilles de calcul contenant des informations différentes sur les mêmes personnes. Avec RECHERCHEV, vous récupérez les informations de la feuille de travail 2 de la feuille de travail 1. Pour faciliter cela, nous avons attribué à chaque personne un numéro d'enregistrement unique sur les deux onglets. Donnez également un nom à la plage de l'onglet 2 à partir de laquelle vous souhaitez obtenir des informations. Dans cet exemple, dans la feuille de calcul 2, nous sélectionnons les colonnes A et B et dans la zone de nom en haut à gauche, nous tapons le nom Liste d'adresses . Dans la cellule E2 de la feuille de calcul 1, nous plaçons la fonction RECHERCHEV . Le format est maintenant = RECHERCHEV (A2, Liste d'adresses, 2, FALSE) . A2 fait référence à la cellule avec le numéro d'inscription dans la deuxième feuille de calcul, Liste d'adressesindique la plage de recherche, 2 est le numéro de la colonne dans la feuille de calcul 2 où se trouvent les données demandées. Le dernier argument est une valeur logique dans laquelle vous entrez FALSE si vous voulez que la valeur trouvée corresponde exactement.
12 Supprimer les espaces
Utilisez la fonction TRIM pour supprimer les espaces inutiles dans le texte. Cette fonction laisse quelques espaces entre les mots, mais supprimera les espaces avant ou après le mot. = TRIM (plage de cellules) est utile pour le texte importé d'un autre programme. Dans certaines versions d'Excel, cette fonction est appelée SPACES.CLEAR .
13 Échange
Vous pouvez transférer le contenu des colonnes en lignes ou vice versa avec la fonction TRANSPOSE . Sélectionnez d'abord les cellules dans lesquelles les informations doivent être placées. Assurez-vous de sélectionner autant de cellules que la série d'origine. Ici, nous avons tapé les années de la ligne 8 et les trimestres de la colonne A. Tapez ensuite la fonction = TRANSPOSE et ouvrez les parenthèses. Faites ensuite glisser sur les cellules que vous souhaitez permuter (ici des cellules B2 à E5). Fermez les crochets et appuyez maintenant sur la combinaison de touches Ctrl + Maj + Entrée. Cela créera une formule matricielle contenue entre accolades.
14 Remboursement mensuel
Si vous empruntez pour un achat, combien devez-vous rembourser par mois? Supposons que vous empruntez 25 000 euros ( B1 ) à 6% d'intérêt ( B2 ) pendant 5 ans ( B3 ). Nous montrons la formule dans l'assistant, mais vous pouvez également simplement taper. Avec Intérêts, vous placez B2 / 12 , car les intérêts se réfèrent à une année et vous voulez savoir combien vous payez chaque mois. Pour les versements en nombre, vous multipliez B3 par 12 , car vous devez convertir des années en mois. La boîte PV signifie Valeur actuelle , qui est de 25 000 euros. Cela donne la formule = BET (B2 / 12; B3 * 12; B1) ou =BET (6% / 12,5 * 12,25000) .
15 fausses figurines
Lors de l'expérimentation de formules, il est utile d'avoir de fausses données. La fonction SELECT BETWEEN génère des données aléatoires comprises entre une valeur la plus basse et la plus élevée spécifiée. La fonction = RANDBET (50, 150) produit des nombres entre 49 et 151.
