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

Excel posiada bardzo ciekawą funkcję zliczającą komórki, które spełniają określone kryteria.  Funkcja działa na wskazanym zakresie komórek i zwraca liczbę wystąpień danego ciągu lub zadanej wartości. Funkcja może mieć zastosowanie w obróbce tekstu lub danych do raportu. Zliczanie wystąpień danego ciągu może być pomocne na przykład przy podsumowaniu tematyki posiadanych książek na podstawie słów kluczowych, które je opisują

Postać funkcji to

LICZ. JEŻELI (zakres; kryterium)

Przykładowo

LICZ. JEŻELI(A1:A10; “jabłko”) kryterium może mieć też postać  np “>10″

Jeśli chodzi o kryteria to można stosować wyrażenia regularne, ponieważ nie jestem specjalistą w dziedzinie wyrażeń regularnych to ograniczę się do dwóch przykładów:

Jeśli chcemy zliczyć te komórki w których słowa kończą się na “o” i mają dowolną liczbę znaków to napisalibyśmy kryterium “*o” a jeżeli komórki z wyrazami mającymi dokładnie 5 dowolnych znaków to napisalibyśmy “?????

Kryteria można łączyć ale na tej zasadzie

=LICZ. JEŻELI (zakres; kryterium1) + LICZ. JEŻELI (zakres; kryterium2) daje to sumę wyliczeń dla dwóch niezależnych kryteriów. Można też zastosować znak minus i wtedy otrzymamy liczbę reprezentującą określony podzbiór wyników.

Sporo czasu temu omawiałem dla wersji Excel 2003 funkcję Z.WIELKIEJ.LITERY() która w wyniku daje WIELKĄ pierwszą literę wyrazu a pozostałe zamienia na małe. Jej odpowiednikiem w anglojęzycznej wersji Excela jest funkcja PROPER(). Obie wersje językowe robią dokładnie to samo.
Poza tym zamieniają one w ten sam sposób litery zaczynające każdy wyraz w zdaniu które wskazujemy jako argument funkcji.
Ma to zastosowanie przy imporcie danych do excela, kiedy zdarzy się że każda litera imienia i nazwiska lub nazwy miasta w zaimportowanym tekście jest duża, a nam potrzeba naturalnego formatu. Pewnie znajdzie się jeszcze setki zastosowań tej funkcji, między innymi przeróbka fragmentów tekstów marketingowych w ofercie, tak aby pierwsza litera każdego wyrazu była duża.
Poniżej pokazałem jak wygląda przykład zastosowania tej funkcji.

Prawda że fajne ?

Poza tym jak szybko nas wyręcza ze żmudnej pracy.

Co to są dodatki w Excelu

Dodatek: Program uzupełniający, który dodaje niestandardowe polecenia lub funkcje do pakietu Microsoft Office (czytaj źródło)

Zalety tworzenia dodatków

Excel zawiera sporo przydatnych funkcji zawartych w kilkunastu dodatkach. Może się jednak zdarzyć że funkcja której szukamy nie została jeszcze napisana a my chcemy ją stworzyć i wiemy jak się do tego zabrać. Jeżeli wiemy że będziemy z niej korzystać także w innych plikach excela to warto ją umieścić w pliku dodatku. Co zyskujemy
- dostępność w innych plikach
- po rozpropagowaniu dodatku wśród znajomych (chcących korzystać z danej funkcji) ułatwiamy pracę innym
- treść dodatku możemy łatwo zabezpieczać przed niechcianą edycją i podglądem

Jak utworzyć dodatek w Excelu ?

Utworzenie dodatku w Excelu sprowadza się do tego że zwykły plik Excela z napisanymi w module VBA funkcjami (zobacz jak je tworzyć) i zazwyczaj bez treści w komórkach arkuszy zapisujemy jako plik w formacie xla (excel aż do wersji 2003) lub xlam (Excel 2007-2010). Kiedy zapisujemy plik dodatku powinniśmy zapisać go najpierw jako zwykły plik a potem dopiero jako plik dodatku ułatwia nam to później jego dystrybucję.

Krok po kroku

Dla przykładu piszemy taką funkcję

Zabezpieczamy dostęp do edycji modułu

Efekt zabezpieczenia modułu

Testujemy działanie funkcji:

Po przetestowaniu funkcji czyścimy zawartość arkusza, W przypadku Excela 2010 plik zapisujemy jako plik z makrami

Teraz zapiszemy plik jako dodatek w domyślnym miejscu, tak abyśmy mogli sami z niego korzystać w innych plikach excela

Ciąg dalszy tego wątku za moment a teraz jeszcze zapiszmy ten dodatek na pulpicie abyśmy mogli nasz dodatek dystrybuować

Załóżmy że to zrobiliśmy (wybieramy tylko inną ścieżkę dostępu)

A teraz rejestrujemy nasz dodatek

W tym celu przechodzimy do karty Developer (możesz takiej nie mieć w danej chwili - zobacz jak znaleźć) i szukamy ikony Add-ins

Sprawdzamy jak działa nasz dodatek:

Errata:
W trakcie tworzenia grafiki do artykułu w kilku miejscach wkradł mi się błąd w anglojęzycznym słowie checkbox (pole wyboru) i pisałem chceckbox za pomyłkę przepraszam

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: