下载文档原格式
省市区三级联动菜单（附数据库）<html><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><!----------------------------------------------------------------省的字段是：province--市的字段是：city--县的字段是：area---------------------------------------------------------------------------><mce:script type="text/javascript"><!--/*********定义创建XMLHttpRequest对象的⽅法***************************************/var xmlHttp;//声明变量var requestType="";//声明初始类型为空function createXMLHttpRequest()//定义创建⼀个跨浏览器函数的开头{if(window.ActiveXObject)//ActiveXObject对象到找到的时候返回的是真，否则是假{xmlHttp=new ActiveXObject("Microsoft.XMLHTTP");//这个是使⽤IE的⽅法创建XmlHttp}else if(window.XMLHttpRequest){xmlHttp=new XMLHttpRequest();//这个是使⽤⾮IE的⽅法创建XmlHttp}}/****************判断服务器响应的事件，如果返回是4则说明交互完成，判断标⽰头，**************************************************/function handleStateChange(){//判断返回的⼀个函数，来确定执⾏那个的函数if(xmlHttp.readystate==4){//4说明是执⾏交互完毕0 (未初始化)1 (正在装载)2 (装载完毕) 3 (交互中)4 (完成)if(xmlHttp.status==200){//http的⼀个报头说明成功找到if(type=="city"){//判断查询的类型showcity();//返回响应的显⽰}else if(type="area"){//判断响应的查询的类型showarea();//返回响应的显⽰}}}}/**************************城市的⼀个查询函数**********************************************************/function queryCity(citycode){//执⾏程序查询，查询城市的createXMLHttpRequest();//调⽤创建XmlHttp的函数type="city";//表⽰类型，查询城市处理显⽰的时候需要⽤到var url='data.php?provincecode='+citycode+'&n='+Math.random();//设定URL传值⽅法同时防⽌缓存xmlHttp.open("GET",url,true);//建⽴服务器连接，异步传输treexmlHttp.onreadystatechange=handleStateChange;//处理这个响应所需要的函数xmlHttp.send(null);//执⾏程序函数这⾥的中间的参数是因为GET原因}/**********************县区的⼀个查询函数***********************************************************/function queryArea(citycode){//查询程序createXMLHttpRequest();//调⽤创建XmlHttp的函数type="area";//查询县的var url="data.php?citycode="+citycode+'&n='+Math.random();//设定URL传值⽅法同时防⽌缓存xmlHttp.open("GET",url,true);xmlHttp.onreadystatechange=handleStateChange;//处理响应的函数名xmlHttp.send(null);//执⾏程序函数这⾥的中间的参数是因为GET原因}/*********************⼀个显⽰函数**********************************************************/function showcity(){//显⽰函数document.getElementById("city").innerHTML=xmlHttp.responseText;//捕获ID显⽰返回的数据}function showarea(){document.getElementById("area").innerHTML=xmlHttp.responseText;//捕获ID显⽰返回的数据}$conn=mysql_connect("localhost","root","");//链接数据库mysql_select_db("novel");//选择数据库mysql_query("set names 'utf8'");//设定字符集$sql="select * from province";//查询数据库province表也就是省$result=mysql_query($sql);//执⾏语句赋值给变量><from id='form1'><!--输出表单头--><!--输出下拉列表框，并设定onchange响应事件，把省值传递过去--><select id='province' onchange='queryCity(this.options[this.selectedIndex].value)'><!--输出下拉列表项值--><option value='-1' selected>请选择省份</option><?phpwhile($row=mysql_fetch_row($result)){//循环循环查询显⽰省输出数据显⽰echo "<option value='$row[1]'>$row[2]</option>/n";}></select><!--下拉列表项尾数--><span id='city'></span><!--显⽰数据的城市的位置--><span id='area'></span><!--显⽰数据的城市的位置--></form><!--表单项结尾--></body></html><?php$provincecode=$_GET['provincecode'];//接收省键值$citycode=$_GET['citycode'];//接收城市键值$conn=mysql_connect("localhost","root","");//连接主机mysql_select_db("novel");//选择数据库mysql_query("set names 'utf8'");if($provincecode!=""){//如果传递过来的不为空则执⾏$sql="select * from city where provincecode=$provincecode";//查询城市符合属于上边传递过来的省的字段 $result=mysql_query($sql);//执⾏SQL查询语句print_r($row);><select onchange='queryArea(this.options[this.selectedIndex].value)'><!--下拉列表框开头--><option value='-1' selected>请选择城市</option><!--下拉列表框值开头--><?php while($row=mysql_fetch_row($result)){//循环记录集?><option value="<?= $row[1]?>"><?=$row[2]?></option><?php }?></select><?php }?><?phpif($citycode!=""){$sql="select * from area where citycode=$citycode";$result=mysql_query($sql);echo "<select>/n";echo "<option value='-1' selected>请选择县</option>/n";while($row=mysql_fetch_row($result)){echo "<option value='$row[1]'>$row[2]</option>/n";}echo "</select>/n";}>-- phpMyAdmin SQL Dump-- version 2.11.2-- ---- 主机: localhost-- ⽣成⽇期: 2008 年 07 ⽉ 11 ⽇ 00:45-- 服务器版本: 5.0.27-- PHP 版本: 5.2.0SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";---- 数据库: `novel`--CREATE DATABASE `novel` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `novel`;-- 表的结构 `area`--CREATE TABLE IF NOT EXISTS `area` (`id` int(11) NOT NULL auto_increment,`code` varchar(6) NOT NULL,`name` varchar(20) NOT NULL,`citycode` varchar(6) NOT NULL,PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3145 ; ---- 导出表中的数据 `area`--INSERT INTO `area` (`id`, `code`, `name`, `citycode`) VALUES(1, '110101', '东城区', '110100'),(2, '110102', '西城区', '110100'),(3, '110103', '崇⽂区', '110100'),(4, '110104', '宣武区', '110100'),(5, '110105', '朝阳区', '110100'),(6, '110106', '丰台区', '110100'),(7, '110107', '⽯景⼭区', '110100'),(8, '110108', '海淀区', '110100'),(9, '110109', '门头沟区', '110100'),(10, '110111', '房⼭区', '110100'),(11, '110112', '通州区', '110100'),(12, '110113', '顺义区', '110100'),(13, '110114', '昌平区', '110100'),(14, '110115', '⼤兴区', '110100'),(15, '110116', '怀柔区', '110100'),(16, '110117', '平⾕区', '110100'),(17, '110228', '密云县', '110200'),(18, '110229', '延庆县', '110200'),(19, '120101', '和平区', '120100'),(20, '120102', '河东区', '120100'),(21, '120103', '河西区', '120100'),(22, '120104', '南开区', '120100'),(23, '120105', '河北区', '120100'),(24, '120106', '红桥区', '120100'),(25, '120107', '塘沽区', '120100'),(26, '120108', '汉沽区', '120100'),(27, '120109', '⼤港区', '120100'),(28, '120110', '东丽区', '120100'),(29, '120111', '西青区', '120100'),(30, '120112', '津南区', '120100'),(31, '120113', '北⾠区', '120100'),(32, '120114', '武清区', '120100'),(33, '120115', '宝坻区', '120100'),(34, '120221', '宁河县', '120200'),(35, '120223', '静海县', '120200'),(36, '120225', '蓟 县', '120200'),(37, '130101', '市辖区', '130100'),(38, '130102', '长安区', '130100'),(39, '130103', '桥东区', '130100'),(40, '130104', '桥西区', '130100'),(41, '130105', '新华区', '130100'),(42, '130107', '井陉矿区', '130100'),(43, '130108', '裕华区', '130100'),(44, '130121', '井陉县', '130100'),(45, '130123', '正定县', '130100'),(46, '130124', '栾城县', '130100'),(47, '130125', '⾏唐县', '130100'),(48, '130126', '灵寿县', '130100'),(49, '130127', '⾼⾢县', '130100'),(50, '130128', '深泽县', '130100'),(51, '130129', '赞皇县', '130100'),(52, '130130', '⽆极县', '130100'),(53, '130131', '平⼭县', '130100'),(54, '130132', '元⽒县', '130100'),(55, '130133', '赵 县', '130100'),(56, '130181', '⾟集市', '130100'),(57, '130182', '藁城市', '130100'),(58, '130183', '晋州市', '130100'),(59, '130184', '新乐市', '130100'),(60, '130185', '⿅泉市', '130100'),(61, '130201', '市辖区', '130200'),(62, '130202', '路南区', '130200'),(63, '130203', '路北区', '130200'),(64, '130204', '古冶区', '130200'),(73, '130230', '唐海县', '130200'), (74, '130281', '遵化市', '130200'), (75, '130283', '迁安市', '130200'), (76, '130301', '市辖区', '130300'), (77, '130302', '海港区', '130300'), (78, '130303', '⼭海关区', '130300'), (79, '130304', '北戴河区', '130300'), (80, '130321', '青龙满族⾃治县', '130300'), (81, '130322', '昌黎县', '130300'), (82, '130323', '抚宁县', '130300'), (83, '130324', '卢龙县', '130300'), (84, '130401', '市辖区', '130400'), (85, '130402', '邯⼭区', '130400'), (86, '130403', '丛台区', '130400'), (87, '130404', '复兴区', '130400'), (88, '130406', '峰峰矿区', '130400'), (89, '130421', '邯郸县', '130400'), (90, '130423', '临漳县', '130400'), (91, '130424', '成安县', '130400'), (92, '130425', '⼤名县', '130400'), (93, '130426', '涉 县', '130400'), (94, '130427', '磁 县', '130400'), (95, '130428', '肥乡县', '130400'), (96, '130429', '永年县', '130400'), (97, '130430', '邱 县', '130400'), (98, '130431', '鸡泽县', '130400'), (99, '130432', '⼴平县', '130400'), (100, '130433', '馆陶县', '130400'), (101, '130434', '魏 县', '130400'), (102, '130435', '曲周县', '130400'), (103, '130481', '武安市', '130400'), (104, '130501', '市辖区', '130500'), (105, '130502', '桥东区', '130500'), (106, '130503', '桥西区', '130500'), (107, '130521', '邢台县', '130500'), (108, '130522', '临城县', '130500'), (109, '130523', '内丘县', '130500'), (110, '130524', '柏乡县', '130500'), (111, '130525', '隆尧县', '130500'), (112, '130526', '任 县', '130500'), (113, '130527', '南和县', '130500'), (114, '130528', '宁晋县', '130500'), (115, '130529', '巨⿅县', '130500'), (116, '130530', '新河县', '130500'), (117, '130531', '⼴宗县', '130500'), (118, '130532', '平乡县', '130500'), (119, '130533', '威 县', '130500'), (120, '130534', '清河县', '130500'), (121, '130535', '临西县', '130500'), (122, '130581', '南宫市', '130500'), (123, '130582', '沙河市', '130500'), (124, '130601', '市辖区', '130600'), (125, '130602', '新市区', '130600'), (126, '130603', '北市区', '130600'), (127, '130604', '南市区', '130600'), (128, '130621', '满城县', '130600'), (129, '130622', '清苑县', '130600'), (130, '130623', '涞⽔县', '130600'), (131, '130624', '⾩平县', '130600'), (132, '130625', '徐⽔县', '130600'), (133, '130626', '定兴县', '130600'), (134, '130627', '唐 县', '130600'), (135, '130628', '⾼阳县', '130600'), (136, '130629', '容城县', '130600'), (137, '130630', '涞源县', '130600'), (138, '130631', '望都县', '130600'), (139, '130632', '安新县', '130600'), (140, '130633', '易 县', '130600'), (141, '130634', '曲阳县', '130600'), (142, '130635', '蠡 县', '130600'), (143, '130636', '顺平县', '130600'), (144, '130637', '博野县', '130600'), (145, '130638', '雄 县', '130600'), (146, '130681', '涿州市', '130600'), (147, '130682', '定州市', '130600'), (148, '130683', '安国市', '130600'),(157, '130723', '康保县', '130700'),(158, '130724', '沽源县', '130700'),(159, '130725', '尚义县', '130700'),(160, '130726', '蔚 县', '130700'),(161, '130727', '阳原县', '130700'),(162, '130728', '怀安县', '130700'),(163, '130729', '万全县', '130700'),(164, '130730', '怀来县', '130700'),(165, '130731', '涿⿅县', '130700'),(166, '130732', '⾚城县', '130700'),(167, '130733', '崇礼县', '130700'),(168, '130801', '市辖区', '130800'),(169, '130802', '双桥区', '130800'),(170, '130803', '双滦区', '130800'),(171, '130804', '鹰⼿营⼦矿区', '130800'), (172, '130821', '承德县', '130800'),(173, '130822', '兴隆县', '130800'),(174, '130823', '平泉县', '130800'),(175, '130824', '滦平县', '130800'),(176, '130825', '隆化县', '130800'),(177, '130826', '丰宁满族⾃治县', '130800'), (178, '130827', '宽城满族⾃治县', '130800'), (179, '130828', '围场满族蒙古族⾃治县', '130800'), (180, '130901', '市辖区', '130900'),(181, '130902', '新华区', '130900'),(182, '130903', '运河区', '130900'),(183, '130921', '沧 县', '130900'),(184, '130922', '青 县', '130900'),(185, '130923', '东光县', '130900'),(186, '130924', '海兴县', '130900'),(187, '130925', '盐⼭县', '130900'),(188, '130926', '肃宁县', '130900'),(189, '130927', '南⽪县', '130900'),(190, '130928', '吴桥县', '130900'),(191, '130929', '献 县', '130900'),(192, '130930', '孟村回族⾃治县', '130900'), (193, '130981', '泊头市', '130900'),(194, '130982', '任丘市', '130900'),(195, '130983', '黄骅市', '130900'),(196, '130984', '河间市', '130900'),(197, '131001', '市辖区', '131000'),(198, '131002', '安次区', '131000'),(199, '131003', '⼴阳区', '131000'),(200, '131022', '固安县', '131000'),(201, '131023', '永清县', '131000'),(202, '131024', '⾹河县', '131000'),(203, '131025', '⼤城县', '131000'),(204, '131026', '⽂安县', '131000'),(205, '131028', '⼤⼚回族⾃治县', '131000'), (206, '131081', '霸州市', '131000'),(207, '131082', '三河市', '131000'),(208, '131101', '市辖区', '131100'),(209, '131102', '桃城区', '131100'),(210, '131121', '枣强县', '131100'),(211, '131122', '武⾢县', '131100'),(212, '131123', '武强县', '131100'),(213, '131124', '饶阳县', '131100'),(214, '131125', '安平县', '131100'),(215, '131126', '故城县', '131100'),(216, '131127', '景 县', '131100'),(217, '131128', '⾩城县', '131100'),(218, '131181', '冀州市', '131100'),(219, '131182', '深州市', '131100'),(220, '140101', '市辖区', '140100'),(221, '140105', '⼩店区', '140100'),(222, '140106', '迎泽区', '140100'),(223, '140107', '杏花岭区', '140100'),(224, '140108', '尖草坪区', '140100'),(225, '140109', '万柏林区', '140100'),(226, '140110', '晋源区', '140100'),(227, '140121', '清徐县', '140100'),(228, '140122', '阳曲县', '140100'),(229, '140123', '娄烦县', '140100'),(230, '140181', '古交市', '140100'),(231, '140201', '市辖区', '140200'),(232, '140202', '城 区', '140200'),(241, '140226', '左云县', '140200'), (242, '140227', '⼤同县', '140200'), (243, '140301', '市辖区', '140300'), (244, '140302', '城 区', '140300'), (245, '140303', '矿 区', '140300'), (246, '140311', '郊 区', '140300'), (247, '140321', '平定县', '140300'), (248, '140322', '盂 县', '140300'), (249, '140401', '市辖区', '140400'), (250, '140402', '城 区', '140400'), (251, '140411', '郊 区', '140400'), (252, '140421', '长治县', '140400'), (253, '140423', '襄垣县', '140400'), (254, '140424', '屯留县', '140400'), (255, '140425', '平顺县', '140400'), (256, '140426', '黎城县', '140400'), (257, '140427', '壶关县', '140400'), (258, '140428', '长⼦县', '140400'), (259, '140429', '武乡县', '140400'), (260, '140430', '沁 县', '140400'), (261, '140431', '沁源县', '140400'), (262, '140481', '潞城市', '140400'), (263, '140501', '市辖区', '140500'), (264, '140502', '城 区', '140500'), (265, '140521', '沁⽔县', '140500'), (266, '140522', '阳城县', '140500'), (267, '140524', '陵川县', '140500'), (268, '140525', '泽州县', '140500'), (269, '140581', '⾼平市', '140500'), (270, '140601', '市辖区', '140600'), (271, '140602', '朔城区', '140600'), (272, '140603', '平鲁区', '140600'), (273, '140621', '⼭阴县', '140600'), (274, '140622', '应 县', '140600'), (275, '140623', '右⽟县', '140600'), (276, '140624', '怀仁县', '140600'), (277, '140701', '市辖区', '140700'), (278, '140702', '榆次区', '140700'), (279, '140721', '榆社县', '140700'), (280, '140722', '左权县', '140700'), (281, '140723', '和顺县', '140700'), (282, '140724', '昔阳县', '140700'), (283, '140725', '寿阳县', '140700'), (284, '140726', '太⾕县', '140700'), (285, '140727', '祁 县', '140700'), (286, '140728', '平遥县', '140700'), (287, '140729', '灵⽯县', '140700'), (288, '140781', '介休市', '140700'), (289, '140801', '市辖区', '140800'), (290, '140802', '盐湖区', '140800'), (291, '140821', '临猗县', '140800'), (292, '140822', '万荣县', '140800'), (293, '140823', '闻喜县', '140800'), (294, '140824', '稷⼭县', '140800'), (295, '140825', '新绛县', '140800'), (296, '140826', '绛 县', '140800'), (297, '140827', '垣曲县', '140800'), (298, '140828', '夏 县', '140800'), (299, '140829', '平陆县', '140800'), (300, '140830', '芮城县', '140800'), (301, '140881', '永济市', '140800'), (302, '140882', '河津市', '140800'), (303, '140901', '市辖区', '140900'), (304, '140902', '忻府区', '140900'), (305, '140921', '定襄县', '140900'), (306, '140922', '五台县', '140900'), (307, '140923', '代 县', '140900'), (308, '140924', '繁峙县', '140900'), (309, '140925', '宁武县', '140900'), (310, '140926', '静乐县', '140900'), (311, '140927', '神池县', '140900'), (312, '140928', '五寨县', '140900'), (313, '140929', '岢岚县', '140900'), (314, '140930', '河曲县', '140900'), (315, '140931', '保德县', '140900'), (316, '140932', '偏关县', '140900'),(325, '141026', '安泽县', '141000'),(326, '141027', '浮⼭县', '141000'),(327, '141028', '吉 县', '141000'),(328, '141029', '乡宁县', '141000'),(329, '141030', '⼤宁县', '141000'),(330, '141031', '隰 县', '141000'),(331, '141032', '永和县', '141000'),(332, '141033', '蒲 县', '141000'),(333, '141034', '汾西县', '141000'),(334, '141081', '侯马市', '141000'),(335, '141082', '霍州市', '141000'),(336, '141101', '市辖区', '141100'),(337, '141102', '离⽯区', '141100'),(338, '141121', '⽂⽔县', '141100'),(339, '141122', '交城县', '141100'),(340, '141123', '兴 县', '141100'),(341, '141124', '临 县', '141100'),(342, '141125', '柳林县', '141100'),(343, '141126', '⽯楼县', '141100'),(344, '141127', '岚 县', '141100'),(345, '141128', '⽅⼭县', '141100'),(346, '141129', '中阳县', '141100'),(347, '141130', '交⼝县', '141100'),(348, '141181', '孝义市', '141100'),(349, '141182', '汾阳市', '141100'),(350, '150101', '市辖区', '150100'),(351, '150102', '新城区', '150100'),(352, '150103', '回民区', '150100'),(353, '150104', '⽟泉区', '150100'),(354, '150105', '赛罕区', '150100'),(355, '150121', '⼟默特左旗', '150100'), (356, '150122', '托克托县', '150100'),(357, '150123', '和林格尔县', '150100'), (358, '150124', '清⽔河县', '150100'),(359, '150125', '武川县', '150100'),(360, '150201', '市辖区', '150200'),(361, '150202', '东河区', '150200'),(362, '150203', '昆都仑区', '150200'),(363, '150204', '青⼭区', '150200'),(364, '150205', '⽯拐区', '150200'),(365, '150206', '⽩云矿区', '150200'),(366, '150207', '九原区', '150200'),(367, '150221', '⼟默特右旗', '150200'), (368, '150222', '固阳县', '150200'),(369, '150223', '达尔罕茂明安联合旗', '150200'), (370, '150301', '市辖区', '150300'),(371, '150302', '海勃湾区', '150300'),(372, '150303', '海南区', '150300'),(373, '150304', '乌达区', '150300'),(374, '150401', '市辖区', '150400'),(375, '150402', '红⼭区', '150400'),(376, '150403', '元宝⼭区', '150400'),(377, '150404', '松⼭区', '150400'),(378, '150421', '阿鲁科尔沁旗', '150400'), (379, '150422', '巴林左旗', '150400'),(380, '150423', '巴林右旗', '150400'),(381, '150424', '林西县', '150400'),(382, '150425', '克什克腾旗', '150400'), (383, '150426', '翁⽜特旗', '150400'),(384, '150428', '喀喇沁旗', '150400'),(385, '150429', '宁城县', '150400'),(386, '150430', '敖汉旗', '150400'),(387, '150501', '市辖区', '150500'),(388, '150502', '科尔沁区', '150500'),(389, '150521', '科尔沁左翼中旗', '150500'), (390, '150522', '科尔沁左翼后旗', '150500'), (391, '150523', '开鲁县', '150500'),(392, '150524', '库伦旗', '150500'),(393, '150525', '奈曼旗', '150500'),(394, '150526', '扎鲁特旗', '150500'),(395, '150581', '霍林郭勒市', '150500'), (396, '150602', '东胜区', '150600'),(397, '150621', '达拉特旗', '150600'),(398, '150622', '准格尔旗', '150600'),(399, '150623', '鄂托克前旗', '150600'), (400, '150624', '鄂托克旗', '150600'),(407, '150722', '莫⼒达⽡达斡尔族⾃治旗', '150700'), (408, '150723', '鄂伦春⾃治旗', '150700'),(409, '150724', '鄂温克族⾃治旗', '150700'), (410, '150725', '陈巴尔虎旗', '150700'),(411, '150726', '新巴尔虎左旗', '150700'),(412, '150727', '新巴尔虎右旗', '150700'),(413, '150781', '满洲⾥市', '150700'),(414, '150782', '⽛克⽯市', '150700'),(415, '150783', '扎兰屯市', '150700'),(416, '150784', '额尔古纳市', '150700'),(417, '150785', '根河市', '150700'),(418, '150801', '市辖区', '150800'),(419, '150802', '临河区', '150800'),(420, '150821', '五原县', '150800'),(421, '150822', '磴⼝县', '150800'),(422, '150823', '乌拉特前旗', '150800'),(423, '150824', '乌拉特中旗', '150800'),(424, '150825', '乌拉特后旗', '150800'),(425, '150826', '杭锦后旗', '150800'),(426, '150901', '市辖区', '150900'),(427, '150902', '集宁区', '150900'),(428, '150921', '卓资县', '150900'),(429, '150922', '化德县', '150900'),(430, '150923', '商都县', '150900'),(431, '150924', '兴和县', '150900'),(432, '150925', '凉城县', '150900'),(433, '150926', '察哈尔右翼前旗', '150900'), (434, '150927', '察哈尔右翼中旗', '150900'), (435, '150928', '察哈尔右翼后旗', '150900'), (436, '150929', '四⼦王旗', '150900'),(437, '150981', '丰镇市', '150900'),(438, '152201', '乌兰浩特市', '152200'),(439, '152202', '阿尔⼭市', '152200'),(440, '152221', '科尔沁右翼前旗', '152200'), (441, '152222', '科尔沁右翼中旗', '152200'), (442, '152223', '扎赉特旗', '152200'),(443, '152224', '突泉县', '152200'),(444, '152501', '⼆连浩特市', '152500'),(445, '152502', '锡林浩特市', '152500'),(446, '152522', '阿巴嘎旗', '152500'),(447, '152523', '苏尼特左旗', '152500'),(448, '152524', '苏尼特右旗', '152500'),(449, '152525', '东乌珠穆沁旗', '152500'),(450, '152526', '西乌珠穆沁旗', '152500'),(451, '152527', '太仆寺旗', '152500'),(452, '152528', '镶黄旗', '152500'),(453, '152529', '正镶⽩旗', '152500'),(454, '152530', '正蓝旗', '152500'),(455, '152531', '多伦县', '152500'),(456, '152921', '阿拉善左旗', '152900'),(457, '152922', '阿拉善右旗', '152900'),(458, '152923', '额济纳旗', '152900'),(459, '210101', '市辖区', '210100'),(460, '210102', '和平区', '210100'),(461, '210103', '沈河区', '210100'),(462, '210104', '⼤东区', '210100'),(463, '210105', '皇姑区', '210100'),(464, '210106', '铁西区', '210100'),(465, '210111', '苏家屯区', '210100'),(466, '210112', '东陵区', '210100'),(467, '210113', '新城⼦区', '210100'),(468, '210114', '于洪区', '210100'),(469, '210122', '辽中县', '210100'),(470, '210123', '康平县', '210100'),(471, '210124', '法库县', '210100'),(472, '210181', '新民市', '210100'),(473, '210201', '市辖区', '210200'),(474, '210202', '中⼭区', '210200'),(475, '210203', '西岗区', '210200'),(476, '210204', '沙河⼝区', '210200'),(477, '210211', '⽢井⼦区', '210200'),(478, '210212', '旅顺⼝区', '210200'),(479, '210213', '⾦州区', '210200'),(480, '210224', '长海县', '210200'),(481, '210281', '⽡房店市', '210200'),(482, '210282', '普兰店市', '210200'),(483, '210283', '庄河市', '210200'),(484, '210301', '市辖区', '210300'),。
excel省市区三级分类级联前⾔：同事正好需要⼀个这样的地址类型给⽤户使⽤下载模板，改好地址再导⼊，这样就不会出现地址不匹配问题。
所以就⾃⼰也整理了⼀套，以备不时之需。
效果展⽰：图⼀：省级图⼆：市级图三：区级图四：各乡镇当更换⼀级菜单时⼆、三、四级会提⽰更新级联选项，否则提⽰错误。
依次类推，⼆级改变时，三、四级提⽰错误⾸先，要实现上⾯效果，需要⼆⼤部：第⼀部：准备做级联的数据源；(主要就是看你的数据来源，是通过数据库还是其它)第⼆部：excel函数及规则应⽤；(主要使⽤OFFSET, MATCH, CONTIF),即可实现上⾯效果。
在使⽤上⾯excel函数之前，⾸先要熟悉⼀下⼏个函数的作⽤，如果已经知道可忽律OFFSET(reference,rows,cols,height,width)Reference 作为偏移量参照系的引⽤区域。
Reference 必须为对单元格或相连单元格区域的引⽤；否则，函数 OFFSET 返回错误值#VALUE!。
Rows相对于偏移量参照系的左上⾓单元格，上（下）偏移的⾏数。
如果使⽤ 5 作为参数 Rows，则说明⽬标引⽤区域的左上⾓单元格⽐ reference 低 5 ⾏。
⾏数可为正数（代表在起始引⽤的下⽅）或负数（代表在起始引⽤的上⽅）。
Cols 相对于偏移量参照系的左上⾓单元格，左（右）偏移的列数。
如果使⽤ 5 作为参数 Cols，则说明⽬标引⽤区域的左上⾓的单元格⽐ reference 靠右 5 列。
列数可为正数（代表在起始引⽤的右边）或负数（代表在起始引⽤的左边）。
Height⾼度，即所要返回的引⽤区域的⾏数。
Height 必须为正数。
不可为负Width宽度，即所要返回的引⽤区域的列数。
Width 必须为正数。
不可为负offset 在汇编中可以获得操作数的偏移地址。
⽤法：MATCH(lookup_value, lookup_array, match_type)lookup_value：需要在数据表（lookup_array）中查找的值。
我写的全国省市县三级联动菜单，拿出来和大家分享了（原创）$(function() { var areaPicker = newAreaPicker({provId:"province",cityId:"city",townId:"town",url :"ajax_search_area.jsp"}); areaPicker.pick(); selectid="province" /select select id="city" /select selectid="town" /select ajax页面（数据源） %@ page language="java"pageEncoding="UTF-8"% %@page import="java.sql.*"% private ic Connection conn; private static String driver ="com.mysql.jdbc.Driver"; // 数据库驱动 private static String ulr = "jdbc:mysql://172.16.1.115:3306/test"; private static String username = "root"; // 数据库的用户名 private static String = "root";// 数据库的密码 static { try{ Class.forName(driver); conn = DriverManager.getConnection(ulr, username, pwd);conn.AutoCommit(false); } ch (ClassNotFoundException e){ e.printStackTrace(); System.exit(-1); } catch (SQLException e) { e.printStackTrace(); System.exit(-1); //清除缓存 response.setHeader("Pragma","No-cache");response.setHeader("Cache-Control","no-cache");response.setDateHeader("Expires", 0); out.(); //接收参数 int level = -1; String levelStr = request.getParameter("level"); if(levelStr!=null levelStr.matches("^\\d+$")) level = Integer.parseInt(levelStr); String parentidStr =request.getParameter("parentid"); long parentid = -1;if(parentidStr!=null parentidStr.matches("^\\d+$"))parentid = Long.parseLong(parentidStr); //查询数据 if(level 0 parentid 0) return; if(level =0 parentid 0) { String sql ="select id,name from china_area where level=? order by id asc;"; PreparStatement ps = conn.prepareStatement(sql); ps.setInt(1, level); ResultSet rs = ps.exeeQuery(); StringBuffer sb = new StringBuffer(); while (rs.next()){ sb.append(";"+rs.getLong("id")+","+rs.getString("name")); rs.close(); ps.close(); if(sb.length() 0) sb.delete(0, 1); out.print(sb.toString()); if(level 0 parentid =0) { String sql = "select id,name from china_area where parentid=? order by id asc;"; PreparedStatement ps = conn.prepareStatement(sql); ps.setLong(1, parentid); ResultSet rs = ps.executeQuery(); StringBuffer sb = new StringBuffer(); while (rs.next()){ sb.append(";"+rs.getLong("id")+","+rs.getString("name")); rs.close(); ps.close(); if(sb.length() 0) sb.delete(0, 1); out.print(sb.toString()); if(level =0 parentid =0) { String sql = "select id,name from china_area where level=? and parentid=? order by id asc;"; PreparedStatement ps =conn.prepareStatement(sql); ps.setInt(1, level);ps.setLong(2, parentid); ResultSet rs = ps.executeQuery(); StringBuffer sb = new StringBuffer(); while (rs.next()){ sb.append(";"+rs.getLong("id")+","+rs.getString("name")); rs.close(); ps.close(); if(sb.length() 0) sb.delete(0, 1); out.print(sb.toString());。
【Excel技巧】制作⼀级、⼆级、三级联动下拉菜单
使⽤数据有效性制作⼀级下拉菜单对⼤多数⼈来说并不陌⽣，但你知道如何制作⼆级、三级联
动的下拉菜单吗？
如下⾯动图所⽰，当在A11单元格选择不同省份时，单元格B11、C11下拉菜单对应的市和区也
随之变化。
本⽂将依次介绍如何制作⼀级、⼆级、三级下拉菜单。
⼀、制作⼀级下拉菜单
选中A2：D2单元格区域，单击【公式】选项卡下的【名称管理器】按钮，“名称”框内输⼊“省
份”。
选中A11单元格，点击【数据】选项卡下的【数据验证】按钮，在弹出的对话框中，“允许”选
择“序列”，来源输⼊公式“=省份”，点击确定。
设置完成后，A11单元格右下⾓出现倒三⾓符号，点击倒三⾓，就会出现列出各省名称的下拉菜
单。
⼆、制作⼆级下拉菜单
选中A2：D7单元格区域，单击【公式】选项卡下的【根据所选内容创建】按钮，勾选“⾸⾏”，
点击确定。
选中B11单元格，点击【数据】选项卡下的【数据验证】按钮，在弹出的对话框中，“允许”选
择“序列”，来源输⼊公式“=indirect(A11)”，点击确定。
三、制作三级下拉菜单
选中F2：L17单元格区域，单击【公式】选项卡下的【根据所选内容创建】按钮，勾选“最左
列”，点击确定。
选中C11单元格，点击【数据】选项卡下的【数据验证】按钮，在弹出的对话框中，“允许”选
择“序列”，来源输⼊公式“=indirect(B11)”，点击确定。
通过以上设置，就可以出现本⽂开头展⽰的三级联动下拉菜单。
总结：
制作下拉菜单需要⽤到“数据验证”功能，结合indirect()函数，“数据验证”功能变得更为强⼤。
省市区三级联动数据库代码（国家统计局最新代码、包含整理步骤）最终整理后的成果图。
最近项⽬要⽤到省市区地址三级联动。
原有的数据是⽼版本的。
在⽹上找了找，发现很多，也很杂。
最终决定⾃⼰整理⼀份，就当练⼿了。
看到的数据只有代码和名称两列数据如：110000 北京市110100 市辖区110101 东城区去空格整理后复制到excel 表中供导⼊数据库1、⾸先要把地址分出 1 、2 、3 级代表省、市、区。
那么问题来了。
原先想依据名称列的缩进来进⾏区分，但是想想有点复杂，⽽且容易出错。
后来⽤地区的编码规律来区分编码为 6 个字符组成例如：110101⾸先把表结构建起来吧。
从Excel表导⼊数据吧,这个就不说咯现在开始了！如果后4位为"0000" ---------------------是省级 1UPDATE[region]SET level=1WHERE SUBSTRING(code,3,6) ='0000'GO如果后2位为"00" 和等级level 不等于1----------是市级 2UPDATE[Test].[dbo].[region]SET[level]=2WHERE SUBSTRING(code,5,6) ='00'and ([level]<>1 ro [level] is null)GO备注：什么？执⾏了没效果？先把 level 字段赋值为 0 吧。
UPDATE [region]SET level = 0WHERE level is nullGO其他的 -----------------------------------是区级 3UPDATE[region]SET level=3WHERE level = 0GO2、现在我们来把⽗ID 整理出来也是依靠地址编码看代码就懂了啦省级UPDATE[region]SET parent_id =0WHERE level=1GO市级UPDATE aSET a.[parent_id]= b.[id]from[region] a,[region] bWHERE SUBSTRING(a.[code],1,2) =SUBSTRING(b.[code],1,2) and a.[level]=2and b.[level]=1GO区级UPDATE aSET a.[parent_id]= b.[id]from[region] a,[region] bWHERE SUBSTRING(a.[code],1,4) =SUBSTRING(b.[code],1,4) and a.[level]=3and b.[level]=2GO3、整理地区全称（这个字段有必要整理出来，原因嘛，选择联动地区获得省市区地址名称的时候会有意外的惊喜！例如：北京市市辖区XXX区，北京市县XXX县虽然没错但是影响美观）直接上代码：UPDATE[region]SET text_full =textWHERE id = id and level=1GOUPDATE aSET a.text_full = b.text_full + a.text from[region] a,[region] bWHERE a.parent_id = b.id and a.[level]=2and b.[level]=1GOUPDATE aSET a.text_full = b.text_full + a.text_full from[region] a,[region] bWHERE a.parent_id = b.id and a.[level]=3and b.[level]=2GO现在好了，看下检查下数据。
在excel中创建三级联动下拉菜单如何在excel中做下拉菜单，详情请点击以下链接：如何创建excel中的下拉菜单。
本文是上述文章的基础上完成如何在excel中实现三级联动下拉菜单？如果你有一定有基础，请继续阅读本文，否则请点击以上链接。
三级联动下拉菜单，例如：要实现如下功能：一级下拉列表是四川省内的地市州，二级下拉列表内是地级单位下辖的县级单位，三级下拉列表就是县级单位下辖的乡镇。
下面说一说具体做法。
1、一级下拉菜单。
一级下拉菜单与上文中的做法完全一样，要在A2处做一级下拉菜单。
在空白表格处输入四川省内的所有市地州（本文只列举了部分），依次点击数据---数据验证----“允许”选择序列，“来源”里直接用鼠标选择刚才建立J1到J7的表格区域。
确定后返回即可完成一级下拉菜单制作。
2、二级菜单。
制作二级菜单方法：（1）在数据空白处输入每个地级单位下辖的县级单位，如下图示：本处以广安市辖各区县为准，如图示。
（2）用鼠标选中K列中刚才输入的广安辖各区县的数据后（只选择已输入数据，不要整列选择），点击公式----“根据所选内容创建”，弹出一个对话框，如下图，首行打勾的，默认如此即可，点确定，这样就建立了一个以“广安市”命名的列（这里的命名必须与一级菜单的名字完全一样）。
（3）然后，在A2单元格选择广安市，选中B2单元格，点击菜单栏，数据----数据验证，“允许”栏选择序列，在“来源”栏里输入如下公式：=indirect(A2)。
这里函数indirect是一个引用函数，是对引用的数据立即进行计算并显示出来，这里表示它引用A2这个单元格显示的广安市，所以显示出的内容就是以“广安市”命名的列的内容。
确定后返回即可。
本例中用同样的方法，输入成都市部分区县，并以同样方法以“成都市”为名给所辖区县命名即可在二级菜单中找到成都市辖区县了。
（无须再进行数据验证了）其它地级单位下辖区县，在设置好数据验证情况下，只需要给地级单位下辖区县命名即可。
excel表格使用公式提取省份及市excel表格使用公式提取省份及市一、添加公式1、提取省份，案例截图2、中国的省份、自治区、直辖市、特别行政区基本是固定的，名称最短的是2个字加“省”，而且名称不重复，最简单的方法就是全部罗列，然后查找匹配。
3、公式=vlookup(left(a2,2)&"*",{"河北省";"山西省";"辽宁省";"吉林省";"黑龙江省";"江苏省";"浙江省";"安徽省";"福建省";"江西省";"山东省";"河南省";"湖北省";"湖南省";"广东省";"海南省";"四川省";"贵州省";"云南省";"陕西省";"甘肃省";"青海省";"台湾省";"北京市";"天津市";"上海市";"重庆市";"广西壮族自治区";"内蒙古自治区";"西藏自治区";"宁夏回族自治区";"新疆维吾尔自治区";"香港特别行政区";"澳门特别行政区"},1,0)4、先提取地址表中的前两个字，利用通配符“*”完成需要vlookup中的第1个参数-查找值，将所有的省份全部罗列作为第2个参数查找区域，然后就在查找区域中找到对应的省份，即可完成匹配。
最近做了一份文档，需要从地址里提取出省份市区和县区，我从网上搜索了很多方法，看似很简单，但是操作起来好像不是那么回事。
经过仔细琢磨研究，最后还是成功了。
网上也有很多方法，在这给大家分享一个我觉得最简单的一个方式，用公式简单就能提取出来，如果提取不出来问题在哪里，我也会说一下。
用到的公式函数是LEFT
left函数用来对单元格内容进行截取。
从左边第一个字符开始截取，截取指定的长度
在B2这个地方用公式=LEFT（E2,3）注意括号和逗号都要用英文版的，意思就是E2这个详细地址从第一个字符开始截取，截取3个字符长度就可以，然后按回车键就得出安徽省。
然后下面的省份就很自然的下拉一下就都形成了，就这么简单。
当然我在做的时候没这么简单明了，其中出现了一个问题，就是我用了公式然后按了回车键仍然不出结果还是只显示公式，如下图：
那么小伙伴会问这是怎么回事？
其实是你B2这一格设置成了文本模式，只需要修改一下格式就能解决，方法是右键设置单元格格式。
然后选择分类里面的常规选项就可以。
点击确定就OK了。
然后回去重新输入刚才那个公式=LEFT(E2,3)，按回车键，结果就出来了，你看懂了吗？
提取省份你会了的话，下面我们来提取市区和县区
用到公式MID ，作用是从一个字符串中截取出指定数量的字符提取市区，还是上面那个表格，咱们利用公式=MID(E2,4,3)，意思是从E2这个详细地址里第四个字符开始提取三个字符，第四个字符是宿，提取三个就是宿州市，简单吧
相同的提取县区就更简单了，利用公式=MID(E2,7,3)，也很好理解，就是E2格的详细地址从第七个字符开始，提取3个字符就可以，最后得到结果埇桥区，你GET到了吗？。
省市区三级联动下拉列表，获取地区编号省市区名字+编号数据结构（JSON格式，来源于国家统计局，附带统计⽹站数据整理脚本，请戳最下⽅）本⽂中使⽤angularJS实现省市区三级联动下拉菜单，⽂章末尾新增了jquery的简单实现（2018年9⽉12⽇1），如需使⽤其他⽅式实现省市区三级联动，也可以复制省市区的数据结构过去，原理⼀样，实现⽅法⼤同⼩异效果图: (对于县级市是没有分区的，所以有时候区是没有可选的，⽽对于台湾、澳门、⾹港下暂没有市，数据来源于国家统计局，下⾯有地区编号+名字的JSON数据）HTML：1<!DOCTYPE html>2<html lang="en">3<head>4<meta charset="UTF-8">5<title>省市区三级联动</title>6<style>7 select {8 width: 200px;9 padding: 5px;10 }11 .code-print{12 box-sizing: border-box;13 padding:10px;14 margin-top:20px;15 width:200px;16 border:1px solid blue;17 }18</style>19</head>20<body ng-app="areaApp">21<div ng-controller="GetAreaCodeController">22<div>23<!--ng-options中key作为select的value绑定到ng-model（就是省市区的编号),value为省市区的名字显⽰在下拉列表中-->24<select ng-model="areaObj.addressProvince"25 ng-change="areaObj.addressCity=undefined;areaObj.addressDistrict=undefined"26 ng-options="key as value for (key,value) in DISTRICTS['100000']">27<option value="">省</option>28</select>29<select ng-model="areaObj.addressCity"35 ng-model="areaObj.addressDistrict"36 ng-options="key as value for (key,value) in DISTRICTS[areaObj.addressCity]">37<option value="">区</option>38</select>39</div>40<div class="code-print">41<p>【县级市没有区！】</p>42<p>省编号：<span ng-bind="areaObj.addressProvince"></span></p>43<p>市编号：<span ng-bind="areaObj.addressCity"></span></p>44<p>区编号：<span ng-bind="areaObj.addressDistrict"></span></p>45<p>最终编号:<span ng-bind="areaObj.getAreaCode(areaObj.addressProvince, areaObj.addressCity, areaObj.addressDistrict)"></span></p> 46</div>47</div>48<script src="../angular.js"></script>49<script src="areaCodes.js"></script>50</body>51</html>展开更多areaCodes.js:↓展开后点击下⾯的图标来复制没有⾏号的代码1/**2 * Created by dell on 2017/10/18.3*/4 (function () {5var app = angular.module('areaApp', []);6 app.controller('GetAreaCodeController', GetAreaCodeController);7function GetAreaCodeController($scope, $http) {8var DISTRICTS;9 $http.get('districts.json').then(function (res) {10/*下⾯就是获取到的数据*/11 $scope.DISTRICTS = res.data;12 });13 $scope.areaObj = {14 'addressProvince' : undefined,15 'addressCity' : undefined,16 'addressDistrict' : undefined,17/** 获取最终地区代码（具体到区或不设区的县级市）参数：city为该城市的地区码，district为第三级的地区代码**/18 'getAreaCode': function (province, city, district) {19var areaCode;20if(province && $scope.DISTRICTS[province]) {21if (city && !$scope.DISTRICTS[city])22 areaCode = city;23else if (district)24 areaCode = district;25return areaCode;26 }else{27return province;28 }29 },3031/**根据地区代码获取地址名的object**/32 'getAreaName': function (areaCode) {33 areaCode = parseInt(areaCode);34var provinceCode = parseInt(areaCode / 10000) * 10000;35var provinceName = $scope.DISTRICTS['100000'][provinceCode];36var cityCode = parseInt(areaCode / 100) * 100;37var districtName = '';38var cityName = '';39if($scope.DISTRICTS[provinceCode]) {45 }46var areaObj = {47 "areaProvince": provinceName,48 "areaMunicipality": cityName,49 "areaDistrict": districtName50 };51return areaObj;52 }53 };54 }55 })();展开更多districts.json省市区编号+名字数据结构（数据来源于国家统计局，如果过期了，请看下⾯的数据整理脚本）1 {2 "100000": {3 "110000": "北京市",4 "120000": "天津市",5 "130000": "河北省",6 "140000": "⼭西省",7 "150000": "内蒙古⾃治区",8 "210000": "辽宁省",9 "220000": "吉林省",10 "230000": "⿊龙江省",11 "310000": "上海市",12 "320000": "江苏省",13 "330000": "浙江省",14 "340000": "安徽省",15 "350000": "福建省",16 "360000": "江西省",17 "370000": "⼭东省",18 "410000": "河南省",19 "420000": "湖北省",20 "430000": "湖南省",21 "440000": "⼴东省",22 "450000": "⼴西壮族⾃治区",23 "460000": "海南省",24 "500000": "重庆市",25 "510000": "四川省",26 "520000": "贵州省",27 "530000": "云南省",28 "540000": "西藏⾃治区",29 "610000": "陕西省",30 "620000": "⽢肃省",31 "630000": "青海省",32 "640000": "宁夏回族⾃治区",33 "650000": "新疆维吾尔⾃治区",34 "710000": "台湾省",35 "810000": "⾹港特别⾏政区",36 "820000": "澳门特别⾏政区"37 },38 "110000": {39 "110100": "市辖区"40 },41 "110100": {42 "110101": "东城区",43 "110102": "西城区",44 "110105": "朝阳区",45 "110106": "丰台区",46 "110107": "⽯景⼭区",47 "110108": "海淀区",48 "110109": "门头沟区",49 "110111": "房⼭区",50 "110112": "通州区",51 "110113": "顺义区",52 "110114": "昌平区",53 "110115": "⼤兴区",58 },59 "120000": {60 "120100": "市辖区"61 },62 "120100": {63 "120101": "和平区",64 "120102": "河东区",65 "120103": "河西区",66 "120104": "南开区",67 "120105": "河北区",68 "120106": "红桥区",69 "120110": "东丽区",70 "120111": "西青区",71 "120112": "津南区",72 "120113": "北⾠区",73 "120114": "武清区",74 "120115": "宝坻区",75 "120116": "滨海新区",76 "120117": "宁河区",77 "120118": "静海区",78 "120119": "蓟州区"79 },80 "130000": {81 "130100": "⽯家庄市",82 "130200": "唐⼭市",83 "130300": "秦皇岛市",84 "130400": "邯郸市",85 "130500": "邢台市",86 "130600": "保定市",87 "130700": "张家⼝市",88 "130800": "承德市",89 "130900": "沧州市",90 "131000": "廊坊市",91 "131100": "衡⽔市",92 "139000": "省直辖县级⾏政区划"93 },94 "130100": {95 "130101": "市辖区",96 "130102": "长安区",97 "130104": "桥西区",98 "130105": "新华区",99 "130107": "井陉矿区",100 "130108": "裕华区",101 "130109": "藁城区",102 "130110": "⿅泉区",103 "130111": "栾城区",104 "130121": "井陉县",105 "130123": "正定县",106 "130125": "⾏唐县",107 "130126": "灵寿县",108 "130127": "⾼⾢县",109 "130128": "深泽县",110 "130129": "赞皇县",111 "130130": "⽆极县",112 "130131": "平⼭县",113 "130132": "元⽒县",114 "130133": "赵县",115 "130183": "晋州市",116 "130184": "新乐市"117 },118 "130200": {119 "130201": "市辖区",120 "130202": "路南区",121 "130203": "路北区",122 "130204": "古冶区",123 "130205": "开平区",124 "130207": "丰南区",125 "130208": "丰润区",126 "130209": "曹妃甸区",127 "130223": "滦县",128 "130224": "滦南县",129 "130225": "乐亭县",130 "130227": "迁西县",131 "130229": "⽟⽥县",132 "130281": "遵化市",133 "130283": "迁安市"134 },135 "130300": {136 "130301": "市辖区",137 "130302": "海港区",144 },145 "130400": {146 "130401": "市辖区", 147 "130402": "邯⼭区", 148 "130403": "丛台区", 149 "130404": "复兴区", 150 "130406": "峰峰矿区", 151 "130421": "邯郸县", 152 "130423": "临漳县", 153 "130424": "成安县", 154 "130425": "⼤名县", 155 "130426": "涉县",156 "130427": "磁县",157 "130428": "肥乡县", 158 "130429": "永年县", 159 "130430": "邱县",160 "130431": "鸡泽县", 161 "130432": "⼴平县", 162 "130433": "馆陶县", 163 "130434": "魏县",164 "130435": "曲周县", 165 "130481": "武安市" 166 },167 "130500": {168 "130501": "市辖区", 169 "130502": "桥东区", 170 "130503": "桥西区", 171 "130521": "邢台县", 172 "130522": "临城县", 173 "130523": "内丘县", 174 "130524": "柏乡县", 175 "130525": "隆尧县", 176 "130526": "任县",177 "130527": "南和县", 178 "130528": "宁晋县", 179 "130529": "巨⿅县", 180 "130530": "新河县", 181 "130531": "⼴宗县", 182 "130532": "平乡县", 183 "130533": "威县",184 "130534": "清河县", 185 "130535": "临西县", 186 "130581": "南宫市", 187 "130582": "沙河市" 188 },189 "130600": {190 "130601": "市辖区", 191 "130602": "竞秀区", 192 "130606": "莲池区", 193 "130607": "满城区", 194 "130608": "清苑区", 195 "130609": "徐⽔区", 196 "130623": "涞⽔县", 197 "130624": "⾩平县", 198 "130626": "定兴县", 199 "130627": "唐县",200 "130628": "⾼阳县", 201 "130629": "容城县", 202 "130630": "涞源县", 203 "130631": "望都县", 204 "130632": "安新县", 205 "130633": "易县",206 "130634": "曲阳县", 207 "130635": "蠡县",208 "130636": "顺平县", 209 "130637": "博野县", 210 "130638": "雄县",211 "130681": "涿州市", 212 "130683": "安国市", 213 "130684": "⾼碑店市" 214 },215 "130700": {216 "130701": "市辖区", 217 "130702": "桥东区", 218 "130703": "桥西区", 219 "130705": "宣化区", 220 "130706": "下花园区", 221 "130708": "万全区",228 "130727": "阳原县",229 "130728": "怀安县",230 "130730": "怀来县",231 "130731": "涿⿅县",232 "130732": "⾚城县"233 },234 "130800": {235 "130801": "市辖区",236 "130802": "双桥区",237 "130803": "双滦区",238 "130804": "鹰⼿营⼦矿区",239 "130821": "承德县",240 "130822": "兴隆县",241 "130823": "平泉县",242 "130824": "滦平县",243 "130825": "隆化县",244 "130826": "丰宁满族⾃治县",245 "130827": "宽城满族⾃治县",246 "130828": "围场满族蒙古族⾃治县" 247 },248 "130900": {249 "130901": "市辖区",250 "130902": "新华区",251 "130903": "运河区",252 "130921": "沧县",253 "130922": "青县",254 "130923": "东光县",255 "130924": "海兴县",256 "130925": "盐⼭县",257 "130926": "肃宁县",258 "130927": "南⽪县",259 "130928": "吴桥县",260 "130929": "献县",261 "130930": "孟村回族⾃治县",262 "130981": "泊头市",263 "130982": "任丘市",264 "130983": "黄骅市",265 "130984": "河间市"266 },267 "131000": {268 "131001": "市辖区",269 "131002": "安次区",270 "131003": "⼴阳区",271 "131022": "固安县",272 "131023": "永清县",273 "131024": "⾹河县",274 "131025": "⼤城县",275 "131026": "⽂安县",276 "131028": "⼤⼚回族⾃治县",277 "131081": "霸州市",278 "131082": "三河市"279 },280 "131100": {281 "131101": "市辖区",282 "131102": "桃城区",283 "131103": "冀州区",284 "131121": "枣强县",285 "131122": "武⾢县",286 "131123": "武强县",287 "131124": "饶阳县",288 "131125": "安平县",289 "131126": "故城县",290 "131127": "景县",291 "131128": "⾩城县",292 "131182": "深州市"293 },294 "139000": {295 "139001": "定州市",296 "139002": "⾟集市"297 },298 "140000": {299 "140100": "太原市",300 "140200": "⼤同市",301 "140300": "阳泉市",302 "140400": "长治市",303 "140500": "晋城市",304 "140600": "朔州市",305 "140700": "晋中市",310 },311 "140100": {312 "140101": "市辖区", 313 "140105": "⼩店区", 314 "140106": "迎泽区", 315 "140107": "杏花岭区", 316 "140108": "尖草坪区", 317 "140109": "万柏林区", 318 "140110": "晋源区", 319 "140121": "清徐县", 320 "140122": "阳曲县", 321 "140123": "娄烦县", 322 "140181": "古交市" 323 },324 "140200": {325 "140201": "市辖区", 326 "140202": "城区",327 "140203": "矿区",328 "140211": "南郊区", 329 "140212": "新荣区", 330 "140221": "阳⾼县", 331 "140222": "天镇县", 332 "140223": "⼴灵县", 333 "140224": "灵丘县", 334 "140225": "浑源县", 335 "140226": "左云县", 336 "140227": "⼤同县" 337 },338 "140300": {339 "140301": "市辖区", 340 "140302": "城区",341 "140303": "矿区",342 "140311": "郊区",343 "140321": "平定县", 344 "140322": "盂县"345 },346 "140400": {347 "140401": "市辖区", 348 "140402": "城区",349 "140411": "郊区",350 "140421": "长治县", 351 "140423": "襄垣县", 352 "140424": "屯留县", 353 "140425": "平顺县", 354 "140426": "黎城县", 355 "140427": "壶关县", 356 "140428": "长⼦县", 357 "140429": "武乡县", 358 "140430": "沁县",359 "140431": "沁源县", 360 "140481": "潞城市" 361 },362 "140500": {363 "140501": "市辖区", 364 "140502": "城区",365 "140521": "沁⽔县", 366 "140522": "阳城县", 367 "140524": "陵川县", 368 "140525": "泽州县", 369 "140581": "⾼平市" 370 },371 "140600": {372 "140601": "市辖区", 373 "140602": "朔城区", 374 "140603": "平鲁区", 375 "140621": "⼭阴县", 376 "140622": "应县",377 "140623": "右⽟县", 378 "140624": "怀仁县" 379 },380 "140700": {381 "140701": "市辖区", 382 "140702": "榆次区", 383 "140721": "榆社县", 384 "140722": "左权县", 385 "140723": "和顺县", 386 "140724": "昔阳县", 387 "140725": "寿阳县", 388 "140726": "太⾕县", 389 "140727": "祁县",394 "140800": {395 "140801": "市辖区",396 "140802": "盐湖区",397 "140821": "临猗县",398 "140822": "万荣县",399 "140823": "闻喜县",400 "140824": "稷⼭县",401 "140825": "新绛县",402 "140826": "绛县",403 "140827": "垣曲县",404 "140828": "夏县",405 "140829": "平陆县",406 "140830": "芮城县",407 "140881": "永济市",408 "140882": "河津市"409 },410 "140900": {411 "140901": "市辖区",412 "140902": "忻府区",413 "140921": "定襄县",414 "140922": "五台县",415 "140923": "代县",416 "140924": "繁峙县",417 "140925": "宁武县",418 "140926": "静乐县",419 "140927": "神池县",420 "140928": "五寨县",421 "140929": "岢岚县",422 "140930": "河曲县",423 "140931": "保德县",424 "140932": "偏关县",425 "140981": "原平市"426 },427 "141000": {428 "141001": "市辖区",429 "141002": "尧都区",430 "141021": "曲沃县",431 "141022": "翼城县",432 "141023": "襄汾县",433 "141024": "洪洞县",434 "141025": "古县",435 "141026": "安泽县",436 "141027": "浮⼭县",437 "141028": "吉县",438 "141029": "乡宁县",439 "141030": "⼤宁县",440 "141031": "隰县",441 "141032": "永和县",442 "141033": "蒲县",443 "141034": "汾西县",444 "141081": "侯马市",445 "141082": "霍州市"446 },447 "141100": {448 "141101": "市辖区",449 "141102": "离⽯区",450 "141121": "⽂⽔县",451 "141122": "交城县",452 "141123": "兴县",453 "141124": "临县",454 "141125": "柳林县",455 "141126": "⽯楼县",456 "141127": "岚县",457 "141128": "⽅⼭县",458 "141129": "中阳县",459 "141130": "交⼝县",460 "141181": "孝义市",461 "141182": "汾阳市"462 },463 "150000": {464 "150100": "呼和浩特市", 465 "150200": "包头市",466 "150300": "乌海市",467 "150400": "⾚峰市",468 "150500": "通辽市",469 "150600": "鄂尔多斯市", 470 "150700": "呼伦贝尔市", 471 "150800": "巴彦淖尔市", 472 "150900": "乌兰察布市",478 "150101": "市辖区",479 "150102": "新城区",480 "150103": "回民区",481 "150104": "⽟泉区",482 "150105": "赛罕区",483 "150121": "⼟默特左旗",484 "150122": "托克托县",485 "150123": "和林格尔县",486 "150124": "清⽔河县",487 "150125": "武川县"488 },489 "150200": {490 "150201": "市辖区",491 "150202": "东河区",492 "150203": "昆都仑区",493 "150204": "青⼭区",494 "150205": "⽯拐区",495 "150206": "⽩云鄂博矿区",496 "150207": "九原区",497 "150221": "⼟默特右旗",498 "150222": "固阳县",499 "150223": "达尔罕茂明安联合旗"500 },501 "150300": {502 "150301": "市辖区",503 "150302": "海勃湾区",504 "150303": "海南区",505 "150304": "乌达区"506 },507 "150400": {508 "150401": "市辖区",509 "150402": "红⼭区",510 "150403": "元宝⼭区",511 "150404": "松⼭区",512 "150421": "阿鲁科尔沁旗",513 "150422": "巴林左旗",514 "150423": "巴林右旗",515 "150424": "林西县",516 "150425": "克什克腾旗",517 "150426": "翁⽜特旗",518 "150428": "喀喇沁旗",519 "150429": "宁城县",520 "150430": "敖汉旗"521 },522 "150500": {523 "150501": "市辖区",524 "150502": "科尔沁区",525 "150521": "科尔沁左翼中旗",526 "150522": "科尔沁左翼后旗",527 "150523": "开鲁县",528 "150524": "库伦旗",529 "150525": "奈曼旗",530 "150526": "扎鲁特旗",531 "150581": "霍林郭勒市"532 },533 "150600": {534 "150601": "市辖区",535 "150602": "东胜区",536 "150603": "康巴什区",537 "150621": "达拉特旗",538 "150622": "准格尔旗",539 "150623": "鄂托克前旗",540 "150624": "鄂托克旗",541 "150625": "杭锦旗",542 "150626": "乌审旗",543 "150627": "伊⾦霍洛旗"544 },545 "150700": {546 "150701": "市辖区",547 "150702": "海拉尔区",548 "150703": "扎赉诺尔区",549 "150721": "阿荣旗",550 "150722": "莫⼒达⽡达斡尔族⾃治旗", 551 "150723": "鄂伦春⾃治旗",552 "150724": "鄂温克族⾃治旗",553 "150725": "陈巴尔虎旗",554 "150726": "新巴尔虎左旗",555 "150727": "新巴尔虎右旗",556 "150781": "满洲⾥市",562 "150800": {563 "150801": "市辖区",564 "150802": "临河区",565 "150821": "五原县",566 "150822": "磴⼝县",567 "150823": "乌拉特前旗",568 "150824": "乌拉特中旗",569 "150825": "乌拉特后旗",570 "150826": "杭锦后旗"571 },572 "150900": {573 "150901": "市辖区",574 "150902": "集宁区",575 "150921": "卓资县",576 "150922": "化德县",577 "150923": "商都县",578 "150924": "兴和县",579 "150925": "凉城县",580 "150926": "察哈尔右翼前旗", 581 "150927": "察哈尔右翼中旗", 582 "150928": "察哈尔右翼后旗", 583 "150929": "四⼦王旗",584 "150981": "丰镇市"585 },586 "152200": {587 "152201": "乌兰浩特市",588 "152202": "阿尔⼭市",589 "152221": "科尔沁右翼前旗", 590 "152222": "科尔沁右翼中旗", 591 "152223": "扎赉特旗",592 "152224": "突泉县"593 },594 "152500": {595 "152501": "⼆连浩特市",596 "152502": "锡林浩特市",597 "152522": "阿巴嘎旗",598 "152523": "苏尼特左旗",599 "152524": "苏尼特右旗",600 "152525": "东乌珠穆沁旗", 601 "152526": "西乌珠穆沁旗", 602 "152527": "太仆寺旗",603 "152528": "镶黄旗",604 "152529": "正镶⽩旗",605 "152530": "正蓝旗",606 "152531": "多伦县"607 },608 "152900": {609 "152921": "阿拉善左旗",610 "152922": "阿拉善右旗",611 "152923": "额济纳旗"612 },613 "210000": {614 "210100": "沈阳市",615 "210200": "⼤连市",616 "210300": "鞍⼭市",617 "210400": "抚顺市",618 "210500": "本溪市",619 "210600": "丹东市",620 "210700": "锦州市",621 "210800": "营⼝市",622 "210900": "⾩新市",623 "211000": "辽阳市",624 "211100": "盘锦市",625 "211200": "铁岭市",626 "211300": "朝阳市",627 "211400": "葫芦岛市"628 },629 "210100": {630 "210101": "市辖区",631 "210102": "和平区",632 "210103": "沈河区",633 "210104": "⼤东区",634 "210105": "皇姑区",635 "210106": "铁西区",636 "210111": "苏家屯区",637 "210112": "浑南区",638 "210113": "沈北新区",639 "210114": "于洪区",640 "210115": "辽中区",。
设置省名称的数据有效性
1=COUNTA(数据!$1:$1)1统计数据表第一行有多少数据
2
2用OFFSET函数定义一个区间：
定义起始位置是数据!$A$1，
偏移行是0，偏移列是0，取值
行数是1，取值列数是
COUNTA(数据!$1:$1)。
3
3定义名称：省=上面带红字的公式
4设置C2单元的数据有效性序列
=省
4
5
6 =OFFSET(数
据!$A$1,0,0,1,INDEX($I$1:$
I$10,MATCH($A1,$H$1:$H$10,
0)))
设置市县名称的数据有效性
=MATCH($C2,数据!$1:$1,0)
=OFFSET(数据!$A$1,1,MATCH($C2,数据!青铜峡=COUNTA(OFFSET(数
据!$A$1,1,MATCH($C2,数
据!$1:$1,0),500,1))
统计上述区间有多少市县
知道了C2单元指定的省所在的列，又知道了该省有多少市县，用
OFFSET函数进而取得市县的名称
用E18单元的公式定义名称：市县=OFFSET(数据!$A$1,1,MATCH($C2,数据!$1:$1,0),COUNTA(OFFSET(数据!$A$1,1,MATCH($C2,数
据!$1:$1,0),500,1)),1)
定义D2单元的数据有效性序列=市县查找C2单元的内容在数据表的第几列
为上述列定义区间（其中的500要大于每个省中的市县数量
=OFFSET(数据!$A$1,1,MATCH($C2,数据!$1:$1,0),COUNTA(OFFSET(数据!$A$1,1,MATCH($C2,数
据!$1:$1,0),500,1)),1)。
