[XL-2010] Compter les départements à partir de codes postaux [Résolu]

[texas2607]

Bonjour à tous
J'ai un fichier Excel 2010 nommé "Vente semaine 1.xlsx" puis un autre "Vente semaine 2.xlsx" etc... en fonction de l'avancement de l'année.
Bref, dans chaque fichier j'ai une colonne avec les code postaux en fonction de mes clients que j'ai vu courant de la semaine.
Je voudrais avoir un total par département.

Par exemple si j'ai fais 1 client en 26120, puis 2 en 07190 et 1 en 07110 alors avoir une cellule ou :
client 07 : 3
client 26 : 1
client 43 : 0

Précision : :
Les code postaux sont tous dans la zone B7:B36.
Je n'utilise que 3 département : 26 - 07 - 43 (enfin pour l'instant ... )

D'avance merci pour votre aide
Cordialement,
Fred

[Pierre Fauconnier]

Salut.

Tu veux le nombre de clients pour une semaine précise ou pour l'ensemble des semaines???

Quoi qu'il en soit, tu as intérêt à ramener toutes les infos sur une seule table, puis tu en fais ce que tu veux avec des formules telles SOMME.SI.ENS, SOMMEPROD ou encore en TCD.

C'est avant tout une question de conception de classeur, et cela impose que les saisies se trouvent dans la même feuille pour l'ensemble des semaines, et tu dois revoir la structure de ton classeur avant de vouloir continuer.

[corona]

Bonjour,
Je ne peux que surenchérir à ce que Pierre vient d'écrire.
J'allais proposer une solution avec SOMMEPROD et te proposer aussi de tout regrouper dans un seul classeur avec tout les mouvements sur une seule feuille. En 2010 tu disposes de plus d'1 million de lignes ce qui me semble pas mal pour un seul homme.
Avec cette méthode, tu peux créer autant de rapports que tu le souhaites. par clients, par années, par mois etc ...
Le rapport de tableau croisé dynamique en quelques clics te donne aussi tout ce que tu peux rêver d'avoir.
Un petit code pour ta demande.
En A1 se trouve le n° de département en 2 chiffres, précédé de l'apostrophe ex : '07

Code :

=SOMMEPROD( (A4=GAUCHE($D$2:$D$11;2)) * 1)

en $D$2:$D$11 les n° 07190, 26120 etc ... également précédé de l'apostrophe '07190

[texas2607]

Envoyé par Pierre Fauconnier

Salut.

Tu veux le nombre de clients pour une semaine précise ou pour l'ensemble des semaines???

Salut, et merci de votre rapidité,
Non en fait je fais une synthèse par semaine (d'ou les fichier "Vente semaine 1","Vente semaine 2"....) puis je dispose d'un autre fichier "RECAP" ou je recapitule toutes mes données :
> nombre de vente
> CA....
mais il me manque une chose c'est comment rapatrier ce calcul par département en partant d'un CP.

Je vous joint mes fichiers de travail, sachant que ce que je cherche c'est pour l'avoir dans le fichier RECAP en :
G8 H8 I8 pour la semaine1
G9 H9 I9 pour la semaine2 etc...
sachant que ceux existant pour l'instant sont renseignés en manuel.

Merci
Fred

[corona]

Bonjour,
Je reviens sur ce que j'ai écrit tout à l'heure.
Pour faire des rapports facilement que ce soit avec des fonctions ou avec un rapport de tableau croisé dynamique. Il faut que ta base de données soit enregistrée comme une liste et non pas comme un rapport.
Première ligne, le nom de tes champs et ensuite ligne par ligne tes mouvements et ce sans aucun totaux.
Sans quoi tu vas passer des heures et des jours à essayer d'alimenter ta feuille de RECAP.
De plus, tu as encodé comme 07240 (en B23 de la feuilel semaine 1) avec un format "code postal" or pour Excel c'est en réalité 7240 donc pour faire un calcul sur le département si tu prends les 2 caractères de gauche, tu vas avoir comme résultat 72 par contre évidemment pour le code postal 26120 là nous aurons bien comme résultat 26.
Tout est possible en informatique on pourrais bien sûr calculer que lorsqu'il y a 4 chiffres on ajoute un 0 mais cela deviendra vite un usine à gaz.
Bien repenser ta base de données est la voie à suivre et moi je mettrais tout sur une feuille.
J'ai fait de la vente pendant 10 ans et tout mes rapports de ventes, prévisions etc .. étaient fait avec Excel. J'ai toujours utilisé une seule feuille dans laquelle je mettais tout les mouvement de ventes et de prospections. Je pouvais au départ de cela tirer toutes les statistiques que je voulais et ce en quelques minutes.
De plus avec la fonctions SOMMEPROD, si tu organises bien ton classeur, tu peux avoir tes chiffres à jour dès l'ouverture du classeur en fonction de la date du jour.

[texas2607]

merci de ta réponse corona et je l'ai bien comprise,
en fait si j'ai plusieurs feuille semaine par semaine c'est que normalement dans son ensemble elles disposent de 3 onglets (que j'ai enlevé pour ce travail) mais où je renseigne :
>1 onglet pour mon rapport de vente semaine (l'onglet que tu as sur mes fichiers joint)
>1 onglet pour mon rapport véhicule (km, essence, péage...)
>1 pour mes prévisions pour la semaine à venir
et cette feuille je la transmet par mail à ma société en fin de semaine, et ce sous le format que tu as. A la boite ils reprennent la totalité des fichiers au niveau national et ils font leur sauce.

La feuille RECAP est une que je me suis faites afin de rapatrier toutes mes données, semaine par semaine, mois par mois et aussi sur les différentes années et ce effectivement sur 1 seule feuille.

Le problème c'est que je ne peux pas supprimer ni modifier mes feuilles "Vente semaine X" car elles partent au siège telles quelles.

D'où effectivement mes recherches et ce post pour rapatrier mes variables vers RECAP et ce, le plus correctement possible.

Comme tu as pu le voir sur mes fichiers joints j'ai déjà pu rapatrier toutes mes variables de ventes, CA, Financement... mais il me reste au moins 2 problèmes :
1> celui de ce post, nombre par département
2> et surtout, lors de la semaine 5, ou la 9 ou encore la 13 cela sera des semaines à cheval sur 2 mois. La 5ème semaine par exemple sera janvier et février. donc il faudra que je renseigne mes données de la feuille RECAP en tenant compte de ca. En partant du fichier qui sera "Vente semaine 5.xlsx" Une partie des données devra aller sur janvier et l'autre sur février.

Mais bon, ca je verrais après, chaque chose en son temps

Fred

[corona]

Bonjour,
Je comprends bien que tu doives remettre un document qui doit être identique pour tout le monde. C'est comme cela dans la plupart de toutes les sociétés.
Cependant rien ne t'empêche de faire ton encodage comme tu le souhaites et de créer une feuille ou plusieurs feuilles que tu remplis avec des fonctions ou à l'aide de VBA au gabarit souhaité par ton entreprise.
Tu pourrais faire administrativement en 10 minutes maximum ce que tes collègues feraient en 1 ou 2 heures et je te parle d'expérience.
Pour illustrer mes propos, j'ai mis en ligne un classeur contenant une feuille mvt qui reprend tout les mouvements de ventes, 2 feuilles Recap par clients et par articles basées sur la fonction SOMMEPROD et une autre feuille faites avec le rapport de tableau croisé dynamique.
Ce n'est qu'une infime partie de ce que l'on peut faire en partant d'une seule feuille.

[Pierre Fauconnier]

En fait, tu prends le problème à l'envers.

Tu pars d'une mise en page de présentation, puis tu veux traiter les données. Malheureusement pour toi, c'est dans l'autre sens que tu dois travailler.

Tu dois saisir les données dans une feuille qui contient tout, ce qui permet le travail statistique, et tu dois après, si on te demande une présentation spécifique d'un rapport, créer le rapport. La démarche est alors beaucoup plus simple à réaliser, mais elle demande évidemment une remise du fichier sur le métier, et je comprends que tu sois réticent.

De plus, l'utilisation d'un format spécial "code postal" fausse le traitement des données car le format affiche une valeur qui n'est pas celle traitée (cas des 9 premiers départements - et donc dans ton cas de l'Ardèche)...

Tu ne peux pas t'en sortir sans passer par la case conception si tu veux un travail professionnel et pérenne. Toute autre solution sera le bricolage d'une usine à gaz très difficilement maintenable. Nul doute que certains ici auront envie de s'y casser les dents, mais ce n'est pas te rendre service.

Et pour compléter Philippe, la version 2010 d'Excel te permet de travailler avec des tableaux (sélection de la plage des données puis Insertion > Tableau), ce qui te permet d'avoir:

• une plage dynamique qui grandit en fonction de tes données ;
• d'utiliser les intitulés des colonnes dans les formules en profitant de l'aide à la saisie (un pur bonheur).

[texas2607]

Ouaf, vous n'êtes même pas drôle...

corona
j'ai récupérer ton fichier et je commence à le regarder de près. Mais effectivement j'étais parti du mauvais coté. Mais c'est comme cela qu'on avance

Pierre Fauconnier
Merci de tes conseils mais comme tu le dis si bien, il est vrai que cela risque de devenir une usine à gaz.

en fait si j'ai bien compris, je part de ma feuille RECAP et ensuite je fais la création de la feuille "Vente semaine X" sur laquelle je peux rapatrier les infos nécessaires via des formules ou du VBA.
Mais dans tous les cas c'est RECAP qui contient les données.

Par contre, concernant le format code postal (qui a première vu n'est qu'une couche "visuelle") est ce que si je rentre mes codes postaux avec format personnalisé 00000 cela serait ok pour être repris dans des formules et ce correctement ou alors faut t'il les mettre uniquement en texte avec ' ?

Fred

EDIT : Par contre juste une autre question qui me vient a l'esprit.
Si effectivement je refais ma feuille RECAP en "maitre" et que j'utilise des formules ou/et du VBA pour remplir mes feuilles "Vente semaine X", lorsque je vais envoyer cette feuille par mail elle n'aura aucune donnée puisque pas de liaison avec RECAP ???

[corona]

Bonsoir,

Par contre, concernant le format code postal (qui a première vu n'est qu'une couche "visuelle") est ce que si je rentre mes codes postaux avec format personnalisé 00000 cela serait ok pour être repris dans des formules et ce correctement ou alors faut t'il les mettre uniquement en texte avec ' ?

Si tu utilises un format personnalisé pour tes codes postaux, tu verras effectivement le code proprement mais comme je te l'ai indiqué pour Excel une donnée formatée qui apparait comme 07120 a comme valeur réelle 7120. Il suffit pour t'en convaincre de sélectionner la cellule contenant cette donnée et regarder ce qu'elle contient. C'est vrai pour tout les formats. Ce que l'on visualise n'est pas la valeur réelle de la cellule.

EDIT : Par contre juste une autre question qui me vient a l'esprit.
Si effectivement je refais ma feuille RECAP en "maitre" et que j'utilise des formules ou/et du VBA pour remplir mes feuilles "Vente semaine X", lorsque je vais envoyer cette feuille par mail elle n'aura aucune donnée puisque pas de liaison avec RECAP ???

Lorsqu'on envoie à un tiers une feuille qui est liée à d'autres feuilles ou classeur(s) et que l'on ne veut envoyer que le résultat (la feuille ou une plage de la feuille)
On fait un copier de la partie à envoyer et ensuite un collage spécial Valeur et format pour garder la mise en forme. Ainsi seul les données sont envoyées sans calculs ni liaisons.
Ce collage spécial peut bien entendu être fait sur un autre classeur.

[texas2607]

Bon ben il y a du boulot..

En tout cas, Corona et Pierre Fauconnier, merci pour tout et à bientôt.
Amicalement
Fred