Excel comprend un large éventail d’outils que vous pouvez utiliser pour résumer les données de la feuille de calcul. Cette rubrique explique comment sélectionner des lignes de manière aléatoire à l’aide des fonctions RAND et RANDBETWEEN,

comment résumer les données de la feuille de calcul à l’aide des fonctions SUBTOTAL et AGGREGATE et comment afficher une liste de valeurs uniques dans un jeu de données.

Sélectionnez les lignes de la liste au hasard
Outre le filtrage des données stockées dans vos feuilles de calcul Excel, vous pouvez choisir des lignes de manière aléatoire dans une liste. Sélectionner des lignes au hasard est utile pour choisir les clients qui recevront une offre spéciale, pour choisir les jours du mois à vérifier, ou pour choisir les gagnants d’un prix lors d’une fête entre employés.

Pour choisir des lignes de manière aléatoire, vous pouvez utiliser la fonction RAND, qui génère une valeur décimale aléatoire comprise entre 0 et 1, et comparer la valeur renvoyée à une valeur de test incluse dans une formule. Si vous recalculez la fonction RAND 10 fois et vérifiez à chaque fois si la valeur est inférieure à 0,3, il est très peu probable que vous obteniez exactement trois cas où la valeur est inférieure à

0,3 Tout comme lancer une pièce de monnaie peut donner le même résultat 10 fois de suite par hasard, les résultats de la fonction RAND peuvent sembler faussés si vous ne la recalculez que quelques fois. Cependant, si vous deviez recalculer la fonction
10 000 fois, il est fort probable que le nombre de valeurs inférieures à 0,3
serait très proche de 30 pour cent.

CONSEIL La fonction RAND étant une fonction volatile (c’est-à-dire qu’elle recalcule ses résultats chaque fois que vous mettez à jour la feuille de calcul), vous devez copier les cellules contenant la fonction RAND dans une formule et coller les valeurs des formules dans leurs cellules d’origine. Pour ce faire, sélectionnez les cellules contenant les formules RAND et collez-les dans les mêmes cellules que les valeurs.

La fonction RANDBETWEEN génère un nombre entier aléatoire dans une plage définie. Par exemple, la formule = RANDBETWEEN (1 100) générerait une valeur entière aléatoire comprise entre 1 et 100 inclus. La fonction RANDBETWEEN est très utile pour créer des échantillons de collections de données pour les présentations. Avant que la fonction RANDBETWEEN ne soit introduite, vous deviez créer des formules qui ajoutaient, soustrayaient, multipliaient et divisaient les résultats de la fonction RAND, qui sont toujours des valeurs décimales comprises entre 0 et
1, pour créer des données.

Pour utiliser RAND ou RANDBETWEEN pour sélectionner une ligne, créez une formule IF qui teste les valeurs aléatoires. Si vous souhaitez vérifier 30% des lignes, une formule telle que = IF (adresse_cellule <0.3, «TRUE», «FALSE») affichera TRUE dans les cellules de formule pour une valeur inférieure ou égale à 0.3 et FALSE sinon.

Résumer les données dans des feuilles de calcul comportant des lignes masquées et filtrées

La capacité d’analyser les données les plus vitales pour vos besoins actuels est importante, mais
Vous pouvez limiter la manière dont vous pouvez synthétiser vos données filtrées à l’aide de fonctions telles que SUM et AVERAGE. Une limite est que les formules que vous créez et qui incluent les fonctions SOMME et MOYENNE ne modifient pas leurs calculs si certaines des lignes
utilisés dans la formule sont cachés par le filtre.

Excel propose deux méthodes pour résumer uniquement les cellules visibles dans une liste de données filtrée. La première méthode consiste à utiliser AutoCalculate. Pour utiliser le calcul automatique, vous sélectionnez les cellules à résumer. Lorsque vous le faites, Excel affiche la moyenne des valeurs dans les cellules, la somme de

les valeurs dans les cellules et le nombre de cellules visibles (le nombre) dans la sélection. L’affichage se trouve dans la barre d’état dans la partie inférieure de la fenêtre Excel.

Lorsque vous utilisez le calcul automatique, vous n’êtes pas obligé de rechercher la somme, la moyenne et le nombre de cellules sélectionnées. Vous pouvez ajouter ou supprimer des calculs en fonction de vos besoins. une coche apparaît en regard du nom d’une fonction si le résultat de cette fonction apparaît dans la barre d’état.

La barre d’état affiche les valeurs récapitulatives lorsque vous sélectionnez plusieurs cellules contenant des données numériques.

Le calcul automatique est idéal pour trouver un total ou une moyenne rapide pour les cellules filtrées, mais il ne rend pas le résultat disponible dans la feuille de calcul. Les formules telles que = SUM (C3: C26) prennent toujours en compte chaque cellule de la plage, que vous masquiez ou non la ligne d’une cellule manuellement. Vous devez donc créer une formule à l’aide de la fonction SUBTOTAL ou de la fonction AGGREGATE pour résumer simplement ces valeurs qui sont visibles dans votre feuille de calcul. La fonction SOUS-TOTAL vous permet de choisir de récapituler chaque valeur d’une plage ou de ne résumer que les valeurs de lignes que vous n’avez pas masquées manuellement. La fonction SUBTOTAL a la syntaxe suivante: = SUBTOTAL (num_fonction, ref1, ref2,…). L’argument function_num contient le numéro de l’opération que vous souhaitez utiliser pour résumer vos données. (Les numéros d’opération sont résumés dans un tableau plus loin dans cette section.) Les arguments ref1, ref2 et autres représentent jusqu’à 29 plages à inclure dans le calcul.

Par exemple, supposons que vous ayez une feuille de calcul dans laquelle vous avez masqué les lignes 20 à 26 manuellement. Dans ce cas, la formule = SOUS-TOTAL (9, C3: C26, E3: E26, G3: G26) trouverait la somme de toutes les valeurs des plages C3: C26, E3: E26 et G3: G26, que cela soit ou non gamme contenait toutes les lignes cachées. La formule = SOUS-TOTAL (109, C3: C26, E3: E26, G3: G26) trouverait la somme de toutes les valeurs des cellules C3: C19, E3: E19 et G3: G19, en ignorant les valeurs des lignes masquées manuellement.

 Important

Veillez à placer votre formule SOUS-TOTALE dans une ligne égale ou supérieure aux en-têtes de la plage filtrée. Sinon, votre filtre pourrait masquer le résultat de la formule!

Le tableau suivant répertorie les opérations récapitulatives disponibles pour la formule SOUS.TOTAL. Excel affiche les opérations récapitulatives disponibles dans le cadre de la saisie semi-automatique de formules.

vous n’avez donc pas besoin de vous souvenir des numéros d’opération ou de les rechercher dans le
Système d’aide.

Comme le montre le tableau précédent, la fonction SOUS-TOTAL comporte deux jeux d’opérations. Le premier ensemble (opérations 1 à 11) représente des opérations qui incluent des valeurs cachées dans leur

summary, et le second ensemble (opérations 101 à 111) représentent des opérations qui résument uniquement les valeurs visibles dans la feuille de calcul. Les opérations 1 à 11 récapitulent toutes les cellules d’une plage, qu’elles contiennent ou non des lignes masquées manuellement. En revanche, les opérations 101 à 111 ignorent les valeurs des lignes masquées manuellement. Ce que la fonction SOUS-TOTAL ne fait pas, cependant, change son résultat pour refléter les lignes masquées à l’aide d’un filtre.

 Important

Excel considère la première cellule de la plage de données comme une cellule d’en-tête. Par conséquent, il ne tient pas compte de la cellule car il crée la liste des valeurs uniques. Assurez-vous d’inclure la cellule d’en-tête dans votre plage de données!

La fonction AGGREGATE étend les capacités de la fonction SUBTOTAL. Grâce à cela, vous pouvez choisir parmi une plus grande gamme de fonctions et utiliser un autre argument pour déterminer les éventuelles valeurs à ignorer dans le calcul. AGGREGATE a deux syntaxes possibles, en fonction de l’opération récapitulative sélectionnée. La première syntaxe est

= AGGREGATE (nombre_fonctions, options, ref1…), similaire à la syntaxe de la fonction SOUS-TOTAL. L’autre syntaxe possible, = AGGREGATE (nombre_fonctions, options, array, [k]), permet de créer des fonctions AGGREGATE utilisant les opérations LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC et QUARTILE.EXC.

Le tableau suivant récapitule les opérations récapitulatives disponibles pour une utilisation dans
Fonction AGREGATE.

Vous utilisez le deuxième argument, options, pour sélectionner les éléments que la fonction AGGREGATE doit ignorer. Ces éléments peuvent inclure des lignes cachées, des erreurs et des fonctions SUBTOTAL et AGGREGATE. Le tableau suivant récapitule les valeurs disponibles pour l’argument d’options et leur effet sur les résultats de la fonction.

Pour résumer des valeurs à l’aide de la fonction de calcul automatique

1. Sélectionnez les cellules dans votre feuille de calcul.

2. Affichez les résumés dans la barre d’état.

Pour modifier les résumés de calcul automatique affichés dans la barre d’état
1. Cliquez avec le bouton droit sur la barre d’état.

2. Cliquez sur une opération récapitulative sans coche pour l’afficher.

Ou

Cliquez sur une opération récapitulative avec une coche pour la masquer.

Pour créer une formule de sous-total

1. Dans une cellule, entrez une formule utilisant la syntaxe = SOUS-TOTAL (num_fonction, ref1, ref2,
…) Les arguments dans la syntaxe sont les suivants:

• L’argument function_num est le numéro de référence de la fonction que vous souhaitez utiliser.

• Les arguments ref1, ref2 et ref ultérieur font référence à des plages de cellules.

Pour créer une formule AGGREGATE

1. Effectuez l’une des opérations suivantes:

• Créez une formule de la syntaxe = AGGREGATE (num_fonction, options, ref1…). Les arguments dans la syntaxe sont les suivants:

• L’argument function_num est le numéro de référence de la fonction que vous souhaitez utiliser.

• • L’argument options est le numéro de référence des options souhaitées.

  • Les arguments ref1, ref2 et ref ultérieur font référence à des plages de cellules.

  Ou

  • Créez une formule avec la syntaxe = AGGREGATE (num_fonction, options, tableau, [k]). Les arguments dans la syntaxe sont les suivants:

  • L’argument function_num est le numéro de référence de la fonction que vous souhaitez utiliser.

  • L’argument options est le numéro de référence des options que vous souhaitez utiliser.

  • L’argument de tableau représente la plage de cellules (tableau) qui fournit des données pour la formule.

  • L’argument optionnel k, utilisé avec les valeurs LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC et QUARTILE.EXC, indique la valeur, le centile ou le quartile à renvoyer.