業務で使ったのでメモ
１．住所から都道府県を取り出す
- 先頭から４文字目が「県」なら４文字、そうじゃないなら３文字が都道府県名
という判定で47都道府県全部行ける。Excelではけっこうメジャーな方法らしく、「Excel ＋ 住所から都道府県」という検索で直ぐ出てきた。
考えた人は偉い。２文字の県名を作らなかった人も偉い。
エクセル関数なら以下の通り。
Excel関数
= IF(MID(A1,4,1)<>"県",LEFT(A1,3),LEFT(A1,FIND("県",A1)))
正規表現ならこんな感じ。
python
re = r'^.{3}県?'
２．都道府県番号を取得する
とりあえず都道府県と番号対応表。
| 都道府県名 | 都道府県番号 | 地方名 | 地方番号 |
|---|---|---|---|
| 北海道 | 1 | 北海道地方 | 1 |
| 青森県 | 2 | 東北地方 | 2 |
| 岩手県 | 3 | 東北地方 | 2 |
| 宮城県 | 4 | 東北地方 | 2 |
| 秋田県 | 5 | 東北地方 | 2 |
| 山形県 | 6 | 東北地方 | 2 |
| 福島県 | 7 | 東北地方 | 2 |
| 茨城県 | 8 | 関東地方 | 3 |
| 栃木県 | 9 | 関東地方 | 3 |
| 群馬県 | 10 | 関東地方 | 3 |
| 埼玉県 | 11 | 関東地方 | 3 |
| 千葉県 | 12 | 関東地方 | 3 |
| 東京都 | 13 | 関東地方 | 3 |
| 神奈川県 | 14 | 関東地方 | 3 |
| 新潟県 | 15 | 中部地方 | 4 |
| 富山県 | 16 | 中部地方 | 4 |
| 石川県 | 17 | 中部地方 | 4 |
| 福井県 | 18 | 中部地方 | 4 |
| 山梨県 | 19 | 中部地方 | 4 |
| 長野県 | 20 | 中部地方 | 4 |
| 岐阜県 | 21 | 中部地方 | 4 |
| 静岡県 | 22 | 中部地方 | 4 |
| 愛知県 | 23 | 中部地方 | 4 |
| 三重県 | 24 | 近畿地方 | 5 |
| 滋賀県 | 25 | 近畿地方 | 5 |
| 京都府 | 26 | 近畿地方 | 5 |
| 大阪府 | 27 | 近畿地方 | 5 |
| 兵庫県 | 28 | 近畿地方 | 5 |
| 奈良県 | 29 | 近畿地方 | 5 |
| 和歌山県 | 30 | 近畿地方 | 5 |
| 鳥取県 | 31 | 中国地方 | 6 |
| 島根県 | 32 | 中国地方 | 6 |
| 岡山県 | 33 | 中国地方 | 6 |
| 広島県 | 34 | 中国地方 | 6 |
| 山口県 | 35 | 中国地方 | 6 |
| 徳島県 | 36 | 四国地方 | 7 |
| 香川県 | 37 | 四国地方 | 7 |
| 愛媛県 | 38 | 四国地方 | 7 |
| 高知県 | 39 | 四国地方 | 7 |
| 福岡県 | 40 | 九州・沖縄地方 | 8 |
| 佐賀県 | 41 | 九州・沖縄地方 | 8 |
| 長崎県 | 42 | 九州・沖縄地方 | 8 |
| 熊本県 | 43 | 九州・沖縄地方 | 8 |
| 大分県 | 44 | 九州・沖縄地方 | 8 |
| 宮崎県 | 45 | 九州・沖縄地方 | 8 |
| 鹿児島県 | 46 | 九州・沖縄地方 | 8 |
| 沖縄県 | 47 | 九州・沖縄地方 | 8 |
県名のセルからVLOOKUP関数で番号を引く
Excel関数
=VLOOKUP(B1,Sheet2!$B$1:$C$47,2,0)
-
B1が県名の入ったセル -
Sheet2!$A$1:$D$47が番号対応表の範囲 -
2は対応番号の取得。「2」なら都道府県番号。「4」なら地方番号 - 文字列による判定の時は最後の引数は「0」で。
３．集計する
COUNTIF(S)関数,SUMIF(S)関数を使って集計する。
あらためで考えると番号に変換する必要は無いかもしれない…。
あとExcelのCOUNTIFS,SUMIFSはOR条件が使えないので、OR条件を使うときは無理に工夫するより個別に出したものを合計する方針がいい。
COUNTIFの例
C1:C1000が都道府県番号の入ったセル範囲。
このなかで"1（北海道）"の件数をカウント
= COUNTIF(C1:C1000,"1")
SUMIFの例
C1:C1000が都道府県番号の入ったセル範囲。
D1:D1000が売上のセル。
このなかで"1（北海道）"の売上合計をカウント。
= SUMIF(C1:C1000,"1",D1:D1000)