XL 2016 [RESOLU]Remplacer fonction indirect

[Riko]

Bonjour et meilleurs vœux tardifs.
Je souhaiterais remplacer la fonction indirect qui semble me ralentir mon fichier (sauf erreur de ma mart)
En effet je souhaite compter le nombre de cellules contenant un texte spécifique selon le mois et l'année et pour chaque personne puis afficher le résultat dans une autre feuille.
Lorsque le fichier devient plus important (10 ans et 30 membres environ), ça se complique et le fichier devient très lent
ci-joint le fichier exemple
Merci d'avance pour votre aide.

 

[chris]

Bonjour

Ta formule de numéro de semaine est fausse : si elle semble OK sur 2020, elle ne marchera pas sur 2021 et suivantes

Utilise en 2ème argument 21 ou bien la fonction SEMAINE.ISO

Une solution POwrerQuery (intégré à Excel)

J'ai légèrement adapté le tableau source pour créer une plage nommée Data traités par PowerQuery puis un TCD

Il suffit de cliquer sur Données, Actualiser Tout pour mettre à jour après saisie/modif dans la source

On peut éventuellement utiliser une ligne de VBA pour automatiser l'actualisation quand on active l'onglet de synthèse

 

[Riko]

merci de ta réponse, je n'avais pas pensé au TCD car je ne les ai pas bien utilisés.
POwrerQuery je ne sais même ce que c'est
je regarde ça de plus près si je peux adapter le reste du tableau final.
Merci encore et bon dimanche. Si je ne reviens pas encore demander de l'aide.

 

[mapomme]

Bonjour @Riko , @chris ,

Une simple formule suffit à condition d'avoir un tableau qui s'y prête . Regardez le fichier joint.

Feuille "Decompte":

• on a dé-fusionné les cellules des en-têtes puis introduit de nouvelles formules simplissimes (voir formule en C3 recopiée jusqu'en D3 et formule en E3 recopiée vers la droite)
• on a formaté les cellules B3, C3 et D3 puis recopié les formats de B3: D4 à droite
• ainsi chaque colonne de "Decompte" contient en ligne 3 le mois idoine

Ensuite :

• En cellule A5, on a saisi une formule qui recopie le premier nom de la feuille "Calend
• En cellule B5, on a saisi une formule calculant ce qu'on désire
• La cellule B5 a été formaté avec un format spécialisé
• Puis on a recopié cette formule et ce format vers la droite

Il suffit de recopier/tirer la ligne A5: P5 vers le bas jusqu'à obtenir des lignes vides.

La formule de comptage en B5 est :

=SI($A5="";"";SOMMEPROD(--(Calend!$D13:$ZZ13=B$4)*(TEXTE(Calend!$D$2:$ZZ$2;"aaaamm")=TEXTE(B$3;"aaaamm"))))

nota: modifiez ZZ dans la formule à votre convenance pour ne pas prendre trop de colonnes inutiles ou prendre en compte toutes les colonnes nécessaires.

 

[zebanx]

Bonjour Riko, Mapomme, Chris, le forum

@mapomme
On ne peut pas rajouter une condition supplémentaire sur le nom stp ?
Je butais totalement sur le mois() par contre qui me mettais systématiquement sommeprod(--) en erreur, la fonction texte semble parfaitement remplir l'affaire.

SI($A6="";"";SOMMEPROD(--(Calend!$D$13:$ZZ$17=C$4)*(TEXTE(Calend!$D$2:$ZZ$2;"aaaamm")=TEXTE(C$3;"aaaamm"))*($A6=Calend!$A$13:$A$17)))

Bon sujet avec l'utilisation de sommeprod sur une autre feuille.
@+

 

[Riko]

Merci, je regarde en détail tout ça.
@zebanx sauf erreur de ma part il n'y a pas de formules matricielle sur la 1ere colonne et il n'y a rien en feuil1.

 

[mapomme]

On ne peut pas rajouter une condition supplémentaire sur le nom stp ?

La méthode justement tente d'éviter le recherche du nom . C'était voulu.

 

[Riko]

oula, je ne vous suis plus. (Je vais rester sur le post de mapomme et tenter de comprendre)
Il me faut un peu de temps, j'ai le "cerveau lent"
Puis il faut que j'adapte pour mon utilisation car sur mon fichier final je faisais

=(NB.SI.ENS(INDIRECT($D5);"am";INDIRECT($C5);">="&FIN.MOIS(AI$2;-1)+1;INDIRECT($C5);"<="&FIN.MOIS(AI$2;0)))/2 +
(NB.SI.ENS(INDIRECT($D5);"aa";INDIRECT($C5);">="&FIN.MOIS(AI$2;-1)+1;INDIRECT($C5);"<="&FIN.MOIS(AI$2;0)))/2 +
NB.SI.ENS(INDIRECT($D5);"A";INDIRECT($C5);">="&FIN.MOIS(AI$2;-1)+1;INDIRECT($C5);"<="&FIN.MOIS(AI$2;0))

 

[Riko]

je commence à comprendre un peu mieux votre fichier.
je rencontre plusieurs problèmes mais le plus embêtant et que les noms ne sont pas tous à la suite, il y a des lignes entre les noms.
Cela fonctionne en modifiant les formules manuellement, la copie étirée ne fonctionne plus.

 

[mapomme]

Re @Riko

Quand on répond, on indique à qui on répond .

Quand on fournit un fichier, le minimum requis est qu'il reflète le véritable fichier !!!!!!!!!!!!!!!

 

[Riko]

@mapomme
oups. désolé.

je vais tenter de refaire un fichier qui sera plus prêt de la réalité sans les données réelles

 

[Riko]

Voici le fichier que j'aurais dû vous présenter au début de mon post.

 

[zebanx]

Re-

Un essai en f2.
Quelques remarques :
- De mémoire, INDIRECT n'aime pas les " " ou les "-" dans le nom des feuilles (le nom de la première feuille a été modifié)
- Il ne semble pas y avoir que 3 codes sur la première feuille. Il y a absence "A" mais aussi des absences partielles.
Il conviendrait de créer des colonnes pour chaque item puisque chaque code semble avoir une importance relative, autant ne pas les rassembler
- La formule reprend ma compréhension depuis le code présenté en #5 qui reprend lui même grandement celui indiqué par Mapomme #4

 

[Riko]

bonjour,
@zebanx
merci de ta réponse, je ne peux pas regarder en détail avant mercredi soir.

Il ne semble pas y avoir que 3 codes sur la première feuille. Il y a absence "A" mais aussi des absences partielles.
Il conviendrait de créer des colonnes pour chaque item puisque chaque code semble avoir une importance relative, autant ne pas les rassembler

Oui en effet, je vais faire plusieurs colonnes car en plus lorsque ce sont des absences partielles, je dois /2 le résultat.
je vais créer 3 plages nommées dynamiquement pour remplacer

calend!$C$13:$RT$60

calend!$C$2:$RT$2

calend!$A$13:$A$60

De mémoire, INDIRECT n'aime pas les " " ou les "-" dans le nom des feuilles (le nom de la première feuille a été modifié)

Merci de l'info.

 

[zebanx]

Bonjour Riko

Pour confirmation sur indirect, parce que tu sembles un habitué de son utilisation.

Bonne soirée
zebanx