CN105868212A - VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method - Google Patents
VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method Download PDFInfo
- Publication number
- CN105868212A CN105868212A CN201510032022.0A CN201510032022A CN105868212A CN 105868212 A CN105868212 A CN 105868212A CN 201510032022 A CN201510032022 A CN 201510032022A CN 105868212 A CN105868212 A CN 105868212A
- Authority
- CN
- China
- Prior art keywords
- bucket
- information
- cells
- circulation
- sublist
- 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.)
- Withdrawn
Links
Landscapes
- Management, Administration, Business Operations System, And Electronic Commerce (AREA)
Abstract
The invention provides a VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method. The method is characterized by comprising the following steps: establishing an EXCEL file of a packaging bucket management system, designing the storage format of circulation information in a ''circulation detail'' sublist, and carrying out system function design, retrieval algorithm design and input program design. Each packaging bucket has a unique bucket number, the bucket numbers occupy the first column and are arranged in an ascending order, the second column is ''bucket returning date'', the third column is ''bucket returning client name'', the fourth column is ''delivery date'', the fifth column is ''delivery client name'', and each time of circulation comprises the four contents; and in next circulation, successively, the sixth column is ''bucket returning date'', the seventh column is ''bucket returning client name'', the eighth column is ''delivery date'', the ninth column is ''delivery client name'', and so on. When the method is used, workers can clearly know the state of the packaging bucket, so that the searching time for the workers is saved, and working efficiency is improved.
Description
Technical field
The present invention relates to one VBA be programmed at EXCEL the exterior and the interior, the feature recycled for electrolyte pail pack, the algorithm of design packet barrelling information retrieval and information management system.
Background technology
The packed and transported of lithium-ion battery electrolytes be unable to do without pail pack, can there be substantial amounts of 200KG stainless steel pail pack in each electrolyte company, these pail pack Reusabilities, substantial amounts, general electrolyte company pail pack quantity is no less than more than 6000, every day, whom pail pack issued, and had how many, the date of delivery.Having how many empty buckets to return, empty bucket is returned wherefrom, returns that barrelage is the most correct etc. is required for accurate statistics.And pail pack also differed in the demurrage of different clients, can return bucket in 2 months had, and can't return in 3 months had.This requires again to add up external bucket, looks at which bucket that has more than 3 months, all in which client's hands.Manually add up can also deal with when bucket negligible amounts, the most unable to do what one wishes during bucket substantial amounts, inefficiency is filled with flaws.Corporate assets, if losing serious, being caused greater loss, thus company are more and more urgent to the demand of pail pack information system management by stainless steel cask relatively costly by one more than 2000 yuan.
Summary of the invention
According to above technical problem, the present invention provides a kind of electrolyte pail pack based on VBA management system and operation method, it is characterised in that comprise the following steps:
(1) EXCEL file of pail pack management system, is set up
" homepage " sublist is set up in EXCEL table, described " homepage " sublist includes the trigger button such as typing, conditional information retrieval, set up " circulation detail " sublist, the circulation information of each barrel is stored in this sublist, set up " bucket status retrieval result " sublist, " retrieving result by barrel number " sublist, " retrieving result by client " sublist, in order to show Query Result；
(2) storage format of circulation information in, design " circulation detail " sublist
Each pail pack has a unique barrel number, and barrel number takies first row, arranges the most from small to large, second is classified as " returning the bucket date ", 3rd is classified as " returning bucket customer name ", and the 4th is classified as " Shipping Date ", and the 5th is classified as " delivery customer name ", circulation includes this four contents every time, circulating next time and and then the 6th be classified as " returning the bucket date ", the 7th is classified as " returning bucket customer name ", and the 8th is classified as " Shipping Date ", 9th is classified as " delivery customer name ", by that analogy.Each EXCEL table comprises 256 row, 65536 row, removing the first row is title, and first row is barrel number, 255 ÷ 4=63.7, i.e. EXCEL list at most can store 65535 buckets, the circulation information of 63 times, if the circulation of each pail pack once calculated by 2 months, 1 year 6 times, this table can use 10 years, fully meets use requirement；
(3), system function design
According to user's request, homepage comprises following functions option:
a)
Retrieve by barrel number, i.e. retrieve all circulation informations of certain barrel；
B) by visitor's title retrieval, i.e. retrieve which bucket certain client once used；
c)
Retrieve by date, i.e. which bucket certain sky sends, and whom issues；Regain which bucket；
D) retrieval of external pail pack, i.e. retrieves the information of all external buckets, is specifically divided into which bucket certain client is now detained, and how long is detained；Also can find by date, the bucket sent out for certain day, which has the most do not return, all in which client's hands.Which in retrieval result, also can count barrel lose；
e)
Delivery bucket input function；
f)
Return bucket input function；
(4), searching algorithm design
VB editing environment is programmed, realize each function, wherein the core of each search program is to judge the state of each barrel, this needs to design an algorithm and judges each bucket, i.e. judge that the last information of each barrel is Shipping Information or returns bucket information, if " Shipping Information " illustrates that bucket is not the most returned, this barrel number and last Shipping Information are extracted, if " returning bucket information " then explanation bucket has been regained；
If the columns of the final data unit lattice that certain barrel number is expert at is n, making z=(n-3)/4, if z < 0, bucket loss, if z is positive integer includes 0, bucket is in company, if z is positive decimal, bucket is outside company.In Microsoft Excel, maximum number of column is 256 row, if the row at certain barrel number place is x, defines m=256, calls following statement
While (IsEmpty(Cells(x, m)))
m = m -
1
Wend
The most first judge cells (x, 256) whether cell is empty, judge cells (x if it is empty, 255) whether cell is empty, by that analogy until corresponding cell is not sky, take the columns value of this cell, be n, using above-mentioned algorithm to carry out judging the state of bucket again, arithmetic statement is as follows:
Cells(i + 2, n + 4) = (Cells(i +
2, n + 3) - 3) / 4
If Cells(i
+ 2, n + 4) < 0 Then
Cells(i + 2, n+ 5)
=" loss "
ElseIf (Int(Cells(i + 2, n + 4)) < Cells(i + 2, n + 4)) Then
Cells(i + 2, n +
5)=" outside "
Else
Cells(i + 2, n +
5)=" interior "
End If
By each intermediate variable and result in program, store by array, export in the cell specified in the corresponding page the most again, as the basic data of conditional information retrieval；
(5), recording program design
Recording program is divided into Shipping Information recording program and returns bucket Data Enter program, need before typing to carry out information check, mentality of designing is basically identical, here illustrate as a example by Shipping Information typing, after " homepage " sublist specifies region input to want the information of typing, first check that information is the most wrong, need to judge the current state of bucket in " inspection program " the inside, and the current state of delivery bucket must be to have returned company, check errorless, recording program can be started, find out the row that this barrel number is corresponding, and then find out this row the most not for empty cell, the Shipping Information of correspondence is inserted after this cell.
The invention have the benefit that the present invention develops with VBA in Microsoft Excel, made pail pack information management system, it is achieved that the flowing to more than 6000 pail packs is followed the tracks of, statistics retrieval.The use of the present invention alleviates the burden of worked personnel, and staff's record is only according to pail pack, it need to be carried out state editor, just can be clearly understood that the state of pail pack during inquiry by native system, simple and convenient.In the present invention, each pail pack has a unique barrel number, barrel number takies first row, arrange the most from small to large, second is classified as " returning the bucket date ", 3rd is classified as " returning bucket customer name ", 4th is classified as " Shipping Date ", 5th is classified as " delivery customer name ", circulation includes this four contents every time, circulate next time and and then the 6th be classified as " returning the bucket date ", 7th is classified as " returning bucket customer name ", 8th is classified as " Shipping Date ", 9th is classified as " delivery customer name ", by that analogy, the state understanding pail pack that staff can be cheer and bright in use, save the time that staff ransacks, improve work efficiency.
Accompanying drawing explanation
Fig. 1 is the user home page face that the present invention provides.
Fig. 2 is the circulation detail list that the present invention provides.
Fig. 3 is the search program flow chart that the present invention provides.
Detailed description of the invention
Embodiment
1
Seeing Fig. 1, the homepage of present invention design, each functional area position is fixed, it is impossible to amendment.Three conditions of pail pack retrieval can select simultaneously, it is possible to individually selects a condition to retrieve, and stops by " retrieval " year after selecting.Two conditions of external pail pack retrieval can select simultaneously, it is also possible to individually selects, selected after need first to press " primary retrieval " button and perform preliminary search program, obtain basic data, then press " conditional information retrieval " button and complete finally to retrieve.Because the condition adjudgement of bucket and be sorted in data volume big in the case of program execution time longer, so in the case of not having Data Enter, basic data is constant, so can not perform " primary retrieval " program, directly perform " conditional information retrieval " program.
Seeing Fig. 1, Fig. 2, the pail pack input function of present invention design mainly includes " Shipping Information typing " and " returning bucket Data Enter ".Can once input multiple typing information formulating region, need to carry out information check before typing, illustrate as a example by Shipping Information typing here.Region input " barrel number " is specified in " homepage " sublist, " customer name ", " Shipping Date ", first check that information is the most wrong, need to judge the current state of bucket in " inspection program " the inside, and the current state of delivery bucket must be to have returned company, check errorless, last circulation can be shown in " returning bucket client " hurdle, pail pack returns from that client, recording program can be started after confirmation, program finds out, in " circulation detail " sublist, the row that this barrel number is corresponding, and then find out this row the most not for empty cell, the Shipping Information of correspondence is inserted after this cell.
Embodiment 2
Seeing Fig. 3, the search program kind of present invention design is more, and wherein the most complicated with " external bucket retrieval " program, it needs to judge the current state of pail pack, this present invention devises a kind of algorithm and can solve this difficult problem.If the columns of the final data unit lattice that certain barrel number is expert at is n, making z=(n-3)/4, if z < 0, bucket loss, if z is positive integer includes 0, bucket is in company, if z is positive decimal, bucket is outside company.
The programming of conditional information retrieval is mainly screened in the result of preliminary search, according to customer requirement, fuzzy search to be capable of when retrieving by " customer name ", which bucket such as inquiry " Harbin light space " has been detained, then have only to input " light space " and can retrieve all information comprising " light space " printed words in delivery customer name.Here calling InStr () function to realize, concrete statement is as follows:
While Not (IsEmpty(Cells(n,
1)))
If InStr(Cells(n,
2), c1) <> 0 Then
a(i)
= Cells(n, 1)
b(i)
= Cells(n, 2)
c(i)
= Cells(n, 3)
i = i + 1
End If
n = n + 1
Wend
As InStr (Cells (n, 2), c1)
During<>0, illustrate to comprise keyword, the information of bucket is stored in array.
By each intermediate variable and result in program, store by array, export in the cell specified in the corresponding page the most again, as the basic data of conditional information retrieval.
The above is only the preferred embodiment of the present invention, it is noted that for those skilled in the art, under the premise without departing from the principles of the invention, it is also possible to make some improvement, and these improvement also should be regarded as protection scope of the present invention.
Claims (1)
1. electrolyte pail pack based on a VBA management system and operation method, it is characterised in that comprise the following steps:
(1) EXCEL file of pail pack management system, is set up
" homepage " sublist is set up in EXCEL table, described " homepage " sublist includes the trigger button such as typing, conditional information retrieval, set up " circulation detail " sublist, the circulation information of each barrel is stored in this sublist, set up " bucket status retrieval result " sublist, " retrieving result by barrel number " sublist, " retrieving result by client " sublist, in order to show Query Result；
(2) storage format of circulation information in, design " circulation detail " sublist
nullEach pail pack has a unique barrel number，Barrel number takies first row，Arrange the most from small to large，Second is classified as " returning the bucket date "，3rd is classified as " returning bucket customer name "，4th is classified as " Shipping Date "，5th is classified as " delivery customer name "，Circulation includes this four contents every time，Circulate next time and and then the 6th be classified as " returning the bucket date "，7th is classified as " returning bucket customer name "，8th is classified as " Shipping Date "，9th is classified as " delivery customer name "，By that analogy，Each EXCEL table comprises 256 row，65536 row，Removing the first row is title，First row is barrel number，255÷4=63.7，I.e. EXCEL list at most can store 65535 buckets，The circulation information of 63 times，If the circulation of each pail pack once calculated by 2 months，1 year 6 times，This table can use 10 years，Fully meet use requirement；
(3), system function design
According to user's request, homepage comprises following functions option:
Retrieve by barrel number, i.e. retrieve all circulation informations of certain barrel；
By visitor's title retrieval, i.e. retrieve which bucket certain client once used；
Retrieve by date, i.e. which bucket certain sky sends, and whom issues；Regain which bucket；
External pail pack is retrieved, and i.e. retrieves the information of all external buckets, is specifically divided into which bucket certain client is now detained, and how long is detained；Also can find by date, the bucket sent out for certain day, which has the most do not return, all in which client's hands, in retrieval result, also can count which barrel lose；
Delivery bucket input function；
Return bucket input function；
(4), searching algorithm design
VB editing environment is programmed, realize each function, wherein the core of each search program is to judge the state of each barrel, this needs to design an algorithm and judges each bucket, i.e. judge that the last information of each barrel is Shipping Information or returns bucket information, if " Shipping Information " illustrates that bucket is not the most returned, this barrel number and last Shipping Information are extracted, if " returning bucket information " then explanation bucket has been regained；
If the columns of the final data unit lattice that certain barrel number is expert at is n, make z=(n-3)/4, if z < 0, bucket is lost, if z is positive integer includes 0, bucket is in company, if z is positive decimal, bucket is outside company, and in Microsoft Excel, maximum number of column is 256 row, if the row at certain barrel number place is x, definition m=256, call following statement
While (IsEmpty(Cells(x, m)))
m = m - 1
Wend
The most first judge cells (x, 256) whether cell is empty, judge cells (x if it is empty, 255) whether cell is empty, by that analogy until corresponding cell is not sky, take the columns value of this cell, be n, using above-mentioned algorithm to carry out judging the state of bucket again, arithmetic statement is as follows:
Cells(i + 2, n + 4) = (Cells(i + 2, n + 3) - 3) / 4
If Cells(i + 2, n + 4) < 0 Then
Cells (i+2, n+ 5)=" loss "
ElseIf (Int(Cells(i + 2, n + 4)) < Cells(i + 2, n + 4))
Then
Cells (i+2, n+5)=" outside "
Else
Cells (i+2, n+5)=" interior "
End If
By each intermediate variable and result in program, store by array, export in the cell specified in the corresponding page the most again, as the basic data of conditional information retrieval；
(5), recording program design
Recording program is divided into Shipping Information recording program and returns bucket Data Enter program, need before typing to carry out information check, mentality of designing is basically identical, here illustrate as a example by Shipping Information typing, after " homepage " sublist specifies region input to want the information of typing, first check that information is the most wrong, need to judge the current state of bucket in " inspection program " the inside, and the current state of delivery bucket must be to have returned company, check errorless, recording program can be started, find out the row that this barrel number is corresponding, and then find out this row the most not for empty cell, the Shipping Information of correspondence is inserted after this cell.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510032022.0A CN105868212A (en) | 2015-01-22 | 2015-01-22 | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510032022.0A CN105868212A (en) | 2015-01-22 | 2015-01-22 | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN105868212A true CN105868212A (en) | 2016-08-17 |
Family
ID=56623550
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510032022.0A Withdrawn CN105868212A (en) | 2015-01-22 | 2015-01-22 | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN105868212A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109698504A (en) * | 2019-03-08 | 2019-04-30 | 张文韬 | Calculation of tidal current analysis method and system |
| CN111914526A (en) * | 2020-07-24 | 2020-11-10 | 中铁建电气化局集团第一工程有限公司 | Method for calculating and managing bus use by using excelVBA |
Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN101419628A (en) * | 2008-12-03 | 2009-04-29 | 浪潮集团山东通用软件有限公司 | Data general-purpose leading-in method based on excel file format |
| US20110119092A1 (en) * | 2007-08-07 | 2011-05-19 | Szela Jr Erwin G | Electronic health management system |
| CN104268728A (en) * | 2014-06-09 | 2015-01-07 | 乐视网信息技术（北京）股份有限公司 | Inventory control method and system |
-
2015
- 2015-01-22 CN CN201510032022.0A patent/CN105868212A/en not_active Withdrawn
Patent Citations (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20110119092A1 (en) * | 2007-08-07 | 2011-05-19 | Szela Jr Erwin G | Electronic health management system |
| CN101419628A (en) * | 2008-12-03 | 2009-04-29 | 浪潮集团山东通用软件有限公司 | Data general-purpose leading-in method based on excel file format |
| CN104268728A (en) * | 2014-06-09 | 2015-01-07 | 乐视网信息技术（北京）股份有限公司 | Inventory control method and system |
Non-Patent Citations (1)
| Title |
|---|
| 靳建水 等: "基于VBA的包装桶管理系统的实现", 《物流技术》 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN109698504A (en) * | 2019-03-08 | 2019-04-30 | 张文韬 | Calculation of tidal current analysis method and system |
| CN111914526A (en) * | 2020-07-24 | 2020-11-10 | 中铁建电气化局集团第一工程有限公司 | Method for calculating and managing bus use by using excelVBA |
| CN111914526B (en) * | 2020-07-24 | 2023-10-20 | 中铁建电气化局集团第一工程有限公司 | Method for calculating and managing bus use by utilizing excelVBA |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| JP6854349B2 (en) | Generation of goods sorting tasks, goods sorting methods and equipment | |
| US8479089B2 (en) | Constructing and applying a constraint-choice-action matrix for decision making | |
| CN102682116A (en) | Method and device for processing table items based on Hash table | |
| Tao et al. | A column generation approach for the route planning problem in fourth party logistics | |
| JP4713965B2 (en) | Design manufacturing support device, design manufacturing support program, and design manufacturing support method | |
| CN109636306A (en) | Node-flow shifting method, device, terminal and storage medium | |
| CN105005562A (en) | Retrieval result display processing method and apparatus | |
| CN107679690A (en) | A kind of worksheet method, storage medium and electronic equipment | |
| CN103024149A (en) | Mobile terminal and method for processing information content thereof | |
| CN107515875A (en) | Data query method and device | |
| CN104166710B (en) | A kind of weather prediction method and its mobile terminal based on mobile terminal | |
| CN101675415A (en) | Program pattern analyzer, pattern appearance status information production method, pattern information generating device, and program | |
| CN104574044A (en) | Task originating method in office automation and office automation system | |
| CN105868212A (en) | VBA(Visual Basic for Applications)-based electrolyte packaging bucket management system and operation method | |
| CN109523085A (en) | A kind of filling method of resume, device, electronic equipment and storage medium | |
| CN111930823A (en) | Data query method and device, data center station and storage medium | |
| TW202143062A (en) | Systems and methods for reducing database query latency | |
| CN113408862A (en) | Operation and maintenance scheduling method, equipment and storage medium | |
| Pascual et al. | Joint optimization of fleet size and maintenance capacity in a fork-join cyclical transportation system | |
| CN106547805A (en) | The method and apparatus of optimization database index | |
| CN107273511A (en) | Supervisory systems after one kind planning batch | |
| CN109446220B (en) | Express user-based customer service voice menu customization method and customization system | |
| CN104408189A (en) | Keyword ranking display method and device | |
| CN103049313B (en) | For label stopping device and the method for browser | |
| Bulut | Multiloop transportation simplex algorithm |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| WW01 | Invention patent application withdrawn after publication |
Application publication date: 20160817 |
|
| WW01 | Invention patent application withdrawn after publication |