Tableau et données - comptage

[goldenboy]

Bonjour à tous,

Deuxième fois de l'année que je viens vous solliciter.

J'ai besoin de votre aide pour réaliser un comptage. Le fichier joint vous donnera l'idée de ce qui doit être réalisé.

Je pense que l'utilisation de tableau et de boucle imbriquées sont nécessaires, mais je ne maitrise pas vraiment les tableaux.

Le nombre de ligne à traiter est généralement entre 1500 et 2000.

Je reste à votre disposition pour toutes informations complémentaires.

Très cordialement.

 

[youky(BJ)]

Re : Tableau et données - comptage

Bonjour,
C'est maintenant ou jamais d'utiliser un tableau croisé dynamique (TCD)
A voir si cela conviens
Bruno

 

[goldenboy]

Re : Tableau et données - comptage

Bonsoir Youky(BJ).

Malheureusement, le TCD ne semble pas convenir.
En voici la raison :
Les résultats affichés dans la feuille résultats seront ensuite insérés dans un autre tableau sous la forme de commentaires.

'Ajout com
Dim commentaire as comment
Commentaire = sheets("résultats").range("a1").value & sheets("résultats").range("b1").value & vblf & sheets("résultats").range("c1").value

Il faut vraiment arriver au résultat que j'ai inscrit et ce n'est pas facile.

Je pense qu'il faut plutôt quelque chose du genre :

'une boucle for sur les principaux rangement
for i = 1 to maxcol step 1
var = cells(1,i).value
'Une sous boucle for pour chaque item
for j = 3 to maxitem step 1
varitem = cells(i,j).value
'après il faut encore une boucle pour les codes a moins qu'elle n'englobe tout celà.
'puis il faut tester avec des conditions et aussi utiliser countif à un moment donné.

Je donne des pistes, mais je ne suis pas sur d'être dans le vrai.

La tâche me semble complexe.

 

[mapomme]

Re : Tableau et données - comptage

Bonjour goldenboy, youky(BJ), à tous,

Un autre essai via un TCD et quelques formules.

1) Une colonne a été rajoutée à la table des données. Cette colonne contient une formule matricielle pour renvoyer la catégorie du type d'objet. La formule est:

=INDEX(paramètres!$A$2:$E$2;MAX((paramètres!$A$3:$E$13=B2)*COLONNE(paramètres!$A$3:$E$13)))

2) la tables des données a été transformée en tableau de feuille nommé Tableau1

3) Basé sur Tableau1, un TCD a été créé et nommé TCD3

4) Sur la feuille Résultats, une table a été créée avec les formules permettant d'aller chercher dans TCD3 les résultats désirés:
en A1:

=SI(NB.SI(données!$F$1:F2;données!F2)=1;données!F2;"")

en B1:

=SI(A1="";"";SOMME.SI(données!$F$1:F4000;A1;données!$H$1:H4000))

en C1:

=SI(LIGNE()>NBVAL(données!$F$1:F4000)-1;""; SI(NB.SI(données!$F$1:F2;données!F2)<=NB.SI(données!$F$2:F4000;données!F2);données!H2  & " du " & TEXTE(données!G2;"jj/mm/aaaa");""))

Tirer/copier ensuite les formules de A1 à C1 vers le bas jusqu"à la ligne 4000.

4) Un tout petit code VBA dans le module de la feuille Résultats pour actualiser les données du TCD et donc par ricochet celles du tableau résultat:

Private Sub Worksheet_Activate()
  Feuil2.PivotTables("TCD3").PivotCache.Refresh
End Sub

Formule matricielle: Elle doit être validée par la combinaison des touches Ctrl+Maj+Entrée au lieu de la seule touche Entrée comme une formule classique.
Si la validation matricielle est correcte, alors Excel entoure la formule d'accolades {=.......}.
Chaque fois que cette formule sera modifiée, la validation devra se faire par Ctrl+Maj+Entrée. Les accolades apparaissent à la validation et ne doivent pas être saisies au clavier.

Edit: v2 avec simplification de la formule en C1

 

[goldenboy]

Re : Tableau et données - comptage

Bonjour mapomme et merci de t'être penché sur mon problème.

je ne comprend pas vraiment les formules matricielles. J'ai essayé des valider avec Ctrl+Mal+Entrée, mais je n'obtient pas les résultats escomptés.
Voici ce que j'ai à l'ouverture du fichier et même après validation des formules matricielles :


C'est bizarre parce que sur ma tablette, j'avais cru voir des résultats pour toutes les catégories et notamment un résultat de 8 pour "cuisine" au lieu de 7 car vous n'avez pas pris en considération les codes valables pour chaque liste.

Ce problème viens peut-être du fait que je suis en version 2007 chez moi. Mais au boulot se sera en 2010.

cordialement.

 

[mapomme]

Re : Tableau et données - comptage

Bonsoir Goldenboy,

C'est quoi cette notion de "code valide" ?


On ne va s'en doute pas échapper au VBA...

 

[Staple1600]

Re : Tableau et données - comptage

Bonsoir à tous

mapomme: (salut )
Tu crois pas si bien dire
https://www.excel-downloads.com/threads/soustraction-cellule.215145/

PS: Personnellement je préfère le houblon au doublon ;op

 

[goldenboy]

Re : Tableau et données - comptage

Bonsoir a tous.

Il ne s'agit pas d'un doublon.
Le sujet final est le même effectivement c'est le même tableau que je rempli de commentaire.
Dans le sujet que Staple1600 fait mention, j'avais déjà réussi à créer mes commentaires mais il me fallait faire une comparaison de deux d'entre eux. Ce a quoi l'on m'a correctement répondu et que je test la semaine prochaine en réel.

La sur le sujet présent, je n'arrive pas a créer mes commentaires, soit les résultats que je souhaite obtenir en feuille résultats.

Mais la solution peut éventuellement être du même ordre que celle qui m'a déjà été donnée. C'est à dire l'utilisation de tableau.

Pour l'histoire de code :
Dans les données, j'ai plusieurs types d'objet (voiture, camion, fourchette etc..) que je regroupe en catégories principales. (Véhicules, cuisine etc...)
Les objets peuvent être de n'importe quelle provenance (ce que j'appelle code).
Et je souhaite savoir le nombre d'objets d'une certaine catégorie et pour une liste de provenance définie que j'ai reçu par jour.
Un exemple : je souhaite connaître le nombre d'objet de la catégorie véhicule et pour les codes 90935, 90940, 90944 et 90953 que j'ai reçu et ce pour chaque date où j'en ai reçu. Si j'ai reçu une voiture du code 90956, je ne souhaite pas comptabiliser. Sauf si je rajoute le code dans la liste 1.( qui est celle attribuée aux catégories 1 a 3).

J'espère être assez claire. Sinon je reste à votre disposition pour vous donner plus d'explications.

 

[laurent950]

Re : Tableau et données - comptage

Bonjour,

En passant par des variables tableaux je suis arrivé a ce resultat dans le fichier Excel joint.
J'ai ajouter en complément entre le Nombre et la date :
exemple 2 - Fourchettes - 31/12/2013
pour plus de lisibilité par contre j'ai pas encore fait le tri.

laurent

 

[goldenboy]

Re : Tableau et données - comptage

Bonsoir Laurent. Ce premier jet se rapproche de ce que je souhaite obtenir comme résultat.

J'attends avec impatience la suite.

Petite précision : si une catégorie principale n'a pas d'objet en résultat. Il faudra tout de même affiché un résultat à zéro.
Par exemple si je redoute une catégorie métier qui aurait les objets (dentiste, pharmacien et maçon), dans les données on voit bien qu'il n'y a rien. Le résultat à afficher sera donc "métier : 0".

Merci d'avance pour tout le travail accompli.

 

[laurent950]

Re : Tableau et données - comptage

Bonsoir,

Vous avez un fichier plus complet, et aussi juste ecrire le resultat a obtenir comme vous le souhaité dans la feuille resultat
cela est suffisant et finis le programme.

Ici je comprend pas :
Par exemple si je redoute une catégorie métier qui aurait les objets (dentiste, pharmacien et maçon), dans les données on voit bien qu'il n'y a rien. Le résultat à afficher sera donc "métier : 0".

Pourquoi affiché 0 (ici l'exemple serait un plus avec votre fichier joint au message)

laurent

laurent

 

[goldenboy]

Re : Tableau et données - comptage

Bonjour Laurent.

Voici un fichier avec les données modifiées et les résultats attendus.

Mes doigts on fourchés, il faut remplacer le mot "redoute" par "rajoute".

Dans le fichier joint, je n'ai pas rajouté de catégorie, j'ai simplement enlever des données pour que la catégorie Papeterie soit à zéro. Pourquoi je l'affiche alors qu'il n'y a rien ? Pour être sur qu'elle ait bien été comptabilisée.

J'ai modifié la disposition des codes en répétant sous chaque catégorie la liste des codes recherchés.

J'ai modifié le code de l'objet "voiture" (ligne 1 dans la feuille donnée) en "90956", pour qu'il ne soit pas compté et que l'on arrive bien à un total de 3 véhicules pour les codes indiqués sous cette catégories.

Il faut que les résultats respectent l'ordre d'inscription des catégories.
La première catégorie étant "Véhicule", son résultat doit être sur la ligne 1 etc...
C'est aussi pour cela que je laisse affiché les résultats à zéro : parce qu'après je exporter ces résultats dans un autre tableau.

Il faut que quelqu'un qui ne connait pas le code VBA puisse ajouter des catégories comme précisé sur la feuille paramètres : "nombre de catégorie limité au nombre de colonne XFD étant la dernière sur 2010"

Un truc du genre DernièreColonne = Sheets("paramètres").Range("A2").End(xlToRight).Column ou Sheets("paramètres").Cells(2, 1).End(xlToRight).Column

Je reste à disposition pour plus d'informations.

Merci beaucoup.

 

[laurent950]

Re : Tableau et données - comptage

Bonsoir,

J'ai finalisé le projet par contre j'ai pas fait le test pour les catégories qui sont vides.

PS : le module est applicable toute version excel

Pour info, les resultats de votre feuilles ne semble pas excate

Sport : 14
3 du 31/12/2014
3 du 01/01/2014
3 du 02/01/2014
5 du 03/01/2014

je trouve :

Sport : 16
5 du 03/01/2014
4 du 31/12/2013
3 du 01/01/2014
4 du 02/01/2014

Laurent

 

[goldenboy]

Re : Tableau et données - comptage

Bonjour,

Les résultats que j'ai indiqué sont justes.
Pour le sport, les codes recherchés sont 90935, 90940, 90944 et 90953.
Il y en a 16 au total dont 14 avec ces codes. les deux autres ont les codes 90957 et 90958 (lignes 27 et 28) et ne doivent donc pas être compatabilisés.

Le cas est le même pour les catégories Véhicules et Cuisines.

Dans les résultats, les dates doivent être dans l'ordre croissant et les catégories ne respectent toujours pas l'ordre de la feuille "paramètres".

Ma demande semble complexe mais j'ai l'espoir que vous trouverez une solution à toutes ces problèmatiques.

Au plaisir de vous lire.

 

[mapomme]

Re : Tableau et données - comptage

Bonsoir à tous,

J'avais commencé quelque chose, je l'ai fini, je le publie.

le résultat comprend le tableau des résultas et un tableau des anomalies.

Le tableau des classements et les listes sont disposées différemment.

Le tableau des classements posséde une 2ième ligne qui permet d'indiquer pour chaque catégorie quelle liste de validation est utilisée.

Le nom des listes est indiqué en première colonne. Les codes de la liste figurent ensuite en ligne à la droite du nom de la liste.

Le code a été placé dans le module de code de la feuille Feuil1.
Un peu de code a été placé dans ThisWorbook pour référencer la bibliothèque de MS scripting Runtime pour utiliser les dictionary.