Suma. Iloczynów mnoży odpowiadające sobie składniki w podanych tablicach I zwraca sumę tych iloczynów. Składnia suma. IloczynóW



Pobieranie 97.1 Kb.
Data28.04.2016
Rozmiar97.1 Kb.
SUMA.ILOCZYNÓW

Mnoży odpowiadające sobie składniki w podanych tablicach i zwraca sumę tych iloczynów.



Składnia SUMA.ILOCZYNÓW(tablica1;tablica2;tablica3;...)

Tablica1; tablica2; tablica3;... to od 2 do 30 tablic, których elementy należy pomnożyć i następnie dodać.



Spostrzeżenia

  • Argumenty tablic muszą mieć takie same wymiary. Jeśli tak nie jest, funkcja SUMA.ILOCZYNÓW zwraca wartość błędu #ARG!.

  • Funkcja SUMA.ILOCZYNÓW traktuje pozycje tablicy, które nie są wartościami numerycznymi, tak jakby były zerami.

 

A

B

C

D

1

 

Waga (kg)

Cena (zł/kg)

Wartość (zł)

2

mak

0,3

4

 

3

rodzynki

0,2

6

 

4

migdały

0,1

9

 

  1. Jak policzyć wartość (w zł) bakalii wstawiając tylko jedną formułę?
    B6: =SUMA.ILOCZYNÓW(B2:B4;C2:C4).

WYSZUKAJ.PIONOWO

Wyszukuje wartość w skrajnej lewej kolumnie tablicy, a następnie zwraca wartość w tym samym wierszu z kolumny określonej w tablicy.



Składnia WYSZUKAJ.PIONOWO(odniesienie;tablica;nr_kolumny;kolumna)

Odniesienie   to wartość, którą należy znaleźć w pierwszej kolumnie tablicy. Odniesienie może być wartością, odwołaniem albo ciągiem tekstowym.



Tablica   to tablica zawierająca informacje, w których należy znaleźć poszukiwaną wartość. W funkcji należy używać odwołania do zakresu lub nazwy zakresu, takiej jak Baza_danych lub Lista.

  • Jeśli argument kolumna ma wartość logiczną PRAWDA, wartości w pierwszej kolumnie określonej przez argument tablica muszą być umieszczone w kolejności rosnącej: ..., -2, -1, 0, 1, 2, ..., A-Z, PRAWDA, FAŁSZ; w przeciwnym przypadku funkcja WYSZUKAJ.PIONOWO może nie podać poprawnej wartości. Jeśli argument kolumna ma wartość FAŁSZ, nie ma potrzeby sortowania tablicy.

  • Teksty pisane wielkimi i małymi literami są równoważne.

Nr_kolumny   to numer kolumny w tablicy, z której pochodzić powinna pasująca wartość. Nr_kolumny o wartości 1 zwraca wartość z pierwszej kolumny tablicy określonej przez argument tablica; nr_kolumny o wartości 2 zwraca wartość z drugiej kolumny tablicy określonej przez argument tablica i itd. Jeśli argument nr_kolumny jest mniejszy niż 1, funkcja WYSZUKAJ.PIONOWO zwraca wartość błędu #ARG!; jeśli argument nr_kolumny jest większy niż liczba kolumn znajdujących się w tablicy określonej przez argument tablica, funkcja WYSZUKAJ.PIONOWO zwraca wartość błędu #ADR!.

Kolumna   to wartość logiczna określająca, czy funkcja WYSZUKAJ.PIONOWO ma znaleźć dokładne czy też przybliżone dopasowanie. Jeśli argument ten ma wartość PRAWDA bądź został pominięty, zwracane jest przybliżone dopasowanie. Innymi słowy, jeśli nie zostanie znalezione dokładne dopasowanie, zwracana jest następna największa wartość, mniejsza od argumentu odniesienie. Jeśli argument ma wartość FAŁSZ, funkcja WYSZUKAJ.PIONOWO wyszuka dopasowanie dokładne. Jeśli nie zostanie ono znalezione, funkcja zwraca wartość błędu #N/D!.



Spostrzeżenia

  • Jeśli funkcja WYSZUKAJ.PIONOWO nie może znaleźć argumentu odniesienie, a argument kolumna ma wartość PRAWDA, funkcja użyje największej wartości, która jest mniejsza niż albo równa argumentowi odniesienie.

  • Jeśli argument odniesienie ma wartość mniejszą niż najmniejsza wartość znajdująca się w pierwszej kolumnie tablicy określonej przez argument tablica, funkcja WYSZUKAJ.PIONOWO zwraca wartość błędu #N/D!.

  • Jeśli funkcja WYSZUKAJ.PIONOWO nie może znaleźć wartości odniesienie, a argument kolumna ma wartość FAŁSZ, funkcja WYSZUKAJ.PIONOWO zwraca wartość błędu #N/D!.

Przykład:

  1. Wyobraźmy sobie takie zadanie: mamy listę artykułów z danymi w kolejnych kolumnach: symbol artykułu, nazwa artykułu, cena artykułu (zakres A2:D9).

  2. Drugie zestawienie (zakres F2:I9) to rejestr sprzedaży, w którym mamy zawarty symbol artykułu i ilość sprzedanych sztuk – w kolumnie H).

  3. Jak każdemu artykułowi z rejestru sprzedaży przypisać cenę z listy artykułów?

Składnia tej funkcji: =WYSZUKAJ.PIONOWO(co;gdzie;w_której_kolumnie;prawda/fałsz),

Ostatni argument to wartość logiczna:



  • fałsz – jeżeli w tabeli 'gdzie' szukamy dokładnie element 'co' (dokładnego odpowiednika),

  • prawda lub pominięty argument – zostanie wyszukany, w przypadku braku dokładnie takiego samego elementu, największy element mniejszy od 'co'

  • tabela 'gdzie' musi być posortowana rosnąco według pierwszej jej kolumny.

 

A

B

C

D

E

F

G

H

I

1

Symbol

Nazwa

Cena

Kategoria 




Data

Symbol

Cena

Kategoria 

2

A1

Rower

1299

I




2004-06-19

A1




 

3

A11

Narty

1500

I




2004-06-19

A2




 

4

A2

Rolki

299

II




2004-06-20

A1




 

5

A3

Koszulka

99

II




2004-06-21

A1




 

6

 

 

 

 




2004-06-21

A11




 

7

 

 

 

 




2004-06-22

A2




 

8

 

 

 

 




2004-06-23

A2




 

9

 

 

 

 




2004-06-23

A3




 

Jak obliczyć cenę? Formuła w H2 to:

=WYSZUKAJ.PIONOWO(G2;$A$2:$D$5;3;FAŁSZ)

Jak przypisać kategorię? Formuła w I2 to:


=WYSZUKAJ.PIONOWO(G2;$A$2:$D$5;4;FAŁSZ)

Funkcje złożone/ zagnieżdżone/tablicowe

Formuły w tym przykładzie muszą zostać wprowadzone jako formuły tablicowe. Należy zaznaczyć każdą komórkę zawierającą formułę, nacisnąć klawisz F2, a następnie nacisnąć klawisze CTRL+SHIFT+ENTER.



 

A

B

1

Sprzedawca

Faktura

2

Buchanan

15000

3

Buchanan

9000

4

Sue

8000

5

Sue

20000

6

Buchanan

5000

7

Dodsworth

30000

  1. =SUMA(JEŻELI((A2:A7="Buchanan")+(A2:A7="Sue");1;0))
    Liczba faktur dla sprzedawcy o nazwisku Buchanan lub Sue.
    Wynik funkcji wynosi 5.

  2. Rysunek pokazuje, że funkcja jeżeli pobiera dane z zakresu A2:A7 oraz B2:B7. Następnie sprawdza, czy sprzedawca to Buchanan lub Sue. W wyniku testu logicznego otrzymuje tablicę zer i jedynek oznaczającą, że w 5 przypadkach warunek został spełniony.



  3. Rysunek pokazuje, że funkcja jeżeli sprawdza, czy sprzedawca to Buchanan lub Sue zwraca tablicę zer i jedynek. Liczba 1 oznacza, że przynajmniej jeden z warunków został spełniony. W tym wypadku, kryterium zostało spełnione w 5 wierszach . Odpowiednie elementy tablicy zwróconej przez funkcję jeżeli są sumowane, w wyniku czego otrzymujemy liczbę 5 jako wynik funkcji
    =SUMA(JEŻELI((A2:A7="Buchanan")+(A2:A7="Sue");1;0))
    Znak plus (+) w zapisie JEŻELI((A2:A7="Buchanan")+(A2:A7="Sue");1;0) oznacza, że co najmniej 1 z warunków połączonych tym znakiem został spełniony.


  4. =SUMA(JEŻELI((B2:B7<9000)*(B2:B7>10);1;0))
    Liczba faktur o wartościach mniejszych niż 9000 lub większych niż 10.
    Wynik funkcji wynosi 1.

  5. =SUMA(JEŻELI((B2:B7<15000)*(B2:B7>8000);1;0)).
    Liczba faktur o wartościach od 8 000 do 15 000.

  6. =SUMA(JEŻELI((A2:A7="Buchanan")*(B2:B7<9000);1;0)) Liczba faktur dla sprzedawcy o nazwisku Buchanan i wartości mniejszej niż 9000.
    Wynik funkcji wynosi 1.

     

    A

    B

    C

    D

    1

    Miesiąc

    Region

    Sprzedaż

    Reklamacje

    2

    Sty

    północ

    100

    1

    3

    Sty

    Południe

    200

    2

    4

    Sty

    Wschód

    300

    1

    5

    Sty

    Zachód

    400

    3

    6

    Lut

    Północ

    500

    4

    7

    Mar

    Południe

    600

    2

    8

    Kwi

    Wschód

    700

    3

    9

    Maj

    Zachód

    800

    5

  7. Liczba wierszy, gdzie miesiąc równa się sty i region równa się wschód, zachód i południe =SUMA((A2:A9="sty")*(B2:B9<>"północ"))

  8. Suma wartości sprzedaży w wierszach, gdzie miesiąc = sty i region jest inny niż północ
    =SUMA((A2:A9="sty")*(B2:B9<>"północ")*(C2:C9))

  9. Suma reklamacji w wierszach, gdzie miesiąc = sty i region jest inny niż zachód
    =SUMA((A2:A9="sty")*(B2:B9<>"zachód")*(D2:D9))

  10. Suma wartości sprzedaży w wierszach, gdzie miesiąc równa się „sty” i region równa się „północ”.

=SUMA((A2:A9="sty")*(B2:B9="północ")*(C2:C9))

  1. Suma reklamacji w wierszach, gdzie miesiąc równa się „lut” i region równa się „południe”.

=SUMA((A2:A9="lut")*(B2:B9="południe")*(D2:D9))

  1. Suma wartości sprzedaży w wierszach, gdzie miesiąc równa się „sty” i region równa się „południe”.

=SUMA((A2:A9="sty")*(B2:B9="południe")*(C2:C9))

  1. Suma wartości sprzedaży w wierszach, gdzie sprzedaż jest mniejsza niż 400.

=SUMA((C2:C9<400)*(C2:C9))

  1. Suma wartości sprzedaży w wierszach, gdzie sprzedaż jest większa równa 400 lub mniejsza równa 600.

=SUMA((C2:C9>=400)*(C2:C9<=600)*(C2:C9))

  1. Suma reklamacji w regionie zachód:
    =SUMA((B2:B9="zachód")*(D2:D9))

  2. Suma sprzedaży w regionie zachód:
    =SUMA((B2:B9="ZACHÓD")*(C2:C9))

  3. Suma wierszy, gdzie miesiąc równa się „sty” i miesiąc równa się „lut”.
    Rysunek pokazuje, że funkcja jeżeli pobiera dane z zakresu A2:A9 oraz A2:A9. Następnie sprawdza, czy miesiąc to "sty" lub "lut". W wyniku testu logicznego otrzymuje tablicę zer i jedynek oznaczającą, że w 5 przypadkach warunek został spełniony.

=SUMA(JEŻELI((A2:A9="sty")+(A2:A9="lut");1;0))


Formuła tablicowa zwraca wartość 5.

  1. Suma wartości sprzedaży w wierszach, gdzie miesiąc równa się „sty” i miesiąc równa się „lut”.
    Rysunek pokazuje, że funkcja jeżeli pobiera dane z zakresu A2:A9 oraz A2:A9. Na stępnie sprawdza, czy miesiąc to "sty" lub "lut". W wyniku testu logicznego otrzymuje tablicę zer i jedynek oznaczającą, że w 5 przypadkach warunek został spełniony. Odpowiednie elementy tablicy zwróconej przez funkcję jeżeli są mnożone przez wartości sprzedaży.

=SUMA(JEŻELI((A2:A9="sty")+(A2:A9="lut");1;0)*(C2:C9))
Formuła tablicowa zwraca wartość 1500.

  1. Suma reklamacji w wierszach, w styczniu, lutym i marcu.
    =SUMA(JEŻELI((A2:A9="sty")+(A2:A9="lut")+(A2:A9="mar");1;0)*(D2:D9))
    Formuła tablicowa zwraca wartość 13.

  2. Liczba miesięcy, kiedy wartość sprzedaży była mniejsza równa 400 lub większa od 600
    =SUMA((C2:C9<=400)+(C2:C9>600))

  3. Wartość sprzedaży w miesiącach, kiedy wartość sprzedaży była mniejsza równa 400 lub większa od 600
    =SUMA(((C2:C9<=400)+(C2:C9>600))*(C2:C9))

  4. Liczba reklamacji w miesiącach, kiedy wartość sprzedaży była mniejsza równa 400 lub większa od 600
    =SUMA(((C2:C9<=400)+(C2:C9>600))*(D2:D9))

Linki do stron na temat funkcji tablicowych w Excelu

Excel - arkusz dla każdego, http://excel.republika.pl/podstawy/wyszukajpionowo/wyszukajpionowo.htm

Wyszukaj.pionowo, Microsoft Polska,
http://office.microsoft.com/pl-pl/assistance/hp052093351045.aspx

Funkcja WYSZUKAJ.PIONOWO, http://scorec.scholaris.pl/index_pliki/arkusz/wyszukaj.pionowo.htm

The Frequency function, http://www.meadinkent.co.uk/xlfreq.htm

Using Array Formulas in Excel, http://www.emailoffice.com/excel/arrays-bobumlas.html

Array Formulas, Pearson Software Consulting, LLC, http://www.cpearson.com/excel/array.htm

Using SUM(IF()) as an array function instead of COUNTIF() with AND or OR criteria, Microsoft Polska, http://support.microsoft.com/?id=275166

Identifying Unique Items in an Array or Range (VBA), Excel Developer Tip, http://j-walk.com/ss/excel/tips/tip15.htm

Returning Arrays (VBA), Pearson Software Consulting, LLC, http://www.cpearson.com/excel/returnin.htm


Inne strony na temat Excela

Rediscover Excel, http://www.excel-vba.com/index-excel.htm



Opracowanie na podstawie informacji zawartej w Helpie, J. Walkenbach Programowanie – Excel 2000, MacDonald Excel – Nieoficjalny podręcznik ©ASB & BI




©absta.pl 2016
wyślij wiadomość

    Strona główna