Za pomocą dodatku Solver do określenia optymalnego produkt mix

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 przy użyciu dodatku Solver, program-dodatek Microsoft Excel są dostępne dla analizy warunkowej ustalenie mix optymalnego produktu.

Jak ustalić, miesięczny produkt mix maksymalizuje rentowność?

Często firmy trzeba określić ilość każdego produktu w celu utworzenia miesięcznych. W postaci najłatwiejszym mieszać produktu problem polega na sposób ustalania ilości każdego produktu, które powinny być przedstawiane w celu zwiększenia zysków miesiącu. Produkt pakietu Office mix zazwyczaj muszą spełniać następujące ograniczenia:

  • Produkt pakietu Office mix nie może zawierać więcej zasobów niż są dostępne.

  • Jest ograniczony żądanie dla każdego produktu. Firma Microsoft nie może utworzyć więcej produktu w miesiącu niż żądanie ile wymagają tego warunki, ponieważ nadmiernej produkcji jest potrzebne dodatkowe (na przykład nietrwałych leków).

Przejdźmy teraz rozwiązać następujący przykład problemu mix produktów. Rozwiązanie tego problemu można znaleźć w pliku Prodmix.xlsx, pokazano na ilustracji 27-1.

Obraz książki
Rysunek 27-1 produkt mix

Załóżmy, że pracujemy nad firmy leków dająca sześć różnych produktów w ich fabryki. Produkcji każdego produktu wymaga pracy i surowca. Wiersz 4 w rysunek 27-1 pokazano liczbę godzin pracy potrzebne do utworzenia Funt każdego produktu oraz wiersz 5 jednostkach funt materiału potrzebne do utworzenia Funt każdego produktu. Na przykład produkcji numeru produktu 1 wymaga sześć godzin pracy i 3,2 jednostkach funt materiału. Dla każdego leków kwotę w przeliczeniu na funt znajduje się w wierszu 6, koszt jednostkowy na funt znajduje się w wierszu 7 i udział w zysku na funt znajduje się w wierszu 9. Na przykład produkt 2 sprzedawanych dla 11,00 $ na funt, ponosi koszt jednostkowy 5.70 $ na funt i składa się zysku $5.30 na funt. Żądanie miesiąca dla każdego leków znajduje się w wierszu 8. Na przykład żądanie produkt 3 to 1041 jednostkach funt. Ten miesiąc, 4500 godzin pracy i 1600 jednostkach funt materiału są dostępne. Jak to przedsiębiorstwo maksymalizowanie miesięczny zysku

Jeśli firma Microsoft wiedziała nic o dodatek Excel Solver, firma Microsoft może ataki ten problem, tworząc arkusza do śledzenia użycia zasobów i zysku skojarzone z produktu pakietu Office mix. Następnie jako należy użyć wersji próbnej i błędów się różnić mix produktu, aby zoptymalizować zysku, bez użycia więcej pracy lub surowiec nie jest dostępny i bez wytwarzania dowolnego leków powyżej żądanie. Używamy dodatku Solver w tym procesie tylko na etapie prób i błędów. Przede wszystkim dodatek Solver jest Optymalizacja aparatu flawlessly wykonujące wyszukiwania prób i błędów.

Klucz do rozwiązywania problemu mix produktów jest efektywne obliczyć użycia zasobów i zysku skojarzonych z dowolnym mix danego produktu. Ważnym narzędziem, które możemy użyć, aby wprowadzić te obliczenia jest funkcja suma. Funkcja suma mnoży odpowiadających sobie wartości w zakresach komórek i zwraca sumę tych wartości. Każdy zakres komórek używane w oceny suma musi mieć tych samych wymiarach, co oznacza, że można używać funkcji Suma.iloczynów z wierszami lub kolumnami, ale nie z jedną kolumnę i jeden wiersz.

Jako przykład tego, jak firma Microsoft mogą używać funkcji Suma w naszym produktu mieszać przykład, spróbuj do obliczenia naszych użycie zasobu. Nasze zastosowania pracy jest obliczana na podstawie

*(Drug 1 pounds produced) (pracy używane na funt leków 1) +
(pracy używane na funt leków 2) * (leku 2 jednostkach funt wyprodukowano) +...
(Pracy używane na funt leków 6) * (6 jednostkach funt wyprodukowano narkotyków)

Firma Microsoft może obliczyć zastosowania pracy w sposób bardziej nużące jako D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4. Podobnie zużycia surowca może zostać obliczona jako D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * I5. Wprowadzanie formuły w arkuszu związane z produktami sześć jest jednak czasochłonne. Załóżmy, jak długo będzie trwać Jeśli pracy w firmie, że wyprodukowano, na przykład 50 produktów po ich fabryki. Wiele łatwiejszy sposób obliczyć pracy i zastosowania surowiec jest kopiowanie z D14 do D15 formuły SUMPRODUCT($D$2:$I$2,D4:I4). Ta formuła oblicza D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (czyli naszych zastosowania pracy), ale jest o wiele łatwiejsze wprowadzić! Zwróć uwagę, można użyć znaku $ zakres D2:I2 tak, aby podczas kopiowania formuły można nadal przechwycić mix produktu z wiersza 2. Formuła w komórce D15 oblicza zużycia surowca.

W podobny sposób naszych zysk jest określona przez

(Leku 1 zysku na funt) * (realizowane jednostkach funt leków 1) +
(2 leków zysk na funt) * (leku 2 jednostkach funt wyprodukowano) +...
(6 leków zysk na funt) * (6 jednostkach funt wyprodukowano narkotyków)

Zestawienie zysków łatwo jest obliczana w komórce D12 formułą SUMPRODUCT(D9:I9,$D$2:$I$2).

Firma Microsoft obecnie określają trzy części naszych mix produktu modelu dodatku Solver.

  • Komórkę docelową. Celem jest maksymalizowanie zysku (obliczona w komórce D12).

  • Liczba zmian komórek. Liczba jednostkach funt wyprodukowano każdego produktu (wymienione w zakresie komórek D2:I2)

  • Ograniczeń. Udostępniamy następujące ograniczenia:

    • Nie używaj więcej pracy lub surowiec nie jest dostępna. Oznacza to, że wartości w komórkach D14:D15 (zasobów używanych) musi być mniejsza niż lub równa wartości komórek F14:F15 (dostępne zasobów).

    • Nie daje więcej leków niż na żądanie. Oznacza to, że wartości w D2:I2 komórek (jednostkach funt wyprodukowano każdego leków) musi być mniejsza niż lub równa argumentowi żądanie dla każdego leków (wymienione w D8:I8 komórek).

    • Firma Microsoft nie może utworzyć liczbą ujemną dowolnego leków.

I opisano sposób wprowadzania komórkę docelową Zmiana komórki i ograniczenia w dodatku Solver. Następnie to wszystko, co należy zrobić, kliknij przycisk Rozwiąż, aby znaleźć maksymalizacja zysków Miks produktu!

Aby rozpocząć, kliknij kartę dane, a w grupie analiza kliknij dodatku Solver.

Uwaga: Zgodnie z opisem w rozdział 26 "wprowadzenie do optymalizacji z dodatek Excel Solver," dodatku Solver jest zainstalowany, klikając przycisk pakietu Microsoft Office, następnie Opcje programu Excel, a po nim dodatki. Na liście Zarządzaj kliknij pozycję Dodatki programu Excel, zaznacz pole dodatek Solver, a następnie kliknij przycisk OK.

Okno dialogowe Solver — Parametry pojawi się, jak pokazano na ilustracji 27-2.

Obraz książki
Okno dialogowe Solver — Parametry rysunek 27-2

Kliknij pole komórka celu, a następnie zaznacz komórkę naszych zysku (komórka D12). Kliknij pole przez zmienianie komórek, a następnie wskaż zakres D2:I2, zawierający wyprodukowano jednostkach funt każdego leków. Okno dialogowe powinna wyglądać rysunek 27-3.

Obraz książki
Okno dialogowe Solver — Parametry rysunek 27-3 z komórką docelową i zmieniania komórki zdefiniowane

Teraz możemy gotowe do dodania ograniczenia do modelu. Kliknij przycisk Dodaj. Pojawi się okno dialogowe Dodawanie ograniczenia, pokazano na ilustracji 27-4.

Obraz książki
Okno dialogowe Dodawanie ograniczenia rysunek 27-4

Aby dodać ograniczenia użycia zasobów, kliknij pole odwołanie do komórki, a następnie zaznacz zakres D14:D15. Wybierz pozycję < = na liście drugie imię. Kliknij pole ograniczenia, a następnie zaznacz zakres komórek F14:F15. Okno dialogowe Dodawanie ograniczenia powinna wyglądać podobnie jak rysunek 27-5.

Obraz książki
Okno dialogowe Dodawanie ograniczenia rysunek 27-5 z ograniczeniami użycia zasobów wprowadzone

Firma Microsoft mają teraz zapewnić, że po dodatku Solver spróbuje zmianę różne wartości komórek, tylko kombinacje, które spełniają oba D14 < = F14 (pracy używany jest mniejsza niż lub równa pracy dostępne) i D15 < = klawisz F15 (użytego surowca jest mniejsza niż lub równe uznaje się surowca dostępne). Kliknij przycisk Dodaj, aby wprowadzić ograniczenia żądanie. Wypełnij okno dialogowe Dodawanie ograniczenia, jak pokazano na ilustracji 27-6.

Obraz książki
Okno dialogowe Dodawanie ograniczenia rysunek 27-6 z ograniczeniami żądanie wprowadzone

Dodanie tych ograniczeń zapewnia, że gdy dodatku Solver próbuje różne kombinacje zmiana wartości komórek, tylko kombinacje, które spełniają następujące parametry uznaje się:

  • D2 < = D8 (kwota wyprodukowano leków 1 jest mniejsza niż lub równa argumentowi żądanie leków 1)

  • E2 < = E8 (ilość wyprodukowano 2 leków jest mniejsza niż lub równa argumentowi żądanie leków 2)

  • F2 < = F8 (kwotę wynikającą z narkotykami 3 wprowadzone jest mniejsza niż lub równa argumentowi żądanie leków 3)

  • G2 < = G8 (kwota wyprodukowano 4 leków wprowadzone jest mniejsza niż lub równa argumentowi żądanie leków 4)

  • H2 < = H8 (kwota wyprodukowano 5 leków wprowadzone jest mniejsza niż lub równa argumentowi żądanie leków 5)

  • I2 < = I8 (kwota wyprodukowano 6 leków wprowadzone jest mniejsza niż lub równa argumentowi żądanie leków 6)

Kliknij przycisk OK w oknie dialogowym Dodawanie ograniczenia. Okno dodatku Solver powinna wyglądać podobnie jak rysunek 27-7.

Obraz książki
Rysunek 27-7 końcowe okno dialogowe Solver — Parametry problemu mix produktu

Firma Microsoft wprowadź ograniczenia, które zmiana komórki musi być nieujemna w oknie dialogowym Opcje dodatku Solver. Kliknij przycisk Opcje w oknie dialogowym Parametry dodatku Solver. Zaznacz pole Model liniowy i w polu nieujemna przyjęto założenie, jak pokazano na ilustracji 27-8 na następnej stronie. Kliknij przycisk OK.

Obraz książki
Ustawienia argumentu Opcje rysunek 27-8 dodatku Solver

Zaznaczając pole nieujemna założono gwarantuje, że dodatek Solver są uwzględnione tylko kombinacje zmian komórek, w których każdej komórki, zmienianie przyjmuje wartość ujemna. Firma Microsoft zaznaczone, że pole Model liniowy, ponieważ produktu mieszać problemu jest specjalnego typu problem dodatku Solver o nazwie model liniowy. Model dodatku Solver to zasadniczo liniowy w następujących warunkach:

  • W komórce docelowej jest obliczana przez zsumowanie warunków formularza (zmiana cell)*(constant).

  • Każde ograniczenie spełnia "wymóg model liniowy". Oznacza to, że każde ograniczenie jest obliczane przez dodanie warunków formularza (zmiana cell)*(constant) i porównywanie kwot, które mają stałą.

Ten problem dodatku Solver jest liniowe Nasze komórkę docelową (zysk) jest obliczana jako

(Leku 1 zysku na funt) * (realizowane jednostkach funt leków 1) +
(2 leków zysk na funt) * (leku 2 jednostkach funt wyprodukowano) +...
(6 leków zysk na funt) * (6 jednostkach funt wyprodukowano narkotyków)

Te obliczenia następuje deseń obliczoną wartość komórki docelowej, dodając ze sobą terminów formularza (zmiana cell)*(constant).

Nasze ograniczenia pracy jest obliczane przez porównanie wartości pochodzące z (pracy używane na funt leków 1) * (realizowane jednostkach funt leków 1) + (Praca używane na funt leków 2) *(Drug 2 pounds produced) +... (Robociznę usEdytuj na funt leków 6) * (6 jednostkach funt wyprodukowano narkotyków) do pracy dostępne.

W związku z tym, ograniczenie pracy jest szacowana, dodając warunków formularza (zmiana cell)*(constant) i porównywanie kwot, które mają stałą. Zarówno ograniczenie pracy, jak i ograniczenia surowca spełniających wymagania model liniowy.

Nasze ograniczeń żądanie formę

(Leków 1 wyprodukowano) < = (żądanie leków 1)
(2 leków wyprodukowano) < = (żądanie leków 2)
§
(6 leków wyprodukowano) < = (żądanie leków 6)

Każde ograniczenie żądanie również spełnia wymagania model liniowy, ponieważ każdy jest szacowana, dodając warunków formularza (zmiana cell)*(constant) i porównywanie kwot, które mają stałą.

O pokazano, że naszego modelu mix produktu jest model liniowy, dlaczego warto możemy zwracać?

  • Jeśli modelu dodatku Solver jest liniowy i firma Microsoft wybierz Model liniowy, aby znaleźć optymalne rozwiązanie do modelu dodatku Solver jest gwarantowana dodatku Solver. Jeśli modelu dodatku Solver nie jest liniowy, dodatek Solver może lub nie może znaleźć optymalne rozwiązanie.

  • Jeśli modelu dodatku Solver jest liniowy i firma Microsoft wybierz Model liniowy, dodatek Solver używa algorytmu dobry (metoda simpleks) aby znaleźć optymalne rozwiązanie modelu. Jeśli model dodatku Solver jest liniowy i firma Microsoft nie należy zaznaczać Model liniowy, dodatek Solver używa algorytmu bardzo nieefektywne (metoda GRG2) i może być problemów ze znalezieniem optymalne rozwiązanie modelu.

Po kliknięciu przycisku OK w oknie dialogowym Opcje dodatku Solver, możemy powrócić do głównego okno dialogowe Solver pokazano wcześniej w rysunek 27-7. Po kliknięciu przycisku Rozwiąż, dodatek Solver oblicza optymalne rozwiązanie (jeśli istnieje) dla naszych modelu mix produktu. Jak mogę podano w rozdział 26, optymalne rozwiązanie do modelu mix produktu będzie zestaw zmiana wartości komórek (jednostkach funt wyprodukowano każdego leków) maksymalizuje zysku zestawu wszystkie możliwe rozwiązania. Ponownie wiarygodnego rozwiązania to zbiór zmiana wartości komórek spełniających wszystkie ograniczenia. Zmiana wartości komórek pokazano na ilustracji 27-9 są rozwiązaniem jest to możliwe, ponieważ wszystkie poziomy produkcyjnej jest ujemna, poziomu produkcji nie przekracza żądanie i użycie zasobu nie przekracza dostępne zasoby.

Obraz książki
Rysunek 27-9 A dopuszczalnego rozwiązania produktu mieszać problem mieści się w przypadku ograniczeń.

Zmiana wartości komórek pokazano na ilustracji 27-10 na następnej stronie reprezentują praktyce rozwiązanie z następujących powodów:

  • Firma Microsoft większej liczby 5 leków niż żądanie dla tego produktu.

  • Firma Microsoft korzysta z pracy więcej niż są dostępne.

  • Firma Microsoft korzysta z więcej surowca niż są dostępne.

Obraz książki
Rysunek 27-10 praktyce rozwiązanie tego problemu mix produktu nie mieszczą się zdefiniowane ograniczenia.

Po kliknięciu przycisku Rozwiąż, szybko znalezieniem optymalne rozwiązanie pokazano na ilustracji 27-11. Należy wybrać Zachowaj rozwiązanie dodatku Solver, aby zachować wartości optymalne rozwiązanie w arkuszu.

Obraz książki
Rysunek 27-11 optymalne rozwiązanie tego problemu mix produktu

Firmy leków można zmaksymalizować miesięczny zysku na poziomie $6,625.20 przedstawiając 596.67 jednostkach funt 4 leków, 1084 jednostkach funt 5 leków i żaden z innych leków! Firma Microsoft nie może ustalić, jeśli możemy maksymalny zysk 6,625.20 $ w inny sposób. Firma Microsoft może być pewności co do wszystkich jest z naszych ograniczonych zasobów i żądanie, można wprowadzić więcej niż 6,627.20 $ w tym miesiącu.

Załóżmy, że żądanie dla każdego produktu, muszą być spełnione. (Zobacz arkusza Nie dopuszczalnego rozwiązania w pliku Prodmix.xlsx). Mamy następnie zmień naszych ograniczeń żądanie z D2:I2 < = D8:I8 do D2:I2 > = D8:I8. W tym celu należy otworzyć dodatku Solver, wybierz pozycję D2:I2 < = D8:I8 ograniczenia, a następnie kliknij przycisk Zmień. Pojawi się okno dialogowe Zmienianie ograniczenia, pokazano na ilustracji 27-12.

Obraz książki
Okno dialogowe Zmienianie ograniczenia rysunek 27-12

Zaznacz > =, a następnie kliknij przycisk OK. Firma Microsoft została teraz zapewnić, że dodatek Solver będzie rozważyć zmianę tylko wartości komórek, które spełniają wszystkie wymagania. Po kliknięciu przycisku Rozwiąż, pojawi się komunikat "Nie można znaleźć wiarygodnego rozwiązania." Ten komunikat nie oznacza, że wprowadzono błąd w naszym model, a które z naszych ograniczonych zasobów, firma Microsoft nie spełnia żądanie dla wszystkich produktów. Dodatek Solver jest po prostu Przekaż nam że jeśli chcemy zapotrzebowania dla każdego produktu, trzeba dodać więcej pracy, więcej materiałów lub więcej z obu.

Zobaczmy, co się stanie, jeśli firma Microsoft zezwalanie na nieograniczoną żądanie dla każdego produktu i dopuszczalną ilości ujemne, które należy opracować każdego leków. (Ten problem dodatku Solver można znaleźć w arkuszu Ustaw wartości nie są zbieżne w pliku Prodmix.xlsx.) Aby znaleźć optymalne rozwiązanie w takiej sytuacji, otwórz dodatku Solver, kliknij przycisk Opcje, a następnie wyczyść pole nieujemna przyjęto założenie. W oknie dialogowym Parametry dodatku Solver wybierz ograniczenie żądanie D2:I2 < = D8:I8, a następnie kliknij przycisk Usuń, aby usunąć ograniczenia. Po kliknięciu przycisku Rozwiąż dodatek Solver zwraca komunikat "Wartości komórek zestawu nie są zbieżne" Ten komunikat oznacza, że jeśli w komórce docelowej ma jest zmaksymalizowany (tak jak w naszym przykładzie), dopuszczalnych rozwiązań z dowolnie dużą docelowej wartości komórek. (W przypadku komórce minimalizowanie, komunikat "Ustawianie komórki wartości nie są zbieżne" oznacza, że istnieje dopuszczalnych rozwiązań z wartości komórek dowolnie małych docelowej.) W naszym sytuacji, umożliwiając ujemne produkcji leków obowiązywać "tworzymy" zasoby, które mogą być używane do utworzenia dowolnie dużych ilości innych leków. Mieć naszych nieograniczoną żądanie, dzięki nam nawiązać nieograniczoną zysków. W przypadku rzeczywistą nie udzielamy nieskończonej kwoty. Innymi słowy Jeśli widzisz "Ustawić wartości nie są zbieżne", modelu zawiera błąd.

  1. Załóżmy, że firmy leków kupić do 500 godzin pracy w $1 / godz ponad bieżącej kosztów pracy. Jak można zmaksymalizować zysku?

  2. W układzie produkcji cztery techników (A, B, C i D) warzywa trzy products (produkty 1, 2 i 3). Ten miesiąc, sprzedać producenta układzie 80 jednostki produkt 1, 50 jednostek produkt 2 i co najwyżej 50 jednostek produkt 3. Technika A może mieć tylko produktów 1 i 3. Technika B może mieć tylko produktów 1 i 2. Technika C, można wprowadzać tylko produkt 3. Technika D mogą być tylko produkt 2. Dla każdej jednostki wyprodukowane, produktów do współtworzenia następujące zysku: produkt 1 $6; Produkt 2, $7; a produkt 3, 10 zł. Czas (w godzinach) każdego technik należy produkowanie produktu jest następująca:

    Produktu

    Technika odpowiedzi

    Technika B

    Technika C

    Technika D

    1

    2

    2,5

    Nie

    Nie

    2

    Nie

    3

    Nie

    3,5

    3

    3

    Nie

    4

    Nie

  3. Każdy technik pracować do 120 godzin na miesiąc. Jak producenta układzie maksymalizowanie miesięczny zysku Załóżmy, że może utworzyć ułamkowa liczba jednostek.

  4. Komputer produkcji tworzy myszy, klawiatury i joysticki gier wideo. Zysk na jednostkę, zastosowania pracy na jednostkę, żądanie miesięczny i zastosowania czas komputera na jednostkę są podane w poniższej tabeli:

    Myszy

    Klawiatury

    Joysticki

    Zestawienie zysków i jednostki

    $8

    $11

    $9

    Użycie pracę i jednostki

    .2 godziny

    .3 godziny

    Godzina.24

    Jednostki czasu komputera

    Godzina.04

    Godzina.055

    Godzina.04

    Żądanie miesięczny

    15 000

    27 000

    11 000

  5. Każdego miesiąca Suma 13 000 godzin pracy i 3000 godzin czas komputera są dostępne. Jak producenta maksymalizowanie jego Miesięczny udział zysków z zakładu

  6. Rozwiązywanie problemów naszych leków przykład przy założeniu minimalne żądanie 200 jednostek czasu dla każdego leków muszą być spełnione.

  7. JASON sprawia, że bransoletek romb, necklaces i kolczyków. Chce pracować maksymalnie 160 godzin na miesiąc. Ma on 800 uncji romby. Poniżej podano zysku, godziny pracy i uncji romby wymagane do utworzenia każdego produktu. W przypadku nieograniczoną żądanie dla każdego produktu, jak można Jason zmaksymalizować jego zysku?

    Produkt

    Jednostka zysku

    Godziny robocze na jednostkę

    Uncji romby na jednostkę

    Bransoleta

    300 zł

    .35

    1.2

    Necklace

    200 zł

    .15

    .75

    Kolczyków

    100 zł

    0,05

    0,5

Rozwijaj umiejętności związane z pakietem Office
Poznaj szkolenia
Uzyskuj nowe funkcje w pierwszej kolejności
Dołącz do niejawnych testerów pakietu Office

Czy te informacje były pomocne?

Dziękujemy za opinię!

Dziękujemy za opinię! Wygląda na to, że połączenie Cię z jednym z naszych agentów pomocy technicznej pakietu Office może być pomocne.

×