Von: excel-weekly-text-request@cgn.smarttools.de im Auftrag von SmartTools Publishing [excel-weekly-text-reply@smarttools.de] Gesendet: Dienstag, 15. März 2005 03:25 An: Excel Weekly Abonnenten Betreff: SmartTools Excel Weekly vom 15.3.2005 ----------------------------------------------------------- SmartTools Excel Weekly vom 15.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: Excel-Tabellen automatisch vergleichen und zusammenfuehren + DOWNLOAD-TIPP: Die kostenlosen Druckertools fuer Excel EXCEL-TIPP DER WOCHE + Datumsberechnungen per Mausklick oder auf Tastendruck EXCEL-HELPLINE + Fortlaufende Nummerierung trotz Filterfunktion + NACHTRAG: Zeitangaben in Dezimalwerte umwandeln + NACHTRAG: Fehlerwerte in Tabellen unterdruecken NEWSLETTER-TIPP + Word-Tipps kostenlos per E-Mail Abonnementverwaltung und Copyright +++ EXCEL-NEWS & DOWNLOADS +++ SPOTLIGHT: Excel-Tabellen automatisch vergleichen und zusammenfuehren ------------------------------------------ Nutzen auch Sie das Excel-Tool, auf das Tausende von Excel- Anwendern vom Kleinunternehmer bis hin zu Grossfirmen, Banken und Versicherungen seit Jahren nicht mehr verzichten wollen: den Synkronizer 8.0 Premium. Der Synkronizer 8.0 Premium liefert genau die Funktionen, die Microsoft in Excel vergessen hat: Er kann Ihre Kalkulationsmodelle, Tabellen, Listen und Datenbanken in Excel voll automatisch vergleichen, Unterschiede markieren und auf Wunsch zu einer einzigen aktuellen Version zusammenfuehren. Sparen Sie kuenftig viele Stunden muehseliger Kleinarbeit, denn der Synkronizer 8.0 Premium wird Ihnen bei der taeglichen Arbeit mit Excel immer wieder eine unentbehrliche Hilfe sein. Zum Beispiel: + Mehrere Mitarbeiter haben unterschiedliche Versionen der gleichen Tabelle bearbeitet. Der Synkronizer 8.0 Premium liefert Ihnen im Handumdrehen eine einzige Tabelle, die alle Aenderungen der anderen Versionen enthaelt. + Sie muessen ein Kalkulationsmodell ueberarbeiten, finden aber zwei Versionen auf Ihrer Festplatte. Der Synkronizer 8.0 Premium zeigt Ihnen nach wenigen Mausklicks wo die Unterschiede der beiden Versionen liegen. + Sie arbeiten mit einer Preisliste und ein Kollege schickt Ihnen eine Liste der Artikel, bei denen sich etwas geaendert hat. Der Synkronizer 8.0 Premium pflegt die neuen Informationen automatisch in Ihre Gesamtliste ein und bringt sie auf den neuesten Stand. + Ihnen liegen mehrere Listen mit Adressen vor, an die Sie ein Mailing verschicken moechten. Mit dem Synkronizer 8.0 Premium erstellen Sie mit wenig Aufwand eine einzige Adressliste ohne Duplikate! Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/synkronizer/ DOWNLOAD-TIPP: Die kostenlosen Druckertools fuer Excel ------------------------------------------------------ Mit den Drucker-Tools bieten wir Ihnen eine Sammlung von nuetzlichen Tools und Assistenten rund um den Ausdruck von Tabellen zur Verfuegung. Nach der automatischen Installation koennen Sie ueber eine neue Symbolleiste auf verschiedene nuetzliche Funktionen mit einem einzigen Mausklick zugreifen. Zum Beispiel: * Markierung drucken: Waehlen Sie einfach einen Tabellenbereich aus und klicken Sie auf dieses Symbol. Excel druckt dann ohne Umwege die aktuelle Markierung. * Tabellen drucken mit Komfort: So haben Sie den Ausdruck Ihrer Arbeitsmappe optimal im Griff. In einer Liste werden Ihnen alle verfuegbaren Tabellen angeboten und Sie koennen schnell und komfortabel per Mausklick bestimmen, welche davon ausgedruckt werden sollen. * Arbeitsmappe drucken: Ein Mausklick genuegt und voll automatisch werden alle Tabellen der aktuellen Arbeitsmappe gedruckt. * Kopien: Diese Schaltflaeche oeffnet ein Dropdown-Menue, ueber das Sie den Ausdruck der aktuellen Arbeitsmappe mit 2 bis 5 Kopien direkt starten koennen. Ein weiterer Eintrag MEHR erlaubt die Eingabe einer abweichenden Anzahl Kopien. * Verzeichnis drucken: Mit Hilfe dieser Funktion koennen Sie alle Arbeitsmappen, die in einem Verzeichnis gespeichert sind, in einem Durchgang ausdrucken. Das Add-In zeigt eine Verzeichnisauswahl an, ueber die Sie das gewuenschte Verzeichnis lokalisieren. Ein Klick auf OK druckt die darin enthaltenen Arbeitsmappen dann der Reihe nach aus. Sie finden den kostenlosen Download unter: http://www.add-in-world.com/katalog/xldruckertools/ +++ EXCEL-TIPP DER WOCHE +++ Datumsberechnungen per Mausklick oder auf Tastendruck ----------------------------------------------------- Versionen: Excel 97, 2000, 20002/XP und 2003 Wenn Sie Termine in Excel verwalten gibt es immer wieder Situationen, in denen Sie Datumsangaben anpassen muessen. Der Liefertermin wird um einen Tag vorgezogen, der Abgabetermin um eine Woche verschoben oder Sie moechten die Terminliste aus dem letzten Jahr ganz einfach auf das neue Jahr anpassen. In diesem Tipp der Woche moechten wir Ihnen einige kleine VBA-Makros vorstellen, mit deren Hilfe Sie Datumsangaben in Zukunft ganz einfach neu setzen koennen. Erhoehen Sie das Datum der aktuellen Zelle beispielsweise mit jedem Tastendruck um einen Tage, eine Woche, einen Monat oder ein Jahr. Bei Bedarf lassen sich auch Bereiche markieren und gleich mehrere Termine in einem Arbeitsschritt neu ermitteln. Sollten Sie bisher keine Erfahrung mit dem Einsatz von Makros in VBA haben, werden Sie die folgenden Anweisungen ebenfalls umsetzen koennen, aber wir empfehlen zusaetzlich die Lektuere der beiden kostenlosen Techinfos: Excel-Makros Schritt fuer Schritt erfassen: http://www.add-in-world.com/katalog/techinfo-makro/ Excel-Makros optimal verwalten: http://www.add-in-world.com/katalog/xlmakro/ Die wichtigste VBA-Funktion zur Durchfuehrung der gewuenschten Berechnungen ist DateAdd mit der folgenden Syntax: DateAdd(,,) Dabei uebergeben Sie zunaechst mit eine Zeichenkette, die die gewuenschte Berechnungsart angibt. Wir verwenden dabei die folgenden Einstellungen: * "yyyy" = Jahr * "q" = Quartal * "m" = Monat * "ww" = Woche * "d" = Tag Der Parameter bestimmt dann die Anzahl der gewuenschten Jahre, Quartale, Monate etc. wobei Sie mit positiven Werten vor und mit negativen Werten zurueck rechnen koennen. ist schliesslich das Datum, auf dessen Basis die Berechnung durchgefuehrt werden soll. Um nun per VBA, das Datum der aktuellen Zelle um einen ganzen Monat in die Zukunft zu verschieben, genuegt die folgende Zeile: ActiveCell.Value = DateAdd("m", 1, ActiveCell.Value) Auf dieser Basis haben wir die folgende Prozedur entwickelt, die noch ein wenig mehr kann: Sie rufen sie einfach mit der gewuenschten Einheit (Tag, Woche, Monat, Quartal oder Jahr werden repraesentiert durch 1, 2, 3 usw.) sowie der Zahl fuer die Berechnung auf und der Wert der aktuellen Zelle wird entsprechend neu gesetzt: Sub procDatumsRechner(intEinheit As Integer, _ intAnzahl As Integer) Dim varStartDatum As Variant Dim strEinheit As String Select Case intEinheit Case 1 strEinheit = "d" 'Tag Case 2 strEinheit = "ww" ' Woche Case 3 strEinheit = "m" 'Monat Case 4 strEinheit = "q" 'Quartal Case 5 strEinheit = "yyyy" 'Jahr Case Else MsgBox "Ungueltiger Parameter fuer Einheit", _ vbCritical, "Excel Weekly Datumsrechner" Exit Sub End Select varStartDatum = ActiveCell.Value If IsDate(varStartDatum) Then ActiveCell.Value = DateAdd(strEinheit, _ intAnzahl, varStartDatum) End If End Sub Wichtig dabei ist, dass wir noch eine kleine Fehlerpruefung eingebaut haben. Der Inhalt der aktuellen Zelle wird zunaechst der Variablen "varStartDatum" zugewiesen, um dann ueber "IsDate" feststellen zu koennen, ob die aktuelle Zelle ueberhaupt ein Datum enthaelt. Ist die Zelle leer oder enthaelt sie eine Zeichenkette bzw. eine beliebige Zahl, die kein Datum ist, passiert nichts. Andernfalls wird die entsprechende Berechnung mit DateAdd durchgefuehrt und das Ergebnis zurueck geschrieben. Auf Basis dieser Prozedur koennen Sie Excel nun ganz einfach so erweitern, dass das Datum der aktuellen Zelle auf Tastendruck um einen Tag hoch- oder heruntergezaehlt wird: 1. Geben Sie zunaechst die oben gezeigte Prozedur "procDatumsrechner" ein. 2. Erfassen Sie in demselben Modul die folgenden Prozeduren, die den Datumsrechner jeweils mit Parametern zum Addieren bzw. Subtrahieren von einem Tag aufrufen: Sub procPlusTag() procDatumsRechner 1, 1 End Sub Sub procMinusTag() procDatumsRechner 1, -1 End Sub 3. Waehlen in Excel das Menue EXTRAS-MAKRO-MAKROS an und markieren Sie den Eintrag "procPlusTag". 4. Klicken Sie auf OPTIONEN und geben Sie unter TASTENKOMBINATION den gewuenschten Buchstaben fuer die die Strg-Tastenkombination ein, die Sie verwenden moechten. Zum Beispiel: Strg+Q. 5. Nachdem Sie Ihre Einstellung mit OK bestaetigt haben, markieren Sie "procMinusTag", klicken auf OPTIONEN und legen eine weitere Tastenkombination fest. Zum Beispiel: Strg+W. 6. Nachdem Sie mit OK bestaetigt und den Makro-Dialog mit ABBRECHEN verlassen haben, koennen Sie in Zukunft jedes Datum in einer Tabelle mit den Tasten Strg+Q und Strg+W jeweils um einen Tag herauf- oder heruntersetzen. Nach dem gleichen Prinzip koennen Sie nun kleine Makros und Tastenkombinationen fuer jede Datumsberechnung erstellen, die Sie benoetigen. Zum Beispiel zum Ermitteln der naechsten oder vorhergehenden Woche: Sub procPlusWoche() procDatumsRechner 2, 1 End Sub Sub procMinusWoche() procDatumsRechner 2, -1 End Sub Sollten Sie lieber mit der Maus anstelle der Tastatur arbeiten, koennen Sie die Prozeduren natuerlich auch entsprechenden Symbolen zuordnen, indem Sie das Dialogfeld EXTRAS-ANPASSEN (Registerkarte "Befehle") verwenden. Moechten Sie schliesslich nicht nur einzelne Zellen, sondern gleich einen kompletten Bereich mit Datumsberechnungen anpassen, koennen Sie Makros wie das folgende einsetzen: Sub procDatumsbereichAnpassen() Dim rngAktiveZelle As Range For Each rngAktiveZelle In Selection rngAktiveZelle.Activate procDatumsRechner 5, 1 Next End Sub Wenn Sie mehrere Zellen markieren und dieses Makro starten, werden die ausgewaehlten Datumsangaben jeweils um ein Jahr hochgezaehlt. Eine Datumsliste aus dem letzten Jahr, in der Sie jeweils den 1. Tag eines Monats eingegeben haben, koennen Sie so beispielsweise in einem einzigen Arbeitsschritt auf das aktuelle Jahr anpassen. ++++++++++++++++ ANZEIGE ++++++++++++++++ Kalender, Kontakte und E-Mails ganz einfach im Team nutzen ---------------------------------------------------------- Mit OLXTeamOutlook, der neuesten Generation intelligenter Team-Software fuer Outlook 97, 98, 2000, 2002/XP und 2003 nutzen Sie Ihre Outlook-Daten in Zukunft noch effizienter: + Nutzen Sie ab sofort Ihre Termine, Aufgaben, Kontakte, E- Mails und Notizen aus Outlook gemeinsam mit anderen Anwendern im Netzwerk. + Richten Sie mit wenigen Mausklicks oeffentliche Ordner fuer Gruppentermine, Raumbelegungen, gemeinsamen Firmenadressen usw. ein oder geben Sie einen oder mehrere Ihrer persoenlichen Ordner mit Terminen, Kontakten, E- Mails etc. fuer andere Anwender frei. + Alle Aenderungen und Neueingaben in den gemeinsamen Ordnern sind immer sofort ohne Verzoegerung in den Ordnern der anderen Teilnehmer sichtbar. + Im nuetzlichen Gruppenkalender sehen Sie jederzeit die Termine aller freigegebenen Kalender im Wochenueberblick. + Sie benoetigen keinen Exchange-Server, sondern lediglich ein freigegebenes Verzeichnis im Netzwerk, auf das alle beteiligten Anwender zugreifen koennen. Dieses Verzeichnis muss sich noch nicht einmal auf einem Server befinden! Jeder beliebige Rechner in Ihrem Netzwerk kann diese Aufgabe uebernehmen + Der Datenabgleich mit PDA's und Handy's ist weiterhin ohne Probleme moeglich. OLXTeamOutlook ist komplett in Outlook integriert. Unterstuetzt werden alle Versionen. Also: Outlook 97, 98, 2000, 2002/XP und 2003. Sie koennen innerhalb eines Netzwerkes auf Wunsch sogar unterschiedliche Outlook- Versionen nutzen! Weitere Informationen zu dieser brandneuen Outlook- Erweiterung finden Sie unter: http://www.add-in-world.com/katalog/olxteamoutlook/ +++++++++++++++++++++++++++++++++++++++++ +++ 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. Fortlaufende Nummerierung trotz Filterfunktion ---------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Ich arbeite mit Listen in Excel, in die Daten aus einer Access-Datenbank einfliessen. Die Daten werden staendig aktualisiert, was dazu fuehrt, dass Zeilen hinzugefuegt, geloescht oder verschoben werden. Nach einem Aktualisierungsvorgang sollen die Informationen in der Excel-Tabelle mit Hilfe von AutoFiltern ausgewertet werden. Bis dahin funktioniert auch alles. Darueber hinaus will ich den Zeilen aber auch eine fortlaufende Nummerierung geben, die sich immer nur auf die gerade angezeigten Daten bezieht. Die Zeilen muessten also nach Anwendung eines Filters automatisch neu nummeriert werden. An diesem Punkt komme ich nicht weiter. Koennen Sie mir eine Loesung verraten, die ausschliesslich die gerade sichtbaren Zeilen durchnummeriert? H. Nieke ANTWORT: Diese Aufgabe laesst sich in einer zusaetzlichen Tabellenspalte mit Hilfe einer speziellen TEILERGEBNIS- Formel loesen. Damit berechnen Sie jeweils die Anzahl der sichtbaren Eintraege bis zur aktuellen Datenbankzeile, was zu einer fortlaufenden Nummerierung fuehrt. Deutlicher wird das Prinzip an einem Beispiel: Angenommen, Sie haetten die aktuellen Access-Daten in die Zeilen 6 bis 100 eines Excel-Tabellenblatts eingefuegt. Die entsprechenden Spaltenueberschriften (Feldnamen) stehen in Zeile 5. Fuer eine fortlaufende Nummerierung fuegen Sie zuerst eine neue Spalte ein. Alternativ koennen Sie auch eine freie Spalte links oder rechts neben den eigentlichen Daten nutzen. Darin geben Sie in der Zeile des ersten Datensatzes (im Beispiel: Zeile 6) eine Formel nach folgendem Muster ein: =TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($[1. Wertezelle];0;0;ZEILE()-[Abstand zw. Datensatz 1 und Zeile 1];1)) [1. Wertezelle] steht fuer die Adresse einer Zelle im ersten Datensatz. Die Zelle muss sich in einer Spalte befinden, die in jedem Datensatz mit einem Wert belegt ist. Ansonsten kann die Anzahl der Eintraege nicht korrekt berechnet werden und die fortlaufende Nummerierung schlaegt fehl. Die Zelladresse muss als absoluter Bezug eingegeben werden - deshalb das vorangestellte $-Zeichen in der Formelsyntax. [Abstand zw. Datensatz 1 und Zeile 1] entspricht der Zeilendifferenz zwischen dem ersten Datensatz und der ersten Tabellenzeile. Wenn, wie im oben genannten Beispiel, die Daten in Zeile 6 beginnen, tragen Sie als [Abstand zw. Datensatz 1 und Zeile 1] den Wert 5 (= 6 minus 1) ein. Nehmen Sie an, im genannten Beispiel waeren in Spalte D Artikelbezeichnungen aus der Datenbank uebernommen worden. Sie wissen, dass jeder Datensatz eine solche Bezeichnung enthaelt. Somit eignet sich Spalte D zur Ermittlung der Datensatzanzahl, wie sie von der TEILERGEBNIS-Formel benoetigt wird. Die fortlaufende Nummerierung, die sich auch nach Einschalten eines AutoFilters anpasst, erhalten Sie dann, indem Sie in Zeile 6 der hinzugefuegten Nummerierungsspalte folgende Formel eingeben: =TEILERGEBNIS(3;BEREICH.VERSCHIEBEN($D$6;0;0;ZEILE()-5;1)) Anschliessend kopieren Sie die Formel in alle nachfolgenden Datensaetze - im Beispiel in die darunter befindlichen Zellen bis Zeile 100. Sie sehen sofort eine fortlaufende Nummerierung. Das Besondere ist aber, dass Sie problemlos per DATEN-FILTER- AUTOFILTER Ihre Datenbankauswertungen vornehmen koennen. Die Formelergebnisse passen sich automatisch so an, dass nur die sichtbaren Zeilen nummeriert werden. ++++++++++++++++ ANZEIGE ++++++++++++++++ Die komfortable Urlaubs-Datenbank fuer Access --------------------------------------------- Mit dieser Datenbank haben Sie die Fehlzeiten in Ihrer Firma oder Abteilung optimal im Griff: Urlaub, Krankheit, Sonderurlaub und sonstige Abwesenheiten lassen sich fuer jeden Mitarbeiter komfortabel mit wenigen Mausklicks erfassen. Wenn Sie mit dem einen oder anderen Detail nicht zufrieden sind, aendern Sie es einfach ab, denn Sie erhalten die Datenbank ungeschuetzt mit dem kompletten Sourcecode. Ab sofort steht Ihnen die brandneue Version 2.11 der Urlaubsdatenbank zur Verfuegung. Zu den wichtigsten Neuerungen zaehlen: + Voll netzwerkfaehig durch Front- und Backend-Loesung. Im Mehrplatzbetrieb fallen keine weiteren Kosten an! + Unterstuetzung von halben Abwesenheitstagen + Balken- und Mitarbeiterzahl auf 25 pro Abteilung erweitert + Erweiterte Druckfunktionen fuer uebersichtliche Berichte + Ausfuehrliches deutsches Handbuch Die Urlaubs-Datenbank fuer Access 97, 2000, 2002/XP und 2003 hat sich bereits hundertfach in der Praxis bewaehrt und ist nach der voll automatischen Installation sofort einsatzbereit: http://www.add-in-world.com/katalog/urlaubsdatenbank/ +++++++++++++++++++++++++++++++++++++++++ NACHTRAG: Zeitangaben in Dezimalwerte umwandeln ----------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 Vor einigen Wochen haben wir in einem QuickTipp die Formel =STUNDE(A1)+(MINUTE(A1)/60) vorgestellt, um Zeitangaben in entsprechende Dezimalwerte umzuwandeln. Aufgrund von mehreren Leseranfragen hier noch zwei Ergaenzungen: * Die vorgestellte Formel funktioniert immer dann, wenn es sich um normale Zeitangaben unter 24 Stunden handelt. Wenn Sie hoehere Werte wie 34:45 in Dezimalwerte umwandeln moechten, muessen Sie auch den "Tagesanteil" beruecksichtigen und stattdessen die folgende Formel nutzen: =TAG(A1)*24+STUNDE(A1)+(MINUTE(A1)/60) * Excel versucht nach der Eingabe einer Formel, das passende Zahlenformat zur korrekten Darstellung des Ergebnisses automatisch zu bestimmen. Da Sie sich in unserem Fall auf eine Zelle mit einer Zeitangabe beziehen, geht Excel davon aus, dass auch das Ergebnis wiederum als Uhrzeit dargestellt werden soll. Damit das Ergebnis wie gewuenscht als Dezimalzahl dargestellt wird, muessen Sie daher zunaechst ein entsprechendes Zahlenformat zuweisen. NACHTRAG: Fehlerwerte in Tabellen unterdruecken ----------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 In einer vergangenen Ausgabe hatten wir gezeigt, wie Sie ueber eine Formel verhindern koennen, dass Fehlerwerte angezeigt werden, nur weil bestimmte Zellen noch nicht ausgefuellt wurden. Unser Leser G. Gehringer hat zu diesem Thema noch eine interessante Anregung: Um das Ausfuellen wichtiger Zellen zu unterstuetzen, definiert er fuer diese Zellen eine bedingte Formatierung, die dafuer sorgt, dass leere Zellen mit einer auffaelligen Farbe ausgefuellt sind. Die entsprechende Bedingung lautet einfach: ="" +++ NEWSLETTER-TIPP +++ Word-Tipps kostenlos per E-Mail ------------------------------- SmartTools Word Weekly ist unser erfolgreicher E-Mail- Newsletter, der Ihnen jede Woche kostenlose Tipps rund um Word 97, 2000, 2002/XP und 2003 liefert: + Undokumentierte Parameter und Funktionen + Berichte ueber Bugs, Patches und Sicherheitsluecken + Fertige Makros zur Automatisierung haeufiger Arbeitsvorgaenge + Perfekte Datensicherung Ihrer Dokumente + So nutzen Sie Word im Netzwerk + Helpline fuer Ihre Fragen zu Word Bestellen Sie jetzt Ihr kostenloses Abo: http://www.smarttools.de/newsletter/word-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