2015年8月
            1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31          

最近のトラックバック

最近買った本(Amazon.co.jp)

広告(Amazon.co.jp)

広告(Google AdSense)

無料ブログはココログ

カテゴリー「Excel」の3件の記事

2011年9月25日 (日)

Excel/VBA入門: 英数字を半角に変換し、半角カナを全角に変換する。

以前、某所に掲載したものの改良版です。

ワークシートに入力された文字列について、英数字や片仮名のように半角(1Byte)と全角(2Byte)の文字が存在するものをどちらか一方に統一したいということが良くある。この場合、VBAのStrConv関数を使用して全てのセルの文字列を一括変換すれば良い。

str = StrConv(str, vbNarrow) ' 文字列を半角(1Byte)に変換する。
str = StrConv(str, vbWide)   ' 文字列を全角(2Byte)に変換する。

しかし、英数字は半角に統一したいが、「いわゆる半角カナ」などと呼ばれ過去に多くの問題を引き起こしてきた半角カナ(0xA1-0xCF)だけは全角に変換したいという場合、StrConvなどの既存の関数では一度に変換できないため、少し工夫が必要となる。

というわけで、英数字を半角に変換し、半角カナを全角に変換するプロ-シージャーを作成してみた。

Sub ToHankakuWithoutKatakana()
    '
    ' 現在のワークシートの値が入力されている全てのセルについて、
    ' 半角変換可能な全角文字を全て半角に変換する。
    ' ただし、半角カナ(0xA1-0xCF)については全角に変換する。
    ' また、可能であればカナ文字と直後の濁点・半濁点が合成される。
    '
    ' 2011/09/25 hichon: 新規作成。
    '
    Dim re As Object
    Dim Cell As Range
    Dim Str As String
    Dim Match As Object

    '
    ' 正規表現オブジェクトを作成する。
    ' 1文字以上の半角カナ(0xA1-0xCF)を検索対象とする。
    '
    Set re = CreateObject("VBScript.RegExp")
    re.Pattern = "[。-゚]+"
    re.Global = True

    '
    ' 値が入力されている全てのセルについて、変換処理を実行する。
    ' 尚、値が入力されているセルが存在しない場合、エラーとなる。
    '
    On Error GoTo Error
    For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)

        Str = Cell.Value
        If Str <> "" Then

            '
            ' 半角変換可能な文字を半角に変換する。
            ' 全角カナもいったん半角カナに変換される。
            '
            Str = StrConv(Str, vbNarrow)

            '
            ' 半角カナを全角に変換する。
            ' 半角カナの直後に濁点・半濁点があればここで合成される。
            '
            For Each Match In re.Execute(Str)
                Str = Replace(Str, Match, StrConv(Match, vbWide), , 1)
            Next
            Cell.Value = Str

        End If
 
    Next

    Exit Sub

Error:
    MsgBox Err.Description

End Sub

2011年9月 1日 (木)

Excel入門: CSVファイルをダブルクリックで開いたとき、数値の先頭の0が削除されないようにする。(増補版)

はじめに

ExcelではCSVファイルをダブルクリックで開いたとき、各列の表示形式が一律に標準となる。そのためデータが数字列の場合、自動的に数値に変換されて読み込まれる。通常ではこれで問題ないが、0011のように先頭に0が付いたデータの場合、先頭の0が削除されてしまう。同様に1-2-1、1/2/2ようにハイフン、または、スラッシュで区切られた数字が日付に変換される、1:2:3のようにコロンで区切られた数字が時間に変換される、全角数字が半角に変換されるなどの問題がある。

期待した結果

001,002,003           → |001     |002     |003     |
1-2-1,1/2/2,1:2:3     → |1-2-1   |1/2/2   |1:2:3   |
121,122,123  → |121  |122  |123  |

実際の結果

001,002,003           → |       1|       2|       3|
1-2-1,1/2/2,1:2:3     → |2001/2/1|2001/2/2| 1:02:03|
121,122,123  → |     121|     122|     123|

回避方法としては、先にExcelを起動して、ファイルメニューの開くから(Excel 2007以前の場合)、または、データメニューのテキストファイルから(Excel 2010の場合)ファイルを読み込み、テキストファイルウイザードで各列に表示形式を指定する方法が一般的だが、これをダブルクリックしか知らない相手に一から説明するのはなかなか面倒である。

この件については、以前某所に回避方法を書いたことがあったが、今回追加の調査を行ったので増補版としてあらためてまとめてみた。

回避方法の考察

とにかくデータを文字列として認識させれば良いということで、ダブルクォーテーションで括ってみる。→だめである。読み込み時にダブルクォーテーションが削除されてしまうので同じ結果になってしまう。

"001","002","003"           → |       1|       2|       3|
"1-2-1","1/2/2","1:2:3"     → |2001/2/1|2001/2/2| 1:02:03|
"121","122","123"  → |     121|     122|     123|

では、データの先頭に文字列をあらわすシングルクォーテーションを付けてみる。→文字列として読み込まれるが、シングルクォーテーションが表示されたままとなる。セルを一個一個確定し直せば意図した結果になるが…。

'001,'002,'003           → |'001    |'002    |'003    |
'1-2-1,'1/2/2,'1:2:3     → |'1-2-1  |'1/2/2  |'1:2:3  |
'121,'122,'123  → |'121 |'122 |'123 |

回避方法

データをダブルクォーテーションで括った上で先頭にイコールを付けて数式にしてしまう。こうすると読み込み時にダブルクォーテーションが削除されないので文字列として表示される。しかし、このCSVファイルをExcel以外のアプリケーションで読み込むと当然問題が発生する。

="001",="002",="003"           → |001     |002     |003     |
="1-2-1",="1/2/2",="1:2:3"     → |1-2-1   |1/2/2   |1:2:3   |
="121",="122",="123"  → |121  |122  |123  |

データの先頭にタブ文字を付ける(以下の<tab>は実際にはタブ文字)。こちらの方がスマートで良い。しかも、タブ文字は空白文字の一つなのでExcel以外のアプリケーションでも問題が発生しにくそうである。

<tab>001,<tab>002,<tab>003           → |001     |002     |003     |
<tab>1-2-1,<tab>1/2/2,<tab>1:2:3     → |1-2-1   |1/2/2   |1:2:3   |
<tab>121,<tab>122,<tab>123  → |121  |122  |123  |

同様にデータの末尾にタブ文字を付けても良い。むしろこちらの方が問題が発生しにくいかも。

001<tab>,002<tab>,003<tab>           → |001     |002     |003     |
1-2-1<tab>,1/2/2<tab>,1:2:3<tab>     → |1-2-1   |1/2/2   |1:2:3   |
121<tab>,122<tab>,123<tab>  → |121  |122  |123  |

Excelでのタブ文字の扱い

データにタブ文字を付ける方法でCSVファイルを読み込んだ場合、タブ文字は読み込み時に削除されたわけではなく、読み込まれたデータにしっかり付加されている。実際にセルのデータにカーソルを立て、矢印キーで移動すると横幅0の文字があることが分かる。以下のようにLEN()関数でデータの文字数を表示すると一文字分多く表示される。

A B:数式 B:表示
1 001 =LEN(A1) 4
2 1-2-1 =LEN(A2) 6
3 121 =LEN(A3) 4

しかも、このタブ文字は前後の空白文字を削除するTRIM()関数では削除できない。

A B:数式 B:表示
1 001 =LEN(TRIM(A1)) 4
2 1-2-1 =LEN(TRIM(A2)) 6
3 121 =LEN(TRIM(A3)) 4

タブ文字を削除するにはSUBSTITUTE()関数を使用する。尚、Excelではタブ文字を直接入力する方法がないので代わりにCHAR(9)を使用する。

A B:数式 B:表示
1 001 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 3
2 1-2-1 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 5
3 121 =LEN(SUBSTITUTE(A1,CHAR(9),"")) 3

以上

2011/09/02 記事のカテゴリーを変更した。

2011年8月16日 (火)

EXCEL入門: 時間を切り上げ、切り捨て、四捨五入する。

とりあえず覚え書き。

EXCELには CEILING(),FLOOR(),MROUND()など、数値を指定された値の倍数になるように切り上げ、切り捨て、四捨五入する関数があるが、この倍数に時間を"0:05"などのように指定すると時間を丸めることができる。

使用例

 AB:数式B:結果
18:43 =MROUND(A1,"0:05") 8:45
29:15 =MROUND(A2,"0:05") 9:15
310:21 =MROUND(A3,"0:05") 9:20

以上

2011/09/02 記事のカテゴリーを変更した。