excel表格省市区三级联动

左手的ㄟ右手 2022-04-23 00:54 823阅读 0赞

# 目录 #

> 想直接使用的直接看编写联动，修改参数

### 文章目录 ###

*  目录
 *   *  主要使用三个表格函数
     *  编写三级联动
     *   *  设置省
         *  设置市
         *  设置区(县)
         *  公式原理说明
         *  附上demo

## 主要使用三个表格函数 ##

1.  VLOOKUP(新吸毒人员登记表!J7,A:B,2,)
    
    百度百科：VLOOKUP函数是Excel中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，在工作中都有广泛应用，例如可以用来核对数据，多个表格之间快速导入数据等函数功能。功能是按列查找，最终返回该列所需查询列序所对应的值；与之对应的HLOOKUP是按行查找的。
    
    参数使用：VLOOKUP(lookup\_value,table\_array,col\_index\_num,range\_lookup)
    
    <table> 
     <thead> 
      <tr> 
       <th>参数</th> 
       <th>简单说明</th> 
       <th>输入数据类型</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>lookup_value</td> 
       <td>要查找的值</td> 
       <td>数值、引用或文本字符串</td> 
      </tr> 
      <tr> 
       <td>table_array</td> 
       <td>要查找的区域</td> 
       <td>数据表区域</td> 
      </tr> 
      <tr> 
       <td>col_index_num</td> 
       <td>返回数据在查找区域的第几列数</td> 
       <td>正整数</td> 
      </tr> 
      <tr> 
       <td>range_lookup</td> 
       <td>模糊匹配/精确匹配</td> 
       <td>TRUE/FALSE（或不填）</td> 
      </tr> 
     </tbody> 
    </table>
2.  MATCH(新吸毒人员登记表!J7,市县!A:A,0)-1
    
    百度百科：匹配函数,指返回指定数值在指定数组区域中的位置。MATCH函数是EXCEL主要的查找函数之一，该函数通常有以下几方面用途：  
    (1)确定列表中某个值的位置；  
    (2)对某个输入值进行检验，确定这个值是否存在某个列表中；  
    (3)判断某列表中是否存在重复数据；  
    (4)定位某一列表中最后一个非空单元格的位置。  
    查找文本值时，函数 MATCH 不区分大小写字母。
    
    语法：MATCH(lookup\_value, lookup\_array, match\_type)
    
    <table> 
     <thead> 
      <tr> 
       <th>参数</th> 
       <th>简单说明</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>lookup_value</td> 
       <td>需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。</td> 
      </tr> 
      <tr> 
       <td>lookup_array</td> 
       <td>可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。</td> 
      </tr> 
      <tr> 
       <td>match_type</td> 
       <td>表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况。为1时，查找小于或等于lookup_value的最大数值在lookup_array中的位置，lookup_array必须按升序排列：否则，当遇到比lookup_value更大的值时，即时终止查找并返回此值之前小于或等于lookup_value的数值的位置。如果lookup_array的值均小于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均大于lookup_value，则返回#N/A。为0时，查找等于lookup_value的第一个数值，loo</td> 
      </tr> 
      <tr> 
       <td>kup_array</td> 
       <td>按任意顺序排列.为-1时，查找大于或等于lookup_value的最小数值。在lookup_array中的位置，lookup_array必须按降序排列；否则，当遇到比lookup_value更小的值时，即时终止查找并返回此值之前大于或等于lookup_value的数值的位置。如果lookup_array的值均大于或等于lookup_value，则返回数组最后一个值的位置；如果lookup_array的值均小于lookup_value，则返回#N/A。</td> 
      </tr> 
     </tbody> 
    </table>
3.  OFFSET(市县!$C$1,MATCH(新吸毒人员登记表!J7,市县!A:A,0)-1,VLOOKUP(新吸毒人员登记表!J7,A:B,2,))
    
    百度百科:在Excel中，offset函数是一个引用函数，表示引用某一个单元格或者区域。其也是我们日常做一些组合函数中的一种
    
    语法:OFFSET(reference,rows,cols,height,width)
    
    接着的2,2分别表示下，右移动的行数和列数，同样向上，左则是负数。最后面的1,1表示引用的区域大小是一行一列，也就是一个单元格。
    
    <table> 
     <thead> 
      <tr> 
       <th>参数</th> 
       <th>简单说明</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td>reference</td> 
       <td>单元格作为参考系</td> 
      </tr> 
      <tr> 
       <td>rows</td> 
       <td>向下移动rows行</td> 
      </tr> 
      <tr> 
       <td>cols</td> 
       <td>向右移动2列</td> 
      </tr> 
      <tr> 
       <td>height</td> 
       <td>引用的区域行大小</td> 
      </tr> 
      <tr> 
       <td>width</td> 
       <td>引用的区域列大小</td> 
      </tr> 
     </tbody> 
    </table>

## 编写三级联动 ##

### 设置省 ###

1.  菜单栏选择公式 --> 名称管理器 --> 新建 --> 名称:省 引用位置选择省一行(包含所有省)
2.  选择需要使用下拉框的格 --> 菜单栏选择数据 --> 点击有效性 --> 选择 允许:序列 数据:介于 来源:=省
3.  设置省设置完毕

### 设置市 ###

1.  菜单栏选择公式 --> 名称管理器 --> 新建 --> 名称:市
2.  引用位置写入:=OFFSET(省市!$A$3,MATCH(Sheet1!**J10**,省市!$1:$1,)-1,HLOOKUP(Sheet1!**J10**,省市!$1:$2,2,))
3.  说明:J10为省(上级联动)的单元格,它是这两个函数的参考值,根据自己需求进行修改即可
4.  选择需要使用下拉框的格 --> 菜单栏选择数据 --> 点击有效性 --> 选择 允许:序列 数据:介于 来源:=市
5.  设置省设置完毕

### 设置区(县) ###

1.  菜单栏选择公式 --> 名称管理器 --> 新建 --> 名称:县
2.  引用位置写入:=OFFSET(市县!$C$1,MATCH(Sheet1!**K10**,市县!H:H,)-1,VLOOKUP(Sheet1!**K10**,市县!H:I,2,))
3.  说明:K10为市(上级联动)的单元格,它是这两个函数的参考值,根据自己需求进行修改即可
4.  选择需要使用下拉框的格 --> 菜单栏选择数据 --> 点击有效性 --> 选择 允许:序列 数据:介于 来源:=县
5.  设置省设置完毕

### 公式原理说明 ###

这个公式主要是利用设置范围来达到下拉项,其中在省市的表格中有一列数字比较关键，通过这个数字来确定取多大范围格。将公式拿下来一个一个比较即可知道其中原理。

如果想要搞清楚建议看着公式定义一个一个撸一次，就能明白其中道理。如果没兴趣那就copy公
    式修改标记的值吧。

### 附上demo ###

[省市区联动demo][demo]

[demo]: https://download.csdn.net/download/qq_28325291/10929510