Excel – Bereichsbezug über zwei benannte Bereiche – INDIREKT

Bei Funktionen bestehen häufig Bezüge auf Bereiche vom Typ A:F (Spalte A bis Spalte F), A2:A7 (von Zelle A2 bis Zelle A7) oder A2:D7 (rechteckiger Bereich zwischen A2 und D7).

Der Bereich kann aber auch durch die Angabe von Namen aufgebaut werden.
Sind zum Beispiel Monatsdaten in den Spalten B bis M und die Spalten wurden entsprechend Januar, Februar,…, Dezember benannt, kann man auch mit =SUMME(Juli:Dezember) die Summe für das zweite Halbjahr bilden.

Stehen die Monatsnamen nicht direkt in der Funktion, sondern in zwei Zellen, können die Bereiche mit der Funktion INDIREKT aufgerufen werden. In N4 steht zum Beispiel Februar und in N5 steht Mai, so bildet =SUMME(INDIREKT(N4):INDIREKT(N5)) die Summe de-r Februar bis Mai-Werte.

Die Übungsdatei:
Excel 486 – Benannte Verbuende

Hier geht es zum Video:
Videolink: http://youtu.be/FaOni3HoqSU

PowerPoint – Wolke erstellen

Durch Kombination und Subtraktion von Formen können Wolkensymbole erstellt werden. In PowerPoint 2013 sind die Möglichkeiten direkt sichtbar. In 2010 müssen diese Funktionalitäten erst aktiviert werden.

Erstellen Sie zunächst ein paar Kreise und entfernen Sie die Rahmenlinien. Die obere Kontur der drei Kreise wird nachher die Wolkenform zeichnen.

PP1

Legen Sie anschließend ein Rechteck mit abgerundeten Ecken über die Kreise.

PP2

Wählen Sie, dass die Formen in Einzelmengen zerlegt werden sollen.

PP3

Sie sehen nun alle Einzelmengen.

PP4

Entfernen Sie die unteren Einzelmengen.

PP5

Fügen Sie nun die restlichen Einzelmengen zu einer Form zusammen.

PP6

Die neue Form kann nun formatiert und beschriftet werden.

PP7

Hier gibt es das Video:
Videolink: http://youtu.be/qcsIfdFAYgk

Excel – Kleinster positiver und größter negativer Wert – AGGREGAT

In einer Liste liegen Werte aus dem negativen Bereich nahe Null und aus dem positiven bei Null. Es wird der größte negative und der kleinste positive Wert gesucht.

AGGREGAT Typ 14 sucht den größten Wert einer Liste. Diese Werte lassen sich aber dadurch einschränken, dass man nur nach den negativen Werten suchen lässt. Dies kann durch die Prüfung, ob die Werte auch kleiner Null sind realisiert werden. Bei Null oder positiven Werten ergibt die Prüfung in der Klammer ein FALSCH, also wird durch Null geteilt, es resultiert ein Fehler und der Wert fliegt wegen des Parameters 6 für Fehler ignorieren aus dem Rennen.

Analog wird mit AGGREGAT Typ 15 nach dem kleinsten positiven Wert gesucht. Hier werden über die Prüfung nur Werte größer als Null zugelassen.

485a

Hier gibt es die Übungsdatei mit einem interaktivem Diagramm. Einfach mal die Zahlen in Spalte A ändern:
Excel 485 Größter negativer und kleinster positiver Wert

Hier geht es zum Video:
Videolink: http://youtu.be/rnqJhCZ83pQ

Excel – Benutzerdefiniertes Zahlenformat unterscheidet Gramm und KG

Bei den benutzerdefinierten Zahlenformaten können Prüfungen nach der Größe des Zahlenwerts stattfinden. Bei Werten kleiner 1000 soll das Kürzel „g“ als Einheit genommen werden, größere Zahlen erhalten ein „kg“. Damit die Zahlenwerte von Gramm und kg untereinander stehen, wird ein _k vor das „g“ ergänzt.

Um nun die Werte nicht umrechnen zu müssen, also kg = g / 1000, wird einfach an der Stelle des Tausendertrennpunkts ein Komma angezeigt. 0,000. (der hintere Punkt ist dabei wichtig) erhält den Zahlenwert.

484a

484b

Hier ist die Übungsdatei:
Excel 484 Benutzerdefiniertes Zahlenformat

Hier geht es zum Video:
Videolink: http://youtu.be/Vwum-1_JSRk

Weitere Videos zum Thema benutzerdefiniertes Zahlenformat wurden veröffentlicht. Unter anderem lassen sich darüber die Schriftfarben setzen.

Excel – Anzahl der Personen einer Altersgruppe ermitteln – DATEDIF

Liegt in einer Liste das jeweilige Geburtsdatum vor, kann mit der Funktion DATEDIF(Ausgangsdatum, Späteres Datum, „Y“) das Alter in abgeschlossenen Jahren ermittelt werden.
DATEDIF ist innerhalb von Excel nicht dokumentiert. Folgende Parameter können in DATEDIF genutzt werden:

„Y“ Volle Jahre
„M“ Volle Monate
„D“ Volle Tage
„MD“ Differenz in Tagen, wenn volle Monate bereits berücksichtigt wurden
„YM“ Differenz in Monaten, wenn abgeschlossene Jahre bereits berücksichtigt wurden
„YD“ Differenz in Tagen, wenn abgeschlossene Jahre bereits berücksichtigt wurden

482a

Die Formel
=SUMMENPRO
DUKT((DATEDIF(tab_Liste[Geburtstag];HEUTE();"Y")>=20)*(DATEDIF(tab_Liste[Geburtstag];HEUTE();"Y")<30))

ermittelt, bei wie vielen Prüfungen zum einen der vordere Teil mit der Prüfung >=20 WAHR ist und gleichzeitig bei der hinteren die Prüfung <30 WAHR ist. WAHR multipliziert mit WAHR ergibt den Wert 1, alle anderen Rechnungen ergeben 0*0, 1*0 bzw. 0*1 und somit den Wert Null. SUMMENPRODUKT ermittelt hier also nur die Anzahl der Werte, die im gesuchten Intervall liegen.

Hier gibt es die Übungsdatei:
Excel 482 Personen im Datumsbereich

Hier geht es zum Video:
Videolink: http://youtu.be/Uwo1PnqyaGw

Hier ist auch noch ein Video zur Funktion DATEDIF:
Videolink: http://youtu.be/4g8mE6P0ONw

Excel – Bildlaufleiste mit dynamischem Zellbezug

Ein Regler soll sich mit mehreren Zellen verbinden können, dessen Werte anzeigen und manipulieren können. Vielen Dank für den Code an Thomas Ramel.

480b

Über die Entwicklertools lassen sich zwei unterschiedliche Bildlaufleisten verwenden. Ich wähle die ActiveX-Steuerelemente, da diese programmiertechnisch komplexer angesprochen werden können.

480c

Der Code wird nicht in einem Modul, sondern direkt im VBA-Editor im gleichen Tabellenblatt hinterlegt.

480

Hier ist die als ZIP komprimierte XLSM-Datei:
Excel 480 Prozentanzeige Bedingte Formatierung

Hier geht es zum Video:
Videolink: http://youtu.be/cY0Hkal6AIQ

Excel – Zellen ohne Zellschutz optisch hervorheben

Sobald ein Tabellenblatt geschützt wird, können nur noch Eingaben in die Zellen vorgenommen werden, die vom Blattschutz ausgenommen sind.

Zunächst müssen die Zellen markiert werden. Dann entweder über STRG + 1 und dann im Register Schutz den Haken bei Gesperrt entfernen, oder im Register START in der Gruppe Zellen auf Format klicken und bei Zellen sperren den Schutz entfernen.

479a

Anschließend lässt sich die bedingte Formatierung auf einen Bereich, entweder die vorher formatierten Zellen oder einen größeren Bereich, anwenden. Wichtig bei der Eingabe der Formel ist, dass der Zellbezug dem der gerade aktiven Zelle ist.

Die Formel

=ZELLE("schutz";Zellbezug)=0

prüft, ob für die entsprechende Zelle der Zellschutz deaktiviert ist. Alternativ würde dort der Wert 1 resultieren. Bei der Eingabe der Funktion ZELLE werden weitere Argumente wie z.B. spalte und zeile angezeigt.

479b

Zum Schluss noch den Blattschutz aktivieren. Fertig.

Hier gibt es die formatierte Übungsdatei:
Excel 479 – ZELLE Schutz

Hier geht es zum Video:
Videolink: http://youtu.be/yq48FdV6Ohk

Excel – Kreissegmente mit zwei Beschriftungsfeldern – Aufgabe

Prozentwerte, Werte, Beschriftungen und Rubrikenname können gleichzeitig zur Beschriftung von Kreissegmenten genutzt werden. Dann wird es aber ganz schön voll, gerade bei mehreren Kreissegmenten.

Labels

Wie würdet Ihr die Beschriftungen trennen? Tricks, Tools, Standardfunktionen?
Die Auflösung folgt ein paar Videos später.
Viel Spaß beim tüfteln…

Hier gibt es die Aufgabe:
Videolink: http://youtu.be/VrDhgw7Sa-w