Elektroda.pl
Elektroda.pl
X
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

Excel - Bardziej skomplikowana wersja formatowania warunkowego

steelek56 15 Jan 2014 21:48 2505 16
  • #1
    steelek56
    Level 12  
    Witam.

    Kilka dni temu pozmieniało się trochę u mnie w pracy. Mianowicie ja dostałem kilka nowych obowiązków, które miała koleżanka, a ta z kolei dostała zadanie, które dotychczas było robione przez kierownika.

    Chodzi o planowanie dostaw mięsa do sieci sklepów. Sposób tworzenia tego planu jest dość prymitywny, ale z racji tego że nie ma wiele czasu, to nie ma czasu na jakieś większe udoskonalenia czy zmiany w systemie tworzenia planu. Więc póki co ona robi to tak samo jak dotychczas robił to kierownik. Przykładowy plik pokazujący mechanizmy działania takiego arkusza dodałem do zalączników.

    Sam arkusz nie jest skomplikowany. Skomplikowane jest wprowadzanie zmian w tym arkuszu w przypadku, gdy na daną trasę wychodzi więcej mięsa niż auto jest w stanie załadować (wtedy trzeba przesunąć jakiś sklep do innej trasy). A w podsumowaniu trasy usunąć cześć formuły odpowiedzialnej za "wagę" zamówienia z danego sklepu i tą część przypisać do innej trasy. W przykladowym pliku nie jest to trudne, ale robi się upierdliwe w momencie kiedy sklepów jest 400. W dodatku goni człowieka czas, a na dodatek co chwilę ktoś dzwoni bądź wchodzi do biura z pytaniem. Łatwo wtedy o pomyłkę. Znalezienie błędu w takim przypadku to prawie jak szukanie igły w stogu siana.


    I tu moje pytanie. Czy jest techniczna możliwość (np poprzez wykorzystanie makra) na wyeliminowanie głównego problemu - odnalezienie sklepu który się pomineło/zdublowało? Docelowo myślałem, aby w arkuszu X te sklepy, które nie zostały uwzględnione automatycznie oznaczały sie kolorem np niebieskim, a te które zostały policzone więcej niż raz kolorem np czerwonym. Moja znajomość excela zamyka się w korzystaniu z formuł dostępnych w programie. Makra to poza moimi wyobrażeniami (ale o tym za chwile jeszcze coś napiszę). Myślałem żeby użyć kombinacji funkcji licz.jeżeli, wyszukaj i może jeszcze kilku innych. Nie zagłębiałem się zbytnio póki co w to, bo wydaje mi się że będzie to skomplikowane a samo użycie do końca nie bedzie spełniać stawianych przeze mnie wymagań. Dlatego też myślę o makrach. Mam co do tego dwa pytania.

    Po pierwsze. Czy takie coś o czym pisze jest wykonalne poprzez napisanie odpowiedniego makra? (nie mówie żeby ktoś to za mnie zrobił po prostu zastanawiam sie jak daleko można się posunąć w obsłudze excela korzystając z własnoręcznie pisanych makr)

    Po drugie. Jeżeli można (a nawet w sumie jeżeli nie), to czy zna ktoś jakaś publikacje z której mógłbym się nauczyć pisać makra (o ile wiem to całe te oparte są o język programowania VBA więc w zamyśle szukam kursu VBA)? Najlepiej takie z przykładami. Chodzi o publikacje książkowe jak i elektroniczne które uważacie za lepsze.

    Z góry dziękuję za wszelką pomoc

    Pozdrawiam.
  • #2
    JRV
    VBA, Excel specialist
    Witam
    Moim zdaniem jest:
    Po pierwsze arkusz należy przebudować tak, żeby sklepy były od prawej krawędzi.
    Każdy sklep ma zajmować pojedynczej komórki.
    Analizować "sklep5-sklep4-sklep11-sklep1-sklep10" będzie znacznie trudniejsze, niż oni indywidualnie(chociaż nawet formuły, chociaż makro).
    Złożoność więcej na tym, że nie można jakoś przenieść sklepy, ponieważ długość ścieżki może być zbyt długa.
    Dla jednego auto powinny być tylko niektóre liczby sklepów, w których jest to bardziej rentowny sposób
  • #3
    adamas_nt
    Moderator of Programming
    Popieram przedmówcę. Mały przykład w załączniku. "Czerwone" formatowanie można również zastosować w "głównym" arkuszu...
  • #6
    steelek56
    Level 12  
    Dzięki za wszelkie posty.

    Propozycja pisania każdego sklepu w oddzielnej komórce była z jednym pomysłów na który wpadłem w pierwszej kolejności. Pytanie tylko jak na taką wersję planu zareaguje magazyn firmy. Chociaż dziś wstepnie rozmawiałem z koleżanką ta stwierdziła że przecież przepisać trasę na podstawie tego co tam jest to żaden problem. W sumie pewnie ma rację.

    Ogolnie to zarys jaki chciałem z tego zrobić jest mocno zbliżony do tego co zaproponował @JRV. Tyle, że tak jak ja pisałem chciałem to ugryźć bardziej przez formuły oparte o wyszukiwanie. Ten sposób wydaje sie być sporo lepszy i mniej skomplikowany przy okazji eliminuje większość możliwych do popelnienia błędów nawet tych mało istotnych.

    Nie oznaczę póki co tematu jako rozwiązany bo sprawdzę jak to się ma w przykładowym planie do całości. Wtedy ostatecznie komuś postawie kawę i ciastko.
  • #7
    JRV
    VBA, Excel specialist
    Witam
    Sklepy już wiem, istnieją 400 +/-
    Jest pożądane, aby znać ile samochodów są maksymalne.
    ile około sklepy na 1 trasę ?
  • #8
    steelek56
    Level 12  
    Ciężko to jednoznacznie określić. Wahania są różne w zależności od sezonu. Przy świętach itp. robi się czasem po 4-5 sklepów na jedno auto. Teraz np w styczniu zdarzają się sytuacje gdzie jest ich nawet 16. Ogólnie to przy 14 jest już problem, aby kierowca się wyrobił nawet ładując się na pierwszą możliwą godzinę (16:00) żeby zdążyć do ostatniego sklepu na czas (max do godziny 7:00). Więc mozna przyjąć że max to 18 sklepów i więcej nie powinno mieć miejsca. Wyedytowałem odpowiednio formułę którą zaproponowałeś. Pojawia się problem przy tworzeniu macierzy sprawdzającej czy i gdzie który sklep został wpisany. Auta zamieniłem ogólnie na trasy z racji tego, że nazwy aut pochodzą od nazwisk przewoźników i często się pokrywają więc mogłoby to wprowadzić jakieś zamieszanie. Więc zamiast auto1, auto2 itd mamy trasa1, trasa2 itd. Więc Twoje pytanie ile jest maksymalnie tras. Cóż. Wziełem do testów jakiś stary plik z sierpnia tamtego roku. Tras/aut było 39. Chociaż sytuacje przy okresach świątecznych były nawet do 80 tras. Cały teraz problem polega na uzupełnieniu wszystkich tych komórek odpowiednimi formułami dla wszystkich możliwych tras (chodzi o stworzenie czegoś na wzór szablonu, że jeżeli okaże się że musimy stworzyć dodatkową trasę, to żeby nie trzeba było dopisywać kolejnej kolumny formuł w arkuszu 'X').

    Może znasz sposób na autouzupełnienie tych kolumn kolejnymi formułami?

    =JEŻELI((CZY.BRAK(PODAJ.POZYCJĘ($A2;Arkusz1!$I$3:$R$3;0)));0;1)
    =JEŻELI((CZY.BRAK(PODAJ.POZYCJĘ($A2;Arkusz1!$I$4:$R$4;0)));0;1)
    =JEŻELI((CZY.BRAK(PODAJ.POZYCJĘ($A2;Arkusz1!$I$5:$R$5;0)));0;1)
    ...
    =JEŻELI((CZY.BRAK(PODAJ.POZYCJĘ($A2;Arkusz1!$I$n:$R$n;0)));0;1)


    Dodałem Twój załącznik z problemem o którym mówię.


    EDIT

    Cóż do szblonu dla 100 tras (raczej starczy na długo) dopisałem te funkcje ręcznie. Jest tylko mały problem z sortowaniem przez Drag&Drop między trasami (jeżeli zrobi się luka to się cały plan rozjeżdza. Chociaż to jest najmniejszy problem bo wystarczy zaznaczyć całośc do sortowania albo po prostu usunąć puste komórki w kolumnach z tras i już jest zero problemów.
  • #9
    JRV
    VBA, Excel specialist
    Teraz trochę zajęty. Będę odpowiadać później.
    Quote:
    autouzupełnienie tych kolumn
    Jest to możliwe
    Prosze więcej informacji o sortowaniu
  • #10
    steelek56
    Level 12  
    Jednak okazuje się, że jest większy problem z sortowaniem tego. Wszystko opisałem i zaznaczyłem w Twoim pliku.

    Myślę ogólnie nad transpozycją w arkuszu X - Auta/trasy do wierszy, a kolejne sklepy do kolumn. Wtedy zamiast macierzy 400Wx100K wyjdzie macierz 100Wx400K.

    Niby nie było by to problemem w końcu excel oferuje wklejanie z transpozycji, ale

    Producent listę zamówień przesyła do nas w postaci 2 kolumn. Kolumna A - nazwa sklepu + jego adres, Kolumna B - masa zamówienia. Wpisywanie tych długich nazw z adresami jest bez sensu. Stąd zrobiłem małą tabelę ze skróconymi nazwami sklepów jakie stosujemy w firmie na co dzień. Wklejając to co przesyła nam producent jako transpozycje musiałbym wklepać 400 kolumn formuły wyszukaj.pionowo. Bez opcji autouzupełnienia.

    Wrzuciłem też "przerobiony" stary plan przez co można zobaczeć o co mi chodzi.

    Proszę w miarę możliwości o nie udostępnianie go dalej. Sam też usunę ten konkretny plik jak tylko znajdziemy jakieś konkretne rozwiązanie.
  • Helpful post
    #12
    JRV
    VBA, Excel specialist
    steelek56 wrote:
    jeżeli zrobi się luka
    Co to znaczy?
  • #13
    steelek56
    Level 12  
    Wow... nie wiedziałem że excel ma takie formuły... chyba za bardzo zostałem w wersji 2003. Kiedyś szukałem jak połączyć tekst z róznych komórek w jedną...

    Chociaż wprowadziłem Cię lekko w błąd. Zamieniłem nazwy samochodów żeby nie ujawniać poufnych danych. Ale nie zastosowałem czegoś co w tym planie jest bardzo częste. Mianowicie. Auta, a raczej ich nazwy często sie powtarzają. Przez co nie ma sytuacji Auto1,2,3,...,n. Samochody nazywane są jak juz wcześniej napisałem po nazwiskach przewoźników/nazwach firm. I tak przewoźnik który ma kilka aut w planie bedzie widniał jako kowalski, kowalski, kowalski, kowalski a czasem kowalski1 kolejne auta nazywać się będą nowak, nowak, nowak, nowak itd. Więc formuła PODAJ.POZYCJĘ tutaj psuje całą sprawę, bo o ile wiem to poda pozycję pierwszego auta o zadanej nazwie. Najgorsze w tym wszystkim jest to ze sam system jest tak zastany, że nie wchodzi w grę zmiana nazw aut na kowalski1, kowalski2, kowalski3 itd. Nawet jesli. Czasem auta dublują trasy wtedy PODAJ.POZYCJĘ mimo wszystko nie da porządanego efektu.

    @UP: to już nieistotne poradziłem sobie już z tym. Poza tym jeżeli wyjdzie na korzystanie z formuły ZŁACZ.TEKST to i tak ten problem zniknie, bo sklepy będą musiały być przypisywane w odpowiednich kolejnościach.

    EDIT
    Wysłałem do Ciebie wiadomość prywatną z oryginałem planu
  • Helpful post
    #14
    JRV
    VBA, Excel specialist
    steelek56 wrote:
    ZŁACZ.TEKST
    nawet Excel97 ma takie
  • #15
    steelek56
    Level 12  
    To mnie teraz zdziwiłeś. Nigdy nie przeglądałem wszystkich formuł dokładnie. Zdawało się że nie są mi one potrzebne do takich podstawowych rzeczy. Kiedyś nawet na jakimś forum pytałem o możliwość łączenia takich komórek nikt nie wiedział dlatego zarzuciłem pomysł.
  • Helpful post
    #16
    JRV
    VBA, Excel specialist
    do postu #13
    a Jeśli dodamy kolumnę z prawdziwego nazwiska? i kolumna avto1 avto2 i itp. Ukryj
  • #17
    steelek56
    Level 12  
    Myślę że to co ustaliliśmy póki co - czyli opieranie sie o liczbę porządkową a nie o nazwisko jest najmniej inwazyjna jeżeli chodzi o zmiany. Plan i jego układ zostaje taki sam wiec nikt nie bedzie narzekał że jakieś zmiany sie porobiły. Jutro posprawdzam co i jak i myślę że w końcu zakończymy całą tą sprawę.

    Naprawdę nie wiem jak podziękować koledze JRV...

    Sam bym tego w zyciu nie ogarnął i pomyśleć że wszystko to dało zrobić sie samymi formułami. Przynajmniej mam teraz pretekst do głebszego sprawdzenia możliwości Excel. Myślałem że w sferze formuł wiem już wszystko. A tu się okazuje że jeszcze sporo mi brakuje.

    Jeszcze raz dziękuję za pomoc.