NAVIGATION SCOLAIRE
- Pourquoi avez-vous besoin de formules et de fonctions?
- Définir et créer une formule
- Référence relative et absolue des cellules et formatage
- Fonctions utiles à connaître
- Recherches, graphiques, statistiques et tableaux croisés dynamiques
Ces fonctions sont importantes pour les entreprises, les étudiants et ceux qui souhaitent simplement en savoir plus.
VLOOKUP et HLOOKUP
Voici un exemple pour illustrer les fonctions de recherche verticale (VLOOKUP) et horizontale (HLOOKUP). Ces fonctions permettent de traduire un nombre ou une autre valeur en quelque chose de compréhensible. Par exemple, vous pouvez utiliser VLOOKUP pour prendre un numéro de pièce et renvoyer la description de l'article.
Pour en savoir plus, revenons à notre feuille de calcul «Décideur», dans la quatrième partie, où Jane tente de décider quoi porter à l’école. Elle ne s’intéresse plus à ce qu’elle porte, puisqu’elle a atterri un nouveau petit ami, elle va donc porter des tenues et des chaussures au hasard.
Dans le tableur de Jane, elle répertorie les vêtements en colonnes verticales et les chaussures, colonnes horizontales.
Elle utilise la fonction RANDBETWEEN (1,5) pour choisir parmi cinq types de chaussures.
Comme Jane ne peut pas porter un numéro, nous devons le convertir en un nom. Nous utilisons donc des fonctions de recherche.
Nous utilisons la fonction VLOOKUP pour traduire le numéro de la tenue en nom de la tenue. HLOOKUP traduit le numéro de la chaussure en différents types de chaussures dans la rangée.
Le tableur fonctionne comme ceci pour les tenues:
Ensuite, la formule traduit le nombre en texte à l'aide de = VLOOKUP (B11, A2: B4,2), qui utilise un nombre aléatoire de la valeur de B11 dans la plage A2: B4. Il donne ensuite le résultat (C11) à partir des données répertoriées dans la deuxième colonne.
Nous utilisons la même technique pour choisir les chaussures, sauf que cette fois-ci, nous utilisons VOOKUP au lieu de HLOOKUP.
Exemple: statistiques de base
Presque tout le monde connaît une formule de statistiques - moyenne - mais il existe une autre statistique importante pour les entreprises: l'écart type.
Par exemple, beaucoup de personnes qui sont allées à l'université ont souffert de leur score au SAT. Ils voudront peut-être savoir comment ils se classent par rapport aux autres étudiants. Les universités veulent également le savoir, car de nombreuses universités, en particulier des universités prestigieuses, refusent les étudiants ayant un score SAT faible.
Alors, comment pourrions-nous, ou une université, mesurer et interpréter les scores SAT? Vous trouverez ci-dessous les scores SAT pour cinq étudiants allant de 1 870 à 2 230.
Moyenne - La moyenne est également appelée «moyenne».
Déviation standard (STD ou σ) - Ce nombre montre à quel point un ensemble de nombres est très dispersé. Si l'écart type est grand, les nombres sont très éloignés et s'il est nul, tous les chiffres sont identiques. On pourrait dire que l’écart-type est la différence moyenne entre la valeur moyenne et la valeur observée, c’est-à-dire 1 998 et chaque score SAT. Veuillez noter qu'il est courant d'abréviation de l'écart type à l'aide du symbole grec sigma «σ».
Rang centile - Lorsqu'un étudiant obtient un score élevé, il peut se vanter de figurer dans les 99 premiers centiles ou quelque chose du genre. «Rang en centile» signifie que le pourcentage de scores est inférieur à un score particulier.
L'écart-type et la probabilité sont étroitement liés. Vous pouvez dire que pour chaque écart-type, la probabilité que ce nombre se situe à l'intérieur de ce nombre est de:
| MST | Pourcentage des scores | Gamme de scores SAT |
| 1 | 68% | 1,854-2,142 |
| 2 | 95% | 1,711-2,285 |
| 3 | 99.73% | 1,567-2,429 |
| 4 | 99.994% | 1,424-2,572 |
Comme vous pouvez le constater, la probabilité que tout score SAT se situe en dehors de 3 STD est pratiquement nulle, car 99,73% des scores se situent à moins de 3 STD.
Maintenant regardons à nouveau la feuille de calcul et expliquons-en le fonctionnement.
= MOYENNE (B2: B6)
= STDEV.P (B2: B6)
= PERCENTRANK.EXC ($ B $ 2: $ B $ 6 $, B2)
Représentation graphique des résultats
Le fait de mettre les résultats dans un graphique facilite la compréhension des résultats. Vous pouvez également l'afficher dans une présentation pour mieux faire comprendre votre point de vue.
Le classement en centiles correspond à l'axe vertical de droite, compris entre 0 et 90%. Il est représenté par la ligne grise.
Comment créer un graphique
La création d’un graphique est un sujet en soi, mais nous expliquerons brièvement comment le graphique ci-dessus a été créé.
Commencez par sélectionner la plage de cellules devant figurer dans le graphique. Dans ce cas, A2 à C6 car nous voulons les chiffres ainsi que les noms de l’étudiant.
La fonction «Graphiques recommandés» vous évite généralement de vous occuper de détails aussi complexes que déterminer les données à inclure, comment attribuer des étiquettes et définir les axes verticaux gauche et droit.
Dans la boîte de dialogue «Sélectionner la source de données», cliquez sur «score» sous «Entrées de légende (série)», puis appuyez sur «Modifier» et modifiez-le pour indiquer «Score».
Exemple: le problème du transport
Le problème du transport est un exemple classique d'un type de mathématique appelé «programmation linéaire». Cela vous permet de maximiser ou de minimiser une valeur soumise à certaines contraintes. Il a de nombreuses applications pour un large éventail de problèmes d’entreprise, il est donc utile d’apprendre comment cela fonctionne.
Avant de commencer avec cet exemple, nous devons activer le «Solveur Excel».
Activer le complément du solveur
Sélectionnez «Fichier» -> «Options» -> «Compléments». Au bas des options des compléments, cliquez sur le bouton «Aller» à côté de «Gérer: Compléments Excel».
Exemple: calculez les coûts d'expédition iPad les plus bas
Supposons que nous expédions des iPads et que nous essayons de remplir nos centres de distribution en utilisant les coûts de transport les plus bas possibles. Nous avons passé un accord avec une entreprise de camionnage et de transport aérien afin d’envoyer des iPad de Shanghai, Beijing et Hong Kong aux centres de distribution indiqués ci-dessous.
Le prix d’expédition de chaque iPad correspond à la distance entre l’usine et le centre de distribution, divisée par 20 000 kilomètres. Par exemple, il faut parcourir 8 024 km entre Shanghai et Melbourne, ce qui représente 8 024/20 000 ou 0,40 USD par iPad.
Comme vous pouvez l'imaginer, il pourrait être très difficile de comprendre cela sans une formule et un outil. Dans ce cas, nous devons expédier un total de 462 000 (F12) iPad. Les usines ont une capacité limitée de 500 250 unités (G12).
SOMMAIRE multiplie les «coûts» par la plage «livrée» (B14). C'est ce qu'on appelle la «multiplication matricielle».
Utiliser le solveur
Si tout ce que nous devions faire, c’était multiplier les matrices «coûts» multipliées par «expédiées», ce qui ne serait pas trop compliqué, mais nous devons également y faire face.
Nous devons expédier ce que chaque centre de distribution exige. Nous mettons cette constante dans le solveur comme ceci: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. Cela signifie que la somme de ce qui est expédié, c’est-à-dire que les totaux des cellules $ B $ 12: 12 $ E, doit être supérieure ou égale à ce que chaque centre de distribution exige (13 $ B: 13 $ E).
Tapez les deux contraintes détaillées précédemment et sélectionnez la plage «Envois», qui correspond à la plage de nombres que nous souhaitons calculer par Excel. Choisissez également l’algorithme par défaut «Simplex LP» et indiquez que vous souhaitez «minimiser» la cellule B15 («frais d’expédition totaux»), où il est indiqué «Définir l’objectif».
Si l'ordinateur vous dit qu'il ne peut pas trouver de solution, vous avez alors fait quelque chose de pas logique, par exemple, vous avez peut-être demandé plus d'iPad que les plantes ne peuvent en produire.
Ici, Excel dit qu’il a trouvé une solution. Appuyez sur «OK» pour conserver la solution et revenir à la feuille de calcul.
Exemple: valeur actuelle nette
Comment une entreprise décide-t-elle d'investir ou non dans un nouveau projet? Si la «valeur actuelle nette» (VAN) est positive, ils y investiront. C'est une approche standard adoptée par la plupart des analystes financiers.
Par exemple, supposons que la société minière Codelco souhaite agrandir la mine de cuivre d’Andinas. L'approche standard pour déterminer si un projet doit aller de l'avant consiste à calculer la valeur actuelle nette. Si la VAN est supérieure à zéro, le projet sera rentable compte tenu de deux entrées (1) temporelles et (2) du coût du capital.
En clair, le coût du capital signifie combien cet argent gagnerait s'il le laissait juste à la banque. Vous utilisez le coût du capital pour actualiser les valeurs de rachat par rapport à la valeur actuelle, ce qui signifie que 100 $ dans cinq ans pourraient être de 80 $ aujourd'hui.
Au cours de la première année, 45 millions de dollars ont été mis de côté pour financer le projet. Les comptables ont déterminé que leur coût du capital est de six pour cent.
Après 13 ans, la valeur actualisée nette est de 3 945 074 USD. Le projet sera donc rentable. Selon les analystes financiers, la «période de remboursement» est de 13 ans.
Création d'un tableau croisé dynamique
Un «tableau croisé dynamique» est essentiellement un rapport. Nous les appelons tableaux croisés dynamiques car vous pouvez facilement les basculer d'un type de rapport à un autre sans avoir à créer un tout nouveau rapport. Afin qu'ils pivot en place. Montrons un exemple de base qui enseigne les concepts de base.
Exemple: Rapports de vente
Les vendeurs sont très compétitifs (cela fait partie du métier de vendeurs). Ils veulent donc naturellement savoir comment ils se comparent les uns aux autres à la fin du trimestre et de la fin de l’année, ainsi que le montant de leurs commissions.
Supposons que nous ayons trois vendeurs - Carlos, Fred et Julie - vendant tous du pétrole. Leurs ventes en dollars par trimestre fiscal pour l'année 2014 sont présentées dans le tableur ci-dessous.
Sélectionnez “Insérer -> Tableau croisé dynamique, il se trouve sur le côté gauche de la barre d’outils:
Si nous cliquons sur les quatre champs de la boîte de dialogue du tableau croisé dynamique (trimestre, année, ventes et vendeur), Excel ajoute un rapport à la feuille de calcul qui n’a aucun sens, mais pourquoi?
Ici, il nous donne la somme de l'année 2014 + 2014 + 2014 + 2014 = 24.168, ce qui est un non-sens. La somme des trimestres 1 + 2 + 3 + 4 = 10 * 3 = 3 0 est également indiquée. Nous n’avons pas besoin de cette information, nous avons donc désélectionné ces champs pour les supprimer de notre tableau croisé dynamique.
Exemple: Ventes par vendeur
Vous pouvez éditer «Somme des ventes» pour dire «Total des ventes», ce qui est plus clair. En outre, vous pouvez formater les cellules en tant que devise, comme vous le feriez pour toute autre cellule. Cliquez d'abord sur «Somme des ventes» et sélectionnez «Paramètres du champ de valeur».
Dans la boîte de dialogue résultante, nous modifions le nom en «Ventes totales», puis cliquez sur «Format de nombre» et en le changeant en «Devise».
Exemple: Ventes par vendeur et par trimestre
Ajoutons maintenant des sous-totaux pour chaque trimestre. Pour ajouter des sous-totaux, il suffit de cliquer avec le bouton gauche de la souris sur le champ «Trimestre», de le maintenir enfoncé et de le faire glisser vers la section «Lignes». Vous pouvez voir le résultat sur la capture d'écran ci-dessous:
Conclusion
En conclusion, nous vous avons montré certaines des fonctionnalités des formules de Microsoft Excel et des fonctions que vous pouvez appliquer à vos besoins professionnels, académiques ou autres.
Comme vous l'avez vu, Microsoft Excel est un énorme produit doté de nombreuses fonctionnalités que la plupart des gens, même les utilisateurs avancés, ne connaissent pas tous. Certaines personnes pourraient dire que cela complique les choses; nous pensons que c’est plus complet.
Espérons que, en vous présentant de nombreux exemples concrets, nous avons non seulement démontré les fonctions disponibles dans Microsoft Excel, mais également enseigné des notions de statistiques, de programmation linéaire, de création de graphiques, d’utilisation de nombres aléatoires et d’autres idées que vous pouvez maintenant adopter. utiliser dans votre école ou où vous travaillez.
N'oubliez pas que si vous souhaitez revenir en arrière et reprendre le cours, vous pouvez recommencer à zéro avec la leçon 1!
