Excel VBA handles the method that repeats data
Write in front:
We are in to be when processing Excel data in Excel-VBA, a lot of moment can be divided for 3 measure, read take construction of disposal data, computation to output result of data, output.
Former exchange processing data with latter meeting and Excel job watch, and that measure among is in computer memory processing is finished.
We know the structure of raw data decided algorithm, that is to say of the first pace read the code that took disposal data to decide the computation among and the output from the back to be spent easily hard. When so we are designing source data, the construction that should notice source data is reasonable.
PS: ? Does personal Mei seek aluminium of Huang of surname of Yu engrave formic peptone does aluminium of Xing Yun of magnesian smile of Ω of smile of silent flood Ang fall be contrary to?
A. Abide by certain variable to name regulation. Everybody meets those who have his to name a habit, but what I want to say is to let this kind of habit follow certain regulation, and as far as possible the meaning that lets it convey oneself.
B. Write code to be written into a module as far as possible, carry keep a process alone or coming out is function, its advantage is to avoid reduplicative code not just, bigger gain is to make you OK the function that more dedicated settlement becomes independent relatively and the circumstance that need not go considering overall situation
Have a proper name to this process, the code that can let you likewise is understood more easily, the code that lets you can talk.
Use VBA to handle a few kinds of methods of reduplicative data in Excel a little with everybody today.
In the process of Excel data processing, we often can encounter existence of a certain form to repeat data. But await in great a long time, we often need these to repeat data to undertake handling, get the data of our place need.
We still cite a case, we are not the data that pursues below to often see actually, specific following plan institute are shown
So our issue comes! See a problem please:
Assume we want calculation the sectional measure of the place of on-the-job personnel, how should be we calculated? Still need consideration at the same time of departmental door how many person is the number respectively? Below we one step will come true.
Say according to introductive place, we analyse the data discovery of above, the result of this source data is single, data is very normative, have the law.
We handle data to read access to occupy namely above all, read access to occupy namely we often say keep Exce computer memory, in this position we use array with respect to what think of, keep source data area memory.
We choose appropriate method, undertake data processing. What we think of is the keyword data that uses a vocabulary not repeatability, the data processing that finish.
Output our result namely finally, namely we will be treated data, in writing form afresh.
So far the trilogy that we process data gets be reflectinged thoroughly. We see specific unit process of cargo bandling in light of now.
PS: ? Cheek invades rice of Tao to be worth rice of clear to be worth Zuo Ey not reduplicative is characteristic, we stock province the dictionary in
We see the result after carrying out:
Area of maize Gao Liang uses lexical character for us namely, among the dictionary that joins the department us, be opposite next reduplicative branch undertakes cumulative, achieve computational reduplicative thereby several. Of course we also can be calculated the amount of sectional salary.
Attach code:
Sub Test()
Lexical early days is bound write a law surely
Dim D As New Dictionary
Dim Arr, i&
Cleared H and the value that I lists
[H:i] .ClearContents
the unit case area of data direct assignment gives array Arr
Arr = Range("a2:f15" )
Use character of circular general dictionary, in writing sectional keyword the dictionary, be opposite next his have cumulative consideration.
For I = 1 To UBound(arr)
The meaning of this word is, if the dictionary is nonexistent the keyword of this branch, in writing this keyword the dictionary. If this keyword has existed in the dictionary, right his have cumulative consideration. Namely the K=K+1 of our platitude.
D(arr(i, 2)) = D(arr(i, 2)) + 1
Next
Write headline
[H1].Resize(1, branch of 2) = Array("
