Pobieranie wartości z komórki oddalonej o wybraną ilość kolumn — porada #46

Jak w Excelu cofać się o zmienną ilość kolumn?


Pobieranie wartości z komórki oddalonej o wybraną ilość kolumn — porada #46

Miałem potrzebę pobierać wartość z wcześniejszych kolumn. Tylko ilość kolumn o które się przesuwałem była zmienna. Załóżmy, że jednego dnia chciałem, żeby była pobierana wartość z kolumny sąsiadującej, drugiego z 2 kolumny po lewej, a trzeciego z 4 kolumny na prawo, albo nawet więcej.

Dane te były wprowadzana, jako część składników do tabeli, która rozrastała się w prawą stronę. Czyli dodatkowo chciałem stworzyć taką formułę, którą będzie można kopiować i będzie dalej działała.

Funkcja INDEKS

Najprostszy sposób jaki znalazłem wykorzystywał funkcję INDEKS.

Funkcja INDEKS zwraca wybraną pozycję (wartość komórki) z zaznaczonego obszaru (wprowadzanego jako pierwszą zmienną do funkcji).

W filmie podany jest przykład:

INDEKS($B$4:$E$7;2;3)

Excel dla takich parametrów zwróci wartość przechowywaną w 2 wierszu i 3 kolumnie wybranego obszaru ($B$4:$E$7), czyli wartość komórki D5.

Co wybiera funkcja INDEKS

Co wybiera funkcja INDEKS

Przesuwanie o wybraną ilość kolumn

Jak to wykorzystać do pobierania wartości z komórek przesuniętych o zmienną ilość kolumn? Przede wszystkim potrzebowałem zaznaczyć odpowiedni obszar. Ten wprowadzony jako pierwszy miał jedną wadę, nie zmieniał się wraz z przechodzeniem na prawo w arkuszu Excela. dlatego usunąłem znak $ przy E, czyli przykładowy obszar wyglądał tak $B$4:E$7, dzięki temu zawsze zaczynałem od 1 komórki ustawionej bezwzględnie, a przesuwałem kolumny tabeli (wierszy nie) wraz z przesuwaniem się na prawo.

Następnie trzeba było dopasować odpowiedni wiersz. Ponieważ pierwszym wierszem dla wybranego obszaru jest wiersz w arkuszu oznaczony numerem 4 trzeba było to odpowiednio obliczyć. Zastosowałem funkcję WIERSZ. Zwraca ona numer wiersza komórki, do której się odwołujesz. Odwoływałem się względnie do komórki, w której była zapisywana formuła. Następnie trzeba było odjąć ilość wierszy w arkuszu nad zaznaczonym obszarem. Dlatego odwoływałem się do komórki o 1 wiersz wyżej nad górną granicą obszaru. Na przykładzie wyglądało to tak:

WIERSZ(F4)-WIERSZ($F$3)

pierwszy wiersz się zmienia wraz z budowaniem tabeli w dół i prawo, a wiersz nad tabelą ma się nie przesuwać, żeby zawsze była odejmowana jednakowa wartość.

Teraz został element przesuwania się o wybraną ilość kolumn. Nie można w funkcji INDEKS wpisać po prostu ilości kolumn, o które chce się cofnąć, ponieważ funkcja INDEKS zwraca konkretny numer kolumny od początku, czyli trzeba było odjąć tą wartość od całej szerokości obszaru. Uzyskałem ją dzięki funkcji LICZBA.KOLUMN. Jednak po odjęciu okazało się, że formuła zwraca mi wartość z kolumny o 1 wcześniej niż chciałem. Mogłem to rozwiązać na 2 sposoby, albo uwzględnić w obszarze kolumnę komórki, w której wpisuję formułę, albo dodać 1. Wolałem mieć mniej składników sumy.

Ewentualnie jeśli chcesz skrócić długość ten formuły, możesz powiększyć obszar, żeby zaczynał się od 1 wiersza arkusza, wtedy nie trzeba odejmować numeru wiersza tuż nad obszarem. Wtedy formuła wygląda tak:

INDEKS($B$1:F$7;WIERSZ(F4);LICZBA.KOLUMN($B$1:F$7)-$F10)

Końcowa wersja formuły odwołująca się do różnych kolumn

Końcowa wersja formuły odwołująca się do różnych kolumn

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 — Pobieranie wartości z komórki oddalonej o wybraną ilość kolumn — porada #46

Wybieranie losowo osoby z listy INDEKS() — porada #45

Jak wygląda wybieranie losowo osoby z listy?


Wybieranie losowo osoby z listy INDEKS() — porada #45

Jak proste jest wybieranie losowo osoby z listy w Excelu? Wystarczy przygotować listę i wykorzystać funkcję INDEKS. Przy okazji przyda się jeszcze funkcja losująca. Tą którą chcesz wykorzystać w tej sytuacji jest LOS.ZAKR

Listę najlepiej przygotować w formie jednej kolumny i wpisać w niej wszystkie osoby, z których chcesz losować. Kiedy już masz taką kolumnę osób korzystasz z funkcji INDEKS, która zwraca Ci wybraną pozycję z zaznaczonego obszaru.

Lista osób

Lista osób

Twoim obszarem w tej sytuacji jest Twoja lista. To będzie pierwszy parametr funkcji — odwołanie do całej listy.

Ponieważ przygotowałeś listę w 1 kolumnie wystarczy jeszcze jeden parametr, a mianowicie numer wiersza listy, z którego chcesz pobrać wartość. Jeśli wpisałbyś jako drugi parametr liczbę 2 to Excel zawsze zwracałby Ci pozycję, która jest druga na liście, a ponieważ Ty chcesz mieć osobę wybraną losowo korzystasz w tym miejscu z funkcji LOS.ZAKR

Zakładając, że Twoja lista ma 10 pozycji funkcja losująca mogłaby wyglądać w następujący sposób:

LOS.ZAKR(1;10)

a cała formuła

INDEKS(E2:E11;LOS.ZAKR(1;10))

Funkcja INDEKS - wybieranie losowo

Funkcja INDEKS — wybieranie losowo

Już masz wybieranie losowe osoby z listy. Prawda, że proste.

Wybieranie z listy dowolnej wartości

A co kiedy nie znasz ilości pozycji na liście, albo nie chce Ci się liczyć, spokojnie, Excel został stworzony po to, żeby ułatwić Ci pracę. I może za Ciebie policzyć ilość wierszy — funkcja ILE.WIERSZY, do której podajesz jeden parametr jakim jest odwołanie do całej listy. Po zmodyfikowaniu cała formuła będzie wyglądać tak:

INDEKS(E2:E11;LOS.ZAKR(1; ILE.WIERSZY(E2:E11)))

Funkcja INDEKS z policzoną liczbą wierszy ILE.WIERSZY

Funkcja INDEKS z policzoną liczbą wierszy ILE.WIERSZY

Co gdy chcesz wybierać z listy nie osobę, ale różne wartości? Np: daty, liczby, godziny i tekst? Funkcja INDEKS to umożliwia, nawet jeśli masz mieszane wartości na liście, ale pamiętaj, że Excel nie będzie zmieniał formatowania komórki, w której wpisałeś całą formułę, dlatego np: daty przy formatowaniu Ogólnym mogą wyglądać inaczej niż byś tego oczekiwał.

Wybrana wartość data, a formatowanie ogólne

Wybrana wartość data, a formatowanie ogólne

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 — Wybieranie losowo osoby z listy INDEKS() — porada #45

Drukowanie wielkich tabel — nagłówek na każdej stronie — porada #44

Jak drukować wielkie tabele w Excelu?


Drukowanie wielkich tabel — nagłówek na każdej stronie — porada #44

W Excelu masz możliwość tworzenia tabel. Możesz wpisywać bardzo dużo danych i nadać im nagłówki na górze tabeli, ale co się dzieje kiedy Twoja tabela się rozrasta — już nie mieści się na 1 stronie wydruku, potrzebuje kilku, a nawet kilkunastu stron? Jak sprawić żebyś widział nagłówki na każdej stronie?

Przede wszystkim dobrze najpierw zobaczyć jak będzie wyglądać wydruk — wystarczy, że wykorzystać skrót klawiszowy:
Ctrl + P.

Podgląd wydruku wielkiej tabeli

Podgląd wydruku wielkiej tabeli

Zobaczysz nie tylko jak podgląd wydruku, ale od razu będziesz też mógł zmienić podstawowe opcje drukowania.

Na filmie zobaczysz przykład, który pokazuje próbę wydrukowania wielkiej tabeli. Nie mieści się ona na 1 stronie, ani na szerokość, ani na wysokość. Sytuację poprawia ustawienie orientacji drukowania na poziomom, ale dalej nie widać nagłówków tabeli na kolejnych stronach, na których nam zależy i zostają jeszcze inne niedociągnięcia.

Żeby wydrukować poprawnie wielką tabelę chcemy wejść w ustawienia strony i w polskiej wersji Excela natknąłem się na pewien błąd. Wykorzystując klawisz Alt i skróty do poszczególnych zakładek wstążki. Po naciśnięciu klawisza Alt naciskasz naciskasz A, by wejść na zakładkę Układ strony, teraz powinieneś nacisnąć S, a potem Y, żeby otworzyć ustawienia strony, niestety jak naciskałem S włączała mi się funkcja, która powinna być odpalana literą Ś (Okienko zaznacz), na szybko sobie poradziłem usuwając ją z menu. Wtedy już naciskanie po kolei:

Alt A S Y

otworzyło mi ustawienia strony.

Ustawienia strony

Ustawienia strony

Ale zanim wejdziesz w ustawienia strony, ważne jest żeby zaznaczyć obszar wydruku, żebyś dał znać Excelowi co chcesz drukować, a co ma być widoczne tylko w arkuszu, a nie brane pod uwagę przy drukowaniu.

Na wideo możesz zobaczyć, że skrót klawiszowy

Ctrl + Shift + 8

pozwala Ci zaznaczyć od razu całą tabelę nie zaznacza on małych okien informacyjnych nad tabelą, a które też chcę wydrukować.

Żeby zaznaczyć cały interesujący mnie obszar wydruku stosuję sztuczkę. Najpierw zaznaczam wiersz na samej górze arkusza na szerokość tabeli następnie

Ctrl + Shift + Strzałka w dół

2 razy. Mam już zaznaczony obszar teraz wystarczy, że wybiorę opcję Obszar wydruku.

Teraz mogę już wykorzystać ustawienia strony. Z zakładki Strona wybieram opcję
Wpasuj w strony, przy literze H (czyli horyzontalnie — poziomo) wpisuję 1, sprawi to, że Excel ściśnie cały obszar wydruku w poziomie tak, żeby zmieścił się na 1 stronie. Przy literze V (wertykalnie — pionowo) zostawiam puste pole. To puste pole zapewnia mi, że Excel będzie drukował strony, aż do momentu kiedy skończy mu się obszar wydruku.

Wpasuj strony

Wpasuj strony

Teraz dodamy sobie nagłówki na każdej stronie. Przechodzimy na zakładkę Arkusz i na niej wybieramy opcję U góry powtarzaj wiersze. Wystarczy zaznaczyć nagłówek. Zwróć uwagę, że Excel i tak zaznaczył całą linie, ale nie musisz się przejmować, bo będzie powtarzał tylko fragment z obszaru wydruku.

U góry powtarzaj wiersze

U góry powtarzaj wiersze

Teraz możesz nacisnąć Ctrl + P i zobaczyć efekty zmian. Cała tabela mieści się na szerokość na 1 stronie w układzie wydruku pionowym oraz na kolejnych stronach powtarza się nagłówek.

Nagłówek na 3 stronie drukowania

Nagłówek na 3 stronie drukowania

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 — Drukowanie wielkich tabel — nagłówek na każdej stronie — porada #44

Obszar wydruku i drukowanie różnych obszarów — porada #43

Drukowanie różnych obszarów na różnych stronach


Obszar wydruku i drukowanie różnych obszarów — porada #43

Podgląd wydruku

W którymś momencie zapragniesz wydrukować to co stworzyłeś w Excelu. Skrót klawiszowy Ctrl + P umożliwia Ci podgląd wydruku. Od razu też możesz dokonać podstawowych zmian. Co jeśli te podstawowe zmiany są nie wystarczające? Wtedy trzeba wejść trochę głębiej.

Podgląd wydruku

Podgląd wydruku

Obszar wydruku

Możesz np: sam zaznaczyć obszar wydruku, czyli powiedzieć, żeby drukował tylko to co znajduje się na wybranym przez Ciebie obszarze, a wszystko inne omijał. Dzięki temu możesz w arkuszu przechowywać notatki na temat swojej pracy, a jednocześnie będziesz miał czyste przedstawienie tego, co jest istotne i wymaga drukowania.

Obszar wydruku

Obszar wydruku

Kiedy zaznaczysz obszar wydruku i będziesz chciał drukować, Excel podpowie Ci, gdzie są granice obszarów drukowania, rysując odpowiednie obramowania.

Zauważyłeś liczbę mnogą przy obszarach? Tak, możesz wybrać kilka obszarów wydruku. Nawet nie sąsiadujących ze sobą. Żeby to zrobić przede wszystkim zwróć uwagę na kolejność, w której zaznaczasz obszary, bo te, które zaznaczysz jako pierwsze będą na pierwszych stronach, a te obszary, które zaznaczysz na końcu, będą na ostatnich stronach. Dzięki temu możesz wydrukować jako pierwsze obszar który jest niżej, a ten, który jest wyżej jako późniejszy (patrz wideo).

Obszary najłatwiej zaznaczyć myszką i dodatkowo trzymać klawisz Ctrl.

Możesz jeszcze zerknąć na zakładkę Widok -> Podgląd podziału strony. Zobaczysz tam obszary wydruku otoczone niebieską krawędzią wraz z przypisanymi im numerami stron, na których będą wydrukowane oraz cały pozostały obszar zaznaczony na szaro, który nie będzie drukowany.

Podgląd podziału stron

Podgląd podziału stron

Możesz tutaj od razu modyfikować obszary wydruku odpowiednio przeciągając ich krawędzie (niebieskie linie).

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 — Obszar wydruku i drukowanie różnych obszarów — porada #43

Przeskakiwanie między oknami Excela Ctrl + Tab — porada #42

Jak przeskakiwać między oknami Excela?


Przeskakiwanie tylko między oknami Excela Ctrl + Tab — porada #42

Przeskakiwanie między oknami windowsa

Pewnie miałeś pracowałeś na Windowsie i wiesz, że istnieje w nim skrót do przeskakiwania pomiędzy oknami różnych otwartych aplikacji. Ten skrót klawiszowy to:

Alt + Tab.

Przeskakiwanie między oknami

Przeskakiwanie między oknami

Przeskakiwanie między oknami Excela

Trochę utrudnione może być przeskakiwanie w ten sposób pomiędzy okna, gdy masz ich otworzonych dużo. Załóżmy, że pracujesz w Excelu i chcesz przeskoczyć do innego otwartego pliku, ale nie chcesz przeskakiwać po drodze tych wszystkich okien, które nie są związane z Excelem. Wystarczy wykorzystać specjalny skrót klawiszowy:

Ctrl + Tab

Dzięki niemu będziesz przeskakiwał tylko pomiędzy aktywnymi oknami Excela. Pomoże Ci to łatwiej dotrzeć do pliku, który aktualnie potrzebujesz.

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 — Przeskakiwanie tylko między oknami Excela Ctrl + Tab — porada #42