小技集

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

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

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

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

「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関数についての詳しい解説は以下になります。

＞解説動画

表の更新したセルを自動で色付けする設定方法について解説していきます。

このようなことは、条件付き書式を活用することで実現できます。
条件付き書式とは、「特定の条件を満たしたセルにだけ特定の書式を設定する」という特殊な書式になります。

前半にお伝えする方法で実現は出来ますが、１点問題点があります。
後半に、その問題点を解決する方法について解説していきます。

・設定手順

まず初めに、色付けしたい対象のシートを複製します。
こちらでは、複製したシートに「BK」という名前を設定しています。

複製することができましたら、色付け対象のシート「顧客管理」を選択し、色付け対象のセルを選択します。
こちらでは、セルA1を基準にA列からE列を全体選択しています。

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

条件付き書式の設定画面にて、[ルールの種類…]を[数式を使用して、…]にし、以下の数式を入力します。

=A1<>BK!A1

数式の初めの「=」は、数式の開始の意味であるため、入力するものだと思っていただければと思います。
「A1<>BK!A1」は、設定しているシートのセルA1とBKシートのセルA1が等しくないという意味になります。

この時のセルA1とは、選択した時の基準のセルになります。
こちらでは、セルA1を基準に選択しているため、セルA1を基準とした数式を入力しています。

他のシートは、「シート名!」で指定します。
シート名によっては、「’シート名’!」とシングルコーテーションで囲む必要があるものもあります。
また、直接手入力すると入力を間違える可能性もあるため、「=A1<>」まで入力した後に、該当するシートのセルA1を直接選択して入力するのが良いかと思います。

ただ、「=A1<>BK!$A$1」と参照に「$」が表示され、絶対参照になってしまいます。
こちらで設定した数式は、選択範囲の他のセルに反映する際に、相対的に変化させる必要があるため、「$」は外す必要があります。

数式の入力ができましたら、[書式]を選択し、好みの書式を設定します。
こちらでは、[塗りつぶし]タブの中から黄色を選択しています。

以上の設定を行うことで、セルの値を変更すると同時に、そのセルを自動で色付けすることができます。

ただ、初めにお伝えした通り、１点問題点があります。
それは、全角文字と半角文字を正しく判定できないというところです。
先ほどの画像のように、「A」から「B」に変更した場合は色付けされますが、「A」から「a」に変更した場合は色付けされません。

では次に、この原因と対策について解説していきます。

・原因と対策

この原因は、条件付き書式に設定した「=A1<>BK!A1」という数式の「<>」にあります。
「<>」は等しくないという意味を表す記号ですが、大文字と小文字の違いを正しく判定することができません。

大文字と小文字の違いを判定するには、EXACT関数を活用します。
ただ、EXACT関数は、一致した場合にTRUEを返す関数になるため、今回の目的とは逆になってしまいます。
そのため、NOT関数を活用して、値を反転させる必要があります。

=NOT(EXACT(A1,B1))

このように、EXACT関数では、大文字と小文字の違いを正しく判定することができます。
ただ、欠点として、「文字列の数字」と「数値」を正しく判定することができません。

そのため、「<>」と「EXACT」の両方を活用し、「いずれかがTRUEの場合は一致しない」と判断する数式を作成します。
そのような数式は、以下のように表現できます。

=OR(A1<>B1,NOT(EXACT(A1,B1)))

条件付き書式に設定する数式に関しても、同じように編集します。
すでに設定した条件付き書式を修正する場合は、[条件付き書式]の中の[ルールの管理]を選択します。

表示された画面より、編集したい設定内容を選択し、[ルールの編集]を選択します。
設定した内容が表示されない場合は、[書式ルールの表示]の[現在の選択範囲]という設定を変更することで表示されるかと思います。

次の画面にて、以下の数式に変更して確定します。

=OR(A1<>BK!A1,NOT(EXACT(A1,BK!A1)))

以上の設定で、小文字と大文字に関しても対応することができます。

更新したシートの値を複製したシート「BK」に貼り付けることで、色付けをリセットし、シートを再利用することができます。