Excel-Minis 04/2018

Für den vierten Beitrag des Jahres „Excel-Minis“ erwarten Dich fünf Themen.

Dies sind sie:

  1. Hyperlinks im gleichen Arbeitsblatt
  2. Hyperlinks zählen
  3. Die Textfunktionen GROSS und GROSS2
  4. Welche Zahlen sind Quadratzahlen?
  5. Welche Zahlen sind Primzahlen?

1. Hyperlinks im gleichen Arbeitsblatt

Es soll auf eine Zelle im aktuellen Dokument und im aktuellen Arbeitsblatt verlinkt werden, z.B. auf Tabelle1, A19.

Wähle die Zelle, die den Hyperlink erhalten soll. Klicke mit der rechten Maustaste und wähle „Link“.

Fülle das Eingabefeld so aus:

Hyperlink1

Wähle Link zu „Aktuelles Dokument“, Zellbezug „A19“, Stelle im Dokument „Tabelle1“ und Anzuzeigender Text z.B. „Nächste“.

Jetzt soll eine Zelle im gleichen Dokument, aber einem anderen Arbeitsblatt angesteuert werden, z.B. Tabelle2, D8.

Führe die Schritte wie zuvor aus und nehme diese Eintragungen vor:

Hyperlink2

2. Hyperlinks zählen

Alleine mit Excel ist das Zählen von Hyperlinks nur eingeschränkt möglich, wenn Links zu Websites gesetzt sind.

=ZÄHLWNWENN(E:E;“http*“)

oder

=ZÄHLENWENN(E:E;“*www*“)
Berücksichtigt wird jeweils der anzuzeigende Text.

Besser geht es mit einer kleinen VBA-Lösung. Schreibe dieses Makro in ein Modul Deiner xlsm-Datei:

Sub Hyper()
Range(„J4“)=Columns(„I“).Hyperlinks.Count
End Sub

Es werden hiermit alle Hyperlinks einer Spalte, einer Zeile oder eines Bereiches gezählt, unabhängig davon, wie der anzuzeigende Text aussieht.

Ersetze bei Zeilen Columns durch Rows und bei Bereichen durch Range.

3. Die Textfunktionen GROSS und GROSS2

Die Funktion GROSS wandelt Text in Großbuchstaben um.

Beispiel:

Text in B95: „Heute Nacht wird es nebelig“.

=GROSS(B95)
Ergebnis: „HEUTE NACHT WIRD ES NEBELIG“

=GROSS(„Heute Nacht wird es nebelig“)
Ergebnis: „HEUTE NACHT WIRD ES NEBELIG“

Die Funktion GROSS2 wandelt den ersten Buchstaben aller Wörter einer Zeichenfolge in Großbuchstaben um. Alle anderen Buchstaben werden in Kleinbuchstaben umgewandelt.

Beispiel:

Text in B105: „Der Mond hängt in den Bäumen“

=GROSS2(B105)
Ergebnis: „Der Mond Hängt In Den Bäumen“

=GROSS2(„Der Mond hängt in den Bäumen“)
Ergebnis: „Der Mond Hängt In Den Bäumen“

4. Welche Zahlen sind Quadratzahlen?

Es könnte diese Frage durchaus einmal aufkommen: Welche Zahlen in einem Bereich sind Quadratzahlen?

Beispiel:

Quadratzahl1

Du willst den Bereich B50:B71 hinsichtlich des Vorliegens von Quadratzahlen prüfen. Zunächst soll im Bereich D50:D71 der Wurzelwert geschrieben werden, wenn es sich in B50:B71um eine Quadratzahl handelt.

Schreibe in D50 diese Formel und ziehe sie bis D71 herunter:

=WENN(GANZZAHL(WURZEL(B50))*GANZZAHL(WURZEL(B50))=B50;WURZEL(B50);““)

Die gesuchten Werte sind eingetragen.

Nun willst Du die Quadratzahlen im Bereich B50:B71 optisch hervorheben. Dazu eignet sich die bedingte Formatierung.

Markiere den Bereich, gehe über Start/Bedingte Formatierung auf Neue Regel und wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.

Schreibe diese Formel:

=B50=GANZZAHL(WURZEL(B50))*GANZZAHL(WURZEL(B50))

Wähle unter Formatieren z.B. Ausfüllen und blau.

Quadratzahl2

Deine Tabelle sieht jetzt so aus:

Quadratzahl3

Die Quadratzahlen sind farbig markiert.

5. Welche Zahlen sind Primzahlen?

Primzahlen können nur durch 1 und durch sich selbst dividiert werden.

Nachfolgend sind beispielhaft die Zahlen von 1 bis 50 aufgelistet. Mittels bedingter Formatierung sollen die Primzahlen markiert werden.

Primzahl1

Dazu hast Du den Bereich B74:B123 markiert, bist in die Bedingte Formatierung / Neue Regel gegangen und hast auch hier „Formel zur Ermittlung …“ gewählt.

Trage diese Formel ein:

=NICHT(SUMME(WENN(REST(B74;ZEILE(INDIREKT(„2:“&AUFRUNDEN(B74^0,5;0))));0;1)))

Die Primzahlen dieses Bereiches sind jetzt markiert.

Die Formel wurde in Anlehnung an einen Beitrag in excelformeln.de [1] entwickelt.

[1] http://www.excelformeln.de/formeln.html?welcher=129

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 )

Google Foto

Du kommentierst mit Deinem Google-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