Formuła – Ile brakuje Ci sprzedaży do następnego bonusu – sztuczki #19

Jak obliczyć ile brakuje Ci sprzedaży do następnego bonusu?

Jeśli masz tabele z zabranymi progami sprzedaży i odpowiadającymi im bonusami, to kwestia wyznaczenia bonusu jaki Ci przysługuje jest bardzo prosty. Wystarczy, że wykorzystasz taką funkcję:

=WYSZUKAJ.PIONOWO(B1;A6:B11;2)


Formuła – Ile brakuje Ci sprzedaży do następnego bonusu – sztuczki #19

Funkcja WYSZUKAJ.PIONOWO wyszykuje przybliżonej wartości (B1) w obszarze (A6:B11), a następnie zwraca wartość bonusu z wyszukanego wiersza i wyznaczonej kolumny (2).

Wyszukany należny bonus dla danej wielkości sprzedaży

Wyszukany należny bonus dla danej wielkości sprzedaży

Jeśli jednak chcesz wyszukać ile brakuje Ci sprzedaży do następnego bonusu potrzebujesz zbudować większą formułę.

Najpierw potrzebujesz znaleźć w tabeli (A6:A11) pozycję gdzie znajduje się próg (B1) dla kolejnego bonusu. Najlepiej wykorzystaj funkcję:

=PODAJ.POZYCJĘ(B1;A6:A11)+1

Do funkcji dodajesz 1 ponieważ zwracany jest wiersz o 1 za nisko.

Znaleziona pozycja kolejnego progu bonusu

Znaleziona pozycja kolejnego progu bonusu

Kiedy masz już pozycję z tabeli, trzeba wyciągnąć wartość z tego pola i odjąć aktualną wartość sprzedaży:

=INDEKS(A6:A11;PODAJ.POZYCJĘ(B1;A6:A11)+1)-B1

Obliczenie ile potrzebujesz by zasłużyć na większy bonus

Obliczenie ile potrzebujesz by zasłużyć na większy bonus

Ewentualnie możesz jeszcze zabezpieczyć się przed błędem, gdy Twoja sprzedaż przekracza próg sprzedaży dla najwyższego bonusu:

=JEŻELI.BŁĄD(INDEKS(A6:A11;PODAJ.POZYCJĘ(B1;A6:A11)+1)-B1;”Masz najwyższy bonus”)

P.S.

Wpis na podstawie Excel Magic Trick 995

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Formuła – Ile brakuje Ci sprzedaży do następnego bonusu – sztuczki #19

Zaznacz wszystkie puste komórki – sztuczki #18

Jak zaznaczyć wszystkie puste komórki w tabeli?

Czasami zdarza się tak, że tworzysz tabelę ona się rozrasta, a zostały w niej puste pola z różnych przyczyn. Teraz chcesz te pola szybko i łatwo uzupełnić, jak to zrobić szybko i łatwo w Excelu?


Zaznacz wszystkie puste komórki – sztuczki #18

Żeby w Excelu chcesz zaznaczyć wszystkie puste komórki z tabeli to najszybciej zrobisz to wykorzystując następującą kombinacje klawiszy i opcji:

      1) Zaznacz tabelę (Ctrl + Shift + 8),
    Zaznaczona cała tabela

    Zaznaczona cała tabela

      2) Naciśnij F5,
    Okno Przechodzenie do po naciśnięciu klawisza F5

    Okno Przechodzenie do po naciśnięciu klawisza F5

      3) Wybierz guzik Specjalnie,
      4) Wybierz opcję Puste,
    Okno Przechodzenie do - specjalnie - zaznaczona opcja Puste

    Okno Przechodzenie do – specjalnie – zaznaczona opcja Puste

      5) Naciśnij guzik Ok lub wciśnij Enter

    Zaznaczona puste komórki w tabeli

    Zaznaczona puste komórki w tabeli

Będziesz miał zaznaczone wszystkie puste. Teraz możesz się przemieszczać pomiędzy nimi naciskając Enter lub Tab aby przesuwać w dół albo kombinację tych klawiszy z Shiftem by przesuwać się w górę.

Dodatkowo możesz wpisywać dowolne wartości w aktywne komórki i zatwierdzać je Enterem, a wszystkie zaznaczone komórki dalej będą zaznaczone. Możesz w ten sposób wpisać wartości do wszystkich pustych komórek.
Jeśli zatwierdzisz wartość naciskając np: strzałkę w dół, to komórki zostaną odznaczone.

Możesz również wypełnić wszystkie zaznaczone komórki jednocześnie, wpisując wartość, bądź formułę i zatwierdzając ją kombinacją klawiszy Ctrl + Enter

Wypełnianie formułą wszystkich komórek (Ctrl + Enter)

Wypełnianie formułą wszystkich komórek (Ctrl + Enter)

P.S.

Wpis na podstawie EMT 996

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Zaznacz wszystkie puste komórki – sztuczki #18

Formatowanie warunkowe kolejność formatowania – porada #75

Które formatowanie warunkowe bierze górę, gdy jest ustawione kilka formatowań?

Kiedy korzystasz z formatowania warunkowego, z kilkoma warunkami dla danego obszaru ważna jest kolejność w jakiej znajdują się na liście formatowania warunkowego dla danego obszaru. Jest to istotne tylko wtedy, kiedy granice warunków ustawionych w regułach zachodzą na siebie.


Formatowanie warunkowe kolejność formatowania – porada #75

Formatowanie warunkowe ustawione wyżej na liście zamalowuje te ustawione niżej. Również każde formatowanie warunkowe zamalowuje formatowanie, które ustawiłeś sam ręcznie.

Formatowanie wyżej jest ważniejsze od formatowania ustawionego niżej

Formatowanie wyżej jest ważniejsze od formatowania ustawionego niżej

Zwróć uwagę, że czasem przy formatowaniu warunkowym nie masz ustawionych żadnych parametrów. Np: możesz nie stosować żadnego wypełnienia tła komórki. Ważne nie zastosowanie żadnego tła jest różne od ustawienia parametru brak wypełnienia. Pierwsze nie będzie nic zamalowywało, natomiast drugie zamaluje wszystkie wcześniejsze wypełnienia i pozostawi komórkę bez wypełnienia nawet jakby wcześniej było nadane jej wypełnienie.

Formatowanie nie zamalowuje obramowań, 2 ma obramowania, ale 1 nie ma ustawionych żadnych zmian

Formatowanie nie zamalowuje obramowań, 2 ma obramowania, ale 1 nie ma ustawionych żadnych zmian

Najłatwiej to poznasz jeśli przy ustawianiu formatowaniu zauważysz aktywny przycisk Wyczyść. Oznacza to, że przy tym formatowaniu dany parametr formatowania (np: wypełnienie) będzie zamalowywane.

Na karcie wypełnienie nie zostało wybrane żadne formatowanie - przycisk Wyczyść nieaktywny

Na karcie wypełnienie nie zostało wybrane żadne formatowanie – przycisk Wyczyść nieaktywny

Kiedy będziesz dodawał nowe formatowanie warunkowe,

Dodane nowe formatowanie na zaznaczonym obszarze

Dodane nowe formatowanie na zaznaczonym obszarze

będzie ono ustawione na szczycie listy. Oznacza to, że jest ono nadrzędne do wcześniej wpisanych reguł i będzie je zamalowywało jeśli będą spełnione warunki reguły.

Nowe formatowanie warunkowe dodaje się na samą górę

Nowe formatowanie warunkowe dodaje się na samą górę

Do zarządzania regułami możesz wejść z karty Narzędzia główne, albo naciskając kombinację klawiszy z wcześniejszych wersji Excela. Po kolei:

  • Alt
  • F
  • F

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Formatowanie warunkowe kolejność formatowania – porada #75

Jak sumować wybrane 5 komórek w kolumnie w zależności od wiersza w którym jesteś – porada #74

Jak sumować wybrane 5 komórek w kolumnie w zależności od wiersza w którym jesteś?

Miałem potrzebę, żeby sumować 5 wybranych komórek w zależności od wiersza w którym byłem. Numery wierszy odpowiadały konkretnym datą i w zależności od przedziału, była potrzeba sumować inne zakresy, w grupach po 5 komórek.

Komórki do sumowania i oczekiwany wynik

Komórki do sumowania i oczekiwany wynik


Jak sumować wybrane 5 komórek w kolumnie w zależności od wiersza w którym jesteś – porada #74

Zacząłem od tego, że potrzebowałem wiedzieć, w którym z kolei wierszu jestem i która z kolei to piątka. Zacząłem od funkcji ILE.WIERSZY z wpisanym dynamicznie zakresem:

ILE.WIERSZY($B$2:B2)

Liczenie numeru wiersza przy pomocy dynamicznie powiększającego się zakresu

Liczenie numeru wiersza przy pomocy dynamicznie powiększającego się zakresu

Następnie potrzebowałem wyznaczyć do której z kolei grupy należą poszczególne wiersze. Wykorzystałem funkcję ZAOKR.W.DÓŁ i dzielenie przez 5. Trzeba tylko było zwrócić uwagę, że ILE.WIERSZY zaczyna liczyć od 1, a do uzyskanie prawidłowego podziału na grupy trzeba zacząć od zera, czyli potrzebuje odjąć 1 i zwrócić uwagę na kolejność działań arytmetycznych (dodać nawiasy)

ZAOKR.W.DÓŁ((ILE.WIERSZY($B$2:B2)-1)/5

Podział komórek na grupy w zależności od ich pozycji

Podział komórek na grupy w zależności od ich pozycji

Kiedy mam już podział na grupy mogę użyć funkcji PRZESUNIĘCIE, by znaleźć zakres, który chce zsumować. Zaczynam od 1 komórki zakresu ($C$2), a następnie przesuwam się w dół o ilość wierszy odpowiednią od numeru grupy przemnożonej przez 5, o 0 kolumn, a następnie ma pobrać zakres o wysokości 5 wierszy i 1 kolumnie.

=SUMA(PRZESUNIĘCIE($C$2;5*ZAOKR.W.DÓŁ((ILE.WIERSZY($B$2:B2)-1)/5;1);0;5;1))

Przy funkcji PRZESUNIĘCIE zwróć uwagę na to, że zwraca ono nazwę zakresu, a nie konkretną wartość, więc jeśli będziesz podglądał wynik w trybie edycji klawiszem F9, to uzyskasz inny wynik niż w przy Szacowaniu Formuły.

Funkcja przesunięcie sama zwraca tylko wartość z 1 komórki obszaru

Funkcja przesunięcie sama zwraca tylko wartość z 1 komórki obszaru

Możesz zobaczyć w opisie funkcji przesunięcie albo Szacując formułę, że zwraca ona nazwę zakresu:

Przy szacowaniu formuły widać, że funkcja PRZESUNIĘCIE zwraca zakres

Przy szacowaniu formuły widać, że funkcja PRZESUNIĘCIE zwraca zakres

Zamiast funkcji ILE.WIERSZY możesz użyć funkcji WIERSZ, wtedy formuła będzie wyglądać tak:

=SUMA(PRZESUNIĘCIE($C$2;5*ZAOKR.W.DÓŁ((WIERSZ()-WIERSZ($B$2))/5;1);0;5;1))

Końcowa funkcja sumująca 5 wybranych komórek w zależności od pozycji

Końcowa funkcja sumująca 5 wybranych komórek w zależności od pozycji

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Jak sumować wybrane 5 komórek w kolumnie w zależności od wiersza w którym jesteś – porada #74

Jak wstawić do Excela 16 cyfrowy numer karty kredytowej – sztuczki #17

Jak wstawić do Excela 16 cyfrowy numer karty kredytowej?

Excel potrafi obsługiwać maksymalnie liczby 15 cyfrowe. Prowadzi to, do tego, że przekłamuje on liczby, które mają więcej cyfr np: numer karty kredytowej (przykładowy 2300111122223333) wpisany do Excela traci ostatnią cyfrę.

Obcięta ostatnia cyfra z numeru karty kredytowej

Obcięta ostatnia cyfra z numeru karty kredytowej

Niestety Excel może Cię też niemile zaskoczyć, przy wpisywaniu innych liczb zwłaszcza tych zaczynających się od zera. Np: jeśli chcesz wpisać 02/17 Excel może to całkiem inaczej zinterpretować, nawet jeśli potraktuje to jako ułamek to obetnie zero, a przecież na karcie kredytowej te zera są na początku terminu ważności.


Jak wstawić do Excela 16 cyfrowy numer karty kredytowej – sztuczki #17

Jak poradzić sobie z tymi niedoskonałościami Excela. Możesz nadać komórkom, w które będziesz wpisywał numer karty kredytowej i jej termin ważności formatowanie tekstowe. Liczby będą wpisywały się całe, tylko będą traktowane jak tekst (np: wyrównanie do lewej).

Przykładowy sposób nadania formatowania tekstowego komórkom

Przykładowy sposób nadania formatowania tekstowego komórkom

Dla kart kredytowych ważny jest jeszcze 3 cyfrowy kod, który też może zaczynać się od zera. Tutaj możesz zastosować inną strategię i zastosować formatowanie niestandardowe

  • wejdź w okno formatowania komórek (Ctrl + 1),
  • wybierz formatowanie niestandardowe,
  • wpisz w nie 3 zera 000
Formatowanie niestandardowe, które wyświetla przynajmniej 3 cyfry (nawet tylko zera)

Formatowanie niestandardowe, które wyświetla przynajmniej 3 cyfry (nawet tylko zera)

Teraz Excel będzie zawsze wstawiał przynajmniej 3 cyfry, nawet jeśli byłyby to same zera.

Jest jeszcze jedna prosta możliwość. Wystarczy, że zaczniesz wpisywaną wartość w komórce poprzedzić apostrofem

Liczba poprzedzona apostrofem, będzie wpisana jako tekst

Liczba poprzedzona apostrofem, będzie wpisana jako tekst

Dasz znać Excelowi, że podaną wartość ma traktować jak tekst (Excel nie wyświetli apostrofu).

P.S.

Wpis na podstawie podcastu MrExcela 1702

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube – Jak wstawić do Excela 16 cyfrowy numer karty kredytowej – sztuczki #17