FinTech Dialy

FinTech Diary

FinTechのこととかクレジットのこととか。金融・ITの話題を中心に書いていきます。

【プログラミング】文字コードを変換するユーザー定義関数(ASCII⇒BASE64/EBCDIC)【エクセル】

仕事のついでに作った副産物的なものです。

また使う機会があるか微妙ですが、どなたかの参考になる可能性も微粒子レベルで存在しているかもしれないので、一応φ(..)メモしておきます。

 

 

作成した関数と変換結果

エクセルのマクロ(VBA)で、文字列の形式を変換するユーザー定義関数を作成した。関数の引数にセル番地を入力すると、参照先の文字列を各形式に変換する。

作成した関数と変換結果の例は以下の通り(例:セルA1=Natsume)。

No 文字列 関数 形式 備考
1 Natsume 文字列 プレーンテキスト(ASCII)
2 01001110011000010111010001110011011101010110110101100101 =Str2Bin(A1) バイナリ ASCIIコード値(2進)
3 7897116115117109101 =Str2Dec(A1) デシマル ASCIIコード値(10進)
4 4E617473756D65 =Str2Hex(A1) ヘキサ ASCIIコード値(16進)
5 TmF0c3VtZQ== =Str2Base64(A1) Base64 電子メール等で使用される文字コード
6 D581A3A2A49485 =Asc2Ebc(A1) EBCDIC メインフレーム等で使用される文字コード

 

VBAのコード

[1]参考サイトを参考に作成した関数

'文字列をバイナリ列に変換
Function Str2Bin(strText)
    hData = Str2Hex(strText)
    Str2Bin = Hex2Bin(hData)
End Function

'文字列をHexに変換
Function Str2Hex(strText)
  bData = String2Bytes(strText)
  sHex = Bytes2Hex(bData)
  Str2Hex = strConv(Replace(sHex, vbLf, ""), vbUpperCase)
End Function

'HexをBase64に変換
Function Hex2Base64(strText)
  bData = Hex2Bytes(strText)
  sBase64 = Bytes2Base64(bData)
  Hex2Base64 = Replace(sBase64, vbLf, "")
End Function

'文字列をBase64に変換
Function Str2Base64(strText)
  hData = Str2Hex(strText)
  sBase64 = Hex2Base64(hData)
  Str2Base64 = Replace(sBase64, vbLf, "")
End Function

 参考①:VBScriptでBase64やHex、バイナリを扱う関数をまとめてみた – でじたぬ

 

 

 

'Hexをバイナリ列に変換
Function Hex2Bin(strText)
s = "0123456789ABCDEF"
t = "0000000100100011010001010110011110001001101010111100110111101111"
For i = 1 To Len(strText)
    p = InStr(s, Mid(strText, i, 1))
    bit1 = Mid(t, (p - 1) * 4 + 1, 1)
    bit2 = Mid(t, (p - 1) * 4 + 2, 1)
    bit3 = Mid(t, (p - 1) * 4 + 3, 1)
    bit4 = Mid(t, (p - 1) * 4 + 4, 1)
    bita = bit1 & bit2 & bit3 & bit4
    Hex2Bin = Hex2Bin & bita
    Next i
End Function

'文字列をDec(ASCIIコード)に変換
Function Str2Dec(strText)
For i = 1 To Len(strText)
    p = Mid(strText, i, 1)
    d = Asc(p)
    Str2Dec = Str2Dec & d
    Next i
End Function

参考②:VBAで4桁の16進数から16桁の2進数変換 -VBAで4桁の16進数(FFFFとか)を2- Visual Basic(VBA) | 教えて!goo

 

 

 

'ASCIIをEBCDICに変換
Function Asc2Ebc(strText)
Dim AscCode As String
Dim EbcCode As String
Dim ArrayLen As Integer: ArrayLen = 256
Dim AscArray() As String
Dim EbcArray() As String
ReDim AscArray(ArrayLen)
ReDim EbcArray(ArrayLen)
Dim InCode As Long
Dim HexCode As String

AscCode = "000000100020001A00200026002D25332546254F2560256A007B007D003F0030" & _
          "000100110021001A001A2525002F25350061006A2141256B0041004A001A0031" & _
          "000200120022003221232527252425370062006B0073256C0042004B00530032" & _
          "000300130023003321562529252625390063006C0074256D0043004C00540033" & _
          "0004001400240034215725632528253B0064006D0075256F0044004D00550034" & _
          "000500150025003521222565252A253D0065006E007625730045004E00560035" & _
          "000600160026003621262567252B253F0066006F0077212B0046004F00570036" & _
          "000700170027003725722543252D2541006700700078212C0047005000580037" & _
          "00080018002800382521213C252F2544006800710079001A0048005100590038" & _
          "0009001900290039252325222531212E00690072007A001A00490052005A0039" & _
          "000A001A002A003A003F0021003F003A254825522561001A001A001A001A001A" & _
          "000B001B002B003B002E0024002C0023254A25552562001A001A001A001A001A" & _
          "000C001C002C003C003C002A00250040254B25582564001A001A001A001A001A" & _
          "000D001D002D003D00280029005F0027254C255B2566001A001A001A001A001A" & _
          "000E001E002E003E002B003B003E003D254D255E2568001A001A001A001A001A" & _
          "000F001F002F003F2822224C003F0022254E255F2569001A001A001A001A00FF"

EbcCode = "00102030405060708090A0B0C0D0E0F001112131415161718191A1B1C1D1E1F1" & _
          "02122232425262728292A2B2C2D2E2F203132333435363738393A3B3C3D3E3F3" & _
          "04142434445464748494A4B4C4D4E4F405152535455565758595A5B5C5D5E5F5" & _
          "06162636465666768696A6B6C6D6E6F607172737475767778797A7B7C7D7E7F7" & _
          "08182838485868788898A8B8C8D8E8F809192939495969798999A9B9C9D9E9F9" & _
          "0A1A2A3A4A5A6A7A8A9AAABACADAEAFA0B1B2B3B4B5B6B7B8B9BABBBCBDBEBFB" & _
          "0C1C2C3C4C5C6C7C8C9CACBCCCDCECFC0D1D2D3D4D5D6D7D8D9DADBDCDDDEDFD" & _
          "0E1E2E3E4E5E6E7E8E9EAEBECEDEEEFE0F1F2F3F4F5F6F7F8F9FAFBFCFDFEFFF"

For i = 1 To ArrayLen
    a = i * 4 - 3
    e = i * 2 - 1
    AscArray(i) = Mid(AscCode, a, 4)
    EbcArray(i) = Mid(EbcCode, e, 2)
Next i

For k = 1 To Len(strText)
    InCode = Asc(Mid(strText, k, 1))
    HexCode = Right$("0000" & Hex$(InCode), 4)
    For l = 1 To ArrayLen
        If HexCode = AscArray(l) Then
            Asc2Ebc = Asc2Ebc & EbcArray(l)
        End If
    Next l
Next k

End Function

参考③:VBS/EBCDICファイルをシフトJISに変換する | Tipi

参考④:EBCDIC - Wikipedia

参考⑤:ASCII 文字セットと EBCDIC 文字セット(IBM Knowledge Center)

参考⑥:【69542】10進→16進8桁への変換(Excel VBA質問箱 IV )

 

 

[2]参考サイトから引用させて頂いた関数

注:以下も[1]と一緒にマクロに保存しておかないと[1]の関数も動きません([1]の関数内で使用しているため)

'文字列をバイナリに変換
Function String2Bytes(strText)
  Set objEncoder = CreateObject("System.Text.UTF8Encoding")
  String2Bytes = objEncoder.GetBytes_4(strText)
  Set objEncoder = Nothing
End Function

'バイナリを文字列に変換
Function Bytes2String(Bytes)
  Set objEncoder = CreateObject("System.Text.UTF8Encoding")
  Bytes2String = objEncoder.GetString((Bytes))
  Set objEncoder = Nothing
End Function

'文字列をバイナリに変換 (Shift_JIS)
Function SJIS2Bytes(strText)
  Set objStream = CreateObject("ADODB.Stream")
  objStream.Open
  objStream.Type = 2  'adTypeText
  objStream.Charset = "Shift_JIS"
  objStream.WriteText strText
  objStream.Position = 0
  objStream.Type = 1  'adTypeBinary
  SJIS2Bytes = objStream.Read
  objStream.Close
  Set objStream = Nothing
End Function

'バイナリを文字列に変換 (Shift_JIS)
Function Bytes2SJIS(Bytes)
  Set objStream = CreateObject("ADODB.Stream")
  objStream.Open
  objStream.Type = 1  'adTypeBinary
  objStream.Write Bytes
  objStream.Position = 0
  objStream.Type = 2  'adTypeText
  objStream.Charset = "Shift_JIS"
  Bytes2SJIS = objStream.ReadText
  objStream.Close
  Set objStream = Nothing
End Function

'バイナリをBase64に変換
Function Bytes2Base64(Bytes)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("base64")
  objElement.DataType = "bin.base64"
  objElement.NodeTypedValue = Bytes
  Bytes2Base64 = objElement.Text
  Set objElement = Nothing
End Function

'Base64をバイナリに変換
Function Base642Bytes(strText)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("base64")
  objElement.DataType = "bin.base64"
  objElement.Text = strText
  Base642Bytes = objElement.NodeTypedValue
  Set objElement = Nothing
End Function

'バイナリをHexに変換
Function Bytes2Hex(Bytes)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("hex")
  objElement.DataType = "bin.hex"
  objElement.NodeTypedValue = Bytes
  Bytes2Hex = objElement.Text
  Set objElement = Nothing
End Function

'Hexをバイナリに変換
Function Hex2Bytes(strText)
  Set objElement = CreateObject("Msxml2.DOMDocument").createElement("hex")
  objElement.DataType = "bin.hex"
  objElement.Text = strText
  Hex2Bytes = objElement.NodeTypedValue
  Set objElement = Nothing
End Function

引用元:VBScriptでBase64やHex、バイナリを扱う関数をまとめてみた – でじたぬ

 

 

ASCII⇒EBCDICの変換について

変換テーブルは以下のように作成した。

wikiからEBCDICコード表をエクセルにコピペ

f:id:natsumedeus:20180321232705p:plain
 参考:EBCDIC - Wikipedia

 

②制御コード等を手動でASCIIコード(10進)に置換

f:id:natsumedeus:20180321232737p:plain

④DEC2HEX関数(桁数=4)でHEXに変換

f:id:natsumedeus:20180322000340p:plain

 

⑤CONCAT関数で結合 

⇒ASCIIコード配列(HEX4桁区切り)ができる。実際の関数では、4桁区切りで配列に格納する。

f:id:natsumedeus:20180321232910p:plain

 

EBCDICコード表を作る

f:id:natsumedeus:20180321232934p:plain

⑦CONCAT関数で結合

EBCDICコード配列ができる。実際の関数では、2桁区切りで配列に格納する。

f:id:natsumedeus:20180321232953p:plain

 

 

 おまけ

エクセル表のはてなブログへの貼り付け

エクセルの表をはてなブログに貼り付けるには、Googleスプレッドシートを利用するのがよい。

参考:ブログ記事に表(table)を入れる時はGoogleスプレッドシートを活用すべし|はてな・ワードプレス対応 | ANAマイルdeうりと行くスカイ?

 

文字コード・基数変換

下記サイトが色々できて大変便利。

単位変換、進数変換等の便利なWEBアプリ集

 基数変換

2進数、8進数、10進数、16進数相互変換ツール

 BASE64変換

データ変換ツール(BASE64, URLエンコード(URLデコード), HEX(16進ダンプ), MD5, SHA-1変換フォーム)

 

ASCII⇔EBCDIC

金融・決済関連事業者を支援する目的のサイト。メインはEMVというICカードの規格に関するツールだが、一部文字コードを変換するツールもある。

Character encoding conversion(emvlab.org)

 

応援チップについて

サイドバーに「応援チップ」を配置しました。

もし記事が何かの役にたつことがありましたら、チップして頂けるととっても喜びます。

   喜びの舞 (ノ゚д゚)ノソイヤ!゙(ノ゚д゚)ノソイヤ!゙(*'Д')bギュン!゙(*'Д')b゙

  

また、「はぁ~何このクソ記事、つっかえ…」という場合は、「ごっこ」からビットコインに似た何かをぶつけることができるので、憂さ晴らしに遊んでみてください。

 

 

 

おわり

BASE64はともかく、EBCDICて馴染みない人も多いかと思いますが、汎用機系の古いシステムではよく使われるんです…

 

そもそも、2進数とか16進数とかも、IT関係にお勤めでない方には意味不明かとは思いますが…私も、最初に2進とか16進の勉強をした時は理解不能でした(×_×)

2進だと1+1は2じゃなくて10になる…とかねw

 

 

 

では最後に一句…

 

文字コード みんな違って みんないい

                    なつめ

 

 

 

ごめなさいちょっと言ってみたかっただけです><;

 

広告を非表示にする