Excel w firmie
Ułatw sobie życie. Poznaj funkcje Excela!

Auto-kalendarz z formatowaniem warunkowym

Posted In: . By Andrzej Dobrucki

Do ostatnio opublikowanej metody tworzenia autodatowania 1-31 (dni miesiąca) dołączam również metodę formatowania Sobót i Niedziel tak, żeby pojawiały się np na czerwono. Sprawa jest dość zawiła, ale taki arkusz funkcjonuje doskonale.


Pierwsza funkcja pobiera datę kolejnego dnia miesiąca (zaczynając od 1)

[k1] =DATE(2008,month(today()),1)

Komórka poniżej jest tak sformatowana, by tą samą datę odczytać w formacie 'ddd' czyli nazwą dnia. Niestety komórka tylko pozornie zawiera tekst, bo jak wiadomo faktycznie jest to liczba porządkowa np 36536.

To co chcemy osiągnąć, czyli formatowanie warunkowe, jeśli w komórce występuje ciąg "So" lub "N" (lub Sat i Sun dla wersji angielskiej) jest w tej chwili niemożliwe. Teraz trzeba więc skonwertować datę z systemu liczbowego na czysty tekst. Możemy to zrobić tak:

[k3] =TEXT(K2,"ddd")

Otrzymany tekst nadaje się już do autoformatowania.

W zakładce formatowania warunkowego(conditional format) określamy więc 2 warunki:




="So" i ="N" używamy oczywiście jeśli nasz Excel jest po polsku (o zgrozo).
Dla angielskego będą to ="Sat" i ="Sun".

Środkowy wiersz [K2] można oczywiście schować dla lepszej przejrzystości kalendarza.

 

Excel kopiuje ukryte pola :o(

By Andrzej Dobrucki

No jasne, każdy pewnie spotkał się z sytuacją, w której musi skopiować pewną porcję danych, które na nieszczęście poprzetykane są ukrytymi wierszami / kolumnami. Jak sprawić, by przez zwyczajne CTRL+C (u mnie OPTION+C) i CTRL+V Excel skopiował tylko widoczne komórki?

1. Zaznacz obszar tekstu, który chcesz skopiować (to co widzisz)
2. Przejdż w menu Edit > Go To > Special > Only visible cells
3. Skopiuj tekst CTRL+C/V. Voila.

Pozdrawiam

 

Oto bardzo przydatna funkcja. Narodziła się z potrzeby stworzenia arkusza analizującego plan produkcyjny, czyli pewien ciąg liczb przyporządkowanych do dni modeli produkcyjnych i dni w kalendarzu. Oto jak wygląda ciąg wejściowy:



Jak widać plan produkcyjny rozpoczyna się w przypadku pierwszej pozycji 29 czerwca (10 sztuk). Co zrobić by Excel odnalazł tę wartość i podał kiedy dany LOT (partia produkcyjna) będzie produkowany. Dodatkowo przyda się również ilość dni produkcyjnych LOTu.

Oto pierwsza funkcja:


[C16]=INDEX(E9:K9;MATCH(TRUE;E9:K9<>0;0))


[PL : INDEX=INDEKS
MATCH=PODAJ.POZYCJĘ
TRUE=PRAWDA]


Uwaga: Jest to funkcja ciągu a zatem zatwierdzenie jej następuję po jednoczesnym naciśnięciu CTRL+SHIFT+ENTER (nie samo ENTER jak w przypadku zwykłych funkcji!). Excel zamknie funkcję dwoma nawiasami klamrowymi {}.

Funkcja indeksująca wyszukuje w pewnym ciągu cyfr (tutaj linia 2 naszego zapisu) pierwszą wartość od lewej różną od zera i podaje wartość napotkanej komórki.
Kolejna funkcja poda absolutne położenie komórki względem lewej krawędzi arkusza, a więc numer kolumny:



[D16]=MATCH(C16;9:9;0)


[PL : MATCH = PODAJ.POZYCJĘ]

Posiadamy już zatem wartość komórki i jej pozycję w kolumnach. Numer kolumny musimy teraz wykorzystać do ponownego indeksowania tym razem wiersza daty (w moim arkuszu nadałem mu nazwę data.


[E16]=INDEX(data;D16)


[PL : INDEX = INDEKS]

W polu pobierającym datę musimy oczywiście zmienić formatowanie na format daty.

Na końcu potrzebujemy jeszcze ilości dni produkcyjnych dla danego LOTu. Otrzymujemy je za pomocą kolejnej przydatnej funkcji:


[F16]=COUNTIF(9:9;">0")


[PL COUNTIF=LICZ.JEŻELI]

Funkcja ta zwróci nam w pewnym ciągu (tu: cały wiersz 9) ilość komórek, których wartość jest różna od zera.

I to tyle na dziś. Mam nadzieję, że te funkcje będą Wam przydatne.

 

Wygodna kontrola budżetu

Posted In: . By Andrzej Dobrucki


Ten temat nie jest związany z Excelem, jednak postanowiłem nawet kosztem offtopicu wrzucić coś o tym zgrabnym webowym trackerze wydatków. Tego typu programów dla Maca i PC jest bez liku - większość shareware z dużym wachlarzem opcji i raportowania.

Expenseview to coś extra. Aplikacja jest zupełnie darmowa. Działa w przeglądarce (w Safari nie wyświetla pie charta) Firefox i zapewne IE. Po zalogowaniu wita nas przyjaznymi kolorami ekran edycji kategorii wydatków i przychodów. Możemy wprowadzić roczny / miesięczny / tygodniowy budżet dla poszczególnych kategorii, co pozwoli zaplanować np kolejny miesiąc. Przychody / Wydatki możemy obejrzeć na wykresie kołowym z wyróżnieniem poszczególnych kategorii.

Jedyną wadą wydaje się brak możliwości eksportu wydatków do arkusza .xls (choć jest to możliwe do pliku .csv) z podziałem na kolumny. Jednak bogate możliwości wyszukiwania konkretnych pozycji wydają się rekompensować braki.

Narzędzie bardzo wygodne. Dostęp do stanu portfela z każdego miejsca na ziemi. Gratualcje za pomysł i zapraszam do korzystania z serwisu.

http://www.expenseview.com

 

Autodatowanie arkusza

Posted In: . By Andrzej Dobrucki


Wpadłem ostatnio na pomysł, który rozwiązuje problem z datowaniem arkuszy przy zmianie miesiąca.

Załóżmy, że w arkuszu górny rząd zawiera ponumerowane dni (domyślnie 1-31). Nie jest żadnym problemem wprowadzenie ręcznie daty (01 / miesiąc) i przeciągnięcie w prawo formuły. Jedyny problem to to, że taki zabieg musimy wykonać w każdym kolejnym miesiącu. Jeśli o tym zapomnisz - narażasz się na to, że arkusz będzie działał nieprawidłowo (jeśli komórki korzystają z wiersza daty).

Oto, co chcę otrzymać (tak jak na obrazku)

Pole od 1-31 (dni)
Pole nazwy (Mon-Sun)

Aby w pierwszej komórce w rzędzie dni pojawiał się pierwszy dzień danego miesiąca, należy w komórce tej odwołać się do innej komórki, która generuje aktualny l.p. miesiąca:

=DATE(2008,E1,1) ; w 2008 roku, pole E1 wskazuje aktualny miesiąc, generowany dzień = 1(pierwszy danego miesiąca)

[ E1 ] =MONTH(TODAY())

I kłopot z głowy. Oczywiście rok możemy generować w podobny sposób odwołując się do innej komórki n.p. tak:

[ E2 ] = YEAR(TODAY())


Cheers everyone!

 

Rejestr obrotu materiałami

Posted In: . By Andrzej Dobrucki

Obrót materiałami prędzej czy później czeka każdą firmę w branży produkcyjnej. W momencie kiedy zaczynamy dużo odbierać i dużo wysyłać, prosta kalkulacja na kartce papieru przestaje być wystarczającym dokumentem potwierdzającym przyjęcia i wydania towaru. Tym bardziej sprawę skomplikuje sytuacja, w której towar zakupujemy w obcej walucie np. w Euro lub Dolarze.

Moim celem było stworzenie czytelnego i prostego arkusza kalkulacyjnego, który na pierwszy rzut oka dawałby wymierne i precyzyjne informacje dotyczące stanów materiału w magazynie, zużycia własnego w produkcji oraz stanowiłby podstawę do raportów zakupów i sprzedaży z obrotu materiałami w relacji dostawca / klient.

Oto pierwsza (podsumowująca) zakładka arkusza:



Dla każdego materiału otrzymujemy podsumowanie przyjęć / wydań oraz tendencję wzrost / spadek liczoną od początku danego miesiąca księgowego. W ostatniej rubryce znajdziemy podsumowanie wydań wewnętrznych (jeśli sami wykorzystujemy dany materiał)

Podsumowanie w moim przypadku rozbiłem na tworzywo sztuczne oraz lakier.

Drugi arkusz dokonuje szczegółowej kalkulacji przychodów / rozchodów materiału na bazie jego ceny wejściowej oraz wyjściowej oraz dwóch innych arkuszy: rejestru przyjęć i rejestru wydań



Rodzaj materiału wybierany jest z listy, dane wyjściowe generują się na podstawie wybranego rodzaju materiału, tabele są więc dynamiczne i łatwo dostosować je do swoich potrzeb. Jedyną wartością wprowadzaną ręcznie w tym arkuszu jest kurs wymiany walut dla każdego materiału osobno (jeśli wyrażony w PLN, wtedy równa się on 1). Dlaczego nie możemy wprowadzić ogólnego dziennego przelicznika walutowego? W praktyce dostawcy zagraniczni mają bowiem różne zasady przeliczania kwot faktur: zazwyczaj wykorzystując przelicznik NBP z dnia dostawy, czasem jednak jest to średnia z ostatniego kwartału bądź ostatniego roku. Dlatego wartość kursu musi być rozważona dla każdego materiału indywidualnie.

Wartość wydań wewnętrznych obliczana jest według rejestru przyjęć / wydań zewnętrznych oraz stanu magazynowego. Dodatkowo dla today() oraz today()+1 wartość wydań wewnętrznych wynosi zawsze 0. Brzmi skomplikowanie ale jest bardzo proste: skoro stan fizyczny dotyczy ostatniego dnia (today()-1), dzisiejszy stan będzie wynosił zawsze 0 lub "", a zatem arkusz stwierdzi, iż cały towar został wydany wewnętrznie. Podwójna funkcja warunkowa stanowi tu skuteczny bezpiecznik:

Karty przyjęć i wydań są do siebie podobne, karta wydań posiada dodatkowo funkcę wprowadzania odbiorcy, co pozwala w łatwy sposób sprawdzić, gdzie towar pojechał)



Podsumowanie wydań w tym samym arkuszu:



Dodatkowo z podarkuszy warto wspomnieć jeszcze o:

- Stanach magazynowych (wewnętrzna wartość inwentaryzowana dziennie pozwala dokładniej określić ile materiału zużyliśmy w toku produkcji)
- Informacji o materiale - arkusz ten zawiera wszystkie podstawowe wiadomości o każdym z materiałów wraz z miejscem pozyskania i cenami sprzedaży zakupu. Dodatkowo znajduje się tu lista firm, od których kupujemy i tych, do których towar jest sprzedawany.

Całość arkusza oparta jest na bardzo prostych ale potężnych funkcjach:

- VLOOKUP, HLOOKUP (Wyszukiwanie / indeksacja danych w pionie i poziomie)
- SUMIF (Sumowanie warunkowe)
- IF(IF = funkcja warunkowa z dwiema zmiennymi

 

AMP - Analiza mocy produkcyjnych

Posted In: . By Andrzej Dobrucki

Podstawą działania przedsiębiorstwa produkcyjnego jest skuteczne planowanie zarówno długo jak i krótko-terminowe. W przypadku maszyn praktycznie zawsze mamy do czynienia z pewną stałą wartością, którą operujemy.

Ta stała wartość to w branży produkcyjnej UPH /units per hour/ - wskaźnik wydajności godzinowej maszyny. Na podstawie wydajności godzinowej możemy zaplanować wydajność zmianową, dzienną, miesięczną, kwartalną itp.

Przyjmijmy, że na jednej wtryskarce zakładamy dwie formy wtryskowe do dwóch różnych modeli telewizora. UPH obliczymy dzieląc cykl przez 60 sek.

UPM /Units per minute/ = Czas cyklu / 60
UPH /Units per hour/ = UPM * 60

A zatem, jeśli cykl zamknięcia i otwarcia maszyny wynosi 60 sek, to godzinna wartość UPH wyniesie 60 (Najprostszy z możliwych przykładów)

Oto jakie zmienne zastosowałem w arkuszu:

Po lewej: Maszyna, ilość gniazd (matryc w formie), UPH, ilość sztuk na dobę, tydzień, miesiąc.

Dalej w tabeli:

Stan31 - stan produktu z ostatniego zamknięcia miesięcznego
Klient - Plan zamówień klienta
CKD - elementy zmontowane zamawiane przez klienta oddzielnie (dodawane do stanu)
Plan E - Zakładany plan produkcji (Sumuje się na dole jako DY Plan)
Wynik - Faktyczny wynik (wprowadzany w celu porównania / potwierdzenia planu)
Balans - Ilość +/- produktu zakładana na podstawie planu klienta (- oznacza, że źle zaplanowaliśmy produkcję)
W/Days (working days) - ilość dni pracujących w tygodniu (wprowadzany ręcznie)

Balans całkowity maszyny określa nam wartością od 0 (brak mocy produkcyjnej) do maks. mocy produkcyjnej w jednostce tygodnia.

Możemy zatem tygodniami zaplanować produkcję nawet na rok i uaktualniać ją tylko przy zmianach planu (w branży telewizyjnej min. 1 dziennie)

Jeśli brakuje nam mocy przy 5 dniach roboczych, możemy zawsze pracować w sobotę i/lub niedzielę. W tym przypadku zmiana dni roboczych powoduję podwyższenie tygodniowej wydajności.

Oto zrzut arkusza:



A to podsumowanie (Wolne moce produkcyjne vs ich brak w ujęciu tygodniowym)

 

Etykiety Identyfikacyjne Materiałów

Posted In: . By Andrzej Dobrucki

Pomysł na stworzenie etykiet materiałowych pojawił się w związku z dużym zamieszaniem na etapie produkcja - magazyn. Rzecz dotyczyła kart identyfikacji materiału zwanych potocznie pilotówkami.

Przyjmijmy, że nasz zakład produkuje dziennie około 3 - 4 tysięcy obudów plastikowych, przy czym obudowy można podzielić na grupy ze względu na poniższe zależności:

1) Zastosowany budulec (granulat polimerowy)
2) Klasa palności wraz ze specyfikacją granulatu
3) Dostawca tworzywa
4) Numer seryjny modelu odbiorcy (W naszym przypadku 11 liter i cyfr)
5) Data produkcji
6) Grupa produkcyjna (zmiana pracowników)
7) Inne

Oto jakie informacje musi nanieść przeciętny pracownik produkcyjny by oznaczyć wózek przekazywany z działu produkcji do magazynu. W praktyce wypisywanie oznaczeń technicznych długopisem przez osoby nie-techniczne, często przypadkowe zupełnie nie zdaje egzaminu. Informacje są błędne i nieczytelne. Dlatego zastosowałem prosty arkusz Excela, który oferuje następujące udogodnienia:

1) Po wybraniu numeru modelu, inne informacje uzupełniane są automatycznie (za wyjątkiem informacji indywidualnych t.j. - pracownika odpowiedzialnego (tutaj brygadzisty), daty produkcji oraz zmiany
2) Dzięki wprowadzeniu imienia i nazwiska brygadzisty łatwiej jest zidentyfikować pochodzenie towaru w przypadku ewentualnej wady
3) Klient otrzymuje czytelny dowód potwierdzający zgodność materiału z normami europejskimi (tutaj ROHS)
4) Czas wypisywania "pilotówek" zostaje skrócony do minimum
5) Dokument w ciągu sekund może być wydrukowany i wykorzystany (6 etykiet na stronie)

Oto zrzut rzeczonego arkusza:

 

Kalkulator premii wydajnościowej

Posted In: . By Andrzej Dobrucki

Są różne sposoby by zmotywować pracownika do wydajniejszej pracy. Oczywiście zawsze można po prostu podnieść mu pensję, czyniąc go szczęśliwym (czasowo) i bardziej odpowiedzialnym za swoje stanowisko. W naszej firmie wydajność liczymy dla pracowników w dziale produkcji. Nie od dziś wiadomo, że są oni słabo zmotywowani - otrzymują zazwyczaj pensję w okolicach najniższej krajowej, plany urlopowe firmy są często zmieniane, pracownicy nie mają więc poczucia stabilizacji potrzebnej do wydajniejszej pracy. Odpowiednie naliczanie premii wydajnościowej mogłoby w znacznym stopniu pomóc w uzyskaniu kilku zasadniczych celów:

1) Obniżeniu nieobecności chorobowej i ilości urlopów na żądanie (zdarzały się dni, kiedy w jednym dziale brakowało 20 ludzi)
2) Zwiększeniu odpowiedzialności i świadomości u pracownika co do globalnego obrazu wydajności jego grupy w fabryce
3) Eliminacji zjawiska nepotyzmu premiowego wśród kadry zarządzającej średniego i wysokiego szczebla
4) Ułatwieniu naliczania premii przez dział kadr

Kalkulator Premii Wydajnościowych (KPW) ma za zadanie rozwiązać 4 powyższe kwestie. Oto on:



Krótki opis wygląda mniej więcej tak:
Wszelkie zmienne wprowadza się w żółte pola, inne pola wyliczane są automatycznie.
1) Na początku interesują nas dane osoby, dla której wyliczymy premię a więc: Imię, Nazwisko, stanowisko i grupa, do której należy.
2) Druga zmienna do podstawa wynagrodzenia w kwocie brutto
3) Urlopy pracownika - DPR - Liczba dni pracujących, UŻ - Urlop na żądanie, UW - urlop wypoczynkowy, NN - nieusprawiedliwiona nieobecność, ZCH - zwolnienie chorobowe.
4) Subiektywna ocena pracy pracownika przez bezpośredniego przełożonego (skala 1-5)
5) Suma nadgodzin w miesiącu

Kalkulator działa tak, że od podstawy wynagrodzenia wylicza premię maksymalną (według danych w Arkuszu2 - o tym dalej).
Jest to pewien procent wynagrodzenia, od którego to procenta odejmuje się następnie punkty procentowe.
Druga strona arkusza służy do wydruku. Z pozoru jest to tylko alternatywny sposób przedstawienia danych z kalkulatora, w praktyce jednak może się okazać potężnym narzędziem motywującym pracownika. Osoba, która otrzymała taki wydruk wraz z kwotą premii może od razu zobaczyć, które z elementów pracy (frekwencja, wydajność, ocena przez przełożonego) są u niego niewystarczające. Wydruk pokazuje punktowo i procentowo wszystkie wartości (-), za które nastąpiły potrącenia. W kolejnym miesiącu nasz pracownik będzie musiał się bardziej starać doskonale wiedząc, co ma zrobić by otrzymać maksymalną premię.

Arkusz 2

Tutaj znajdują się dane bazowe dla kalkulatora. Innymi słowy są to dane, według których kalkulator będzie wiedział ile potrącić z kalkulowanej premii. Znajduje się tu pole premii maksymalnej (% podstawy), ilość dni pracujących (jeśli stała) itd.
Ocena pracy oraz potrącenia za urlopy można łatwo prześledzić na rysunku. Godziny nadliczbowe zostały potraktowane jako dodatek do premii maksymalnej (czyli osobna premia), gdzie wartość brutto pobierana jest według przedziałów godzinowych ( na dole)
Po prawej znajduje się tabela z wydajnością danej grupy produkcyjnej wyliczona na zasadzie TARGET : RESULT. Wydajność jest również przeliczana na wartości procentowe.

I to mniej więcej wszystko. Wydaje mi się, że ważny w tym projekcie jest nie tyle kunszt w posługiwaniu się arkuszami excela, bowiem wykorzystane formuły są niezwykle proste, ale pomysł na kalkulację wraz z gotowym wydrukiem. Taka metoda po prostu musi zmobilizować naszego pracownika do wydajniejszej pracy i nienagannej frekwencji.

Pozdrawiam

 

Kilka słów o mnie

Posted In: . By Andrzej Dobrucki

Mam na imię Andrzej, jestem managerem w dosyć dużym międzynarodowym koncernie z siedzibą w Mławie. Jesteśmy największym dostawcą części plastikowych (również odbudów) do telewizorów LCD, PDP i monitorów obecnego lidera na światowym rynku elektronicznym.

Do Excela przez długi czas nie mogłem się przekonać w związku z moją humanistyczną proweniencją i niechęcią do matematyki. To jednak przeszłość. Od czasu objęcia przeze mnie stanowiska kierowniczego Excel towarzyszy mi praktycznie przez cały dzień. Tworzę mniejsze lub większe arkusze, które kalkulują i dostarczają informacji potrzebnych w zarządzaniu firmą.

W naszej firmie brak jest zintegrowanego systemu zarządzania produkcją i zapasem magazynowym, chociaż dział księgowości korzysta z oprogramowania komputerowego. W związku z tym wymiana danych odbywa się poprzez arkusze Excela. Umieszczenie jednego wielopoziomowego arkusza na serwerze lokalnym pozwala na jednoczesną edycję i zapis arkusza przez wielu użytkowników w pewnym sensie spełniając zadanie systemu komputerowego. Nie jest to jednak jedyny sposób na "zaprzęgnięcie" Excela do pracy fabryce.

Zapewne wielu z nas w czasie pracy korzysta z Excela, wiele osób tworzy arkusze, być może bardzo skomplikowane. W tym BLOGU chciałbym podzielić się z resztą świata kilkoma projektami, nad którymi kiedyś pracowałem bądź pracuję. Może komuś z Was przydadzą się moje pomysły, może ktoś stworzy o wiele lepsze ich wersje i będzie łaskawy podzielić się nimi ze mną.