Formule de transformation de tableau lignes en colonnes

Bonjour,

Je voudrais créer un grand tableau synthétique (à partir de celui généré par un Google Forms) mais je ne trouve pas la formule pour transformer le 1er tableau en 2ème comme dans l'exemple très simplifié ci-dessous :

Sachant que chaque asso n'a pas le même nombre d'adhérents (par exemple ici, l'asso B n'en a que 2, donc il faudra directement créer la ligne suivante pour l'asso C plutôt que de laisser un champ vide dans mon 2ème tableau).

(L'idéal serait aussi qu'un tri automatique se fasse au fur et à mesure de la remontée des résultats du questionnaire mais bon, ça c'est la cerise sur le gâteau, pas obligatoire...)

Si vous avez une idée, je vous serais éternellement reconnaissant !!! :-)

Merci !

J'ai une solution intermédiaire:

Créer une colonne avec ce type de query qui liste tous les adhérents

={query(B2:B4;"select B where B is not null");query(C2:C4;"select C where C is not null");query(D2:D4;"select D where D is not null")}

Pour y associer le nom d'asso, utiliser un index equiv

Bonjour,

C'était un bon casse-tête

Donc voila une formule magique qui fait tout (crée les combinaisons, ignore les lignes vides, ignore les colonnes vides et trie les résultats) :

=MAP(SORT(TOCOL(SPLIT(REDUCE(0;TOCOL(BYROW(FILTER(A3:F7;B3:B7<>"");LAMBDA(lig;LET(asso;INDEX(lig;1;1);adh;FILTER(lig;lig<>"";lig<>asso);MAP(adh;LAMBDA(ad;asso&"|"&ad))))));LAMBDA(tab;val;SI(val="";tab;SI(tab<>0;tab&"¦"&val;val))));"¦")));LAMBDA(lig;SPLIT(lig;"|")))

Pour l'utiliser, remplace simplement A3:F7 par la plage et B3:B7 par la colonne 2 de la plage.

Cordialement,

Bonjour,

Une solution moins élaborée avec un QUERY , mais qui fait le job:

=query(ArrayFormula(split(tocol(A3:A&"|"&B3:D;1;0);"|"));"select * where Col2 is not null")

Cordialement,

Fil.

Bonjour et tout d’abord un grand merci à vous trois pour vos efforts pour m’aider !

Je suis désolé pour mon délais de réponse, mon travail a dû s’interrompre et je le reprends seulement maintenant.

J’ai essayé vos trois propositions.

Pour commencer, je dois préciser que je suis une burne en formules de calcul sur Google Sheets !

Alors j’ai commencé par essayer la solution la plus courte, celle de Filoche :

Un message d’erreur (#REF!) est d’abord apparu me demandant de changer les paramètres, ce que j’ai fait (dans « fichiers ») en activant le calcul itératif pour résoudre les erreurs venant de formules contenant des références circulaires, comme c’était le cas ici.

Une fois le paramètre activé, le tableau a bien été transformé comme voulu (youpi !)… seul problème, une fois qu’une ligne a été créée pour chaque adhérent de chaque asso (dans mon exemple), après la dernière asso il poursuit en repartant de la 1ère asso, créant ainsi des lignes à l’infini.

Ta formule m’intéresse car c’est la plus simple, peut-être as-tu une solution pour régler ce détail et adapter le nombre de lignes créées au nombre d’associations et d’adhérents, pas plus ?

J’ai ensuite essayé la proposition d’EVOLM mais elle ne correspond pas à ma demande, car elle ne liste pas les adhérents d’une asso à la suite (A1, A2, A3, B1, B2, C1, C2, C3) mais ainsi :

A1

B1

C1

A2

B2

C2

A3

C3

J’ai fini par la proposition de Sébastien et là… (suspens !) Bingo ! Ça marche, j’ai bien obtenu :

Asso A A1

Asso A A2

Asso A A3

Asso B B1

Asso B B2

Asso C C1

Asso C C2

Asso C C3

En plus, en essayant de décortiquer vos formules, vous m’avez tous les trois permis de m’intéresser et de commencer à apprendre à en faire.

Un problème n’est pas réglé cependant (mais ce doit être simple pour vous) : dans la mesure où mon Google Sheet sera alimenté tout au long de l’année par un Google Forms renseigné par une multitude d’associations, je ne peux pas déterminer à l’avance la taille de la plage (nombre de colonnes), qui augmente sans cesse dans l’année.

Comment ne pas se réduire à une plage déterminée dans votre formule mais la rendre évolutive (j’ai l’impression que c’est le cas de la formule de Filoche) ?

Merci encore mille fois pour vos lumières !!

Fred

Bonjour,

Voilà une solution, à mettre en A2 dans l'onglet "TRANSFORMATION" :

Si tu as besoin de plus de colonnes, remplace ZZ par la valeur nécessaire...

=query(ArrayFormula(split(tocol('GOOGLE FORM'!A2:A&"|"&'GOOGLE FORM'!B2:ZZ;1;0);"|"));"select * where Col2 is not null")

Cordialement,

Fil.

Bonjour,

Merci beaucoup, Fil, pour cette solution très claire et très efficace !

Hélas, j’avais proposé cet exemple simplifié pour ne pas en demander trop aux membres de ce forum, pensant que je pourrais adapter vos propositions à ma situation plus complexe, mais je me rends compte que je ne suis pas assez balèze pour ça.

J’aurais dû faire comme toi et montrer directement ma situation sur un lien Google Sheet.

Alors si tu n’es pas encore découragé, j’ai ajouté 2 pages au Google Sheet que tu as inclus à ta réponse précédente : une première page contenant toutes les réponses au formulaire (pour l’instant ce sont des réponses fictives, j’en suis à la phase d’essai) et une deuxième page contenant ce que j’aimerais obtenir.

Tu verras que ce qui change, c’est que je ne souhaite pas seulement obtenir une colonne contenant seulement les noms des adhérents, mais aussi pour chaque adhérent, sur la même ligne, d’autres infos renseignées sur le formulaire d’origine.

Ces infos se répètent sur la page formulaire à partir de la colonne AG, par exemple pour le 1er adhérent de chaque asso : de la colonne AG à AM puis pour le deuxième de AO à AV, etc. (j'ai prévu un maximum de 50 adhérents par asso).

...Sachant aussi que toutes les infos ne doivent pas se retrouver dans mon tableau de destination (par exemple la colonne AN) mais seulement certaines (les titres des colonnes qui se retrouvent dans les deux tableaux).

Enfin, dans mon tableau destination, deux colonnes supplémentaires sont réservées seulement à une saisie manuelle.

J’espère avoir été clair, je suis conscient que ce n’est pas facile, mais compte-tenu du grand nombre de données que j’aurai à traiter, je serais super content si c’était possible.

Grand merci d’avance !

Fred

Bonjour,

J'ai rajouté un onglet "Proposition"...

Merci beaucoup, on y est presque !... Presque, car ta proposition ne reprend que le premier adhérent de chaque asso mais pas les adhérents suivants : il peut y en avoir jusqu'à 50 par asso (jusqu'à la colonne PP - mais sans les colonnes intitulées "Poursuivre")...

J'ai mis à jour pour les 3 premiers, la suite n'est que répétition de la méthode....

C'est génial ! 1000 merci !

Je vais mettre ça en œuvre immédiatement dans mon Google Sheet.

Si je comprends bien, je poursuis la formule avec les 50x8 colonnes potentiellement renseignées... Il n'y a pas de limite de taille pour une formule de calcul ?

C'est exactement ça...

Tu n'as plus qu'à....

Jusqu'à 10 millions de cellules ou 18 278 colonnes (colonne ZZZ) pour les feuilles de calcul créées dans Google Sheets ou converties en feuilles de calcul Google Sheets.

Oups ! pas tout à fait encore (mais bientôt, sûr !) : un problème se pose quand la colonne A (département de l'asso) n'est pas totalement numérique car contenant plusieurs départements (ex : "16,86,87"). Dans ce cas, la ligne apparait en fin de tableau (avec la première cellule vide).

Désolé, j'aurais dû te dire que ce cas était possible... :-(

J'ai ajouté cet exemple dans ton Google Sheet.

Il suffit de mettre la colonne C de ton onglet "Réponses au formulaire" au format " TEXTE BRUT"...

J'ai modifié la formule pour éviter les problèmes de formats :

=sort({query(arrayformula(query(({to_text('Réponses au formulaire'!A2:BC)});"select Col3,Col33,Col34,Col35,Col36,Col37,Col38,Col39 where Col33 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire'!A2:BC)});"select Col3, Col41, Col42, Col43, Col44, Col45, Col46,Col47 where Col42 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire'!A2:BC)});"select Col3, Col49, Col50, Col51, Col52, Col53, Col54,Col55 where Col49 is not null")))})

Bonne soirée...

Bonsoir Fil,

Je m'arrache les cheveux... Ta formule fonctionne nickel mais quand je l'étends à mes 50x8 colonnes, j'ai ce message qui apparaît :

"Erreur - Dans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d'un littéral de tableau."

En dehors de l'extension, je n'ai changé que 2 trucs pour l'adapter à mon Google Sheet : le nom de la page source (Réponses au formulaire 5) et le champ (A2:PP).

J'ai beau vérifier et revérifier si je ne me suis pas trompé dans les références de colonnes, je ne trouve pas.

Voici ma formule complète (je l'ai aussi mise dans un nouvel onglet de ton Google Sheet avec le nom de page source adéquat) :

=sort({query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3,Col33,Col34,Col35,Col36,Col37,Col38,Col39 where Col33 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col41, Col42, Col43, Col44, Col45, Col46,Col47 where Col41 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col49, Col50, Col51, Col52, Col53, Col54,Col55 where Col49 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col57, Col58, Col59, Col60, Col61, Col62, Col63 where col57 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col65, Col66, Col67, Col68, Col69, Col70, Col71 where col65 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col73, Col74, Col75, Col76, Col77, Col78, Col79 where col73 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col81, Col82, Col83, Col84, Col85, Col86, Col87 where col81 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col89, Col90, Col91, Col92, Col93, Col94, Col95 where col89 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col97, Col98, Col99, Col100, Col101, Col102, Col103 where col97 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col105, Col106, Col107, Col108, Col109, Col110, Col111 where col105 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col113, Col114, Col115, Col116, Col117, Col118, Col119 where col113 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col121, Col122, Col123, Col124, Col125, Col126, Col127 where col121 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col129, Col130, Col131, Col132, Col133, Col134, Col135 where col129 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col137, Col138, Col139, Col140, Col141, Col142, Col143 where col137 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col145, Col146, Col147, Col148, Col149, Col150, Col151 where col145 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col153, Col154, Col155, Col156, Col157, Col158, Col159 where col153 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col161, Col162, Col163, Col164, Col165, Col166, Col167 where col161 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col169, Col170, Col171, Col172, Col173, Col174, Col175 where col169 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col177, Col178, Col179, Col180, Col181, Col182, Col183 where col177 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col185, Col186, Col187, Col188, Col189, Col190, Col191 where col185 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col193, Col194, Col195, Col196, Col197, Col198, Col199 where col193 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col201, Col202, Col203, Col204, Col205, Col206, Col207 where col201 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col209, Col210, Col211, Col212, Col213, Col214, Col215 where col209 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col217, Col218, Col219, Col220, Col221, Col222, Col223 where col217 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col225, Col226, Col227, Col228, Col229, Col230, Col231 where col225 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col233, Col234, Col235, Col236, Col237, Col238, Col239 where col233 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col241, Col242, Col243, Col244, Col245, Col246, Col247 where col241 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col249, Col250, Col251, Col252, Col253, Col254, Col255 where col249 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col257, Col258, Col259, Col260, Col261, Col262, Col263 where col257 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col265, Col266, Col267, Col268, Col269, Col270, Col271 where col265 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col273, Col274, Col275, Col276, Col277, Col278, Col279 where col273 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col281, Col282, Col283, Col284, Col285, Col286, Col287 where col281 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col289, Col290, Col291, Col292, Col293, Col294, Col295 where col289 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col297, Col298, Col299, Col300, Col301, Col302, Col303 where col297 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col305, Col306, Col307, Col308, Col309, Col310, Col311 where col305 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col313, Col314, Col315, Col316, Col317, Col318, Col319 where col313 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col321, Col322, Col323, Col324, Col325, Col326, Col327 where col321 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col329, Col330, Col331, Col332, Col333, Col334, Col335 where col329 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col337, Col338, Col339, Col340, Col341, Col342, Col343 where col337 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col345, Col346, Col347, Col348, Col349, Col350, Col351 where col345 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col353, Col354, Col355, Col356, Col357, Col358, Col359 where col353 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col361, Col362, Col363, Col364, Col365, Col366, Col367 where col361 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col369, Col370, Col371, Col372, Col373, Col374, Col375 where col369 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col377, Col378, Col379, Col380, Col381, Col382, Col383 where col377 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col385, Col386, Col387, Col388, Col389, Col390, Col391 where col385 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col393, Col394, Col395, Col396, Col397, Col398, Col399 where col393 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col401, Col402, Col403, Col404, Col405, Col406, Col407 where col401 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col409, Col410, Col411, Col412, Col413, Col414, Col415 where col409 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col417, Col418, Col419, Col420, Col421, Col422, Col423 where col417 is not null")));query(arrayformula(query(({to_text('Réponses au formulaire 5'!A2:PP)});"select Col3, Col425, Col426, Col427, Col428, Col429, Col430, Col431 where col425 is not null")))})

Where is the blème ?...

Tu t'es juste trompé en recopiant la formule, tu as mis un " col " (après le WHERE) au lieu de " Col "...

query(arrayformula(query(({to_text('Réponses au formulaire'!A2:PP)});"select Col3, Col185, Col186, Col187, Col188, Col189, Col190, Col191 where col185 is not null")))

J'en peux plus ! Et toi non plus, je suppose, merci pour ta patience !!!

Alors voila : dans mon Google Sheet perso, contrairement au tien, c'est toujours le même message ("dans la fonction ARRAY_LITERAL, il manque des valeurs pour une ou plusieurs lignes d'un littéral de tableau").

J'ai pourtant remplacé les "c" minuscules par des "C" majuscules (je ne pensais pas que ça pouvait se jouer à si peu !)...

Alors j'ai 3 hypothèses :

1) La page source-test que je t'ai soumise ne reflète pas ma page perso, qui comporte peut-être une anomalie ;

2) Un problème proche à ce que j'ai trouvé ici (mais que je ne comprends pas) : https://forum.excel-pratique.com/sheets/erreur-dans-la-fonction-array-literal-dans-addition-de-query...

3) une erreur quant à la plage définie (A2:PP)

Bref... Je t'ai finalement copié ma page perso (qui est aussi au stade de test) dans ton tableau pour que tu te rendes compte (son nom diffère : "Réponses au formulaire 5")

Thannnnnnnnnnks so much !!!

Bon, j'ai trouvé le problème !

Tu as bien rempli les nouveaux numéros de colonne, mais pour qu'il n'y ait pas de messages d'erreur il faut remplir une ligne complète dans l'onglet "Formulaire".

Par exemple, une numérotation horizontale de 1 à (ton nombre de colonnes) et 0 en colonne C, afin que les données sorties par QUERY soient triées en début de tableau.

Tu masques ces lignes et le tour est joué...

Sauf nouvelle problématique ???