zwei Bereiche zu einem zusammenfassen für Formel

[Frank Lauter]

Hallo NG!

Eine Formel benötigt einen Bereich als Parameter. Meine Datenanordnung
ermöglicht es aber leider nicht, dass die Daten in einem zusammenhängenden
Bereich stehen.

Wie kann ich einer Formel einen Mehrfachauswahlbereich als Bereich
weitergeben?

Konkret stehen die Werte in A10:A15 und in B4.

Excel soll so tun, als ob es eine Liste mit 7 Werten wäre.

=Summe((A10:A15;B4)) liefert einen Fehlerwert.

Ein Ansatz mit VBA führt ebenfalls zu einem Fehler:

Public Function RUnion(A As Range, B As Range) As Range
RUnion = Union(A, B)
End Function

Ich weiß, dass die Funktion Summe mehrere Argumente verwenden kann, aber die
Funktion XINTZINSFUSS(Werte;Zeitpunkte), bei der das Problem besteht, kann
das nicht. Zur Vereinfachung habe ich das Beispiel mit Summe gewählt.

Vielen Dank für eure Hilfe und viele Grüße

Frank

[Alexander Wolff]

Post by Frank Lauter
=Summe((A10:A15;B4)) liefert einen Fehlerwert.

=SUMME(A10:A15;B4) klappt (Klammerung nur einfach, ist aber egal)

Post by Frank Lauter
Public Function RUnion(A As Range, B As Range) As Range
RUnion = Union(A, B)
End Function
Ich weiß, dass die Funktion Summe mehrere Argumente verwenden kann,
aber die Funktion XINTZINSFUSS(Werte;Zeitpunkte), bei der das Problem
besteht, kann das nicht. Zur Vereinfachung habe ich das Beispiel mit
Summe gewählt.

Mehrere Bereiche in einer Funktion (s.o. mit SUMME) möglich: Das ist eher
die Ausnahme als die Regel. Du wirst um eine Umorganisation wohl nicht
herumkommen.

[Frank Lauter]

Hallo Alexander,

Post by Alexander Wolff
=SUMME(A10:A15;B4) klappt (Klammerung nur einfach, ist aber egal)

die Klammerung war doppelt, um mit "F9" die Auflösung der inneren Klammer
zu testen. Sorry, das hatte ich vergessen, dazuzuschreiben. Dass die
Summenformel selbst klappt, ist bekannt, aber sie ist ja nur eine Hilfe.

Post by Alexander Wolff

Post by Frank Lauter
Ich weiß, dass die Funktion Summe mehrere Argumente verwenden kann,
aber die Funktion XINTZINSFUSS(Werte;Zeitpunkte), bei der das Problem
besteht, kann das nicht. Zur Vereinfachung habe ich das Beispiel mit
Summe gewählt.

Mehrere Bereiche in einer Funktion (s.o. mit SUMME) möglich: Das ist eher
die Ausnahme als die Regel. Du wirst um eine Umorganisation wohl nicht
herumkommen.

Die Umorganisation ist nicht so einfach:
Die Daten stellen finanzielle Zahlungsströme dar, bei denen am Ende weitere
Daten angefügt werden.
Für meine Berechnung muss ich einen Verkauf des Restbestandes annehmen, der
aber, da tatsächlich nicht vorhanden, nicht verbucht wird. Diese
theoretische
Zeile darf nicht am Anfang stehen, da XINTZINSFUSS einen bestimmten
Startwert zuerst erwartet. Ein Einfügen am Ende würde bedeuten, dass ich die
Zeile zur Berechnung einfügen muss und danach entfernen muss, da es sich
nicht um eine echte Buchung handelt. Das wäre extrem aufwändig und würde
nicht zur Datengliederung passen.

Gibt es nicht eine andere Idee, die Datenbereiche der Funktion zu übergeben?

Vielen Dank für Deine Hilfe und freundliche Grüße

Frank

[Alexander Wolff]

wird. Diese theoretische
Zeile darf nicht am Anfang stehen, da XINTZINSFUSS einen bestimmten
Startwert zuerst erwartet. Ein Einfügen am Ende würde bedeuten, dass
ich die Zeile zur Berechnung einfügen muss und danach entfernen muss,
da es sich nicht um eine echte Buchung handelt. Das wäre extrem
aufwändig und würde nicht zur Datengliederung passen.
Gibt es nicht eine andere Idee, die Datenbereiche der Funktion zu übergeben?

Selbst als VBA-Funktion schreiben :) ?

Leider bringt Dich http://excelformeln.de/formeln.html?welcher=203 auch
nicht weiter; zwar brauchst Du kein Add-In mehr, aber immer noch einen
zusammenhängenden Bereich.

Ich hätte da aber noch so ne Idee (hier mit IKV; adaptieren auf
XINTZINSSFUSS mögest Du selbst):
X1: =WENN(A10;A10;$B$4) runterkopieren (länger als A10:A15)
Y1: hier Deine Zeitpunkte als zB =WENN(B10;B10;$B$4)
Z1: =IKV(BEREICH.VERSCHIEBEN(X1;;;ANZAHL(A:A)+1))

[Frank Lauter]

Hallo Alexander,

Post by Alexander Wolff
Ich hätte da aber noch so ne Idee (hier mit IKV; adaptieren auf
X1: =WENN(A10;A10;$B$4) runterkopieren (länger als A10:A15)
Y1: hier Deine Zeitpunkte als zB =WENN(B10;B10;$B$4)
Z1: =IKV(BEREICH.VERSCHIEBEN(X1;;;ANZAHL(A:A)+1))

das ist ein interessanter Ansatz, nur müsste die letzte Zeile dann immer
nach unten verschoben werden, wenn neue Datensätze hinzukommen.

Das Problem ist aber gelöst, Michael hatte den entscheidenden Hinweis. Im
Makro fehlte ein "Set".

Trotzdem vielen Dank für Deine Mühe und viele Grüße

Frank

[Alexander Wolff]

Post by Frank Lauter

Post by Alexander Wolff
Ich hätte da aber noch so ne Idee (hier mit IKV; adaptieren auf
X1: =WENN(A10;A10;$B$4) runterkopieren (länger als A10:A15)
Y1: hier Deine Zeitpunkte als zB =WENN(B10;B10;$B$4)
Z1: =IKV(BEREICH.VERSCHIEBEN(X1;;;ANZAHL(A:A)+1))

das ist ein interessanter Ansatz, nur müsste die letzte Zeile dann
immer nach unten verschoben werden, wenn neue Datensätze hinzukommen.

Nein, das nun gerade nicht. Ich habe es ja gerade dynamisch entworfen.

[Frank Lauter]

Hallo Alexander,

Post by Alexander Wolff

Post by Frank Lauter
das ist ein interessanter Ansatz, nur müsste die letzte Zeile dann
immer nach unten verschoben werden, wenn neue Datensätze hinzukommen.

Nein, das nun gerade nicht. Ich habe es ja gerade dynamisch entworfen.

das habe ich gesehen, nur kommen zu den Zahlungsströmen regelmäßig welche
hinzu, danach muss die theoretische Zeile kommen. Die theoretische Zeile
muss dann halt immer versetzt werden. Aber das Problem hat sich ja
glücklicherweise erledigt.

Vielen Dank und viele Grüße

Frank

[Alexander Wolff]

Post by Frank Lauter

Post by Alexander Wolff

Post by Frank Lauter
das ist ein interessanter Ansatz, nur müsste die letzte Zeile dann
immer nach unten verschoben werden, wenn neue Datensätze hinzukommen.

Nein, das nun gerade nicht. Ich habe es ja gerade dynamisch entworfen.

das habe ich gesehen, nur kommen zu den Zahlungsströmen regelmäßig welche
hinzu, danach muss die theoretische Zeile kommen. Die theoretische Zeile
muss dann halt immer versetzt werden. Aber das Problem hat sich ja
glücklicherweise erledigt.

Mitnichten. Du hast es nicht ausprobiert, scheint mir.

A:A muss _ansonsten_ leer sein (wegen ANZAHL).

B4 wird durch die Formel X1 immer hinten dran gesetzt, daher absolut ref.

Außerdem hatte ich geschrieben, dass das nur für IKV (einspaltig)
funktioniert; für das zweispaltige XINTZINSFUSS braucht es tatsächlich noch
etwas mehr Formeln. Das mache ich, wenn Du mir die Richtigkeit der
IKV-Lösung bestätigst.

[Frank Lauter]

Hallo Alexander,

Post by Alexander Wolff
Mitnichten. Du hast es nicht ausprobiert, scheint mir.

du hast Recht. Ich habe irgendwie die erste Formel überlesen und die
Vorgehensweise mit Bereich.Verschieben gesehen und für bekannt gehalten.

Jetzt habe ich es gesehen und auch auf XINTZINSFUSS angepasst. Vielen Dank
für den Hinweis.

Bei der anderen Lösung (Union in VBA) musste ich gestern leider feststellen,
dass diese nur funktioniert, wenn die Bereiche nebeneinander liegen. Das
erreiche ich aber auch nur mit deiner Vorgehensweise.

Viele Grüße und noch mal vielen Dank für deine Hilfe

Frank

[Alexander Wolff]

Post by Frank Lauter

Post by Alexander Wolff
Mitnichten. Du hast es nicht ausprobiert, scheint mir.

du hast Recht. Ich habe irgendwie die erste Formel überlesen und die
Vorgehensweise mit Bereich.Verschieben gesehen und für bekannt gehalten.

Danke schön! Es ist bestimmt nicht leicht, so etwas zuzugeben, aber Du
vergibst Dir dadurch gar nix ... und ich kann mich beruhigt zurücklehnen,
dass ich richtig und verständlich helfen konnte. Verständlichkeit war/ist
nämlich zeitlebens eine Schwäche bei mir.

Post by Frank Lauter
Jetzt habe ich es gesehen und auch auf XINTZINSFUSS angepasst. Vielen
Dank für den Hinweis.

Gern geschehen!

[Frank Lauter]

Hallo Alexander,

Post by Alexander Wolff
Danke schön! Es ist bestimmt nicht leicht, so etwas zuzugeben, aber Du
vergibst Dir dadurch gar nix ... und ich kann mich beruhigt zurücklehnen,
dass ich richtig und verständlich helfen konnte. Verständlichkeit war/ist
nämlich zeitlebens eine Schwäche bei mir.

da habe ich keine Probleme zuzugeben, dass ich das nicht gesehen habe. Ich
vermute, dass ich zu beschäftigt war mit den verschiedensten Lösungansätzen,
die ich verfolgt habe und deshalb das überlesen habe.
Ich habe deine Lösung aber nicht als unverständlich angesehen, im Gegenteil.
Sie war klar strukturiert und übersichtlich.

Zum Thema "Unverständlichkeit ": Fast jede Erkärung ist doch ein Abwägen
zwischen fachlich sehr präzise in einer Fachsprache und leicht
verständlicher aber ungenauer Sprache. Wenn sich die Kommunikationspartner
kennen, findet sich eher ein gemeinsames Sprachniveau, als wenn man sich wie
in einer NG zum ersten Mal trifft. Von daher hängt Verständlichkeit meiner
Meinung nach auch vom Zuhörer ab.

Viele Grüße

Frank

[Michael v. Fondern]

Post by Frank Lauter
Public Function RUnion(A As Range, B As Range) As Range
RUnion = Union(A, B)
End Function

Ungeprüft: sollte es nicht

Set RUnion = Union(A, B)

sein?

Grüße

- Michael -

[Frank Lauter]

Hallo Michael,

Post by Michael v. Fondern
Ungeprüft: sollte es nicht
Set RUnion = Union(A, B)

selbstverständlich! Das hätte ich eigentlich sehen müssen!
Und das löst das ganze Problem. Ich habe in etliche Richtungen gesucht, aber
nicht daran gedacht.

Vielen Dank und viele Grüße

Frank

[Bernd]

Hallo Frank,

eine etwas allgemeinere Union Loesung, die von Dana DeLois stammt und
auf einem Codebeispiel von Tushar Mehta aufsetzt:

Function VBA_Union(ParamArray V()) As Variant
Dim J, K
Dim Sd
Const Dummy As Byte = 0


Set Sd = CreateObject("Scripting.Dictionary")


On Error Resume Next
For J = 0 To UBound(V)
For K = 0 To UBound(V(J))
Sd.Add V(J)(K), Dummy
Next K
Next J
VBA_Union = Sd.Keys
End Function

Achtung: Diese Loesung loescht Duplikate und sortiert die Eintraege!

Viele Gruesse,
Bernd

[Frank Lauter]

Hallo Bernd,

Post by Bernd
Achtung: Diese Loesung loescht Duplikate und sortiert die Eintraege!

danke für deine allgemeine Lösung, aber das Löschen von Duplikaten könnte im
konkreten Fall fatale Auswirkungen haben.

Viele Grüße

Frank