JP2014153751A - Data extraction/edition/output device and program - Google Patents

Abstract

PROBLEM TO BE SOLVED: To provide a device capable of extracting data corresponding to extraction criteria input from a database which allows spreadsheet operation, editing the extracted data by adding an item name and outputting it to a display, and a program thereof.SOLUTION: A first database storing many pieces of data, extraction criteria for extracting pieces of data from the first database, a second database temporally to store extracted pieces of data and a VBA program 23 written in a macro language and the Visual Basic language are used. When extraction processing is selected, the VBA program 23 extracts pieces of data of the first database by using extraction criteria stored in the second database, outputs item names and the extracted pieces of data to a new third database to store them and outputs them to a display 7 to display them.

Description

  The present invention has a plurality of item names in the column direction, and uses a database capable of a table calculation in which data corresponding to the item names is stored in the row direction. The present invention relates to an apparatus and a program for generating a new database by extracting data of a part, adding an item name to the extracted data and editing the data, and outputting the new database to a display or a printer.

  As a database that stores data such as customer information and product sales status and is displayed in a spreadsheet that can be spreadsheeted, for example, Excel (registered trademark: Excel; hereinafter simply referred to as Excel) manufactured by Microsoft Corporation. Are generally used (Non-Patent Document 1).

  These databases use a spreadsheet that has a plurality of item names in the column direction and that can perform spreadsheets in which data corresponding to each item name is stored in the row direction. As a method of utilizing these databases, extraction / editing of a part of necessary data and output to a display or a printer are indispensable.

  As a means for extracting and outputting a part of necessary data from a spreadsheet such as Excel described above, a method using a filter function provided as a standard is generally used (Non-Patent Document 1).

  On the other hand, each step of the filter function is performed using a program (hereinafter referred to as a VBA program) described in a macro language and a visual basic language (Visual Basic for Application; hereinafter referred to simply as VBA) manufactured by Microsoft Corporation. There is also an attempt to automate this (Non-patent Document 2).

http://office.microsoft.com/ja-jp/excel http://msdn.microsoft.com/ja-jp/library/office/ee814737 (v = office.14) .aspx

  However, the method using the filter function provided as standard in Excel as described above can extract and output some necessary data, but it is necessary to perform many manual steps in succession. It is extremely cumbersome and requires work and patience.

  On the other hand, the method using the VBA program described above improves the workability because each step of the filter function can be automated. However, there is a problem that it is not possible to cope with cases where additions or deletions are made to some of the column-direction item names of spreadsheets in which spreadsheets such as customer information can be calculated.

  That is, in such a case, it is necessary to execute the VBA program after making appropriate corrections or changes. Therefore, the VBA program cannot be modified or changed unless it is a person who is familiar with the database, macro language, and VBA.

  In addition, with the above-mentioned method, it is possible to extract and output only within the range of spreadsheet item names that allow spreadsheet calculations such as customer information, and a new item name is added to the original spreadsheet item name However, there is a problem that it is not possible to create a new type of database that can be edited and perform editing work according to the purpose.

  The present invention has a plurality of item names in the column direction, and anyone can easily extract target data from a database capable of table calculation in which data corresponding to the item names is stored in the row direction. It is an object of the present invention to provide a device and a program that can perform editing, adding an item name to extracted data, editing it, and outputting it to a display or the like.

  The present invention aims to solve the above-mentioned problems.

The invention described in claim 1 is based on an extraction condition that is input using a database that has a plurality of item names in the column direction and that can perform spreadsheets in which data corresponding to the item names is stored in the row direction. This is a data extraction / editing / output device that extracts data that falls under and creates and outputs a new database,
The database is
Having at least a first database, a second database, and a third database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
The item name and the extracted data are output to the third database.

  By using the invention described in claim 1, data such as customer information and product sales status is stored, and in a database displayed in a spreadsheet that can be calculated in a spreadsheet, anyone can easily extract necessary data You can create a new database by automatically performing tasks such as output and output.

  In addition, even when some of the spreadsheet item names that can be spreadsheeted, such as customer information, are added or deleted, it is possible to cope with the need to modify the VBA program itself written in macro language and VBA. .

The invention described in claim 2 is based on an extraction condition that is input using a database that has a plurality of item names in the column direction and that can perform a table calculation in which data corresponding to the item names is stored in the row direction. This is a data extraction / editing / output device that extracts data that falls under and creates and outputs a new database,
The database is
Having at least a first database, a second database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
Using the extraction conditions, extract some data from the first database, temporarily stored in the second database,
The data temporarily stored in the second database is output to the fourth database.

  By using the invention described in claim 2, in addition to the effect described in claim 1, a new database is created by adding the item name of the spreadsheet data and stored in a hard disk or the like Can do. Therefore, it is possible to perform editing work according to the work purpose using a new format database.

The invention described in claim 3 is based on an extraction condition that is input using a database that has a plurality of item names in the column direction and that can perform spreadsheets in which data corresponding to the item names is stored in the row direction. This is a data extraction / editing / output device that extracts data that falls under and creates and outputs a new database,
The database is
Having at least a first database, a second database, a third database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
If the extraction process is selected,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
Outputting the item name and the extracted data to the third database;
If editing is selected,
Using the extraction conditions stored in the second database, extracting a part of the data in the first database, temporarily storing in the second database,
The temporarily stored data is output to the fourth database.

  By using the invention described in claim 3, it is possible to appropriately select and execute either the invention related to the extraction process of claim 1 or the invention related to the edit process of claim 2.

The invention described in claim 4 is the invention according to any one of claims 1 to 3, wherein the second database is:
An item name list in which the item name and a number corresponding to the item name are stored;
The item name, an extraction condition list in which the extraction condition is stored, and
The program is created from the extraction condition list and has a program list used by the program.

  By using the invention described in claim 4, each of the extraction conditions is stored in a list format. Therefore, it can be made clear at a glance whether there is an error in the extraction condition input from the outside.

The invention described in claim 5 is the invention described in claim 4,
The program is
It is described in the macro language and the visual basic language.

  Here, the macro language and the visual basic language are software built in Excel, so there is no need to purchase them separately. In addition, it naturally has a feature that it is compatible with Excel and can be used stably.

In the invention described in claim 6, the extraction condition inputted using a database having a plurality of item names in the column direction and capable of a spreadsheet in which data corresponding to the item names is stored in the row direction Is a program that extracts, edits, and outputs data that is created by extracting data corresponding to
The database is
Having at least a first database, a second database, a third database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
If the extraction process is selected,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
Outputting the item name and the extracted data to the third database;
If editing is selected,
Using the extraction conditions stored in the second database, extracting a part of the data in the first database, temporarily storing in the second database,
The temporarily stored data is output to the fourth database.

  By using the present invention, data such as customer information and product sales status is stored, and in a database that is displayed in a spreadsheet that can be spreadsheeted, anyone can easily extract, edit, and output the necessary data It is possible to automatically create a new database.

  In addition, a new database can be created by adding item names of spreadsheet data, etc., and saved on the hard disk, etc., so editing work can be performed according to the work purpose using a new format database. can do.

  In addition, by using the present invention, the VBA program itself written in the macro language and VBA itself even when some of the item names of the spreadsheet capable of spreadsheet calculation such as customer information are added or deleted It has an excellent effect in terms of operability and functionality that can be dealt with without correcting.

It is a block diagram of a data extraction / editing / output device. It is an extraction condition input screen (screen display described in step 30 in FIG. 3).

(1) Process selection (corresponding to step 40 in FIG. 3)
(2) Extraction key 1
(3) Extraction key 2
(4) Extraction key 3
(5) Extraction check box (6) Edit check box (7) Extraction key 1 extraction item name combo box (8) Extraction key 1 symbol selection area (9) Extraction key 1 condition input Area (10) Extraction key name combo box for extraction key 2 (11) Extraction key 2 symbol selection area (12) Extraction key 2 condition input area (13) Extraction key 3 extraction item name combo box ( 14) Extraction key 3 symbol selection area (15) Extraction key 3 condition input area
It is a flowchart of the main routine of a VBA program. It is a flowchart of a subroutine of a VBA program. It is an item name list in which NO (number) corresponding to the item name is stored. It is an extraction condition list in which extraction conditions are stored. This is a program list created from the extraction condition list and used by the VBA program. This is an area in which extracted data is temporarily stored when editing processing is selected. It is an example of a database 25 (first database) of customer information. It is a format figure of the customer database 31 for edit newly created (4th database). It is a figure which specifies the execution button of extraction and edit in the database 25 (1st database) of customer information (step 20). This is a newly created post-extraction customer database 29 (third database). It is the state figure where the result extracted from the database 25 (1st database) of customer information was temporarily memorize | stored in the customer database 27 for extraction processing (2nd database). This is the contents of the newly created editing customer database 31 (fourth database). It is an input screen of extraction conditions, and is a state diagram in which some item names and the like are input.

(A) Checked for processing of edit (6) in process selection (b) “Company NO” is displayed in extraction item name (7) of extraction key 1 (2), symbol (8)
“=” Has been entered for “1”, and “1” has been entered for condition (9).

(C) “Dispatch date” is the symbol (11) in the extraction item name (10) of the extraction key 2 (3).
In “> =”, “5/1” is the condition (12), and the symbol (11)
In “<=”, “5/31” has already been entered as the condition (12).

        (D) Nothing is entered in the extraction key 3 (4) and it is blank.

  In the following, using a database that has a plurality of item names in the column direction and the data corresponding to the item names is stored in the row direction, data corresponding to the input extraction condition A data extraction / editing / output apparatus and program for creating and outputting a new database will be described in detail.

As an example, a spreadsheet for managing customer information and the like created using Excel, that is, extraction and editing of some necessary data from the contents of the customer information database 25 (first database: FIG. 9) The output device and its program will be described in detail (FIGS. 1 to 15).
1. Hardware Overview FIG. 1 is a block diagram of a data extraction / editing / output apparatus according to the present invention. This apparatus is a general personal computer system mainly composed of an interface unit 1, a data processing unit 11, a program / data storage unit 21, and the like.

  The interface unit 1 includes an input device such as a keyboard 3 and a mouse 5, an output display device such as a display 7, and an output printing device such as a printer 9.

  The data processing unit 11 includes a CPU 13, a ROM 15, a RAM 17, and the like.

  The program / data storage unit 21 includes a macro language for extracting / editing / outputting data according to the present invention and a VBA program 23 (FIG. 3) written in VBA (Visual Basic Language), For example, a fourth type of database is stored in a hard disk or the like.

Macro language and VBA are software built into Excel, so there is no need to purchase them separately. In addition, it naturally has a feature that it is compatible with Excel and can be used stably. Details of the VBA program 23 will be described in detail later in the embodiments, and the first to fourth types of databases will be described in detail in the next section.
2. First to Fourth Four Types of Databases (1) The first database is a customer information database 25 in which, for example, customer information created by general-purpose software such as Excel is stored in a spreadsheet ( FIG. 9). That is, the first database is a database in which initial data before extraction is stored.

In the database 25 of customer information, a plurality of item names such as month and day (acceptance date), order number, company number, etc. are described in the column direction (right direction) from the fifth row of the spreadsheet. Then, after manually creating a plurality of data such as month and day (column A) by adding them to the bottom row in sequence or inserting them in the middle of the row, they are saved on a hard disk or the like. (FIG. 9).
(2) The second database constitutes a characteristic part of the present invention. That is, as will be described later, in the process of extraction from the first database and the process of editing, it is used to temporarily store information that assists in inputting extraction conditions and editing conditions, This is an extraction processing customer database 27 for temporarily storing functions used by a VBA program 23 to be described later, data extracted from the first database, and the like (FIGS. 5 to 8 and 13).

  That is, the second database is opened from the first database by opening the spreadsheet by clicking the first database (customer information database 25) stored in the Excel format with the mouse 5 or the like. This database is temporarily created and used by the VBA program 23 in the process of creating the database or the fourth database.

  FIG. 5 shows the contents (item name list) of the U column and V column of the second database, that is, the extraction processing customer database 27. Items such as the date (reception date) corresponding to the column direction (rightward direction) of the fifth row of the first database, for example, the customer information database 25 (FIG. 9), the order number, the company number, etc. The names are arranged in the row direction (downward direction) from the second row of the U column. Then, NO (numbers) of item names corresponding to the row direction (downward direction) of the U column are stored side by side in the row direction (downward direction) from the second row of the V column.

  When the item name in the fifth line of the customer information database 25 as shown in FIG. 9 is changed or added, it is changed or added so as to be linked with it by the VBA program 23. In this way, the U row and the V row shown in FIG. 5 are formed. By using the present invention, it is necessary to modify or change the VBA program 23 written in the macro language and VBA even when the item name of the first database is changed or added. It has an excellent effect in terms of operability that it can be handled without any problems.

  As will be described later, the item names described in the U column of the item name list (FIG. 5) are respectively extracted in the extraction key 1 (2), the extraction key 2 (3), and the extraction key 3 (4) in FIG. Extraction item names (7), (10), and (13) are selected and correspond to item names (U column) in order from column 15 to line 17 in the extraction condition list (FIG. 6). Stored using NO (column V).

  In the extraction condition list (FIG. 6), the contents described above and the contents selected by the extraction key 1 (2), the extraction key 2 (3) and the extraction key 3 (4) in FIG. It is an extraction condition list that can be changed or changed later. The extraction condition list is composed of column A and column B, and the portion of column B that is not input is left blank.

  In the extraction condition list (FIG. 6), the number corresponding to the item name selected by the extraction item name (7) of the extraction key 1 (2) is input to the 15th row of the B column, and the extraction key 2 (3) A number corresponding to the item name selected by the extracted item name (10) is input to the 16th row of the B column, and the number corresponding to the item name selected by the extracted item name (13) of the extraction key 3 (4). Is input to the 17th row of the B column.

  In addition, the extraction condition list (FIG. 6) is obtained from “8”, (11), and (14) of the extraction key 1 (2), the extraction key 2 (3), and the extraction key 3 (4) in FIG. Stores the fact that any of “=”, “> <”, “> =”, “<=” is selected, and the numerical values input in the conditions (9), (12), (15) Is memorized. The portions corresponding to (8) and (9) of the extraction key 1 (2) are stored in the third to sixth rows of the B column, and the extraction keys 2 (3) (11) and (12) are stored. The corresponding part is stored in the 7th to 10th lines of the B column, and the part corresponding to (14) and (15) of the extraction key 3 (4) is stored in the 11th to 14th lines of the B column. The

  For example, in the extraction condition list (FIG. 6), “4” is stored in the 15th row of column B as the extraction condition. Therefore, the extraction item name (7) of the extraction key 1 (2) is “company NO”. Is selected (corresponding to “4” in the fifth row of column V (NO) in the item name list (FIG. 5)). In addition, since “1” is stored in the third row of the B column of the extraction condition list (FIG. 6), the D column 16 to 32 rows of the customer information database 25 (first database: FIG. 9) are stored. The company “company NO = 1”, that is, the company “Kuroki” is extracted by the extraction key 1 (2).

  Further, for example, in the extraction condition list (FIG. 6), “10” is stored as the extraction condition in the 16th row of column B, and therefore, the extraction item name (10) of the extraction key 2 (3) is “dispatch date”. Is selected (10th row of column V (NO) in the item name list (FIG. 5)), and “5/1” is stored in the 8th row (> =) of column B. Since the date, “5/31” is stored in the ninth row (<=) of the B column, so that the date before May 31 is selected. That is, May 1 to May 31 are extracted by the extraction key 2 (3) as the dispatch date of the customer information database 25 (first database: FIG. 9).

  In addition, in the extraction condition list (FIG. 6), the 17th row and the 11th to 14th rows of column B are blank, so that nothing is input to the extraction key 3 (4). I understand. In the VBA program 23 according to the present invention, extraction processing is performed in the order of the extraction key 1 (2), the extraction key 2 (3), and the extraction key 3 (4), and each is in the AND format. Therefore, if any extraction condition is input to at least one, for example, the extraction key 1 (2), processing such as extraction from the first database is performed.

  That is, in the extraction condition list (FIG. 6), data sent to the company “Kuroki” from the extraction key 1 (2) during the period “5/1 to 5/31” from the extraction key 2 (3) is extracted. Has been shown to be.

  FIG. 7 is a program list formed from the extraction condition list (FIG. 6) after the input conditions are confirmed and an execution button shown in steps 60, 90, and 110 described later is pressed (FIG. 3). The program list (FIG. 7) is stored in the area from the E column to the P column, and the contents of the extraction condition list (FIG. 6) are converted into “functions” that can be used by the VBA program 23. It has been saved.

  The VBA program 23 searches for matching data from each item in the customer information database 25 (first database: FIG. 9) while referring to the “function” in the program list (FIG. 7), and performs extraction processing. I do. That is, the program list (FIG. 7) is sent to the company (company No. 1) “Kuroki” in the customer information database 25 (FIG. 9) as the extraction condition list (FIG. 6). Thereafter, it is shown that data dispatched before May 31 (5/1 to 5/31) is extracted by the VBA program 23.

  FIG. 8 shows areas after column AA in the extraction processing customer database 27 (second database) in which data extracted from the customer information database 25 (FIG. 9: first database) is temporarily stored. Show. When the item names in the customer information database 25 shown in FIG. 9 are increased, decreased, changed, etc., the extraction processing customer database 27 (second list) in which the data extracted by the VBA program 23 is stored. The area after the AA column in the database) is also increased or decreased or changed accordingly.

  In FIG. 13, as an example, from the customer information database 25 (FIG. 9), which is the first database, to the company (company NO1) “Kuroki”, the dispatch date is from May 1 to May 31 ( Data dispatched during the period of 5/1 to 5/31) are extracted, and the results temporarily stored after the AA column of the second database (extraction processing customer database 27) are shown. The contents of FIG. 13 are stored in the program / data storage unit 21 as a part of the fourth database (editing customer database 31) and output and displayed on the display 7 (FIG. 14).

That is, the second database (for example, the extraction processing customer database 27) is a database in which extraction conditions for extracting data from the first database and a part of the extracted data are temporarily stored. is there.
(3) The third database stores the extraction conditions from the customer information database 25, which is the first database, when “extraction” processing is selected in step 40 of FIG. 3 to be described later. This is a new database in which the results extracted using the second database (extraction processing customer database 27) are output and stored, for example, a database 29 of customer information after extraction (FIG. 12).

  The third database, which is the extraction processing customer database 27, has the same spreadsheet format as the customer information database 25, which is the first database, and the arrangement of item names. It is a new database in the extracted state.

  As shown in FIG. 12, for example, the result extracted from the above-described customer information database 25 (FIG. 9) under the extraction condition of May 1 to May 31 as the company name and Kuroki as the dispatch period. Then, it is stored in the program / data storage unit 21 as a database 29 of customer information after extraction.

In other words, the third database is a part of the first database, a part of the data is extracted using the extraction conditions stored in the second database (extraction processing customer database 27), and output and saved. This is a new database.
(4) When the “edit” process is selected in step 20 of FIG. 3 to be described later, the fourth database is the second database (for the extraction process) from the customer information database 25 which is the first database. This is a database in which the results extracted and temporarily stored using the extraction conditions stored in the customer database 27) and the newly added item names are stored, for example, the editing customer database 31 (see FIG. 14).

  The editing customer database 31 as the fourth database is a spreadsheet rather than the customer information database 25 (FIG. 9) as the first database and the extracted customer information database 29 as the third database. Can be output and saved in the program / data storage unit 21 in a state where the item names are increased. (FIG. 14).

  For example, in the editing customer database 31, the extracted result is stored in a format in which new item names such as working hours, hourly unit price, hourly wage, etc. (the fifth line in FIG. 14, the portion of columns J to O) are added. (FIG. 14). In addition, the part of J column-O column is an example, and it cannot be overemphasized that these items can be added or changed.

  As will be described later, when the “editing” process is selected in step 20 of FIG. 3, for example, an item in which a portion from column J to column O as shown in FIG. A storage area for data having a name is secured. The data after the AA column extracted from the customer information database 25 and temporarily stored in the extraction processing customer database 27 are stored in the A column to the I column portion and the P column and subsequent portions (see FIG. 13) For example, it is output and stored for each column (FIG. 14).

  Therefore, the use of the fourth database has a feature that it can be effectively used for salary calculation or the like when the working hours or hourly wage unit prices vary depending on the dispatch destination.

That is, the fourth database is a database in a new format that can be edited using the data extracted from the first database and the added item name.
(5) Summary of the first database to the fourth database As described above, the present invention is characterized by the VBA program 23 described later from the first database (for example, the customer information database 25). Using the second database (for example, the extraction processing customer database 27) constituting the portion, the third database (for example, the post-extraction customer database 29) or the fourth database (for example, the editing customer database 31) Either is output and displayed or saved.

  Then, as described later, when “extraction” processing is selected, it is extracted from the first database (customer information database 25) using the second database (extraction processing customer database 27). The result is output to the third database (extracted customer database 29) and stored in the program / data storage unit 21.

  On the other hand, as described later, when “edit” processing is selected, the data is extracted from the first database (customer information database 25) using the second database (extraction processing customer database 27). The result and the newly added item are output to a fourth database (editing customer database 31) and stored in the program / data storage unit 21.

  An embodiment using the VBA program 23 according to the present invention will be described in detail (FIGS. 3 and 4). The present invention has a plurality of item names in the column direction, a first database capable of table calculation in which data corresponding to the item names are stored in the row direction, a second database, a third database One of the database and the fourth database is created and output.

  Then, by inputting the extraction conditions from the outside, the input extraction conditions and the data extracted from the first database are stored in the second database as a program list used by the VBA program 23.

  Here, when the extraction process is selected by the input from the outside, the VBA program 23 uses the program list (FIG. 7) stored in the second database to store one of the first database. Part of data is extracted, and the extracted data is output to a new third database.

  On the other hand, when the editing process is selected by the input from the outside, the VBA program 23 uses the program list (FIG. 7) stored in the second database to create a part of the first database. Is extracted and temporarily stored in the second database, and the temporarily stored data is output to a new fourth database.

  The main routine of the VBA program 23 is composed of steps 10 to 150 (FIG. 3), and the subroutine is composed of steps 200 to 290 (FIG. 4).

  First, the first database described above, for example, the customer information database 25 in which customer information created by general-purpose software such as Excel is stored, is opened by double-clicking with the mouse 5 or the like. When the customer information database 25 is opened, an Excel spreadsheet is opened (FIG. 9), and the VBA program 23 according to the present invention is started (FIG. 3).

  It should be noted that item names such as “Monday”, “Order No.”, “Company Name”, and “Company No.” described in the fifth line of the customer information database 25 shown in FIG. 9 are added or deleted. If an item name is changed, the order of item names is changed, or new data is added to or deleted from the area after the sixth line, execute "Save" on the Excel screen (not shown) Then, after the data is stored in the hard disk, the termination process is once performed. Then, the customer information database 25 is opened again by double clicking with the mouse 5 or the like to start the VBA program 23 (FIG. 3).

As will be described later, even when the item name or data of the first database is changed, the VBA program 23 according to the present invention does not need to be corrected, and the following steps can be executed.
(1) Step 10
In step 10, an Excel spreadsheet screen of the first database, for example, the customer information database 25 as shown in FIG. Above the spreadsheet, execution buttons such as “extract / edit” and “extract release” executed by the VBA program 23 are displayed. The “Extraction Cancel” execution button is a button for returning to the initial screen of the customer information database 25 as shown in FIG. In the following, the “Extract / Edit” execution button, which is a feature of the present invention, will be described in detail.

  When the screen of FIG. 9 is displayed, the VBA program 23 searches the column A in the customer information database 25 from the top to the bottom in the order of the first row, the second row, and the third row. The item name, that is, the line number of the first found “Monday” is stored as a variable, and the item name in the column direction (the right direction of the fifth row) such as “Monday”, “Order No.”, “Company Name”, etc. In order.

  Here, since the VBA program 23 stores a line number such as “month and day” as a variable, for example, the line number such as “month and day” is changed from the fifth line to the sixth line, etc. The VBA program 23 does not need to be corrected or changed.

  Then, the contents of the item names are sequentially written in the second database, that is, the U column of the extraction processing customer database 27 from the second row (FIG. 5). That is, a plurality of item names such as “month / day”, “order received NO”, and “company name” stored in the column direction of the fifth row of the customer information database 25 (FIG. 9) are stored in the extraction processing customer database. In the 27th U column, the data is written in the lower row in the row direction (vertical direction) in the order of the second row, the third row, and the fourth row (FIG. 5).

  Next, the numbers (NO) corresponding to the item names written in the U column are incremented downward in the order of 1 in the 2nd row, 2 in the 3rd row, and 3 in the 4th row. Is written (FIG. 5). That is, the item name data and the item name data described in the fifth line of the customer information database 25 (first database), such as month: 1, order number: 2, company name: 3, A list of column U and column V (hereinafter referred to as “item name list”) with a corresponding NO (number) is stored in the extraction processing customer database 27 (second database) (see FIG. 5).

The contents of the item name list in FIG. 5 are extracted item names (7), (10), (10) of the extraction key 1 (2) to the extraction key 3 (4) in FIG. This is used for displaying the item name selected in 13) (steps 50, 80, 100).
(2) Step 20
In step 20, the process waits until the "extract / edit" button at the top of the screen of the spreadsheet of the first database, for example, the customer information database 25 is clicked with the mouse 5 or the like (FIG. 11). If this “extract / edit” button is clicked, the process goes to step 30.

Therefore, when the item names in the spreadsheet are added or deleted, the order of the item names is changed, or new data is added or deleted from the sixth line onwards, it is shown in the figure. Execute "Save" on the Excel screen that is not stored, store it on the hard disk, etc., and after finishing, double-click the customer information database 25 with the mouse 5 to open it and start the VBA program 23 If so, the state of step 20 is automatically set.
(3) Step 30
In step 30, a screen for inputting conditions to be extracted from the first database, for example, the customer information database 25, that is, an input screen for data extraction or editing is activated and displayed on the display 7 ( Figure 2). This input screen is created by the VBA program 23.

By starting this input screen, the contents of the list of item names in the extraction processing customer database 27 (second database) created in step 10 (FIG. 5) are the extracted item names (7), (10 ) And (14) can be used from the combo box. That is, in step 50, step 80, or step 100, the contents of the item names in FIG. 5 are clicked by clicking the black triangle mark part on the right side of the combo box of the extracted item names (7), (10), and (14). Is displayed, and any item name can be selected by clicking the mouse 5 or the like.
(4) Step 40
In step 40, a check mark is placed on either extraction (5) or editing (6) of the process selection (1) portion on the standby input screen of FIG.

Here, as described later, when extraction (5) is checked, the third database (extracted customer database 29 (FIG. 12)) is checked in step 140, and editing (6) is checked. In step 150, the fourth database (editing customer database 31 (FIG. 14)) is output and stored in the program / data storage unit 21 and output and displayed on the display 7.
(5) Step 50
In step 50, the target condition is input in the extraction item name (7), symbol (8), and condition (9) portions of the extraction key 1 on the standby input screen of FIG.

  Click the black triangle mark on the right side of the combo box for the extracted item name (7) to display the U column part of the item name list (Fig. 5), and select one of them. Do. For example, in the embodiment shown in FIG. 15, “Company NO” is selected as the extraction item name (7).

  When “company NO” is input as the extraction item name (7), the input content is stored in the database 27 of customer information for extraction processing, which is the second database. That is, from the item name list (FIG. 5), “company NO” in the U column corresponds to 4 in the V column, and a value of 4 is stored in the 15th row of the B column in the extraction condition list (FIG. 6). The

  Next, the symbol (8) such as “=”, “> <”, “> =”, “<=”, etc. is selected. For example, “=” is used as a symbol representing “equal” or “equivalent”, “> <” is used as a symbol representing “ambiguous” or “part”, and “greater than” or “after” is represented as “ > = ”And“ <= ”is used in advance as a symbol representing“ below ”or“ previous ”.

  Then, according to the extracted item name (7), if you select any of the symbols (8) such as “=”, “> <”, “> =”, “<=”, the condition (9) A text box for inputting a condition value to be extracted is displayed (FIGS. 2 and 15).

For example, if “company NO” is input as the extracted item name (7), “=” is selected as the symbol (8), and “1” is selected as the condition (9), the company name “Kurogi” is selected. (FIG. 9, D column 16th row etc.). In this case, “1”, which is the company name “Kurogi”, is stored in the third column of the extraction condition list (FIG. 6).
(6) Step 60
In step 60, it is determined whether or not the execution (16) button in FIG. If the execute (16) button is clicked, go to step SUB1 of step 120, and if the execute (16) button is not clicked, go to step 80.
(7) Step 80
In step 80, the extraction item name (10), symbol (11), and condition (12) of the extraction key 2 (3) are input. The extraction key 1 (2) and the extraction key 2 (3) have an AND configuration, and the extraction key 2 (3) enables further narrowing down. Note that the input method of the extraction key 2 (3) is the same as that of the extraction key 1 (2), and therefore the details are omitted.

  The content selected by the extraction item name (10) of the extraction key 2 (3) is stored in the database 27 of customer information for extraction processing, which is the second database. That is, the content of the extracted item name (10) is stored in the 16th row of the B column in the extraction condition list (FIG. 6).

  Next, click one of the symbols (11) to select it, and then enter the condition (12). The values of these input extraction conditions are represented by the symbols (“=”, “>>” in the column A between the 7th to 10th columns of the B column of the extraction condition list (FIG. 6) as the second database. ”,“> = ”,“ <= ”, Etc.).

  For example, as shown in FIG. 15, “dispatch date” is selected as the extraction item name (10) of the extraction key 2 (3), “> =” is selected as the symbol (11), and “5” is set as the condition (12). / 1 ”,“ <= ”is selected as the symbol (11), and“ 5/31 ”is input to the condition (12). That is, in addition to the extraction condition by the extraction key 1 (2) (AND), the extraction key 2 (3) is narrowed down to “dispatch date” “after May 1” and “before May 31”. be able to.

In this case, “10” corresponds to the dispatch date from the item name list (FIG. 5), and the number “10” is stored in the B column 16th line of the extraction condition list (FIG. 6). Also, “5/1” in column B, 8th row corresponding to “numerical value input =” in column A, 8th row of the extraction condition list (FIG. 6), and “numerical value input =” in column A, 9th row 5/31 is stored in the 9th row of the B column corresponding to.
(8) Step 90
In step 90, it is determined whether or not the execution (16) button in FIG. If the execute (16) button is clicked, go to SUB1 in step 120, and if the execute (16) button is not clicked, go to step 100.
(9) Step 100
In step 100, the extracted item name (13), symbol (14), and condition (15) of the extraction key 3 (4) are input. The extraction key 3 (4) has an AND relationship with the extraction key 1 (2) and the extraction key 2 (3), and enables further narrowing down. Note that the input method of the extraction key 3 (4) is the same as that of the extraction key 1 (2) or the extraction key 2 (3), and the details thereof are omitted.

  The content selected by the extraction item name (13) of the extraction key 3 (4) is stored in the database 27 of customer information for extraction processing, which is the second database. That is, the content of the extracted item name (13) is stored in the 17th row of the B column of the extraction condition list (FIG. 6). The other items are the same as those in step 60 and step 80, and are therefore omitted.

FIG. 15 shows a state in which no input is made to any part of the extraction item name (13), the symbol (14), and the condition (15) of the extraction key 3 (4). Therefore, the B column 17th row and the B column 11th to 14th rows in the extraction condition list (FIG. 6) are all blank.
(10) Step 110
In step 110, it is determined whether or not the execute (16) button in FIG. 2 has been clicked with the mouse 5 or the like. If the execute (16) button is clicked, the process goes to step SUB1 in step 120.

  Note that the extraction item names such as the extraction key 1 (2), the extraction key 2 (3), and the extraction key 3 (4) are clicked until the execution (16) button in FIG. 2 is clicked by the mouse 5 or the like. (7), (10), (13), symbols (8), (11), (14), conditions (9), (12), (15), etc. can be modified or added (Figure 6). On the other hand, when the extraction operation is to be stopped, the “x” button in the upper right of FIG. 2 is pressed.

  As described above, the extraction processing customer information database 27 is extracted by inputting the extraction key 1 (2) at step 60, the extraction key 2 (3) at step 80, and the extraction key 3 (4) at step 100, respectively. The condition list (FIG. 6) is completed.

That is, each extraction condition is stored in a list format (FIGS. 5 and 6). Whether or not there is an error in the extraction conditions input from the outside by referring to the columns A, B, U, and V of the database 27 of the customer information for extraction processing, which is the second database Can be clearly seen (FIGS. 5 and 6).
(11) Step 120
In step 120, the process jumps to step 200 of SUB1. When the execution (16) button in FIG. 2 is pressed, processing selection (1) of either “extraction (5)” or “edit (6)”, extraction key 1 (2), extraction key is selected. The input contents such as extraction item names (7), (10), (13) by 2 (3) and extraction key 3 (4) are confirmed.
(12) Step 200 (subroutine)
In step 200, an extracted item name is created. That is, the contents of column B 15 to 17 of the extraction item name (FIG. 6) among the extraction conditions inputted from the outside in step 50, step 80 and step 100 are the database 27 (first number of extraction processing customer information). The second database) is stored as a program list in the E column 1st row to the P column 1st row (FIG. 7).

  That is, the extracted item name “company NO” corresponding to the number “4” in the 15th row of the extracted item name (FIG. 6) B is the E column 1st row to the H column 1st row in the program list (FIG. 7). The extracted item name “dispatched date” corresponding to the number “10” in the 16th row of the extracted item name (FIG. 6) B column is input to the eye and stored in the extracted item name (FIG. 6). It is inputted and stored in the first to Lth rows. Since the 17th row of the extracted item name (FIG. 6) B column is blank, the M column 1st row to the P column 1st row of the program list (FIG. 7) are also blank.

In this way, by providing a list of extracted item names (FIG. 6), the extracted item names (7), (10), (13), etc., are clicked until the execution (16) button in FIG. 2 is clicked. Extraction conditions input from outside can be freely added or changed.
(13) Step 210 (subroutine)
In step 200, an extraction condition formula is created. That is, the symbols (8), (11), (14) and the conditions (9), (12), (15) among the extraction conditions input from the outside in Step 50, Step 80, and Step 100 are the extraction process. Is stored as a program list (FIG. 7) in the E column 2nd row to the P column 2nd row of the customer information database 27 (second database).

That is, in the item name list (FIG. 6), the contents of the 3rd to 6th lines by the extraction key 1 (2) are changed to the 2nd line E column to H column of the program list (FIG. 7). The contents of lines 7 to 10 according to (3) are the contents of columns I to L of the second line of the program list (FIG. 7), and contents of lines 11 to 14 with the extraction key 3 (4) ( Are stored in the M-th to P-th columns (blank) in the second row of the program list (FIG. 7).
(14) Step 220 (subroutine)
In step 220, a function that can be used by the VBA program 23 is created from the contents stored in columns E to P of the program list (FIG. 7). Then, the VBA program 23 acquires the values of the E column 2nd row (extraction key 1), the J column 2nd row and the K column 2nd row (extraction key 2) of the program list (FIG. 7). Use the value as a specific function.
(15) Step 230 (subroutine)
In step 230, it is determined whether extraction is checked or editing is checked in step 40. If extraction is checked, go to step 240; if edit is checked, go to step 250.
(16) Step 240 (subroutine)
In step 240, the VBA program 23 creates the extracted customer database 29 using the function obtained in step 220 described above (FIG. 12), and outputs and stores it in the program / data storage unit 21. Thereafter, in step 290, the process returns to the main routine.
(17) Step 250 (subroutine)
In step 250, the VBA program 23 secures an area (area) in which the editing data extracted from the customer information database 25 is temporarily stored after the AA column of the extraction processing customer information database 27. (FIG. 8).
(18) Step 260 (subroutine)
In step 260, the VBA program 23 creates an area (edit data storage area) in which data for editing in the editing customer database 31 is stored (FIG. 10). As will be described later (FIG. 14), a plurality of new item names can be inserted between the J column to the O column of the item names in the customer information database 25 (FIG. 10).
(19) Step 270 (subroutine)
In step 270, the editing data extracted from the customer information database 25 is temporarily stored by the VBA program 23 after the AA column of the extraction processing customer information database 27 created in step 250 ( FIG. 13).
(20) Step 280 (subroutine)
In step 280, the VBA program 23 writes the editing data stored after the column AA of the extraction processing customer information database 27 (FIG. 13) into the editing data storage area (FIG. 10) and edits the customer. A database 31 is created (FIG. 14) and output and saved in the program / data storage unit 21.

The data for each column temporarily stored after the AA column of the database 27 for extraction processing customer information is output and stored in the editing customer database 31. Therefore, as shown in FIG. 14, processing such as addition of a plurality of new item names and insertion of calculation formulas can be performed between column J to column O of the item names in the customer information database 25. Thereafter, in step 290, the process returns to the main routine.
(21) Step 130
In step 130, it is determined whether the “extraction” process is selected in step 20 or the “edit” process is selected. If the “extract” process is selected, the process goes to step 140, and if the “edit” process is selected, the process goes to step 150.
(19) Step 140
In step 140, the post-extraction customer database 29 stored in the program / data storage unit 21 is output and displayed on the display 7 (FIG. 12). If the “extraction” process is selected in step 40, the extraction condition stored in the second database (extraction processing customer database 27) is used from the first database (customer information database 25). The extracted and stored data is output and displayed as a third database (extracted customer database 29) (step 240).

That is, the third database is a new database composed of a part of data of the first database.
(20) Step 150
In step 150, the edited customer database 31 stored in the program / data storage unit 21 is output and displayed on the display 7 (FIG. 14).

  That is, when the “edit” process is selected in step 40, the process is temporarily stored from the first database (customer information database 25) into the second database (extraction process customer database 27). It is output and displayed as a fourth database (customer database 31 for editing) having data and a newly added item name (step 280). Here, a numerical value or a calculation formula can be stored and used in the lower part of the newly added item name.

  Therefore, as shown in FIG. 14, when the editing customer database 31 (fourth database) is used, it can be used for calculation in the case where the working hours and the hourly rate change depending on the dispatch destination. Have.

  In other words, the fourth database is a new database that can be edited and is composed of data extracted from the first database and added item names.

  INDUSTRIAL APPLICABILITY The present invention can be used for an apparatus or a program for extracting data corresponding to an extraction condition input from the outside using a database capable of spreadsheet, and creating a new database.

DESCRIPTION OF SYMBOLS 1 Interface part 3 Keyboard 5 Mouse 7 Display 9 Printer 11 Processing part 13 CPU
15 ROM
17 RAM
21 Program / Data Storage Unit 23 VBA Program 25 Customer Information Database 27 Extraction Processing Customer Information Database 29 Extracted Customer Information Database 31 Editing Customer Information Database

Claims (6)

Using a database that has a plurality of item names in the column direction and the data corresponding to the item names is stored in the row direction and that can be calculated, the data corresponding to the input extraction conditions is extracted and newly A data extraction / editing / output device for creating and outputting a simple database,
The database is
Having at least a first database, a second database, and a third database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
An apparatus for extracting / editing / outputting data, wherein the item name and the extracted data are output to the third database. Using a database that has a plurality of item names in the column direction and the data corresponding to the item names is stored in the row direction and that can be calculated, the data corresponding to the input extraction conditions is extracted and newly A data extraction / editing / output device for creating and outputting a simple database,
The database is
Having at least a first database, a second database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
Using the extraction conditions, extract some data from the first database, temporarily stored in the second database,
An apparatus for extracting, editing, and outputting data, wherein the data temporarily stored in the second database is output to the fourth database. Using a database that has a plurality of item names in the column direction and the data corresponding to the item names is stored in the row direction and that can be calculated, the data corresponding to the input extraction conditions is extracted and newly A data extraction / editing / output device for creating and outputting a simple database,
The database is
Having at least a first database, a second database, a third database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
If the extraction process is selected,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
Outputting the item name and the extracted data to the third database;
If editing is selected,
Using the extraction conditions stored in the second database, extracting a part of the data in the first database, temporarily storing in the second database,
An apparatus for extracting, editing, and outputting data, wherein the temporarily stored data is output to the fourth database. The second database is
An item name list in which the item name and a number corresponding to the item name are stored;
The item name, an extraction condition list in which the extraction condition is stored, and
4. The data extraction / editing / output device according to claim 1, wherein the data extraction / editing / output device is created from the extraction condition list and has a program list used by the program. The program is
5. The data extraction / editing / output device according to claim 4, which is described in a macro language and a visual basic language. Using a database that has a plurality of item names in the column direction and the data corresponding to the item names is stored in the row direction and that can be calculated, the data corresponding to the input extraction conditions is extracted and newly A program that creates, edits, and outputs data that is created and output
The database is
Having at least a first database, a second database, a third database, and a fourth database;
The first database is a database in which the data before being extracted is stored,
The second database is a database in which the extraction conditions for extracting the data from the first database and a part of the extracted data are temporarily stored,
The third database is a new database in which the data extracted from the first database is stored,
The item names of the first database and the third database are of the same format,
The fourth database is a new database in which the data extracted from the first database is stored and editable.
The item name of the fourth database is added more than the item name of the first database,
The extraction conditions are:
At least a part of the item name of the first database and a part of the data are determined and stored and used in the second database,
If the extraction process is selected,
Using the extraction conditions stored in the second database, extracting a portion of the data of the first database;
Outputting the item name and the extracted data to the third database;
If editing is selected,
Using the extraction conditions stored in the second database, extracting a part of the data in the first database, temporarily storing in the second database,
A program for extracting, editing, and outputting data, characterized in that the temporarily stored data is output to the fourth database.

Images