Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

EXCEL - formuła daty

28 Sty 2009 21:54 33864 30
  • Poziom 15  
    Potrzebuję pomocy do stworzenia formuły związanej z datą. Mam rejestr umów i chciałbym tak skonfigurować komórkę aby na 60 dni przed końcem przypisanej daty robiła się na zielono a po przekroczeniu daty na czerwono. Dawało by mi to informację o zbliżającym się końcu umowy oraz o jej przekroczonym terminie. Z góry dziękuję za wszelką pomoc.
  • Poziom 22  
    Poszukaj w pomocy hasła "formatowanie warunkowe"
    W office 2003 już powinno dać się z robić, w 2007 raczej na pewno.
    Jeśli nie tym to pozostaje jakaś prosta funkcja w VB.
  • Poziom 15  
    Właśnie to czytam i jestem z tego głupi gdyż nie miałem do czynienia jeszcze z tworzeniem formuł. Nie rozumiem gdzie mam mieć kod DZIŚ (w której komórce). Np. w mojej komórce A1 jest pewna data 01.02.2009 i chciałbym ją tak sformatować formułą aby cały czas na 60 dni wcześniej zaznaczona była na zielono, a gdy data ta pokryje się z datą dnia i ją przekroczy zaznaczona by była na czerwono. Niby proste ale nie wiem jak wpisać tę formułę i jak to ma się do tej komórki z kodem DZIŚ. Jeśli ktoś ma cierpliwość odpowiedzieć mi dla niektórych na tak banalne pytania to z góry dzięki.
  • Pomocny post
    Poziom 40  
    Może to ci trochę rozjaśni. Chodzi o formatowanie warunkowe. W moim przypadku posłużyłem się dodatkowymi komórkami pozwalającymi wyliczyć dane wartości ale możesz je pominąć wpisując odpowiednio zmodyfikowane warunki formatowania (np. tak jak w poście podanym przez adamas_nt).
    Dla zrozumienia stań na komórce (zaznacz komórkę) a2 (lub a3,a4,a5) - menu Format - Formatowanie warunkowe... Zwróć uwagę na warunki a w szczególności na to że nie posiadają znaku $ co pozwoli je skopiować dla kolejnych komórek (w dół).
    Mam nadzieje że skąd się wzięły daty w danych kolumnach zrozumiesz.

    Jeżeli dalej masz problem to pisz.

    Ps. excel inaczej zapisuje daty -s tandard to 2009-01-02
  • Poziom 15  
    Niestety nie umiem tego zastosować. Daje wycinek tego co bym chciał mieć zrobione tzn. pola i daty w chwili obecnej powinny tak sygnalizować mi terminy niestety to tylko wypełnienie komórki bo nie wiem jak te pola sformatować formułą.
  • Pomocny post
    Moderator Programowanie
    Formatowanie warunkowe: Pierwszy warunek: D2 czerwone, jeśli data mniejsza od dzisiejszej.
    Podał Ci to już marek003. Wpisujesz warunek:=JEŻELI(D2<=DZIŚ();PRAWDA;FAŁSZ)
    Drugi warunek: D2 zielone, jeśli data większa od dziś i mniejsza od dziś+60. Wpisujesz warunek:=JEŻELI(ORAZ(D2>=DZIŚ();D2<DZIŚ()+61);PRAWDA;FAŁSZ)
    Obie formuły wpisujesz dla formatowania warunkowego komórki D2. Następnie klikasz prawym pm w D2, wybierasz: kopiuj. Następnie zaznaczasz zakres, dla którego formatowanie ma działać (D3:D10 w Twoim arkuszu), klikasz prawym pm i wybierasz: wklej specjalnie>Formaty>OK i gotowe.
    Tak to wygląda w arkuszu:
    EXCEL - formuła daty
  • Poziom 15  
    Dziękuję za wszelką pomoc ale w miarę jedzenia apetyt rośnie :). Tak sporządzony arkusz poprzerabiał mi kolega ale nie działa tak jak sobie wymyśliłem. W kolumnie E chciałbym mieć 6 informacji (3 informacje na podstawie kolumny C, a 3 pozostałe informacje na podstawie kolumny I. 3 pierwsze informację określają datę (kolumna E) zakończenia i przekroczenia terminu (PO TERMINIE), zbliżaniu się na 60 dni przed końcem terminu (PODPISZ UMOWĘ) oraz pozostałym okresie (JESZCZE CZAS). Chciałbym aby to się zmieniło po wpisaniu w kolumnie G nr aneksu przedłużającego umowę na kolejny okres który określi kolumna I. Data zawarta w kolumnie I chciałbym żeby dawała informację do kolumny E o dacie zakończenia i przekroczenia terminu aneksu (ANEKS - PO TERMINIE), zbliżaniu się na 60 dni przed końcem terminu aneksu (ANEKS - PODPISZ UMOWĘ) oraz pozostałym okresie (JESZCZE CZAS). Sygnalizacja po tych 3 stanach określają kolory komórek które dobrze działają w pierwszym przypadku ale nie działają przy aneksie.
  • Pomocny post
    Moderator Programowanie
    Czyli:
    Jeżeli pusta G3 to:
    Jeżeli C3 mniejsze od dziś to: "Po terminie"
    Jeżeli C3 większe od dziś i mniejsze od dziś+60 to: "Podpisz"
    W pozostałym przypadku: "Jeszcze czas"
    =JEŻELI(CZY.PUSTA(G3);JEŻELI(C3<DZIŚ();"PO TERMINIE";JEŻELI(ORAZ(C3>=DZIŚ();C3<DZIŚ()+60);"PODPISZ UMOWĘ";"JESZCZE CZAS")))
    Jeżeli nie pusta G3 to: to samo tylko z I3 + "Aneks"
    Razem:
    =JEŻELI(CZY.PUSTA(G3);JEŻELI(C3<DZIŚ();"PO TERMINIE";JEŻELI(ORAZ(C3>=DZIŚ();C3<DZIŚ()+60);"PODPISZ UMOWĘ";"JESZCZE CZAS"));JEŻELI(I3<DZIŚ();"ANEKS - PO TERMINIE";JEŻELI(ORAZ(I3>=DZIŚ();I3<DZIŚ()+60);"ANEKS - PODPISZ UMOWĘ";"ANEKS - JESZCZE CZAS")))

    Napisz czy działa. Czy dzisiejszy dzień to :"Podpisz", czy "Po terminie"?
  • Pomocny post
    Poziom 40  
    :) Widzę że kolega załapał (co prawda nie do końca) ale już widać twórczość excelowską. Jestem mile usatysfakcjonowany. :) Połączył kolega moje rady i rady adamas_nt w swój własny działający twór.

    Ale racja, że z formatowaniem jeszcze musi kolega poćwiczyć.
    w załączeniu gotowy (czego nie lubię) projekt. Jednak nie wiem jak to lepiej wytłumaczyć. Proponuje zobaczyć na własne oczy sformułowanie warunków w formatowaniu warunkowym. To nie jest trudne. Trzeba tylko pamiętać że dana funkcja by spełnić warunek musi dać prawdę, w innym przypadku fałsz.

    Dodane po czasie

    ----------------------------------------
    Widze że kolega adamas_nt też już zerknął :)

    Co prawda ważne jest jeszcze pytanie (zadane przez adamas_nt):
    "Czy dzisiejszy dzień to :"Podpisz", czy "Po terminie"?"
  • Poziom 15  
    Wielkie dzięki. Wy to macie głowy. Ja dopiero raczkuje z formułami i formatowaniem warunkowym aczkolwiek jest to fantastyczna sprawa. Zaczynam wchodzić w średni poziom Excela. Pozdrawiam i dziękuję. Na pewno się jeszcze odezwę.
  • Poziom 15  
    Żebym mógł zakończyć to co namieszałem chciałbym prosić jeszcze o jedną rzecz: pomoc przy sporządzeniu formuły w kolumnach „I”,”J”. Chciałbym mieć informację w kolumnie „I” po wpisaniu daty wyjścia umowy o jej nie dostarczeniu jeżeli data zostanie przekroczona o 30 dni (30 dni to czas na podpisanie i odesłanie umowy z powrotem od kontrahenta) wtedy komórka zaznacza się na czerwono. Jeżeli zostanie cokolwiek wpisane w kolumnie (komórce z kolumny „J – docelowo data otrzymania umowy) to komórka z kolumny „I” zaznacza się na zielono (informacja o umowie otrzymanej od kontrahenta). Z góry dziękuję za kolegom za pomoc przy wykonaniu całego arkusza.
  • Pomocny post
    Poziom 40  
    A byłem pewny że załapałeś zasadę. :(

    W czym konkretnie masz problem?
    Stwórz na chwile w bocznej komórce odpowiednio liczącą formułę tak by dawała w wyniku prawdę lub fałsz. Zgodnie z twoją koncepcja po 30 dniach ma być "prawda" więc wykorzystaj jeżeli, dziś() itd. Sprawdz czy działa w tej komórce. Następnie skopiuj całą formułę i wklej do formatowania warunkowego komórki dla której to obliczałeś. Ustaw kolor i już.
  • Poziom 15  
    Powiem szczerze nie umiem tego powiązać ponieważ chciałbym żeby kolumna J była w chwili wpisania w kolumnie I daty pusta, następnie jak będę wpisywał w tej komórce datę otrzymania umowy to kolumna I będzie zmieniać się na zielono i tak pozostać.
  • Pomocny post
    Poziom 40  
    Trochę cię podprowadzę. (Bo nie koniecznie trzeba używać jeżeli ale dla zrozumienia warto)

    Stajesz na komórce z datą ( I3 ) Format - formatowanie warunkowe
    Pierwszy warunek
    Jeżeli komórka jest pusta pozostaw bez formatowania
    Wybierasz formuła jest... i tu wpisujesz funkcję sprawdzającą = czy.pusta(I3)
    Pozostawiasz bez formatowania

    Drugi warunek
    Jeżeli komórka J3 jest zapełniona daj zielony
    Znów wybierasz formuła jest... i tu wpisujesz ... funkcję sprawdzającą czy komórka j3 ma jakieś dane (spróbuj napisać sam tą formułę - jeżeli naprawdę nie dasz rady to podpowiem)
    Format ustawiasz zielony
    Trzeci warunek
    Jeżeli dziś jest więcej niż 30 dni od daty w I3 daj czerwony
    Znowu formuła jest ... i od daty dzisiejszej odejmij tą z komórki I3 i sprawdz czy jest większa od 30
    Format ustawiasz czerwony

    Daj OK.

    Skopiuj formaty w dół i już.

    Dodano po 2 [minuty]:

    A już ci podpowiem bo idę do domu.

    Drugi warunek:
    =J3<>""

    Trzeci warunek
    =DZIŚ()-I3>30

    Kolejność warunków bardzo ważna.
  • Poziom 15  
    Dziękuję i pozdrawiam. Czy kolega ma jakieś pomoce do nauki Excela i co by kolega polecał do godnej uwagi ?
  • Poziom 40  
    Przyznam że excela uczyłem się na żywca (zaczynałem bodajże od Excel v2.0).
    Jak coś trzeba było znaleźć to wciskałem fx (chociaż nie wiem czy już wtedy to było w tej wersji jak teraz, ale na pewno coś podobnego np poprzez wstaw) i szukałem funkcji a poprzez pomoc do tej funkcji i przykłady tam zawarte tworzyłem własne obliczenia.

    EXCEL - formuła daty

    Co prawda same podstawy arkusza (zasady działania i postępowania z komórkami) liznąłem na podstawie dosowej wersji Quattro Pro na studiach.

    Później (jak internet) stał się bardziej popularny korzystałem z różnych kursów i przykładów ale na zasadzie przejrzenia i uświadomienia jakie nowe ciekawostki oferują kolejne wersje.

    Proponuje wpisać w googlach "kurs excela" i coś wybrać. Osobiście nie polecę ci żadnego bo jak pisałem jeżeli już to przeglądam je bardziej by znaleźć jakieś ciekawostki o których jeszcze nie wiem (do tej pory nie miałem potrzeby z tego korzystać - jak np z obliczeń statystycznych które wyliczałem na kartce mimo że excel też to niby potrafi).
    Z drugiej strony najlepiej się uczyć excela jeżeli się ma coś samemu zrobić i samemu do tego dojść. Wtedy jest ta satysfakcja i duma że umiem i panuje nad tym narzędziem (przynajmniej w zakresie w którym wykorzystałem tego excela).
    Może kolega adamas_nt ma sprawdzone dobre kursy.
  • Moderator Programowanie
    W zasadzie do pewnego momentu mógłbym przepisać powyższe :)
    Też jestem samoukiem. I z tego powodu widać na pewno różnice w konstruowaniu formuł. Zaraziłem się Excelem po przeczytaniu artykułu w "Bajtku". Kto to jeszcze pamięta?
    Polecam przeszukać internet. Na stronach Microsoftu też jest sporo na temat Excela i Accessa i wszystko poparte przykładami. Jest też kilka forum poświęcone tylko Excelowi Np: http://www.excelforum.pl/ Znajdziesz tam sporo ciekawych rozwiązań...
  • Poziom 20  
    Proponuję dodatkowo pobawić się accesem zabawa jest znacznie lepsza, reguły uruchamiane przyciskiem, widok po kolei rekordów. Jest nawet prostszy niż excel robisz aplikację wizualnie.
  • Moderator Programowanie
    piochuxl napisał:
    Proponuję dodatkowo pobawić się accesem zabawa jest znacznie lepsza, reguły uruchamiane przyciskiem, widok po kolei rekordów. Jest nawet prostszy niż excel robisz aplikację wizualnie.

    Tyle, że Access jest w wersji Profesional, która jest (niestety) znacznie droższa.
    A'propos zabawy w daty. Jedna z ciekawostek Excela:
    Pyt.1. Jak obliczyć kiedy w danym roku wypada Wielkanoc (pierwsza niedziela po pierwszej pełni księżyca po przesileniu wiosennym)?
    EXCEL - formuła daty
    Odp (format komórki wynikowej "dd-MM-rrrr"):
    Code:
    =ZAOKR.W.DÓŁ(DATA(A1;5;DZIEŃ(MINUTA(A1/38)/2+56));7)-34

    Pyt.2. Jak działa ta funkcja?
  • Poziom 20  
    Cytat:
    Tyle, że Access jest w wersji Profesional, która jest (niestety) znacznie droższa.
    A'propos zabawy w daty. Jedna z ciekawostek Excela:
    Pyt.1. Jak obliczyć kiedy w danym roku wypada Wielkanoc (pierwsza niedziela po pierwszej pełni księżyca po przesileniu wiosennym)?


    Można zainstalować triala 90 dniowego office 2007 enterprice. Instalujemy Tylko Acces i git.
  • Poziom 40  
    Akces to baza danych a excel to arkusz kalkulacyjny. Mają inne zastosowania. Excel też potrafi to co akces (przy wykorzystaniu Query) tylko zastosowanie tego jest bardziej pracochłonne niż w akcesie. W drugą stronę jest to samo. Zbudowanie prostych obliczeń w akcesie wymaga więcej pracochłonności niż w excelu.

    A jeżeli w 90 dni nauczysz się porządnie posługiwać akcesem (mając pewność że to co ci pokazuje w wyniku jest prawidłowe) to gratuluje zdolności. I druga rzecz: gdzie tą wiedzę wykorzystasz po tych 90 dniach?

    :) A z tą Wielkanocą to ciekawe. :)

    Funkcja działa bo .. działa :) Najciekawsze zastosowanie ma funkcja MINUTY.
    :) Główne pytanie to: Jak ktoś na to wpadł? :)

    Przesilenie to 22.marca. W roku jest 13 pełni księżyca. pełnia jest co 28 dni. Lecz jaka jest zasada (jeżeli jest) "występowania" pełni w stosunku do daty (roku)? (niestety nie znam jej). Po tych wyliczeniach znalezienie niedzieli to już betka.

    Jednak tu ktoś zastosował jakieś jeszcze inne zasady bo wykorzystał najpierw dodawanie, zaookrąglenie a potem odejmowanie od daty.

    W szczególności znalezienie czegoś takiego:
    MINUTA(A1/38 )/2+56
    Podziwiam :)
  • Moderator Programowanie
    No właśnie... Od dłuższego czasu próbuję to rozgryźć. Bezskutecznie. Ciągle wydaje mi się, że kluczem jest data 01-01-1900, od której zaczyna się liczenie dat w Excelu. Próbowałem inaczej: znalazłem w kalendarzu kiedy była pierwsza pełnia po tej dacie (również 01-01-1900). Pełny cykl faz księżyca to 29,5 doby. Należy dodać 1 co 4 lata i 1 co 1000 lat i... Za nic mi to nie wychodzi :). Ciekawe jest też rozwiązanie w VBA. Zupełne czary-mary...
  • Poziom 40  
    Nie mam teraz za dużo czasu ale na szybko znalazłem coś takiego:

    Jak obliczyć kiedy pełnia

    Z tym że tu też nie ma odwołania do głównej zasady (wytłumaczenia dlaczego)
  • Poziom 40  
    Z tą Wielkanocą tu jest wytłumaczenie.

    Kiedy wypada Wielkanoc

    :) I już wszystko jasne... :) :) :)

    :) Kurcze... czytam 3 raz. :) Wydaje mi się że już zaraz załapie. :)

    A tu jeszcz 3 inne algorytmy (trzeci jest z nich "podobno" najlepszy).
    Wielkanoc
  • Moderator Programowanie
    Eeee, taaaak. Rzeczywiście proste... :)
  • Poziom 20  
    Cytat:
    Akces to baza danych a excel to arkusz kalkulacyjny. Mają inne zastosowania. Excel też potrafi to co akces (przy wykorzystaniu Query) tylko zastosowanie tego jest bardziej pracochłonne niż w akcesie. W drugą stronę jest to samo. Zbudowanie prostych obliczeń w akcesie wymaga więcej pracochłonności niż w excelu.


    Rejestr- baza danych!! ja się nauczyłem w 2 tygodnie wystarczy zupełnie
  • Poziom 40  
    piochuxl napisał:

    Rejestr- baza danych!! ja się nauczyłem w 2 tygodnie wystarczy zupełnie


    No to gratuluję.
    A miałeś jakąś podbudowę teoretyczną lub działałeś w innych programach na relacyjnych bazach danych? Załapałeś to z miejsca?
    Relacje, typy sprzężeń, klucze podstawowe, 6 typów kwerend z możliwością ich mieszania, budowanie skomplikowanych kryteriów, tworzenie specyficznych raportów? I to wszystko bez wykorzystania kreatorów (które czasami zamiast pomagać utrudniają pracę) a tym bardziej bez podbudowy teoretycznej?

    Jeżeli tak - to jesteś wielki i "chylę czoła".
  • Poziom 20  
    najpierw access później mysql więc nauczyłem się na sucho bez jakiejkolwiek wiedzy. Ale acces jest naprawdę prosty.
  • Poziom 40  
    piochuxl napisał:
    najpierw access później mysql więc nauczyłem się na sucho bez jakiejkolwiek wiedzy. Ale acces jest naprawdę prosty.


    Mówimy o niezależnych tabelach - rejestrach czy o bazach danych (tabelach połączonych relacjami z różnymi typami sprzężeń)?

    Nie przeczę że access nie jest prosty. Po to jest żeby upraszczał szereg bardziej pracochłonnych rzeczy występujących w innych (starszych) programach bazodanowych. Myślę jednak że bez żadnej wiedzy nie stworzysz w nim w miarę porządnie funkcjonującej bazy danych (nie mówię tu o paru niezależnych tabelach z których będziesz tylko wyciągał dane a powiązania między nimi będziesz tworzył " na żywca" w samych kwerendach wyszukujących).
    Ale mogę się mylić. Doświadczenie mam tylko na sobie i na kolegach i koleżankach ze studiów gdzie tworzenie w pełni funkcjonujących baz danych (nie samych tabel ani kwerend) nie było takim prostym zajęciem. Ale kierunek moich studiów był daleki od informatyki tak że może mam za mało doświadczenia.

    Powtarzam więc jeszcze raz: jeżeli Tobie przyszło to tak łatwo to... czapki z głów.