作者別: it-yobi

今回は、データの増減に対応させた罫線を設定する方法について解説していきます。
また、以下のようにカテゴリーなどの特定の項目の値を元に、罫線の種類を変更する方法についても解説していきます。

①表の増減に対応させた罫線を設定

まずは、以下の表に対し、データの増減に対応させた罫線を設定します。
自動で表を拡張する機能として「テーブル」がありますが、こちらではテーブルを使わずに設定していきます。

特定の条件を満たしている時に、罫線などの書式を変更するには、「条件付き書式」を活用します。
今回の場合、以下の条件を元に罫線を設定していきたいと思います。

「Noの項目に値が入力されている場合」

まずは、設定する対象のセルを選択する必要があります。
今回の場合は、セルA3からC列の一番下までのセルに設定していきます。

その場合、AからC列を全選択し、Ctrlキーを押しながら不要なセルを選択して、選択を解除することで、以下のように対象のセルのみを選択します。

選択できましたら、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

以下の画面にて、[ルールの種類]を[数式を使用して、…]にし、テキストボックス内に以下の数式を入力します。

=$A3<>""

テキストボックス内に設定する数式は、現在の選択基準のセル（A3）に対しての数式である必要があります。
選択基準のセルに罫線を設定する時は、選択基準のセルに値が何かしら入力されている場合になるため、こちらでは、「選択基準のセルが空白でない場合（=$A3<>””）」と入力しています。

他の選択範囲に関しては、入力した数式がコピーされて設定されます。
そのため、相対参照や絶対参照を意識した数式を入力する必要があります。
こちらの場合は、値の有無を確認するのは必ずA列になるため、「$A3」とA列のみに「$」を加え固定しています。

数式の入力ができましたら、[書式]より罫線の設定を行います。

こちらで設定した内容は、「点線の下の罫線」になります。
※罫線の設定画面の予め表示されている太い薄い線は、未設定という意味になります。

この設定で確定することによって、以下のようにデータの増減に対応させて罫線を設定することができました。

②特定の項目の値を元に罫線を設定

次は、特定の項目の値を元に罫線を設定する方法について解説していきます。
こちらでは、カテゴリーの項目の値を元に、以下のように罫線を設定し、同じカテゴリーのデータを目立たせていきます。

このような設定を行う場合に関しても、「条件付き書式」を活用することで実現できます。

まずは、条件を考える必要があります。
今回の場合は、以下のような条件になります。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」

まずは、設定する対象のセルを選択する必要があります。
先ほどの同じ範囲を選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

現在の選択基準のセルは、先ほどと同様にセルA3になるため、セルA3に対する条件を数式で表現していきます。

「Noの項目に値が入力されている場合、尚且つ、カテゴリーの項目の値が１つ下の値と異なる場合」という条件を数式で表現すると、以下のようになります。

=AND($A3<>"",$B3<>$B4)

こちらでは、AND関数で「Noの項目に値が入力されている場合（$A3<>””）」と「カテゴリーの項目の値が１つ下の値と異なる場合（$B3<>$B4）」を設定しています。

この条件式を、[数式を使用して、…]のテキストボックス内に設定し、[書式]より罫線を設定します。

これで確定することにより、以下のようにカテゴリー単位でも罫線を設定することができました。

[補足]

複数の条件付き書式を設定しているセルで、複数の条件を満たしている場合、基本は最後に設定した条件付き書式が優先されて反映されます。
優先順位を変更したい場合は、[ホーム]タブの中の[条件付き書式]の[ルールの管理]より行えます。

FILTER関数は、以下のように指定した表のデータから指定した条件に合ったデータのみを抽出する時に活用する関数になります。

実は、必要な項目のみを抽出する際にも、FILTER関数を活用することができます。
今回は、指定した項目をFILTER関数で抽出する方法について解説していきます。

抽出する方法はいくつかあるため、順番に解説していきます。

FILTER関数の使い方は次の通りです。

=FILTER(配列, 含む, [空の場合])
// 配列：絞り込み対象の表の範囲など
// 含む：絞り込み条件
// [空の場合]：条件を満たす対象のデータが見つからない時に表示する内容（省略時はエラーが表示される）

この含むという条件ですが、通常は以下のように指定します。

=FILTER(B3:F12,E3:E12="男")
// 性別の項目が「男」の場合

ここで設定されている条件式は「E3:E12=”男”」になります。
この条件式は、「E3:E12」の１列に対しての式になります。

実は、ここで指定する式を行方向にすることで、行に対しての絞り込みができます。
行に対しての絞り込みを行うことによって、必要な項目のみを抽出することができます。

まずは簡単な方法から確認していきます。
必要な項目のみを抽出する場合、その対象の項目名の上に「・」などの任意の文字を入力します。

次に、その項目名の上の文字を条件式に加えます。
例えば、上記の画像のように「・」という文字を入力している場合は、抽出する条件式は「B1:F1=”・”」になります。

実際に、その条件式を用いて数式を作成していきます。

=FILTER(B3:F12,B1:F1="・")

このように必要な項目のみを抽出することができました。

抽出した項目に対して、更にデータの絞り込みを行いたい場合は、このFILTER関数をFILTER関数の配列の引数に設定するという方法で実現します。

=FILTER(FILTER(B3:F12,B1:F1="・"),E3:E12="男")

ただ、この方法ですと、項目名の上に「・」などといった文字を入力する必要があります。
そのため、次に「・」などといった文字を入力せずに項目を抽出する方法について解説していきます。

そのためには、「B1:F1=”・”」というような条件式が何を表しているのかを理解する必要があります。

試しに、「B1:F1=”・”」という条件式を単体で入力してみます。

= B1:F1="・"

入力すると、一行のTRUEとFALSEで構成された値が表示されるかと思います。

つまり、FILTER関数の条件式に設定する値とは、「抽出したい位置がTRUEになり、それ以外がFALSEとなるような式」であれば良いということが分かります。

直接、TRUEとFALSEで構成された数式を入力するには、カンマ区切りの値を「{」と「}」で囲みます。

={TRUE,FALSE,FALSE,TRUE,TRUE}

このように直接入力することができます。

また、TRUEは0以外の数値、FALSEは0としても表現することができます。
実際に、1と0に置き換えてみると以下のようになります。

={1,0,0,1,1}

この式をFILTER関数に設定することで、項目名の上に「・」などといった文字を入力しなくても、好みの項目のみを抽出することができます。

=FILTER(B3:F12,{1,0,0,1,1})

このように好みの項目のみを抽出することができました。

ただ、「{1,0,0,1,1}」などといった値を入力するのは、若干の手間になります。
1と0を入力している時に、誤って１つずつずれてしまう可能性もあります。

そのため、以下のようにIF関数やSWITCH関数などを用いて表現するのも良いかと思います。

IF関数の場合は、以下のようになります。

=IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0)
// 抽出したい項目を「(B2:F2=B2)」のように「＋」で並べる
// 「+」はORという意味なる

=FILTER(B3:F12,IF((B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2),1,0))

ちなみに、以下のようにIF関数を使わなくても表現することができます。

=FILTER(B3:F12,(B2:F2=B2)+(B2:F2=E2)+(B2:F2=F2))

SWITCH関数の場合は、以下のようになります。

=SWITCH(B2:F2,B2,1,E2,1,F2,1,0)
// 抽出したい項目を「B2,1」のように必要な分を並べる
// 「B2」などと指定された項目名の場合は1、それ以外は0が返される

=FILTER(B3:F12,SWITCH(B2:F2,B2,1,E2,1,F2,1,0))

これらは例になるため、他の方法を考えてみるのもよいかと思います。

SORTBY関数を用いて項目を好みの順番に並べ替える方法について解説していきます。

まずは、SORTBY関数について解説していきます。

SORTBY関数は、表を並べ替える関数になります。

=SORTBY(配列, 基準配列1, [並べ替え順序1], [基準配列2, 並べ替え順序2], … )
// 配列：対象の表
// 基準配列1：並べ替えたい基準
// [並べ替え順序1]：基準配列1の順序を指定（1=昇順, -1=降順）※省略時は昇順
// [ ]に囲まれている引数は省略可能

例えば、以下の左の表に関して、「性別」の項目を基準に昇順にする場合は、以下のような数式になります。

=SORTBY(B3:E12,D3:D12)

「性別」の項目を基準に降順にする場合は、以下のような数式になります。

=SORTBY(B3:E12,D3:D12,-1)

また、[基準配列2, 並べ替え順序2]以降の引数を設定することで、複数の項目を基準に並べ替えることができます。
例えば、先ほどの結果に加えて、「生年月日」の項目を昇順する場合は、以下のようになります。

=SORTBY(B3:E12,D3:D12,-1,E3:E12,1)

次に、本題の項目の並べ替えです。

SORTBY関数は、列方向の並べ替えにも対応しています。
列方向に並べ替える場合は、基準配列の引数に横向きの範囲を指定します。

例えば、以下の左の表のように、項目名の上に数値を入力することで、その数値を基準に並べ替えることができます。

=SORTBY(B2:E12,B1:E1)

このように項目を好みの順番にすることができます。
ただ、表の上に「1,3,2,4」などの数値を入力したくないこともあるかと思います。
そのような場合に関しても、好みの順番に並べ替える方法があります。

表の上に「1,3,2,4」などと直接入力しない場合は、『配列定数』を活用します。
配列定数を用いて、「1,3,2,4」を参照する場合と同じ値を表現します。

配列定数の活用例は以下になります。

={1,3,2,4}
// 配列定数として列方向の値を表現する場合は、「{ }」で囲み、「,」区切りで値を指定する

配列定数を用いることで、以下のように表現することができます。

=SORTBY(B2:E12,{1,3,2,4})

FILTER関数を用いて、一部のデータを抽出した後に、項目の順番を並べ替えたい場合は、SORTBY関数と組み合わせると良いです。
先ほどの表から「性別」が「男」のデータのみを抽出した例が以下になります。

=SORTBY(FILTER(B3:E12,D3:D12="男"),{1,3,2,4})

SORTBY関数についての詳しい解説は以下になります。

＞解説動画

FILTER関数についての詳しい解説は以下になります。

＞解説動画