Excel中的数学位置函数，用于矩阵查询 [英] Position function of Mathematica in Excel for matrix lookup by value

问题描述

我在Excel中有一个表，列和行标题和相应的值。如何在Excel中查找表中的列和行标题名称/索引值？

在数学中，等同的功能是Position [listoflist，value]

编辑：

---

我在VBA中做了一个简单的功能，但这远远不够完美

 函数MathematicaPosition（Lookvalue As Range，TableRange As Range，RowOrColumn As Boolean）As Integer 
 Dim r As Integer 
 Dim c As Integer 
 Dim tempindex As Integer 
 Dim i As Integer，j As Integer 
 
 tempindex = 0 
r = TableRange.Rows.Count 
c = TableRange.Columns.Count 
对于i = 1到r 
对于j = 1到c 
如果lookvalue.Value = TableRange.Cells（i，j）.Value Then 
 tempindex = IIf（RowOrColumn，i，j）
结束如果
下一个j 
下一个i 
 MathematicaPosition = tempindex 
结束函数
  

解决方案

 函数MathematicaPosition（Lookvalue As Range，TableRange As Range，RowOrColumn As Boolean）As Integer 
 Dim r As Integer 
 Dim c As Integer 
 Dim tempindex As Integer 
 Dim i As Integer，j As Integer 
 
 tempindex = 0 
r = TableRange.Rows。计数
c = TableRange.Columns.Count 
对于i = 1至r 
对于j = 1至c 
如果lookvalue.Value = TableRange.Cells（i，j）.Value然后
 tempindex = IIf（RowOrColumn，i，j）
结束如果
下一个j 
下一个i 
数学位置= tempindex 
结束函数
  

I have a table in Excel with column and row headers and corresponding values. How do I lookup in Excel both the column and row header names/index by value in the table?

In Mathematica the equivalent function is Position[listoflist,value]

EDIT:

---

I made a simple function in VBA, but this is far from perfect

Function MathematicaPosition(lookvalue As Range, TableRange As Range, RowOrColumn As Boolean) As Integer
Dim r As Integer
Dim c As Integer
Dim tempindex As Integer
Dim i As Integer, j As Integer

tempindex = 0
r = TableRange.Rows.Count
c = TableRange.Columns.Count
For i = 1 To r
    For j = 1 To c 
        If lookvalue.Value = TableRange.Cells(i, j).Value Then
            tempindex = IIf(RowOrColumn, i, j)
        End If
    Next j
Next i
MathematicaPosition = tempindex
End Function

解决方案

Function MathematicaPosition(lookvalue As Range, TableRange As Range, RowOrColumn As Boolean) As Integer
Dim r As Integer
Dim c As Integer
Dim tempindex As Integer
Dim i As Integer, j As Integer

tempindex = 0
r = TableRange.Rows.Count
c = TableRange.Columns.Count
For i = 1 To r
    For j = 1 To c 
        If lookvalue.Value = TableRange.Cells(i, j).Value Then
            tempindex = IIf(RowOrColumn, i, j)
        End If
    Next j
Next i
MathematicaPosition = tempindex
End Function

这篇关于Excel中的数学位置函数，用于矩阵查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！