Formuł ciąg dalszy.

Cześć! Dziś kolejna część o formułach – tym razem bardziej zaawansowane tematy, ale niezwykle potrzebne. Odpowiemy na pytania z ostatniej lekcji. Nauczymy się jak za jednym zamachem tworzyć i przeliczać setki, a nawet tysiące formuł. Zapraszam!

Bardzo często zdarza się, że chcemy przeliczyć formuły dla jakiś większych zbiorów danych, na przykład dla całej kolumny. Takim prostym przykładem może być przemnożenie jednej kolumny przez drugą kolumnę. Na przykład kiedy chcemy przeliczyć zysk ze sprzedanych produktów, gdzie w jednej kolumnie mamy koszt jednostkowy, a w drugiej liczbę sprzedanych sztuk. Wtedy wystarczy napisać formułę, która mnoży wartość z jednej kolumny razy wartość z drugiej.

Mnożenie wartości z różnych kolumn

Smutnie by było, gdybyśmy musieli ręcznie wpisywać wszystkie formuły, aż do ostatniego wiersza w danej kolumnie. A jeśli tych wartości będzie kilka tysięcy? Na szczęście jest sposób, aby wszystkie formuły przeliczyć według wzoru w pierwszej napisanej formule. Wystarczy, że przeciągniesz w dół, chwytając lewym przyciskiem myszy za kwadracik w prawym dolnym rogu komórki. Komórki poniżej wypełnią się wartościami. Do każdej komórki w formule od której zaczynamy będzie dodana taka liczba wierszy, o ile wierszy niżej ta komórka się znajduje (lub odjęta jeśli przeciągamy formułę w górę). Dla przykładu, jeżeli w komórce w drugim wierszu mamy formułę:

Formuła w drugim wierszu

To w trzeciej (jedna poniżej) będziemy mieć:

Formuła w trzecim wierszu

A w ósmej (dodajemy do pierwszej formuły 8 (wiersz obecnej komórki) -2 (wiersz pierwszej komórki) = 6):

Formuła w ósmym wierszu

Jeżeli masz do czynienia z danymi w kolumnie, która nie ma pustych wartości, a zaraz po jej prawej stronie wpisujesz formułę, którą chcesz wypełnić całą kolumnę to możesz to zrobić jeszcze szybciej. Wystarczy, że wpiszesz formułę w pierwszą komórkę, a następnie dwukrotnie klikniesz mały kwadracik w prawym dolnym rogu komórki. Wtedy cała kolumna wypełni się formułami, a w konsekwencji ich wynikiem. Pamiętaj, że ta metoda zadziała tylko, gdy po lewej stronie masz kolumnę z wartościami.

Czerwony obszar nie wypełni się formułami po dwukrotnym naciśnięciu kwadracika w prawym dolnym rogu

Natomiast to co powyżej zakłada, że w każdych obliczeniach będziemy chcieli skorzystać z wartości w kolejnych komórkach. Czyli, że wszystkie potrzebne do obliczeń wartości, będą się znajdować w takiej samej relacji odniesienia jak w pierwszej komórce. Nie zawsze tak jest. Na przykład kiedy mamy jedną wartość w konkretnej komórce, z której chcemy skorzystać w każdej kolejnej formule. Oczywiście moglibyśmy stworzyć nową kolumnę i wypełnić ją takimi samymi wartościami, ale to zbędne zajmowanie miejsca i komplikowanie arkusza.

Mamy na szczęście możliwość blokowania danej komórki w formule. Służą do tego znaki dolara ($) umieszczane przed literą oznaczającą kolumnę i liczbą określającą wiersz. Jeśli znak dolara umieścisz zarówno przed literą jak i przed liczbą, to ta komórka będzie zablokowana zarówno przy przeciąganiu formuł w orientacji pionowej, jak i w orientacji poziomej.

Zablokowanie komórki w obliczeniach
Przeciąganie formuł w pionie
Przeciąganie formuł w poziomie

Aby połączyć wartości z dwóch lub więcej komórek w ciąg tekstowy należy użyć w formule znaku konkatenacji czyli łączenia. Ten znak & – widnieje na klawiaturze standardowo na klawiszu z numerem 7.

Formuła konkatenacji (łączenia) wartości w komórkach
Rezultat powyższej formuły

Nie musimy do formuły z konkatenacją wrzucać tylko odnośników do komórek. Możemy także dopisać jakikolwiek tekst. Należy jednak pamiętać, że każdy tekst, który chcemy dodać, musi być połączony znakiem łącznia &, oraz jeśli z nim coś chcemy połączyć to też używamy tego znaku. Ponadto tekst wpisany bezpośrednio zawsze otaczamy znakami cudzysłowu.

Dodanie ciągu znajów – konstrukcja
Efekt powyższej formuły

Tak jak wspomniałem w filmie, są różne metody separacji liczby całkowitej od dziesiętnej. W Polsce przyjęło się, że rozdzielamy takie liczby przecinkiem, natomiast w wielu krajach używa się kropki. Weź to pod uwagę, przy kolejnej analizie jakiegoś pliku Excelowego.

Jeśli chcesz zmienić separator w ustawieniach to wystarczy, że wejdziesz według schematu: File -> Options ->Advanced. Tam znajdziesz opcję zmiany separatora dziesiętnego:

Zmiana separatora dziesiętnego

Jeśli natomiast dostałeś od kogoś plik excelowy, który ma inny separator dziesiętny, możesz jednorazowo podmienić go na inny jednorazowo dla wszystkich komórek jednocześnie. O tym jak to zrobić, będę mówił w kolejnych materiałach. Już teraz zapraszam!

Jeśli jeszcze nie oglądałeś praktycznej części dotyczącej tej lekcji to zapraszam: