Posts Tagged ‘Excel’

Dienstag, 14. Juli 2015

In Excel ein Pivot nach Monat und Jahr gleichzeitig gruppieren

Wer von SQL her kommt und gelegentlich Auswertungen mit Excel fahren muss, wird die Pivot-Funktion zu schätzen wissen: Sie bietet annähernd etwas wie die GROUP BY-Klausel in SQL-Statements.

Kürzlich durfte ich auf der Arbeit die zeitlichen Aufwendungen für eine IT-Prüfung analysieren. Dazu erfasste ich alle Meetings mit ihrer Dauer, ihrem Datum und dem Titel in eine Tabelle. Anschliessend wollte ich mir ausrechnen lassen, wie viele Stunden pro Monat aufgewendet wurden.

Da die Prüfperiode im Juli 2014 begann und im Juli 2015 endete, gab es beim ersten Auswertungsversuch das Problem, dass die Aufwendungen im Juli des Vorjahres mit denjenigen des Julis des aktuellen Jahres addiert wurden. Doch wie macht man statt ein GROUP BY month ein GROUP BY month, year?

Stellt sich heraus, dass man in einer Pivot-Tabelle mittels Rechtsklick auf einen Datumswert im Menupunkt „Grouping“ durchaus auch zwei Elemente in der Liste („By“) auswählen kann, also beispielsweise auch „Months“ und „Years“.

Quelle: Pro tip: Group an Excel PivotTable by dates

Tags: , , , , , , , , , , , , ,
Labels: IT

Keine Kommentare | neuen Kommentar verfassen

Mittwoch, 29. April 2015

Excel-Zellenreferenz nicht anpassen, wenn Zeilen oder Spalten eingefügt werden

Dies geht mit der Funktion INDIRECT(A4): Excel INDIRECT Function. Auch nach dem Einfügen von Zeilen oder Spalten bevor der Zelle mit dem INDIRECT-Befehl wird weiterhin der Wert der Zelle A4 angezeigt.

Tags: ,
Labels: IT

Keine Kommentare | neuen Kommentar verfassen

Donnerstag, 13. November 2014

In Excel-Diagrammen die Zahl 0 als Datenlabel nicht anzeigen

Auf der Arbeit produziere ich hie und da Excel-Diagramme, welche aus gestapelten Balken bestehen. Kürzlich aktivierte ich in einem solchen Diagramm die Datenbeschriftung der einzelnen Balken. Für Balken, deren Wert 0 betrug und deshalb über keine Höhe verfügten, sah das Datenlabel „0“ fürchterlich aus und kam teilweise mit den Achsen und deren Beschriftung in Konflikt. Ist es möglich, Datenlabels nur dann anzuzeigen, wenn sie grösser 0 sind? Ja!

Datenbeschriftungen formatieren

Nach einer kurzen Google-Suche hatte ich die Lösung: Man klicke mittels Rechtsklick auf ein Datenlabel, wähle Datenbeschriftung formatieren… und wähle den Punkt Zahl an. Dort ist die Kategorie auf „Benutzerdefiniert“ zu setzen und als Formatcode folgendes einzugeben:

0;;;

Noch ein Klick auf Schliessen, und die sprichwörtlichen Nullen werden nicht mehr angezeigt.

Das Endresultat sieht im Excel-Diagramm dann folgendermassen aus:

Excel-Diagramm ohne Nullen

Tags: , , , , ,
Labels: IT

4 Kommentare | neuen Kommentar verfassen

Sonntag, 29. Juni 2014

Kolonne A ist in Microsoft Excel kaum sichtbar

Ich verursachte im Büro kürzlich mit Excel einen Betriebsunfall: Auf den ersten Blick sah es zudem so aus, als wäre Kolonne A ausgeblendet worden. Doch alle Hantiererei brachte die Kolonne nicht mehr zum Vorschein. Nach etwas pröbeln realisiert ich, dass die Kolonne immer noch sichtbar war — aber einfach gefühlte 1 Pixel breit. Wenn ich in Kolonne B navigierte und die ausgewählte Zelle mit dem Tastencursor nach links bewegte, wurde der erwartete Wert der Kolonne A angezeigt. Die Spalte konnte aber nicht verbreitert werden. Was nun?

Google half, die Lösung aufzuzeigen:

From your worksheet menu click on Window > Unfreeze Panes. Then select any cell and hit the Home key.

Quelle: column A is missing. not hidden.

Danach war alles wieder gut.

Tags: , , , , , ,
Labels: IT

Keine Kommentare | neuen Kommentar verfassen

Mittwoch, 13. Februar 2013

Mit PHPExcel eine xlsx-Datei parsen und in MySQL importieren

Die einfachste Methode, die sich nach dem Studium der Dokumentation und Stackoverflow-Posts zum Thema PHPExcel ergeben hat, greift auf die PHPExcel_Worksheet::toArray()-Funktion zurück:

$objReader = PHPExcel_IOFactory::createReader(PHPExcel_IOFactory::identify($path));
$objReader->setReadDataOnly(true);

$objPHPExcel = $objReader->load($path);

$sheets = $objPHPExcel->getSheetNames();	
$sheetsRev = array_flip($sheets);

if(!isset($sheetsRev['Queries'])) {
	die('$sheetsRev[Queries] not set');
}

$objSheet = $objPHPExcel->setActiveSheetIndex($sheetsRev['Queries']);

$data = array();
foreach($objSheet->toArray() as $rowNum=>$row) {
	foreach($row as $cellNum=>$value) {
	...
	}
}

Es gibt eine andere Lösung, welche aber erforderlich macht, dass man die Dimension der einzulesenden Daten kennt; sprich die Anzahl der Spalten und Zeilen müssen vor der Iteration ausfindig gemacht werden. Mit toArray() überspringt man diesen unnötigen Schritt.

Tags: , , , ,
Labels: Programmierung

1 Kommentar | neuen Kommentar verfassen

Freitag, 30. November 2012

Eine Liste in Excel nach eindeutigen Werten filtern

Da hat man also eine ellenlagen Listen von Zahlen oder Wörtern und möchte keine Duplikate angezeigt bekommen? Kein Problem (Excel 2007, englisch):

  1. Alle Zellen mit den zu filternden Inhalten markieren
  2. Sort & Filter
  3. Advanced
  4. [x] Unique records only

Fertig.

Tags: , ,
Labels: Arbeit

Keine Kommentare | neuen Kommentar verfassen

Donnerstag, 26. Juli 2012

Access stellt importierte Excel-Zeilenschaltungen als Sonderzeichen dar

Microsoft-Produkte weisen seit Jahrzehnten kompromisslose Interoperabilität auf:

As you have found, Excel uses only the line feed character (ASCII 10) to indicate a line break, while Access uses carriage return + line feed (ASCII 13 and ASCII 10).

Damit die Zeilenschaltungen auch in Access als Zeilenschaltungen angezeigt werden, führt man nach dem Import einer Excel-Datei in eine Access-Tabelle folgendes Query aus:

UPDATE Feststellungen SET Feststellung = Replace(Feststellung,Chr(10),Chr(13) & Chr(10));

Via: Import Text from Excel with line breaks (2003)

Tags: , , , , , ,
Labels: IT

Keine Kommentare | neuen Kommentar verfassen

Mittwoch, 18. Juli 2012

Microsoft Excel beschwert sich über „Too many different cell formats“

Es vergeht keinen Tag am Bürorechner, an welchem man nicht eine neue Fehlermeldung in Microsoft-Produkten präsentiert kriegt – und sich beim der Recherche zum Fehler wieder einmal wünschen würde, jeden einzelnen Entwickler in Redmond einen unendlich langsamen Tod sterben zu lassen.

Microsoft gibt seinen Endanwendern die grandiose Möglichkeit, jede einzelne Tabellenzeile mit einem individuellen Format zu versehen. Rahmendicke, Hintergrundfarben, Schriftschnitte können individuell definiert werden. Und jede so formatierte Zelle ergibt ein neues Zellformat, welches in der Excel-Datei zentral gesichert wird.

Das Schöne daran ist, dass es offenbar Endanwender in Schwellenländern gibt, welche es bei der Erledigung von ausgelagerten Arbeiten fertigbringen, annähernd 65’000 Formate in einer einzigen Excel-Datei mit ca. 20 Sheets unterzubringen. Heilige Shiva! Das Problem: Excel kriegt massiven Schluckauf, sobald diese Limite (wahrscheinlich mit der Grenze für unsigned integers) erreicht ist – nichts geht mehr, keine Zellformatierung, kein Zusammenführen von Zellen, ja nicht einmal kopieren geht mehr. Und dazu die lapidare Fehlermeldung:

Too many different cell formats.

Die Lösung ist im Grunde simpel: Das Dokument muss analysiert und nicht verwendete Styles gelöscht werden. Dafür gibt es mittlerweile unzählige Varianten, davon hier nur drei:

  • Kommerziell. 39 Dollar will der Entwickler des Style Reduction Tools haben, damit die Excel-Endanwender wieder arbeiten und nicht Excel-Fehlermeldungen wegglicken und Abstürze überwinden müssen.
  • Handgestrickt. Auf Stackoverflow findet sich VBA-Code, welchen man mit entsprechenden Kenntnissen über eine Excel-Datei laufen lassen kann. Fallstricke:
    • .xlsx-Dateien müssen zuerst in .xlsm-Dateien umkonvertiert werden
    • Danach muss man das Developer-Ribbon aktivieren
    • Anschliessend fügt man ein Modul in die Excel-Datei ein
    • Nun kopiert man den Source-Code in das Modul hinein
    • Schlussendlich muss unter Tools > References noch „Microsoft Scripting Runtime“ aktiviert werden
    • Jetzt kann man die Funktion starten, fliegt aber rasch auf die Schnauze, weil Dim iStyleCount As Integer als Integer definiert ist und deshalb überläuft
    • Nachdem der Laptop für ca. 20-30 Minuten wie totgefroren ist, erscheint dann die erlösende Meldung: Statt der ursprünglich 64999 gefundenen Styles sind nach der Bereinigung nur noch effektiv 10 verwendete Stück im Dokument
  • Beschleunigt. Da sich Microsoft des Problems bewusst ist, verlinken sie in ihrem KB-Artikel zur Fehlermeldung auf ein kostenfreies Tool eines unabhängigen Entwicklers (.NET4.0 version of the XLStylesTool is now available), welcher die Styles auf OOXML-Ebene säubert, was einen massiven Geschwindigkeitsgewinn gegenüber der VBA-Version ermöglicht. WICHTIG: Nachdem die Applikation gestartet und die Datei ausgewählt wurde, muss zwingend [x] „Commit changes …“ ausgewählt werden, damit die Anpassungen gespeichert werden können.

VBA-Code

Via: Too Many Different Cell Formats, ergänzt/überarbeitet von eMeidi.com

' Description:
'    Borrowed largely from http://www.jkp-ads.com/Articles/styles06.asp
' Additional debug message piping to .txt file in C:\ by eMeidi.com
' Bugfixing iStyleCount from Integer to Long by eMeidi.com

Option Explicit

' Description:
'    This is the "driver" for the entire module.
Public Sub DropUnusedStyles()

    Dim styleObj As Style
    Dim rngCell As Range
    Dim wb As Workbook
    Dim wsh As Worksheet
    Dim str As String
    Dim iStyleCount As Long
    Dim dict As New Scripting.Dictionary    ' <- from Tools / References... / "Microsoft Scripting Runtime"

    ' wb := workbook of interest.  Choose one of the following
    ' Set wb = ThisWorkbook ' choose this module's workbook
    Set wb = ActiveWorkbook ' the active workbook in excel
    
    Dim strPath As String
    strPath = "C:\excelFormatCleanerDebug.txt"
    Dim f
    f = FreeFile()
    Open strPath For Output As f
    'Print #f, Text

    Debug.Print "BEGINNING # of styles in workbook: " & wb.Styles.Count
    'GoTo Bla
    
    ' dict := list of styles
    For Each styleObj In wb.Styles
        str = styleObj.NameLocal
        iStyleCount = iStyleCount + 1
        Call dict.Add(str, 0)    ' First time:  adds keys
        'Debug.Print str & ", ";
        Print #f, str & ", "
    Next styleObj
    Debug.Print "  dictionary now has " & dict.Count & " entries."
    ' Status, dictionary has styles (key) which are known to workbook
    'GoTo Bla
    
    ' Traverse each visible worksheet and increment count each style occurrence
    For Each wsh In wb.Worksheets
        If wsh.Visible Then
            For Each rngCell In wsh.UsedRange.Cells
                str = rngCell.Style
                dict.Item(str) = dict.Item(str) + 1     ' This time:  counts occurrences
                Print #f, str & "+1"
            Next rngCell
        End If
    Next wsh
    ' Status, dictionary styles (key) has cell occurrence count (item)


    ' Try to delete unused styles
    Dim aKey As Variant
    On Error Resume Next    ' wb.Styles(aKey).Delete may throw error

    For Each aKey In dict.Keys

        ' display count & stylename
        '    e.g. "24   Normal"
        Debug.Print dict.Item(aKey) & vbTab & aKey
        Print #f, dict.Item(aKey) & vbTab & aKey

        If dict.Item(aKey) = 0 Then
            ' Occurrence count (Item) indicates this style is not used
            Call wb.Styles(aKey).Delete
            If Err.Number <> 0 Then
                Debug.Print vbTab & "^-- failed to delete"
                Err.Clear
            End If
            Call dict.Remove(aKey)
        End If

    Next aKey

    Debug.Print "ENDING # of style in workbook: " & wb.Styles.Count
Bla:
End Sub

Tags: , , , , , , ,
Labels: IT, Programmierung

Keine Kommentare | neuen Kommentar verfassen

Donnerstag, 29. März 2012

Mit Access eine Textdatei mit Pipe-Trennzeichen einlesen

Will man unter Access 2007 eine Textdatei einlesen, welche Datensätze enthält, dessen Felder mit einem Strich (engl. „Pipe“) getrennt sind, muss man folgenden Trick beachten:

Damit das Pipe-Symbol in das Trennzeichenfeld eingeben kann, muss der Inhalt des leeren Feldes zuerst mit der Maus ausgewählt werden (Ctrl-A). Erst dann kann das Symbol eingegeben werden:

Quelle: Import Text file with Pipe Delimited – 2007

Tags: , , , ,
Labels: IT

Keine Kommentare | neuen Kommentar verfassen