Optimization in Microsoft Excel has over the years become popular because of the built-in solver in Excel. Spreadsheet optimization allows users to create models that are easy to use, enabling the user to quickly update the data and solve the model. Spreadsheets are efficient at handling and managing two-dimensional dense data (rows and columns) and single scalar values.
Excel has the Visual Basic programming language built in, which enables easy pre- and post-processing of the data. The solution from the optimization can then be used to update the spreadsheet, and to create graphs that visually represent the solution. Excel provides some excellent user-interface capabilities for optimization models. MPL builds on this by offering the ability to import and export data directly from Excel ranges. This allows the developer to use an Excel spreadsheet for the user interface and data manipulation while using MPL to specify the model formulation. The model can then be solved with any solver supported by MPL with no limits on the size, speed or robustness of the solution.
Furthermore, MPL is available as a part of the OptiMax 2000 Component Library, which is specially designed to embed optimization models into application programs, such as Excel. OptiMax 2000 allows MPL models to be linked directly with the Visual Basic for Applications language in Excel enabling the developer to create large-scale optimization models that can be solved directly from an Excel spreadsheet. Please contact Maximal Software for more details.
In the DATA section, where you define the data vector, enter the keyword EXCELRANGE after the assignment symbol (:=), followed by parentheses containing the Excel workbook filename and the Excel range name you want to import from.
To give an example of how data is imported from an Excel range, here is an MPL statement that is used to read in the pattern cuts generated for a cutting stock model:
DATA
CutsInPattern[patterns, cuts] := EXCELRANGE("Cutstock.xls","Patterns");
In the above example, MPL will open the Excel spreadsheet Cutstock.xls, locate the range Patterns, and then retrieve the entries for the MPL data vector CutsInPattern.
In some cases, you will want to read only a specific column from the range. MPL allows you to specify which column to read byentering a comma and the column number after the range name.
The EXCELRANGE command is used when the data is stored in a dense format in the spreadsheet. In some instances it is better to store the data in a sparse column format where the first columns store the values for the indexes followed by columns containing the data values. In this case you use the EXCELSPARSE command to specify that the data is to be read in a sparse format. For example:
DATA
CutWidths[cuts] := EXCELSPARSE("CutsTable", 2);
CutDemand[cuts] := EXCELSPARSE("CutsTable", 3);
In this example the Excel range CutsTable contains three columns. The first column stores the values for the index Cuts followed by two columns containing the widths and the demand for each cut. The column number 2 given means the second column in the range while 3 specifies the third column in the range.
If the Excel range contains the data as an Excel List there will be an extra row at the top of the range that contains the name of each column. MPL allows you to skip that row automatically by using the EXCELLIST command instead of the EXCELSPARSE command.
Also note, that if you are reading multiple indexes and data vectors from the same Excel spreadsheet, you can omit the workbook filename on all entries after the first one.