Aide sur la fonction Somme.Si

Version imprimable

21/11/2011, 14h21
kyros21

Aide sur la fonction Somme.Si

bonjour à tous,
je fouille un peu partout mais en vain une fonction existante ou à composer pour réaliser les instructions suivantes:

Rechercher dans une colonne ( exemple : B) toutes les cellules qui ont une valeur prédéfinies ( pas de msgbox quoi ) , ex: 4014, récuperer les coordonnées des lignes, et sommer les valeurs contenues dans ces meme lignes mais en colonne D par exemple,

Je me doute qu'une fonction ne fait pas toute cela, mais je cherche si il existe une fonction pour la recherche, et egalement une qui recherche et qui somme tout sa en une seule fonction. ou autres...

merci beaucoup de votre aide
21/11/2011, 14h29
mercatog

Citation:

SOMME.SI
21/11/2011, 16h43
bueno3

Sommprod peut aussi marcher
21/11/2011, 16h47
ZebreLoup

Bonjour Bueno3. Je ne pense pas que SommeProd puisse faire ce que kyros21 souhaite. A part en se compliquant la vie en rajoutant une colonne qui fait des 0 ou des 1 si la valeur en B est 4014. Ou en mettant un IF et en faisant une formule matricielle.
Ce que suggérait Mercatog est exactement ce qu'il voulait faire.

Ou alors, il y a peut-être une utilisation de SommeProd que je ne connais pas ? Je serais ravi de l'apprendre dans ce cas.

A+
21/11/2011, 19h35
SfJ5Rpw8

oui l'on peu avec les deux .. les valeurs à testé de b1..b25 , les valeurs à sommer D1..D25

avec Somme.SI :

Code:

=SOMME.SI(B1:B25;4014;D1:D25)

Avec sommeprod

Code:

=SOMMEPROD((B1:B25=4014)*(D1:D25))

;)
22/11/2011, 08h24
kyros21

merci à tous pour votre aide, mais je viens de tester le Somme.Si et il semble correspondre parfaitement à ce que je cherche,
autant que le Somme.prod d'ailleur.

merci beaucoup de votre aide

bonjour, maintenant que j'ai trouvé la fonction, la transcrire en VBA n'est pas si facile,

en fait ma fonction doit regarder dans la colonne Q ( en excluant Q2) les lignes qui ont les valeurs 4014, et sommer les valeurs contenues dans ces memes lignes mais dans la colonne G ( en excluant G2), tout sa sur une feuille qui change de nom et d'index, mais dont l'index est contenue dans une variable nommé : variable 3.
sachant que je veux insérer cette formule dans une cellule d'une feuille nommé
Top20 répartition h MO
je travaille sur la formulation, mais j'avou que je bloque un petit peu ...
voyez vous l'erreur que je fais ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Sub Dim produit As String, feuille As String Dim final As Integer, dernier As Integer, variable1 As Integer, variable2 As Integer, variable3 As Integer dernier = 0 final = 0 feuille = LastNameSheet 'nom de la derniere feuille du fichier variable1 = 13 'numero de feuille' variable2 = 7 'colonne' variable3 = 0 'colonne des données à extraire' variable3 = variable2 + 1 Sheets("Top20 répartition h MO").Cells(4, variable3).FormulaR1C1 = "=SUMIF (variable3 ! Q:Q , 4014 ,variable3 ! G:G )" end sub
merci de votre aide

22/11/2011, 10h14
ZebreLoup

Désolé pour le SOMMEPROD, ça ne me semblait pas le plus logique.

Pour ton VBA, je n'ai pas regardé en détail, mais première remarque, dans ta formule, tu rentres en dur variable3, remplace

Code:

"=SUMIF(variable3!Q:Q,4014,variable3!G:G)"

par

Code:

"=SUMIF(" & variable3 & "!Q:Q,4014," & variable3 & "!G:G)"

Mais quitte à faire du vba, tu veux vraiment écrire une formule ?
22/11/2011, 10h47
kyros21

en faisance ceci, il cherche en fait un fichier nommé "Variable 3" et non pas une feuille de calcul ,

comment faire?

pourquoi ? c'est plus simple en vba que plutot faire appel à une formule existante?

merci
22/11/2011, 10h58
ZebreLoup

Normalement, dans le code que tu as fourni, variable3 vaut 8. Donc il écrit dans la cellule H4 de la feuille "Top20 répartition h MO" la formule suivante"

Code:

=SUMIF(8!Q:Q,4014,8!G:G)

Qui ne doit pas fonctionner si tu n'as pas de feuille qui s'appelle "8"

Je suppose que le code que tu as donné n'est pas celui que tu utilises (tu ne te sers pas de la moitié des variables et les initialisations sont bizarres), donc peut-être que l'erreur vient d'autre part.

Si 8 n'est pas le nom de la feuille mais son index (dans ce cas, ce n'est pas très propre, il suffit que quelqu'un déplace une feuille et ça ne marche plus), utilise à la place de variable3 :

Code:

Worksheets(variable3).Name

c'est effectivement son index, et oui je suis d'accord les feuilles peuvent etre deplacées, mais en l'occurence, c'est le but, car ces feuilles sont utilisé juste pour extraire les données via la formule somme.Si, et quelques autres dans ma macro, et ensuite elle peuvent etre supprimé et remplacé par d'autre, donc je pense que cela suffit pour mon utilisation.

En fait ma macro, possede 3 feuilles fixes, qui ne bouge jamais, et ensuite les utilisateurs ajoute à la suite ( donc toujours un index suppérieur à 3) des feuilles qui contiennent des données à extraire, que la macro analyse y récupere ce qui l'interesse et voila. la seule vérif qu'elle effectue, est que ces données n'existe pas déja dans les feuilles fixes.
voici la macro au complet, avec toutes les variables utilisées ( ou casiment car elle n'est pas finie):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
Sub Extraction_donnee_CCR()
 
'adresse = ActiveCell.Address recuperation donnée cellule
'ligne = ActiveCell.Row recuperation numero de ligne
 
' Extraction_donnee_CCR Macro
' Macro enregistrée le 17/11/2011 par T0116493
 
'analyser le contenu de la cellule A2 de la premiere page ( page numéro 7'
 
Dim produit As String, feuille As String
Dim final As Integer, dernier As Integer, variable1 As Integer, variable2 As Integer, variable3 As Integer
 
dernier = 0
final = 0
feuille = LastNameSheet 'nom de la derniere feuille du fichier
variable1 = 13 'numero de feuille'
variable2 = 7 'colonne'
variable3 = 0 'colonne des données à extraire'
 
'active la feuille 7 pour eviter la validation de la condition de la boucle suivante'
Sheets(variable1).Activate
'verifier si l'on a pas atteint la derniere feuille nomme Fin'
Do Until variable1 >= Sheets.Count
 
    'recuperer le nom du 1er tube'
    produit = Sheets(variable1).Cells(2, 1).Value
 
    'verifier si l'on a atteint la derniere colonne a verifier'
    Do Until variable2 = 251 Or dernier = 1
 
            'verifier que le tube n'existe pas deja'
            If Sheets("Top20 répartition h MO").Cells(1, variable2).Value = produit Then
            dernier = 1
 
            Else
                'Si la cellule est vide alors'
                If Not (IsEmpty(Sheets("Top20 répartition h MO").Cells(1, variable2))) Then
                'incrémente les colonnes pour changer de colonne'
                variable2 = variable2 + 4
 
                Else
                'sinon recopier le nom du produit'
                Sheets("Top20 répartition h MO").Cells(1, variable2) = produit
                variable3 = variable2 + 1
 
                'recupere la somme des heures affecté au poste 4014
                Sheets("Top20 répartition h MO").Cells(4, variable3).FormulaR1C1 = "=SUMIF(" & variable1 & "!Q:Q,4014," & variable1 & "!G:G)"
 
                'recupere la somme des heures affecté au poste 40..
 
                 'recupere la somme des heures affecté au poste 40..
 
 
                  'recupere la somme des heures affecté au poste 40..
 
                   'recupere la somme des heures affecté au poste 40..
 
                    'recupere la somme des heures affecté au poste 40..
 
                variable3 = variable2
 
                dernier = 1
 
 
                End If
 
            End If
    Loop
    'reinitialise la variable dernier condition de la boucle'
    dernier = 0
    'reinitialise les colonnes pour commencer un nouveau tube'
     variable2 = 7
    variable1 = variable1 + 1
 
Loop
variable1 = 12
MsgBox "Recopie des tubes terminée"
 
End Sub

en tout cas merci de votre aide, si vous voyez des choses choquantes pour un vba de débutants, je suis preneur de tout conseils ;D , merci encore

J'ai fait quelques modifs en espérant que j'ai bien compris ce que tu voulais faire. Ca a l'air de marcher comme il faut chez moi.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Option Explicit
 
Private Const PREMIERE_FEUILLE_NON_FIXE = 4
Private Const PREMIERE_COLONNE = 7
 
Sub Extraction_donnee_CCR()
    Dim iFeuille As Integer
    Dim iCol As Integer
 
    Dim wsTop20 As Worksheet
    Dim wsTemp As Worksheet
 
    Dim produit As String
    Dim produitPresent As Boolean
 
    'On définit la feuille fixe où on écrit les données
    Set wsTop20 = Worksheets("Top20 répartition h MO")
 
    'On boucle sur toutes les feuilles non fixes
    For iFeuille = PREMIERE_FEUILLE_NON_FIXE To Worksheets.Count
        Set wsTemp = Worksheets(iFeuille)
 
        'On récupère le nom du produit
        produit = wsTemp.Cells(2, 1).Value
 
        If produit <> "" Then
            'On cherche s'il est déjà présent, sinon on récupère la première colonne vide
            iCol = PREMIERE_COLONNE
            produitPresent = False
 
            While wsTop20.Cells(1, iCol).Value <> "" And Not produitPresent
                If wsTop20.Cells(1, iCol) = produit Then
                    produitPresent = True
                End If
                iCol = iCol + 1
            Wend
 
            'S'il n'est pas déjà présent on effectue le traitement
            If Not produitPresent Then
                'Recopier le nom du produit
                wsTop20.Cells(1, iCol) = produit
 
                'Somme des heures affectées au poste 4014
                wsTop20.Cells(4, iCol).FormulaR1C1 = "=SUMIF(" & wsTemp.Name & "!Q:Q,4014," & wsTemp.Name & "!G:G)"
 
                'Autres traitements...
 
            End If
        End If
    Next iFeuille
 
    MsgBox "Recopie des tubes terminée"
End Sub

22/11/2011, 12h59
kyros21

merci beaucoup pour cette simplification de macro :D,
il me reste un dernier petit souci sur cette formule,

la formule somme.si ne donne pas d'erreur dans le vba, mais sur la feuille de calcul elle afiche : #NOM?, mais si je rentre dans la formule et que j'appui sur entree, et bien la , la bonne valeur s'affiche,

y a-t-il quelques choses que j'ai loupé ?

merci
22/11/2011, 13h26
ZebreLoup

Essaie de remplacer

Code:

FormulaR1C1

par

Code:

Formula
22/11/2011, 15h34
kyros21

un grand merci sa marche nikel,

derniere question, pourquoi une ligne comme celle ci, me renvoie la variable 2 brute dans la formule et ne va pas chercher sa valeur:

Code:

Sheets("Top20 répartition h MO").Cells(18, variable3).Formula = "=SUM(Range(Cells(4, variable3):Cells(17, variable3)))"

merci
22/11/2011, 15h41
ZebreLoup

Comme la dernière fois, tu mélanges du code VBA et des formules Excel. Tout ce qui est entre guillemets va être mis dans la cellule sans interprétation et Excel ne connait pas Range ou Cell. Essaie :

Code:

"=SUM(" & Range(Cells(4, variable3), Cells(17, variable3)).Address & ")"
22/11/2011, 16h47
kyros21
ok donc pour cela, on ajoute & et " pour cibler ce qui est a interpréter, et on ajoute .Adress à la fin du range?

j'avais pas tilté

et comme je suis un peu dur de la feuille, pour une formule comme celle ci?quelle est la syntaxe a adopter?
Code:

1 2 3 Sheets("Top20 répartition h MO").Cells(4, variable2).Formula = "=PRODUCT(" & Cells(4, 6).Address ; Cells(4, variable3).Address & ")"
merci encore pour votre aide
22/11/2011, 18h22
ZebreLoup

Soit

Code:

"=PRODUCT(" & Cells(4, 6).Address & ":" & Cells(4, variable3).Address & ")"

Soit

Code:

"=PRODUCT(" & Range(Cells(4, 6),Cells(4, variable3)).Address & ")"

Essaie de bien comprendre le principe de création de texte, ce n'est pas compliqué.