Beiträge zum Thread: Datensatz Von-Bis einzeln ausgeben

Hallo Aex,

/01 = von und /02 = bis ?

Gruß Uwe

Hallo Alex,

wenn ja dann so:

=LET(xA; FILTER(A1:A1000; A1:A1000<>"");xB; ANZAHL(xA);MTRANS(MAP(SEQUENZ(xB); LAMBDA(i;WENN(REST(i-1;3)+1=1;INDEX(xA;i);"/" & TEXT(INDEX(xA;i);"00"))))))

Gruß Uwe

Moin Alex, :-)

... so: ;-)
https://www.herber.de/bbs/user/180794.xlsx

Mit Power Query wäre das auch möglich. ;-)

Servus
Case

Moin Alex, :-)

... Power Query Variante: ;-)
https://www.herber.de/bbs/user/180795.xlsx

Und Hilfe dazu: ;-)
https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/

Servus
Case

Ja — ohne VBA geht das sehr gut mit dynamischen Array-Formeln in Excel 365.

Angenommen Quelldaten stehen in Tabelle1:

A B C
Gruppe Von Bis
120 1 2
121 1 3

Dann im Zielblatt in A1:

=LET(
Gruppe;Tabelle1!A2:A1000;
Von;Tabelle1!B2:B1000;
Bis;Tabelle1!C2:C1000;
Daten;FILTER(HSTAPELN(Gruppe;Von;Bis);Gruppe<>"");
REDUZIEREN(
"";SEQUENZ(ZEILEN(Daten));
LAMBDA(x;i;
VSTAPELN(
x;
HSTAPELN(
INDEX(Daten;i;1)+0*SEQUENZ(INDEX(Daten;i;3)-INDEX(Daten;i;2)+1);
TEXT(SEQUENZ(INDEX(Daten;i;3)-INDEX(Daten;i;2)+1;;INDEX(Daten;i;2));"/00")
)
)
)
)
)

Falls oben eine leere erste Zeile entsteht, nimm stattdessen ab A2 oder filtere sie weg:

=FILTER(A1#;INDEX(A1#;;1)<>"")

Ergebnis:

Gruppe Wert
120 /01
120 /02
121 /01
121 /02
121 /03

Besser noch: Mach aus dem Grunddatensatz eine Excel-Tabelle mit Strg + T, z. B. Name tblGruppen. Dann wächst die Formel automatisch mit.

Hallo Robert,

... in meiner Microsoft 365 MSO (Version 2605 Build 16.0.20026.20140) 32 Bit erhalte ich da nur #NAME?

Der Formelvorschlag von Daniel funktioniert dagegen einwandfrei und erscheint auf den ersten Blick auch wesentlich einfacher nachvollziehbar.

Gruß Werner
.. , - ...
aus C the unseen

Hallo Werner,

- es gibt keine 'REDUZIEREN'-Funktion, denn sie wurde nicht eingedeutscht und nennt sich 'REDUCE'
- aber auch mit dieser Änderung sind Fehler enthalten
- solche Datenumformungen realisiere ich lieber mit Power Query (siehe Lösung von Case)

hier mal meine PQ.Lösung:

let

    Quelle = Table.TransformColumnTypes(

        Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

        {{"Gruppe", Int64.Type}, {"Von", Int64.Type}, {"Bis", Int64.Type}}),

    ExL = Table.AddColumn(Quelle, "Wert", each {[Von]..[Bis]}),

    ExP = Table.TransformColumns(ExL,

        {{"Wert", each List.Transform(_, (k)=> "/"&Text.PadStart(Text.From(k),2,"0") )}})[[Gruppe],[Wert]],

    Erg = Table.ExpandListColumn(ExP, "Wert")

in

    Erg

PS:
Das Nachvollziehen von E_365-Formel-Lösungen kann viel Zeit in Anspruch nehmen - die investiere ich lieber in PQ!

Hallo Luschi,

... mit welcher XL-Version Robert seine Formel erstellt hat. Denn mit Sicherheit führt sie bei ihm zu keiner Fehlermeldung wie bei uns. Das wir beiden kein REDUZIEREN() einsetzen können, hatte ich schon herausgefunden. Aber der Formelansatz von Daniel hatte mir bisher als geeignet(er) für (m)eine Formelanpassung zugesagt.

Die PQ-Lösungen von Case und Dir sehen nur auf den ersten Blick einfacher als die Formellösungen von Case und Robert aus. Aber auch die bedürfen einige Zeit, diese nachzuvollziehen, wenn man sich ansonsten nicht viel mit PQ beschäftigt. So würde es zumindest mir viel schwerer fallen, den PQ-Script so zu erweitern, daß fehlerhafte/ungewollte Datenwerte nicht zu Fehlermeldungen bzw. zu falschen Ergebnisse führen.

Gruß Werner
.. , - ...
aus C the unseen

Hi

Noch eine möglichkeit, die Aufgabe per Formel zu lösen:

=LET(a;Tabelle1!A:C;

b;ANZAHL(SPALTENWAHL(a;3));

c;MAX(SPALTENWAHL(a;3));

d;SEQUENZ(b*c);

e;AUFRUNDEN(d/c;0);

f;REST(d-1;c)+1;

g;HSTAPELN(INDEX(a;e;1);f);

FILTER(g;(INDEX(a;e;2)<=f)*(INDEX(a;e;3)>=f)))

Gruß Daniel

Moin, :-)

... Formel- und Power Query -Lösung "überarbeitet" (REDUCE/VSTAPELN entfernt): ;-)
https://www.herber.de/bbs/user/180799.xlsx

Habe ein paar Erklärungen und Links in die Datei mit aufgenommen. ;-)

FILTER nehme ich, wenn Leerzeilen in den Grunddaten vorkommen können - sonst TRIMRANGE (wenn in Excelversion vorhanden). ;-)
https://support.microsoft.com/de-de/Excel/trimrange-function

Servus
Case

Hallo Alex,

... eine Teilbasis meiner nachfolgenden Formel waren Teile der Formel von Daniel.

=LET(

Nrn;INDEX(_MB;;1);

M;SORTIEREN(FILTER(_MB;Nrn<>""));

Nr;INDEX(M;;1);

Von;WENNFEHLER(--INDEX(M;;2);INDEX(M;;3));

Bis;WENNFEHLER(--INDEX(M;;3);Von);

Mz;HSTAPELN(Nr;WENN(Von>Bis;Bis;Von);WENN(Von>Bis;Von;Bis));

BisX;MAX(INDEX(Mz;;3));

Se;SEQUENZ(ANZAHL(INDEX(Mz;;3))*BisX);

Zl;AUFRUNDEN(Se/BisX;0);

Re;REST(Se-1;BisX)+1;

My;HSTAPELN(INDEX(Mz;Zl;1);Re);

FILTER(My;(INDEX(Mz;Zl;2)<=Re)*(INDEX(Mz;Zl;3)>=Re)))

Folgendes ist/wurde vorausgesetzt:
- Die auszuwertenden Datenwerte sind eine "intelligente 3spaltige Tabelle " (benannt von mir z.B. als: _MB)
Diese können fast beliebig erfasst, erweitert und auch nachträglich in der Datei und oder Tabellenblatt verschoben werden
- Jeder Datensatz muß aber (akt. noch) eine eindeutige GruppenNr. haben (also keine Duplikate!)

folgendes wird in der Formel zusätzlich berücksichtigt:
- Datenwerte der Gruppennummer können Zahlen- oder Textwerte sein
- Datenwerte in [Von] oder [Bis] die Textwerte sind, werden = ihrem Nachbarzahlenwert
- Datenwerte in [Von] oder [Bis] die leer sind, werden wie 1 gewertet
- Wenn [Bis] < [Von] wird von ein Vertauschen der beiden Werte angenommen.
- es erfolgt eine nach GruppenNr sortierte Ergebnisliste
- in 2. Spalte des Ergebnisses habe ich das benutzerdef. Zahlenformat "/"00 definiert (dadurch bleiben die Zahlenwerte auch weiterhin solche)

folgendes wird momentan noch nicht berücksichtigt:
- Datensätze mit identischer Gruppennummer und unterschiedlichen [Von] - [Bis] - Werten (Dazu bin ich u.a. auch aus Zeitgründen momentan nicht gekommen)

Auf evtl. Rückfragen kann ich leider zumindest die nächsten 10 Tage nicht reagieren.

Gruß Werner
.. , - ...
aus C the unseen

Hallo Werner,

im Lösungsvorschlag von Dir und Daniel gibt es ein Problem:
- ihr ermittelt den größten Zahlenwert in der 3. Spalte (bis), ohne den dazugehörigen Anfangswert (von) zu berücksichtigen
- BisX;MAX(INDEX(Mz;;3)); bzw. c;MAX(SPALTENWAHL(a;3));
- bei mir ist Anfangswert: 1.111 und Endwert: 1.112
- damit erzeugen eure Zwischenvariablen 'Zl' bzw. 'g' 16.680 Zeilenwerte
- im Ergebnis werden durch den Filter aber nur 52 Zeilenwerte berücksichtigt
- bei einer Erhöhung der Ausgangswerte auf 11.111 bzw. 11.112 werden es schon 166.680 Zwischenzeilenwerte
- die unnütz berechnet werden um dann meist durch das Gitterrost des Filters fallen
- das ist mir auch schon passiert ist, deshalb weise ich der Ergebnisformel grundsächlich auch eine Variable 'erg' zu
- um beim Testen sie gegen andere Variablen, die Zwischenergebnisse darstellt, auszutauschen

https://www.herber.de/bbs/user/180807.xlsx

Gruß von Luschi
aus klein-Paris

Hallo Luschi,

... leider.
Ich hätte dazu schreiben müssen, daß meine Lösung Grenzen hat. Z.B. das es schon bei einem größeren 6 stelligen Zahlenwert in [BIS] oder wenn es viele Gruppendatensätze mit sehr großen Differenzen [Bis] - [Von] gibt, zu Fehlermeldung kommen kann. Die teils Unmenge an berechneten Zwischendatenwerten war mir schon klar. Aber da es quasi meine erste XL365-Formellösung ist, war mir (noch) kein besserer Lösungsansatz eingefallen. Dies zumal auch momentan das Ergebnis relativ sehr schnell ermittelt wird. Meine nun eingestellte Datei https://www.herber.de/bbs/user/180808.xlsx dürfte das zeigen.
Du kannst dafür ja mal Deine PQ-Lösung anpassen. Ich schau mir das allerdings dann erst Ende nächster Woche an. Dann will ich mich auch noch der noch offenen Lösung für Datenzeilen mit der gleichen GruppenNr aber mit mehreren Datenbereichsangaben [Von] bis [Bis] annehmen. Momentan schwebt mir dafür ein evtl. möglicher Lösungsansatz nur schemenhaft vor. Aber es fehlt mir jetzt an Zeit, dies auch umzusetzen.

Gruß Werner
.. , - ...
aus C the unseen

Moin Werner, :-)

... PQ-Lösung habe ich deine "Bedingungen verwurschtelt": ;-)
https://www.herber.de/bbs/user/180809.xlsx

Prinzipiell kannst du das mit "try" erschlagen - {"Von", each try Int64.From(_) otherwise null, Int64.Type} - allerdings nur "generell". Zum Fehler abfangen reicht das schon, aber du willst ja mehrere Bedingungen. ;-)

Grob umrissen: ;-)
Mit der Funktion "GetWert" sage ich erstmal - was will ich haben, was sind die Bedingungen. ;-)
Hat den Vorteil, wenn neue Bedingungen aufschlagen, muss ich nur dort anpassen. ;-)

Dann lade ich die Quelle. ;-)
Nun werden die Zeilen entfernt, wo alle drei Werte gleich sind - also keine Doppelten/Mehrfachen. ;-)
Dann baue ich die Liste auf - mit meinen/deinen Bedingungen aus der Spalte "Wert" bezogen auf die Funktion "GetWert". ;-)
Anschließend lasse ich nur noch Zeilen übrig, wo Gruppe und Wert <> null. ;-)
Zum Schluß wird die Liste expandiert und nur noch die beiden Spalten Gruppe und Wert bleiben erhalten. ;-)

In "tblErgfnc" habe ich die Funktion "fncGetWert" ausgelagert. Das macht Sinn, wenn viele Bedingungen vorkommen. Wird übersichtlicher. ;-)
"tblErgfnc" ist "Nur Verbindung". ;-)

Der Unterschied in der Ausgabe liegt daran, dass deine Formel die "Doppelten" noch mit ausgibt. ;-)

Servus
Case

Hallo Case,

... denn momentan ist mir das nicht möglich.

Habe aber inzwischen noch mal unabhängig vom PC über meinen vorgeschlagenen Lösungsweg nachgedacht.
Meine Formel könnte vereinfacht werden, wenn ich anstelle der Überprüfung einer ordnungsgemäßen Datenerfassung, diese bereits bei der Datenerfassung vornehmen lasse. D.h. in den Zellen von [Von] und [Bis] eine entsprechende Datengültigkeitsprüfung definieren. So das dort keine Textwerte eingegeben werden können und der jeweilige [Bis]-nie kleiner als der [Von] - Datenwert ist.

Und bzgl. dessen, daß bei vorliegenden identischen Gruppennummer mit unterschiedlichen [Von] - [Bis] - Werten, müßte es einfach ausreichend sein, wenn ich zum Abschluß in meiner Formel, die bisherige Ergebnismatrix mit EINDEUTIG() und danach dann noch mit SORTIEREN() nach Spalte 2 sowie Spalte 1 klammere. Das bisherige eingebaute anfängliche Sortieren in meiner Formel kann dann auch wegfallen.

Ich schau es mir Ende kommender Woche am PC an und dann auch Deine PQ-Lösung.

Gruß Werner
.. , - ...
aus C the unseen