Zaawansowane funkcje w MS Excel 2010

Ciąg dalszy sztuczek i podpowiedzi dotyczących korzystania z programu Excel. Tym razem bardziej zaawansowane funkcje.

• Funkcja SUMA.JEŻELI przyjmuje następujące argumenty: Zakres - zakres komórek, które mają być przeszukane; Kryteria - co szukamy w komórkach z Zakresu; Suma_zakres - komórki, które będą zsumowane jeśli w Zakresie znajdzie się Kryterium (gdy ten argument pominiemy, to funkcja będzie sumować wartości z Zakres)
• Funkcja LICZ.JEŻELI przyjmuje 2 argumenty: Zakres oraz Kryteria i tylko zlicza ile razy w Zakresie wystąpiło Kryterium. Kryterium może być liczbą, tekstem, odwołaniem do komórki lub wyrażeniem typu: ">35", "*ski".
• Jeśli chcemy wypełniać komórki powtarzającą się serią danych, to dobrze jest zdefiniować Listę niestandardową. Robimy to wybierając po kolei: Plik -> Opcje -> Zaawansowane -> Edytuj listy niestandardowe. W oknie opcji list niestandardowych możemy dodać pozycje listy w polu Lista wpisów a następnie klikając Dodaj, albo importować sekwencję z arkusza i zatwierdzając wybór przyciskiem Importuj
  
  Po wpisaniu na arkuszu pierwszego elementu utworzonej listy, mamy możliwość rozciągnięcia jej na większy zakres przeciągając prawy dolny róg komórki.
  
• Nadanie komórkom lub zakresom komórek unikatowych nazw, pozwala stosować później te nazwy w formułach i odwołaniach. Po zaznaczeniu komórek, nadajemy im nazwę w Polu nazwy.
  
  Aby zarządzać nazwami komórek lub zakresów komórek otwieramy okno dialogowe managera nazw: Formuły -> Nazwy zdefiniowane - Menedżer nazw, lub skrótem Ctrl + F3.
• Funkcja SUMY.CZĘŚCIOWE działa na zasadzie: przy każdej zmieniającej się wartości w jednej kolumnie, zastosuj funkcję na innej kolumnie. Wynika z tego, że aby stosować SUMY.CZĘŚCIOWE należy najpierw posortować dane. Funkcje, jakie można zastosować do obróbki danych, podawane są w postaci numeru.
  
  Niestety w sumie częściowej możemy użyć tylko jedną funkcję pomocniczą, więc jeśli jest konieczność policzenia np. sumy dla jednej kolumny i średniej dla innej, to należy jeszcze raz użyć sumy częściowej dla danego zakresu danych poprzez menu Dane -> Sumy częściowe i w oknie dialogowym odznaczyć opcję Zamień bieżące sumy częściowe.
  
  Stosowanie sum częściowych powoduje utworzenie tzw. konspektów, które usunąć można poprzez menu Dane -> Konspekt -> Rozgrupuj.
• Tabele i wykresy przestawne są prostym sposobem przedstawienie danych z tabeli głównej. Pozwalają na wprowadzanie szybkich zmian i elastyczne tworzenie powiązań między danymi. Wykres utworzony na podstawie tabeli przestawnej aktualizuje się automatycznie po wprowadzeniu zmian w układzie tabeli.
  
• Funkcje WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO dają możliwość przeszukiwania większej tabeli na podstawie wzorca zapisanego w mniejszej. Możemy np. utworzyć kolumnę z opisem słownym oceny (1 - niedostateczny, itd.) na podstawie oceny zapisanej w postaci cyfr od 1 do 6. Należy wtedy utworzyć funkcję w postaci: =WYSZUKAJ.PIONOWO(ocena_cyfrowa_w_głównej_tabeli;cała_tabela_pomocnicza_z_opisami_ocen;numer_kolumny_tabeli_pomocniczej_z_opisem_oceny). Tabelę z argumentu drugiego należy podać bez nagłówków i zablokować znakiem $. Gdy tabela pomocnicza utworzona jest w układzie poziomym, to stosujemy funkcję WYSZUKAJ.POZIOMO podając jako trzeci argument numer wiersza. Opcjonalny, czwarty argument (typu PRAWDA/FAŁSZ) w powyższych funkcjach, określa czy chcemy wyszukać wartość dokładną (PRAWDA) czy przybliżoną, np. z pewnego zakresu. Ważne jest również, aby kolejność kolumn w tabeli pomocniczej była taka sama jak w tabeli głównej.
• Podobne do powyższych są tzw. funkcje bazodanowe. W funkcjach BD.SUMA, BD.MIN, BD.MAX i BD.ŚREDNIA jako pierwszy argument należy podać zakres danych które przeszukujemy (tabela z nagłówkiem), potem którą kolumnę przeszukujemy (numer), a następnie co szukamy (również jako tabela z nagłówkiem takim samym jak w pierwszej tabeli).
• Funkcja USUŃ.ZBĘDNE.ODSTĘPY, jak sama nazwa mówi, usuwa spacje z początku i końca tekstu w komórce oraz wielokrotne spacje pomiędzy znakami w tekście (nie są usuwane pojedynce spacje pomiędzy wyrazami).
• Gdy chcemy zabezpieczyć skoroszyt (czyli plik) hasłem, to należy wybrać: Plik -> Zapisz jako -> Narzędzia -> Opcje ogólne (hmm... bardzo intuicyjne...).
  
  Możemy tam zdefiniować hasło na dwóch poziomach zabezpieczeń: ochrona przed otwarciem i ochrona przed wprowadzaniem zmian.