동적 피벗 테이블

When a pivot view from an Odoo database is inserted in a spreadsheet, it is by default a static pivot table. Each cell in a static pivot table contains an Odoo-specific function that retrieves data from your database.

When the corresponding data in your database changes, e.g., the sales related to a given quarter or an individual salesperson, the cells of your static pivot table are updated.

다만 정적 피벗 테이블에는 새로운 데이터가 자동으로 반영되도록 하는 확장 기능이 없으며, 예를 들어 새로운 분기나 신규 채용된 영업 담당자의 판매 데이터 등은 반영이 되지 않습니다. 또한 피벗 테이블 속성을 통해 축(예: 열 또는 행)이나 측정값을 추가하거나 가공하는 것도 불가능합니다.

참고

If you attempt to update or manipulate the properties of a pivot table that has just been inserted into a spreadsheet, an error message appears in the top right corner of the screen:

To have more flexibility in how you can manipulate your pivot table, you can create a dynamic pivot table from a static pivot table.

동적 피벗 테이블 생성하기

There are two main ways to create a dynamic pivot table from a static pivot table:

• Duplicate the static pivot table from the pivot table properties: Open the pivot table properties, click the (gear) icon at the top right of the pane, then click Duplicate.

  A new data source is created and a dynamic version of the pivot table is inserted into a new sheet. The dynamic pivot table has the same styling as the original pivot table.

  참고

  When you use this method, your new dynamic pivot table gets the next available pivot ID. This means you can create multiple pivot views associated with the same model, but with distinct settings, groupings, or calculations.

• 데이터 메뉴에서 동적 피벗 테이블 다시 삽입: 정적 피벗 테이블이 있는 시트에서 커서를 빈 셀에 놓습니다. 메뉴 모음에서 데이터 ‣ 동적 피벗 다시 삽입 을 클릭한 다음 해당하는 피벗 테이블을 선택합니다.

  A new, dynamic pivot table appears, with the same styling as the original pivot table.

  참고

  When you use this method, your static and dynamic pivot share the same data source, and, consequently, the same pivot ID. To avoid confusion, delete the original static pivot table.

팁

It is also possible to directly enter the function of the dynamic pivot table in an empty cell. However, with this method, the table styling needs to be re-applied manually.

동적 피벗 테이블 기능

Instead of each cell containing a unique function that retrieves data from your database, as in a static pivot table, a dynamic pivot table has a single function:

=PIVOT(pivot_id, [row_count], [include_total], [include_column_titles], [column_count] )

The arguments of the function are as follows:

• pivot_id: the ID assigned when the pivot table is inserted. The first pivot table inserted in a spreadsheet is assigned pivot ID 1, the second, pivot ID 2, etc.

• row_count and column count: the number of rows and columns respectively.

• include_total and include_column_titles: values of 0 remove the total and column titles respectively.

This is an array function, which allows the pivot table to expand automatically to accommodate the results of the function.

The top-left cell contains the editable function, while clicking on any other cell reveals this formula greyed out.

팁

If necessary, you can update the function of a dynamic pivot table to remove elements like the total or column titles.

수식 입력줄 또는 피벗 테이블의 왼쪽 상단 셀에서 함수를 연 후 피벗 ID 뒤에 커서를 위치시킨 후 쉼표 `,`를 입력하면 수정하려는 선택 필드로 이동합니다. 아래의 예시에서 [include_total]에 0 값을 추가하면 피벗 테이블에서 행 합계와 열 합계가 모두 제거됩니다.

동적 피벗 테이블 다루기

To manipulate data in a dynamic pivot table, open the pivot table properties.

The following options are available by clicking the (gear) icon:

• Flip axes: to move all the dimensions represented in columns to rows and vice versa.

  팁

  축을 반전시킬 경우 데이터를 다른 관점에서 볼 수 있으므로 새로운 인사이트로 자료를 검토할 수 있습니다. 다만 데이터 양에 따라서는 #SPILL 오류가 발생할 수 있습니다. 이 오류는 수식을 통해 값 범위가 출력될 때 다른 데이터, 병합된 셀 또는 현재 시트의 경계 등과 같은 요소가 해당 셀을 가로막는 경우에 발생합니다.

  Hovering over the cell containing #SPILL details the error.

• Duplicate: to duplicate the dynamic pivot table and create a new data source with distinct properties.

• Delete: to delete the data source of the dynamic pivot table.

  참고

  Deleting the data source of a pivot table does not delete the visual representation of the data. Delete the table from the spreadsheet using your preferred means, e.g., via keyboard commands, spreadsheet menus, or by deleting the sheet.

크기

The dimensions of the pivot table, i.e., how the data is grouped, are placed in Columns and Rows according to how they appeared in the pivot view in your database, i.e., before the pivot table was inserted in the spreadsheet.

가능 항목:

• 추가 를 클릭하여 새 축 추가하기

• delete existing dimensions by clicking the (delete) icon on the relevant dimension

• change the order in which dimensions are displayed in Columns or Rows by clicking then dragging the dimension to the desired position within its respective section

• change the axis on which a dimension is shown by clicking then dragging the dimension from Columns to Rows or vice versa

• change how a dimension’s values are ordered by selecting Ascending, Descending, or Unsorted in the Order by field

• for date- or time-based dimensions, select the desired Granularity from the options in the dropdown menu

계산

The measures of your pivot table, i.e., what you are measuring, or analyzing, based on the dimensions you have chosen, are listed in the order they appeared in the pivot view in your database.

가능 항목:

• add new measures, including calculated measures, by clicking Add

• hide (), show (), or delete () existing measures

• edit the name of existing measures by clicking on the measure’s name

• change the order in which measures are displayed by clicking then dragging the measure to the desired position

• (톱니바퀴) 아이콘을 클릭한 다음 드롭다운 메뉴에서 원하는 옵션(예: 총합계 중 % 또는 가장 작은 것부터 큰 순으로 정렬)을 선택하여 측정값이 표시되는 방식을 변경할 수 있습니다. 피벗 테이블 데이터는 다른 옵션을 선택하면 동적으로 업데이트됩니다.

• choose how measures are aggregated, e.g., by Sum, Average, Minimum

계산 방법

It is possible to add calculated measures if the desired measure did not exist in the original pivot view. For example, a calculated measure could be added to show the average revenue per order or the profit margin per product.

계산한 측정값 추가 방법:

1. From the Measures section of the pivot table properties, click Add.

2. Below the scrollable list, click Add calculated measure.

3. Rename the calculated measure by clicking on the name and typing.

4. Click on the line starting with = and enter the formula.

   Example

   In the below example, the average revenue per order is added by dividing the sum of the sales by the number of orders.

   

5. Choose how the measure should be aggregated by selecting a value from the dropdown.

팁

정적 피벗 테이블의 경우 여러 가지 장점이 있으며, 예를 들어 개별 셀에 숨겨진 함수 입력 내용을 확인할 수 있습니다. 이같은 기능을 활용하려면 동적 피벗 테이블에서 해당 부분을 선택하고 복사한 다음 시트의 빈 곳에 붙여넣으세요. 붙여넣은 셀을 클릭하면 Odoo 함수 를 통해 데이터를 가져올 수 있습니다.