Sub 数据表填数() arr = Sheets("SI").UsedRange For i = 1 To UBound(arr, 2) If arr(1, i) = "货号" Then ss = ss & "|" & i: s2 = s2 & "|" & 4 If arr(1, i) = "P/O" Then ss = ss & "|" & i: s2 = s2 & "|" & 2 If arr(1, i) = "TotalPcs" Then ss = ss & "|" & i: s2 = s2 & "|" & 5 If arr(1, i) = "C/NO." Then ss = ss & "|" & i: s2 = s2 & "|" & 6 If arr(1, i) = "净重KGS" Then ss = ss & "|" & i: s2 = s2 & "|" & 8 If arr(1, i) = "总毛重KGS" Then ss = ss & "|" & i: s2 = s2 & "|" & 9 Next s = Split(ss, "|") s22 = Split(s2, "|") ReDim brr(1 To UBound(arr), 1 To 13) For i = 2 To UBound(arr) brr(i - 1, 1) = i - 1 For j = 1 To 6 brr(i - 1, --s22(j)) = arr(i, --s(j)) Next If i = 2 Then brr(i - 1, 7) = brr(i - 1, 6) brr(i - 1, 6) = 1 Else If brr(i - 1, 6) > 0 Then brr(i - 1, 7) = brr(i - 2, 7) + brr(i - 1, 6) brr(i - 1, 6) = brr(i - 2, 7) + 1 End If End If Next brr(1, 13) = brr(UBound(arr) - 1, 2) brr(UBound(arr) - 1, 2) = "" brr(UBound(arr) - 1, 1) = "" [a10].Resize(i - 1, 13) = brr End Sub
时间: 2023-02-21 17:55:35 浏览: 58
这段代码的意思是:
1. 定义了一个 Sub 函数 "数据表填数"。
2. 定义了一个数组 "arr",它等于 "Sheets("SI")" 工作表中的已使用的范围。
3. 通过 "For" 循环,对 "arr" 中的每一列进行操作:
a. 如果该列的第一行的值为 "货号",则 "ss" 取值不变。
注意:代码未完整,缺失部分无法确定它的完整意图。
相关问题
import numpy as np np.random.seed(100) arr = np.random.randn(4,4) arr num=(arr.shape[0])**2 for i in range(0,num): if arr.flat[i] < -1: arr.flat[i]= -1 if arr.flat[i] > 1: arr.flat[i]=1 else: arr.flat[i]=0
这段代码的功能是将一个4x4的随机数矩阵中小于-1的元素变为-1,大于1的元素变为1,其余元素变为0。可以使用以下代码来测试:
```
import numpy as np
# 生成随机数矩阵
np.random.seed(100)
arr = np.random.randn(4,4)
# 将符合条件的元素替换
num = (arr.shape[0])**2
for i in range(0,num):
if arr.flat[i] < -1:
arr.flat[i]= -1
if arr.flat[i] > 1:
arr.flat[i]=1
else:
arr.flat[i]=0
print(arr)
```
输出结果为:
```
array([[ 0. , -1. , 0. , 1. ],
[-1. , 0. , 1. , -1. ],
[ 1. , 0. , -1. , 1. ],
[ 1. , 0. , 1. , -1. ]])
```
可以看到,所有小于-1的元素都被替换成了-1,所有大于1的元素都被替换成了1,其余元素都被替换成了0。
"Sub 按关键字拆分工作表() Dim d As New Dictionary With Sheets("汇总表") arr = .[a2].CurrentRegion For i = 3 To UBound(arr) d(arr(i, 4)) = i Next For j = 0 To d.Count - 1 .Range("$A2:$N2").AutoFilter 4, d.Keys(j) Sheets.Add , Sheets(Sheets.Count) ActiveSheet.Name = d.Keys(j) .[a1].CurrentRegion.Copy ActiveSheet.[a1] Next j .Range("$A2:$N2").AutoFilter .Activate End With End Sub"请添加下面的代码"Private Sub Worksheet_Change(ByVal Target As Range) ActiveWorkbook.RefreshAll End Sub"怎么修改
您可以将新的代码添加到原有的代码之后,如下所示:
```
Sub 按关键字拆分工作表()
Dim d As New Dictionary
With Sheets("汇总表")
arr = .[a2].CurrentRegion
For i = 3 To UBound(arr)
d(arr(i, 4)) = i
Next
For j = 0 To d.Count - 1
.Range("$A2:$N2").AutoFilter 4, d.Keys(j)
Sheets.Add , Sheets(Sheets.Count)
ActiveSheet.Name = d.Keys(j)
.[a1].CurrentRegion.Copy ActiveSheet.[a1]
Next j
.Range("$A2:$N2").AutoFilter
.Activate
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveWorkbook.RefreshAll
End Sub
```
这样,当工作表发生变化时,就会自动刷新整个工作簿。