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

=== 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.: <code>$C$2</code>: 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. <code>A$4</code>: 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. <code>$A4</code>: Die Spalte A ist unveränderlich, variieren können dagegen die Zellen).

=== Beispielrechnungen mit Zellbezügen ===
==== Beispiel 'Absoluter Zellbezug': Mehrwertsteuer ====
<small><code>Link auf Datei ergänzen</code></small><br />
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: <code>=B3*C3</code>). Für die weiteren Berechnungen ziehen wir mit dem Cursor von <code>D3</code> nach <code>D6</code>, damit die weiteren Gesamtpreise ohne MwSt. automatisch ausgefüllt werden ('''= relativer Zellbezug'''). Die '''Gesamtsumme ohne MwSt.''' ermitteln wir in der Zelle <code>D7</code> ü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 <code>I2</code> 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 (<code>E3</code>) folgende Formel ein: <code>=D3+D3*$I$2</code> ("D3 addiert um den Preis multipliziert mit der MwSt").<br />
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 <code>E7</code>) angeben. Damit haben Sie den Gesamtpreis inkl. MwSt. des Einkaufs im Museumsshop ermittelt.

==== Beispiel 'Gemischter Zellbezug': Museumsshop ====
<small><code>Link auf Datei ergänzen</code></small><br />
Zunächst kopieren wir wiederum die Werte aus der verlinkten Datei in die Tabelle (Zelle <code>A9</code>). 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.<br />
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 (<code>B15</code>): 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: <code>=B10/($B10+$C10+$D10)</code><ref group="A">Damit der Wert als Prozentangabe ausgegeben wird, klicken Sie auf das entsprechende Symbol (und können die Anzahl der Nachkommastellen nach Belieben einstellen).</ref><br />
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<ref group="A">Hinweis zur Kontrolle: Die Quersumme muss immer 100% betragen.</ref>.
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:<br />
Hierzu findet sich eine separate Calc-Datei mit dem Namen "Mehrwertsteuer" <small><code>Link auf Datei ergänzen</code></small>.
# Öffnen Sie die Datei.
# Gehen Sie nun zurück in die Zelle, in der wir zuvor den Gesamtpreis mit MwSt. berechnet hatten (<code>E3</code>).
# Löschen Sie den bisherigen absoluten Zellbezug auf die MwSt.
# Gehen Sie in die Datei "Mehrwertsteuer" und klicken Sie dort das Feld mit dem Prozentwert (<code>A5</code> = 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''' [<code>#$Tabelle1.$A$5</code>] in der Eingabezeile angezeigt; der '''Wert''' hat sich durch die niedrigere Mehrwertsteuer entsprechend '''verändert'''.

=== Konsolidieren und Transponieren ===
Folgt.

=== Vertiefende Rechenaufgaben ===
==== Top-Monate der Museen ====
Kehren wir zurück zu Tabelle 1 (Besucherzahlen).<br />
Hier hatten wir zuvor bei der Alten Pinakothek den Monat ausgegeben, in dem die meisten Besucher gezählt wurden: <code>=INDEX(A5:A16;VERGLEICH(MAX(D5:D16);D5:D16;0))</code><br />
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 <code>A5</code> und <code>A16</code> jeweils um ein '''Dollarzeichen''', um den Spaltenbezug für die Monatsnamen '''absolut''' zu setzen: <code>$A5:$A16</code><br />
Die Formel <code>=INDEX($A5:$A16;VERGLEICH(MAX(D5:D16);D5:D16;0))</code> kann für die beiden anderen Museen entsprechend aufgezogen werden. Es ergeben sich nun sinnvolle Werte ("Februar" und "März").<br />
==== 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.<br />
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" (<code>G1</code>) und "Mittelwert privat" (<code>F1</code>) an.<br />
Berechnen wir mit der uns bekannten '''Mittelwertfunktion''' zunächst die beiden Mittelwerte:<br />
* staatlich: <code>=MITTELWERT(B17;D17;E17)</code> → 258.358 (ohne Nachkommastellen)
* privat: <code>=MITTELWERT(C17;F17)</code>        → 225.848 (ohne Nachkommastellen)
Anschließend berechnen wir die entsprechenden '''Prozentwerte''' in der Zelle darunter:<br /><code>=G17/(G17+H17)</code> bzw. <code>H17/(G17+H17)</code><br />
'''Ergebnis''': Die staatlichen Museen liegen prozentual gesehen bei den Besucherzahlen leicht vor den privat getragenen Einrichtungen (53,36% zu 46,64%).

<br \><br \>
'''Zurück zu [[Berechnungen in Calc - Teil 1: Von Formeln und Funktionen]]''' ⇐⇒ '''Weiter zu [[Fehlermeldungen in Calc]]'''

== Anmerkungen ==
<references group="A" />