Excel: Funktion für Kategorisieren innerhalb eines SVerweises?
Entschuldigt die Fragestellung, mir fiel kein besserer Titel ein.
Ich habe eine Art "Essensplaner" entworfen, der per SVerweis funktioniert, falls der Kalorientabelle die Kalorienzahl des Produkts bekannt ist, gibt es mir in der dritten Zeile die Kalorienzahl abhängig vom Gewicht aus. Falls nicht, trage ich das Produkt nach.
Jetzt würde ich gerne noch ein Tortendiagramm oder Ähnliches erstellen, um eine Übersicht zur Ernährung zu erstellen. Dazu würde ich im Blatt "Kalorientabelle" jedem Lebensmittel eine Kategorie zuweisen (ähnlich wie unten skizziert) und dann für jeden Monat zählen lassen, wie viele Lebensmittel welcher Kategorie ich zu mir genommen habe. Ich habe über die Funktion "SummeWenn" nachgedacht, aber das scheint mir doch irgendwie unpraktikabel.
Die Tabelle umfasst 14 Blätter, eins für jeden Monat, eins für die Kalorientabelle und eines für Statistiken.
Ja, ich weiß, ich könnte mir auch irgendwelche Programme runterladen, aber ich habe Spaß am Umgang mit Excel und auch daran, etwas Neues zu lernen.
Vielleicht hat ja jemand eine hilfreiche Idee oder den Gedankenanstoß, den ich bräuchte :)
Vielen Dank im Voraus!
Noch mal Ergänzend ein Ausschnitt aus der bisherigen Tabelle
Oh und auch interessant bzw zum Verständnis beitragend ist sicher die Funktion, die (z.B.) in Zelle F6 (und allen anderen Kalorien-Zellen) steckt:
=WENN(ISTNV(SVERWEIS(F4;Kalorientabelle!$B$6:$C$213;2;0));0;F5*((SVERWEIS(F4;Kalorientabelle!$B$6:$C$213;2;0)/100)))
2 Antworten
Da Du bis jetzt noch keine Antwort hast, gehe ich davon aus, dass nicht nur ICH das nicht nachvollziehen kenn.
Dein zweiter Screenshot sieht für mich total chaotisch aus.
Ich kann da keine Struktur entdecken.
Vielleicht kannst Du die Frage noch etwas erläutern?!
Ich zeig Dir mal einen Ansatz:
Angenommen in Deiner Kalorientabelle stehen die einzelnen Lebensmittel in Spalte A ab A2. Angenommen die erste Tabelle in Deiner Frage heißt Tabelle1.
Dann schreibe in der Kalorientabelle in z.B. B2 diese Formel:
=SUMMEWENN(Tabelle1!$A$2:$X$200;A2;Tabelle1!$A$3:$X$201)
und kopiere das runter. Dann hast Du schon mal hinter jedem Lebensmittel die Menge stehen, die in Tabelle1 eingetragen wurde.
Wenn Du jetzt noch in Z.B. Spalte C Deine Kategorien einträgst, dann kannst Du in z.B. Spalte D nochmal eine Übersicht über die Kategorien erstellen, als eine Liste, welche es geben kann, und dann in E2 diese Formel:
=SUMMEWENN($C$2:$C$100;D2;$B$2:$B$100)
und runterkopieren und Du siehst die Menge je Kategorie.
Die Adressen kannst/ musst Du natürlich anpassen.
Beachte bei der ersten Formel aber, dass der zweite Bereich um 1 Zeile verschoben ist"
Würde ich so definitiv unterschreiben, wenn meine Tabelle nicht etwas komplexer wäre. Ich habe meine Frage deshalb mal um einen Screenshot ergänzt, der ein bisschen eher erahnen lässt, wie umfangreich das Ganze ist.
Das sollte meine Formel "nicht stören". Die zählt alles zusammen, was in der Zelle unter dem Signalwort steht und das scheint mir immer der Fall zu sein für die gesuchten Werte.
ODER?
Die Idee wäre also, erst einmal in der Kalorientabelle die Mengen der Blätter zu summieren und anschließend erst die Kategorisierung nach Produkt vorzunehmen?
Ja, klar. Du kannst ja nicht direkt die Kategorien zählen, da die ja nicht auftauchen. Also die Mengen der einzelnen Lebensmittel zusammenrechnen lassen und dann anhand der zugewiesenen Kategorien die Mengen für die Kategorien berechnen.
Hat doch auch dein Vorteil, dass Du zusätzlich noch direkt siehst, wieviel z.B. Milch Du verbraucht hast.
Klingt super! Die Formel wird dann wahrscheinlich ziemlich umfangreich (weil die Zellen so aufgebaut sind, wie sie aufgebaut sind, s.o.), aber sobald sie einmal steht ist es ja nur noch ein runterziehen. Wahrscheinlich werde ich das mit dem Vorschlag von IchMalWiederXY kombinieren - oder zumindest testen, ob es sich kombinieren lässt, danke ^^
Okay, für einen Tag funktioniert das ganze sehr gut
(=SUMMEWENN(Januar!$F$4:$O$4;B6;Januar!$F$5:$O$5))
sobald ich aber den ganzen Monat (geschweige denn das ganze Jahr! Ich habe erst einmal nur den Monat genommen!) in die Funktion einbeziehen möchte, wehrt sich Excel mit dem Argument, ich hätte zu viele Argumente eingegeben. Wie kann ich das umgehen? Schließlich möchte ich, dass er lediglich die Menge in die Rechnung mit einbezieht und die Kalorien außen vor lässt
Und ich wünschte, ich könnte meine Kommentare im Nachhinein löschen. Das ganze funktioniert nun tadellos mit folgender Funktion:
=SUMME(SUMMEWENN(Januar!$F$4:$O$94;B6;Januar!$F$5:$O$95);SUMMEWENN(Februar!$F$4:$O$94;B6;Februar!$F$5:$O$95);SUMMEWENN(März!$F$4:$O$94;B6;März!$F$5:$O$95);SUMMEWENN(April!$F$4:$O$94;B6;April!$F$5:$O$95);SUMMEWENN(Mai!$F$4:$O$94;B6;Mai!$F$5:$O$95);SUMMEWENN(Juni!$F$4:$O$94;B6;Juni!$F$5:$O$95);SUMMEWENN(Juli!$F$4:$O$94;B6;Juli!$F$5:$O$95);SUMMEWENN(August!$F$4:$O$94;B6;August!$F$5:$O$95);SUMMEWENN(September!$F$4:$O$94;B6;September!$F$5:$O$95);SUMMEWENN(Oktober!$F$4:$O$94;B6;Oktober!$F$5:$O$95);SUMMEWENN(November!$F$4:$O$94;B6;November!$F$5:$O$95);SUMMEWENN(Dezember!$F$4:$O$94;B6;Dezember!$F$5:$O$95))
Baue deine Tabelle um.
dann kannst du immer wieder Zeilen mit weiteren Dingen ergänzen UND Spalten mit weiteren Attributen. Einmal so aufgestellt lässt sich dies in eine PIVOT Tabelle überführen, was dann alle möglichen Auswertungen (und Graphen) erlaubt.
Das Problem daran wäre, dass ich bei einer solchen Tabelle nicht mit einem S-Verweis arbeiten könnte und das Eintragen bei derartigen Mengen äußerst umständlich werden würde
Jetzt muss ich vielleicht noch hinzufügen, dass ich keine PIVOT Tabellen kenne und dazu evtl. eine nähere Erläuterung bräuchte, falls mein Kommentar dann unbrauchbar wäre
Du selbst ordnest deine Daten an (Wie in meinem Bespiel)
Dann markierst du die Tabelle zusammen mit den Überschriften und wählst aus dem Menü "Einfügen" ganz links PIVOT Tabelle.
Ggf auf einem weiteren Blatt erzeugen lassen um Rohdaten von der Analyse zu trennen. Dann ein Tutorial auf Youtube dazu anschauen, was du nun alles machen kannst. In deinem Fall basics. Also keine Panik.
UND. eine Tabelle, die dies im Prinzip auch erledigt gibt es in den Vorlagen in Excel selbst. Diese könntest du mit deinen Sonderwünschen ergänzen.
Gehe mal auf "Datei->Neu" und dann in den Vorlagen unter "Diagramme"
Das Ernährungs und Trainingjournal auswählen.
Heute Abend habe ich nicht mehr so super viel Zeit, was ich bisher gesehen habe, klingt aber sehr attraktiv. Ich habe ja jetzt meine Frage noch einmal ergänzt und möchte dich als offenbar aktiven Nutzer von Pivot Tabellen fragen: Lässt sich das dann auch in den Alltag integrieren?
Die Tabelle die ich bisher nutze, ist ja ziemlich alltagstauglich und wird mehrmals am Tag gefüttert, damit ich auch eine Übersicht darüber habe, wie "gut" oder "schlecht" meine Tage verlaufen sind.
Also - ich setze mich mal morgen früh ausführlicher damit auseinander, aber vielleicht hast du ja auch schon einmal eine Beurteilung anhang des neuen Screenshots, den ich hinzugefügt habe?
Kann ich verstehen, der zweite Screenshot ist aus einer leeren Tabelle, die ich nur zu Versuchszwecken "angelegt" habe (wenn man das so nennen kann)
Am allerliebsten würde ich ja direkt die ganze Tabelle hochladen.
Man sieht im ersten Screenshot ja, dass ich da Tag für Tag brav abgewogenes Essen eintrage. Die Kalorien werden durch den SVerweis ermittelt, ich hoffe, bis hierhin ist alles klar.
Mein Gedanke war, in dem Arbeitsblatt, aus dem der SVerweis die Kalorienzahl nimmt, eine weitere Spalte einzufügen, in der ich hinter jedem Lebensmittel noch eine Kategorie eintrage (Getreide, Milchprodukt, Gemüse, ....).
Im Statistik-Blatt würde ich mir dann gerne ausgeben lassen, wie viele Lebensmittel der jeweiligen Kategorie ich zu mir genommen habe. Dazu müsste die Funktion natürlich auf die Monatsblätter zugreifen, bekäme z.B. den Wert "Milch", müsste sich merken, dass für "Milch" 250ml eingetragen wurden und dann auch noch im Arbeitsblatt für die Kalorien "nachsehen", zu welcher Lebensmittelkategorie "Milch" eigentlich gehört.
Ist das etwas klarer? :/