RU2563144C1 - Spreadsheet filling method - Google Patents

Abstract

FIELD: information technologies.

SUBSTANCE: in this method through the data input device the source data are inserted into the influencing spreadsheet cell, their resultant parameter dependent by means of formulas on their change in the influencing cell is determined with subsequent display in the resultant cell by means of the data output device, designation of the resultant cell by a visual signal depending on the preset condition. The source data are inserted into the influencing or the resultant cell; if the source data have been inserted into the resultant cell, then there the inserted value is indicated as the resultant parameter, and if the source data have been inserted into the influencing cell, then in case of coincidence of the previous value of their resultant parameter with its value displayed in the resultant cell in the resultant cell the previous value of the resultant parameter is replaced by its new value, and by visual signal the resultant cell is designated if the value of the resultant parameter displayed in it doesn't conform to its value determined at least by one of its formulas.

EFFECT: automation and improvement of efficiency of data input and calculation.

4 dwg

Description

Spreadsheet Fill Method

The present invention relates to the automation of processes for inputting and processing data from accounting (financial) statements, as well as any other tables with numerical data, interconnected by mathematical formulas, and can be used, for example, for preparing financial (accounting) statements of organizations, notes (explanations) ) to the financial statements, other summary data on the financial and economic operations of legal entities and individuals.

It is known that a common type of business problem is the generalization of numerical data, interconnected by arithmetic dependencies (formulas), and their presentation in tabular form. To solve this problem, application software related to the input and processing of numerical data, such as spreadsheets, is used. In this case, the data input fields (cells) are usually divided by the user into cells associated with data input (influencing cells) and cells associated with the output of the calculation result based on the data entered (resulting cells).

Known patent for the invention RU 2487402 "DYNAMIC FORMULAS FOR CELLS OF THE ELECTRON TABLE", publ. 07/10/2013, patent holder: MICROSOFT CORPORATION (US). From this document, a method for working with a spreadsheet is known, which contains the steps in which: they access a cell in a spreadsheet; moreover, the cell includes a basic formula and a correction formula that represents a different value from the basic formula for the cell; setting the correction formula as the active formula for the cell of the spreadsheet, the active formula being visible to the cell when it is displayed after evaluating the spreadsheet; ensure that the basic formula is not extracted from the data source when the basic formula is a data extraction formula and the correction formula is an active formula; evaluate the spreadsheet to determine the result for the correction formula by executing the correction formula and display the result, so that the result shows the result of the correction formula and its effect on the dependent cells in the spreadsheet. At the same time, the cells in the spreadsheet are marked with a visual signal, which changed due to the evaluation of the correction formula. Those. visual signal indicate a cell to indicate which formula (basic or correction) is currently working.

Further in the present description, the following terms are used: “influencing cell” - in a known manner: a data source that is data anywhere in the table; "Resulting cell" - in a known manner: a cell that contains at least one formula; "Resulting indicator" - in a known manner: the value determined by these formulas.

Thus, in essence, the known method is a method of filling in a spreadsheet, which includes the following steps: entering through the input device of the electronic computer of the source data in at least one influencing cell of the spreadsheet contained in the electronic computer; determination of their resulting indicator, dependent by formulas on their changes in the influencing cell, with its subsequent display in the resulting cell by means of an information output device of an electronic computer; designation of the resulting cell with a visual signal depending on a given condition.

This known method of filling out a spreadsheet is selected as a prototype, since it has the largest number of essential features that match the essential features of the claimed invention.

However, the prototype has a significant drawback, namely, the low efficiency of the user with the spreadsheet in cases where the user operates on tables whose cells are associated with more than one formula, and the formulas are not alternative (basic and corrective), but equally true for this indicator. Having more than one formula associated with the resulting cell, the prototype contains a manual (independent) user choice of the formula, the result of which is displayed in the cell at a given time. In addition, the prototype does not provide for the use of cells associated with formulas for input into the user of the source data, but only as cells for outputting the result of the formulas contained in them.

The objective of the present invention is to provide a new method of filling a spreadsheet with the achievement of the following technical result, namely, increasing the efficiency of a user with a spreadsheet by speeding up and simplifying the data entry process.

The problem is solved due to the fact that in the known method of filling in a spreadsheet, which consists in introducing at least one influencing cell of the spreadsheet contained in the electronic computer using the input device of the electronic computer, determining their resultant index dependent by means of formulas from their change in the influencing cell, with its subsequent display in the resulting cell by means of an information output device, electronically calculates According to the present invention, the input data is selected in the influencing or in the resulting cell, if the initial data is entered into the resulting cell, then the input value is displayed in it as the resultant parameter, according to the present invention , and if the initial data was entered into the influencing cell, then when the previous value of their resulting indicator coincides with its value displayed in the resulting cell, ene in the resulting cell of the previous value of the resultant index at its new value, which represent a visual signal resulting cell if it displayed in the resulting index value does not match its value determined for at least one of the following claims.

Thus, the claimed technical solution with its entire set of essential features allows you to increase the efficiency of the user with the spreadsheet by speeding up and simplifying data entry, since not only the initial data is input to evaluate the resulting indicator by its formula, but also the resulting indicator, which is in this case source data, while not destroying the formulaic relationships between cells and leaving the possibility of automatically recounting them. That is, according to the claimed method, cells in electronic form (table) of data entry are not divided at the stage of inputting information into cells of the source data and cells of the results of calculations. A cell can be simultaneously used both as a source for inputting information by a user and as an interface for displaying a result indicator determined by one or more formulas specified for it. At the same time, the user, unlike the prototype, does not make a choice, the result of calculating which particular formula of the several available for this indicator he wants to see in the cell. The output of the resulting indicator is determined automatically. This allows the user, on the one hand, to start inputting with the final values of the resulting indicator, which is especially true in the case of interconnected tables, on the other hand, to save the automatic recalculation of values in the resulting cells when the data in the influencing cells changes. Moreover, for more efficient work with tables, the resulting cell is indicated by a visual signal if the value of the resulting indicator displayed in it does not correspond to its value determined by at least one of the formulas. This allows the user to timely notice and eliminate the mismatch between the displayed value and the result determined by the formulas.

The applicant conducted a patent information search on this topic, as a result of which the claimed combination of essential features was not identified. Therefore, the present invention can be recognized as new.

The compliance of this invention with the patentability criterion of "inventive step" is justified by the following.

This invention for a specialist does not follow logically from the prior art. So, for example, the currently used method of filling out a spreadsheet and other forms of data entry is as follows (implemented in the table processors MS Excel, Lotus 1-2-3, OpenOfiice.org Calc, LibreOffice Calc): the user enters data into the influencing cells and automatically gets the value of the resulting metric in the resulting cell associated with a particular formula.

Wherein:

- in one resultant cell displays the resulting indicator, determined by only one formula (the only one or one of several currently selected by the user, as provided in the prototype);

- the user does not have the opportunity to enter the resulting metric in the resulting cell, or there is such a possibility, and the value of the resulting cell from this moment will no longer be determined by the formula (the cell ceases to be the result), or will continue to be determined by the formula, automatically replacing the previously entered value by the user for any changing the influencing cell.

It follows that the tabular forms are filled in one direction - by entering a value in the influencing cell with automatic calculation of the value of the resulting cell. Entering first the result of the formula, and then the values of its members without refusing to automatically recount, it makes no sense to pre-enter the resulting value. Another disadvantage of the described method is the lack of a reasonable representation in the cell of the result of calculating an indicator for which there is more than one equal formula (the choice of one result from several if they do not coincide).

For example, if there are 2 interconnected tables (Fig. 1), in which the values of the resulting rows are estimated by formulas, then according to the currently widespread automation method for filling in numeric tabular forms, implemented, for example, in EXCEL ™ table processors from MICROSOFT CORPORATION, OpenOfilce. org Calc et al., it is supposed to enter first M and K, and then B, i.e. first fill out table 2, and then table 1. But often in practical tasks the user already knows the value of A and it is more convenient to fill out table 1 and then table 2. If the user enters the value A, then the formula A = M + K will temporarily not be true, until the user enters the values of M and K. But as soon as the user enters the value of the first of the missing members (M or K), indicator A is automatically recalculated by the formula A = M + K, the value of A will change compared to the correct value, earlier entered by the user. At the same time, the value in cell C will change compared to the already presented correct one. Those. it doesn’t make sense for the user to enter the value A in table 1, it will still begin to change as table 2 is filled in. Moreover, if a user who has already entered all the values in table 1 and starts to enter data in table 2 decides to refuse to fill it in further, he will have to re-enter the data in table 1, since the values of cells A and C have already been automatically recounted with the input of the first indicator (M or K) of table 2.

Thus, the well-known methods of working with spreadsheets allow you to enter a formula in a cell and then the resulting value from it will be determined automatically. However, you cannot enter a numerical value in the resulting cell, because the formula will be replaced and the value of the resulting indicator from that moment will not be automatically determined. At the same time, they also use visualization in case of an error, for example, an error message appears in the cell if division by 0 occurs, or if the entered formula is syntactically incorrect, or if the formula refers to a nonexistent cell. Those. known solutions flag technical errors.

In the claimed invention, the situation is different. In the proposed method of filling in a spreadsheet, which is a two-way input method, they solve this problem by means of a specific algorithm for turning on and off the automatic change (conversion) of the values of the resulting indicator in the resulting cells. Recalculation of the formula and the replacement in the resultant cell of the value of the resulting indicator with a new one occurs provided that before entering the result of the formula was correct (i.e., it coincided with the value presented in the resultant cell). A cell in which the presented value of the resulting indicator does not coincide with the result of its calculation by the formula is marked with a visual signal. As soon as the user settles the formula (by entering a new value in the influencing or resulting cells), the formula will be correct and any change in the values of the influencing cells will again lead to automatic recalculation of the indicator in the resulting cell. Using visualization when the result of the formula does not match the displayed result allows the user to understand what mode the formula is in, whether the value of the resulting indicator is correct now (and therefore the formula continues to be recalculated automatically) or incorrect (and auto-recalculation is suspended until the formula is settled). At the same time, a visual signal draws the user's attention to the cell without interfering with the display of a numerical value in it.

The essence of the claimed invention and the possibility of its practical implementation is illustrated by the description and drawings below.

In FIG. 2 is a diagram of a method for filling out a spreadsheet.

In FIG. 3 shows a possible configuration of a device for implementing the proposed method.

In FIG. 4 presents an example implementation of the proposed method.

The method of filling in the spreadsheet (Fig. 2) consists in entering, by means of the input device of the electronic computer, the source data into at least one influencing cell of the electronic table contained in the electronic computer, determining their resultant index, dependent by formulas on their change in influencing cell, with its subsequent display in the resultant cell by means of the information output device of the electronic computer, designation of the resultant cell visa signal depending on the specified condition. In this case, the initial data is brought to the choice in the influencing or in the resultant cell, in this case, if the initial data is entered into the resulting cell, then the entered value is displayed in it as the resulting indicator, and if the initial data is entered into the influencing cell, then if the previous value coincides their resultant indicator with its value displayed in the resultant cell, the previous value of the resultant indicator is replaced in the resultant cell with its new value, moreover, with a visual signal about mean the resultant cell if it displayed in the resulting index value does not match its value determined for at least one of the following claims.

A cell in this document refers to a visual field of an electronic form intended for a user to enter a numerical value and / or display a numerical value obtained by mathematically calculating the results of formulas.

A visual signal is a color or other marking of a cell or the number contained in it in order to draw the user's attention to the discrepancy between the value presented in the cell and the value obtained by calculating this indicator by the formula. The visual signal can additionally be divided into two types:

- Type A (“error”): if there is a discrepancy, while at least one of the members of the formula is not equal to zero;

- type B (“warning”): if there is a mismatch, while all members of the formula have zero values.

Type B differs from type A in that the type B mismatch does not always indicate the input of erroneous data. For example, the user only populated table 1 (see FIG. 1). The cell with indicator A is marked with a visual signal, because data M and K are not entered (equal to 0). But this does not mean that the incorrect value A is entered, it means that the user did not decrypt this value in the context of M and K (a visual signal of type B is used). This approach gives the user information about what the visual signal is associated with: entering erroneous data (type A) or with the presence of other cells (tables), where the value of the indicator is described (decrypted) through other members whose values have not yet been entered (type B).

The proposed method can be implemented in any computing environment suitable for executing program code, including personal computers, handheld computing devices, mini-computers, computing servers, etc. Distributed computing environments can also be used where tasks are performed by remote processing devices connected between a computer network.

In FIG. 3 shows a possible configuration of a device for implementing the proposed method. The specified device is an electronic computer and includes a computing processor 1 ("CPU"), internal memory 2, consisting of random access memory ("RAM") and read-only memory ("ROM"), external memory 3, which can be represented by hard drives, solid state drives, optical disks, flash memory and other devices for storing information. As a source of user data 4, peripheral computer information input devices 5 (keyboard, mouse-type manipulator, electronic pen, touch-sensitive and other information input devices) can be used, as well as network devices connected via the corresponding network interface 6, receiving information entered by a user on remote devices of a computer network. As the interface 7 data output using an electronic display or printing device on paper or other media connected through an input / output controller. The listed devices are interconnected via a system bus 8.

As the computing environment for the implementation of the proposed invention, the operating system 9 can act on the basis of which the application software 10 is functioning, including, inter alia, the electronic data entry form 11 (table). Examples of operating systems are the WINDOWS operating system from MICROSOFT CORPORATION, as well as UNIX-like operating systems (LINUX, FREEBSD, ANDROID, etc.). As an application software that implements an electronic form (table) of data entry and the method of working with it described in this patent, a program developed by the patent author for preparing financial statements in accordance with international standards “IFRS Reporting + Transformation” [http: // www .audit-it.ru / ifrs /].

Electronic form 11 (table) data entry consists of a number of fields (cells) associated with certain indicators. Some of these indicators are calculated according to formulas whose members are other indicators. An indicator can be the result of calculations using one or more formulas that are not alternative but equally valid for a given indicator. For example, an indicator of the value of inventories can be obtained by two equal formulas: a) the amount of inventories for all types, and b) the amount for all storage locations.

A feature of the proposed method is the lack of communication of one specific formula with a specific cell in which its result is presented. A cell is not associated with a formula, but with an indicator, which may be the result determined by more than one formula (Fig. 4). Moreover, the formulas are not alternative, but are equally valid for this indicator, have the same priority.

This is often found in financial statements when one indicator is decoded in several sections (characteristics). For example, the total amount of material assets is presented in two sections: the sum by types of values and the sum by places of their storage. The two-way input algorithm proposed in the present method facilitates filling in such tables, since allows you to start input from any of the indicators (Fig. 4). Moreover, the display of the number in the resulting cell (C), in accordance with the claimed method, makes the process of filling out tables more convenient and visual for the user compared to the constant automatic recalculation of the indicator C for any change in the value of one of the terms of the formulas (A, B, T, R).

Thus, the technical result of the proposed method is achieved, namely, increasing the efficiency of the user with the spreadsheet.

Claims (1)

1. A method of filling in a spreadsheet, which consists in introducing at least one influencing cell of the spreadsheet contained in the computing machine by input device of the electronic computer, determining their resultant index, dependent by formulas on their changes in the influencing cell , with its subsequent display in the resultant cell by means of the information output device of the electronic computer, designation of the resultant cell visual signal, depending on the given condition, characterized in that the source data is optionally entered into the influencing or the resultant cell, in this case, if the source data is entered into the resultant cell, then the entered value is displayed as the result indicator in it, and if the source data introduced into the influencing cell, if the previous value of their resulting indicator coincides with its value displayed in the resulting cell, the previous value of the resulting value is replaced in the resulting cell indicator to its new value, and the resultant cell is indicated by a visual signal if the value of the resulting indicator displayed in it does not correspond to its value determined by at least one of its formulas.

Images