Formule Excel ! Au secours !
Fermé
lily64
-
12 mars 2009 à 14:14
leplot Messages postés 191 Date d'inscription lundi 1 décembre 2008 Statut Membre Dernière intervention 13 août 2015 - 13 mars 2009 à 07:06
leplot Messages postés 191 Date d'inscription lundi 1 décembre 2008 Statut Membre Dernière intervention 13 août 2015 - 13 mars 2009 à 07:06
A voir également:
- Formule Excel ! Au secours !
- Formule excel - Guide
- Liste déroulante excel - Guide
- Formule moyenne excel - Guide
- Formule excel si et - Guide
- Formule excel pour additionner plusieurs cellules - Guide
4 réponses
leplot
Messages postés
191
Date d'inscription
lundi 1 décembre 2008
Statut
Membre
Dernière intervention
13 août 2015
130
12 mars 2009 à 16:58
12 mars 2009 à 16:58
Je propose une solution entièrement sous Excel donc sans VBA.
=SI(LIGNE(A3)-1<=Nb_Lignes_X;X!A3;SI(LIGNE(A3)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!L"& LIGNE(A3)-Nb_Lignes_X & "C1";FAUX);SI(LIGNE(A3)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!L"& LIGNE(A3)-Nb_Lignes_X - Nb_Lignes_Y & "C1";FAUX);"")))
Le principe est de remplir la feuille W avec les lignes de X puis de Y puis de Z en fonction de la ligne où l'on se trouve dans W et les nombres de lignes de X, Y et Z.
Premièrement dans un onglet "paramètres", je crée trois cellules nommées Nb_Lignes_X (Y et Z) avec le nombre de valeurs dans la feuille X "=NBVAL(X!A2:A65536)"
Dans W, je teste le numéro de la ligne et le nombre de lignes de X, Y et Z
Si la ligne est inférieure à Nb_Lignes_X alors, je prends la ligne correspondance sinon ...
si la ligne est entre nb_Lignes_X et nb_Lignes_Y alors je prends la ligne correspondante dans Y sinon ...
si la ligne est entre nb_Lignes_Y et nb_Lignes_Z alors je prends la ligne correspondante dans Z sinon ...
L'exemple correspond à la celle A3.
La fonction indirecte permet d'appeler la cellule de la feuille avec la ligne calculée en format L1C1.
Il faut copier cette formule sur un nombre suffisant de ligne. Pour chaque colonne il faut modifier le numéro de la colonne à récupérer "C1" pour la colonne A, C2 pour B...
J'espère être clair car la formule est assez chiadée mais çà marche.
LePlot
=SI(LIGNE(A3)-1<=Nb_Lignes_X;X!A3;SI(LIGNE(A3)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!L"& LIGNE(A3)-Nb_Lignes_X & "C1";FAUX);SI(LIGNE(A3)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!L"& LIGNE(A3)-Nb_Lignes_X - Nb_Lignes_Y & "C1";FAUX);"")))
Le principe est de remplir la feuille W avec les lignes de X puis de Y puis de Z en fonction de la ligne où l'on se trouve dans W et les nombres de lignes de X, Y et Z.
Premièrement dans un onglet "paramètres", je crée trois cellules nommées Nb_Lignes_X (Y et Z) avec le nombre de valeurs dans la feuille X "=NBVAL(X!A2:A65536)"
Dans W, je teste le numéro de la ligne et le nombre de lignes de X, Y et Z
Si la ligne est inférieure à Nb_Lignes_X alors, je prends la ligne correspondance sinon ...
si la ligne est entre nb_Lignes_X et nb_Lignes_Y alors je prends la ligne correspondante dans Y sinon ...
si la ligne est entre nb_Lignes_Y et nb_Lignes_Z alors je prends la ligne correspondante dans Z sinon ...
L'exemple correspond à la celle A3.
La fonction indirecte permet d'appeler la cellule de la feuille avec la ligne calculée en format L1C1.
Il faut copier cette formule sur un nombre suffisant de ligne. Pour chaque colonne il faut modifier le numéro de la colonne à récupérer "C1" pour la colonne A, C2 pour B...
J'espère être clair car la formule est assez chiadée mais çà marche.
LePlot
Merci beaucoup pour ta formule chiadée! Mais je n'y comprends rien, je veux dire pourrais-tu m'expliquer étape par étape comment tu fais ? :S
Merci beaucoup ! ;)
Merci beaucoup ! ;)
leplot
Messages postés
191
Date d'inscription
lundi 1 décembre 2008
Statut
Membre
Dernière intervention
13 août 2015
130
12 mars 2009 à 20:51
12 mars 2009 à 20:51
Salut
J'ai préparé un fichier Excel avec quelques explications et en particulier la décomposition du calcul en plusieurs colonnes.
Mais je ne sais pas comment déposer un fichier sur le forum...
Je vais tenter une explication de texte (j'ai apporté une modification sur la formule)
=SI(LIGNE(A2)-1<=Nb_Lignes_X;X!A2;SI(LIGNE(A2)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X);SI(LIGNE(A2)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!A"& LIGNE(A2)-Nb_Lignes_X - Nb_Lignes_Y);"")))
1) Nb_Lignes_X correspond à une cellule nommée avec pour formule =NBVAL(X!A2:A65536)
Compte le nombre de valeurs dans la colonne A de la feuille X.
Il ne faut pas de lignes vides car cela planterait la suite.
idem pour X et Y. Ces 32 cellules peuvent être mises n'importe où
2) La feuille W
La feuille W contient les mêmes titres de colonnes que les feuilles X, Y Z
X a 2 lignes
Y a 1 ligne
Z a 3 lignes
La cellule W!A2 doit appeler la X!A2
La cellule W!A3 doit appeler la X!A3
La cellule W!A4 doit appeler la Y!A2
La cellule W!A5 doit appeler la Z!A2 ...
idem pour les colonnes B, C etc...
3 Première partie de la formule SI(LIGNE(A2)-1<=Nb_Lignes_X;X!A2;
La ligne de la cellule de W est "ligne(A2)" soit 2, 3...
Nb_Lignes_X=2 (2 lignes)
n° ligne -1 <= Nb_Lignes_X
A2 2-1 <= 2 ==> prendre la cellule X!A2
A3 3-1 <= 2 ==> prendre la cellule X!A3
A4 4-1 faux ==> passer à la deuxième partie de la condition
4) Deuxième condition
SI(LIGNE(A2)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X);
La ligne de la cellule de W est 4,5,6...
Nb_Lignes_X=2 (2 lignes)
Nb_Lignes_Y=1 (1 lignes)
n° ligne - Nb_Lignes_X - 1 <= Nb_Lignes_Y
A4 4 - 2 - 1 <= 1 ==> prendre la cellule Y!A2 (voir ensuite la fonction Indirect)
A5 5 - 2 - 1 faux ==> passer à la troisième partie de la condition
5) Troisième condition
SI(LIGNE(A2)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!A"& LIGNE(A2)-Nb_Lignes_X - Nb_Lignes_Y);"")))
La ligne de la cellule de W est 5,6,7...
Nb_Lignes_X=2 (2 lignes)
Nb_Lignes_Y=1 (1 lignes)
Nb_Lignes_Z=3 (3 lignes)
n° ligne - Nb_Lignes_X - Nb_Lignes_X - 1 <= Nb_Lignes_Y
A5 5 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A2 (voir ensuite la fonction Indirect)
A6 6 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A3 (voir ensuite la fonction Indirect)
A7 7 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A4 (voir ensuite la fonction Indirect)
A8 8 - 2 - 1 - 1 faux ==> mettre "" pour vide
6) fonction indirect
Cette fonction permet d'appeler une cellule à partir de son adresse en texte donc il est possible de construire l'adresse.
Pour appeler Y!A2 : indirect("Y!A2")
comme le 2 est variable : INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X)
Pour les autres colonnes, il faut changer le A dans la fonction indirecte.
Toute modification dans les feuilles X, Y, Z se répercute dans W.
Il n'y a donc aucun tri possible dans W. A mon avis, il faut passer par un tableau croisé pointant sur W pour analyser les données.
Par contre pour avoir des données dans W et non des formules, il faut passer par du VBA pour copier les données ou tout simplement faire saisir tout le monde dans W avec un cellule pour mettre le nom du commercial.
A + pour une autre explication de texte.
Leplot
J'ai préparé un fichier Excel avec quelques explications et en particulier la décomposition du calcul en plusieurs colonnes.
Mais je ne sais pas comment déposer un fichier sur le forum...
Je vais tenter une explication de texte (j'ai apporté une modification sur la formule)
=SI(LIGNE(A2)-1<=Nb_Lignes_X;X!A2;SI(LIGNE(A2)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X);SI(LIGNE(A2)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!A"& LIGNE(A2)-Nb_Lignes_X - Nb_Lignes_Y);"")))
1) Nb_Lignes_X correspond à une cellule nommée avec pour formule =NBVAL(X!A2:A65536)
Compte le nombre de valeurs dans la colonne A de la feuille X.
Il ne faut pas de lignes vides car cela planterait la suite.
idem pour X et Y. Ces 32 cellules peuvent être mises n'importe où
2) La feuille W
La feuille W contient les mêmes titres de colonnes que les feuilles X, Y Z
X a 2 lignes
Y a 1 ligne
Z a 3 lignes
La cellule W!A2 doit appeler la X!A2
La cellule W!A3 doit appeler la X!A3
La cellule W!A4 doit appeler la Y!A2
La cellule W!A5 doit appeler la Z!A2 ...
idem pour les colonnes B, C etc...
3 Première partie de la formule SI(LIGNE(A2)-1<=Nb_Lignes_X;X!A2;
La ligne de la cellule de W est "ligne(A2)" soit 2, 3...
Nb_Lignes_X=2 (2 lignes)
n° ligne -1 <= Nb_Lignes_X
A2 2-1 <= 2 ==> prendre la cellule X!A2
A3 3-1 <= 2 ==> prendre la cellule X!A3
A4 4-1 faux ==> passer à la deuxième partie de la condition
4) Deuxième condition
SI(LIGNE(A2)-Nb_Lignes_X-1<=Nb_Lignes_Y;INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X);
La ligne de la cellule de W est 4,5,6...
Nb_Lignes_X=2 (2 lignes)
Nb_Lignes_Y=1 (1 lignes)
n° ligne - Nb_Lignes_X - 1 <= Nb_Lignes_Y
A4 4 - 2 - 1 <= 1 ==> prendre la cellule Y!A2 (voir ensuite la fonction Indirect)
A5 5 - 2 - 1 faux ==> passer à la troisième partie de la condition
5) Troisième condition
SI(LIGNE(A2)-Nb_Lignes_X-Nb_Lignes_Y-1<=Nb_Lignes_Z;INDIRECT("Z!A"& LIGNE(A2)-Nb_Lignes_X - Nb_Lignes_Y);"")))
La ligne de la cellule de W est 5,6,7...
Nb_Lignes_X=2 (2 lignes)
Nb_Lignes_Y=1 (1 lignes)
Nb_Lignes_Z=3 (3 lignes)
n° ligne - Nb_Lignes_X - Nb_Lignes_X - 1 <= Nb_Lignes_Y
A5 5 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A2 (voir ensuite la fonction Indirect)
A6 6 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A3 (voir ensuite la fonction Indirect)
A7 7 - 2 - 1 - 1 <= 3 ==> prendre la cellule Z!A4 (voir ensuite la fonction Indirect)
A8 8 - 2 - 1 - 1 faux ==> mettre "" pour vide
6) fonction indirect
Cette fonction permet d'appeler une cellule à partir de son adresse en texte donc il est possible de construire l'adresse.
Pour appeler Y!A2 : indirect("Y!A2")
comme le 2 est variable : INDIRECT("Y!A"& LIGNE(A2)-Nb_Lignes_X)
Pour les autres colonnes, il faut changer le A dans la fonction indirecte.
Toute modification dans les feuilles X, Y, Z se répercute dans W.
Il n'y a donc aucun tri possible dans W. A mon avis, il faut passer par un tableau croisé pointant sur W pour analyser les données.
Par contre pour avoir des données dans W et non des formules, il faut passer par du VBA pour copier les données ou tout simplement faire saisir tout le monde dans W avec un cellule pour mettre le nom du commercial.
A + pour une autre explication de texte.
Leplot
eriiic
Messages postés
24570
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
23 avril 2024
7 215
12 mars 2009 à 22:06
12 mars 2009 à 22:06
Bonsoir,
Tu peux déposer ton fichier sur cijoint.fr et coller ici le lien fourni.
eric
Tu peux déposer ton fichier sur cijoint.fr et coller ici le lien fourni.
eric
leplot
Messages postés
191
Date d'inscription
lundi 1 décembre 2008
Statut
Membre
Dernière intervention
13 août 2015
130
13 mars 2009 à 07:06
13 mars 2009 à 07:06
Merci Eriic pour l'info
http://www.cijoint.fr/cjlink.php?file=cj200903/cijoixktyo.xls
http://www.cijoint.fr/cjlink.php?file=cj200903/cijoixktyo.xls
