Archiwa tagu: Excel

Definiowanie nazw w Excelu

Definiowanie nazw w Excelu

Definiowanie nazw w Excelu, polega na nadaniu zakresowi komórek, unikalnej nazwy. Co powoduje, że w łatwy sposób można odwołać się do konkretnego zdefiniowanego zakresu np. podczas tworzenia funkcji. Szczególnie ma to zastosowanie w funkcjach takich jak:
INDEX jako pierwszy argument
MATCH jako drugi argument
VLOOKUP oraz HLOOKUP jako drugi argument

Stworzone formuły zawierające nazwy zamiast zakresów, są bardziej zrozumiałe i łatwiejsze w obsłudze, pod warunkiem, że nazwy jakie nadamy będą czytelne. Nazwy te wykorzystywane są również w programowaniu VBA.

W jaki sposób zdefiniować, czyli nadać konkretna nazwę zakresowi?
• Zaznaczamy interesujący nas zakres.
• W polu nazwy wpisujemy unikatową nazwę zaznaczanego zakresu.  Nie wolno używać, niedozwolonych znaków specjalnych np. spacji,$,@,#,! czy też operatorów arytmetycznych  ( +, -, /, *,^), porównania (<>=) i operatora tekstowego (&).
• Akceptujemy klawiszem Enter.

Ponadto zdefiniowana nazwa może nie tylko odnosić się do komórki bądź zakresu komórek, ale zawierać formułę czy też konkretną wartość bądź tekst.

W karcie Formulas znajdziemy sekcję Defined names:

• Gdzie możemy zdefiniować nazwy Define Name na poziomie Arkusza lub Skoroszytu.
• Stworzyć nazwy z selekcji [Ctr] +[Shift] +[F3].
• Zarządzać nazwami poprzez Name Manager [Ctrl] + [F3].
• Wstawić zdefiniowaną nazwę w formułę [F3].

Podobne zagadnienia i sztuczki w Excel-u można nauczyć się u mnie na kompleksowym Szkoleniu w Londynie.

English Version

Funkcja IFS kontra zagnieżdżanie funkcji IF

Funkcja IF (polska nazwa funkcji to JEŻELI)

Składnia funkcji IF:

= IF ( warunek logiczny , co jeśli spełniony jest warunek , co jeśli nie jest spełniony warunek )

Warunki logiczne przykłady:

• Równa się: B2=A1.
• Większe niż: B2>A1.
• Większy lub równe: B2>=A1.
• Mnie niż: B2<A1.
• Mniejsze lub równe: B2<=A1.
• Różne od: B2<>A1.

Jeśli nie zadeklarujemy trzeciego argumentu, gdyż nie jest obowiązkowy, domyślnie zwróci nam FALSE.  Natomiast w przypadku kiedy chcemy zadeklarować w drugim lub trzecim argumencie tekst jako rezultat, to należy go ująć w cudzysłów.

=IF(A1>5,"Pass","Fail")

Jeżeli w komórce A1 wartość będzie wyższa niż 5 formuła zwróci Pass w przeciwnym wypadku Fail.

Kiedy chcemy zastosować jeden warunek logiczny to tworzenie funkcji IF jest proste i oczywiste. Natomiast co jeśli chcemy wprowadzić kolejne warunki? Wtedy należy dodać kolejna funkcje IF w już istniejacą.

Zagnieżdżenie funkcji IF

Chyba każdy z nas miał przynajmniej w pewnym momencie kłopoty z opanowaniem funkcji IF, kiedy dochodziło do jej zagnieżdżania. Czyli wtedy, gdy przynajmniej raz funkcja IF stanowiła argument funkcji IF. Ponadto zagnieżdżona funkcja stanowi obciążenie dla pracy systemu podczas przeliczania, oczywiście w przypadku, kiedy tą funkcję zastosujemy bardzo często w arkuszu.
Podam teraz prosty przykład funkcji IF zagnieżdżonej jednokrotnie. W miejsce trzeciego argumentu wprowadzona jest nowa funkcja IF zawierająca kolejny warunek.

= IF ( warunek logiczny , co jeśli spełniony jest warunek , IF ( warunek logiczny 2 , co jeśli spełniony jest warunek 2 , co jeśli nie ))
=IF(A1>5,"Excellent",IF(A1>3,"Good","Poor"))

Jeżeli w komórce A1 wartość będzie wyższa niż 5 formuła zwróci Excellent, jeżeli  nie ale bedzie wyższa niż 3 zwróci Good, w przeciwnym wypadku Fail.

Funkcja IFS (polska nazwa funkcji to WARUNKI)

W 2016 w Excelu pojawiła się alternatywa dla funkcji IF, czyli funkcja IFS, która daje możliwość wprowadzenie więcej warunków logicznych niż jeden, która uproszcza tworzenie formuły oraz znacznie przyspiesza jej prace.

= IFS ( Pierwszy warunek logiczny , co jeśli spełniony jest warunek, kolejny warunek logiczny , co jeśli spełniony jest kolejny warunek , …)
=IFS(A1>5,"Excellent",A1>3,"Good")

Jeżeli w komórce A1 wartość będzie wyższa niż 5 formuła zwróci Excellent, jeżeli  nie ale bedzie wyższa niż 3 zwróci Good, w przeciwnym wypadku zwróci błąd #NA.

Może się zdarzyć, że więcej niż jednokrotnie warunek zostanie spełniony w argumentach funkcji IFS. W takim wypadku decydujący jest ten, który jest zapisany najwcześniej.

=IFS(A1>3,"Good",A1>5,"Excellent")

W takim wypadku gdy zawartość komórki A1 będzie wyższa niż 3 funkcja zwróci zawsze Good (nigdy nie zwróci Excellent). Dlatego ważna jest kolejność zapisywania warunków. Jeśli żaden z warunków nie będzie spełniony, formuła zwróci błąd #NA. 

IFS vs. IF (WARUNKI kontra JEŻELI)

  • Funkcja IFS pozwala w prosty sposób na wprowadzenia kolejnych warunków logicznych bez konieczności zagnieżdżania.
  • Ze względu na brak zagnieżdżania wydajność funkcji IFS jest znacznie efektywniejsza.
  • Pozwala zastosować dwukrotnie więcej, czyli 127 warunków, podczas gdy zagnieżdżona formuła 64 warunki.
  • Jeśli żaden z warunków nie zostanie spełnionych funkcja IFS zwróci błąd #NA. W takim przypadku warto dodatkowo zastosować, funkcje IFEROOR. W drugim argumencie tej funkcji podajemy co ma być, jeśli nie spełniony jest żaden z  warunków.
= IFERROR ( IFS ( warunek logiczny , co jeśli pełniony jest warunek , warunek logiczny 2 , co jeśli pełniony jest warunek 2 ) , co jeśli nie jest spełniony żaden warunek )
=IFERROR(IFS(A1>5,"Excellent",A1>3,"Good"),"Poor")

Jeżeli w komórce A1 wartość będzie wyższa niż 5 formuła zwróci Excellent, jeżeli  nie ale bedzie wyższa niż 3 zwróci Good, w przeciwnym wypadku zwróci Poor.

Podobne zagadnienia i sztuczki w Excel-u można nauczyć się u mnie na kompleksowym Szkoleniu w Londynie.

English Version

Tabliczka mnożenia w Excelu

Tabliczka mnożenia w Excelu.

Starając się o pracę, zdarza się podczas testu z Excel-a rozwiązanie tzw. tabliczki mnożenia. W tym artykule podam dwie metody jak szybko poradzić sobie z tym zadaniem.

Przy okazji podam różne sposoby operowania formułami: po pierwsze na zakresach z użyciem tzw. Formuły Tablicowej bądź też poprzez skrót klawiszowy wypełniając wyedytowaną formułę w zaznaczonym zakresie. Ponadto podam wiele skrótów klawiszowych, które znacznie ułatwiają i przyspieszają naszą pracę, przez co stajemy się bardziej efektywni a co za tym idzie bardziej konkurencyjni, choćby na rynku pracy.

Budowa tabliczki mnożenia

Załóżmy, ze tworzymy tabliczkę mnożenia w arkusz na samej górze w lewym rogu.

1. Wstawienie pionowe
Wstawiamy pionowo kolejno liczby od 1 do 10 w rzędach począwszy od komórki A2.

a) każdą liczbę pojedynczo

b) lub wstawiamy 1 do A2 oraz 2 do A3, następnie zaznaczamy obydwie komórki, po czym za pomocą przeciągnięcia myszą w dolnym prawym rogu komórki A3 aż do komórki A11 wypełniamy pozostałe komórki automatycznie kolejnymi liczbami

c) można tez użyć metody szybkiego wypełnienia wybierając kartę
• Home, w sekcji Editing wybierami ikonke Fill oraz Series…

• następnie zaznaczamy Columns, Step value: 1, Stop Value: 10 i OK

2. Wstawienie poziome
Analogicznie wstawiamy 10 liczb, ale tym razem poziomo począwszy od komórki B1 do K1

a) Można użyć podobnej metody co powyżej tylko zamiast Columns zaznaczamy Rows.

b) Alternatywnie, mając wstawiony już ciąg liczb pionowo, można go użyć w poziomie:
• zaznacz stworzony zakres liczb A2:A11;
• kopijujemy do schowka CTRL + C;
• począwszy od komórki B1 wklej jako Transpozycja
Paste Special / Transpose.

c) Ponadto istnieje jeszcze jedna metoda wstawienia transpozycji liczby na sztywno, powiązanych ze sobą za pomocą formuły tablicowej.
• najpierw zaznaczamy zakres B1:K1, w którym chcemy wprowadzi formułę.
• w nawiasie funkcji jako argument wstawiamy zakres, który chcemy skopiować =TRANSPOSE(A2:A11)
• i akceptujemy CTRL + SHIFT + ENTER

Rozwiązanie tabliczki mnożenia

Kiedy już mamy przygotowana podstawę tabliczki mnożenie możemy przejść do jej rozwiązania. W tym wypadku zakres ten będzie B2:K11. Podam dwie szybkie metody rozwiązania:

a) Za pomocą zwyklej formuły

• zaznaczamy zakres, w którym chcemy wypełnić formułę B2:K11 począwszy od B2
• wstawiamy formułę: =$A2*B$1
• akceptujemy wyedytowana formułę wypełniając ją w zaznaczonym zakresie za pomocą kombinacji klawiszy Ctrl + Enter

b) Za pomocą formuły tablicowej

• zaznaczamy zakres, w którym chcemy wypełnić formułę B2:K11
• wstawiamy formułę: =A2:A11*B1:K1
• jak każda formułę tablicową, akceptujemy kombinacja klawiszy CTRL + SHIFT + ENTER

Formatowanie tabliczki mnożenia

• Zaznacz cała tabelę CTRL + * bądź jej zakres wewnętrzny z formułami
• Home > Conditional Formatting > Color scales i wybierz 2 opcje co spowoduje, że najwyższe wartości pokolorują się na czerwono, najniższe zaś na zielono a pomiędzy nabiorą kolorów pośrednich.

Tutaj pobierz plik przykładowy zawierający rozwiązania.
Podobne zagadnienia i sztuczki w Excel-u można nauczyć się u mnie na kompleksowym szkoleniu w Londynie.

Jak znacznie zmniejszyć rozmiar pliku Excel

Jak znacznie zmniejszyć rozmiar pliku Excel?

10 najskuteczniejszych sposobów na zmniejszenie pliku Excel-a bez szkody dla jego potrzebnej zawartości.

1) Zapisz plik we właściwej wersji.

    • zapisz plik w nowej wersji Excel Workbook (xlsx) zamiast (xls)
    • zapisz plik w systemie binarnym Excel Binary workbook (xlsb)

    • spakuj plik w formacie (zip)

2) Usuń niepotrzebne komórki i zakresy komórek.

    • usuń bezużyteczne tabele, kolumny, rzędy
    • usuń niewidoczną zawartość w pustych zakresach metodą poniżej:

F5 / Special… / Blanks

Home / Clear / Clear All

    • usuń niewidoczna zawartość w pustych zakresach metodą poniżej:

Naciśnij CTR + End aby przeskoczyć na koniec arkusza. Jeśli występują puste odstępy pomiędzy końcem arkusza a końcem naszych widocznych zawartości w komórkach, należy się pozbyć. Najlepiej poprzez usunięcie całych kolumn jak i całych rzędów.

3) Zredukuj grafikę.

    • usuń bezużyteczną grafikę, obiekty, zdjęcia

Aby znaleźć wszystkie również niewidoczne czy też ukryte obiekty kliknij:

Home, Find & Select, Selection Pane…

    • skompresuj pozostałą grafikę

Zaznacz zdjęcie i kliknij:

Compress Picture / Minimise document size for sharing
Compressing Picture

 

4) Zredukuj sprawdzanie poprawności danych.

    • zaznacz cały arkusz


Data / Data Validation / Setting / Allow: Any value

5) Zredukuj formatowanie warunkowe.

    • zaznacz cały arkusz jak powyżej

Home / Conditional Formatting / Manage Rules…

    • zaznacz nieistotne Reguły i usuń

Rule / Delete Rule

6) Zredukuj liczbę niepotrzebnych arkuszy (być może ukrytych).

7) Zoptymalizuj używanie formuł.

  • unikaj użycia funkcji nietrwałych takich jak: TODAY, NOW, INDIRECT, RAND, RANDBETWEEN, CELL, OFFSET, INFO.
  • zastąp VLOOKUP, HLOOKUP i LOOKUP na INDEX & MATCH.
  • zastąp zagnieżdżone funkcje IF na alternatywne typu: IFS, REPT, INDEX & MATCH, SUMIF(s) lub funkcjami logicznymi.
  • zastąp formuły wartościami tam, gdzie to jest możliwe.

8) Używaj manualnego liczenia.

Formulas / Calculation options / Manual

Tylko pamiętaj, jeśli chcesz odświeżyć liczenie, należy użyć klawisz F9, za każdym razem po prowadzeniu nowych danych mających wpływ na wynik formuł lub po prowadzeniu bądź modyfikowaniu formuł.

9) Usuń bądź napraw błędne, niepotrzebne Hiperłącza, Nazwy i formuły.

10) Zastąp formuły makrem.

Request a quote

Istnieje jeszcze jedna najskuteczniejsza metoda na radykalne zmniejszenie pliku Excel-a, mianowicie usunięcie całej zawartości. Niemniej nie jestem pewien czy spełnia warunek o zachowaniu potrzebnych danych😊

English Version

Podwójne kliknięcia w Excelu – 20 sztuczek

Podwójne kliknięcia w Excelu

Podwójne kliknięcia w Excelu

20 sztuczek za pomocą podwójnego kliknięcia myszą w Excelu:

1. Zatwierdzanie z pominięciem klawisza OK

Podczas formatowania komórek, otwiera się okno, w którym można zmienić ustawienia i zatwierdzić je przyciskiem OK. Można tą czynność pominąć dwukrotnie kliknąć na konkretnych ustawieniach.
Skip the OK button

2. Zwinięcie / rozwinięcie wstążki

Poprzez podwójne kliknięcie którejkolwiek karty, wstążkę można zwinąć bądź rozwinąć w celu zwiększenia widoczność powierzchni arkusza.
[Ctrl]+[F1].
Collapse the Ribbon
Aby przywrócić wstążkę z powrotem, należy ponownie dwukrotnie kliknąć w wybrana karte

3. Zmiana nazwy arkusza

Poprzez dwukrotne kliknięcie na nazwie arkusza, podświetla się ta nazwa co oznacza, że można teraz wprowadzić nową nazwę.
[Alt]+[O][H][R].
Rename Sheet

4. Zablokowanie malarza formatu

Dwukrotne kliknięcie przycisku malarz formatów powoduje, zablokowanie go co oznacz, że zaadaptowany format można używać wielokrotnie.
format painter
Aby wyłączyć zablokowanie malarza formatów, należy kliknąć ponownie jego przycisk lub nacisnąć klawisz ESC.

5. Autouzupełnianie danych

a) Po zaznaczeniu jednej komórki
Jeśli najedziemy myszką na prawy dolny róg komórki, wyświetli się nam czarny krzyżyk. Podwójne jego kliknięcie spowoduje wypełnienie kolumny w dół tą samą formułą aż do momentu, gdzie ciąg wypełnienia w poprzedzającej kolumnie się zakończy.
W przypadku, gdy pierwsza komórka zawiera format daty, autouzupełnianie wypełni się tak, że każda kolejna powstała komórka będzie domyślnie kolejną datą. Natomiast w przypadku, gdy pierwsza komórka zawiera tekst zakończony cyfrą, autouzupełnianiem będzie tekst z kolejną liczbą.
auto fill down
b) Po zaznaczeniu wielu komórek, wypełnienia danych nastąpi na podstawie algorytmu uwzgledniającego wskazane dane.

6. Autodostosowanie szerokości kolumn lub wysokości wierszy

a) Szerokość kolumn
Zaznacz kolumnę bądź kolumny, które chcesz, aby optymalnie auto dopasowały swoją szerokość na podstawie zawartości w danych kolumnach. Następnie dwukrotnie kliknij na prawą krawędź nagłówka kolumny.
[ALT]+[O][C][A].
adjast columns

b) Wysokość wierszy
Analogicznie można dostosować wysokość kolumn do zawartości w wybranych wierszach poprzez ich zaznaczenie oraz dwukrotne klikniecie krawędzi dolnej nagłówka wiersza.

7. Szybki skok na brzegi tabeli

[Ctrl]+(Arrow) 
jump to edge

8. Wygenerowanie poszczególnych rekordów z wyników tabeli przestawnej

Pivot Table Data

Podwójne kliknięcie na wynikach tabeli przestawnej powoduje wygenerowanie w osobnym arkuszu tabele rekordów ale tylko tych, które wpłyneły na ten wynik.

9. Formatowanie wyświetlanych wartości w tabeli przestawnej.

Podwójne kliknięcie na nagłówku w tabeli przestawnej spowoduje wyświetlenie ustawień formatowania wartości w kolumnie.

10. Tworzenie podkategorii serii danych z tabeli przestawnej

Podwójne kliknięcie w serie danych w tabeli przestawnej spowoduje stworzenie podkategorii w tych seriach.

11. Wyświetlenie podkategorii serii danych w tabeli przestawnej

Podwójne kliknięcie w serie danych w tabeli przestawnej, które zawierają podkategorie spowoduje ich zwinięcie bądź rozwinięcie.

12. Wyłączenie podziału okna

Podwójne kliknięcie w pasek podziału pionowego lub poziomego okna spowoduje jego usunięcie.

Skrót klawiaturowy [ALT]+[W][S] włącza lub wyłącza obydwa paski podziału okna.

13. Powiększanie / pomniejszanie okna

[windows]+[down]/[up].
maximise or minimise

14. Śledzenie formuły

a) Podwójne kliknięcie w pasek formuły zawierający formułę spowoduje podświetlenie kolorowo komórki użyte w równaniu [F2].
b) Natomiast podwójne kliknięcie w komórkę zawierający formułę spowoduje zaznaczenie komórek użytych w równaniu.

15. Otwarcie okna formatowania wykresu

16. Edytowanie tekstu w kształcie

17. Zamykanie pliku

Podwójne kliknięcie w lewym górnym rogu arkusza spowoduje próbę zamknięcia aktywnego pliku. Jeśli w pamięci zachowały się niezapisane zmiany pojawi się okno z zapytaniem czy je zapisać, jeśli natomiast plik został zapisany, to spowoduje jego zamknięcie.
[Alt]+[F4].
Close Excel

18. Śledzenie źródła zdjęcia wykonanego aparatem.

Podwójne kliknięcie na zdjęcia spowoduje przekierowanie na oryginalne źródło obiektu fotografowanego pod warunkiem, ze znajduje się w tym samym arkuszu. Jak robimy zdjęcia – aparat w Excelu?

19. Zaznaczenie wyrazu

Podwójne kliknięcie na wyraz w pasku formuły powoduje jego zaznaczenie podobnie jak w innych aplikacjach MS Office.

20. Potrójne kliknięcie na pasku formuły w którym znajduje się więcej niż jeden wyraz spowoduje zaznaczenie całego akapitu podobnie jak w innych aplikacjach MS Office

English Version