行和列的交集，包括if-else语句

So I am making a tolerance calculator for mechanical purposes. I created an Excel sheet with the specified tolerances. With this sheet Excel needs to check which column and which row the user inputs and than return the intersecting value.

所以我做了一个机械公差计算器。我用指定的公差创建了一个Excel表。使用此表，Excel需要检查用户输入的列和行，并返回交叉值。

Now checking the column isn't much of a problem since it exists of hole numbers. However, for the rows Excel needs to check if the value is between the value of B and C and use that row to intersect with.

现在检查列并不是什么问题，因为它存在空穴号。但是，对于行，Excel需要检查值是否在B和C之间，并使用该行与之相交。

My question is if it's possible to use normal Excel formula or do I have to create a macro? Does anyone know a solution?

我的问题是，是否可以使用普通的Excel公式，还是需要创建一个宏?有人知道解决方案吗?

Thanks in advance!

提前谢谢!

2 个解决方案

#1

You might use CHOOSE to select columns D:E, F:H or I:K depending on a, b or c.

您可以根据a、b或c选择D:E、F:H或I:K列。

=INDEX(CHOOSE(MATCH(B2, {"a","b","c"}, 0), D6:E9, F6:H9, I6:K9), MATCH(A2, B6:B9, 1), MATCH(C2, CHOOSE(MATCH(B2, {"a","b","c"}, 0), D5:E5, F5:H5, I5:K5), 1))

The terminating nominal sizes in column C are wholly irrelevant.

列C中的终止尺寸是完全无关的。

#2

You need to use the worksheet change event, to see when a user has made a change. The code below will start you off, and give you the general idea, it needs to go in the relevant sheet module, ie "Sheet1" in the VB editor:

您需要使用工作表更改事件，以查看用户何时进行了更改。下面的代码将会为你开始，并给你一个大致的概念，它需要进入相关的表格模块，即VB编辑器中的“Sheet1”:

Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "Value: " & Target.Value
    Debug.Print "Row: " & Target.Row
    Debug.Print "Col: " & Target.Column
    Debug.Print "Col Header y: " & Cells(Target.Row, 1).Value
    Debug.Print "Row Header x: " & Cells(1, Target.Row).Value
End Sub

You will need to use these values to decide if the user value is correct. You'll need to update the values of 1 for the row and columns with headers. You can then use simple operators to compare them

您将需要使用这些值来决定用户值是否正确。您需要更新带有标题的行和列的1的值。然后可以使用简单的操作符对它们进行比较

#1