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

  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é. Vous pouvez masquer le détail (et afficher uniquement 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 (Données | Sous-totaux | Supprimer tout) 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. Choisissez Tableau croisé dynamique et rapport de graphique croisé dynamique dans le menu Données. Excel démarre l’Assistant Tableau et graphique croisés dynamiques.

  2. Cliquez sur Suivant. (Les sélections par défaut à l’étape 1 sont OK.) L’étape 2 de l’Assistant Tableau croisé dynamique et graphique croisé dynamique s’affiche.

  3. La plage entière de vos données (A1: C36001) doit être sélectionnée. Cliquez sur Suivant. L’étape 3 de l’Assistant Tableau croisé dynamique et graphique croisé dynamique s’affiche.

  4. Assurez-vous que Nouvelle feuille de calcul est sélectionnée, puis cliquez sur Terminer. Excel crée un tableau croisé dynamique vide et affiche une liste de champs.

  5. Faites glisser le champ Groupe vers la zone Ligne.

  6. Faites glisser le champ Heure vers la zone Données.

  7. Faites glisser le champ Signal vers la zone Données.

  8. Faites à nouveau glisser le champ Signal vers la zone Données. Le tableau croisé dynamique devrait maintenant afficher «Count of Time», «Sum of Signal» et «Sum of Signal2»

pour chaque groupe.

  1. Dans la zone Données, cliquez avec le bouton droit sur l’une des étiquettes «Nombre de temps». Excel affiche un menu contextuel.

  2. Choisissez Paramètres de champ dans le menu contextuel. Excel affiche la boîte de dialogue Champ de tableau croisé dynamique.

  3. Dans la liste Synthétiser, choisissez Moyenne.

  4. Cliquez sur OK. Tous les libellés «Count of Time» deviennent «Average of Time».

  5. Dans la zone Données, cliquez avec le bouton droit sur l’une des étiquettes «Somme du signal». Excel affiche un menu contextuel.

  6. Choisissez Paramètres de champ dans le menu contextuel. Excel affiche à nouveau la boîte de dialogue Champ de tableau croisé dynamique.

  7. Dans la liste Synthétiser, choisissez Moyenne.

  8. Cliquez sur OK. Toutes les étiquettes «Somme du signal» deviennent «Moyenne du signal».

  9. Dans la zone Données, cliquez avec le bouton droit sur l’une des étiquettes «Somme de Signal2».

Excel affiche un menu contextuel.

  1. Choisissez Paramètres de champ dans le menu contextuel. Excel affiche à nouveau la boîte de dialogue Champ de tableau croisé dynamique.

  2. Dans la liste Summarize, choisissez StdDev.

  3. Cliquez sur OK. Toutes les étiquettes «Somme de Signal2» deviennent «StdDev de Signal2».

Vous maintenant comment les données souhaitées. Vous pouvez préférer une configuration qui n’affiche qu’une seule ligne par catégorie pour vos données. Si tel est le cas, cliquez simplement sur l’en-tête Données et maintenez le bouton de la souris enfoncé tout en déplaçant lentement la souris vers la droite. Lorsque vous déplacez le pointeur de la souris dans la zone d’en-tête Total, une petite icône près du pointeur de la souris indique un «changement» dans la disposition. Relâchez le bouton de la souris et vous n’aurez qu’une seule ligne pour chaque groupe dans vos donné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, cliquer dessus avec le bouton droit de la souris et choisir Actualiser les données.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2771) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

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