Remplir des champs éloignés automatiquement via des formules glissées
Bonjour,
D'habitude plutôt à l'aise avec excel (pour l'utilisation quotidienne que j'en ai à faire), je sèche pour essayer d'automatiser mon tableur.
Je dois aller chercher les informations d'une liste déroulante sur ma feuille 1, pour les appliquer sur ma feuille 2 de façon éparpillée. Je vous ai mis le début de mon travail pour vous aider à comprendre. Pour la feuille 2, la case D2 vaut "='Liste Casiers'!A6", puis la case H2 vaut "='Liste Casiers'!A7". Il va de même pour les cases D3, D4, D8, D12. J'ai essayé de me servir de la fonction décaler, mais je ne la maitrise pas et n'ai donc pas réussi à continuer à remplir mes "coupons" selon les infos qu'il me faut (à chaque c/c, excel décale du nombre de ligne ou de colonnes alors que j'aimerai c/c mes 3 premiers coupons, pour les appliquer à l'ensemble de ma feuille).
Merci pour votre aide, n'hésitez pas si ce n'est pas clair, j'essayerai d'eclaircir au mieux. Bonne journée :)
EDIT : Difficile de fournir le fichier source étant donné qu'il contient plus de 750 lignes de noms et prénoms. J'ai simplement anonymisé le début de mon tableau pour faire ces screens.
Bonjour SteMa14 et
Pour commencer, je vous invite à lire la charte du forum [A LIRE AVANT DE POSTER]
qui vous aidera dans vos demandes et réponses sur ce forum et notamment
- Joignez (si possible) un fichier pour augmenter vos chances d'obtenir de l'aide en cliquant sur le bouton Fichier de l'éditeur. Si votre fichier est trop lourd ou contient des données personnelles, créez une version allégée de votre fichier avec juste assez d'informations pour permettre de comprendre votre problème. Dans tous les cas, ne postez JAMAIS de fichiers avec des informations personnelles ou confidentielles (cet utilitaire)
Sinon, comme vous êtes sur M365, vous devez utiliser la fonction RECHERCHEX()
A+
Bonjour Bruno et merci pour ta réponse.
Je suis bien passé par la charte avant de poster, j'ai juste pas ouvert la partie pour anonymiser les données, pensant que ça allait être trop fastidieux de refaire tout. Voici donc mon fichier anonymisé :)
Je n'ai pas compris comment la fonction Recherchex pourrait m'aider à répondre à ma demande par contre ?
Bonne journée
Bonjour SteMa14,
Salut Bruno
Je n'ai pas compris comment la fonction Recherchex pourrait m'aider à répondre à ma demande par contre ?
Sans aucun doute parce que ce qui te semble clair, à toi qui connais ton fichier ... ne l'est pas du tout pour nous
Si tu encodais les n° des casiers dans la feuille "coupons", la RECHERCHEX répondrait à la demande. Ici, tu utilises des formules pour compléter tous les champs des coupons.
Par ailleurs, dans ton exemple, tous les coupons ne sont pas édités : tu passes du 9 au 28, puis du 36 au 56 ... Comment savoir lesquels sont édités (ou pas) !? Si tu veux écrire une formule, il faut bien qu'une règle puisse être définie, mais là elle m'échappe
Hmm, je ne sais pas encoder, c'est bien là mes limites de l'utilisation d'excel :/ Les numéros de casiers sont la seule information qui restera fixe quoi qu'il arrive, ce qui m'intéresse surtout c'est le fait de pouvoir éditer ma liste en Page 1 (les nom, les prénoms, le code du casier et la classe) et que tous mes coupons s'actualisent avec les bonnes informations.
Actuellement, j'ai fait les 9 premiers coupons à la main. à chaque fois j'édite les cases pour chaque ligne (Nom, Prénom, Classe, numéro de casier, code de casier) en changeant simplement le numéro de la case dedans.
Si mes numéros passent de 9 à 28, c'est parce que j'ai c/c mes 9 premiers coupons, puis collés à la suite (cela fait donc un décalage dans mes formules, le nom de la 1ère étiquette passant de "='Liste Casiers'!A6" à "='Liste Casiers'!A33").
Ce que je cherche à faire, c'est qu'en faisant ce c/c ma formule passe de "='Liste Casiers'!A6" à "='Liste Casiers'!A15", donc qu'excel prenne un décalage de 9 lignes, et non pas de 27 comme actuellement.
En espérant que ce soit un peu plus clair pour que vous puissiez m'aider ;) J'ai toujours la possibilité d'éditer chaque coupon à la main mais ça va être fastidieux de le faire pour mes 755 lignes :D
Re-bonjour,
J'ai parlé d'encoder ... pas de coder ! Encoder, c'est juste saisir une valeur au clavier et ça je parie que tu sais faire
Tu auras toujours tous tes n° de casiers, dans la feuille coupons ? Leur nombre et leur ordre ne varieront jamais ?
Ah, alors oui je pourrais remplir ces champs manuellement, mais j'ai préféré l'automatiser au cas où (un jour) les numéros de casiers viendraient à changer. Mais si c'est plus pratique de les laisser fixe, on peut en effet se passer de la formule dans la case du numéro de casier.
Ils resteront toujours dans l'ordre oui. Là je n'ai fait des coupons que sur une feuille (j'ai travaillé ma mise en page pour pouvoir optimiser l'espace lors de l'impression) mais l'idée c'est bien de dérouler la liste complète de tous les casiers, du 1 jusqu'au 2075 (numéros complets entre 1 et 630, puis on passe à 1001 jusqu'à 1045, puis de 2001 à 2075). Sur ma feuille "coupon" il doit donc apparaître les 755 coupons différents ;)
Re,
En t'attendant, j'ai testé un premier truc ... mais je découvre maintenant qu'il y a des "trous" dans la numérotation
Bref, je te livre ce que j'ai imaginé et tu verras bien si tu gagnes du temps, mais normalement ça devrait déjà être mieux !)
Dans la pièce jointe, j'ai fait les lignes des 3 premiers coupons. J'ai supprimé tes cellules fusionnées et pour chaque paire de cellules défusionnées, j'ai appliqué un alignement centré sur plusieurs colonnes (voir la liste déroulante dans Format de cellule > Alignement > Horizontal)
Les n° des 3 premiers casiers sont encodés, le reste est rempli avec la RECHERCHEX
Il te reste à :
- sélectionner B2:L9 et copier cette plage
- la coller en B11
- en C15, écrire =C6+3
- copier C15 en G15 et K15
- sélectionner cette fois la plage B11:L19 et, à l'aide de la poignée de recopie, propager les formules vers le bas aussi loin que nécessaire. N'interromps la recopie qu'à hauteur d'une ligne vide sous la bordure noire entourant un coupon
Si tu dois étendre un jour vers le bas, il faudra bien sélectionner la plage encadrée de ta bordure noire + la ligne vide juste en-dessous
Avec tes dernières infos, il faudra encoder (après le coupon 630) les n° des 1001, 1002 et 1003 ; les suivants devraient se mettre à jour. Puis recommencer avec 2001, 2002 et 2003
Je n'ai pas mieux en stock ... teste et tu verras bien. Si besoin (mais avec des infos complètes et détaillées dès le départ) on peut sans doute imaginer un autre système pour les n° de casiers. Le reste devrait être OK ... à vérifier soigneusement, tout de même !
Bonjour SteMa14,
Une autre proposition. Des colonnes (pouvant être masquées) sont ajoutées à droite des coupons pour identifier les n° de lignes du tableau "Liste Casiers". La mise à jour des coupons se fait en utilisant la fonction "INDIRECT". Il suffit de recopier les lignes entières 10 à 18 pour mettre à jour les autres coupons. Les lignes 2 à 9 ne doivent pas être recopiées car la cellule O2 ne contient pas de formule (1er n° de ligne du tableau).
Cdlt,
Cylfo
En réponse à U. Milité :
Eh bien, c'est exactement ce que je souhaitais obtenir, merci beaucoup !!
Juste 2-3 petites choses sur lesquelles vous allez peut-être pouvoir améliorer le rendu :
Lorsque j'étire ma plage de cellule, cela ne garde pas la hauteur des lignes, alors que j'aimerai vraiment appliquer le même format tout au long des coupons. Ça facilite aussi l'impression. C'est pourquoi pour coller, je sélectionnais directement les lignes 2 à 10 sur toute la longueur. A moins qu'on ne puisse dire directement au fichier excel qu'on limite à 7 coupons en hauteur lors de l'impression ?
Pendant que j'écrivais, une autre réponse est apparue, alors pour répondre à Cylfo :
Ça semble encore mieux.... ça répond à tous mes critères, je copie mes lignes entières donc je garde le format de ligne que j'ai adapté sur le premier coupon, ça s'adapte si je change le numéro du casier, ça fait fi des numéros de casiers absents de ma liste.... Je pense garder ta solution du coup, merci beaucoup !! Juste, peux-tu m'expliquer la fonction "indirect" et la façon dont elle fonctionne pour aller récupérer les infos recherchées ? Histoire d'apprendre et de pouvoir éventuellement refaire une prochaine fois :)
Il me reste juste une chose à régler, comment faire que j'ai toujours le même nombre de coupons sur mes pages lors de mon impression ? Je pensais avoir bien reglé pour la première page mais un décalage se crée sur les suivantes, ce qui coupe les coupons en deux...
Encore merci à ceux qui ont pris du temps pour m'aider et automatiser la chose, ça va nous faire gagner pas mal de temps sur le futur !
Pour la fonction INDIRECT : Elle fabrique une référence à partir d'une chaîne texte et retourne la valeur correspondant à cette référence. Donc dan le cas présent et pour le nom, la chaîne de texte est "'Liste Casiers'!A" (attention Liste Casiers est entourée par des apostrophes car il y a un espace dans le nom de l'onglet) à laquelle est concaténée le n° de la ligne O2, O11, O20, etc. donc =INDIRECT("'Liste Casiers'!A"&O2) retourne la valeur située en cellule O2 de la feuille [Liste Casiers].
Pour la plage à imprimer : j'ai défini la zone d'impression A2:M2251 et la césure des pages se fait correctement ... voir fichier joint.
Merci pour ton explication, très claire. Ravi d'avoir pu apprendre quelque chose qui me resservira certainement.
Pour la zone d'impression, c'est marrant car avec les mêmes paramètres et la même zone définie sur mon fichier, les césures ne sont pas au même endroit, j'ai donc remis ma liste sur le tien pour avoir le découpage au bon endroit.
Merci à ceux qui ont pris du temps pour m'aider à résoudre ce problème, vous êtes top
J'ai vérifié : on pouvait aussi recopier les lignes entières, dans ma proposition. Je rappelle que j'y avais travaillé avant de voir les trous dans la numérotation et lorsque je proposais de réfléchir à un autre système pour le n° des casiers, je pensais à la fonction INDIRECT effectivement.
Si la proposition de Cylfo convient, c'est tant mieux, je ne peux que m'en réjouir !
Oui en effet j'ai vu après qu'on pouvait aussi c/c les lignes entières pour garder le formatage. Mais Cylfo est arrivé entre deux et sa solution convient parfaitement en effet
J'abuse de vos connaissances, serait-il possible lorsque les lignes (Nom, Prénom, classe, code de casier) sont vides, que le coupon n'affiche rien plutôt qu'un 0 ? Ça permettrait un remplissage manuel une fois la liste imprimée. J'ai bien envie de faire intervenir une fonction SI mais je sais pas vraiment où la mettre pour ne pas interférer avec la fonction INDIRECT.
Je viens de m'y essayer, ça à l'air de fonctionner avec =@SI(@INDIRECT("'Liste Casiers'!A"&O2);INDIRECT("'Liste Casiers'!A"&O2);"") (pour le Nom du coupon n°1). Mais uniquement lorsque la case est vide, si elle est remplie cela m'affiche "#VALEUR!" donc ça ne convient pas complètement.
Re,
Le plus simple est sans doute, dans les options d'Excel > Options avancées > Options d'affichage de la feuille de calcul > décocher la case devant "afficher un zéro dans les cellules qui ont une valeur nulle"
Attention, tu ne verras plus aucun zéro (seul dans une cellule) dans toute la feuille ... et il faut espérer que la feuille "liste casiers" ne contient pas des zéro que la formule devrait retourner (ce qui n'est pas le cas actuellement).
Teste et dis-nous s'il faut trouver autre chose
Incroyable, je ne connais rien en excel finalement
Allez ce coup-ci je vous laisse en paix, un énorme merci à tous les 2 particulièrement !!