Uwaga: Staramy się udostępniać najnowszą zawartość Pomocy w Twoim języku tak szybko, jak to możliwe. Ta strona została przetłumaczona automatycznie i może zawierać błędy gramatyczne lub nieścisłości. Chcemy, aby ta zawartość była dla Ciebie przydatna. Prosimy o powiadomienie nas, czy te informacje były pomocne, u dołu tej strony. Oto angielskojęzyczny artykuł do wglądu.

W tym artykule omówiono korzystanie z dodatku Solver, programu Microsoft Excel, którego można użyć do przeanalizowania optymalnego produktu.

Jak mogę ustalić miesięczny asortyment produktu, który maksymalizuje rentowność?

Firmy często muszą określić ilość każdego produktu, który ma być wytwarzany co miesiąc. W najprostszym formularzu problem z mieszaniem produktu polega na ustaleniu kwoty każdego produktu, który ma zostać wyprodukowany w ciągu miesiąca, aby zmaksymalizować zyski. Kombinacja produktów musi zwykle spełniać następujące warunki:

  • Miks produktu nie może korzystać z większej liczby zasobów niż jest dostępne.

  • Dla każdego produktu istnieje ograniczone zapotrzebowanie. Nie możemy utworzyć większej ilości produktu w ciągu miesiąca niż dzwonienie, ponieważ nadwyżka produkcji jest zajęta (na przykład nietrwały narkotyk).

Teraz rozwiążemy problem z przykładem miksu produktu. Rozwiązanie tego problemu można znaleźć w pliku prodmix. xlsx przedstawionym na rysunku 27-1.

Obraz książki

Załóżmy, że pracujemy nad firmą narkotykową, która produkuje w swoim zakładzie sześć różnych produktów. Produkcja każdego produktu wymaga robocizny i surowca. W wierszu 4 na rysunku 27-1 jest wyświetlana liczba godzin robocizny potrzebnych do wygenerowania funta każdego produktu, a w wierszu 5 przedstawiono funty surowca potrzebnego do wytworzenia funta każdego produktu. Na przykład wytworzenie funta produktu 1 wymaga sześciu godzin robocizny i 3,2 funtów surowca. Dla każdego narkotyku cena za funt jest podana w wierszu 6, koszt jednostkowy na Funt jest podany w wierszu 7, a udział w zysku na funtach jest podany w wierszu 9. Na przykład produkt 2 sprzedaje się na $11,00 za funt, ponosi koszt jednostkowy w wysokości $5,70 za funt, a program Contribute $5,30 zysk na funtach. Zapotrzebowanie na miesiąc każdego narkotyku jest podane w wierszu 8. Na przykład popyt na produkt 3 wynosi 1041 funtów. W tym miesiącu dostępne są 4500 godzin robocizny i 1600 funty surowca. W jaki sposób ta firma może zmaksymalizować miesięczne zyski?

Jeśli nie mamy nic na temat dodatku Solver dla programu Excel, wystąpił ten problem, tworząc arkusz w celu śledzenia zużycia zysku i zasobów związanych z produktem mix. Następnie będziemy korzystać z wersji próbnej i błędu w celu zamieszania produktu w celu zoptymalizowania zysku bez korzystania z większej ilości robocizny lub surowca, niż jest on dostępny i bez wyprodukowania żadnego narkotyku poza popytem. Używamy dodatku Solver w tym procesie tylko na etapie próbno-i-Error. W zasadzie dodatek Solver jest aparatem optymalizacyjnym, który flawlessly przeprowadzać wyszukiwanie próbne i błędy.

Klucz do rozwiązywania problemów z mieszaniem produktu polega na efektywnym obliczaniu obciążenia zasobów i zysku związanego z danym asortymentem produktu. Ważne narzędzie, za pomocą którego można wykonać to obliczenie, to funkcja iloczynów. Funkcja iloczynów mnoży odpowiadające sobie wartości w zakresach komórek i zwraca sumę tych wartości. Każdy zakres komórek użyty w ocenie iloczynów musi mieć te same wymiary, co oznacza, że można użyć iloczynów z dwoma wierszami lub dwiema kolumnami, ale nie z pojedynczą kolumną i jednym wierszem.

Jako przykład, w jaki sposób możemy korzystać z funkcji iloczynów. w naszych przykładach miksu produktów, spróbujemy obliczyć nasze obciążenie zasobów. Nasze wykorzystanie robocizny jest obliczane przez

(Robocizna wykorzystana za 1 funtów) * (produkcja 1 funtów wyprodukowana) +
(robocizna używana za funta 2) * (wyprodukowano 2 funty) +...
(Robocizna wykorzystana za 1 funtów *) * (wyprodukowana jest 6 funtów)

Możemy obliczyć zużycie robocizny w bardziej nużące sposób, jak D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4. Podobnie, zużycie surowca może zostać obliczone jako D2 * od + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5. Jednak wprowadzenie tych formuł w arkuszu dla sześciu produktów jest czasochłonne. Wyobraźmy sobie, jak długo zajmiesz się, jeśli pracujesz z firmą, która wyprodukowała, na przykład produkty 50 w ich zakładach. Znacznie łatwiej jest obliczyć zużycie robocizny i surowców, aby skopiować je z D14 do D15 formuły iloczynów ($D $2: $I $2, D4: i4). W tej formule jest obliczana D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * i4 (to jest zużycie w naszej robociznie), ale jest znacznie łatwiejsze do wprowadzenia! Zwróć uwagę, że używam podpisu $ z zakresem D2: I2, aby po skopiowaniu formuły przechwycić produkt mix z wiersza 2. Formuła w komórce D15 oblicza zużycie surowców.

W podobny sposób zysk jest określany na podstawie

(Zysk 1-procentowy na funt) * (wyprodukowano 1 funtów) +
(zysk 2-procentowy na funt) * (wyprodukowano 2 funty) +...
(Zysk z zysku 6 zł za 1 funt) * (wyprodukowana jest 6 funtów)

Zysk jest w prosty sposób obliczany w komórce D12 z formułą iloczynów (D9: i9, $D $2: $I $2).

Teraz możemy znaleźć trzy składniki naszego modelu dodatku Solver mix.

  • Komórka Target.Naszym celem jest zmaksymalizowanie zysku (obliczanego w komórce D12).

  • Zmienianie komórek.Liczba funtów wyprodukowanych dla każdego produktu (wymieniona w zakresie komórek D2: I2)

  • Wstrzymywać. Mamy następujące ograniczenia:

    • Nie używaj większej liczby robocizny lub surowców niż jest to dostępne. Oznacza to, że wartości w komórkach D14: D15 (używane zasoby) muszą być mniejsze lub równe wartościom w komórkach F14: F15 (dostępne zasoby).

    • Nie produkuje więcej narkotyków niż jest to na żądanie. Oznacza to, że wartości w komórkach D2: I2 (funty wyprodukowane z każdego narkotyku) muszą być mniejsze lub równe zapotrzebowaniu na każdy narkotyk (wymieniony w komórkach D8: i8).

    • Nie możemy uzyskać ujemnej kwoty żadnego narkotyku.

Pokażę, jak wprowadzić komórkę docelową, zmieniające komórki i ograniczenia na Solver. Następnie wystarczy kliknąć przycisk Rozwiąż, aby znaleźć produkt z możliwością maksymalizowania zysku.

Aby rozpocząć, kliknij kartę dane, a następnie w grupie Analiza kliknij pozycję Solver.

Uwaga:  Jak wyjaśniono w rozdziale 26, "wprowadzenie do optymalizacji za pomocą dodatku Solver programu Excel", dodatek Solver jest instalowany po kliknięciu przycisku pakietu Microsoft Office, a następnie opcji programu Excel, a następnie dodatków. Na liście Zarządzaj kliknij pozycję Dodatki programu Excel, zaznacz pole dodatku Solver, a następnie kliknij przycisk OK.

Zostanie wyświetlone okno dialogowe Parametry dodatku Solver, jak pokazano na rysunku 27-2.

Obraz książki

Kliknij pole Ustaw komórkę doCelową, a następnie wybierz pozycję nasza komórka zysku (komórka D12). Kliknij pole przez zmianę komórek, a następnie wskaż zakres D2: I2, który zawiera funty wyprodukowane dla każdego narkotyku. Okno dialogowe powinno teraz wyglądać pod obrazem 27-3.

Obraz książki

Teraz możesz dodać ograniczenia do modelu. Kliknij przycisk Dodaj. Zostanie wyświetlone okno dialogowe Dodawanie ograniczenia, wyświetlane na rysunku 27-4.

Obraz książki

Aby dodać ograniczenia dotyczące użycia zasobów, kliknij pole odwołanie do komórki, a następnie wybierz zakres D14: D15. Wybierz < = z środkowej listy. Kliknij pole ograniczenia, a następnie wybierz zakres komórek F14: F15. Okno dialogowe Dodawanie ograniczenia powinno teraz wyglądać następująco: Rysunek 27-5.

Obraz książki

Zapewniamy, że gdy dodatek Solver próbuje różne wartości dla zmienianych komórek, tylko kombinacje spełniające oba D14< = F14 (używana robocizna jest mniejsza niż lub równa robocizny) i D15< = F15 (użyte surowce jest mniejsze niż lub równe będą brane pod uwagę dostępne surowce). Kliknij przycisk Dodaj, aby wprowadzić ograniczenia popytu. Wypełnij okno dialogowe Dodawanie ograniczenia, jak pokazano na rysunku 27-6.

Obraz książki

Dodanie tych ograniczeń zapewnia, że gdy dodatek Solver próbuje inne kombinacje zmian wartości komórek, będą brane pod uwagę tylko te kombinacje, które spełniają następujące parametry:

  • D2< = D8 (kwota wyprodukowana 1 jest mniejsza lub równa zapotrzebowaniu na narkotyk 1)

  • E2< = E8 (kwota wyprodukowanego narkotyku 2 jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 2)

  • F2< = F8 (ilość zrealizowanych środków na 3 rzecz jest mniejsza niż lub równa zapotrzebowaniu na narkotyki 3)

  • G2< = G8 (kwota wyprodukowana z 4 leków jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 4)

  • H2< = H8 (ilość uzyskana z przeprowadzonych narkotyków jest mniejsza niż lub równa zapotrzebowaniu na 5)

  • I2< = i8 (kwota wyprodukowana z poziomu 6 jest mniejsza niż lub równa zapotrzebowaniu na narkotyk 6)

Kliknij przycisk OK w oknie dialogowym Dodawanie ograniczenia. Okno funkcji Solver powinno wyglądać następująco: Rysunek 27-7.

Obraz książki

Wprowadzamy ograniczenie, które zmienia komórki w oknie dialogowym Opcje dodatku Solver muszą być nieujemne. Kliknij przycisk Opcje w oknie dialogowym Parametry dodatku Solver. Zaznacz pole Przyjmij model liniowy i pole przyjmij nieUjemnie, jak pokazano na rysunku 27-8 na następnej stronie. Kliknij przycisk OK.

Obraz książki

Zaznaczenie pola przyjmij nieUjemne pole wyboru gwarantuje, że dodatek Solver uzna tylko kombinacje zmienianych komórek, w których każda zmieniona komórka przyjmuje wartość nieujemną. Pole Przyjmij model liniowy jest zaznaczone, ponieważ problem z mieszaniem produktu jest specjalnym typem dodatku Solver, nazywanym modelem liniowym. Zasadniczo model dodatku Solver jest liniowy pod następującymi warunkami:

  • Komórka Target jest obliczana przez dodanie razem warunków formularza (zmieniając komórkę) * (stała).

  • Każde ograniczenie spełnia wymagania "model liniowy". Oznacza to, że każde ograniczenie jest oceniane przez dodanie do niego warunków formularza (zmieniając komórkę) * (stała) i porównanie sum z stałą.

Dlaczego problem jest występujący w dodatku Solver? Nasza komórka Target (zysk) jest obliczana jako

(Zysk 1-procentowy na funt) * (wyprodukowano 1 funtów) +
(zysk 2-procentowy na funt) * (wyprodukowano 2 funty) +...
(Zysk z zysku 6 zł za 1 funt) * (wyprodukowana jest 6 funtów)

To obliczenie jest zgodne ze wzorcem, w którym wartość komórki docelowej jest uzyskiwana przez dodanie razem warunków formularza (zmiana komórki) * (stała).

Nasze ograniczenie robocizny ocenia się, porównując wartość pochodną od (robocizna wykorzystana za 1 funtów) * (wytworzono 1 funty) + (robocizna wyprodukowana w funtach 2) * (wyprodukowano 2 funty) +... (Robocizna w Stanach Zjednoczonych ) Ed na Funt o wartości 6) * (wytworzono więcej niż 6 funtów) do dostępnej robocizny.

W związku z tym ograniczenie robocizny jest oceniane przez dodanie razem warunków formularza (zmiana komórki) * (stała) i porównanie sum z stałą. Zarówno ograniczenie robocizna, jak i ograniczenie surowca spełniają wymagania modelu liniowego.

Nasze ograniczenia popytu zajmują formularz

(Wyprodukowana wartość 1) < = (zapotrzebowanie na
narkotyki 1) (wyprodukowana wartość 2) < = (narkotyk 2
) §
(wyprodukowana wartość 6) < = ( narkotyka 6 popytu)

Każde ograniczenie na żądanie spełnia także wymagania modelu liniowego, ponieważ każdy z nich jest obliczany przez dodanie do siebie warunków formularza (zmiana komórki) * (stała) i porównanie sum z stałą.

Po sprawdzeniu, że nasz model miksu produktów jest modelem liniowym, dlaczego warto zadbać o to?

  • Jeśli model dodatku Solver jest liniowy, a wybieram pozycję Przyjmij model liniowy, funkcja Solver gwarantuje optymalne rozwiązanie w modelu dodatku Solver. Jeśli model dodatku Solver nie jest liniowy, dodatek Solver może lub nie może znaleźć optymalnego rozwiązania.

  • Jeśli model dodatku Solver jest liniowy, a wybieram pozycję Przyjmij model liniowy, dodatek Solver używa bardzo wydajnego algorytmu (metody jednostronne), aby znaleźć optymalne rozwiązanie. Jeśli model dodatku Solver jest liniowy i nie wybieramy opcji Przyjmij model liniowy, dodatek Solver używa bardzo niewydajnego algorytmu (metody GRG2) i może mieć trudności z znalezieniem optymalnego rozwiązania modelu.

Po kliknięciu przycisku OK w oknie dialogowym Opcje dodatku Solver powrócimy do głównego okna dialogowego dodatku Solver, pokazanego wcześniej na rysunku 27-7. Gdy klikam pozycję Rozwiąż, dodatek Solver obliczy optymalne rozwiązanie (jeśli istnieje) dla modelu miksu produktów. Jak stwierdzono w rozdziale 26, najlepszym rozwiązaniem w modelu miksu produktu jest zestaw zmian wartości komórek (funty wyprodukowane dla każdego narkotyku), które maksymalizują zyski wszystkich możliwych rozwiązań. Z tego względu jest to, że możliwe rozwiązanie to zestaw zmian wartości komórek spełniających wszystkie ograniczenia. Zmiany wartości komórek przedstawionych na rysunku 27-9 to wykonalne rozwiązanie, ponieważ wszystkie poziomy produkcji są nieujemne, poziomy produkcji nie przekraczają popytu, a użycie zasobów nie przekracza dostępnych zasobów.

Obraz książki

Zmiany wartości komórek przedstawionych na rysunku 27-10 na następnej stronie stanowią niewykonalne rozwiązanie z następujących powodów:

  • Oferujemy więcej niż 5-tego zapotrzebowania.

  • Używamy więcej robocizny niż jest to dostępne.

  • Używamy bardziej surowych materiałów, niż są dostępne.

Obraz książki

Po kliknięciu przycisku Rozwiąż dodatek Solver szybko znajdzie optymalny roztwór przedstawiony na rysunku 27-11. Musisz wybrać pozycję Zachowaj rozwiązanie dodatku Solver, aby zachować optymalne wartości w arkuszu.

Obraz książki

Nasze przedsiębiorstwo z narkotykami może zmaksymalizować miesięczny zysk na poziomie $6 625,20 przez produkowanie 596,67 funta 4, 1084 funtów 5 i żadnych innych narkotyków! Nie możemy ustalić, czy można uzyskać maksymalny zysk $6 625,20 w inny sposób. Wszystko, o czym możemy się upewnić, że masz ograniczone zasoby i popyt, nie ma sposobu na dokonywanie ponad $6 627,20 w tym miesiącu.

Załóżmy, że zapotrzebowanie na każdy produkt musi być spełnione. (ZaPoznaj się z arkuszem Brak możliwych rozwiązań w pliku prodmix. xlsx). Następnie musimy zmienić nasze ograniczenia popytu z D2: I2< = D8: i8 na D2: I2> = D8: i8. W tym celu otwórz dodatek Solver, wybierz ograniczenie D2: I2< = D8: i8, a następnie kliknij przycisk Zmień. Zostanie wyświetlone okno dialogowe Zmienianie ograniczenia przedstawione na rysunku 27-12.

Obraz książki

Wybierz pozycję > =, a następnie kliknij przycisk OK. Mamy teraz pewność, że w dodatku Solver będzie warto zmienić tylko wartości komórek, które spełnią wszystkie wymagania. Gdy klikniesz przycisk Rozwiąż, zobaczysz komunikat "dodatek Solver nie znalazł możliwego rozwiązania". Ta wiadomość nie oznacza, że wystąpił błąd w naszym modelu, ale chociaż nie są to nasze ograniczone zasoby, nie możemy zrealizować popytu na wszystkie produkty. Jeśli chcemy zaspokoić zapotrzebowanie na każdy produkt, w dodatku Solver należy dodać więcej robocizny, więcej surowców lub więcej.

Zobaczmy, co się stanie, Jeśli zezwolisz na nieograniczoną podaż każdego produktu i zezwolimy na wytwarzanie ilości ujemnych poszczególnych leków. (W pliku prodmix. xlsx nie można zobaczyć tego problemu w dodatku Solver). Aby znaleźć optymalne rozwiązanie w takiej sytuacji, Otwórz dodatek Solver, kliknij przycisk Opcje i wyczyść pole przyjmij wartość nieUjemną. W oknie dialogowym Parametry dodatku Solver wybierz ograniczenie popytu D2: I2< = D8: i8, a następnie kliknij pozycję Usuń, aby usunąć ograniczenie. Gdy klikniesz przycisk Rozwiąż, funkcja Solver zwróci komunikat "Ustaw wartości komórki nie jest zbieżny". Ten komunikat oznacza, że jeśli komórka docelowa ma zostać zmaksymalizowana (jak w naszym przykładzie), istnieje możliwość uzyskania możliwych rozwiązań z zawieszeniem arbitralnie dużych wartości komórek docelowych. (Jeśli komórka docelowa ma zostać zminimalizowana, wiadomość "Ustaw wartości komórek nie jest zbieżna" oznacza, że istnieją możliwe rozwiązania z niewielkimi, niewielką wartością docelowej komórki). W naszej sytuacji, dopuszczając negatywną produkcję narkotyków, obowiązuje "Tworzenie" zasobów, których można użyć w celu uzyskania arbitralnie dużych ilości innych leków. Biorąc pod uwagę nasze zapotrzebowanie bez ograniczeń, umożliwia nam nawiązanie nieograniczoną zyski. W realnej sytuacji nie możemy dokonywać nieskończonej kwoty pieniędzy. W skrócie, jeśli zostanie wyświetlony komunikat "Ustaw wartości nie zbieżność", model zawiera błąd.

  1. Załóżmy, że nasze przedsiębiorstwo z narkotykami może zakupić nawet 500 godzin pracy o godzinie $1 większej, niż koszty robocizny. Jak można zmaksymalizować zyski?

  2. W zakładzie produkcyjnym, czterech pracowników (A, B, C i D) produkuje trzy produkty (produkty 1, 2 i 3). W tym miesiącu Producent mikroukładu może sprzedawać 80 jednostek produktu 1, 50 jednostek produktu 2 i maksymalnie 50 jednostki produktu 3. Technika A może nakonywać tylko produktów 1 i 3. Technika B może mieć tylko produkty 1 i 2. Technika C może nakonywać tylko produktu 3. Technika D może mieć tylko produkt 2. Dla każdej produkowanej jednostki produkty wnoszą następujące zyski: produkt 1, $6; Produkt 2, $7; i 3 produkt $10. Czas (w godzinach), jaki musi wyprodukować produkt, jest następujący:

    Produkt

    Technika A

    Technika B

    Technika C

    Technika D

    1

    2

    2,5

    Nie można wykonać

    Nie można wykonać

    2

    Nie można wykonać

    3

    Nie można wykonać

    3,5

    3

    3

    Nie można wykonać

    4

    Nie można wykonać

  3. Każdy pracownik może współpracować nawet z 120 godzin miesięcznie. W jaki sposób Producent chipu maksymalizuje miesięczne zyski? Załóżmy, że można wyprodukować ułamkową liczbę jednostek.

  4. Elektrownia komputerowa produkuje myszy, klawiatury i joysticki do gier wideo. W poniższej tabeli przedstawiono sposób użycia zysku jednostkowego, za jednostkę pracy na jednostkę, miesięczny popyt oraz użycie czasu na oddzielne godziny na komputerach.

    Mysz

    Klawiatur

    Joysticki

    Zysk/jednostka

    $8

    $11

    $9

    Sposób użycia/jednostka robocizny

    2 godziny

    .3 godziny

    24 godziny

    Czas komputera/jednostka

    04 godz.

    .055 godzin

    04 godz.

    Miesięczny popyt

    15 000

    27 000

    11 000

  5. W każdym miesiącu są dostępne łącznie 13 000 godzin robocizny i 3000 godzin pracy. W jaki sposób producent może zmaksymalizować swój miesięczny udział w zysku z zakładu?

  6. ZaPoznaj się z naszym Przykładem narkotyków, założono, że musi być spełniony minimalny popyt na 200 sztuk każdego narkotyku.

  7. Jason to Bracelets karo, Necklaces i Earrings. Chce pracować maksymalnie 160 godzin miesięcznie. Posiada 800 uncji diamentów. Poniżej przedstawiono zyski, czas pracy i uncje diamentów wymagane do produkcji każdego produktu. Jeśli zapotrzebowanie na każdy produkt jest bez ograniczeń, jak Jason zwiększyć jego zyski?

    Produkt

    Zysk jednostkowy

    Godziny robocizny na jednostkę

    Uncje karo na jednostkę

    Bracelet

    300 zł

    .35

    1,2

    Necklace

    200 zł

    .15

    .75

    Earrings

    100 zł

    0,05

    0,5

Potrzebna dalsza pomoc?

Rozwijaj swoje umiejętności
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów Microsoft Office

Czy te informacje były pomocne?

Jaka jest jakość tłumaczenia?
Co wpłynęło na Twoje wrażenia?

Dziękujemy za opinię!

×