Was sonst noch wichtig ist

Teil 1: Tipps und Kniffe 1 Der schnelle Einstieg 23 2 Tipps und Kniffe 31 3 Was sonst noch wichtig ist 53 Was sonst noch wichtig ist 3.1 Die Matrix-Operation Angenommen, Sie wollen Bereiche innerhalb der Tabelle miteinander multipli- zieren und das Ergebnis dann summieren, aber benötigen die Zwischenergeb- nisse nicht. Welche Möglichkeit haben Sie? Die Antwort ist einfach: Verwenden Sie eine Matrix-Operation! Wie das geht? Angenommen, Sie wollen Werte, die in zwei Spalten stehen, miteinander multiplizieren und die Ergebnisse summieren. Aber eigentlich interessiert Sie nur die Summe und nicht die Einzelergebnisse, die aus irgend- welchen Gründen in der Berechnung auch nicht auftauchen sollen. Die Lösung heißt Matrix-Operation. 9 Geben Sie entsprechend der nachstehenden Abbildung in die Zellen B2 bis B4 die Werte 10, 20 und 30 und in die Zellen C2 bis C4 die Werte 50, 100 und 150 ein. 9 Wählen Sie die Zelle E3 aus und klicken Sie dann auf das SUMMENSYMBOL in der Symbolleiste. 9 Markieren Sie den Bereich B2 bis B4. 9 Klicken Sie auf das Mal-Zeichen (*). 9 Markieren Sie den Bereich C2 bis C4. 9 Beenden Sie den Vorgang durch gleichzeitige Betätigung der Tasten (Strg)+(ª)+(¢). Das Ergebnis müsste der nachstehenden Abbildung entsprechen. Einen praxisnahen Einsatz zeigt auch das Beispiel für den Mittelwert im fol- genden Kapitel. 53 3.2 Mittelwert ohne Berücksichtigung von Nullwerten Abbildung 3.1: Matrix-Operation 3.2 Mittelwert ohne Berücksichtigung von Nullwerten Vielleicht ist Ihnen das auch schon mal passiert: Sie wollen einen Mittelwert bilden, allerdings beruhen die Basiszahlen auf Formeln, die eventuell als Ergebnis einen Nullwert liefern können. Ist dies der Fall, so soll dieser Null- wert bei der Mittelwertbildung nicht berücksichtigt werden. Abbildung 3.2: Matrix kontra Mittelwert Wie Sie mit einem Blick erkennen, beträgt der Durchschnitt der Monate Januar und Februar 180,0 T€. Durchschnitt, so habe ich hier den von mir ermittelten Wert genannt, um ihn von der eingebauten Funktion Mittelwert zu unter- scheiden. Die Funktion Mittelwert errechnet einen Wert von 30,0 T€, da sie alle Positionen zur Bildung des Mittelwerts (fälschlicherweise) heranzieht. Übrigens tragen die Zellen C3 bis C14 den Zellnamen Gesamtkosten_Monat. 54 Die eigene Matrixfunktion, denn um eine solche handelt es sich hier, geht Kapitel 3 – Was sonst noch wichtig ist anders als die Funktion Mittelwert vor. Die nachstehende Funktion wird nun von innen nach außen aufgelöst: {=WENN(SUMME(Gesamtkosten_Monat)<>0;SUMME(Gesamtkosten_Monat)/ SUMME(WENN(Gesamtkosten_Monat<>0;1;0));0)} Alle Werte, die im Zellbereich Gesamtkosten_Monat enthalten sind, werden addiert: SUMME(Gesamtkosten_Monat) Es wird festgestellt, ob einige Positionen innerhalb des Zellbereichs einen Wert ungleich 0 (Null) besitzen. Dies geschieht mit WENN(Gesamtkosten_Monat<>0;1;0) Dann wird die Anzahl der festgestellten Positionen summiert: SUMME(WENN(Gesamtkosten_Monat<>0;1;0)) Die Gesamtsumme wird nun durch die Anzahl der ermittelten Positionen geteilt. Das Ergebnis ist der Durchschnitt. Die Wenn-Bedingung, die die soeben beschriebenen Funktionen einschließt, verhindert die Anzeige eines Fehlerwerts. Ein Fehler würde angezeigt werden, wenn sich im Bereich Gesamtkosten_Monat kein Wert befindet. Nun kommt aber das Wichtigste. Der Abschluss der Eingabe muss mit der Tas- tenkombination (Strg)+(ª)+(¢) erfolgen. Vergessen Sie dies, erscheint in die- ser Zelle die Fehlermeldung #WERT! Dies ist aber kein Beinbruch. Mit der Funktionstaste (F2) öffnen Sie diese Zelle wieder zur Bearbeitung und schließen sie sofort wieder mit der Tastenkombi- nation (Strg)+(ª)+(¢). 3.3 Interessante Funktionen 3.3.1 Die Funktion SummeWenn Auch diese Funktion ist noch nicht so bekannt, wie sie es eigentlich verdient hätte. Sie ist eigentlich eine Mischung von Summen-Funktion und Wenn- Bedingung. Es lassen sich beispielsweise Summen ungruppierter gleichlauten- der Positionen bilden. Angenommen, Sie haben Röcke, Hosen und Hemden und wollen für diese Artikel Summen bilden, so ist SummeWenn hierfür hervor- ragend geeignet. Aus folgender Tabelle sollen die entsprechenden Summenwerte ermittelt wer- den: 55 3.3 Interessante Funktionen Abbildung 3.3: Summieren von ungruppierten gleichlautenden Positionen Ein kleiner Tipp am Rande: Um sich das Leben leichter zu machen, ist es auch an dieser Stelle angebracht, Namen für die in Frage kommenden Bereiche zu vergeben. So habe ich für die Artikel, also den Bereich B3:B11, den Namen Arti- kel und für die Preise, Bereich C3:C11, den Namen Preise vergeben. 9 Rufen Sie den Funktionsassistenten auf und wählen Sie die Funktion SUM- MEWENN. Im zweiten Schritt des Diagramm-Assistenten geben Sie ein bzw. wählen Sie aus: 9 BEREICH, dies ist der Bereich, in dem der Begriff gesucht wird. Hierfür habe ich den Namen Artikel vergeben. 9 SUCHKRITERIEN: B12, in dieser Zelle steht das Suchkriterium »Röcke«. 9 SUMME_BEREICH: der Bereich, aus dem addiert werden soll. Hierfür habe ich den Namen Preise gewählt. Abbildung 3.4: Die Funktion SummeWenn 56 9 Beenden Sie die Eingabe mit Ka. Kapitel 3 – Was sonst noch wichtig ist Abbildung 3.5: Die fertige Tabelle mit den Bezügen 3.3.2 Die Funktion VRUNDEN Nicht sehr bekannt, aber in einigen Fällen unentbehrlich ist die Funktion VRUN- DEN. Ab Excel 5.0 steht eine Funktion zur Verfügung, mit der Zahlen auf das Vielfache einer Zahl gerundet werden können. Diese Funktion gehört aller- dings nicht zum Standardumfang von Excel, sondern ist in den Analyse-Funk- tionen enthalten. Diese Analyse-Funktionen sind als Add-In vorhanden, das über den Add-In-Manager (Menü EXTRAS) eingebunden werden kann. Abbildung 3.6: Die Funktion VRUNDEN Die Syntax der Funktion lautet VRUNDEN(Zahl;Vielfaches), wobei Zahl der Wert ist, der gerundet werden soll, und Vielfaches das Vielfache, auf das die Zahl gerundet werden soll. Wie Sie der obigen Abbildung entnehmen können, wird die Zahl 123,345 auf drei unterschiedliche Arten gerundet. 57 3.3 Interessante Funktionen 3.3.3 Runden nach Art des Hauses Sollten Sie auch mal negative Zahlen runden wollen, so liefert Ihnen die Funk- tion VRUNDEN() nur dann einen Wert zurück, wenn das Vielfache ebenfalls negativ ist. Wissen Sie nicht, ob der zu erwartende Wert positiv oder negativ sein wird, so bauen Sie diese Funktion einfach nach. Hierzu benötigen Sie aller- dings 4 Funktionen, nämlich RUNDEN, GANZZAHL, LOG und ABS. Fertig sieht die Funktion dann folgendermaßen aus: =RUNDEN(Ausgangswert;-GANZZAHL(LOG(ABS(Ausgangswert))-Stellenzahl)) Dieses Beispiel geht davon aus, dass sich der zu rundende Wert in der Zelle B4(Ausgangswert) befindet. Die Funktion ABS liefert den Absolutwert einer Zahl, also den Wert ohne Vorzeichen. Aus diesem Wert ermittelt die Funktion LOG den Logarithmus. Bei Zahlen zwischen 0 und 9 ist dieser Wert kleiner 1, bei Zahlen zwischen 10 und 99 kleiner 2 usw. Die Funktion GANZZAHL macht, wie der Name schon nahe legt, aus diesem Logarithmus eine Ganzzahl, indem sie diesen Wert auf die nächstkleinere ganze Zahl abrundet. Damit haben wir das zweite Argument für die Funktion RUNDEN, das erste Argument ist ja die zu rundende Zahl oder der Bezug auf diese Zahl. Von diesem zweiten Argument wird eine von Ihnen einzugebende Ziffer abgezogen. Mit dieser Ziffer bestim- men Sie die Anzahl der zu rundenden Stellen: Abbildung 3.7: Runden mit einer zusammengesetzten Funktion 58 3.3.4 Arbeiten mit Zufallszahlen Kapitel 3 – Was sonst noch wichtig ist Um bestimmte Situationen durchzuspielen, kann es sinnvoll sein, Bereiche mit zufälligen Zahlen zu füllen. Excel stellt hier im normalen Umfang die Funktion Zufallszahl und im erweiterten Umfang die Funktion Zufallsbereich zur Verfügung. Die Funktion Zufallszahl Die Funktion Zufallszahl liefert einen Wert zurück, der größer gleich 0 und kleiner als 1 ist. Wollen Sie einen Wert zwischen 1 und 100 haben, müssen Sie diese Funktion mit 100 multiplizieren. Lassen Sie die Tabelle neu berechnen, so wird auch die Zufallszahl neu ermittelt. Wenn Sie eine Zufallszahl erzeugen wollen, die zwischen 80 und 100 liegt, so hilft Ihnen diese Funktion nicht wei- ter. In diesem Fall greifen Sie auf die Funktion Zufallsbereich zurück. Die Funktion Zufallsbereich Die Funktion Zufallsbereich verfügt über zwei Argumente, mit denen der untere und der obere Bereich der Zufallszahl eingeschränkt werden kann. Der zurückgelieferte Wert ist eine Ganzzahl, die sich im angegebenen Bereich bewegt. Diese Funktion befindet sich jedoch nicht bei den normalen Funktionen, son- dern gehört zu den Analyse-Funktionen. Diese lassen sich, wie bereits beschrie- ben, mit dem Add-In-Manager installieren. Bei einer Neuberechnung der Tabelle werden auch diese Werte neu ermittelt. 3.4 Dynamische Bereiche anlegen Nehmen wir an, Sie benötigen einen Bezug auf einen bestimmten Bereich einer Tabelle. Was machen Sie? Richtig! Sie vergeben einen Zellbereichsnamen.