数学转置在excel中 [英] Mathematical transpose in excel
问题描述
我正在尝试在excel中找到一些东西,然后实现它的VBScript。我必须以数学方式将几个单元格(10 * 10或5r * 10c)转换成矩阵:
----------- --------------------
| .. | .. | .. | .. | .. | .. |
| 21 | 22 | 23 | 24 | 25 | .. |
| 11 | 12 | 13 | 14 | 15 | .. |
| 1 | 2 | 3 | 4 | 5 | .. |
-------------------------------
必须成为
--------------------------- ----
| .. | .. | .. | .. | .. | .. |
| 3 | 13 | 23 | 33 | 43 | .. |
| 2 | 12 | 22 | 32 | 42 | .. |
| 1 | 11 | 21 | 31 | 41 | .. |
-------------------------------
现在我不是一个数学家(我现在更少一个程序员),但我想出了: F(y)=((MOD(x,10) 1)* 10)+(1 +((x-MOD(x,10))/ 10))
( x
预先块的顶部, y
是下面的块中的值。)现在这样可以正常工作到某一点(例如
在VBScript中,我首先写下:
函数GetPosInSrcRack(Pos)
Dim PlateDef(9),x,y,i,tmp
'板定义
ReDim tmp(UBound ))
对于x = 0到UBound(PlateDef)
PlateDef(x)= tmp
下一个
i = 1
对于x = 0到UBound (PlateDef)
对于y = 0到UBound(PlateDef(x))
PlateDef(x)(y)= i
i =(i + 1)
下一个
Next
'Dim msg'检查定义
'对于x = 0 To(UBound(PlateDef))
'msg = Join(PlateDef(x),,)&vbCrLf&msg
'Next
'获取位置
y =(pos Mod 10)
x =((pos - y)/ 10)
GetPosInSrcRack = PlateDef(y)(x)
结束函数
当然,这个工作很糟糕,但很糟糕。
使用上面的公式我会写:
函数GetPosInSrcRack(Pos)
Pos =(((Pos MOD 10)-1)* 10)+(1 +((Pos - (Pos MOD 10 ))
结束函数
但是像我说的,这仍然是不正确的(10给-8)
有人帮我吗?
y =(MOD(x-1 ,10))* 10 + INT((x-1)/ 10)+1
方式,你在做什么不是矩阵转置,但这样做你做的只有更好。)
Good day everybody! I'm currently trying to figure something out in excel before implementing in it VBScript. I have to mathematically transpose a few cells (10*10 or 5r*10c) in a matrice:
------------------------------- | .. | .. | .. | .. | .. | .. | | 21 | 22 | 23 | 24 | 25 | .. | | 11 | 12 | 13 | 14 | 15 | .. | | 1 | 2 | 3 | 4 | 5 | .. | -------------------------------
Must become
------------------------------- | .. | .. | .. | .. | .. | .. | | 3 | 13 | 23 | 33 | 43 | .. | | 2 | 12 | 22 | 32 | 42 | .. | | 1 | 11 | 21 | 31 | 41 | .. | -------------------------------
Now I'm not a mathematician (I'm more ore less a programmer at the moment), but I came up with: F(y)=((MOD(x,10)-1)*10)+(1+((x-MOD(x,10))/10))
(x
is the value in the pre-block a the top, y
is the value in the pre-block below.) Now this works fine up to a certain point (e.g. 10
).
In VBScript, I wrote the below at first:
Function GetPosInSrcRack(Pos) Dim PlateDef(9), x, y, i, tmp ' Plate Definition ReDim tmp(UBound(PlateDef)) For x = 0 To UBound(PlateDef) PlateDef(x) = tmp Next i = 1 For x = 0 To UBound(PlateDef) For y = 0 To UBound(PlateDef(x)) PlateDef(x)(y) = i i = (i + 1) Next Next 'Dim msg ' Check definition 'For x = 0 To (UBound(PlateDef)) ' msg = Join(PlateDef(x), ", ") & vbCrLf & msg 'Next ' Get the Position y = (pos Mod 10) x = ((pos - y) / 10) GetPosInSrcRack = PlateDef(y)(x) End Function
Which, of course, works but is crappy.
Using the above formula I would write:
Function GetPosInSrcRack(Pos) Pos = (((Pos MOD 10)-1)*10)+(1+((Pos - (Pos MOD 10))/10)) End Function
But like I said, this still is incorrect (10 gives -8) Can somebody help me?
y=(MOD(x-1,10))*10+INT((x-1)/10)+1
(By the way, what you are doing is not matrix transposition, but this does do what you do, only better.)
这篇关于数学转置在excel中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!