In dieser Lektion des MATLAB-Kurses zeige ich Ihnen, wie Sie Daten aus MS Excel Dateien in MATLAB importieren, wie Sie Daten sortieren und filtern und wie Sie Daten wieder in MS Excel-Dateien speichern.
Inhaltsverzeichnis
Daten aus Excel nach MATLAB importieren
Daten, die in Excel-Dateien gespeichert sind können Sie über den Befehl xlsread(dateiname,arbeitsblatt,zellen) in MATLAB einlesen. Um den Befehl zu nutzen, muss MS Excel auf Ihrem Computer installiert sein. Die Parameter arbeitsblatt und zellen können (auch einzeln) weggelassen werden. Beim Parameter zellen steht 'C5:E9' für den Bereich der Zellen C5 bis E9.
Zur Demonstration der Funktion xlsread habe ich eine Excel-Tabelle mit Stundenbuchungen vorbereitet. Diese soll im Folgenden ausgewertet werden. Die Tabelle kann hier mit allen anderen Beispielen dieser Lektion heruntergeladen werden.
Über die folgende Zeile können, Sie die Excel-Arbeitsmappe nach Matlab importieren:
>> [num,txt,raw] = xlsread('Arbeitszeiten.xlsx');
Rückgabewerte von xlsread
Wie Sie sehen gibt die Funktion die drei Ausgabewerte num, txt und raw zurück.
Der Rückgabewert num enthält ausschließlich die Zahlen der Tabelle. Die Zahlen werden in einer Matrix gespeichert. Diese enthält nur die Einträge der Excel-Tabelle ab der ersten Zeile mit mindestens einer Zahl. In unserem Beispiel werden also die Überschriften ausgeblendet. Tauchen in der Excel-Tabelle zwischendurch Zellen auf, die keine Zahl enthalten, so wird für diese in Matlab jeweils der Wert NaN (Not a Number – keine Zahl) gesetzt.
Der Rückgabewert txt ist ein cell array, das ausschließlich die Zellen enthält, die in der Excel-Tabelle mit Text gefüllt sind. In unserem Beispiel besteht txt nur aus einer Zeile und enthält die Überschriften der Tabelle.
Der Rückgabewert raw ist ebenfalls ein cell array und enthält alle Zellen der Tabelle. Die Größe von raw richtet sich nach der unteren rechten Ecke der Excel-Tabelle.
TIPP: Ich empfehle Ihnen, die Rückgabewerte num und txt bei einfacheren Tabellen wie der hier vorliegenden zu verwenden. Bei komplexeren Tabellen empfehle ich zur Fehlervermeidung die Nutzung des Rückgabewerts raw und die anschließende Konvertierung der enthaltenen Zahlen.
Im Folgenden führe ich die beiden Varianten anhand beispielhafter Auswertungen der Daten vor.
Nutzung der Rückgabewerte num und txt
Ziel des ersten Beispiels ist eine Übersicht über die in den verschiedenen Projekten angefallenen Stunden zu erhalten.
clear
clc
[num,txt] = xlsread('Arbeitszeiten.xlsx');
datum = datestr(datetime(num(:,1),'ConvertFrom','excel'));
arbeitszeiten.gesamt = num(:,6)*24;
arbeitszeiten.gesamt(isnan(arbeitszeiten.gesamt)) = 0;
for idProjekt = 7:size(num,2)
projektname = strrep(txt{idProjekt},' ','_');
arbeitszeiten.(projektname) = num(:,idProjekt)*24;
arbeitszeiten.(projektname)(isnan(arbeitszeiten.(projektname))) = 0;
monatssumme.(projektname) = sum(arbeitszeiten.(projektname));
end
zeitenAlsMatrix = cell2mat(struct2cell(monatssumme));
figure
pie(zeitenAlsMatrix,strrep(fieldnames(monatssumme),'_',' '))
title('Verteilung der Arbeitszeit auf Projekte')
In Zeile 5 wird die Tabelle in die Variablen num und txt geladen.
In Zeile 6 werden die Datumsangaben aus der Excel-Tabelle zuerst über datetime(num(:,1),'ConvertFrom','excel') in das Matlab-eigene Datenformat übertragen. Diese Zahlen werden in der gleichen Befehlszeile über datestr in einen Text umgewandelt. Dieser wird im Beispiel nicht weiter benutzt, aber es mag für Sie sinnvoll sein zu wissen, wie es geht.
Das Zeitformat aus Excel kann durch Multiplikation mit 24 in die Einheit Stunden umgewandelt werden. Dies geschieht in Zeile 8 und 13. Außerdem werden die verbuchten Stunden in eine Struktur mit den Feldnamen gesamt bzw. dem Namen des Projekts gespeichert. Dies geschieht in einer for-Schleife. Diese läuft automatisch über alle Spalten der Matrix mit einer Spaltennummer größer als 7.
In Zeile 9 und 14 werden die Einträge, die NaN enthalten durch 0 ersetzt. Sie können die Einträge einer Matrix m, die NaN enthalten durch die Funktion isnan(m) auffinden. Der Rückgabewert ist eine logische Matrix und kann folglich auch zur sogenannten logischen Indizierung verwendet werden. m(isnan(m)) = 0; bewirkt, dass alle NaNs durch 0 ersetzt werden.
Die Funktion struct2cell in Zeile 18 wandelt eine Struktur in ein Cell Array um. Die Funktion cell2mat wandelt ein Cell Array in eine Matrix um. Zusammen wandelt die Funktion cell2mat(struct2cell(s)) also die Struktur s in eine Matrix um. Ich empfehle Ihnen etwas mit diesen Funktionen zu spielen. Es ist häufig nötig, dass Sie Ihre Daten von einer Organisation in eine andere überführen.
In Zeile 19 bis 21 werden die verbuchten Stunden als Tortendiagramm (Befehl pie) dargestellt. Die Ausgabe sieht so aus:
Nutzung des Rückgabewerts raw
Ziel des zweiten Beispiels ist die Darstellung der Verteilung der Arbeitszeiten auf die verschiedenen Wochentage in einem Säulendiagramm.
clear
clc
[~,~,raw] = xlsread('Arbeitszeiten.xlsx');
% Konvertierung zu num und txt
num = cell2mat(raw(2:end,:));
txt = raw(1,:);
datum = datetime(num(:,1),'ConvertFrom','excel');
arbeitszeiten.gesamt = num(:,6)*24;
arbeitszeiten.gesamt(isnan(arbeitszeiten.gesamt)) = 0;
[DayNumber,DayName] = weekday(datum);
mittelProTag = zeros(1,7);
for idTag = 1:7
mittelProTag(idTag) = mean(arbeitszeiten.gesamt(DayNumber==idTag));
end
figure
bar(1:7,mittelProTag)
set(gca,'xtick',1:7,'xticklabel',{'So','Mo','Di','Mi','Do','Fr','Sa'})
In Zeile 30 werden die zweite bis letzte Zeile des cell arrays raw über den Befehl cell2num vom Typ cell in die Matrix num umgewandelt. Diese entspricht der gleichnamigen Matrix aus dem ersten Beispiel.
In Zeile 31 wird die erste Zeile des cell arrays raw extrahiert und der Variablen txt zugewiesen. Auch diese entspricht der gleichnamigen Variablen aus dem ersten Beispiel.
In Zeile 33 werden die Daten vom Excel-Format in das Matlab-Format konvertiert. In Zeile 38 wird daraus der Wochentag berechnet (Befehl weekday).
In Zeile 42 wird für jeden Wochentag die Summe der Arbeitsstunden, die an dem jeweiligen Wochentag gearbeitet wurden ermittelt. Dabei läuft die Variable idTag in einer for-Schleife über die Werte 1 bis 7. Der Ausdruck
DayNumber==idTag
erzeugt einen Vektor mit dem Wert 1 (true) an den Stellen, an denen DayNumber den Wert idTag hat und mit dem Wert 0 an den Stellen, an denen DayNumber nicht den Wert idTag hat. Dieser Vektor kann zur sogenannten logischen Indizierung verwendet werden. Der Ausdruck
arbeitszeiten.gesamt(DayNumber==idTag)
ergibt die Werte von arbeitszeiten.gesamt, an denen der Ausdruck DayNumber==idTag den Wert 1 hat. Sie können durch Setzen eines Haltepunkts (Klick auf die Zeilennummer im Editor) in Zeile 42 das Skript bei jedem Erreichen von Zeile 42 anhalten. Dann können Sie die einzelnen Ausdrücke markieren und durch Drücken der Taste F9 (Mac: Shift-F7) den markierten Ausdruck ausführen. So können Sie sich die Zusammenhänge zwischen den Ausdrücken klarmachen.
In Zeile 46 wird ein Balkendiagramm erzeugt. Diesem werden in Zeile 47 noch die Wochentage als Achsenbeschriftung zugewiesen.
Das Diagramm sieht so aus:
Daten sortieren
Um Daten in Matlab zu sortieren verwenden Sie den Befehl sort. Diesen können Sie sowohl auf Vektoren oder Matrizen mit Zahlen als auch auf cell arrays mit Text anwenden.
Einen Vektor sortieren
Einen Vektor mit Zahlen können Sie entweder in aufsteigender oder in absteigender Reihenfolge sortieren.
Aufsteigend:
>> a = [4 7 9 1 5 2 4 4 8 -5];
>> b = sort(a)
b =
-5 1 2 4 4 4 5 7 8 9
Absteigend:
>> a = [4 7 9 1 5 2 4 4 8 -5];
>> b = sort(a,'descend')
b =
9 8 7 5 4 4 4 2 1 -5
Vektor, Matrix oder Cell Array nach anderem Vektor sortieren
Angenommen, Sie haben eine Liste mit Artikelnamen, zugehörigen Preisen und Verkaufszahlen pro Tag und wollen beides nach dem Preis sortieren. Die Funktion sort gibt dazu als zweiten Ausgabewert die Indizes des sortierten Vektors zurück. Diese können Sie nutzen um einen anderen Vektor oder ein Cell Array in die gleiche Reihenfolge zu bringen. Das folgende Beispiel demonstriert dies:
artikelname = {'Seife','Zahnpasta','Schokolade',...
'Kartoffelchips','Batterien'};
preis = [1.17,0.89,2.04,1.99,3.12];
verkaufszahlenProTag = [...
83 46 37 44 65;...% Tag 1
2 55 63 45 38;...% Tag 2
5 30 79 31 82;...% ...
17 75 9 51 54;...
65 19 93 52 36;...
74 69 78 82 94;...
65 19 49 80 88];
[preisSortiert,idSort] = sort(preis,'descend')
Dieser erste Abschnitt erzeugt folgende Ausgabe:
preisSortiert =
3.1200 2.0400 1.9900 1.1700 0.8900
idSort =
5 3 4 1 2
Der Rückgabewert idSort lässt sich so verstehen, dass das Element Nummer 5 das größte, Element 3 das zweitgrößte usw. ist. Der Vektor idSort kann nun dazu verwendet werden auch die Artikelnamen sowie die Verkaufszahlen zu sortieren:
artikelnameSortiert = artikelname(idSort)
verkaufszahlenSortiert = verkaufszahlenProTag(:,idSort)
Dieser zweite Abschnitt erzeugt folgende Ausgabe:
artikelnameSortiert =
'Batterien' 'Schokolade' 'Kartoffelchips' 'Seife' 'Zahnpasta'
verkaufszahlenSortiert =
65 37 44 83 46
38 63 45 2 55
82 79 31 5 30
54 9 51 17 75
36 93 52 65 19
94 78 82 74 69
88 49 80 65 19
Daten filtern
Das Filtern von Daten funktioniert in MATLAB immer nach dem gleichen Schema. Zunächst wird eine logische Matrix id (Typ: bool, Werte: 1 (true), 0 (false)) erzeugt, der genau so viele Einträge enthält wie die zu filternde Variable v (Matrix, Vektor oder Cell Array). An den Stellen, die herausgefiltert werden sollen, muss eine 1 (true) stehen. Über den Ausdruck v(id) erhält man die gefilterten Daten.
BEISPIEL:
v = [1 2 3 4 5 6 7 8]
id = [false,true,false,false,true,true,false,true]
v(id)
erzeugt folgende Ausgabe:
v =
1 2 3 4 5 6 7 8
id =
0 1 0 0 1 1 0 1
ans =
2 5 6 8
Filtern über größer, kleiner, gleich
Im obigen Beispiel wurde die logische Variable durch direkte Deklaration mit den Werten true und false erzeugt um das Prinzip der sogenannten logischen Indizierung zu veranschaulichen. Meist wird die logische Variable allerdings durch einen zu berechnenden Ausdruck definiert. Will man beispielsweise alle Werte erhalten, die größer oder gleich 3 sind, so definiert man sie über folgenden Ausdruck:
v = [1 2 3 4 5 6 7 8]
id = v>=3 % nimmt den Wert true für v>=3 an
v(id)
Die Ausgabe sieht dann so aus:
v =
1 2 3 4 5 6 7 8
id =
0 0 1 1 1 1 1 1
ans =
3 4 5 6 7 8
TIPP: Sie müssen die Variable id nicht in einer eigenen Zeile definieren, sondern können den definierenden Ausdruck auch direkt zur Indizierung verwenden:
v = [1 2 3 4 5 6 7 8]
v(v>=3)
Für den Vergleich können Sie die folgenden Operatoren verwenden:
| Operator | Bedeutung |
> |
größer als |
< |
kleiner als |
>= |
größer oder gleich |
| <= | kleiner oder gleich |
== |
gleich |
Filtern über Text
Neben dem Filtern über Matrizen kann auch ein Cell Array mit Texteinträgen verwendet werden. Hier funktioniert der Vergleich allerdings nicht über oben aufgeführten Operatoren, sondern über die Funktion strcmp.
BEISPIEL:
kategorie = {'Non-Food','Non-Food','Food','Food','Non-Food'}
verkaufszahlenProTag = [...
83 46 37 44 65;...% Tag 1
2 55 63 45 38;...% Tag 2
5 30 79 31 82;...% ...
17 75 9 51 54;...
65 19 93 52 36;...
74 69 78 82 94;...
65 19 49 80 88];
idFood = strcmp(kategorie,'Food')
idNonFood = ~idFood
summenFood = sum(verkaufszahlenProTag(:,idFood),2);
summenNonFood = sum(verkaufszahlenProTag(:,idNonFood),2);
disp(array2table([summenFood,summenFood],...
'VariableNames',{'Food','NonFood'}));
erzeugt folgende Ausgabe:
kategorie =
'Non-Food' 'Non-Food' 'Food' 'Food' 'Non-Food'
idFood =
0 0 1 1 0
idNonFood =
1 1 0 0 1
Food NonFood
____ _______
81 81
108 108
110 110
60 60
145 145
160 160
129 129
Die Variable idFood, die in Zeile 99 definiert wird, hat an den Stellen, an denen die Variable kategorie den Wert ‚Food‘ hat, den Wert true (1).
Die Variable idNonFood wird in Zeile 100 aus der Variablen idFood definiert. Der Operator ~ vertauscht dabei die Werte true und false. Der Operator entspricht der logischen Operation der Verneinung.
Filtern über ismember
Die Matlab-Funktion ismember(a,b) gibt zurück, an welchen Stellen die Matrix oder das Cell Array a Werte aus b enthält. Der Rückgabewert ist ein logischer Vektor. Dieser kann zur logischen Indizierung verwendet werden.
BEISPIEL:
artikelname = {'Seife','Zahnpasta','Schokolade',...
'Kartoffelchips','Batterien'};
preis = [1.17,0.89,2.04,1.99,3.12];
idSeifeSchokolade = ismember(artikelname,...
{'Seife','Schokolade'})
preis(idSeifeSchokolade)
erzeugt folgende Ausgabe:
idSeifeSchokolade =
1 0 1 0 0
ans =
1.1700 2.0400
Filtern über mehrere Bedingungen
In den obigen Beispielen habe ich Ihnen gezeigt, wie sie aus einzelnen Bedingungen wie beispielsweise einem Textvergleich, einem Zahlenvergleich oder über die Funktion ismember einen logischen Vektor erzeugen, der dann zur logischen Indizierung eines Vektors oder eines Cell Arrays verwendet werden kann.
Wenn Sie mehrere Bedingungen gleichzeitig erfüllen wollen, so können Sie dies über die logischen Verknüpfungen und, oder sowie Negation erreichen. Diese logischen Operationen werden in Matlab über die folgenden Operatoren vorgenommen:
| Operator | Bedeutung |
& |
und |
| |
oder |
~ |
Negation (Verneinung) |
BEISPIELE:
Beispiel 1:
preis = [1.17,0.89,2.04,1.99,3.12];
idGroesser1Kleiner3 = (preis>1) & (preis<3) %und
preis(idGroesser1Kleiner3)
filtert alle Preise heraus, die größer als 1 sind und kleiner als 3 sind. Das Beispiel erzeugt folgende Ausgabe:
idGroesser1Kleiner3 =
1 0 1 1 0
ans =
1.1700 2.0400 1.9900
Beispiel 2:
idKleiner1Groesser3 = (preis<1) | (preis>3) %oder
preis(idKleiner1Groesser3)
filtert alle Preise heraus, die kleiner als 1 sind oder größer als 3 sind. Das Beispiel erzeugt folgende Ausgabe:
idKleiner1Groesser3 =
0 1 0 0 1
ans =
0.8900 3.1200
Dieselbe Ausgabe wie im letzten Beispiel können Sie auch durch logische Negation (Verneinung) des logischen Vektors aus dem ersten Beispiel erhalten:
idKleiner1Groesser3 = ~idGroesser1Kleiner3 %nicht
preis(idKleiner1Groesser3)
erzeugt dieselbe Ausgabe wie das vorhergehende Beispiel.
Daten nach MS Excel schreiben
Über den Matlab-Befehl xlswrite(dateiname,daten,arbeitsblatt,zelle) können Sie Daten aus Matlab in eine MS Excel Arbeitsmappe schreiben. Die Variable daten darf dabei eine Matrix oder ein cell array mit Text oder Zahlen enthalten. Die Variablen arbeitsblatt und zelle sind optional und können (auch einzeln) weggelassen werden.
TiPP:
Auf dem MAC funktioniert die Funktion xlswrite nicht für cell arrays. Sie können sich hier eine verbesserte Variante herunterladen.
BEISPIEL:
ueberschr = {'Artikel','Montag','Dienstag',...
'Mittwoch','Donnerstag','Freitag'};
zeilenn = {'Zahnpasta';'Schokolade';'Batterien';'Summe'};
daten = randi(100,[3,5]);
daten(end+1,:) = sum(daten,1);
xlswrite('ausgabe.xlsx',ueberschr,'Ausgabeblatt')
xlswrite('ausgabe.xlsx',zeilenn,'Ausgabeblatt','A2')
xlswrite('ausgabe.xlsx',daten,'Ausgabeblatt','B2')
schreibt die Überschriften der Variablen ueberschr, die Zeilennamen der Variablen zeilenn sowie die daten der Variable daten in die Excel Arbeitsmappe ausgabe.xlsx auf das Arbeitsblatt Ausgabeblatt.
Download der Beispiele
Alle Beispiele dieser Lektion können Sie hier herunterladen.
Zusammenfassung
Über den Befehl xlsread können Sie Daten aus MS Excel in Matlab einlesen.
Sie können Daten über die Funktion sort sortieren. Die Funktion gibt als zweiten Rückgabewert die Indizes der sortierten Werte zurück. Diese können Sie verwenden um weitere Daten in die selbe Reihenfolge zu bringen.
Zum filtern von Daten nutzen Sie in MATLAB logische Arrays. Diese können über Bedingungen wie größer (>), kleiner (<) und gleich (==) oder beispielsweise durch Funktionen zum Vergleich von Text (strcmp) erzeugt werden. Das logische Array kann dann zur Indizierung der Daten verwendet werden. Dadurch werden die Daten herausgenommen, an denen das logische Array den Wert 1 (true) enthält.
Über die MATLAB-Funktion ismember(A,B) können Sie überprüfen, welche Elemente eines (Cell) Arrays A auch in einem (Cell) Array B enthalten sind.
Zum filtern mir mehreren Bedingungen können Sie die entsprechenden logischen Array für die einzelnen Bedingungen über die logischen Operatoren und (&), oder (|) miteinander verknüpfen sowie über den Operator nicht (~) invertieren.
Quiz
Hier gelangen Sie zu einem kurzen Quiz mit Fragen zu Lektion 5.