JP2656923B2 - Spreadsheet processor - Google Patents

Description

DETAILED DESCRIPTION OF THE INVENTION [Summary] A table calculation processing system in which an element (referred to as a cell) at the intersection of a row and a digit in a table is specified by coordinates, and a calculation formula for giving the value of the cell as a cell attribute can be defined. Then, in order to eliminate the complexity caused by specifying the cell coordinates, a temporary calculation formula using the table item names is created, the spreadsheet is instructed, and the actual calculation formula using the cell coordinates is converted in the system. And then run it.

[Industrial applications]

The present invention relates to a spreadsheet processing apparatus, and more particularly to a spreadsheet processing apparatus for facilitating a spreadsheet format table calculation process.

[Conventional technology]

2. Description of the Related Art Conventionally, spreadsheets have been used in personal computers for the purpose of efficiently performing spreadsheet calculations. Spreadsheets are, for example, 9.99 spread out on a single plane
This is for a single large table of rows x 127 columns.

A series of coordinates are allocated to the rows and columns, and the intersection of each row and each column can be a cell, where a character string or a numerical value can be input or displayed, and a calculation formula using another cell as an operand can be input. These cells are specified by coordinates.

A major feature of spreadsheets is that a calculation formula can be defined in an arbitrary cell.

FIG. 16 is an example of a spreadsheet. This table is for the purpose of automatically calculating the sales ratio of each store based on the painting data of Store A, Store B, Store C and Store D. For automatic calculation, the following formula must be entered in cells B3 to E3 and F2 (B3 to E3, F2 indicate the coordinates of the cells).

(B2 / F2) * 100 C3; (C2 / F2) * 100 D3; (D2 / F2) * 100 E3; (E2 / F2) * 100 F2; (B2 + C2 + D2 + E2) [Problems to be Solved by the Invention In the conventional spreadsheet processing method using a spreadsheet format table, cells are the basis of all processing, and especially in the case of calculation formulas, the coordinates of the cells to be operands are calculated for each cell to be input. And had to have a line.

Therefore, the larger the table becomes, the more complicated it becomes. Therefore, it is necessary to prepare a command or a function for eliminating such complexity. A typical example is the Replicate command. For example, in the example of FIG. 16, / R133, C3: E3
, An appropriate calculation formula is input to cells C3 to E3 at a time. Similar results can be obtained by inputting a function such as SUM (B2: E2) into cell F3.

However, the more complicated the user wants to solve, the more complicated commands and functions as in the above example must be prepared, and the operation method tends to be more difficult to understand.

The basic reason for this is that the functions of spreadsheet-type tables are all presented at the level of cells, which makes it difficult for beginners to understand the concept of spreadsheets. ing.

[Means for solving the problem]

The present invention focuses on the fact that the meaning of a table is understood through the item names on the table, and places the item names at the center of the concept of a table in a spreadsheet format. That is, the user can easily obtain the spreadsheet function of the spreadsheet format table by defining the provisional calculation formula using the item names appearing in the table after giving the table format. .

FIG. 1 shows the principle configuration of the present invention using an exemplary method.

In FIG. 1, reference numeral 10 denotes a data processing device having a function of calculating a spreadsheet format table.

Numeral 11 'denotes a table data designating section which receives a table-format instruction from the user.

Reference numeral 11 denotes a table data storage unit, which stores data relating to the format of one or more tables to be processed specified by the table data specification unit 11 ', for example, table structure data including table names, item names, cell coordinates, and the like. It is remembered.

Reference numeral 12 denotes a provisional calculation formula designating unit, which receives instructions of the provisional calculation formula from the user. The tentative formula is specified using the item names of one or more tables, or the table name and the item names.

Reference numeral 13 denotes a temporary calculation formula storage unit that stores the temporary calculation formula specified by the temporary calculation formula specification unit 12.

Reference numeral 14 denotes an actual calculation formula generation unit, which uses a specified provisional calculation formula and table data for a cell whose value needs to be calculated, using the cell coordinates of a related cell as an operand, or a function. Performs processing to generate an actual calculation expression as an argument of.

Reference numeral 15 denotes an actual calculation expression storage unit, which stores the generated actual expression in association with a table.

Reference numeral 16 denotes a spreadsheet, which calculates a value of a corresponding cell using an actual calculation formula and creates a table.

Reference numeral 17 denotes a data input device such as a keyboard, which is used as input means when a user specifies a table format or a provisional calculation formula.

Reference numeral 18 denotes a display device, which is used together with the data input device 17 in order for the data processing device to proceed with processing in a dialogue with the user.

Reference numeral 19 denotes a printer, which is used to output a processing result table.

[Action]

The operation of the present invention will be described with reference to FIG. FIG. 2 is an example of a calculation table for distributing the head office expenses to each branch.

First, the table format shown in FIG. 2 is specified by the table data specifying unit 11 ', and the sales are stored in the table data storage unit 11, and sales are generated for each item of entertainment expenses, travel expenses, utility expenses, and rent which are incurred at the head office. High,
To calculate the cost allocation according to the allocation rate according to the sales floor area and the number of employees, the user sets the provisional calculation formula [cost allocation]
= [Expense] * [allocation ratio], and is designated by the provisional calculation formula designation unit 12. As a result, the real calculation formula generation unit 14 automatically generates the following real calculation formula.

Here, the row coordinates of the table are represented by 1,2,3, ..., 33, and the column coordinates are represented by A, B, C, ..., I. Therefore, the cell coordinates of the table are, for example, C24, E26 Is given as

Next, the result of the cost distribution to each parameter (sales, sales floor area, number of personnel) is allocated to each store according to the parameter ratio. For this purpose, the provisional calculation formula may be designated by the provisional calculation formula designating unit 12 as [burden expense] = [cost allocation] * [ratio]. As a result, the real calculation formula generation unit 14 automatically generates the following real calculation formula.

Putting item names on the basis of the concept of a spreadsheet-type table in this way makes it even more compatible with the thinking style of those who intend to use a spreadsheet-type table.
This essentially improves usability.

〔Example〕

FIG. 3 shows a block configuration of one embodiment of the present invention.

In FIG. 3, reference numeral 20 denotes a data processing device, 21 denotes a dialogue control unit, 22 denotes a knowledge base relating to a table form and a tentative calculation formula, 23 denotes an inference engine unit that generates a real calculation formula, and 24 denotes a table calculation based on the real calculation formula. A conventional spreadsheet function unit 25 for processing is a user terminal.

In the dialogue control unit 21, the terminal 25 receives a table format or a temporary calculation formula defined by the user, and develops the information in the knowledge base 22 as structured data according to a predetermined format.

FIG. 4 (including FIGS. 4 (a) and 4 (b)) is an example of a tabular form corresponding to the table of FIG. 2, and FIG. 5 is a provisional calculation formula [burden cost] = An example of storing structured data on [cost allocation] * [ratio] will be described.

The data structures in FIGS. 4 and 5 are based on a frame expression, which is a technique known in the field of artificial intelligence.

When storing in table format, it is required to identify whether each item name controls a digit, a row, or their hierarchical relationship.

In order to distinguish between a digit and a row, for example, focus on the bottom right cell,
This can be achieved by advancing the auxiliary line one address at a time in the upward and left directions, and drawing a temporary identification line at the location where it hits the cell having the item name.

In addition, identification of the hierarchical relationship can be easily achieved by the conventional technology by recognizing the drawing state of the ruled line and the inclusion relationship.

In FIG. 4, names, row-items, etc. indicate slots of a frame, and name is a slot for storing a table name given to the table. In this example, the table name is undefined, but how to assign the table name is a matter of operation specification and does not relate to the present invention.

row-item is a slot for storing a group of row item names.
“lumn-item” is a slot for storing a group of digit item names.

The head-item is a slot for storing a group of item names that do not belong to any column or row at the top of the head.

The row-range is a slot storing row coordinates indicating a table range and a cell area of an input operation, and the column-range is a slot storing digit coordinates.

The item name group is stored in the form of a list in the form of (L item name pointer coordinate) or (p pointer item name). This is for identification.

In FIG. 5, ident is a slot for storing an indicator relating to an INSTANCE frame generated for each actual calculation formula generated as a specific example of the tentative calculation formula, and exp is a list of the format of the tentative calculation formula. The slot to store.

entry is a slot for storing an entry item name on the left side of the provisional calculation formula, and operand is a slot for storing an operand item name or a table name constituting the right side of the provisional calculation formula. The rule slot will be described later.

The inference engine unit 23 performs the following processing on the knowledge base, for example, FIG. 4 and FIG.

(1) The entry item name, that is, [burden] is extracted from the entry slot of the EXPRESSION frame (see FIG. 5).

(2) row-item, Column-item, head of TABLE frame
Search for -item slot and (L Burden cost * p1 * a
1) and (p * p2 Expense) are compared as patterns. * P1, * a1, etc. are variables.

In this case, (L cost nil A15) and (p006 cost) are collated, and ni1 is assigned to * p1, so [burden cost] is at the top of the hierarchical structure. It is recognized as a line item name because it is found in the −item slot.

(3) Check with the pattern (L * item1 * p2 * a2)
* Since p2 = 006, the list having the point 006, that is, (L entertainment expenses 006 B9), (L travel expenses 006 B12),
(L utilities 006 B15) and (L rent 006 B18) are extracted.

Next, by repeating the pattern (p * p3 * item 1) and matching, * enter1 is replaced with 002,003,004,005 because * item1 contains entertainment, travel, utilities, and rent. .

Next, by matching with the pattern (L * item2 * p3 * a3), (L sales 002 C8), (L area 002 C9), (L
Personnel 002 C10), (L sales 003 C11), (L area 003 C1)
2), (L personnel 003 C13) (L sales 004 C14), (L area 0
04 C15), (L personnel 004 C16), (L sales 005 C17),
(L area 005 C18) and (L personnel 005 C19) are obtained,
Matching with the next pattern (p * p4 * item2) does not succeed, and therefore it is recognized that these are the bottom layer.

(4) Lines 8 to 19 are extracted from (3) as lines in which the entry item name [burden expense] is dominant.

(5) Column-range slot list ((AH) (D
H)), apply the CDAR function to extract (DH),
The coordinates D8, D9 to H19 are obtained by the direct product operation of H｝ and {8 9 10 11 12 13 14 15 16 17 18 19}. Cells corresponding to these coordinates are entry cell candidates.

(6) An item name group consisting of all line item names and column item names that control the cell corresponding to the coordinates obtained in (5) is linked to the cell. FIG. 6 shows an example.

(7) Operand item names [cost allocation] and [ratio] are obtained from the operand slot of the EXPRESSION frame. Operand cell candidates are obtained by the same processing as in (2) to (6), and the item name group is determined for each cell. Link to
FIG. 7 and FIG. 8 are examples.

(8) Each of the entry candidates (D8 to H19) is selected as a real entry, the item names linked to the operand cell candidates are checked against the operand item names, and the matching is performed except for the entry item name and the operand item name. The only cell selected in the state where the number of item names to be executed is the largest is the real operand cell for the real entry cell.

Obtain the actual operand cells for all operand item names, and use the set of these actual entry cells and actual operand cells to refer to the exp slot of the EXPRESSION frame,
An actual calculation formula is generated as one specific example for the provisional calculation formula. If this actual calculation formula is passed to the conventional spreadsheet function part, the input of the actual calculation formula into the relevant cell can be achieved. Generate an INSTANCE frame corresponding to the generation of the actual calculation formula and place it in the knowledge base.

In FIG. 6, when D8 is selected as the actual entry cell, the item name group linked to this is excluded except for the entry item name. In order to select the actual operand of the operand item name [cost allocation], the data is compared with the data structured in the format shown in FIG. 7 excluding the operand item name. Seventh
In the example shown Has the most item names to be matched and is the only item selected, so coordinates
Let the cell corresponding to C28 be the actual operand cell.

As for the operand item name [ratio], the cell corresponding to the coordinate D5 is selected as the actual operand cell from the structured data shown in FIG. 8 by the same processing.

Here, a set of D8, C28, and D5 is obtained, and EXPRES shown in FIG. 5 is obtained.
With reference to the exp slot of the SION frame, D8; C28 * D5 is obtained as an actual calculation formula as one specific example for the provisional calculation formula.

FIG. 9 shows an INSTANCE frame corresponding to the actual calculation formula.

The above-described processing is executed by being guided by the rules of the if-then format stored in the knowledge base. The inference engine unit 23 interprets and executes the rules. This is a technique known as a production system. In the above example, the H digit is not valid as a real entry cell. As a means for excluding this, a method of embedding knowledge for excluding a specific item name in a rule or asking the operator to confirm when an entry cell candidate is obtained is possible.

The following describes techniques for supporting a wider range of tabular forms.

(1) Figure 16 shows the tentative formula [sales ratio] = [sales] /
When [Sum] is applied, the actual operand cell corresponding to the operand item name [Sum] is undefined in the above-described processing. When the entry item name and the operand item name belong to the row and the column, respectively, the uncertainty of the actual operand cell is normal. To cope with this situation, a new real operand cell selection rule may be provided.

The rule has the following form: That is, if (the actual operand cell is undefined) ∧ (the entry item name and the operand item name are divided into rows and digits) ∧ (there is only one operand item name that belongs to the same row or digit as the entry item name) then (the cell controlled by both the operand item name and the local operand item name is defined as an actual operand cell).

(2) Figure 10 is a simple salary table. In this table, the column item names are undefined. Such a table is called an incomplete table. On the other hand, as shown in FIGS. 16 and 2, a table in which all rows and columns are controlled by some item names is referred to as a full format table. If the table is in an incomplete format, the above processing cannot be performed. In order to cope with this, a row or a digit not controlled by the item name of the provisional calculation formula is selected and a provisional item name is given.

In the example of Fig. 10, the temporary calculation formula [payment amount] = [basic salary]-
In order to process the [tax], the above-described processing may be performed after adding an arbitrary temporary item name, for example, PSEUD 01 to PSEUD 05 to the coordinates B1 to A1.

(3) Figure 11 is a simple balance table. The temporary formula in this case is [balance] = [balance] + [income] − [expenditure], but the actual entry cell and the actual operand cell corresponding to [balance] on both sides must be shifted by one line. As a means for coping with such a situation, there is a method in which the operator indicates a prototype of the substitution example.

In the example of FIG. 11, for example, the cell at coordinate E3 corresponding to [balance] on the left side, the cell at coordinate E2 corresponding to [balance] on the right side, and the coordinate for [income] and [expense] The operator designates cells C3 and D3 with a pointing device such as a mouse.

Thereby, the rule slot of the EXPRESSION frame is modified, and in this example, the EXPRESSION frame of FIG. 12 is generated.

As a specific example of the tentative calculation formula, if the set of real operand cells forming the real calculation formula is obtained by the above-described processing, the coordinates of the real operand cells are corrected by referring to the rule slot again, and Get the operand cell.

For example, by the above-described processing, a set of real entry cells; real operand cells E4; E4, C4, and D4 are obtained as one specific example. Referring to (balance-1) in the rule slot, the operand E4 is set to 1 A line subtraction E4; a set of E3, C4, D4 can be obtained, and a correct actual calculation formula can be generated from this.

(4) FIGS. 13 and 14 show examples where a table search is required.

Fig. 13 shows a part of the salary calculation table, in which the salary deduction is obtained by referring to the separate table from the value of the amount deducted from company insurance (amount deducted from company insurance x ratio + fixed amount).

FIG. 14 is a table for listing the salaries of employees according to their equal salaries and proficiency levels by name with reference to the salary table.

In either case, a search function with the form of Look up (table pointer, digit number, search range) is conventionally used,
For example, for cell C6 in FIG. 13, 1 is a ratio, 2 is a fixed amount, and C4 * Look up (C4,1, A11: D17) + Look up (C4,2, A11,
D17), and for cell D3 in FIG. 14, Look up (B3,
C3, B12: F15).

In the present invention, the provisional calculation formula is calculated as follows: [Deduction of employment income] = [Amount after deduction of company insurance] * [Appendix: Ratio] +
[Appendix: fixed amount], and for [Fig. 14] [professional salary] =
This is entered in the form of [profession pay table], and the above-mentioned actual calculation formula including an appropriate search function can be generated.

A requirement of the present invention is that, for each item name corresponding to the table pointer and the column name corresponding to the digit number extracted from the table to be searched, the structure related to the format of the table (main table) to which the entry cell item name belongs. Searches the coded data and finds whether the item name is a line item or a column item on the main table, and if it is a line item, the intersection of the row dominated by the item name and the digit to which the actual entry cell belongs on the main table In the case of a digit item, the search function corresponding to the actual entry cell is generated using the coordinates of the intersection of the digit dominated by the item name and the row to which the actual entry cell belongs as an argument of a predetermined position of the search function.

In the following, the processing when the cell D3 in FIG. 14 is set as the real entry cell will be described.

The tentative calculation formula is [profession salary table] = [profession salary table].
The e slot, row-item, column-item, and head-item slots are searched, and a TABLE frame having a (profession pay table) in the name slot is found. Next, the table format of the TABLE frame is analyzed, and an item name serving as a search key is extracted.

For this analysis, basically, a template for the table format shown in Fig. 15 is assumed, and the existence of a hierarchical relationship between the item name groups, h
Whether or not extraction of the ead-item is necessary is included. In this example, [equal pay] corresponding to the table pointer and [proficiency] corresponding to the digit number are extracted. Next, a search is made for a TABLE claim relating to the main table, and it is found that [equal pay] is a digit item dominating the B digit, and [proficiency] is a digit item dominating the C digit. In this example, since the actual entry is D3, the intersection of 3 digits, B digit, and C digit, B3, C3
Is obtained as an argument placed at the position of the table pointer and column number. The search range is row-range, Co of the TABLE frame.
Obtained easily from the lumn-range slot.

〔The invention's effect〕

As described above, according to the present invention, when a calculation formula is set for a cell in a spreadsheet format table, instead of using a cell coordinate to specify each related cell as in the related art, an item name of the table is used. The input operation is easy for the user because of the provisional calculation formula specified using
The effect of reducing the load is greater as the size of the table is larger.

In addition, there is no need to check and enter many related cell loci, and search for structured data related to the format of the table to which the entry cell item name belongs, and find the actual entry cell dominated by the item name on the table. Since the selection is made automatically, the number of input operations is reduced, the error rate is reduced, and the reliability of the spreadsheet processing can be improved.

[Brief description of the drawings]

FIG. 1 is a diagram showing the basic configuration of the present invention, FIG. 2 is an explanatory diagram showing an example of a calculation table used to explain the operation of the present invention,
FIG. 3 is a block diagram showing an embodiment of the present invention, and FIG.
4 (b) is an explanatory diagram showing an example of a table format corresponding to the calculation table in FIG. 2, FIG. 5 is an explanatory diagram showing an example of structured data stored as a knowledge base, and FIG. FIGS. 7 and 8 show examples of links between entry cells and item names.
FIG. 9 is an explanatory diagram showing an example of a link between an operand cell and an item name. FIG.
Explanatory diagram showing an example of an ANCE frame, FIG. 10 is an explanatory diagram showing an example of a salary table, FIG. 11 is an explanatory diagram showing an example of a balance table, and FIG. 12 is an explanatory diagram showing an example of a generated EXPRESSION frame ,
Fig. 13 is an explanatory diagram showing an example of a salary calculation table, Fig. 14 is an explanatory diagram showing an example of a table for finding professional salary, Fig. 15 is an explanatory diagram showing an example of a tabular template, and Fig. 16 is an explanatory diagram. It is an explanatory view showing an example of a conventional spreadsheet. In FIG. 1, 10: data processing unit 12: provisional calculation formula specification unit 14: actual calculation formula generation unit 16: spreadsheet unit 17: data input device 18: display device

Claims (4)

(57) [Claims] 1. Each cell is arranged in a row direction and a column direction, and the position of each cell is represented by position coordinates based on the row position and the column position. In a spreadsheet processor that processes a table in which item names are set, a provisional calculation expression specification unit (for specifying an item name of one or more tables or a provisional expression defined using the item name and table name) 1
2) and for each cell in the row-wise or column-wise cell array dominated by the item name specified by the provisional formula on the table, the actual formula that uses the cell coordinates as operands or function arguments for each cell A real-formula generation unit (14) for generating a table, the real-formula being used to execute the calculation process of the table specified by the tentative formula; Defined using the above item names, the actual calculation formula generation unit (14) performs the following processing
A spreadsheet processing device. A process of searching the structured data related to the table format for an item name to be matched with the entry item name of the tentative formula, and extracting all rows or digits controlled by the item name. A process of linking, for each cell belonging to the row or column extracted in the process, an item name group consisting of all the row item names and column item names that control the cell to the cell. A process of searching for an item name to be matched with the item name of an individual operand item name in a provisional calculation expression from structured data related to a table format, and extracting all rows or digits controlled by the item name. A process of linking, for each cell belonging to the row or column extracted in the process, an item name group consisting of all the row item names and column item names that control the cell to the cell. The cell selected from the cells in the processing is regarded as a real entry cell, and the item name group linked to the real entry cell and the item name group linked to the cell in are compared for each operand item name, A process in which the only cell selected in the state where the number of item names to be collated is the largest except for the entry item name and the operand item name is set as the actual operand cell for the actual entry cell. A process of using a set of real entry cells and real entry cells extracted in the process to generate a real calculation formula as one specific example of a provisional calculation formula and inputting the formula to a position corresponding to a real entry cell on a spreadsheet. 2. The real calculation formula generation unit (14) converts a provisional calculation formula defined using an item name on a single or plural tables, including an incomplete form, into a valid cell on the table. 4. The method according to claim 2, wherein, when automatically generating and inputting an actual calculation formula using a valid cell as an operand, a provisional item name is assigned to a row or a column where the item name is undefined. Spreadsheet processing device as described. 3. A relative position relationship of an operand cell with respect to an entry cell is extracted by explicitly giving one or a plurality of pairs of an entry cell and an operand cell as a specific example of an item name of the provisional calculation formula. 4. The spreadsheet processing device according to claim 2, wherein the data is stored in a data area structured in a predetermined format, and the selection of the real operand cell is corrected accordingly. 4. The spreadsheet processing apparatus according to claim 2, wherein said real calculation formula generation section (14) includes the following processings (1) to (4). A process of searching for structured data related to the format of a table indicated by a table name constituting an operand of a provisional calculation formula, and extracting an item name serving as a search key. A process of searching structured data relating to the format of a main table to which an entry cell item name belongs, and extracting a row or a digit controlled by an item name to be matched with a key item name extracted in the process. A process of extracting, as an argument of a search function, the coordinates of the cell at the intersection of the row or digit extracted by the process on the main table and the digit or row to which the actual entry cell belongs. A process of generating a search function in which cell coordinates extracted corresponding to one or more key items are set at appropriate argument positions according to whether a key item is a row item or a digit item on the searched table.