中彩网|首页|官网电脑版

                                Manipulating Tables

                                This post is also available in: Italian

                                Turn Excel spreadsheets into powerful planning and scheduling software.? These simple data manipulation techniques process large tables in a systematic way.? Data processing tasks are broken into steps, with one or more steps performed on a single worksheet.

                                The method enables you to structure complex planning and scheduling routines into simple stages.? This brings agile software development techniques to Excel without having to learn programming code.

                                In an Excel-based planning system, each worksheet will typically pull in table data, perform calculations and then present the results to another worksheet or table. Much of the routine is manipulating data, applying formula and presenting it to the next process.

                                With normalised tables you will often have data to be joined, stacked, exploded or pivoted.? The information and calculation flow in a system is mostly made up of a series of these table operations.

                                Joining tables

                                Joining tables is equivalent to a database query. We are using a column of one table as the key to find a unique value associated with it from another table. For example, we might look up the model type for an item code in the order book by joining it with part of the item master.

                                Experienced users of Excel might be familiar with the VLOOKUP function. With Fast Excel, there is a better way of joining tables using INDEX and MATCH. This is a much more powerful method and results in fast, accurate table joins over hundred thousand rows of data.

                                Stacking tables

                                Stacking tables is simply combining two or more tables with the same level of uniqueness. We might wish to take order or forecasts from different regions and combine them into the same table.

                                Often, the format or columns are different and Fast Excel has a method that is flexible to stack data from many different table formats quickly and easily.

                                The Fast Excel Development Template has a automatic table stacker that can help you to combine multiple sources of data without needing complex formulas. Download the Development Template here.

                                Exploding tables

                                Exploding tables takes summary-level data and explodes each record according to its lower level relationships. The most common explosion in production scheduling is exploding sales demand against its dependent items or exploding a list of parts-to-make against the process routing for all operations required.

                                The Fast Excel Method uses simple formula logic to perform BOM and Routing explosions. This way enables you to take actual customer demand and calculate the exact resources required in minutes and seconds to fill customer demand. This supports powerful capacity planning functions and feeds the scheduler with the demand and process time requirement.

                                Next: Automating with VBA macros.

                                Leave a Reply

                                Scroll to Top

                                                              education

                                                              culture

                                                              game

                                                              Mobile phone

                                                              Real estate

                                                              Finance

                                                              Super League

                                                              society

                                                              game