Funktion "VERKETTENWENN"

[Herbstkatze]

Ich habe bereits die Calc-Funktionen ZÄHLENWENN und SUMMEWENN kennen gelernt, aber ich suche nach einer Funktion, die die Zellen einer Linie verkettet und durch ein bestimmtes Zeichen (z.B. Komma) trennt, wenn ein bestimmtes Kriterium erfüllt wird.
Existiert sowas schon oder gibt es jemanden, der in der Lage ist, diese Funktion zu schreiben? Ich selbst bin nämlich blutiger Anfänger in solchen Dingen.

Vielen Dank für eure Hilfe...

[balu]

Hallo Herbstkatze,

muss es eine Benutzerdefenierte Funktion (Makro) sein, oder geht auch eine Formellösung?

die die Zellen einer Linie verkettet

Meinst Du mit Linie eine Zeile?
Wenn ja, dann denke ich mir (sinngemäß gesprochen) so was in der Art von

Code: Alles auswählen

=WENN(Kriterium=Bedingung;Zelle1&","&Zelle2;Zelle0)

Gruß
balu

[Herbstkatze]

So habe ich es auch versucht, aber ich habe ca. 100 Spalten und da schleichen sich einfach zu viele Fehler ein.
Deswegen wäre wohl ein solches Makro am sinnvollsten. Weiss jemand, wie ich das anstelle?

Und natürlich meine ich Zeile und nicht Linie

[Karolus]

Hallo
Als Function ist das nicht ganz einfach umzusetzen, würde dir eine Sub reichen die alle Zeilen eines definierten Bereichs (oder einer Selektion) nach der Bedingung verketten und in eine Nachbarspalte schreibt?
Wie lautet die Bedingung? Wird sie in der zu verkettenden Zeile selbst angewand?

Gruß Karo

[Herbstkatze]

Ich denke schon, dass das funktionieren könnte... Meine Spalten G bis BZ (könnte noch mehr werden...) enthalten alle die Funktion

Code: Alles auswählen

=IF(SUMPRODUCT($Synthèse.$C$3:$C$150=$B5;$Synthèse.$I$3:$I$150=G$2);G2;0)

(die Bezugszellen ändern sich...) und spucken dadurch alle eine natürliche Zahl oder 0 aus.

In den Spalten D bis F würde ich gerne die Werte aus verschiedenen Bereichen der Zeile verknüpfen, wenn sie ungleich 0 sind, und zwar immer durch ein einfaches Komma getrennt.
Meint ihr, das ist möglich? Und wenn ja, wie stelle ich das an?

Danke für eure Hilfe

[Karolus]

Hallo
Ich versuchs mal als Function():

Code: Alles auswählen

function zverketten(von , bis, tab ,zzeile )as string

	osheet = thisComponent.sheets( tab -1 )
	srow = zzeile -1
	scol = von-1
	ecol = bis-1

	adat() = osheet.getcellrangebyposition(scol,srow,ecol,srow).getdataarray
	dim outlist(ubound(adat(0)))
	c = 0
	for i = scol to ecol
	if val(adat(0)(i)) = 0 then
	goto weiter:
	end if
		if isnumeric(adat(0)(i))  then
			outlist(c)= adat(0)(i)
			c = c+1
		end if
		weiter:
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		zverketten = join(outlist(),",")
	else 
		zverketten ="###"
	end if
End function

Die Syntax in der Zielzelle ist erklärungsbedürftig:
=zverketten(von;bis;Quelltabelle;zeile)
von = die Nr der ersten Spalte zb. 1 oder SPALTE(A1)
bis = die Nr der letzten Spalte z.B. 8 oder SPALTE(H1)
Quelltabelle = die Blattnr. des Quellbereiches z.B. 1
zeile = die Zeilennr die du verketten willst , praktisch ist es hier zb. ZEILE(A3) zu verwenden.

Gruß Karo

[Herbstkatze]

Danke dafür schon mal... Allerdings läuft es noch nicht fehlerfrei.
Ich habe die Function angelegt und die Funktion

Code: Alles auswählen

=ZVERKETTEN(COLUMN(F5);COLUMN(BX5);1;ROW(B5))

in meine Zelle eingefügt.
Dabei gibt es mir sofort die Fehlermeldung "Inadmissible value or data type. Index out of defined range." Und es öffnet sich der Code mit der Markierung auf

Code: Alles auswählen

if val(adat(0)(i)) = 0 then

Was ist zu tun bzw. was habe ich diesmal übersehen?

[Karolus]

Hallo
Vmtl. kommt deine OOoversion ? noch nicht mit 2dimensionalen Arrays zurecht, ich habs mal umgeschrieben auf Prüfung der einzelnen Zellwerte:

Code: Alles auswählen

function zverketten(von , bis, tab ,zzeile )as string

	osheet = thisComponent.sheets( tab -1 )
	srow = zzeile -1
	scol = von-1
	ecol = bis-1

	'adat() = osheet.getcellrangebyposition(scol,srow,ecol,srow).getdataarray
	dim outlist(ecol-scol)
	c = 0
	for i = scol to ecol
	if osheet.getcellbyposition(i,srow).value = 0 then
	goto weiter:
	end if
		if isnumeric(osheet.getcellbyposition(i,srow).value)  then
			outlist(c) = osheet.getcellbyposition(i,srow).string
			c = c+1
		end if
		weiter:
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		zverketten = join(outlist(),",")
	else 
		zverketten ="###"
	end if


End function

Gruß Karo

[Herbstkatze]

Jetzt klappts... Wunderschön! Vielen Dank dafür, Karo!

Nachtrag: Wirst du böse, wenn ich jetzt auch Zellen verknüpfen will, die nicht nur Zahlen, sondern auch Text enthalten? In der Function steht ja was von Integer. Muss man das einfach durch einen anderen Begriff ersetzen?

[Karolus]

Hallo
Ändere wie hier:

Code: Alles auswählen

...
if osheet.getcellbyposition(i,srow).string = "" then '!!passe diese Zeile an
   goto weiter:
   end if
'      if isnumeric(osheet.getcellbyposition(i,srow).value)  then 'auskommentieren
         outlist(c) = osheet.getcellbyposition(i,srow).string
         c = c+1
'      end if  'auskommentieren
      weiter:
   next i
...

Gruß Karo

[Herbstkatze]

Vielen Dank Karo! Jetzt klappts. Ich lade noch mal eine funktionierende Beispieldatei hoch, für den Fall, dass irgendwann noch mal jemand die gleiche Frage haben könnte

[Herbstkatze]

So eine letzte Frage habe ich noch:
Die Funktion kann man doch auch bestimmt so umformulieren, dass sie auch für Spalten funktioniert, also zum Beispiel die Zellen A1 bis A10 verknüpft.
Kannst du mir noch mal weiterhelfen?
Vielen, vielen Dank!

[Karolus]

Hallo

So eine letzte Frage habe ich noch:
Die Funktion kann man doch auch bestimmt so umformulieren, dass sie auch für Spalten funktioniert, also zum Beispiel die Zellen A1 bis A10 verknüpft.

Ja, aber auf die notwendigen Änderungen wärst du nach 15 Minuten Code lesen und nachdenken auch gekommen.

Code: Alles auswählen

function sverketten(von , bis, tab ,sspalte )as string

	osheet = thisComponent.sheets( tab -1 )
	scol = sspalte -1
	srow = von-1
	erow = bis-1

	dim outlist(erow-srow)
	c = 0
	for i = srow to erow
		if osheet.getcellbyposition(i,scol).string = 0 then
			goto weiter:
		end if
		if isnumeric(osheet.getcellbyposition(scol,i).value)  then
			outlist(c) = osheet.getcellbyposition(scol,i).string
			c = c+1
		end if
		weiter:
	next i
		if c > 0 then
			redim preserve outlist(c-1)
			sverketten = join(outlist(),",")
		else
			sverketten ="###"
		end if
End function

Gruß Karo

[Herbstkatze]

Du hast Recht...
Wie kommt es dazu, dass es Zellen mit dem Wert 0 doch des Öfteren verkettet, obwohl die doch laut Function eigentlich ignoriert werden sollen?
Die 0 entsteht immer durch eine Wenn-Dann-Funktion...

[Karolus]

Hallo
Ich hab noch gefunden wie man Addressbereiche in der normalen Formelsyntax verarbeitet:

Code: Alles auswählen

function rverketten(range() as variant )as string

if isarray(range()) then

	z = ubound(range,1)
	s = ubound(range,2)
	dim outlist(z*s)
	for i = 1 to z 'Zeilenindex
		For ii = 1 to s 'Spaltenindex
		if range(i,ii) <> ""  and range(i,ii) <> 0 and range(i,ii) <> " "  then
			outlist(c)= range(i,ii)
			c = c+1
		end if		
		next ii
	next i
	if c > 0 then
		redim preserve outlist(c-1)
		rverketten = join(outlist(),",")
	else
		rverketten = "# #"
	end if
	
else 
	rverketten = "# #"
end if
End function

Diese kannst du aufrufen mit:
=RVERKETTEN(A1:K1)
oder
=RVERKETTEN(A1:A11)
oder
=RVERKETTEN(A1:D5)

Null-werte, leere Zellen und Zellen mit einzelnen Leerzeichen werden nicht verkettet.

Gruß Karo