Fonctions utiles à connaître

Dans cette leçon, nous aborderons les catégories de fonctions - leur travail et divers exemples - et pour illustrer leur fonctionnement, nous allons vous montrer plusieurs exemples de feuilles de calcul. Nous vous encourageons à suivre en créant vos propres feuilles de calcul..

NAVIGATION SCOLAIRE

1. Pourquoi avez-vous besoin de formules et de fonctions?
2. Définir et créer une formule
3. Référence relative et absolue des cellules et formatage
4. Fonctions utiles à connaître
5. Recherches, graphiques, statistiques et tableaux croisés dynamiques

Il y a beaucoup d'autres fonctions, bien sûr, et ce ne sont en aucun cas les seules que vous devriez apprendre. Nous vous donnons juste un ensemble de fonctions vraiment utiles que vous devrez utiliser régulièrement. La prochaine leçon présentera quelques fonctions plus avancées que vous pourriez utiliser, mais un peu moins souvent.

Fonctions mathématiques et financières

Encore une fois, ce n’est pas une liste complète de fonctions mathématiques ou financières, mais un exemple parmi quelques-uns que vous devriez apprendre à connaître. Nous vous recommandons de les tester dans votre propre tableur..

Les fonctions	Objectif
SQRT	Racine carrée, par exemple, SQRT (4) = 2 car 2 * 2 = 4
PMT	Paiement du prêt
DEGRÉS	Utilisé par les ingénieurs, par exemple, pour convertir des degrés (par exemple 360 ​​°) en radians (par exemple 2π)
GCD	Trouve le plus grand commun diviseur entre deux nombres. Par exemple, GDC (5,15) = 5, car 5 est le plus grand nombre qui divise 15. 3 divise également 15, mais ce n'est pas le le plus grand diviseur ils ont en commun.
RAND (23)	Générez un nombre décimal aléatoire compris entre 0 et 1. Vous pouvez utiliser ceci par exemple pour choisir un gagnant du concours (voir l'exemple ci-dessous). RANDBETWEEN est plus facile à utiliser, car vous n’avez pas à convertir ce nombre décimal en nombre entier, par exemple, 3 est plus facile à utiliser que 0,3..

Exemple: gagnant aléatoire du concours

Voici un exemple d'utilisation de RANDBETWEEN (). Supposons que nous ayons huit employés au bureau et que le patron distribue un iPad via un tirage au sort..

N'utilisez pas de chapeau ni de morceaux de papier pour choisir un gagnant, utilisez des technologies de pointe telles qu'Excel.!

Faites une feuille de calcul comme celle ci-dessous:

Nous utilisons = RANDBETWEEN (1,8) dans la cellule B10 pour générer un nombre entier compris entre 1 et 8..

Le nom de chaque employé est compris dans la plage A2: A9 puisqu'il y a huit employés. Nous devons donc choisir un nombre aléatoire compris entre ou égal à 1 et 8. La personne dont le numéro est le plus élevé gagne l'iPad..

Vous connaissez la position de l'employé dans la liste - James correspond à 1, Mark à 2, etc. Utilisez la fonction INDEX pour obtenir le nom de l'employé afin d'attribuer le numéro généré aléatoirement au nom correspondant. Voici comment nous choisissons notre gagnant.

• A2: A9 est la gamme.
• B10 est le numéro de l'employé qui a gagné.

Dans cet exemple, 1 signifie que la première colonne de la plage est sélectionnée et = INDEX () est une fonction qui récupère la valeur d'une cellule (Jam.es = 1, il est donc notre gagnant).

Si vous souhaitez choisir un autre nom, par exemple si le gagnant doit être présent pour gagner, il vous suffit de modifier la feuille de calcul. Vous pouvez l'essayer vous-même, il vous suffit de changer la largeur d'une colonne ou d'ajouter des données. Chaque fois que la feuille de calcul est mise à jour, un nouveau nombre aléatoire est régénéré et un nouveau gagnant est sélectionné..

Fonctions logiques

Les fonctions logiques sont utilisées pour tester si quelque chose est vrai ou faux.

Une fonction	Objectif
SI	Si (, X, Y) Si est vrai, alors la valeur est X, sinon la valeur est Y.Par exemple cette formule = IF (C2 = 5, "C2 = 5", "C25") Affiche "C2 = 5" si C2 = 5, sinon il affichera “C25”
ET	= AND (C3 = 5, C4 = 5) Affiche «TRUE» si C3 = 5 et C4 = 5
FAUX	Définit la cellule sur false
VRAI	Définit la cellule sur true

Fonctions date et heure

Les dates sont stockées dans Excel sous forme de nombres, ce qui signifie que vous pouvez faire des calculs avec elles. Vous pouvez utiliser +1 pour calculer le jour suivant et vous pouvez soustraire une date à une autre pour trouver combien de jours se sont écoulés entre deux dates..

Formule	Résultat	Objectif
= MAINTENANT ()	16/09/2013	Obtenir la date et l'heure actuelles
= DATEVALUE («16/09/2013»)	41533	Convertir la date au format texte en nombre. Ce numéro s'appelle le «numéro de série». Le numéro 1 correspond au 1er janvier 1900. 41 533 soit 41 532 jours plus tard ou le 16/09/2013.
= WEEKDAY (NOW ())	2	Extrait la date sous forme de nombre. 1 est dimanche et 7 est samedi.
= MAINTENANT () + 1	17/09/2013	Comme les dates dans Excel sont stockées sous forme de nombres, vous pouvez faire des calculs avec elles.

Par exemple, Excel ne peut pas simplement ajouter = 9/16/2013 + 1. Il doit d'abord le convertir en un nombre qui lui convient. Dans la capture d'écran, vous voyez que nous devions d'abord affecter la fonction DATEVALUE () à la formule pour qu'Excel sache que la «valeur» du texte est une «date».

Exemple: quand prendre des vacances

Voici un exemple qui montre comment utiliser différentes fonctions de date.

Le patron dit que vous pouvez prendre des vacances en fonction de vos ventes. Si vous réalisez 1 million de dollars de ventes, vous obtenez deux semaines (10 jours ouvrables). Si vous réalisez 10 millions de dollars de ventes, vous disposez de trois semaines (15 jours ouvrables). Faire moins de 1 million de dollars de ventes et vous obtenez un nouvel emploi ailleurs.

Vous êtes un excellent vendeur. Vous envisagez donc de réaliser des ventes d'au moins 10 millions de dollars et de le faire rapidement. Vous estimez qu'il vous faudra cinquante jours pour atteindre votre quota. Vous voulez projeter quand vous pouvez partir en vacances, alors vous faites une feuille de calcul.

Pour trouver la date de vos vacances, vous ne pouvez pas simplement ajouter 50 jours au premier de l'année. Ce doit être 50 jours ouvrables - non compris les jours fériés. De plus, comme personne ne veut partir en vacances en milieu de semaine, vous devez trouver le samedi suivant, à moins que la date calculée ne soit déjà un samedi..

Comment faites-vous ce calcul? Regardons attentivement le tableur ci-dessous:

Si vous projetez 15 millions de dollars de chiffre d'affaires, entrez cette valeur dans la cellule B2. Vous estimez également qu’il faudra 50 jours de travail pour atteindre cet objectif, que vous entrez dans B3..

À partir de là, nous pouvons calculer la date du 50 janvier à compter du 1er janvier, puis afficher la date du samedi suivant - votre premier jour de vacances!

La formule = WORKDAY (B1, B3, A13: A20) ajoute B3 (50 jours de travail) à B1 (1er janvier) à l’exclusion des jours fériés figurant dans la plage A13: A20..

Ensuite, vous devez trouver le samedi suivant. C'est un peu délicat. La formule entière est = IF (WEEKDAY (B4) = 7,7,7-WEEKDAY (B4)) + B4.

Pour expliquer, rappelez-vous que vendredi et samedi sont les 6^th et 7^th jours de la semaine, respectivement. Le 14 mars, le premier jour où nous serons admissibles à partir en vacances est le 14 mars (cellule B4), qui est un vendredi (6)..

Nous prenons ensuite le nombre 7 (samedi) et soustrayons la valeur de B4.

Une fois que Excel détermine le nombre de jours de différence entre le jour de vos vacances projetées et le samedi suivant, il ajoute cette différence, dans ce cas 1, à la valeur indiquée en B4 et l'affiche en B5..

Une fois que vous avez décidé de partir en vacances, vous pouvez déterminer quand revenir. Si vos ventes sont supérieures ou égales à 1 000 000 $, vous disposez de 10 jours de vacances. S'ils sont supérieurs ou égaux à 10 000 000 $, vous avez 15 jours de vacances.

La formule que nous allons utiliser pour déterminer notre date de retour est = IF (B2> = A10, WORKDAY (B11, B5), WORKDAY (B11, B4)).

Ici, nous déterminons d’abord la valeur de B2 (ventes réalisées), puis nous la comparons à la valeur de A10 (10 000 000 $)..

Si la valeur est supérieure ou égale à 10 000 000 USD, la formule ajoutera 15 jours à la cellule B5 et imprimera le résultat dans B6, qui correspond à votre date de retour..

Sinon, la valeur sera ajoutée dans B9 (10 jours) et imprimée dans B6..

Nous nous moquons des vacances car nous serons en vacances, nous ne les incluons donc pas dans la fonction WORKDAY ().

Malheureusement, aussi utile soit-il, Excel ne peut toujours pas vous dire si vous voudrez même retourner au travail une fois vos vacances terminées.!

À suivre…

En conclusion, vous devriez avoir une bonne connaissance pratique des fonctions mathématiques et financières, des fonctions logiques et des fonctions de date et d’heure. Demain, dans notre dernière leçon 5 de ce cours, nous explorerons les fonctions de référence, le solveur, les fonctions financières et la création d'un tableau croisé dynamique..