EXCEL、3桁区切りで罫線を表示する


EXCELで、<E列>入力用セルと書いてある列の数値を<A><B><C><D>の様に3桁区切りの罫線で表示したい場合、 (右から3桁毎にセルを分けて表示)IF関数を使わずに済ませる方法は無いのか考えてみました。

 ABCDE
1表示用セル入力用セル
2   00
3   11
4   1212
5   123123
6  12341,234
7  1234512,345
8  123456123,456
9 12345671,234,567
10 1234567812,345,678
11 123456789123,456,789
1212345678901,234,567,890
131234567890112,345,678,901
14123456789000123,456,789,000

 結論としては、<E列>の数値の左側に半角の空白を12個加えて、RIGHT関数で右から12文字切り出してから、 MID関数で必要な位置の数字を3文字づつ切り出し、最後にTRIM関数で余計な空白文字を削除してあげる方法が楽そうです。
 この考え方を使って式はいくつか作れるのですが、先ずは、計算用のセルを作らずにダイレクトに3桁区切りのセルを作成してしまう 方法です。

EXCELをお持ちの方は下記の表(表示用セルなどと書かれた行を含めて)をEXCELの<A1>の位置に貼り付けてみて下さい。
表示用セル入力用セル
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E2,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E2,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E2,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E2,12),10,3))0
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E3,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E3,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E3,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E3,12),10,3))1
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E4,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E4,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E4,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E4,12),10,3))12
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E5,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E5,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E5,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E5,12),10,3))123
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E6,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E6,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E6,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E6,12),10,3))1234
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E7,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E7,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E7,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E7,12),10,3))12345
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E8,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E8,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E8,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E8,12),10,3))123456
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E9,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E9,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E9,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E9,12),10,3))1234567
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E10,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E10,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E10,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E10,12),10,3))12345678
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E11,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E11,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E11,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E11,12),10,3))123456789
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E12,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E12,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E12,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E12,12),10,3))1234567890
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E13,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E13,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E13,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E13,12),10,3))12345678901
=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E14,12),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E14,12),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E14,12),7,3))=TRIM(MID(RIGHT(REPT(CHAR(32),12)&E14,12),10,3))123456789000
貼り付けたら、 と見やすくなります。 その後、別のセルへ移動したり列を挿入したりの改造を行うと楽にできます。もちろん、表はオートフィル機能で下方向へドラッグして、 行数を追加することも可能です。

見かけ上はスッキリしていますが、式が何だか膨大ですね…。別解もいずれ考えてみたいところです。

ちなみに、9桁の場合はこんな感じです!!
表示用セル入力用セル
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D2,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D2,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D2,9),7,3))0
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D3,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D3,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D3,9),7,3))1
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D4,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D4,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D4,9),7,3))12
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D5,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D5,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D5,9),7,3))123
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D6,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D6,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D6,9),7,3))1234
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D7,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D7,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D7,9),7,3))12345
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D8,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D8,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D8,9),7,3))123456
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D9,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D9,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D9,9),7,3))1234567
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D10,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D10,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D10,9),7,3))12345678
=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D11,9),1,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D11,9),4,3))=TRIM(MID(RIGHT(REPT(CHAR(32),9)&D11,9),7,3))123456789

2005.11.24

[HOME][とまて週報TOP]