Somme en passant par une liste nommée définie dans le gestionnaire

[CISCO]

Bonjour à tous

Pour résoudre un petit problème sans passer par des cellules intermédiaires (https://www.excel-downloads.com/thr...on-delais-avec-contraintes-sous-excel.211284/), j'essaye de faire uniquement avec des noms définis dans le gestionnaire de noms.

En simplifiant, il s'agit d'heures de travail. Je cherche à connaitre la somme de ces heures, en retirant les samedis, les dimanches, les jours fériés. En pièce jointe, j'ai mis un fichier présentant le contenu des divers noms utilisés. J'essaye d'obtenir les résultats présentés en rouge ou en vert, mais pas avec les formules utilisées dans ces cellules rouges ou vertes (qui utilisent le contenu des cellules voisines), mais avec une définition mise dans le gestionnaire de noms, du style :

SOMME(DECALER(listeavecconditions;;;;LIGNE(INDIRECT("1:25"))))

mais cela ne fonctionne pas. Je n'obtiens que #VALEUR
Dans ce fichier, les formules en INDEX(nom;LIGNES(...)) ne sont là que pour visualiser facilement le contenu de ces noms.

Si vous avez des idées, merci d'avance.

@ plus

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour CISCO,

La fonction DECALER ne fonctionne que sur des plages de cellules (de plus, rectangulaires).

A+

 

[CISCO]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonsoir

Merci Job75. Et voui, la fonction DECALER... Je cherche toujours une solution...

@ plus

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour CISCO,

Si j'ai bien compris, le problème est de calculer la somme des n premiers éléments d'une matrice (colonne).

C'est évidemment très facile avec cette fonction VBA :

Function SOMMEMAT#(matrice, n&)
'matrice colonne
Dim v
For n = 1 To n
  v = Application.Index(matrice, n, 1)
  If IsNumeric(v) Then SOMMEMAT = SOMMEMAT + CDbl(v)
Next
End Function

Fichier joint.

A+

 

[R@chid]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour @ tous,

J'ai pas bien saisir la demande, mais avec la fonction SOUS.TOTAL on peut faire la somme de n premiers lignes d'une plages..
Voir ce lien


@ + +

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Re,

On peut même éviter la boucle :

Function SOMMEMAT#(matrice, n&)
'matrice colonne
Dim mat
mat = Application.Transpose(matrice)
ReDim Preserve mat(1 To n)
SOMMEMAT = Application.Sum(mat)
End Function

Mais attention, Application.Transpose nécessite une matrice de 65536 éléments au maximum.

Fichier (2).

A+

 

[CISCO]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour à tous, bonjour R@chid

Toujours présent, R@chid. Merci pour ta proposition . J'avais aussi essayé avec SOUS.TOTAL, sans succès... Ta proposition va dans la bonne direction, malheureusement, cela ne fonctionne pas. Effectivement, je cherche à connaitre la position d'un nombre, 16 par exemple, dans une liste (liste sommeaveccondition). J'avais fait avec EQUIV. Avec ta méthode,

=NB(1/(SOUS.TOTAL(9;DECALER(H6;;;LIGNE(INDIRECT("1:"&NB(H6:H61)))))<16/24))

en matriciel
je pourrais rechercher cette position directement dans la plage source, en H6:H61. Malheureusement, dans mon fichier réel, cette plage n'existe pas. Le contenu de cette plage existe uniquement dans le nom listeavecconditions, pas en dur sur la feuille. Et malheureusement :

=NB(1/(SOUS.TOTAL(9;DECALER(listeavecconditions;;;LIGNE(INDIRECT("1:"&NB(listeavecconditions)))))<16/24))

ne fonctionne pas. Apparemment, c'est la partie DECALER(listeavecconditions;;; qui pose problème, dans ta formule comme dans celles que j'ai déja essayées (du style SOMME(DECALER(listeavecconditions....

Le "pire", c'est qu'avec la fenêtre "Evaluation des formules", on a bien le contenu de listeaveccondition qui est affiché. Mais lors d'une des étapes suivantes, on n'obtient que des #VALEUR. Grrrrrr. Dans certaines formules "compliquées", dans cette fenêtre d'évaluation, on a parfois des #VALEUR, mais des résultats corrects apparaissent ensuite, dans les étapes suivantes. Ce n'est pas le cas ici.

Encore merci.

@ plus

P.S : Apparemment, il y avait un terme de trop dans listeécart dans mon précédent fichier. C'est corrigé dans la pièce jointe.

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Salut R@chid

J'ai pas bien saisir la demande, mais avec la fonction SOUS.TOTAL on peut faire la somme de n premiers lignes d'une plages..

Si tu ouvres le fichier tu verras que listeavecconditions n'est pas une plage mais une matrice (définie par une formule).

C'est là le problème, et avec VBA il est facile à résoudre.

A+

 

[CISCO]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour job75

Tu résumes bien le problème... Et effectivement, en VBA, ta macro fonctionne.

Faut pas se prendre la tête pour autant. Je continue dans cette voie (Trouver une solution au problème posé sur l'autre fil, sans colonnes intermédiaires), juste pour le fun, juste pour apprendre une nouvelle astuce... avec des formules.

@ plus

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Re,

Faut pas se prendre la tête pour autant.

Non mais le problème est intéressant, on peut se fatiguer un peu

Alors voici une solution par formule matricielle :

=SOMME(SI(LIGNE(INDIRECT("1:"&NBVAL(listeavecconditions)))<=LIGNES(K$5:K5);listeavecconditions))

Noter qu'on ne peut pas utiliser SOMMEPROD car le dernier élément de listeavecconditions est #N/A...

Fichier joint.

A+

 

[CISCO]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour

Merci Job75 pour ta proposition.
Ce n'est pas exactement ce qu'il me faut. Le but est d'avoir les valeurs directement dans le gestionnaire, par exemple sous le nom listesommeavecconditions, bien sûr, sans avoir à les écrire, puisqu'on ne les connait pas à l'avance (la date en B2 peut changer, donc les samedis et les dimanches ne se trouvent pas toujours au même endroit dans les listes utilisées), mais à l'aide d'une formule.
Sur la feuille, on doit pouvoir avoir ces valeurs, par exemple avec INDEX(listesommeavecconditions;LIGNES(L$5:L5)), à tirer vers le bas, juste pour vérifier le contenu de cette liste. Dans le fichier réel, je n'ai pas besoin, ou plus exactement, je ne veux pas l'affichage de ces valeurs, puisque le but est d'obtenir un autre résultat sans cellules intermédiaires.

@ plus

P.S : J'ai supprimé le #NA en trop.

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Re CISCO,

Donc le problème est de créer le nom listesommeavecconditions donnant les valeurs cumulées de la matrice listeavecconditions.

Je n'ai pas de solution par formule et je crains fort qu'il n'y en ait pas.

Par contre cette macro dans le code de la feuille crée ce nom chaque fois que la cellule B2 est modifiée :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
Dim tablo(), i&, v
ReDim tablo(1 To [COUNTA(listeavecconditions)], 1 To 1)
tablo(1, 1) = [INDEX(listeavecconditions,1,1)]
For i = 2 To UBound(tablo)
  v = Evaluate("INDEX(listeavecconditions," & i & ",1)")
  tablo(i, 1) = tablo(i - 1, 1) + v
Next
ThisWorkbook.Names.Add "listesommeavecconditions", tablo
End Sub

Fichier joint.

A+

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Re,

Une solution plus simple et d'exécution plus rapide :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
Dim tablo, i&
tablo = Evaluate("MMULT(listeavecconditions,1)") 'PRODUITMAT
For i = 2 To UBound(tablo)
  tablo(i, 1) = tablo(i, 1) + tablo(i - 1, 1)
Next
ThisWorkbook.Names.Add "listesommeavecconditions", tablo
End Sub

Fichier (2).

A+

 

[CISCO]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonsoir à tous

Merci Job75 pour cette solution. Si je ne trouve pas de solution avec des formules, j'utiliserai ta macro.

@plus

 

[job75]

Re : Somme en passant par une liste nommée définie dans le gestionnaire

Bonjour CISCO,

Pour pinailler.

S'il n'y a jamais de minutes avec les heures il est mieux d'avoir des nombres entiers dans la matrice :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B2]) Is Nothing Then Exit Sub
Dim tablo, i&
tablo = Evaluate("MMULT(listeavecconditions,24)") 'PRODUITMAT
For i = 2 To UBound(tablo)
  tablo(i, 1) = tablo(i, 1) + tablo(i - 1, 1)
Next
ThisWorkbook.Names.Add "listesommeavecconditions", tablo
With ThisWorkbook.Names("listesommeavecconditions")
 .RefersTo = .RefersTo & "/24"
End With
End Sub

Regarde la fin de la formule du nom créé dans le fichier (3).

A+