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 - rozwijana lista rzeczy w dwóch komórkach

dominikzw 14 Sie 2014 08:14 6921 62
  • #31
    dominikzw
    Poziom 17  
    Udało mi się policzyć ilość godzin ponad 8, może nie jest to jakiś piękny wzór ale efekt jest prawidlowy. Jakby miał Pan jakieś uwagi albo sugestie co poprawić jeszcze to będę wdzięczny. Mi osobiście już nic nie przychodzi do glowy.
  • #32
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Trochę długo milczałem, bo miałem problemy z połączeniem Internetowym i jeszcze parę innych spraw. Ten wzór na godziny nadliczbowe jest dobry, tylko nie potrzeba tylu nawiasów, wystarczy tak:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Te formuły nazwane, które wprowadziłem miały na celu uproszczenie formuł sprawdzania poprawności (rozróżnienie dni wolnych), ale jeśli Pan zdecydował, żeby tego nie stosować, to może zostać tak, jak Pan zrobił. Jeśli korzystamy tylko z formuł poprawności danych nie da się zablokować skasowania danych. Nie da się również zablokować skopiowania innej komórki do komórki ze sprawdzaną poprawnością danych, co spowoduje anulowanie kontroli poprawności. Generalnie sprawdzanie poprawności ma zabezpieczać przed przypadkową pomyłką przy normalnej pracy, nie zabezpieczy, jeśli ktoś rozmyślnie będzie chciał coś popsuć. Większe możliwości daje zastosowanie Visual Basica. Poza tym przy poprawności danych jest taka opcja "zakreśl nieprawidłowe dane". Nie jest to doskonałe narzędzie, ale jak ma Pan dużo komórek z kontrolą poprawności, to czasem może pomóc znaleźć niewypełnione komórki (te, w których została data, a nie wpisano danej z listy).
    Pozdrowienia.
  • #33
    dominikzw
    Poziom 17  
    Rozumiem że opcja "zakreśl nieprawidłowe dane" nie sprawdza się w przypadku jeśli mam zrobione w ten sposób że w komórce mam pole do wyboru? Bo w tym pliku co jest powyżej jak się to opcje zaznaczy i wybierze prawidłową wartość to i tak komórka jest zaznaczona jako nieprawidłowe wartość
  • #34
    Maciej Gonet
    Poziom 33  
    Dlatego napisałem, że jest to narzędzie niedoskonałe. Ono nie reaguje od razu, jak formatowanie warunkowe. Trzeba je uruchomić ponownie, wtedy się wynik zaktualizuje. Czyli nie można go włączyć na początku i oczekiwać, że w miarę wprowadzania danych obwódki same będą znikać. Można wypełnić co jest do wypełnienia i dopiero pod koniec włączyć to zakreślanie. Ewentualnie można napisać w VBA obsługę zadrzenia Change, żeby po każdej zmianie w komórkach uruchamiać automatycznie to zaznaczanie. Ale i tak dni wolne będzie Pan miał zakreślone.
  • #35
    dominikzw
    Poziom 17  
    dziękuje :)
    A mam jeszcze pytanie, czy jest możliwość dodania rekordów w kalendarzu żeby zaznaczał np. na czerwono (tak jak niedziele) że jest święto np. 6.01, 15.08 itd? Tak żeby ten rekord można było modyfikować dodając samemu kolejne dni wolne od pracy
  • #36
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Dopisałem Święta jako zakres nazwany. Gdyby trzeba było dodać jeszcze jakieś święto, to trzeba poprawić definicję. Daty świąt można edytować. Święta są sprawdzane wraz z niedzielami, ten warunek powinien być sprawdzany jako pierwszy (w przeciwnym razie, jak święto wypada w sobotę, byłaby wyświetlana sobota, a nie święto). Mam nadzieję, że o to chodziło.
    Pozdrowienia.
  • #37
    dominikzw
    Poziom 17  
    Witam
    Tak oto chodziło, ale czy jest możliwość żeby to działało na tym pliku?? Bo wszystko mam ustawione pod ten...chyba że nie ma takich możliwości
  • #38
    Maciej Gonet
    Poziom 33  
    A czym te pliki się różnią? Ja przecież to wstawiłem do Pana pliku, a nie tworzyłem nowego. Ustawiłem tylko inny miesiąc, żeby było widać efekt.
  • #39
    dominikzw
    Poziom 17  
    Przepraszam bardzo źle spojrzałem. Pomyliły mi się pliki. Pytania tamtego nie było.

    Borykam się z innym problemem czy zna Pan sposób żeby wyszukiwanie pionowe działało w tym pliku ale przy zachowaniu kolejności kolumn jak jest? BO jak zamienię to wtedy działa.
    W polu G2 wybieram z pola rozwijanego nazwisko i chodzi o to żeby po wyborze wyskakiwało w polu F2 jego numer
  • #40
    Maciej Gonet
    Poziom 33  
    Funkcja WYSZUKAJ.PIONOWO działa w ten sposób, że szuka wartości zawsze w pierwszej kolumnie, a zwraca wynik z jednej z następnych kolumn. W sytuacji, gdy trzeba zwrócić wynik z kolumny wcześniejszej można posłużyć się kombinacją funkcji INDEKS i PODAJ.POZYCJĘ, chociaż w tym przypadku jeśli numeracja nazwisk jest kolejna, wystarczy funkcja PODAJ.POZYCJĘ.
    Jeśli koniecznie musi być WYSZUKAJ.PIONOWO, to można wirtualnie przestawić kolumny, np. za pomocą funkcji WYBIERZ, ale taka formuła jest dłuższa.
    Pozdrowienia
  • #42
    dominikzw
    Poziom 17  
    Witam
    Czy w załączonym pliku jest możliwość zrobienia następujących rzeczy:
    - czy jest możliwość zablokowania w dni wolne od pracy (kratki oznaczone kolorem) żeby nic z ręki nie można było wpisać
    - żeby domyślnie ale tylko w dni pracujące była wpisana wartość 8 (jako cyfra), ale też żeby można było z pola wyboru wybrać inną rzecze ewentualnie.

    dziękuje za pomoc
  • #43
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Obawiam się, że formułami to byłoby trudno osiągnąć.
    Można próbować za pomocą makr, ale należałoby dokładnie określić co te makra mają robić i w jakim zakresie komórek.
    Więc jeśli makra wchodzą w grę to trzeba określić: jak będzie określony zakres komórek, w którym makro ma działać.
    Rozumiem, że byłoby to jedno lub dwa makra - jedno inicjujące, które wstawiałoby wartość 8 do komórek odpowiadających dniom roboczym, a czyściłoby komórki odpowiadające dniom wolnym i dniom nieistniejącym. Poza tym to makro musiałoby ustawić sprawdzanie poprawności danych w dniach roboczych na listę, a w dniach wolnych na blokadę. Należy określić, w jaki sposob to makro wywoływać.
    Drugie makro ewentualnie mogłoby pilnować, żeby po inicjacji nie można było usunąć zawartości komórek, bo procedura sprawdzania poprawności tego nie zapewnia. Ale można z tego zrezygnować, jeśli pogodzimy się z tym, że zawartość komórki można skasować i zostawić pustą.
    Proszę się zastanowić, co jest dla Pana ważne i doprecyzować warunki.
    Pozdrowienia.
  • #44
    dominikzw
    Poziom 17  
    dziękuje za odpowiedź.
    Jeśli dobrze zrozumiałem to co Pan napisał to z drugiego makra zrezygnowałbym.
    Myśl moja jest taka.
    Wybieram miesiąc jaki mnie interesuje np. wrzesień. Kolorami zaznaczają się dni wolne na kalendarzu oraz na wierszach gdzie są osoby - to wzsystko działa.
    Jak to zostanie wybrane to myśl jest taka (może być makrem) żeby właśnie z automatu w dni pracujące (czyli dni nie zaznaczone kolorem) wskakiwała cyfra 8, ale żeby jednocześnie była możliwość wyboru w każdym w dniu roboczym inne opcji która jest dostępna teraz czyli np. 2,3,9,10 urlop itd). oraz jednocześnie żeby w dzień wolny (kolorowe pole) było całkowicie puste nic nie wpisane i żeby nic nie można było z ręki wpisać.
    W jaki sposób wywołać...hmmm najlepiej żeby automatycznie wskakiwało to wszystko zaraz po tym jak wybierze się miesiąc, jeśli jednak nie jest to możliwe to wstawić jakś przycisk który po wciśnięciu uruchomi to makro.
    Makro ma działać na komórkach D7:S12, na kalendarzu który jest D3:S5 nie
    Jest to jakoś do zrobienia?
  • #45
    Maciej Gonet
    Poziom 33  
    Jeśli chodzi o to makro inicjujące, to można zrobić przycisk albo skrót klawiszowy. Można by zrobić automatycznie po wprowadzeniu miesiąca, ale to na Pana odpowiedzialność - ja bym tego sposobu nie polecał, bo jak ktoś przypadkowo wejdzie w edycję miesiąca i nawet nic tam nie zmieni tylko naciśnie Enter, to cała tablica się zresetuje.
    Trzeba jeszcze określić w jakich komórkach makro ma działać. Makro to nie formuła - ono działa w całym arkuszu, więc trzeba dokładnie określić w których komórkach - nie musi to być konkretny adres, ale konkretny algorytm pozwalający ten adres określić.
    Widzę, że Pan dopisał adresy do S12 to będą tylko 3 osoby - tyle Panu wystarczy?
  • #46
    dominikzw
    Poziom 17  
    To wybieram przycisk szczerze mówiąc.
    A jak mam Panu określić w jakich komórkach ma działać?? Bo tego nie rozumiem szczerze mówiąc (D7:S12 ten zakres komórek mnie interesuje)

    Dodano po 2 [godziny] 18 [minuty]:

    Jeszcze jedna sugestia czy jest możliwość ustawienia tak makra żeby wypełniał komórki tylko wówczas jak jest wybrana osoba w komórce B7, B9 itd. bo ja mam taką listę na 3 strony i nie zawsze jest wypełniania osobami.
  • #47
    Maciej Gonet
    Poziom 33  
    Pytałem o zakres komórek, to napisał Pan, że wystarczy na 3 osoby, a teraz pisze Pan o jakichś 3 stronach, to proszę się zdecydować co Pan chce - napisałem, że trzeba precyzyjnie określić zakres działania makra.
    Makro to nie jest formuła, którą można sobie skopiować z jednej komórki do drugiej.
  • #49
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    A co z tymi brakującymi osobami, o których Pan pisał w poprzednim poście?
    Jak w kolumnie B nic nie jest wpisane, to komórki w zakresie dni miesiąca mają być puste i zabezpieczone przed wpisem, czy puste i niezabezpieczone?
    Rozumiem, że nie planuje Pan tych komórek wypełniać w danym miesiącu.
    Pozdrowienia
  • #50
    dominikzw
    Poziom 17  
    Już tłumacze. Lista dla jednej brygady liczy max 67 osób. Ale są miesiące że w danej brygadzie pracuje 13 osób a są miesiące że pracuje 45. Są nawet miesiące że jest brygada X i u niej w miesiącu lipcu było 40 osób a miesiąc później 45. WIęc nie ma żadnej reguły co do ilości.
    Proponowałbym że jeśli w kolumnie B nie wybierze się osoby to komórki w zakresie dni miesiąca niech będą puste ale niezabezpieczone. Bo jak ktoś coś wpisze tam to i tak kwota się nie wyliczy bo osoba ze stawką godzinową nie będzie wybrana.
    Reasumując: jeśli osoba nie będzie wybrana to komórek w zakresie dni miesiąca nie będą wypełniane, jeśli osoba będzie wybrana to trzeba uzupełnić.
  • #51
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    W załączniku plik z makrem, które powinno spełniać Pana założenia. Przy większej liczbie nazwisk makro będzie się wykonywało dość długo, bo tworzenie listy rozwijanej w każdej komórce jest dość czasochłonne. Proszę o cierpliwość.
    Pozdrowienia
  • #52
    dominikzw
    Poziom 17  
    Witam
    Dziękuje, jest super

    Dodano po 10 [minuty]:

    Czy jest możliwość żeby w dzień który nie istnieje w kalendarzu np. 29 luty, 30 luty, 31 luty, 31 wrzesień zamiast pustej kratki był "-" ?
  • #53
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Czy ta kreska ma być we wszystkich komórkach, czy tylko w tych, gdzie wybrano osobę.
    W tej chwili jest to we wszystkich komórkach.
    Jeśli ma być tylko tam gdzie wybrano osobę, należy usunąć dwie linijki kodu w końcowej części:
    Kod: vb
    Zaloguj się, aby zobaczyć kod

    Mam nadzieję, że to już wszystko.
    Pozdrowienia
  • #54
    dominikzw
    Poziom 17  
    Dziękuje za pomoc
  • #55
    dominikzw
    Poziom 17  
    Plik działa pięknie na office 2010, jednak na 2007 już przesuniecia kolorów nie działają. Porównałem wersje tam gdzie przesunięcie kolorów działa i tą najnowszą:
    W wersji najnowszej zmienił Pan komendę z tej:

    =(DZIEŃ.TYG(DATA($F$3;MIESIĄC($N$3);PRZESUNIĘCIE(D7;WIERSZ(zakres1)-WIERSZ(zakres)+ISEVEN(WIERSZ(zakres));)))=1)+LICZ.JEŻELI(Święta;DATA($F$3;MIESIĄC($N$3);PRZESUNIĘCIE(D7;WIERSZ(zakres1)-WIERSZ(zakres)+ISEVEN(WIERSZ(zakres));)))

    na

    =(DZIEŃ.TYG(DATA($F$3;MIESIĄC($N$3);PRZESUNIĘCIE(D9;WIERSZ(zakres1)-WIERSZ(zakres)+CZY.PARZYSTE(WIERSZ(zakres));)))=1)+LICZ.JEŻELI(Święta;DATA($F$3;MIESIĄC($N$3);PRZESUNIĘCIE(D9;WIERSZ(zakres1)-WIERSZ(zakres)+CZY.PARZYSTE(WIERSZ(zakres));)))

    różnica polega na usunięciu ISEVEN i wstawienie CZY.PARZYSTE.

    Czemu tak zostało zmienione nie może zostać komenda ISEVEN??
  • #56
    dominikzw
    Poziom 17  
    Już wiem czemu się zmieniło. Wersja 2010 sama automatyczynie zmienia komendę z ISEVEN na czy.parzyste. Jak się pracuje na wersji 2010 i się zapisze plik to już na 2007 nie działa.
  • #57
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Funkcja ISEVEN (CZY.PARZYSTE) pochodzi z dodatku Analysis Toolpak i w wersji 2007 została zintegrowana z arkuszem, ale pozostawiono jej starą angielską nazwę. W wersji 2010 ta nazwa została spolszczona. Efekt tego jest taki, że jak używa się Excela 2007, to należy używać nazwy angielskiej, a jak Excela 2010 lub 2013, to nazwy polskiej. Jeśli plik zapisany w wersji 2007 otworzy się w wersji 2010, to Excel sam dokona konwersji, ale odwrotnie już nie. Jeśli zależy Panu, żeby korzystać z tego samego pliku w wersji 2007 i 2010 można przerobić formuły tak, żeby nie zawierały tej funkcji. A jeśli korzysta Pan na danym komputerze z jednej wersji, to ja Panu przesłałem pierwotnie wersję 2007, potem Pan przesłał już inny plik w wersji 2010, więc ja tego nie zmieniałem, bo uznałem, że używa Pan wersji 2010.
    Jeśli chce Pan, aby działało w obu wersjach proszę zamienić ISEVEN(WIERSZ(zakres)) na (MOD(WIERSZ(zakres);2)=0)
    Pozdrowienia
  • #58
    dominikzw
    Poziom 17  
    Witam
    Nie wiedziałem wcześniej że excel taki bystry jest i sam zamienia. Zauważyłem to wczoraj wieczorem dopiero że coś nie gra a że sam zamienia dzisiaj dopiero. Dziękuje za funkcje mod. Skorzystam z niej bo pracuje raz na 2007 a raz na 2010.
    Pozdrawiam
  • #59
    dominikzw
    Poziom 17  
    Witaj
    Czy byłbyś w stanie pomóc mi usunąć ten problem??
    Jak wybierze się dowolny miesiąc z 2014 r., następnie daną osobę i wciśnie się uruchom to wówczas dni pracujące prawidłowo się wypełniają 8. Jednak jak się wybierze dowolny miesiąc z roku 2015 albo 2016 albo 2017 wówczas wszystko się rozjeżdża - dni wolne wypełniają się 8 zaś dni pracujące są puste.
  • #60
    Maciej Gonet
    Poziom 33  
    Dzień dobry,
    Istotnie w arkuszu był błąd, data w komórce N3 odnosiła się zawsze do roku bieżącego, a nie do roku wybranego w komórce F3. Poprawiłem to, ale nie wiem, czy taki wariant Pana zadowoli. W tej chwili arkusz 'dane do listy' jest powiązany z arkuszem 'czysty formularz'. Gdyby chciał Pan dodać nowy arkusz taki sam jak 'czysty formularz', to albo do każdego arkusza trzeba dołączyć też osobne 'dane do listy', albo wszystkie arkusze w skoroszycie muszą się odnosić do tego samego roku.
    Pozdrowienia