Excel: Difference between revisions
No edit summary |
|||
| (43 intermediate revisions by 7 users not shown) | |||
| Line 1: | Line 1: | ||
[[File:Tql_webinar.png|link=https://www.tygron.com/nl/2019/05/22/tql-en-upgrades/]] | |||
[[Category:files]] | |||
==What is an Excel file== | ==What is an Excel file== | ||
| Line 6: | Line 7: | ||
Excel files can be created and edited using a variety of programs. The most obvious program is by using Microsoft Office's Excel. Alternative programs include OpenOffice or LibreOffice, although minor differences in both use and calculation can occur, and support for these differences may be limited. | Excel files can be created and edited using a variety of programs. The most obvious program is by using Microsoft Office's Excel. Alternative programs include OpenOffice or LibreOffice, although minor differences in both use and calculation can occur, and support for these differences may be limited. | ||
For the | For the {{software}}, the file format .xlsx is specifically required. | ||
==How do Excel files relate to the | ==How do Excel files relate to the {{software}}== | ||
One of the core functions of the | One of the core functions of the {{software}} is to calculate prospective effects of actions and scenarios. Depending on the case being explored, what exactly to calculate and the precise method of calculation can vary. To provide the greatest freedom in the ability to configure a project with relevant, accurate, and third-party approved calculation models, the {{software}} allows some calculation to be made in Excel files. Most notably [[indicators]] can easily be created or adjusted by inspecting and editing the underlying Excel file. By defining specific cells to act as input and output for the calculation, the {{software}} can perform calculations using user-defined Excel files. | ||
Excels can also output text to provide information about the current state of the [[Session]] or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with [[HTML]]. The | Excels can also output text to provide information about the current state of the [[Session]] or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with [[HTML]]. The {{software}} will interpret the text output of an Excel file as [[HTML]] if possible. | ||
==General structure of Excel files== | ==General structure of Excel files== | ||
Excel files, in general, have three parts to them: input, calculation, and output. The input and output are "connected" to the | Excel files, in general, have three parts to them: input, calculation, and output. The input and output are "connected" to the {{software}}. The contents of each input cell is changed by the {{software}} to reflect some form of data from the [[project]]. Each output cell is eventually read by the {{software}}, and its content used in some fashion. Input cells, and some of the output cells, are defined using [[TQL]]. | ||
===Cell types=== | ===Cell types=== | ||
| Line 21: | Line 22: | ||
====Output cells==== | ====Output cells==== | ||
[[File:Explanation_named_cell.jpg|framed|right|In this image the cell B2 is named "EXPLANATION". This serves as an output cell. For its content it refers to a different cell.]] | [[File:Explanation_named_cell.jpg|framed|right|In this image the cell B2 is named "EXPLANATION". This serves as an output cell. For its content it refers to a different cell.]] | ||
The most important part the | The most important part of the Excel file is the output. The {{software}} will detect each output cell, and calculate their contents. If those cells reference other cells, those other cells will be calculated as well. The {{software}} will continue this until the contents for all output cells are calculated. Each component which uses an Excel file for its calculation has one or more required output cells. The output can then be used during a [[session]]. Additional output cells are created when [[TQL]] <code>update</code> statements are added to the Excel. | ||
'''Explanation'''<br> | |||
: Each Excel file should have an <code>EXPLANATION</code> cell. The content in this cell is text, which can be displayed as a description or explanation of what is happening, either in the calculation or in the project as a whole. | |||
'''Score'''<br> | |||
: Some Excel files, such as those for [[Indicator]]s and [[Zoning#Permit calculations|Zoning excels]], require a <code>SCORE</code> cell. The content in this cell is a number up to 1 (inclusive). The exact meaning of the number in this cell varies by component.{{clear|right}} | |||
====Input cells==== | ====Input cells==== | ||
[[File:Query_named_cell.jpg|framed|right|In this image the cell B4 is named "SELECT_UNITS_WHERE_STATE_IS_READY". This serves as an input cell. It has a value, which will be overwritten by the | [[File:Query_named_cell.jpg|framed|right|In this image the cell B4 is named "SELECT_UNITS_WHERE_STATE_IS_READY". This serves as an input cell. It has a value, which will be overwritten by the {{software}}.]] | ||
Next to the output cells, the input cells are most important. You will generally want your Excel file to produce different outputs based on the current state of the project or session. Input cells are always defined using [[TQL]]. When the Excel file is uploaded and used by the | Next to the output cells, the input cells are most important. You will generally want your Excel file to produce different outputs based on the current state of the project or session. Input cells are always defined using [[TQL]]. When the Excel file is uploaded and used by the {{software}}, the content of the input cells will be overwritten by the {{software}} with the proper, requested value.{{clear|right}} | ||
====Calculation cells==== | ====Calculation cells==== | ||
[[File:Calculation_cell.jpg|framed|right|In this image the cell D2 contains a formula. It's not directly overwritten by the | [[File:Calculation_cell.jpg|framed|right|In this image the cell D2 contains a formula. It's not directly overwritten by the {{software}}, but the results change based on input from the {{software}}. It is referred to by an output cell, so this is a calculation cell.]] | ||
In between, there are the calculation cells. It's likely you don't want to output the data from the | In between, there are the calculation cells. It's likely you don't want to output the data from the {{software}} directly. Instead, you want to use it in some form of calculation, and display only the result, likely with some formatting. For example, you may not want to return the amount of green and the amount of houses, but the [[Green (Indicator)|amount of green per house]], rounded to a decimal or two, with some unit appended to it. Any cell, even empty cells, are deemed to be calculation cells if they are referenced by an output cell, because their contents are calculated (evaluated) to determine what the output will be. <br style="clear:right;"> | ||
====Unreferenced cells==== | ====Unreferenced cells==== | ||
| Line 37: | Line 44: | ||
==Naming cells== | ==Naming cells== | ||
[[File:ExcelNameField.jpg|framed|right|The Name field. The name of a cell can be entered here.]] | [[File:ExcelNameField.jpg|framed|right|The Name field. The name of a cell can be entered here.]] | ||
The functioning of Excel files in the | The functioning of Excel files in the {{software}} hinges on the named cell functionality in Excel files. Input cells and Output cells require names to be set before they function as such. If the required output cells don't exist (for example, no cells are named <code>EXPLANATION</code>), the Excel file will not function as expected in the {{software}}. To set a cell name in an Excel file, select the cell and enter its name in the name field (just under the ribbon). | ||
{{Editor steps|title=name a cell|Open the Excel file in Microsoft Office Excel|Select the cell you wish to use as output cell|In the top left of the window, find the "Name" field (just under the ribbon)|Enter the name for the cell (for example: <code>EXPLANATION</code>)|Press "Enter"}} | {{Editor steps|title=name a cell|Open the Excel file in Microsoft Office Excel|Select the cell you wish to use as output cell|In the top left of the window, find the "Name" field (just under the ribbon)|Enter the name for the cell (for example: <code>EXPLANATION</code>)|Press "Enter"}} | ||
| Line 60: | Line 67: | ||
To delete one or multiple names, select them in the Name Manager, and select "Delete". You will be asked to confirm that action. <br style="clear:right;"> | To delete one or multiple names, select them in the Name Manager, and select "Delete". You will be asked to confirm that action. <br style="clear:right;"> | ||
==Using Excel files | ==Using Excel files== | ||
Excel files are used in a number of places in the | Excel files are used in a number of places in the {{software}}, including [[Indicator]]s, [[Zone]]s, and [[Panel]]s. To allow for the reuse of Excel files, the {{software}} presents an Excel File Manager, in which Excel files can be uploaded, replaced, and selected for use. | ||
===Uploading and Downloading=== | ===Uploading and Downloading=== | ||
Read here more about the [[Excel_file_manager|Excel file manager]] for uploading, replacing and selecting Excels. | |||
===Debugging=== | ===Debugging=== | ||
Sometimes, when you look at the output of an Excel-based calculation in the | Sometimes, when you look at the output of an Excel-based calculation in the {{software}}, you may not directly recognize how a certain result was achieved. As more complex Excel files are uploaded, this may occur more often. At these times, it may be desirable to see how the Excel file has been filled by the {{software}}, and how these values are treated by the Excel file's calculation. This can be done by downloading the Excel file using the "Debug Excelsheet" option. This will allow you to download the Excel file with the current values filled in. | ||
After download, you can open the Excel File in Microsoft Office Excel. You will see that all input cells are filled with values from the | After download, you can open the Excel File in Microsoft Office Excel. You will see that all input cells are filled with values from the {{software}}. Inspecting the output cells and tracing back through your calculation will allow you to determine how certain values came to be, based on the provided input. | ||
It is important to note that the downloaded file may have lost some formatting in the sheet, as well as contain some project-specific adjustments when "X" or "ID" queries were used. For this reason, we advise not to re-upload Excel files which were downloaded with current values, even when they require a change altered. Instead, download the original file using the Excel File Manager, adjust it, and re-upload that version. | It is important to note that the downloaded file may have lost some formatting in the sheet, as well as contain some project-specific adjustments when "X" or "ID" queries were used. For this reason, we advise not to re-upload Excel files which were downloaded with current values, even when they require a change altered. Instead, download the original file using the Excel File Manager, adjust it, and re-upload that version. | ||
| Line 84: | Line 86: | ||
===ID queries=== | ===ID queries=== | ||
{{main|ID query (Excel)}} | |||
Excel files can be used for general calculations which are basically predictable. You always know which elements in the world you want to retrieve or manipulate. However, in some cases, you may not know that beforehand. Examples are when an Excel is used for a [[ | Excel files can be used for general calculations which are basically predictable. You always know which elements in the world you want to retrieve or manipulate. However, in some cases, you may not know that beforehand. Examples are when an Excel is used for a [[Template Panel]] or a [[Zones|Zoning permit calculation]]. In these situations, an ID query allows for a generic reference to whichever [[Item]] is applicable, such as: | ||
<code>SELECT_LANDSIZE_WHERE_AREA_IS_ID</code> | |||
===X queries=== | |||
{{main|X query (Excel)}} | |||
For use-cases in which an Excel is used to aggregate data, for example to compute specific scores per [[Neighborhood]] for display in an [[Indicator]], it is not desirable to create individual queries for each piece of data that needs to be retrieved. Rather than stating each individual [[Neighborhood]] for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all [[Neighborhood]]s should be retrieved, regardless of how many or how few there may be. Such a query would look as follows: | |||
<code>SELECT_LANDSIZE_WHERE_AREA_IS_X</code> | |||
< | |||
===Maptype postfixes=== | ===Maptype postfixes=== | ||
{{main|Maptype postfix (Excel)}} | |||
The default, non-query output cells (EXPLANATION and SCORE) can output a single result. In situations where you want to compare the original (current) situation with the planned situation, the Excel should actually output separate results, based on the [[Map Type]]. This can be accomplished by expanding the cell names with an additional maptype postfix to the name of the cell, such as: | |||
<code>SCORE_CURRENT</code> or <code>SCORE_MAQUETTE</code> | |||
===VARs=== | ===VARs=== | ||
{{main|Var (Excel)}} | |||
Calculated numbers can be automatically injected into the EXPLANATION output cell by defining specific cells as VAR cells. These effectively serve as additional output cells. The {{software}} will take the value in the EXPLANATION cell and inject into spots marked with "%s" the numbers which are output in the VAR cells. | |||
A cell named <code>VAR_0</code> will have its numeric value inserted in the EXPLANATION output cell's content, which for example would be "The value is %s, and now we know.". | |||
==Excel tips and tricks== | ==Excel tips and tricks== | ||
| Line 122: | Line 113: | ||
===Filtering data=== | ===Filtering data=== | ||
[[File:ExcelFilterFormula.jpg|framed|right|All data is retrieved from the | [[File:ExcelFilterFormula.jpg|framed|right|All data is retrieved from the {{software}}. Data which is to be ignored is filtered out using Excel's functions.]] | ||
It is possible that, although you retrieve all elements which exist in the | It is possible that, although you retrieve all elements which exist in the {{software}}, you only wish to perform calculations with a subset of them. . In these cases, Excel formulas can be used to determine which data should or should not be processed in your calculations. Especially functions which perform conditional sums work well for this purpose, or statements which multiply irrelevant data with "0".<br style='clear:right'> | ||
===Combining text=== | ===Combining text=== | ||
| Line 129: | Line 120: | ||
Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go. | Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go. | ||
Do note that using this technique makes it very easy to create a very deep chain of commands to process by the | Do note that using this technique makes it very easy to create a very deep chain of commands to process by the {{software}}. When combining many hundreds of rows, the {{software}} may indicate it was unable to process the entire file due to something called a "Stack overflow". Make sure you use this combining technique only for limited amounts of rows. | ||
<br style='clear:right'> | <br style='clear:right'> | ||
===Excel formatting and descriptions=== | ===Excel formatting and descriptions=== | ||
When dealing with larger spreadsheets and workbooks, it is easy for the structure to become overwhelming and thus less easy to understand. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file. | When dealing with larger spreadsheets and workbooks, it is easy for the structure to become overwhelming and thus less easy to understand. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file. | ||
<!-- | |||
==Excel issues== | |||
COUNTIF and COUNTIFS don't seem to be processed correctly yet. | |||
--> | |||
{{article end | |||
|seealso= | |||
* [[Var (Excel)]] | |||
* [[Excel Indicator]] | |||
* [[TQL]] | |||
|howtos= | |||
* [[How to prepare data in Microsoft Excel]] | |||
}} | |||
{{Indicator nav}} | |||
Latest revision as of 16:41, 21 February 2023
What is an Excel file
An Excel file is a file, consisting of one or multiple tables of values, generally used in business environments for administration and calculation purposes. A single file can consist of multiple sheets. Each sheet is a grid of cells, each possibly containing either a value or a formula.
Excel files can be created and edited using a variety of programs. The most obvious program is by using Microsoft Office's Excel. Alternative programs include OpenOffice or LibreOffice, although minor differences in both use and calculation can occur, and support for these differences may be limited.
For the Tygron Platform, the file format .xlsx is specifically required.
How do Excel files relate to the Tygron Platform
One of the core functions of the Tygron Platform is to calculate prospective effects of actions and scenarios. Depending on the case being explored, what exactly to calculate and the precise method of calculation can vary. To provide the greatest freedom in the ability to configure a project with relevant, accurate, and third-party approved calculation models, the Tygron Platform allows some calculation to be made in Excel files. Most notably indicators can easily be created or adjusted by inspecting and editing the underlying Excel file. By defining specific cells to act as input and output for the calculation, the Tygron Platform can perform calculations using user-defined Excel files.
Excels can also output text to provide information about the current state of the Session or provide insight into the calculation of some value. To make information provided in this fashion clear and understandable to a user, the output can be styled with HTML. The Tygron Platform will interpret the text output of an Excel file as HTML if possible.
General structure of Excel files
Excel files, in general, have three parts to them: input, calculation, and output. The input and output are "connected" to the Tygron Platform. The contents of each input cell is changed by the Tygron Platform to reflect some form of data from the project. Each output cell is eventually read by the Tygron Platform, and its content used in some fashion. Input cells, and some of the output cells, are defined using TQL.
Cell types
Cells can be divided into 4 categories: Output, Input, Calculation, and Unreferenced cells.
Output cells
The most important part of the Excel file is the output. The Tygron Platform will detect each output cell, and calculate their contents. If those cells reference other cells, those other cells will be calculated as well. The Tygron Platform will continue this until the contents for all output cells are calculated. Each component which uses an Excel file for its calculation has one or more required output cells. The output can then be used during a session. Additional output cells are created when TQL update statements are added to the Excel.
Explanation
- Each Excel file should have an
EXPLANATIONcell. The content in this cell is text, which can be displayed as a description or explanation of what is happening, either in the calculation or in the project as a whole.
Score
- Some Excel files, such as those for Indicators and Zoning excels, require a
SCOREcell. The content in this cell is a number up to 1 (inclusive). The exact meaning of the number in this cell varies by component.
Input cells
Next to the output cells, the input cells are most important. You will generally want your Excel file to produce different outputs based on the current state of the project or session. Input cells are always defined using TQL. When the Excel file is uploaded and used by the Tygron Platform, the content of the input cells will be overwritten by the Tygron Platform with the proper, requested value.
Calculation cells
In between, there are the calculation cells. It's likely you don't want to output the data from the Tygron Platform directly. Instead, you want to use it in some form of calculation, and display only the result, likely with some formatting. For example, you may not want to return the amount of green and the amount of houses, but the amount of green per house, rounded to a decimal or two, with some unit appended to it. Any cell, even empty cells, are deemed to be calculation cells if they are referenced by an output cell, because their contents are calculated (evaluated) to determine what the output will be.
Unreferenced cells
Lastly there are unreferenced cells. These are cells which are not input cells, and are not referenced directly or indirectly by output cells. Empty cells, when referenced directly or indirectly by output cells, are evaluated, and thus seen as used. Unreferenced cells can contain values or even formulas, or even reference input cells. Unreferenced cells with formulas can be useful when creating or inspecting excel sheets, to provide insight in how the calculation works.
Naming cells
The functioning of Excel files in the Tygron Platform hinges on the named cell functionality in Excel files. Input cells and Output cells require names to be set before they function as such. If the required output cells don't exist (for example, no cells are named EXPLANATION), the Excel file will not function as expected in the Tygron Platform. To set a cell name in an Excel file, select the cell and enter its name in the name field (just under the ribbon).
- Open the Excel file in Microsoft Office Excel
- Select the cell you wish to use as output cell
- In the top left of the window, find the "Name" field (just under the ribbon)
- Enter the name for the cell (for example:
EXPLANATION) - Press "Enter"
Correcting and removing cell names
There may be a number of reasons to correct the naming of a cell:
- The cell is no longer required
- The cell no longer exists
- The cell has been given multiple names
- The same name has been applied to multiple cells, or a range of cells
- The name of the cell is no longer correct, and needs to be changed
To correct issues like these, you can use the Name Manager in Microsoft Office Excel. You can find the Name Manager under the "Formulas" tab in the ribbon.
The Name manager displays a list of all assigned names in the Excel file, as well as what cells they refer to, and what the current value of those cells are.
To edit a cell name, or change what cells are referred to by that name, select it, and select "Edit". The "Edit Name" panel will appear, where you can enter a new name, as well as redefine to what cell the name refers.
To delete one or multiple names, select them in the Name Manager, and select "Delete". You will be asked to confirm that action.
Using Excel files
Excel files are used in a number of places in the Tygron Platform, including Indicators, Zones, and Panels. To allow for the reuse of Excel files, the Tygron Platform presents an Excel File Manager, in which Excel files can be uploaded, replaced, and selected for use.
Uploading and Downloading
Read here more about the Excel file manager for uploading, replacing and selecting Excels.
Debugging
Sometimes, when you look at the output of an Excel-based calculation in the Tygron Platform, you may not directly recognize how a certain result was achieved. As more complex Excel files are uploaded, this may occur more often. At these times, it may be desirable to see how the Excel file has been filled by the Tygron Platform, and how these values are treated by the Excel file's calculation. This can be done by downloading the Excel file using the "Debug Excelsheet" option. This will allow you to download the Excel file with the current values filled in.
After download, you can open the Excel File in Microsoft Office Excel. You will see that all input cells are filled with values from the Tygron Platform. Inspecting the output cells and tracing back through your calculation will allow you to determine how certain values came to be, based on the provided input.
It is important to note that the downloaded file may have lost some formatting in the sheet, as well as contain some project-specific adjustments when "X" or "ID" queries were used. For this reason, we advise not to re-upload Excel files which were downloaded with current values, even when they require a change altered. Instead, download the original file using the Excel File Manager, adjust it, and re-upload that version.
- Select the item with the relevant Excel File in the editor
- Select "Debug Excelsheet"
- Select the location to save the file and select "Save"
- Download the original Excel file as well
- Open the Excel file with the current values and step through it to find any issues
- Make any desired changes in the original Excel file
- Upload the modified original Excel file
Advanced Excel Functionalities
Besides the basics described above, a number of more advanced functionalities exist.
ID queries
- Main article: ID query (Excel)
Excel files can be used for general calculations which are basically predictable. You always know which elements in the world you want to retrieve or manipulate. However, in some cases, you may not know that beforehand. Examples are when an Excel is used for a Template Panel or a Zoning permit calculation. In these situations, an ID query allows for a generic reference to whichever Item is applicable, such as:
SELECT_LANDSIZE_WHERE_AREA_IS_ID
X queries
- Main article: X query (Excel)
For use-cases in which an Excel is used to aggregate data, for example to compute specific scores per Neighborhood for display in an Indicator, it is not desirable to create individual queries for each piece of data that needs to be retrieved. Rather than stating each individual Neighborhood for which to retrieve data, an X query can be used to indicate that, across multiple rows, the data of all Neighborhoods should be retrieved, regardless of how many or how few there may be. Such a query would look as follows:
SELECT_LANDSIZE_WHERE_AREA_IS_X
Maptype postfixes
- Main article: Maptype postfix (Excel)
The default, non-query output cells (EXPLANATION and SCORE) can output a single result. In situations where you want to compare the original (current) situation with the planned situation, the Excel should actually output separate results, based on the Map Type. This can be accomplished by expanding the cell names with an additional maptype postfix to the name of the cell, such as:
SCORE_CURRENT or SCORE_MAQUETTE
VARs
- Main article: Var (Excel)
Calculated numbers can be automatically injected into the EXPLANATION output cell by defining specific cells as VAR cells. These effectively serve as additional output cells. The Tygron Platform will take the value in the EXPLANATION cell and inject into spots marked with "%s" the numbers which are output in the VAR cells.
A cell named VAR_0 will have its numeric value inserted in the EXPLANATION output cell's content, which for example would be "The value is %s, and now we know.".
Excel tips and tricks
Besides the advanced functionalities available when using excel sheets, much of the effectiveness comes from possibilities inherent to Excel. There are a number of techniques which allow Excel files to become more functional and maintainable.
Filtering data
It is possible that, although you retrieve all elements which exist in the Tygron Platform, you only wish to perform calculations with a subset of them. . In these cases, Excel formulas can be used to determine which data should or should not be processed in your calculations. Especially functions which perform conditional sums work well for this purpose, or statements which multiply irrelevant data with "0".
Combining text
Excel does not provide a dynamic solution for appending large amounts of text. However, there is a way to put text together from consecutive rows or columns. By creating a formula that appends the contents of the current row to the contents of the next row, data from all rows can be appended together, and thus placed in the explanation in one go.
Do note that using this technique makes it very easy to create a very deep chain of commands to process by the Tygron Platform. When combining many hundreds of rows, the Tygron Platform may indicate it was unable to process the entire file due to something called a "Stack overflow". Make sure you use this combining technique only for limited amounts of rows.
Excel formatting and descriptions
When dealing with larger spreadsheets and workbooks, it is easy for the structure to become overwhelming and thus less easy to understand. For this reason, while creating Excel files, it is important to describe in the file itself what cells do, and why. Text next to or above cells to explain the reason of method of a calculation are a simply method for this. To help with creating a quick overview of the structure of sheets, you can apply borders and colors to cells, rows, and columns. When cells are provided with colors in a consistent fashion, it becomes much easier to recognize the overall structure of an Excel file.