Chris a une énorme quantité de données dans une feuille de calcul et il souhaite analyser les données en fonction de différents regroupements. Par exemple, il a des données dans les cellules A2: B36001, où la ligne 1 contient les en-têtes de colonne Heure et Signal. Il veut diviser les données en groupes constitués d’un certain nombre arbitraire de valeurs séquentielles, puis extraire, pour chaque groupe, une valeur moyenne pour le temps, une valeur moyenne pour le signal et un écart type pour le signal.

Le moyen le plus simple de gérer ce type d’exigence consiste à ajouter une colonne utilisée pour indiquer un numéro de groupe pour chaque ligne. Suivez ces étapes:

  1. Mettez le groupe d’en-tête dans la cellule C1.

  2. Dans la cellule E1, entrez le nombre de valeurs devant figurer dans chaque groupe.

Par exemple, si vous souhaitez que chaque groupe contienne 10 valeurs séquentielles, entrez le nombre 10 dans la cellule E1.

  1. Dans la cellule C2, entrez cette formule: = INT ROW () – ROW ($ C $ 2 / $ E $ 1) +1. Copiez la formule de la cellule C2 dans la plage C3: C36001. La colonne C contient désormais un «numéro de groupe» pour chaque ligne, basé sur la valeur de la cellule E1.

Si E1 est 10, vous vous retrouvez avec 3600 groupes, 1 à 3600. Si E1 est 100, vous vous retrouvez avec 360 groupes, 1 à 360.

Une fois les numéros de groupe définis, vous êtes prêt à effectuer l’analyse. Il existe plusieurs façons de procéder. Une façon consiste à utiliser les capacités de sous-total d’Excel. Sélectionnez l’une des cellules de la zone de données et procédez comme suit:

  1. Choisissez Sous-totaux dans le menu Données. Excel affiche la boîte de dialogue Sous-total.

  2. Remplacez la liste déroulante À chaque changement par Groupe.

  3. Modifiez la liste déroulante Utiliser la fonction pour indiquer le type de statistique que vous souhaitez calculer pour chaque groupe.

  4. Modifiez la zone Ajouter un sous-total à afin que seuls l’heure ou le signal soient sélectionnés, selon le cas.

  5. Cliquez sur OK.

Excel regroupe et sous-totalise les données, comme indiqué. (Ce processus peut prendre un certain temps en fonction de la taille de vos groupes.) Vous pouvez masquer le détail et n’afficher que les sous-totaux en cliquant sur le petit 2 (avec la boîte autour) dans la zone de contour à gauche de la feuille de calcul. Si vous souhaitez modifier ultérieurement ce qui est calculé ou si vous devez modifier le nombre d’éléments dans chaque groupe, supprimez simplement les sous-totaux (à l’aide du bouton de la boîte de dialogue Sous-total) et répétez les étapes ci-dessus.

Une autre façon de dériver les statistiques de vos données consiste à utiliser un tableau croisé dynamique. Assurez-vous qu’il n’y a pas de sous-totaux dans les données et sélectionnez une cellule dans les données. Suivez ensuite ces étapes:

  1. Affichez l’onglet Insertion du ruban.

  2. Cliquez sur l’outil de tableau croisé dynamique. (Cet outil est le premier à gauche de l’onglet Insertion.) Excel affiche la boîte de dialogue Créer un tableau croisé dynamique.

  3. Cliquez sur OK. (Les options par défaut de la boîte de dialogue sont très bien.) Excel crée un tableau croisé dynamique vide et affiche une liste de champs à droite de la feuille de calcul.

  4. Faites glisser le champ Groupe vers la zone Étiquettes de ligne, juste en dessous de la liste des champs.

  5. Faites glisser le champ Heure vers la zone Valeurs, juste en dessous de la liste des champs.

  6. Faites glisser le champ Signal vers la zone Valeurs, juste en dessous de la liste des champs.

  7. Faites à nouveau glisser le champ Signal vers la zone Valeurs. Le tableau croisé dynamique doit maintenant afficher «Count of Time», «Sum of Signal» et «Sum of Signal2».

  8. Dans la zone Valeurs, cliquez sur l’étiquette «Count of Time». Excel affiche un menu contextuel.

  9. Choisissez Paramètres du champ de valeur. Excel affiche la boîte de dialogue Paramètres du champ de valeur.

  10. Dans la liste Summarize Value Field By, choisissez Average.

  11. Cliquez sur OK. Les libellés «Count of Time» deviennent «Average of Time».

  12. Dans la zone Valeurs, cliquez sur l’étiquette « Somme du signal ». Excel affiche un menu contextuel.

  13. Choisissez Paramètres du champ de valeur. Excel affiche la boîte de dialogue Paramètres du champ de valeur.

  14. Dans la liste Summarize Value Field By, choisissez Average.

  15. Cliquez sur OK. Les étiquettes «Somme du signal» deviennent «Moyenne du signal».

  16. Dans la zone Valeurs, cliquez sur l’étiquette « Somme de Signal2 ». Excel affiche un menu contextuel.

  17. Choisissez Paramètres du champ de valeur. Excel affiche la boîte de dialogue Paramètres du champ de valeur.

  18. Dans la liste Summarize Value Field By, choisissez StdDev.

  19. Cliquez sur OK. Les étiquettes «Somme du signal» deviennent «StdDev of Signal».

Vous avez maintenant les données souhaitées. Si vous devez modifier le nombre d’éléments de données dans chaque groupe, revenez simplement à la feuille de calcul de données et remplacez la cellule E1 par une valeur différente. Vous pouvez ensuite revenir au tableau croisé dynamique, afficher l’onglet Options du ruban et cliquer sur le bouton Actualiser.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (8628) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

link: / excel-Calculating_Statistical_Values_on_Different-Sized_Subsets_of_Data [Calcul des valeurs statistiques sur des sous-ensembles de données de différentes tailles].