使用 vbscript 创建稀疏矩阵 [英] Sparse matrix creation using vbscript
本文介绍了使用 vbscript 创建稀疏矩阵的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一个数组={A,B,Y,X}.现在我有一个 Excel 工作表,它可以有动态的列数和行数.以下面的例子为例:
Suppose I have an Array={A,B,Y,X}. Now I do have an Excel sheet which can have dynamic number of columns and Rows.Say as an example below :
输入:
ColA ColB ColC ....
T P Y ....
C Y D ....
B A M ....
Z R X ....
输出:
ColA ColB ColC ....
- - Y ....
- Y - ....
B A - ....
- - X ....
如果所有列都只有数组值,如果发现任何其他值,则需要将它们替换为 "-"
Where all the columns will have only the Array values,if any other values are found they are required to be replaced by "-"
除了相对较慢的循环技术外,是否有任何更快的过程可以使用 VBscript 来完成这些操作?
Is there any faster process to do these using VBscript except comparatively slow looping technique?
谢谢,
推荐答案
Sub Macro1()
Dim arr, i, rng As Range
arr = Array("X", "Y", "Z")
Set rng = ActiveSheet.Range("A1").CurrentRegion
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = LBound(arr) To UBound(arr)
rng.Replace What:=arr(i), Replacement:="-", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Sub KeepValues()
Dim arr, arrVals, i, rng As Range, r, c
Dim keepval As Boolean
arr = Array("X", "Y", "Z")
Set rng = ActiveSheet.Range("A1").CurrentRegion
arrVals = rng.Value
For r = 1 To UBound(arrVals, 1)
For c = 1 To UBound(arrVals, 2)
keepval = False
For i = LBound(arr) To UBound(arr)
If arr(i) = arrVals(r, c) Then
keepval = True
Exit For
End If
Next i
If Not keepval Then arrVals(r, c) = ""
Next c
Next r
rng.Value = arrVals
End Sub
这篇关于使用 vbscript 创建稀疏矩阵的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文