• Zapisz się na newsletter i odbierz DARMOWY EBOOK: 10 najprzydatniejszych porad excelowych

Film: Suma czasu pracy w projektach wyodrębniana formułą tablicową

Niedawno pomagałam Leszkowi w zsumowaniu czasu pracy w grafiku, w którym notował przepracowane godziny łącznie z nazwą projektu. Były wiec tam wpisy typu 8A1, gdzie 8 to przepracowane godziny, a A1 to symbol projektu, w którym pracował pracownik. Zaproponowana przeze mnie formuła liczyła łączny czas pracy, a jak się później okazało, Leszek chciał również czas pracy w podziale na projekty. Zmodyfikowałam więc trochę tabelkę wynikową i formuły i udało się to osiągnąć. Jak – o tym jest najnowszy film.

Użyte formuły to:

1. Formuła licząca czas pracy w projekcie:

=SUMA(JEŻELI.BŁĄD(--LEWY($B3:$AF3;SZUKAJ.TEKST(AG$2;$B3:$AF3)-1);0))

2. Łączny czas pracy w miesiącu:

=SUMA(AG3:AH3;B3:AF3)

Oraz plik do pobrania:

Plik do pobrania:

 

 

 

Film: suma czasu pracy wyodrębniana formułą tablicową

Z problemem zaprezentowanym w dzisiejszym filmie przyszedł do mnie Leszek, który potrzebował obliczyć sumę przepracowanych godzin, które to musiał wyodrębnić z tekstu, np: z tekstu 8A1 potrzebował zsumować 8. W zaprezentowanym w filmie rozwiązaniu używam do tego formuły tablicowej oraz funkcji tekstowych: LEWY, SZUKAJ.TEKST i DŁ, oraz SUMA i JEŻELI.

Jeśli chcesz poznać sposób na obliczenie czasu pracy w poszczególnych projektach, to znajdziesz go tutaj. Nagrałam o tym kolejny film.

Oto film:

 

I jeszcze obiecana w filmie formuła (pamiętaj o zatwierdzeniu jej Ctrl + Shift + Enter):

=SUMA(JEŻELI(DŁ(B3:AF3)<3;B3:AF3;--LEWY(B3:AF3;SZUKAJ.TEKST("A";B3:AF3)-1)))

Oraz plik do pobrania:

 

 

Transpozycja danych za pomocą funkcji

Nie uwierzycie, ale temat tego wpisu wymyśliłam 3 kwietnia 2015 roku. Od tego czasu pisałam na blogu o milionie innych wskazówkach, które były z różnych powodów „pilniejsze”. Powiem Wam, że mam takich pomysłów cały segregatorek formatu A6 (ze Statuą Wolności na okładce – taki akurat był w sklepie ;)). Co jakiś czas dorzucam tam pomysły, ale prawda jest taka, że i tak wciąż dochodzą mi nowe, właśnie te pilniejsze i segregatorek sobie leży prawie zapomniany…

 

Segregatorek

Tak wygląda mój segregatorek. 🙂

Ale dziś się zdenerwowałam i stwierdziłam, że go odkurzę i napiszę o czymś, co tam się znajduje. Przeglądając go, znalazłam ciekawy temat, o którym kiedyś (bardzo dawno) już trochę pisałam. Chodzi o temat transpozycji, czyli jak zamienić kolumny na wiersze lub odwrotnie. Używałam do tego metody kopiowania i opcji wklej specjalnie jako transpozycja. Metoda świetna i skuteczna – sama bardzo często jej używam. Niestety w niektórych sytuacjach jest niewystarczająca. Głównym założeniem wklejania specjalnego jest to, że zadziała ono zgodnie z oczekiwaniami tylko wtedy, gdy ręcznie wkleimy wartości (np. na formuły już nie zadziała tak, jakbyśmy tego chcieli). Można więc w uproszczeniu powiedzieć, że jest statyczna. A przecież czasem chcemy, by taka transpozycja się aktualizowała, była dynamiczna, czyli działa się za pomocą formuły. Z moich obserwacji wynika, że wtedy użytkownicy po prostu ręcznie wpisują w pionie odwołania do odpowiednich komórek w poziomie lub odwrotnie. Trochę to karkołomne, ale działa. 🙂

Mało osób jednak wie, że istnieje funkcja, która umie sprawić, aby transpozycja była dynamiczna. Ma ona pewien haczyk, więc trzeba wiedzieć, jak ją zastosować, ale zobaczycie sami, że jest prościutka. Pokażę wam!

Tradycyjnie zacznijmy od formatki:

TRANSPONUJ_Formatka

Formatka

Czytaj dalej

Data najbliższego przelewu

Dzisiaj będzie rozbudowanie tematu z poprzedniego wpisu, w którym opisywałam jak poznać datę np. pierwszego czwartku miesiąca. Wykorzystałam do tego dość skomplikowaną formułę (ale formułę! nie makro ;)), zawierającą aż 4 funkcje. Dziś sobie to rozszerzymy, ponieważ założenia są takie:

W naszej firmie przelewy wychodzą tylko w pierwszy albo trzeci czwartek miesiąca. Jeśli faktura się „nie załapie” na pierwszy czwartek – jest płacona w trzeci czwartek, jeśli na niego się „nie załapie: – dopiero w pierwszy czwartek następnego miesiąca. Itd…

Straszne 🙂

Czyli, z danych do zadania potrzebujemy na pewno termin płatności faktury i kilka komórek roboczych (można byłoby je wrzucić bezpośrednio do formuły – jak chcecie). Formatka do tego zadania wygląda tak:

Formatka

Formatka

Czytaj dalej

Pierwszy czwartek miesiąca

Dziś temat, który miałam z tyłu głowy już dość długo. Pierwszy raz zaczęłam się zastanawiać na nim, kiedy tworzyłam brytyjski grafik czasu pracy. Anglicy mają kilka świąt, które wypadają zawsze w pierwszy poniedziałek miesiąca. Akurat nie było konieczności, abym wprowadzała taki warunek do arkusza, jednak zastanowił mnie on. Ponieważ od razu nie wymyśliłam rozwiązania, zostawiłam temat. Niestety nadal siedział mi on w głowie. 🙂

Niedawno odezwała się do mnie Marta, która pracuje w księgowości, i odgrzebała kwestię spod gruzów innych pomysłów. Marta ma bardziej skomplikowany problem, jednak zagadnienie, które dzisiaj omówię, jest niezbędne do jego rozwiązania. Pokażę dziś, jak się ustala datę np. pierwszego czwartku miesiąca. Napisałam „np.”, ponieważ zaprezentowaną metodą można ustalić dowolny numer dowolnego dnia tygodnia (oczywiście bez przesady – nie będziemy ustalać 15. poniedziałku miesiąca, bo taki przecież nie istnieje). 😉

Aby formuła była uniwersalna, będziemy potrzebowali następujących danych:

  1. Który z kolei dzień nas interesuje (np. pierwszy).
  2. Jaki dzień tygodnia (np. czwartek).
  3. W jakim miesiącu (np. październik).
  4. W którym roku (np. 2016).

Formatka jest prościutka i wygląda tak:

Pierwszy czwartek miesiaca-formatka

Formatka

Czytaj dalej

Funkcja SUMA niewyświetlająca błędów – formuła tablicowa

To zagadnienie chodzi za mną już od jakiegoś czasu. Wiem, że za wami też, bo pytacie mnie o nie. 😉

O czym mowa? Otóż o tym, co zrobić, aby funkcja SUMA wyświetlała poprawną wartość, jeśli w sumowanym zakresie są błędy.

Często zdarza się tak, że zakres, który chcemy zsumować, zawiera już inne formuły. Bywa, że formuły te zwracają błędy. Niestety, jeśli w sumowanym zakresie funkcja SUMA napotka błąd, również zwróci błąd. Taką sytuację ilustruje poniższy obrazek:

Suma wyświetlająca błąd - działanie niepożądane

Suma wyświetlająca błąd – działanie niepożądane

Funkcja SUMA zwróciła błąd (#N/D), ponieważ jedna z sumowanych komórek zwróciła tenże błąd. Zauważcie, że w tabeli z kursami walut na obrazku nie ma dolara australijskiego (AUD). W tym przykładzie SUMA ma policzyć wszystko to, co się da (czyli ekwiwalent CHF, USD, EUR), i wyświetlić wynik – czyli wszystko oprócz tej błędnej wartości (ekwiwalent AUD). Ponieważ zwykła SUMA nie radzi sobie z tym zadaniem, należy użyć formuły tablicowej.

Czytaj dalej