Anzahl und Prozente automatisch berechnen – unterschiedliche Zellbezüge – Formeln kopieren – in Microsoft Excel

Heute mal wieder ein Ausflug zu Microsoft Office.

Nachdem ich in den letzten Wochen ja eine Umfrage / Studie begonnen hatte, hab ich dann die Ergebnisse in einer Excel Tabelle abgespeichert – und dann noch Ergänzungen vorgenommen – und dann Diagramme daraus gemacht – und dann kam die Idee mit VerbundDiagrammen, um auf zu zeigen, wie der aktuelle Trend meiner aktuellen Umfrage – mit dem überein stimmt, was so generell die Verteilung der unterschiedlichsten Aspekte ist.

Zu kompliziert?

Beginnen wir am Beginn – heute geht es mir mal um die Formeln und Funktionen und Zellbezüge

Ich formulier es einfacher – bzw. mach dir gleich auch die entsprechenden Unterschiede hier bildlich rein. Also zuerst mal die ursprüngliche Tabelle:

Die erste Überlegung war, einfach mal an zu schauen, welcher Typ der jeweilige Mensch ist – welches Profil sie/er hat – ob Split oder nicht – und ich nahm dann gleich noch ein paar Überlegungen mit rein, wo ich vermutete, dass die wichtig werden könnten.

Dann kam ich auf die Idee, oben jeweils Summen zu machen – außer bei den Profilen – wobei da machte ich sie dann erst recht, damit ich später immer auch ganz genau weiß, wie viele Daten von Menschen ich schon erfasst habe – und auch als fixe BerechnungsZelle für die Prozente.

Warum X?

Einfach so – ist egal, was in den Zellen drinnen steht, die Formel zum Berechnen der Summen, die von dem jeweiligen „Zustand“ betroffen sind, ist nicht nur gleich.gültig, sondern schlichtweg egal – es geht darum, dass die Zellen addiert werden, in denen irgend etwas drinnen steht – könnte auch ein Leerzeichen sein – aber das wär dann schwieriger zum nachvollziehen.

Wie lautet jetzt diese Formel?

ANZAHL2(xx:yyy)

  1. Diese Formel erhältst du über den Menüpunkt Formeln
  2. dann das erste = fx – damit kannst du diverseste Funktionen einfügen – unter anderem eben auch Anzahl2
  3. Wenn du die Funktion auswählst, dann einfach bei Wert1 den Bereich markieren, von dem die Anzahl ermittelt werden soll

Ich ziehe das meist weiter runter – und markiere mir das dann auch durch eine Rahmenlinie, damit ich weiß, wenn ich bei neuen Einträgen außerhalb des Bereiches kommen würde – bzw. falls ich zur Linie komme, füge ich innerhalb des Bereiches neue Zeilen ein.

ACHTUNG: bei allen Formeln / Funktionen, die durch einen markierten Bereich entstehen – kannst du innerhalb diese Bereiches beliebig viele Zeilen nachträglich einfügen – und es wird automatisch mit berechnet – wenn du außerhalb des Bereiches kommt, müsstest du die Formel / Funktion entsprechend anpassen.

Prozente

Die Prozente sind dann relativ einfach – es muss immer die jeweils errechnete Anzahl durch die GesamtAnzahl dividiert werden – in meinem Fall zum Beispiel eben

=W3/$W$3

und dann eben als Prozent angezeigt werden – unter Zellen formatieren:

Jeder Mensch hat ein Profil – in meinem Fall = meiner Tabelle – daher kann ich diese errechnete Anzahl als Basiswert nehmen, mit dem wir dividieren können – und daher fixiere ich diese Zelle mit $-Zeichen – dann kann ich die Formel kopieren, weil sich der 2 Teil der Formel anpasst, je nachdem, wohin ich sie kopiere.

Zellbezüge in Formeln

Wenn wir Formeln kopieren wollen, ist das jetzt nicht immer ganz so einfach – wir sollten wissen, was wir möchten – weil sich normalerweise die Formeln anpassen – je nachdem, wohin wir sie kopieren

  • SZ = flexibler Zellbezug
  • S$Z = fixiert die Spalte
  • $SZ = fixiert die Zeile
  • $S$Z = fixiert diese ausgewählte Zelle

Du verstehst nur Bahnhof?

Dann etwas mehr Basics

Wenn du eine Formel eingibst, dann kannst du sie kopieren – und sie wird sich – je nachde, wohin du sie kopierst – entsprechend anpassen.

Was ist jetzt überhaupt eine Formel?

Eine Berechnung der Inhalte unterschiedlichster Zellen – oder – ja, klar, du kannst auch einfach eingeben =3*5 – aber dafür macht (für mich) Excel keinen Sinn – da wär der Taschenrechner sinnvoller – darum soll es hier und heute nicht gehen.

Mir geht es darum, mit den Inhalten von bestimmten Zellen so weit wie möglich automatisiert zu rechnen.

Alter in Tagen

Das war immer das erste Beispiel in meinen alten Seminaren zu Excel, um überhaupt mal eine Idee einer Formel zu bekommen 😉

  • Heutiges Datum
  • Geburtsdatum
  • = Heute minus Geburtsdatum

Wobei es für das heutige Datum natürlich auch wieder eine Funktion gibt, die dann jederzeit das aktuelle Alter in Tagen anzeigt 😉

Es soll hier jetzt mal nur um die Formeln gehen – wenn wir sie nach rechts ziehen, dann passen sich in der Formel jeweils die Spaltenbezeichnungen an:

Warum verwende ich hierzu Excel?

Weil ich damit jedes Alter von beliebigen Personen automatisch ausrechnen lassen kann – indem ich dann einfach in der 2. Zeile ein anderes Geburtsdatum eingebe:

Nein – ist jetzt überhaupt nicht wichtig, das zu wissen – aber war ein gutes Beispiel, um die Logik hinter kopierten Formeln zu erkennen – und meine realen Teilnehmer fanden es immer ganz spannend, dies dann gleich für die ganze Familie aus zu probieren 😉

ZapfenRechnung oder TurmRechnung

Das war immer das 2. Beispiel in meinen realen Seminaren – Aufgabenstellung – mit 2 Formeln eine derartige Berechnung durch zu führen – so solls aussehen:

Wir geben nur die Zahl 100 ein und dann, womit die jeweiligen Ergebnisse multipliziert – und dann dividiert – werden sollen:

Nachdem wir diese Formel dann kopiert haben, kommt die Division:

Auch die wird dann kopiert – so sollte das Ergebnis dann aussehen, wenn wir uns nur die Formeln anschauen

WICHTIG: Formeln anzeigen lassen kannst du dir im Menü Formeln – rechts oben findest du – Formeln anzeigen – zum Ausschalten einfach wieder dort hin klicken 😉

Warum Excel?

Weil, wenn du statt der ersten Zahl eine andere nimmst, wird nach dem Drücken der Enter Taste die komplette Liste neu berechnet 😉

Beispiel Umsatztabelle

Wenn ich Formeln eingebe – und die nach unten kopieren möchte, dann passen sich die Zellbezeichnungen entsprechend den Zeilen an, in welche sie kopiert werden:

Wenn du jetzt also nicht alle Teile deiner Formen automatisch anpassen lassen möchtest, kannst du Teile davon einfach auch fixieren, damit immer mit dem gleichen Wert gerechnet wird.

das keine und große 1×1

Hier noch eine Aufgabe, die ich früher in fortgeschritteneren Kursen immer gestellt habe – Lasse mit einer Formel diese Tabelle berechnen:

Was passiert, wenn wir jetzt nur mal eine „normale“ Formel eingeben?

Also die Formel würd ja stimmen – aber wir können sie nicht kopieren – weil wenn, kommt das raus:

A2 = wir wollen hier zwar immer mit dem Wert rechnen, der in der Spalte A ist – aber die Zeile soll sich anpasssen

B1 = hier sollte die Zeilennummer gleich bleiben und die Spalte sollte sich anpassen

daher ist die richtige Lösung:

Die $-Zeichen kannst du händisch eingeben – oder durch Drücken der F4 Taste erzeugen, wenn du in einem Zellbezug drinnen stehst mit dem Cursor – so sieht dann das Ergebnis aus:

Und wenn wir mehr als 10 wollen?

Dann ergänzen wir die Überschriften und kopieren die Formel weiter

Übrigens – die Hashtags rechts unten bekommst du weg, wenn du die Spalten verbreiterst = nur die Anzeige, dass die Zahl größer ist als die Zelle 😉


Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s