CN103020031A - Method and device for updating data pivot table intelligently - Google Patents
Method and device for updating data pivot table intelligentlyDownload PDFInfo
- Publication number
- CN103020031A CN103020031ACN2012105541495ACN201210554149ACN103020031ACN 103020031 ACN103020031 ACN 103020031ACN 2012105541495 ACN2012105541495 ACN 2012105541495ACN 201210554149 ACN201210554149 ACN 201210554149ACN 103020031 ACN103020031 ACN 103020031A
- Authority
- CN
- China
- Prior art keywords
- data
- source area
- data source
- pivot table
- area
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
Translated fromChinese
Description
Translated fromChinese技术领域technical field
本发明涉及一种电子表格的数据处理方法，特别是一种智能更新数据透视表的方法及装置。The invention relates to a data processing method of an electronic form, in particular to a method and device for intelligently updating a pivot table.
背景技术Background technique
在电子表格中，经常会使用数据透视表进行数据的处理及分析，它能基于指定的数据源，创建出不同视角的二维、三维统计表。它可以动态地改变版面布置，以便按照不同方式分析数据，也可以重新安排行号、列标和页字段。每一次改变版面布置时，数据透视表会立即按照新的布置重新计算数据。另外，如果原始数据发生更改，则可以更新数据透视表。而在实际使用中，指定的数据源往往是电子表格的某个区域，而且该区域的数据是在持续维护、不断增长中，这个时候，更新透视表只能更新初始区域的数据变化，即更新创建透视表时所指定的数据源区域的数据变化，而无法将新增的数据一并统计进去。往往当数据源区域增加新的行或列时，需要手动更新数据源区域，才能将所有数据一起进行统计，这样，不仅操作繁琐，时效性差，而且因为用户容易忘记手动更新数据源区域，导致统计结果不准确。In spreadsheets, pivot tables are often used for data processing and analysis, which can create two-dimensional and three-dimensional statistical tables from different perspectives based on specified data sources. It can dynamically change the layout to analyze data differently, and rearrange row numbers, column headings, and page fields. Every time the layout is changed, the pivot table immediately recalculates the data according to the new layout. Also, the PivotTable can be updated if the original data changes. In actual use, the specified data source is often a certain area of the spreadsheet, and the data in this area is continuously maintained and growing. At this time, updating the pivot table can only update the data changes in the initial area, that is, update The data in the data source area specified when creating the pivot table changes, and the newly added data cannot be counted together. Often when a new row or column is added to the data source area, it is necessary to manually update the data source area to count all the data together. In this way, not only the operation is cumbersome and the timeliness is poor, but also because the user easily forgets to manually update the data source area, resulting in statistics The result is inaccurate.
发明内容Contents of the invention
为了解决上述的技术问题，本发明的目的是提供一种操作方便、时效性好及准确度高的智能更新数据透视表的方法。本发明的另一目的是还提供一种操作方便、时效性好及准确度高的智能更新数据透视表的装置。In order to solve the above-mentioned technical problems, the object of the present invention is to provide a method for intelligently updating pivot tables with convenient operation, good timeliness and high accuracy. Another object of the present invention is to provide a device for intelligently updating pivot tables with convenient operation, good timeliness and high accuracy.
本发明解决其技术问题所采用的技术方案是：The technical solution adopted by the present invention to solve its technical problems is:
一种智能更新数据透视表的方法，包括：A way to intelligently update pivot tables, including:
S1、检测是否接收到更新数据透视表的指令，当检测到更新数据透视表的指令时，发送数据获取指令；S1. Detect whether an instruction to update the pivot table is received, and when an instruction to update the pivot table is detected, send a data acquisition instruction;
S2、根据所述数据获取指令，获取数据透视表的当前的数据源区域以及当前数据透视表的设置指令；S2. According to the data acquisition instruction, obtain the current data source area of the pivot table and the setting instruction of the current pivot table;
S3、检测数据源区域的相邻区域是否有新增数据，若有，则将数据源区域进行扩展；S3. Detect whether there is new data in the adjacent area of the data source area, and if so, expand the data source area;
S4、从扩展后的数据源区域中读取所有数据；S4. Read all data from the expanded data source area;
S5、使用获取的设置指令对读取的数据进行重新运算；S5. Recalculate the read data by using the obtained setting instruction;
S6、根据重新运算的结果对数据透视表进行更新。S6. The pivot table is updated according to the result of the recalculation.
进一步，所述步骤S3，其具体为：Further, the step S3 is specifically:
检测数据源区域相邻的行区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有行区域并入到数据源区域中；Detect whether there is new data in the row area adjacent to the data source area, and if so, expand the data source area, that is, merge all the row areas where the new data is located into the data source area;
和/或and / or
检测数据源区域相邻的列区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有列区域并入到数据源区域中。Detect whether there is new data in the column area adjacent to the data source area, and if so, expand the data source area, that is, merge all the column areas where the new data is located into the data source area.
进一步，所述步骤S6，其具体为：Further, the step S6 is specifically:
根据重新运算的结果对数据透视表的数据区域及图表区域进行更新。The data area and graph area of the pivot table are updated according to the result of the recalculation.
本发明解决其技术问题所采用的另一技术方案是：Another technical solution adopted by the present invention to solve its technical problems is:
一种智能更新数据透视表的装置，包括：A device for intelligently updating a pivot table, comprising:
监测模块，用于检测是否接收到更新数据透视表的指令，当检测到更新数据透视表的指令时，发送数据获取指令；The monitoring module is used to detect whether an instruction to update the pivot table is received, and when an instruction to update the pivot table is detected, send a data acquisition instruction;
第一数据获取模块，用于根据所述数据获取指令，获取数据透视表的当前的数据源区域以及当前数据透视表的设置指令；The first data acquisition module is used to obtain the current data source area of the pivot table and the setting instruction of the current pivot table according to the data acquisition instruction;
扩展模块，用于检测数据源区域的相邻区域是否有新增数据，若有，则将数据源区域进行扩展；The expansion module is used to detect whether there is new data in the adjacent area of the data source area, and if so, expand the data source area;
第二数据获取模块，用于从扩展后的数据源区域中读取所有数据；The second data acquisition module is used to read all data from the expanded data source area;
计算模块，用于使用获取的设置指令对读取的数据进行重新运算；A computing module, configured to recalculate the read data by using the obtained setting instruction;
更新模块，用于根据重新运算的结果对数据透视表进行更新。The update module is used to update the pivot table according to the result of the recalculation.
进一步，所述扩展模块包括：Further, the expansion module includes:
用于检测数据源区域相邻的行区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有行区域并入到数据源区域中的行扩展模块；It is used to detect whether there is new data in the row area adjacent to the data source area, and if so, expand the data source area, that is, merge all the row areas where the new data is located into the row expansion module in the data source area;
及and
用于检测数据源区域相邻的列区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有列区域并入到数据源区域中的列扩展模块。A column extension module used to detect whether there is new data in the column area adjacent to the data source area, and if so, expand the data source area, that is, merge all the column areas where the new data is located into the data source area.
本发明的有益效果是：本发明的一种智能更新数据透视表的方法，在更新数据透视表的过程中，当自动检测到数据源区域的相邻区域有新增数据时，可自动将数据源区域进行扩展，从而基于扩展后的数据源区域对数据透视表进行更新。本方法操作方便，时效性好且准确度高。The beneficial effects of the present invention are: a method for intelligently updating the pivot table of the present invention, in the process of updating the pivot table, when it is automatically detected that there is new data in the adjacent area of the data source area, the data can be automatically The source range is expanded, thereby updating the pivot table based on the expanded data source range. The method is convenient to operate, has good timeliness and high accuracy.
本发明的另一有益效果是：本发明的一种智能更新数据透视表的装置，在更新模块更新数据透视表的过程中，若监测模块自动检测到数据源区域的相邻区域有新增数据时，扩展模块自动将数据源区域进行扩展，从而更新模块基于扩展后的数据源区域对数据透视表进行更新。本装置操作方便，时效性好且准确度高。Another beneficial effect of the present invention is: a device for intelligently updating the pivot table of the present invention, in the process of updating the pivot table by the update module, if the monitoring module automatically detects that there is new data in the adjacent area of the data source area , the extension module automatically extends the data source area, so that the update module updates the pivot table based on the expanded data source area. The device is easy to operate, has good timeliness and high accuracy.
附图说明Description of drawings
下面结合附图和实施例对本发明作进一步说明。The present invention will be further described below in conjunction with drawings and embodiments.
图1是本发明的一种智能更新数据透视表的方法的流程图；Fig. 1 is a flow chart of a method for intelligently updating a pivot table of the present invention;
图2是本发明的一种智能更新数据透视表的装置的结构框图。Fig. 2 is a structural block diagram of a device for intelligently updating a pivot table according to the present invention.
具体实施方式Detailed ways
参照图 1，本发明提供了一种智能更新数据透视表的方法，包括：Referring to Figure 1, the present invention provides a method for intelligently updating a pivot table, including:
S1、检测是否接收到更新数据透视表的指令，当检测到更新数据透视表的指令时，发送数据获取指令；S1. Detect whether an instruction to update the pivot table is received, and when an instruction to update the pivot table is detected, send a data acquisition instruction;
S2、根据所述数据获取指令，获取数据透视表的当前的数据源区域以及当前数据透视表的设置指令；S2. According to the data acquisition instruction, obtain the current data source area of the pivot table and the setting instruction of the current pivot table;
S3、检测数据源区域的相邻区域是否有新增数据，若有，则将数据源区域进行扩展；S3. Detect whether there is new data in the adjacent area of the data source area, and if so, expand the data source area;
S4、从扩展后的数据源区域中读取所有数据；S4. Read all data from the expanded data source area;
S5、使用获取的设置指令对读取的数据进行重新运算；S5. Recalculate the read data by using the obtained setting instruction;
S6、根据重新运算的结果对数据透视表进行更新。S6. The pivot table is updated according to the result of the recalculation.
进一步作为优选的实施方式，所述步骤S3，其具体为：Further as a preferred embodiment, the step S3 is specifically:
检测数据源区域相邻的行区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有行区域并入到数据源区域中；Detect whether there is new data in the row area adjacent to the data source area, and if so, expand the data source area, that is, merge all the row areas where the new data is located into the data source area;
和/或and / or
检测数据源区域相邻的列区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有列区域并入到数据源区域中。Detect whether there is new data in the column area adjacent to the data source area, and if so, expand the data source area, that is, merge all the column areas where the new data is located into the data source area.
进一步作为优选的实施方式，所述步骤S6，其具体为：Further as a preferred embodiment, the step S6 is specifically:
根据重新运算的结果对数据透视表的数据区域及图表区域进行更新。The data area and graph area of the pivot table are updated according to the result of the recalculation.
上述方法在具体实施中，也可以先执行步骤S3，再依次执行步骤S1、S2、S4、S5、S6，即检测到有数据源区域的相邻区域有新增数据时，即对数据源区域进行扩展，之后若检测到更新数据透视表的指令，直接获取数据源区域及设置指令进行更新即可。这种方法，一旦有新增数据，即进行更新，实际运行时会相对比较耗费计算机资源。因此，依次执行步骤S1、S2、S3、S4、S5、S6的方法是本发明的一种智能更新数据透视表的方法的最优选择。In the specific implementation of the above method, it is also possible to execute step S3 first, and then execute steps S1, S2, S4, S5, and S6 in sequence, that is, when it is detected that there is new data in the adjacent area with the data source area, that is, the data source area After expansion, if an instruction to update the pivot table is detected, directly obtain the data source area and set the instruction to update. In this method, once there is new data, it will be updated, and it will relatively consume computer resources during actual operation. Therefore, the method of executing steps S1 , S2 , S3 , S4 , S5 , and S6 in sequence is the optimal choice for a method of intelligently updating the pivot table of the present invention.
下面结合具体实施例对本发明的一种智能更新数据透视表的方法做详细说明：A method for intelligently updating pivot tables of the present invention will be described in detail below in conjunction with specific embodiments:
A1、当检测到用户更新数据透视表的指令时，例如“刷新”指令时，发送数据获取指令；A1. When an instruction to update the pivot table by the user is detected, such as a "refresh" instruction, a data acquisition instruction is sent;
A2、检测到数据获取指令后，获取数据透视表的当前的数据源区域以及当前数据透视表的设置指令，这里的设置指令包括数据汇总统计的项目、方法，以及相关图表的绘制方法等；A2. After detecting the data acquisition instruction, obtain the current data source area of the pivot table and the setting instructions of the current pivot table. The setting instructions here include the items and methods of data summary statistics, and the drawing method of related charts, etc.;
A3、检测数据源区域的相邻区域是否有新增数据，若有，则将数据源区域进行扩展，包括：A3. Detect whether there is new data in the adjacent area of the data source area, and if so, expand the data source area, including:
检测到数据源区域相邻的列区域有新增数据，则将新增数据所在的所有列区域并入到数据源区域中；If new data is detected in the column area adjacent to the data source area, all the column areas where the new data is located are merged into the data source area;
和/或and / or
检测到数据源区域相邻的行区域有新增数据，则将新增数据所在的所有行区域并入到数据源区域中；If new data is detected in the row area adjacent to the data source area, all the row areas where the new data is located are merged into the data source area;
A4、从扩展后的数据源区域中读取所有数据；A4. Read all data from the expanded data source area;
A5、使用获取的设置指令对读取的数据进行重新计算，例如使用获取的数据汇总统计的项目、方法，以及相关图表的绘制方法等对读取的数据进行重新计算；A5. Use the obtained setting instructions to recalculate the read data, for example, use the obtained data summary statistics items, methods, and drawing methods of related charts to recalculate the read data;
A6、根据重新运算的结果对数据透视表进行更新，包括更新透视表的数据区域及图表区域。这里的更新方法，可以是将旧的数据区域及图表区域等统计结果删除，然后再采用相同的绘制方法绘制新的统计结果。A6. Update the pivot table according to the result of the recalculation, including updating the data area and chart area of the pivot table. The update method here can be to delete the statistical results such as the old data area and chart area, and then use the same drawing method to draw new statistical results.
参照图2，本发明还提供了一种智能更新数据透视表的装置，包括：Referring to Fig. 2, the present invention also provides a device for intelligently updating the pivot table, including:
监测模块，用于检测是否接收到更新数据透视表的指令，当检测到更新数据透视表的指令时，发送数据获取指令；The monitoring module is used to detect whether an instruction to update the pivot table is received, and when an instruction to update the pivot table is detected, send a data acquisition instruction;
第一数据获取模块，用于根据所述数据获取指令，获取数据透视表的当前的数据源区域以及当前数据透视表的设置指令；The first data acquisition module is used to obtain the current data source area of the pivot table and the setting instruction of the current pivot table according to the data acquisition instruction;
扩展模块，用于检测数据源区域的相邻区域是否有新增数据，若有，则将数据源区域进行扩展；The expansion module is used to detect whether there is new data in the adjacent area of the data source area, and if so, expand the data source area;
第二数据获取模块，用于从扩展后的数据源区域中读取所有数据；The second data acquisition module is used to read all data from the expanded data source area;
计算模块，用于使用获取的设置指令对读取的数据进行重新运算；A computing module, configured to recalculate the read data by using the obtained setting instruction;
更新模块，用于根据重新运算的结果对数据透视表进行更新。The update module is used to update the pivot table according to the result of the recalculation.
进一步作为优选的实施方式，所述扩展模块包括：Further as a preferred embodiment, the expansion module includes:
用于检测数据源区域相邻的行区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有行区域并入到数据源区域中的行扩展模块；It is used to detect whether there is new data in the row area adjacent to the data source area, and if so, expand the data source area, that is, merge all the row areas where the new data is located into the row expansion module in the data source area;
及and
用于检测数据源区域相邻的列区域是否有新增数据，若有，则将数据源区域进行扩展，即将该新增数据所在的所有列区域并入到数据源区域中的列扩展模块。A column extension module used to detect whether there is new data in the column area adjacent to the data source area, and if so, expand the data source area, that is, merge all the column areas where the new data is located into the data source area.
以上是对本发明的较佳实施进行了具体说明，但本发明创造并不限于所述实施例，熟悉本领域的技术人员在不违背本发明精神的前提下还可作出种种的等同变形或替换，这些等同的变型或替换均包含在本申请权利要求所限定的范围内。The above is a specific description of the preferred implementation of the present invention, but the invention is not limited to the described embodiments, and those skilled in the art can also make various equivalent deformations or replacements without violating the spirit of the present invention. These equivalent modifications or replacements are all within the scope defined by the claims of the present application.
Claims (5)
Translated fromChinesePriority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2012105541495ACN103020031A (en) | 2012-12-19 | 2012-12-19 | Method and device for updating data pivot table intelligently |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2012105541495ACN103020031A (en) | 2012-12-19 | 2012-12-19 | Method and device for updating data pivot table intelligently |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN103020031Atrue CN103020031A (en) | 2013-04-03 |
Family
ID=47968650
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2012105541495APendingCN103020031A (en) | 2012-12-19 | 2012-12-19 | Method and device for updating data pivot table intelligently |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN103020031A (en) |
Cited By (7)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106375360A (en)* | 2015-07-24 | 2017-02-01 | 阿里巴巴集团控股有限公司 | A method, device and system for updating map data |
| WO2019033511A1 (en)* | 2017-08-17 | 2019-02-21 | 平安科技（深圳）有限公司 | Data pivot method and apparatus based on database, and computer storage medium |
| CN111061883A (en)* | 2019-10-25 | 2020-04-24 | 珠海格力电器股份有限公司 | Method, device and equipment for updating knowledge graph and storage medium |
| CN111552705A (en)* | 2020-04-24 | 2020-08-18 | 北京字节跳动网络技术有限公司 | Data processing method and device based on chart, electronic equipment and medium |
| CN111581933A (en)* | 2020-04-01 | 2020-08-25 | 北京明略软件系统有限公司 | Method and device for managing forms in webpage |
| CN112784556A (en)* | 2019-11-08 | 2021-05-11 | 珠海金山办公软件有限公司 | Method and device for generating data perspective table value |
| CN114648007A (en)* | 2020-12-18 | 2022-06-21 | 珠海金山办公软件有限公司 | A method, device, computer storage medium and terminal for realizing data processing |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101071414A (en)* | 2007-06-15 | 2007-11-14 | 珠海金山软件股份有限公司 | Device and method for automatically detecting and tracking data change |
| US7493557B2 (en)* | 2001-09-12 | 2009-02-17 | Seiko Epson Corporation | Source file generation apparatus |
| CN102117269A (en)* | 2010-01-06 | 2011-07-06 | 佳能株式会社 | Apparatus and method for digitizing documents |
- 2012
- 2012-12-19CNCN2012105541495Apatent/CN103020031A/enactivePending
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US7493557B2 (en)* | 2001-09-12 | 2009-02-17 | Seiko Epson Corporation | Source file generation apparatus |
| CN101071414A (en)* | 2007-06-15 | 2007-11-14 | 珠海金山软件股份有限公司 | Device and method for automatically detecting and tracking data change |
| CN102117269A (en)* | 2010-01-06 | 2011-07-06 | 佳能株式会社 | Apparatus and method for digitizing documents |
Cited By (10)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN106375360A (en)* | 2015-07-24 | 2017-02-01 | 阿里巴巴集团控股有限公司 | A method, device and system for updating map data |
| WO2019033511A1 (en)* | 2017-08-17 | 2019-02-21 | 平安科技（深圳）有限公司 | Data pivot method and apparatus based on database, and computer storage medium |
| CN111061883A (en)* | 2019-10-25 | 2020-04-24 | 珠海格力电器股份有限公司 | Method, device and equipment for updating knowledge graph and storage medium |
| CN111061883B (en)* | 2019-10-25 | 2023-12-08 | 珠海格力电器股份有限公司 | Method, device, equipment and storage medium for updating knowledge graph |
| CN112784556A (en)* | 2019-11-08 | 2021-05-11 | 珠海金山办公软件有限公司 | Method and device for generating data perspective table value |
| CN112784556B (en)* | 2019-11-08 | 2023-06-30 | 珠海金山办公软件有限公司 | Method and device for generating pivot table value |
| CN111581933A (en)* | 2020-04-01 | 2020-08-25 | 北京明略软件系统有限公司 | Method and device for managing forms in webpage |
| CN111552705A (en)* | 2020-04-24 | 2020-08-18 | 北京字节跳动网络技术有限公司 | Data processing method and device based on chart, electronic equipment and medium |
| CN111552705B (en)* | 2020-04-24 | 2023-08-04 | 北京字节跳动网络技术有限公司 | Data processing method and device based on chart, electronic equipment and medium |
| CN114648007A (en)* | 2020-12-18 | 2022-06-21 | 珠海金山办公软件有限公司 | A method, device, computer storage medium and terminal for realizing data processing |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN103020031A (en) | Method and device for updating data pivot table intelligently | |
| WO2016091174A1 (en) | Search method and apparatus for graph data | |
| CN108628608A (en) | A kind of method and device of data visualization | |
| CN107977396B (en) | A kind of updating method and table data updating device of data table of KeyValue database | |
| WO2017003810A1 (en) | Enhanced mechanisms for managing multidimensional data | |
| WO2015010566A1 (en) | Method for accurately searching for comprehensive information | |
| EP3175373A2 (en) | Presenting dataset of spreadsheet in form based view | |
| JP2022548605A (en) | Spreadsheets with dynamic database queries | |
| CN103902718A (en) | Report control and application thereof | |
| CN105654022A (en) | Method and device for extracting structured document information | |
| EP2988226A1 (en) | Electronic document data updating method and device | |
| CN108108392B (en) | Commodity data management method, device, computer equipment and storage medium | |
| CN104516864A (en) | Report generating method and report generating device | |
| CN101650754A (en) | System and method for marking dimension of dynamically previewable graphics | |
| CN116305895A (en) | Product model generation method, device, computer equipment and storage medium | |
| WO2015188488A1 (en) | Method and apparatus for generating database dictionary of word documents | |
| CN105573769A (en) | Visual configuration method and device of remote control of cement production | |
| CN101436309B (en) | Method and device for modifying formula operator | |
| CN104516919A (en) | Quoting annotation processing method and system | |
| CN109543495B (en) | A method, device, electronic device and storage medium for marking key points of face | |
| CN105930354A (en) | Storage model conversion method and device | |
| CN104850623A (en) | Dynamic extension method and system for multidimensional data analysis model | |
| US9372563B2 (en) | Editing on a touchscreen | |
| CN103605640B (en) | Form adaption method and device | |
| CN103678420B (en) | Method for establishing multi-dimension data set and multi-dimension data set processing device |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| RJ01 | Rejection of invention patent application after publication | Application publication date:20130403 | |
| RJ01 | Rejection of invention patent application after publication |