tupaj.com

Funkcja o której teraz wspomnę jest bardzo podobna do poprzednio opisanej LICZ.JEŻELI(). Składnia jest identyczna natomiast idea działania polega na sumowaniu zawartości komórek będących w zadanym zakresie i zgodnych z kryterium.

Jeśli używamy tylko tych dwóch argumentów (zakres i kryteria) to należy przyjąć że mają to być komórki zawierające wartości liczbowe.

Na początku tego postu pominąłem argument trzeci, czyli suma_zakres jako że jest on opcjonalny, nie mniej nadaje się się on znakomicie do sumowania w sytuacji gdy badany zakres jak i kryterium nie jest wartością liczbową a na przykład nazwą jakiegoś produktu. W tej chwili wydaje się to dziwne, bo nie napisałem co tak naprawdę jest sumowane.

Poniżej pokazałem działanie funkcji, jak widać sumujemy ilość tysięcy ton zebranych owoców w tym przypadku jabłek. Można eksperymentować zmieniając położenie kolumny z “tysiącami ton” ale ważne jest aby sumowane wartości znajdowały się w tych samych wierszach co ich opis (kryterium).

Funkcja może może mieć wiele zastosowań w handlu, statystyce i przemyśle

W excelu istnieje pewien mechanizm który bardzo ułatwia operowanie na dużych zbiorach danych, mało tego - jest też przydatny w przypadku pojedynczych komórek. Mowa tu o nazywaniu zakresów. Polega ono na przydzieleniu nazwy do jednej lub więcej komórek i używanie tej nazwy w formułach każdym miejscu skoroszytu.
To może przykład:
Zaznaczmy komórkę A1 i przypiszmy do niej nazwę PODATEK7 (zaraz opiszę jak)
Wpiszmy wartość dla tej komórki wynoszącą 0,07

Teraz zaznaczamy komórkę B1 i nazwiemy ją PRODUKT
Wpiszmy wartość dla tej komórki wynoszącą 100

a teraz napiszmy regułę dla komórki C10

=PRODUKT + PODATEK7*PRODUKT

Opis jak to zrobić w Excel 2010

Jest też drugi wiele szybszy sposób nadawania nazw:

1. Zaznacz jedną komórkę lub obszar kilku komórek
2. Następnie kliknij w pole na pasku formuł w lewym rogu

3. Wpisz w to pole swoją specyficzną nazwę

4. Naciśnij Enter

Jest to oczywiście dużo szybszy sposób, ale jego możliwości są ograniczone.

Wykorzystanie wcześniej zdobytej wiedzy o formułach CSE (tablicowych - poszukaj w serwisie kilka postów wcześniej)

Teraz trochę o nazwach dla zakresów komórek i wykorzystaniu ich zalet

Załóżmy że chcemy zrobić małą symulację cen produktów dla różnych marż lub opodatkowania (w tym przypadku to drugie)

Przygotowujemy dane:

Dość dawno temu pisałem o zagnieżdżonej funkcji JEŻELI w Excelu, a ponieważ doszedłem do wniosku że dałoby się to łatwiej wyjaśnić postanowiłem napisać o tym ponownie.

Funkcja IF lub po polsku JEŻELI ma następującą konstrukcję

JEŻELI (test logiczny; wartość jeśli prawda; wartość jeśli fałsz) - tego możemy się dowiedzieć z pomocy w Excelu.

Jeśli rozumiemy istotę działania tej funkcji to jej zagnieżdżenie nie sprawi nam już żadnego problemu
Z tej prostej definicji wynikają następujące wnioski:
- Argument pierwszy - test logiczny - ma za zadanie porównać coś z czymś

testem logicznym może być na przykład:
A1 > C1
D1 < 20
E2 > SUMA(A1:D1)
itp

- Argument drugi - wartość jeśli prawda - w tym miejscu wpisujemy konkretną wartość jak na przykład
1, 100, “Franek” itp
lub …
właśnie …
Może to być funkcja jak SUMA(A10:A100), PRAWY(tekst,2), JEŻELI(tl;wjp;wjf)

Argument trzeci właściwie może być tym samym pod względem definicji co argument drugi z tą różnicą że obowiązuje dla niespełnionego testu logicznego czyli np A1>C1 jest nieprawdą bo w komórce C1 wpisaliśmy wartość większą niż jest a A1

Argumenty oddzielamy średnikami ( ; ) a tam gdzie trzeba dajemy nawiasy - zresztą ich pomijanie jest najczęstszą przyczyną błędów
Jak widzimy nic nie stoi na przeszkodzie aby zagnieżdżoną funkcję jeżeli zapisać na przykład tak

JEŻELI( test_logiczny_główny;instrukcja_wewnętrzna_JEŻELI_dla_PRAWDY; instrukcja_wewnętrzna_JEŻELI_dla_FAŁSZU)

p> instrukcja_wewnętrzna_JEŻELI jest podobna do tej nadrzędnej

Dla jakiegoś przykładu

Rysunek i postać funkcji.

Schemat w pliku pdf

Postać funkcji
= IF(A1 > C1; IF(A1 < D1; "Wartosc jest w zadanym zakresie"; "Wartosc przekracza zadany zakres"); IF(A1=C1; "Wartosc minimalna"; "Wartosc ponizej zadanego zakresu"))

Uwaga:
Powyżej skopiowałem regułę, której użyłem w anglojęzycznej wersji Excela aby poprawnie działała należy zamienić słowo IF na JEŻELI w każdym miejscu formuły.

przykłady dla różnych wartości A1

Chciałem omówić małą ale przydatną rzecz, która mi ostatnio bardzo pomogła w pracy a mianowicie określenie za pomocą komend VBA ilości użytych wierszy i kolumn w pliku. Jest to o tyle ułatwieniem że na tej podstawie mogę zadać granicę dla pętli for (lub innej) w celu dalszej analizy danych.

Aby sprawdzić jak to działa warto otworzyć jakiś plik z danymi lub samemu je przygotować

Warto zwrócić uwagę przy otwieraniu pliku Excela 2010 z makrami (jeżeli mamy już plik z napisanymi makrami) że trzeba niestety kliknąć przycisk “Enable content” (zezwalaj na treść) bez tego nasze makra nie będą działać.

Druga sprawa, od której należało może zacząć to ta że plik excela 2010 “utrudni” nam bezmyślne zapisanie go jako zwykłego pliku excela i zasugeruje by zapisać go w postaci pliku z zezwoleniem dla makr

i ten właśnie plik będzie wymagał wciśnięcia przycisku enable content aby je uruchamiać. oznaczony jest on specjalną ikoną

Teraz do rzeczy
1 mamy już dane
2 Po otwarciu pliku Excela naciskamy Lewy Alt +F11 i otwiera nam się okno VBA (Visual Basic for Applications) i zaznaczamy klikając dwukrotnie arkusz z naszymi danymi (w moim przypadku Arkusz1)

3. Wpisujemy kod jak niżej

Sub granice()

Funkcje Msgbox służą mi jedynie do upewnienia się że wszystko jest ok i po ich użyciu mogę na przedzie każdej takiej lini dodać pojedynczy apostrof aby interpretator VB widział te linie jako komentarz i nic z nimi nie robił.

4. Uruchamiamy makro używając F5 lub menu

Możemy widzieć jeszcze coś takiego

5. Klikamy przycisk Run i oglądamy wyniki

To na razie tyle. Makro można zmodyfikować do swoich potrzeb. Dodam jeszcze że testowałem je w wersjach Excel 2000 i Excel 2003 a skoro w niezmienionej formie treść makra działa również w tej wersji (2010) to śmiało można powiedzieć że nie powinno być przeszkód aby działało również w wersji 2007