Berechnungen in Calc - Teil 2: Zellbezüge und Anwendungsbeispiele

From DHVLab

Verschiedene Arten von Zellbezügen

  • Relativer Zellbezug

Im vorangehenden Kapitel haben Sie bei der Berechnung der Besucherzahlen und der Anwendung der einzelnen Funktionen bereits eine von drei möglichen Varianten des Zellbezuges kennengelernt, nämlich den relativen Zellbezug. Beim relativen Zellbezug verändert sich der Bezug relativ zur Ausgangsposition, d.h. beim Kopieren von Formeln passt das Programm die Zelladresse automatisch an den neuen Zellbereich an.

  • Absoluter Zellbezug

Im Gegensatz dazu steht der absolute Zellbezug. Der absolute Zellbezug bezieht sich immer auf eine fest definierte Zelle. Der Zellbezug wird beim Kopieren nicht verändert. Feste Werte werden durch ein Dollarzeichen ($) gekennzeichnet, z.B.: $C$2: sowohl die Spalte C als auch die Zeile 2 sind absolut gesetzt und damit unveränderlich bei Berechnungen.

  • Gemischter Zellbezug

Eine dritte Variante sind gemischte Zellbezüge, sprich die Kombination aus relativen und absoluten Zellbezügen. Es wird unterschieden in absolute Zellbezüge, d.h. die Zelle ist absolut gesetzt, während die Spalte relativ bleibt (z.B. A$4: Die Spalte ist veränderbar, jedoch immer in Bezug auf die Zellen in Zeile 4). absolute Spaltenbezüge, d.h. die Spalte ist absolut gesetzt, während die Zelle relativ bleibt (z.B. $A4: Die Spalte A ist unveränderlich, variieren können dagegen die Zellen).

Beispielrechnungen mit Zellbezügen

Beispiel 'Absoluter Zellbezug': Mehrwertsteuer

Link auf Datei ergänzen
Für das Beispiel "Mehrwertsteuer" öffnen wir ein neues Arbeitsblatt. Übertragen Sie die Daten aus der verlinkten Datei und passen Sie das Format der Tabelle entsprechend an. Klicken Sie auf das Eurozeichen in der Bearbeitungsleiste, um den Daten Werte zuzuordnen.

  • Berechnung Gesamtpreis ohne Mehrwertsteuer:

Hierzu multiplizieren wir die Zelle "Anzahl" mit der Zelle "Stückpreis ohne MwSt." (hier: =B3*C3). Für die weiteren Berechnungen ziehen wir mit dem Cursor von D3 nach D6, damit die weiteren Gesamtpreise ohne MwSt. automatisch ausgefüllt werden (= relativer Zellbezug). Die Gesamtsumme ohne MwSt. ermitteln wir in der Zelle D7 über den bekannten Weg der Summenfunktion.

  • Berechnung Gesamtpreis mit Mehrwertsteuer

Es folgt nun die Berechnung mit Mehrwertsteuer (= absolute Zellbezug). Die Mehrwertsteuer befindet sich in unserem Fall in der Zelle I2 und beträgt 19% (Ein Klick auf das Prozentzeichen formatiert den Zahlenwert in Prozent). Da sich die Mehrwertsteuer in der Rechnung nicht verändern soll, wenden wir einen absoluten Zellbezug an. Um den Gesamtpreis mit Mehrwertsteuer zu ermitteln geben wir in der entsprechenden Zelle (E3) folgende Formel ein: =D3+D3*$I$2 ("D3 addiert um den Preis multipliziert mit der MwSt").
Auch dieses Ergebnis können Sie wieder für alle folgenden Fälle nach unten automatisch ausfüllen und unter der Berechnung die Summe (hier E7) angeben. Damit haben Sie den Gesamtpreis inkl. MwSt. des Einkaufs im Museumsshop ermittelt.

Beispiel 'Gemischter Zellbezug': Museumsshop

Link auf Datei ergänzen
Zunächst kopieren wir wiederum die Werte aus der verlinkten Datei in die Tabelle (Zelle A9). Wir möchten in dieser Beispielrechnung sehen, wie sich der Verkauf der Postkarten von Dürer, Cranach und Botticelli im Museumsshop im Laufe der Jahre prozentual verändert hat.
Nachdem wir die Werte übertragen haben, tragen wir mit einer Zeile Abstand darunter nochmals die Jahreszahlen (2012-2015) in Spalte A ein. Beginnen wir mit der Prozentberechnung in der Zelle neben 2012 (B15): Um den Wert für Dürer im Jahr 2012 zu erhalten, müssen wir die Anzahl der verkauften Dürerpostkarten durch die Summe aus allen verkauften Karten dividieren: =B10/($B10+$C10+$D10)[A 1]
Der relative Spaltenbezug gibt in diesem Beispiel an, dass die Werte immer aus derselben Spalte stammen, die Zelle jedoch relativ ist. Dadurch ist es möglich, das Ergebnis durch Aufziehen auf die anderen Zellen für das Jahr 2012 zu übertragen und anschließend die Formel auch auf die anderen Jahre ebenfalls durch Aufziehen zu übertragen[A 2]. Das (exemplarische) Ergebnis lautet: Die Dürerpostkarten behalten über die Jahre hinweg einen relativ konstanten Marktanteil von ca. 35%.

Fernbezüge

An dieser Stelle soll auch auf das Thema Fernbezüge eingegangen werden. Es kann selbstverständlich auch auf andere Tabellenblätter oder gar andere Excel/Calc-Dateien Bezug genommen werden und z.B. innerhalb einer Funktion eine Zelle aus einer anderen Arbeitsmappe oder Datei verwendet werden. Veranschaulichen wir dies anhand des Beispiels zur Mehrwertsteuerberechnung:
Hierzu findet sich eine separate Calc-Datei mit dem Namen "Mehrwertsteuer" Link auf Datei ergänzen.

  1. Öffnen Sie die Datei.
  2. Gehen Sie nun zurück in die Zelle, in der wir zuvor den Gesamtpreis mit MwSt. berechnet hatten (E3).
  3. Löschen Sie den bisherigen absoluten Zellbezug auf die MwSt.
  4. Gehen Sie in die Datei "Mehrwertsteuer" und klicken Sie dort das Feld mit dem Prozentwert (A5 = 18%) an. Die farbige Markierung zeigt Ihnen, dass der Wert in die Formel eingebunden wird. Gehen Sie zurück in die Ausgangsdatei und bestätigen Sie mit Enter. Wie Sie sehen wird der Dateipfad [#$Tabelle1.$A$5] in der Eingabezeile angezeigt; der Wert hat sich durch die niedrigere Mehrwertsteuer entsprechend verändert.

Vertiefende Rechenaufgaben

Top-Monate der Museen

Kehren wir zurück zu Tabelle 1 (Besucherzahlen).
Hier hatten wir zuvor bei der Alten Pinakothek den Monat ausgegeben, in dem die meisten Besucher gezählt wurden: =INDEX(A5:A16;VERGLEICH(MAX(D5:D16);D5:D16;0))
Wir möchten nun auch den jeweiligen Top-Monat für die beiden nachfolgenden Museen ermitteln. Da wir soeben die Zellbezüge kennengelernt haben, merken wir, dass die genannte Formel ohne einen gemischten Zellbezug bei den Monatsnamen nur bedingt Sinn macht. Ergänzen Sie daher A5 und A16 jeweils um ein Dollarzeichen, um den Spaltenbezug für die Monatsnamen absolut zu setzen: $A5:$A16
Die Formel =INDEX($A5:$A16;VERGLEICH(MAX(D5:D16);D5:D16;0)) kann für die beiden anderen Museen entsprechend aufgezogen werden. Es ergeben sich nun sinnvolle Werte ("Februar" und "März").

Staatlich vs. privat: Besucherzahlen

Berechnung für staatliche und nicht-staatliche Museen – Besucherzahlen in Prozent Um uns noch ein wenig mit den erlernten Formeln und Berechnungen, die Calc bietet, vertraut zu machen, werden wir noch eine weitere Prozentrechnung durchführen. Ziel ist eine vergleichende Berechnung der prozentualen Besucherzahlen staatlicher und nicht-staatlicher Museen.
Nehmen wir an, der Kunstpalast Düsseldorf und das Städel Museum sind privat, die restlichen drei Museen staatlich. Nun möchten wir sehen, welche Trägerschaft mehr Besucherzahlen aufweist. Hierzu legen wir je eine weitere Spalte für "Mittelwert staatlich" (G1) und "Mittelwert privat" (F1) an.
Berechnen wir mit der uns bekannten Mittelwertfunktion zunächst die beiden Mittelwerte:

  • staatlich: =MITTELWERT(B17;D17;E17) → 258.358 (ohne Nachkommastellen)
  • privat: =MITTELWERT(C17;F17) → 225.848 (ohne Nachkommastellen)

Anschließend berechnen wir die entsprechenden Prozentwerte in der Zelle darunter:
=G17/(G17+H17) bzw. H17/(G17+H17)
Ergebnis: Die staatlichen Museen liegen prozentual gesehen bei den Besucherzahlen leicht vor den privat getragenen Einrichtungen (53,36% zu 46,64%).



Zurück zu Berechnungen in Calc - Teil 1: Von Formeln und Funktionen ⇐⇒ Weiter zu Fehlermeldungen in Calc

Anmerkungen

  1. ↑ Damit der Wert als Prozentangabe ausgegeben wird, klicken Sie auf das entsprechende Symbol (und können die Anzahl der Nachkommastellen nach Belieben einstellen).
  2. ↑ Hinweis zur Kontrolle: Die Quersumme muss immer 100% betragen.
Retrieved from "http://dhvlab.gwi.uni-muenchen.de/wiki/index.php?title=Berechnungen_in_Calc_-_Teil_2:_Zellbezüge_und_Anwendungsbeispiele&oldid=3823"