XL 2013 (RESOULU)Calcul avec plusieurs Formule NB.SI

[nunizgb]

Bonjour,

Je cherche à simplifier ma formule de calcul qui a bcp de Nb.si,
Exemple :
Je doit cherche dans la colonne A7:A14 de la feuille A et dans la colonne A533:536 de la B
nombre de cellule qui ont comme texte : CP,M,CSS puis faire 24- total NB.SI

Actuellement je fait

SOMME(NB.SI(A7:A14;"CP");NB.SI(A7:A14;"M");NB.SI(A7:A14;"CSS");NB.SI(A7:A14;"JF");NB.SI('B'!A533:A536;"CP");NB.SI('B'!A533:A536;"M");NB.SI('B'!A533:A536;"CSS");NB.SI('B'!A533:A536;"JF")))*(24/6)

Donc comment je puisse simplifie, faire plus court ce code afin que cela marche ? ou ce n'est pas possible

Merci pour votre aide

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

Bonjour,

Pour simplifier "un peu" ta formule, tu peux déjà commencer par nommer tes zones
A7:A14 = ZoneA
A533:A536 = ZoneB
ensuite, utiliser la fonction Sommeprod

=24-(sommeprod((ZoneA="CP")*1) + sommeprod((ZoneA="M")*1) + Sommeprod((ZoneA="CSS")*1) + ......)

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

Bonjour,

Pour simplifier "un peu" ta formule, tu peux déjà commencer par nommer tes zones
A7:A14 = ZoneA
A533:A536 = ZoneB
ensuite, utiliser la fonction Sommeprod

=24-sommeprod((ZoneA="CP")*1) + sommeprod((ZoneA="M")*1) + Sommeprod((ZoneA="CSS")*1) + ......

Merci je ne connaisse pas cette fonction sommeprod donc je vais essaye d'y faire comme tu l'a dit merci

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

Ca peut te servir

Excel: Dénombrement et somme conditionnelle.

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

Re bonjour

Voilà j'ai fait pour le test : A533:A536 = ZoneB mais cela ne marche pas parce que cette zone ce trouve sur une autre feuille et elle n'est pas fixe.
En clair comment faire la zoneA=A533:A536 de la feuille A mais qui marche dans le code si on met 'B'!zoneA

Parce que si on a actuellement 3 feuilles on met cette formule manuellement et cela marche parr contre si j'ajoute une autre feuille et je copier les donnes de la feuille 3, le code ne marche plus car zoneA est pris sur la feuille A et non sur la feuilleB

En clair est ce qu'on peut faire une zone qui varie en fonction d'onglet

Merci

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

En clair voilà ce que j'ai fait sur ma feuille B
nommer la zone A7:A14=ABSDA
nommer la zone A533:A566=ABSFA

donc m'a formule est actuellement plus court et elle est comme ça

24-SOMMEPROD((ABSDA="CP")*1)+SOMMEPROD((ABSDA="M")*1)+SOMMEPROD((ABSDA="CSS")*1)+SOMMEPROD((ABSDA="JF")*1)+SOMMEPROD(('A'!ABSFA="CP")*1)+SOMMEPROD(('A'!ABSFA="M")*1)+SOMMEPROD(('A'!ABSFA="CSS")*1)+SOMMEPROD(('A'!ABSFA="JF")*1)*24/6

Mais la zone ABSFA n'est pas bonne car elle était définir sur la feuille B et donne elle ne prend pas les valeur de la feuille A

PS: JE pense que je doit définir la zone ABSFA avec la fonction decaler ?

 

[JHA]

Re : Calcul avec plusieurs Formule NB.SI

Bonjour à tous,

A essayer et adapter:

=SOMMEPROD(NB.SI($A$7:$A$14;liste))*(24/6)

nommer "liste" la plage B533:B536

JHA

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

Nunizgb

Dans ta formule,
il ne faut pas faire référence à la feuille qui contient la zone nommée.. car c'est implicite dans le nom.
avec la formule de JHA
ca te donnerait ceci

24-(SOMMEPROD(NB.SI(ZoneA;Liste))+SOMMEPROD(NB.SI(ZoneB;Liste)))

d'ailleurs. c'est 24-.. ou 24/6 ??

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

Re,

Voir PJ pour différentes pistes

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

Re,

Voir PJ pour différentes pistes

Merci j'y vais regarde mais comment faire si la zoneB bouge pour la feuille1 elle est sur feuille1, pour la feuille3 elle sur la feuille2, etc...

Car actuellement zoneB est lier à la feuille1 et donc si on copie la feuille2 vers feuille3. Sur la feuille3, ceci :

=SOMMEPROD(NB.SI(ZoneA;Liste))+SOMMEPROD(NB.SI(ZoneB;Liste))

vas cherche la zoneB sur la feuille1 alors qu'elle doit aller la cherche sur la feuille2 ?

J'ai joint ton fichier que 'ai modifier afin de montre exemple et ce que je cherche exactement

Merci pour aide

PS dans l'exemple il faut lire quand on est sur feuille2 cherche sur feuil1, quand on est sur la feuile3 cherche sur la feuille2

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

OK, comme quoi, avec un fichier exemple on y voit plus clair...enfin...presque ;-)

donc, on oublie les zones nommées car oui.. une zone nommée = 1 seule feuille
plusieurs feuilles ne peuvent pas avoir la meme zone nommée.. enfin. je crois..

donc. voir PJ

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

OK, comme quoi, avec un fichier exemple on y voit plus clair...enfin...presque ;-)

donc, on oublie les zones nommées car oui.. une zone nommée = 1 seule feuille
plusieurs feuilles ne peuvent pas avoir la meme zone nommée.. enfin. je crois..

donc. voir PJ

Merci pour le fichier mais je ne peux pas le tester car il faut MoreFunc mais elle n'est pas valable pour Excel 2013 64 bits

donc est-ce que tu a le code pour la fonction NOMFEUILLE afin que je la met dans mon module ?


Merci

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

J'ai trouve ce code pour la fonction mais est-ce que c'est valable pour excel 2013 64 bits

    REM  *****  BASIC  *****
    ' Listener
    ' Un grand merci à Pierre-Yves Samyn du forum officiel OOo
    ' pour la partie Listener de cette fonction.
    Option Explicit

    Global PysListener as object

    sub PysListenerAdd

    PysListener = createUnoListener("Pys_", "com.sun.star.util.XModifyListener")
    ThisComponent.AddModifyListener(PysListener)

    end sub

    sub PysListenerRemove
       ThisComponent.RemoveModifyListener(PysListener)
    end sub

    sub Pys_disposing(PysListener)
    end sub

    Sub Pys_Modified(PysListener)
       thiscomponent.calculateAll
    End Sub

    'Fonction NomFeuille Version3
    'Auteurs : Jeff & Larrassac
    'Nos remerciements au forum officiel OOo : http://user.services.openoffice.org/fr/forum/index.php
    'Nos remerciements au Club des développeurs français : http://www.developpez.net/forums/index.php

    Function NomFeuille(Optional position as integer)
       Dim oDoc As Object, oSheets As Object, Feuille As Object, Cellule as Object
       Dim NmbFeuilles As Integer 'Variable qui va stocker le nombre de feuilles
       
        On error goto Erreurs 'Si erreur va à Erreurs
       oDoc = ThisComponent
       oSheets = oDoc.Sheets
       NmbFeuilles = oDoc.Sheets.Count 'stockage nbre de feuille du classeur

             NomFeuille = oDoc.Sheets(position-1).Name'la première feuille est la feuille 0, d'où le -1
             

        Erreurs: 'Gestion des erreurs

             if position < 1 then goto nul'si le critère position est inférieur à 1 va à Nul
                     
             if IsMissing(position) then goto nul'si le critère position est oublié alors va à Nul
              
               if position > NmbFeuilles then goto nul 'si le critère position est supérieur à NmbFeuilles alors va à Nul
              
             goto fin
           
             nul:'si le critère position est inférieur à 1
             
             NomFeuille = ""'n'affiche rien

             Goto fin
                   
        Fin:
           
        End Function

 

[vgendron]

Re : Calcul avec plusieurs Formule NB.SI

Re
Pour le code de la fonction. aucune idée...
par contre, tu peux peut etre contourner le pb avec un bout de code VBA dans l'évènement activate de la feuille
ou meme l'évènement OPen du workbook

Private Sub Workbook_Open()
For Each ws In Worksheets
    ws.[D2] = ws.Name
Next ws
End Sub

 

[nunizgb]

Re : Calcul avec plusieurs Formule NB.SI

Re
Pour le code de la fonction. aucune idée...
par contre, tu peux peut etre contourner le pb avec un bout de code VBA dans l'évènement activate de la feuille
ou meme l'évènement OPen du workbook

Merci avec le code de la fonction que j'ai mis et ton code la formule marche sans installer MoreFunc

Merci, je ne suis pas actuellement sur mon ordi mais sur un autre et elle marche par contre dès que je rentre chez moi, je vais voir si elle marche

Merci
Private Sub Workbook_Open()
For Each ws In Worksheets
    ws.[D2] = ws.Name
Next ws
End Sub