XL 2019 Sommes des cellules fusionnées

Amilo

XLDnaute Accro
Bonjour le forum,

J'ai un tableau qui va de la colonne A à M et sur plusieurs centaines de lignes,
Il y a diverses données dans le tableau sauf dans les lignes en regard des sous-totaux pour lesquelles les cellules sont vides (cellules en vert)

Je souhaiterais obtenir en colonne M la somme des cellules de K par rapport au critère de la colonne L si le mot "OK" est présent.
Le problème est que les cellules peuvent être fusionnées à divers endroits dans la colonne L

C'est un fichier que j'ai reçu tel quel et je préfère ne pas toucher à la structure du fichier

Merci d'avance pour votre aide


Cordialement
 

Pièces jointes

  • SommeCellullesFusionnées.xlsm
    10.7 KB · Affichages: 15

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir @Amilo :),

Voir une fonction personnalisée SousTotal(...) :
=SousTotal(PlageValeur, PlageCrit , ValeurCrit, PlageTitre , ValeurTitre)
  • PlageValeur => la Plage des valeurs à sommer
  • PlageCrit => la plage des critères à prendre en compte
  • ValeurCrit => la valeur du critère à retenir pour la somme
  • PlageTitre => la plage des titres où se trouve l'intitulé de la ligne de la somme
  • ValeurTitre => la valeur de l'intitulé qui repère une ligne de sous-total

Voir la formule en M8 à recopier sur les autres cellules concernées de la colonne M :
VB:
=SousTotal(K$4:K8;L$4:L8;"ok";A$4:A8;"sous-total")

Le code de la fonction dans module1 :
Code:
Function SousTotal(PlageValeur As Range, PlageCrit As Range, ByVal ValeurCrit, PlageTitre As Range, ByVal ValeurTitre)
[LIST]
'PlageValeur  => la Plage des valeurs à sommer
'PlageCrit    => la plage des critères à prendre en compte
'ValeurCrit   => la valeur du critère à retenir pour la somme
'PlageTitre   => la plage des titres où se trouve l'intitulé de la ligne de la somme
'ValeurTitre  => la valeur de l'intitulé qui repère une ligne à soustotal
[/LIST]

Dim i&, tot#, x
   For i = PlageValeur.Rows.Count - 1 To 1 Step -1
      If LCase(PlageTitre.Cells(i, 1)) <> LCase(ValeurTitre) Then
         x = PlageCrit.Cells(i, 1).MergeArea(1, 1).Value
         If LCase(x) = LCase(ValeurCrit) Then If IsNumeric(PlageValeur.Cells(i, 1)) Then tot = tot + PlageValeur.Cells(i, 1)
      Else
         Exit For
      End If
   Next i
   SousTotal = tot
End Function
 

Pièces jointes

  • Amilo- SommeCellullesFusionnées- v1.xlsm
    19 KB · Affichages: 11

Amilo

XLDnaute Accro
Bonsoir @mapomme ,

Merci pour votre réponse et cette solution,

J'ai constaté que la formule se met à jour automatiquement :
- lorsque de nouvelles cellules sont pré-fusionnées et que le mot "OK" est saisi dans cette fusion
- ou inversement, lorsque le mot "OK" est d'abord supprimé et qu'ensuite la sélection est défusionnée.

Par contre, la formule ne se met pas à jour automatiquement dans les cas suivants (il faut forcer le calcul de la formule avec "F2 + Entrée")
- Saisie du mot "OK" dans une seule cellule puis fusion avec des cellules du dessous à l'aide de la commande "Fusionner et Centrer"
- ou inversement lorsque la fusion contenant un "OK" est directement annulée avec la commande "Annuler Fusionner cellules"

J'ai aussi remarqué que la formule est recalculé avec "F2 + Entrée" en étant dans une des cellules de la colonne L ou en étant dans la cellule contenant la formule en colonne M.

Ne sachant pas comment les collègues procèderont, peut-on svp automatiser le recalcul des formules dans ces cas ?

Mille mercis

Cordialement
 

Amilo

XLDnaute Accro
Bonjour @mapomme ,

Merci pour cette nouvelle version,
Bien que mon fichier soit en Mode de calcul "Automatique", je dois cette fois actualiser les formules par "F9" pour les cas cités dans mon précédent message.

Mais c'est très bien déjà et encore merci à vous

Bonne journée

Cordialement
 

chris

XLDnaute Barbatruc
Bonjour à Tous

La fusion, défusion de cellules touche au format, ce qui ne provoque aucun évènement captable en VBA

C'est une peu la même problématique que de compter ou sommer des cellules sur la base de la couleur.

On n'est jamais sûr à 100% que le résultat visible correspond à la situation en cours et non au calcul précedent.

Les fonctions volatiles, sont systématiquement calculées si un calcul se déclenche
En grand nombre elles peuvent ralentir Excel.
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Amilo , @chris ;)

Aussi bizarre et surprenant que ça peut l'être, sur mon office 365, la fusion de cellules et l’annulation de la fusion provoque le recalcul de la fonction personnalisée o_O. Ça ne devrait pas !

J'ai vérifié plusieurs fois et sur mon office 365, quand la fonction est volatile, ça fonctionne.
J'ai vérifié plusieurs fois et sur mon office 2010, quand la fonction est volatile, ça fonctionne pas.

On pourrait en déduire, même si un cas particulier (et celui de mapomme n'est sans doute pas à généraliser) n'est pas une preuve :

  • Sur Excel 2010 (Chris et mapomme), Application Volatile =True ne provoque pas le recalcul de la fonction personnalisée quand on fusionne ou annule la fusion

  • Sur Excel 2016 (Amilo), Application Volatile =True ne provoque pas le recalcul de la fonction personnalisée quand on fusionne ou annule la fusion

  • Sur Excel 365 (mapomme), Application Volatile =True provoque le recalcul de la fonction personnalisée quand on fusionne ou annule la fusion
 
Dernière édition:

Amilo

XLDnaute Accro
Bonjour chris , re mapomme ,

@chris, merci pour cette précision, effectivement idem que pour les cellules en couleur,
J'ai fait un test, dans le gestionnaire de noms avec la fonction =LIRE.CELLULE(38;!C10) et il faut actualiser manuellement la formule en cas de changement de couleur

@mapomme , merci pour ce complément d'information et de vos vérifications sur différentes versions.
Sinon, mon test en message #5 a été réalisé effectivement sur une version 2016 au travail
J'essaierai par curiosité ce soir sur ma version 2019 de la maison.

En tout cas, je suis très content de la solution proposée,

Bonne journée à vous
 
Dernière édition:

Amilo

XLDnaute Accro
Re,

Je reviens sur un autre problème,

Le fichier réel fait plus de 500 lignes et comporte près de 80 cellules devant contenir la formule

En plus de la formule à @mapomme , la colonne M contient également du texte,

Aussi, je ne peux pas tirer la formule sur toute la colonne au risque de supprimer les autres cellules.

J'ai réussi dans mon fichier réel à appliquer en quelques secondes la formule dans une colonne vide en regard des cellules à sommer (ici en colonne 0)

Par contre, je cherche un moyen rapide de copier les formules des cellules de la colonne O dans la colonne M

J'ai fait plusieurs tentative notamment avec "Cellules visibles seulement" mais sans succès,

Avez-vous svp une solution pour ce problème sachant que j'ai encore plusieurs autres fichiers dans ce cas ?

Merci d'avance
 

Pièces jointes

  • SommeCellullesFusionnées- v1a (1).xlsm
    19 KB · Affichages: 6

Amilo

XLDnaute Accro
@mapomme ,

Je suppose que vous avez rentré une à une, la formule dans chacune des cellules de la colonne M puis adapté la plage selon la sélection à sommer ?

Dans mon cas réel, j'ai plus de 80 cellules où je dois indiquer votre formule.

Si je rentre la formule en cellule M8 et que je tire vers le bas, j'écrase toutes les cellules contenant du texte.

Comment faites-vous pour remplir rapidement la formule dans chacune des cellules ?

Merci d'avance
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Comment faites-vous pour remplir rapidement la formule dans chacune des cellules ?

Sans doute comme vous mais, chez moi, ça a l'air d'être correct.
  • je filtre sur la colonne A les sous-totaux
  • dans la cellule M8, je saisie la formule (attention j'ai ajouté des $ pour fixer les colonnes) :
    VB:
    =SousTotal($K$4:$K8;$L$4:$L8;"ok";$A$4:$A8;"sous-total")
  • je copie la cellule M8
  • je sélectionne le reste des cellules sous-total de la colonne M
  • je sélectionne les cellules visibles (via la touche F5)
  • et j'y colle en une seule fois la cellule M8 qu'on avait copiée
 

Amilo

XLDnaute Accro
Arffff, j'ai la tête ailleurs….!!

Merci @mapomme , effectivement nous avions la même méthode et sur ce coup là cela fonctionne.
J'aurais dû réessayer tranquillement de la maison au lieu poster ce message dans la précipitation :mad:

Désolé pour le dérangement :)

P.S : sinon le problème ou le résultat que j'avais ce matin, c'était un message d'erreur du type "Nous ne pouvons pas effectuer cette action sur une sélection de plusieurs plages"

Bonne journée
 
Dernière édition:

Discussions similaires