Dieser Tipp ist eine Erweiterung des Excel-Tipps der letzten Woche. Mit dem Befehl Gehe zu Spezial können Sie die leeren Zellen aus einem Bereich auswählen. Dies ist eine großartige Excel-Technik zum Bereinigen von Daten in einer Pivot-Tabelle.
Pivot-Tische sind wunderbare Werkzeuge. Was aber, wenn Ihre Pivot-Tabelle nur ein Zwischenschritt bei Ihren Datenmanipulationen ist und Sie die zusammengefassten Daten in der Pivot-Tabelle weiter nutzen möchten? Früher habe ich regelmäßig 5000 Datenzeilen von einem älteren Mainframe-System empfangen. Diese Daten enthalten Spalten für Produkt, Monat, Region, Status und Menge. Ich musste die Daten in einer Pivot-Tabelle zusammenfassen, wobei die Monate oben, das Produkt, die Region und der Status in den Zeilen liegen. Pivot-Tabellen machen dies zu einem Kinderspiel.
Von dort musste ich die Daten aus der Pivot-Tabelle nehmen und in Access importieren. Wie Sie sehen, ist die Standard-Pivot-Tabelle hierfür nicht geeignet. Excel verwendet einen Gliederungsstil, sodass jede Region nur einmal angezeigt wird und leere Zellen bis zur Zwischensumme der Region folgen. Zellen im Datenbereich ohne Daten sind leer anstatt numerisch. Ich möchte auch nicht die Zwischensummen in den Daten.
Ab Excel 97 gibt es Optionen für die Pivot-Tabelle, um einige dieser Verhaltensweisen zu steuern.
Doppelklicken Sie im Dialogfeld "Pivot-Tabellenlayout" auf den Feldnamen für die Region. Klicken Sie im Abschnitt Zwischensummen auf das Optionsfeld für "Keine". Dadurch werden die unerwünschten Zwischensummen entfernt.
In Excel 2000 gelangen Sie über die Optionsschaltfläche zu einem Dialogfeld, in dem Sie "Für leere Zellen anzeigen:" angeben und eine Null eingeben können, damit Sie keine Tabelle mit leeren Zellen haben.
Um die Pivot-Tabelle weiter zu bearbeiten, müssen Sie festlegen, dass es sich nicht mehr um eine Pivot-Tabelle handelt. Wenn Sie versuchen, Zellen in einer Pivot-Tabelle zu ändern, werden Sie von Excel darüber informiert, dass Sie einen Teil einer Pivot-Tabelle nicht ändern können. Gehen Sie folgendermaßen vor, um von einer Pivot-Tabelle zu nur Werten zu wechseln:
- Bewegen Sie den Zellenzeiger außerhalb der Pivot-Tabelle.
- Wenn die Pivot-Tabelle in Zeile 1 beginnt, fügen Sie eine neue Zeile 1 ein.
- Beginnen Sie mit der Hervorhebung in der neuen leeren Zeile 1 und ziehen Sie sie nach unten, um die gesamte Pivot-Tabelle auszuwählen.
- Führen Sie Strg + C (oder Bearbeiten - Kopieren) aus, um diesen Bereich zu kopieren.
- Führen Sie bei ausgewähltem Bereich Bearbeiten - Spezial einfügen - Werte - OK aus, um die Pivot-Tabelle in einen statischen Wert zu ändern.
Aber hier ist der wahre Tipp für diese Woche. Jennifer schreibt
Ich bin ständig auf das Problem gestoßen, die Pivot-Tabellenoption zu verwenden, um Unmengen von Daten zusammenzufassen, aber sie füllt nicht die Zeilen unter jeder Änderung der Zeilenkategorie aus. Wissen Sie, wie Sie die Pivot-Tabelle unter jeder Änderung der Kategorie ausfüllen können? Ich musste jeden Code ziehen und nach unten kopieren, damit ich mehr Pivot-Tabellen oder Sortierungen durchführen kann. Ich habe versucht, die Optionen in der Pivot-Tabelle zu ändern, ohne Erfolg.
Die Antwort ist nicht leicht zu lernen. Es ist nicht intuitiv. Aber wenn Sie es hassen, diese Zellen nach unten zu ziehen, werden Sie sich gerne die Zeit nehmen, diesen Prozess zu lernen! Folgen Sie uns - es scheint lang und langwierig zu sein, aber es funktioniert wirklich wirklich. Sobald Sie es erhalten haben, können Sie dies in 20 Sekunden tun.
Hier gibt es tatsächlich 2 oder 3 neue Tipps. Angenommen, Sie haben links zwei Spalten im Umrissformat, die ausgefüllt werden müssen. Markieren Sie von Zelle A3 bis zur Zelle B999 (oder was auch immer Ihre letzte Datenzeile ist).
Trick Nr. 1. Auswahl aller leeren Zellen in diesem Bereich.
Drücken Sie Strg + G, alt = "" + S + K und geben Sie dann ein. huh?
Strg + G ruft den GoTo-Dialog auf
Alt + S wählt die Schaltfläche "Spezial" aus dem Dialogfeld
Der Goto-Special-Dialog ist eine großartige Sache, die nur wenige kennen. Drücken Sie "k", um "Leerzeichen" auszuwählen. Drücken Sie die Eingabetaste oder klicken Sie auf OK, und Sie haben jetzt nur alle leeren Zellen in den Umrissspalten der Pivot-Tabelle ausgewählt. Dies sind alle Zellen, die Sie ausfüllen möchten.
Trick Nr. 2. Beobachten Sie dabei nicht den Bildschirm - er ist zu beängstigend und verwirrend.
Drücken Sie die Gleichheits-Taste. Drücke den Aufwärtspfeil. Halten Sie die Strg-Taste gedrückt und drücken Sie die Eingabetaste. Wenn Sie gleich treffen, sagt der Aufwärtspfeil: "Ich möchte, dass diese Zelle genau wie die Zelle über mir ist." Wenn Sie die Strg-Taste gedrückt halten, wenn Sie die Eingabetaste drücken, wird Folgendes angezeigt: "Geben Sie in jede ausgewählte Zelle dieselbe Formel ein. Dank Trick 1 sind dies alle leeren Zellen, die wir ausfüllen wollten.
Trick # 3. Was Jennifer bereits weiß, aber der Vollständigkeit halber hier ist.
Sie müssen jetzt alle diese Formeln in Werte ändern. Wählen Sie erneut alle Zellen in A3: B999 aus, nicht nur die Leerzeichen. Drücken Sie Strg + C, um diesen Bereich zu kopieren. Drücken Sie alt = "" + E und dann sv (Enter). Sonderwerte dieser Formeln einfügen.
Ta-da! Sie werden nie wieder einen Nachmittag damit verbringen, Spaltenüberschriften in einer Pivot-Tabelle manuell herunterzuziehen.
