Vous utilisez un navigateur obsolète. Il se peut que ce site ou d'autres sites Web ne s'affichent pas correctement. Vous devez le mettre à jour ou utiliser un navigateur alternatif.
J'ai chercher sur internet tout ce que j'ai trouver c'est qu'il faut passer par un macro mais je n'y connais rien, c'est pour quoi je viens demander de l'aide
J'ai plusieurs classeur Excel comporte 1 à 2 feuille avec chacune des données différente je veux donc récupère une partie dans chaque classeur pour en crée une nouvelle classeur (je sais que le plus simple c'est de refaire un nouveau tableau mais je ne peux pas car les données sont génère par une application ce qui veux dire que je ne peux pas modifier le classeur de données). explication plus détaillé:
classeur1 j'ai besoin trier en fonction de série récupère les numéros et les travaux à réaliser,
classeur2 en fonction de numéro récupère l'heure de début et fin
on obtiens donc dans la nouvelle classeur 4 nouvelle colonne: numéro,travaux à réaliser, l'heure de début et fin.
J'espère que mon explication a était claire, et je vous remercie d'avance pour le temps que vous avez pris.
J'ai corrigé un défaut dans la précédente pièce jointe. Il fallait remplacer sur la ligne 2:
* plusieurs NB.SI(P$2: P$36;B2) par SOMME((P$2: P$36=B2)*(N$2:N$36="N"))
* et des SI(P$2: P$36=B2 par SI((P$2: P$36=B2)*(N$2:N$36="N")
pour prendre correctement en compte la présence, ou non, du N dans la colonne Série.
@ plus
P.S : Dans l'ancienne version, si on mettait par ex 761 à la place de 122 sur la ligne 14, on obtenanit un 761 en plus dans la colonne A, or il ne le faut pas (la cellule N14 ne contient pas de N). Cela vient du fait que NB.SI(P$2: P$36;761) compte tout les 761 dans la plage P$2: P$36 sans prendre en compte l'existence d'une série N ou pas sur les lignes correspondantes. SOMME((P$2: P$36=B2)*(N$2:N$36="N")) en matriciel prend en compte ce dernier critère.
Une possibilité en pièce jointe avec les n° directement dans l'ordre croissant en colonne A. Puisque cela ne fonctionnait pas simplement avec un PETITE.VALEUR((calculs à partir de la plage 1;calculs à partir de la plage 2);LIGNE(...)), avec des tests par rapport aux contenus des classeurs 1 et 2 uniquement, je suis passé par un MIN (Calculs bis à partir de la plage 1;Calculs bis à partir de la plage 2).
avec plusieurs tests dans les calculs, par rapport aux contenus des classeurs 1 et 2, mais aussi par rapport aux n° déjà cités au -dessus de la cellule en cours (pour ne pas les répéter inutilement).
SI(ESTNA(EQUIV(O$2:O$36;A$1:A7;0))*(M$2:M$36="N");O$2:O$36) concerne uniquement le classeur 1.
1ère condition :
EQUIV(O$2:O$36;A$1:A7;0) renvoie la position des n° du classeur 1 dans la plage au dessus de la cellule en cour, c.à-d. des n° déjà cités, et #NA pour les n° du classeur 1 pas encore cités.
ESTNA(EQUIV(O$2:O$36;A$1:A7;0)) renvoie FAUX pour les n° déjà cités et VRAI pour ceux non cités.
2ème condition :
M$2:M$36="N" renvoie VRAI pour les cellules de la colonne M contenant un N, FAUX pour les autres.
Les produits VRAI*VRAI renvoient un 1, les autres contenant au moins un FAUX renvoient un 0.
SI(ESTNA(EQUIV(O$2:O$36;A$1:A7;0))*(M$2:M$36="N");O$2:O$36) renvoie donc la liste des n° non cités dans le classeur 1 ayant un N sur leur ligne respective, et des FAUX pour les autres. Ici, cela fait plein de FAUX, puis 765;766 puis à nouveau plein de FAUX, du style {FAUX;FAUX;....;765;766;FAUX;FAUX....;FAUX}.
SI((G$2:G$22<>"")*ESTNA(EQUIV(G$2:G$22;A$1:A7;0));G$2:G$22) ne concerne que le classeur 2 et fonctionne comme la partie précédente, mais avec d'autres conditions
1ère condition :
ESTNA(EQUIV(G$2:G$22;A$1:A7;0)) renvoie FAUX pour les n° déjà cités dans le classeur 2 et VRAI pour ceux non cités au dessus (dans la plage A1:A7).
2ème condition :
(G$2:G$22<>"") renvoie VRAI pour les lignes contenant une valeur autre que "" (des dates ici).
Les produits (G$2:G$22<>"")*ESTNA(EQUIV(G$2:G$22;A$1:A7;0)) renvoie donc une série de 1 et de 0.
SI((G$2:G$22<>"")*ESTNA(EQUIV(G$2:G$22;A$1:A7;0));G$2:G$22) renvoie les n° du classeur 2 non cités au dessus, et FAUX dans tout les autres cas. Ici, cela donne {FAUX;FAUX....;765;FAUX;FAUX;FAUX;766;FAUX;FAUX;767;FAUX;FAUX;770;FAUX}
Donc on a quelque chose du genre MIN({FAUX;....;765;766;FAUX;FAUX....;FAUX};{FAUX;FAUX....;765;FAUX;FAUX;FAUX;766;FAUX;FAUX;767;FAUX;FAUX;770;FAUX}), ce qui donne 765.
Avec cette partie de la formule, on a la liste des n° des deux classeurs, classés par ordre croissant, mais cités qu'une seule fois.
Maintenant, regardons ce qui se passe dans A9. 765 a été cité une fois dans la plage A1:A8, mais il faut le citer 4 fois : on le trouve une fois dans le classeur 1 avec une série N, et 3 fois dans le classeur 2.
Il faut donc trouver un test imposant sa répétition jusqu'à avoir 4 fois le n° 765 dans la colonne A. Reagardons le test suivant (dénommée partie 2)
NB.SI(A$1:A8;A8) compte le nombre de A8 dans la plage A1:A8 (ce qui donne ici 1 pour la valeur 765).
SOMME((O$2:O$36=A8)*(M$2:M$36="N")) donne le nombre de A8 (de 765) dans le classeur 1 ayant une série N, ce qui donne 1.
Et là, ça se complique... Il faut compter le nombre de lignes attribuées au 765 dans le classeur 2... On va le faire en faisant la soustraction N° de la ligne vide dans la colonne I après le 765 (donc sans date) - n° de la ligne contenant le 765 (ce qui fait ici 14 - 11 = 3, ce qui est bien le nombre de lignes attribuées au n° de chantier 765 dans le classeur 2)
SI(I$2:I$22="";LIGNE($2:$22) ; ) permet de lister les n° de ligne ayant une cellule vide dans la plage I2:I22. ici, cela renvoie quelque chose du genre {FAUX;3;FAUX;5;FAUX;7;FAUX;FAUX;10;FAUX;FAUX;FAUX;14;FAUX...}.
Seul nous intéresse le premier n° après la ligne contenant 765 dans le classeur 2
SI(SI(I$2:I$22="";LIGNE($2:$22) ; )>EQUIV(A8;G$1:G$22;0);LIGNE($2:$22)) nous donne tous les n° de ligne supérieur à la ligne contenant le 765 dans le classeur 2.
MIN(SI(SI(I$2:I$22="";LIGNE($2:$22) ; )>EQUIV(A8;G$1:G$22;0);LIGNE($2:$22))) nous donne le premier de ces n°.
MIN(SI(SI(I$2:I$22="";LIGNE($2:$22); )>EQUIV(A8;G$1:G$22;0);LIGNE($2:$22)))-EQUIV(A8;G$1:G$22;0) nous donne la différence demandée, ici 14 - 11 = 3
SOMME((O$2:O$36=A8)*(M$2:M$36="N"))+MIN(SI(SI(I$2:I$22="";LIGNE($2:$22); )>EQUIV(A8;G$1:G$22;0);LIGNE($2:$22)))-EQUIV(A8;G$1:G$22;0) donne le nombre de 765 qu'on doit avoir dans la colonne A, ici 1+3.
NB.SI(A$1:A8;A8)<SOMME((O$2:O$36=A8)*(M$2:M$36="N"))+MIN(SI(SI(I$2:I$22="";LIGNE($2:$22); )>EQUIV(A8;G$1:G$22;0);LIGNE($2:$22)))-EQUIV(A8;G$1:G$22;0) donne ici 1 < 4 ce qui renvoie VRAI
Donc on va utiliser une formule du style SI(partie 1;A8;partie 2), ce qu'on pourrait "traduire" en français par SI le nombre de répétition nécessaire n'est pas atteint, réécrire le n° juste au dessus, sinon, chercher le plus petit n° du classeur 1 ayant une série N, ou du classeur 2, pas encore cités.
Comme cela ne fonctionne pas parfaitement sur la ligne 2, on rajoute dans A2 un test A1<>"numéro".
Et comme cela donne une série de 0 une fois tout les n° cités, en dessous de la liste, il faut compter le nombre de n° à citer, ce que fait (partie 3) NB.SI(M$2:M$36;"N")+NBVAL(I$2:I$22)
On a donc une formule du style, dans A2
SI(LIGNE(1:1)>partie 3;"";SI((A1<"numero")*partie2;A1;partie1)).
Facile non .
J'ai étendue certaines plages jusque la ligne 22 car il faut absolument prendre une ligne vide après la dernière valeur de la colonne I (Autrement, on ne pourrait pas faire la soustraction citée précédemment dans le cas du dernier n° de la colonne G, 770 ici). Parmi celles-ci, certaines peuvent s'arrêter à la ligne 21, mais, histoire d'uniformiser tout cela, dans le but d'utiliser un seul et unique nom plutôt que deux dans le gestionnaire de noms...
Pour ce qui est des colonnes B, C, D et E, on utilise des parties de la formule décrit ci-dessus en utilisant la fonction INDEX.
Dans la colonne B, c'est quelque chose du style SIERREUR(formule par rapport au classeur 1; formule par rapport au classeur 2).
Pour les colonnes C et D, cela ne concerne que le classeur 2, mais il y a un test SI(NB.SI(A$2:A2;A2)<=SOMME((O$2:O$36=A2)*(M$2:M$36="N")); par rapport au classeur 1.
Pour la colonne E, c'est le contraire, d'ou le test SI(D2<>"";
@ plus
P.S : En rédigeant ce texte, je viens de voir qu'il y avait un SIERREUR inutile dans cette formule. Je modifie la pièce jointe en conséquence.
je comprend mieux maintenant merci. j'essayer de l'applique ce la sur le classeur avec les données séparé et j'ai un peu de mal. mais les solutions que vous proposer sont superbe.
Pour l'appliquer simplement sur des fichiers séparés, ce n'est pas très difficile : il suffit de couper la plage M1 : M36 et de la coller dans le classeur 1. Idem avec G1:J21 à coller dans le classeur 2. Excel met automatiquement le nom du fichier et de la feuille destinataires.
Pour autant, il faut encore définir des plages dynamiques pour que cela soit vraiment pratique : Quel que soit le nombre de lignes de tes classeurs 1 et 2, tu n'auras pas besoin d'aller modifier des nombres dans les formules. Il faudra juste que tu mettes assez de lignes avec des formules dans le fichier nouveau classeur.
Je avais définis des plages dynamiques dans un des fichiers du post #8, mais comme les formules ont changé depuis, il faut vérifier ou refaire...
Je te fais ça, peut être ce soir, peut être ce W.E.
Cf. en pièce jointes avec les données sur les 3 fichiers. On doit forcément ouvrir les fichiers Classeur1, classeur2, et l'un des deux autres.
Parmi les deux derniers fichiers, celui finissant en ...plusieurs fichiers utilise les plages utiles directement. Il te faudra donc adapter ces plages à tes fichiers réels en modifiant le n° de la dernière ligne utilisée dans classeur 1 et dans classeur 2 (sans oublier de prendre une ligne vide tout en bas dans ce dernier cas).
L'autre, en ...plusieurs fichiersavecnoms, utilise des plages dynamiques nommées, définies dans le gestionnaire de noms. Dans ce cas, si tu utilises les mêmes colonnes, tu as juste à copier tes données sur les fichiers classeur 1 et classeur 2 et à tirer les formules dans ce 3ème fichier assez longtemps vers le bas.
Comme il y a beaucoup de formules matricielles, et des fonctions DECALER dans le gestionnaire, il est possible que ton ordinateur prenne du temps pour afficher tous les résultats. Si cela rame vraiment de trop, cela veut dire qu'il aurait mieux fallu faire cela avec une macro, ce que je ne sais pas faire facilement.
@ plus
P.S : Il y a peut être moyen de faire un peu plus simple, en faisant commencer toutes les plages en ligne 1, et en changeant un peu les formules.
Pièces jointes
Classeur2.xlsx
14.1 KB
· Affichages: 35
Classeur1.xlsx
9.9 KB
· Affichages: 33
nouveau classeurNNNNNsurplusieurs fichiers.xlsx
25.1 KB
· Affichages: 30
nouveau classeurNNNNNsurplusieurs fichiersavecnoms.xlsx
Si cela te dit, une autre possibilité pour le fichier du post #17 (c.à-d. toutes les données sur un seul fichier) avec moins de formules matricielles.
Avant, la formule de la colonne E dépendait du contenu de la colonne D avec SI(D2<>"";"". Maintenant, c'est le contraire, ce sont les formules, non matricielles, des colonnes C et D qui dépendent du contenu de la colonne E avec SI(E2<>"";"";... Attention, j'y ai remplacé SOMME par SOMMEPROD pour que cela fonctionne correctement.
Idem, en faisant commencer les plages utilisés des classeurs 1 et 2 à la ligne 1, aussi bien dans le fichier contenant toutes les données, que dans celui avec des plages dynamiques et des noms travaillant à partir des classeurs 1 et 2 (Cf. dans le gestionnaire de noms : Il ni a plus que 8 noms. Les 2 principaux sont numéro1 et numéro2. Les autres sont définis par rapport à ces deux là. Le chiffre à la fin du nom indique le n° du fichier source, classeur 1 ou classeur 2).
@ plus
Pièces jointes
nouveau classeurNNNNNter.xlsx
22.9 KB
· Affichages: 27
nouveau classeurNNNNNsurplusieurs fichiersavecnomster.xlsx
merci pour tout ce que vous avez fait, j'ai réussi a l'applique une gros partie sur mes fichier complet, comme c'est un gros fichier ça prend un peu de temps pour charger mais ça c'est pas grave. le problème c'est pour le code suivant :
si j'ai bien compris elle permet de calculer le nombre de répétition du numéros avant d'arriver au suivante, mais le problème c'est que j'arrive pas a l'utiliser sur un fichier plus grand. car mon fichier complet à un volumes beaucoup plus tôt important, que je ne peux pas vous fournir désoler
exemple dans mon classeur2 je rajoute des ligne en plus donc au lieu de 21 ligne elle devient 28 ligne de données, j'ai donc modifier le code en suivant:
Il faut mieux travailler avec le fichier avec les noms (post #23). Cela fonctionnera automatiquement. Fais un essai, tu verras.
Si tu veux absolument faire avec des plages en dur (c.à-d. en écrivant les plages), il faudra que tu le fasses à chaque fois. Je te met le fichier correspondant qui fonctionne avec 29 lignes dans le classeur 2. Si tu a 100 lignes dans ce classeur 2, il te faudra remplacer tous les 29 par des 101. Tu peux aussi tout de suite mettre un nombre plus grand que le nombre max supposé de lignes de ton fichier. Cela prendra peut être un peu plus de temps de calcul, mais tu n'auras pas de modification à faire à chaque fois.
j'ai réussi à replacer les deux classeur par mes fichier complet. et je trouver pourquoi même si je replacer tout le 29 par 101 il me fait une erreur #N/A. c'est à cause d'un espace, il y avait un espace après le mot numéros dans le classeur de données est pas d'espace dans le nouveau classeur, qui était difficile à voir.
par contre j'ai encore une autre question si maintenant je veux rajouter un 3eme classeur (similaire à la classeur2) que dois-je faire?
Je n'ai pas le temps maintenant de faire cela. J'essayerai ce soir. Est-ce que le 3ème classeur ressemble au classeur 1 (donc avec un critère du style ="N") ou au classeur 2 (donc avec des lignes vides servant de séparateur entre des groupes) ?
désole pour le réponse un peu tardive. le fichier est comme le classeur2 déjà trier mais elle n'a pas de séparateur entre les groupes, c'est suivi l'un après l'autre.
Avant de passer au cas avec trois fichiers à regrouper, j'ai repéré une erreur, ou plutôt un oubli (les joies de l'informatique !) dans le fichier nouveau classeur... En effet, si tu te souviens bien, par ex dans A3, on compte le nombre de 761 dans le classeur 1 avec
Cela fonctionne bien si A2 existe dans le classeur 2, mais pas si A2 n'est pas dans ce classeur. Remplace dans le classeur 2 le 761 par 1761 par ex. Tu verras plein de #NA dans le fichier nouveau classeur... Normal, les EQUIV(A2;....;0) renvoie des #NA. Dans ce cas, comme il ni y a pas de 761 dans le classeur 2, il faut que la partie en rouge, concernant le classeur 2, renvoie 0. Pour corriger cela, il suffit de rajouter un SIERREUR( devant le MIN, et à la fin, toujours de cette partie, un ;0).
Ce site utilise des cookies pour personnaliser le contenu, adapter votre expérience et vous garder connecté si vous vous enregistrez.
En continuant à utiliser ce site, vous consentez à notre utilisation de cookies.
