Excel table has two columns of data to be used in A column and M columns. A column and column M one-to-one correspondence
Example:
If M1 ~ M10 & gt; 3, 0 & lt; M11 ~ M20 & lt; 3, M21 ~ (& gt; 3, M31 ~ M40 & lt; 0, 0 & lt; The M41 ~ M50 & lt; 3,,,,,
note is each value meet the certain conditions, such as M1, M2, M3,... M10 this number 10 all meet & gt; 3
You need to output the following data:
A1, A10, M1 ~ M10 average
A10, A20, M11 ~ M20 average
A20, A30, M21 ~ (average
A30, A40, the average of the M31 ~ M40
A40, A50, M41 ~ M50 average
,
,
,
is simply the M column data according to less than zero, greater than zero and less than 3, more than 3 classification, filtering, and then output the beginning and the end of the each corresponding to A column of data, and M column average
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
I wove a code but exist the following problems, please everybody glad to
Unable to count 1. Finally a kind of classification, such as case, if the data to the line is over 50, "A40, A50, M41 ~ M50 average" this set of data can't output
2. Unable to select & lt; 0, so the & lt; 0 this category to statistics to greater than zero and less than 3 of this kind of
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
The attached code:
Sub fenleishaixuan ()
Dim n As Integer
Dim As Integer I
Dim j As Integer
Dim x As Integer
Dim y As Single
Dim z As Single
Dim aver () As Single
Dim k1 () As Long
Dim k2 () As Long
N=[m65536]. End (xlUp). Row 'M column the last line is not empty cell line number
ReDim k1 (0 To n)
ReDim k2 (0 To n)
ReDim aver (0 To n)
I=1
J=0
X=1
Y=Cells (1, "m")
K1 (j)=Cells (I, "a")
The Do While I & lt;=n 'if the current cell line number is not greater than the last line of the execution cycle
If Cells (I, "m") & gt; If more than 3=3 Then 'cell
J=j + 1
If j & lt; 2 Then
K1 (j - 1)=Cells (1, "a")
The Else
K1 (j - 1)=k2 (j - 2)
End the If
K2 (j - 1)=Cells (I, "a")
Z=1
Y=Cells (x, "m")
The Do While x & lt; I
Y=y + Cells (x + 1, "m")
X=x + 1
Z=z + 1
Loop
Aver (j - 1)=y/z
I=I + 1
J=j + 1
If Cells (I, "m") & lt; 3 Then 'cell if less than 3
K1 (j - 1)=Cells (I - 1, "a")
K2 (j - 1)=Cells (I - 1, "a")
Aver (j - 1)=Cells (I, "m")
The Else
Y=Cells (I - 1, "m")
The Do While I & lt;=n 'if the current cell line number is not greater than the last line of the execution cycle
If Cells (I, "m") & lt; 3 Then 'cell if less than 3
K1 (j - 1)=k2 (j - 2)
K2 (j - 1)=Cells (I - 1, "a")
Z=1
The Do While x & lt; I - 1
Y=y + Cells (x + 1, "m")
X=x + 1
Z=z + 1
Loop
Aver (j - 1)=y/z
'I=I + 1
The Exit Do
End the If
I=I + 1
Loop
End the If
End the If
I=I + 1 'line number + 1, ready for the next cycle
Loop 'data output
Dim wk As a Worksheet
Dim k As String
K=InputBox (" both Please input the name ")
The Set wk=a Worksheets. Add
Wk. A Name=k
Range (" a1: a100 ")=Application. The Transpose (k1)
B1: b100 Range (" ")=Application. The Transpose (k2)
Range (" c1: c100)=Application. The Transpose (aver)
End Sub
CodePudding user response:
Should be a very simple question, I believe you can do it, temporarily don't have the time to help you, help top it,CodePudding user response:
Start recording macros in Excel 2003, manual, complete the required function end record macros, press Alt + F11 key, check just record macro corresponding VBA code,CodePudding user response:
1, if the M1 ~ M10 & gt; 3, 0 & lt; M11 ~ M20 & lt; 3, M21 ~ (& gt; 3, M31 ~ M40 & lt; 0, 0 & lt; The M41 ~ M50 & lt; 3,,,,,2, is simply the M column data according to less than zero, greater than zero and less than 3, more than 3 classification, screening...
This two put together, you don't know what to do...
CodePudding user response:
Try to get a new one, self-test, average display beside the first value, ACTS as a topic,
Sub getNumbers ()
Dim getRows
GetRows=Range (" M65536 "). The End (xlUp). Row
Dim switchChange
SwitchChange=0
Range (" M1 "). The Activate
Dim valueTxt
Dim startCell, endCell
Dim firstNumber, lastNumber
Dim tempNumber
TempNumber=0
Dim numberCount
For I=1 To getRows
ValueTxt=values (ActiveCell. Text)
If I & gt; 1 Then
If switchChange & lt;> ValueTxt Then
EndCell=ActiveCell. Address
Range (" A "& amp; (ActiveCell. Row - 1)). Activate
ActiveCell=lastNumber
Range (" A "& amp; StartCell). Activate
ActiveCell=firstNumber
ActiveCell. Offset (0, 1). Activate
ActiveCell=tempNumber/numberCount
Range (endCell). Activate
FirstNumber=ActiveCell. Text
StartCell=ActiveCell. Row
TempNumber=CLng (ActiveCell. Text)
NumberCount=1
SwitchChange=valueTxt
The Else
NumberCount=numberCount + 1
TempNumber=tempNumber + CLng (ActiveCell. Text)
LastNumber=ActiveCell. Text
End the If
The Else
SwitchChange=valueTxt
StartCell=ActiveCell. Row
FirstNumber=ActiveCell. Text
TempNumber=CLng (ActiveCell. Text)
NumberCount=1
End the If
ActiveCell. Offset (1, 0). The Activate
Next I
Range (" A "& amp; (ActiveCell. Row - 1)). Activate
ActiveCell=lastNumber
Range (" A "& amp; StartCell). Activate
ActiveCell=firstNumber
ActiveCell. Offset (0, 1). Activate
ActiveCell=tempNumber/numberCount
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull