CN113626422A - A method of coke data management based on VBA technology and data pivot table - Google Patents
A method of coke data management based on VBA technology and data pivot table Download PDFInfo
- Publication number
- CN113626422A CN113626422A CN202110949905.3A CN202110949905A CN113626422A CN 113626422 A CN113626422 A CN 113626422A CN 202110949905 A CN202110949905 A CN 202110949905A CN 113626422 A CN113626422 A CN 113626422A
- Authority
- CN
- China
- Prior art keywords
- data
- coke
- quasi
- vba
- strength
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
- G06F16/215—Improving data quality; Data cleansing, e.g. de-duplication, removing invalid entries or correcting typographical errors
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2453—Query optimisation
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Quality & Reliability (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a coke data management method based on a VBA technology and a data pivot table, which comprises the following steps: deriving quasi-primary focus data and quasi-secondary focus data from a database according to a preset time interval; respectively copying the data obtained by inquiring the quasi-primary coke data and the quasi-secondary coke data according to the quality indexes into two workbooks, namely a coke quality change trend summary table and an outsourcing coke data perspective table; setting a pivot table format, unifying dates and supplier names, simultaneously calculating the qualification rate, and updating a coke quality change trend summary table and a data pivot table once every time the quasi primary coke data and the quasi secondary coke data are updated; integrating the VBA statements in the steps into a window interface, and automatically updating the data obtained by query to a coke quality change trend summary table and an outsourcing coke data perspective table by clicking a command button. The data management method improves the working efficiency and simultaneously ensures the data accuracy.
Description
Technical Field
The application relates to a coke data management method based on a VBA technology and a data pivot table.
Background
At present, one of the raw materials required by blast furnace production is generally produced by a coke oven, the production practice can be met only by supplementing the purchased coke due to the limited daily yield of the self-produced coke, the purchased coke suppliers can reach dozens of families, the quality fluctuation is large, the quality fluctuation can influence the fluctuation of the blast furnace condition, in order to ensure the stable iron-making production, the purchased coke quality index needs to be monitored and managed in multiple directions, and the parameters of the purchased coke quality index are as follows every week: and (4) calculating the average value and the qualified rate of the strength, the coke powder, the moisture and the like after the reaction. Currently, most ledgers for outsourcing coke data management rely on related administrators, 9 items of required data such as reactivity (%), post-reaction strength (%), coke powder content (%), total moisture (%), crushing strength M25 (%), abrasion resistance M10 (%), ash content (Ad%), volatile component (Vdaf%), total sulfur (St, d%) and the like are manually recorded, designated time periods are selected through the average formula in an Excel table, and each index average value and qualification rate are calculated.
Due to the fact that data formats exported from a quality network are inconsistent, data are identified and checked manually, errors are prone to occurring, data cannot be copied and pasted simply, indexes and corresponding parameters need to be checked repeatedly, operation amount is large, the period is frequent, time and labor are consumed, and an effective data management method is difficult to form.
Disclosure of Invention
The invention aims to provide a coke data management method based on a VBA technology and a pivot table, so as to overcome the defects in the prior art.
In order to achieve the purpose, the invention provides the following technical scheme: a coke data management method based on VBA technology and a data pivot table comprises the following steps:
step 1: the method comprises the steps of deriving quasi-primary coke data and quasi-secondary coke data from a database according to a preset time interval, setting a search object and a range by utilizing an Excel self-contained HLookup function, automatically inquiring the reactivity, post-reaction strength, coke powder content, total moisture, crushing strength M25, wear resistance strength M10, ash content, volatile matter and total sulfur 9 indexes of the quasi-primary coke data and the quasi-secondary coke data in a search mode, and programming by utilizing VBA statements;
step 2: respectively copying data obtained by inquiring the quasi-primary focus data and the quasi-secondary focus data into two workbooks, namely a coke quality change trend summary table and an outsourcing coke data perspective table, and compiling a program by using VBA statements;
and step 3: setting a pivot table, unifying dates and names of suppliers, simultaneously calculating the qualification rate, updating a coke quality change trend summary table and a data pivot table once every time when the quasi first-level coke data and the quasi second-level coke data are updated, and compiling a program by using VBA statements;
and 4, step 4: integrating the VBA statements in the steps into a window interface, and automatically updating the query data to a coke quality change trend summary table and an outsourcing coke data perspective table by clicking a command button.
Preferably, in the step 2, a summary table is added to the derived quasi-first-stage focal data and quasi-second-stage focal data by using worksheets.
In the step 2, the EXCEL function is integrated into the VBA code by utilizing application.
Preferably, in the step 3,
the yield of strength after the quasi-first coke reaction was IF (OR (AND (supplier name < > "FG", strength after reaction > -60), AND (supplier name ═ FG ", strength after reaction > -58)), 100,0)
The yield of strength after the secondary coke reaction was IF (OR (AND (supplier name < > "FG", strength after reaction > -55), AND (supplier name ═ FG ", strength after reaction > -50)), 100,0),
coke powder qualification rate IF (coke powder content is less than 8,100,0)
Moisture yield IF (total moisture < 6,100,0)
After the corresponding index qualification rate data item is input into the above formula, the VBA code automatically fills the calculation formula using a selection.
Preferably, in step 3, the setting of the pivot table and unifying the date and the name of the supplier includes: the supplier name is standardized by using a like statement and if-ElseIf-Then statements, and VBA codes are as follows:
if supplier name Like
Supplier name "ABC"
ElseIf supplier name Like DE "the n
Supplier name "DE"
............
Else
Supplier name ═ supplier name
End If
Next。
Preferably, the step 4 further comprises inserting a data perspective, and the slicer is configured to be automatically updated according to the data perspective after the slicer is updated according to the trend of the relevant indexes viewed by the specified supplier at a specific time.
Compared with the prior art, the coke data management method based on the VBA technology and the pivot table at least has the following beneficial effects:
(1) indexes required by the primary focus data and the quasi-secondary focus data are automatically matched and registered by using the Hlookup function, manual matching is not needed, and errors in data checking are avoided;
(2) the VBA technology is utilized to complete the query function of data exported every time by using double nested loops, and the data are dynamically exported to a quality change trend summary table and a data perspective table;
(3) integrating the VBA codes into a window interface, and finishing data analysis work by one key, so that the work efficiency is improved, and the data accuracy is ensured;
(4) and updating the statistical data in real time by utilizing a data pivot table refreshing function.
Drawings
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly described below, it is obvious that the drawings in the following description are only some embodiments described in the present application, and other drawings can be obtained by those skilled in the art without creative efforts.
FIG. 1 is a flowchart illustrating a coke data management method based on VBA technology and a pivot table according to an embodiment of the present invention;
FIG. 2 is a VBA window integration interface for a coke data management method based on VBA technology and a pivot table in accordance with an embodiment of the present invention;
FIG. 3 is a pivot table of a coke data management method based on VBA technology and the pivot table in accordance with an embodiment of the present invention.
Detailed Description
The technical solutions in the embodiments of the present invention will be described in detail below with reference to the drawings in the embodiments of the present invention, and it is obvious that the described embodiments are only a part of the embodiments of the present invention, and not all embodiments. All other embodiments, which can be obtained by a person skilled in the art without any inventive step based on the embodiments of the present invention, are within the scope of the present invention.
With reference to fig. 1, the present invention adopts the following technical scheme, specifically including the following steps:
step A1:
(1) and exporting original detection data of the purchased coke, including the primary coke and the secondary coke, from a quality network into a workbook containing two worksheets according to a preset time interval.
(2) The data format of the two worksheets is not uniform after derivation, the physical addresses of all indexes (16 items in total) are different, 9 indexes such as reactivity (%), post-reaction strength (%), coke breeze content (%), total moisture (%), crushing strength M25 (%), abrasion resistance M10 (%), ash content (Ad%), volatile content (Vdaf%), total sulfur (St, d%) and the like are obtained by simply searching for a target value and copying and pasting, the data processing capacity is large, errors are easily caused by manual resolution, a search object is set by using an HLookup function carried by Excel, the range is wide, the search mode is automatically inquired, and the error rate of 0 can be achieved.
(3) And programming by using VBA statements, and designing a summary form format and a sorting rule of the required data.
Step A2:
(4) add summary table in the exported workbook by using Worksheets.add, and set the header of the newly added table, including the required 9 indexes.
(5) HLookup integrates the EXCEL function into the VBA code, and the query function of exporting the required 9 data indexes by using the quasi-first-level coke and second-level coke worksheets is completed by using double nested loops.
(6) And respectively copying the data obtained by query into two workbooks of a coke quality change trend summary table and an outsourcing coke data perspective table. In order to conveniently look up original data, the coke quality change trend is summarized without processing, and the query data is copied according to the type original state of the if statement judgment material name attribute (quasi-first-level coke and second-level coke); in order to facilitate data processing and calculation of yield, the query data in different categories (quasi-first focus and second focus) are copied to an outsourcing focus data perspective table for data perspective.
Step A3:
(7) unifying formats of pivot table date, supplier name, etc. And (3) changing the date format: the original "time of year, month, day" is changed to "yyyy/m/d" format using a NumberFormat statement.
The supplier name is standardized by using a like statement and if-ElseIf-Then statements, and VBA codes are as follows: if supplier name Like
Supplier name "ABC"
ElseIf supplier name Like DE "the n
Supplier name "DE"
............
Else
Supplier name ═ supplier name
End If
Next
(8) The yield was calculated, AND the strength yield after the quasi-first-order coke reaction was IF (OR (AND (supplier name < > "FG", strength after reaction > 60), AND (supplier name ═ FG ", strength after reaction > -58)), 100,0) the strength yield after the second-order coke reaction was IF (OR (AND (supplier name < >" FG ", strength after reaction > -55), AND (supplier name ═ FG", strength after reaction > -50)), 100,0)
Coke powder qualification rate IF (coke powder content is less than 8,100,0)
Moisture yield IF (total moisture < 6,100,0)
After the corresponding index qualification rate data item is input into the above formula, the VBA code automatically fills the calculation formula using a selection. The pivot table is refreshed each time the A1-A3 steps are updated more than once. Step A4:
(9) all VBA codes of the steps A1-A3 are integrated into a window interface, and as shown in figure 2, the query data can be automatically updated to two workbooks, namely a coke quality change trend summary table and an outsourcing coke data perspective table by clicking a command button respectively.
(10) Classifying the outsourcing coke data perspective table according to the quasi-first-stage coke and the second-stage coke as shown in fig. 3, respectively establishing a calculation table and a data perspective table of the outsourcing coke data perspective table and the second-stage coke, and displaying field average values, qualification rates, maximum and minimum values and the like according to the month and the year. And the latest summary statistics can be obtained by clicking the pivot table tool-refreshing only by updating the calculation table each time. And inserting a data perspective, and setting a slicer to be capable of viewing related index trends according to a specified supplier at a specific time and automatically updating along with the update of the data perspective.
It is noted that, herein, relational terms such as first and second, and the like may be used solely to distinguish one entity or action from another entity or action without necessarily requiring or implying any actual such relationship or order between such entities or actions. Also, the terms "comprises," "comprising," or any other variation thereof, are intended to cover a non-exclusive inclusion, such that a process, method, article, or apparatus that comprises a list of elements does not include only those elements but may include other elements not expressly listed or inherent to such process, method, article, or apparatus. Without further limitation, an element defined by the phrase "comprising an … …" does not exclude the presence of other identical elements in a process, method, article, or apparatus that comprises the element.
The foregoing is merely a detailed description of the present application, and it should be noted that modifications and embellishments could be made by those skilled in the art without departing from the principle of the present application, and these should also be considered as the protection scope of the present application.
Claims (6)
1. A coke data management method based on VBA technology and a data pivot table is characterized in that: the method comprises the following steps:
step 1: the method comprises the steps of deriving quasi-primary coke data and quasi-secondary coke data from a database according to a preset time interval, setting a search object and a range by utilizing an Excel self-contained HLookup function, automatically inquiring the reactivity, post-reaction strength, coke powder content, total moisture, crushing strength M25, wear resistance strength M10, ash content, volatile matter and total sulfur 9 indexes of the quasi-primary coke data and the quasi-secondary coke data in a search mode, and programming by utilizing VBA statements;
step 2: respectively copying data obtained by inquiring the quasi-primary focus data and the quasi-secondary focus data into two workbooks, namely a coke quality change trend summary table and an outsourcing coke data perspective table, and compiling a program by using VBA statements;
and step 3: setting a pivot table, unifying dates and names of suppliers, simultaneously calculating the qualification rate, updating a coke quality change trend summary table and a data pivot table once every time when the quasi first-level coke data and the quasi second-level coke data are updated, and compiling a program by using VBA statements;
and 4, step 4: integrating the VBA statements in the steps into a window interface, and automatically updating the query data to a coke quality change trend summary table and an outsourcing coke data perspective table by clicking a command button.
2. The method of claim 1, wherein the coke data management is based on VBA technology and a pivot table, and comprises: in the step 2, a summary table is added to the derived quasi-first-stage focal data and quasi-second-stage focal data by using Worksheets.
3. The method of claim 1, wherein the coke data management is based on VBA technology and a pivot table, and comprises: in the step 2, the EXCEL function is integrated into the VBA code by utilizing application.
4. The method of claim 1, wherein the coke data management is based on VBA technology and a pivot table, and comprises: in the step 3, the step of processing the image,
the yield of strength after the quasi-first coke reaction was IF (OR (AND (supplier name < > "FG", strength after reaction > -60), AND (supplier name ═ FG ", strength after reaction > -58)), 100,0)
The yield of strength after the secondary coke reaction was IF (OR (AND (supplier name < > "FG", strength after reaction > -55), AND (supplier name ═ FG ",
intensity > 50) after reaction), 100,0),
coke powder qualification rate IF (coke powder content is less than 8,100,0)
Moisture yield IF (total moisture < 6,100,0)
After the corresponding index qualification rate data item is input into the above formula, the VBA code automatically fills the calculation formula using a selection.
5. The method of claim 1, wherein the coke data management is based on VBA technology and a pivot table, and comprises: in step 3, setting a pivot table and unifying dates and names of suppliers includes: the supplier name is standardized by using a like statement and if-ElseIf-Then statements, and VBA codes are as follows:
6. the method of claim 1, wherein the coke data management is based on VBA technology and a pivot table, and comprises: and step 4, inserting a data perspective, and setting the slicer to be capable of viewing related index trends according to specific time and a specified supplier and automatically updating along with the updating of the data perspective table.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110949905.3A CN113626422A (en) | 2021-08-18 | 2021-08-18 | A method of coke data management based on VBA technology and data pivot table |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110949905.3A CN113626422A (en) | 2021-08-18 | 2021-08-18 | A method of coke data management based on VBA technology and data pivot table |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113626422A true CN113626422A (en) | 2021-11-09 |
Family
ID=78386427
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110949905.3A Pending CN113626422A (en) | 2021-08-18 | 2021-08-18 | A method of coke data management based on VBA technology and data pivot table |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113626422A (en) |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1077420A2 (en) * | 1999-07-01 | 2001-02-21 | Lucent Technologies Inc. | Systems and methods for visualizing multi-dimensional data in spreadsheets and other data structures |
| CN109359790A (en) * | 2018-12-26 | 2019-02-19 | 黑龙江建龙化工有限公司 | The method for predicting smelter coke quality index |
| CN110377893A (en) * | 2019-07-12 | 2019-10-25 | 中国轻工业长沙工程有限公司 | The method of mono- key of Excel pivot table combination VBA generation material table |
| CN112668939A (en) * | 2021-01-18 | 2021-04-16 | 大连海事大学 | Enterprise performance automatic evaluation method and system based on excelVBA technology |
-
2021
- 2021-08-18 CN CN202110949905.3A patent/CN113626422A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| EP1077420A2 (en) * | 1999-07-01 | 2001-02-21 | Lucent Technologies Inc. | Systems and methods for visualizing multi-dimensional data in spreadsheets and other data structures |
| CN109359790A (en) * | 2018-12-26 | 2019-02-19 | 黑龙江建龙化工有限公司 | The method for predicting smelter coke quality index |
| CN110377893A (en) * | 2019-07-12 | 2019-10-25 | 中国轻工业长沙工程有限公司 | The method of mono- key of Excel pivot table combination VBA generation material table |
| CN112668939A (en) * | 2021-01-18 | 2021-04-16 | 大连海事大学 | Enterprise performance automatic evaluation method and system based on excelVBA technology |
Non-Patent Citations (1)
| Title |
|---|
| 贾晋林;: "数据透视表在数据分析中的应用", 天津冶金, no. 1, 15 August 2018 (2018-08-15) * |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| Nordhaus | Evolution of modeling of the economics of global warming: changes in the DICE model, 1992–2017 | |
| CN111444293B (en) | Intelligent report generation method for multi-source heterogeneous safety monitoring data | |
| US8364300B2 (en) | Retrieving and navigating through manufacturing data from relational and time-series systems by abstracting the source systems into a set of named entities | |
| US6704015B1 (en) | Methods and apparatus for providing a quality control management system | |
| US6700575B1 (en) | Methods and apparatus for providing a quality control management system | |
| CN103606045B (en) | Report form generation method and report form system | |
| CN112016287B (en) | Data management method, platform, storage medium and electronic equipment | |
| CN102375449B (en) | Database optimization storage and query method based on industrial control field | |
| CN101025805A (en) | Product-based bill of material generating method | |
| CN111400354B (en) | Machine tool manufacturing BOM (Bill of Material) storage query and tree structure construction method based on MES (manufacturing execution System) | |
| US8239757B2 (en) | System and method for creating and modifying test data files | |
| CN103678648A (en) | Classifying and gathering system and method of BOM tables | |
| CN112579629A (en) | Method for helping purchasers of electronic component enterprises to accurately find products | |
| CN104615669A (en) | Data correction method, device and system | |
| CN114548062A (en) | Report arranging method | |
| CN101470865A (en) | Quotation information collecting system and collecting method thereof | |
| CN119721868A (en) | A construction project quality control method and system based on BIM | |
| KR100982527B1 (en) | Total Dimensional Accuracy Control System and Method for Block Assembly | |
| CN115170034A (en) | Factory object full life cycle management method | |
| CN113626422A (en) | A method of coke data management based on VBA technology and data pivot table | |
| US9785638B1 (en) | Document display system and method | |
| CN120429838B (en) | Multi-source heterogeneous data intelligent fusion method and device for carbon footprint calculation | |
| GB2379530A (en) | Handling production data | |
| CN110717322A (en) | Universal generation method for quality monitoring analysis original record table | |
| CN120069816A (en) | IBIM platform-based engineering data dynamic matching method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| CB02 | Change of applicant information |
Country or region after: China Address after: 215600 Jiangsu Shagang Steel Technology Building, Yongxin Road, Jinfeng Town, Zhangjiagang City, Suzhou City, Jiangsu Province, China. Jiangsu Shagang Steel Research Institute Co.,Ltd. Applicant after: INSTITUTE OF RESEARCH OF IRON & STEEL,SHAGANG,JIANGSU PROVINCE Applicant after: Jiangsu Shagang Steel Co.,Ltd. Applicant after: JIANGSU SHAGANG GROUP Co.,Ltd. Address before: 215600 Jiangsu Shagang Steel Technology Building, Yongxin Road, Jinfeng Town, Zhangjiagang City, Suzhou City, Jiangsu Province, China. Jiangsu Shagang Steel Research Institute Co.,Ltd. Applicant before: INSTITUTE OF RESEARCH OF IRON & STEEL,SHAGANG,JIANGSU PROVINCE Country or region before: China Applicant before: ZHANGJIAGANG HONGCHANG STEEL PLATE Co.,Ltd. Applicant before: JIANGSU SHAGANG GROUP Co.,Ltd. |
|
| CB02 | Change of applicant information | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20211109 |
|
| RJ01 | Rejection of invention patent application after publication |