Excel: Difference between revisions

From Tygron Support wiki
Jump to navigation Jump to search
No edit summary
 
(22 intermediate revisions by 3 users not shown)
Line 1: Line 1:
[[File:Tql_webinar.png|link=https://www.tygron.com/nl/2019/05/22/tql-en-upgrades/]]
[[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 11: Line 12:
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.
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 {{software}} will interpret the text output of an Excel file as HTML if possible.
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==
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 Excelfile 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.
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>
'''Explanation'''<br>
Line 85: Line 86:


===ID queries===
===ID queries===
[[File:ExcelIDs.jpg|framed|right|The <code>ID</code> in the query will be replaced with the specific AREA the file is applied to, and thus the name of the AREA concerned is retrieved.]]
{{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 [[Panel#Template Excel Panel|Panel Template]] or a [[Zones|Zoning permit calculation]]. In these situations, you may likely want a query which refers to whatever element the Excel is applied to. This can be done using an ID query. For any given query which uses an ID, the ID can be replaced with <code>ID</code>, with the result being a query such as <code>SELECT_UNITS_WHERE_BUILDING_IS_1</code> turning into <code>SELECT_UNITS_WHERE_BUILDING_IS_ID</code>. Only one <code>ID</code> term may exist in a query, but multiple ID queries may exist in an Excel file.<br style='clear:right'>
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:


===X Queries===
<code>SELECT_LANDSIZE_WHERE_AREA_IS_ID</code>
[[File:Xquery.jpg|framed|right|Example of what will happen with an X query with 3 neighborhoods.]]
In some cases, especially when developing projects which are prone to change, or when creating [[project templates]], it may be desirable to add queries to your excel sheet which retrieve all of a certain type of thing. For example, each project can have a different amount of [[neighborhoods]]. In these cases you can't create queries for all the items you wish to retrieve. Instead, you can use X queries to instruct the {{software}} to dynamically create the queries for you. For any given query with uses an ID, you can replace the ID with <code>X</code>, with the result being a query such as <code>SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_1</code> turning into <code>SELECT_UNITS_WHERE_NEIGHBORHOOD_IS_X</code>. Only one <code>X</code> term may exist in a query, but you may add multiple X queries to an Excel file.


When an Excel file with one or more X queries is uploaded, the {{software}} prepares it for calculation by transforming the single X query into a column of queries which refer to specific IDs. When multiple X queries have their <code>X</code> refer to the same type of element from the project, the amount and order in which the IDs are filled in are consistent. The created queries are placed in the same column as the original X query, starting in the cell directly below the X query and continuing downward. The X queries are then removed. In other words, when using an X query, the first cell in that column with the intended query will be the cell under the X query. Each cell which now has a query will be properly filled in by the {{software}}. Note that not every cell in the column is given a query. If there are more cells with values than are overwritten by queries, those excess cells retain their value. From this point on, the file is used as normal.
===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:


The original Excel file is saved by the {{software}}. When a change occurs which would affect how many and which queries should be generated from an X query, it's possible the Excel file needs to be re-prepared for use in the {{software}}. It is possible to trigger the {{software}} to do this, by using the "Reset X Queries" option in the editor.
<code>SELECT_LANDSIZE_WHERE_AREA_IS_X</code>
{{Editor location|reset x}}
 
When the original file is downloaded, it will be the file with the X queries, before the exact queries were generated from them. When the file with the current values is downloaded, it will contain the generated queries, and not the X queries themselves. It is recommended that you don't reupload the file with current values, because it is no longer generically applicable.
 
<br style="clear:right;">
 
===XA Queries===
To select specific Items (for example [[Neighborhoods|neighborhoods]] or [[Areas|areas]]) in combination with a X query, use a XA query.
XA queries will select only Items with a specific [[Attribute|attribute]]. <br>
Example: SELECT_ID_WHERE_AREA_IS_X will show all [[Areas|areas]], using SELECT_ID_WHERE_AREA_IS_XA_PEIL will only show the areas with the [[Attribute|attribute]] PEIL (where the value of PEIL is not 0).


===Maptype postfixes===
===Maptype postfixes===
[[File:postfixes.jpg|framed|right|The name of this cell is appended with <code>_CURRENT</code>, and the other with <code>_MAQUETTE</code>.]]
{{main|Maptype postfix (Excel)}}
Non-query output cells use a word or phrase to indicate that the value should be output in some fashion. By default these values are used for both the [[Map Type|current and maquette views]] in the {{software}}. However, sometimes it may be desirable to output different scores for the current state and the maquette state of the project. One such situation is when the [[Simulation Type]] of the project is set to timeline. In these cases, the cell names can be expanded by adding a <code>_CURRENT</code> or <code>_MAQUETTE</code> to the name of the cell. Both will need to exist to make the Excel file valid.
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:


This functionality does not extend to query (output) cells. For input cells, a switch between map views is possible via a built-in clause.<br style="clear:right;">
<code>SCORE_CURRENT</code> or <code>SCORE_MAQUETTE</code>


===VARs===
===VARs===
[[File:ExcelVars2.jpg|framed|right|In this example, the output of the Excel will read "this 1.234,568 is 56 % text". There are not enough places to insert all VARs, so <code>VAR_2</code> is ignored. However, it is calculated.]]
{{main|Var (Excel)}}
In some situations, you may find that the contents output of the Excel file remains mostly constant, save for a number of numerical values. In this case, you can choose to use VAR cells, which serve as additional output cells. The values placed in these cells are read by the {{software}}, and inserted into the explanation output. The {{software}} will also format the numbers for readability. Specifically, thousands separators will be added (if needed), a decimal separator will be added (if needed), and the number will be rounded to 3 decimal digits.
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.
 
To define one or multiple VAR cells, name a cell <code>VAR_N</code>, where N is a sequential number starting at 0 (so <code>VAR_0</code>, <code>VAR_1</code>, <code>VAR_2</code>, etc). To indicate where in the explanation the VARs should be inserted, you can create an insertion point by writing "%s" in the explanation. The {{software}} will insert each VAR, in order, into these places in the text.
 
Note that while it's possible to use formulas or conditional structures in your Excel to come to an explanation with zero, one, or multiple insertion points parts, it is important to remember that the {{software}} will only look at the text which ends up in the <code>EXPLANATION</code> cell to insert VARs. If you remove a segment of the explanation which contained an insertion point, the VARs which used to be inserted there will be inserted in the next ones instead. If this is undesirable, either keep the insertion points for the VARs in the text, or restructure the VAR cells in the excel so that the correct values are inserted in the correct locations.
 
Also note that the {{software}} will ignore additional VAR cells if there are not enough insertion points (although the cells will still be evaluated), but that the {{software}} will be unable to display the explanation properly if there are too many insertion points. If that is the case, an error will be displayed instead of the proper explanation.


Lastly, when using VARs, the {{software}} will check the output text for any "%" symbols. If you wish to use a literal "%" in your explanation while using VARs, you can instruct the {{software}} to leave it as is by writing "%%" instead.<br style='clear:right'>
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 145: Line 130:
COUNTIF and COUNTIFS don't seem to be processed correctly yet.
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

Tql webinar.png

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

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 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 EXPLANATION 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

Some Excel files, such as those for Indicators and Zoning excels, require a SCORE 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.

Input cells

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 Tygron Platform.

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 this image the cell D2 contains a formula. It's not directly overwritten by the Tygron Platform, but the results change based on input from the Tygron Platform. 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 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

In this image the cell C3 is empty and unnamed, so it is not an input or output cell. It is also not referred to, so its an unreferenced cell.

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 Name field. The name of a cell can be entered here.

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).

How to name a cell:
  1. Open the Excel file in Microsoft Office Excel
  2. Select the cell you wish to use as output cell
  3. In the top left of the window, find the "Name" field (just under the ribbon)
  4. Enter the name for the cell (for example: EXPLANATION)
  5. Press "Enter"

Correcting and removing cell names

Where to find the Name Manager.
The Name Manager.

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.

How to debug an Excel File:
  1. Select the item with the relevant Excel File in the editor
  2. Select "Debug Excelsheet"
  3. Select the location to save the file and select "Save"
  4. Download the original Excel file as well
  5. Open the Excel file with the current values and step through it to find any issues
  6. Make any desired changes in the original Excel file
  7. 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

All data is retrieved from the Tygron Platform. 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 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

Each row's contents are composed of the data on that row, followed by whatever is composed on the next row.

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.