August 21, 2010

excel如何自动生成查询?

比方:
sheet1:
A1 B1   C1
孙 1000  @
李 2000  #
蒋 3000  $
问题:
如何在sheet2A1单元格中输入:"孙"时如何在B1和C1位置自动调用生成动在sheet1中对应的1000 @?
在sheet2B1单元格中输入:"李"时如何在B1和C1位置自动调用生成动在sheet1中对应的2000 #?
依次类推?!!


在sheet2表中:
B1输入: =IF($A$1<>"",INDEX(Sheet1!$A$1:$C$3,MATCH($A$1,Sheet1!$A$1:$A$3,0),2),"")
C1输入: =IF($A$1<>"",INDEX(Sheet1!$A$1:$C$3,MATCH($A$1,Sheet1!$A$1:$A$3,0),3),"")
如果出现错误 #N/A,则表示查无此人。 

or

设sheet1数据区域为 A1:C100,即100行。
则sheet2!B1的公式为:
=VLOOKUP($A1,sheet1!$A$1:$C$100,2,FALSE)
C1的公式为:
=VLOOKUP($A1,sheet1!$A$1:$C$100,3,FALSE)
其中公式中数字2、3为数据区域A1:C100中相应列号。

Today's Visitors: 0 Total Visitors: 0
Personal Category: Uncategorized Articles Topic: learning / education / linguistic
[Trackback URL]

Post A Comment









Yes No



Please input the magic number:

( Prevent the annoy garbage messages )
( What if you cannot see the numbers? )
Please input the magic number

誰來收藏
Loading ...
unlog_NVPO 0