WPS or other forms and document processing are often used in learning and work. In particular, some formulas or calculations are often used in tables to process data. It is OK to simply take the maximum or minimum, but it is troublesome if too much data is involved or cyclic processing is required.
For example, I want to get an accumulated value through the table, 1, 2, 3, 5, 8, 13 When this kind of data interacts with each other or needs cyclic judgment, there will be the embarrassment of not knowing how to use the formula. However, if you can customize the function, you don't have to worry about these problems. In the future, take the commonly used collection cards as an example.
Requirement rules: cards are divided into five categories: A/B/C/D/E. one card in each category can be exchanged for s card, and one card in each of the other three categories can be exchanged for X card. Given the number of A/B/C/D/E five cards, automatically calculate the number of S and X cards.
For example:
| A number of cards | B number of cards | Number of C cards | D number of cards | E number of cards |
| 5 | 3 | 2 | 1 | 3 |
Calculation process:
It'S easy to get the number of S cards. Just sort the number of all cards from small to large and take the minimum
The sorted quantity is {1(E card) 2(C card) 3(B card) 3(E card) 5(A card)
Therefore, one S card can be exchanged, and the remaining situation after exchange is 0(E card) 1(C card) 2(B card) 2(E card) 4(A card)
The remaining number of X cards is more complex. It needs to be sorted and exchanged continuously, and it needs to judge whether the cards can be exchanged
0(E card) 1(C card) 2(B card) 2(E card) 4(A card) < < number of cards remaining after exchanging S card
0(E card) 1(C card) 1(B card) 1(E card) 3(A card) < < quantity after exchange for X card < 1
0(E card) 1(C card) 0(B card) 0(E card) 2(A card) < < quantity after exchange for X card < 2
0(E card) 1(C card) 0(B card) 0(E card) 2(A card) < < number of cards remaining after exchanging X card
The result is:
The above convertible cards are 1 S card and 2 X cards.
However, the above process is only a simple calculation, and the results of judgment and processing need to be taken. It may be possible to obtain the use formula of S card, but it is more complex to implement the later X card with the formula in xlsx table, Next, this paper will introduce a method of processing table data which is more suitable for simple operation processing and needs cyclic judgment.
Automatic formula calculation process
1. New table file
This document can also be added to the original file, but if it is the first attempt to prevent data loss, it is better to test it first to avoid data being deleted or modified by mistake.
2. Save as Microsoft Excel Macro enabled Workbook (. xlsm)
Save the table as a Microsoft Excel Macro enabled Workbook with the suffix (. xlsm). The special feature is that the macro definition is enabled. This step cannot be omitted, otherwise the macro definition cannot be enabled.
After completing the above operations, you can get a table file with macro definition enabled.
3. Open the table and enter the WPS macro editor
After opening the document, click Development Tools > > to switch to JS environment > > WPS macro editor.
After clicking, the macro editor will be opened automatically, and a blank macro definition template of Module1 will be created for you, as shown in the figure below
4. Preliminary form preparation
>>The function here is to prevent errors in the operation of rows and columns, and to make more accurate preparations when reading and writing data. Notes can be modified according to different small functions, and everyone can design according to different situations.
5. Program design of macro definition 1 (trigger condition)
The macro definition is triggered conditionally. Here, the change of row data (row 3) is the trigger condition. You can select the SheetChange(Sh, rg) function.
After selection, the editor will create a function function according to your trigger conditions. As long as you write down the processing process in this function, you can complete the customized function.
5. Macro definition programming 2 (row and column selection)
Because the trigger mechanism of SheetChange is that changes will be made in the whole table, the operation needs to be adjusted to the third column. The modification is as follows:
function Application_SheetChange(Sh, rg)
{
let hang=rg.Row; //Record line
let lie=rg.Column;//Record column
if(hang==3)
{
// Processing function
}
}In this way, the data will be processed only when the third row changes, and can be changed to other rows or columns as needed.
5. Programming of macro definition 3 (reading and writing data)
After determining the modified row position, it is now necessary to read the data at the specified position of the third row for processing. At this time, some input and output functions are required,
function Application_SheetChange(Sh, rg)
{
let hang=rg.Row; //Record line
let lie=rg.Column;//Record column
if(hang==3)
{
//Regular reading and writing
let A_NUM= Range("C3").Value2; //Read the value of C3 position to A_NUM
Application.Range("C4").Value2=0;//Change the value of C4 position to 0
//Read with parameter
let B_NUM=Application.Worksheets.Item(1).Cells.Item(3,4).Value2;
//Read the value of D3 position to B_NUM
//Write with parameters
Application.Worksheets.Item("Sheet1").Activate() //Open Sheet1 table
let rng = Range("A1:G5"); //Table area involving modification
rng.Cells.Item(5,4).Value2 = 1; // Item (row, column) changes the value of D5 to 1
}
}5. Macro definition programming 4 (array processing)
function Application_SheetChange(Sh, rg)
{
let hang=rg.Row; //Record line
let lie=rg.Column;//Record column
if(hang==3)
{
var card=[0,0,0,0,0];//Define initial array
//Read multiple data
card[0]=Application.Worksheets.Item(1).Cells.Item(3,3).Value2;//Read class A card
card[1]=Application.Worksheets.Item(1).Cells.Item(3,4).Value2;//Read class B card
card[2]=Application.Worksheets.Item(1).Cells.Item(3,5).Value2;//Read class C card
card[3]=Application.Worksheets.Item(1).Cells.Item(3,6).Value2;//Read class D card
card[4]=Application.Worksheets.Item(1).Cells.Item(3,7).Value2;//Read class E card
//Write with parameters
Application.Worksheets.Item("Sheet1").Activate() //Open Sheet1 table
let rng = Range("A1:G8"); //Table area involving modification
rng.Cells.Item(6,3).Value2 = card[0]; // Print the number of A cards read on C6
rng.Cells.Item(6,4).Value2 = card[1]; // Print the number of B cards read on D6
rng.Cells.Item(6,5).Value2 = card[2]; // Print the number of C cards read on E6
rng.Cells.Item(6,6).Value2 = card[3]; // Print the number of D cards read in F6
rng.Cells.Item(6,7).Value2 = card[4]; // Print the number of E cards read in G6
}
}Read the data in multiple tables and modify the data in multiple locations according to the amount of data processed. After modifying to the above code, click Run macro to see that changes have also taken place in the table.
The above function is to copy the data of the third row from C3-G3 and display it to row 6. When modifying the data of the third row, Row 6 will be automatically updated to the modified value.
5. Program design of macro definition 5 (function realization)
The previous operation is just to be familiar with the operation logic and novice learning operation, followed by the code for the whole calculation and processing function.
The implementation code is as follows:
function Application_SheetChange(Sh, rg)
{
let hang=rg.Row; //Record line
let lie=rg.Column;//Record column
if(hang==3)
{
var card=[0,0,0,0,0];//Define initial array
var orde=[0,1,2,3,4];//Record card group sequence
//Read multiple data
card[0]=Application.Worksheets.Item(1).Cells.Item(3,3).Value2;//Read class A card
card[1]=Application.Worksheets.Item(1).Cells.Item(3,4).Value2;//Read class B card
card[2]=Application.Worksheets.Item(1).Cells.Item(3,5).Value2;//Read class C card
card[3]=Application.Worksheets.Item(1).Cells.Item(3,6).Value2;//Read class D card
card[4]=Application.Worksheets.Item(1).Cells.Item(3,7).Value2;//Read class E card
for(let i=0;i<5;i++) /****Sort from small to large*****/
for(let j=0;j<5;j++)
{
if(card[j]>card[j+1])
{
var temp=card[j+1]; card[j+1]=card[j]; card[j]=temp;
temp=orde[j+1]; orde[j+1]=orde[j]; orde[j]=temp;
}
}
let s; s=0; //Initialize S card value
if(card[0]!=0) //The minimum value is not 0
{
s=card[0]; //Record the exchange quantity of S card
card[0]=card[0]-s;
card[1]=card[1]-s;
card[2]=card[2]-s;
card[3]=card[3]-s;
card[4]=card[4]-s; //Deduct the card required to exchange s card
}
for(let i=0;i<5;i++) /****Sort from small to large*****/
for(let j=0;j<5;j++)
{
if(card[j]>card[j+1])
{
var temp=card[j+1]; card[j+1]=card[j]; card[j]=temp;
temp=orde[j+1]; orde[j+1]=orde[j]; orde[j]=temp;
}
}
let x; x=0; //Initialize x card value
while(card[2]>0)
{
card[2]--;
card[3]--;
card[4]--; //Deduct the card required to exchange X card
x++; //X quantity + 1
for(let i=0;i<5;i++) /****Sort from small to large*****/
for(let j=0;j<5;j++)
{
if(card[j]>card[j+1])
{
var temp=card[j+1]; card[j+1]=card[j]; card[j]=temp;
temp=orde[j+1]; orde[j+1]=orde[j]; orde[j]=temp;
}
}
}
//Write with parameters
Application.Worksheets.Item("Sheet1").Activate() //Open Sheet1 table
let rng = Range("A1:G8"); //Table area involving modification
rng.Cells.Item(5,4).Value2 = s; // The number of S cards is printed on D5
rng.Cells.Item(5,6).Value2 = x; // The number of X cards is printed in F5
rng.Cells.Item(4,3).Value2 = card[orde[0]]; // Print the number of A cards read in C4
rng.Cells.Item(4,4).Value2 = card[orde[1]]; // Print the number of B cards read on D4
rng.Cells.Item(4,5).Value2 = card[orde[2]]; // Print the number of read C cards on E4
rng.Cells.Item(4,6).Value2 = card[orde[3]]; // Print the number of D cards read in F4
rng.Cells.Item(4,7).Value2 = card[orde[4]]; // Print the number of E cards read in G4
}
}6. Function realization
After writing the macro definition function, you can find that the table has automatically completed the calculation and processing.
Modify different ownership cards, and the form will automatically get the number of remaining cards and convertible cards.
Postscript: the above is only a small function written to solve some of the data processing at hand. There may be some ambiguities or errors. Readers and leaders are welcome to give advice and correction. If you have doubts, you can also comment and leave a message. I will answer, share and supplement.
Time: December 26, 2021
Platform: WPS Office} Win10 professional (64 bit)
Relevant reference links: https://www.cnblogs.com/nutix/p/15189657.html
https://zhuanlan.zhihu.com/p/387247248
Relevant reference links: https://zhuanlan.zhihu.com/p/387247248
Relevant reference https://blog.csdn.net/u012735516/article/details/118163658