メンバー

セルに入力されている文字から特定の文字の数を数式で求める方法について解説していきます。
こちらでは、以下の黄色のセルから「A」と「B」のそれぞれの数を数式で求めていきます。

・求め方

まずは、求める方法について考えていきます。
Excelには、セル内の特定の文字を数えるという機能は備わっていません。
そのため、数える仕組みを自身で作成する必要があります。
こちらでは、１つの方法について解説していきます。

こちらで紹介する方法は、「セル内の数えたい対象の文字を削除した文字列の文字数」と「元々の文字列の文字数」の差分を計算することで、対象の文字の数を求めます。

・数式の作成

では実際に、先ほどの仕組みを数式で求めていきます。
数式で求めるには、「特定の文字を消すための関数」と「文字の数を求める関数」を活用する必要があります。

「特定の文字を消すための関数」はExcelには用意されていないため、文字を置換するSUBSTITUTE関数を活用して求めていきます。

SUBSTITUTE関数の使い方は、以下の通りです

=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
// [文字列]の中の[検索文字列]を[置換文字列]に置換する
// 文字列：対象の文字列
// 検索文字列：置換したい文字列
// 置換文字列：置換する文字列
// [置換対象]：検索文字列が複数存在する場合に、先頭から何番目の対象の文字を置換するのかを数値で指定
// ※省略時は全ての検索文字列を置換

SUBSTITUTE関数を用いて特定の文字を消すためには、以下のように活用します。

=SUBSTITUTE(B2,"A","")

「文字の数を求める関数」は、LEN関数になります。

LEN関数の使い方は、以下の通りです。

=LEN(文字列)
// 指定した[文字列]の文字の数を返す
// 文字列：対象の文字列

以下のように活用します。

=LEN(B2)

SUBSTITUTE関数とLEN関数を用いて、特定の文字の数を求めていきます。

「セル内の数えたい対象の文字を削除した文字列の文字数」は、以下のように求めることができます。

=LEN(SUBSTITUTE(B2,"A",""))
// 「A」の文字を削除した場合の文字数

そのため、以下のように、元々の文字数から上記の文字数を引くことで、特定の文字の数を求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))

上記の内容は「A」の文字数になります。
「B」の文字数に関しても、以下のように求めることができます。

=LEN(B2)-LEN(SUBSTITUTE(B2,"B",""))

今回のように、関数を組み合わせることによって、好みの値を求めることができます。
特定の文字の数ではなく、他の値を求める場合などで、関数を組み合わせても求めることができない場合、もしくは、かなり複雑な数式になってしまう場合は、VBAを活用するのが良いかと思います。
（VBAとは、「Visual Basic for Applications」の略称で、ExcelやWordなどといったMicrosoft Officeのアプリケーションの機能を拡張することができるプログラミング言語になります。）

「文字を入力するだけで、その文字を含む名前のデータのみを瞬時に抽出することができる検索機能」を数式のみで実現する方法について解説していきます。
以下の画像では、セルH3に入力した文字を含む名前のデータを左の表から右の表に抽出しています。

こちらでは、完全一致検索と部分一致検索の２通りについて順番に解説していきます。
※使用したファイルは、記事の最後にて配布しています。

・完全一致検索の実現

特定の条件を満たすデータのみを抽出するには、FILTER関数が便利です。
FILTER関数は、365もしくはExcel2021以降で対応している関数になります。
FILTER関数の使い方は以下の通りです。

=FILTER(配列, 含む, [空の場合])
// 条件を満たすデータのみを抽出する
// 配列：対象の表
// 含む：抽出条件
// [空の場合]：対象が1件もない時に表示する値（省略時はエラーを表示）

この関数を使用して、以下のように記述することで実現することができます。

=FILTER(B3:E1000,C3:C1000=H3,"")
// B3:E1000：表の範囲はデータの増減に対応するため大きめに指定する
// C3:C1000=H3：検索対象の項目の行数は表の範囲と同じにする必要がある

この１つの数式だけで、以下のように対象のデータを抽出することができます。

こちらの場合は、対象が１件のみのため、１件のみが抽出されました。

・部分一致検索の実現

部分一致検索を実現するには、先ほどの以下の数式の「C3:C1000=H3」という条件式について理解する必要があります。

=FILTER(B3:E1000,C3:C1000=H3,"")

条件式について理解するために、まずは「C3:C1000=H3」という数式のみを入力してみます。

= C3:C1000=H3

入力して確定すると、以下のようにTRUEもしくはFALSEという値が表示されます。

TRUEもしくはFALSEという値は、指定した表のデータの数分表示されます。
このTRUEにあたるデータが条件を満たしていて抽出する対象のデータになります。

そのため、部分一致の検索を実現するには、検索欄「セルH3」の値が含むデータのみにTRUEと表示するような条件式を考える必要があります。

そのような条件式を実現するには、FIND関数とIFERROR関数を活用します。
FIND関数の使い方は、以下の通りです。

=FIND(検索文字列, 対象, [開始位置])
// [対象]に[検索文字列]が含まれる場合に、その文字列の先頭からの位置情報を返す
// 検索文字列：検索する文字列
// 対象：検索する対象の文字列
// ※[開始位置]に関しては、今回は使用しないため省略する

実際に活用した例は、以下になります。

=FIND(H3,C4)

入力して確定すると、以下のように見つかった位置の情報を返します。

ちなみに、検索文字列が見つからない場合は、エラーになります。

=FIND(H3,C3)

条件式では、0以外の数値がTRUE、0がFALSEとして扱われるため、エラーの値に関してIFERROR関数を用いて、0と表示していきます。
IFERROR関数の使い方は、以下の通りです。

=IFERROR(値, エラーの場合の値)
// 指定した[値]がエラーの場合に、[エラーの場合の値]を返す
// エラーでない場合は、指定した[値]をそのまま返す
// 値：エラーを検証する対象の値
// エラーの場合の値：エラーの場合に返す値

先ほどの数式にIFERROR関数を組み合わせると、以下のようになります。

=IFERROR(FIND(H3,C3),0)

入力して確定することで、以下のようにエラーの場合に0とすることができます。

後は、必要な行数分表示させる必要があるため、FIND関数の引数[対象]に複数の範囲を指定します。

=IFERROR(FIND(H3,C3:C1000),0) 

これだけで、必要なデータの数分のTRUEもしくはFALSEを意味する値を表示することができるため、こちらの条件式をFILTER関数に設定します。

=FILTER(B3:E1000,IFERROR(FIND(H3,C3:C1000),0),"")

入力して確定することで、部分一致の検索機能を実現することができます。

FILTER関数を用いて検索機能を実現することによって、元の表を触らずに対象のデータの抽出ができます。
検索した内容を残したい場合は、同じような検索機能をコピーして作成すると良いです。
コピーして作成した場合に関しても、元のマスタとなる表は１つのため、最新の表が分からなくなるというような事態を防ぐことができます。

こちらで作成した検索機能のExcelファイルは、以下になります。

以下の表の黄色のセルには数式が入力されています。
数式の内容は、「60点以上なら合格、未満なら不合格、空白なら空白」というものです。

この表に関して、不要になった行の値を削除する際に、数式のセルを含め行全体を選択して削除しても、数式だけは消えずに残ります。
そのため、何度でも再利用することができます。

このような行ごと削除できる数式の作成方法について解説していきます。

この数式を実現するには、「スピル」という機能が必要になります。
この機能はExcel2021以降かMicrosoft365で対応しております。

スピルという機能は、数式を入力したセルから溢れ出して数式の結果を表示させるというものになります。

まずは、下記の表のセルD3に「60点以上なら合格、未満なら不合格、空白なら空白」となるような数式をIFS関数で入力していきます。

IFS関数の使い方は以下の通りです。

=IFS(論理式1, 値が真の場合1, [論理式2, 値が真の場合2, …])
// 論理式1：結果がTRUEもしくはFALSEになる式
// 値が真の場合1：論理式1がTRUEの場合に表示する値
// 論理式2以上は必要に応じて指定することができます。
// 複数の論理式がTRUEになる場合は、一番先頭の「値が真の場合」に指定した値が表示されます。

IFS関数を使用して表現したものが以下になります。
※IFS関数で「その他」を指定するには、最後の論理式にTRUEと指定します。

=IFS(C3="","",C3>=60,"合格",TRUE,"不合格")

このように表現することができましたら、数式の参照する範囲を複数行にします。
今回の場合は、セルC3の点数の参照のみならず、セルC3からC7までを指定します。

そのように指定するだけで、以下のようにセルから溢れ出て結果が表示されます。
※スピルが対応していない場合は、以下のように表示されません。

=IFS(C3:C7="","",C3:C7>=60,"合格",TRUE,"不合格")

この数式は、セルD3のみに入力がされていて、他のセルD4からD7には入力されていません。
他のセルD4からD7に関しては、値を削除しようとしても削除することができません。
そのため、４行目以降に関しては、行ごと削除しても、数式のみが残るようになります。

ただ、このままですと、３行目を削除してしまった際に、数式がすべて消えてしまいます。
そのため、次に、３行目に関しても行ごと削除できるようにしていきます。

セルD3に数式を入力している場合は、このようなことが実現できないため、まずは数式を入力する位置から変更する必要があります。

そのため、セルD3の数式を削除し、セルD2の項目名の位置に数式を入力していきます。
項目名の位置に先ほどと同じ数式を入力し、１行上になるため、行番号を変更します。

=IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格")

このようにすると、項目名の位置に「合格」と表示されてしまいますが、３行目以降に関しては、行ごと削除することができるようになります。

次に、項目名が「合格」ではなく、「評価」となるように数式を修正していきます。
そのような数式を実現するには、ROW関数が活用できます。
ROW関数の使い方は以下になります。

=ROW([参照])
// 参照：指定したセルの行番号を返す（省略時は数式を入力したセルが指定される）

まずは、ROW関数を用いて、項目名に「評価」と表示し、それ以外のセルには「-」と表示する数式を作成していきます。

=IF(ROW(C2:C7)=2,"評価","-")

こちらでは、ROW関数での取得結果が２（２行目）の場合に「評価」、それ以外の場合に「-」を表示させています。

次に、先ほどの数式の「-」という表示を、IFS関数の内容に置き換えます。

=IF(ROW(C2:C7)=2,"評価",IFS(C2:C7="","",C2:C7>=60,"合格",TRUE,"不合格"))

このようにすることで、項目の位置に項目名を表示し、各行に関しては、数式の判定結果を表示することができました。
この場合、数式はセルD2にしか入力されていないため、３行目以降に関しては、行ごと削除しても数式が消えることはありません。

数式が消されないように「シートの保護」を活用することがありますが、部分的に保護すると、行ごとの削除ができなかったりと不便もあります。
そのため、スピルが使える環境の場合は、こちらのような数式で表現してみるのも良いかと思います。

以下のような更新履歴表に関して、「更新」ボタンを押して更新内容を入力するだけで、表の最終行に「更新日、更新者（パソコンにログオン（ログイン）したユーザー名）、更新内容（インプットボックスに入力した値）」が自動で入力される仕組みを開発していきます。

VBAを活用することで、簡単にパソコンに記録されている情報（パソコンにログオンしたユーザー名など）を取得することができます。
今回は、更新者としてパソコンにログオンしたユーザー名を取得していますが、他の値を取得する方法についても、確認用のマクロを配布して解説しています。

機能を開発するために、まずは更新履歴表を用意します。
今回こちらでは、以下のような表を用意しています。

５行目以降に関しては、罫線のみで値を入力していない状態にしておきます。
※用意した表のレイアウト（項目の位置など）によっては、開発するプログラムの内容が異なります。

次に、[開発]タブの中の[マクロ]を選択します。

※[開発]タブが表示されていない場合は、以下の手順をご確認ください。

＞マクロを開発する準備

以下の画面が表示されましたら、開発する機能の名前（こちらでは「更新」）を入力し、[作成]を選択します。

以下の画面が表示されましたら、「Sub 更新()～End Sub」の間にプログラムを入力していきます。
※環境によっては、「Option Explicit」が表示されていないことがありますが、今回はなくても問題ございません。

では次に、以下のようにプログラムを入力します。
※こちらを実装したExcelファイルは、記事の最後にて配布しています。

Sub内はTabキーにてインデント（字下げ）しています。
インデントの有無では処理に影響はないのですが、処理の見やすさの観点からインデントする癖をつけておいた方が良いです。

プログラムの内容について簡単に確認していきます。

まずは、以下の内容です。

「Dim」と書かれているのは、一時的に値を格納する部屋（変数）を用意する宣言（変数宣言）になります。
「As」の後に部屋の種類を記述しています。（「String」は文字専用の型）

次に、用意した「detail」という変数にインプットボックス「InputBox」に入力した値を格納します。

「InputBox」の括弧の中の文字は、インプットボックスに表示される文字になります。
イメージは以下になります。

次に以下の内容です。

「Dim」は先ほど同様に変数宣言になります。（「Long」は数値専用の型）

「Rows.Count」はシートに存在している最大の行番号になるため、「Cells(Rows.Count,”B”)」でシート上のB列の一番下のセルを指定しています。

このセルから、Ctrlキーを押しながら上に移動「.End(xlUp)」し、止まった位置の行番号「.Row」に１を加えた数を取得しています。
→ B列の下から上に向かっていく中で、次に文字が入力されているセルの一つ下の行番号（5）を取得

次に以下の内容です。

先ほど取得した行番号のB列のセルに実行した時の日付「Date」を入力し、C列のセルにパソコンにログオンしたユーザー名を入力、D列のセルにインプットボックスに入力した値を入力しています。 ユーザー名はEnviron関数を用いて取得しています。
※Environ関数で取得できる値については、最後に確認用マクロを配布して解説しています。

最後に以下の内容にて、Excelファイルを保存しています。

これで機能としては完成しているため、後はボタンを作成し、そのボタンに開発した機能を割り当てれば完成です。

ボタンは、[開発]タブの中の[挿入]から作成すると良いです。

上記のボタンを選択後、図形の作成時と同様にボタンを作成すると、[マクロの登録]画面が表示されます。
そこでは、開発したマクロの名前を選択することで、マクロを割り当てたボタンを用意することができます。
ボタンの表示名などは必要に応じて変更してください。
※作成したボタンを編集する場合は、Ctrlキーを押しながら選択します。

このようにして、ボタンを押してインプットボックスに更新内容を入力するだけで、表の最終行に値が入力されます。

・Environ関数について

最後に、Environ関数で取得できる値について、確認用のファイルを配布して解説していきます。

Environ関数で取得できる値は以下になります。

Environ関数の引数に上記の左側にある環境変数名を設定することで、その値を取得することができます。
先ほどのプログラムで使用した値は「USERNAME」になります。

どのような値が取得できるのかは、実際に試された方がイメージしやすいかと思いますので、各々で確認できるファイルを配布します。
配布ファイルのマクロの実行を許可し、「出力」ボタンを押すだけで、値を確認することができます。

今回開発した「更新履歴を自動で入力する機能のExcelファイル」と「Environ関数の出力確認用のExcelファイル」は以下になります。

ぜひ試してみてください。

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

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

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

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

「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」に貼り付けることで、色付けをリセットし、シートを再利用することができます。

通常、VLOOKUP関数でURLを取得しても文字の情報しか取得できないため、ハイパーリンクにはなりません。
※XLOOKUP関数などの関数でも同様です。

こちらでは、取得された文字をハイパーリンクとして取得する方法について解説していきます。

文字をハイパーリンクとして取得する方法について、以下の３つに分けて解説していきます。

・取得元の文字がURLの場合
・取得元の文字がメールアドレスの場合
・取得元の文字がハイパーリンクになる文字（URLなど）でない場合

では、順番に解説していきます。

・取得元の文字がURLの場合

取得元の文字が以下のようにURLの場合は、そのURLをそのままリンクにするだけになります。

URLをそのままリンクにして表示するには、HYPERLINK関数を活用します。

=HYPERLINK(リンク先, [別名])
// リンク先：ハイパーリンクとして設定したいURLなどのリンク情報
// [別名]：ハイパーリンクとして表示させたい文字（省略時はリンク先に設定した文字が表示される）

実際に活用した例が、以下になります。

=HYPERLINK(VLOOKUP(C11,C3:D7,2,0))

このようにリンクの状態を維持して表示させることができます。

・取得元の文字がメールアドレスの場合

取得元の文字が以下のようにメールアドレスの場合は、HYPERLINK関数で単純に囲むだけでは、正しいリンクとすることができません。

メールアドレスの場合は、以下のように入力する必要があります。

HYPERLINK("mailto:"&VLOOKUP(C11,C3:D7,2,0))

メールアドレスの先頭に「mailto:」を加えることで、メールアドレスのリンクとして活用することができるようになります。
ただ、表示される内容にも「mailto:」が加えられてしまうので、以下のように、HYPERLINK関数の引数の[別名]を設定する必要があります。

以下のように、VLOOKUP関数を２か所で指定する必要があります。

=HYPERLINK("mailto:"&VLOOKUP(C11,C3:D7,2,0),VLOOKUP(C11,C3:D7,2,0))

ちなみに、2020年の11月半ばに追加されたLET関数を活用すると、より簡潔に表現することができます。

=LET(x,VLOOKUP(C11,C3:D7,2,0),HYPERLINK("mailto:"&x,x))

LET関数については、以下の記事にて解説しています。

＞【Excel】セル参照や数式に名前を付ける「LET関数」

・取得元の文字がハイパーリンクになる文字（URLなど）でない場合

最後に、以下のように取得元の文字がURLなどのハイパーリンクになる文字ではない場合に、リンク状態を維持して取得する方法についてになります。

この場合、通常の関数で取得される文字の情報に、リンクの情報が一切含まれません。
そのため、リンクの情報を取得する用の関数を自作する必要があります。

リンクの情報を取得する関数の自作方法と活用例は、以下の記事にて解説しています。

＞【便利】リンク取得可能な検索関数

多くの場合、HYPERLINK関数を活用することで、効率的にリンクを作成することができます。
そのため、HYPERLINK関数を活用したことがない方は、ぜひ活用してみると良いかと思います。

以下のような表があります。
表の上の登録フォームに値を入力して「登録」ボタンを押すことで、表の先頭行（６行目）に挿入される仕組みをノーコードで実現していきます。

ノーコードで作成するために、こちらでは「マクロの記録」を活用します。
マクロの記録は、[開発]タブの中の[マクロの記録]から行えます。

「マクロの記録」とは、記録を開始してからの操作内容が自動で記録され、自動でVBA（マクロを開発するためのプログラム）を作成する機能になります。
無駄な操作を行うと、その内容まで記録されてしまうため、結果的に処理が重くなったり予期せぬエラーに繋がり兼ねます。
そのため、マクロの記録を開始する際は、予め記録する手順を確認してから行うと良いです。

また、記録された内容を放置すると機能がブラックボックス化（仕組みが分からない状態）します。
そうなると、正しく処理されるという保証がなくなります。
たまたま上手く実行出来ているけど、ある日を境に正しく実行できなくなるということが起こりやすいです。

これは、最近話題のChatGPTにVBAを書かせることに関しても同様です。
内容を理解せずに運用するということは、急に業務が回らなくなるリスクがあるということになります。

そのため、今回の内容に関しては、実質ノーコードで作成しますが、作成されたプログラムの内容についても振り返りたいと思います。

・記録する手順を確認する

まずは、記録する手順を確認します。
登録フォームに値が入力されている前提で、どのようにしたら、先頭行にデータが挿入されるかと考えます。

今回は、次のような手順を行います。

①登録フォーム全体を選択し、コピー（Ctrl＋C）する

②表の１行目を選択し、右クリックのメニューから[コピーしたセルの挿入]を選択する

③次の画面で[下方向にシフト]を選択し、[OK]で確定する

④再度、登録フォーム全体を選択し、値を削除（Delete）する

⑤登録フォームの日付の項目を選択する

この手順ならば、登録フォームの入力値に関わらず、常に対応ができるかと思います。
最後に、登録フォームの日付の項目を選択した理由は、次のデータの入力へと誘導するためです。

・マクロの記録を行う

[開発]タブの中の[マクロの記録]を選択します。

以下の画面にて、開発するマクロの名前（こちらでは「登録」）を入力し、[OK]で確定します。

次に、先ほどの①～⑤の手順を無駄な操作なく行います。
※無駄な操作を行うと、処理が正しく記録されなくなります。

手順が完了しましたら、[開発]タブの中の[記録終了]を選択します。

以上で、マクロが自動で作成されます。

・記録したマクロを確認する

記録したマクロは、[開発]タブの中の[マクロ]を選択することで、確認できます。
以下の画面から記録したマクロ名（こちらでは「登録」）を選択して[実行]を選択すると、記録した処理を実行することができます。
また、[編集]を選択すると、記録されたVBA（プログラム言語）の内容を確認することができます。
→VBAの内容についての解説と改善方法については、最後に解説します。

[実行]を選択し、記録した内容が正しく実行できることを確認します。

また、[編集]を選択して確認できるプログラムは以下になります。
※「Option Explicit」は設定によっては表示されません。

ただ、このプログラムには無駄な処理があります。
この無駄な処理によって、実行時に画面がパカパカするかと思います。
後から無駄な処理については解説するため、一度のこのままで実行ボタンを作成していきます。

・記録したマクロをボタンに割り当てる

記録したマクロは、好みの図形や画像、ボタンに割り当てることができます。
割り当てることで、その図形や画像、ボタンを選択するだけで、処理を実行することができるようになります。

ボタンを作成するには、[開発]タブの中の[挿入]にある[ボタン]を選択します。
※フォームコントロール内のボタンを選択してください。

選択後は、通常の図形と同じようにボタンを作成することができます。
作成後に、[マクロの登録]という画面が表示されるため、記録したマクロ名（こちらでは「登録」）を選択します。

ボタンの名前に関しては、好みの名前を設定します。
※ボタンを作成後に選択すると、処理が実行されてしまいます。そのため、作成後のボタンを編集する場合は、Ctrlキーを押しながら選択します。

これで機能を開発することができました。

・記録したマクロを改善する

記録したマクロの内容を細かく確認し、画面のパカパカに関しても改善していきます。

こちらのプログラムでは、「Sub」から始まり、次に登場する「End Sub」までの処理が、上から順番に行われます。

この中の「○○.Select」というのは、該当する要素を選択するという処理になります。
「Range(“B3:D3”).Select」の場合は、セルB3からD3を選択するという処理です。

また、この中に「Selection」というものがあります。
これは、その時点で選択しているものに対して処理を行うというものです。
「Selection.Copy」の場合は、その前の行でセルB3からD3が選択されているため、セルB3からD3をコピーするという処理です。
「Selection.Insert Shift:=xlDown」の場合は、その前の行でセルB6が選択されているため、セルB6を基準に下方向にコピーしたセルを挿入するという処理になります。
最後の「Selection.ClearContents」は、その前の前の行でセルB3からD3が選択されているため、セルB3からD3の値を削除するという処理になります。

「Application.CutCopyMode = False」は、コピーモードを解除するという処理になります。

この中の無駄な処理は、以下の２つになります。

・毎回セルを選択する必要がない → 画面がパカパカする原因に繋がる
・コピーモードの解除に関しては、セルの値を削除すると同時に勝手に解除されるため、記述する必要がない

この２点を改善したコードが以下になります。

最後の登録フォームの先頭を選択する処理に関しては「Select」を残していますが、それ以外に関しては全て修正しました。
実際は、これだけで同じ処理を実現することができます。

また、この処理の中には、毎回セルを選択する処理がないため、画面がパカパカしません。

こちらで使用したファイルは、以下にて配布します。

というような感じで、「マクロの記録」は便利な機能ではありますが、無駄が多いということも分かったかと思います。
AIも発達してきたため、より誰もがプログラムを開発しやすい時代にはなってきましたが、最低限の知識は今後も必要になることが考えられます。

「マクロの記録」の活用を機に、プログラムを学習してみるのも、良いかと思います。

以下のように指定した範囲（セルB2～F13）に関して、選択している行全体を色付けする方法について解説していきます。
こちらで作成したファイルは、記事の最後にて配布しています。

このような仕組みを実現するには、条件付き書式を活用します。
条件付き書式とは、指定した条件を満たしている時に、指定したセルの書式を変更するという機能になります。
色を設定する条件を考えると以下のようになります。

・現在選択している行番号と、対象の行番号が一致する場合

少しイメージが難しいかもですが、例えば、セルB2に以下のような式が設定されているとします。

=現在選択している行番号=ROW(B2)
// 「現在選択している行番号」は、実際には別の数式が入ります。

ROW関数では、引数に指定したセルの行番号を取得することができます。
そのため、2行目のセルのいずれかが選択されている時に、この条件を満たすことになります。

この条件を満たしている時に、対象のセルの背景色を変える必要があるため、この条件の数式を条件付き書式に設定する必要があります。

「現在選択している行番号」を取得するには、CELL関数を活用します。
CELL関数の引数に以下のように指定することで、現在選択しているセルの行番号を取得することができます。

=CELL("row")

複数のセルが選択されている場合は、アクティブになっているセルの行番号が取得されます。

先ほどの条件を表す数式にCELL関数を用いると、以下のようになります。

=CELL("row")=ROW(B2)

この数式を、条件付き書式にて設定していきます。

対象の範囲を選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面にて、[数式を使用して、…]を選択し、先ほどの数式を入力し、書式を設定します。

=CELL("row")=ROW(B2)
// 「B2」はアクティブセルのアドレスにする

このように設定することで、対象の範囲内に関して、選択している行全体を色付けすることができます。

ただ、条件付き書式の設定のみですと、色付けを反映するには、セルを編集するか数式を更新する必要があります。
その理由は、CELL関数で取得される値は、数式が更新されたタイミングに、値が更新されるためです。

そのため、対象の範囲が選択された場合は、自動で数式を更新する仕組みを実現する必要があります。

セルの選択と同時に数式を更新する仕組みを実現するには、シートモジュールを活用します。
[開発]タブの中の[Visual Basic]を選択し、表示された画面(VBE)から対象のシートモジュールを開きます。
※VBEに[プロジェクト]が表示されていない場合は、VBEの[表示]タブより表示することができます。
　「Option Explicit」は設定によっては表示されませんが、表示がなくても問題ありません。

次に、[General]と表示されているリストから、[Worksheet]を選択します。
[Worksheet]を選択することで、Worksheet_SelectionChangeというプロシージャが表示されます。
万が一、別のプロシージャが表示された場合は、右隣りのリストから[SelectionChange]を選択します。

このプロシージャは、該当するシートのセルが選択されたタイミングに自動で実行されます。
そのため、以下のように「数式を更新する」という内容を記述するだけで、セルの選択と同時に数式を更新させることができます。

ただ、この状態ですと、常に数式が更新されることになるため、Excelファイル自体の処理が重くなる原因に繋がります。
そのため、該当する範囲を選択した場合に関してのみ、数式が更新されるように記述する必要があります。

そのためには、以下のように記述します。

プロシージャの引数の「Target」に、選択されたセルの情報が格納されます。
そのため、IF文を用いて、「その対象の行番号が2以上かつ13以下、列番号が2以上かつ6以下である場合」という条件を満たしたときのみに、数式が更新されるように記述しています。
※IF文の中の「_」は、プログラムを改行するために記述しています。

このようにすることで、対象の範囲を選択した場合に関してのみ、その範囲の行全体を色付けすることができます。

▼サンプルファイル▼

以下のような表があります。
この表の[内容]という項目に何かしら入力すると同時に、[記入時刻]という項目に、入力時の時刻が自動で入力される仕組みの実現方法について解説していきます。

特定のシートの特定のセルに値を入力した後に、何かしらの処理を実行するには、「シートモジュール」を活用します。

まず、[開発]タブの中の[Visual Basic]を選択し、表示された画面(VBE)から、該当するシートモジュールを開きます。
※設定によっては「Option Explicit」が表示されないことがありますが、そのままでも問題ございません。

プロジェクトの画面が表示されていない場合は、[表示]タブのメニューから表示することができます。
プロジェクトの中に「Sheet1」と書かれている箇所があります。
その隣の括弧の中の文字（議事録）はシート名になります。

次に、シートモジュールの上の[General]と表示されたリストから[Worksheet]を選択します。

次に、右隣りのリストから[Change]を選択します。

この時に表示される[Worksheet_Change]というプロシージャにて開発していきます。
[Worksheet_SelectionChange]のプロシージャに関しては、削除しても問題ございません。

[Worksheet_Change]というプロシージャは、該当するシートのセルが編集された時に実行される特殊なプロシージャ（イベントプロシージャ）になります。
実行時に、引数の[Target]に操作されたセルの情報が格納されて実行されます。

このプロシージャに以下のように記述します。

If文の中の「Target.CountLarge = 1」で編集された対象のセルが１つであることを確認しています。
その上で、「.Row >= 5」と「.Column = 2」で、対象が５行目以上であることと２列目（B列）であることを確認しています。

この条件を満たした時に以下の処理が行われます。

こちらでは、対象のセルと同じ行のA列の値が空の場合に、そのセルに現在の時刻（Time）を入力するようになっています。

このような設定をすることで、以下のように[内容]の項目に何かしら入力すると同時に、A列の[記入時刻]の項目に入力時の時刻が入力されます。

今回開発したファイルは、以下からダウンロードできます。

このような機能を活用することで、会議の際の議事録として活用することができるかと思います。
また、会議を録音する際に、セルB1に録音の開始時刻を入力することで、後からの確認が容易になります。
開始時刻と記入時刻の差を表示する項目を用意すると、より便利になるかと思います。