Excel – Zamiana „angielskich” liczb na „polskie” za pomocą Tekst jako kolumny – Porada #292

W tym tygodniu na szkoleniu, które prowadziłem, został poruszony również wątek zamiany angielskiego zapisu liczb na polski za pomocą polecenia Tekst jako kolumny.
Zacznijmy od tego, ze mamy liczby, gdzie separatorem tysięcy jest przecinek, a część całkowitą od ułamkowej liczby oddziela kropka czasem też się trawi minus na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 01

Liczby te możemy łatwo zamienić na polskie (czy też takiej jakie wynikają z Twoich ustawień regionalnych) za pomocą polecenia Tekst jako kolumny, które znajduje się na karcie Dane. Musimy tylko zaznaczyć kolumnę z liczbami, które chcemy zamienić.
Przez pierwsze dwa kroki przechodzimy szybko upewniając się tylko, że nie jest zaznaczony żaden ogranicznik, który spowodowałby podział liczby na osobne kolumny. Musimy się na chwilę zatrzymać w kroku 3 i kliknąć przycisk Zaawansowane.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 02

W oknie, które się otworzy musimy wybrać takie separatory jakie są w liczbach, które chcemy zmienić, a Excel zamieni je na takie, które wynikają z ustawień regionalnych. Możemy też zaznaczyć checkbox, że znak minus znajduje się na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 03

Po tym wystarczy zatwierdzić opcje i wkleić liczby tam, gdzie chcesz np.: w kolumnę obok, żeby było widać wcześniej „angielski” zapis i aktualny „polski” zapis liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 04

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Sumowanie całego wiersza na podstawie pojedynczego kryterium – Porada #291

Dzisiaj chcemy zsumować wartości z całego wiersza na podstawie pojedynczego kryterium. Czyli przykładowo jeśli wybierzemy Tadeusza to chcemy zsumować 3 wartości (cały wiersz).

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 01

Problem jest taki, że SUMA.JEŻELI, z której najczęściej korzystamy, gdy chcemy zsumować wartości po warunku nie zadziała tu poprawnie:

=SUMA.JEŻELI(A3:A9;G1;B3:D9)

Wynika to ze sposobu działania funkcji SUMA.JEŻELI – na 1 komórkę, która spełnia warunek funkcja SUMA.JEŻELI zsumuje 1 komórkę z zakresu podanego do sumowania. Dlatego tutaj musimy skorzystać z innej formuły.
Odpowiednią możliwość daje nam SUMA.ILOCZYNÓW z odpowiednimi obliczeniami w argumencie.

Zaczniemy od tego, że potrzebujemy sprawdzić warunki, czyli prosta operacja porównania A3:A9=G1. Następnie tą wartość musimy (wciąż wewnątrz funkcji) przemnożyć przez cały zakres danych do zsumowania (A3:A9=G1)*B3:D9

Wtedy dla każdej wartości PRAWDA z operacji porównania zostanie przemnożony cały wiersz z drugiego zakresu – po ewaluacji (klawisz F9), będzie to taki wynik w argumencie funkcji

SUMA.ILOCZYNÓW: {0\0\0;193\148\276;0\0\0;0\0\0;0\0\0;0\0\0;0\0\0}

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 03

Funkcja SUMA.ILOCZYNÓW bez problemu sumuje wszystkie wartości z takiej tablicy i uzyskujemy poprawny wynik, czyli sumę wartości z każdego wiersza, dla którego zostanie spełnione kryterium w pierwszej kolumnie.

=SUMA.ILOCZYNÓW((A3:A9=G1)*B3:D9)

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 04

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

SUMA.JEŻELI po zakresie 2D – Porada #290

Mamy taką sytuację, że sprzedaż poszczególnych sprzedawców mamy w osobnych kolumnach i każdy sprzedawca sprzedaje różne produkty w różnej ilości.

Porada 290 - SUMA.JEŻELI po zakresie 2D 01

Chcemy mieć możliwość zsumowania ilości sprzedaży dowolnego wybranego produktu za pomocą jednej formuły. Można to zrobić za pomocą funkcji SUMA.JEŻELI – trzeba tylko wiedzieć dokładnie jak ona działa.

Zacznijmy od tego, że wybierzemy sobie produkt powiedzmy kokos. Dołożone formatowanie warunkowe zaznaczy odpowiednie pozycje.

Porada 290 - SUMA.JEŻELI po zakresie 2D 02

Łatwo zauważyć, że zawsze chcemy sumować wartość z komórki o jedną kolumnę na prawo od komórki z kokosem. Jak możemy coś takiego zrobić za pomocą funkcji SUMA.JEŻELI?

Zaczniemy od tego, że poniżej naszych danych napiszemy prostą operację porównania przyrównującą wartość komórki do wybranego produktu. Dodatkowo pomalujemy komórki obok komórek z prawdami, bo to z nich tak naprawdę chcemy wziąć wartości do sumowania.

=A3=$K$1

Porada 290 - SUMA.JEŻELI po zakresie 2D 03

Dzięki temu widzimy jaką macierz/tablicę zbuduje sobie funkcja SUMA.JEŻELI. To będą pojedyncze komórki z wartością logiczną PRAWDA tam, gdzie warunek został spełniony. W takim razie jak zsumować wartości, które są obok wartości PRAWDA? To proste – wystarczy, że odpowiednio przesuniemy zakres, po którym będziemy sumować w odniesieniu do zakresu sprawdzającego wartości.
Czyli nasza funkcja SUMA.JEŻELI powinna wglądać tak:

=SUMA.JEŻELI(A3:G9;K1;B3:H9)

Porada 290 - SUMA.JEŻELI po zakresie 2D 04

Czyli zakres, po którym sumujemy musimy być odpowiednio przesunięty w stosunku do zakresu, po którym sprawdzamy kryterium. W tym przykładzie jest to dokładnie 1 kolumna w prawo: A3:G9 -> B3:H9.

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak wstawić etykiety danych z komórek arkusza do wykresu Excel 2013 – Porada #289

Od Excela 2013 pojawia się bardzo ciekawa funkcjonalność jeśli chodzi o etykiety danych na wykresie, a mianowicie możliwość ich pobrania z komórek arkusza. Pokażemy tą możliwość na przykładzie wykresu słupkowego.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 01

W Excelu 2013, żeby dodać etykiety danych, należy kliknąć w zielony plus, a następnie wybrać odpowiedni element wykresu, który chcemy dodać. W naszym przykładzie będą to etykiety na końcu zewnętrznym.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 02

Po ich dodaniu pokażemy jeszcze jedną sztuczkę dostępną od Excela 2013 – zaznaczymy pojedynczą etykietę danych i przytrzymując klawisz Shift (żeby pozostała na tej samej wysokości) przesuniemy ją myszką w bok. Następnie zmienimy parę parametrów tej etykiety, żeby było widoczne, że jest inna od pozostałych etykiet danych.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 03

Teraz naciskamy Ctrl + 1 (wciąż zaznaczona jest zmieniana przez nas etykieta danych) i w zakładce Opcje etykiet klikamy w przycisk Sklonuj bieżącą etykietę danych.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 04

Po tej operacji wszystkie etykiety będą miały takie samo formatowanie jak nasza zmieniona etykieta.
Teraz chcemy dodać dane do etykiet z zakresu komórek. Żeby to zrobić musimy mieć zaznaczone wszystkie etykiety i wtedy w zakładce Opcje etykiet będzie się wyświetlała opcja Wartości z komórek. Wystarczy, że oknie, które się pojawi, zaznaczysz zakres komórek, z których chcesz pobrać wartości do etykiet danych.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 05

Ewentualnie powinniśmy odznaczyć informacje, których nie chcemy, żeby etykiety pokazywały. Teraz mamy etykiety pokazujące wartości z komórek, które są tak samo sformatowane jak te komórki, poza drobnym wyjątkiem, że nie kopiują koloru czcionki.

Porada 289 - Jak wstawić etykiety danych z komórek do wykresu Excel 2013 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak znaleźć komórkę z odwołaniem cyklicznym – porada #288

Odwołania cykliczne, czy też iteracyjne, to takie odwołania, które odwołują się do komórki, w której same się znajdują. Najczęściej są tworzone nieumyślnie – powoduje to problem, gdyż formuła odwołująca się do wartości, którą sama zwraca przeważnie działa niepoprawnie.

Jeśli Excel wyświetli Ci komunikat o niepoprawnym działaniu formuł cyklicznych,

Porada 288 - Jak znaleźć komórkę z odwołaniem cyklicznym 01

to żeby je szybko odnaleźć wejdź na kartę Formuły, odszukaj polecenie Sprawdzanie błędów rozwiń je i wybierz Odwołania cykliczne.

Porada 288 - Jak znaleźć komórkę z odwołaniem cyklicznym 02

Wystarczy, że klikniesz odwołanie wskazujące do komórki z odwołaniem cyklicznym, a Excel Cię tam przeniesie.
Polecenie odszukujące odwołania cykliczne będzie aktywne tylko wtedy kiedy masz wyłączone obliczenia iteracyjne (cykliczne) w swoim pliku. Wystarczy, że wejdziesz do karty Plik, polecenie opcje i na zakładce formuły odnajdziesz odpowiedni checkbox.

Porada 288 - Jak znaleźć komórkę z odwołaniem cyklicznym 03

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak dodać makro do paska narzędzi szybkiego dostępu – widzowie #114

Jeśli chcesz dodać makro jako przycisk w pasku narzędzi szybkiego dostępu to wystarczy, żebyś kliknął prawym przyciskiem myszy i z podręcznego menu musisz wybrać polecenie Dostosuj pasek narzędzi Szybki dostęp.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 01

Następnie w oknie, które się pojawi potrzebujesz wybrać u góry z listy rozwijanej pozycję makra.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 02
Zobaczysz wtedy wszystkie makra z otwartych plików (przykładowo na rysunku poniżej są to makra ze skoroszytu makr osobisty – PERSONAL.XLSB). Wystarczy, że naciśniesz przycisk Dodaj, żeby dodać je do poleceń paska narzędzi szybkiego dostępu.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 03

Ponieważ wszystkie makra mają domyślnie takie same ikony, to warto zmodyfikować ją dla makra, z którego korzystamy, żebyśmy szybko je rozpoznawali – wystarczy, że zaznaczysz polecenie/makro, a następnie klikniesz przycisk Modyfikuj. Pokaże się wtedy okno, gdzie możesz wybrać ikonę dla polecenia i ewentualnie zmienić nazwę dla niego.

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 04

Wystarczy pozatwierdzać wybór ikony i dodane makro przyciskami OK i już na pasku narzędzi zobaczysz wybrane makro (na obrazie poniżej ikona klepsydry).

Widzowie 114 - Jak dodać makro do paska narzędzi szybkiego dostępu 05

Analogicznie możesz dodać makro do kart wstążki, ale dopiero od Excela 2010, bo dopiero ta wersja udostępnia opcję modyfikacji wstążki.

Link do strony z makrami wykorzystanymi w filmie:
https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx

Pozdrawiam
Adam Kopeć
Miłośnik Excela