小技集

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も発達してきたため、より誰もがプログラムを開発しやすい時代にはなってきましたが、最低限の知識は今後も必要になることが考えられます。

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