Mettre dans tableau 2 entrées, les données d'un tableau 3 colonnes

Question

Mes connaissances en Excel ne me permettent pas de surmonter l'obstacle que je rencontre dans la création d'une feuille Excel qui doit servir à gérer les bénévoles pour l'organisation d'un vide greniers. Evidemment, VBA exclu étant donné mon niveau Excel ! Dans un tableau de données de 3 colonnes, j'ai le nom des bénévoles en col 1 (triée alphab croissant), les taches en col 2, et les tranches horaires en col 3. Ces colonnes sont informées par listes déroulantes (jusque là pas de problèmes!) 1er PROBLEME : je souhaite un tableau où en col 1, j'aurais la liste de noms des bénévoles et en haut, une ligne mentionnant tous les créneaux horaires (ces informations venant du tableau de données) (jusque là, toujours pas de soucis!!), et qu'à l'intersection des lignes et colonnes NOM et TRANCHES HORAIRES, s'affiche la TACHE trouvée dans le tableau de données, sur la ligne où ces 3 informations apparaissent. Une même personne pouvant apparaitre sur plusieurs tranches horaires avec des tâches différentes ou similaires. 2me PROBLEME : Un autre tableau ayant les TACHES en col 1 et toujours les TRANCHES HORAIRES en ligne supérieure (jusque là, toujours pas de soucis!!), aurait dans les cellules d'intersections le NOM des personnes affectées à cette tache dans ce créneau horaire. Souvent, la cellule comportera plusieurs noms, avec un retour à la ligne pour chaque entrée concaténée. Je pense qu'il doit bien y avoir une solution à chacun de ces problèmes, étant donné la quantité de formules du tableur, mais je bloque ! Je joins un fichier Excel pour illustrer ma demande. Le problème y est reposé : Merci aux bonnes âmes qui voudront bien m'aider...

MFerrand · Answer

Une première leçon utile et pratique d'Excel : n'utiliser le format Texte (en format de cellule) qu'avec beaucoup de circonspection et uniquement dans les rares cas où il n'est pas possible de faire autrement. Donc : tu commences par supprimer ce format dans tous tes tableaux (y compris le premier) et remettre les cellules au format Standard. Ce n'est pas parce que tu tapes du texte qu'il faut mettre un format Texte ! Au contraire ! Si tu as besoin un jour de l'utiliser ce sera avec des nombres ! Ne tape aucune formule dans tes tableaux avant d'avoir modifié le format : son utilisation a pour particularité (lorsqu'on le modifie dans un sens ou l'autre) de ne prendre effet que pour une nouvelle saisie. Et comme il empêche une formule de se calculer (elle s'affiche comme texte !), il faut respecter cet ordre pour avoir le moins de manipulations possibles. Pour ton tableau 2, formule en J6 : =INDEX($E$1:$E$15;SOMMEPROD(($D$6:$D$15=$I6)*($F$6:$F$15=J$5)*LIGNE($E$6:$E$15))) La recopier sur le tableau... Pour ce calcul, on a encore une formule simple ! Pour le tableau suivant, soit tu individualises tous les éléments dans des cellules distinctes et je veux bien concocter une formule qui restera de longueur acceptable (à mon goût), mais qui sera matricielle. &#91;Une formule matricielle se valide par une combinaison de touches : Ctrl+Maj+Entrée (au lieu de seulement la touche Entrée)&#93; Si tu tiens à ta présentation, je ne le ferai qu'en VBA, et on peut le faire sous forme de fonction personnalisée qui s'utilisera comme les fonctions natives d'Excel. Dans un tel cas, une même fonction utilisable pour tes deux tableaux, ce qui simplifiera la chose. A toi d'y réfléchir... Je pense aussi que tu pourrais obtenir tes résultats à partir de ton premier tableau, au moyen de TCD, mais ce n'est pas ma spécialité et je laisse le soin à d'autres intervenants de te le proposer. Cordialement...

dhany · Answer

Bonjour Pastoutnet , je confirme : c'est bien ça ! dhany...

MFerrand · Answer

Etant fortuitement retombé sur ton fichier, j'ai eu d'abord l'intention de te concocter les fonctions personnalisées dont j'avais émis l'idée, mais après un examen plus approfondi, il m'est apparu plus efficace dans ton cas d'avoir une procédure VBA qui, à partir de ta saisie de données initiales, produise tes deux tableaux de répartition et les mette en forme de la façon dont tu le prévoyais. Les éléments en jeu étant sensiblement les mêmes que pour produire des fonctions, mais des fonctions impliquaient que tu réalises les tableaux afin d'y placer lesdites fonctions... autant tout faire d'un coup donc ! Etant novice dans Excel et ignorant de VBA, je conçois que tu soies tenté d'écarter l'utilisation de VBA, mais tu peux toutefois constater les possiblités offertes en testant la procédure que j'ai incluse dans ton fichier légèrement remanié. Pour tester, il suffit de cliquer sur le bouton "Répartir..." après avoir autorisé les macros à s'exécuter... Les remaniements que j'ai opérés ont consisté à éliminer partiellement la mise en forme de ton tableau initial de façon que leur mise en place s'apparente plus nettement à une base de données. Et j'ai remplacé la mention 8.00 - 13.00 par 08.00 - 13.00 , tout simplement parce qu'en alignant le nombre de caractères des indications horaires, on peut trier ces textes par ordre alphabétique et il le seront également dans l'ordre chronologiques des plages horaires (sinon 8.00 - 13.00 se placerait après 13.00 - 18.00 , ce qui ne permet plus de définir une mise en ordre à partir des données du tableau quelles que soient la façon dont elles seraient initialement ordonnées. La procédures est prévue pour traiter les données initiales quels que soient le nombre de noms et de tâches, et aussi quels que soient le nombre de créneaux horaires (à partir de 2 toutefois, un seul génèrerait une erreur). Elle prélève ces éléments initiaux, les comptabilise et les trie, de façon à former les tableaux de répartitions voulus en les dimensionnant en lignes et en colonnes... Elle est donc susceptible de s'adapter sans modification aux variations que tu es susceptible d'introduire dans ton organisation, tant qu'elle repose sur les mêmes éléments précités. J'ai exceptionnellement (car personnellement les commentaires me gênent pour travailler sur de code...) placé des commentaires destinés à te permettre de suivre le cheminement de la procédure, ce qu'elle fait successivement. Cela reste cependant insuffisant pour en produire une semblable ou la modifier, sans avoir acquis quelques rudiments de VBA... Quelques précisions pour ne pas être totalement perdu : la procédure utilise un outil Dictionnaire, lequel est un ensemble d'élément associant une clé à un item (ou valeur), la clé étant par définition unique dans un dico. On en constitue plusieurs au fil de la procédure, qu'on supprime après utilisation pour passer à la constitution du suivant. Un dico des créneaux horaires (avec item vide), destinés à les compter et classer, on en conserve une liste pour la suite (en VBA c'est un tableau à une dimension). On fera de même avec un dico des noms, puis un dico des tâches, pour conserver des listes triées de noms et de tâches, mais là en même temps on associe un item qui sera constitué d'un tableau correspondant au contenu des colonnes de chacun des tableaux de répartitions à produire. Ne te frappe pas si tout ne paraît pas très clair, mais il m'est difficile de faire ici un cours sur les tableaux VBA... la compréhension se développera avec le temps si tu poursuis... Ci-dessous la procédure, que tu pourras trouver dans le fichier en faisant Alt+F11 pour accéder à l'éditeur VBA, dans le Module1. Sub TableauxRépartitionNomsTâches() Dim d As Object, T(), N(), tmp(), hor, tch, nm, Init, itm, k, i%, j% 'Initialisation de d en tant qu'objet Dictionnaire Set d = CreateObject("Scripting.Dictionary") 'Prélèvement des données initiales sous forme de tableau Init = ActiveSheet.Range("A1").CurrentRegion.Value 'Constitution dico des créneaux horaires For i = 2 To UBound(Init) d(Init(i, 3)) = "" Next i 'Tableau des créneaux horaires hor = d.keys 'Tri du tableau des créneaux horaires For i = 0 To UBound(hor) - 1 For j = i + 1 To UBound(hor) If hor(j) &lt; hor(i) Then k = hor(j): hor(j) = hor(i): hor(i) = k End If Next j Next i 'Dimensionnement d'un tableau tmp à la taille des lignes de données ' des tableaux de répartition ReDim tmp(d.Count) 'Elimination du dico créneaux horaires d.RemoveAll 'Remplacement du créneau horaire dans le tableau Init par le rang ' de l'horaire (indice de sa position dans le tableau hor +1, car de base 0) For i = 2 To UBound(Init) For j = 0 To UBound(hor) If Init(i, 3) = hor(j) Then Init(i, 3) = j + 1: Exit For Next j Next i 'Constitution d'un dico Noms ' On y place un tableau vide lors de la création de l'élément ' On remplit ce tableau au fur et à mesure... For i = 2 To UBound(Init) k = Init(i, 1) If d.exists(k) Then itm = d(k) itm(Init(i, 3)) = Init(i, 2): d(k) = itm Else d(k) = tmp: itm = d(k) itm(0) = k: itm(Init(i, 3)) = Init(i, 2): d(k) = itm End If Next i 'Tableau des noms nm = d.keys 'Tri du tableau des noms For i = 0 To UBound(nm) - 1 For j = i + 1 To UBound(nm) If nm(j) &lt; nm(i) Then k = nm(j): nm(j) = nm(i): nm(i) = k End If Next j Next i 'Constitution du tableau de répartition des tâches par nom et créneau horaire ' Dimensionnement du tableau ReDim N(UBound(nm) + 2, UBound(hor) + 1) ' Lignes d'en-tête du tableau N(0, 0) = "Noms": N(0, 1) = "Créneaux horaires" For i = 0 To UBound(hor) N(1, i + 1) = hor(i) Next i ' Remplissage des données For i = 0 To UBound(nm) k = nm(i): itm = d(k) For j = 0 To UBound(itm) If itm(j) &lt;&gt; "" Then N(i + 2, j) = itm(j) Next j Next i 'Elimination du dico des noms d.RemoveAll 'Constitution d'un dico Tâches ' dans les mêmes conditions que le dico noms précédent ' mais le remplissage devra tenir compte que plusieurs noms peuvent être ' affectés à une tâche dans le même créneau horaire For i = 2 To UBound(Init) k = Init(i, 2) If d.exists(k) Then itm = d(k): j = Init(i, 3) If itm(j) &lt;&gt; "" Then itm(j) = itm(j) &amp; Chr(10) &amp; Init(i, 1) Else itm(j) = Init(i, 1) End If d(k) = itm Else d(k) = tmp: itm = d(k) itm(0) = k: itm(Init(i, 3)) = Init(i, 1): d(k) = itm End If Next i 'Tableau des tâches tch = d.keys 'Tri du tableau des tâches For i = 0 To UBound(tch) - 1 For j = i + 1 To UBound(tch) If tch(j) &lt; tch(i) Then k = tch(j): tch(j) = tch(i): tch(i) = k End If Next j Next i 'Constitution du tableau de répartition des noms par tâche et créneau horaire ' Dimensionnement du tableau ReDim T(UBound(tch) + 2, UBound(hor) + 1) ' Lignes d'en-tête du tableau T(0, 0) = "Tâches": T(0, 1) = "Créneaux horaires" For i = 0 To UBound(hor) T(1, i + 1) = hor(i) Next i ' Remplissage des données For i = 0 To UBound(tch) k = tch(i): itm = d(k) For j = 0 To UBound(itm) If itm(j) &lt;&gt; "" Then T(i + 2, j) = itm(j) Next j Next i 'Création d'une nouvelle feuille pour y affecter les tableaux de répartition With Worksheets.Add(after:=ActiveSheet) 'Désactivation de la mise à jour de l'affichage (qui ralentirait...) Application.ScreenUpdating = False 'Affectation et mise en forme 1er tableau With .Range("B3").Resize(UBound(N, 1) + 1, UBound(N, 2) + 1) .Value = N .Rows("1:2").HorizontalAlignment = xlCenter .Cells(1, 1).Resize(2).Merge .Cells(1, 2).Resize(, .Columns.Count - 1).Merge For j = 2 To .Columns.Count .Columns(j).HorizontalAlignment = xlCenter Next j .VerticalAlignment = xlCenter .Borders.Weight = xlThin End With 'Affectation et mise en forme 2e tableau i = UBound(nm) + 8 With .Range("B" &amp; i).Resize(UBound(T, 1) + 1, UBound(T, 2) + 1) .Value = T .Rows("1:2").HorizontalAlignment = xlCenter .Cells(1, 1).Resize(2).Merge .Cells(1, 2).Resize(, .Columns.Count - 1).Merge For j = 2 To .Columns.Count .Columns(j).HorizontalAlignment = xlCenter Next j .WrapText = True .VerticalAlignment = xlCenter .Borders.Weight = xlThin End With End With End Sub Cordialement...

MFerrand · Answer

La remarque que tu cites de mon dernier post n'a pas d'application dans un traitement par formules. Cela concernait le traitement macro : j'ai indiqué pour les plages horaires, 2 plages mini, car s'il n'y en a qu'une je risque de ne pas avoir un tableau des plages horaires avec un seul élément, et l'appelant dans un tableau cel déclencherait alors une erreur (erreur 9 au cas particulier), mais le cas survenant il serait possible de le prendre en charge avec quelques tests, mais j'ai laissé de côté le cas ne me semblant pas devoir survenir...

Je ne vois pas où tu as une difficulté avec la formule que j'avais fournie au départ... ?

Une chose cependant : les en-têtes de tes tableaux 2 et 3, soit les plages horaires (en-têtes de colonnes) et les noms (ou tâches) (en-têtes de lignes) ne sont pas à mettre au moyen de formules ! Tu les mets dans l'ordre que tu veux, ils servent de critères pour les formules du tableau. Et de même tu peux avoir les données de la bases dans n'importe quel ordre (en respectant les colonnes) pour ce qui est des lignes, c'est à la formule de faire le travail de recherche.

La formule fournie était :

=INDEX($E$1:$E$15;SOMMEPROD(($D$6:$D$15=$I6)*($F$6:$F$15=J$5)*LIGNE($E$6:$E$15)))

Elle est basée sur la fonction INDEX utilisée selon la syntaxe :

=INDEX(VecteurColonne;indexLigne)

Pour produire l'indexLigne, pour la formule ci-dessus qui se trouve en J6, on va chercher la ligne qui contient à la fois le nom en I6 (en-tête de ligne de ton tableau2) et l'horaire en J5 (en-tête de colonne de ton tableau2).

Pour cela on utilise des matrices :

(ColNomsTableau1=I6) qui renvoie une matrice de VRAI (si valeur Nom = I6) ou FAUX (si différent)

et : (ColHorairesTableau1=J5) qui pareil, renvoie une matrice de VRAI (si Horaire =J5) ou FAUX (sinon)

en multipliant ces 2 matrices, les VRAI deviennent des 1 et les FAUX des 0 : le résultat est une nouvelle matrice composé de 0 et 1, seul le produit d'éléments de mêmes rang des 2 matrices origine à VRAI aura la valeur 1 (et il ne peut par construction n'y en avoir qu'un..

Pour savoir quel est cet élément, on multiplie par une 3e matrice composée des numéros de lignes des valeurs du Tableau1 (soit au cas particulier les nombres 6 à 15) et comme on multiplie par une matrice ne comprenant que des 0 et un seul 1, le résultat de la somme des valeurs de la matrice résultante sera le numéro de ligne où trouver notre valeur.

C'est la fonction SOMMEPROD qui permet de faire cette somme... SOMMEPROD est une fonction qui traite de matrices, pouvant faire la somme du produit de plusieurs matrices, avec la particularité d'être directement matricielle, sans avoir à être validée comme formule matricielle par Ctrl+Maj+Entrée.

Ayant notre indexLigne, on l'applique (dans la fonction INDEX) à la colonne portant les résultats cherchés, en faisant partir la colonne de la ligne 1 pour ne pas avoir à rectifier l'index (ce qui n'a aucune incidence puisque le résultat du calcul de ce index est forcément inclus dans la colonne de valeurs et ne peut se trouver en dehors).

Voilà pour l'explication de la formule.

Je souhaiterais savoir par ailleurs si tu as testé la macro (il suffit d'appuyer sur le bouton) et vu le résultat qu'elle produit.

Cordialement.

MFerrand · Answer

Ah ! Je vois ! C'est un comportement normal d'INDEX qui renvoie une matrice en cas d'index 0, et la formule ne pouvant renvoyer qu'une valeur, elle renvoie la valeur située sur la même ligne que la formule !

On va ramener la plage de recherche à sa dimension réelle, ce qui éliminera la valeur d'index 0 au profit d'une valeur négative, qui elle fera renvoyer une erreur que l'on récupère :

=SIERREUR(INDEX($E$6:$E$15;SOMMEPROD(($D$6:$D$15=$I6)*($F$6:$F$15=J$5)*LIGNE($E$6:$E$15))-5);"")

Moyen le plus simple car on ne met pas de condition pour éliminer un renvoi de 0 par SOMMEPROD : en cas de 0, l'index sera -5 (=erreur...)

Cordialement.

MFerrand · Answer

Pour ma part, je ne vois pas d'obstacle à ce que tu poursuives sur le même sujet, c'est le même objet qui est en question, et une même discussion qui évolue... La seule considération est de voir s'il conviendrait à un moment donné de fermer cette discussion pour en ouvrir une nouvelle en redéfinissant les questions, de façon à inciter de nouveaux intervenants à y participer... Je dois m'interrompre car on m'appelle pour une tâche urgente ! Je reprends un peu plus tard. @+...

MFerrand · Answer

Je reprends donc !

Tu pourras déjà dire à la personne commanditaire que la présentation sous forme de base de données est celle qui rend possible l'exploitation ultérieure de ces données !

Au cas particulier, cela consiste en une combinaison Nom de personne-Tâche-Créneau horaire par ligne. Moyennant quoi on dispose d'une colonne Noms, d'une colonne Tâches et d'une colonne Créneau horaire, que l'on peut aisément cibler dans des formules. On peut filtrer la base par Nom ou par Tâche ou par Horaire, pour vérifier que les attributions sont exemptes d'erreurs, on peut lui appliquer divers traitements au moyen des fonctionnalités d'Excel et on peut aussi plus facilement la traiter par VBA.

En regard, si on doit partir du tableau qui constituait ton tableau 3 antérieurement, la situation ne se présente plus de tout de la même façon...

Pour ma part, je ne me lance plus dans une telle configuration à opérer par formules, mais exclusivement en VBA pour reconstituer les données de façon à pouvoir les traiter...

La solution la plus sage serait donc qu'il révise sa façon d'opérer !

Cordialement.

MFerrand · Answer

L'abnégation n'a jamais fait partie de mes qualités... Cependant mon conseil (implicite) de revenir au point de départ des données saisies telles que tu les présentais dans ton post initial était tout à fait sérieux. La saisie de données sous la forme base de données est la méthode qui les rend plus aisément exploitable, elle est rationnelle. Partir d'un tableau tel que tu entends maintenant le présenter est irrationnel, c'est une mauvaise méthode de saisie (qui ne permet ni ajout ni modification sans changement de la structure du tableau) qui doit tout simplement être abandonnée. Bien sûr on peut parvenir à tout traiter et VBA permet généralement de récupérer des données présentées atypiquement pour les remettre en tableaux virtuels sous une forme qui les rend adéquates à un traitement... On est plus limité avec des formules car même les matricielles complexes ont des limites et on ne peut pas toujours passer d'une présentation à une autre avec une seule formule recopiable... Evidemment, tu peux toujours traiter cellule par cellule, avec une formule individuelle dans chaque, un simple = pointant vers le résultat, puis que tu le connais ! Cela te permet qu'une modification soit répercutée, mais je ne trouve pas que l'on ait vraiment intérêt à utiliser Excel de cette façon. Pour bien utiliser un outil il convient de voir d'abord comment l'utiliser efficacement, et avant de se lancer dans une réalisation réfléchir à ce qu'on veut exactement obtenir au final... Cordialement...