[Résolu][Calc] Concaténer une plage de cellules

[gerard24]

Bonjour,

J'ai récupéré sur le forum anglais une fonction.
Celle-ci permet de concaténer une plage de cellules avec un séparateur. JOINCELLS(plage;séparateur).

La plage peut être en ligne, colonne ou rectangulaire. Les cellules vides sont ignorées.

Elle me convenait en l'état mais j'ai voulu l'améliorer. Je l'ai modifiée pour rendre le paramètre "séparateur" optionnel (facile à l'aide du livre de Bernard).

J'aimerai y ajouter encore d'autres modifs, mais là cela me dépasse.

1. Si par erreur le premier argument n'est pas une plage mais une seule cellule, la fonction renvoie une chaine vide.
J'aurais aimé qu'elle renvoie le contenu de la cellule. (sauf si vide, pas de zéro).

2. La fonction ignore les cellules vides (ce qui est très bien) mais aussi les cellules contenant 0 (ce qui l'est moins)
Et je ne vois pas comment différencier une cellule vide d'un 0.

Je vous joins le fichier contenant la macro.

concat.ods

[Alain de La Chaume]

Bonjour Gérard,

Voici, vite fait et pas encore vérifié, une proposition :

Code : Tout sélectionner

REM  *****  BASIC  *****
option explicit

function concat(cells, Optional sep as string) as string
dim ligne as long, col as long
dim bufOut as string, bufTmp as string

	If isMissing(sep) then sep = ""

	if typeName(cells) <> "Variant()" then
'		print "type: " & typename(cells)
		concat = cStr(cells)
	else
	   for ligne = lBound(cells, 1) to uBound(cells, 1)
	      for col = lBound(cells, 2) to uBound(cells, 2)
	         if cells(ligne, col) <> "" then
	         	bufTmp = cStr(cells(ligne, col)
	            bufOut = bufOut & bufTmp & sep
	         end if
	      next
	   next
	   concat = left(bufOut, len(bufOut) -len(sep))
	end if
end function

Ajout : Il apparaît après essais que le paramètre cells transmet la valeur zéro pour une cellule vide.

[gerard24]

Bonjour Alain,

Après un petit essai rapide, j'ai bien un résultat avec une cellule seule au lieu d'une plage.
Mais pour le reste, cela ne correspond pas à ce que j'attends : Les cellules vides sont concaténées pour 0 alors qu'avec la première version, elles sont ignorées.
Et je souhaites qu'elles soient ignorées.

A+

Ajout : Pas vu ton édit, c'est effectivement le problème.

[Alain de La Chaume]

... Je recherche un truc que j'ai fait il y a ... hou-là !
qui consiste à passer en paramètre la plage (ou la cellule) entre guillemets, donc en tant que chaîne, à charge pour la fonction d'extraire elle-même les valeurs de chacune des cellules. Et qui doit "traîner" quelque part sur le forum...

Ajout :

Je ne trouve pas, donc je vais refaire...

La plage peut être en ligne, colonne ou rectangulaire. Les cellules vides sont ignorées.

Dans le cas d'une plage rectangulaire, comment enchaîne-t-on les données ?
Chaque colonne d'une ligne de la plage (ensoleillée), avec répétition pour chaque ligne de la plage...
Ou l'inverse ?

Mais ce ne sera pas avant cette nuit, car là j'ai une gamelle sur le feu.

Bien cordialement, Alain

[Jeff]

Bonjour

Je ne trouve pas

J'ai souvenir de ce sujet :
http://user.services.openoffice.org/fr/ ... 541#p91541

A +

[Churay]

Bonjour Gérard

Une proposition :

Code : Tout sélectionner

Sub Test
Dim Ret
	Ret = CONCAT2("A7",) ' qui contient 0
	MsgBox Ret
End Sub 

Function CONCAT2(plage As String, Optional sep$) 
Dim oPlage As Object, oRange As Object
Dim Retour As String, Contenu As String
Dim nRow As Long, nCol As Long

	If isMissing(sep) then sep = ""
	
on error goto exitErr
	Retour = ""
	oPlage = thisComponent.Sheets(0).getCellRangeByName(plage)
	oRange = oPlage.RangeAddress
   
	for nRow = oRange.StartColumn to oRange.EndColumn 
		for nCol = oRange.StartRow to oRange.EndRow
			Contenu = thisComponent.Sheets(0).getCellByPosition(nRow, nCol).String
			Retour = Retour & Contenu & sep
      next
   next
   CONCAT2 = Retour
exit function
exitErr:
   REM #VALUE
   CONCAT2 = Null
End Function

[gerard24]

@ Alain

Je me suis servi de ton test if typeName(cells) <> "Variant()" then
Je ne sais pas si j'ai placé les if, else et end if au bon endroit mais cela à l'air de fonctionner.

Code : Tout sélectionner

Function CONCAT(cells, Optional sep$)
	If isMissing(sep) then sep = ""
REM J'ai aussi rendu le séparateur optionnel

on error goto exitErr
   if typeName(cells) <> "Variant()" then
      concat = cStr(cells)
   	else
   CONCAT = ""
   for row = lBound(cells, 1) to uBound(cells, 1)
      for col = lBound(cells, 2) to uBound(cells, 2)
         if cells(row, col) <> 0 then
            if CONCAT <> "" then
               CONCAT = CONCAT & sep
            end if
            CONCAT = CONCAT & cells(row, col)
         end if
      next
   next
   end if
exit function
exitErr:
   REM #VALUE
   CONCAT = Null
End Function

Il ne me reste alors à résoudre que le problème des 0 qui doivent être traités différement des cellules vides.

@ Churray

Je n'ai pas encore testé.

[Alain de La Chaume]

Bonsoir,

Si je veux "livrer" avant minuit, va falloir faire vite

110721_gerard24_concat_adlc.ods

Dans un Ajout de cet après-midi, je demandais :

Dans le cas d'une plage rectangulaire, comment enchaîne-t-on les données ?
Chaque colonne d'une ligne de la plage (ensoleillée), avec répétition pour chaque ligne de la plage...
Ou l'inverse ?

Mais le problème des ajouts c'est qu'ils ne sont pas forcément lus.
Pourquoi remonter voir un message déjà lu ? Moi non plus je ne le fais pas.
L'exemple joint fait donc ce qu'il veut, comme il le veut...

Merci à Jeff de m'avoir remis en mémoire (pleine comme un oeuf) cette macro de mon crû.
Elle m'aura au moins servi pour retrouver le raccourci pour actualiser les cellules
(quand je vous dis que Calc c'est pas mon truc !).

@ Churray
Je n'ai pas encore testé.

Heu... J'ai honte, mais moi non plus.

Bien cordialement, Alain.

[Churay]

Bonsoir,

@ Churray
Je n'ai pas encore testé.

Heu... J'ai honte, mais moi non plus.

Moi si
Mais j'avais encore compris à côté de la plaque :

J'ai récupéré sur le forum anglais une fonction.
Celle-ci permet de concaténer une plage de cellules avec un séparateur. JOINCELLS(plage;séparateur).

En section macros, je comprends macro-fonction. C'est oublier les liens Gérard & Calc ...

(quand je vous dis que Calc c'est pas mon truc !).

Un peu quand même : j'avais oublié de virer le dernier séparateur... Donc je te pique ton
concat = left(bufOut, len(bufOut) -len(sep))
que je place avant mon affectation de valeur de retour, en l'adaptant à ma mouture :
Retour = left(Retour, len(Retour) -len(sep))
CONCAT2 = Retour

[gerard24]

Super, je vois que vous avez passé la nuit dessus.
Mais (ben oui, je pinaille... ), il subsiste avec vos 2 fonctions le même problème (que je n'ai pas avec la fonction que j'ai récupéré) :
Avec des cellules vides, les séparateurs sont doublés, triplés...
CONCAT(A1:A4;"-") avec A2 et A3 vide me renvoie A1---A4 au lieu de A2-A4.

Le but n'est pas ici de préparer le terrain pour un export .csv.

Pour enfin répondre à ta question, Alain, dans le cas d'un rectangle : ligne par ligne me convient. A1-A2-B1-B2

A+

[Alain de La Chaume]

Avec des cellules vides, les séparateurs sont doublés, triplés...

Nouvelle version qui en tient compte. A vérifier.

J'en ai profité pour virer l'appel d'une macro à l'ouverture du doc (spécifique ADLC).

110721_gerard24_concat_adlc_v2.ods

Ajout : Rectification : j'ai voulu supprimer l'appel à la macro... Echec et mat ! Je ferais des tests sur le sujet et si nécessaire ouvrirai un nouveau fil sur le sujet.

Bien cordialement, Alain

Ajout : Encore un... On ne va tout de même pas se bousculer pour répondre. Je laisse donc l'ami Churay sur le coup et m'en vais de ce pas voir l'état de la mer.

[Churay]

Bonjour,

J'avais remarqué que ce n'était pas très élégant, mais que cela donnait une indication.

Voici donc la version qui filtre le vide

Ajout :  J'en ai profité pour virer l'appel d'une macro à l'ouverture du doc (spécifique ADLC). Ca faisait négligé en effet

[gerard24]

Vous allez trop vite pour moi.

Je regarderais ça plus tard. Un premier essai à l'air concluant hormis le fait qu'il faille relancer le calcul après modification d'une valeur ?

Je vous tiens au courant.

[Churay]

hormis le fait qu'il faille relancer le calcul après modification d'une valeur ?Je vous tiens au courant.

Je ne reproduis pas : modification d'une cellule → maj de la cellule comportant la formule

[Jeff]

modification d'une cellule → maj de la cellule comportant la formule

Je vois 2 fonctions personnalisées :

1. Fonction CONCAT :
   • Mise à jour automatique +
   • Pas besoin d'utiliser de guillemets pour une plage +
   • Necessite de construire une plage type A1:A1 pour avoir une seule cellule renvoyée +-
   • Ne prends pas en compte les cellules contenant 0 -
2. Fonction CONCAT2
   • Mise à jour sur ordre +-
   • Utilisation de guillemets pour une plage -
   • Permet d'utiliser "A1" pour avoir une seule cellule renvoyée +-
   • Prends en compte les cellules contenant 0 +

Je ne sais pas quel est le fonctionnement voulu par Gérard, je me suis contenté de signaler par des + et/ou des - ce que j'en pense au premier abord.

Dans tout les cas :

[Jean-Louis Cadeillan]

hormis le fait qu'il faille relancer le calcul après modification d'une valeur

Idem ==> On peut certes contourner avec CONCAT2()&T(ALEA()*0)...
Autres remarques, en vrac :

• la date 12/12/87 (en format date) ressort en chaîne 32123 avec CONCAT et en chaîne 12/12/87 avec CONCAT2
• le premier argument est en référence avec CONCAT et en chaîne avec CONCAT2 : avec CONCAT2, on ne peut pas dérouler la fonction => par exemple :
  • CONCAT(A1:B1) recopié vers le bas donne CONCAT(A2:B2) puis CONCAT(A3:B3) etc.
  • CONCAT2("A1:B1")recopié vers le bas donne CONCAT2("A1:B1"), etc.
• On peut contourner par :
  CONCAT2(CELLULE("adresse";A1)&":"&CELLULE("adresse";B1)), mais c'est quand même frustrant...
• d'ailleurs je ne comprends pas dans le code qu'est-ce qui fait que plage doit être une chaîne de caractère : bien sûr cette variable sert d'argument à getCellRangeByName qui exige une chaîne. Quand on met une référence au lieu d'une chaîne dans CONCAT2, on a l'erreur Variable d'objet non définie (en déconnectant la routine d'erreur). Donc si je comprends bien, on met ce qu'on veut pour premier argument de CONCAT2, mais comme getCellRangeByName exige du texte, tout autre entrée est gérée par la routine d'erreur avec un retour NULL, c'est ça ?
• Pour la lecture horizontale puis verticale d'une plage, je suppose qu'il faut inverser les nRow et nCol dans les For / Next pour inverser le sens de lecture ?
• C'est le fait de mettre un String (on ne ricane pas) qui permet de différencier 0 de vide ==> la longueur de 0 est 1, la longueur de vide est 0, c'est ça ?
• Donc, n' y a-t-il pas moyen de récupérer cette propriété String pour l'objet cells(row, col) de CONCAT (tout ça bien sûr pour avoir en entrée des références et non une chaîne...)
• Autre idée, page 461 de Programmation OpenOffice.org 3, il semble qu'on puisse utiliser toute fonction de Calc dans le code, en lançant un service spécial. Je reprends l'exemple donnée :

  Code : Tout sélectionner

  Function PPCM(liste As Variant) As Long
  Dim acceder As Object
  acceder = CreateUnoService("com.sun.star.sheet.FunctionAccess")
  PPCM = acceder.callFunction("LCM", liste)
  End Function

  Ne pourrait-on pas l'adapter à la fonction ESTVIDE(), qui différencie 0 de vide (anglais ISBLANK) (tu vois Gérard, j'utilise ton traducteur !), pour donner quelque chose comme ça :

  Code : Tout sélectionner

  Function Vide(liste As Variant) As Boolean
  Dim acceder As Object
  acceder = CreateUnoService("com.sun.star.sheet.FunctionAccess")
  Vide = acceder.callFunction("ISBLANK", liste)
  End Function

  qu'on appellerait ainsi ==> Vide(A1) ?

Bon tu me diras, faut essayer, ben justement j'ai le retour Variable d'objet non définie
A+
Jean-Louis

[Churay]

@ Jeff :
CONCAT, c'est la fonction originale
CONCAT2, version modifiée pour essentiellement prendre en compte les 0. Une des limitations, comme tu l'as remarqué, c'est l'obligation d'utiliser les " pour délimiter la cellule ou la plage.

@JLC :
Je vais lire lentement....

Un moment plus tard : Bin tu as tout bon

Donc, n' y a-t-il pas moyen de récupérer cette propriété String pour l'objet cells(row, col) de CONCAT (tout ça bien sûr pour avoir en entrée des références et non une chaîne...)

Je ne vois que passer par une Zone nommée, ce qui complexifie un peu la récupération des cellules concernées

Ne pourrait-on pas l'adapter à la fonction ESTVIDE()

Si tu veux, mais le cas du 0 vs "" est réglé

Ajout : on peut même poser un test du genre IF oCell.Type = com.sun.star.table.CellContentType.EMPTY Then ' si vide ou IF NOT oCell.Type = com.sun.star.table.CellContentType.EMPTY Then ' si non vide

Variable d'objet non définie

Code : Tout sélectionner

Vide = acceder.callFunction("ISBLANK", liste)

Vide n'est pas défini

---

Ajout : 2 Si dans le document il n'y a qu'une seule Zone nommée c'est simple, le début du code Code : Tout sélectionner Function CONCAT2(plage, Optional sep$) Dim oPlage As Object, oRange As Object Dim Retour As String, Contenu As String Dim nRow As Long, nCol As Long, aArr Dim aRange as new com.sun.star.table.CellRangeAddress If isMissing(sep) then sep = "" xRange = thisComponent.NamedRanges(0) XRay xRange on error goto exitErr Retour = "" oPlage = thisComponent.Sheets(0).getCellRangeByName(Plage devient Code : Tout sélectionner Function CONCAT2(plage, Optional sep$) Dim oPlage As Object, oRange As Object Dim Retour As String, Contenu As String Dim nRow As Long, nCol As Long, xRange ' Dim aRange as new com.sun.star.table.CellRangeAddress If isMissing(sep) then sep = "" xRange = thisComponent.NamedRanges(0) XRay xRange on error goto exitErr Retour = "" oPlage = thisComponent.Sheets(0).getCellRangeByName(xRange.Content) Le reste inchangé. S'il y a plusieurs zones, il faut les analyser. Le soucis, c'est que pour le moment je ne sais pas analyser Plage. Le seule chose que je vois avec XRay : c'est un Tableau(1 To 3, 1 To 2). Un Ubound, me donne la dimension 3 (j'ai eu une bonne idée de ne pas prendre une zone carrée, au moins, je sais à quoi correspond le 3...). Après c'est le brouillard. Sauf à chercher une erreur Out of Range et la gérer, je ne vois pas. Mais programmer en provoquant délibérément une erreur pour récupérer une dimension, ce n'est pas très sérieux...

Ajout : 3 En fin de compte, même avec les ", elle n'est pas si mal la fonction... GOOorOOos au secours...!!!

[Jean-Louis Cadeillan]

Mais oui elle est géniale ta fonction
Il se trouve que la programmation objet, c'est pas ma tasse de thé (et pourtant tu auras remarqué que j'en tiens toujours une...)
Alors j'ai du mal à comprendre les arcanes du code. En plus, je le reconnais, mes remarques étaient en vrac. J'essayais de réconcilier les deux approches (CONCAT et CONCAT2) ==> mon idée d'utiliser la fonction ISBLANK était bien sûr pour CONCAT et non pour CONCAT2, puisque le plus gros défaut de CONCAT est bien de confondre O et vide, donc j'espérais une hybridation entre les deux démarches...
Ceci dit ma vision a fortement tendance à devenir cellulaire (comme les mouches...) ce qui ne facilite pas le dialogue... bravo pour ta patience en tout cas
Peut-être que Gérard va nous revenir avec des pépites et Alain ...avec la marée
A+
Jean-Louis

[Churay]

Bonne fin de nuit

Il se trouve que la programmation objet, c'est pas ma tasse de thé (et pourtant tu auras remarqué que j'en tiens toujours une...)

En tasse, je préfère le café noir, sans sucre.

mon idée d'utiliser la fonction ISBLANK

Ca m'a donné l'idée de tester plus sérieusement. Résultats :
=CONCAT(A3:A4;" - ") passe en paramètre un tableau de dimension 2,1, avec en 1,1 le contenu d'une cellule vide, en 2,1 le caractère =, donc un string.
Arrivé dans la fonction, le tableau en 2,1 a bien le le caractère = donc un type string , mais en 1,1 la cellule vide est devenue un 0, donc un numérique de type 5 (double en virgule flottante). Partant de là, par les valeurs passées dans le tableau, on ne sait plus différencier une cellule vide d'une cellule ne contenant rien.
Par contre, on peut concaténer une seule cellule (les puristes de la langue vont aimer ce raccourci) en rusant : =CONCAT(A4:A4).

Si le contenu de la plage est unique dans la feuille, on peut concevoir de concaténer, rechercher (et trouver) les coordonnées des cellules et analyser la plage pour distinguer le 0 du vide. Peut-être un peu lourd pour ne pas avoir à saisir deux "...
Mais dès lors qu'il y a deux plages identiques à la nuance près du 0 / vide, il est impossible de savoir laquelle a été passée en paramètre. L'intérêt de la lourdeur du traitement devient moins évident.

Donc les options sont :
- sans guillemets et sans distinction entre 0 et rien
- avec distinction entre 0 et rien, mais avec des guillemets délimitant la plage.

[Did]

Bonjour,

Bon, sans vouloir rivaliser avec Churay et sans avoir consulté son code, je présente le mien qui part du même principe que le sien.

L'appel de la fonction se fait aussi avec la plage entre guillemet.
=CONCACT(''A1:A7''; '';'')

Tout en respectant les critères de Gérard24 il prend aussi le multiple sélection en charge avec un ( ; ) comme séparateur ex .
=CONCACT(''A1:A7; B1:B7; C7''; ''-'')

Ajout : Au fait je vient de constaté que ma fonction ne se déclenche pas automatiquement à l'ouverture du fichier

[Churay]

Bonjour

L'idée du ; est sympa J'en étais resté basiquement à la demande de Gérard : une cellule ou une plage...

Mais, il y a un léger soucis de séparateur avec =CONCAT("A1:C7";"B2";"C7";"C3";"A5";"-") par exemple.
Une fois réglécela rajoutera l'option de la multisélection.

Ajout : J'ai rien dit... : j'ai posé les " autour de chaque référence...

Ajout : 2 Par contre, ça m'a donné de tester si l'utilisateur final innove et demande des séparateurs différents, donc en posant des " un peu partout : =CONCAT("A1:C7";" √ ";"B1"; " - ";"C7";" | ";"C3";" ² ";"A5";" \\\") ==> seuls "A1:C7";" √ " est pris en compte : dommage, mais normal

[Did]

Salut,

Mais, il y a un léger soucis de séparateur avec =CONCAT("A1:C7";"B2";"C7";"C3";"A5";"-") par exemple.

La formule exacte est =CONCAT("A1:C7;B2;C7;C3;A5";"-")

Par contre, ça m'a donné de tester si l'utilisateur final innove et demande des séparateurs différents, donc en posant des " un peu partout : =CONCAT("A1:C7";" √ ";"B1"; " - ";"C7";" | ";"C3";" ² ";"A5";" \\\") ==> seuls "A1:C7";" √ " est pris en compte : dommage, mais normal

Essai la formule suivante :
=CONCATENER(CONCAT("A1:A2";" √ "); CONCAT("A2:A4"; " - "); CONCAT("A5:A6";" | "); CONCAT("A5:A6";" ² "); CONCAT("A5:A6";" \\\"))

Réponse : texte √ puis videpuis vide - =résultat | <>résultat ² <>résultat \\\<>

[Jeff]

Une autre approche, vue par Pierre-Yves Samyn, et inspiré de ce fil :

Pys_ConcatenerPlage.ods

A +

[gerard24]

Bien vu. L'utilisation de CELLULE("adresse") permet l'incrémentation de la formule et le "flag" son recalcul. (Même si c'est un peu long)

Je vais clore le sujet. Je pense que parmi toutes ces versions, chacun devrait pouvoir trouver celle qui lui convient.
(d'autant que je suis en train de me battre avec l'enregistreur de macro à cause d'une nouveauté de LibO. Je vais devoir passer par l'écriture)

A+

[Churay]

Bonne nuit

Superbe en effet ! du Pys_Inside...

(Même si c'est un peu long)

?
Pas instantané