Nachhilfe

 

 

 

Startseite

Dienstleistungen

Software

Datenschutz

Impressum

 

 

 

 

 

 

 

Excel Spezielle Formelsammlung 09f

 

Umgebungsbezogene Formeln für die Programmierung

 

In den Beispielen findet die Zelle C7 Verwendung.

Zusätzlich wird Zelle C8 mit einbezogen, wenn es um zwei Parameter geht.

Fließen drei Werte in die Berechnungen mit ein, wird Zelle C9 dabei berücksichtigt.

 

 

Bereiche

 

Daten aus einem Bereich übernehmen (einfach)

Daten aus einem Bereich übernehmen (Index)

Einträge zählen

Spezielle Formeln (Umgebungsbezug)

 

 

Daten aus einem Bereich übernehmen (einfach)

 

Die Basisdaten befinden sich im Bereich $B$3:$D$12.

Sie sollen im Bereich $F$3:$H$12 übernommen werden.

 

Beispiel

 

 

A

B

C

D

E

F

G

H

I

1

 

 

 

 

 

 

 

 

 

2

 

Datum

Uhrzeit

Name

 

Datum

Uhrzeit

Name

 

3

 

01.01.2020

09:00

Larah

 

01.01.2020

9:00

Larah

 

4

 

02.01.2020

09:30

Paul

 

02.01.2020

9:30

Paul

 

5

 

03.01.2020

10:00

Simon

 

03.01.2020

10:00

Simon

 

6

 

04.01.2020

10:30

Carl

 

04.01.2020

10:30

Carl

 

7

 

05.01.2020

11:00

Andrea

 

05.01.2020

11:00

Andrea

 

8

 

06.01.2020

11:30

Sabine

 

06.01.2020

11:30

Sabine

 

9

 

07.01.2020

12:00

Timo

 

07.01.2020

12:00

Timo

 

10

 

08.01.2020

12:30

Larah

 

08.01.2020

12:30

Larah

 

11

 

09.01.2020

13:00

Paul

 

09.01.2020

13:00

Paul

 

12

 

10.01.2020

13:30

Simon

 

10.01.2020

13:30

Simon

 

13

 

 

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

 

 

Die Formel in Zelle $F$3 (gelb) lautet:

 

=WENN(B3="";"";B3)

 

Die Formel kann für den gesamten Zielbereich übernommen werden.

Daher ist die relative Adressierung an dieser Stelle sinnvoll.

 

Bei Verkürzung der Formel zu =B3 werden leere Zellen als Null (0) gewertet.

 

Bereiche

 

Daten aus einem Bereich übernehmen (Index)

 

Die Basisdaten befinden sich im Bereich $B$3:$D$12.

Sie sollen im Bereich $F$3:$H$12 übernommen werden.

 

Beispiel

 

 

A

B

C

D

E

F

G

H

I

1

 

 

 

 

 

 

 

 

 

2

 

Datum

Uhrzeit

Name

 

Datum

Uhrzeit

Name

 

3

 

01.01.2020

09:00

Larah

 

01.01.2020

9:00

Larah

 

4

 

02.01.2020

09:30

Paul

 

02.01.2020

9:30

Paul

 

5

 

03.01.2020

10:00

Simon

 

03.01.2020

10:00

Simon

 

6

 

04.01.2020

10:30

Carl

 

04.01.2020

10:30

Carl

 

7

 

05.01.2020

11:00

Andrea

 

05.01.2020

11:00

Andrea

 

8

 

06.01.2020

11:30

Sabine

 

06.01.2020

11:30

Sabine

 

9

 

07.01.2020

12:00

Timo

 

07.01.2020

12:00

Timo

 

10

 

08.01.2020

12:30

Larah

 

08.01.2020

12:30

Larah

 

11

 

09.01.2020

13:00

Paul

 

09.01.2020

13:00

Paul

 

12

 

10.01.2020

13:30

Simon

 

10.01.2020

13:30

Simon

 

13

 

 

 

 

 

 

 

 

 

14

 

 

 

 

 

 

 

 

 

15

 

 

 

 

 

 

 

 

 

 

Die Formel in Zelle $F$3 (gelb) lautet:

 

=WENN(INDEX($B$3:$D$12;ZEILE()-2;SPALTE()-5)="";"";INDEX($B$3:$D$12;ZEILE()-2;SPALTE()-5))

 

Die Formel kann für den gesamten Zielbereich übernommen werden.

 

Der Bereich der Basisdaten ($B$3:$D$12) ist absolut adressiert.

Um für die erste Zeile als Auswahlkriterium in Zelle $F$3 zu sorgen, wird ZEILE()-2 verwendet.

Also minus die Vorgängerzeile von der linken, oberen Zelle im Zielbereich.

Um für die erste Spalte als Auswahlkriterium in Zelle $F$3 zu sorgen, wird SPALTE()-5 verwendet.

Also minus die Vorgängerspalte von der linken, oberen Zelle im Zielbereich.

 

Bei Verkürzung der Formel zu =INDEX($B$3:$D$12;ZEILE()-2;SPALTE()-5)

werden leere Zellen als Null (0) gewertet.

 

Der Vorteil der Programmierung über die INDEX-Funktion liegt in der Anpassbarkeit der Formel.

So können verschiedene Bereiche von Basisdaten berücksichtigt werden.

Eine Integration der INDIREKT-Funktion bietet sich an, wenn die Ausgangsdaten über einen Vektor adressiert werden sollen.

 

Bereiche

 

Einträge zählen

 

Die Basisdaten befinden sich im Bereich $B$3:$B$12.

Eine Auswertung soll in Zelle $D$3 angezeigt werden.

 

Wie viele Einträge sind größer als 10 und kleiner als 40?

 

Formel in Zelle $D$3 (gelb):

 

=ZÄHLENWENNS(B3:B12;">10";B3:B12;"<40")

 

Beispiel

 

 

A

B

C

D

E

1

 

 

 

 

 

2

 

Zahlen

 

Zähler

 

3

 

1

 

4

 

4

 

3

 

 

 

5

 

5

 

 

 

6

 

11

 

 

 

7

 

18

 

 

 

8

 

27

 

 

 

9

 

36

 

 

 

10

 

45

 

 

 

11

 

56

 

 

 

12

 

89

 

 

 

13

 

 

 

 

 

14

 

 

 

 

 

15

 

 

 

 

 

 

 

Wie viele Einträge sind größer als 40?

 

Formel in Zelle $D$3 (gelb):

 

=ZÄHLENWENNS(B3:B12;">40")

 

Beispiel

 

 

A

B

C

D

E

1

 

 

 

 

 

2

 

Zahlen

 

Zähler

 

3

 

1

 

3

 

4

 

3

 

 

 

5

 

5

 

 

 

6

 

11

 

 

 

7

 

18

 

 

 

8

 

27

 

 

 

9

 

36

 

 

 

10

 

45

 

 

 

11

 

56

 

 

 

12

 

89

 

 

 

13

 

 

 

 

 

14

 

 

 

 

 

15

 

 

 

 

 

 

 

Wie viele Einträge sind kleiner als 10?

 

Formel in Zelle $D$3 (gelb):

 

=ZÄHLENWENNS(B3:B12;"<10")

 

Beispiel

 

 

A

B

C

D

E

1

 

 

 

 

 

2

 

Zahlen

 

Zähler

 

3

 

1

 

3

 

4

 

3

 

 

 

5

 

5

 

 

 

6

 

11

 

 

 

7

 

18

 

 

 

8

 

27

 

 

 

9

 

36

 

 

 

10

 

45

 

 

 

11

 

56

 

 

 

12

 

89

 

 

 

13

 

 

 

 

 

14

 

 

 

 

 

15

 

 

 

 

 

 

Bereiche