Tri sous-totaux multiples dans Excel
Fermé
Bruno
-
Modifié par Bruno le 5/07/2011 à 12:02
Patrice33740 Messages postés 8556 Date d'inscription dimanche 13 juin 2010 Statut Membre Dernière intervention 2 mars 2023 - 4 sept. 2011 à 21:59
Patrice33740 Messages postés 8556 Date d'inscription dimanche 13 juin 2010 Statut Membre Dernière intervention 2 mars 2023 - 4 sept. 2011 à 21:59
A voir également:
- Excel sous-total plusieurs colonnes
- Classer par ordre alphabétique excel plusieurs colonnes - Guide
- Formule moyenne excel plusieurs colonnes - Guide
- Liste déroulante excel - Guide
- Déplacer une colonne excel - Guide
- Formule excel pour additionner plusieurs cellules - Guide
11 réponses
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 778
Modifié par Patrice33740 le 5/07/2011 à 15:42
Modifié par Patrice33740 le 5/07/2011 à 15:42
Bonjour,
Pour pouvoir l'exploiter facilement, la base de données ne doit pas comporter de lignes de calculs (sous.totaux), j'ai donc utilisé la feuille "Codage (2)".
J'ai inséré une ligne vide au dessus (la ligne 5) pour permettre à Excel de reconnaitre automatiquement la plage de données.
Je n'ai pas trouvé la donnée qui permet d'identifier les étapes, seule la première partie du tableau est résolue, Voici donc un début de solution :
http://www.cijoint.fr/cjlink.php?file=cj201107/cijvcmZSwz.xlsx
Cordialement
Patrice
Pour pouvoir l'exploiter facilement, la base de données ne doit pas comporter de lignes de calculs (sous.totaux), j'ai donc utilisé la feuille "Codage (2)".
J'ai inséré une ligne vide au dessus (la ligne 5) pour permettre à Excel de reconnaitre automatiquement la plage de données.
Je n'ai pas trouvé la donnée qui permet d'identifier les étapes, seule la première partie du tableau est résolue, Voici donc un début de solution :
http://www.cijoint.fr/cjlink.php?file=cj201107/cijvcmZSwz.xlsx
Cordialement
Patrice
Bonjour,
merci beaucoup
j'ai bien compris ce qui a été réalisé et j'ai fait qq modif pour que cela fonctionnne presque bien.
Il reste une inconnue à résoudre
Dans l'onglet Synthèse, cellule B12
j'ai tenté de modifier la formule pour prendre en compte une matrice supplémentaire (celle correspondant à la colonne "Sujet" dans la BD "Onglet Données")
J'ai tenté deux formules :
=SOMMEPROD((BD_Nom=Synthèse!$A12)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));(BD_Nom=Synthèse!$B9)*1;BD_Nbre_de_caractères)
celle-ci donne comme valeur 0 alors que la valeur devrait être 363
et
=SOMMEPROD((BD_Nom=Synthèse!$A13)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));BD_Nbre_de_caractères)
qui donne comme résultat #/NA
Donc les deux me mènent à une impasse puisque je ne peux prendre en compte ce critère "Sujet" qui correspond à mes étapes ou mes phases que j'ai décrites dans le problème initial.
Voilà le fichier dans son état le plus avancé :
http://dl.dropbox.com/u/4711820/FS_G1_Equipe01_test_synth%C3%A8se4.xlsx
Si vous voyez une solution ?
Merci d'avance.
Bruno De Lièvre
merci beaucoup
j'ai bien compris ce qui a été réalisé et j'ai fait qq modif pour que cela fonctionnne presque bien.
Il reste une inconnue à résoudre
Dans l'onglet Synthèse, cellule B12
j'ai tenté de modifier la formule pour prendre en compte une matrice supplémentaire (celle correspondant à la colonne "Sujet" dans la BD "Onglet Données")
J'ai tenté deux formules :
=SOMMEPROD((BD_Nom=Synthèse!$A12)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));(BD_Nom=Synthèse!$B9)*1;BD_Nbre_de_caractères)
celle-ci donne comme valeur 0 alors que la valeur devrait être 363
et
=SOMMEPROD((BD_Nom=Synthèse!$A13)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));BD_Nbre_de_caractères)
qui donne comme résultat #/NA
Donc les deux me mènent à une impasse puisque je ne peux prendre en compte ce critère "Sujet" qui correspond à mes étapes ou mes phases que j'ai décrites dans le problème initial.
Voilà le fichier dans son état le plus avancé :
http://dl.dropbox.com/u/4711820/FS_G1_Equipe01_test_synth%C3%A8se4.xlsx
Si vous voyez une solution ?
Merci d'avance.
Bruno De Lièvre
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 778
8 juil. 2011 à 16:41
8 juil. 2011 à 16:41
Bonjour,
C'était presque çà.
Il faut comparer la valeur en $B9 avec celles de la colonne Sujet (au lieu de la colonne Nom désignée par BD_Nom).
Il suffit donc d'ajouter le nom BD_Sujet pour désigner la bonne colonne et de corriger la formule :
=SOMMEPROD((BD_Nom=Synthèse!$A12)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));(BD_Sujet=Synthèse!$B9)*1;BD_Nbre_de_caractères)
Fichier corrigé :
http://www.cijoint.fr/cjlink.php?file=cj201107/cijOYPauRS.xlsx
C'était presque çà.
Il faut comparer la valeur en $B9 avec celles de la colonne Sujet (au lieu de la colonne Nom désignée par BD_Nom).
Il suffit donc d'ajouter le nom BD_Sujet pour désigner la bonne colonne et de corriger la formule :
=SOMMEPROD((BD_Nom=Synthèse!$A12)*1;DECALER(BD_L1C1;;EQUIV(B$11;BD_L1;0)-1;NBVAL(BD_C2));DECALER(BD_L1C1;;EQUIV(B$10;BD_L1;0)-1;NBVAL(BD_C2));(BD_Sujet=Synthèse!$B9)*1;BD_Nbre_de_caractères)
Fichier corrigé :
http://www.cijoint.fr/cjlink.php?file=cj201107/cijOYPauRS.xlsx
Merci beaucoup
Cela fonctionne bien ;-)
J'ai une autre question :
A partir de plusieurs fichiers (un par groupe et il y a 42 groupes), je voudrais placer dans un autre fichier (différent des 42 évoqués) un ensemble de données (systématiquement les mêmes). En clair, c'est un fichier de synthèse globale mais je voudrais systématiser le passage des 42 fichiers vers le fichier synthèse.
Voici la forme que prendrait le fichier synthèse dans lequel j'ai pour l'instant "copier-coller" les données que je voulais y voir figurer.
http://dl.dropbox.com/u/4711820/Synthese_total_contenu_V1.xlsx
Merci encore
Bruno De Lièvre
Cela fonctionne bien ;-)
J'ai une autre question :
A partir de plusieurs fichiers (un par groupe et il y a 42 groupes), je voudrais placer dans un autre fichier (différent des 42 évoqués) un ensemble de données (systématiquement les mêmes). En clair, c'est un fichier de synthèse globale mais je voudrais systématiser le passage des 42 fichiers vers le fichier synthèse.
Voici la forme que prendrait le fichier synthèse dans lequel j'ai pour l'instant "copier-coller" les données que je voulais y voir figurer.
http://dl.dropbox.com/u/4711820/Synthese_total_contenu_V1.xlsx
Merci encore
Bruno De Lièvre
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 778
14 juil. 2011 à 18:41
14 juil. 2011 à 18:41
Bonjour,
Je vois deux pistes possibles :
- utiliser la fonction INDIRECT en utilisant une table des noms de fichiers et des adresses à lire sur une feuille dédiée : pas très compliqué à mettre en oeuvre, inconvénient : table à tenir à jour.
- utiliser une macro qui analyserait le(s) répertoire(s) ad hoc et mettrait en place les formules adéquates : simplicité d'emploi mais nécessite une certaine rigueur dans la gestion des fichiers et surtout de bonnes connaissances du VBA.
Patrice
Je vois deux pistes possibles :
- utiliser la fonction INDIRECT en utilisant une table des noms de fichiers et des adresses à lire sur une feuille dédiée : pas très compliqué à mettre en oeuvre, inconvénient : table à tenir à jour.
- utiliser une macro qui analyserait le(s) répertoire(s) ad hoc et mettrait en place les formules adéquates : simplicité d'emploi mais nécessite une certaine rigueur dans la gestion des fichiers et surtout de bonnes connaissances du VBA.
Patrice
Bonjour,
1. je comprends bien ce que peut être la table des noms de fichiers...
moins bien pour les adresses à lire sur une feuille..
En fait, les cellules des fichiers "origine" sont bien identifiées.. elles sont toujours identiques d'un fichier à l'autre.. seuls changent les noms des fichiers origine.
Ce que je ne vois pas c'est comment les "copier" vers le fichier destination de manière automatique.
2. Je cherche aussi à identifier la présence d'un mot dans l'ensemble des textes d'une colonne BD (genre celle intiulée "message" dans l'onglet "donnée"). Je voudrais ensuite calculer la fréquence d'apparition de ce mot et copier le nombre calculé dans un onglet à part. Le vrai problème est d'aller chercher un mot dans un ensemble de textes.
Bien à vous,
Bruno De Lièvre
1. je comprends bien ce que peut être la table des noms de fichiers...
moins bien pour les adresses à lire sur une feuille..
En fait, les cellules des fichiers "origine" sont bien identifiées.. elles sont toujours identiques d'un fichier à l'autre.. seuls changent les noms des fichiers origine.
Ce que je ne vois pas c'est comment les "copier" vers le fichier destination de manière automatique.
2. Je cherche aussi à identifier la présence d'un mot dans l'ensemble des textes d'une colonne BD (genre celle intiulée "message" dans l'onglet "donnée"). Je voudrais ensuite calculer la fréquence d'apparition de ce mot et copier le nombre calculé dans un onglet à part. Le vrai problème est d'aller chercher un mot dans un ensemble de textes.
Bien à vous,
Bruno De Lièvre
Bonjour monsieur,
dans la suite de ce qui a déjà été réalisé ci-dessus.
J'ai un problème à résoudre dans la case Z11 de l'onglet Synthèse du fichier suivant : http://dl.dropbox.com/u/4711820/Synth%C3%A8se_FNS_G3_Equipe42_test.xlsx
Je voudrais obtenir la somme des valeurs de la colonne Total_Concepts_P (dans l'onglet codage sémantique (colonne EQ))
pour un nom donné (BD_Nom)
pour un sujet donné (BD_Sujet)
voici la formule utilisée
=SOMMEPROD((BD_Nom=Synthèse!$A11)*1;(BD_Sujet=Synthèse!$B8)*1;(BD_Total_Concepts_P)*1)
Il me renvoie Nom ?.. et donc pas cette somme ..
J'ai bien conscience que c'est la dernière partie de la formule qu'il faut modifier.
Pouvez-vous m'aider ?
Si je peux trouver une solution là, je pourrai le faire pour toutes les autres cellules.
Merci d'avance
Bruno De Lièvre
dans la suite de ce qui a déjà été réalisé ci-dessus.
J'ai un problème à résoudre dans la case Z11 de l'onglet Synthèse du fichier suivant : http://dl.dropbox.com/u/4711820/Synth%C3%A8se_FNS_G3_Equipe42_test.xlsx
Je voudrais obtenir la somme des valeurs de la colonne Total_Concepts_P (dans l'onglet codage sémantique (colonne EQ))
pour un nom donné (BD_Nom)
pour un sujet donné (BD_Sujet)
voici la formule utilisée
=SOMMEPROD((BD_Nom=Synthèse!$A11)*1;(BD_Sujet=Synthèse!$B8)*1;(BD_Total_Concepts_P)*1)
Il me renvoie Nom ?.. et donc pas cette somme ..
J'ai bien conscience que c'est la dernière partie de la formule qu'il faut modifier.
Pouvez-vous m'aider ?
Si je peux trouver une solution là, je pourrai le faire pour toutes les autres cellules.
Merci d'avance
Bruno De Lièvre
Vaucluse
Messages postés
26496
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
1 avril 2022
6 411
Modifié par Vaucluse le 4/09/2011 à 18:18
Modifié par Vaucluse le 4/09/2011 à 18:18
Bonjour
1°) je pense qu'il faudrait écrire la formule comme ceci:
=SOMMEPROD((BD_Nom=Synthèse!$A11)*(BD_Sujet=Synthèse!$B8)*(BD_Total_Concepts_P)*1)
2°) sauf erreur, le nom BD_Total_Concepts_P n'est pas un nom référencé dans la liste de champs nommés que donne le fichier?
il semblerait que ce champ doivent concerner:
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
1°) je pense qu'il faudrait écrire la formule comme ceci:
=SOMMEPROD((BD_Nom=Synthèse!$A11)*(BD_Sujet=Synthèse!$B8)*(BD_Total_Concepts_P)*1)
2°) sauf erreur, le nom BD_Total_Concepts_P n'est pas un nom référencé dans la liste de champs nommés que donne le fichier?
il semblerait que ce champ doivent concerner:
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
crdlmnt
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 778
4 sept. 2011 à 21:09
4 sept. 2011 à 21:09
Bonjour,
Comme dit Vaucluse (bonjour Vaucluse) :
1°) L'erreur #NOM est due au fait que le nom BD_Total_Concepts_P n'a pas été créé.
Ce nom doit effectivement faire référence à :
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
2°) Pour totaliser les Total_Concept_P, il suffit d'écrire :
=SOMMEPROD((BD_Nom=Synthèse!$A11)*1;(BD_Sujet=Synthèse!$Z8)*1;BD_Total_Concepts_P)
En effet, dans SOMMEPROD, on utilises *1 (ou +0) pour transformer en nombre, le booléen résultat d'une équation de comparaison, mais lorsque la colonne est déjà un résultat numérique, il ne faut pas effectuer d'opération (sinon on obtient #VALEUR!).
Par exemple BD_Nom=Synthèse!$A11 donne VRAI ou FAUX, en multipliant par 1 (ou en ajoutant 0) on force Excel à effectuer une conversion en 0 (pour FAUX) ou 1 (pour VRAI), mais BD_Total_Concepts_P est déjà une valeur numérique, il ne faut pas écrire BD_Total_Concepts_P*1 .
Comme dit Vaucluse (bonjour Vaucluse) :
1°) L'erreur #NOM est due au fait que le nom BD_Total_Concepts_P n'a pas été créé.
Ce nom doit effectivement faire référence à :
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
2°) Pour totaliser les Total_Concept_P, il suffit d'écrire :
=SOMMEPROD((BD_Nom=Synthèse!$A11)*1;(BD_Sujet=Synthèse!$Z8)*1;BD_Total_Concepts_P)
En effet, dans SOMMEPROD, on utilises *1 (ou +0) pour transformer en nombre, le booléen résultat d'une équation de comparaison, mais lorsque la colonne est déjà un résultat numérique, il ne faut pas effectuer d'opération (sinon on obtient #VALEUR!).
Par exemple BD_Nom=Synthèse!$A11 donne VRAI ou FAUX, en multipliant par 1 (ou en ajoutant 0) on force Excel à effectuer une conversion en 0 (pour FAUX) ou 1 (pour VRAI), mais BD_Total_Concepts_P est déjà une valeur numérique, il ne faut pas écrire BD_Total_Concepts_P*1 .
Bonjour,
pour la formule j'ai bien compris
pour ce qui concerne la création du nom BD_Total_Concepts_P.. je vois bien la formule mais je ne vois pas où je dois la placer.
Si qqn voit une solution, ce serait super de l'intégrer dans mon fichier joint (voir message ci-dessus) ... pour que je comprenne comment cela marche précisément.
Bien à vous tous,
et Merci.
Bruno De Lièvre
pour la formule j'ai bien compris
pour ce qui concerne la création du nom BD_Total_Concepts_P.. je vois bien la formule mais je ne vois pas où je dois la placer.
Si qqn voit une solution, ce serait super de l'intégrer dans mon fichier joint (voir message ci-dessus) ... pour que je comprenne comment cela marche précisément.
Bien à vous tous,
et Merci.
Bruno De Lièvre
Patrice33740
Messages postés
8556
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
2 mars 2023
1 778
Modifié par Patrice33740 le 4/09/2011 à 22:00
Modifié par Patrice33740 le 4/09/2011 à 22:00
Re,
Onglet Formules / gestionnaire des noms / Nouveau... /
Nom :
BD_Total_Concepts_P
Zone :
Ce classeur
Fait référence à :
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
Cordialement
Patrice
Onglet Formules / gestionnaire des noms / Nouveau... /
Nom :
BD_Total_Concepts_P
Zone :
Ce classeur
Fait référence à :
=DECALER(Codage_sémantique!$EQ$6;;;NBVAL(Codage_sémantique!$B:$B))
Cordialement
Patrice
5 juil. 2011 à 16:28
Pour la différenciation des étapes, il s'agit des intitulés dans le colonne Sujet (oui je sais ce n'est pas clair comme intitulé :-). Chaque sujet est un moment différent dans le processus. C'est pour cela que dans les tableaux de synthèse il y a les intitulés des sujets qui dont présents.
Merci de votre aide
On avance
Bruno
5 juil. 2011 à 17:35
- J'ai émis l'hypothèse que la colonne B de la base de données ne contient que des données (contrairement à A, U, V, W) ;
- J'ai ajouté une formule pour le nom des champs à chercher sur les lignes 10, 18, 26, 35, 43, 51, 61 avec couleur de police blanche (C10 à F10, H10 à K10, ... )
- J'ai créé des noms pour simplifier les formules, certains sont auto adaptatif au nombre de lignes de la base de données (définis par DECALER).
Bon courage
Patrice