Recherches, graphiques, statistiques et tableaux croisés dynamiques

Après avoir examiné les fonctions de base, les références de cellule et les fonctions de date et d’heure, nous passons maintenant à certaines des fonctionnalités les plus avancées de Microsoft Excel. Nous présentons des méthodes pour résoudre des problèmes classiques en matière de finance, de rapports de ventes, de coûts d’expédition et de statistiques..

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

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 étudier cela, revenons à notre feuille de calcul «Décideur» de la partie 4, où Jane tente de décider quoi porter à l'école. Elle ne s'intéresse plus à ce qu'elle porte depuis qu'elle a atterri un nouveau petit ami, alors elle va maintenant porter des tenues et des chaussures au hasard.

Dans le tableur de Jane, elle répertorie les tenues en colonnes verticales et les chaussures, les colonnes horizontales.

Elle ouvre la feuille de calcul et la fonction RANDBETWEEN (1,3) génère un nombre égal ou égal à un et trois correspondant aux trois types de tenues qu'elle peut porter..

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 l'équipement en nom de l'équipement. 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:

Excel choisit un nombre aléatoire de un à trois, car elle dispose de trois options de tenue..

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 aussi 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? Voici les scores SAT pour cinq étudiants allant de 1 870 à 2 230.

Les chiffres importants à comprendre sont:

Moyenne - La moyenne est aussi 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 en utilisant le symbole grec sigma «σ».

Rang centile - Lorsqu'un étudiant obtient un score élevé, il peut se vanter de se situer 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, le risque qu'un score SAT soit supérieur à 3 STD est pratiquement nul, car 99,73% des scores se situent à moins de 3 STD..

Maintenant regardons à nouveau la feuille de calcul et expliquons son fonctionnement.

Maintenant, nous expliquons les formules:

= MOYENNE (B2: B6)

La moyenne de tous les scores dans la plage B2: B6. Plus précisément, la somme de tous les scores divisée par le nombre de personnes ayant passé le test.

= STDEV.P (B2: B6)

L’écart type sur la plage B2: B6. Le «.P» signifie que STDEV.P est utilisé pour tous les scores, c’est-à-dire l’ensemble de la population et pas seulement un sous-ensemble..

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6 $, B2)

Ceci calcule le pourcentage cumulé sur la plage B2: B6 en fonction du score SAT, dans ce cas B2. Par exemple, 83% des scores sont inférieurs au score de Walker.

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 que votre argument soit plus clair..

Les étudiants sont sur l’axe horizontal et leurs scores au SAT sont représentés par un graphique à barres bleu sur une échelle (axe vertical) de 1 600 à 2 300..

Le classement en centile 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 parce que nous voulons les nombres ainsi que les noms des étudiants.

Dans le menu "Insérer", sélectionnez "Graphiques" -> "Graphiques recommandés":

L'ordinateur recommande un diagramme «Colonne en cluster, axe secondaire». La partie «Axe secondaire» signifie qu’elle dessine deux axes verticaux. Dans ce cas, ce graphique est celui que nous voulons. Nous n'avons rien d'autre à faire.

Vous pouvez utiliser déplacer le graphique et le redimensionner jusqu'à ce que vous l'aviez comme taille et dans la position souhaitée. Une fois que vous êtes satisfait, vous pouvez enregistrer le graphique dans la feuille de calcul..

Si vous cliquez avec le bouton droit de la souris sur le graphique, puis sur «Sélectionner les données», les données sélectionnées pour la plage sont affichées..

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 affecter les axes verticaux gauche et droit..

Dans la boîte de dialogue «Sélectionner la source de données», cliquez sur «partition» sous «Entrées de légende (série)», puis appuyez sur «Modifier» et modifiez-le pour indiquer «Score».

Puis changez la série 2 («percentile») en «percentile».

Retournez à votre graphique, cliquez sur «Titre du graphique» et remplacez-le par «Scores SAT». Nous avons maintenant un graphique complet. Il a deux axes horizontaux: un pour le score SAT (bleu) et un pour le pourcentage cumulé (orange).

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».

Dans le menu qui apparaît, cochez la case pour activer «Complément du solveur», puis cliquez sur «OK».

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 conclu un accord avec une entreprise de camionnage et de transport aérien afin d’expédier 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 et l’usine divisée par 20 000 kilomètres. Par exemple, la distance entre Shanghai et Melbourne est de 8 024 km, ce qui correspond à 8 024/20 000, soit 0,40 USD par iPad..

La question est de savoir comment expédier tous ces iPad de ces trois usines vers ces quatre destinations au coût le plus bas possible.?

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 462 000 (F12) iPad au total. Les usines ont une capacité limitée de 500 250 unités (G12).

Dans le tableur, pour que vous puissiez voir comment cela fonctionne, nous avons saisi 1 dans la cellule B10, ce qui signifie que nous souhaitons expédier un iPad de Shanghai à Melbourne. Les coûts de transport sur cet itinéraire étant de 0,40 USD par iPad, le coût total (B17) est de 0,40 USD..

Le nombre a été calculé à l'aide de la fonction = SOMMAIRE (coûts, livrés). Les «coûts» correspondent aux plages B3: E5..

Et «expédiés» sont la gamme B9: E11:

SOMMAIRE multiplie les «coûts» par la plage «livrée» (B14). Cela s'appelle la «multiplication matricielle».

Pour que SOMPRODUCT fonctionne correctement, les deux matrices - coûts et livraison - doivent avoir la même taille. Vous pouvez contourner cette limitation en créant des coûts supplémentaires et en envoyant des colonnes et des lignes avec une valeur nulle de sorte que les tableaux soient de la même taille et sans impact sur les coûts totaux..

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 avec des contraintes..

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 le total des cellules des cellules $ B $ 12: 12 $ E, doit être supérieure ou égale aux besoins de chaque centre de distribution (13 $ B: 13 $ E)..

Nous ne pouvons pas expédier plus que ce que nous produisons. Nous écrivons que des contraintes comme celle-ci: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

Maintenant, allez dans le menu «Données» et appuyez sur le bouton «Solver». Si le bouton «Solver» n’est pas là, vous devez activer le complément Solver..

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».

Appuyez sur «Résoudre» et Excel enregistre les résultats dans la feuille de calcul, ce que nous voulons. Vous pouvez également enregistrer ceci afin de pouvoir jouer avec d'autres scénarios..

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 intrants (1) et 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.

Quand ils commencent à exploiter, l'argent commence à arriver et la société trouve et vend le cuivre qu'elle produit. Évidemment, plus ils exploitent, plus ils gagnent d'argent et, selon leurs prévisions, leur flux de trésorerie augmente jusqu'à atteindre 9 millions de dollars par an..

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 vendeur), ils veulent donc naturellement savoir comment ils se comparent les uns aux autres à la fin du trimestre et à la fin de l'année, ainsi que le montant de leurs commissions..

Supposons que trois vendeurs, Carlos, Fred et Julie, vendent tous du pétrole. Leurs ventes en dollars par trimestre fiscal pour l'année 2014 sont indiquées dans le tableur ci-dessous.

Pour générer ces rapports, nous créons un tableau croisé dynamique:

Sélectionnez “Insérer -> Tableau croisé dynamique, il se trouve sur le côté gauche de la barre d’outils:

Sélectionnez toutes les lignes et colonnes (y compris le nom du vendeur) comme indiqué ci-dessous:

La boîte de dialogue du tableau croisé dynamique apparaît à droite de la feuille de calcul..

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 à la feuille de calcul un rapport qui n'a aucun sens, mais pourquoi?

Comme vous pouvez le constater, nous avons sélectionné les quatre champs à ajouter au rapport. Le comportement par défaut d’Excel consiste à regrouper les lignes par champs de texte, puis à additionner le reste des lignes..

Ici, il nous donne la somme de l'année 2014 + 2014 + 2014 + 2014 = 24 168, ce qui est un non-sens. En outre, il donne la somme des trimestres 1 + 2 + 3 + 4 = 10 * 3 = 3 0. Nous n'avons pas besoin de ces informations, nous avons donc désélectionné ces champs pour les supprimer de notre tableau croisé dynamique..

La «somme des ventes» (ventes totales) est pertinente, cependant, nous allons résoudre ce problème.

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 toutes les autres cellules. 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 du nombre» et en le changeant en «Devise».

Vous pouvez ensuite voir votre travail dans le tableau croisé dynamique:

Exemple: Ventes par vendeur et par trimestre

Ajoutons maintenant les 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:

Pendant que nous y sommes, supprimons les valeurs de la «somme des trimestres». Cliquez simplement sur la flèche et cliquez sur «Supprimer le champ». Dans la capture d'écran, vous pouvez maintenant voir que nous avons ajouté les lignes «Trimestre», qui décomposent les ventes de chaque vendeur par trimestre..

Gardant ces compétences à l’esprit, vous pouvez désormais créer des tableaux croisés dynamiques à partir de vos propres données.!

Conclusion

En résumé, nous vous avons présenté certaines des fonctionnalités des formules et des fonctions de Microsoft Excel que vous pouvez appliquer à des besoins professionnels, universitaires ou autres..

Comme vous l'avez vu, Microsoft Excel est un énorme produit avec tant de fonctionnalités que la plupart des gens, même les utilisateurs expérimentés, ne les connaissent pas toutes. 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 démontré non seulement les fonctions disponibles dans Microsoft Excel, mais aussi des notions de statistique, 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 voulez revenir en arrière et reprendre le cours, vous pouvez recommencer à zéro avec la leçon 1.!