Excel 2013中多个工作表的索引匹配 [英] Index match over multiple sheets in excel 2013

问题描述

这是我的第一篇文章，如果有什么不合时宜之处，请告诉我，以便我进行纠正.

对于一个项目，我正在构建一个原型，该原型将管道组件文件读取到excel中，提取零件并进行坐标处理并在图形中显示它们. 有了一个宏，该宏使我能够通过单击坐标上的图形来订购零件，因此我可以对零件进行手动排序.在此工作表中，我需要添加数据，例如零件名称和绝对Z值. 这是我的问题所在.

For a project, I'm building a prototype that reads pipeline component files into excel, extracts the parts and coordinates and displays them in a graph. With a macro that gives me the ability to order the parts by clicking in the graph on the coordinates, I sort the parts manually. In this sheet I need to add data like part name and the absolute Z value. Here is where my problem comes in.

到目前为止，我已经能够使用单独的INDEX和MATCH函数从管道数据表之一中调用丢失的信息，但是当我将它们组合在一起时，会返回错误(我有#N/A; #REF和#VALUE入门).

So far I've been able to use a separate INDEX and MATCH function to call my missing information from one of the pipeline datasheets, but when I combine them, the return an error (I've had #N/A; #REF and #VALUE for starters).

我的问题是；

1. 如何构建INDEX MATCH函数以从一张纸返回名称和Z值?

1. how can I build the INDEX MATCH function to return the name and Z value from one sheet?

是否可以嵌套此函数以搜索文件中的所有工作表以返回这些值?

Is it possible to nest this function to search all sheets in the file to return these values?

我将公式和可视化基础知识结合使用，可以使它更具动态性吗?

I use a combination of formulas and visual basics, which one can be used to be more dynamic?

我们将不胜感激:)

背景信息: 我至少有4张带有管道数据的图纸，1张带有单击图形的图纸和1张放置过滤数据的图纸.

Background info: I have at least 4 sheets with pipeline data, 1 sheet with the graph to click on and 1 sheet where the filtered data is placed.

从数据表中调用图形表中的数据.然后，选择图形中的数据并将其复制到Sheet3，其中黑线是从路径中调用的数据，红色是图形表中的数据，蓝色是grpah的一个来源(我用一个数据文件作为开始)(紫色/粉红色，我尝试过的东西). 然后必须从数据表中调用所需的数据，所有数据表都具有相同的格式.

The data from the the graph sheet is called from the datasheets. Then the data in the graph is selected and copied to Sheet3 where the black line is the recall from path, red is data from the graph sheet and blue one source for the grpah (I've used one datafile to start with) (purple/pink, things I've tried). Then the required data has to be called from the datasheet, all the datasheets have the same format.

图片 工作表3

数据表

我确实在多张纸上找到了有关VLOOKUP的一些信息]，但是我无法在我的情况下实现该示例.我还检查了subReddits Excel和Visual Basic中的信息，但收效甚微.

I did find some information about a VLOOKUP across multiple sheets], but I was unable to implement the example in my situation. I've also checked the subReddits Excel and visual basic for information with little success.

推荐答案

借助Reddit网站上的u/semicolonsemicolon.这是我自己的问题的VBA解决方案.为使此代码正常工作，请检查我的工作簿，其中包含我已使用的模块.对于我的解决方案，我使用了一些如何在图表上获取x和y"的约翰·佩尔捷(John peltiers)代码.可以将类模块中的代码交换为以下代码. 感谢您抽出宝贵的时间来检查我的问题.

With the help of u/semicolonsemicolon from the website Reddit. here is the VBA solution to my own problem. For this code to work check my workbook that contains the modules that I've used. For my solution I've used some of john peltiers code on "how to get x andy on your chart". The code in the class module can be swapped for the code below. Thank you for taking the time to check my problem.

Private Sub EmbChart_MouseUp _
    (ByVal Button As Long, ByVal Shift As Long, ByVal X As Long, ByVal Y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Double, myY As Double
Dim rng As Range, t As String

If Button = xlPrimaryButton Then
    With EmbChart
       Call .GetChartElement(X, Y, ElementID, Arg1, Arg2) ' call the coordinates that have been clicked

        Application.StatusBar = "[" & ElementID & "]"

         If ElementID = xlSeries Or ElementID = xlDataLabel Then
                If Arg2 > 0 Then
                    myX = WorksheetFunction.Index(.SeriesCollection(Arg1).XValues, Arg2)
                    myY = WorksheetFunction.Index(.SeriesCollection(Arg1).Values, Arg2)
                    Application.StatusBar = "[" & myX & ", " & myY & "]"
                    Select Case Arg1
                     Case 1: t = "tabbleud"
                     Case 3: t = "tabblad"
                     Case 4: t = "tableeed"
                    End Select
                    With ActiveWorkbook.Worksheets("Sheet3").[B200].End(xlUp).Offset(1, 0)
                          .Value = myX
                          .Offset(0, 1).Value = myY
                          .Offset(0, -1).Value = Evaluate("=INDEX(" & t & "[IDENT name],SUMPRODUCT((" & t & "[X]=" & myX & ")*(" & t & "[Y]=" & myY & ")*(ROW(" & t & "[X])-ROW($A$2)+1)))")
                          .Offset(0, 2).Value = Evaluate("=INDEX(" & t & "[Z],SUMPRODUCT((" & t & "[X]=" & myX & ")*(" & t & "[Y]=" & myY & ")*(ROW(" & t & "[X])-ROW($A$2)+1)))")
                        End With
                End If
        Else
            If TypeName(Selection) = "Nothing" Then
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            ElseIf ElementID = xlShape Then

                'Chart Embedded in Chart Sheet
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            Else
                MsgBox "Chart element " & ElementID _
                    & " (" & Arg1 & ", " & Arg2 & ")."
            End If
        End If

    End With
End If
Application.StatusBar = False
End Sub

这篇关于Excel 2013中多个工作表的索引匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！