XL 2016 Calculer les présences en "Seul" ou "Accompagné" en tableau 1 et présenter la synthèse en tableau 2

[Webperegrino]

Bonjour Le Forum,

Ci-joint un fichier qui me fait exploser la tête depuis des heures et des jours de recherches, et ci-après mes explications du problème rencontré pour lequel je vous serais reconnaissant d’apporter une solution satisfaisante.

Le bouton rouge « CALCULER » situé dans la feuille «CALCUL TablCarré » démarrage une analyse de la feuille ORIGINE par VBA.
Une à une, les cellules de « ORIGINE » sont placées dans la cellule [F6] de la feuille « CALCUL TablCarré ».
Les formules magiques que j’ai placées en pavé [H4:K43 ] "éclatent" le groupe de prénoms de cette cellule.
Le résultat qui en découle s’affiche en [B6:8] par comparaison des prénoms qui ont servi à cette analyse

Pas de souci jusque-là.​

Le VBA, par "balayage" dans DESTINATION, pour le premier dans les colonnes [C4:C4] et dans la colonne [B5:B109] pour le deuxième prénom, exploite le résultat [B6:B8] de la feuille « CALCUL TablCarré » qui indiquer si le premier prénom était seul, ou s’il est en accompagnement du deuxième prénom.

Jusqu’ici mon VBa fonctionne encore bien.​

Le hic se place dans le tableau DESTINATION pour le placement des résultats de l’analyse.
1.
Analysons pour ANNE, tant en abscisses qu’en coordonnées du tableau DESTINATION.
Ça donnera de bons résultats :
ANNE est 15 fois avec un autre prénom (en groupe dans ORIGINE)
ANNE apparaît bien 3 fois seule dans les cellules d’ORIGINE. C’est indiqué grâce à la formule magique de Job75 en cellule G2, et aussi par Vba en G9.
Au départ je ne désirais pas ce résultat en G9, c’est la macro qui le place donc… J’aurai préféré garder la diagonale du tableau grise et sans valeurs à l’intérieur, mais mes lignes de codes ont décidé autrement…

2.
Passons à ALAIN :
RIEN NE VA PLUS ! LE JEU N’EST PAS BON !
ALAIN est 2 fois avec un autre prénom (en groupe dans ORIGINE)
ALAIN apparaît SEUL 3 fois dans les cellules de « ORIGINE ».
MAIS Indiqué 5 fois par la formule de Job75 en cellule C2, et 5 fois en C5.

3. (sujet moins grave car j'ai trouvé une parade "système D à la McGuyver"...
Pour le prénom MICHEL, dans la liste où il y a aussi MICHELLE :
Pourquoi le prénom MICHEL est compté avec MICHELLE si je ne place pas, en parade, un point juste derrière MICHEL [MICHEL.] au lieu de [MICHEL] ?
Comment rectifier le VBA pour cela ?

4.
Enfin, ma VBA analyse et affiche le résultat…. seulement au bout de 22 à 24 minutes de calculs ! C'est beaucoup.

Auriez-vous plus rapide à me proposer ?
Merci pour votre aide précieuse.

Webperegrino

 

[Webperegrino]

Bonsoir-Bonjour Le Forum,

Je reviens ici avec le fichier joint : j'ai pu y progresser un peu.

En feuille DESTINATION, après les calculs faits par la feuille du milieu du fichier, les lignes 1, 2 et 3 me donnent maintenant les présences correctes "SEUL", "ACCOMPAGNÉ", "TOTAL DE PRÉSENCE".

En lignes 112/113, j'ai conservé la belle formule de Job75 ; mais les résultats diffèrent de la réalité, dommage... J'ai donc placé un formule, variante, plus proche du réel pour la récolte des trois critères ci-avant indiqués.

En codification VBA, ma deuxième partie me demande de patienter quand même 35 minutes de calculs avec l'écran bloqué pendant ceux-ci.

J'ai le souhait que l'un d'entre vous réussisse à me proposer une solution de programmation donnant un laps de temps de calcul plus restreint.

Merci pour votre aide,
Webperegrino

 

[TooFatBoy]

Bonjour,

1.
Analysons pour ANNE, tant en abscisses qu’en coordonnées du tableau DESTINATION.
Ça donnera de bons résultats :
ANNE est 15 fois avec un autre prénom (en groupe dans ORIGINE)
ANNE apparaît bien 3 fois seule dans les cellules d’ORIGINE. C’est indiqué grâce à la formule magique de Job75 en cellule G2, et aussi par Vba en G9.
Au départ je ne désirais pas ce résultat en G9, c’est la macro qui le place donc… J’aurai préféré garder la diagonale du tableau grise et sans valeurs à l’intérieur, mais mes lignes de codes ont décidé autrement…

Faux : Anne n'apparaît que 3 fois au total : 3 fois accompagnée et 0 fois seule.
La formule de Job75 te donne bien le total de 3.

2.
Passons à ALAIN :
RIEN NE VA PLUS ! LE JEU N’EST PAS BON !
ALAIN est 2 fois avec un autre prénom (en groupe dans ORIGINE)
ALAIN apparaît SEUL 3 fois dans les cellules de « ORIGINE ».
MAIS Indiqué 5 fois par la formule de Job75 en cellule C2, et 5 fois en C5.

Exact : Alain apparaît 5 fois au total : 2 fois accompagné et 3 fois seul.
La formule de Job te donne bien le total de 5.

3. (sujet moins grave car j'ai trouvé une parade "système D à la McGuyver"...
Pour le prénom MICHEL, dans la liste où il y a aussi MICHELLE :
Pourquoi le prénom MICHEL est compté avec MICHELLE si je ne place pas, en parade, un point juste derrière MICHEL [MICHEL.] au lieu de [MICHEL] ?
Comment rectifier le VBA pour cela ?

Ta macro compte "Michelle" avec "Michel" parce que tes formules en B6 et B7 de la feuille "CALCUL TablCarré" recherchent "*Michel*" et que "Michel" et "Michelle" répondent tous les deux à ce critère.
La formule de Job donne bien le total de 3.

4.
Enfin, ma VBA analyse et affiche le résultat…. seulement au bout de 22 à 24 minutes de calculs ! C'est beaucoup.

C'est normal que ta macro prenne un temps quasi infini : d'abord tu n'arrêtes pas d'écrire dans la feuille "CALCUL TablCarré" au lieu d'utiliser des variables en mémoire, en suite la logique de ton code n'est peut-être pas optimale.

 

[TooFatBoy]

Une proposition en pièce jointe.


ps : j'ai fait du ménage dans tes MFC...
J'ai laissé la MFC ="""MOD(LIGNE(A65508);2)""" mais elle ne sert à rien.

 

[Webperegrino]

Bonjour Le Forum,
Bonjour TooFatBoy,
Merci beaucoup pour toutes ces explications.
En effet j'ai décidé de garder la formule de de Job75 car elle donnait bien un total.

Toutefois il faut bien tenir compte de ANNE qui est seule en AV29 :
accompagnée dans 3 cellules ET seule 1 fois dans cette cellule.

Quand à trouver une technique de variables pour remplacer les recherches du pavé [H6:K43] de la feuille intermédiaire de calcul pour constituer en final la présentation du pavé [C6:C110] de feuille DESTINATION, ça je ne sait faire autrement et suis bien conscient de la lourdeur de ma programmation...
S'il n'y a pas moyen d'aller plus vite dans les calcils je resterai avec cette méthode de débutant qui fournit un résultat de comptage correct.

Merci beaucoup, TooFatBoy, pour votre intervention ; quant à moi je continue à fureter sur le web pour trouver aussi une autre méthode pour un affichage final plus rapide dans DESTINATION en C6:C110.
Cordialement,
Webperegrino

 

[TooFatBoy]

suis bien conscient de la lourdeur de ma programmation...

Bienvenue au club des joyeux faiseurs de code lourd. Je me sens moins seul.

S'il n'y a pas moyen d'aller plus vite dans les calculs je resterai avec cette méthode de débutant qui fourni un résultat de comptage correct.

Ma proposition en #4 est un peu moins lente, mais je ne sais pas si elle fait exactement ce que tu veux.

 

[Webperegrino]

Le Forum,
Merci TooFatBoy,
Merci encore pour ton intervention et ton aide.
Je vais étudier ta solution dès que mon planning de journée sera moins dense.
J'ai hâte de m'y pencher et de l'expérimenter.
Merci et bonne journée, ensoleillée comme ici je l'espère. Bon apéro... moi je trinquerai à ta solution.
Webperegrino

 

[Webperegrino]

Le Forum,
TooFatBoy,
Je suis impressionné par ce que tu viens de m'expédier : quelle rapidité, cette macro !

Dans la version n°2 que voici, j'ai redéfini la dénomination de B2 (Job75) : il s'agit bien du nombre de groupes (les cellules où le prénom apparaît).

J'ai aussi corrigé la formule en C1 et l'ai glissée vers la droite.

Mais ALAIN me donne un drôle de résultat en C1 et en C2 alors que pour le cas de ANNE c'est parfait en C1 et C2.
Toutefois si j'ajoute ANNE en AU29, par exemple, SEULE, elle sera aussi comptée comme "Accompagnante" en C1 : donc pas normal, la première ligne de comptage devient fausse.

En tout cas la répartition des accompagnants dans le tableau semble parfaite. Ton travail est fantastique !

Je vais essayer de transposer ta solution dans le fichier du post #2 et reviens vers toi.
Merci
Webperegrino

 

[TooFatBoy]

La macro que j'ai pondue ne s'occupe que de remplir le tableau. Je n'ai pas regardé tout ce qu'il y a autour.

 

[Webperegrino]

Le Forum,
Merci TooFatBoy,
Parfait ! Pour moi, c'est déjà énorme ce que tu as accompli.
Webperegrino

 

[Webperegrino]

Le Forum,
TooFatBoy,
Ci-joint le fichier #2 rectifié et adapté selon tes lignes de programmation.
Je n'ai plus rien à ajouter à ceci : c'est parfait ! Tout fonctionne bien dans ce dernier.
Les résultats annoncés répondent parfaitement à ce que j'espérais.
J'ai même enlevé la feuille du milieu devenue inutile grâce à ton VBA super-rapide.
Le bouton rouge CALCUL a été reporté en feuille DESTINATION et ça fonctionne.
Il me faut vraiment me lancer dans l'apprentissage des variables comme tu l'as fait si bien ici.
Merci TootFatBoy,
Cordialement,
Webperegrino

 

[Webperegrino]

Le Forum,
J'ai tout recopié dans mon fichier de travail "usine à gaz" de 3855ko à l'identique des lignes de Sub MonCalcul().
A la ligne

  With Sheets("DESTINATION").Range("B5").Offset(x, y)

je me retrouve avec le message erreur d'exécution de type '13'.
D'où peut provenir le problème s'il vous plait ?
Merci
Webperegrino

 

[TooFatBoy]

Est-ce que ton vrai fichier est très différent du fichier de #11 ?


Je viens de survoler rapidement le fichier de #11 : tu devrais faire du ménage dans les MFC, comme je l'ai fait pour le fichier de #4.

 

[TooFatBoy]

Bon, j'ai toujours un message ou deux de retard, mais je te réponds quand même...

Mais ALAIN me donne un drôle de résultat en C1 et en C2 alors que pour le cas de ANNE c'est parfait en C1 et C2.

Tes formules en ligne 1 ne sont pas toutes identiques.
En C1 je te propose ceci, à tirer vers la droite :

=SI(SOMME(C5:C109)=0;"";SOMME(C5:C109)-DECALER(C5;COLONNE(C5)-3;0))

 

[Webperegrino]

Non TooFatBoy, les deux feuilles sont identiques, j'ai toutefois ré-identifié dans mon vrai fichier le nom de ORIGINE par son vrai nom PANORAMIQUE dans les lignes de codes avec Sheet("....")... et en vérifiant bien ligne après ligne.
Que signifie MFC s'il te plait ?
Je vais voir où je peux faire le ménage.
Merci
Webperegrino