_ win_logo.gif (1023 Byte)


zur Homepage
 Registry - Aufbau und Funktionsweise
Win95 Tips
Win95 Tuning
Internet Explorer 4 Tips
Winword Tips

Seite 1

Seite 3
Email schreiben

Microsoft Exel 8.0 - Tips

_


Deutsche Kalenderwoche berechnen
Text im Textfeld frei drehbar gestalten
Push-Push Button mit VBA realisieren
Doppelte Datensätze aus einer Liste entfernen
Beschriftungen automatisch anpassen
Add-ins löschen
Letzter Werktag des Monats
Web-Abfragen erstellen
Web-Abfragen aufrufen
Ersetzen in Zellnotizen

_

vorherige Seite | Seite 2 | nächste Seite

Deutsche Kalenderwoche berechnen

Die deutschen Kalenderwochen berechnen sich wie folgt: Der 1. Januar eines Jahres gehört erst dann zur ersten Kalenderwoche, wenn dieser Tag auf einen Montag, Dienstag, Mittwoch oder Donnerstag fällt. Falls der 1.Januar ein Freitag, Samstag oder Sonntag ist, zählt er, und ggf. auch der 2. und 3. Januar, noch zur letzten Kalenderwoche des vorherigen Jahres.

Weiterhin können der 29., 30. und 31.12. eines Jahres schon zur Kalenderwoche 1 des neuen Jahres gehören. Das ist genau dann der Fall, wenn der 31.12. auf einen Montag, Dienstag oder Mittwoch fällt.

Die folgende VBA-Funktion (VISUAL BASIC für Applikationen) liefert für ein Datum die Kalenderwoche nach deutschem Standard:

Function dt_Kalenderwoche(dat As Date) As Integer
Dim a As Integer
a = Int((dat - DateSerial(Year(dat), 1, 1) + _
((WeekDay(DateSerial(Year(dat), 1, 1)) + 1) Mod 7) - 3) / 7) + 1
If a = 0 Then
a = dt_Kalenderwoche(DateSerial(Year(dat) - 1, 12, 31))
ElseIf a = 53 And (WeekDay(DateSerial(Year(dat), 12, 31)) - 1) Mod 7  < = 3 Then
a = 1
End If
dt_Kalenderwoche = a
End Function

Text im Textfeld frei drehbar gestalten

Werden in Powerpoint gedrehte Texte nach Exel portiert, verlieren sie dabei ihren Drehwinkel. Um diesen jedoch zu behalten, gehen Sie wie folgt vor:

1. Erstellen Sie das Zeichnungselement mit dem gedrehten Text in POWERPOINT
2. Markieren Sie das Zeichnungselement und kopieren Sie es in die Zwischenablage.
3. Fügen Sie es über den Menüpunkt BEARBEITEN - INHALTE EINFÜGEN in WORD 8.0 oder EXCEL 8.0 ein. In EXCEL müssen Sie im INHALTE EINFÜGEN-Dialog das Format "Grafik (Enhanced Metadatei)" wählen, in WORD entweder "Grafik" oder "Bild (Enhanced Metafile)".

Das Zeichnungselement mit dem gedrehten Text wird dadurch als Grafik eingefügt. Der gedrehte Text ist aber danach in WORD oder EXCEL nicht mehr als Text zu bearbeiten.

Push-Push Button mit VBA realisieren

Mit dem folgenden Beispiel erzeugen Sie unter Microsoft EXCEL 5.0/7.0/8.0 einen Symbolleistenschalter, der beim ersten Mausklick gedrückt bleibt und beim zweiten Mausklick ungedrückt ist (Push-Push-Schalter) usw.

Erstellen Sie hierzu die folgenden Prozeduren unter den jeweiligen Microsoft EXCEL Versionen und weisen Sie dem neuen Symbol die Prozedur zu. Im Beispiel heißt die neue Symbolleiste "Symbolleiste 1" kann aber entsprechend Ihrer Vorstellung angepaßt werden.

Beispiel zu Microsoft EXCEL 5.0/7.0:

Sub Push_Push_Button_Toolbar()
  

If Toolbars("Symbolleiste 1").ToolbarButtons(1).Pushed Then Toolbars("Symbolleiste 1").ToolbarButtons(1).Pushed = False 'hier Ihren Makro-Code einfügen Else Toolbars("Symbolleiste 1").ToolbarButtons(1).Pushed = True 'hier Ihren Makro-Code einfügen End If End Sub

Beispiel zu Microsoft EXCEL 8.0:

Sub Push_Push_Button_Commandbar()
  
   If CommandBars("Symbolleiste 1").Controls(1).State = msoButtonDown Then
       CommandBars("Symbolleiste 1").Controls(1).State = msoButtonUp
       'hier Ihren Makro-Code einfügen
   Else
       CommandBars("Symbolleiste 1").Controls(1).State = msoButtonDown
       'hier Ihren Makro-Code einfügen
   End If
  
End Sub

Anmerkung: Das Beipiel für Microsoft EXCEL 5.0/7.0 ist auch unter der Version 8.0 lauffähig. Es wird jedoch empfohlen unter Microsoft EXCEL 8.0 das neue Objekmodell einzusetzen. Das alte Objektmodell ist jedoch aus Kompatibilität noch enthalten.

Doppelte Datensätze aus einer Liste entfernen

Über einen Spezialfilter können Sie doppelte Datensätze aus einer Liste entfernen.
Der Spezialfilter ist zum Ausfiltern von Duplikaten geeignet. Im folgenden Text finden Sie die genaue Vorgehensweise:

 
Wählen Sie aus den Menü DATEN den Menüpunkt FILTER - SPEZIALFILTER.
Deaktivieren Sie die Option "An eine andere Stelle kopieren".
Geben Sie als Listenbereich den Zellbereich mit den Datensätzen einschließlich Überschriften an.
Als Kriterienbereich verwenden Sie den unter "Listenbereich" angegebenen Zellbereich.
Kreuzen Sie "Keine Duplikate" an.
Geben Sie den gewünschten Ausgabebereich an.

Das Ergebnis ist eine Datenbank, die jeden eindeutigen Datensatz einmal enthält. Da der Kriterienbereich dem Listenbereich entspricht, werden außer den Duplikaten keine weiteren Datensätze ausgefiltert.

Beschriftungen automatisch anpassen

Das Problem in fast jeder Tabelle: Die Beschriftungen sind zu lang. Bisher haben Sie in diesen Fällen mit Spaltenbreiten, Zeilenumbruch in einer Zelle oder der Schriftgröße experimentiert, bis endlich das gewünschte Ergebnis erzielt war. Excel 97 macht es Ihnen einfach: Paßt eine Beschriftung nicht ganz in eine Zelle rufen Sie das Dialogfeld Format / Zellen auf (oder drücken Strg+1) und wechseln auf die Registerkarte "Ausrichtung". Hier aktivieren Sie dann ganz einfach das Kontrollkästchen An Zellgröße anpassen und bestätigen mit Ok. Excel paßt die Schriftgröße daraufhin voll automatisch so an, daß die Zeichenkette in die Zelle paßt.

 

Add-ins löschen

Im Add-in-Manager fehlt die Möglichkeit, einmal installierte Add-ins zu löschen. Excel läßt sich aber auf zwei Arten überlisten. Der eine Weg geht über die Registrierdatenbank:

1. Deaktivieren Sie unter Extras/Add-in-Manager alle Add-ins, die Sie löschen möchten, und schließen Sie Excel.
2. Starten Sie den Registrierungseditor (regedit), und suchen Sie (je nach Excel-Version) nach folgendem Schlüssel:
Excel 95: HKEY_CURRENT_USER/Software/Microsoft/Excel/7.0
Excel 97: HKEY_CURRENT_USER/Software/Microsoft/Office/7.0/Excel
3. Ein Klick auf Add-in-Manager in der Baumstruktur zeigt alle installierten und derzeit nicht aktiven Add-ins im rechten Fenster. Markieren Sie nun das Add-in, das Sie entfernen wollen, und wählen Sie Bearbeiten/Löschen. Nachdem Sie die Sicherheitsabfrage mit Ja beantwortet haben, können Sie weitere Add-ins löschen oder den Registrierungseditor verlassen.

Wer die Registrierungsdatenbank nicht manuell bearbeiten möchte, kann den Add-in-Manager auch anders austricksen:

1. Ist Excel geschlossen, wechseln Sie zum Explorer, und löschen beziehungsweise verschieben Sie die Datei der entsprechenden Add-ins.
2. Starten Sie anschließend Excel, und klicken Sie im Add-in-Manager auf den Eintrag des Add-ins, das Sie eben im Explorer gelöscht oder verschoben haben. Da Excel die Datei nicht mehr findet, bietet er jetzt an, das Add-in aus der Liste zu löschen.

Letzter Werktag des Monats

Der letzte Werktag eines Monats wird in vielen Tabellen benötigt, um zum Beispiel Termine für Abrechnungen festzulegen. Zwar bietet Excel dazu keine spezielle Funktion, aber nach ein paar Schritten haben Sie das gewünschte Ergebnis:

1. Geben Sie in zwei benachbarte Zellen 31.01.97 und 28.02.97 ein.
2. Markieren Sie die beiden Zellen, und erstellen Sie mit Hilfe der Autofüllfunktion von Excel eine Datumsreihe bis zum gewünschten Monat. Anschließend geben Sie in die Zelle unter dem ersten Datum die folgende Formel ein (ersetzen Sie A1 durch den Bezug der entsprechenden Zelle in Ihrem Arbeitsblatt):
=WENN(WOCHENTAG(A1)=1; A1-2; WENN(WOCHENTAG(A1) = 7; A1-1; A1))
3. Nun müssen Sie die Formel nur noch kopieren und die Ergebnisse ins Format Datum umwandeln.

Web-Abfragen erstellen

Excel wird zwar mit einigen Web-Abfragen ausgeliefert, diese liefern aber nicht unbedingt die Daten, an denen Sie interessiert sind. Es ist aber ganz einfach, eigene Web-Abfragen zu erstellen. Denn die Abfragen bestehen aus maximal vier Zeilen und sind in jedem beliebigen Texteditor mit der Endung .iqy zu speichern:

 
Die ersten Zeile legt fest, wo die Abfrage ausgeführt wird. Hier ist derzeit WEB
der einzig mögliche Eintrag.
In der zweiten Zeile steht - sofern vorhanden - immer die Zahl 1. Da die zweite Zeile und die erste zusammengehören, müssen Sie entweder beide weglassen oder beide verwenden.
In der dritten und vierten Zeile stehen die eigentlichen Anweisungen. Dabei kann es sich um eine statische oder eine dynamische Abfrage handeln:

Bei statischen Abfragen benötigen Sie lediglich die dritte Zeile, in die Sie die URL der Seite eintragen, die Sie anzeigen möchten:
http://<server>/<datei>

Dynamische Abfragen enthalten neben der URL die Angaben für einen Dialog, in den der Anwender Werte für ein bestimmtes Feld eingeben kann. Je nachdem, welche Methode für ein HTML-Formular verwendet wird (Get oder Post), gibt es zwei Syntaxvarianten.

Die Methode HTML Get form kommt mit einer Zeile aus:
http://<server>/<datei>?<feldname>=["<feldname>","<anweisungstext>"]

Die HTML-Post-form-Methode verteilt diese Informationen auf zwei Zeilen:
http://<server>/<datei>
<feldname>=["FELDNAME", "<anweisungstext>"]

Web-Abfragen aufrufen

Damit Excel Ihre selbsterstellten Abfragen unter Abfrage ausführen öffnet, speichern Sie die Abfragen im Ordner Microsoft Office\Abfragen.

Web-Abfragen lassen sich von VBA aus definieren und öffnen. In beiden Fällen fügen Sie mit Add ein Objekt zur QueryTables-Sammlung hinzu und geben dabei die Datei an, in der die Web-Abfrage steht oder die URL der Daten, die dargestellt werden sollen. Außerdem gibt es eine Reihe von Parametern, mit deren Hilfe Sie das Verhalten der Web-Abfrage näher bestimmen können.

Der folgende Ausdruck öffnet beispielsweise die Web-Abfrage von PC Quote, Inc. Diese Abfrage liefert die Aktienkurse derjenigen Firmen, die ein Dialog abfragt.

With ActiveSheet.QueryTables.Add
(Connection:="FINDER;C:\Programme\Microsoft Office\Abfragen\Aktienkurse
von PC Quote, Inc.iqy", Destination:=Range("A1"))

.FieldNames = False
.RefreshStyle = xlInsertDeleteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = True
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = False
.SaveData = True
End With

Das Argument Connection gibt die auszuführende Web-Abfrage an. Ist diese nicht vorhanden, verwenden Sie das Schlüsselwort URL an Stelle von FINDER und geben hinter dem Semikolon die URL an.

Das Argument Destination legt fest, wo das Abfrageergebnis im Tabellenblatt angezeigt wird. Die übrigen Argumente sind optional und legen das Aussehen sowie das Verhalten der Abfrage, das Sie im Dialog Eigenschaften des externen Datenbereichs manuell festlegen können.

Ersetzen in Zellnotizen

Die Suchen-und-Ersetzen-Funktion von Excel berücksichtigt ausschließlich Zellinhalte. Geht's aber um Informationen, die in Kommentaren stecken, weiß Excel keinen Rat. Das folgende Makro füllt diese Lücke und durchsucht alle Kommentare im vor dem Start des Makros markierten Bereich:

Sub NotizErsetzen()
SuchenNach = InputBox("Suche nach:", "Suchen/Ersetzen in Zellnotizen")
ErsetzenDurch = InputBox("Ersetzen durch:", "Suchen/Ersetzen in Zellnotizen")
If SuchenNach = ErsetzenDurch Then
MsgBox "Such- und Ersetzungsstring stimmen überein. Das Makro wird abgebrochen!", 16
End If
For Each MarkierteZelle In Selection
Position = InStr(1, MarkierteZelle.NoteText, SuchenNach, 1)
If Position > 0 Then
Text1 = Left(MarkierteZelle.NoteText, Position - 1)
Text2 = Mid(MarkierteZelle.NoteText, Position, Len(SuchenNach))
Text3 = Right(MarkierteZelle.NoteText, Len(MarkierteZelle.NoteText) - (Position + Len(SuchenNach) - 1))
MarkierteZelle.NoteText Text:=Text1 & ErsetzenDurch & Text3
End If
Next MarkierteZelle
Ende:
End Sub


vorherige Seite | Seitenanfang | nächste Seite