ChiquilinSite
★はじめに：組換えの必要性
下記の Ａ２：Ｄ４セルのような データを Ｆ１：Ｆ１５セルのように並べ直したい時 あなたならどうしますか？ エクセルに慣れていない人なら 手で作業するかもしれませんね。この逆のパターンで
Ｆ１：Ｆ１５セルのように並んだデータを Ａ２：Ｄ４セルの形に組み換える場合はどうでしょう。
データベースを アクセスで管理していれば 表の組換えで悩んだりはしないでしょうが やはり Excelを使う職場は多いでしょうし 別アプリケーションとの連動を図る上でも
データを望みの形に作り変えなければならない場面というのはありえましょう。 またマクロを使えばこのような処理は特別難しくないでしょう。この手の作業は
マクロ向きです。ただ数式だけですんなりできるなら それはそれで便利と思いませんか？ それが実はそれほど難しくはないとしたら。
★その１：規則性を把握する
上記の表の場合 一定の法則に従ってデータが並べればいいことは分かりますね？ 組換えをする時もっとも大事なのは どのような規則に従って組み替えればいいのかを把握することです。
分かりやすくする為に Ｅ列に連番を振りました。ルールは Ｇ列に書いた通りです。
１. 行の順番は ２行目から始まって ５行ごとに１ずつ増加する。
２. 列の順番は「３・４・１・２・×」を繰り返す
３. ５の倍数の時 データを表示しない
この内 一番簡単なのは「３」です。５の倍数というのは「５で割った時に余りが出ない数値」ですから ＭＯＤ関数を使えば条件を振り分けできます。
■Ｆ１セル
=IF(MOD(E1,5)=0,"",参照式)
このように条件を分けてやれば ５の倍数の行は表示されなくなります。
★その２：行番号と列番号を計算する
次に「１」の条件を計算します。「２から始まる」と「５行ごとに１ずつ増える」とは分けて考えます。といっても区切りになる行数で割ってやるだけです。
■Ｈ１セル
=E1/5
■Ｉ１セル
=INT(H1)+2
勿論 この計算は分けずにまとめてしまうこともできます。
=INT((E1+9)/5)
５行目は表示させませんから「+9」でも「+10」でもいいです。
続いて 列番号を計算しましょう。「３・４・１・２」とバラバラの順番ですから 難しいように思うかもしれませんが ＣＨＯＯＳＥ関数を使えば規則に従って番号を返すことができます。
■Ｈ１セル
=CHOOSE(E1,3,4,1,2,0,3,4,1,2,0,3,4,1,2,0)
ただこの方法では データが多いと大変です。そもそも ＣＨＯＯＳＥ関数は３０個までしか指定できません。そこで連番を「１・２・３・４・５・１・２・３・４・５……」と繰り返すように加工します。
=CHOOSE(MOD(E1-1,5)+1,3,4,1,2,0)
今回は ５行目を表示させませんので ５の倍数行でエラーが返りますが
=CHOOSE(MOD(E1,5),3,4,1,2)
としてもいいです。これで行番号と列番号が計算できましたので 後はＩＮＤＥＸ関数を使って 参照するだけです。
★その３：ＩＮＤＥＸ関数で参照
■Ｆ１セル
=IF(MOD(E1,5)=0,"",T(INDEX(A:D,(E1+10)/5,CHOOSE(MOD(E1,5),3,4,1,2))))
Ｔ関数は 参照しているセルが空白だった時に何も表示させない為に入れています。式の後に「&""」を入れても同じ結果になります。数値を参照する時には使えませんから外します。詳しくは「
検索結果が空白の時に空白を返す」を参照して下さい。
今回は連番を振りましたが ＲＯＷ関数や ＣＯＬＵＭＮ関数を使ってしまえば 連番を式の中に組み込むこともできます。
=IF(MOD(ROW(F1),5)=0,"",T(INDEX(A:D,(ROW(F1)+10)/5,
CHOOSE(MOD(ROW(F1),5),3,4,1,2))))
こんな感じです。逆のパターンで参照する場合もやることは概ね似ています。
■Ｃ２セル
=T(INDEX(A:A,B2*5-2))
■Ｄ２セル
=T(INDEX(A:A,B2*5-1))
■Ｅ２セル
=T(INDEX(A:A,B2*5-4))
■Ｆ２セル
=T(INDEX(A:A,B2*5-3))
全部同じ式にするなら
■Ｃ２セル
=T(INDEX($A:$A,ROW(A1)*5-CHOOSE(COLUMN(A1),2,1,4,3)))
慣れると それほど考えなくてもできるようになると思います。
→参考ファイル(039.xls)
