COUNTIF avec plusieurs plages variables

Bonsoir à tous,

Chaque semaine, j'importe des données depuis un logiciel externe pour Excel : j'ai réussi par macro à toujours lui donner la même structure : lignes vides entre les différentes CSP comme vous pourrez le voir sur le fichier joint.

Par contre, je ne vois pas comment réaliser un COUNTIF qui s'ajustera sur chaque CSP automatiquement (+ ou - de lignes sur chacune en fonction de chaque semaine.

Merci de votre aide.

Bonne soirée

Cordialement

Bonjour,

Manifestement, tu effectues grâce à ta macro un traitement qui conduit à la mise du tableau final ...

Au cours de ce processus, tu pourrais attribuer des noms à tes différentes plages ...

Du coup, il te suffirait de reprendre ces noms dans tes formules ...

Bonjour le forum, bonjour James,

Suite à ta réponse, j'ai essayé d'avancer par rapport à tes remarques.

Comme tu l'indiquais, je confirme que des macros ont pour but de mettre en forme le tableau final. Débutant en VBA, j'ai découvert après recherche et à l'aide de l'enregistreur de macros comment nommer des plages (chaque plage au nombre de 7, étant délimitée par une ligne vide au dessus et au dessous). Bien entendu, ces plages seront variables en fonction du fichier importé mais toujours délimitées en haut et en bas par une ligne vide à chaque extrémité.

Toutefois, mon problème à l'heure actuelle demeure le suivant :

je ne peux nommer des plages (noms des personnes) que sur la colonne A : je suis sûr qu'il n'y aura pas de cellules vides sur cette dernière à l'intérieur de la plage. Ce n'est pas le cas en revanche pour la colonne B (Jour 1) jusqu'à l'avant avant dernière colonne(Jour 31).

J'ai donc nommé ces différentes plages à partir de la colonne A alors que je dois calculer avec la fonction NB.SI : " pommes, raisins, poires etc " sur chaque colonne, de la colonne (2) à la colonne (30 ou 31) par exemple : je voudrais donc pouvoir me servir du nom de chaque plage pour être sûr que le calcul à partir de NB.SI s'ajuste automatiquemenrt en fonction de la plage nommée.

J'ai essayé par la suite de me référer à une fonction que je ne connaissais pas : "DECALER", à tort ou à raison, je ne sais.

Mais de toute façon, sans succès malheureusement.

Si l'un d'entre vous pouvait m'apporter son aide, je l'en remercie dès à présent, car je tourne en rond depuis un bon moment...

Dans l'espoir de vous lire bientôt

Cordialement

Re,

Peux-tu joindre ton dernier fichier de travail ... avec les macros ...?

Il me semble qu'il suffit de donner un nom à tes plages ... pour que le problème soit résolu ...

Bonjour James, bonjour à tous,

Merci tout d'abord de te pencher sur mon problème.

Je te joins le fichier initial sur lequel tu pourras faire tourner la macro : débutant en VBA, tu trouveras sûrement le code un peu lourd

A bientôt de te lire

Merci encore

PS :ce n'est qu'un détail sans grande importance pour la suite mais je ne m'explique pas pourquoi l'avant avant dernière colonne (vide) n'est pas supprimée

Bonjour,

Merci pour la maquette ...

Voilà qui est clair comme du .......

Première question, tes quatre dernières macros fonctionnent-elles comme tu le veux .... et créent-elles les noms de plages que tu demandes ...?

Bonjour,

Tu as raison, James, les macros InfNS et EduNS sont instables : elles fonctionnaient depuis mon ordi perso (mac excel 2011) mais effectivement sur celui du travail (windows excel 2002) les noms sud et nord "se mélangent".

Par contre, les noms des plages sont corrects : j'ai interverti pour cela l'appel des macros de la façon suivante :

Call inf

Call edu

Call technique

Call personuit

Call InfNS

Call EduNS

et modifié la bévue sur la plage6 comme ci-dessous

   MsgBox tech.Address
    Set maplage6 = Range(tech.Offset(1, 0), tech.Offset(1, 0))
    MsgBox maplage6.Address
    Range(maplage6, maplage6).Name = "Tech"
        Set pr5 = tech.Offset(1, 0)

Les adresses msgbox sont destinées à être enlevées, c'est juste un contrôle pour moi pour m'assurer de la hauteur des plages nommées.

Je n'ai pas été clair semble-t-il par rapport à mon attente et le code certes est très brouillon : galère après galère pour arriver à quelque chose

Je voudrais pouvoir me servir de la hauteur des plages nommées (sachant qu'elles varieront en fonction des mois) mais je suis sûr de ne pas avoir de cellules vides en début ou en fin de plage sur cette colonne A. Alors que toutes les autres colonnes peuvent en avoir : et le calcul à partir de countif ne serait plus juste.

C'est pourquoi je pensais pour le calcul dans chaque colonne représentant le jour à une combinaison countif-offset : mais là pb, je bloque sévère...

A bientôt

Merci encore

Bonjour,

Je crois qu'effectivement ... tu te compliques la vie inutilement ...

Deux pistes de réflexion :

en fonction du fait que les noms des plages (comme actuellement définis ...) te servent à d'autres usages que le Countif() ou pas ...

1. Profiter de ta macro pour insérer 31 colonnes dans les noms des plages

ou

2. Ajouter six nouveaux noms de plages ... qui utilisent les précédents et qui ( avec décalage ..) engloblent l'entièreté des plages

Dans les deux cas, tu finis avec des noms de plages sur lesquelles il devient très facile tous tes Countif() ...

Bonjour à nouveau,

Pour répondre à ta question, les noms de plage ne me serviront qu'à des countif.

Pour autant, simple pour toi sûrement, pas forcément pour moi.

J'ai essayé de créer une plage avec nouveau nom de la façon suivante :

plage5=maplage5.offset(0,1) puis je lui ai donné un nom(NuitCalcul).

Le "Countif" avec nom de la nouvelle plage (NuitCalcul) sur la colonne B pas de problème, ça fonctionne. Mais comment faire sur les colonnes suivantes? Je pense qu'il y a plus simple que des: offset(0,2) puis (0,3) et ainsi de suite pour en avoir 30 ou 31 selon le mois.

Mais d'ailleurs, dans le cas où ces plages ne me servent qu' à effectuer un countif, quel choix vaut-il mieux retenir à ton avis : 6 ou 31noms?

Sans vouloir abuser, peux-tu m'expliquer pour quelle raison les macros (cf fichier déjà envoyé) InfNS et EduNS n'ajoutent pas les noms dans l'ordre voulu : est ce un problème avec xlpart ou xwhole? Où est ce que le problème est plus profond que ça.

Merci de ton aide

A+

Re,

Je me suis surement mal exprimé ...

Tu as six noms de plages qui sont automatiquement et correctement créées par tes macros ...

Dans la mesure où tu n'en as besoin que pour les countifs() ... il te suffit de modifier le nombre de colonnes à 31 ...

Ensuite, tes formules countif() fonctionneront pour tous les jours ...de 1 à 31 ... non ?

Est-ce-que j'ai loupé une étape ou une contrainte ?

Ré,

Tu dois être clair mais je n'arrive pas à comprendre...

Que veux tu dire par modifier le nombre de colonnes à 31?

Les 6 plages nommées automatiquement sont référencées sur la colonne A seulement.

Si je modifie la colonne à 31 alors la plage va devenir : A5:AF11

Maintenant, si je veux calculer le nombre de "M",ou de "AMI" etc. sur la seule colonne B, puis sur la seule colonne C, puis sur la seule colonne D etc..... la formule me renverra le nombre total de M ou AMI sur la totalité de la plage A:AF, non? Or, je veux le calcul jour par jour et qui se fera par macro dans le tableau qu'il me reste à créer pour récupérer chaque M ou AMI à partir de noms de plage préalablement créé par la macro.

La plage dynamique nommée sur la colonne A me garantit sur sa hauteur l'exhaustivité de l'effectif contrairement aux colonnes suivantes qui peuvent comporter des blancs.

J'imaginais donc la formule countif() avec la hauteur de la plage nommée en colonne A, INF (A5:A11) avec celle nommée en colonne B (jour1) à partir de la colonne A :INFCALC= INF(offset 0,1) pour compter les M ou AMI du jour concerné. Et ainsi de suite pour les jours suivants. Mais comment faire pour les colonnes suivantes car INFCALC est en références absolues sur $B&5:$B&11 : je veux m'épargner d'avoir à modifier manuellement les $ de chaque nom généré automatiquement mais que la macro puisse me faire les calculs directement à partir des noms (et je voudrais éviter de créer 31 plages).

Est ce plus clair au niveau du calcul? Etais-tu déjà dans cette logique, au quel cas je ne comprends pas comment créer mes formules countif()

A+

Re,

Tu as entièrement raison ...

Mais pour moi ... bien qu'aucune date n'apparaisse ... tu bosses sur un planning mensuel ... et je pensais qu'avec un bloc de 31 colonnes, tu couvrais automatiquement la totalité du mois ... non ?

Ré,

En fait, j'ai besoin de connaître les besoins journaliers (par ex si countif sur "M" et pour J1 me restitue 0 (zéro)) alors une MFC à venir me donnera une alerte par rapport au manque d'effectif.

Mais je n'en suis pas là... car je bloque sur countif() ou plus exactement comment créer automatiquement une plage nommée avec offset de (0,1) à (0,31) pour les 31 jours par rapport à celle de A : $A$5:$A$11.

As tu une idée car je sèche dur !

A+

Re,

Je commence à saisir l'ampleur de ton défi ...

Ci-joint ton fichier test ... avec une piste de réflexion et un exemple ...

En espèrant que cela t'aide ...

Bonjour à tous, bonjour James,

Merci, James pour ton envoi qui me servira sûrement de support à un autre travail.

Je pense, James, après réflexion, que c'est toi qui a raison depuis le départ.

J'ai une plage nommée TOTO en $A$5 :$A$11 sur la colonne 1 : je précise que cette plage est automatiquement délimitée suite à une recherche avec cell.find (cette plage pourra varier) à laquelle j'attribue le nom"TOTO".

J'ai absolument besoin de récupérer cette plage pour sa hauteur (qui peut varier) pour faire un calcul dessus avec NB.SI : le problème c'est qu'elle s'inscrit en références absolues ($) pour les lignes et les colonnes.

Or j'aurais besoin de la transformer comme cela : A$5 : A$11 à partir de la programmation en VBA directement (sans avoir à la modifier à partir de Insertion==>Nom).

Existe t-il une façon de faire cette transformation : $A$5 : $$A$11 vers A$5 : A$11 à partir de VBA uniquement ???

Si l'un d'entre vous peut me dépanner

Merci d'avance

Cordialement

A+

Bonjour,

Dans la mesure où tu n'as pas de Dates dans les entêtes de tes colonnes ... Comment comptes-tu identifier le bon nombre de colonnes nécessaire au bon fonctionnement de tes Countifs() ... ?

Ré,

A vrai dire je n'ai pas arrêté de solutions particulières jusqu'alors (peut-être à tort). L'important était pour moi de pouvoir récupérer les jours de la semaine en L1 (après suppression des colonnes vides).

Par rapport à la date, j'envisage 2 pistes :

A1 : "Autres" remplacé par mois de l'extraction et génération de la date sur la ligne 2 - je n'ai pas étudié plus avant la question par rapport à mes petites compétences

Ou sans remplir A1, se positionner sur L2C2 et à l'instar de la fonction : si(L1<>""; date;"") : voilà l'idée mais les dates apparaîtront sur la ligne 2 (le format est à voir).

Par contre, James, je ne comprends pas la relation entre date et countif dès lors que la ligne 1 est remplie : peux tu m'en dire plus sachant que mon souci est de pouvoir programmer un offset pour la colonne de la plage nommée TOTO : $A$5 :$A$11 en A$5 : A$11 puis offset (0,1). Avec le blocage des lignes, je suis sûr d'avoir la bonne hauteur de la plage pour effectuer chaque countif. Si cela est possible, je crois le problème au moins en grande partie résolu : le nb.si (TOTO; critère) se fera sur la bonne colonne ne serait ce que par copier coller !

Mais comment transformer cette plage en référence mixte, là je sèche toujours car l'adresse de la msgbox n'a qu'une valeur informative.

Vos idées seront les bienvenues sur la possibilité de modifier ainsi la plage

A+

Re,

La première question que j'aurais dû te poser dès le départ :

As-tu la main sur la construction de ta requête Crystal Reports ?

Si j'ai bien compris, tu veux moduler tes formules de Countif() par date ... or chaque colonne est bien un jour donné ... non ???

Dans ma compréhension de ton rapport, chaque colonne est un jour du mois ... donc une date ...!!!

Me trompe-je ???

Bonjour James, bonjour à tous,

Sur la requête, j'ai la main en partie seulement, le masque initial avec les données m'est imposé dans sa forme : pas de date sauf lu, ma, me..di et que je dois réajuster car crystal me génère des colonnes vides et des espaces... Par contre, je peux sélectionner les catégories professionnelles mais là je dois aussi refaire des réajustements : utilité des premières macros.

Countif par date : à vrai dire, tout dépend ce que tu entends par là. La date n'est pas ma préoccupation première (peut-être existe-t-il une astuce que je ne connais pas) : en fait c'est plutôt chaque colonne qui m'intéresse (donc relation avec jour inévitablement, tu as raison) mais l'important c'est qu'à partir de la colonne2, je puisse faire les countif sur les différentes ruptures pour lesquelles j'ai nommé les plages : et avec countif associé au nom de la plage (INORD, ISUD, ENORD etc), je veux dénombrer dans un tableau les : "M", AMI ou AME, N (etc...) de chaque catégorie donc Ex de COUNTIF INORD,"M"). Mais le big Problème c'est que INORD récupéré par VBA avec end.XLdown est en références absolues. Alors j'ai nommé dans VBA à partir de INORD, INORDA pour la colonne 2 (offset de "INORD). J'ai donc une nouvelle plage INORDA pour la colonne 2 et là, je peux faire les calculs voulus(sur la colonne 2 avec INORDA dont la plage est référencée sur colB et non plus sur colA comme avec INORD. Mais INORDA (attribué dans VBA) est aussi et toujours en références absolues ET LORS DE LA COPIE de la colonne 2(col B) vers la 3(colC) LE RESULTAT EST FAUX : le countif avec INORDA se réfère à la plage nommée(données) en col2 et pas celles sur la col C (problème des références absolues lors de l'adressage des plages et des noms associés). Selon cette méthode, il faudrait que je nomme pour chacune des colonnes suivantes une plage(variable) avec offset à partir de la précédente et la nommer, et ainsi de suite pour les tous les jours suivants.

C'est ce que je veux éviter de faire dès lors que l'adressage pourra être modifié de références absolues vers références mixte (libération de la colonne en supprimant le $) : Et là est toute la question, comment faire?

J'ai essayé de te décrire du mieux possible les différentes étapes successives et espère de ne pas "t'avoir perdu " dans une foule de détails.

En tous cas, merci de l'intérêt que tu portes à ma demande

Cordialement

Bonjour,

Il faut que je consacre un moment ... à tête reposée ... à ton dossier et à tes contraintes ... en reprenant toutes tes macros ...

Dès que j'ai un moment, je replongerai dedans ...

Je te tiens au courant ...