Diese Website ist optimiert für Firefox.   

Die Tippfarm

TIPPTREE

Excel

Allgemeine Tipps

Datenerfassung

Datenverwaltung und -bearbeitung

Datum und Uhrzeit

Diagrammtipps

Drucktipps

Formatierung Diverse

Formatierung Zahlen

Formeln und Funktionen

0 in Formelergebnissen unterdrücken

Abfangen: Div/0!

Abhängigkeiten in Formeln finden

Absolute Bezüge in relative umwandeln

Alle Formeln anzeigen

Alle Formeln markieren

Alle Sonder- und überflüssigen Leerzeichen aus Zelltext entfernen

Alle Zellen mit Formeln, Konstanten oder Kommentaren markieren

Anzahl der unterschiedlichen Einträge eines Bereichs ermitteln

Arbeitsblätter über Zellbezug auswählen

Aus Einzelpositionen gleitende Wochenergebnisse aufaddieren

Automatischer Vergleich zweier Tabellenbereiche

Bedingte Formatierung mit der AGGREGAT-Funktion

Bedingte Summe mit mehreren Kriterien

Berechnungen in einer Excel-Tabelle

Bereichsnamen auflisten

Bezug auf Bereichsnamen in externer Mappe

Bezug auf Tabelle schnell ändern

Bezüge (relativ / absolut) schnell konvertieren

Blattname in Zelle übernehmen

Datentyp des Zellinhalts auswerten

Datum in Textverknüpfung

Datums-Wertreihen bequem analysieren

Den Funktionsassistenten für verschachtelte Funktionen nutzen

Dezimalpunkt durch Dezimalkomma ersetzen

Echte Zufallszahlen erzeugen

Eindeutige Werte aus Liste ausgeben 1

Eindeutige Werte aus Liste ausgeben 2

Erster, Zweiter, Dritter

Externe Bezüge in einer Arbeitsmappe aktualisieren

Externe Verknüpfungen anpassen

Faktor bedingt berücksichtigen

Fehler-Informationsfunktionen

Fehlermeldungen vermeiden

Fehlerwerte in Formeln unterdrücken

Formatierte Zahl in Textzelle integrieren

Formel für einen Bereich erstellen

Formel in Teilschritten prüfen

Formelauswertung findet Fehler in Formeln

Formelbezug auf Arbeitsblätter variabel machen

Formeln endgültig in Ergebnisse umwandeln

Formeln mit fehlerhaften Ergebnisse markieren

Formeln statt Ergebnisse anzeigen

Formeln und Ergebnisse vergleichen

Formeln übersichtlich kontrollieren

Formelteile hinter Namen verstecken

Funktion TEILERGEBNIS ohne Gruppierung

Funktionen auf "Knopfdruck" verwenden

Funktionen Deutsch - Englisch

Funktions-Assistent schnell aufrufen

Funktionsargumente einfach festlegen

Für Mailadressen Umlaute aus Namen entfernen

GANZZAHL oder KÜRZEN

Grafische Darstellung der Formelzusammenhänge

Großbuchstaben erzwingen

Gültigkeitsbereich von Namen

Hexadezimale Zahlen umwandeln

In Zelle nur Texte ausgeben

Ist ein Wert bereits vorhanden?

Jede zweite Spalte addieren

Kassabuch mit zwei Konten

Keine Doppelerfassung in einem Bereich

Keine Fehler bei Zusammenfassungen (AGGREGAT)

Kommt ein Wert "x" in einem Bereich vor?

Konstante mit Namen belegen

Kürzestschreibweise für Bedingungsprüfung

Letztes Vorkommen eines Zeichens finden

Liste der Zell- und Bereichsnamen einer Arbeitsmappe

Lokale und globale Bereichsnamen

Matrix nach mehreren Kriterien durchsuchen

Matrixformel ohne Zellbezug

Matrixformeln verstehen

Maximalwert eines Teils einer Tabelle ermitteln

Maßeinheiten umwandeln

Mehrere Bereiche auf Übereinstimmung prüfen

Mehrfacheinträge hervorheben

Mehrwertsteuer berechnen

Minimum positiver Zahlen

Mittelwert ohne Ausreißer

Monatswert mit Vorjahrsvergleich

Monatszinssatz korrekt berechnen

Namen als Textbausteine verwenden

Namen anzeigen

Namen vereinfachen Formel

Negative Zahlen korrekt anzeigen

Neue Bereichsnamen auf kurzem Weg

Nur jede 2.(3.) Zeile einer Spalte addieren

Nur negative Werte summieren

Planwerte um fixe Faktoren verändern

Planziffern auf Sensitivität prüfen (1)

Planziffern auf Sensitivität prüfen (2)

Preisvergleich über mehrere Produkte und unterschiedliche Mengen

Prozentuelle Auf- und Abschläge

Rang innerhalb einer Zahlenliste

Rechenergebnisse per Überwachung kontrollieren

Rechenergebnisse runden

Rechnen mit Wahrheitswerten

Relative Bereichsnamen

Relative externe Bezüge unmittelbar erzeugen

Runden mit OBERGRENZE / UNTERGRENZE

Runden von Zahlen auf die ERSTEN x Stellen

Schrittweise Auswertung von Formeln und Fehlersuche

Spaltenbezeichnung aus Spaltennummer und umgekehrt

Spaltennummerierung umwandeln

Spezialität beim Runden

Standardabweichung - einfach erklärt

Statistikfunktion TREND

Statusleistenfunktionen für Turboergebnisse

Summe aus dem Produkt mehrerer Spalten bilden

Summe der Schnittmenge zweier Bereiche

Summe ohne Extremwerte

Summen bilden

Summenprodukt mit Bedingung

SUMMEWENN - Ausdruck als Kriterium

SUMMEWENN: Bedingung als Zellbezug

Summieren trotz Fehlerwerten

SVERWEIS mit zwei Bedingungen

Tabellen-Datenpunkte mit INDEX auswerten

Tabellenbereich mit Bedingungen summieren

Teile aus Zelltext auslesen

Telefonnummern automatisch "bereinigen"

Text einer Formel darstellen

Text in Zahlen umwandeln

Textbausteine als Namen festlegen

Texte in Formeln

Textzellen verknüpfen

Transponieren und verknüpfen

Unsichtbare Parameter

Variable Summenbereiche

Verknüpfung mit Doppelklick anwählen

Verknüpfungen mit anderen Dokumenten nicht automatisch aktualisieren

Verschachtelte Funktionen

Vertikale Liste zu Matrix umformen

Vor- und Nachnamen trennen (1)

Vor- und Nachnamen trennen (2)

Websites mit der Funktion HYPERLINK aufrufen

Wert schon vorhanden?

Werte gerundet summieren

Werte nach Monaten aufaddieren

Wurzel ziehen

Über mehrere Blätter summieren

Überflüssige Leerzeichen entfernen

Zahl aus Verkettung von Ziffern erstellen

Zahl in formatierten Text umwandeln

Zahlen automatisch runden

Zeilennummer eines bestimmten Inhalts finden

Zelladressen und Zellbezugsarten

Zellbezug zu externer Arbeitsmappe

Zellen mit Zahl und Text addieren

Zellinformationen abfragen

Zielwert aus rechteckigem Bereich auslesen

Zielwertprognosen erstellen

Ziffernsumme bilden

Zufallszahlen für Testzwecke

Zusätzliche Funktionen für Datum & Uhrzeit

Zweit- und drittgrößten Wert berechnen

Zweit- und drittgrößter Wert einer gefilterten Tabelle

Zwischensummen summieren

ZÄHLENWENN mit mehreren Bedingungen

PowerPivot

Zu noch mehr Excel-Tipps…

Hard-, Software, IT-Security

Internet

Office-Tools

OneNote

Outlook

PowerPoint

Windows

Word

Zeitschriften, Fachbücher

Zusatzinfos

Nr. 1382: Zelladressen und Zellbezugsarten

Tippfarm auch auf:  XING und Twitter 

Tipp getestet unter Excel 2003, 2007, 2010.

Das Verständnis der Adress-Syntax von Excel ist von grundlegender Bedeutung für den sicheren Umgang mit Formeln. Daher hier eine kurze Zusammenfassung:

Zelladressen

  • Die Adresse einer Zelle beschreibt den Schnittpunkt der Spalten- und Zeilennummer
  • In der A1-Bezugsart wird zuerst die Spaltennummer ("C") und dann die Zeilennummer ("4") angegeben.
    Der Bezug auf die Zelle D2 wird in einer Formel dann, unabhängig von der Adresse der Zelle, die die Formel enthält, so angezeigt:

    =D2

  • Die Bezugsart Z1S1 dagegen zeigt zuerst die Zeilennummer und dann die Spaltennummer (beide nummerisch) in Klammern gesetzt und im relativen Abstand zur Zelle, die die Formel enthält an.
    In der Zelle C4 (Z4S3) sieht ein Bezug auf Zelle D2 (Z2S4) so aus:

    =Z(-2)S(1)

  • So schalten Sie zwischen den beiden Bezugsarten um:
    • Excel 2003
      1. Menü Extras - Optionen.
      2. Register Allgemein.
      3. Checkbox Z1S1-Bezugsart (de)aktivieren.
      4. OK
    • Excel 2007/10
      1. Office Schaltfläche - Excel-Optionen (Excel 2007) bzw. Menüband Register Datei - Optionen (Excel 2010).
      2. Im vertikalen Menü auf Formeln klicken.
      3. Z1S1-Bezugsart aktivieren bzw. deaktivieren.
      4. OK.

Zellbezugsarten

  • Die oben verwendeten Zellbezüge sind relativ, d.h., sie beziehen sich nicht auf die angeschriebene Zelladresse, sondern sie beschreiben (relativ) den Abstand zu der Zelle, in der die Adresse verwendet wird.
    Beispiel: In der Zelle C4 steht die Formel "=D2". Gemeint ist damit die Zelle, die eine Spalte rechts von und zwei Zeilen über der aktiven Zelle (C4) steht. Kopieren Sie die Formel um eine Zeile nach unten und zwei Spalte nach rechts (in die Zelle E5), dann ändert sich die Formel automatisch zu "=F3".
    Daher sprechen wir hier von einem relativen Bezug.
    In der Z1S1-Schreibweise werden relative Bezugswerte nach "Z" bzw. "S" eingeklammert (.s.o.)
  • Wenn Sie wollen, dass ein Zellbezug sich nicht ändert, brauchen Sie einen absoluten Bezug.
    Dazu tippen Sie unmittelbar nach der Eingabe der Zelladresse oder nachdem Sie die Einfügemarke auf diese gestellt haben, auf die Taste . Die Schreibeweise der Adresse ändert sich. Sie lautet nun: "=$D$2". Die beiden "$"-Zeichen verhindern das Ändern der Adresse beim Kopieren. Auch nach dem Kopieren der Formel zur Zelle E5 lautet der Bezug "=$D$2".
    Z1S1-Schreibweise: Die Zeilen und Spaltennummern werden ohne Klammern geschrieben.
  • Soll sich beim Kopieren nur die Spalten-, nicht aber die Zeilennummer ändern, stellen Sie gemischte Bezugsart Zeile absolut her.
    Stellen Sie den absoluten Bezug her und drücken Sie nochmals . Die Formel lautet nun "=D$2".
    Z1S1-Schreibweise: Der Wert nach "Z" wird ohne, derjenige nach "S" mit Klammern angegeben.
  • Wenn Sie im Zustand gemischte Bezugsart Zeile absolut nochmals drücken, erhalten Sie die gemischte Bezugsart Spalte absolut. Die Formel sieht nun so aus: "=$D2".
    Beim Kopieren ändert sich die Zeilennummer, die Spalte aber bleibt fix.
    Z1S1-Schreibweise: Der Wert nach "Z" wird mit, derjenige nach "S" ohne Klammern angegeben.
  • Bemerkung 1: Drücken Sie in der gemischten Bezugsart Spalte absolut noch einmal , wird wieder der relative Bezug eingestellt.
  • Bemerkung 2: Wenn Sie nicht verwenden wollen, dürfen Sie die "$"-Zeichen natürlich auch selber tippen.
Eingestellt: 12.02.12 - Abrufe: 5.366