Excel:从矩阵& quot;方案& quot;中找到正确的匹配项. [英] Excel: Finding the right match from matrix "scheme"

问题描述

我正在尝试将值或样式从一张纸匹配"到另一张纸.我像矩阵方案"(第一个图片)一样，在其中寻找某种标记为"_"和"1"的模式.如果有匹配项("1")，我应该将其涂成红色，而如果没有匹配项("_")，则应涂成绿色.但是那部分很容易.如您所见，目前有4个案例"，这恰恰是我想要在另一张纸上(第二张图片)匹配的内容.但是，即使有匹配项，我也只能得到一个匹配项(从S列("XC0")中获得)，而其他人则获得空白("_").

I am trying to "match" values or patterns from one sheet to another. I got like "matrix scheme" (the first pic) where I am looking for a certain pattern which is labeled as " _ " and "1". If there is a match ("1"), I should color it in red, or green when no match (" _ "). But that part is easy. As you can see there are 4 "cases" for now, and that is exactly what I want to have on another sheet (the second pic) as match. But I am getting only one match (from Column S ("XC0")) and empty spaces (" _ ") for others even if there is a match.

这是我使用此功能的比赛表(下图):

Here is my match table (on picture beneath) where I used this function:

=VLOOKUP($A10;_0528_matrix[#All];MATCH(A$10;_0528_matrix[#Headers];0);0)

索引/匹配也给了我相同的结果.这些 NA错误暂时适用，这就是我稍后要删除的内容.

Index/Match also gave me the same results. These NA errors are fine for now, that's what I am gonna remove later.

对此进行了尝试，但仍然一无所获...

Tried with this and still nothing...

=SUM(INDIRECT("_0528_matrix[" & A2 & "]"))

推荐答案

似乎您需要一个数，而不仅仅是一个匹配项.在这种情况下，请尝试将其放入单元格 A3 :

It seems you need a count, not just a match. In this case, try putting this in the cell A3:

=COUNTIF(INDIRECT("_0528_matrix[" & A2 & "]");"1")

另一个版本:

=COUNTIF(INDEX(Matrix;0;MATCH(A10;Matrix[#Headers];0));"1")

这篇关于Excel:从矩阵& quot;方案& quot;中找到正确的匹配项.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！