Tag Archives: sverweis

#WENN im #Beispiel für die #Planung

Excel, Uncategorized

Weiter geht’s mit der #Excel-#Arbeitsmappe für die #Planung: Heute gibt’s zwei Formeln, beide basierend auf der Funktion WENN(Bedingung;dann-Ausdruck;sonst-Ausdruck). Diese Funktion beinhaltet zwei Berechnungsvorschriften, und entscheidet dann, wenn sie aufgerufen wird, aufgrund der Bedingung, welche der beiden Berechnungen durchgeführt werden soll. Das brauchen Sie in Situationen, in denen Sie unterschiedliche Berechnungen durchführen wollen, und entweder die Bedingungsparameter selbst immer wieder anders eingeben, oder eine Formel kreieren wollen, die zwei Ausgangssituationen abdeckt und dann mit AutoAusfüllen in mehreren Zeilen funktionieren soll. Liefert die Bedingung das Ergebnis WAHR,…

Read More »

#Maximum aus einem #Bereich mit #VBA ermitteln

Excel, Uncategorized, VBA

Heut gibt’s wieder einmal VBA – die lustige Programmiersprache, mit der Sie in Office nahezu alles automatisieren können. Die aktuelle Anforderung: Welches Datum ist in einem bestimmten Bereich das größte? In Excel verwenden Sie für die Beantwortung so einer Frage die Funktion MAX. Schreiben Sie aber MAX in VBA, so werden Sie nicht verstanden. Das muss also anders gehen … und zwar so: Public Sub Datum_Max() Dim DatBis As Date Set dWs = Worksheets(„Datenbasis“) DatBis = Application.WorksheetFunction.Max(dWs.Range(„Datum“)) … Set dWs = Nothing End Sub Zur…

Read More »

Excel, Uncategorized

#nv #nichtvorhanden #Excel #SVERWEIS – #ISTNV

Bild

Sie verwenden den SVERWEIS? Dann kennen Sie sicher auch meine Freundin, die Fehlermeldung #NV – nicht vorhanden sagt sie mir, wenn ich mit dem SVERWEIS etwas suche, aber nicht finde. Ich mag sie gern, denn zumeist ist sie ein Hinweis darauf, dass ich ein Datenproblem habe – etwa, dass ich für einen Kunden arbeite, für den es gar keine Stammdaten gibt (und dem ich daher auch nichts verrechnen kann 🙁 ). Manchmal jedoch stört sie mich, weil es mir eben egal ist, und wenn ich nichts finde, dann soll eben nichts da stehen … Mit einer Informations-Funktion kriege ich das in den Griff:

ISTNV(Ausdruck) liefert mir WAHR als Ergebnis, wenn der Ausdruck als Ergebnis die Fehlermeldung #NV zurückgibt – der Ausdruck wird also sinnvollerweise ein Verweis sein. Liefert er mir FALSCH zurück, so ist der Verweis gut gegangen, und ich kann mit einem erfreulichen Ergebnis rechnen.

Ein etwas konkreteres Beispiel: Ich ermittle mit ISTNV(SVERWEIS([@Menü];Speiseplan[#Alle];2;FALSCH)), ob das Nachschauen mit dem Wert in der Tabellenspalte Menü in der Tabelle Speiseplan gelingt. Wird das Menü gefunden, so ist das Ergebnis FALSCH (weil es keinen Fehler bringt); im Fehlerfall bekomme ich WAHR zur Antwort. (Wenn sich Ihr Gehirn soeben verknotet hat, dann lesen Sie den Absatz noch einmal.)

Das Häppchen bette ich jetzt als Bedingung in ein WENN ein:
WENN(ISTNV(SVERWEIS([@Menü];Speiseplan[#Alle];2;FALSCH));„“;SVERWEIS([@Menü];Speiseplan[#Alle];2;FALSCH)). Also: Wenn das Nachschauen zu einem Fehler führt, dann gebe ich einen Leerstring aus, sonst das Ergebnis des nun mit Sicherheit erfreulich funktionierenden SVERWEIS.

Hui.

Das Ist übrigens das letzte Häppchen zur Camp-Küchenplanung! Es sei denn, Sie haben noch eine Frage, dann mach ich gerne weiter 🙂

Zum Abschluss gibt es hier noch die Datei zum Herunterladen – achten Sie bitte mehr auf die Excel-Formeln als auf die Rezepte, ich habe manches zu Demozwecken ein bisschen verunstaltet 😉

Ta-daaaaa: Kitchenstaff_Mainlist

#Entspannung in der #Küche – einfache #Funktionen in #Excel

Excel, Uncategorized

Das schon lange laufende Küchenbeispiel ist noch nicht zu Ende – aber bald wird angerichtet. Damit’s auch wirklich bekömmlich wird, müssen wir noch ein paar Informationen in der Liste ergänzen: Die Spalte fh fischt sich aus der Produkte-Liste die Information heraus, ob das Produkt eins ist, bei dem man auf Frische achten muss, oder ob es haltbar ist (ich unterscheide nur diese beiden Fälle). Die Formel dazu ist =SVERWEIS([@was];Produkte[#Alle];7;FALSCH) – und wenn Sie die vergangenen Tage durchblättern, erklärt sie sich sozusagen von selbst, wenn Sie nicht blättern wollen, erklär ich sie Ihnen: SVERWEIS schaut…

Read More »

#Excel #Funktionen – als bunte #Minestrone angerichtet

Excel, Uncategorized

Küche, Küche, Küche … das Koch- und Einkaufsplanungs-Beispiel verfolgt uns nun schon lange – und immer noch basteln wir an der Basisliste für allerhand Auswertungen. Noch fehlen Spalten: Preis: Um den herauszufinden (um vorab einzuschätzen, wie viel wir z.B. pro Person und Tag ausgeben werden) müssen wir die gestern ermittelte Zahl mit dem Preis pro Einheit multiplizieren. Der steht in der Produkte-Tabelle in der sechsten Spalte, jeweils zu dem passend, was hier in der Mengentabelle in der Spalte was steht. Etwas Passendes aus einer anderen…

Read More »

Excel, Uncategorized

#Verweis auf #Tabelle in #Excel – #kochfest

Bild

Sie befürchteten, mit der Kocherei der letzten Tage ist schon Schluss? Aber nein! Jetzt geht’s erst richtig los: nun geht’s ans Mengen bestimmen. Eine weitere neue Tabelle entsteht, in der für jedes Menü die Mengen der verwendeten Produkte bestimmt werden; wir gehen sie Schritt für Schritt durch:

Menü: hier kommt die Bezeichnung der Speise hin, so wie sie (wichtig!) in der Speiseplan-Tabelle geschrieben ist. Pro Menü gibt es mehrere Zeilen.

was: das zu verwendende Produkt. Muss genau so geschrieben werden wie in der Produkte-Tabelle (wichtig!).

Einheit: wird gar nicht geschrieben, sondern aus der Produkte-Tabelle geholt, und zwar mit der Formel =SVERWEIS([@was];Produkte[#Alle];5;FALSCH). SVERWEIS schlägt nach, was in der fünften Spalte der Produkte-Tabelle steht, und zwar in der Zeile, in der in der ersten Spalte das steht, was hier in der Spalte mit der Überschrift was zu finden ist. FALSCH bedeutet nur, dass genau gesucht werden soll (also nicht die Einheit aus der Semmel-Zeile geliefert wird, wenn nach Semmelbröckerl gesucht wird) – und wenn ein Produkt nicht gefunden wird, dann sehen Sie den Fehler #NV und müssen das Produkt eben noch in der Produkte-Tabelle ergänzen.

5Pers: hier schreibe ich die Menge hinein, die ich verwende, wenn ich für fünf Personen koche (das ist meine Referenzmenge – mein Mann und ich und drei meiner Kinder. Der vierte ist schon erwachsen und sehr sportlich und isst für zwei und ist daher als Referenz ungeeignet.)

AFV: A = Alle, F = Fleischesser, V=Vegetarier kennzeichnet, wer aller diese Zutat essen wird. (Für die Vegetarier gibt’s nämlich zumeist eine Variante der Fleischesser-Speise, wenn die Speise nicht sowieso vegetarisch ist.)

Kategorie: Wieder etwas, was wir uns mit einer Formel ausfüllen lassen: =SVERWEIS([@was];KatListe;2;FALSCH). Dieses Mal suchen wir mit der Funktion SVERWEIS unser was in einem Bereich, den ich KatListe genannt habe*, und lassen uns den Inhalt der zweiten Spalte zurückliefern.

Für heute ist das genug, schaut schon recht schön aus, meine Mengentabelle. Morgen kommen noch ein paar neue Formeln dran, mit neuen Funktionen drin. Tipp Excel SVERWEIS #NV

*Einem Bereich einen Namen geben geht so: einfach den Bereich markieren, und dann im Namenfeld den gewünschten Namen eingeben. Dieser muss mit einem Buchtstaben beginnen, darf Buchstaben, Ziffern und den Unterstrich _ enthalten und kein reserviertes Wort sein (im Zweifelsfall hilft der Unterstrich da enorm, denn der kommt in keinem reservierten Wort vor 🙂 )

Tipp Excel benannter Bereich

Excel, Uncategorized, VBA

#Excel #VBA #Funktion verkettet Verweisergebnisse

Bild

Tipp VBA SVerweisM

Mit VBA können Sie eigene Funktionen kreieren.

Die hier hab ich SVerweisM genannt. Sie sucht wie der SVERWEIS nach dem Vorkommen einen Schlüsselbegriffs, liefert aber eine Textwurst aus allen möglichen Treffern (Trennzeichen ist das vierte Argument).

War übrigens eine Auftragsarbeit 🙂

Public Function SVerweisM(was As Variant, wo As Range, Ergebnisspalte As Long, Trennzeichen As Variant) As Variant
Dim Erg As Variant
Dim Zeile As Long
Erg = „“

For Zeile = 1 To wo.Rows.Count
If wo.Cells(Zeile, 1).Value = was Then
Erg = Erg & IIf(Erg <> „“, Trennzeichen, „“) & wo.Cells(Zeile, Ergebnisspalte)
End If
Next Zeile

SVerweisM = Erg

End Function

Excel, Uncategorized

#SVERWEIS geht nur, wenn die Schlüsselspalte links ist

Bild

Tipp Excel Index VergleichStimmt. Und wie kann man trotzdem was nachschlagen? Weil: Die Schlüsselspalte ist nun mal nicht immer da, wo man sie gern haben möchte.
Eine Lösung bringt die Kombination der beiden Funktionen INDEX und VERGLEICH.
VERGLEICH nämlich nennt mir die Fundstelle eines Wertes in einer Matrix. Also in meinem Beispiel die Zeile, in der der gesuchte Wert innerhalb einer Schlüsselspalte steht.
Und mit INDEX kann ich dann aus einem anderen Bereich den ebensovielten Wert zurückgeben.
Im Beispiel im Bild wird sogar gleich zwei Mal verglichen, um in einer beliebigen Spalte suchen zu lassen. Damit ist die INDEX-VERGLEICH-Kombi mindestens so gut wie der SVERWEIS 🙂 Das sehen Sie sicher genau so 🙂