Calcul complexe dans un tableau

Version imprimable

08/11/2014, 20h48
DelilahH

Calcul complexe dans un tableau

Bonjour à tous,

Je viens vers vous avec un problème très particulier, j'ai des fichiers excels qui se présentent toujours de la même manière :
La première colonne contient une liste de couleurs (5): bleu, blanc, rouge, vert, blanc
Les 4 colonnes suivantes contiennent des propriétés sur ces couleurs que je dénomme simplement a,b,c et d.

Je dois effectuer les calculs suivants pour chaque colonne :
Diviser la valeur de la propriété a par la valeur moyenne des deux couleurs blanc pour la propriété a toujours et ce dans chaque colonne,

Je voudrais écrire une macro pour automatiser ces calculs mais je ne m'en sors pas j'ai des connaissances de base avec les IF mais pour les boucles FOR NEXT j'ai du mal de voir comment parcourir mon tableau et calculer la moyenne pour certaines lignes et pas d'autres...

Merci de votre aide !
08/11/2014, 21h03
Stef999

Bonjour,

Citation:

Diviser la valeur de la propriété a par la valeur moyenne des deux couleurs blanc pour la propriété a toujours et ce dans chaque colonne,

hum hum .... un exemple peut être ? histoire de dissiper mes brumes !

Cordialement
08/11/2014, 22h41
DelilahH

Bonsoir,

Bien sur, je sais que c'est assez compliqué à voir en fait!

Prenons un tableau exemple

Couleur a b c d

Bleu 1 1 1 1

Blanc 2 2 2 2

Vert 3 3 3 3

Rouge 3 3 3 3

Mauve 3 3 3 3

Blanc 3 3 3 3

Je voudrais dans un premier temps parcourir le tableau en VBA pour trouver tous les échantillons qui sont de couleurs blanches. Avec ces échantillons de couleurs blanches je voudrais faire la moyenne par colonne soit pour la propriété a puis b puis c et enfin d :

(Valeur du blanc numéro 1 colonne a + valeur du blanc numéro 2 colonne a) / nombre de blancs
(Valeur du blanc numéro 1 colonne b + valeur du blanc numéro 2 colonne b) / nombre de blancs
(Valeur du blanc numéro 1 colonne c + valeur du blanc numéro 2 colonne c) / nombre de blancs
(Valeur du blanc numéro 1 colonne d + valeur du blanc numéro 2 colonne d) / nombre de blancs

Je voudrais conserver ces valeurs dans des variables styles : moy_blanc_a, ...

Ensuite je voudrais créer un deuxième tableau de résultats (toujours à l'aide d'une macro) qui reprend la colonne échantillon mais sans reprendre les échantillons blancs donc

Couleur a b c d

Bleu

Vert

Rouge

Mauve

Dans les colonnes de résultats il faudrait que j'écrive pour chaque échantillon:
valeur_échantillon_a*100 / moy_blanc_a pour le bleu, vert, mauve, rouge
valeur_échantillon_b*100 / moy_blanc_b pour le bleu, vert, mauve, rouge
valeur_échantillon_c*100 / moy_blanc_c pour le bleu, vert, mauve, rouge
valeur_échantillon_d*100 / moy_blanc_d pour le bleu, vert, mauve, rouge

Les valeur_échantillon_a(b,c ou d) sont en fait les chiffres du premier tableaux.

J'espère avoir été plus clair mais je suis consciente que mon problème est quelque peu complexe ^^

Merci!

1 pièce(s) jointe(s)

Re ,

Alors il faudrait pour comprendre les tableaux aller jeter un coup d'oeil à :
"Conceptualisation des variables tableau en VBA et Application à l'optimisation du code sous Excel" (Didier Gonard) http://didier-gonard.developpez.com/
et puis "Utiliser les variables tableaux en VBA Excel" (SilkyRoad) http://silkyroad.developpez.com/

A condition que le tableau commence en A1 ... cela crée à coté ce qui est demandé
Attention il n'y a aucun code de contrôle des données

Pièce jointe 160947

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
Sub CalculBlanc()
 
Dim TabInit As Variant
Dim TabFinal As Variant
 
Dim MoyBlc() As Double
Dim NbBlc As Long
Dim PosTabSortie As Long
Dim NbLigneTabInit As Long
Dim NbColonneTabInit As Long
 
TabInit = Cells(1, 1).CurrentRegion
 
NbLigneTabInit = UBound(TabInit, 1)
NbColonneTabInit = UBound(TabInit, 2)
 
ReDim MoyBlc(1 To NbColonneTabInit - 1)
 
'Comptage des blancs
For x = 1 To NbLigneTabInit
    If TabInit(x, 1) = "Blanc" Then
        NbBlc = NbBlc + 1
        For y = 1 To UBound(MoyBlc, 1)
            MoyBlc(y) = MoyBlc(y) + TabInit(x, y + 1)
        Next y
    End If
Next x
 
'Calcul des moyennes
For y = 1 To UBound(MoyBlc, 1)
    MoyBlc(y) = MoyBlc(y) / NbBlc
Next y
 
'Création du tableau de sortie
ReDim TabFinal(1 To NbLigneTabInit - NbBlc, 1 To NbColonneTabInit)
'ligne de titre
PosTabSortie = 1
For y = 1 To NbColonneTabInit
    TabFinal(PosTabSortie, y) = TabInit(PosTabSortie, y)
Next y
'Autres lignes
For x = 2 To NbLigneTabInit
    If TabInit(x, 1) <> "Blanc" Then
        PosTabSortie = PosTabSortie + 1
        TabFinal(PosTabSortie, 1) = TabInit(x, 1)
        For y = 2 To NbColonneTabInit
            TabFinal(PosTabSortie, y) = TabInit(x, y) * 100 / MoyBlc(y - 1)
        Next y
    End If
Next x
 
Range(Cells(1, NbColonneTabInit + 2), Cells(UBound(TabFinal, 1), UBound(TabFinal, 2) + NbColonneTabInit + 1)) = TabFinal
 
End Sub

09/11/2014, 08h39
SfJ5Rpw8

bonjour,

oui je crois que le plus dur c'est de comprendre l’énoncé ... je te propose une solution via formule ....

tout d'abords pour caculer la moyenne si ton premier tableau est placé en A1 cela donne pour la moy_blanc_a :

Code:

=MOYENNE.SI($A$2:$A$7;"blanc";B2:B7)

pour moy_blanc_b et suivant on décale vers la gauche

Code:

=MOYENNE.SI($A$2:$A$7;"blanc";C2:C7)

.....

ensuite si tu place ton 2°Tableau en A14...
tu peu avoir valeur_échantillon_? ... grâce à la formule :

Code:

=INDEX(B$2:B$7;EQUIV($A15;$A$2:$A$7))

placée en B15 puis étendue de B15 à E18...

et ensuite modifier cette formule pour prendre en compte la moyenne blanche

Code:

=INDEX(B$2:B$7;EQUIV($A15;$A$2:$A$7))*100/MOYENNE.SI($A$2:$A$7;"blanc";B$2:B$7)

placée en B15 puis étendue de B15 à E18...

voilà ..
10/11/2014, 17h45
DelilahH

Bonjour à vous deux,

Désolé du retard de ma réponse mais j'étais en weekend loin de mon bureau, un tout grand merci! grâce à vous j'ai compris comment manipuler des tableaux en VBA qui peuvent varier en dimension et comment stocker mes calculs dans ces tableaux sans avoir besoin d'écrire des données dans une feuille Excel pour les remanipuler !

Merci!
10/11/2014, 22h14
DelilahH
Re bonsoir,

En chipotant ce soir je rencontre un problème pour la recopie des résultats, je voudrais transférer le tableau final dans une autre feuille excel j'ai donc écris :
Code:

1 2 Sheets("feuil2").Range(Cells(1, NbColonneTabInit + 2), Cells(UBound(TabFinal, 1), UBound(TabFinal, 2) + NbColonneTabInit + 1)) = TabFinal
Mais il renvoit une erreur 1004 je me suis dit que en ajoutant :
Code:

1 2 Sheets("feuil2").Range(Cells(1, NbColonneTabInit + 2), Cells(UBound(TabFinal, 1), UBound(TabFinal, 2) + NbColonneTabInit + 1)).value = TabFinal
Mais même erreur, à mon avis j'ai mal compris la partie recopie d'un array vers une worksheet

Merci pour vos lumières :-)
11/11/2014, 12h31
Stef999

Bonjour

Quand on utiliser "range(cell1, cell2)" c'est bon pour la feuille en cours ...
Sinon il faut indiquer le chemin de qualification complet : "feuil2.range(feuil2.cell1, feuil2.cell2)"

Dans notre cas :

Code:

Sheets("feuil2").Range(Sheets("feuil2").Cells(1, 1), Sheets("feuil2").Cells(UBound(TabFinal, 1), UBound(TabFinal, 2))).value = TabFinal

Stéphane
11/11/2014, 12h34
DelilahH

Bonjour Stéphane,

Merci pour votre réponse !

Je comprends mieux, je pensais qu'en précisant Sheets(Feuil2) tout ce que je mettais était inhérent à la feuille 2 !

Couleur	a	b	c	d
Bleu	1	1	1	1
Blanc	2	2	2	2
Vert	3	3	3	3
Rouge	3	3	3	3
Mauve	3	3	3	3
Blanc	3	3	3	3