{"id":6893,"date":"2019-08-28T10:32:46","date_gmt":"2019-08-28T09:32:46","guid":{"rendered":"http:\/\/smart--grid.net\/?page_id=6893"},"modified":"2022-12-03T23:02:06","modified_gmt":"2022-12-03T22:02:06","slug":"resolution-affectation-avec-excel","status":"publish","type":"page","link":"https:\/\/complex-systems-ai.com\/en\/planning-problem\/resolution-assignment-with-excel\/","title":{"rendered":"Resolution Assignment with Excel"},"content":{"rendered":"<div data-elementor-type=\"wp-page\" data-elementor-id=\"6893\" class=\"elementor elementor-6893\">\n\t\t\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-7455d8c elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"7455d8c\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-ef4009d\" data-id=\"ef4009d\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-a7b7114 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"a7b7114\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/planning-problem\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\"> Planning problem<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-f972f28\" data-id=\"f972f28\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-e7e65f1 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"e7e65f1\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/complex-systems-ai.com\/en\/\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Home page<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t<div class=\"elementor-column elementor-col-33 elementor-top-column elementor-element elementor-element-d8e7d10\" data-id=\"d8e7d10\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-59ef8e7 elementor-align-justify elementor-widget elementor-widget-button\" data-id=\"59ef8e7\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"button.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t<div class=\"elementor-button-wrapper\">\n\t\t\t\t\t<a class=\"elementor-button elementor-button-link elementor-size-sm\" href=\"https:\/\/fr.wikipedia.org\/wiki\/Probl%C3%A8me_d%27affectation\" target=\"_blank\" rel=\"noopener\">\n\t\t\t\t\t\t<span class=\"elementor-button-content-wrapper\">\n\t\t\t\t\t\t\t\t\t<span class=\"elementor-button-text\">Wiki<\/span>\n\t\t\t\t\t<\/span>\n\t\t\t\t\t<\/a>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<section class=\"elementor-section elementor-top-section elementor-element elementor-element-5008f551 elementor-section-boxed elementor-section-height-default elementor-section-height-default\" data-id=\"5008f551\" data-element_type=\"section\" data-e-type=\"section\">\n\t\t\t\t\t\t<div class=\"elementor-container elementor-column-gap-default\">\n\t\t\t\t\t<div class=\"elementor-column elementor-col-100 elementor-top-column elementor-element elementor-element-761ee189\" data-id=\"761ee189\" data-element_type=\"column\" data-e-type=\"column\">\n\t\t\t<div class=\"elementor-widget-wrap elementor-element-populated\">\n\t\t\t\t\t\t<div class=\"elementor-element elementor-element-6da29d1d elementor-widget elementor-widget-text-editor\" data-id=\"6da29d1d\" data-element_type=\"widget\" data-e-type=\"widget\" data-widget_type=\"text-editor.default\">\n\t\t\t\t<div class=\"elementor-widget-container\">\n\t\t\t\t\t\t\t\t\t\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/complex-systems-ai.com\/en\/planning-problem\/resolution-assignment-with-excel\/#Resolution-probleme-daffectation-avec-Excel\" >Solving assignment problem with Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/complex-systems-ai.com\/en\/planning-problem\/resolution-assignment-with-excel\/#Resoudre-le-modele\" >Solve the model<\/a><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Resolution-probleme-daffectation-avec-Excel\"><\/span>Solving assignment problem with Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Here&#039;s how to troubleshoot assignment problems with Excel.<\/p>\n\n<p>To formulate an assignment problem, it is necessary to answer these three questions.<\/p>\n\n<ul class=\"wp-block-list\">\n<li>What are the decisions to be made? For this problem, we need Excel to know which person to assign to which task (Yes = 1, No = 0). For example, if we assign person 1 to task 1, cell C10 is equal to 1. Otherwise, cell C10 is equal to 0. (in yellow)<\/li>\n<li>What are the constraints on these decisions? Each person can only perform one task (Supply = 1). Each task only needs one person (Request = 1). (in light blue)<\/li>\n<li>What is the overall measure of performance for these decisions? The overall measure of performance is the total cost of the assignment. The objective is therefore to minimize this quantity. (in dark blue)<\/li>\n<\/ul>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img fetchpriority=\"high\" decoding=\"async\" class=\"alignnone wp-image-6862 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp41.png\" alt=\"problem solving assignment with Excel simplex\" width=\"604\" height=\"324\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp41.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp41-300x161.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\n<p>Let us name the following ranges:<\/p>\n\n<figure class=\"wp-block-table gridtable\">\n<table>\n<tbody>\n<tr>\n<th>Beach name<\/th>\n<th>Cells<\/th>\n<\/tr>\n<tr>\n<td>Cost<\/td>\n<td>C4: E6<\/td>\n<\/tr>\n<tr>\n<td>assignment<\/td>\n<td>C10: E12<\/td>\n<\/tr>\n<tr>\n<td>PersonsAssigned<\/td>\n<td>C14: E14<\/td>\n<\/tr>\n<tr>\n<td>Demand<\/td>\n<td>C16: E16<\/td>\n<\/tr>\n<tr>\n<td>TasksAssigned<\/td>\n<td>G10: G12<\/td>\n<\/tr>\n<tr>\n<td>Supply<\/td>\n<td>I10: I12<\/td>\n<\/tr>\n<tr>\n<td>TotalCost<\/td>\n<td>I16<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/figure>\n\n<p>And let&#039;s insert the following functions:<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" class=\"alignnone wp-image-6863 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp42.png\" alt=\"solving assignment problem with Excel simplex\" width=\"604\" height=\"324\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp42.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp42-300x161.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Resoudre-le-modele\"><\/span>Solve the model<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n<p>Let\u2019s enter the solver parameters:<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img decoding=\"async\" class=\"alignnone wp-image-6864 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp43.png\" alt=\"solving assignment problem with Excel simplex\" width=\"570\" height=\"599\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp43.png 570w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp43-285x300.png 285w\" sizes=\"(max-width: 570px) 100vw, 570px\" \/><\/figure>\n<\/div>\n\n<p>The optimal solution is:<\/p>\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-6865 size-full\" src=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp44.png\" alt=\"solving assignment problem with Excel simplex\" width=\"604\" height=\"324\" title=\"\" srcset=\"https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp44.png 604w, https:\/\/complex-systems-ai.com\/wp-content\/uploads\/2019\/04\/lp44-300x161.png 300w\" sizes=\"(max-width: 604px) 100vw, 604px\" \/><\/figure>\n<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"<p>Planning Problem Wiki Home Solving Assignment Problem with Excel Here&#039;s how to solve assignment problems with Excel. To formulate an assignment problem, he ... <\/p>","protected":false},"author":1,"featured_media":0,"parent":868,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-6893","page","type-page","status-publish","hentry"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6893","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/comments?post=6893"}],"version-history":[{"count":3,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6893\/revisions"}],"predecessor-version":[{"id":16940,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/6893\/revisions\/16940"}],"up":[{"embeddable":true,"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/pages\/868"}],"wp:attachment":[{"href":"https:\/\/complex-systems-ai.com\/en\/wp-json\/wp\/v2\/media?parent=6893"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}