Aide sur la fonction Somme.Si

**kyros21** · 21/11/2011, 14h21

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

**mercatog** · 21/11/2011, 14h29

SOMME.SI

**bueno3** · 21/11/2011, 16h43

Sommprod peut aussi marcher

**ZebreLoup** · 21/11/2011, 16h47

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+

**SfJ5Rpw8** · 21/11/2011, 19h35

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

avec Somme.SI :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Avec sommeprod

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**kyros21** · 22/11/2011, 08h24

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

**kyros21** · 22/11/2011, 09h26

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**ZebreLoup** · 22/11/2011, 10h14

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Mais quitte à faire du vba, tu veux vraiment écrire une formule ?

**kyros21** · 22/11/2011, 10h47

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

**ZebreLoup** · 22/11/2011, 10h58

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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 :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets(variable3).Name

**kyros21** · 22/11/2011, 11h08

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**ZebreLoup** · 22/11/2011, 11h47

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**kyros21** · 22/11/2011, 12h59

merci beaucoup pour cette simplification de macro

,
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

**ZebreLoup** · 22/11/2011, 13h26

Essaie de remplacer

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FormulaR1C1

par

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Formula

**kyros21** · 22/11/2011, 15h34

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

merci

**ZebreLoup** · 22/11/2011, 15h41

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

"=SUM(" & Range(Cells(4, variable3), Cells(17, variable3)).Address & ")"

**kyros21** · 22/11/2011, 16h47

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

**ZebreLoup** · 22/11/2011, 18h22

Soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Soit

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

"=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é.

Aide sur la fonction Somme.Si

Macros et VBA Excel

Discussions similaires

Partager

Partager