Excel関数 参照セルを規則的に増やす

Excelを用いて表を製作しています。

A1に　あいうえおかきくけこ
A2に　さしすせそたちつてと
A3に　なにぬねのはひふへほ
以下同様（本来は規則的な文字列ではなく不規則な13ケタの数字です）

このように入力されているものをMID関数で1つのセルに1文字ずつ表示しています。

B1=MID(A1,1,1)　C1=MID(A1,2,1)　D1=MID(A1,3,1)･･･
（B1 あ　C1 い　D1 う　E1　え　･･･）

これを2行に分け、
B1 あ　C1 い　D1 う　E1 え　F1 お
B2 か　C2 き　D2 く　E2 け　F2 こ
B3 さ　C3 し　D3 す　E3 せ　F3 そ
B4 た　C4 ち　D4 つ　E4 て　F4 と
以下同様

と表示したいのですが、以下のような数式が延々と続く形になります。

B1=MID(A1,1,1)　C1=MID(A1,2,1)　D1=MID(A1,3,1)･･･
B2=MID(A1,6,1)　C2=MID(A1,7,1)　D2=MID(A1,8,1)･･･
B3=MID(A2,1,1)　C3=MID(A2,2,1)　D3=MID(A2,3,1)･･･
B4=MID(A2,6,1)　C4=MID(A2,7,1)　D4=MID(A2,8,1)･･･

これをタテに見て、MID関数の
文字列は　　A1 A1 A2 A2 A3 A3･･･　と2つ連続で表示しながら1つずつ増加
開始位置は　列により　1,6,1,6･･･　2,7,2,7･･･　3,8,3,8･･･と2つの数字の繰り返し
文字数は　　全て変わらず1のまま

これを全て手打ちできればよいのですが、膨大な量があるのでオートフィル等を利用して簡単に数式を作るためにはどうしたらよいでしょうか？
素人が調べながらなんとか作った数式なので、根本的なやり方を変えたほうがよければご助言をいただけると助かります。
よろしくお願いいたします。

No.1 ベストアンサー 回答者： fujillin 回答日時： 2016/06/30 11:14

こんにちは

Ａ列は13文字ということなので、B2=MID(A1,6,1)と6番目でいいのか、なんだか疑問ですが・・・

とりあえずご質問文通りとして、B1に

　=MID(INDIRECT("A"&INT(ROW(A2)/2)),5*MOD(ROW(A2),2)+COLUMN(A1),1)

を入力して、右側、下側に必要な範囲にフィルコピーでどうでしょうか？

この回答へのお礼

ありがとうございます。
元データは13文字なのですが、実際には
1行目　1～4文字　(5文字目は非表示)
2行目　6～13文字　
と部分的にピックアップしているため6番目に設定していました。
混乱させてしまい申し訳ありません。
おかげさまでコピーして希望通りの表を作ることが出来ました。
ありがとうございました。

お礼日時：2016/06/30 13:45

No.2 回答者： 銀鱗 回答日時： 2016/06/30 11:29

セルの行や列の番号を値として使う関数がある。

・COLUMN関数は列を
・ROW関数は行を
それぞれ値として返す。

＝COLUMN(A1)
とB2セルに入力して、D3セルまでコピーすると
　B2セル　＝COLUMN(A1)
　B3セル　＝COLUMN(A2)
　C2セル　＝COLUMN(B1)
　C3セル　＝COLUMN(B2)
　D2セル　＝COLUMN(C1)
　D3セル　＝COLUMN(C2)
と数式中の参照セルが変わり、返ってくる値は
　B2セル　＝COLUMN(A1)　→１
　B3セル　＝COLUMN(A2)　→１
　C2セル　＝COLUMN(B1)　→２
　C3セル　＝COLUMN(B2)　→２
　D2セル　＝COLUMN(C1)　→３
　D3セル　＝COLUMN(C2)　→３
となる。
同様にROW関数でも
　B2セル　=ROW(A1)　→1
　B3セル　=ROW(A2)　→2
　B4セル　=ROW(A3)　→3
　C2セル　=ROW(B1)　→1
　C3セル　=ROW(B2)　→2
　C4セル　=ROW(B3)　→3
となる。

応用として、これにMOD関数やINT関数を組み合わせるという手法もある。
　＝MOD(ROW(A100),３)
とすると、100を3で割った余りを値として得られる。
0、1、2の値を繰り返して使いたいときに便利に利用できる。
※＝MOD(ROW(A100),3)+1 とすると、1,2,3の値を繰り返し…。
　＝INT(ROW(A100)/3)
ならば、100を3で割った商が値として得られる。
３つ同じ値を繰り返し、１つ増やしたものをまた３つ繰り返す…という使い方になる。
0,0,1,1,1,2,2,2,3,3,3,…のような値を得られる
※＝INT((ROW(A100)+2)/3) とすると、1,1,1,2,2,2,3,3,3,…

これらを工夫してみてください。
面倒ですが、意外と簡単にできたりします。

この回答へのお礼

ありがとうございます。
応用の部分の組み合わせ説明が分かり易く大変参考になりました。
今回は最初に回答を頂いた方をベストアンサーに選ばせて頂きましたが、
今後も様々な表を作る機会がありますので参考にさせて頂きます。

お礼日時：2016/06/30 13:49