Solver był darmowym dodatkiem od czasów Lotus 1-2-3
Excel nie był pierwszym programem do obsługi arkuszy kalkulacyjnych. Lotus 1-2-3 nie był pierwszym programem do obsługi arkuszy kalkulacyjnych. Pierwszym programem do obsługi arkuszy kalkulacyjnych był VisiCalc w 1979 roku. Opracowany przez Dana Bricklina i Boba Frankstona, VisiCalc został opublikowany przez Dana Fylstrę. Dziś Dan zarządza Frontline Systems. Jego firma napisała Solver używany w programie Excel. Opracował również cały pakiet oprogramowania analitycznego, które współpracuje z programem Excel.
Jeśli masz program Excel, masz dodatek Solver. Może nie jest włączona, ale masz ją. Aby włączyć dodatek Solver w programie Excel, naciśnij klawisze alt = "" + T, a następnie I. Dodaj znacznik wyboru obok opcji Solver.
Aby pomyślnie używać Solvera, musisz zbudować model arkusza, który ma trzy elementy:
- Musi być jedna komórka celu. Jest to komórka, którą chcesz zminimalizować, zmaksymalizować lub ustawić na określoną wartość.
- Może być wiele komórek wejściowych. Jest to jedno z fundamentalnych ulepszeń w stosunku do funkcji Goal Seek, która może obsługiwać tylko jedną komórkę wejściową.
- Mogą istnieć ograniczenia.
Twoim celem jest stworzenie wymagań dotyczących harmonogramu dla parku rozrywki. Każdy pracownik będzie pracował przez pięć dni z rzędu, a następnie będzie miał dwa dni wolne. Istnieje siedem różnych sposobów, aby zaplanować komuś pięć dni pod rząd i dwa dni wolne. Są one pokazane jako tekst w A4: A10. Niebieskie komórki w B4: B10 to komórki wejściowe. W tym miejscu określasz, ile osób pracuje dla każdego harmonogramu.
Komórka celu to łączna lista płac na tydzień, pokazana w B17. To jest prosta matematyka: suma osób z B11 razy 68 dolarów pensji na osobę dziennie. Poprosisz Solvera o znalezienie sposobu na zminimalizowanie tygodniowej listy płac.
Czerwone pole pokazuje wartości, które się nie zmienią. Tyle osób potrzebujesz do pracy w parku każdego dnia tygodnia. Potrzebujesz co najmniej 30 osób w ruchliwe weekendowe dni - ale tylko 12 w poniedziałek i wtorek. Pomarańczowe komórki używają SUMPRODUCT do obliczenia liczby osób, które zostaną zaplanowane każdego dnia na podstawie danych wejściowych w niebieskich komórkach.
Ikony w wierszu 15 wskazują, czy potrzebujesz więcej osób, czy mniej osób, lub czy masz dokładnie odpowiednią liczbę osób.
Najpierw próbowałem rozwiązać ten problem bez dodatku Solver. Codziennie chodziłem z 4 pracownikami. To było świetne, ale w niedzielę nie miałem wystarczająco dużo ludzi. Zacząłem więc zwiększać harmonogramy, dzięki czemu będę miał więcej niedzielnych pracowników. Skończyło się na czymś, co działa: 38 pracowników i 2,584 dolarów tygodniowej listy płac.
Kliknij ikonę Solver na karcie Dane. Poinformuj Solvera, że próbujesz ustawić listę płac w B17 na minimum. Komórki wejściowe to B4: B10.
Ograniczenia dzielą się na oczywiste i nieoczywiste kategorie.
Pierwszym oczywistym ograniczeniem jest to, że D12: J12 musi być> = D14: J14.
Ale jeśli spróbujesz teraz uruchomić Solvera, uzyskasz dziwne wyniki, w których masz ułamkowe liczby osób i prawdopodobnie ujemną liczbę osób pracujących według określonych harmonogramów.
Chociaż wydaje się oczywiste, że nie możesz zatrudnić 0,39 osoby, musisz dodać ograniczenia, aby powiedzieć Solverowi, że B4: B10 są> = 0, a B4: B10 to liczby całkowite.
Wybierz Simplex LP jako metodę rozwiązywania i wybierz Rozwiąż. Za kilka chwil Solver przedstawia jedno optymalne rozwiązanie.
Solver znalazł sposób na pokrycie kosztów zatrudnienia w parkach rozrywki, zatrudniając 30 pracowników zamiast 38. Oszczędności tygodniowe wynoszą 544 USD - czyli ponad 7 000 USD w okresie letnim.
Zwróć uwagę na pięć gwiazdek poniżej Potrzebni pracownicy. Harmonogram zaproponowany przez Solver dokładnie odpowiada Twoim potrzebom przez pięć z siedmiu dni. Efektem ubocznym jest to, że w środę i czwartek będziesz mieć więcej pracowników, niż naprawdę potrzebujesz.
Rozumiem, jak Solver wymyślił to rozwiązanie. Potrzebujesz wielu ludzi w sobotę, niedzielę i piątek. Jednym ze sposobów, aby przyciągnąć ludzi tam w ten dzień, jest dać im wolny poniedziałek i wtorek. Dlatego Solver umieścił 18 osób z wolnymi w poniedziałek i wtorek.
Ale to, że Solver wymyślił optymalne rozwiązanie, nie oznacza, że nie ma innych równie optymalnych rozwiązań.
Kiedy tylko zgadywałem, jak wygląda obsada, nie miałem dobrej strategii.
Teraz, gdy Solver dał mi jedno z optymalnych rozwiązań, mogę założyć kapelusz logiki. Zatrudnienie 28 pracowników w wieku akademickim w środę i czwartek, kiedy potrzeba tylko 15 lub 18 pracowników, może spowodować kłopoty. Nie wystarczy do zrobienia. Dodatkowo, mając dokładnie odpowiednią liczbę pracowników przez pięć dni, będziesz musiał wezwać kogoś na nadgodziny, jeśli ktoś inny zgłosi się na chorobę.
Ufam Solverowi, że potrzebuję 30 osób, aby to zadziałało. Ale założę się, że mogę przestawić tych ludzi, aby wyrównać harmonogram i zapewnić mały bufor w inne dni.
Na przykład przyznanie komuś wolnego w środę i czwartek gwarantuje również, że osoba będzie w pracy w piątek, sobotę i niedzielę. Dlatego ręcznie przeniosłem niektórych pracowników z rzędu w poniedziałek i wtorek do rzędu w środowy czwartek. Ciągle ręcznie podłączałem różne kombinacje i wymyśliłem to rozwiązanie, które ma takie same koszty wynagrodzeń jak Solver, ale lepsze wartości niematerialne. Sytuacja overstaff trwa teraz cztery dni zamiast dwóch. Oznacza to, że możesz obsługiwać odwołania od poniedziałku do czwartku bez konieczności dzwonienia do kogoś z weekendu.
Czy to źle, że udało mi się wymyślić lepsze rozwiązanie niż Solver? Nie. Faktem jest, że nie byłbym w stanie uzyskać tego rozwiązania bez użycia dodatku Solver. Kiedyś Solver dał mi model, który minimalizował koszty, mogłem zastosować logikę dotyczącą wartości niematerialnych i prawnych, aby utrzymać tę samą listę płac.
Jeśli potrzebujesz rozwiązać problemy bardziej złożone niż może obsłużyć Solver, sprawdź najlepsze rozwiązania Excela dostępne w Frontline Systems: http://mrx.cl/solver77.
Podziękowania dla Dana Fylstry i Frontline Systems za ten przykład. Walter Moore zilustrował kolejkę górską XL.
Obejrzyj wideo
- Solver był darmowym dodatkiem od czasów Lotus 1-2-3
- Solver jest produktem założyciela Visicorp, Dana Fylstry
- Solver w programie Excel to mniejsza wersja zaawansowanych solverów
- Dowiedz się więcej o profesjonalnych rozwiązaniach: http://mrx.cl/solver77
- Aby zainstalować dodatek Solver, wpisz alt = "" + T, a następnie I. Zaznacz Solver.
- Solver znajduje się po prawej stronie karty Dane
- Chcesz mieć obiektywną komórkę, którą próbujesz zminimalizować lub zmaksymalizować.
- Możesz określić wiele komórek wejściowych.
- Możesz określić ograniczenia, w tym takie, których się nie spodziewasz:
- Żadnych pół-ludzi: użyj INT dla liczby całkowitej
- Solver znajdzie optymalne rozwiązanie, ale mogą istnieć inne rozwiązania
- Gdy otrzymasz rozwiązanie Solver, możesz je dostosować.
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2036 - Wprowadzenie do rozwiązania Solver!
W porządku, podcastuję całą książkę. Kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania, na której możesz odtworzyć wszystkie filmy!
Witaj ponownie w netcast, jestem Bill Jelen. Rozmawialiśmy ostatnio o niektórych analizach typu „co-jeśli”, takich jak szukanie celu, wiesz, z jedną komórką wejściową, którą zmieniasz, ale co, jeśli masz coś bardziej złożonego? Jest świetne narzędzie o nazwie Solver, Solver istnieje od dawna, gwarantuję, że jeśli masz Excela i pracujesz w systemie Windows, masz Solvera, prawdopodobnie nie jest włączony. Aby go włączyć, musisz przejść do alt = "" T, a potem I, więc T jak Tom, I dla lodów i zaznacz to pole dla Solvera, kliknij OK i po kilku sekundach będziesz mieć zakładka Solver po prawej stronie. W porządku i mamy zamiar ustawić tutaj model, który rozwiązujący może być w stanie rozwiązać, mamy park rozrywki, próbujemy ustalić, ilu pracowników ma zaplanować. Wszyscy pracują przez pięć kolejnych dni, więc nie majest naprawdę siedem możliwych harmonogramów, w których jesteś wolny, niedziela poniedziałek, poniedziałek wtorek, wtorek środa. Musimy dowiedzieć się, ilu pracowników umieścić na każdym z tych harmonogramów.
A więc wystarczy prosta matematyka tutaj, wykonując SUMPRODUCT, liczbę pracowników razy w niedzielę, aby obliczyć, ile osób było w niedzielę, poniedziałek, wtorek, środę. Prowadząc ten park rozrywki, nauczyliśmy się, że w sobotę i niedzielę potrzebujemy wielu ludzi. 30 osób w sobotę i niedzielę, w tygodniu poniedziałek, wtorek, trochę wolno, 12 pracowników będzie mogło to zrobić. W porządku, po prostu przychodząc tutaj i po prostu spieprzając, wiesz, próbując znaleźć właściwe liczby, możesz po prostu podłączać rzeczy, ale przy siedmiu różnych wyborach zajmie to wieczność, w porządku.
Teraz w Solver mamy serię komórek wejściowych, a myślę, że w darmowej wersji Solvera możesz mieć, czy jest to sto? Nie wiem, jest pewna liczba, a jeśli musisz wyjść poza to, jest Premium Solver, który możesz zdobyć z Frontline Systems. W porządku, więc mamy kilka komórek wejściowych, mamy kilka komórek z ograniczeniami, a następnie musisz sprowadzić to wszystko do ostatecznej liczby. Tak więc w moim przypadku staram się zminimalizować tygodniową listę płac, aby ta zielona liczba była tym, co chcę zoptymalizować, w porządku, więc oto, co zrobimy!
Solver, oto celowa komórka, to zielona komórka i chcę ustawić ją na minimalną wartość, obliczyć personel, który zapewni mi minimalną wartość, zmieniając te niebieskie komórki. A teraz są ograniczenia, w porządku, więc pierwsze ograniczenie polega na tym, że suma harmonogramu musi być> = sekcja czerwona i możemy to wszystko zrobić jako pojedyncze ograniczenie. Zobacz, jakie to fajne, wszystkie te komórki muszą być> = te odpowiadające im komórki tutaj, super, kliknij Dodaj, w porządku, ale są też inne rzeczy, o których byś nie pomyślał. Na przykład Solver w tym momencie może zdecydować, że najlepiej jest mieć 17 osób w tym harmonogramie, 43 osoby w harmonogramie i -7 osób w tym harmonogramie. W porządku, więc musimy powiedzieć Solverowi, że te komórki wejściowe muszą być liczbami całkowitymi, kliknij Dodaj. Nie możemy też pozwolić, aby ktoś się nie pojawił,a oni zwrócą nam swoją pensję, prawda? Więc powiemy, że te komórki muszą być> = 0, kliknij Dodaj, wracamy teraz, mamy tam nasze trzy ograniczenia.
Istnieją trzy różne sposoby rozwiązania, a ten opiera się na matematyce liniowej, więc możemy po prostu przejść do Simplex LP. Jeśli ten nie zadziała, to zdecydowanie spróbuj dwóch pozostałych, miałem przypadki, w których Simplex mówi, że nie może znaleźć rozwiązania, a jeden z pozostałych dwóch działa. Frontline Systems ma świetne samouczki na temat Solvera, próbuję tylko przeprowadzić cię przez pierwszy dzisiaj, nie ogłaszam, że jestem ekspertem w Solver. Kiedyś miałem Solvera, który nie działał, i wysłałem notatkę do Frontline Systems i wow, dostałem ten niesamowity 5-stronicowy list z powrotem od samego Dana Fylstry, prezesa Solvera! Zaczęło się: „Drogi Billu, miło cię słyszeć!” A potem ciągnąłem się przez 4,9 strony, to wszystko było prawie całkowicie ponad moją głową, w porządku. Ale wiesz, wiem wystarczająco dużo o Solver, aby przejść przez to, w porządku,więc klikniemy tutaj na Rozwiąż, znalazło rozwiązanie: „Wszystkie ograniczenia i warunki optymalności są spełnione”. Zamierzam to zachować, mogę tworzyć raporty, nie muszę tego teraz robić. Och, faktycznie mogę zapisać scenariusz, wczoraj żartowałem sobie ze scenariuszy, może Solver byłby w stanie stworzyć dla mnie nowy scenariusz, więc klikniemy OK.
W porządku, i na pewno zaoszczędziło nam to pieniądze, pisaliśmy wcześniej 2584, a teraz sprowadziło nas to do 2040 roku. Potrzebujemy więc wielu ludzi wolnych w poniedziałek i wtorek, w porządku, niektórzy ludzie, 2 osoby wolne w środę czwartek i potem w piątek w sobotę. Cóż, to jest niesamowite, nigdy nie wymyśliłbym przypadkowo tego zestawu odpowiedzi, w porządku, ale czy to oznacza, że to najlepsza odpowiedź? Cóż, oznacza to, że jest to minimalna lista płac, ale prawdopodobnie mogę wymyślić inny zestaw odpowiedzi, który nadal miałby tę minimalną listę płac. Są na to inne sposoby, może to być nieco lepszy harmonogram. Na przykład teraz mamy 28 osób w środę i czwartek, kiedy potrzebujemy tylko 15 i 18 osób, to dużo ludzi. Pomyśl o tym, kto pracuje w parkach rozrywki, to dzieciaki z college'u na przerwie w domu,to będzie kłopot, jeśli mamy tylu dodatkowych ludzi. A w poniedziałek we wtorek jesteśmy martwi, dokładnie tam, gdzie chcemy. To oznacza, że jeśli ktoś, kogo mam zwolnić z pracy, będzie musiał, no wiesz, zadzwonić do kogoś i zapłacić mu półtora czasu, ponieważ przepracował już pięć innych dni.
W porządku, więc po prostu z prostą małą matematyką, jeśli zabiorę 8 z poniedziałku we wtorek i zrobię 10, weźmie te 8 i dodam je do środowego czwartku, w porządku. Teraz mam rozwiązanie Solvera z dokładnie tą samą odpowiedzią, 2040, mają odpowiednią liczbę osób. Po prostu zbilansowałem harmonogram i teraz mamy 8 dodatkowych, 8 dodatkowych, 3 dodatkowe i 2 dodatkowe, i dokładnie to, czego potrzebujemy w weekend, czyli, wiesz, scenariusz z pełnym personelem. Dla mnie jest to nieco lepsze niż to, co wymyślił Solver, czy to oznacza, że solver zawiódł? Nie, absolutnie nie, ponieważ nigdy nie byłbym tak blisko bez dodatku Solver. Gdy Solver dał mi odpowiedź, tak, udało mi się to trochę poprawić i dostać się tam, w porządku. Wskazówka # 37, „40 najlepszych wskazówek dotyczących programu Excel wszechczasów”, zbliżanie się do końca tych pierwszych 40, świetne wprowadzenie do dodatku Solver.Przewodnik po wszystkich podcastach z tej serii jest tutaj: „MrExcel XL - 40 najlepszych wskazówek dotyczących Excela wszechczasów”. Możesz mieć e-book za jedyne 10 USD, wydrukować książkę za 25 USD, kliknij „i” na górze -prawy róg!
W porządku, podsumuj: Solver, jeśli korzystasz z wersji programu Excel, Lotus 1-2-3 dla systemu Windows, jest tam, został stworzony przez założyciela Visicorp, Dana Fylstrę. To darmowa wersja solwerów do dużych obciążeń, tutaj jest link, aby sprawdzić te rozwiązania, które zostaną umieszczone w komentarzach na YouTube. Prawdopodobnie po prostu nie są zainstalowane, alt = "" TI, zaznacz Solver, spójrz na prawą stronę zakładki Data, aby znaleźć Solver. W porządku, musisz mieć celową komórkę, którą próbujesz zminimalizować, zmaksymalizować lub ustawić na wartość, jeden zakres komórek wejściowych. Określ ograniczenia, w tym coś, czego się nie spodziewałeś, tak jak musiałem powiedzieć „Żadnych pół-ludzi” i „Żadnych negatywnych ludzi”. Solver znajdzie optymalne rozwiązanie, ale mogą istnieć inne, które są powiązane i możesz je dostosować, aby uzyskać lepsze rozwiązanie.
W porządku, proszę bardzo, chcę ci podziękować za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2036.xlsx
