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.

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.

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

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!

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

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)