Problème avec des cellules videsRésolu

Question

Bonjour à tous,

Je travaille sous Excel et je commence à m'arracher les cheveux... 
Voilà mon soucis:

J'ai crée une macro qui trace un graphique tout simple à partir de données (des valeurs chiffrées ordinaires) qui sont entrées sur une ligne (A13 jusqu'à O13).
J'ai aussi demandé dans ma macro qu'un courbe de tendance soit tracée.
Mes problèmes:

1- Comment puis je lui dire de ne pas tracer cette courbe de tendance si les dernières cellules sont vides? En effet, si par exemple les cellules M13 à O13 ne contiennent aucune valeurs cela fausse la courbe de tendance (qui est une polynomiale).  Dépendament du degrès choisi celle ci fait un pic vers le haut ou vers le bas...ce qui fausse pas mal le résultat!

2- Si par hasard la cellule B13 (au milieu des autres) ne contient aucune valeur, comment faire pour que lors du tracage du graph, cette cellule vide soit ignorée?

Merci d'avance à tous.

Pafacile

bigfish_le vrai · Accepted Answer

Salut,

voici une methode sans aucune boucle, qui utilise la fonction filtre d'excel. 

remplace cette partie :

    Range("A14:K15").Select <--- remarque cette ligne ne sert a rien
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("DONNÉES").Range("A14:K15"), PlotBy _
        :=xlRows

Par ce qui suit :

Noublie pas de declarer une nouvelle variable: 
Dim MonTableau As String

    Sheets("DONNÉES").Range("A14:K15").Copy
    Worksheets.Add after:=Sheets(Worksheets.Count) 'creation d'une feuille temporaire en derniere position
    
   'on pose les données dans la nouvelle feuille en les tranposant en colonne pour pouvoir utiliser l'autofilter
   'qui helas ne fonctionne que sur des colonnes
   'attention si la ligne suivante est dans le with cela ne marchera pas !
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    With Selection
        .AutoFilter 'on active le filtre automatique
        .AutoFilter Field:=1, Criteria1:="<>" 'on applique les parametres de filtrage
        .Copy 'copy des données filtrées
    End With

    Sheets("Sheet1").Activate 'retour sur la feuille source
    'on pose les données filtrée dans la feuille source en les tranposant en ligne
    'attention ne sachant pas ou tu veux mettre ton tableau netoyer de ses cellules vides, j'ai choisi de le mettre en A17
    'tu peux le mettre ou tu veux enremplacent A17. A17 etant la cellule en haut a gauche du tableau
    Sheets("Sheet1").Range("A17").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    MonTableau = Selection.Address 'on en profite pour recuperer l'adresse du nouveau tableau de données
    Application.DisplayAlerts = False 'on desactive les messages d'alertes pour la suppression de la feuille temporaire
    Sheets(Worksheets.Count).Delete 'on supprime la feuille temporaire
    Application.DisplayAlerts = True 'on réactive les messages d'alertes

    Charts.Add 'creation du graphique
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("DONNÉES").Range(MonTableau), PlotBy _
        :=xlRows

Vala 

A+

bigfish_le vrai · Answer

Salut,

c'est sympas de me tenir informé !!! lien

Je suis etonné que ce que je t'ai donné ne fonctionne pas. J'ai testé de mon cote et cela marche.Les cellules vides (il faut qu'elles soient vide pas = 0) n'influance pas la courbe de tendance. 
As-tu essayé de remplacer la constante xlInterpolated parxlNotPlotted?

A+

cs_pafacile · Answer

Salut!

Excuse moi, je pensais que comme je m'étais inscrite dans le mauvais forum, la réponse que tu m'avais donné ne fonctionnerait finalement pas...

Je teste et je donne des nouvelles

Merci!

Pafacile

cs_pafacile · Answer

Ok, Cela fonctionne très bien pour la courbe tracé par les points.
Par exemple si les donnée sont:

3,   5,   4,  rien,  4,   3

La courbe ne tient pas compte du ''rien'' et cela fonctionne!

Merci vraiment!

Par contre maintenant mon problème subsiste au niveau du tracage de la courbe de tendance. 

Par exemple dans le cas ou les données sont:

3,   rien,   4,   5,  rien,    rien

Comme les ''rien'' sont à la fin la courbe de tendance qui, dans mon cas est une polynomiale d'ordre 2, celle-ci continue de se tracer et elle fait une brusque hausse (ou bien chute, ca dépend). C'est un problème mathématique bien sur puisqu'une polynomiale tend vers + ou - l'infini.
Je ne veux aucune autre forme de courbe de tendance donc il faut trouver une facon de dire à excel d'arreter de tracer cette courbe dans le cas de cellules vides à la fin de la série....

Une idée? ....

Merci encore Bigfish_le vrai

Pafacile

cs_pafacile · Answer

Si cela peut t'aider, voici mon code sous sa nouvelle forme:

Sub Macro1()
'
' Macro1 Macro
' Vernis teste adhérance
'
' Vérifie les valeurs contenus dans chaque cellule, si > 4 affiche: Valeurs incorrectes
For Each Cell In Range("B15:K15")
If Cell.Value > 4 Then
Cell.Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
MsgBox "VALEURS INCORRECTS", 48, "Information fichier"




msg = "Voulez-vous corriger les valeurs incorrectes ?"
' Définit le message.
Style = vbYesNo + vbDefaultButton1
'Définit les boutons.
Title = "Attention !"
' Définit les titres.
Réponse = MsgBox(msg, Style, Title)
If Réponse = vbYes Then

ActiveCell.Select
Exit Sub
End If



' Sinon remet les cellules en blanc et continue

Else
Cell.Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlSolid
End With
End If
Next





' Selectionne les cellules du tableau

    Range("A14:K15").Select
    Charts.Add
    ActiveChart.ChartType = xlXYScatterSmooth
    ActiveChart.SetSourceData Source:=Sheets("DONNÉES").Range("A14:K15"), PlotBy _
        :=xlRows
        
 ' Test si un graphique du même type existe deja et le supprime si oui en évitant l'affichage du message d'erreur
        Application.DisplayAlerts = False
    If WsExist("ADHÉRANCE") Then
        Sheets("ADHÉRANCE").Delete
    End If
    Application.DisplayAlerts = True
    
 ' Trace le graphique
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="ADHÉRANCE"
    With ActiveChart
        
        .DisplayBlanksAs = xlInterpolated
        
        .HasTitle = True
        .ChartTitle.Characters.Text = "QUALITÉ DU VERNIS (ADHÉRANCE)"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveChart.PlotArea.Select
    With Selection.Border
        .ColorIndex = 16
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
    
 ' Fait un dégradé pour le fond du graphique
    Selection.Fill.TwoColorGradient Style:=msoGradientHorizontal, Variant:=1
    With Selection
        .Fill.Visible = True
        .Fill.ForeColor.SchemeColor = 3
        .Fill.BackColor.SchemeColor = 4
    End With
    
 ' Trace une courbe de tendance polynomiale d'ordre 2
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Trendlines.Add(Type:=xlPolynomial, Order:=2 _
        , Forward:=0, Backward:=0, DisplayEquation:=False, DisplayRSquared:= _
        False).Select
    ActiveChart.PlotArea.Select
    With ActiveChart
        .HasAxis(xlCategory, xlPrimary) = True
        .HasAxis(xlValue, xlPrimary) = True
    End With
    
 ' Limite la graduation des axes abcisses et ordonnées
     ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MinimumScale = 0
        .MaximumScale = 4
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
    End With
    
End Sub



' Fonction qui test si un graphique du même type existe déjà
Function WsExist(Nom$) As Boolean
On Error Resume Next
WsExist = Sheets(Nom).Index
End Function

Voila...

bigfish_le vrai · Answer

...

j'insiste encore avec mon option mais de coter j'ai resolut le probleme avec la constante emplacer la constante xlInterpolated a la place de xlNotPlotted.
Cette constante est interressante car elle vas demander a excel d'estimer une valeur en fonction pour remplacer le rien.

A+

cs_pafacile · Answer

Lorsque je fais ceci, il en resulte juste que la courbe qui relie les points est tracé même dans le cas de la valeur rien. A ce niveau pas de soucis, je l'avais fait. (voir le code)

Pour la polynomiale, cela ne change rien. Et elle continue d'interpoler à la fin pour les 2 dernières valeurs ''Rien''.

Pafacile

PS: je dois cliquer sur reponse acceptée?

cs_pafacile · Answer

Wooaw!

Je teste tout ceci et je te donne des nouvelles.

Merci beaucoup! 

Pafacile

mortalino · Answer

Salut,

honte à moi, j'ai eu la flemme de lire les réponses données, mais juste pour dire que si tu veux chercher la dernière colonne utilisée à partir de A13, il faut faire :

Dim iCol as inteeger, sPlage as string

iCol = Range("A13").End(xlToright).Column
sPlage = Range(Cells(13, 1), Cells(13, iCol))
MsgBox sPlage

pas testé mais ça devrait le faire
@++

le mystérieux chevalier,"Provençal, le gaulois"
Forum Office & VBA

mortalino · Answer

sPlage = Range(Cells(13, 1), Cells(13, iCol)).Address

@++

le mystérieux chevalier,"Provençal, le gaulois"
Forum Office & VBA

bigfish_le vrai · Answer

...
au fait j'ai oublié une remarque, si ton tableau source est toujours comme ceci

(ce n'est qu'un exemple)

ligne14 : 3  |  5  |  4  |  rien  |  4  |  3 |
ligne15 : 3  |  5  |  4  |  rien  |  4  |  3 |

en face de rien en ligne 14 il n'y a rien en ligne 15 donc cela marchera sans probleme, mais si ton tableau est comme suit :

ligne14 : 3  |  5  |  4  | rien |  4  |  3 |

ligne15 : 3  |  5  |  4  |   3   |  4  |  3 |

dans ce cas on vas avoir un probleme il te faudra peut etre changer les parametres de filtrages en remplacent Field:=1 par Field:=2...

non tout bien reflechit, tu devrais changer Field:=1 par Field:=2 avant meme d'essayer

De toute façon tient moi informé

Have a good night

bigfish_le vrai · Answer

Salut mortalino 

en fait c'est un probleme de cellule vide dans un tableau de données qui alimente un graphique. Ces cellules vides influance la courbe de tendance qui, dans son cas est une polynomiale d'ordre 2... c'est jolie non 
On a surtout travailé a supprimer ces cellules en creent un nouveau tableau sans les cellules vide a l'aide la fonction autofilter d'excel et du parametre nonblanks qui s'ecrit en VBA "<>"

mortalino · Answer

Salut bigfish_le_vrai

il me semblait bien que vous trouviez une soluce ensemble pour contourner un problème, mais trop de texte à lire 
(et pas en état)
Merci pour ce résumé, qui me donne un aperçu de ce tu proposais, j'imagine en gros la proposition faite.

(grrr.. 'une polynomiale d'ordre 2' : C'est pas faux !!  => j'ai fait Economie et social, donc c'est du chinois ! [enfin, des maths..]) 

@++

le mystérieux chevalier,"Provençal, le gaulois"
Forum Office & VBA

cs_pafacile · Answer

Bonjour à vous deux.

Tout d'abord Mortalino, merci de t'être interéssé à mes soucis!

Ensuite Bigfish_le vrai MERCI!

C'est Magique, Cela fonctionne parfaitement avec Field:= 2... Vraiment merci du fond du coeur. Je ne l'aurais jamais résolu seule ( moi et mes 2 semaines d'expérience en VBA). C'est vraiment sympa de prendre du temps comme cela pour aider les autres. Ce forum est Top.

Je ne crois pas pouvoir rendre la pareille mais si je peux faire qqc...

Pafacile
(Marine)

bigfish_le vrai · Answer

Salut,

de rien ce fut un plaisir 

Oui tu peux faire quelque chose pour nous et pour tous ceux qui pourraient avoir un probleme similaire, c'est de valider la/les reponse(s) qui ton aidees a resoudre ton probleme.

A+

Problème avec des cellules vides

15 réponses

Votre réponse

Discussions similaires