Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

Der wesentliche Unterschied zwischen Matrix- und normalen Formeln besteht darin, daß man mit Matrixformeln nicht nur einzelne Zellen berechnet, sondern ganze Zellbereiche mit einer einzigen Formel.

Der sichtbare Unterschied zu normalen Formeln besteht darin, daß eine Matrixformel in geschweifte Klammern eingefaßt ist: {SUMMENPRODUKT((C4:D7)*(E10:F13))}. Damit Calc die Formel als Matrix-Formel erkennt, muß man sie anders abschließen als nicht-Matrix-Formeln:
Entweder man schließt die Formel mit Shift+Ctrl+Return ab statt einfach nur mit Return, oder man benutzt den FunktionsAutopiloten und aktiviert dort das Kästchen Matrix. Keinenfalls erhält man eine Matrixformeln, wenn man einfach geschweifte Klammern von Hand in der Eingabezeile setzt.

Der Ergebnisbereich einer Matrixformel kann auch wieder eine Matrix sein. Ist dies der Fall, so kann auch immer nur der gesamte Ergebnisbereich wieder bearbeitet werden. Es ist nicht möglich, einzelne Zellen darin zu ändern. Um den gesamten Ergebnisbereich zu markieren, setzen Sie den Cursor in irgendeine seiner Zellen und drücken dann Ctrl+ ÷  wobei ÷ das Divisionszeichen im Nummernblock ist.

Matrixformeln können nicht durch Ziehen mit der Maus kopiert werden, bzw. das können sie schon, aber damit wird der Ergebnisbereich auf eine Matrix ausgeweitet. Will man eine Matrixformel einfach nur kopieren, so tut man das am besten mit Ctrl+C/Ctrl+V.

1. Rechnen mit Zellbereichen

Editieren

(./) OOo 2.0 (./) OOo 1.1

Eine Matrix in Calc ist zunächst einfach ein zweidimensionaler Zellbereich, wie z.B.: A1:D7. Genauso wie man zwei oder mehrere Zellen z.B. addieren oder multiplizieren kann, kann man das auch mit ganzen Zellbereichen oder Matrizen.
Zur Erinnerung: Matrix-Formeln immer mit Shift+Ctrl+Return abschließen.


1.1. Gleichgrosse Bereiche

A

B

C

D

E

F

G

H

 

Hier werden die drei gelben Zellbereiche addiert, das Ergebnis ist der rote Bereich.
Die Matrixformel dazu lautet: {=A1:B2+D2:E3+G1:H2}. Diese Formel muss nur in Zelle B5 eingetragen werden, sie wird automatisch auf die weiteren Zellen der Ergebnis-Matrix übertragen. Wenn die Formel nachträglich bearbeitet werden soll, muss jedoch der ganze Ergebnisbereich ausgewählt werden.

Es werden die sich entsprechenden Zellen (=gleiche Position innerhalb der Matrix) addiert.

1

1

2

9

10

2

3

4

5

6

11

12

3

7

8

4

5

15

18

6

21

24

1.2. Unterschiedlich grosse Bereiche

A

B

C

D

E

F

G

 

Hier ist die Ergebnis-Matrix so gross wie die Schnittmenge der beiden Ausgangs-Matrizen.

Zellen, die nicht in beiden Ausgangs-Matrizen vorkommen, werden ignoriert.

Die Matrixformel dazu lautet: {=A1:C3/F1:G4}.

1

220

340

440

4

5

2

480

260

180

2

10

3

20

16

4

18

14

5

6

55

68

7

240

26

1.3. Alle Zellen einer Matrix mit sich selbst multiplizieren

A

B

C

D

E

F

G

H

I

J

K

Die Formel für die Ergebnis-Matrix lautet:

{=A1:E5*A1:E5}

1

1

2

3

4

5

1

4

9

16

25

2

6

7

8

9

10

36

49

64

81

100

3

11

12

13

14

15

121

144

169

196

225

4

16

17

18

19

20

256

289

324

361

400

5

21

22

23

24

25

441

484

529

576

625

1.4. Eine Matrix mit sich selbst addieren, wobei Zeilen und Spalten vertauscht werden

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

Der grüne Bereich ist die Ausgangstabelle gespiegelt an ihrer Diagonalen und der rote Bereich ist die Summe aus den beiden Tabellen

1

1

2

3

4

5

1

6

11

16

21

2

8

14

20

26

2

6

7

8

9

10

2

7

12

17

22

8

18

20

26

32

3

11

12

13

14

15

3

8

13

18

23

14

20

26

32

38

4

16

17

18

19

20

4

9

14

19

24

20

26

32

38

44

5

21

22

23

24

25

5

10

15

20

25

26

32

38

44

50

6

Formel für die grüne Matrix

Formel für die rote Matrix

7

{=MTRANS(A1:E5)}

{=A1:E5+G1:K5}

8

ohne Zwischenschritt:

{A1:E5+MTRANS(A1:E5)}

2. Mathe- und Statistik-Funktionen auf Matrizen anwenden

Editieren

(./) OOo 2.0

{i} Die nachstehenden Beispiele wurden mit OpenOffice.org, Version 2.0 erstellt. Da sich in dieser Version im Bereich Matrixformeln einiges geändert hat, ist es gut möglich, daß nicht alle Beispiele in früheren Versionen nachvollzogen werden können.
Zur Erinnerung: Matrix-Formeln immer mit Shift+Ctrl+Return abschließen.


2.1. Diejenigen Zellen eines Bereichs addieren, die bestimmte Bedingungen erfüllen

Bei den Rechenbeispielen für Zellbereiche ist die Funktionsweise von Matrix-Formeln klar ersichtlich: es werden mehrere Zellbereiche miteinander verrechnet und das Ergebnis ist wieder ein ganzer Zellbereich.

Bei den folgenden Anwendungen ist das nicht von vornherein sichtbar, dennoch ist die Funktionsweise die gleiche.

Es sollen nur Werte summiert werden, die

größer als 2 sind
UND
kleiner als 6 sind
UND
bei denen in Spalte B II steht

A

B

1

 1 

 I

2

 2 

 II

3

 3 

 II

4

 4 

 I

5

 5 

 II

6

 6 

 II

7

8

{=SUMME((A1:A6>2)*(A1:A6<6)*(B1:B6="II")*(A1:A6))}

Was intern geschieht:
Pro Bedingung wird eine Spalte mit einer Wahrheitsprüfung angelegt, die als Ergebnis 1 (Richtig) oder 0 (Falsch) ergibt. All diese Wahrheitswerte werden horizontal multipliziert, d.h., es kann in einer Zeile nur dann "1" herauskommen, wenn alle Wahrheitswerte dieser Zeile "1" ergeben haben. Dieses Wahrheitsergebnis wird nun noch multipliziert mit der zu summierenden Spalte. Dabei kommt überall 0 heraus, wo das Wahrheitsergebnis nicht "1" ist. Überall da, wo das Wahrheitsergebnis "1" ist, bleibt der Wert aus der Ausgangsspalte übrig. Diese Werte werden nun addiert.

A

B

C

D

E

F

1

 0 

WENN(A2:A7>2;1;0)

WENN(A2:A7<6;1;0)

WENN(B2:B7="II";1;0)

(C2:C7)*(D2:D7)*(F2:F7)*(A2:A7)

2

 1 

 I 

 0 

 1 

 0 

 0 

3

 2 

 II 

 0 

 1 

 1 

 0 

4

 3 

 II 

 1 

 1 

 1 

 3 

5

 4 

 I 

 1 

 1 

 0 

 0 

6

 5 

 II 

 1 

 1 

 1 

 5 

7

 6 

 II 

 1 

 0 

 1 

 0 

8

SUMME(F2:F7)

 8 

2.2. Weitere Anwendungsbeispiele zum Thema "Bereich berechnen wenn"

A

B

C

D

E

1

02.05.05

8,00 €

Äpfel

Die Berechnungen für

SUMME
MITTELWERT
MAXIMUM
MINIMUM
ANZAHL

sollen nur für eine Auswahl der Zellen
im Bereich B1:B15 durchgeführt werden.

Für die Auswahl werden
die Spalten A und C herangezogen

2

16.05.05

12,00 €

Pflaumen

3

24.05.05

9,00 €

Birnen

4

06.06.05

9,00 €

Äpfel

5

17.06.05

12,00 €

Äpfel

6

26.06.05

8,00 €

Pflaumen

7

03.07.05

14,00 €

Birnen

8

06.07.05

8,00 €

Birnen

9

14.07.05

7,00 €

Pflaumen

10

25.07.05

10,00 €

Birnen

11

26.07.05

10,00 €

Birnen

12

28.07.05

8,00 €

Äpfel

13

30.07.05

9,00 €

Birnen

Werte sollen errechnet werden für:

14

06.08.05

14,00 €

Birnen

Monat:

7

15

29.08.05

13,00 €

Äpfel

Artikel:

Birnen

16

17

Summe

51,00 €

{=SUMME(((MONAT(A1:A15)=E14)*(C1:C15=E15)*(B1:B15)))}

18

Mittelwert

10,20 €

{=MITTELWERT(WENN((MONAT(A1:A15)=E14)*(C1:C15=E15)=1;B1:B15))}

19

Maximum

14,00 €

{=MAX(WENN((MONAT(A1:A15)=E14)*(C1:C15=E15)=1;B1:B15))}

20

Minimum

8,00 €

{=MIN(WENN((MONAT(A1:A15)=E14)*(C1:C15=E15)=1;B1:B15))}

21

Anzahl

5,00 €

{=ANZAHL(WENN((MONAT(A1:A15)=E14)*(C1:C15=E15)=1;B1:B15))}

3. Eindeutige Matrix-Funktionen

Editieren

(./) OOo 2.0

{i} Die nachstehenden Beispiele wurden mit OpenOffice.org, Version 2.0 erstellt. Da sich in dieser Version im Bereich Matrixformeln einiges geändert hat, ist es gut möglich, daß nicht alle Beispiele in früheren Versionen nachvollzogen werden können.


Auf den Seiten CalcFunktionenMatrix/RechnenWenn und CalcFunktionenMatrix/GrundRechenarten wurde beschrieben, wie gängige Funktionen dazu benutzt werden können, ganze Zellbereiche mit einer einzigen Formel zu bearbeiten, wobei als Ergebnis entweder wieder ein Zellbereich herauskommen kann oder auch ein Zusammenzug im Sinn von SUMME, MITTELWERT, etc.

Eigentlich werden aber in Calc ganz besondere Funktionen als Matrix-Funktionen bezeichnet, die überhaupt nur im Zusammenhang mit Matrizen oder auch Arrays Sinn machen.


3.1. SUMMENPRODUKT

Die Funktion SUMMENPRODUKT ist per Definition eine Matrixformel und muss nicht in {} eingefasst sein. Um die Funktionsweise zu verstehen, hier ein Vergleich von SUMME() und SUMMENPRODUKT()

A

B

C

D

 

Bei der Summenbildung für eine einzelne Spalte ist kein Unterschied zu erkennen zwischen SUMME() und SUMMENPRODUKT

Sobald jedoch mit mehr als einer Spalte gearbeitet wird, ist die unterschiedliche Funktionsweise ersichtlich

1

8,00

2

96,00

=SUMME(A1:A9)

2

12,00

3

96,00

=SUMMENPRODUKT(A1:A9)

3

9,00

5

4

12,00

1

127,00

=SUMME(A1:A9;B1:B9)

5

11,00

3

331,00

=SUMMENPRODUKT(A1:A9;B1:B9)

6

14,00

2

7

9,00

6

127,00

=A1+A2+A3+A4+A5+A6+A7+A8+A9+B1+B2+B3+B4+B5+B6+B7+B8+B9

8

8,00

2

331,00

=A1*B1+A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8+A9*B9

9

13,00

7

Wir halten fest: SUMMENPRODUKT multipliziert nebeneinander liegende Zellen und summiert das Produkt. Diese Art der Tabellenverarbeitung bedingt, dass die in einer SUMMENPRODUKT-Formel verwendeten Zellbereiche jeweils gleich groß sind. Zum Vergleich: Eine normale Summenformel kann mehrere Zellbereiche beinhalten, die unterschiedlich groß sind:
=SUMME(A1:A9;B1:B3)
Würde man in gleicher Weise Bereiche in einer Summenprodukt-Formel angeben, also:
=SUMMENPRODUKT(A1:A9;B1:B3)
so würde das zur Fehlermeldung #WERT! führen.

  • (!) Tipp: Wenn man mit Zellnamen oder Bereichsnamen arbeitet, kann es passieren, dass die Zellbereiche unterschiedlich groß sind. Wenn Sie also den Fehlercode #WERT! bekommen und in Ihrer Formel solche Namen verwenden, sollten sie überprüfen, ob die Bereichsadressen korrekt definiert sind.
    (!) Klarstellung: Die Zellen müssen nicht unmittelbar nebeneinander liegen.

3.1.1. Umsätze auswerten mit SUMMENPRODUKT und mehreren einschränkenden Bedingungen

A

B

C

D

E

1

Datum

Name

PLZ

Betrag

Artikel

2

02.05.05

Kerstin Meier

52550

9,00 €

Äpfel

3

09.05.05

Barbara Müller

60573

8,00 €

Birnen

4

21.05.05

Berta Hermann

52550

14,00 €

Äpfel

5

22.05.05

Heinz Schmidt

95047

8,00 €

Pflaumen

6

01.06.05

Berta Hermann

52550

10,00 €

Birnen

7

06.06.05

Barbara Müller

60573

12,00 €

Pflaumen

8

07.06.05

Viktor Keller

60573

14,00 €

Birnen

9

13.06.05

Kerstin Meier

52550

7,00 €

Pflaumen

10

14.06.05

Heinz Schmidt

95047

12,00 €

Äpfel

11

24.06.05

Kerstin Meier

52550

11,00 €

Pflaumen

12

24.06.05

Berta Hermann

52550

14,00 €

Pflaumen

13

04.07.05

Daniel Huber

95047

8,00 €

Äpfel

14

06.07.05

Daniel Huber

95047

12,00 €

Äpfel

15

16.07.05

Daniel Huber

95047

9,00 €

Birnen

16

18.07.05

Viktor Keller

60573

9,00 €

Äpfel

17

19.07.05

Viktor Keller

60573

13,00 €

Pflaumen

18

24.07.05

Heinz Schmidt

95047

8,00 €

Pflaumen

19

27.07.05

Barbara Müller

60573

9,00 €

Birnen

20

21

Auswahlkriterien

22

Monat:

5

23

PLZ-Bereich:

50000

59999

24

Artikel:

Äpfel

25

26

Umsatz:

23,00 €

27

Formel:

=SUMMENPRODUKT(D2:D19;MONAT(A2:A19)=B22;(C2:C19>=B23)*(C2:C19<=C23);E2:E19=B24)

28

Da die einzelnen Matrizen in den Parametern der Funktion per Definition mulitpliziert werden, ist auch diese Schreibweise möglich:

29

Formel:

=SUMMENPRODUKT((D2:D19)*(MONAT(A2:A19)=B22)*((C2:C19>=B23)*(C2:C19<=C23))*(E2:E19=B24))

3.1.2. Erweiterung des Beispiels um ODER-Bedingungen

Da SUMMENPRODUKT für jede Bedingung einen Wahrheitswert generiert und damit multipliziert, bedeutet jede nicht zutreffende Bedingung eine Multiplikation mit 0, was die Auswahl verringert.

Bei ODER-Verknüpfungen geht es jedoch darum, die Auswahl zu erweitern. Das bewerkstelligt man dadurch, dass man die Bedingungen nicht multipliziert, sondern addiert.

A

B

C

D

E

1

Datum

Name

PLZ

Betrag

Artikel

2

02.05.05

Kerstin Meier

52550

9,00 €

Äpfel

3

09.05.05

Barbara Müller

60573

8,00 €

Birnen

4

21.05.05

Berta Hermann

52550

14,00 €

Äpfel

5

22.05.05

Heinz Schmidt

95047

8,00 €

Pflaumen

6

01.06.05

Berta Hermann

52550

10,00 €

Birnen

7

06.06.05

Barbara Müller

60573

12,00 €

Pflaumen

8

07.06.05

Viktor Keller

60573

14,00 €

Birnen

9

13.06.05

Kerstin Meier

52550

7,00 €

Pflaumen

10

14.06.05

Heinz Schmidt

95047

12,00 €

Äpfel

11

24.06.05

Kerstin Meier

52550

11,00 €

Pflaumen

12

24.06.05

Berta Hermann

52550

14,00 €

Pflaumen

13

04.07.05

Daniel Huber

95047

8,00 €

Äpfel

14

06.07.05

Daniel Huber

95047

12,00 €

Äpfel

15

16.07.05

Daniel Huber

95047

9,00 €

Birnen

16

18.07.05

Viktor Keller

60573

9,00 €

Äpfel

17

19.07.05

Viktor Keller

60573

13,00 €

Pflaumen

18

24.07.05

Heinz Schmidt

95047

8,00 €

Pflaumen

19

27.07.05

Barbara Müller

60573

9,00 €

Birnen

20

21

Auswahlkriterien

22

Monat:

5

23

Artikel:

Äpfel

Birnen

Pflaumen

24

25

Umsatz:

39,00 €

27

Formel:

((D2:D19)*(MONAT(A2:A19)=B22)*(E2:E19=B23)) +((D2:D19)*(MONAT(A2:A19)=B22)*(E2:E19=C23)) +((D2:D19)*(MONAT(A2:A19)=B22)*(E2:E19=D23))

4. siehe auch

CalcFunktionen


KategorieCalc


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28