Jak policzyć ilość wielkiej litery U w komórkach – porada #287

Załóżmy taką sytuację, że musisz zliczyć ilość wszystkich wystąpień wielkiej litery U w zakresie komórek. To zadanie jest relatywnie proste 😉

Porada 287 - Jak policzyć ilość wielkiej litery U w komórkach 02

Zacznijmy od tego, że będzie nam potrzebna funkcja PODSTAW, która w danym ciągu tekstowym (nawet w zakresie komórek) zamieni dany ciąg tekstowy na inny ciąg tekstowy. W naszym przykładzie ważne jest to, że rozróżnia ona wielkość liter, czyli jeśli każemy jej zmienić wielką literę U, to podmieni tylko ją, a małej litery u nie ruszy. W naszej sytuacji, będziemy chcieli usunąć literę/ciąg, więc będziemy chcieli ją zamienić na pusty ciąg tekstowy (dwa podwójne cudzysłowy ) np:

=PODSTAW(B2;”U”;””)

Ale zanim będziemy podstawiać litery, musimy najpierw policzyć długość wszystkich ciągów w naszym zakresie komórek. Wystarczy, że skorzystamy z funkcji po tym zakresie. Excel policzy nam długość ciągów tekstowych w poszczególnych komórkach:

=DŁ(A2:B10) -> {3\3;2\3;3\2;3\2;3\2;3\4;3\3;3\3;3\3}

Porada 287 - Jak policzyć ilość wielkiej litery U w komórkach 02

Tylko zanim te wartości zsumujemy będziemy chcieli odjąć od nich długość ciągów tekstowych z usuniętą wielką literą U.

=DŁ(A2:B10)-DŁ(PODSTAW(A2:B10;”U”;””)) -> {0\1;1\1;0\1;2\0;0\0;0\2;1\1;0\1;0\1}

Porada 287 - Jak policzyć ilość wielkiej litery U w komórkach 03

Teraz wystarczy zsumować wynikową tablicę wartości. Żebyś nie musiał zatwierdzać formuły jako tablicową kombinacją klawiszy Ctrl + Shift + Enter, to wstawimy naszą formułę do funkcji SUMA.ILOCZYNÓW i już mamy interesujący nas wynik

=SUMA.ILOCZYNÓW(DŁ(A2:B10)-DŁ(PODSTAW(A2:B10;”U”;””)))

Porada 287 - Jak policzyć ilość wielkiej litery U w komórkach 04

Żeby w analogiczny sposób obliczyć ilość wystąpień ciągu tekstowego, musisz tylko podmieniać zmieniany tekst i podzielić formułę przez długość tego tekstu np:

=SUMA.ILOCZYNÓW(DŁ(A2:B10)-DŁ(PODSTAW(A2:B10;”WU”;””)))/DŁ(„WU”)

Porada 287 - Jak policzyć ilość wielkiej litery U w komórkach 05

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Automatyczne budowanie hiperłączy do dat z drugiego arkusza – widzowie #109

Dzisiaj naszym zadaniem jest stworzenie Hiperłączy do drugiego arkusza na podstawie dat. Dokładnie chodzi o to, że po kliknięciu w datę (hiperłącze) w pierwszym arkuszu

Widzowie 109 - Automatyczne budowanie hiperłączy do dat z drugiego arkusza 01

przeskoczymy do pierwszej takiej daty w drugim arkuszu

Widzowie 109 - Automatyczne budowanie hiperłączy do dat z drugiego arkusza 02

Żeby to zrobić za pomocą formuły w Excelu będzie potrzebowali kolumny pomocniczej, w której zbudujemy odpowiednie łącze, za pomocą funkcji HIPERŁĄCZE.

Najważniejsze będzie zbudowanie odpowiedniego odwołania. Możemy zacząć od tego, że w formule klikniemy na komórkę w drugim arkuszu wtedy pojawi się odwołanie podobne do tego:

=Drugi!C2

Czyli nazwa, wykrzyknik i komórka do której się będziemy odwoływać. My potrzebujemy się zmieniać numer wiersza (2). Żeby to zrobić będziemy potrzebowali połączyć pierwszą cześć odwołania z funkcją, która wyszuka numer wiersza. Najprostsza tu będzie funkcja PODAJ.POZYCJĘ, która będzie szukała daty w kolumnie drugiego arkusza. Łącznie nasze połączenie będzie wyglądać tak:

=”Drugi!C”&PODAJ.POZYCJĘ(A2;Drugi!C:C;0)

Widzowie 109 - Automatyczne budowanie hiperłączy do dat z drugiego arkusza 03

Teraz wystarczy to odwołanie wstawić do funkcji HIPERŁĄCZE i jeśli chcielibyśmy zamiast odwołania do komórki widzieć jako link datę to w drugim argumencie funkcji wpiszemy odwołanie do komórki z datą.

Potrzebujemy jeszcze symbolu # na początku naszego odwołania, żeby Excel wiedział, że komórka, do której ma kierować Hiperłącze znajduje się w tym samym pliku co nasza formuła.

=HIPERŁĄCZE(„#Drugi!C”&PODAJ.POZYCJĘ(A2;Drugi!C:C;0);A2)

Widzowie 109 - Automatyczne budowanie hiperłączy do dat z drugiego arkusza 04

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Separator dziesiętny – kropka czy przecinek, ustawienia regionalne, daty itp. – porada # 286

Czasami separator dziesiętny z jakiego korzysta Twój system może być problemem – ma znaczenie czy to jest kropka czy przecinek chociażby przy importowaniu danych. Problemy mogę się też pojawiać chociażby przy datach itp.
Obrazek poniżej pokazuje taką sytuację, że mamy polskie nazwy kart i poleceń, natomiast separatory są angielskie (Stany Zjednoczone).

Porada 286 - Separator dziesiętny kropka czy przecinek ustawienia regionalne 01

W Excelu dość prosto zmienić separatory dziesiętne – wystarczy wejść do menu Plik, następnie polecenie Opcje, a w nich zakładka zaawansowane. Tam już na pierwszej stronie znajdziesz możliwość używania separatorów dziesiętnych systemowych, albo takich jakie Ty ustawisz.

Porada 286 - Separator dziesiętny kropka czy przecinek ustawienia regionalne 02

Jeśli zaznaczony jest pole wyboru (checkbox) Użyj separatorów systemowych, to Excel korzysta z separatorów systemowych, ale chcesz możesz go odznaczyć i wtedy samemu ustawić znak dla separatora dziesiętnego i tysięcy.

Czasami jednak chcesz zmienić od razu wszystkie separatory systemowe. Żeby to zrobić musisz wejść Panelu sterowania windowsa i sekcji Zegar, języki i region. Tam klikasz link Region i język. W jego pierwszej zakładce możesz zobaczyć ustawienia daty i czasu wynikające z aktualnych ustawień regionalnych.

Porada 286 - Separator dziesiętny kropka czy przecinek ustawienia regionalne 03

Powyższe są obraz prezentuje ustawienie angielskie dla Stanów zjednoczonych (możesz ten wybrany format zmienić na dowolny z listy). My jeszcze potrzebujemy zobaczyć dokładne separatory dziesiętne i waluty, dlatego klikamy w przycisk Ustawienia dodatkowe. W oknie, które się otworzy będzie nas interesowała przede wszystkim pierwsza zakładka liczby.

Porada 286 - Separator dziesiętny kropka czy przecinek ustawienia regionalne 04

W niej możesz zmienić odpowiednie separatory na takie, do których się przyzwyczaiłeś/które Ci odpowiadają, czyli możesz zmienić ustawienia domyślne.

Przykładowo dla domyślnych ustawień języka polskiego możesz zmienić ustawienia tak, żeby separator dziesiętny to była kropka, a separator listy to był przecinek.

Porada 286 - Separator dziesiętny kropka czy przecinek ustawienia regionalne 05

Sprawi to, że kropka będzie rozdzielać część całkowitą od dziesiętnej, a przecinek będzie m.in. rozdzielał argumenty w funkcjach i pozycja w ręcznie wpisywanych listach rozwijanych.

W zależności z jakiego językowego źródła pobierzesz dane Twoje ustawienia regionalne mogą Ci ułatwić lub utrudnić zadanie.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak policzyć ilość wielkiej litery U w komórkach – porada #285

Załóżmy, że potrzebujesz zliczyć komórki w których wystąpiła wielka litera „U”, bo ona oznacza dla Ciebie coś istotnego, a mała litera „u”, nie jest dla Ciebie istotna. Jak to zrobić.

Rozważmy dwie sytuacje. W pierwszej w komórce znajduje się tylko 1 litera. W drugiej w komórce jest więcej liter.

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 01

W pierwszej sytuacji możemy skorzystać z funkcji PORÓWNAJ, która porównuje, czy dwa ciągi znaków są identyczne m.in. pod kątem wielkości liter. Zwykłe porównanie nie zapewniłoby rozróżnienia pomiędzy małymi, a wielkimi literami. Czyli środek naszej formuły będzie wyglądać tak:

=PORÓWNAJ(A2:A10;”U”)

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 02

Powyższa formuła zwraca wartości PRAWDA, gdy w komórce jest wielka litera „U” i FAŁSZ w przeciwnej sytuacji. Funkcja PORÓWNAJ zwraca odpowiedni ciąg tych wartości:

{FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ}

Musimy zsumować prawdy, ale, żeby uniknąć zatwierdzania formuły jako formuły tablicowej będziemy chcieli wspomóc się funkcją SUMA.ILOCZYNÓW, która radzi sobie z operacjami na tablicach, ale niestety ignoruje wartości logiczne PRAWDA i FAŁSZ. Dlatego musimy je zamieć odpowiednio na 1 i 0, wykonując na nich dowolną operację matematyczną. Najszybsza jest podwójna negacja, czyli dołożenie dwóch znaków minus przed funkcją PORÓWNAJ. W takiej sytuacji cała formuła będzie wyglądała tak:

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 03

Teraz rozwiążmy drugą sytuację. Musimy sprawdzić, czy w poszczególnych tekstach z interesującego nas zakresu znajduje się wielka litera „U”. Tym razem zaczniemy od funkcji ZNAJDŹ, która zwraca pozycję jednego ciągu tekstowego w innym tekście, a dodatkowo zwraca uwagę na wielkość liter.

=ZNAJDŹ(„U”;D2:D10)

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 04

Jeśli funkcja ZNAJDŹ nie znajdzie szukanego tekstu to zwraca błąd argumentu, w przeciwnej sytuacji zwraca liczbę w omawianym przykładzie wynikiem będzie ciąg:

{#ARG!;2;#ARG!;1;#ARG!;#ARG!;2;#ARG!;#ARG!}

Na błędach trudno wykonywać sumowanie, dlatego musimy je przekształcić. W tej sytuacji najprościej skorzystać z funkcji CZY.LICZBA, wtedy uzyskamy wartości logiczne PRAWDA, gdy „U” udało się znaleźć, a FAŁSZ, gdy był błąd.

=CZY.LICZBA(ZNAJDŹ(„U”;D2:D10))

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 05

Wychodzi ciąg – {FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ}

Czyli znowu możemy skorzystać z funkcji SUMA.ILOCZYNÓW i uzyskujemy interesujący nas wynik:

=SUMA.ILOCZYNÓW(–CZY.LICZBA(ZNAJDŹ(„U”;D2:D10)))

Porada 285 - Jak policzyć ilość wielkiej litery U w komórkach 06

Pozdrawiam
Adam Kopeć
Miłośnik Excela

SUMA.JEŻELI 2D – porada #284

Czasami dane, które chcesz sumować są rozdzielone na kilka kolumn. W przykładowych danych możesz zobaczyć, że zyski z każdego miesiąca są rozpisywane w 2 kolumnach – kto dokonał sprzedaży i jaka była jej wartość.

Porada 284 - SUMA.JEŻELI 2D 01

Jak możesz teraz zrobić szybko podsumowanie sprzedaży dla poszczególnych pracowników? Okazuje się, że wystarczy jedna funkcja SUMA.JEŻELI. Musisz tylko odpowiednio zaznaczyć zakresy.

Pierwszy zakres to zakres, na którym będziemy sprawdzali kryterium, czyli czy to jest konkretny sprzedawca. Excel nie przyjmie tutaj rozdzielnie zaznaczonych kolumn, ale śmiało możemy zaznaczyć większy zakres danych, nawet z tymi kolumnami, które nie zawierają sprzedawców ($A$3:$F$12) – dla tych kolumn nie będzie spełniony warunek.

Kryterium jest proste, bo to konkretny pracownik zapisany w pojedynczej komórce (H2).

Zostaje nam tylko zakres, po którym będziemy sumować. W tym przykładzie wystarczy, że zakres, w którym sprawdzamy kryterium przesuniemy o 1 kolumnę w prawo ($B$3:$G$12). Wtedy odpowiedni sprzedawcy będą odpowiadać odpowiednim wartościom sprzedaży, na zasadzie prostego przesunięcia.

=SUMA.JEŻELI($A$3:$F$12;H2;$B$3:$G$12)

Porada 284 - SUMA.JEŻELI 2D 02

Właśnie zrobiłeś SUMĘ.JEŻELI 2D 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela