Double condition pour compter récurrence d'une donnée
Bonjour le forum,
j'ai décidé de créer une macro qui me permet de compter le nombre de problèmes qui surviennent sur une ligne de production.
Les données sont extraites à partir d'un fichier txt que j'ouvre avec excel.
Je vais donc créer un simple tableau dans une nouvelle feuille et aller piocher les données dans mon fichier txt.
Mais j'aurais besoin de votre aide,
explications :
Dans la colonne BA de mon fichier txt, j'ai plusieurs stations : "SIMATIC4" , "SIMATIC4_1", "TEST", etc...
Dans la colonne BQ, j'ai plusiseurs types de problèmes : "FC: dMax Error Tq Asic 1", " FC: ZeroTorque Asic 1 [Nm]", " FC: Torsion Bar Rate CCW", " FC: Torsion Bar Rate CW",etc... (si un problème intervient la case est remplie par la chaine de caractère correspondant au problème, sinon la case est vide)
Je souhaite remplir mon tableau de la façon suivante :
- cellule B5 = Nombre de problèmes "FC: Torsion Bar Rate CCW" pour la station "SIMATIC4_1"
- cellule B6 = Nombre de problèmes "FC: Torsion Bar Rate CCW" pour la station "SIMATIC4"
- cellule C5 = = Nombre de problèmes "FC: Torsion Bar Rate CW" pour la station "SIMATIC4_1"
- cellule C6 = Nombre de problèmes "FC: Torsion Bar Rate CW" pour la station "SIMATIC4"
- D5 = B5 + C5 (total de problèmes sur SIMATIC4_1)
- D6 = B6 + C6 (total de problèmes sur SIMATIC4)
- B7 = B5 + B6 (total de problèmes "FC: Torsion Bar Rate CCW")
- C7 = C5 + C6 (total de problèmes "FC: Torsion Bar Rate CW")
Mon problème est la programation de ce qui est en gras, je ne trouve pas comment compter le nombre de problème définis en fonction de la station!
Si une âme charitable pouvait m'expliquer le code, la syntaxe à utiliser, ça serait génial!
Merci d'avance
Bonjour,
un exemple (sans tester vu que pas de fichier):
pour cellule B5 = Nombre de problèmes "FC: Torsion Bar Rate CCW" pour la station "SIMATIC4_1"
=NB.SI.ENS(BA:BA;"SIMATIC4_1";BQ:BQ;"FC: Torsion Bar Rate CCW")Après si chaque ligne correspond forcement à un problème, pour tes totaux tu peux utiliser directement :
pour D5 :
=NB.SI(BA:BA;"SIMATIC4_1")ce qui comptera directement le nombre de ligne où apparait ce poste.
Cordialement,
Aymeric
bonjour
met un fichier joint : soit une maquette represententative ou un tronçon de l'actuel pour voir la structure
et surtout saisis manuellement quelques vavaleurs que tu desires voir "piochées"
cordialement
Merci pour ces réponses rapides!
Ca n'a pas l'air de fonctionner Aymeric
Je vous joint un exemple de fichier que j'ai à traiter pour que vous puissiez voir la structure!
Si tu as saisie les formules excatement comme ça, c'est normal que cela ne marche pas, vu que ton tableau à remplir n'est pas sur la même feuille que tes données (chose que tu n'avais pas précisé dans l'enoncé initial);
Il faut donc préciser de quelle feuille viennent les données de la colonne BA, voir exemple dans ton fichier joint en retour.
Cordialement,
Oui voilà, les données sont à piocher dans une autre feuille qui porte le nom du fichier txt.
Selon toi, la formule est donc la suivante:
=_xlfn.COUNTIFS(P2110103!BA:BA;"SIMATIC4_1";P2110103!BQ:BQ;"FC: Torsion Bar Rate CCW")
Mais ça ne marche pas, j'ai "#NOM?"qui apparait dans la cellule.
Et un autre détail :
quelle est la syntaxe à utiliser dans ma macro pour remplacer le nom de la feuille dans laquelle on va piocher les données par un nom de variable (exemple : Fichier), comme le nom n'est jamais le même.
Dois-je utiliser
=_xlfn.COUNTIFS(& Fichier!BA:BA;"SIMATIC4_1";& Fichier!BQ:BQ;"FC: Torsion Bar Rate CCW")
Je suis vraiment désolé, je débute en VBA...
A tester (en partant du principe que le tableau est déjà créé avant)
Sub test()
Dim Formule As String, Fichier As String
Fichier = Worksheets(1).Name
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$5;" & Fichier & "!BQ:BQ; $B$4)"
Range("B5").FormulaLocal = Formule
End SubCordialement
edit : enfin c'est vrai que j'étais parti sur des formules car je n'avais pas fais attention que c'était pour une macro, sinon on pourrait limite faire le calcul direct dans la macro et juste afficher le résultat, mais faut tout reprendre à zéro sinon ><
Surtout que j'ai fais quelques essais, mais les formules excel sur une feuille normale ou en anglais dans du vba n'ont pas du tout la même syntaxe, et je n'arrive pas à incorporer le nom de fichier variable, et étant au boulot, je n'ai pas spécialement le temps de faire 2h d'essais désolé ^^
bonjour
un essai a coller en b5
=SOMMEPROD(--(P2110103!$BA$1:$BA$30=Feuil3!A5)*(P2110103!$BQ$1:$BQ$30=Feuil3!B4))
ce que je ne comprend pas c'est si les valeur en colonne br correspondent au critere b4 ouc4
cordialement
@ Tulipe
La formule en NB.SI.ENS marche , pas besoin d'un sommeprod, mais il ne lui faut pas une formule, tu as dut être induit en erreur par mes précédents postes, mais il cherche à le faire par macro.
bonjour
c'est plus chic
il va vraiment falloir que je m'y mette
cordialement
Merci beaucoup Zirak de te pencher sur mon problème à ton boulot!
Fais gaffe de ne pas te faire choper!
Et merci à tulipe pour ton aide, mais en effet je désire utiliser une macro.
Cette formule à l'air de fonctionner, je vais essayer de travailler avec ça et je reviens si j'ai besoin d'aide!!
Merci!
EDIT : voici mes lignes de code pour remplir mon tableau préalablement créé :
Fichier = Worksheets(2).Name
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$5;" & Fichier & "!BQ:BQ; $B$4)"
Range("B5").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$6;" & Fichier & "!BQ:BQ; $B$4)"
Range("B6").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$5;" & Fichier & "!BQ:BQ; $C$4)"
Range("C5").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$6;" & Fichier & "!BQ:BQ; $C$4)"
Range("C6").FormulaLocal = Formule
Range("B7").FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("C7").FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("D5").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("D6").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("D7").FormulaR1C1 = "=R[-2]C+R[-1]C"Et avec ça j'ai toujours le message "#NOM!" dans mes cellules
Mais je sens qu'on se rapproche!!
Peux-tu joindre ton fichier test où tu viens de saisir ces lignes de codes ?
Car la dernière formule que je t'ai donné et avec laquelle tu viens de construire ta macro ci-dessus me retourne bien un chiffre moi, et pas une erreur de type #NOM!
ton worksheets(2).name correspond bien à la feuille dont le nom d'onglet = nom du fichier et ou se trouvent les données ?
Dans un premier module, j'ai saisis le code suivant, permettant de créer le tableau et sa mise en forme dans une nouvelle feuille :
Sub saisie()
Application.ScreenUpdating = False
Dim Formule As String, Fichier As String
ChDir "K:\Projets\2-Projet C1 MCA\98-Macro Excel\production"
Fichier = Application.GetOpenFilename
Workbooks.OpenText Filename:=Fichier, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
Tab:=True, Semicolon:=True, Comma:=False, Space:=False, _
Other:=False, FieldInfo:=Array(1, 1)
Columns("BP:BP").Select
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Columns("BO:BO").Select
Selection.TextToColumns Destination:=Range("BO1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Range("BP1").Select
ActiveCell.FormulaR1C1 = "Value"
Range("BQ1").Select
ActiveCell.FormulaR1C1 = "Fail"
Sheets.Add
Range("A3").Formula = "Nombre de fail"
Range("A4").Formula = "Station ID"
Range("B3").Formula = "fail"
Range("B4").Formula = "FC: Torsion Bar Rate CCW"
Range("C4").Formula = "FC: Torsion Bar Rate CW"
Range("D4").Formula = "Total"
Range("A5").Formula = "SIMATIC4_1"
Range("A6").Formula = "SIMATIC4"
Range("A7").Formula = "Total"
Range("A3:D7").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("A3,A4,B3,B4,C4,D4,A5,A6,A7").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Range("B3:C3").Select
Selection.Merge
End With
Range("A3:A7,B3:D4").Select
Range("B3").Activate
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("C:C").EntireColumn.AutoFit
Call test
Application.ScreenUpdating = True
End SubPuis dans un deuxième module j'ai saisis le code suivant :
Sub test()
Fichier = Worksheets(2).Name
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$5;" & Fichier & "!BQ:BQ; $B$4)"
Range("B5").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$6;" & Fichier & "!BQ:BQ; $B$4)"
Range("B6").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$5;" & Fichier & "!BQ:BQ; $C$4)"
Range("C5").FormulaLocal = Formule
Formule = "=NB.SI.ENS(" & Fichier & "!BA:BA; $A$6;" & Fichier & "!BQ:BQ; $C$4)"
Range("C6").FormulaLocal = Formule
Range("B7").FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("C7").FormulaR1C1 = "=R[-2]C+R[-1]C"
Range("D5").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("D6").FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("D7").FormulaR1C1 = "=R[-2]C+R[-1]C"
End SubJe ne sais pas s'il faut procéder de cette façon mais la macro s'éxécute bien en tout cas, seulement je n'obtiens pas de valeurs.
Et oui le worksheets(2).name correspond bien à la feuille dont le nom d'onglet = nom du fichier et ou se trouvent les données
Ok, mais joint un fichier si tu peux, que je puisse tester en mode débug sans devoir refaire un fichier test complet de mon coté; étant au boulot, je suis quand même assez ric-rac niveau temps :p
Bonjour
Comme je ne maitrise pas tout ça je vais sans doute dire une bêtise
La fonction NB.SI.ENS ne fonctionne que sur 2007 ( et ultérieur) et non sur 2003 ( comme le profil de Emeric le signale)
Désolé
Salut Banzai,
Ah, c'est surement de la que viens l'erreur #NOM! alors, merci de la précision, je pensais qu'elle fonctionnait toutes versions..
Bon bah on va pas s'embeter, on va faire direct le calcul en vba et on affichera le resultat direct dans la cellule du tableau, par contre pour ma part, ca ne sera que demain, à moins qu'un vba-iste ai le temps de se pencher dessus cet après-midi.
Cordialement,
Bonjour Banzai64,
et merci de ta remarque car je crois en effet que le problème vient de là, en effet j'utilise la version 2003 qui ne lit pas la fonction NB.SI.ENS, je viens de voir ça sur le net!
Il faut utiliser la fonction SOMMEPROD, mais comment adapter la syntaxe avec cette fonction?
Essai ça, basé sur la formule fournie par Tulipe plus tôt.
Formule = "=SOMMEPROD(--(" & Fichier & "!$BA$1:$BA$30=A5)*(" & Fichier & "!$BQ$1:$BQ$30=B4))par contre il avait défini une plage de 30 lignes en rapport avec to nfichier exemple, à modifier comme ceci pour toutes les colonnes, mais à tester si ca marche avec sommeprod de prendre les colonnes entières:
Formule = "=SOMMEPROD(--(" & Fichier & "!BA:BA=A5)*(" & Fichier & "!BQ:BQ=B4))Avec la formule suivante :
Formule = "=SOMMEPROD(--(" & Fichier & "!BA:BA=A5)*(" & Fichier & "!BQ:BQ=B4))J'ai une erreur de type #NOMBRE!
Je crois avoir trouvé d'où cela vient
re
jy connais pas grand chose en vba mais ; il me semble que les codes que j'ai vu sentent l'usine a gaz je ne sais pas ce qu'en pense Banzai ; pour des choses bien plus complexes ;j'ai vu bien plus short
attention avec l'emploi de colonne entieres mieux vaux nommer une plage
cordialement