2009-11 アーカイブ

0462242317 を 046-224-2317 というハイフン付きの形にしたい

昼休みに、休憩室でのんびりと携帯で Twitter を眺めていたら、【誰か教えて】もしかしたらすごい初歩的な質問かもしれないんだけど、エクセルで '0462242317' を 046-224-2317 に楽に変換できる方法ないですか？つまり電話番号なんだけど、あるサイトからcsvに落ちてくるのが前者。配送会社の伝票ソフト対応が後者。という post が目に入りました。

休憩室から眺めていたということは Excel を使える環境が手元にないということなので、 @mikiSanktGallen セルの表示形式でどうにかなりそうな。昼休み終わったら調べられるけど、昼休み終わったらついったができないｗ [mb] という回答をし、今後の Excel 作業のために調べようかなあと思っていたら、 @hayamiz @hxxk セルの表示方式変換しても、前後に ' が入ってるからダメ。で、 ' を外すと、市外局番の頭の0が消える。文字列とかにしてもダメ。。。あとセルの表示方式の電話番号変換だと(046)224-2317ってなっちゃう。との追加情報が来たので、それを元に昼休みの残り時間で試してみました。

よくよく考えたら伝票ソフトにデータを送るということは、セルの書式設定をいじっても本質的な解決にはならない ( 文字列としてコピー→ペーストすれば OK かも ?) ので、 LEFT 関数や MID 関数を組み合わせつつハイフンを挿入すればいいのかなあと考え、 =CONCATENATE("0",LEFT(A1,2),"-",MID(A1,3,3),"-",RIGHT(A1,4)) という数式を B1 セルに、そして 0462242317 というハイフン無しの電話番号を A1 セルに入力してみると、 046-224-2317 として表示できました !

......しかし、これだと市外局番が 3 桁の場合にはいいのですが、私の居住地域みたいに市外局番が 4 桁だったらこれじゃマズくないか ? と思い、試しに 0123121234 という数字を A1 セルに入力してみると、やはり 012-312-1234 として表示されます。

市外局番が何桁でもハイフン付きの形にしたい

......ということで、昼休みが終わってしまったので残りは自宅に持ち越し。 桁数が変わっても大丈夫なようにするにはちょっとしたマクロを組む必要があるかなあ、でも一から組むのはなあ、と思い「電話番号 ハイフン マクロ」で検索してみると、エクセルで電話番号にハイフンを入れるには？ - 教えて！goo という正解に近いものがヒットしました。

この次のセクションで修正後の VBA コードと、実際にそれを適用した Excel ブックを公開しますが、とりあえず作成手順をメモします。

1. エクセルで電話番号にハイフンを入れるには？ - 教えて！goo の説明に従い、市外局番の一覧から市外局番のリストを Sheet2 に作成し、「市外局番一覧」というシート名に変更
2. 「市外局番一覧」シートの最後に、 090 と 080 と 070 と 050 という携帯電話や IP 電話の局番を追加
3. 市外局番の先頭が 0 であるため、頭の 0 が Excel 上で消えるので、「市外局番一覧」シートの A 列全てのセルの書式設定を "0"0 として見かけ上 0 を追加
4. Sheet1 のシート名を「変換用シート」に変更し、確認用に A 列に市外局番が 2 桁、 3 桁、 4 桁、 5 桁の電話番号および携帯電話の 11 桁の番号を入力
5. Visual Vasic Editor を起動し、後述のコードを入力
6. マクロを実行して確認作業。エクセルで電話番号にハイフンを入れるには？ - 教えて！goo のコードは電話番号の先頭の 0 が存在している前提で書かれているようなので、先頭の 0 が消えた状態で変換できるようにコード修正
7. サンクトガーレンのトップページにアクセスする
8. http://www.sanktgallenbrewery.com/img/headsg.gif にマウスを合わせ、画像をコピー
9. 「変換用シート」の D1 セルに画像を貼り付け、その画像に電話番号変換マクロを登録
10. 完了 ! サンクトガーレンのロゴをクリックするたびに A 列の電話番号にハイフンを加えたものが B 列に表示されました ! あとは B 列のハイフン付き電話番号をコピーしてソフトに流し込めばたぶん OK

まあ、マクロの実行ボタンにサンクトガーレンのロゴを貼り付けたものは、 @mikiSanktGallen に送る分はともかく、こちらで公開するものは通常のフォームボタンに差し替えています。

修正した VBA コードと、マクロを適用した Excel ブックを公開

さて、修正後の VBA コードと、完成した Excel ブックを公開します。これは 2009 年 4 月 28 日現在の市外局番の一覧を参考にしていますが、市外局番は滅多に増えることはない ( はず ) ので、おそらく市外局番リストをメンテナンスする必要はないはずです。

• 市外局番の桁数に関わらずハイフンを適切に挿入する Excel ブック

なお、この Excel ブックおよび VBA コードは、 10 桁または 11 桁の数字のみを入力することを想定しています。 @mikiSanktGallen の場合は数字の前後に ' が配置されていたので、もう一工夫しました。

Sub 電話番号変換()
'
' http://oshiete1.goo.ne.jp/qa2888873.html を参考に作成
' Thanks to ham_kamo ( http://oshiete1.goo.ne.jp/user.php3?u=205516 )
'
  Dim WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Worksheets("変換用シート") '電話番号が入っているシート名
  Set WS2 = Worksheets("市外局番一覧") '市外局番一覧が入っているシート名
  Const C1 As String = "A" '元の電話番号が入っている列名
  Const C2 As String = "B" 'ハイフンを挿入した電話番号を記入する列名
 
  Dim i As Integer
  Dim Tel1 As String, Tel2 As String

  With WS1
    For i = 2 To .Cells(Rows.Count, C1).End(xlUp).Row
      Tel1 = .Cells(i, C1)
      If Not WS2.Columns(1).Find(What:=Mid(Tel1, 1, 4), LookAt:=xlWhole) Is Nothing Then
        '市外局番が５桁のとき
        Tel2 = "0" & Left(Tel1, 4) & "-" & Mid(Tel1, 5, 1) & "-" & Mid(Tel1, 6, 4)
      ElseIf Not WS2.Columns(1).Find(What:=Mid(Tel1, 1, 3), LookAt:=xlWhole) Is Nothing Then
        '市外局番が４桁のとき
        Tel2 = "0" & Left(Tel1, 3) & "-" & Mid(Tel1, 4, 2) & "-" & Mid(Tel1, 6, 4)
      ElseIf Not WS2.Columns(1).Find(What:=Mid(Tel1, 1, 2), LookAt:=xlWhole) Is Nothing Then
        If Mid(Tel1, 2, 1) = "0" Then
          '市外局番が３桁で0x0の場合（11桁）
          Tel2 = "0" & Left(Tel1, 2) & "-" & Mid(Tel1, 3, 4) & "-" & Mid(Tel1, 7, 4)
        Else
          '市外局番が３桁で0x0でない場合（10桁）
          Tel2 = "0" & Left(Tel1, 2) & "-" & Mid(Tel1, 3, 3) & "-" & Mid(Tel1, 6, 4)
        End If
      ElseIf Not WS2.Columns(1).Find(What:=Mid(Tel1, 1, 1), LookAt:=xlWhole) Is Nothing Then
        '市外局番が２桁のとき
        Tel2 = "0" & Left(Tel1, 1) & "-" & Mid(Tel1, 2, 4) & "-" & Mid(Tel1, 6, 4)
      Else
        'それ以外の時はハイフンを入れずそのままにする
        Tel2 = Tel1
      End If
      .Cells(i, C2) = Tel2
    Next
  End With
End Sub

なお、解決後にお聞きしたのですが、アドインで対処する方法もあるようです。

平成 21 年分 給与所得者の保険料控除申告書 兼 給与所得者の配偶者特別控除申告書

唐突ですが、そろそろ年末調整、そして確定申告のことを考える時期がやってきました。 というのも、先日経理部門から 「保険料控除申告書 兼 配偶者特別控除申告書を配るから、年末調整で保険料控除や配偶者特別控除を適用してもらいたい人は提出しなさい」 というお達しがあったので、記録に残しておこうと思ったためです。

私自身は確定申告を行うので年末調整での控除申告は行わない予定ですが、この制度について後輩に説明を行う必要が出てきたため、いちいち確認のために都度電卓を弾くのは面倒臭いしミスの元になるということで、記入様式を Excel に当てはめて、計算をお任せすることにしました。 といっても大した内容のものではなく、 (PDF)平成 21 年分 給与所得者の保険料控除申告書 兼 給与所得者の配偶者特別控除申告書と同じような枠組みを作り、簡単な計算式を配置しただけです。

• 平成 21 年分 給与所得者の保険料控除申告書 兼 給与所得者の配偶者特別控除申告書 計算用 Excel ブック

「控除申告書を経理や人事からもらったけど、検算が面倒臭い」といった場合にご利用ください。

=IF(
 D27+G27>=15000,
 15000,
 D27+G27
)

=IF(
 D27+G27>=50000,
 50000,
 D27+G27
)

個人的に今回これを作成して収穫だったのは、

• 数字を入力する前の時点では「 0 円」ではなく「 ( 空白 ) 円」と表示したい
• 数字を入力した段階で、「 50,000 円」のように桁区切りありで表示したい

といった条件でも、 CONCATENATE 関数などの文字列操作関数ではなく、書式設定を #,###_ "円" とするだけで良い、ということを知ったことでした。 これまでは =CONCATENATE(A1,"円") なんて連結をしたり、

=IF(
 SUM(A1:A3)=0,
  "",
  CONCATENATE(SUM(A1:A3),"円")
)

といった IF 文を設定したりしていたので、何という回りくどい書き方をしていたのだ自分は......と思った次第です。 なお、 #,###と#,##0の違いは？：Excel エクセルの使い方-書式-表示形式に詳しい解説がありますが、数字を入力する前の時点では「 ( 空白 ) 円」ではなく「 0 円」と表示したい場合は書式設定を #,###_ "円" ではなく #,##0_ "円" とすると良いようです。