XL 2016 [RESOLU]Remplacer fonction indirect

chris

XLDnaute Barbatruc

• 24 Janvier 2021

• #2

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

 

Pièces jointes

• TestRiko_PQ.xlsx

  30.7 KB · Affichages: 10

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #3

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.

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 24 Janvier 2021

• #4

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 :

VB:

=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.

 

Pièces jointes

• Riko- formules sans index- v1.xlsx

  18 KB · Affichages: 11

Dernière édition: 24 Janvier 2021

Vote positif 0 Vote négatif

zebanx

XLDnaute Accro

• 24 Janvier 2021

• #5

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.
@+

 

Pièces jointes

• riko_ma pomme.xlsx

  20.2 KB · Affichages: 11

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #6

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.

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 24 Janvier 2021

• #7

zebanx à dit:

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

Cliquez pour agrandir...

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

 

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #8

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

VB:

=(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))

 

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #9

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.

 

Vote positif 0 Vote négatif

mapomme

XLDnaute Barbatruc

Supporter XLD

• 24 Janvier 2021

• #10

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 !!!!!!!!!!!!!!!

 

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #11

@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

 

Dernière édition: 24 Janvier 2021

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 24 Janvier 2021

• #12

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

 

Pièces jointes

• Calend_Riko.xlsm

  111.9 KB · Affichages: 6

Vote positif 0 Vote négatif

zebanx

XLDnaute Accro

• 24 Janvier 2021

• #13

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

 

Pièces jointes

• Riko_2.xlsm

  115.9 KB · Affichages: 10

Vote positif 0 Vote négatif

Riko

XLDnaute Occasionnel

• 25 Janvier 2021

• #14

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

Cliquez pour agrandir...

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

VB:

calend!$C$13:$RT$60

Code:

calend!$C$2:$RT$2

Code:

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é)

Cliquez pour agrandir...

Merci de l'info.

 

Vote positif 0 Vote négatif

zebanx

XLDnaute Accro

• 25 Janvier 2021

• #15

Bonjour Riko

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

Bonne soirée
zebanx

 

Pièces jointes

• indirect_erreur.xlsm

  11.4 KB · Affichages: 2

Vote positif 0 Vote négatif