XL 2016 Pb avec SOMMEPROD

[Phoenix23]

Bonsoir à toutes et tous,
Dans le fichier joint un problème complexe pour moi, puisque je n'ai pas les compétences pour élaborer une telle formule.
Celle ci fonctionnait bien jusqu'au moment ou j'ai voulu agrandir mon tableau de donnés...
Et je n'arrive pas malgré plusieurs tentative à faire évoluer cette formule.
SI parmi vous une personne ou plusieurs pouvaient me venir en aide..
D'avance Merci.
Les détails sont dans le fichiers ci-joint feuille "Tableau"

 

[Dudu2]

Bonjour,
J'ai un peu de mal à comprendre ta formule mais ce que je remarque 4 choses:
- Les dates en ligne 3 de la feuille "Chevreuils" sont des cellules fusionnées donc une référence à une cellule qui n'est pas la 1ère de la fusion rend 0 (aucune chance de trouver le mois)
- Il y des plages de tailles différentes dans le SOMMEPROD. Je n'ai pas investigué suffisamment pour
savoir l'impact mais ça me parait douteux.
- Pourquoi fais-tu référence à une plage Chevreuils!$B$3:$CB$3 par exemple ? Pourquoi CB et pas IE ?
- Pourquoi n'utilises-tu pas la ligne 24 de la feuille "Chevreuils" pour faire le compte ?

Si le but est un décompte mensuel M/F des Chevreuils je vais essayer de faire les modifs pour le retrouver.

 

[Phoenix23]

Bonsoir dudu,
Pour répondre à ta question, le classeur n'est complet.... en fait il y aussi une feuille graphiqe ou tout est répertorié par MOIS.... D'ou le besoin des détails.....

 

[Dudu2]

La formule serait simplissime s'il n'y avait pas ces cellules fusionnées pour les dates en feuille Chevreuils.
J'ai essayé mais je n'arrive pas à intégrer correctement le décalage de -1 pour retrouver la date quand celle-ci est vide (2ème cellule des cellules fusionnées).

 

[Dudu2]

Après pas mal d'essais, j'ai pu faire ces 2 formules (M et F) pour faire le total en utilisant la ligne 24 de la feuille "Chevreuils".
Pourquoi 2 formules ? Parce que la date en $B$3:$IE$3 est à tester sans (M) et avec (F) un DECALER à cause de ces @#! cellules fusionnées (toujours à éviter dans les calculs et réserver uniquement pour les titres).

Ce sont des formules matricielles (à valider par Ctrl + Shift + Entrée).

J'ai changé la liste des mois en feuille "Tableau" pour les exprimer en format date par commodité.
Ex: 01/01/1900 en fomat "mmmm" -> janvier (tanpis pour la majuscule, c'est infiniment plus pratique de comparer des dates avec des dates).

S'il manque 2 au total des F c'est que la cellule EZ de la la feuille "Chevreuils" contient une date invalide. Je te laisse corriger.

 

[Dudu2]

Sinon, pour revenir à une formule unique pour M et F avec un SOMMEPROD "normal" (solution que je te recommande), il faut ajouter une ligne des mois dans la feuille Chevreuils pour faire disparaitre le problème des cellules fusionnées des dates.

Si la formule extensible aux autres cellules pour déterminer le mois te semble compliquée:
En B3: =SI(ESTNUM(DECALER(B3;0;-MOD(COLONNE(B3);2)));MOIS(DECALER(B3;0;-MOD(COLONNE(B3);2)));0)

C'est pour éviter d'avoir 2 formules strictement identiques par date:
En B3: =SI(ESTNUM(B3);MOIS(B3);0)
En C3: =SI(ESTNUM(B3);MOIS(B3);0)
et provoquer ces énervants petits triangles d'erreur en cellule indiquant "La formule de cette cellule diffère des formules de cette zone de la feuille de calcul"

Ceci dit, si besoin, on peut toujours virer ces triangles d'erreur en exécutant une petite macro du genre:

'https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.xlerrorchecks?view=excel-pia
'Possible values of xlErrorChecks
'xlEvaluateToError          1    The cell evaluates to an error value.
'xlTextDate                 2    The cell contains a text date with 2-digit years.
'xlNumberAsText             3    The cell contains a number stored as text.
'xlInconsistentFormula      4    The cell contains an inconsistent formula for a region.
'xlOmittedCells             5    The cell contains a formula omitting a cell for a region.
'xlUnlockedFormulaCells     6    The cell, which is unlocked, contains a formula.
'xlEmptyCellReferences      7    The cell contains a formula referring to empty cells.
'xlListDataValidation       8    The cell contains a value inconsistent with list data validation.
'xlInconsistentListFormula  9    The cell contains an inconsistent formula for a list.

  
Sub IgnoreErrors()
    Dim Cel As Range
    Dim i As Integer

    For Each Cel In ActiveSheet.UsedRange
        For i = 1 To 9
            Cel.Errors.Item(i).Ignore = True
        Next i
    Next Cel
End Sub

 

[Jocelyn]

Bonjour le Forum,
Bonne année,
Bonjour Phoenix23, Dudu2,

Pour le Fun avec un sommeprod sans validation matricielle

Voir onglet tableau plage A2638

Cordialement

 

[Phoenix23]

Bonjour Dudu / Jocelyn
Merci pour votre aide, vraiment merci.
Je viens de voir la proposition de Jocelyn qui me parait la plus simple(pour moi qui n'y connais pas grand chose)...
Petite question Jocelyn, Pourrai- je sans pb et en conservant ta formule rajouter de nouvelles colonnes dans la feuille chevreuil?
Quand à moi j'ai planché un petit peu également de mon coté.
J'ai pris un autre Classeur (2018) sur lequel j'ai effectué la manip à savoir rajouter des colonnes dans la feuille "Chevreuils" la miracle la formule originale fonctionne dans la feuille tableau....
Une explication ?
Par contre je ne peux pas recopier les formules sur le classeur 2019...
Je joins le fichier si vous souhaitiez y jeter un oeil.
Cordialement.

 

[Jocelyn]

re,

Concernant la formule que j'ai proposée il suffit d'agrandir les plage que se soit vers la droite ou vers le bas.Dans le fichier joint je te fais un test qui sur la largeur prend jusqu'en colonne IV (dernière colonne possible pour moi

Cordialement

 

[Phoenix23]

re,

Concernant la formule que j'ai proposée il suffit d'agrandir les plage que se soit vers la droite ou vers le bas.Dans le fichier joint je te fais un test qui sur la largeur prend jusqu'en colonne IV (dernière colonne possible pour moi

Cordialement

Bonjour, Jocelyn.
Merci de ton aide, je prends en compte..
Je te remercie chaleureusement de ton aide.
Très cordialement.