NB.SI

[CISCO]

Bonsoir à tous

Histoire d'améliorer une proposition faite sur ce fil, je fais divers essais avec la fonction NB.SI. Et je découvre un truc étrange...

J'ai défini une plage dynamique, lettres, $A3:$Ax, et une autre recherches, correspondant à C$3:$C$y.
Lorsque j'écris dans la cellule D3 la formule NB.SI(lettres;recherches), Excel me donne le résultat de NB.SI(lettres;$C3).
Dans la cellule en dessous, toujours avec NB.SI(lettres;recherches), j'obtiens le résultat de NB.SI(lettres;$C4). Et ainsi de suite en dessous... Je n'avais jamais remarqué ce type d'enchainement.

Au passage, si quelqu'un a une idée. Mon "problème" :
Dans la colonne D, j'ai diverses valeurs correspondant à NB.SI(lettres;$C3), puis en dessous à

NB.SI(lettres;$C3)+NB.SI(lettres;$C4), et en dessous encore à NB.SI(lettres;$C3)+NB.SI(lettres;$C4)+NB.SI(lettres;$C5). Et ainsi de suite.
J'aimerai avoir ces valeurs non pas sur la feuille, mais dans un nom, une liste dynamique dans le gestionnaire de noms, histoire de l'utiliser dans une formule sans avoir besoin de l'afficher à l'écran.
J'aimerai bien, mais je n'y arrive pas.
Donc, si vous avez une idée, merci d'avance... Sans VBA, bien sûr. Ne vous prenez pas la tête pour autant, ce n'est pas très important, c'est juste pour le fun et pour en apprendre un peu plus...

@ plus​

 

[Dranreb]

Bonsoir.
C'est normal. Si on précise un bout de colonne ou de ligne là ou dans une formule il attend une seule valeur, il en prend l'intersection avec la ligne ou de la colonne de la cellule portant la formule.
J'avoue ne pas être assez familier des formules trop complexes pour savoir définir une telle table. Mais je subodore que c'est possible…

 

[CISCO]

Bonjour

Merci Dranreb. Toujours bon à savoir...

@ plus

 

[job75]

Bonjour CISCO, Bernard, le forum,

Voyez le fichier joint, la définition (relative) du nom MaSomme est tout à fait classique.

Bonne journée.

 

[CISCO]

Bonjour à tous

Merci Job75.
Ce n'est pas exactement cela que je veux. Cela serait trop facile.

Pas évident à expliquer correctement : J'aimerai avoir dans le gestionnaire de noms un nom définit par une formule donnant, dans le cas présent, la liste {5;8;13;17;22;26;31;34;36;41;46;48}. Bien sûr, les valeurs de cette liste sont variables (elles dépendent du contenu des plages lettres et recherches), ainsi que le nombre de valeurs (qui dépend du nombre de valeurs de la plage recherches), et cette liste doit être fixe, indépendante de la cellule où on écrit le nom.

Par ex, si je défini dans le gestionnaire de noms le nom nbsi par la formule =NB.SI(Lettres;recherches), et si j'écris n'importe où sur la feuille la formule =INDEX(nbsi;4), j'obtiens le calcul intermédiaire ci-contre

.
Et bien, j'aimerai la même chose mais avec la liste {5;8;13;17;22;26;31;34;36;41;46;48}.

@ plus

 

[job75]

Re,

Pour définir une liste en dur à partir de plages il faut nécessairement du VBA.

A+

 

[CISCO]

Bonjour

Merci Job75.

@ plus

 

[job75]

Re,

On peut aussi définir MaSomme en F3 par :

=SOMME(NB.SI(Lettres;DECALER(recherches;;;LIGNES(Sheet1!F$3:F3))))

Fichier (2).

A+

 

[CISCO]

Bonjour

Oui, mais ce n'est pas ce que je cherche à faire...

@ plus

 

[job75]

Re,

Voici 2 solutions VBA dans le fichier joint :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union([LETTRES], [RECHERCHES]).EntireColumn) Is Nothing Then Exit Sub
Dim L As Range, R As Range, a&(), i&
Set L = [LETTRES]: Set R = [RECHERCHES]
ReDim a(1 To R.Count, 1 To 1)
a(1, 1) = Application.CountIf(L, R(1))
For i = 2 To R.Count
    a(i, 1) = a(i - 1, 1) + Application.CountIf(L, R(i))
Next
ThisWorkbook.Names.Add "Somme1", a
End Sub

Function Somme2(L As Range, R As Range)
Dim a&(), i&
ReDim a(1 To R.Count, 1 To 1)
a(1, 1) = Application.CountIf(L, R(1))
For i = 2 To R.Count
    a(i, 1) = a(i - 1, 1) + Application.CountIf(L, R(i))
Next
Somme2 = a 'vecteur colonne
End Function

Les noms définis LETTRES et RECHERCHES ont été un peu améliorés.

A+

 

[CISCO]

Bonsoir

Merci Job75.

@ plus

 

[job75]

Bonjour CISCO, le forum,

Les Application.CountIf prennent du temps, si le tableau RECHERCHES est grand on utilisera :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Union([LETTRES], [RECHERCHES]).EntireColumn) Is Nothing Then Exit Sub
Dim L, R, d As Object, i&, a&()
L = [LETTRES].Resize(, 2): R = [RECHERCHES].Resize(, 2) 'tableaux VBA, au moins 2 éléments
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(L)
    d(L(i, 1)) = d(L(i, 1)) + 1 'comptage
Next
ReDim a(1 To UBound(R), 1 To 1)
a(1, 1) = d(R(1, 1))
For i = 2 To UBound(R)
    a(i, 1) = a(i - 1, 1) + d(R(i, 1))
Next
ThisWorkbook.Names.Add "Somme1", a 'nom défini
End Sub

Function Somme2(LETTRES As Range, RECHERCHES As Range)
Dim L, R, d As Object, i&, a&()
L = LETTRES.Resize(, 2): R = RECHERCHES.Resize(, 2) 'tableaux VBA, au moins 2 éléments
Set d = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(L)
    d(L(i, 1)) = d(L(i, 1)) + 1 'comptage
Next
ReDim a(1 To UBound(R), 1 To 1)
a(1, 1) = d(R(1, 1))
For i = 2 To UBound(R)
    a(i, 1) = a(i - 1, 1) + d(R(i, 1))
Next
Somme2 = a 'vecteur colonne
End Function

Mais la formule du nom défini Somme1 est limitée par la limite Excel de 8192 caractères.

Le tableau RECHERCHES ne pourra donc pas dépasser quelques centaines de lignes avec cette solution.

Avec la fonction Somme2 il n'y a pas de problème.

Fichier (2).

Bonne journée.

 

[CISCO]

Bonjour

Merci Job75. La plage Recherches ne devrait pas compter plus de 50 lignes.

@ plus

 

[job75]

Re,

La plage Recherches ne devrait pas compter plus de 50 lignes.

D'accord mais il est très instructif de voir ce qui se passe sur de grands tableaux.

Je laisse donc de côté la solution avec Somme1.

Et pour Somme2 s'il y a beaucoup de cellules contenant cette fonction il faut absolument éviter les recalculs inutiles.

On mettra donc dans Module1 :

Public d As Object, a 'mémorise les variables

Function Somme2(LETTRES As Range, RECHERCHES As Range)
If d Is Nothing Then 'la fonction n'est calculée qu'une fois
    Dim L, R, i&
    L = LETTRES.Resize(, 2): R = RECHERCHES.Resize(, 2) 'tableaux VBA, au moins 2 éléments
    Set d = CreateObject("Scripting.Dictionary")
    For i = 1 To UBound(L)
        d(L(i, 1)) = d(L(i, 1)) + 1 'comptage
    Next
    ReDim a(1 To UBound(R), 1 To 1)
    a(1, 1) = d(R(1, 1))
    For i = 2 To UBound(R)
        a(i, 1) = a(i - 1, 1) + d(R(i, 1))
    Next
End If
Somme2 = a 'vecteur colonne
End Function

Et dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Union([LETTRES], [RECHERCHES]).EntireColumn) Is Nothing _
    Then Set d = Nothing: Calculate 'recalcule les plages et donc la fonction
End Sub

Dans ce fichier (3) les 6000 formules de la plage E3:E6002 se recalculent en 6,3 secondes chez moi.

Sans le test If d Is Nothing Then le recalcul prend 69 secondes.

A+

 

[job75]

Re,

Au cas où l'on effacerait la dernière cellule d'une des plages j'ai ajouté .EntireColumn dans les Worksheet_Change des fichiers précédents.

A+