Jak podświetlić kilka dat jednocześnie (formatowanie warunkowe)?
Chcesz, żeby formatowanie warunkowe podświetliło Ci linie, w których jest jedna z 3 dat (albo więcej)?
Formatowanie warunkowe podświetlanie 3 lub więcej dat — sztuczki #23
Możesz wykorzystać funkcję LUB i sprawdzić wartość dla każdej komórki osobno, ale jeśli tych komórek jest więcej łatwo się pomylić w odwołaniach względnych i bezwzględnych (tak mi się zdarzyło na filmie, ale akurat na komórce, która się nie sprawdzała 😉
=LUB($A4=$D$4;$A4=$D$5;$A4=$D$6;$A4=$D$7)
Sprawdzanie poprawności dat za pomocą funkcji LUB
Kiedy, jakaś wartość będzie się zgadzała funkcja LUB zwróci wartość PRAWDA, jeśli żadna wartość FAŁSZ
Całkiem innym podejściem jest wykorzystanie funkcji PODAJ.POZYCJĘ, której zadaniem będzie sprawdzenie czy data z danego wiersza znajduje się na liście dat, których szukasz. Jeśli się znajduje to zwróci pozycję, czyli liczbę, jeśli nie to błąd #N/D! , czyli brak na liście.
=PODAJ.POZYCJĘ($A4;$D$4:$D$7;0)
Sprawdzanie poprawności dat za pomocą funkcji PODAJ.POZYCJĘ
W większości operacji Excela ten błąd stanowi duży problem i trzeba sobie z nim poradzić. W tej sytuacji można wykorzystać funkcję CZY.LICZBA, która będzie zwracać wartość PRAWDA dla liczb, a dla błędu #N/D! zwróci fałsz.
=CZY.LICZBA(PODAJ.POZYCJĘ($A4;$D$4:$D$7;0))
Sprawdzanie poprawności dat za pomocą funkcji PODAJ.POZYCJĘ z obsługą błędu
Jednak dla Formatowania Warunkowego nie ma to znaczenia możesz śmiało wykorzystać samą funkcję PODAJ.POZYCJĘ. Formatowanie warunkowe, będzie formatowało komórki, gdzie będą zwracane liczby, a komórki z błędami pominie.
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.
Jak sprawdzić czy została przekroczona data ważności produktu (formatowanie warunkowe)?
Różne produkty mają często daty ważności. Formatowanie warunkowe pomoże Ci szybko zaznaczyć te produkty, którym skończył się termin ważności.
Formatowanie warunkowe — sprawdzanie datę ważności produktu — sztuczki #22
Załóżmy, że termin ważności produktu to 6 miesięcy. Czyli, żeby dowiedzieć się, czy produkt jest jeszcze zdatny do użycia potrzebujesz jeszcze daty produkcji (plus 6 miesięcy to będzie data ważności) i daty dzisiejszego dnia.
Datę ważności wyznaczasz dodając 6 miesięcy do daty produkcji. Żeby to najlepiej i najprościej zrobić skorzystaj z funkcji NR.SER.DATY, zapomnisz dzięki niej o różnych datach końca miesiąca i różnej ilości dni w miesiącu:
=NR.SER.DATY(A2;6)
Obliczanie daty 6 miesięcy po dacie produkcji — NR.SER.DATY
Do wyznaczenia dzisiejszej daty skorzystaj z funkcji DZIŚ. Teraz trzeba te daty porównać i otrzymasz wynik.
Sprawdzanie czy przekroczona data ważności
Ponieważ tabela z produktami składa się z 2 kolumn trzeba odpowiednio jeszcze ustawić odwołania (Wiersze mają się zmieniać, a Kolumna zostać stała)
=DZIŚ()>NR.SER.DATY($A2;6)
Teraz tylko zaznacz obszar pod formatowanie warunkowe produktu (przeważnie zaznaczasz od lewego górnego rogu, komórka w nim ma być aktywną), a następnie wciskasz po kolei przyciski:
Alt
F
F
Alt + N (Nowa reguła)
schodzisz strzałka na ostatnią opcję i wklejasz stworzoną wcześniej formułę. Teraz Excel zaznacza Ci wiersze, dla produktów, którym skończyła się data ważności.
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.
W poszczególnych dniach tygodnia masz różną sprzedaż. Chcesz wiedzieć jak zsumować wartość sprzedaży dla konkretnego dnia.
Sumowanie sprzedaży po dniu tygodnia — sztuczki #21
Najpierw potrzebne są daty. Ich odpowiednie formatowanie
dddd, mmmm dd, rrrr
zapewni Ci, że będziesz widział wszystkie potrzebne informacje wpisując bardzo mało. Powyższe formatowanie zapewni Ci, że każda data będzie pokazywana w komórce w koncepcji:
nazwa dnia, nazwa miesiąca numer dnia, rok
Tabela z sformatowanymi odpowiednio datami
Jednak wyświetlany "piątek" jest wynikiem formatowania i ciężko byłoby wyciągać nazwę dnia, nawet jakby byłby to tekst. O wiele proście pracować na datach (liczbach) i skorzystać z funkcji TEKST z odpowiednimi argumentami:
TEKST($A$2:$A$18;"ddd")
1 argument ($A$2:$A$18) to zakres gdzie są przechowywane daty,
2 argument ("ddd") mówi Excel ma wyciągnąć z podanych liczb tylko nazwę skróconą dnia.
Nazwa skrócone dni tygodnia zawiera tabela poniżej:
Tabela ze skróconymi nazwami dni tygodnia
Teraz należy przyrównać wynik funkcji TEKST do kryterium. Uzyskasz w ten sposób tablicę z wartością PRAWDA wtedy gdy kryterium będzie spełnione ({FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ})
Kolejnym krokiem będzie wykorzystanie funkcji SUMA.ILOCZYNÓW i dołożenie kolejnego czynnika jakim jest zakres z wartościami sprzedaży w poszczególne dni
ponieważ jednak funkcja SUMA.ILOCZYNÓW nie rozumie wartości PRAWDA i FAŁSZ, żeby otrzymać prawidłowe wartości, trzeba jeszcze wynik porównania funkcji TEKST zamienić na wartości liczbowe — trzeba na tekście wykonać operację matematyczną (dodać 0, przemnożyć przez 1, wykorzystać podwójny znak minus –), ponieważ w Excelu z tych operacji najszybciej działa podwójna negacja (2 znaki minus),
Wartości PRAWDA i FAŁSZ zamienione na 0 i 1 w formule
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.
Jak znaleźć najmniejszą cyfrę z liczby (funkcja MIN lub AGREGUJ)?
Żeby znaleźć najmniejszą cyfrę z komórki trzeba się trochę natrudzić. Najpierw trzeba wyciągnąć po jednej wszystkie cyfry, a następnie znaleźć tą najmniejszą.
Najmniejsza cyfra z komórki za pomocą funkcji MIN lub AGREGUJ — sztuczki #20
Najpierw tworzymy formułę, która wyciągamy niejako odwołania do ilości wierszy
="1:"&DŁ(A2)
Formuła z funkcją DŁ — przygotowania do odwołań pośrednich
i ich odwołanie pośrednie
=ADR.POŚR("1:"&DŁ(A2))
Nie przejmuj się jeśli ta część zwraca niepoprawne wartości, bo nie o nie nam chodzi, tylko o to co uzyskamy, gdy wyciągniemy wartość z tej formuły za pomocą funkcji WIERSZ
=WIERSZ(ADR.POŚR("1:"&DŁ(A2)))
Przykładowy wynik tej formuły to {1;2;3;4;5;6} , czyli tablica z numerami pozycji poszczególnych cyfr. Ponieważ formuła zwraca tablicę, a nie da się wpisać poprawnie całej tablicy, do 1 komórki, dlatego Excel wyświetla tylko pierwszą wartość z ciągu tablicy, czyli w tych obliczeniach 1.
Przykładowy wynik przy sprawdzaniu formuły zapisanej powyżej i wyniki wpisywane w komórki
Dodanie zera na końcu formuły służy zamianie wyniku funkcji FRAGMENT.TEKSTU z wartości traktowanych jako tekst ({"6";"4";"6";"5";"6";"4"}) na liczby ({6;4;6;5;6;4})
Zwróć uwagę na to, że funkcja MIN przyjmuje pojedyncze liczby, a podajemy jej całą tablicę cyfr. Jeśli odpowiednio jej nie zatwierdzisz zwróci niepoprawny wynik (pierwszą cyfrę liczby), ponieważ reszty nie bierze pod uwagę, bo nie dałeś jej znać, że ma być traktowana jako formuła tablicowa. Należy ją zatwierdzić naciskając Ctrl + Shift + Enter
Wtedy Excel w pasku formuły otoczy ją nawiasami klamrowymi
Poprawny wynik formuły tablicowej po zaakcepetowaniu jej jako formuła tablicowa klawiszami Ctrl + Shift + Enter
żeby dać Ci znać, że będzie liczył tą formułę jako tablicowa (wpisanie samemu nawiasów klamrowych nic Ci nie da)
Zamiast funkcji MIN możesz wykorzystać funkcję AGREGUJ, która rodzi sobie z tablicami i nie trzeba mówić Excelowi, żeby traktował ją jako formułę tablicową.
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.
