Von: excel-weekly-text-request@cgn.smarttools.de im Auftrag von SmartTools Publishing [excel-weekly-text-reply@smarttools.de] Gesendet: Dienstag, 17. August 2004 01:39 An: Excel Weekly Abonnenten Betreff: SmartTools Excel Weekly vom 17.8.2004 ----------------------------------------------------------- SmartTools Excel Weekly vom 17.8.2004 Jede Woche neu: Tipps, News und Problemloesungen rund um Microsoft Excel (c) 2004 by SmartTools Publishing ----------------------------------------------------------- +++ IN DIESER AUSGABE +++ EXCEL-NEWS & DOWNLOADS + SPOTLIGHT: PDF-Dateien nach Excel und Word konvertieren + DOWNLOAD-TIPP: Makros in Excel optimal verwalten + WEBCAST: Sicherheitsaspekte beim Einsatz der Visual Studio Tools fuer Office EXCEL-TIPP DER WOCHE + Doppelte Eintraege in einer Spalte automatisch verhindern EXCEL-HELPLINE + Jede zweite Tabellenzeile/-spalte summieren + Probleme mit dem 'Worksheet_Change-Ereignis' + Textwerte mit Zahlen oder Formeln kombinieren NEWSLETTER-TIPP + Access-Tipps kostenlos per E-Mail Abonnementverwaltung und Copyright +++ EXCEL-NEWS & DOWNLOADS +++ SPOTLIGHT: PDF-Dateien nach Excel und Word konvertieren ------------------------------------------------------- PDF ist das Standardformat fuer den Austausch von elektronischen Dokumenten: Produktbeschreibungen, Preislisten, Ausschreibungen, Briefe, Konzepte; es gibt kaum etwas, was nicht inzwischen als PDF verschickt wird. Das Angebot von Tools fuer die Umwandlung von Word- und Excel- Dateien in das PDF-Format ist riesig, aber was ist mit dem umgekehrten Weg: Sie erhalten PDF-Dokumente und moechten sie in Word oder Excel bearbeiten? Der PDFgrabber bietet die optimale 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. Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/pdfgrabber/ DOWNLOAD-TIPP: Makros in Excel optimal verwalten ------------------------------------------------ Vor einiger Zeit haben wir uns in Excel Weekly mit der optimalen Verwaltung Ihrer persoenlichen Makros und benutzerdefinierten Funktionen beschaeftigt. Auf vielfachen Wunsch haben wir die entsprechenden Tipps und Kniffe in einer Techinfo zusammengefasst, mit einigen Bildschirmfotos versehen und zum kostenlosen Download bereitgestellt: http://www.add-in-world.com/katalog/xlmakro/ In der Techinfo erklaeren wir Schritt fuer Schritt, wie Sie die zahlreichen VBA-Makros optimal verwalten, die Sie beispielsweise aus Excel Weekly oder aus anderen Publikationen sammeln und verwenden. Sorgen Sie mit wenig Aufwand dafuer, dass Ihnen die Makros jederzeit zur Verfuegung stehen. WEBCAST: Sicherheitsaspekte beim Einsatz der Visual Studio Tools fuer Office -------------------------------------------- Mit den Visual Studio Tools fuer Office (VSTO) koennen komplexe Excel-Anwendungen in Visual Studio entwickelt werden. Der wichtige Sicherheitsaspekt wird dabei haeufig vernachlaessigt. In einem kostenlosen Webcast am 18.8.2004 zeigt ein Programm-Manager von Microsoft, welche Sicherheitsprobleme es geben kann und wie Sie sie mit den neuen VSTO 2005 in den Griff bekommen koennen. Naehere Informationen und die Registrierung zur Anzeige des Webcasts finden Sie unter: http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032256143&Culture=en-US +++ EXCEL-TIPP DER WOCHE +++ Doppelte Eintraege in einer Spalte automatisch verhindern --------------------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 Fuer viele Anwendungen muessen Sie sicherstellen, dass keine doppelten Werte in eine Spalte eingetragen werden. Das gilt zum Beispiel fuer Rechnungs-, Kunden- oder Artikelnummern, die in jedem Fall immer eindeutig vergeben werden muessen. Mit wenig Aufwand koennen Sie dafuer sorgen, dass Excel diese Pruefung fuer Sie uebernimmt und doppelte Eingaben automatisch verhindert. Dazu moechten wir Ihnen im folgenden zwei Varianten vorstellen. Bei der ersten Loesung gehen wir davon aus, dass Sie die Zahlen in einer Spalte untereinander eingeben und neue Zahlen immer am Ende anfuegen: 1. Markieren Sie die komplette Spalte, in der die Eingabe von doppelten Zahlen verhindert werden soll (zum Beispiel Spalte B). 2. Waehlen Sie das Menue DATEN-GUELTIGKEIT an. 3. Markieren Sie in der Liste ZULASSEN den Eintrag "Benutzerdefiniert" und geben Sie dann den folgenden Ausdruck in das Feld FORMEL ein (ersetzen Sie dabei "B" durch den Buchstaben der Spalte, die Sie in Ihrer Tabelle markiert haben): =VERGLEICH(B1;$B:$B;0)=ZEILE(B1) 4. Wechseln Sie auf das Register "Fehlermeldung" und legen Sie Inhalt und Aussehen der Dialogbox fest, die den Anwender auf die Eingabe eines doppelten Wertes aufmerksam machen soll. 5. Bestaetigen Sie Ihre Einstellungen mit OK. Ab sofort ist die Eingabe von doppelten Werten in der markierten Spalte nicht mehr moeglich. Der eigentliche Trick bei dieser Technik ist die Formel, die Sie als Gueltigkeitskriterium eingeben. Dazu muss man folgendes wissen: * Excel zeigt verhindert immer dann die Eingabe eines Wertes, wenn der von Ihnen eingegebene Ausdruck das Ergebnis FALSCH liefert. * Ein relativer Bezug in der Gueltigkeitsformel wird immer automatisch auf jede Zelle des markierten Bereiches angepasst. In der Zelle B10 wendet Excel also beispielsweise folgende Gueltigkeitsformel an: =VERGLEICH(B10;$B:$B;0)=ZEILE(B10) In der Formel passiert nun folgendes: Der Inhalt der aktuellen ZELLE wird mit Hilfe der Funktion VERGLEICH mit dem uebrigen Inhalt der Spalte verglichen. Als Ergebnis erhalten Sie die Nummer der Zeile in Spalte B, in der die Funktion fuendig wurde. Handelt es sich nicht um eine doppelte Eingabe, entspricht diese Zeilennummer der Zeile, in die Sie etwas eingegeben haben. In diesem Fall ist das Funktionsergebnis WAHR und Excel laesst die Eingabe zu. Ist der eingegebene Wert bereits vorhanden, liefert VERGLEICH eine andere Zeilennummer, so dass das Ergebnis FALSCH ist und die Eingabe verhindert wird. Daraus ergibt sich, dass diese Formel nur in den Faellen funktioniert, in denen Sie die Zahlen untereinander eingeben, denn Excel kann nur die Werte ueber der aktuellen Zelle zuverlaessig pruefen. Wenn Sie Zahlen an beliebigen Positionen in der Spalte eingeben oder nachtraeglich aendern moechten, aendern Sie die Technik leicht ab und verwenden die folgende Gueltigkeitsformel (hier ist "D" durch den Buchstaben der von Ihnen verwendeten Spalte zu ersetzen): =ZAEHLENWENN($D:$D;D1)<2 Ueber die Funktion ZAEHLENWENN koennen Sie ermitteln, wie oft der Inhalt einer Zelle in der kompletten Spalte vorkommt. Nur wenn das Ergebnis "<2" ist, liegt keine Doppeleingabe vor und Excel nimmt den Wert an. ++++++++++++++++ ANZEIGE ++++++++++++++++ Das 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 neue 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 97, 2000 und 2002/XP 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/ +++++++++++++++++++++++++++++++++++++++++ +++ 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. Jede zweite Tabellenzeile/-spalte summieren ------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Ich arbeite mit einer Tabelle, in der ich Arbeitszeiten erfasse. Dabei wurde jeder Arbeitstag auf zwei Zeilen verteilt, sodass in der ersten Zeile die normalen Arbeitsstunden und in der zweiten Zeile die Ueberstunden stehen. Jedem Mitarbeiter ist eine Spalte zugeordnet. Nun will ich am Monatsende nicht nur die Gesamtstunden eines Mitarbeiters erhalten, was durch die einfache Summenbildung aller Stunden und Ueberstunden unproblematisch ist. Ich moechte eine Unterscheidung zwischen normalen Arbeitsstunden und Ueberstunden erreichen. Das bedeutet, ich muesste eine Formel entwickeln, die immer nur jede zweite Zeile addiert. Wie muesste eine solche Formel aussehen? S. Engelke ANTWORT: Die gewuenschten Summen koennen Sie mit Matrixformeln berechnen. In den Formeln pruefen Sie, ob sich die Werte in Zeilen mit gerader Zeilennummer befinden oder nicht. Das Ergebnis dieser Pruefung bestimmt dann, ob der Wert zur Gesamtsumme hinzugefuegt wird oder nicht. Angenommen, Sie haetten die erwaehnten Zeitkontingente eines Mitarbeiters in Spalte C erfasst. In C2 stehen die normalen Arbeitsstunden des ersten Arbeitstages, in C3 die Ueberstunden dieses Tages. Darunter in C4 folgen die normalen Stunden des zweiten Tages und in C5 die entsprechenden Ueberstunden. In dieser Form setzt sich die Tabelle fort. Die normalen Arbeitsstunden erhalten Sie somit, wenn Sie die Werte aus den geraden Zeilen addieren; die Ueberstunden stammen aus den ungeraden Zeilen. Ob es sich um eine gerade oder eine ungerade Zeile handelt, erfahren Sie, indem Sie die Zeilennummer durch "2" teilen und den ganzzahligen Rest betrachten: Bei einer geraden Zeile ist der Rest "0", ansonsten nicht. Somit koennen Sie in Zelle C65 (das ist eine freie Zelle, wenn Sie die Tagesliste bis zum 31. Tag fortsetzen wuerden) folgende Matrixformel eingeben: =SUMME((REST(ZEILE(C2:C63);2)=0)*C2:C63) Sie muessen die Formeleingabe mit Strg+Umschalt+Return abschliessen, damit sie als Matrixformel erkannt wird. Excel umgibt die Formel dann automatisch mit geschweiften Klammern. Die Summe der ungeraden Zeilen erhalten Sie entsprechend mit folgender Formel: =SUMME((REST(ZEILE(C2:C63);2)<>0)*C2:C63) Auch diese Formel muessen Sie natuerlich wieder mit Strg + Umschalt + Return abschliessen. Das Formelprinzip funktioniert analog auch bei Spalten: Um nur die geraden Spalten eines Bereichs zu addieren, geben Sie eine Matrixformel nach folgendem Muster ein: {=SUMME((REST(SPALTE( [Datenbereich]);2)=0)* [Datenbereich])} Fuer die ungeraden Spalten lautet die Syntax: {=SUMME((REST(SPALTE( [Datenbereich]);2)<>0)* [Datenbereich])} Probleme mit dem 'Worksheet_Change-Ereignis' -------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Im Visual Basic-Editor bietet Excel bekanntlich Codefenster fuer die einzelnen Blaetter einer Arbeitsmappe an. In den Codefenstern kann ich auf bestimmte Ereignisse in den Tabellenblaettern reagieren, beispielsweise auf das "Worksheet_Change-Ereignis". Auf diese Weise moechte ich jetzt ueberpruefen, ob in dem Tabellenblatt ein Wert eingegeben wurde, der groesser als "100" ist. Wenn das der Fall ist, soll in Zelle A1 der Tabelle ein Warnhinweis erscheinen. Leider funktioniert mein Programmcode nur manchmal: Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Value > 100 Then ActiveSheet.Range("A1").Value = _ "Ein Wert ist zu gross!" End If End Sub Wissen Sie, was hier falsch laeuft? F. Riecken ANTWORT: Wir erhalten haeufiger Anfragen zum "Worksheet_Change-Ereignis". Bei genauer Betrachtung des eingesendeten Programmcodes ist der Fehler oft sehr schnell gefunden. Auch in Ihrer Ereignisprozedur verbirgt sich die uebliche Fehlerquelle: Sie arbeiten mit dem "ActiveCell"-Objekt, um sich auf die Zelle zu beziehen, in der gerade etwas eingegeben wurde. Das ist so nicht richtig. Die Eingabezelle entspricht der aktiven Zelle nur dann, wenn Sie in der Bearbeitungsleiste auf das gruene Haekchen klicken oder wenn Sie Return druecken und sich der Zellzeiger dabei nicht bewegt. In allen anderen Faellen sind Eingabezelle und aktive Zelle nicht identisch. Damit Sie sich dennoch auf die Zelle mit der letzten Benutzereingabe beziehen koennen, bietet Ihnen Excel den Prozedurparameter "Target" an - siehe erste Zeile der Prozedur: Private Sub Worksheet_Change(ByVal Target As Range) Nutzen Sie "Target" anstelle von "ActiveCell", um sich auf die letzte Eingabezelle zu beziehen. Dann spielt es keine Rolle, ob Sie Ihre Eingabe mit Return abschliessen und sich die Zellmarkierung anschliessend nach unten oder rechts verschiebt oder ob Sie nach einer Eingabe einfach eine andere Zelle anklicken. In der folgenden Form sollte die Ereignisprozedur immer den richtigen Warnhinweis ausgeben (Anmerkung: Sie sollten aber auch dafuer sorgen, dass er gegebenenfalls wieder verschwindet): Private Sub Worksheet_Change(ByVal Target As Range) If Target.Value > 100 Then ActiveSheet.Range("A1").Value = _ "Ein Wert ist zu gross!" End If End Sub +++++++++++++++ ANZEIGE +++++++++++++++++ Urlaubs-Datenbank fuer Access 97, 2000, 2002/XP und 2003 - ungeschuetzt mit komplettem Sourcecode ---------------------------------------------------------- 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. + Automatische Auflistung der Fehlzeiten und verbleibenden Urlaubstage fuer jeden Mitarbeiter + Abwesenheiten der kompletten Firma zeigen Sie uebersichtlich per Kalender-Diagramm an. + Die Frage, ob ein Urlaub zu einem bestimmten Zeitpunkt moeglich ist oder nicht, beantwortet sich sozusagen per Mausklick. Die Urlaubs-Datenbank fuer Access 97, 2000, 2002/XP und 2003 hat sich bereits hundertfach in der Praxis bewaehrt. Sie erhalten die Datenbank vollkommen ungeschuetzt mit dem kompletten Sourcecode. Von Korrekturen in Formularen und Berichten bis zur vollstaendigen Integration in eine bestehende Personalverwaltung ist jede Veraenderung moeglich. Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/urlaubsdatenbank/ +++++++++++++++++++++++++++++++++++++++++ Textwerte mit Zahlen oder Formeln kombinieren --------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Gibt es eine Moeglichkeit, in einer Zelle einen feststehenden Text mit dem Inhalt einer beliebigen anderen Zelle zusammenzufassen? - Konkret moechte ich eine Spaltenueberschrift erstellen, in der so etwas wie "Arbeitsstunden (à 32 Euro)" steht. Dabei soll sich die Preisangabe nach dem Inhalt einer anderen Zelle richten. K. Dremel ANTWORT: In Excel koennen Sie beliebige Verknuepfungen mit dem Operator "&" erstellen. Der Operator kann mit festen Text- oder Zahlenwerten, mit Zellbezuegen und sogar mit Tabellenfunktionen eingesetzt werden. Angenommen, Zelle B2 enthaelt den Wert "32". Nun moechten Sie in Zelle D4 eine Spaltenueberschrift eingeben, die sich auf den Inhalt von Zelle B2 bezieht. Um bei dem Beispiel aus Ihrer Frage zu bleiben, geben Sie in D4 folgende Formel ein: ="Arbeitsstunden (à "&B2&" Euro)" Als Spaltenueberschrift erscheint der gewuenschte Text. Und wenn Sie in Zelle B2 einen anderen Wert (beispielsweise "25") eingeben, aendert sich auch die Beschriftung in Zelle D4 - in diesem Fall in "Arbeitsstunden (à 25 Euro)". Wie Sie an der Formel erkennen, setzen Sie den Operator "&" genau so wie einen herkoemmlichen mathematischen Operator (etwa "+" oder "-") ein. Bei Angabe eines Zellbezuges geht im Formelergebnis jedoch die Formatierung der urspruenglichen Zelle verloren. Sie beseitigen das Problem, indem Sie die Tabellenfunktion TEXT zusammen mit dem "&"- Operator einsetzen. Nehmen Sie als Grundlage wieder das oben genannte Beispiel, wobei Sie Zelle B2 das Euro-Waehrungsformat zuweisen. In der Tabelle erscheint der Wert dann als "32,00 EUR". Auf die Verknuepfungsformel in Zelle D4 hat das keinen Einfluss; dort sehen Sie nur den unformatierten Wert "32". Um auch hier zwei Dezimalstellen plus Waehrungssymbol anzuzeigen, aendern Sie die Formel in D4 in: ="Arbeitsstunden (à "&TEXT(B2;"#.##0,00 EUR; -#.##0,00 EUR")&")" Nun erhalten Sie die gewuenschte Spaltenueberschrift "Arbeitsstunden (à 32,00 EUR)". +++ 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/