HERBERS Excel-Forum - das Archiv
Dropn-Down mit wenigen Buchstaben
Duinne

Hallo Leute,
ich möchte mir gerne eine Kalorientabelle basteln und bräuchte dabei ein bisschen Hilfe.
Für die Tabelle habe ich mir überlegt, eine Lebensmitteldatenbank anzulegen. Diese wird sich auf einem separaten Reiter befinden. Auf dem ersten Tabellenblatte möchte ich für jeden Tag alle Lebensmittel auflisten, die ich so verputzt habe.
Dazu möchte ich gerne in der dafür vorgesehen Zelle nur ein paar Buchstaben eingeben. Anhand dieser Buchtstaben möchte ich eine bereits gefilterte Auswahl erhalten. Also z.B. gebe ich "ap" und dabei erscheint eine Auswahlliste mit beispielsweise "Apfel", "Apfelsine" "Apfelsaft" etc. Die Buchstaben, die ich eingebe, müssen nicht zwangsläufig die ersten sein. Folgendes Beispiel: ich gebe "Alme" ein. In der Auswahlliste soll "Frischkäse, Kräuter - Almette" stehen.
Eventuell ist es sinnvoll, vorher noch Kategorien auszuwählen. Ich glaube, dass selbst eine gefilterte Auswahl recht lang werden könnte, weshalb man sich vorher etwas einschränkt und zum Beispiel nur Werte aus der Kategorie "Getreide" oder "Gemüse" wählen kann.
Gibt es eine Möglichkeit, so etwas ohne VBA zu lösen? Könnte mir hierbei jemand helfen?
Vielen Dank und liebe Grüße
Duinne

als Formellösung über Kategorieauswahl oder ...
der

Hallo Duinne,
... und dann über anschließende Direktauswahl möglich. Diese Lösungsart setzt voraus, dass je Kategorie eine überschaubare Menge an Lebensmittel gelistet sind.
Es gibt aber auch eine Lösungsmöglichkeit, die Deinen Vorstellung näher kommt. Dies allerdings nur, wenn Du die die Buchstaben (egal ob die bei Deinem Lebensmittel am Anfang stehen oder nicht) immer erst in eine separate Eingabezelle vornimmst. Erst danach kannst dann Deine Auswahl über die Dropdownzelle aus den dann eingeschränkten Listenangebot treffen. Wenn die dann angebotene Auswahlliste Dir noch zu groß ist, kannst Du deren Reduzierung nur dadurch vornehmen, in dem Du die Dropdownzelle zunächst verlässt und in der Eingabezelle weitere Buchstaben (in der richtigen Reihenfolge) ergänzt.
Entspräche so eine derartige Lösung unter den von Dir gesetzten Bedingungen Deinen Vorstellungen?
Gruß Werner
.. , - ...

AW: als Formellösung über Kategorieauswahl oder ...
Werner

Hi Namens-Vetter,
in kenne die Antwort auf Deine Frage von Duinne (noch) nicht ;)
Nur eines:
Dein Lösungsansatz würd mich seehr interessieren :))
Besten Gruss
Werner

AW: als Formellösung über Kategorieauswahl oder ...
Duinne

Hallo Werner,
ganz lieben Dank für deine Antwort!
Eine separate Zelle für die Eingabe wäre absolut in Ordnung.
Wäre das ungefähr so, wie in folgender Tabelle?
https://www.herber.de/bbs/user/98953.xlsx
Diese habe ich in der im Laufe des Tages gefunden - natürlich nicht von mir sondern von Andreas Thehos.
Leider ist mir nicht ganz klar, wie ich dort noch eine Dropdown-Liste davor schalten kann, in denen ich die Kategorien auswähle, um weitere Einschränkungen vorzunehmen.
Selbstverständlich kann die Lösung auch anders aussehen.
Vielen Dank für die Unterstützung!
Liebe Grüße
Duinne

dazu zunächst soviel ...
der

Hallo Duinne,
... im Prinzip hat es Andreas in seinem Beispiel schon so gelöst, wie ich es geplant hatte. Besonders angetan bin ich (natürlich ;-) ) davon, dass er eine AGGREGAT()-Formellösung zum Erstellen der Dropdownliste genutzt hat. Allerdings seine Bereichsnamensdefinition für die Dropdownzelle würde ich so nie vorschlagen.
Um Deine zusätzliche Bedingung korrekt einzubauen, wäre es hilfreich, dass Du mal einen relevanten Auszug aus Deiner Datenliste hier einstellst, damit wir nicht aneinander vorbei schreiben.
Ich schaue dann später nochmal danach.
Gruß Werner
.. , - ...

AW: dazu zunächst soviel ...
Duinne

Guten Morgen Werner,
schön, dass du mir hilfst!
Die Formel AGGREGAT() war mir zum Beispiel noch völlig unbekannt... Man lernt einfach nicht aus.
Also, ich habe jetzt mal die Tabelle hochgeladen. Auf dem ersten Reiter ist - erst einmal ganz einfach gehalten - die Tabelle zu sehen, in der der tägliche Kalorienverbrauch "getrackt" wird. Hier soll dann auch die dropdown-Auswahl zur Verfügung stehen.
Auf dem zweiten Reiter stehen die Lebensmittel.
https://www.herber.de/bbs/user/98960.xlsx
Möglicherweise muss hier noch umsortiert werden? Ich habe gelesen, dass bei abhängigen Dropdown-Listen ein bestimmtes Format notwendig ist, nämlich die Listen nebeneinander anzuordnen. Das kann ich dann gerne noch machen, sofern notwendig.
Grüße
Duinne

zu dem Listing Deiner Daten und zu AGGREGAT() ...
der

Hallo Duinne,
... eine Umsortierung ist auf jeden Fall sinnvoll. Wenn Du z.B. die Kategorien zugeordnet in einer separaten Spalte links Deiner Lebensmitteldaten schreibst, wäre es möglich sowohl diese in der einen Eingabezelle direkt gleich mit zu zu berücksichtigen, als als auch abhängige Dropdown-Listen anzubieten.
Ersteres dürfte für einen Nutzer wahrscheinlich der einfachere und vor allem schneller Weg sein und würde ich dann so umsetzen.
Kannst Du die Daten-Umsortierung schon mal auf den Weg bringen? Ich nehme mir dann anschließend am späteren Nachmittag dessen nochmal an.
Und noch folgendes: AGGREGAT() ist eine Funktion. In der Art und Weise wie ich sie auch erst seit März dieses Jahres in Formeln einsetze (quasi u.a. um damit echte MATRIXFormeln zu vermeiden) ist sie nicht nur Dir sondern wohl den meisten selbst fortgeschrittenen Excel-Nutzern noch völlig unbekannt. Jedenfalls so lange sie nicht mal über einen entsprechenden Forumsbeitrag von mir oder z.B. Andreas "gestolpert" sind ;-)
Gruß Werner
.. , - ...

AW: zu dem Listing Deiner Daten und zu AGGREGAT() ...
Duinne

Hallo Werner,
hier ist die neu sortierte Tabelle:
https://www.herber.de/bbs/user/98973.xlsx
Vielleicht können wir damit zu einem höheren Bekanntheitsgrad der AGGREGAT()-Funktion beitragen =)
Bin nur jedes Mal beeindruckt, was es alles gibt und wie wenig man dann doch kennt.
Liebe Grüße
Duinne

AW: Dropn-Down mit wenigen Buchstaben
Daniel

Hi
mal ein alternativer Lösungsvorschlag, passend zu deinem Excelniveau:
1. erstelle die Kalorientabelle (deine Datenbank) als durchgehende Liste mit sinnvollen Kategorien.
2. wenn du deine Tagestabelle erstellst dann gehe in deine Datenbank und suche dir die Sachen zusammen, die du am Tag gegessen hast, hierbei kannst du die Sortierung oder den Autofilter nutzen, um die Lebensmittel schneller zu finden.
3. Trage in einer weiteren Spalte hinter alle Lebensmittel, die du gegessen hast, ein "x" ein.
4. wenn du alle Lebensmittel so markiers hast, filtere in dieser Spalte nach den markierten Lebensmitteln und kopiere sie in deine Tracking-Tablle, wo du dann Menge und die weiteren Daten hinzu geben kannst.
5. lösche die "x" wieder.
ist zwar jetzt im Händling etwas aufwendiger, dafür kannst du aber die ganzen Such- und Filtermethoden von Excel nutzen, um den passenden Eintrag zu finden.
Gruß Daniel

Formeln mit AGGREGAT() und INDEX() ...
der

Hallo Duinne,
... ich meinte die Datentabelle etwas anders strukturiert. Dazu siehe unten stehend.
Mit nunmehr zwei Eingabezellen (es kann auch nur eine "gefüllt" werden kannst Du mit zwei Hilfsspaltenformel und zwei weiteren Formeln das gewünschte realisieren.
Ich hab es aus Zeitmangel nicht endgültig getestet, sollte aber im Wesentlichen funktionieren. Bin dann erst Morgen wieder online.
Hilfsspaltenformeln H max. 19 Zellen weit nach unten ziehend kopieren (mehr sind in Dropdownzelle sowie nicht mehr sinnvoll; dann eben Suchzeichen erweitern.
Dropdownzelle in Spalte [@Lebensmittel]
Tracker

 ABCDE
1 Suchzeichen KategorieSuchzeichen Lebensmittel  
2 einsaKalorienverbauch
3     
4DatumKategorieLebensmittelMengeKalorien berechnet
5 Alkoholfreie GetränkeKaffee (Instantkaffee, Pulverkaffee), schwarz  
6 Alkoholfreie GetränkeBier, Pils, alkoholfrei  
7 FleischgerichteBurgunderbraten mit Soße (ohne weitere Beilagen)  
8 Alkoholische GetränkeBier, dunkel (Dunkelbier, dunkles Bier)  
9 Alkoholfreie GetränkeBananensaft (Muttersaft)  
10     

Formeln der Tabelle
ZelleFormel
B5=WENN([@Lebensmittel]="";"";WENNFEHLER(INDEX(Kal_Tab!A:A;VERGLEICH(C5;Kal_Tab!B:B;)); "?"))

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C5Liste =Auswahl 
Namen in Formeln
ZelleNameBezieht sich auf
C5Auswahl=Kal_Tab!$I$2:INDEX(Kal_Tab!$I:$I;SUMME(N(LÄNGE(Kal_Tab!$I$1:$I$999)>1)))
Namen verstehen


Kal_Tab

 ABCDEFGHI
1KategorieLebensmittelkcal    Hilfsspalte KategorieHilfsspalte LM
2Alkoholfreie GetränkeAnanassaft51    Alkoholfreie GetränkeBananensaft (Muttersaft)
3Alkoholfreie GetränkeAnistee9    FleischgerichteBirnensaft
4Alkoholfreie GetränkeApfelfruchtsaftgetränk47      
5Alkoholfreie GetränkeApfelsaft, grüner Apfel48      
6Alkoholfreie GetränkeApfelsaft, roter Apfel46      
7Alkoholfreie GetränkeApfelschorle34      
8Alkoholfreie GetränkeAronia-Nektar, (Schwarze Eberesche, Apfelbeeren)38      
9Alkoholfreie GetränkeBananensaft (Muttersaft)50      
10Alkoholfreie GetränkeBier, Pils, alkoholfrei25      
11Alkoholfreie GetränkeBirnensaft39      
12Alkoholfreie GetränkeBitter Lemon (Bitterlimonade)44      
13Alkoholfreie GetränkeBrennnesseltee3      
14Alkoholfreie GetränkeBrombeersaft (Muttersaft)30      
15Alkoholfreie GetränkeGinger Ale (Ingwerlimonade)45      
16Alkoholfreie GetränkeIngwertee2      
17Alkoholfreie GetränkeKaffee (Instantkaffee, Pulverkaffee), schwarz4      
18Alkoholfreie GetränkeLindenblütentee3      
19Alkoholische GetränkeAbsinth (Kräuterschnaps, Wermutschnaps) 70 Vol.%472      
20Alkoholische GetränkeÄthanol, Ethanol, reiner Alkohol, Reinsprit 98 Vol.%604      
21Alkoholische GetränkeAlsterwasser, Radler45      
22Alkoholische GetränkeAltbier, Alt-Bier49      
23Alkoholische GetränkeAnislikör 40 Vol.%296      
24Alkoholische GetränkeApfelwein Apfelmost, Cidre, Cider, Sidra, Sidre45      
25Alkoholische GetränkeArrak231      
26Alkoholische GetränkeBärwurzschnaps, 40 Vol.%246      
27Alkoholische GetränkeBerliner Weiße mit Schuss (Waldmeister, Himbeer)51      
28Alkoholische GetränkeBier, dunkel (Dunkelbier, dunkles Bier)37      
29Alkoholische GetränkeBier, Export (Exportbier)41      
30FleischgerichteBackschinken (Krustenbraten, Schweinebraten aus der Keule)71      
31FleischgerichteBerner Würstchen, Berner Würstl, Berner Würstel349      
32FleischgerichteBurgunderbraten mit Soße (ohne weitere Beilagen)120      
33FleischgerichteCarpaccio (venezianisches rohes Rinderfilet mit Mayonnaise-Sauce und Brot)345      
34FleischgerichteCevapcici235      
35FleischgerichteChicken-Nuggets (Hähnchen Taler, Chicken Chips, Hähnchennuggets)190      
36FleischgerichteChili con carne89      
37FleischgerichteCordon bleu (aus Hähnchenfleisch)195      

Formeln der Tabelle
ZelleFormel
H2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$999)/ISTZAHL(SUCHEN(Tracker!$B$2;A$2:A$999))/ISTNV(VERGLEICH(A$2:A$999;H$1:H1;)); 1)); "")
I2=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$2:A$999)/ISTZAHL(SUCHEN(Tracker!$C$2;B$2:B$999))/ISTNV(VERGLEICH(B$2:B$999;I$1:I1;))/ISTZAHL(VERGLEICH(A$2:A$999;H$2:$H$19;)); 1)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: Formeln mit AGGREGAT() und INDEX() ...
Duinne

Hallo Werner,
ich glaube, das überfordert mich jetzt doch etwas. Irgendwas passt bei mir noch nicht. Ich habe versucht, deine Formeln und Datenüberprüfungen in meine Tabelle zu übernehmen:
https://www.herber.de/bbs/user/98998.xlsx
Aber so ganz schlüssig ist mir das noch nicht. Da dürfte auch nicht alles drin sein...
Kannst du hier nochmal schauen?
Danke und liebe Grüße
Duinne
@Daniel: Danke für deinen Alternativvorschlag! Freue mich, dass du mir ebenfalls helfen möchtest.

du musst nur noch Formel I2 kopieren ... und
der

Hallo Duinne,
... Formel I2 ziehend nach unten kopieren (soweit es für Dich noch "Sinn" macht; ich hatte vorgeschlagen bis I19 Du kannst natürlich auch mehr Daten erzeugen lassen) und die Dropdownzelle in "Tracker" dem gesamten Spaltenbereich "Lebensmittel" zuweisen.
Dann gib mal als Suchzeichen in C2 "il" ein und nutze eine Dropdownzelle. Anschließend gib zusätzlich in B2 als Suchzeichen "Kategorie" "ei" ein und treffe eine Auswahl dann lösche mal in C2 die Suchzeichen ...
Hab noch eine Formelverbesserung für den Bereichsnamen Auswahl:
=Kal_Tab!$I$2:INDEX(Kal_Tab!$I:$I;ZÄHLENWENN(Kal_Tab!$I:$I;"?*"))
Natürlich musst Du abschließend noch die restlichen Daten aus Deiner Tabelle "Kalorien" in die Tabelle "Kal_Tab" einordnen. Danach kannst Du Deine Tabelle löschen und "Kal_Tab" so benennen wie Du es willst.
Jetzt verständlicher?
Gruß Werner
.. , - ...

AW: du musst nur noch Formel I2 kopieren ... und
Duinne

Hallo Werner,
vielen dank, das ist verständlicher.
Aber ein Problem habe ich noch: in Zelle C5 habe ich die Formel
=WENN([@Lebensmittel]="";"";WENNFEHLER(INDEX(Kal_Tab!A:A;VERGLEICH(C5;Kal_Tab!B:B;)); "?"))
eingetragen. Nur steht da bei mir nichts. Was fehlt denn da noch? Also auf dem Tabellenblatt Tracker in der Spalte Kategorie passiert bei mir gar nichts.
Grüße
Duinne

AW: du musst nur noch Formel I2 kopieren ... und
Duinne

Hallo Werner,
hat sich erledigt!! Ich muss erst das Lebensmittel auswählen - jetzt hab ich es verstanden.
Ganz lieben Dank für deine tolle Unterstützung bei der Lösungsfindung!!
Damit kann ich nun gut arbeiten. Prima!
Herzliche Grüße
Duinne

dazu auch eine Bitte ...
der

Hallo Duinne,
... könntest Du die endgültig umstrukturierte Datei hier einstellen oder mir per Mail senden?
Meine Mailadresse: Dazu gib bzw. kopiere in eine freie Excelzelle folgende Formel ein:
=WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.").
Schreibe dazu in den Betreff: bezogen auf Herber thread Kalorienen.
Danke.
Gruß Werner
.. , - ...

AW: dazu auch eine Bitte ...
Duinne

Hallo Werner,
ich lade die Datei hier hoch, damit vielleicht jemand anderes auch noch etwas davon hat =)
https://www.herber.de/bbs/user/99031.xlsx
Die Auswahl der Kategorie habe ich nun doch noch mittels Datenüberprüfung erstellt.
Den Rest habe ich auf meine Daten und Vorstellungen angepasst.
Liebe Grüße
Duinne

danke ...
der

Hallo Duinne,
... kann ich/man vielleicht mal gebrauchen. Danke.
Gruß Werner
.. , - ...

Dropn-Down mit wenigen Buchstaben
Duinne

Hallo Leute,
ich möchte mir gerne eine Kalorientabelle basteln und bräuchte dabei ein bisschen Hilfe.
Für die Tabelle habe ich mir überlegt, eine Lebensmitteldatenbank anzulegen. Diese wird sich auf einem separaten Reiter befinden. Auf dem ersten Tabellenblatte möchte ich für jeden Tag alle Lebensmittel auflisten, die ich so verputzt habe.
Dazu möchte ich gerne in der dafür vorgesehen Zelle nur ein paar Buchstaben eingeben. Anhand dieser Buchtstaben möchte ich eine bereits gefilterte Auswahl erhalten. Also z.B. gebe ich "ap" und dabei erscheint eine Auswahlliste mit beispielsweise "Apfel", "Apfelsine" "Apfelsaft" etc. Die Buchstaben, die ich eingebe, müssen nicht zwangsläufig die ersten sein. Folgendes Beispiel: ich gebe "Alme" ein. In der Auswahlliste soll "Frischkäse, Kräuter - Almette" stehen.
Eventuell ist es sinnvoll, vorher noch Kategorien auszuwählen. Ich glaube, dass selbst eine gefilterte Auswahl recht lang werden könnte, weshalb man sich vorher etwas einschränkt und zum Beispiel nur Werte aus der Kategorie "Getreide" oder "Gemüse" wählen kann.
Gibt es eine Möglichkeit, so etwas ohne VBA zu lösen? Könnte mir hierbei jemand helfen?
Vielen Dank und liebe Grüße
Duinne

als Formellösung über Kategorieauswahl oder ...
der

Hallo Duinne,
... und dann über anschließende Direktauswahl möglich. Diese Lösungsart setzt voraus, dass je Kategorie eine überschaubare Menge an Lebensmittel gelistet sind.
Es gibt aber auch eine Lösungsmöglichkeit, die Deinen Vorstellung näher kommt. Dies allerdings nur, wenn Du die die Buchstaben (egal ob die bei Deinem Lebensmittel am Anfang stehen oder nicht) immer erst in eine separate Eingabezelle vornimmst. Erst danach kannst dann Deine Auswahl über die Dropdownzelle aus den dann eingeschränkten Listenangebot treffen. Wenn die dann angebotene Auswahlliste Dir noch zu groß ist, kannst Du deren Reduzierung nur dadurch vornehmen, in dem Du die Dropdownzelle zunächst verlässt und in der Eingabezelle weitere Buchstaben (in der richtigen Reihenfolge) ergänzt.
Entspräche so eine derartige Lösung unter den von Dir gesetzten Bedingungen Deinen Vorstellungen?
Gruß Werner
.. , - ...

AW: als Formellösung über Kategorieauswahl oder ...
Werner

Hi Namens-Vetter,
in kenne die Antwort auf Deine Frage von Duinne (noch) nicht ;)
Nur eines:
Dein Lösungsansatz würd mich seehr interessieren :))
Besten Gruss
Werner

AW: als Formellösung über Kategorieauswahl oder ...
Duinne

Hallo Werner,
ganz lieben Dank für deine Antwort!
Eine separate Zelle für die Eingabe wäre absolut in Ordnung.
Wäre das ungefähr so, wie in folgender Tabelle?
https://www.herber.de/bbs/user/98953.xlsx
Diese habe ich in der im Laufe des Tages gefunden - natürlich nicht von mir sondern von Andreas Thehos.
Leider ist mir nicht ganz klar, wie ich dort noch eine Dropdown-Liste davor schalten kann, in denen ich die Kategorien auswähle, um weitere Einschränkungen vorzunehmen.
Selbstverständlich kann die Lösung auch anders aussehen.
Vielen Dank für die Unterstützung!
Liebe Grüße
Duinne

dazu zunächst soviel ...
der

Hallo Duinne,
... im Prinzip hat es Andreas in seinem Beispiel schon so gelöst, wie ich es geplant hatte. Besonders angetan bin ich (natürlich ;-) ) davon, dass er eine AGGREGAT()-Formellösung zum Erstellen der Dropdownliste genutzt hat. Allerdings seine Bereichsnamensdefinition für die Dropdownzelle würde ich so nie vorschlagen.
Um Deine zusätzliche Bedingung korrekt einzubauen, wäre es hilfreich, dass Du mal einen relevanten Auszug aus Deiner Datenliste hier einstellst, damit wir nicht aneinander vorbei schreiben.
Ich schaue dann später nochmal danach.
Gruß Werner
.. , - ...

AW: dazu zunächst soviel ...
Duinne

Guten Morgen Werner,
schön, dass du mir hilfst!
Die Formel AGGREGAT() war mir zum Beispiel noch völlig unbekannt... Man lernt einfach nicht aus.
Also, ich habe jetzt mal die Tabelle hochgeladen. Auf dem ersten Reiter ist - erst einmal ganz einfach gehalten - die Tabelle zu sehen, in der der tägliche Kalorienverbrauch "getrackt" wird. Hier soll dann auch die dropdown-Auswahl zur Verfügung stehen.
Auf dem zweiten Reiter stehen die Lebensmittel.
https://www.herber.de/bbs/user/98960.xlsx
Möglicherweise muss hier noch umsortiert werden? Ich habe gelesen, dass bei abhängigen Dropdown-Listen ein bestimmtes Format notwendig ist, nämlich die Listen nebeneinander anzuordnen. Das kann ich dann gerne noch machen, sofern notwendig.
Grüße
Duinne

zu dem Listing Deiner Daten und zu AGGREGAT() ...
der

Hallo Duinne,
... eine Umsortierung ist auf jeden Fall sinnvoll. Wenn Du z.B. die Kategorien zugeordnet in einer separaten Spalte links Deiner Lebensmitteldaten schreibst, wäre es möglich sowohl diese in der einen Eingabezelle direkt gleich mit zu zu berücksichtigen, als als auch abhängige Dropdown-Listen anzubieten.
Ersteres dürfte für einen Nutzer wahrscheinlich der einfachere und vor allem schneller Weg sein und würde ich dann so umsetzen.
Kannst Du die Daten-Umsortierung schon mal auf den Weg bringen? Ich nehme mir dann anschließend am späteren Nachmittag dessen nochmal an.
Und noch folgendes: AGGREGAT() ist eine Funktion. In der Art und Weise wie ich sie auch erst seit März dieses Jahres in Formeln einsetze (quasi u.a. um damit echte MATRIXFormeln zu vermeiden) ist sie nicht nur Dir sondern wohl den meisten selbst fortgeschrittenen Excel-Nutzern noch völlig unbekannt. Jedenfalls so lange sie nicht mal über einen entsprechenden Forumsbeitrag von mir oder z.B. Andreas "gestolpert" sind ;-)
Gruß Werner
.. , - ...

AW: zu dem Listing Deiner Daten und zu AGGREGAT() ...
Duinne

Hallo Werner,
hier ist die neu sortierte Tabelle:
https://www.herber.de/bbs/user/98973.xlsx
Vielleicht können wir damit zu einem höheren Bekanntheitsgrad der AGGREGAT()-Funktion beitragen =)
Bin nur jedes Mal beeindruckt, was es alles gibt und wie wenig man dann doch kennt.
Liebe Grüße
Duinne

AW: Dropn-Down mit wenigen Buchstaben
Daniel

Hi
mal ein alternativer Lösungsvorschlag, passend zu deinem Excelniveau:
1. erstelle die Kalorientabelle (deine Datenbank) als durchgehende Liste mit sinnvollen Kategorien.
2. wenn du deine Tagestabelle erstellst dann gehe in deine Datenbank und suche dir die Sachen zusammen, die du am Tag gegessen hast, hierbei kannst du die Sortierung oder den Autofilter nutzen, um die Lebensmittel schneller zu finden.
3. Trage in einer weiteren Spalte hinter alle Lebensmittel, die du gegessen hast, ein "x" ein.
4. wenn du alle Lebensmittel so markiers hast, filtere in dieser Spalte nach den markierten Lebensmitteln und kopiere sie in deine Tracking-Tablle, wo du dann Menge und die weiteren Daten hinzu geben kannst.
5. lösche die "x" wieder.
ist zwar jetzt im Händling etwas aufwendiger, dafür kannst du aber die ganzen Such- und Filtermethoden von Excel nutzen, um den passenden Eintrag zu finden.
Gruß Daniel

Formeln mit AGGREGAT() und INDEX() ...
der

Hallo Duinne,
... ich meinte die Datentabelle etwas anders strukturiert. Dazu siehe unten stehend.
Mit nunmehr zwei Eingabezellen (es kann auch nur eine "gefüllt" werden kannst Du mit zwei Hilfsspaltenformel und zwei weiteren Formeln das gewünschte realisieren.
Ich hab es aus Zeitmangel nicht endgültig getestet, sollte aber im Wesentlichen funktionieren. Bin dann erst Morgen wieder online.
Hilfsspaltenformeln H max. 19 Zellen weit nach unten ziehend kopieren (mehr sind in Dropdownzelle sowie nicht mehr sinnvoll; dann eben Suchzeichen erweitern.
Dropdownzelle in Spalte [@Lebensmittel]
Tracker

 ABCDE
1 Suchzeichen KategorieSuchzeichen Lebensmittel  
2 einsaKalorienverbauch
3     
4DatumKategorieLebensmittelMengeKalorien berechnet
5 Alkoholfreie GetränkeKaffee (Instantkaffee, Pulverkaffee), schwarz  
6 Alkoholfreie GetränkeBier, Pils, alkoholfrei  
7 FleischgerichteBurgunderbraten mit Soße (ohne weitere Beilagen)  
8 Alkoholische GetränkeBier, dunkel (Dunkelbier, dunkles Bier)  
9 Alkoholfreie GetränkeBananensaft (Muttersaft)  
10     

Formeln der Tabelle
ZelleFormel
B5=WENN([@Lebensmittel]="";"";WENNFEHLER(INDEX(Kal_Tab!A:A;VERGLEICH(C5;Kal_Tab!B:B;)); "?"))

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C5Liste =Auswahl 
Namen in Formeln
ZelleNameBezieht sich auf
C5Auswahl=Kal_Tab!$I$2:INDEX(Kal_Tab!$I:$I;SUMME(N(LÄNGE(Kal_Tab!$I$1:$I$999)>1)))
Namen verstehen


Kal_Tab

 ABCDEFGHI
1KategorieLebensmittelkcal    Hilfsspalte KategorieHilfsspalte LM
2Alkoholfreie GetränkeAnanassaft51    Alkoholfreie GetränkeBananensaft (Muttersaft)
3Alkoholfreie GetränkeAnistee9    FleischgerichteBirnensaft
4Alkoholfreie GetränkeApfelfruchtsaftgetränk47      
5Alkoholfreie GetränkeApfelsaft, grüner Apfel48      
6Alkoholfreie GetränkeApfelsaft, roter Apfel46      
7Alkoholfreie GetränkeApfelschorle34      
8Alkoholfreie GetränkeAronia-Nektar, (Schwarze Eberesche, Apfelbeeren)38      
9Alkoholfreie GetränkeBananensaft (Muttersaft)50      
10Alkoholfreie GetränkeBier, Pils, alkoholfrei25      
11Alkoholfreie GetränkeBirnensaft39      
12Alkoholfreie GetränkeBitter Lemon (Bitterlimonade)44      
13Alkoholfreie GetränkeBrennnesseltee3      
14Alkoholfreie GetränkeBrombeersaft (Muttersaft)30      
15Alkoholfreie GetränkeGinger Ale (Ingwerlimonade)45      
16Alkoholfreie GetränkeIngwertee2      
17Alkoholfreie GetränkeKaffee (Instantkaffee, Pulverkaffee), schwarz4      
18Alkoholfreie GetränkeLindenblütentee3      
19Alkoholische GetränkeAbsinth (Kräuterschnaps, Wermutschnaps) 70 Vol.%472      
20Alkoholische GetränkeÄthanol, Ethanol, reiner Alkohol, Reinsprit 98 Vol.%604      
21Alkoholische GetränkeAlsterwasser, Radler45      
22Alkoholische GetränkeAltbier, Alt-Bier49      
23Alkoholische GetränkeAnislikör 40 Vol.%296      
24Alkoholische GetränkeApfelwein Apfelmost, Cidre, Cider, Sidra, Sidre45      
25Alkoholische GetränkeArrak231      
26Alkoholische GetränkeBärwurzschnaps, 40 Vol.%246      
27Alkoholische GetränkeBerliner Weiße mit Schuss (Waldmeister, Himbeer)51      
28Alkoholische GetränkeBier, dunkel (Dunkelbier, dunkles Bier)37      
29Alkoholische GetränkeBier, Export (Exportbier)41      
30FleischgerichteBackschinken (Krustenbraten, Schweinebraten aus der Keule)71      
31FleischgerichteBerner Würstchen, Berner Würstl, Berner Würstel349      
32FleischgerichteBurgunderbraten mit Soße (ohne weitere Beilagen)120      
33FleischgerichteCarpaccio (venezianisches rohes Rinderfilet mit Mayonnaise-Sauce und Brot)345      
34FleischgerichteCevapcici235      
35FleischgerichteChicken-Nuggets (Hähnchen Taler, Chicken Chips, Hähnchennuggets)190      
36FleischgerichteChili con carne89      
37FleischgerichteCordon bleu (aus Hähnchenfleisch)195      

Formeln der Tabelle
ZelleFormel
H2=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(A$2:A$999)/ISTZAHL(SUCHEN(Tracker!$B$2;A$2:A$999))/ISTNV(VERGLEICH(A$2:A$999;H$1:H1;)); 1)); "")
I2=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(A$2:A$999)/ISTZAHL(SUCHEN(Tracker!$C$2;B$2:B$999))/ISTNV(VERGLEICH(B$2:B$999;I$1:I1;))/ISTZAHL(VERGLEICH(A$2:A$999;H$2:$H$19;)); 1)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: Formeln mit AGGREGAT() und INDEX() ...
Duinne

Hallo Werner,
ich glaube, das überfordert mich jetzt doch etwas. Irgendwas passt bei mir noch nicht. Ich habe versucht, deine Formeln und Datenüberprüfungen in meine Tabelle zu übernehmen:
https://www.herber.de/bbs/user/98998.xlsx
Aber so ganz schlüssig ist mir das noch nicht. Da dürfte auch nicht alles drin sein...
Kannst du hier nochmal schauen?
Danke und liebe Grüße
Duinne
@Daniel: Danke für deinen Alternativvorschlag! Freue mich, dass du mir ebenfalls helfen möchtest.

du musst nur noch Formel I2 kopieren ... und
der

Hallo Duinne,
... Formel I2 ziehend nach unten kopieren (soweit es für Dich noch "Sinn" macht; ich hatte vorgeschlagen bis I19 Du kannst natürlich auch mehr Daten erzeugen lassen) und die Dropdownzelle in "Tracker" dem gesamten Spaltenbereich "Lebensmittel" zuweisen.
Dann gib mal als Suchzeichen in C2 "il" ein und nutze eine Dropdownzelle. Anschließend gib zusätzlich in B2 als Suchzeichen "Kategorie" "ei" ein und treffe eine Auswahl dann lösche mal in C2 die Suchzeichen ...
Hab noch eine Formelverbesserung für den Bereichsnamen Auswahl:
=Kal_Tab!$I$2:INDEX(Kal_Tab!$I:$I;ZÄHLENWENN(Kal_Tab!$I:$I;"?*"))
Natürlich musst Du abschließend noch die restlichen Daten aus Deiner Tabelle "Kalorien" in die Tabelle "Kal_Tab" einordnen. Danach kannst Du Deine Tabelle löschen und "Kal_Tab" so benennen wie Du es willst.
Jetzt verständlicher?
Gruß Werner
.. , - ...

AW: du musst nur noch Formel I2 kopieren ... und
Duinne

Hallo Werner,
vielen dank, das ist verständlicher.
Aber ein Problem habe ich noch: in Zelle C5 habe ich die Formel
=WENN([@Lebensmittel]="";"";WENNFEHLER(INDEX(Kal_Tab!A:A;VERGLEICH(C5;Kal_Tab!B:B;)); "?"))
eingetragen. Nur steht da bei mir nichts. Was fehlt denn da noch? Also auf dem Tabellenblatt Tracker in der Spalte Kategorie passiert bei mir gar nichts.
Grüße
Duinne

AW: du musst nur noch Formel I2 kopieren ... und
Duinne

Hallo Werner,
hat sich erledigt!! Ich muss erst das Lebensmittel auswählen - jetzt hab ich es verstanden.
Ganz lieben Dank für deine tolle Unterstützung bei der Lösungsfindung!!
Damit kann ich nun gut arbeiten. Prima!
Herzliche Grüße
Duinne

dazu auch eine Bitte ...
der

Hallo Duinne,
... könntest Du die endgültig umstrukturierte Datei hier einstellen oder mir per Mail senden?
Meine Mailadresse: Dazu gib bzw. kopiere in eine freie Excelzelle folgende Formel ein:
=WECHSELN("neopaCode";"Co";ZEICHEN(64)&"email.").
Schreibe dazu in den Betreff: bezogen auf Herber thread Kalorienen.
Danke.
Gruß Werner
.. , - ...

AW: dazu auch eine Bitte ...
Duinne

Hallo Werner,
ich lade die Datei hier hoch, damit vielleicht jemand anderes auch noch etwas davon hat =)
https://www.herber.de/bbs/user/99031.xlsx
Die Auswahl der Kategorie habe ich nun doch noch mittels Datenüberprüfung erstellt.
Den Rest habe ich auf meine Daten und Vorstellungen angepasst.
Liebe Grüße
Duinne

danke ...
der

Hallo Duinne,
... kann ich/man vielleicht mal gebrauchen. Danke.
Gruß Werner
.. , - ...

Bewerten Sie hier bitte das Excel-Portal