Leitfaden für Excel Inhaltsverzeichnis Grundsätzliches ................................................................................ 3 Nützliche Tastenkombinationen .......................................................... 4 Die oberste Zeile einer Tabelle fixieren ................................................ 5 Eine Seite einrichten (für ein übersichtliches Layout beim Drucken) ........ 6 Autofilter einschalten......................................................................... 8 Zellen formatieren ............................................................................ 9 Tabelle sortieren............................................................................. 10 Felder verketten ............................................................................. 11 WENN-Formel................................................................................. 12 S-VERWEIS .................................................................................... 13 LINKS- (bzw. RECHTS-) Formel ........................................................ 14 TEIL-Funktion................................................................................. 15 Pivot-Tabelle (Erklärungen anhand von Beispielen) ............................. 16 2 Grundsätzliches 1.) Soll die gesamte Spalte oder Zeile ausgewählt werden, reicht es auf den Spalten-/Zeilenkopf (Ziffer bzw. Buchstabe) zu klicken 2.) Immer wenn mit komplexen Formeln gearbeitet wird (S-VERWEIS etc.) sollten die „Inhalte“ der Zellen eingefügt werden. Dies spart Speicherplatz und verhindert einen Verlust der Daten, wenn evtl. eine Datei gelöscht wird. Hierfür: Betroffene Zellen/Spalten/Zeilen markieren Rechtsklick Kopieren Rechtsklick Inhalte einfügen Werte 3 Nützliche Tastenkombinationen - Zelle kopieren: Strg. + C - Zelle einfügen: Strg. + V - Zelle ausschneiden: Strg. + X - Zelle Fett machen: Strg. + Shift + F - Zelle formatieren: Strg. + 1 - Tabelle durchsuchen: Strg. + F - An das Ende der Tabelle springen: Strg. + Pfeil nach unten - An den Anfang der Tabelle springen: Strg. + Pfeil nach oben - Mehrere Zellen markieren: Umschalt + Pfeil (oben, rechts…) - „Autovervollständigen“: Strg. + U (Den Wert einer Zelle für die unteren Zellen übernehmen. Zellen müssen vorher markiert sein.) 4 Die oberste Zeile einer Tabelle fixieren Um die oberste Zeile einer Tabelle zu fixieren (beim Herunterscrollen soll sie oben stehen bleiben), wie folgt vorgehen: - Komplette Zeile unter der zu fixierenden Zeile markieren (hierfür auf die Ziffer der Zeile klicken) - Fenster Fenster fixieren - Jetzt bleiben die fixierten Zeilen beim Herunterscrollen immer sichtbar (Dies funktioniert auch bei 2 oder mehr Zeilen, die fixiert werden sollen) 5 Eine Seite einrichten (für ein übersichtliches Layout beim Drucken) Um die Tabelle beim Ausdrucken kompakt auf eine Seite anzupassen, wie folgt vorgehen: - Datei Seite einrichten - Reiter „Papierformat“ „Orientierung“ (i.d.R. hier Querformat auswählen) - Skalierung Anpassung: „1 Seite breit“ UND bei „Seiten hoch“ LEER LASSEN Falls sich die Tabelle auf mehrere Seiten erstreckt müssen noch „Wiederholungszeilen“ festgelegt werden. Hierfür: 6 - Reiter „Tabelle“ aufrufen „Wiederholungsspalte oben“ anwählen und auf die Ziffer der Zeile klicken 7 Autofilter einschalten Um die Filterfunktion einzuschalten, wie folgt vorgehen: - Daten Filter Autofilter - Nun können die Ergebnisse nach verschiedenen Kriterien gefiltert werden - Über die Funktion „Benutzerdefiniert“ sind auch Eingrenzungen wie z.B.: „Ergebnisse: Größer 10 und kleiner 20“ möglich: - Über das Feld „Alle Anzeigen“ werden alle Filterkriterien aufgehoben. 8 Zellen formatieren Um Zellen (bzw. ganze Spalten/Zeilen) zu formatieren (Farbe, Währung, Zeilenumbruch), wie folgt vorgehen. - Rechtsklick auf Zelle/Spalte/Zeile „Zellen formatieren“ - Nun in den entsprechenden Reitern die jeweiligen Formatierungen vornehmen 9 Tabelle sortieren Um eine Tabelle absteigend oder aufsteigend zu sortieren, wie folgt vorgehen: - Für einfache Sortierung in die entsprechende Spalte klicken, danach auf das „Sortiersymbol“ Soll erst nach Kriterium X und dann nach Kriterium Y sortiert werden, wie folgt vorgehen: - Daten Sortieren - Die verschiedenen Merkmale der Reiha nach auswählen 10 Felder verketten Um mehrere Felder miteinander zu „verketten“ (zusammen in ein Feld schreiben), wie folgt vorgehen: - Folgende Formel in die neue Zelle eintragen (Wichtig: Formel beginnt mit einem „=“-Zeichen): =verketten(A1;B1) - Nacheinander die zu verkettenden Zellen anwählen - Die Zelle sollte im Format „Standard“ sein, um das Ergebnis der Formel anzuzeigen. (Im Format „Text“ würde die Formel wortwörtlich zu sehen sein.) 11 WENN-Formel Mit Hilfe der „Wenn(-Dann)-Formel“ können Zellen automatisch mit einem Wert gefüllt werden, WENN eine andere Zelle eine vorher definierte Bedingung erfüllt. Um die Funktion anzuwenden wie folgt vorgehen: Die Formel besteht aus der Prüfung (WENN die Zelle J1 ist gleich…), dem neuen Wert (DANN schreibe in die Zelle den Wert…) und dem Alternativwert (ansonsten schreibe…) - Folgende Formel in die neue Zelle eintragen =wenn(J1<7000;“Ja“;“Nein“) Es können auch mehrere WENN-Formeln hintereinander gestellt werden. Hierzu: - Anstatt dem „Sonst_Wert“, einfach noch mal eine WENN-Formel eintragen 12 S-VERWEIS Mit Hilfe des S-Verweises können Daten aus verschiedenen Tabellen, anhand einer eindeutigen Verbindung zusammengespielt werden. Hierzu: - In die neue Zelle ein „=“ eintragen - Über die Funktionsleiste (oben links) die Funktion „S-Verweis“ auswählen - Als Suchkriterium die Zelle/den Wert auswählen, der in beiden Tabellen vorhanden ist (Bsp. Art.Nr.) - Als Matrix die Tabelle, in der die Information vorhanden ist auswählen - Der Spaltenindex beschreibt die Spalte, in der die Information vorhanden ist. Hierbei muss die Spalte in Zahlen angegeben werden (Bsp. Spalte B wäre der Spaltenindex 2) - Das Feld „Bereich_Verweis“ muss immer mit „falsch“ gefüllt werden. Mögliche Fehlerquellen: - Die Zellen des Suchkriteriums haben unterschiedliche Formate (Text vs. Standard) - Die Tabellen sind nicht innerhalb derselben Excel-Mappe geöffnet 13 LINKS- (bzw. RECHTS-) Formel Mit Hilfe der LINKS- (bzw. RECHTS-) Formel können Zellen an einer bestimmten Stelle „abgeschnitten“ werden. (Hilfreich, wenn nur die ersten X Stellen einer Zelle benötigt werden.) Hierzu: - In die neue Zelle ein „=LINKS“ (Alternativ:=RECHTS) eintragen - „Text“ bezeichnet die Zelle, die „abgeschnitten“ wird - „Anzahl_Zeichen“ gibt an, nach wie viele Zeichen „abgeschnitten“ werden soll 14 TEIL-Funktion Mit Hilfe der TEIL-Funktion können bestimmte Teile aus einer Zelle „herausgeschnitten“ werden. Hierzu: - In die neue Zelle ein „=TEIL“ eintragen - „Text“ bezeichnet die Zelle, aus der „herausgeschnitten“ wird - „Erstes_Zeichen“ gibt an, ab welcher Stelle angefangen wird - „Anzahl_Zeichen“ gibt an, wie viele Zeichen herausgeschnitten werden 15 Pivot-Tabelle (Erklärungen anhand von Beispielen) Mit Hilfe der Funktion des Pivot können komplexe Auswertungen gefahren werden. Bsp. 1 Sachverhalt: Ermittlung des Gesamtbedarfes pro Artikel (Lagerunabhängig) Artikel A B A C B B A A C Lager Lager1 Lager1 Lager2 Lager1 Lager2 Lager3 Lager3 Lager4 Lager2 Verbrauch 5.000 1.000 36.000 50.000 4.000 8.000 9.000 7.000 25.000 Vorgehensweise: Für Aufrufen des Pivot, irgendeine Zelle der Tabelle anwählen „Daten“ „PivotTable-und PivotChart-Bericht“ Fertig stellen 16 - Das Feld Artikel in die „Zeilenfelder“ ziehen; Das Feld Verbrauch in die Datenfelder ziehen Das Ergebnis sieht folgendermaßen aus: Summe von Verbrauch Artikel Summe A 57000 B 13000 C 75000 Gesamtergebnis 145000 17 Bsp. 2 Sachverhalt: Artikel sind in einer Tabelle mehrfach vorhanden und der Bedarf pro Lagerort soll ermittelt werden. Artikel Lager Verbrauch A Lager1 5.000 B Lager1 1.000 A Lager2 36.000 C Lager1 50.000 B Lager2 4.000 A Lager1 5.000 B Lager1 1.000 A Lager2 36.000 C Lager1 50.000 B Lager2 4.000 (Oberen 5 Zeilen zur Vereinfachung doppelt genommen) Vorgehensweise: Aufrufen des Pivot s.o. Das Ergebnis sieht folgendermaßen aus: Summe von Verbrauch Lager Artikel Lager1 A 10.000 B 2.000 C 100.000 Gesamtergebnis 112.000 Lager2 72.000 8.000 80.000 Gesamtergebnis 82.000 10.000 100.000 192.000 18 Bsp. 3 Sachverhalt: Gesucht wird die Anzahl der Kunden mit neuer/alter Kanbanabwicklung pro Region. Kundennummer 385288 398217 476281 652429 246495 292559 485010 652513 653460 653800 658274 662765 662778 664967 Kundenname Fissek GmbH - KANBAN Hommel-Etamic GmbH Weiss Spindeltech. GmbH Atlantic Zeiser GmbH & Co. DB Regio AG Kanban RZW Heidelberg Postpress Waldorf-Technik GmbH+Co.KG Rückle GmbH EMAG AUTOMATION GmbH Rückle GmbH Marbach Werkzeugbau GmbH Marbach Werkzeug GmbH Marbach Werkzeug GmbH Schmidt Technology GmbH Region 77 30 76 30 62 62 30 23 37 23 70 70 70 30 Neue_Kanbanabwicklung no no no no no no yes yes no yes yes yes yes yes Vorgehensweise: Aufrufen des Pivot s.o. Das Ergebnis sieht folgendermaßen aus: Anzahl von Neue_Kanbanabwicklung Region 9 15 17 18 Neue_Kanbanabwicklung no 6 18 11 67 yes Gesamtergebnis 6 11 29 10 21 25 92 19 Bsp. 4 Sachverhalt: Neben der Artikel-Nr. sollen (in der linken Spalte) noch weitere Informationen ausgewiesen werden. Artikel 17850379 08760261 08760232 16981744 08760119 16981634 16981511 08760368 08760355 VK-Wert pro Bezeichnung Teil DIN 7603 Cu A 15x19x1.5 4.118,40 € DIN 7603 CU A 10x16x1 2.504,75 € DIN 7603 CU A 10x13.5x1 1.582,49 € O-Ring 3,7x1,9 NBR 70 Shore A 651,00 € DIN 7603 CU A 6x10x1 964,49 € O-Ring 4,0x1,0 NBR 70Shore A schwarz 420,00 € O-Ring 9,0x1,2 NBR 70Shore A schwarz 401,20 € DIN 7603 CU A 14x20x1.5 1.565,99 € DIN 7603 CU A 14x18x1.5 2.118,79 € Spanne pro WE pro Teil Teil 2.421,51 € 41,20% 1.325,57 € 47,08% 568,53 € 64,07% 651,00 € 0,00% 379,77 € 60,62% 166,62 € 60,33% 292,99 € 26,97% 1.353,12 € 13,59% 940,16 € 55,63% Vorgehensweise: Aufrufen des Pivot s.o. Ergebnis ist unübersichtlich. Daher: 20 - Rechtsklick auf Spaltenkopf „Artikel“ Feldeigenschaften „Keine“ auswählen Das Ergebnis sieht folgendermaßen aus: 21
© Copyright 2024 ExpyDoc
