Jak wstawić wartość faktury po x kolumnach – widzowie #96

Jeśli chcesz, żeby wartość faktury (przykładowo), pokazywała Ci się po x kolumnach, jeśli w komórkę wpiszesz wartość FV, to przede wszystkim musisz sobie przygotować kwoty, które mają się pokazywać oraz po ilu kolumn od wstawionej wartości FV ta wartość ma być wstawiona.

Widzowie 96 - Jak wstawić wartość faktury po x kolumnach 01

Żeby uzyskać taki efekt jak na obrazie powyżej potrzebujesz skorzystać z funkcji PRZESUNIĘCIE, która będzie przesuwała się od komórki, w której jest wpisana formuła (pierwszy argument funkcji) o zero wierszy i ustaloną ilość kolumn w lewo. Żeby przesunięcie odbywało się w lewo wartość musi być ujemna, czyli potrzebujesz dołożyć znak minus przed trzecim argumentem funkcji. Pamiętaj też o odpowiednich rodzajach odwołaniach:

=PRZESUNIĘCIE(D3;0;-$B3)

Widzowie 96 - Jak wstawić wartość faktury po x kolumnach 02

Czyli funkcja PRZESUNIĘCIE będzie patrzyła co jest ustaloną ilość kolumn w lewą stronę i potrzebujemy sprawdzić, czy jest tam wpisana wartość FV, czyli zwykły test logiczny:

=PRZESUNIĘCIE(D3;0;-$B3)=”FV”

a skoro test logiczny, to będziemy jeszcze potrzebowali funkcji JEŻELI, która wstawi kwotę z faktury jeśli znajdzie FV lub pusty ciąg znaków:

=JEŻELI(PRZESUNIĘCIE(D3;0;-$B3)=”FV”;$A3;””)

Widzowie 96 - Jak wstawić wartość faktury po x kolumnach 03

Teraz wystarczy, że przeciągniesz formułę na cały zakres, gdzie chcesz mieć sprawdzane dane i uzyskasz efekt jak na pierwszym rysunku (plus dołożone jest jeszcze formatowanie warunkowe w pliku, na którym pracujemy).

Ta formuła jest prosta, ale niestety ma pewne wady:

Pierwszą jest to, że jeśli wpiszesz w komórkę wartość to nadpiszesz formułę, więc mogą się pojawiać problemy, gdy będziesz chciał później zmieniać pozycję tekstu „FV” w wierszu.

Drugą jest przesunięcie w lewo, bo możesz się przesunąć w lewo poza obszar arkusza i wtedy pojawi się błąd adresu:

Widzowie 96 - Jak wstawić wartość faktury po x kolumnach 04

Na szczęście problem łatwo można rozwiązać dodając wcześniej odpowiednią ilość kolumn.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak stworzyć wykres liniowy i dodać mu tytuł – porada #275

Jeśli potrzebujesz stworzyć wykres liniowy w Excelu, to przede wszystkim potrzebujesz przygotować dane. Przykładowo jeśli chcesz przedstawić na wykresie dane sprzedażowe dwóch pracowników przez okres roku (po miesiącach). To wystarczy, że w pierwszej kolumnie wpiszesz poszczególne miesiące, a w dwóch pozostałych wartości sprzedaży. Warto też zadbać o nagłówki danych:

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 01

Teraz potrzebujesz zaznaczyć całość danych i z karty Wstawianie wybrać odpowiedni rodzaj wykresu (załóżmy, że chcemy wstawić wykres liniowy ze znacznikami). Ponieważ od Excel 2013 zaszło trochę zmian jeśli chodzi o operacje z wykresami pod spodem zobaczysz obrazy dla Excela 2013 i 2010.

Excel 2013 i nowszy:

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 02

Excel 2010 i wcześniejszy:

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 03

W ten prosty sposób wstawiłeś wykres liniowy ze znacznikami. Jeśli chciałbyś go zmienić na inny, to gdy jest zaznaczony wystarczy, że wybierzesz inny z dostępnych na karcie Wstawianie wykresów.

W Excelu 2013 lub wyższym powinieneś już mieć na wykresie jego tytuł, ale jeśli by Ci się nie pojawił to musisz rozwinąć zielony plus, który znajduje się obok wykresu (tam znajdują się elementy wykresu, które wcześniej były na karcie Układ).

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 04

Przed Excelem 2013 będziesz musiał dodać tytuł z karty Układ:

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 05

Zmiana tytułu już przebiega analogicznie – wystarczy, że zaznaczysz tytuł wykresu i zaczniesz pisać tekst jaki chcesz, żeby się pojawił jako tytuł wykresu. Zmiany nie zobaczysz od razu na wykresie. To co wpisujesz będziesz widział w pasku formuły, a na wykresie pojawi się dopiero po zatwierdzeniu Enterem.

Porada 275 - Jak stworzyć wykres liniowy i dodać mu tytuł 06

Jeśli chcesz zmienić tylko fragment tytułu to wystarczy, że po tym jak zaznaczysz tytuł wykresu klikniesz w niego jeszcze raz. Będziesz wtedy mógł edytować tekst podobnie jak zawartość komórki.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak wyszukiwać uwzględniając wielkość liter różne funkcje – widzowie #95

 

Tym razem do wyszukiwania z uwzględnieniem wielkości liter wykorzystamy inne funkcje niż WYSZUKAJ.PIONOWO. Rozpatrzymy dwa rozwiązania. Oba będą wykorzystały wynik funkcji PORÓWNAJ:

=PORÓWNAJ(E2;$A$2:$A$11)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 01

Tak jak w poprzednich wpisach wynikiem będzie tablica wartości logicznych PRAWDA i FAŁSZ. PRAWDA, będzie w tych komórkach, gdzie ciągi tekstowe były identyczne w pozostałych będzie FAŁSZ.

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 02

Pierwsza formuła będzie jeszcze korzystać z funkcji PODAJ.POZYCJĘ oraz INDEKS. Za pomocą funkcji PODAJ.POZYCJĘ odnajdziesz wartość PRAWDA w wynikach funkcji PORÓWNAJ:

=PODAJ.POZYCJĘ(PRAWDA;PORÓWNAJ(F2;$A$2:$A$11);0)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 03

A jak masz już pozycję prawdy, to wystarczy skorzystać z funkcji INDEKS, gdzie jako pierwszy argument wstawisz zakres kolumny, z której chcesz wyciągnąć wartość:

=INDEKS($C$2:$C$11;PODAJ.POZYCJĘ(PRAWDA;PORÓWNAJ(F2;$A$2:$A$11);0))

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 04

Pamiętaj tylko zatwierdzić formułę jako formułę tablicową – kombinacją klawiszy Ctrl + Shift + Enter.

Drugie rozwiązanie będzie wykorzystywało funkcję WYSZUKAJ. Tylko standardowo musisz odpowiednio przygotować dane, żeby z niej skorzystać. Tym razem wynik funkcji PORÓWNAJ posłuży jako dzielnik. Wystarczy, że wartość 1 podzielisz przez wynik funkcji PORÓWNAJ:

=1/PORÓWNAJ(F2;$A$2:$A$11)

Ponieważ w Excelu jeśli wykonasz dowolną operację matematyczną na wartościach logicznych PRAWDA lub FAŁSZ, to Excel zamieni je odpowiednio na liczby 1 i 0. To wynikiem takiego dzielenia, będzie tablica błędów dzielenia przez zero i liczby 1 (dla PRAWDA):

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 05

Teraz już możesz obudować naszą tablicę z wynikami funkcją WYSZUKAJ. Będziesz szukał liczby 2, ponieważ funkcja WYSZUKAJ zawsze działa na zasadzie dopasowania przybliżonego, dlatego jeśli karzesz jej szukać 2 w tablicy 1 i błędów dzielenia przez zero (#DZIEL/0!), to Excel znajdzie ostatnią liczbę 1. (Odwrotnie jak w pierwszej formule, która znajduje pierwsze wystąpienie szukanej wartości).

Potrzeba jeszcze tylko podać argument wektor_wynikowy, czyli analogicznie jak w funkcji INDEKS kolumnę, z której chcesz wyciągnąć wartość:

=WYSZUKAJ(2;1/PORÓWNAJ(F2;$A$2:$A$11);$C$2:$C$11)

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 06

To rozwiązanie fajne jest dlatego, gdyż nie wymaga zatwierdzania specjalna kombinacją klawiszy.

Odpowiednio wyniki dla dwóch zaprezentowanych formuł będą się różnić ze względu na ilość występujących tekstów i ich pozycje:

Widzowie 95 - Jak wyszukiwać z uwzględnieniem wielkości liter różne funkcje 07
Pozdrawiam
Adam Kopeć
Miłośnik Excela

Excel – Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ PIONOWO prostsze – Widzowie #94

Jeśli zależy Ci żeby wyszukiwać uwzględniając wielkość liter to musisz przygotować odpowiednią formułę. Dzięki komentarzowi Patryka Murowskiego pod wideo „Widzowie 93” poznałem prostszą formułę niż tą, którą znałem. Wykorzystuje m.in. funkcję WYSZUKAJ.PIONOWO. Będziemy działać na przykładowych danych:

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 01

Nasz obliczenia zaczniemy od funkcji PORÓWNAJ, która porównuje dwa ciągi tekstowe (uwzględniając wielkość liter) i w zależności od wyniku porównania zwraca wartość logiczną PRAWDA albo FAŁSZ. W naszym przykładzie potrzebujemy, jako jeden z argumentów funkcji PORÓWNAJ podać tekst, którego szukamy, a jako drugi odwołanie do kolumny, którą będziemy przeszukiwać:

=PORÓWNAJ(F2;$A$2:$A$11)

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 02

Przy takich danych funkcja PORÓWNAJ zwróci tablicę wartości logicznych PRAWDA i FAŁSZ. PRAWDA, będzie w tych komórkach, gdzie ciągi tekstowe były identyczne w pozostałych będzie FAŁSZ.

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 03

Teraz na podstawie wyników funkcji PORÓWNAJ stworzymy tablicę, po której będzie szukała funkcja WYSZUKAJ.PIONOWO. Wystarczy, że skorzystasz z funkcji JEŻELI, dla której tablica wartości logicznych PRAWDA i FAŁSZ będą argumentem test_logiczny. Gdy funkcja PORÓWNAJ będzie zwracała PRAWDĘ będziemy chcieli, żeby pokazywał się wiersz z danymi. To bardzo łatwo da się uzyskać, bo wystarczy podać zakres danych jako argument wartość_jeżeli_prawda. Natomiast argument wartość_jeżeli_fałsz możesz całkowicie pominąć, ponieważ domyślnie będzie zamiast niego zwracana wartość FAŁSZ.

=JEŻELI(PORÓWNAJ(F2;$A$2:$A$11);$A$2:$C$11)

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 04

Jeżeli podejrzysz wynik funkcji JEŻELI klawiszem F9, to zobaczysz, że zwracany jest wiersz danych tylko jeśli został znaleziony identyczny tekst. W innych sytuacjach są zwracane wiersze wartości FAŁSZ.

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 05

To pasuje idealnie do obliczeń, bo wystarczy, że wstawisz formułę do funkcji WYSZUKAJ.PIONOWO, która będzie szukała danego słowa, ponieważ tylko ona, lub wartość FAŁSZ jest w pierwszej kolumnie przeszukiwanej tablicy danych. Wystarczy jeszcze dopisać, że chcesz wyciągnąć wartość z 3 kolumny i dokonywać przeszukiwania na zasadzie dokładnej (czwarty argument = 0).

=WYSZUKAJ.PIONOWO(F2;JEŻELI(PORÓWNAJ(F2;$A$2:$A$11);$A$2:$C$11);3;0)

Widzowie 94 - Jak wyszukiwać z uwzględnieniem wielkości liter WYSZUKAJ.PIONOWO prostsze 06

Pamiętaj tylko zatwierdzić formułę jako formułę tablicową – kombinacją klawiszy Ctrl + Shift + Enter.
Pozdrawiam
Adam Kopeć
Miłośnik Excela

Excel – Jak sprawić by WYSZUKAJ PIONOWO zwracała uwagę na wielkość liter – widzowie #93

Funkcja WYSZUKAJ.PIONOWO nie zwraca uwagi, na wielkość znaków, więc jeśli Ci zależy, żeby Excel rozróżniał przy wyszukiwaniu wielkość liter, to musisz zbudować odpowiednią do tego formułę. Będziemy działać na przykładowych danych:

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 01

Nasz obliczenia zaczniemy od funkcji PORÓWNAJ, która porównuje dwa ciągi tekstowe (uwzględniając wielkość liter) i w zależności od wyniku porównania zwraca wartość logiczną PRAWDA albo FAŁSZ. W naszym przykładzie potrzebujemy, jako jeden z argumentów funkcji PORÓWNAJ podać tekst, którego szukamy, a jako drugi odwołanie do kolumny, którą będziemy przeszukiwać:

=PORÓWNAJ(E2;$A$2:$A$11)

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 02
Przy takich danych funkcja PORÓWNAJ zwróci tablicę wartości logicznych PRAWDA i FAŁSZ. PRAWDA, będzie w tych komórkach, gdzie ciągi tekstowe były identyczne w pozostałych będzie FAŁSZ.

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 03
Teraz potrzebujemy na tej podstawie powyższej tablicy stworzyć element, którego będziemy szukać. Relatywnym prostym rozwiązaniem będzie przemnożenie jej przez tablicę z numerami wierszy danych. Wystarczy, że wykorzystasz do tego funkcję WIERSZ z podanym zakresem kolumny, którą będziesz przeszukiwał. Jej wynik musimy przemnożyć przez tablicę zwracaną przez funkcję PORÓWNAJ:

=PORÓWNAJ(E2;$A$2:$A$11)*WIERSZ($A$2:$A$11)

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 04
Wynikiem iloczynu, będzie tablica z zerami oraz numerami wierszy, tylko tam, gdzie Excel znalazł identyczne ciągi tekstowe.

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 05
Nas będzie interesowała z tej tablicy maksymalna wartość, dlatego wystarczy nasze dotychczasowe obliczenia wstawić do funkcji MAX:

=MAX(PORÓWNAJ(E2;$A$2:$A$11)*WIERSZ($A$2:$A$11))

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 06
Uff. Udało się nam wyznaczyć wartość, której będziemy szukać za pomocą funkcji WYSZUKAJ.PIONOWO. Teraz potrzebujesz zbudować tablicę, którą funkcja będzie przeszukiwać. Do tego przyda Ci się funkcja WYBIERZ, która domyślnie zwraca wybrany argument na podstawie kolejności, czyli jeśli jako pierwszy argument wpiszesz 1 to funkcja WYBIERZ zwróci kolejny pierwszy argument, jeśli wpiszesz 2 to zwróci kolejny drugi argument itd. My jedna potrzebujemy, żeby funkcja WYBIERZ zwracała na raz dwa argument. Żeby uzyskać taki efekt musisz wpisać pierwszy argument jako tablicę danych: {1\2}. Dla takiego argumentu funkcja WYBIERZ zwróci zarówno pierwszy jak i drugi wybierany argument.
Na szczęście funkcja WYBIERZ nie ma problemu ze zwracaniem zakresów danych, więc nasz pierwszy argument to będą numery wierszy przeszukiwanej kolumny (WIERSZ($A$2:$A$11)), a drugi argument to będzie kolumna, z której chcemy wyciągnąć informację ($B$2:$B$11). Ponieważ funkcja WYSZUKAJ.PIONOWO zwraca z wartość z pojedynczej kolumny, to nawet jeśli Twoje dane źródłowe mają więcej kolumn to wystarczy, że podasz funkcji WYBIERZ tylko dwie kolumny, tą z numerami wierszy i kolumnę, z której chcesz pobrać wartość. Cała funkcja będzie wyglądała tak:

WYBIERZ({1\2};WIERSZ($A$2:$A$11);$B$2:$B$11)

Jeśli podejrzysz sobie jej wynik, to zobaczysz w jaki sposób Excel wizualizuje zapis jako tablica danych

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 07
Najtrudniejsze już za nami. Wystarczy, jeszcze podać dwa ostatnie argumenty funkcji WYSZUKAJ.PIONOWO – to wartość 2, bo szukamy wartości z drugiej kolumny i wartość 0, ponieważ chcemy wykonywać wyszukiwanie na zasadzie dopasowania dokładnego. Czyli cała formuła będzie miała taką strukturę:

=WYSZUKAJ.PIONOWO(MAX(PORÓWNAJ(E2;$A$2:$A$11)*WIERSZ($A$2:$A$11));WYBIERZ({1\2};WIERSZ($A$2:$A$11);$B$2:$B$11);2;0)

Widzowie 93 - Jak sprawić by WYSZUKAJ.PIONOWO zwracał uwagę na wielkość liter 08
Pamiętaj, że jest to formuła tablicowa, ponieważ podajemy funkcją Excela tablice, w argumentach, gdzie spodziewa się pojedynczej wartości, dlatego tą formułę musisz zatwierdzać kombinacją klawiszy Ctrl + Shift + Enter.

Pozdrawiam
Adam Kopeć
Miłośnik Excela