英文:
Multiple condition matched data in Excel
问题
我有一个包含70行的表格。第一列分为7部分，每部分有10行。行从1到10合并成一行，11到20等等，命名为R1、R2等等。
第二列有10个唯一的名称，用于前10行，也就是R1到R10，相同的名称复制到下面的10行，R21到R30，用于R2。
实际数据位于每行的第三列，可以是数字或文本。
我正在创建一个总结的主表。所以我想匹配比如说R3，对应的有10行R31到R40，其中一行是R35。如果R3和R35都匹配，我想获取第三列的值并打印在单元格中。
有什么建议可以实现这个目标吗？
谢谢。
英文:
I have a sheet with 70 rows. The column 1 divided in 7 parts with 10 rows each. The lines are merged from 1 to 10 , 11 to 20.. and so on. and named as R1, R2.. to R7.
The column 2 has 10 unique names for first 10 rows means for R1, let's says R11 to R20, and same names are copied for next 10 rows R21 to R30 means for R2.
The actual data is in 3rd column for each rows and it's a some number/text.
I am creating a master sheet for summary. so I would like to match let's say R3 and for that R3 there are 10 rows R31- R40, so one of them let's R35. If both R3 and R35 match, I would like to get the value from 3rd column and print in the cell.
Any suggestion that How can I do it.
Thanks.
答案1
得分: 2
这些公式将适用于您的列表数据位于A、B、C列的情况。您要从A列匹配的值（例如“R3”）应该在G1中。您要从B列匹配的值（例如“R35”）应该在G2中。
选项1：FILTER 和 CHOOSECOLS
FILTER将基于您想要匹配的值（例如R3和R35）来筛选您的列表。
CHOOSECOLS将允许您只返回第3列。
=CHOOSECOLS(FILTER(A:C,(A:A=G1)*(B:B=G2)),3)
选项2：SUMIFS 会在第1列和第2列匹配的情况下从第3列中添加所有值。只要您的第2列中的值是唯一的，这应该适用于您。
=SUMIFS(C:C,A:A,G1,B:B,G2)
英文:
These formulas will work if your list data is in columns A, B, C. The value you want to match from column A (example "R3") should be in G1. The value you want to match from column B (example "R35") should be in G2
Option 1: FILTER and CHOOSECOLS
FILTER will filter your list based on the values you want to match, for example R3 and R35.
CHOOSECOLS will let you just return column 3.
=CHOOSECOLS(FILTER(A:C,(A:A=G1)*(B:B=G2)),3)
Option 2: SUMIFS will technically add up all the values from column 3, where there is a match in column 1 and column 2. As long as your values in column 2 are unique, this should word for you.
=SUMIFS(C:C,A:A,G1,B:B,G2)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论