excel表格行列查找 [英] excel table row column lookup
问题描述
我在使用某些 Excel 函数时遇到了困难.
I'm struggling with some Excel functions.
我有一个带有行标题和列标题的表格,然后是数据.例如:如果我有一种披萨,与其尺寸交叉,数据就是价格.
I have a table with row header and column header and then data. For example: If I have a type of pizza, crossed with its Size, and the data is the price.
S M L XL
Cheese 6 6.5 8 10
Pepperoni 6.5 7.5 10 12
Supreme 8 9 12 15
如果我在 excel 中有这张表,如果我知道我想要奶酪"、M",我该如何查找价格?
If I have this table in excel, how can I do a lookup of the price if I know I want "Cheese", "M"?
行和列标题将是文本,不一定是任何类型的排序顺序.
The row and colmn headers will be text and not necessarily be in any type of sorted order.
谢谢!!
推荐答案
使用索引匹配:
=INDEX(A:E,MATCH("Cheese",A:A,0),MATCH("M",1:1,0))
调用单元格中的数据使其动态:
To call with data in cells so it is dynamic:
假设在 F1 中有 Cheese
,在 G1 中有 M
:
Say in F1 you have Cheese
and in G1 you have M
:
=INDEX(A:E,MATCH(F1,A:A,0),MATCH(G1,1:1,0))
索引公式的第一部分是搜索范围.在这种情况下,完整的 A 到 E 列.
The first part of the Index formula is the Search range. In this case full column A to E.
第二部分表示哪一行.匹配在 A 列中找到Cheese"的第一个实例并返回该行.
The second part denotes what row. The match finds the first instance of "Cheese" in column A and returns that row.
第三部分表示列.该匹配在第 1 行中找到M"并返回列号.
The third part denotes the column. That match finds "M" in Row 1 and returns the column number.
现在索引,使用这些坐标,知道要返回什么.
Now Index, using those coordinates, knows what to return.
这篇关于excel表格行列查找的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!