Method and device for editing trigger formula calculation under spreadsheet

Abstract

The invention provides a method and device for editing trigger formula calculation under a spreadsheet, and the method comprises the steps: setting a user-defined formula and a plurality of fields in the spreadsheet, and enabling the fields and the user-defined formula to form a mapping relation; determining a dependency relationship among the fields, setting a configuration rule of the fields based on the dependency relationship until all the fields are traversed and calculated, storing a calculation result corresponding to each field in each calculation, and inputting the calculation result into a corresponding cell; the rule configuration comprises the steps that when the target field does not have the dependency relationship and the input value of the target field is changed or updated, a user-defined formula corresponding to the target field is executed for calculation; and when the target field has the dependency relationship and the input value of the target field is changed or updated, calling the input value corresponding to the dependency field of the target field, and executing the self-defined formula corresponding to the dependency field for calculation. The problem that existing spreadsheet data entry and calculation are tedious is solved.

Description

Technical Field

[0001] The present application belongs to the technical field of electronic tables, and relates to a method and device for editing trigger formula calculation in an electronic table. Background Art

[0002] A spreadsheet is a form of data storage, processing and analysis using computer software. It is widely used in various fields, such as finance, statistics, data analysis, project management, etc.

[0003] The entry of data for multiple fields in existing spreadsheets requires users to enter the corresponding values ​​themselves. When some rules interact with the current values, users are also required to understand the rules and modify or fill in the values ​​to be entered according to the rules. The process is cumbersome and error-prone, and cannot be customized according to business scenarios, nor can it achieve automated entry.

[0004] In addition, although some fields can use formulas to calculate results, the results cannot be entered into the specified fields along with the current entry operation, and the changed values ​​cannot be easily stored automatically. Summary of the invention

[0005] The present application provides a method and device for editing trigger formula calculation in an electronic form, which solves the problem that data entry and calculation in existing electronic forms are relatively cumbersome.

[0006] In the first aspect, an embodiment of the present application provides a method for editing a trigger formula calculation in a spreadsheet, comprising: setting a custom formula and multiple fields in a spreadsheet, and forming a mapping relationship between the fields and the custom formula; determining the dependency relationship between the fields, and setting the field configuration rules based on the dependency relationship until all fields are traversed and calculated, storing the calculation results corresponding to each field in each calculation and entering them into the corresponding cells; the configuration rules include: when there is no dependency relationship with the target field, when the input value of the target field is changed or updated, executing the custom formula corresponding to the target field for calculation; when there is a dependency relationship with the target field, when the input value of the target field is changed or updated, calling the input value corresponding to the dependent field of the target field, and executing the custom formula corresponding to the dependent field for calculation.

[0007] In combination with the first aspect, in a possible implementation method, the method also includes: when the custom formula of the dependent field is a multi-layer calculation formula, and at least one layer of calculation involves the calculation result of the target field, the calculation result of the target field is directly called and the custom formula corresponding to the dependent field is executed for calculation.

[0008] In combination with the first aspect, in a possible implementation, the field and the custom formula form a mapping relationship, including: defining area attributes and conditional attributes for the field; the area attributes are used to specify the cell area involved when the custom formula is triggered; the conditional attributes are used to specify the triggering conditions of the custom formula; and a mapping relationship is established between the field and the custom formula through the area attributes and the conditional attributes.

[0009] In combination with the first aspect, in a possible implementation manner, it also includes: storing the configuration rules as a configuration file, and implementing the call through a corresponding interface.

[0010] In combination with the first aspect, in a possible implementation method, storing the calculation results corresponding to each field in each calculation and entering them into the corresponding cells also includes: replacing the current value of the corresponding cell with the calculation results or filling the corresponding cell for the next calculation call.

[0011] In combination with the first aspect, in a possible implementation manner, the configuration file is in any format of INI, YAML, JSON or XML.

[0012] In combination with the first aspect, in a possible implementation, the method further includes: when a new calculation type needs to be added, updating the field in the configuration file, and updating the custom formula and configuration rules involved in the field for calling.

[0013] In the second aspect, an embodiment of the present application provides a device for editing trigger formula calculation in a spreadsheet, including: a setting module, used to set a custom formula and multiple fields in a spreadsheet, and the fields form a mapping relationship with the custom formula; wherein the field is a value category of the value entered in the cell; a configuration module, used to determine the dependency relationship between the fields, set the configuration rules of the field based on the dependency relationship until all fields are traversed and calculated, store the calculation results corresponding to each field in each calculation and enter them into the corresponding cells; a first execution module, used to execute the custom formula corresponding to the target field for calculation when the entry value of the target field is changed or updated when there is no dependency relationship with the target field; a second execution module, used to call the entry value corresponding to the dependent field of the target field when the entry value of the target field is changed or updated when there is a dependency relationship with the target field, and execute the custom formula corresponding to the dependent field for calculation.

[0014] In a third aspect, an embodiment of the present application provides a device, comprising: a processor; a memory for storing processor-executable instructions; when the processor executes the executable instructions, it implements the method described in the first aspect or any possible implementation method of the first aspect.

[0015] In a fourth aspect, an embodiment of the present application provides a non-volatile computer-readable storage medium, wherein the non-volatile computer-readable storage medium includes a device for storing a computer program or instructions, and when the computer program or instructions are executed, the method described in the first aspect or any possible implementation method of the first aspect is implemented.

[0016] One or more technical solutions provided in the embodiments of the present application have at least the following technical effects or advantages: This application proposes a method and device for editing trigger formula calculation in a spreadsheet. The method can confirm the fields involved in triggering the formula by defining the fields in the spreadsheet and the mapping relationship between the fields and the custom formula; by setting the configuration rules of the fields, the user does not need to understand the data input rules, and only needs to change the data in some fields to realize automatic data calculation and entry, thereby improving the accuracy and convenience of data entry. The problem of cumbersome data entry and calculation in existing spreadsheets is solved. BRIEF DESCRIPTION OF THE DRAWINGS

[0017] Figure 1 A flowchart of a method for editing trigger formula calculation in a spreadsheet provided by the present application; Figure 2 A schematic diagram of the structure of a device for editing trigger formula calculations in a spreadsheet provided in the present application. DETAILED DESCRIPTION

[0018] The technical solutions in the embodiments of the present application will be described clearly and completely below in conjunction with the drawings in the embodiments of the present application. Obviously, the described embodiments are part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0019] The following describes some of the techniques involved in the embodiments of the present application to facilitate understanding, and they should be considered as merely exemplary. Therefore, it should be appreciated by those of ordinary skill in the art that various changes and modifications may be made to the embodiments described herein without departing from the scope and spirit of the present application. Similarly, for the sake of clarity and conciseness, some descriptions of well-known functions and structures are omitted in the following description.

[0020] Figure 1 1 is a flowchart of a method for editing trigger formula calculation in a spreadsheet provided by an embodiment of the present application, including steps 101 to 106. Figure 1This is only an execution order shown in the embodiment of the present application, and does not represent the only execution order of a method for editing trigger formula calculation in a spreadsheet. In the case where the final result can be achieved, Figure 1 The steps shown may be performed in parallel or reversed.

[0021] Step 101: Set a custom formula and multiple fields in a spreadsheet, and form a mapping relationship between the fields and the custom formula. In the embodiment of the present application, area attributes and conditional attributes are defined for the fields; the area attributes are used to specify the cell area involved when the custom formula is triggered; the conditional attributes are used to specify the triggering conditions of the custom formula; the mapping relationship between the field and the custom formula is established through the area attributes and conditional attributes.

[0022] Specifically, multiple fields and custom formulas are pre-set according to business scenarios. It should be noted that the custom formula is used to calculate the input values ​​corresponding to some fields.

[0023] In one embodiment of the present application, the application scenario is a sales form, and the fields set may be: product name, product unit price, quantity, discount, discount conditions, and total price.

[0024] The custom formula can be: Total price = unit price of product Quantity, or total price = unit price of goods quantity Discount.

[0025] Among them, the total price calculation formula used and the corresponding discount are determined according to the quantity of the goods. When the quantity of the goods meets the discount conditions, the total price calculation formula with discount is executed. It can be seen that the total price field establishes a mapping relationship with the custom formula through the regional attributes (commodity unit price field, quantity field, discount field, discount condition field) and the condition attribute (quantity change or new goods). When the user refreshes the spreadsheet, modifies the quantity or adds a new settlement product, the custom formula will be triggered to calculate the value of the total price. The specific calculation method of the custom formula is shown in steps 102 to 106.

[0026] Step 102: Determine the dependency between the fields. In the embodiment of the present application, first determine whether there is a dependency between the fields. For example, taking the sales form as an example, the total price field depends on the commodity unit price field, the quantity field, the discount field and the discount condition field, the discount field depends on the quantity field and the discount condition field, and the discount condition field depends on the quantity field.

[0027] Configuration rules are set according to the dependency relationship between the fields, and the configuration rules include steps 103 to 105.

[0028] In addition, the configuration rules can be stored as configuration files and called through the corresponding interface. Among them, a configuration file can contain one or more configuration rules. The configuration file is in any format of INI, YAML, JSON or XML.

[0029] Step 103: Determine whether the target field has a dependency relationship. In the embodiment of the present application, the target field at this time is determined, and when the target field has a dependency relationship, step 104 is executed, and when the target field has a dependency relationship, step 105 is executed.

[0030] Step 104: When the input value of the target field is changed or updated, execute the custom formula corresponding to the target field for calculation. In the embodiment of the present application, exemplarily, when it is detected that the user enters a quantity in the input box (exemplarily 10), the "quantity" at this time is the target field. When the value of the input "quantity" is small and does not meet the discount condition (exemplarily the discount condition is set to a quantity greater than 30), there is no discount. That is, when the target field changes but does not exceed the discount condition, it is still the target field, triggering the custom formula "Total Price = Unit Price Quantity” calculation.

[0031] Step 105: When the input value of the target field is changed or updated, the input value corresponding to the dependent field of the target field is called, and the custom formula corresponding to the dependent field is executed for calculation. In the embodiment of the present application, if the user modifies the "quantity" from the original 10 to 55, the changed "quantity" meets the discount condition (exemplarily the discount condition is set to a quantity greater than 30). The value of "discount" depends on the size of the input value of "quantity". At this time, "quantity" is the target field and "discount" is the dependent field. At this time, the custom formula "total price = unit price" is triggered. quantity First, determine the specific discount based on the discount conditions satisfied by the input value of the target field "Quantity", save it and enter it into the corresponding field. When triggering the custom formula calculation, directly call the value in "Discount".

[0032] It should be noted that when the custom formula of the dependent field is a multi-layer calculation formula, and at least one of the calculation layers involves the calculation result of the target field, the calculation result of the target field is directly called and the custom formula corresponding to the dependent field is executed for calculation. Specifically, in the above-mentioned total price calculation process including discount, when the user actually modifies the "quantity", the calculation of two custom formulas is triggered at the same time. One custom formula is a custom formula between discount, discount condition and quantity, and the other custom formula is a custom formula between total price, quantity, unit price and discount. In other words, the custom formula for the total price here is a multi-layer calculation formula. Although two custom formulas are triggered at the same time, the value of the dependent field (discount) needs to be calculated first to get the value of the total price.

[0033] This application does not use a complex custom formula (total price, quantity, discount conditions, discount, unit price), but uses multiple short custom formulas to call each other through the dependency relationship between fields. Short custom formulas can greatly shorten the system's calculation time, improve calculation efficiency, and avoid system congestion or errors caused by modifying multiple fields at one time.

[0034] Step 106: traverse and calculate all fields, store the calculation results corresponding to each field in each calculation and enter them into the corresponding cells. In the embodiment of the present application, the current value of the corresponding cell is replaced or the corresponding cell is filled with the calculation result for the next calculation call. When a new calculation type needs to be added, the fields in the configuration file are updated, and the custom formulas and configuration rules involved in the fields are updated, and then the call can be made.

[0035] It should be noted that the same configuration file in the embodiment of the present application can be applied to a variety of business scenarios. For example, the sales form applied for can be used for a variety of sales scenarios such as fruit sales and airport ticket sales by modifying the product name, unit price, discount conditions and custom formulas. The configuration file is stored in the system. When any software is opened, it involves related calculations. It is only necessary to enter the corresponding value in the input box of the corresponding field, and call the configuration file based on the interface matched by the configuration file, so that the input values ​​of different fields can participate in the calculation of the custom formula. For the calculation of large quantities of the same type of data, the calculation of the relevant data can be completed without modifying the relevant programs of the software, thereby reducing the error rate of manual input and improving the calculation efficiency of batch data.

[0036] Exemplarily, the present application provides an operating process of an embodiment for calculating the total price of a commodity, which is as follows.

[0037] When placing a batch order for a certain product, different discounts will be given when the purchase quantity is in different ranges, and the total price will be obtained based on the unit price, quantity and discount of the product. These relevant data will be recorded in the corresponding order information.

[0038] The defined fields are: Amount (total price), Discount (discount), Quantity (quantity), UnitPrice (unit price).

[0039] Define the custom formula as: Amount=UnitPrice Quantity Discount.

[0040] It can be seen that the Discount field depends on the Quantity field. Configure the rule for the Discount field. When the Quantity is greater than 30 pieces, the discount is 20%, {fields: "Quantity", when: "onNewAndUpdate", formula: "=IF([@Quantity]>30,0.8,1)"}.

[0041] It can be seen that the Amount field depends on the Quantity field, the UnitPrice field, and the Discount field. Configure the rule for the Amount field. The total price is the unit price multiplied by the quantity multiplied by the discount. {fields: "Discount", when: "onNewAndUpdate", formula: "=[@UnitPrice] [@Quantity] [@Discount]”}.

[0042] Enter the data "100" in the input box corresponding to the Quantity field, and detect the configuration rules of the Discount field and the Amount field, and find that they are affected by the Quantity field, that is, there is a dependency relationship between the two. According to the custom formula, the value of the Discount field is calculated to be "0.8", and 0.8 is entered into the Discount field as its value. At this time, it is found that the Amount field is affected by the Discount field, triggering the calculation of the custom formula. After the triggering process is executed, the values ​​of the Quantity field, Discount field, and Amount field are stored and entered into the corresponding fields.

[0043] It should be noted that configuration rules, fields and custom formulas can be set according to business scenarios. The business scenarios here are sales forms for goods, airport tickets, etc. If it is modified to a student's evaluation form, the fields involved and the corresponding custom formulas are different, it can be modified according to specific needs without redeveloping special software.

[0044] It should be noted that the complex calculations involved in existing spreadsheets are usually created by software developers or users themselves to expand the existing functions of Excel. For example, add-ins are used to implement functional extensions, which can be macros (such as VBA codes), XLL files (Excel dynamic link library files), COM add-ins, XML add-ins, etc. By installing and using these add-ins, users can perform more complex calculations, data analysis, graphics generation and other tasks in Excel.

[0045] For simple calculations of ordinary cells, the general principle is to create complex calculation formulas based on cell references and text strings. These calculation formulas are not added as new functions to the Excel function library, but are written as expressions in user-specific worksheets or workbooks through languages ​​such as VBA, or implemented by calling the above-mentioned complex add-ins. They are highly complex and costly to develop, and can only be developed for a certain development requirement, and cannot be generally adapted to other scenarios, with great limitations.

[0046] Compared with the prior art, the technical solution of the present application only uses a commonly used configuration file and the underlying dependency relationship between the data, uses fields and custom formulas to adopt a simple property setting configuration file, and through interface matching and calling, it can realize simple calculations, complex calculations, batch calculations of the same type of data, additions, modifications and other calculation methods for the corresponding input values ​​of fields in different software or electronic forms. It has the advantages of high computing efficiency, convenience, low development cost and wide adaptability.

[0047] Although the present application provides method operation steps as described in the embodiments or flowcharts, more or fewer operation steps may be included based on conventional or non-creative labor. The order of steps listed in this embodiment is only one way of executing the order of many steps and does not represent the only execution order. When the actual device or client product is executed, it can be executed in sequence or in parallel according to the method shown in this embodiment or the accompanying drawings (for example, in a parallel processor or multi-threaded processing environment).

[0048] like Figure 2 As shown, the embodiment of the present application also provides a device 200 for editing trigger formula calculation in a spreadsheet. The device includes: a setting module 201, a configuration module 202, a first execution module 203 and a second execution module 204, as follows.

[0049] The setting module 201 is used to set a custom formula and multiple fields in the electronic form, and the fields and the custom formula form a mapping relationship.

[0050] The configuration module 202 is used to determine the dependency relationship between the fields, set the configuration rules of the fields based on the dependency relationship until all the fields are traversed and calculated, store the calculation results corresponding to each field in each calculation and enter them into the corresponding cells.

[0051] The first execution module 203 is used to execute the custom formula corresponding to the target field for calculation when there is no dependency relationship between the target fields and when the input value of the target field is changed or updated.

[0052] The second execution module 204 is used to call the entry value corresponding to the dependent field of the target field when there is a dependency relationship between the target fields and when the entry value of the target field is changed or updated, and execute the custom formula corresponding to the dependent field for calculation.

[0053] Some modules in the apparatus described in the present application can be described in the general context of computer executable instructions executed by a computer, such as program modules. Generally, program modules include routines, programs, objects, components, data structures, classes, etc. that perform specific tasks or implement specific abstract data types. The present application can also be practiced in distributed computing environments, in which tasks are performed by remote processing devices connected through a communication network. In a distributed computing environment, program modules can be located in local and remote computer storage media including storage devices.

[0054] The devices or modules described in the above application embodiments can be implemented by computer chips or entities, or by products with certain functions. For the convenience of description, the above devices are described in various modules according to their functions. When implementing the embodiments of the present application, the functions of each module can be implemented in the same or multiple software and / or hardware. Of course, the module that implements a certain function can also be implemented by combining multiple sub-modules or sub-units.

[0055] The methods, devices or modules described in this application can be implemented in the form of computer-readable program codes. The controller can be implemented in any appropriate manner. For example, the controller can take the form of a microprocessor or processor and a computer-readable medium storing computer-readable program codes (such as software or firmware) that can be executed by the (micro)processor, logic gates, switches, application-specific integrated circuits (English: Application Specific Integrated Circuit; Abbreviation: ASIC), programmable logic controllers and embedded microcontrollers. Examples of controllers include but are not limited to the following microcontrollers: ARC 625D, Atmel AT91SAM, Microchip PIC18F26K20 and Silicone Labs C8051F320. The memory controller can also be implemented as part of the control logic of the memory. Those skilled in the art also know that in addition to implementing the controller in the form of pure computer-readable program codes, the controller can be implemented in the form of logic gates, switches, application-specific integrated circuits, programmable logic controllers and embedded microcontrollers by logically programming the method steps. Therefore, this controller can be considered as a hardware component, and the devices included in it for implementing various functions can also be regarded as structures within the hardware component. Or even, the means for realizing various functions may be regarded as both a software module for realizing the method and a structure within a hardware component.

[0056] An embodiment of the present application further provides a device, comprising: a processor; a memory for storing processor executable instructions; when the processor executes the executable instructions, the method described in the embodiment of the present application is implemented.

[0057] The embodiments of the present application also provide a non-volatile computer-readable storage medium on which a computer program or instruction is stored. When the computer program or instruction is executed, the method described in the embodiments of the present application is implemented.

[0058] In addition, each functional module in each embodiment of the present invention may be integrated into one processing module, or each module may exist independently, or two or more modules may be integrated into one module.

[0059] The above storage media include but are not limited to random access memory (RAM), read-only memory (ROM), cache, hard disk drive (HDD) or memory card. The memory can be used to store computer program instructions.

[0060] It can be seen from the description of the above implementation methods that those skilled in the art can clearly understand that the present application can be implemented by means of software plus necessary hardware. Based on this understanding, the technical solution of the present application can be essentially or partly reflected in the prior art in the form of a software product, or it can be reflected in the implementation process of data migration. The computer software product can be stored in a storage medium, such as ROM / RAM, a magnetic disk, an optical disk, etc., including a number of instructions for a computer device (which can be a personal computer, a mobile terminal, a server, or a network device, etc.) to execute the methods described in the various embodiments of the present application or certain parts of the embodiments.

[0061] The various embodiments in this specification are described in a progressive manner, and the same or similar parts between the various embodiments can be referred to each other, and each embodiment focuses on the differences from other embodiments. All or part of this application can be used in many general or special computer system environments or configurations. For example: personal computers, server computers, handheld devices or portable devices, tablet devices, mobile communication terminals, multi-processor systems, microprocessor-based systems, programmable electronic devices, network PCs, minicomputers, mainframe computers, distributed computing environments including any of the above systems or devices, etc.

[0062] The above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit the present application. Although the present application has been described in detail with reference to the aforementioned embodiments, a person of ordinary skill in the art should understand that the technical solutions described in the aforementioned embodiments may still be modified, or some or all of the technical features thereof may be replaced by equivalents. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the present application.

Claims

1. A method for editing trigger formula calculation in a spreadsheet, It is characterized in that include: A custom formula and a plurality of fields are set in a spreadsheet, and a mapping relationship is formed between the fields and the custom formula; Determine the dependency relationship between the fields, set the configuration rules of the fields based on the dependency relationship until all fields are traversed and calculated, store the calculation results corresponding to each field in each calculation and enter them into the corresponding cells; The configuration rules include: When there is no dependency relationship between the target field and the input value of the target field is changed or updated, the custom formula corresponding to the target field is executed for calculation; When the target field has a dependency relationship, when the input value of the target field is changed or updated, the input value corresponding to the dependent field of the target field is called, and the custom formula corresponding to the dependent field is executed for calculation.

2. A method for editing trigger formula calculation in a spreadsheet as claimed in claim 1, It is characterized in that The method also includes: when the custom formula of the dependent field is a multi-layer calculation formula, and at least one layer of calculation involves the calculation result of the target field, the calculation result of the target field is directly called and the custom formula corresponding to the dependent field is executed for calculation.

3. A method for editing trigger formula calculation in a spreadsheet as claimed in claim 1, It is characterized in that The field and the custom formula form a mapping relationship, including: defining area attributes and conditional attributes for the field; The area attribute is used to specify the cell area involved when the custom formula is triggered; The condition attribute is used to specify the triggering condition of the custom formula; A mapping relationship is established between the field and the custom formula through the area attribute and the condition attribute.

4. A method for editing trigger formula calculation in a spreadsheet as claimed in claim 1, It is characterized in that Also includes: The configuration rules are stored as configuration files and called through corresponding interfaces.

5. A method for editing trigger formula calculation in a spreadsheet as claimed in claim 1, It is characterized in that The storing of the calculation results corresponding to each field in each calculation and entering them into the corresponding cells also includes: The calculation result is used to replace the current value of the corresponding cell or fill the corresponding cell for the next calculation call.

6. A method for editing trigger formula calculation in an electronic form as claimed in claim 4, It is characterized in that The configuration file is in any format of INI, YAML, JSON or XML.

7. A method for editing trigger formula calculation in an electronic form as claimed in claim 4, It is characterized in that The method further includes: when a new calculation type needs to be added, updating the field in the configuration file, and updating the custom formula and configuration rules involved in the field for calling.

8. A device for editing trigger formula calculation in a spreadsheet, It is characterized in that include: A setting module, used for setting a custom formula and a plurality of fields in a spreadsheet, wherein the fields form a mapping relationship with the custom formula; A configuration module is used to determine the dependency relationship between the fields, set the configuration rules of the fields based on the dependency relationship until all the fields are traversed and calculated, store the calculation results corresponding to each field in each calculation and enter them into the corresponding cells; A first execution module, configured to execute the custom formula corresponding to the target field for calculation when an input value of the target field is changed or updated when there is no dependency relationship between the target field and the target field; The second execution module is used to call the entry value corresponding to the dependent field of the target field when there is a dependency relationship between the target field and when the entry value of the target field is changed or updated, and execute the custom formula corresponding to the dependent field for calculation.

9. A device for executing a method for editing a trigger formula calculation in a spreadsheet, It is characterized in that include: processor; a memory for storing processor-executable instructions; When the processor executes the executable instructions, the method according to any one of claims 1 to 7 is implemented.

10. A non-volatile computer-readable storage medium, It is characterized in that The device comprises a computer program or an instruction for storing the computer program or the instruction, which, when executed, enables the method according to any one of claims 1 to 7 to be implemented.

Images