身分証明書番号に基づいて省を自動的に計算する

身分証明書の一番前の二桁のコードは省を表し、各省に対応するコードを知っていれば省の情報を抽出できる。

1.if関数計算

If関数を用いてそれぞれの状況を羅列して計算する。

次のコードは公式エディタに直接コピーできます身分証明書番号フィールドの置き換えに注意してください。

{"text":"IF(EQ(LEN(​身份证号​),18),IF(EQ(VALUE(MID(​身份证号​,1,2)),11),\"北京市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),12),\"天津市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),13),\"河北省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),14),\"山西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),15),\"内蒙古自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),21),\"辽宁省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),22),\"吉林省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),23),\"黑龙江省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),31),\"上海市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),32),\"江苏省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),33),\"浙江省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),34),\"安徽省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),35),\"福建省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),36),\"江西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),37),\"山东省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),41),\"河南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),42),\"湖北省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),43),\"湖南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),44),\"广东省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),45),\"广西壮族自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),46),\"海南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),50),\"重庆市\",IF(EQ(VALUE(MID(​身份证号​,1,2)),51),\"四川省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),52),\"贵州省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),53),\"云南省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),54),\"西藏自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),61),\"陕西省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),62),\"甘肃省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),63),\"青海省\",IF(EQ(VALUE(MID(​身份证号​,1,2)),64),\"宁夏回族自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),65),\"新疆维吾尔自治区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),83),\"台湾\",IF(EQ(VALUE(MID(​身份证号​,1,2)),81),\"香港特别行政区\",IF(EQ(VALUE(MID(​身份证号​,1,2)),82),\"澳门特别行政区\",\"\")))))))))))))))))))))))))))))))))),\"\")","marks":[{"from":{"line":0,"ch":10,"sticky":null},"to":{"line":0,"ch":16,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":38,"sticky":null},"to":{"line":0,"ch":44,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":77,"sticky":null},"to":{"line":0,"ch":83,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":116,"sticky":null},"to":{"line":0,"ch":122,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":155,"sticky":null},"to":{"line":0,"ch":161,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":194,"sticky":null},"to":{"line":0,"ch":200,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":236,"sticky":null},"to":{"line":0,"ch":242,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":275,"sticky":null},"to":{"line":0,"ch":281,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":314,"sticky":null},"to":{"line":0,"ch":320,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":393,"sticky":null},"to":{"line":0,"ch":399,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":432,"sticky":null},"to":{"line":0,"ch":438,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":354,"sticky":null},"to":{"line":0,"ch":360,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":471,"sticky":null},"to":{"line":0,"ch":477,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":549,"sticky":null},"to":{"line":0,"ch":555,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":510,"sticky":null},"to":{"line":0,"ch":516,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":588,"sticky":null},"to":{"line":0,"ch":594,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":627,"sticky":null},"to":{"line":0,"ch":633,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":666,"sticky":null},"to":{"line":0,"ch":672,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":705,"sticky":null},"to":{"line":0,"ch":711,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":744,"sticky":null},"to":{"line":0,"ch":750,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":826,"sticky":null},"to":{"line":0,"ch":832,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":865,"sticky":null},"to":{"line":0,"ch":871,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":783,"sticky":null},"to":{"line":0,"ch":789,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":943,"sticky":null},"to":{"line":0,"ch":949,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1021,"sticky":null},"to":{"line":0,"ch":1027,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1101,"sticky":null},"to":{"line":0,"ch":1107,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1062,"sticky":null},"to":{"line":0,"ch":1068,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1140,"sticky":null},"to":{"line":0,"ch":1146,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1222,"sticky":null},"to":{"line":0,"ch":1228,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1304,"sticky":null},"to":{"line":0,"ch":1310,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1347,"sticky":null},"to":{"line":0,"ch":1353,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1266,"sticky":null},"to":{"line":0,"ch":1272,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":1179,"sticky":null},"to":{"line":0,"ch":1185,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":982,"sticky":null},"to":{"line":0,"ch":988,"sticky":null},"value":"textField_l200ci6g","invalid":false},{"from":{"line":0,"ch":904,"sticky":null},"to":{"line":0,"ch":910,"sticky":null},"value":"textField_l200ci6g","invalid":false}],"isCmData":true}

If (条件を判断し、結果がtrueの戻り値、結果がfalseの戻り値)
Mid (a、b、c): a文字列で、b番目からc文字を取り出します
Value (): mid関数が取り出した文字列を数字に変換します
Eq (value 1、value 2) の2つの値が等しい場合はtrueを返し、数字、日付をサポートします