Von: excel-weekly-text-request@cgn.smarttools.de im Auftrag von SmartTools Publishing [excel-weekly-text-reply@smarttools.de] Gesendet: Montag, 28. März 2005 18:33 An: Excel Weekly Abonnenten Betreff: SmartTools Excel Weekly vom 29.3.2005 ----------------------------------------------------------- SmartTools Excel Weekly vom 29.3.2005 Jede Woche neu: Tipps, News und Problemloesungen rund um Microsoft Excel (c) 2005 by SmartTools Publishing ----------------------------------------------------------- +++ IN DIESER AUSGABE +++ EXCEL-NEWS & DOWNLOADS + SPOTLIGHT: Das grosse Excel-Berater-Komplettpaket - jetzt zum Sonderpreis! + DOWNLOAD-TIPP: Schneller Zugriff auf die wichtigsten Arbeitsmappen + TECHINFO: Inhalte einer MySQL-Datenbank nach Excel exportieren EXCEL-TIPP DER WOCHE + Tipps rund um Geburtstage und Altersberechnung EXCEL-HELPLINE + Alle Dateien eines Ordners in einer Excel-Tabelle ausgeben + Anzeige von Nullwerten unterdruecken NEWSLETTER-TIPP + Access-Tipps kostenlos per E-Mail Abonnementverwaltung und Copyright +++ EXCEL-NEWS & DOWNLOADS +++ SPOTLIGHT: Das grosse Excel-Berater-Komplettpaket - jetzt zum Sonderpreis! --------------------------------------------------- Die aktuelle Auflage der Excel-Berater Archiv-CD-ROM bietet jetzt noch einen Jahrgang mehr: insgesamt befinden sich auf der CD damit drei komplette Archive des Excel-Beraters zum absoluten Sonderpreis. Sie erhalten sowohl die Excel-Berater Archive 2000 und 2001 als auch das aktuelle Archiv 2002 in einem Paket. Die ultimative Informationsquelle fuer alle Excel-Anwender vom Einsteiger bis zum Profi bietet: + Jetzt ueber 2.000 Seiten mit Tipps, Praxisinformationen und Problemloesungen zu Excel im praktischen PDF-Format auf CD-ROM + Bonus: Der Excel-Berater Manager mit ueber 40 Excel- Erweiterungen wird direkt in Excel integriert und steht Ihnen per Mausklick zur Verfuegung + Viele weitere Beispiele, Praxisloesungen, Vorlagen, Kalkulationsmodelle und Makros mit nuetzlichen neuen Funktionen + Alle Themen im Volltext mit Abbildungen samt Beispieldateien + Durchsuchen Sie das Archiv komfortabel im Volltext oder nach einem Index Sie finden auf der CD-ROM Hunderte von Tipps und Praxisloesungen, die Sie fuer Ihre taegliche Arbeit mit Excel nutzen koennen! Das grosse Excel-Berater-Komplettpaket gibt es exklusiv bei SmartTools Publishing zum Aktionspreis mit ueber 30% Rabatt: http://www.add-in-world.com/katalog/exb-komplettpaket/ DOWNLOAD-TIPP: Schneller Zugriff auf die wichtigsten Arbeitsmappen ---------------------------------------------------- Kennen Sie das ARBEIT-Menue aus Word? Viele Anwender nutzen es, um dort die Dokumente aufzunehmen, mit denen Sie immer wieder arbeiten. Danach genuegt dann ein einziger Mausklick, um die entsprechende Datei jederzeit direkt und ohne Umwege aufzurufen. In Excel fehlt eine entsprechende Funktion, aber mit SmartTools QuickStart koennen Sie sie jetzt endlich nachruesten: Nach der automatischen Installation steht Ihnen eine kleine Symbolleiste zur Verfuegung, ueber die Sie Ihre wichtigsten Excel-Arbeitsmappen in Zukunft jederzeit abrufen koennen. Der Einsatz von SmartTools QuickStart ist denkbar einfach: Immer dann, wenn Sie mit einer Arbeitsmappe arbeiten, die Sie in Ihre persoenliche "Favoriten-Liste" aufnehmen moechten, klicken Sie auf das Plus-Symbol. SmartTools QuickStart fuegt daraufhin den Namen der aktuellen Datei in die Liste ein und speichert im Hintergrund den kompletten Pfad. Wenn Sie spaeter eine der aufgenommenen Dateien aufrufen moechten, klicken Sie einfach den gewuenschten Eintrag in der Liste an und SmartTools QuickStart oeffnet die entsprechende Arbeitsmappe; und zwar unabhaengig vom Speichertort und vom aktuellen Verzeichnis. SmartTools QuickStart fuer Excel koennen Sie auf der folgenden Seite kostenlos herunterladen: http://www.add-in-world.com/katalog/xl-quickstart/ TECHINFO: Inhalte einer MySQL-Datenbank nach Excel exportieren --------------------------------------- Dynamische Informationen im Web werden haeufig auf Basis einer MySQL-Datenbank zur Verfuegung gestellt. Moechten Sie Ihren Anwendern einige Daten bei Bedarf als Excel- Arbeitsmappe zum Download anbieten, finden Sie dazu ein fertiges PHP-Script unter der folgenden Adresse: http://www.phpfreaks.com/tutorials/114/0.php +++ EXCEL-TIPP DER WOCHE +++ Tipps rund um Geburtstage und Altersberechnung ---------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 Wir haben uns in Excel Weekly bereits mehrfach mit Berechnungen rund um Datumsangaben beschaeftigt. Ein Thema, das aber immer wieder in der Helpline auftaucht, betrifft den Umgang mit Geburtstagen und Altersberechnungen. Wir moechten Ihnen im Folgenden daher einige nuetzliche Tipps geben, um effektiver mit Geburtstagslisten und aehnlichen Tabellen zu arbeiten. Das grundsaetzliche Problem mit Geburtstagen in Excel besteht darin, dass Sie sie nicht ohne weiteres sinnvoll sortieren koennen, um eine Geburtstagsliste zu erstellen. Wenn Sie den Geburtstag als Sortierkriterium verwenden, entspricht die Reihenfolge lediglich dem Alter der entsprechenden Personen und nicht der Abfolge der Geburtstage waehrend des Jahres. Der "12.12.1956" erscheint also logischerweise vor dem "3.1.1967". Mit einigen kleinen Tricks koennen Sie die Geburtstagsliste aber dennoch richtig nutzen. Die erste Moeglichkeit besteht darin, einen aktuellen Geburtstag durch eine entsprechende Formatierung hervorzuheben: 1. Markieren Sie die Zellen, in die Sie die Geburtstage eingegeben haben. 2. Waehlen Sie das Menue FORMAT-BEDINGTE FORMATIERUNG an. 3. Markieren Sie in der ersten Dropdown-Liste den Eintrag "Formel ist". 4. Geben Sie die folgende Formel ein (ersetzen Sie dabei C5 durch den Bezug der ersten markierten Zelle mit Geburtsdaten): =HEUTE()-DATUM(JAHR(HEUTE());MONAT(C5);TAG(C5))=0 5. Klicken Sie auf FORMAT und legen Sie die Formatierung fest, mit der ein anstehender Geburtstag hervorgehoben werden soll. Anbieten wuerde sich beispielsweise ein roter Zellhintergrund. 6. Bestaetigen Sie die Einstellungen mit OK und verlassen Sie das Dialogfeld "Bedingte Formatierung". In Zukunft genuegt es, die Geburtstagsliste einmal am Tag aufzurufen. Die hervorgehobene Markierung zeigt automatisch, welchem Kunden, Freund oder Verwandten Sie heute gratulieren muessen. Sie koennen diese Technik noch erweitern, wenn Sie beispielsweise drei Tage vor dem Geburtstag vorgewarnt werden moechten. Fuegen Sie dann im Dialog "Bedingte Formatierung" mit HINZUFUEGEN ein weiteres Kriterium hinzu. Waehlen Sie eine andere Formatierung aus (beispielsweise einen gruenen Hintergrund) und verwenden Sie die folgende Formel: =HEUTE()-DATUM(JAHR(HEUTE());MONAT(C5);TAG(C5))=3 Moechten Sie die Liste dennoch so sortieren, dass die Geburtstage in der richtigen Reihenfolge angezeigt werden, koennen Sie sich mit einer Zusatzspalte behelfen. In dieser Spalte berechnen Sie ganz einfach mit der folgenden Formel das Geburtstagsdatum im aktuellen Jahr: =DATUM(JAHR(JETZT());MONAT(C5);TAG(C5)) Ersetzen Sie dabei C5 durch die Zelle mit dem ersten Geburtstag und kopieren Sie die Formel in die uebrigen Zeilen der Liste. Danach fuehren Sie eine Sortierung nach dieser Spalte durch und schon haben Sie eine Geburtstagsliste, die Sie beispielsweise ausdrucken und neben den Kalender haengen koennen. Bevorzugen Sie stattdessen die elektronische Version der Geburtstagsliste, gibt es noch eine interessantere Loesung: Erstellen Sie eine Zusatzspalte, in der fuer jede Person jeweils der naechste Geburtstag berechnet wird. Nach dem Geburtstag des aktuellen Jahres wird also der Geburtstag des naechsten Jahres berechnet, so dass beim Sortieren der Liste in jedem Fall der naechste anstehende Geburtstag oben steht. Die Formel dazu sieht folgendermassen aus: =WENN(DATUM(JAHR(HEUTE());MONAT(C5);TAG(C5)) dtStichtag Then funcLebensalter = DateDiff("yyyy", dtGeburtsdatum, _ dtStichtag) - 1 Else funcLebensalter = DateDiff("yyyy", dtGeburtsdatum, _ dtStichtag) End If End Function Nachdem Sie die Funktion im VBA-Editor eingegeben haben, koennen Sie sie in Ihren Tabellen nutzen, um das Alter einer Person auf Basis des Geburtstagsdatums zu berechnen. Dabei wird beruecksichtigt, ob der Geburtstag im aktuellen Jahr bereits statt gefunden hat oder nicht. Zum Beispiel (C5 ist die Zelle mit dem Geburtsdatum): =funcLebensalter(C5) Interessant an der Funktion ist der zweite Parameter, den Sie auf Wunsch verwenden koennen: Standardmaessig wird das Alter auf Basis des aktuellen Datums berechnet, aber Sie koennen auch einen bestimmten Stichtag angeben. Zum Beispiel: =funcLebensalter(C5;DATWERT("14.9.2005")) Diese Formel liefert Ihnen das Alter der Person am 14.9.2005 (Geburtstdatum in C5). ++++++++++++++++ ANZEIGE ++++++++++++++++ Outlook-Daten komfortabel und voll automatisch sichern ------------------------------------------------------ Was wuerde passieren, wenn Ihre Outlook-Daten ploetzlich und unerwartet verloren gehen? Sie verlieren meistens nicht nur E-Mails, sondern auch wichtige Adressen, Termine aus dem Kalender, Aufgaben und Notizen. Hinzu kommt ein erheblicher Einrichtungsaufwand, um alle Filter und Regeln, Kategorien, Signaturen, Symbolleisten und E-Mail-Konten so einzurichten, dass Sie wieder wie gewohnt arbeiten koennen. Mit dem brandneuen SmartTools Outlook Backup haben Sie diese Probleme zuverlaessig im Griff: + Machen Sie sich nie wieder sorgen um Ihre Daten, denn der Backup-Vorgang laesst sich mit wenig Aufwand automatisieren. + SmartTools Outlook Backup sichert nicht nur die Datendatei, sondern kuemmert sich ausserdem um Ihre Outlook-Symbolleiste, Kategorien, E-Mail- Kontoeinstellungen, Signaturen, Filter und Regeln. + Bei einem Datenverlust sparen Sie viele Stunden Konfigurationsaufwand, denn alle gesicherten Informationen lassen sich mit wenigen Mausklicks wiederherstellen. SmartTools Outlook Backup ist ab sofort verfuegbar und kann zur Zeit mit 10% Einfuehrungsrabatt bestellt werden: http://www.add-in-world.com/katalog/outlook-backup/ +++++++++++++++++++++++++++++++++++++++++ +++ EXCEL-HELPLINE +++ Haben Sie Fragen zu Excel 97, 2000, 2002/XP oder 2003? Dann wenden Sie sich mit einer genauen Problembeschreibung (bitte senden Sie keine Dateien!) an: mailto:excel-helpline@smarttools.de Unser Redaktionsteam waehlt in jeder Ausgabe Fragen aus und beantwortet sie an dieser Stelle im Newsletter. Alle Dateien eines Ordners in einer Excel-Tabelle ausgeben ---------------------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Kann man die Dateien eines Festplattenordners als Verzeichnisliste in eine Excel-Tabelle einfuegen? Nach Moeglichkeit sollen neben dem Dateinamen auch die Dateigroesse sowie Erstelldatum und letztes Aenderungsdatum ausgegeben werden. E. Berger ANTWORT: Fuer die Aufgabe benoetigen Sie ein VBA-Makro. Wie ein solches Makro aussehen koennte, demonstriert das folgende Beispiel: 1. Wechseln Sie aus Excel mit Alt+F11 in den Visual Basic- Editor. 2. Rufen Sie im Menue EINFUEGEN den Befehl MODUL auf. 3. In das daraufhin geoeffnete Codefenster geben Sie die Prozedur zur Ausgabe der Verzeichnisliste ein: Sub Dateiliste() Dim strOrdner As String Dim fso As Object Dim fsOrdner As Object Dim fsDateien As Object, fsDatei As Object Dim wb As Workbook, sh As Worksheet Dim i As Long strOrdner = "C:\Windows" Set fso = CreateObject("Scripting.FileSystemObject") If Not fso.FolderExists(strOrdner) Then MsgBox "Ordner nicht vorhanden." Exit Sub Else Set fsOrdner = fso.GetFolder(strOrdner) Set fsDateien = fsOrdner.Files End If Set wb = Application.Workbooks.Add Set sh = wb.Worksheets(1) With sh .Cells(3, 1) = "Dateiname" .Cells(3, 2) = "Dateigroesse" .Cells(3, 3) = "Dateityp" .Cells(3, 4) = "Erstelldatum" .Cells(3, 5) = "Letzter Zugriff" .Cells(3, 6) = "Letzte Aenderung" .Rows(3).Font.Bold = True On Error Resume Next i = 4 If fsDateien.Count > 0 Then For Each fsDatei In fsDateien .Cells(i, 1) = fsDatei.Name .Cells(i, 2) = fsDatei.Size .Cells(i, 3) = fsDatei.Type .Cells(i, 4) = fsDatei.DateCreated .Cells(i, 5) = fsDatei.DateLastAccessed .Cells(i, 6) = fsDatei.DateLastModified i = i + 1 Next fsDatei Else .Cells(i, 1) = "Keine Dateien" End If On Error GoTo 0 .Columns("A:F").AutoFit .Cells(1, 1) = "Inhalt von " & strOrdner .Cells(1, 1).Font.Bold = True .Cells(1, 1).Font.Size = 12 End With Set fsDatei = Nothing Set fsDateien = Nothing Set fsOrdner = Nothing Set fso = Nothing Set sh = Nothing Set wb = Nothing End Sub 4. In der vorliegenden Fassung erhalten Sie beim Ausfuehren des Makros ein Inhaltsverzeichnis des Ordners "C:\Windows".Wenn es diesen Ordner auf Ihrem System gar nicht gibt oder wenn Sie lieber den Inhalt eines anderen Ordners ausgeben wollen, passen Sie die Pfadangabe in der Makrozeile strOrdner = "C:\Windows" an Ihre Systemumgebung an. 5. Verlassen Sie den Visual Basic-Editor ueber das Menue DATEI und den Befehl SCHLIESSEN UND ZURUECK ZU MICROSOFT EXCEL. Wenn Sie das Makro gleich ausprobieren wollen, druecken Sie im Excel-Fenster die Tastenkombination Alt+F8. Damit oeffnen Sie ein Dialogfenster mit einer Liste verfuegbarer Makros. Doppelklicken Sie auf den Makronamen "Dateiliste". Das Makro legt daraufhin eine neue Arbeitsmappe an und fuegt die Dateinamen inklusive Dateigroesse, Dateityp und verschiedener Datumsangaben in das erste Tabellenblatt dieser Mappe ein. Der Komfort des Makros laesst sich sicherlich noch verbessern. Deshalb hier ein paar Hinweise zur generellen Funktionsweise: Ganz am Anfang uebergeben Sie den Ordner, dessen Dateien in eine Excel-Tabelle eingetragen werden sollen, an die Variable "strOrdner"; hier in Form des festen Textwertes "C:\Windows". Auf die Ordnerinhalte greifen Sie anschliessend mit Hilfe des "FileSystemObject" zurueck. Den Objektbezug auf den Ordner erhalten Sie mit der "GetFolder"-Methode und die Dateien des Ordners finden Sie in der "Files"-Auflistung des Ordnerobjekts. Als naechstes bereiten Sie die neue Arbeitsmappe vor, in der die Dateiliste ausgegeben werden soll. In die dritte Zeile des ersten Tabellenblatts schreiben Sie die Spaltenueberschriften "Dateiname", "Dateigroesse" usw. Anschliessend durchlaufen Sie in einer For-Schleife alle Dateien des Ordners. Dabei werden Name, Groesse ("Size"), Dateityp ("Type") usw. in die Excel-Tabelle eingetragen. Am Ende nehmen Sie noch kleinere Schoenheitskorrekturen an der Excel-Tabelle vor, indem Sie die Breite der Spalten A bis F auf die optimale Breite einstellen und in der ersten Zeile eine Ueberschrift mit Hinweis auf den durchsuchten Ordner einfuegen. Und um den durch die Objektvariablen belegten Arbeitsspeicher wieder frei zu geben, setzen Sie in den letzten Makrozeilen alle Objekte auf "Nothing". Aufbauend auf diesen Hinweisen koennen Sie den Makrocode beliebig erweitern, - beispielsweise um einen Ordner auf Unterverzeichnisse zu pruefen (Unterverzeichnisse erhalten Sie ueber die "SubFolders"-Auflistung des Ordnerobjekts). ++++++++++++++++ ANZEIGE ++++++++++++++++ Professionelle Budgetkontrolle in Excel --------------------------------------- Sind Sie dafuer verantwortlich, dass Budgetrahmen eingehalten werden? Die CD-ROM "Budgetkontrolle leicht gemacht" liefert dazu fertige Excel-Loesungen zum sofortigen Einsatz: + Komfortable Eingabemaske fuer die Einzelbetraege + Sofort starten: Geplante Betraege einfach eingeben und dann Ist-Kosten nach und nach erfassen. + Keine Ueberraschungen: Nach jeder Eingabe werden Abweichungen und andere Auswertungen angezeigt. Sie koennen rechtzeitig reagieren! + Fertige Diagramme zur perfekten Praesentation Ihres Budgets. + Alle Dateien ungeschuetzt zur individuellen Anpassung. Weitere Informationen und Screenshots: http://www.add-in-world.com/katalog/budgetkontrolle/ +++++++++++++++++++++++++++++++++++++++++ Anzeige von Nullwerten unterdruecken ----------------------------------- Versionen: Excel 97, 2000 und 2002/XP FRAGE: In meinem Kalkulationsmodell moechte ich die Anzeige von Nullen als Formelergebnis unterdruecken. Bisher erweitere ich die Formeln dazu immer um eine entsprechende WENN-Abfrage, aber geht es nicht einfacher? C. Schuerer ANTWORT: Fuer dieses Problem gibt es gleich mehrere Loesungswege: * Am einfachsten ist es, das Menue EXTRAS-OPTIONEN anzuwaehlen, auf die Registerkarte "Ansicht" zu wechseln und das Kontrollkaestchen NULLWERTE auszuschalten. Damit sind dann allerdings alle Nullen aus der kompletten Tabelle verschwunden. * Um das Ausblenden von Nullwerten nur auf bestimmte Zellen zu beschraenken, koennen Sie eine bedingte Formatierung einsetzen. Dazu markieren Sie die gewuenschten Zellen und waehlen das Menue FORMAT-BEDINGTE FORMATIERUNG an. Legen Sie dann die Bedingung "Zellwert ist gleich 0" fest und setzen Sie die Schriftfarbe im FORMAT-Dialog auf "Weiss" (oder die von Ihnen verwendete Farbe fuer den Zellhintergrund). * Eine weitere Alternative waere die Anpassung des Zahlenformats. Dazu muss man wissen, dass sich jedes Zahlenformat in Excel in vier Bereiche teilt: ;;; Um Nullwerte auszublenden, muessen Sie lediglich dafuer sorgen, dass der Abschnitt fuer Nullwerte leer ist. Dazu loeschen Sie zum Beispiel die komplette Definition hinter dem zweiten Semikolon: _-* #.##0 DM_-;-* #.##0 DM_-;;_-@_- Sind in dem Zahlenformat nur Formate fuer positive und negative Werte vorhanden, reicht es, ein zweites Semikolon ohne weitere Zeichen an das Ende der Definition zu haengen: #.##0 DM;-#.##0 DM; +++ NEWSLETTER-TIPP +++ Access-Tipps kostenlos per E-Mail --------------------------------- SmartTools Access Weekly ist ein kostenloser woechentlicher Newsletter mit interessanten Tipps fuer Anwender und Entwickler von Access 97, 2000, 2002/XP und 2003: + Aktuelle Informationen ueber Bugs und Patches + Helpline fuer Ihre Fragen zu Access + Tipps fuer effektivere Arbeitstechniken + Undokumentierte Parameter und Funktionen + Nuetzliche VBA-Prozeduren + Download-Links zu Access-Erweiterungen Bestellen Sie jetzt Ihr kostenloses Abo: http://www.smarttools.de/newsletter/access-weekly.newsletter.php +++ ABONNEMENTVERWALTUNG +++ Um SmartTools Excel Weekly abzubestellen, senden Sie einfach das Kommando "unsubscribe" an die Adresse mailto:excel-weekly-text-request@smarttools.de Bitte beachten Sie, dass das Kommando im Nachrichtentext stehen muss (nicht im Betreff) und Sie keine Mail im HTML- Format senden duerfen. Weitere Informationen zur Verwaltung der Liste erhalten Sie mit dem Kommando "help" an dieselbe Adresse. ------------------------------------------------------ (c) 2005 by SmartTools Publishing Thomas Tai Luisenstrasse 52, 49565 Bramsche Telefon: 05461/9952-0, Fax: 05461/2609 E-Mail: mailto:excel-weekly@smarttools.de Internet: http://www.smarttools.de/ --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.859 / Virus Database: 585 - Release Date: 14.02.2005