Von: excel-weekly-text-request@cgn.smarttools.de im Auftrag von SmartTools Publishing [excel-weekly-text-reply@smarttools.de] Gesendet: Montag, 20. September 2004 23:08 An: Excel Weekly Abonnenten Betreff: SmartTools Excel Weekly vom 21.9.2004 ----------------------------------------------------------- SmartTools Excel Weekly vom 21.9.2004 Jede Woche neu: Tipps, News und Problemloesungen rund um Microsoft Excel (c) 2004 by SmartTools Publishing ----------------------------------------------------------- +++ IN DIESER AUSGABE +++ EXCEL-NEWS & DOWNLOADS + SPOTLIGHT: Digitaler Zeitplaner 2005 auf CD-ROM kostenlos + DOWNLOAD-TIPP: Der komfortable Verknuepfungsmanager + NEWS: Wichtige Sicherheitsupdates fuer Office 2000, 2002/XP und 2003 EXCEL-TIPP DER WOCHE + Trendanalyse mit Excel-Funktionen, Teil 2 + QUICKTIPP: Auf einen bestimmten Wert runden EXCEL-HELPLINE + Kalenderwoche korrekt berechnen + Laufende Summen berechnen NEWSLETTER-TIPP + Access-Tipps kostenlos per E-Mail Abonnementverwaltung und Copyright +++ EXCEL-NEWS & DOWNLOADS +++ SPOTLIGHT: Digitaler Zeitplaner 2005 auf CD-ROM kostenlos --------------------------------------------------------- Mit dem Digitalen Zeitplaner koennen Sie Excel schnell und komfortabel fuer Zeit- und Projektplanungen aller Art einsetzen. Sie erhalten fertige Loesungen, die Sie direkt von der CD-ROM nutzen koennen. Die Vorlagen sind komplett ungeschuetzt: Kopieren Sie die Vorlagen also beliebig oft, um Ihre individuellen Planungskalender fuer Projekte, Veranstaltungen usw. zu erstellen. In einer Sonderaktion erhalten Sie zurzeit 2 CD-ROM's zum Preis von einer: Wir schicken Ihnen den aktuellen Zeitplaner 2004 und als kostenlose Zugabe den brandneuen Zeitplaner 2005! Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/zeitplaner/ DOWNLOAD-TIPP: Der komfortable Verknuepfungsmanager --------------------------------------------------- Excel zeigt beim Oeffnen einer Datei eine Meldung wie "Diese Datei enthaelt Verknuepfungen zu anderen Datenquellen" an, wenn Sie in Formeln auf Inhalte aus anderen Arbeitsmappen verweisen. Solange Sie wissen, dass die geoeffnete Arbeitsmappe externe Verknuepfungen enthaelt und das auch in Ihrem Sinne ist, hat die Meldung durchaus ihre Berechtigung, denn schliesslich erlaubt sie die sofortige Aktualisierung der Basisdaten. Problematisch wird es, wenn Sie die externen Verknuepfungen entfernen wollen. Mit unserem Verknuepfungsmanager beseitigen Sie Verknuepfungen zu anderen Dateien in allen Excel-Versionen wirkungsvoll aus Ihren Arbeitsmappen. Beruecksichtigt werden nicht nur direkte Verweise auf externe Zellen oder Zellbereiche. Der Assistent kuemmert sich auch um die in der Arbeitsmappe definierten Namen, die auf Inhalte aus anderen Dateien verweisen SmartTools Publishing bietet Ihnen diese nuetzliche Excel-Erweiterung kostenlos zum Download an: http://www.add-in-world.com/katalog/xl-linkmanager/ NEWS: Wichtige Sicherheitsupdates fuer Office 2000, 2002/XP und 2003 ---------------------------------------- In der letzten Woche hat Microsoft in einem Security- Bulletin auf zwei kritische Sicherheitsluecken in Office hingewiesen. Bei dem ersten Problem geht es um den WordPerfect-Konverter, der bereits im August 2003 ebenfalls wegen einer Sicherheitsluecke aktualisiert wurde. Wenn Sie ein speziell praepariertes Dokument oeffnen, das zum Aufruf des WordPerfect-Konverters fuehrt, koennten unbemerkt schaedliche Programme auf Ihrem Rechner ausgefuehrt werden. Naehere Informationen zu der Sicherheitsluecke, die Office 2000, XP und 2003 betrifft, finden Sie unter: http://support.microsoft.com/?kbid=873380 Installieren Sie in jedem Fall das zu Ihrer Office-Version passende Update: Sicherheitsupdate fuer Office 2000: WordPerfect 5.x-Konverter http://www.microsoft.com/downloads/details.aspx?familyid=88f52e69-99e1-4892-9a53-84e5dfadfe6b&displaylang=de Sicherheitsupdate fuer Office XP: WordPerfect 5.x-Konverter http://www.microsoft.com/downloads/details.aspx?familyid=10a6ceb3-7b94-4f74-a5a0-60c31ce2f57b&displaylang=de Sicherheitsupdate fuer Office 2003: WordPerfect 5.x-Konverter http://www.microsoft.com/downloads/details.aspx?familyid=a0629800-1889-495b-b25e-4637d6b03250&displaylang=de Die zweite Sicherheitsluecke betrifft nur Office XP und 2003; wenn Sie eine Grafik im JPEG-Format in einem Office- Programm oeffnen, kann es zur unbemerkten Ausfuehrung von Makrocode kommen. Naehere Informationen finden Sie unter: http://support.microsoft.com/?kbid=832332 Die Updates fuer Office XP und 2003 koennen Sie unter den folgenden Adressen herunterladen: Sicherheitsupdate fuer Office XP: JPEG-Grafik-Konverter http://www.microsoft.com/downloads/details.aspx?familyid=7d128614-6d34-49df-8d63-6c17e9a2d312&displaylang=de Sicherheitsupdate fuer Office 2003: JPEG-Grafik-Konverter http://www.microsoft.com/downloads/details.aspx?familyid=106bcf99-1ba9-4035-94c5-2a7fa90e5971&displaylang=de +++ EXCEL-TIPP DER WOCHE +++ Trendanalyse mit Excel-Funktionen, Teil 2 ----------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 Aus Teil 1 dieses Tipps kennen Sie die vier Tabellenfunktionen, die Sie bei der Trendanalyse unterstuetzen: TREND fuer den linearen Trend, VARIATION fuer den exponentiellen Trend, RGP fuer die Auswertung eines linearen Trends und RKP fuer die Auswertung eines exponentiellen Trends. Erfahren Sie nun, wie Sie RGP und RKP in weiterfuehrenden Berechnungen einsetzen. Die Kenngroessenmatrizen der Funktionen RGP und RKP sind fuer Statistiker sicher interessant. Doch nicht immer benoetigen Sie alle Ausgabewerte. Manchmal genuegen die Angaben zu den Regressionskoeffizienten "m" und "b" sowie das Bestimmtheitsmass. Um gezielt eine bestimmte Kenngroesse aus der Matrix herauszugreifen, nutzen Sie die INDEX- Funktion in Verbindung mit RGP bzw. RKP. Dazu noch einmal die Uebersicht ueber die Matrixwerte: | 1 | 2 --|--------------------------------------------------------- 1 | Regressionskoeffizient m | Regressionskoeffizient b 2 | Standardabweichung m | Standardabweichung b 3 | Bestimmtheitsmass | Standardabweichung y 4 | F-Statistik | Freiheitsgrad 5 | Quadratsumme d. Regr. | Quadratsumme d. Abweichungen Um nun das Bestimmtheitsmass fuer den linearen Trend in einer einzelnen Zelle zu ermitteln, geben Sie folgende Formel ein: =INDEX(RGP($B$2:$B$10;$A$2:$A$10;;WAHR);3;1) Ausgangspunkt ist wieder die Beispieltabelle aus Teil 1 dieses Tipps mit den bekannten X- und Y-Werten im Bereich A2:B10. Der Aufbau der RGP-Funktion ist somit bekannt. Mit der INDEX-Funktion lassen Sie sich hier gezielt den Wert aus Zeile "3" und Spalte "1" der Ergebnismatrix ausgeben. Es handelt sich um einen einzelnen Wert, weshalb Sie die INDEX- Formel nicht einmal als Matrixformel eingeben muessen. Nach demselben Prinzip koennen Sie beispielsweise den Regressionskoeffizienten "b" fuer den exponentiellen Trend ermitteln. Er steht in Zeile "1" und Spalte "2" der RKP- Matrix, was zu folgender INDEX-Formel fuehrt: =INDEX(RKP($B$2:$B$10;$A$2:$A$10;;WAHR);1;2) Lineare und exponentielle Trends sind aber laengst nicht die einzigen Prognoseverfahren. Polynomische und logarithmische Trends bieten sich ebenso als Analysemethoden an. Dafuer gibt es in Excel zwar keine speziellen Tabellenfunktionen. Sie koennen die Trendwerte aber mit Formeln, die auf RGP zurueckgreifen, berechnen. Trends auf Basis von Polynomen 2., 3. oder hoeherer Ordnung liefern meist praezisere Schaetzwerte als der einfache lineare Trend. Bei der Gleichung fuer polynomische Kurven handelt es sich um eine Erweiterung der linearen Regressionsformel, und zwar in der Form: y = mn * x^n + … + m1 * x + b "n" steht fuer die Ordnung des Polynoms, sodass die Formel fuer Polynome 3. Ordnung zum Beispiel "y = m3 * x^3 + m2 * x^2 + m1 * x + b" ist. Mit RGP lassen sich die statistischen Kenngroessen auch fuer diese hoeheren Polynome berechnen. Sie muessen die Funktion nur in einen entsprechend grossen Zellbereich einfuegen: Waehrend die normale RGP-Matrix (quasi ein Polynom 1. Ordnung) 5 Zeilen hoch und 2 Spalten breit ist, benoetigen Sie fuer ein Polynom 2. Ordnung einen Bereich von 5 Zeilen x 3 Spalten, fuer ein Polynom 3. Ordnung 5 Zeilen x 4 Spalten usw. In der ersten Zeile der RGP-Matrix stehen dann die Koeffizienten "mn" bis "m1" sowie "b". Das funktioniert natuerlich nur, wenn Sie die korrekten X-Werte an die Funktion RGP uebergeben. Es handelt sich um eine Matrix aus einer passenden Anzahl an Potenzen der Originalwerte. Fuer ein Polynom 3. Ordnung laesst sich das in Excel mit folgender Formel ausdruecken: =RGP(B2:B10;A2:A10^{1.2.3};;WAHR) Denken Sie daran, die Formel in einen 5 x 4 Zellen grossen Bereich einzugeben und die Eingabe mit Strg + Umschalt + Return zu beenden. Fuer ein Polynom 2. Ordnung brauchen Sie nur die Quadrate der X-Werte. Sie erhalten die statistischen Kenngroessen dann mit einer Matrixformel, die Sie in einen 5 x 3 Zellen grossen Bereich eingeben: =RGP(B2:B10;A2:A10^{1.2};;WAHR) In den Zeilen 3 bis 5 der Ergebnismatrix fuellt Excel uebrigens nach wie vor nur die ersten beiden Spalten aus. Es ist somit vollkommen in Ordnung, wenn rechts daneben die Fehlerwerte "#NV" erscheinen. Wenn Sie die Regressionskoeffizienten kennen, ist es kein Problem, den polynomischen Trend "per Hand" auszurechnen. Angenommen, Sie haben eine RGP-Matrix fuer ein Polynom 3. Ordnung im Bereich J19:M23 aufgestellt. Dann koennen Sie die entsprechenden Trends im Bereich J2:J16 berechnen lassen. Dazu geben Sie in J2 folgende Formel ein: =$J$19*A2^3+$K$19*A2^2+$L$19*A2+$M$19 Die absoluten Zellbezuege (mit den $-Zeichen) verweisen auf die Koeffizienten "m3", "m2" usw. bis "b" in der RGP-Matrix. Zelle A2 enthaelt den X-Wert, fuer den Sie einen Y-Wert suchen. Anschliessend muessen Sie die Formel nur noch in die Zellen J3:J16 kopieren - fertig ist die Trendanalyse auf Basis eines Polynoms 3. Ordnung. Nach einem aehnlichen Prinzip laesst sich auch ein logarithmischer Trend berechnen. Die Trendkurve basiert in diesem Fall auf der Gleichung y = m * ln(x) + b "ln" steht fuer den natuerlichen Logarithmus. "m" und "b" koennen Sie wieder von der Funktion RGP berechnen lassen. Als zweiten Parameter muessen Sie hier die natuerlichen Logarithmen der X-Werte einsetzen. Die Groesse der Ergebnismatrix entspricht der normalen RGP-Matrix mit 5 x 2 Zellen. Um per INDEX-Funktion (siehe oben) nur die Regressionskoeffizienten zu ermitteln, arbeiten Sie mit folgenden Formeln: fuer "m" (Eingabe zum Beispiel in Zelle O19): =INDEX(RGP($B$2:$B$10;LN($A$2:$A$10);;WAHR);1;1) fuer "b" (Eingabe zum Beispiel in Zelle O20): =INDEX(RGP($B$2:$B$10;LN($A$2:$A$10);;WAHR);1;2) Anhand der Kurvengleichung fuer den logarithmischen Trend koennen Sie jetzt die entsprechenden Schaetzwerte im Bereich O2:O16 ausrechnen. Dazu geben Sie in O2 folgende Formel ein: =$O$19*LN(A2)+$O$20 Die absoluten Zellbezuege verweisen auf die Regressionskoeffizienten und A2 enthaelt den X-Wert (das Datum), fuer das Sie den logarithmischen Trend suchen. Fuer die restlichen Trends kopieren Sie die Formel in O3:O16. Mit diesen Kenntnissen werden Sie Trendanalysen in Zukunft noch praeziser gestalten koennen. Sie haben konkrete Werte an der Hand und muessen nicht auf ungefaehre Angaben, die auf Trendlinien in Diagrammen beruhen, zurueckgreifen. ++++++++++++++++ ANZEIGE ++++++++++++++++ Excel-Tabellen automatisch vergleichen und zusammenfuehren ---------------------------------------------------------- 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: * Fuehren Sie mehrere Versionen der gleichen Tabelle in einer einzigen Datei zusammen, die alle Aenderungen der anderen Versionen enthaelt. * Markieren Sie mit wenigen Mausklicks die Unterschiede zwischen zwei Versionen eines Kalkulationsmodells. * Arbeiten Sie Aenderungen voll automatisch in eine Liste (Preise, Artikel, Auftraege etc.) ein. * Erstellen Sie aus mehreren Adresslisten eine einzige Adressliste ohne Duplikate. * Kombinieren Sie zwei Listen mit unterschiedlichen Informationen zu einer aussagekraeftigen Gesamtliste. Weitere Informationen zu diesem unentbehrlichen Excel-Tool finden Sie unter: http://www.add-in-world.com/katalog/synkronizer/ +++++++++++++++++++++++++++++++++++++++++ QUICKTIPP: Auf einen bestimmten Wert runden ------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 Mit Excel koennen Sie Rechenergebnisse problemlos auf eine bestimmte Anzahl von Nachkommastellen runden. Was aber, wenn Sie einen Wert jeweils auf einen bestimmten Anteil runden wollen? Beispielsweise ist es moeglich, dass Betraege jeweils auf die naechsten 50 Cent gerundet werden sollen. In diesem Fall verwenden Sie die folgende Formel: =RUNDEN(A1/0,5;0)*0,5 Dabei ist A1 natuerlich der Bezug auf die Zelle, dessen Inhalt Sie runden moechten. +++ 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. Kalenderwoche korrekt berechnen ------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Zu den Analyse-Funktionen, die sich als Add-In aktivieren lassen, gehoert auch die Tabellenfunktion KALENDERWOCHE. Sie ist aber offensichtlich nicht in der Lage, korrekte Resultate zu liefern, denn wenn ich damit die Kalenderwoche des 4.1.2005 abfrage, erhalte ich den Wert "2". Der 4. Januar 2005 gehoert aber zur ersten Kalenderwoche des Jahres. Aehnlich ist es beim 1.1.2005, der offiziell zur 53. Woche des Vorjahres gehoert. Hier liefert KALENDERWOCHE den Wert "1". Was laeuft da falsch? F. Mayfarth ANTWORT: Die in Excel bzw. in die Analyse-Funktionen integrierte Tabellenfunktion KALENDERWOCHE ist ausschliesslich fuer den amerikanischen respektive internationalen Raum konzipiert. In Deutschland regelt jedoch die DIN 1355, in welche Kalenderwoche ein bestimmter Tag faellt. Damit kommt die Excel-Funktion nicht zurecht. Abhilfe schafft eine kleine VBA-Funktion, die Sie in der Mappe mit dem Funktionscode anschliessend wie eine normale Tabellenfunktion einsetzen koennen. Zur Eingabe der Funktion fuehren Sie folgende Schritte aus: 1. Oeffnen Sie die Arbeitsmappe, in der Sie die Kalenderwoche berechnen wollen. 2. Wechseln Sie mit Alt + F11 in den Visual Basic-Editor. 3. Fuegen Sie ein neues Modul in das VBA-Projekt der aktuellen Arbeitsmappe ein, indem Sie EINFUEGEN-MODUL aufrufen. 4. In das dadurch geoeffnete Modulfenster geben Sie folgenden Programmcode ein: Function KalWo(d As Date) As Integer Dim Don As Date Dim Jahr As Integer Dim Wochen As Integer Don = d - Weekday(d, vbMonday) + 4 Jahr = Year(Don) Wochen = ((Don - DateSerial(Jahr, 1, 1)) \ 7) + 1 KalWo = Wochen End Function 5. Beenden Sie den Visual Basic-Editor per DATEI-SCHLIESSEN UND ZURUECK ZU MICROSOFT EXCEL. Die Funktion arbeitet nach folgendem Prinzip: Zuerst ermittelt sie den Donnerstag in der Woche des Datums, zu dem Sie die Kalenderwoche suchen. Grund dafuer ist, dass die erste Kalenderwoche eines Jahres laut DIN 1355 mindestens vier Tage des jeweiligen Jahres umfassen muss. Das Jahr muss also mindestens mit einem Donnerstag (oder mit einem Montag, Dienstag, Mittwoch) beginnen. Anderenfalls gehoeren die ersten Tage noch zur letzten Woche des Vorjahres. Somit koennen Sie am Donnerstag der gesuchten Kalenderwoche ablesen, zu welchem Jahr sie gehoert, was natuerlich in erster Linie bei den Tagen um den Jahreswechsel interessant ist. Als naechstes berechnen Sie die Tagesdifferenz zwischen dem Donnerstag und dem 1. Januar des Jahres, zu der dieser Donnerstag gehoert. Diesen Wert teilen Sie in einer ganzzahligen Division durch "7", womit Sie die Wochendifferenz erhalten. Dazu muessen Sie schliesslich noch den Wert "1" addieren, und schon haben Sie die Kalenderwoche. In der Arbeitsmappe koennen Sie die benutzerdefinierte Funktion "KalWo" problemlos zur Berechnung der Kalenderwoche einsetzen. Wenn das zur Diskussion stehende Datum zum Beispiel in Zelle A3 steht, koennen Sie in B3 die (korrekte) Kalenderwoche berechnen, indem Sie folgende Formel eingeben: =KalWo(A3) Zum 1.1.2005 erhalten Sie dann wie gewuenscht die Wochenzahl "53" und zum 4.1.2005 die Wochenzahl "1". ++++++++++++++++ ANZEIGE ++++++++++++++++ PDF-Dateien komfortabel nach Excel und Word konvertieren -------------------------------------------------------- Sie erhalten PDF-Dokumente und moechten sie in Excel oder Word bearbeiten? Der PDFgrabber ist die Loesung: Das pfiffige Tool wandelt PDF-Dateien in Word-, Excel- oder normale Textdateien um, so dass Sie sie bearbeiten oder in Ihren eigenen Texten und Kalkulationsmodellen verwenden koennen. Sie brauchen dazu nur 3 einfache Schritte: 1. Nach der automatischen Installation starten Sie en PDFgrabber ganz einfach ueber das Start-Menue. 2. Waehlen Sie ein oder mehrere PDF-Dokumente zur Konvertierung aus und legen Sie das gewuenschte Format sowie das Zielverzeichnis fest (Word, Excel oder Textdatei). 3. Ein Mausklick startet die Umwandlung, die voll automatisch ablaeuft. Am Ende stehen dann die gewuenschten Word- oder Excel-Dateien, die Sie direkt oeffnen und bearbeiten koennen. Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/pdfgrabber/ +++++++++++++++++++++++++++++++++++++++++ Laufende Summen berechnen ------------------------- Versionen: Excel 97, 2000 und 2002 FRAGE: Wie kann ich moeglichst schnell eine Tabellenspalte mit laufenden Summen einrichten? Die Zahlen, die ich addieren moechte, befinden sich in Spalte B und in Spalte C soll automatisch neben jeder neuen Eingabe die bis dahin erreichte Summe erscheinen. B. Nabermann ANTWORT: Sie erreichen das gewuenschte Resultat mit einer Summenfunktion, in der Sie absolute und relative Zellbezuege miteinander verbinden. Wenn Ihre Zahlenreihe zum Beispiel in Zelle B2 beginnt und sich beliebig nach unten fortsetzt, geben Sie in C2 die folgende Formel ein: =SUMME($B$2:$B2) Anschliessend kopieren Sie die Formel in die darunter stehenden Zellen. Die Groesse des Einfuegebereichs richtet sich danach, wie viele Eingaben in Spalte B zu erwarten sind. Die Formel berechnet automatisch die Summe in Spalte B, und zwar ab Zelle B2 (absoluter Bezug) bis zur jeweils aktuellen Zeile (gemischter Bezug). Nachdem Sie die Formel kopiert haben, lautet sie in Zelle C10 zum Beispiel: =SUMME($B$2:$B10) +++ 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) 2004 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/