RSS

Archiv der Kategorie: Excel

Tips und Tricks zu Excel

Automatische Nummerierung in gefilterter Liste

Bei einer Kunden-, Artikel-, Telefon- oder sonstigen Liste kommt es natürlich vor, dass Einträge ausgeblendet werden. Hat man eine Nummerierung vorgenommen, so wird diese bei einer Standardnummerierung nicht in Reihenfolge sondern unterbrochen angezeigt. (statt 1,2,3,4,5.. steht dort 3,11,42,56…).

Um auch bei einer Filterung immer die 1,2,3-Reihenfolge zu erhalten, wird eine Formel benötigt.

Beispiel der ungefilterten Tabelle:

liste 1

Beispiel der gefilterten Tabelle (absteigend sortiert, gefiltert nach München und Leipzig)

liste 2

Formel:

=TEILERGEBNIS(3;$H$2:H2)

Im Teilergebnis mit der Option 3 wird die Funktion Anzahl2 benutzt. Bei der Anzahl2 werden die ausgeblendeten Zeilen nicht mitgezählt, so dass immer eine „normale“ Reihenfolge der Nummerierung entsteht.

Wichtig hierbei ist, dass die erste Zelle der zu filternden Spalte fixiert (Dollarzeichen) wird (hier $H$2) und die variable Zelle (hier ebenfalls H2) an der gleichen Stelle beginnt. Dann die Formel über die gesamte Spalte nach unten ziehen und filtern

 
Hinterlasse einen Kommentar

Verfasst von - 12. November 2018 in Excel

 

Spur zum Vorgänger

Excel bietet die Möglichkeit die Spur von einer Formelzelle zum Vorgänger per Pfeil anzuzeigen. Eigentlich eine sehr praktische Sache, aber wo sind die Formeln alle und wie kann man das über alle Formeln sofort erreichen?

Vorgehen:

  1. in einer leeren Zelle ein Gleichheitszeichen (=) eingeben und das gesamte Tabellenblatt markieren
  2. Fehlermeldungen ignorieren
    1. Die Formel sieht dann so aus: =1:1048576
  3. Jetzt zweimal Spur zum Vorgänger anklicken und alle Formeln zeigen die Spur zum Vorgänger

excel_spur

 
Hinterlasse einen Kommentar

Verfasst von - 12. November 2018 in Excel

 

Datenbank aus Excel einfügen

 

Daten aus Excel in Word einfügen

Möchte man Daten aus einer Ecxeltabelle in Word einfügen, so geht das recht einfach über die Zwischenablage.

  1. In Excel die Daten markieren
  2. Rechte Maustaste Kopieren (oder STRG-C)
  3. In Word rechte Maustaste Einfügeoptionen – gewünschte Option wählen (oder STRG-V und anschließend die Option wählen)

 

Das funktioniert auch einwandfrei. Was aber, wenn man nicht alle Daten benötigt? Man kann in Excel mittels des Autofilters die Daten filtern und nur diese mittels der Zwischenablage einfügen. Das hat jedoch den Nachteil, dass die Daten nicht aktualisiert werden, wenn Änderungen erfolgen.

 

Lösung: Die Daten als Datenbank einfügen.

Vorgehen:

  1. In Word zur Schnellstartleiste den Befehl Datenbank einfügen hinzufügen
  2. Diesen Befehl anschließend anklicken und Daten importieren wählen
  3. Die Datenbank wählen und darauf achten, dass die Option Erste Datenrehe enthält Spaltenüberschriften aktiviert ist
  4. Man könnte jetzt die Daten schon übernehmen, kann aber auch Abfrageoptionen nutzen, um Datensätze zu filtern
  5. Hier die gewünschten Kriterien festlegen (auf UND und ODER achten) evtl. noch sortieren und Felder auswählen
  6. Setzt man das Häkchen bei: Als Feld einfügen, lassen sich die Daten bei einer Änderung in der Exceltabelle mittels F9 (vorher ganze Tabelle markieren) aktualisieren

 

Schulungscenter Pohlmann
Wiesenweg 2
21365 Adendorf

Tel.: 04131/ 28 44 600

Fax: 04131/ 28 45 612

Web: http://scp-pohlmann.de  

 

 

  

 

 
Hinterlasse einen Kommentar

Verfasst von - 29. April 2013 in Excel, Word

 

Große Zahlen in Excel darstellen

In Excel lässt sich so ziemlich alles an Zahlen darstellen, was möglich ist. Demzufolge auch große Zahlen, die im Millionenbereich liegen. Durch das Tausendertrennzeichen lassen sich große Zahlen auch meistens leicht lesen. Z.B.:

5289656 ohne und

5.289.656 mit Tausendertrennzeichen.

Was aber, wenn dort nur 5,29 Mio stehen soll? Dann kommt folgendes benutzerdefiniertes Zahlenformat zur Anwendung:

[>=1000000]#.##0,00.. „Mio“;#.###

oder auch ohne die ersten drei Rauten:

[>=1000000]0,00.. „Mio“;#.###

Excel fragt im ersten Stepp die Zahl in der Zelle ab und wenn diese größer als 1 Million ist, wird entsprechend das neue Format verwendet und wenn nicht, das Standardformat mit Tausendertrennzeichen. Bei diesem Zahlenformat lohnt es sich, mal mit den Punkten hinter 0,00 zu „spielen“.

Als Ergebnis erhält man folgende Ausgabe:

5,29 Mio

 
Hinterlasse einen Kommentar

Verfasst von - 2. März 2013 in Excel

 

Eine in Excel hinterlegte Zahl in Word in einen Text umwandeln (Serienbrief)

Bei einem meiner Kurse kam das Problem auf, dass Spendenbescheinigungen geschrieben werden sollten und dafür mussten einige Formate in der zugrunde liegenden Exceltabelle angepasst werden. Dabei fiel mir auf, dass es auch eine Spalte mit der Spendensumme gab, in der die Summe noch einmal händisch als Text hinterlegt wurde. Dieses wollte ich automatisieren. In Excel gab es keine Möglichkeit, aber dafür im Serienbrief in Word.

Vorgehensweise:

Das entsprechende Datenfeld in Word in den Serienbrief einfügen (Seriendruckfeld heißt hier SPENDE).

Der Wert in hinterlegter Exceltabelle wäre z.B. 35,77 €

Standardansicht in Word-Serienbrief: 35,77

Bearbeitungsansicht für das Feld aufrufen mittels Alt-F9 (Um wieder zurück in die normale Ansicht zu kommen, nochmals Alt-F9 drücken)

Ansicht: {Mergefield SPENDE}

Folgendes eintragen (ROTES):

{Mergefield SPENDE\ *CardText} Ergebnis: sechsunddreißig (klein geschrieben und aufgerundet)

{Mergefield SPENDE\ *CardText\ *Caps} Ergebnis: Sechsunddreißig (Groß geschrieben und aufgerundet)

{Mergefield SPENDE\ *Dollar\ *Upper} Ergebnis: Fünfunddreißig und 77/100

Soll nicht aufgerundet werden, so muss das in der Exceltabelle mit der Funktion Abrunden und einer zusätzlichen Spalte geregelt werden

Gleiches geht auch mit Formularfeldern. Fügt man ein Textformularfeld ein, so kann man auf dieses mittels eines Feldes (Ref) darauf referieren.

Hier schaut es dann so aus:

 

Beispiel 1 { REF „Textmarkenname“ \*DollarText } Ergebnis: Fünfunddreißig und
77/100
oder { REF „Textmarkenname“ \*CardText \*Caps } Ergebnis: Fünfunddreißig

Der Schalter CardText zeigt die Zahl in Worten in Kleinbuchstaben an. Mit Caps zeigt er den Wortanfang großgeschrieben an. Der Schalter Dollartext fügt „und“ hinter der Dezimalstelle ein und zeigt zwei Nachkommastellen in Hundertsteln an.

 
Hinterlasse einen Kommentar

Verfasst von - 6. Januar 2013 in Excel, Word

 

Zeilen und Spalten einfügen

 


Schnell eine/mehrere Zeilen oder Spalten in Excel einfügen

Manchmal kommt es vor, dass schnell noch eine Zeile oberhalb des aktuellen Datenbereichs, oder eine Spalte zum Datenbereich hinzugefügt werden soll.

Das geschieht am einfachsten mit folgenden Tastenkombinationen: STRG und +
(auf dem Nummernblock)

Zuerst einmal den Zellcursor in die Zeile setzen, über der noch schnell eine Zeile eingefügt werden soll. Jetzt mit Shift(Umschalt)-Leertaste die Zeile, oder mit STRG-Leertaste die Spalte markieren. Mit den Tasten STRG und + (Plus) auf dem Nummernblock lassen sich daran anschließen Zeilen oder Spalten einfügen.

Mit STRG und (Minus) auf dem Nummernblock werden Zeilen oder Spalten gelöscht.

 

Mehrere Zeilen oder Spalten gleichzeitig einfügen:

die gewünschte Anzahl der Zeilen oder Spalten mittels der Spalten- oder Zeilenbezeichnung markieren und mit STRG + entsprechend viele einfügen bzw. löschen (STRG -)

 
Hinterlasse einen Kommentar

Verfasst von - 15. Februar 2012 in Excel

 

Datum und Uhrzeit trennen, wenn sich beides in einer Zelle befindet

Trennen von Datum und Uhrzeit in Excel, wenn sich beides in einer Zelle befindet

Hin und wieder kommt es vor, dass sich in einer Zellen Datum und Uhrzeit gleichzeitig befinden. Das kann beim Datenimport geschehen.

Benötigt man jedoch die vorhandenen Werte in getrennten Zellen, so bietet Excel eine Möglichkeit dieses zu automatisieren. Vorgehensweise:

1 Den entsprechenden Datenbereich markieren

2 Im Menüband Daten in der Gruppe Datentools die Option Text in Spalten  auswählen

3 Das Häkchen lassen bei getrennt und auf Weiter klicken

4 Im folgenden Fenster das Häkchen bei Leerzeichen setzen und abermals auf Weiter. Jetzt sieht man schon das Ergebnis und kann auf Fertigstellen klicken.

Die Daten befinden sich nun in getrennten Spalten und es muss nur noch in der Ursprungsspalte das Zahlenformat geändert werden (z.B. Datum kurz).

 

 
3 Kommentare

Verfasst von - 8. Dezember 2011 in Excel