Excel。結合したセルの値をまとめた一覧表を作りたいけど、コピペじゃ大変なんです。
＜ROW関数・IFERROR+INDEX+SMALL関数＞
セルの結合は設定するのが簡単なのですが、結合したセルの値を別のセルにコピーするとか、一覧表を作る場合、意外と大変というか、困ってしまうことがあります。
例えば、次の表のような処理をする場合で、説明していきます。
A列には、セル結合された担当者名が入力されています。
入力規則のリストなどで使うために、D列のように担当者一覧を作りたいとします。
今回は、4名なので、コピー&ペーストでも、どうにかなりますが、件数が多かったらコピー&ペーストでは大変です。
D2に「=A2」というセル参照を設定して、オートフィルで数式をコピーしても、できませんよね。
Excel VBAでマクロをつくってもいいのですが、マクロを作成すること自体も面倒です。
今回のような、結合セルの値から、一覧表をつくるには、ちょっとした関数を組み合わせれば、可能になります。
では、早速作っていきましょう。
C2に、次の数式を設定して、オートフィルで数式をコピーします。
=IF(A2<>"",ROW(A1),"")
このように、算出されます。
数式の説明をします。
縦方向にセルが結合されている場合、結合されている一番上のセルに値が設定されいます。
値が入っているか、どうかを判断したいので、IF関数を使います。
論理式は、「A2<>""」。
A2が空白ではなかったらと、問い合わせます。
真の場合は、「ROW(A1)」。
ROW関数で行番号をつかって、数値を算出させます。
義の場合は、空白 とします。
これによって、値があるセルの場所がわかりました。
続いて、D列にまとめる作業を行います。
D2に次の数式を設定します。
=IFERROR(INDEX($A$2:$A$10,SMALL($C$2:$C$10,ROW(A1))),"")
数式を説明していきます。
IFERROR関数を使うのは、このあと、オートフィルで数式をコピーすると、抽出データがないセルに、「#NUM!」というエラーが表示されるので、その防止のために使います。
INDEX関数をつかうことで、行番号と列番号が交差したデータを抽出することができます。
INDEX関数は、INDEX（参照,行番号,列番号）という引数をもっています。
参照は、担当者名を算出したいわけですから、$A$2:$A$10。
オートフィルで数式をコピーしますので、絶対参照を設定します。
行番号は、
SMALL($C$2:$C$10,ROW(A1))
SMALL関数は、その数値の順位の数値を算出します。
このために、先程のIF関数で算出したわけです。
SMALL関数は、SMALL（配列,順位）という引数をもっています。
配列は、IF関数で算出した範囲の、$C$2:$C$10
絶対参照も忘れずに設定します。
順位は、ROW(A1) A1を指定することで、「1」という数値を算出することができます。
オートフィルで数式をコピーすると、A2・A3とかわることで「2」「3」とスライドすることができます。
列番号は、今回複数列ではないので、省略します。
すると、このような結果になりました。
このように、セル結合した値でも一覧表を作ることができました。
最後に、C列の数値を表示してあると、カッコ悪いので、非表示にしますので、C2:C10を範囲選択して、セルの書式設定ダイアログボックスを表示します。
表示形式の「ユーザー定義」にして、種類に「;;;」（セミコロン×3）と設定します。
これで、文字を非表示することができました。
セルの結合は、見た目としては、便利なのですが、そのあとに、何かしたい場合は、うまくいかないことがありますので、見た目を重視する必要がない場合は、データベースとして表を作る方がいいかと思います。
