19.01.2022, 21:33
Wenn nötig ja
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
( ô ô ) Grüße, André aus G in T
ooO-(_)-Ooo (Excel 97-2019+365)
Clever-Excel-Forum
›
Office-Anwendungen
›
Excel
Kochrezepte: absolute Angaben aus relativen berechnen
Kochrezepte: absolute Angaben aus relativen berechnen
|
Kochrezepte: absolute Angaben aus relativen berechnen
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
19.01.2022, 23:10
(19.01.2022, 21:33)schauan schrieb: Wenn nötig ja Hast Du Lust ein Beispiel einer Formel zu nennen, damit ich ein Gefühl dafür bekomme, wann ihr INDEX mit benannten Bereichen einsetzt?
VG Andreas
-- Genutzte Version: MS Office 365 für Mac
Ich frage mich gerade, ob es für Experten möglich wäre, für die Aufgabe "Lückenloses Auflisten" eine benutzerdefinierte Funktion zu bauen.
Das ist ja alles andere als eine exotische Aufgabe. MS hat einfach vergessen, eine fertige Funktion dafür bereitzustellen : ) Benutzerdefinierte Funktionen können sogar auf der Plattform macOS verwendet werden: https://docs.microsoft.com/de-de/office/dev/add-ins/excel/custom-functions-overview Weiterhin frage ich mich, ob man das Verfahren mit der Formel =BEREICH.VERSCHIEBEN(Tabelle1!$J$7;0;0;ZÄHLENWENN(Tabelle1!$J$7:$J$107;">@")) im Dialog "Name definieren" ändern und die bereinigte Liste auch in eine Hilfsspalte auslagern kann. Ist mir aber nicht gelungen : ) Im Dropdownfeld wird dann als letzter Eintrag ein leerer gezeigt. Siehe Anhang. 
parameterrezept-template-0.4.xlsx (Größe: 14,28 KB / Downloads: 2)
VG Andreas
-- Genutzte Version: MS Office 365 für Mac 
21.01.2022, 17:15
Hallo Andreas,
also, ich zitiere Dich mal Zitat:Also weise ich C7:C16 den Bereichsnamen "_Nummerierung" und D7:D16 den Bereichsnamen "_Zutat" zu.Das wäre doch schon (D)ein Beispiel für die Verwendung. Oder bekommst Du die Formel nicht hin? Bereich.Verschieben würde ich übrigens nicht nehmen, wenn man es auch mit INDEX lösen kann. Was meinst Du mit lückenlosem auffüllen? Willst Du Deine Daten z.B. in einem anderen Bereich ohne Lücken auflisten oder willst Du in leere Zellen was eintragen?
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
21.01.2022, 17:41
(21.01.2022, 17:15)schauan schrieb: Das wäre doch schon (D)ein Beispiel für die Verwendung. Oder bekommst Du die Formel nicht hin? In Post #20 schrieb ich: Statt "F7" wäre es dann "INDEX(_Skaliert;7)" Das sei doch sehr länglich. Du schriebst dann, manchmal könne sowas sinnvoll sein. Mir ist noch nicht klar, in welcher Situation Du der sehr länglichen Form den Vorzug gegenüber der kurzen gibst. Daher würde ich mich über ein Beispiel von Dir freuen : ) Mein eigenes Beispiel ist ja ein Gegenbeispiel. Zitat:Bereich.Verschieben würde ich übrigens nicht nehmen, wenn man es auch mit INDEX lösen kann.Die Idee mit "Bereich.Verschieben" für die Dropdownbox kam ja von Holger. Wenn es da was Anderes gibt, freue ich mich über einen Vorschlag. Der aktuelle Stand des Projektes steht in #23. Zitat:Was meinst Du mit lückenlosem auffüllen? Willst Du Deine Daten z.B. in einem anderen Bereich ohne Lücken auflisten oder willst Du in leere Zellen was eintragen? Da verstehst Du mich miss. Es geht um die Umwandlung einer Liste mit Lücken in eine Liste ohne Lücken. Code: 3Code: 3Motiv: Ich möchte diese Liste als Quelle für die Dropdownbox verwenden. Denn in einer Dropdownbox soll es weder Lücken noch - am Ende - leere Einträge geben. Ist jetzt klarer geworden, was ich meine?
VG Andreas
-- Genutzte Version: MS Office 365 für Mac
Hallöchen,
Zitat:Mir ist noch nicht klar, in welcher Situation Du der sehr länglichen Form den Vorzug gegenüber der kurzen gibst. Daher würde ich mich über ein Beispiel von Dir freuen : ) Generell: Ein Bereich könnte man so benennen, dass man vom Namen her schon weiß, um was es geht. Wenn Du auf einem Blatt z.B. verschiedene Datenbereiche hast, die sich ggf. auch noch verändern können, braucht man ggf. auch eine Berechnung, um die gewünschte Zelle anzusprechen. Zudem bin ich flexibel und könnte über verschiedene Parameter, aber anders als z.B. bei VERWEISen, verschiedene Zellen ansprechen. Siehe z.B. die ab und an gestellte Frage nach dem SVERWEIS nach links ... Platziere Daten in A1:H8, K1:Z14 und F30:L40. Wenn Ich hier INDEX(Bereich1;1;1) schreibe weiß ich sofort, dass ich in der Matrix Bereich1 oben links bin. Wenn ich oberhalb des Bereichs Zeilen einfüge und anschließend eine weitere Formel benötige, die dort zugreift, bin ich mit 1;1 immer noch oben links und brauche nicht schauen, ob ich nun nach F31, 32, ... muss. Wenn ich später mal die Ergebnisse und Formeln analysiere, sehe ich auch gleich was ich da verrechne. ... Ansonsten, wenn Du genau weißt, dass Deine Daten in F7 stehen und Du weder an F noch an der 7 rütteln musst, macht INDEX oder BEREICH.VERSCHIEBEN keinen Sinn. Ich hatte bis jetzt auch meistens blaue oder grüne Autos ...  Zitat:Die Idee mit "Bereich.Verschieben" für die Dropdownbox kam ja von Holger. Wenn es da was Anderes gibt, freue ich mich über einen Vorschlag. Zitat:Es geht um die Umwandlung einer Liste mit Lücken in eine Liste ohne Lücken.Mit unserer Suche findest Du z.B. das Thread-Dropdown-mit-Suchfunktion-und-ohne-Leere-Zellen
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
Hallo Andreas,
Zitat:Du hast diese verwendet: =WENNFEHLER(@INDEX($I$7:$I$200;AGGREGAT(15;6;ZEILE($I$7:$I$200)-6*($I$7:$I$200<>"");ZEILE(A1)));"") In Office 365 steht dir die neue Funktion FILTER() https://www.youtube.com/watch?v=OHHKhiqQKos zur Verfügung, mit der geht es einfacher. Formel in I7 (Hilfsspalte) zum Lückenlosen auflisten der Zutaten: =FILTER($C$7:$C$16;$F$7:$F$16>0)&" "&FILTER($D$7:$D$16;$F$7:$F$16>0) Diese Formel erzeugt in Spalte I ein dynamisches Array, das dynamisch alle Einträge aus Spalte C bzw. Spalte D auflistet, die der Bedingung (in Spalte F muss ein Wert eingetragen sein) entsprechen. Und in deinem Dropdown in D4 kannst du dich dann direkt auf dieses dynamische Array in Spalte I beziehen (also ohne "Umweg" über einen im Namensmanager definierten Namen) Formel für Datengültigkeit (-> zulassen -> Liste) =$I$7#
Gruß Fred
@Fred11
Ganz herzlichen Dank für die Hinweise zu dieser sehr eleganten Lösung mittels der Formel "Filter". Die Formel habe ich sofort eingebaut, sie funktioniert perfekt. Eine kleine Sache habe ich noch nicht verstanden: In Deinem Beispiel ist nur eine Hilfsspalte zu sehen. Wie realisierst Du bitte E7:E16 ohne eine weitere Hilfsspalte? Am Rande: Du verwendest diese sehr praktischen HTML-Tabellen des Tools "Tab2Html": das will ich auch : ) Muss mich mal belesen, ob das in MS365 für Mac auch verwendbar ist. (22.01.2022, 13:56)schauan schrieb: Wenn Ich hier INDEX(Bereich1;1;1) schreibe weiß ich sofort, dass ich in der Matrix Bereich1 oben links bin. Das ist in der Tat ein schönes Merkmal. Wenn ich Dich richtig verstehe, plädierst Du also für den Ersatz meines bisherigen_ E7=WENN(F7="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*F7) durch E7=WENN(INDEX(_Skaliert;1)="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*INDEX(_Skaliert;1)) Das habe ich mal testweise gemacht, aber leider kann man einen derartigen relativen Bezug nicht über den üblichen Weg "Anfasser rechts unten in E7 greifen und Formel in die darunter liegenden Zellen ziehen" nicht übertragen. E8=WENN(INDEX(_Skaliert;1)="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*INDEX(_Skaliert;1)) Erwartet hatte ich: E8=WENN(INDEX(_Skaliert;2)="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*INDEX(_Skaliert;2)) Damit wäre die Verwendung von INDEX für benannte Bereiche unpraktisch. Gibt es eine Lösung für die Aufgabe? Übersehe ich etwas?
VG Andreas
-- Genutzte Version: MS Office 365 für Mac
24.01.2022, 21:28
Hallöchen,
Wie gesagt, da kann oder muss man zuweilen die Zelllage berechnen. Statt 1;1 kann man ZEILE(A1) ;SPALTE(A1) nehmen und dann geht auch die Änderung beim Ziehen in alle Richtungen. Natürlich unter Beachtung der Blattränder. Kleiner als 1 sollte nicht rauskommen 
. \\\|/// Hoffe, geholfen zu haben.
( ô ô ) Grüße, André aus G in T ooO-(_)-Ooo (Excel 97-2019+365)
25.01.2022, 01:09
Hallo Andreas,
Zitat:Wie realisierst Du bitte E7:E16 ohne eine weitere Hilfsspalte? Ich muss gestehen, ich habe nicht beachtet/bemerkt, dass in deiner Datei die "Hilfsspalte1" (Spalte I), in der bei dir die Zutaten erstmal mit Lücken aufgelistet (und mit der Zutatennummer verkettet) wurden, auch für die XVERWEIS-Formel in E7:E16 verwendet wird; ich habe mich nur auf die Dropdown-Thematik konzentriert... Wenn jetzt in dieser Hilfsspalte1 die Zutaten mit der FILTER-Formel gleich lückenlos aufgelistet würden, passt das ja dann tatsächlich nicht mehr mit der XVERWEIS-Formel in Spalte E. Also entweder musst du halt wieder mit zwei Hilfsspalten arbeiten: Hilfsspalte1 => Daten mit Lücken auflisten und mit der Zutatennummer verketten für die XVERWEIS-Formel Hilfsspalte2 => Daten lückenlos auflisten für das Dropdown Oder aber, und so würde ich das eher machen, du beziehst dich in der XVERWEIS-Formel in Spalte E direkt auf die Zutaten-Spalte (Spalte D). Weil du ja aber in deinem Dropdown in C4 auch die Zutatennummer mit aufgeführt haben möchtest (das macht das Ganze etwas aufwendig/umständlich), musst du halt zusätzlich auch noch die Spalte C mit reinpacken. Also so (Formel für E7): alt: =WENN(F7="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*F7) Neu: =WENN(F7="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_ZutatNummerierung&" "&_Zutat;_Skaliert)*F7) Dann sollte es wieder funktionieren (zumindest hat es bei mir so funktioniert):
Siehe angehängte Beispieldatei. ____________________________________________________________________________________________________________ INDEX ist eine tolle Funktion, mit der man u.a. einen dynamischen/variablen Zellbezug erzeugen kann. Aber im Fall deiner Formel in Spalte E =WENN(F7="";"";_GesetzteZutatMenge/XVERWEIS(_GesetzteZutat;_Hilfsspalte1;_Skaliert)*F7) macht es keinen Sinn, den relativen Zellbezug F7 (2x) durch ein INDEX-Konstrukt zu ersetzen, denn du möchtest ja, dass sich der Zellbezug beim runterziehen der Formel automatisch mitändert. Das wäre sonst ein bisschen von hinten durch die Brust ins Auge... Näheres zur Funktion INDEX siehe z.B. hier: https://www.youtube.com/watch?v=lrkwwNGfbKU https://www.youtube.com/watch?v=XikgEUzFc3o ____________________________________________________________________________________________________________ Das Tool Tab2Html findest du hier: https://www.clever-excel-forum.de/Thread...pid=100180 Gruß Fred | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste