Jak wyszukać fragmentu tekstu funkcją WYSZUKAJ.PIONOWO
Jak wyszukać pionowo po fragmencie tekstu? Jeśli znasz trochę Excela to domyślisz się, że przydatna będzie funkcja WYSZUKAJ.PIONOWO. Tylko wpisywanie do niej samego fragmentu tekstu, który chcesz wyszukać nie sprawdzi się.
WYSZUKAJ.PIONOWO po fragmencie tekstu — porada #49
Trzeba go obwarować dodatkowymi znakami, chodzi o "*" ten ciąg znaków w tłumaczeniu na język Excela oznacza dowolny ciąg znaków, nawet pusty. Czyli jeśli fragment tekstu, którego szukasz znajduje się w komórce C1, to wyszukiwany w fragment w funkcji WYSZUKAJ.PIONOWO będzie wyglądał tak:
"*"&C1&"*"
czyli
1) najpierw dowolny ciąg znaków "*" ,2) następnie jego łączenie z szukanym fragmentem & ,3) szukany fragment C1 ,4) jego połączenie z dowolnym ciągiem znaków & ,5) dowolny ciąg znaków na końcu "*" .
Reszta funkcji WYSZUKAJ.PIONOWO wygląda standardowo i w zależności od miejsca gdzie chcesz znaleźć fragment tekstu może wyglądać tak:
=WYSZUKAJ.PIONOWO("*"&C1&"*";A1:A6;1;FAŁSZ)
Ważny tu jest parametr FAŁSZ na końcu funkcji. Oznacz on, że Excel ma szukać dokładnego dopasowania. Jeśli wstawiłbyś wartość PRAWDA, wynik był błąd — komunikat, że nie znaleziono wartości #N/D!
Tak zapisana formuła wyszukiwania fragmentu tekstu znajdzie Ci jego pierwsze wystąpienie, czyli jeśli zmienisz uporządkowanie swojej listy wynik też może się zmienić. Pod spodem 2 obrazki z dwoma różnymi wynikami w zależności od kolejności elementów na liście.
WYSZUKAJ.PIONOWO wynik pierwszy dla fragmentu tekstu
WYSZUKAJ.PIONOWO wynik drugi dla zmienionej kolejności listy
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.
Tabela kwadratów, które zamalowują się gdy procent rośnie
Wizualizacja 100% za pomocą tabeli małych kwadratów — sztuczki #8
W Excelu możesz robić wiele ciekawych sztuczek. Możesz np: stworzyć tabelę 10x10, której pola zmieniają kolor w zależności od wartości w innej komórce. Ta wartość może być procentem od 1% do 100% i w zależności od wartości kolor zmienia od 1 do 100 kwadracików.
Tablice reprezentujące procenty za pomocą małych kwadratów
To zadanie jest proste, wystarczy znać trochę formatowania, zwłaszcza warunkowego. Najpierw trzeba przygotować teren, czyli zaznaczyć obszar 10x10 i pokolorować go jasnym kolorem. Dodatkowo warto wszystkie obramowania ustawić na białe, wtedy końcowy efekt wizualny wychodzi znacznie lepiej.
Po pokolorowaniu trzeba wpisać wartości w komórki, od 1% do 100%, przydadzą się tutaj odpowiednie formuły i przeciąganie, żeby było szybko.
Teraz potrzebujesz pola według którego będziesz sprawdzać wartość procentową. Dla lepszego efektu w filmie skorzystałem z funkcji losowej LOS.ZAKR w odpowiedniej formule:
=LOS.ZAKR(1;100)/100
Daje ona losowy wynik od 1% do 100% z dokładnością do 1%. Trzeba tylko nadać komórce formatowanie procentowe inaczej będziesz widział ułamki dziesiętne od 0,01 do 1
Ponieważ standardowo komórki Excela nie są kwadratami, trzeba sprawdzić, jaka jest wysokość komórek, a następnie odpowiednio zwęzić wszystkie kolumny, żeby uzyskać efekt jak na filmie.
Teraz najtrudniejsza część zadania — wykorzystanie formatowania warunkowego. Zaznacz obszar tabeli, którą chcesz sprawdzać. Wejdź na zakładkę Narzędzia główne i z niej wybierz polecenie Formatowanie warunkowe — Nowa reguła.
Formatowanie warunkowe i tablica z procentami
Wybierz ostatnią pozycję z listy możliwych warunków, a następnie wpisz podobną formułę:
=B4=$B$14
Gdzie B4 to lewy górny róg tabeli z procentami, a $B$14 to odwołanie bezwzględne do komórki według sprawdzasz warunek (tej z funkcją LOS.ZAKR). Dodaj jeszcze formatowanie — wypełnienie ciemniejszą wersją koloru, który wybrałeś i gotowe.
[caption id="attachment_961" align="aligncenter" width="540"] Formuła sprawdzająca formatowania warunkowego wraz z ustawionym formatowaniem[/caption]
Nie do końca, bo pewnie zauważyłeś, że w komórkach dalej są wartości, a właściwie płotki — #, które mówią, że komórka jest za mała i przechowywana w nich liczba się nie mieści. Wypadałoby to poprawić, bo wygląda tandetnie.
Teraz zaznacz obszar naciśnij Ctrl + 1, przejdź do formatowania liczb, wybierz formatowanie niestandardowe i wpisz ;;; (3 średniki) i załatwione płotki # zniknęły.
Formatowanie niestandardowe — usuwanie płotków #
Teraz możesz obserwować, jak po odświeżeniu (naciśnięciu klawisza F9) zamalowuje się różna liczba kwadracików z zależności od wylosowanego procentu.
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 Excelu masz Pasek szybkiego uruchamiania, do którego możesz dodawać i usuwać polecenia.
Usuwanie poleceń jest łatwe. Wystarczy, że klikniesz na wybrane polecenie prawym przyciskiem myszy, a następnie wybierzesz opcję z menu — Usuń z paska narzędzi szybki dostęp, które się otworzyło.
Pasek szybkiego uruchamiania — Usuń z paska narzędzi Szybki dostęp
Żeby dodać polecenie jest trudniej. Po kliknięciu prawym przyciskiem wybierasz opcję — Dostosuj narzędzi szybki dostęp. Otworzy Ci się nowe okno. Z lewej górnej listy rozwijanej najlepiej wybrać opcję — Wszystkie polecenia. Dzięki temu będziesz miał dostęp do wszystkich poleceń jakie oferuje Excel.
Teraz wystarczy wybrać polecenie i kliknąć guzik Dodaj na środku ekranu. Na filmie dodane zostały polecenia Zamknij wszystkie i Przełącz okna (pokazane przez MrExcela w podcascie 1648).
Pasek narzędzi Szybki dostęp — dodawania i usuwanie poleceń
Jedno zamyka wszystkie okna Excela i pozostawia otwarty program. Podobny efekt uzyskasz ze skrótu klawiszowego Ctrl + W, tylko on zamyka pojedyncze okno.
Polecenie Przełącz okna pozwala Ci przechodzić pomiędzy otwartymi oknami Excela, przez wybieranie ich z rozwijanej listy. Szczególnie przydatne, gdy masz wiele plików otwartych. Gdy masz tylko kilka wygodniej wykorzystać skrót klawiszowy Ctrl + Tab, który przeskakuje tylko pomiędzy oknami Excela, a nie wszystkimi oknami otwartymi w Windowsie.
Pasek szybkiego uruchamiania — przełącz okno
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.
Sumowanie po wielu kryteriach SUMA.WARUNKÓW — pytania widzów #4
Roman miał tabelę z hurtowniami i ich zarobkami uporządkowanymi według dat. Robił sumę zysków dla poszczególnym hurtowni wykorzystując funkcję SUMA.JEŻELI. Jego funkcja miała taką strukturę:
SUMA.JEŻELI(Zakres z nazwami hurtowni; Nazwa hurtowni po której chce sumować; Zakres po którym będzie sumowane po sprawdzeniu warunków)
Ta struktura przekłada się na filmie w odwołania do odpowiednich komórek.
SUMA.JEŻELI(D3:D14;I2;E3:E14)
Tabela hurtowni i funkcja SUMA.JEŻELI
Ta funkcja sprawdzała się, gdy chciał sumować po jednym warunku (nazwie hurtowni), ale gdy zaszła potrzeba do tego dołożyć jeszcze daty, ta formuła nie dawała rady. Należało wykorzystać funkcję SUMA.WARUNKÓW. Ta funkcja pozwala na ustawienie wielu warunków jednocześnie.
Struktura funkcji SUMA.WARUNKÓW pozwala na ustawienie wielu warunków, zawsze ustawianych w pary z zakresami, to znaczy, że 1 parametrem funkcji jest zakres, po którym będzie liczona suma, a następnie podajesz parę:
zakres po którym sprawdzany ma być warunek,
warunek.
Te dwa parametry zawsze muszą być podawane w parze, a takich par możesz dodać bardzo dużo.
Przykładowy zapis dla sprawdzenia nazwy hurtowni i ustawienia daty pomiędzy 2012-11-01, a 2012-10-01 wygląda tak
=SUMA.WARUNKÓW(E3:E14;D3:D14;I2;B3:B14;"2012-11-01";B3:B14;">2012–10-01")
SUMA.WARUNKÓW daty wpisane ręcznie
1 parametr (E3:E14) to zakres, po którym będzie robiona suma,2 parametr (D3:D14) to zakres, po którym będzie sprawdzany 1 warunek (nazwa hurtowni),3 parametr (I2) to nazwa hurtowni, po którym będzie sprawdzany 1 zakres,4 parametr (B3:B14) to zakres, po którym będzie sprawdzany 2 warunek (mniejsze od daty, czyli wcześniej niż data),5 parametr ("2012-11-01") to odpowiednio znak mniejszości i data w cudzysłowie, po której będzie przeszukiwany 2 zakres, 6 parametr (B3:B14) to zakres, po którym będzie sprawdzany 3 warunek (mniejsze od daty, czyli wcześniej niż data),7 parametr (">2012–10-01") to odpowiednio znak mniejszości i data w cudzysłowie, po której będzie przeszukiwany 3 zakres.
W podobny sposób możesz dodawać kolejne warunki — zawsze w parach — zakres_sprawdzania_warunku i warunek
Możesz zauważyć, że w przykładzie powyżej 3 parametr jest zapisany jako odwołanie do komórki, a 5 i 7 parametr są wpisane z cudzysłowach. Jeżeli chciałbyś, żeby były one pokazane jako odwołania do komórek powinny wyglądać odpowiednio tak: ""&J6 i ">"&J7
czyli połączenie (&) znaku mniejsze lub większe w cudzysłowie z odwołaniem do komórki
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.
Na ile sposobów możliwe jest wpisywanie danych w Excelu?
Wpisywanie danych w Excelu — Enter Ctrl Tab Shift — porada #47
Wpisywanie danych do Excela w komórki arkusza jest bardzo proste. Wybierasz komórkę, wpisujesz wartość i naciskasz Enter.
To jest tylko jedna z wielu możliwości jakie daje Ci Excel, bo jeśli naciśniesz kombinację klawiszy Ctrl + Enter to Excel zachowa wpisaną wartość, a dodatkowo dalej będzie aktywna komórką, do której ją wpisałeś. Jak naciśniesz sam Enter Excel przeskoczy do komórki poniżej.
Ta subtelna różnica może mieć znaczenie gdy chcesz od razu formatować zawartość komórki np: ją pogrubić.
Wpisywanie danych w Excelu
Warto wspomnieć, że jeśli wpisałeś już wartość do komórki i chcesz ją zastąpić inną to wystarczy, że zaczniesz ją wpisywać. Excel automatycznie nadpisze nową wartość nad starą. Nie musisz usunąć starej wartości by wprowadzić nową.
Jest jeszcze dużo możliwości zakończenia wprowadzania danych do komórki. Jeśli naciśniesz:
Tab - wylądujesz w komórce, na prawo od tej do której wpisywałeś wartość,
Shift + Tab — wylądujesz na lewo,
Shift + Enter — wylądujesz o jedną komórkę do góry.
Podobnie naciskanie strzałek da Ci podobny efekt — wpisanie danych i przesunięcie się odpowiednio w tą stronę, w którą strzałka wskazuje. Czyli np: jeśli naciśniesz strzałkę w dół wpisując wartość, to ją zaakceptujesz i przesuniesz się o 1 komórkę w dół.
Dodatkowo, co odkryłem przez przypadek, jeśli naciśniesz kombinację klawiszy:
Ctrl + Strzałka
zaakceptujesz wartość i Excel wykona to co robi skrót klawiszowy Ctrl + Strzałka, czyli przesunie się do ostatniej wypełnionej komórki w daną stronę.
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.
