时间戳

首页 > 解决方案 > 返回列与所选颜色匹配的行 EXCEL

excel - 返回列与所选颜色匹配的行 EXCEL

问题描述

我相当肯定有一种更优雅的方法可以做到这一点，但我似乎看不到它，或者如果它正盯着我看，我无法理解它。

我在“NEW DB”中有一个数据表，其中列出了 D4:W 中的数字和空白单元格，然后我有一个名为“颜色列表”的表，您可以在其中选择要查找的颜色，然后我想发生什么在“颜色列表”的“公式”部分中列出包含这些特定颜色的每个公式，即纯黑色和纯白色应该返回公式 3 到 20，纯黑色和纯红色应该返回公式 42 到 60。

我感觉它与 INDEX MATCH MATCH 和 ROWS 以及搜索标题有关，但我不知道如何让它们工作，因为我总是收到 #N/A 错误。

应该注意的是，这将扩展到四次搜索。

这是文件的链接，如果有人可以提供帮助，我将不胜感激，因为将这个数据表放在一起需要一段时间

https://mega.nz/file/Oq4xHDCD#6FBZdIVk0Fn8orIiP-DJ2e-ODD_abHtO6k9WwGfJT5c

谢谢

标签： exceldatabaseindexingmatchrows

解决方案

可爱的工作簿。看到这种创作真的很鼓舞人心和有趣:)！

我没有用excel公式解决这个问题，因为你似乎有很多变量，我认为添加更多颜色会变得更加复杂。随意使用我的答案或忽略它;）

但是，我想为您提供一个 VBA 解决方案，它可能会加快工作簿的速度，因为大型工作簿的矩阵计算需要时间来执行......

一些反馈：

  • 您在“新数据库”工作表中有单元格设置，这使得工作簿为 12 mb。从第 15990 行删除行到最后一行，将工作簿从 12 减少到 1.5mb，工作簿也相当快。
  • PWO，你在“新数据库”表中这个词前面有一个空格。
  • 我强烈建议使用 vlookup 在“颜色列表”表中查找颜色的简称。要保持长公式很痛苦，请在单元格 A5 中查看我的建议。
  • 我冒昧地重组了你的工作表的某些部分，我只是觉得这有点令人困惑......
  • 为“Sheet1”中的所有颜色添加了短期名称。我个人认为您应该将其重命名为“映射表”或将内容移至设置。

最后一句话。惊人的工作:)！！

计算公式代码的代码

链接到工作簿

Option Explicit

Sub Calculate_Formula_Code()

Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim Colour1_Col As Long
Dim Colour2_Col As Long
Dim LastRow_New_DB As Long
Dim Result_Start_Row As Long
Dim Colour_Group_Row As Long

Dim i As Long
Dim j As Long

Set ws1 = Sheets("Colour List") 'Define the Colour list sheet
Set ws2 = Sheets("New DB") 'Define the New DB sheet

Result_Start_Row = 10 'Start from row

LastRow_New_DB = ws2.Cells(Rows.Count, 1).End(xlUp).Row 'Find the last row in the New DB Sheet

ws1.Range(ws1.Cells(9, "A"), ws1.Cells(32, "A")).Value = 0 'Clear previous values


If ws1.Cells(5, 2).Value <> "" Then 'If Both Colour 1 and Colour 2 are selected
    Colour1_Col = Application.Match(ws1.Cells(5, 1).Value, ws2.Range("A2:W2"), 0) 'Check which column colour 1 exists in
    Colour2_Col = Application.Match(ws1.Cells(5, 2).Value, ws2.Range("A2:W2"), 0) 'Check which column colour 2 exists in


    For i = 4 To LastRow_New_DB 'Loop from row 4 to last row in the sheet New DB
            If i = 4 And (ws1.Cells(5, 1).Value = "PB" Or ws1.Cells(5, 2).Value = "PB") Then 'Check if any the Colours are black, If yes, then add pure black to 1st row
                    ws1.Cells(Result_Start_Row - 1, "A").Value = ws2.Cells(i, "A").Value
            ElseIf ws2.Cells(i, Colour1_Col).Value <> "" And ws2.Cells(i, Colour2_Col).Value <> "" Then 'Check for the row where there is a value for both of the selected colours
                If i >= 385 Then 'from row 385 the number of rows for a group colour is 19, before it's 18
                    Colour_Group_Row = 18 'How many rows to loop through to add colours
                Else
                    Colour_Group_Row = 19 'How many rows to loop through to add colours
                End If
                For j = i To i + Colour_Group_Row 'For the group, add all the formula code in the Colour list sheet
                    ws1.Cells(Result_Start_Row, "A").Value = ws2.Cells(j, "A").Value 'Copy from New DB sheet to the Colour List sheet
                    Result_Start_Row = Result_Start_Row + 1 'Add one more row for every iteration
                Next j
            Exit Sub
            End If
    Next i

Else 'If only Colour 1 is selected
    Colour1_Col = Application.Match(ws1.Cells(5, 1).Value, ws2.Range("A2:W2"), 0) 'Check which column colour 1 exists in


    For i = 4 To LastRow_New_DB 'Loop from row 4 to last row in the sheet New DB
        If i = 4 And (ws1.Cells(5, 1).Value = "PB" Or ws1.Cells(5, 2).Value = "PB") Then 'Check if any the Colours are black, If yes, then add pure black to 1st row
                ws1.Cells(Result_Start_Row - 1, "A").Value = ws2.Cells(i, "A").Value
        ElseIf ws2.Cells(i, Colour1_Col).Value <> "" Then 'Check for the row where there is a value for both of the selected colours
            If i >= 385 Then 'from row 385 the number of rows for a group colour is 19, before it's 18
                Colour_Group_Row = 18 'How many rows to loop through to add colours
            Else
                Colour_Group_Row = 19 'How many rows to loop through to add colours
            End If
            For j = i To i + Colour_Group_Row 'For the group, add all the formula code in the Colour list sheet
                ws1.Cells(Result_Start_Row, "A").Value = ws2.Cells(j, "A").Value 'Copy from New DB sheet to the Colour List sheet
                Result_Start_Row = Result_Start_Row + 1 'Add one more row for every iteration
            Next j
        Exit Sub
        End If
    Next i
End If

MsgBox ("Complete")

End Sub

推荐阅读