在 Excel 中，针对各班班级干部的资料表(以下图)，来操练INDEX、MATCH、OFFSET函数。

若是要发生下图的查询成果，该若何处置？此中班级名称和干部的名称要用下拉式清单来遴选，而姓名则是以公式来查询。

(1) 界说以下三个名称

班级：贮存格A2:A29；干部：贮存格B1:H1；资料：贮存格A1:H29。

(2) 设定贮存格的资料验证

设定J栏中的贮存格之资料验证为：「贮存格许可：清单；来历：=班级」。

设定K栏中的贮存格之资料验证为：「贮存格许可：清单；来历：=干部」。

(3) 查表找出姓名，试着操练以下三种体例来查表。

<A> 贮存格L2：=INDEX(资料,MATCH(J2,班级,0)+1,MATCH(K2,干部,0)+1)

这是操纵程度和垂直的交叉点来找到资料。

Y = MATCH(J2,班级,0)+1：找到指定的班级名称在第几列(数值)。

X = MATCH(K2,干部,0)+1：找到指定的干部名称在第几栏(数值)。

贮存格L2：=INDEX(资料,Y, X)，Y和X的交会点便是所要的资料(Z)。

<B> 贮存格L2：=OFFSET($A$1,MATCH(J2,班级,0),MATCH(K2,干部,0))

列偏移量 = MATCH(J2,班级,0)：找到指定的班级名称在第几列(数值)。

栏偏移量 = MATCH(K2,干部,0)：找到指定的干部名称在第几栏(数值)。

贮存格L2：=OFFSET($A$1北京拓展公司,列偏移量,栏偏移量)

<C> 贮存格L2：=VLOOKUP(J2,资料,MATCH(K2,干部,0)+1,0)

X = MATCH(K2,干部,0)+1：找到指定的干部名称在第几栏(数值)。

贮存格L2：=VLOOKUP(J2,资料,X,0)

 

相干函数申明，请参阅微软网站申明：

INDEX：weioffice.microsoft.af/zh-tuozhan/excel-help/HP010342608.aspx

INDEX：传回表格或範围内的某个值或值的参照。

语法：INDEX(array, row_num, [column_num]) array：贮存格範围或阵列常数。 row_num：拔取阵列中传回值的列。 column_num：拔取阵列中传回值的栏。

 

MATCH：weioffice.microsoft.af/zh-tuozhan/excel-help/HP010342679.aspx

MATCH：搜索某贮存格範围内的指定项目，然后再传回该项目在範围内的相对位置。

语法：MATCH(lookup_value, lookup_array, [match_type]) lookup_value：在 lookup_array 中寻觅比对的值。 lookup_array：要搜索的贮存格範围。 match_type：这是一个数字，其值有三种可能：(预设值为 1) 1 或省略：找到即是或仅次于 lookup_value 的值。lookup_array 引数内的值必需以递增顺序摆列。 0：找第一个完全即是 lookup_value 的比力值。lookup_array 引数内的值可以依肆意顺序摆列。 -1：找到即是或年夜于 lookup_value 的最小值。lookup_array 引数内的值必需以递减顺序排序。

 

OFFSET：weioffice.microsoft.af/zh-tuozhan/excel-help/HP010342739.aspx

OFFSET 函数：传回按照所指定的贮存格位址、列距及栏距而算出的参照位址。

语法：OFFSET(reference, rows, cols, [height], [width]) reference：用以计较位移的肇端参照位址。 rows：左上角贮存格要往上或往下参照的列数。rows可所以正数(暗示在肇端参照位址下方)或负数(暗示在肇端参照位址上方)。 cols：左上角贮存格要往左或往右参照的栏数。cols 可所以正数(暗示在肇端参照位址右方)或负数(暗示在肇端参照位址左方)。 height：所传回参照位址的高度 (以列数为单元)。height 必需是正数。 width：所传回参照位址的宽度 (以栏数为单元)。width 必需是正数。

 

VLOOKUP：weioffice.microsoft.af/zh-tuozhan/excel-help/HP010343011.aspx

VLOOKUP：用来搜索贮存格範围的第一栏，然后从範围统一列的任何贮存格传回一个值。

语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) lookup_value：在表格或範围的第一栏中搜索的值。 table_array：包括资料的贮存格範围。可使用範围的参照，也能够利用範围名称。 col_index_num：table_array 引数中必需传回符合值的栏号。 range_lookup：这是一个逻辑值，用以指定VLOOKUP应当要寻觅完全合适仍是年夜约合适的值。

创作者先容 vincent

学不完．教不断．用不尽

• 全站分类：不设分类
• 小我分类：课本资料
• 此分类上一篇： Excel-学生体位鉴定
• 此分类下一篇： Excel-挑选分歧位数之数字
• 上一篇： Goggle出的《阅读器和网路世界 20 年夜须知》
• 下一篇： Excel-挑选分歧位数之数字

汗青上的今天

• 2016: Excel-指定部门的栏位计较均匀(SUMPRODUCT,COUNTIF)
• 2015: Google-透过云端列印功能列印手机上的文件和档案
• 2015: Word-让表格中列的文字内容可以跨页显示
• 2014: Word-埋没档案开启时的保持要求／找出文件保持的档案
• 2013: Excel-善用选择性贴上图片功能
• 2013: Excel-将统计图扭转90度
• 2011: Goggle出的《阅读器和网路世界 20 年夜须知》
• 2010: Excel-SUMIF+万用字元

▲top