Tag Archives: zellbezug

#Formeln in #Tabellen – #strukturierter #Verweis

Happy Nikolo – er bringt uns eine #Formel, eine #Funktion und ein Feature der #Tabelle: In meinem Planungstool möchte ich den Monat, in dem eine Aufgabe hereingeschneit ist, ermitteln. Also gebe ich eine Formel ein: =MONAT(Datumswert), denn die Funktion MONAT ermittelt aus einem Datum den Monat als Zahl (also z.B. 12 aus dem 6.12.2016). Für das Argument Datumswert verweise ich auf die Zelle mit dem Erfassungsdatum. Hoho – da steht jetzt gar nicht A4, sondern [@Datum] – das ist fein, denn jetzt kann ich meine Formel…

Read More »

Verweis auf ein variables #Tabellenblatt in #Excel – #flexibilität

NOCH ein Anwendungsbeispiel für die schöne Funktion INDIREKT, die ich Ihnen gestern gezeigt habe: sagen wir einmal, Sie haben in Ihrer Arbeitsmappe für jedes Jahr ein Arbeitsblatt angelegt und auch so benannt, wie das Jahr heißt – also 2009, 2010, … 2015. Alle Summen stehen in jedem dieser Blätter in der selben Zelle (sagen wir einmal, C1 ist interessant). Nun wollen Sie die Daten Ergebnisse aller Jahre auf einem neuen Blatt darstellen, aber nicht haufenweise Formeln tippen. Da ist INDIREKT eine gute Wahl! Mit INDIREKT(A2…

Read More »

#Wochenende in #Excel #einfärben

Gestern haben wir Wochentage erzeugt – und jetzt wär’s noch schön, wenn die Wochenenden jeweils andersfärbig wären, nicht wahr?

Dazu benötigen wir die Funktion WOCHENTAG und die bedingte Formatierung. Zuerst der WOCHENTAG: WOCHENTAG(Datum;2) liefert eine Zahl, die den Wochentag des übergebenen Datums kennzeichnet, und zwar 1 für den Montag, 2 für den Dienstag, … Verantwortlich für die Art der Nummerierung ist der 2er in meinem Beispiel; es gibt auch andere Nummerierungen, die für uns allerdings etwas weniger nützlich sind.

XTipp WOCHENTAG 1

Allerdings verwenden wir die Formel nicht in einer Zelle, sondern für eine bedingte Formatierung. Also: Markieren Sie die Zellen, die Sie einfärben wollen (im Beispiel B1:B28), und öffnen Sie mit START > Formatvorlagen > Bedingte Formatierung > Neue Regel den Dialog Neue Formatierungsregel. Hier klicken Sie bei Regeltyp auswählen auf Formel zur Ermittlung der zu formatierenden Zellen verwenden. Das bedeutet, Sie müssen unter Werte formatieren, für die diese Formel wahr ist, eine Formel eingeben, die entweder WAHR oder FALSCH als Ergebnis liefert, und wenn sie für eine der markierten Zellen WAHR liefert, dann wird die entsprechende Zelle so umformatiert, wie Sie’s unter Formatieren… angeben.

Unsere Formel lautet nun =WOCHENTAG($A1;2)>5. Also: wenn der Wochentag von dem, was in A1 steht, größer ist als 5, dann haben wir Wochenende (Samstag ist 6, Sonntag ist 7), und dann wird eingefärbt. $A1 ist die Zelle, die für die erste Zelle meines markierten Bereichs angeschaut werden soll, und weil es $A1 und nicht $A$1 ist, „rutscht“ der Zeilenbezug im Bereich weiter. Für B2 wird also $A2 angeschaut, …

XTipp WOCHENTAG 2

Vergessen Sie nicht, auf Formatieren… zu drücken, und ein Format zu definieren. Da ist ganz schön viel möglich.

OK liefert Ihnen dann eine Ansicht, die Ihnen sicher Freude macht:

XTipp WOCHENTAG 3

#letzte #Sortierung in #Excel erneut anwenden

Die in den letzten Tagen begonnene Kochplanung mit Excel geht weiter – heute legen wir eine Liste aller Produkte an, die eventuell benötigt werden. Wir brauchen:

  • was – also eine Bezeichnung
  • Die Gruppe (Sie werden sehen, das erleichtert uns dann später das Basteln einer brauchbaren Einkaufsliste)
  • Den Preis (heißt in meiner Liste „Collipreis“ – damit ist der Preis gemeint, der später auf der Rechnung stehen wird)
  • Die Anzahl der Einheiten, auf die sich dieser Preis bezieht (falls Sie nämlich im Großmarkt einkaufen, und das empfiehlt sich für ein Jugendcamp, weil’s billiger wird, dann können Sie manchmal nur Multipacks kaufen. Der Collipreis ist also der Preis für 30 Knackwürste, und die Anzahl der Einheiten ist dann 30.)
  • Die Einheit (Stk., g, kg, … so, wie Sie’s dann später im Rezept verwenden wollen, und wie es die Collipreisgeschichte vorgibt)
  • Der Preis pro Einheit wird nun berechnet. In meinem Fall mit der Formel =[@Collipreis]/[@Einheiten]*1,1 (und ich darf diese Formel mit diesen lustigen [@]-Bezügen schreiben, weil ich meine Liste schon wieder als Tabelle formatiert habe, damit ich mehr Automatik bekomme und nicht alles selbst machen muss. Ach ja, und das *1,1 ist die Umsatzsteuer – in Österreich müssen auf Lebensmittel 10% dazugerechnet werden, und das mach ich mathematisch mit *1,1. Sie können gerne auch *110% schreiben, das ist dasselbe.)
  • Ich schreib noch dazu, ob das ein eher haltbares Produkt ist, oder ob ich mit der Frische aufpassen muss. Auch das brauchen wir später für die Einkaufsliste.
  • Zu guter Letzt: gibt’s bereits ein paar Einheiten im Lager? Wenn Sie sowas öfter machen, dann ist vielleicht vom letzten Mal etwas übrig geblieben.

Fertig ist die Produktliste. Ich sortier sie jetzt mit START > Bearbeiten > Sortieren > Benutzerdefiniertes Sortieren nach zwei Ebenen: der Gruppe und dann nach dem was.

Tipp Excel Sortieren Ebenen

Ach, da fällt mir noch etwas ein, das ich unten dazufügen muss. Nun kann ich die Sortierung noch einmal machen 🙁

Zum Glück gibt’s dafür eine Abkürzung: START > Bearbeiten > Sortieren und Filtern > Erneut anwenden, oder auch DATEN > Sortieren und Filtern > Erneut anwenden, oder auch Strg+Alt+L.

#kochen und andere #Schweinereien mit #Excel – Tagesdifferenz

Bin wieder da (gutes Neues Jahr!) – und weil das Kochen für ein internationales Jugendcamp einiges an Vorbereitung mit Excel erforderlich gemacht hat, gibt’s jetzt ein paar Tage lang Lösungshäppchen, die auch jenseits der Küchenplanung nützlich sein können. Wir beginnen mit einer einfachen Aufgabe: die Planung. Dazu schreiben wir eine fortlaufende Liste der zu kochenden Rezepte, und zwar mit Rezept und Datum. Und weil es fad ist, auf einem Camp nach Datum zu kochen, weil man nämlich schon bald keine Ahnung mehr vom Datum hat, sondern…

Read More »

Daten in #Excel #zählen, aber nicht alle – #zählenwenn #flexibel

Gestern haben wir Daten gezählt, die einem bestimmten Kriterium genau entsprechen – Sie erinnern sich? Auf die Eingabe von meat hin, wurden alle Einträge gezählt, die eben meat entsprachen. Heute zählen wir Daten, die einem Wert in einer Zelle entsprechen. Das geht ganz leicht. Statt beim Kriterium einen festen Wert (wie meat) einzugeben, darf’s auch ein Zellbezug sein – voila. Mit ZÄHLENWENN($A$2:$A$20;N3) zählen Sie alle Zellen im Bereich A2:A20, in dem das steht, was in N3 steht. Der Zweck dahinter ist meist der, dass wir (wie…

Read More »

#Dollarzeichen im #Zellbezug in #Excel – mal da, mal da #verwirrend

Sie haben gestern mitgelesen, oder? Als es um das Dollarzeichen im Zellbezug ging. Ich hoffe, der Unterschied vom relativen (ohne Dollarzeichen) zum absoluten (mit Dollarzeichen) Bezug war klar.

Zum Hinzufügen der Dollarzeichen hab ich Ihnen die Taste F4 empfohlen (leicht zu merken: denn das Dollarzeichen befindet sich auf der Tastatur da, wo auch die 4 steht). Wenn Sie diese mehrmals betätigen, wechselt allerdings der Zellbezug von A1 auf $A$1, dann zu A$1 und $A1, und erst dann wieder zurück zu A1. Was hat es nun mit diesen A$1 bzw. $A1, den gemischten Bezügen, auf sich?

Mit diesen stecken Sie einen Bezug fest, und zwar den, vor dem das Dollarzeichen steht. Im Falle von A$1 steckt also die Zeile 1 fest und verändert sich nicht, auch wenn Sie die Formel nach oben oder unten kopieren. Die Spalte A hingegen ist frei beweglich, beim Weiterziehen der Formel nach rechts wird aus dem Bezug auf A also B, C, D, und so weiter.
Im Falle von $A1 steckt also die Spalte A fest und verändert sich nicht, auch wenn Sie die Formel nach rechts oder links kopieren. Die Zeile 1 hingegen ist frei beweglich, beim Weiterziehen der Formel nach unten wird aus dem Bezug auf 1 also 2, 3, 4, und so weiter.

Wichtig wird das, wenn Sie ein ganzes Rechteck aus Zellen (eine Matrix) mit einer Formel füllen wollen, die die Werte jeweils aus der Zeile darüber und der Spalte links ziehen soll. Gleichzeitig wollen Sie nur eine einzige Formel schreiben, über den Rest soll Excel nachdenken:

Tipp Excel Zellbezug gemischt

Wo muss jetzt das Dollarzeichen hin? Im Zweifelsfall gehen Sie so vor: Sie basteln die Formel für die erste Zelle, und überlegen: Die „Angaben“ sind in diesem Beispiel in Zeile 1 und Spalte A – also muss dort auch das Dollarzeichen hin. Eines vor den 1er, eines vor das A. Alles andere bleibt dollarfrei.

Die so vorbereitete Formel lässt sich nun in das ganze Rechteck kopieren und – stimmt!

#Zellbezug in #Excel – mit oder ohne #Dollarzeichen?

Weisheit des Tages: jeder Bezug auf eine Zelle in Excel kann absolut oder relativ sein.

Immer diese Fachausdrücke – wie wär’s mit Klartext?

Aaaaalso: Wenn Sie einen Verweis auf eine Zelle setzen, um sich in einer Formel auf „das, was gerade in B1 steht“ zu beziehen, dann klicken Sie auf die Zelle B1, und Excel schreibt B1. (Sie können auch selbst B1 schreiben; Excel ist es ganz egal, wer schreibt.)

Tipp Excel Zellbezug relativ

Wenn Sie die so zurechtgeklickte Formel nun in weitere Zellen kopieren, dann wird aus B1 plötzlich B2, dann B3, B4, … Der Zellbezug war also relativ zur gerade aktiven Zelle und „wandert mit“.

Tipp Excel Zellbezug relativ2

Fein ist das. Nur – manchmal wollen Sie von einem Zellbezug gar nicht, dass er verrutscht. In dem gerade verwendeten Beispiel wollen Sie möglicherweise, dass jede der Zahlen in der Spalte A mit dem Wert 10 aus der Zelle B1 multipliziert wird, nicht mit dem Wert aus der gerade darüberliegenden Zelle. Es wäre also schön, wenn der Bezug auf B1 absolut, also unveränderlich wäre.

Das erreichen Sie durch das Ergänzen von Dollarzeichen im Bezug – eins vor dem Zeilen-, eins vor dem Spaltenbezug, also $B$1. Das können Sie selbst ergänzen, oder Sie verwenden die Taste F4, wenn Sie den Bezug gerade bearbeiten, also mit dem Cursor drauf stehen. Und nun können Sie Ihre Formel ziehen, wohin Sie wollen: der Bezug auf B1 wird sich immer auf B1 beziehen.

Tipp Excel Zellbezug absolut Tipp Excel Zellbezug absolut2

Die Dollarzeichen sind sozusagen die Pinnadeln, mit denen Sie den Bezug „feststecken“.

#Excel #Tabelle #Formeln #Zellbezug mit @

 

Sie waren gestern hier? Da hab ich ein bissl was über Tabellen erzählt. Ich mach heut noch weiter, und erstelle auf herkömmlichem Weg (Tippen und Klicken) eine Formel:

Tipp Excel Tabelle 5

Schauen Sie sich diese Zellbezüge an! Erwarten würde man =D2/E2, aber da steht =[@turnover]/[@units]. Weil ja in einer Tabelle alle so fest zusammenhalten, geht das. [@turnover] bedeutet nämlich nichts anderes als „das, was in derselben Zeile in der Spalte mit der Überschrift turnover steht“.

Ist nachvollziehbarer, lesbarer und übersichtlicher (vor allem, wenn es um Tabellen mit vielen, viiiiiielen Spalten geht).

Ich MAG Tabellen 🙂

#PowerPoint #Hyperlink zu #Excel

Wenn Sie in einer PowerPoint-Präsentation einen Hyperlink zu einer Excel-Arbeitsmappe einbauen, wird diese damit geöffnet – allerdings so, wie sie auch „normalerweise“ geöffnet wird: da, wo Sie (oder jemand anderer) sie verlassen haben. Hm. Nicht immer das Gelbe vom Ei … Wollen Sie einen bestimmten Punkt herzeigen, dann muss der Hyperlink ein bisschen ausführlicher sein: <Pfad und Dateiname>#<Name des Tabellenblatts>!<Zelladresse in dem Blatt> Also beispielsweise C:\Meine Datensammlung\Beispiel.xlsx#’Lange Liste‘!A1. (Die Hochkommas sind wegen des Leerzeichens in Lange Liste da.) Um zu einem benannten Bereich zu springen, geben Sie statt…

Read More »