Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.pl
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Excel - rozwijana lista rzeczy w dwóch komórkach

12 Aug 2014 09:01 7425 62
  • Level 17  
    Witam
    Czy ktoś z Was zna sposób żeby w excelu wykonać następującą rzecz:

    W komórce A1 chce żeby była do wyboru lista rzeczy np. jabła, gruszki, śliwki.
    Natomiast w komórce B1 były do wyboru ceny do tych rzeczy.
    Problem stanowi żeby oba pola A1 i B1 były ze sobą powiązane czyli. Jak wybiorę z pola A1 gruszkę żeby od razu wskoczyła cena tej produktu w polu B1.
    Osobno to robiłem to w sposób "rozwijana lista" ale wówczas trzeba osobno wybierać A1 i osobno w B1.

    Czy ktoś z Was wie jak zrobić (jeśli w ogóle jest to możliwe) żeby powiązać te dwie rzeczy.

    dziękuje
    [30.03.2021, darmowy webinar] Nowoczesna diagnostyka maszyn, monitorowanie i przewidywanie awarii. Zarejestruj się
  • Level 17  
    Dziękuje za tak szybką odpowiedź :)
    Buczo 11 chodziło mi o coś takiego właśnie, a byłbyś tak miły o powiedział jak żeś to zrobił???

    Dodano po 3 [minuty]:

    Już nie trzeba wiem jak to zrobiłeś, dziękuje :)

    Dodano po 5 [minuty]:

    Jednak mam problem. Czy zdefiniowanie które zrobiłeś cena oraz owoc to w przypadku ceny jest potrzebne? bo w przypadku owoc widzę sens tworząc listę rozwijaną, ale nie za bardzo widzę dot. ceny.
  • Level 28  
    dominikzw wrote:
    bo w przypadku owoc widzę sens tworząc listę rozwijaną, ale nie za bardzo widzę dot. ceny.

    Tak, racja. Nie potrzeba definiować nazwy "cena"-chyba, że przyda się jeszcze gdzieś.
  • Level 17  
    Dziękuje jak to czytałeś to akurat doszedłem sam przez definiowanie nazw :)
    ale jeszcze jedno czy zdefiniowanie które zrobiłeś do ceny jest też konieczne żeby wszystko dobrze działało?? bo skasowałem je i nie widzę różnicy żeby coś źle działało.
  • Level 28  
    dominikzw wrote:
    bo skasowałem je i nie widzę różnicy żeby coś źle działało.

    Samo skasowanie nazwy (metodą DELETE) nic nie zmieni, ten obszar nadal nazywa się "owoc", chyba, że podświetlisz wszystko i zrobisz "wyczyść wszystko".
    To zdefiniowanie nazwy listy jest potrzebne, jeśli w tworzeniu listy rozwijanej odwołujesz się do danych w innym arkuszu.
    Aby zmienić nazwę w "pole nazwy" dla zakresu komórek należy:
    1. Podświetlić zakres komórek.
    2. Wybrać kolejno: Wstaw-Nazwa-Definiuj.
    3. Teraz można wykasować i dodawać nazwy dla zakresu podświetlonych komórek.
  • Level 17  
    ok teraz wszystko rozumiem:)
    mogę mieć jezscze jedną prośbę czy Tworzyć osobny wątek?

    Dodano po 1 [godziny] 5 [minuty]:

    a na to miałbyś jakiś pomysł ??
    Excel - rozwijana lista rzeczy w dwóch komórkach

    plik w załączeniu
  • Level 17  
    uruchomiłem plik i nie jest to o co mi chodziło. Interesowałby mnie żeby w wierszu 1 wybieralo się z pola rozwijanego dany miesiąc czyli lipiec albo sierpień, wrzesień itp. Po wyborze danego miesiąca komórki w wierszach poniżej czyli 2-3 same automatycznie zmieniaja kolor tam gdzie jest dzień wolny (jeśli z kolorami odpada to żeby zamiast cyfry było napiasne sob. niedz.
  • Helpful post
    VBA, Excel specialist
    A jakby tak trochę własnej inwencji? W pliku przykładowym nie było listy rozwijanej, więc skąd miałem wiedzieć, że ma być?
    W liście powinien być nie tylko miesiąc, ale i rok. Oczywiście można to rozwiązać również inaczej, ale rok gdzieś musi być.
    Formatowanie jest ustawione w drugiej tabelce. W trzech ostatnich dniach dodałem jeszcze formuły sprawdzające, czy dany dzień jest w kalendarzu.
    Pozdrowienia
  • Level 17  
    Dziękuje, jakbym wiedział jak do tego tematu podejść to bym jakoś podpowiedział, ale nie chciałem namieszać więc tylko napisałem. Jak źle to odebrałeś to Cię bardzo przepraszam za to.
    A zdradzisz jak to zrobiłes?? ze jak się zmieni miesiąc i rok to kolory same się zmieniają??

    Ok już widzę formatowanie warunkowe

    Dodano po 34 [minuty]:

    Tak analizuje to co napisałeś Macieju i masz rację że bardzo dobrą rzeczą byłoby np. uwzględnienie roku osobno.

    Dodano po 16 [minuty]:

    próbuje rozdzielić żeby osobno w jednej komórce był do wyboru rok a w drugiej miesiąc, ale wychodzi mi bzdura. Czy byś mógł zmodyfikować?
  • Level 17  
    Tak jest jest jest super. Powiem szczerze probuje przenieść formułę którą napisałeś na nowy arkusz i coś robię źle bo ciągle wychodzi mi arg....formuła jest identyczna jak u ciebie, format komórek też a mimo to wychodzi arg....

    Dodano po 8 [minuty]:

    Możesz powiedzieć co robię źlę???
  • VBA, Excel specialist
    Nie wiem w którym miejscu jest ten błąd. Ale ogólnie: wzorzec do listy rozwijanej jest w kolumnie W, to też trzeba przenieść wraz z formatowaniem, być może ręcznie trzeba zmodyfikować listę w definicji poprawności danych. Jeszcze trzeba sprawdzić zakresy w formatowaniu warunkowym, czy są poprawnie określone, i czy w formule formatowania warunkowego jest odwołanie do pierwszej (lewej górnej) komórki tego zakresu.
    Poza tym jest takie przydatne narzędzie jak Inspekcja formuł, które pozwala śledzić krok po kroku, jak obliczana jest formuła i zobaczyć w którym momencie i dlaczego pojawia się błąd (dotyczy tylko formuł w komórkach).

    W trakcie pisania tego postu pojawił się załącznik i już wiadomo o co chodzi. W oryginalnym arkuszu miesiąc był wyświetlany jako fragment daty za pomocą odpowiedniego formatowaniu. W nowym pliku lista miesięcy jest listą tekstową, więc numer miesiąca trzeba odczytać w inny sposób.

    Pozdrowienia
  • Level 17  
    a czy przypadkiem w Twoim arkuszu nie dodałeś nowych formatów daty? bo jak uruchamiam swój to tych nie mam (mmmm oraz [$-415]mmmm . Czy trzeba jakoś specjalnie dodać ten format daty czy wystarczy wkleić go w zakładce niestadardowe i powinno działać...

    Excel - rozwijana lista rzeczy w dwóch komórkach

    Dodano po 13 [minuty]:

    Sprawdziłem, jak skopiuje od Ciebie komórkę która z Twojego arkusza ma format daty mmmm to formuła działa, więc ja tworzę źlę format daty mmmm który nie istnieje.

    Jak prawidłowo dodać nowy format niestandardowy możesz powiedzieć??
  • VBA, Excel specialist
    Gdyby to było kopiowane, to format przeniósłby się automatycznie, ale ponieważ było pisane z ręki, to się nie przeniósł. Ale w tym nowym układzie jest niepotrzebny, bo jest inna koncepcja formuły.
    Aby dodać format niestandardowy, należy wybrać format/niestandardowy i z listy dostępnych formatów wybrać podobny, zaznaczyć go i wyedytować w polu edycji kodów formatu. Zamknąć OK. Można też oczywiście wpisać kod formatu z ręki, ale tu łatwo o pomyłkę.
  • Level 17  
    Już wiem co było źle i nie działało. w kolumnie gdzie były miesiąca ja miałem słownie wpisany miesiąc a musi być 2014-01-01
  • Helpful post
    VBA, Excel specialist
    Nie musi być tak jak ja zrobiłem na początku. Ja po prostu bazowałem na tym, co było w pliku przykładowym i starałem się, żeby jak najmniej zmienić. Ale sposobów rozwiązania jest wiele i ten drugi sposób też jest dobry, tylko musi być do niego dostosowana formuła. Wpisałem to w ostatnim załączniku. Proszę sprawdzić, bo nasze posty się zazębiają i może przez to coś jest niejasne. Proszę jeszcze raz spokojnie wszystko przeczytać.
  • Level 17  
    Już zrozumiałem zasadę i wiem co źle robiłem, dziękuje za te uwagi.

    Dodałem jeszcze jedną rzecz która nie chodzi zbyt płynnie tzn.
    Jak się wybierze dany miesiąc dokładnie mówiąc czerwiec 2014 to w komórce a6 pięknie zrobi się na czerwono bo jest to niedziela. Jednak chciałbym w każdej komórce stworzyć pole wybory (np. 8, 10, tekst1). Takie pole wyboru stworzyłem. Jak wybiorę cyfrę czyli 8 i 10 wszystko jest dobrze tzn. jak wybiorę inny miesiąc np. listopad 2014 (sobota) to komórka zmieni się na kolor fiolet, ale problem się pojawia w momencie gdy wybiore z pola wyboru "tekst1" wówczas w momencie wyboru miesiąca który ma w tym dniu wolne czyli listopad pole już się nie zaznacza...można jakoś to poprawić??
  • VBA, Excel specialist
    Nie bardzo rozumiem tę koncepcję. Komórki zawierały daty dzienne (numery dni), które służyły jako źródło danych formatowania warunkowego. Jeżeli do tych komórek wstawi się listy rozwijane, to chyba po to, żeby wybrać z nich jakąś wartość i wyświetlić ją w komórce. Ale co wtedy z tym numerem dnia? W ostateczności da się go wyliczyć na potrzeby formatowania warunkowego, ale tabelka będzie nieczytelna dla człowieka, który będzie na nią patrzył. Więc może lepiej zostawić w spokoju te numery dni, a listy rozwijane zrobić w komórkach poniżej?
  • Level 17  
    Moja myśl całkowita jest taka, żeby najpierw wybierało się miesiąc jaki ma być uzupełniany. Po wyborze miesiąca (tak jak jest teraz zrobione) zaznaczają się w kolorze dni wolne od pracy, ale docelowo chodzi oto żeby zamiast cyfr dni tygodnia pracujących było można wybrać z listy rozwijanej odpowiednie inne wartości (np. 8,10 oraz tekst1). Wszystko pięknie działa do momentu wyboru słowa tekst a nie cyfry. (Te numery dni są potrzebne tylko na początek żeby było wiadomo w jaki dzień jest sobota i niedziela. dni pracujace nie są potrzebne ale żeby po wyborze miesiąca można było wybrać to co napisałem np. 8, 10, tekst1.)

    Chyba że jedyny rozwiązaniem jest też to że każdy nowy miesiąc robić na nowym czystym arkuszu i wtedy nie będzie problemu.

    Niestety liste rozwijaną zrobić poniżej odpada, ponieważ ja tych dni kalendarzowych czyli wiersze 6 i 7 będę miał skopiowanych kilkadziesiąt razy poniżej dla każdej osoby.

    Podejrzewam że się raczej tak nie da zrobić jak planowałem, ponieważ jak zrobie w komórkach pole wyboru (8,10,tekst1) to problem stanowi wybranie też 8 i 10 jeśli w tym danym miesiącu wypadnie dzień wolny wówczas z wiadomych względów komórka zmienia kolor jako dzień wolny od pracy a tak naprawdę chodzi mi o ilość godzin przepracowanych w tym dniu i oprócz tego jest problem z sumowanie dni roboczych i godzin

    Ogółem chodzi o to jak jest teraz tylko ze wiadomo źle to działa (zrobiłem tylko w jednej komórce a6 pole wyboru). W pliku napisalem co mi przychodzi na myśl, może jakoś lepiej można to rozwiązać jak uważasz??
  • Helpful post
    VBA, Excel specialist
    Dzień dobry,
    Obawiam się, że to co Pan zaplanował jest dość ryzykownym rozwiązaniem (podatnym na błędy), chyba że to będzie wypełniane półautomatycznie tzn. w danym dniu ta sama kolumna, bo inaczej łatwo będzie pomylić kolumny. Ale Pan ma pewnie swoje doświadczenia i wprawę. Zrobiłem w ten sposób, że pierwsza tabelka umieszczona bezpośrednio pod wyborem miesiąca będzie zawierać numery dni i tam nie będzie się już nic wprowadzać. Będzie ona wzorcem formatu dla pozostałych tabelek, gdzie będzie Pan mógł sobie wstawić listy rozwijane, czy co tam Pan chce. Jak już Pan sobie przygotuje taką tabelkę, to potem można ją kopiować w całości w nowe miejsca, ale tak, żeby tabelka zaczynała się zawsze od wiersza parzystego i od tej samej kolumny co tabelka z datami u góry. Nie wiem, której wersji Excela Pan używa, jeśli 2003, to musi być zainstalowany dodatek Analysis Toolpak, jeśli wyższej to nic nie trzeba instalować.
    Proszę sprawdzić i gdyby coś nie działało sprawdzić wersję Excela.
    Pozdrowienia.
  • Level 17  
    To co pisałem wyżej to poradziłem sobie, jedynie została kwestia wyboru w polu 8 albo 10 i jak w tym miesiącu przypada dzień wolny - wówczas z automatu (ustawiony jest kryterium) komórka zmienia kolor na dzień wolny. Jest jakiś sposób żeby po wyborze z pola rozwijanego tej cyfry kolor komórka nie zmieniała?
    Probowałem dodać drugie kryterium ale to nie pomogło.

    To już jest nie aktualne
  • Level 17  
    Tak jak Pan zrobił jest super, ale oczywiście mam problem (pamiętając żeby kopiowana tabela była w tej samej kolumnie i wierszu parzystym) żeby po skopiowaniu do mojego arkusza wyskakuje błąd "nie można używać odwołań do innych arkuszy lub skoroszytów dla kryteriów Formatowania warunkowego". a muszę zmienić odwołania do komórek gdzie jest rok i miesiąc bo mam w innym miejscu.

    Jak wejdę w tym arkuszu co wysłał Pan w formatowanie warunkowe komórki A6 mam ten sam komunikat czyli nic nie mogę zmienić...jakaś porada jak podejść do tematu??
    bo na wersji finalnej miesiąc mam w komórce N3 , rok w komórce F3 zaś jeśli to nie problem to kopiowanie zaczynałoby się od wiersza nieparzystego (ale jeśli to problem to dodam w nagłówku coś jeszcze i będzie od parzystych).

    Dodatek analysis toolpak jest dodany

    Czy może prościej i szybciej dla Pana byłoby jakby załączył mój plik finalny?

    Widzę że jest zdefiniowana nazwa zakres oraz zakres1, ale i tak nie widzę co jest nie tak że excel nie daje mi szans na zmienienie czegokolwiek w formatowaniu warunkowym
  • VBA, Excel specialist
    Rozumiem, że Excel 2003.
    Co do sumowania dni i godzin, proszę napisać co dokładnie ma być sumowane, bo nie wiem jak w tym kontekście traktować wpisy "u" i "ch".
    Co do rozpoczynania tabelki w wierszu parzystym czy nieparzystym, to nie ma problemu, to jest tylko kwestia zamiany funkcji ISODD na ISEVEN, byle było zawsze tak samo.
    W tej chwili zrobiłem tak, że listę rozwijaną można rozwinąć tylko w dniu roboczym, ale to nie jest mechanizm doskonały w sensie zabezpieczenia przed błędem, bo można w dniach wolnych wpisać z ręki co się chce, a w dniach roboczych można wyczyścić wpis i zostawić pustą komórkę.
    Poza tym jak się zmieni miesiąc, to stare wpisy pozostają.
    Najlepiej proszę załączyć plik finalny, bo nie wiem co tam Panu z tym kopiowaniem nie wychodzi. Jak się skopiuje wszystko co potrzeba, to nie powinno być problemów.
    Co do wydajności pracy w takim arkuszu to może być z tym problem, jak będzie dużo tych tabelek, to listy rozwijane w każdej komórce zmniejszają wydajność, a w sumie są to 4 proste i krótkie wpisy, myślę że wygodniej byłoby wpisywać to z ręki.
    Pozdrowienia
  • Level 17  
    W załączeniu przesyłam plik.
    Co miesiąc byłoby robione wszystko na czystym formularzu., właśnie żeby uniknąć błędów oraz żeby nie zapomnieć zmienić w danej komórce.

    Tak jak Pan teraz zaproponował czyli blokowanie rozwinięcia w dzień wolny jest dobrym rozwiązaniem, wiem że można del zrobić no ale to już nie da się uniknąć.
    (zdradzi Pan jak Pan zablokował w dzień wolny komórke??)

    Ilość dni przepracowanych mam na myśli żeby liczył dni tylko te w których jest liczba godzin , jeśli jest u-urlop albo jakaś inna litera (jaka jeszcze nie myślałem) żeby tej komórki nie brał pod uwagę.
    Ilość godzin pracy to sumowanie ilości godzin które są w danym miesiącu (czyli sumowanie cyfr 8,10, 9, 6, itp.)

    Ilość nadgodzin czyli suma godzin która przekracza ponad 8. czyli jak było pracowane 9 to liczba nadgodzin wynosi 1.

    Przeanalizowałem wariant z blokowaniem komórek w dni wolne, to faktycznie można z ręki wówczas wpisać co sie chce, ale jak jest pole rozwijalne to wtedy z ręki się nie da. Więc wariant z blokowaniem komórek odpada, lepsza jest jak w każdej jest pole do wyboru i poprostu tam gdzie jest kolor nie wolno tykać tej komórki - tak uważam
  • VBA, Excel specialist
    Jeszcze nie zaglądałem do Pana pliku, ale po przeczytaniu tekstu listu mam wątplwość, czy się rozumiemy. Ja nie sugeruję rezygnacji z kontroli poprawności, tylko z list rozwijanych. Można kontrolować to, co jest wpisywane z ręki i wtedy nawet łatwiej jest zabezpieczyć te komórki świąteczne. Tylko musi Pan precyzyjnie określić jakie wpisy są dozwolne. Dotąd np. mogło być z listy tylko 8 lub 10 godzin, teraz pisze Pan w swoim ostatnim poście, że może być 9 albo 6 godzin. A może 8,5 lub coś w tym stylu?
    Proszę to przemyśleć.
  • Level 17  
    Wole żeby listy rozwijane zostały (dobrze byłoby jakbym mógł edytować je bo nie wiem czy coś nie przyjdzie mi do glowy jeszcze, ale na chwilę obecną żeby z listy wyboru żeby były takie rzeczy, 8, 10, u, ch, nn, ut, święto). Wole żeby były listy rozwijane żeby inne osoby które będą korzystać nie kombinowały i wybierały tylko to co jest do wyboru.

    Dodano po 16 [minuty]:

    Porównuje te dwa pliki od Pana plik_dniwolne 3 oraz plik_dniwolne 4.
    Wpis w dzień roboczy można wyczyścić wszędzie (w jednym i drugim pliku) ale wiadomo że musi być coś wpisane więc ten błąd byłby szybko wychwycony - to akurat nie problem.
    Zastawiam się co jest lepsze w dni wolne. Czy zablokowanie komórki że nie jest nic listy do wyboru ale za to można przez przypadek z ręki wpisać cyfrę czy zostawić pole wyboru które zagwarantuje że nic z ręki nie będzie wpisane ale trzeba za to pamiętać żeby nic z tego pola nie wybierać. Więc wybieram mniejsze zło czyli żeby zostało pole do wyboru.

    Bo rozumiem że nie ma możliwości takiej żeby zablokować komórkę wolną od pracy i żeby nie można było nic z ręki wpisać??

    Dodano po 1 [godziny] 56 [minuty]:

    Udało mi się samemu dojść w sprawie prawidłowego zaznaczania kolorami w dni wolne - to co nie udało mi się wcześniej z Pana pliku skopiować, ale na podstawie komendy: DZIEŃ.TYG(DATA($Q$1;MIESIĄC($F$1);PRZESUNIĘCIE(A16;WIERSZ(zakres1)-WIERSZ(zakres)+ISODD(WIERSZ(zakres));)))=1

    Teraz analizuje plik_dniwolne4 i widzę że wszystko zostało ujęte w menedżerze nazw.

    Pozmieniałem w swoim pliku finalnym i obecnie wygląda tak (plik1), szczerze mówiąc wszystko działa tak jak trzeba jakoś nie zauważyłem żeby coś było nie tak. Wiadomo jeden minus jest tak że trzeba pamiętać żeby w dzień wolny nie wybrać zpola wyboru.
    Ilość dni pracujących sumuje dobrze
    Ilość godzin pracy sumuje dobrze
    Bazowałem na wcześniejszych Pana rozwiązaniach a nie na tych z ostatniego pliku gdzie jest wszystko umieszczone w menedżerze nazw, dlatego że łatwiej jest mi modyfikować i jak coś się "pokręci" to łatwiej mi znaleźć błąd. Chyba że sa jakieś powody/przesłanki dla których lepiej bazować na rozwiązaniach które umieścił Pan właśnie w tym pliku 4.

    ale za to nie wiem jak zrobić zeby liczył ilość godzin nadliczbowych (czyli to co jest powyżej 8 ale różnicę czyli jak ktoś ma 10 w jeden dzień to żeby wskazywał 2). Miałby Pan jakiś pomysł na to?
    Jeśli będzie Pan coś zmieniał w tym pliku teraz to proszę też o info żebym wiedział czy coś jeszcze było zmienianie.
    dziękuje