Method and system for controlling spreadsheet formula

Abstract

The invention relates to the technical field of spreadsheet formula processing, in particular to a spreadsheet formula control method and system, and the method comprises the following steps: setting a formula state for a cell containing a formula in a form, and displaying the formula state in the cell through a visual identifier; responding to predefined operation of formula state switching, and switching the formula state; when the formula state is an enabled state, the cell uses a calculation result of the formula as an actual value of the cell; when the formula state is a forbidden state, the cell neglects the formula, and the formula does not influence the actual value of the cell any more. According to the method, seamless switching between the cell formula content and the user-defined input content can be completed under the condition that the formula is not lost, so that the working efficiency is improved, and errors are reduced.

Description

Technical Field

[0001] This invention relates to the field of spreadsheet formula processing technology, and in particular to a method and system for controlling spreadsheet formulas. Background Technology

[0002] In spreadsheet software, some cells contain formulas. These cells perform calculations based on the formulas and display the results. In practice, sometimes it's necessary to fix the value of cells containing formulas to a specific value, rather than dynamically displaying the calculated result. Currently, modifying a cell value overwrites the existing formula, causing it to be lost. If you later want to restore the formula, you must manually re-enter it. If the cell content needs to switch between automatic calculation and manual entry, frequent manual entry is required, a process that is not only tedious but also prone to errors. Summary of the Invention

[0003] To address the shortcomings of existing technologies, this invention provides a method and system for controlling spreadsheet formulas.

[0004] To achieve the above objectives, in a first aspect, the present invention provides a method for controlling formulas in a spreadsheet. The method includes the following steps: setting a formula state for cells containing formulas in the spreadsheet, the formula state being displayed visually within the cell; switching the formula state in response to a predefined operation of formula state switching; when the formula state is enabled, the cell uses the calculated result of the formula as its actual value; when the formula state is disabled, the cell ignores the formula, and the formula no longer affects the cell's actual value. This invention enables seamless switching between cell formula content and custom input content without losing the formula, thereby improving work efficiency and reducing errors.

[0005] Optionally, the predefined operation is clicking a visual icon.

[0006] Optionally, when the formula state is switched to the disabled state in response to the predefined operation, the actual value of the cell is set to the most recent user-entered content.

[0007] Optionally, when a cell whose formula status is in the enabled state receives a manual modification of the cell value by the user, the formula status is automatically switched to the disabled state.

[0008] Secondly, the present invention provides a control system for spreadsheet formulas, the control system comprising: a display, a processor, and a storage device, the storage device comprising a computer-readable storage medium storing a computer program, the computer program comprising program instructions, the program instructions, when executed by the processor, causing the processor to implement the control method for spreadsheet formulas provided by the present invention.

[0009] The present invention has at least the following beneficial effects: 1. It avoids the problem of information loss caused by new values ​​directly overwriting formulas; 2. Seamless switching between cell formulas and input content can be completed at any time, reducing the need for repeated formula entry and improving work efficiency; 3. Users can quickly switch between formulas and input values ​​with simple clicks, improving work efficiency; 4. This invention provides a system adapted to the method, and the system can stably run the method, thereby improving the practicality of the method. Attached Figure Description

[0010] To more clearly illustrate the technical solutions of the embodiments of this application, the accompanying drawings used in the embodiments will be briefly introduced below. It should be understood that the following drawings only show some embodiments of this application and should not be regarded as a limitation of the scope. For those skilled in the art, other related drawings can be obtained based on these drawings without creative effort.

[0011] Figure 1 This is a flowchart illustrating a method for controlling spreadsheet formulas according to an embodiment of the present invention. Figure 2 This is a schematic diagram of a common table according to an embodiment of the present invention; Figure 3 This is a schematic diagram of a table marked with an enabled formula icon, according to an embodiment of the present invention. Figure 4 This is a schematic diagram of a table marked with a disabled formula icon according to an embodiment of the present invention; Figure 5 This is a schematic diagram of the control system for spreadsheet formulas according to an embodiment of the present invention. Detailed Implementation

[0012] Specific embodiments of the present invention will now be described in detail. It should be noted that the embodiments described herein are for illustrative purposes only and are not intended to limit the invention. In the following description, numerous specific details are set forth in order to provide a thorough understanding of the invention. However, it will be apparent to those skilled in the art that these specific details are not necessary to practice the invention. In other instances, well-known circuits, software, or methods have not been specifically described to avoid obscuring the invention.

[0013] Throughout this specification, references to "an embodiment," "an embodiment," "an example," or "an example" mean that a particular feature, structure, or characteristic described in connection with that embodiment or example is included in at least one embodiment of the invention. Therefore, the phrases "in an embodiment," "in an embodiment," "an example," or "an example" appearing in various places throughout the specification do not necessarily refer to the same embodiment or example. Furthermore, specific features, structures, or characteristics can be combined in one or more embodiments or examples in any suitable combination and / or sub-combination. Moreover, those skilled in the art will understand that the illustrations provided herein are for illustrative purposes and are not necessarily drawn to scale.

[0014] It should be noted in advance that, in one alternative embodiment, except for independent descriptions, the same symbols or letters appearing in all formulas have the same meaning and value.

[0015] In one optional embodiment, please refer to Figure 1 This invention provides a method for controlling spreadsheet formulas, the method comprising the following steps: S1. Set the formula status for cells containing formulas in the table. The formula status is displayed in the cell by a visual identifier.

[0016] Specifically, in this embodiment, for any table, at least three attributes are defined for its cells: cell formula, formula status, and actual value. The cell formula attribute stores the formula within the cell; the formula status attribute stores the formula status of the cell, which includes at least an enabled formula status and a disabled formula status, represented by 1 and 0 respectively. When the formula status is enabled, the cell uses the calculated result of the formula as its actual value; when the formula status is disabled, the formula is ignored, and the formula no longer affects the cell's actual value. The actual value of a cell refers to the value used during cell display, external references, and other operations.

[0017] For easier understanding, please refer to Figure 2 .exist Figure 2In this example, cells A1 and B1 have no formulas, and the actual value of cell C1 is the sum of the actual values ​​of cells A1 and B1. Therefore, the formula in cell C1 can be expressed as "=A1+B1". At this point, no settings have been made for the formula state of cell C1, and the function to switch formula states is not available.

[0018] exist Figure 2 Based on this, please refer to Figure 3 and Figure 4 To clearly indicate the formula status, different visual identifiers are used to distinguish different formula statuses in cells where a formula status is set. This embodiment uses icons as visual identifiers, displayed in the upper left corner of the cell. Specifically, the "Enable Formula" icon is used. "Indicates that formulas are enabled; use the disabled formula icon." "" indicates that the formula is disabled.

[0019] In other alternative embodiments, cell background color can be used to distinguish formula status, for example, using a light green background to indicate that the formula is enabled and a light gray background to indicate that the formula is disabled.

[0020] S2. In response to a predefined operation for switching formula states, switch the formula state.

[0021] Specifically, in this embodiment, in Figure 2 Based on this, please refer to Figure 3 and Figure 4 .

[0022] The predefined operation is clicking the visual icon. The icon displayed in step S1 serves as the operation area for switching formula states. Clicking this area switches between the enabled and disabled states of the formula; that is, clicking the icon switches between the enabled and disabled states of the formula. In other optional embodiments, the formula state can be switched in other ways, such as via a cell right-click menu, a keyboard shortcut when a cell is selected, or a formula state switching button on the toolbar. Clicking this button switches the formula state of the currently selected cell. Of course, the predefined operation can also include one or more of the following: clicking the visual icon, cell right-click menu operation, and toolbar state switching operation.

[0023] Before performing formula calculations on cells, the table first checks the cell's formula status. If the formula is disabled, the calculation is ignored, and the cell's actual value remains unchanged. If the formula is enabled, the formula is calculated normally, and the cell's actual value is updated. Cells without a set formula status do not have the function to switch formula statuses and are treated as normal cells.

[0024] Clicking the visualization icon switches the formula status of cell C1 from enabled to disabled. Cell C1 can then be edited like a normal cell. If the most recent user-entered value in cell C1 is not empty, that value is used as the actual value of cell C1. For example, changing the value of cell C1 to 50 only affects the actual value of cell C1, not the formula or formula status. The actual value of the cell is set to the most recent user-entered value, i.e., 50. Otherwise, the result of the current formula "=A1+B1", 20, is used as the actual value of cell C1. Each time a value is manually entered into cell C1, the most recent user-entered value in C1 is updated accordingly.

[0025] When the formula in cell C1 is enabled, if you manually enter a value into cell C1, the formula status of cell C1 will be automatically set to 0, switching to disabled state, and the actual value of cell C1 will be modified with the entered value, while the formula in cell C1 remains unchanged.

[0026] It should be noted that in some cases, the actions described in the specification can be performed in different orders and still achieve the desired results. In this embodiment, the order of steps is given only to make the embodiment clearer and easier to explain, and not to limit it.

[0027] In one optional embodiment, please refer to Figure 5 The present invention provides a control system for spreadsheet formulas. The control system for spreadsheet formulas includes: a display 1, a processor 2, and a storage 3. The storage 3 includes a computer-readable storage medium storing a computer program. The computer program includes program instructions. When the program instructions are executed by the processor 2, the processor 2 performs the contents described in steps S1 and S2.

[0028] Specifically, in this embodiment, the display 1, processor 2, and storage 3 are electrically connected. Users can view tables, modify cell values, and switch formula states via the display 1.

[0029] In summary, this invention, by setting a formula status flag in cells containing formulas within a table, allows users to quickly switch between formulas and entered values ​​with a simple click. This seamless switching between cell formulas and input content is possible at any time, reducing the need for repetitive formula entry, improving work efficiency, and preventing information loss caused by new values ​​directly overwriting formulas when modifying the table. Furthermore, this invention provides a system compatible with the method, and this system can stably run the method, enhancing its practicality.

[0030] Finally, it should be noted that the above embodiments are only used to illustrate the technical solutions of the present invention, and not to limit them. Although the present invention has been described in detail with reference to the foregoing embodiments, those skilled in the art should understand that modifications can still be made to the technical solutions described in the foregoing embodiments, or equivalent substitutions can be made to some or all of the technical features therein. Such modifications or substitutions do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present invention, and they should all be covered within the scope of the claims and specification of the present invention.

Claims

1. A method for controlling spreadsheet formulas, characterized in that, Includes the following steps: Set a formula status for cells containing formulas in a table; the formula status is displayed in the cell by a visual identifier. In response to a predefined operation that switches the formula state, the formula state is switched; When the formula is enabled, the cell uses the calculation result of the formula as the cell's actual value; When the formula is disabled, the cell ignores the formula, and the formula no longer affects the actual value of the cell.

2. The method for controlling spreadsheet formulas according to claim 1, characterized in that: The predefined operation is clicking the visual icon.

3. The method for controlling spreadsheet formulas according to claim 1, characterized in that: When the formula state is switched to the disabled state in response to the predefined operation, the actual value of the cell is set to the most recent user-entered content.

4. The method for controlling spreadsheet formulas according to claim 1, characterized in that: When a cell whose formula is in the enabled state receives a manual modification of the cell value by the user, the formula state is automatically switched to the disabled state.

5. A control system for spreadsheet formulas, characterized in that, The control system for a spreadsheet formula includes a display, a processor, and a storage device. The storage device includes a computer-readable storage medium storing a computer program. The computer program includes program instructions that, when executed by the processor, cause the processor to implement the control method for a spreadsheet formula as described in any one of claims 1-4.

Images