Von: excel-weekly-text-request@cgn.smarttools.de im Auftrag von SmartTools Publishing [excel-weekly-text-reply@smarttools.de] Gesendet: Montag, 28. Juni 2004 22:19 An: Excel Weekly Abonnenten Betreff: SmartTools Excel Weekly vom 29.6.2004 ----------------------------------------------------------- SmartTools Excel Weekly vom 29.6.2004 Jede Woche neu: Tipps, News und Problemloesungen rund um Microsoft Excel (c) 2004 by SmartTools Publishing ----------------------------------------------------------- +++ IN DIESER AUSGABE +++ EXCEL-NEWS & DOWNLOADS + NEWS: Personal-, Belegungs- und Zeitplaene endlich grafisch in Access darstellen + DOWNLOAD-TIPP: Komfortable Projektplanung in Excel EXCEL-TIPP DER WOCHE + KOLUMNE: Komfortable Suchfunktion mit Markierung der Fundstellen EXCEL-HELPLINE + Aktuellen Benutzernamen in eine Tabelle schreiben + Haeufigkeitsberechnung in Abhaengigkeit von einer Bedingung + Letzte Zelle eines Bereichs per VBA markieren NEWSLETTER-TIPP + Word-Tipps kostenlos per E-Mail Abonnementverwaltung und Copyright +++ EXCEL-NEWS & DOWNLOADS +++ NEWS: Personal-, Belegungs- und Zeitplaene endlich grafisch in Access darstellen -------------------------------------------------- Der beliebte Belegungsplan fuer Access steht ab sofort in einer neuen Version zur Verfuegung: Sie muessen in einer Tabelle lediglich Start- und Enddatumsangaben der einzelnen Vorgaenge verwalten und diese Loesung setzt Ihre Daten voll automatisch in ein uebersichtliches Balkendiagramm um. Mit wenig Aufwand integrieren Sie den Plan in Ihre eigenen Datenbanken, wobei eine Fuelle von Anwendungen denkbar ist: + Urlaubs- und Personalplanung + Belegung von Wohnungen oder Haeusern + Vermietung von Geraeten + Fahrzeuge eine Fuhrparks + Raumbelegung + Projekt- und Ressourcenplanung + u.v.m. Die neue Version 2.11 bietet unter anderem jetzt eine Druckfunktion, eine komfortable Feiertagsverwaltung sowie die Integration von Kuerzeln fuer noch aussagekraeftigere Darstellungen. Sie erhalten diese Loesung in einer komplett ungeschuetzten Entwicklerversion (MDB-Datei) ohne jegliche Einschraenkungen an. Sie koennen den Belegungsplan in beliebig viele Datenbanken integrieren und auf unbegrenzt vielen Arbeitsplaetzen installieren! Weitere Informationen finden Sie unter: http://www.add-in-world.com/katalog/ac-belegungsplan/ DOWNLOAD-TIPP: Komfortable Projektplanung in Excel -------------------------------------------------- Der SmartTools Projektplan ist eine flexible Kalendervorlage mit vielen denkbaren Anwendungen von der Planung von Projekten, ueber die Erfassung von Arbeitsstunden bis hin zur Verwaltung von Geraeten oder Raeumen. Die grundsaetzliche Funktionsweise: 1. Geben Sie zunaechst das gewuenschte Startdatum Ihrer Planung in die dafuer vorgesehene Zelle ein. Die Vorlage wird daraufhin automatisch mit einem Raster fuer die naechsten 8 Monate gefuellt. Das Planungsraster ist nach Wochen aufgeteilt; die Wochenenden werden farbig hervorgehoben. 2. Erfassen Sie in der ersten Spalte der Tabelle die Vorgaenge zu Ihrem Projekt. 3. Sie koennen nun zu jedem Vorgang beispielsweise die Tage markieren, die fuer die Bearbeitung benoetigt werden, indem Sie an den entsprechenden Stellen in dem Raster die Zahl "1" eintragen. Die ausgefuellten Zellen werden automatisch farbig hinterlegt, so dass der zeitliche Ablauf leicht verfolgt werden kann. 4. Besonders interessant ist die Tatsache, dass Sie anstelle der Zahl "1" auch beliebige andere Werte eingeben koennen, um beispielsweise die Anzahl der eingesetzten Personen, die Bearbeitungszeit in Stunden oder aehnliches zu erfassen. Der Projektplan summiert voll automatisch alle Werte in jeder Zeile (zum Beispiel Vorgaenge, Mitarbeiter, Geraete usw.) sowie in jeder Spalte (=Gesamtsumme fuer jeden Tag). SmartTools Publishing stellt Ihnen diese Loesung zum kostenlosen Download zur Verfuegung: http://www.add-in-world.com/katalog/projektplan/ +++ EXCEL-TIPP DER WOCHE +++ Im Tipp der Woche finden Sie einmal im Monat die Excel- Kolumne. Der Autor Bernd Held ist anerkannter Excel- Spezialist und traegt fuer sein Engagement in der Excel- Newsgroup von Microsoft bereits seit mehreren Jahren den Titel "Most Valuable Professional" (MVP) fuer Excel. Naehere Informationen zu seinem erfolgreichen Buch mit Tipps und Loesungen rund um die VBA-Programmierung in Excel, finden Sie unter: http://www.amazon.de/exec/obidos/ASIN/3827265770/smarttoolsbuchsh Fuer Informationen zu seinem neuesten, unter Excel Weekly- Lesern sehr beliebten Buch mit einem gut strukturierten VBA- Einsteigerkurs "Excel-VBA in 14 Tagen" klicken Sie bitte auf den folgenden Link: http://www.amazon.de/exec/obidos/ASIN/382726619X/smarttoolsbuchsh KOLUMNE: Komfortable Suchfunktion mit Markierung der Fundstellen ------------------------------------------------ Versionen: Excel 97, 2000, 2002/XP und 2003 Bei der heutigen Kolumne geht es um das Thema, wie Sie mithilfe der Tabellenfunktion SVERWEIS und der bedingten Formatierung eine komfortable Suche in einer Tabelle erstellen koennen. Bei dem folgenden Beispiel liegt eine Artikel-Liste vor, die ab Zeile 5 mit der Ueberschrift beginnt. Dabei werden folgende Daten in der Liste gefuehrt: * ab A5: Artikel-Nr * ab B5: Artikel-Bezeichnung * ab C5: Lagermenge * ab D5: Preis In den Zeilen 6 bis 20 koennen Sie als kleine Vorarbeit einmal ein paar Testdaten erfassen. Ueber die Eingabe der Artikel-Nr in Zelle A1 sollen die restlichen Daten, die diesem Artikel zugeordnet sind, in der Liste ermittelt und in die Zellen B1, C1 und D1 geschrieben werden. Des Weiteren soll die "Fundzeile" in der Liste automatisch mit der Hintergrundfarbe Rot formatiert werden. Mithilfe der Tabellenfunktion SVERWEIS koennen Sie ueber den eindeutigen Schluessel, hier die Artikel-Nr, die dazugehoerigen Daten aus der Liste ermitteln und uebertragen. Um diese Aufgabe zu loesen, verfahren Sie wie folgt: 1. Geben Sie in Zelle A1 eine Artikel-Nr ein, die in der darunter liegenden Liste auch vorhanden ist. 2. Erfassen Sie die folgenden Formeln: B1: =SVERWEIS($A$1;$A$5:$D$20;2;FALSCH) C1: =SVERWEIS($A$1;$A$5:$D$20;3;FALSCH) D1: =SVERWEIS($A$1;$A$5:$D$20;4;FALSCH) 3. Markieren Sie den Zellenbereich A5:D20. 4. Waehlen Sie aus dem Menue FORMAT den Befehl BEDINGTE FORMATIERUNG. 5. Im Dialog BEDINGTE FORMATIERUNG stellen Sie im ersten Kombinationsfeld den Eintrag Formel ist ein. 6. Geben Sie im Feld rechts daneben die Formel =$A$1= $A5:$A20 ein. 7. Klicken Sie die Schaltflaeche FORMAT. 8. Wechseln Sie auf die Registerkarte "Muster". 9. Waehlen Sie eine gewuenschte Hintergrundfarbe aus und bestaetigen Sie zweimal mit OK. Die "Fundzeile" in der Liste wird nun automatisch rot eingefaerbt. Der Vorteil dieser Loesung liegt auf der Hand: Ueber die Eingabe der Artikel-Nr in eine einzige Zelle (A1) werden alle dazugehoerigen Daten in den Zellen B1, C1 und D1 angezeigt, ohne dass Sie die ganze Liste durchsuchen muessen. Kommt eine Artikelnummer mehrmals in der Liste vor, dann werden die Fundzeilen rot eingefaerbt. Damit koennen Sie auch mit wenig Aufwand pruefen, ob Daten versehentlich doppelt erfasst wurden. ++++++++++++++++ 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. Aktuellen Benutzernamen in eine Tabelle schreiben ------------------------------------------------- Versionen: Excel 97, 2000, 2002, 2003 FRAGE: Im Newsletter vom 5. Mai 2004 haben Sie beschrieben, wie man das letzte Aenderungsdatum in einem Tabellenblatt festhalten kann. Ist es darueber hinaus eigentlich auch moeglich, den Namen des jeweiligen Benutzers/Sachbearbeiters einzutragen? Ich habe schon versucht, Ihre Prozedur um eine "Application.UserName"-Anweisung zu erweitern. Das Ergebnis ist aber nicht zufrieden stellend, da auf diese Weise nicht der Name ausgegeben wird, mit dem sich jemand am Computer angemeldet hat, sondern die Namensbezeichnung, die nach Aufruf von EXTRAS-OPTIONEN auf der Registerkarte ALLGEMEIN steht. Diese Angabe laesst sich ja leicht manipulieren. Haben Sie eine Loesung, wie sich der "echte" Benutzername ermitteln laesst? U. Haberthuer ANTWORT: Der aktuelle Benutzername, mit dem sich jemand beim Starten des Computers angemeldet hat, laesst sich nur ueber eine so genannte API-Funktion, also eine spezielle Windows- Funktion, herausfinden. Diese Funktion in ein Excel-Makro einzubinden, das automatisch beim Aendern eines Tabellenblatts ausgefuehrt wird, ist aber nicht all zu schwer: 1. Oeffnen Sie die Arbeitsmappe, in der Sie den jeweiligen Benutzernamen speichern wollen. 2. Wechseln Sie mit Alt+F11 in den Visual Basic-Editor. 3. Oeffnen Sie im Projekt-Explorer (auf der linken Seite des VB-Editor-Fensters) die untergeordneten Zweige des VBA- Projekts der aktuellen Arbeitsmappe. Doppelklicken Sie dann im Zweig "Microsoft Excel-Objekte" auf den Eintrag "DieseArbeitsmappe". 4. Im daraufhin angezeigten Codefenster geben Sie folgenden Prozedurcode ein: Private Sub Workbook_SheetChange _ (ByVal Sh As Object, _ ByVal Target As Range) Dim UserZelle As Range Application.EnableEvents = False If TypeName(Sh) = "Worksheet" Then Set UserZelle = Sh.Range("A2") UserZelle = Benutzername() End If Application.EnableEvents = True End Sub Ersetzen Sie den Bezug in der Zeile "Set UserZelle = Sh.Range("A2")" durch die Adresse der Zelle, in der der Benutzername stehen soll. Wenn der Name zum Beispiel nicht in Zelle A2 sondern in B3 erscheinen soll, aendern Sie die Zeile in "Set UserZelle = Sh.Range("B3"). 5. Beim genauen Hinsehen werden Sie vielleicht bemerkt haben, dass der Wert in der "UserZelle" von einer Funktion namens "Benutzername()" geliefert wird. Diese Funktion muessen Sie erst noch anlegen, und zwar in einem neuen Modul. Dazu waehlen Sie EINFUEGEN-MODUL an und im danach angezeigten Codefenster geben Sie folgenden VBA- Code ein: Private Declare Function GetUserName _ Lib "advapi32.dll" Alias "GetUserNameA" _ (ByVal lpBuffer As String, _ nSize As Long) _ As Long Public Function Benutzername() As String Dim strName As String Dim lngErgebnis As Long Application.Volatile strName = String(256, Chr(0)) lngErgebnis = GetUserName(strName, 256) If lngErgebnis <> 0 Then strName = _ Left(strName, InStr(strName, Chr(0)) - 1) Else strName = "unbekannt" End If Benutzername = strName End Function Sie verwenden hier die API-Funktion "GetUserName", um den Benutzernamen vom Betriebssystem zu erfragen. Die Funktion muss dazu erst auf Modulebene deklariert werden. Anschliessend koennen Sie "GetUserName" in der benutzerdefinierten VBA-Funktion "Benutzername" einsetzen. 6. Waehlen Sie DATEI-SCHLIESSEN UND ZURUECK ZU MICROSOFT EXCEL an. Sobald Sie nun irgendwelche Aenderungen an der Arbeitsmappe vornehmen, schreibt Excel Ihren Benutzernamen in das aktuelle Tabellenblatt. So koennen Sie Blatt fuer Blatt feststellen, wer es zuletzt bearbeitet hat. ++++++++++++++++ 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/ +++++++++++++++++++++++++++++++++++++++++ Haeufigkeitsberechnung in Abhaengigkeit von einer Bedingung ----------------------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Ich moechte die Haeufigkeitsverteilungen in einer Tabellenspalte berechnen, wobei die Zahlen in der Spalte aber nur beruecksichtigt werden sollen, wenn in einer anderen Spalte bestimmte Werte stehen. Konkret sollen Zahlen aus dem Bereich B2:B1022 nur dann in die Haeufigkeitsberechnung einfliessen, wenn daneben im Bereich A2:A1022 ein Wert groesser als Null steht. Falls die Zelle in Spalte A zum Beispiel leer ist oder kleiner als Null ist, soll der Wert aus Spalte B von der Haeufigkeitsverteilung ausgeschlossen werden. - Ich weiss, dass ich eine HAEUFIGKEIT-Funktion in mehrere Zellen eingeben muss, sodass die Anzahl der Zellen der Anzahl der Haeufigkeitsklassen entspricht. Und ich weiss, dass ich die Funktion als Matrixformel eingeben muss. Dennoch funktioniert eine Formel wie {=WENN(A2:A1022>0;HAEUFIGKEIT(B2:B1022;C2:C4);"")} offensichtlich nicht. Ich waere Ihnen dankbar, wenn Sie mir weiterhelfen koennten. E. Grubner ANTWORT: Sie erhalten das gewuenschte Resultat, wenn Sie die WENN-Funktion in die HAEUFIGKEIT-Funktion integrieren - und nicht umgekehrt, wie in Ihrem Formelbeispiel. Angenommen etwa, Sie haetten im Bereich B2:B1022 Werte zwischen 1 und 100 eingegeben und wollten nun wissen, wie viele dieser Werte zwischen 1 und 25, wie viele zwischen 26 und 50 und wie viele zwischen 51 und 100 liegen. Diese "Klassen" (25, 50 und 100) haben Sie in den Bereich C2:C4 eingegeben. Nun wollen Sie aber nur die Werte zaehlen, neben denen in Spalte A eine Zahl groesser Null steht. Um diese, von einer Bedingung abhaengige Haeufigkeitsverteilung in Spalte D auszugeben, markieren Sie zunaechst drei Zellen in dieser Spalte - beispielsweise D2:D4. So kann Excel fuer jede Werteklasse die Haeufigkeit ausgeben. Dann geben Sie folgende Formel ein: =HAEUFIGKEIT(WENN(A2:A1022>0;B2:B1022);C2:C4) Schliessen Sie die Formeleingabe mit der Tastenkombination Strg + Umschalt + Return ab, damit Excel die Formel als Matrixformel eintraegt. Sie wird dann automatisch von geschweiften Klammern umgeben. Die Zellen D2:D4 enthalten daraufhin das gesuchte Resultat. Letzte Zelle eines Bereichs per VBA markieren --------------------------------------------- Versionen: Excel 97, 2000, 2002/XP und 2003 FRAGE: Beim Arbeiten in einem Tabellenblatt kann ich mit der Tastenkombination Strg+ direkt zur letzten Zelle eines zusammenhaengenden Bereichs springen. Die Richtung bestimme ich durch Auswahl der korrespondierenden Pfeiltaste. Diese schnelle Navigation wuerde ich gerne in ein VBA-Makro einbinden. Geht das? A. Fuchs ANTWORT: VBA stellt dafuer die Eigenschaft "End" zur Verfuegung. Als Parameter uebergeben Sie die Sprungrichtung. Es handelt sich um die Eigenschaft eines Range-Objekts, so dass Sie entweder von der aktuellen Markierung, dem Objekt "Selection", oder von einer Zelle/einem Bereich Ihrer Wahl, etwa "Range("A:A")" fuer Spalte A oder "Range("A1")" fuer Zelle A1, ausgehen koennen. Wenn Sie in einem Tabellenblatt zum Beispiel Zelle A1 markiert haben und dann die Tastenkombination Strg+Pfeil- nach-rechts druecken, springen Sie direkt zum rechten Ende des in A1 beginnenden Bereichs. Um diese Aktion mit einem VBA-Makro auszufuehren, geben Sie folgende Codezeile ein: Selection.End(xlToRight).Select oder: Range("A1").End(xlToRight).Select Als Richtungsangaben akzeptiert die End-Eigenschaft anstelle von "xlToRight" (nach rechts) auch die Parameter "xlToLeft" (nach links), "xlDown" (nach unten) sowie "xlUp" (nach oben). Fuer die schnelle Navigation in einem Tabellenblatt ist darueber hinaus die "SpecialCells"-Methode interessant. Damit koennen Sie direkt zur allerletzten Zelle eines Tabellenblatts springen. Geben Sie in VBA dann die folgende Codezeile ein: Range("A1").SpecialCells(xlCellTypeLastCell).Select +++ 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) 2004 by SmartTools Publishing Thomas Tai Luisenstrasse 52, 49565 Bramsche Telefon: 05461/91052, Fax: 05461/2609 E-Mail: mailto:excel-weekly@smarttools.de Internet: http://www.smarttools.de/