メンバー

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

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」などの数値を入力したくないこともあるかと思います。
そのような場合に関しても、好みの順番に並べ替える方法があります。

項目の並べ替え2

表の上に「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文の中の「_」は、プログラムを改行するために記述しています。

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

▼サンプルファイル▼

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

1. 開発準備

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

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

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

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

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

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

2. コードの記述

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

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

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

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

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

3. 完成

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

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

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

住所を入力する項目などに、以下のように全角文字と半角文字が混ざって入力されていることがあります。

このように、全角文字と半角文字が混ざっている状態は、以下のような問題点があります。

・一貫性がない：データを確認する際の誤解が生じる可能性がある
・データ処理が行いづらい：検索や並べ替え時に想定通りに出来なかったり、重複した内容を認識するのが大変になることがある
・外部システムとの互換性：システムによっては、全角文字のみしか入力を許可していないケースがあるため、そのままの貼り付けで入力ができなくなる

他にも、色んな理由が考えられますが、データを管理する場合は、ルールを統一して管理した方が良いです。
ただ、表を扱う全員が同じ認識でないと、ルールが守られない可能性があります。

ということで、こちらでは、入力した文字を強制的に全角文字に変換する方法について解説していきます。
※こちらで使用したファイルは、記事の最後にて配布しています。

1. 開発準備

こちらでは、VBAを活用して実現していきます。
特定のシートのセルに入力したタイミングに、自動で処理を行う場合は、シートモジュールを活用します。

まず初めに、[開発]タブの中の[Visual Basic]を選択します。

以下の画面が表示されましたら、該当するシートをダブルクリックし、表示された画面の上のリストから[Worksheet]を選択します。

次に、右側のリストから[Change]を選択します。
※「Option Explicit」が表示されていなくても問題ございません。

選択後に表示される[Worksheets_Change]というプロシージャを活用して実現していきます。

このプロシージャは、該当するシート上のセルを編集した後に実行される特殊なプロシージャ（イベントプロシージャ）になります。
引数の[Target]に、編集されたセルの情報が格納され、実行されます。

2. コードの記述

まず以下のように入力します。

こちらの内容は、「編集したセルの値を全角文字に置換して、自身のセルに上書きする」という処理になっています。
「StrConv」という関数が、指定した文字を指定した形式に置換した値を返すというものになります。
「Target」に編集したセルの情報が格納されるため、「Target.Value」というのは、そのセルの中に入力されている値を指します。
「vbWide」というのが、「全角文字に変換する」という設定になります。

プログラムの世界の「=」は、一般的に、「右辺の値を左辺に代入する」という意味になります。
そのため、こちらの内容は、全角文字に置換された値を自身のセルに上書きしていることを意味します。

これだけで、セルに入力した値を自動で全角文字に置換することができます。

ただ、この内容ですと問題点があります。
それは、住所の項目以外でも適用されてしまう点と、複数のセルをコピペした場合など、複数のセルに同時に値を入力した場合にエラーになってしまう点です。

そのため、その問題点を解決する必要があります。
解決するには、以下のように修正します。

すこし難しくなりました。
「Target」には複数のセルの情報が入ってくる可能性があります。
そのため、「Target」が持っているセルの情報を１つ１つに分解して処理を実行する必要があります。
「Dim r As Range」で「r」というセルの情報を格納する用の変数を用意しています。
※ここで言う「変数」とは、一時的に値を格納する用の入れ物のことを指します。

「For Each」にて、「Target」の内容を１つずつ順番に「r」に渡して実行しています。
「For Each」から「Next」までを、繰り返し実行します。

その１つのセルの情報「r」に対して、「If」にて、住所の項目の範囲内かどうかを確認しています。
住所の範囲は、B列の３行目以降になります。
そのため、「r.Row >= 3 And r.Column = 2」と、「r」のセルが３行目以上かつ２列目（B列）であることを確認しています。

「If」から「End If」の中のプログラムは、「r.Row >= 3 And r.Column = 2」という条件を満たしている時のみに実行されます。
その時に、全角文字に置換した内容を上書きする処理を実行します。

以上のように修正することによって、対象の範囲の指定と複数のセルにも対応させることができます。
そのため、住所が入力されている範囲をコピーし、同じ範囲に貼り付けることによって、一瞬で全角文字に変換することができます。

3. 問題点の解決

ここまでで、一応完成していますが、他にも懸念点が残っています。
それは、「膨大な範囲を一括で入力した際に、処理が完了するまで時間がかかってしまう」という点です。

入力したセルの数が多ければ多いほど、「For Each」での繰り返し回数が増えてしまうため、処理が完了するまでに時間がかかってしまうようになります。
私、個人的な感覚では、10,000セルよりも多い範囲を同時に入力した場合は、流石に処理の完了を待つのが辛くなるという印象です。

「そんなに同時に入力することがない」と考えていたとしても、「無意識に列ごとコピーして貼り付け」などを行うと、処理が完了するまでに膨大な時間がかかってしまうことになります。
そのため、なるべく対策はしておいた方が良いかと思います。

その対策を組み込んだプログラムは、以下になります。

「If」は「Then」の後に、１行で処理を記入した場合は、「End If」を省略することができます。
こちらでは、「Target.CountLarge」にて対象のセルの数を取得し、その数が10,000より多い場合という条件が設定されています。
この条件を満たしている場合は、「End」が実行されます。
「End」は処理を終了させるものになるため、対象のセルの数が10,000より多い場合は、下の処理が実行されることなく、処理が中断されます。

機能の開発は以上になります。
開発したファイルは以下よりダウンロードしていただけます。

システムを開発する場合は、利用するユーザーの行動を予想して開発していく必要があります。
システムには、エラーが付き物になります。
そんな中でも、なるべく快適に扱うために、予想できる想定外の操作を考慮して、開発していくのが良いかと思います。
※「予想できる想定外の操作」と若干矛盾している言い方ですが、「想定外によるエラーをなるべく無くす」という意味で表現しています。

条件付き書式のデータバーを活用して、以下のように指定した点数以下の色を変更する方法について解説していきます。

通常、条件付き書式のデータバーの設定では、値が正か負でしか色分けができません。
そのため、「60未満は赤色」などの設定をすることができません。

正か負以外での境界値を設定する機能が現時点ではないため、点数が入力されたセル自体にデータバーを作成することは難しいです。
ですが、先ほどの画像のように隣のセルに表示させるなどと工夫をすることで、正と負以外の境界値を設定して表示させることができます。

ではまずは、データバーを表示させるための値を、表の[データバー]という項目に入力していきます。

データバー用の値を入力

こちらでは、セルD2の点数を基準に色分けできるように作成していきます。

データバーで色分けできるのは、値の正か負のみになるため、合格点（セルD2）を用いて、基準点が0になるように作成していきます。
以下の画像の場合は、合格点が60なので、100点満点で考えた場合、-60～40の範囲で表現する必要があります。

そのため、以下のように、実際の点数から基準点を引くことで、0を基準とした値を求めることができます。

=D4-$D$2

基準点が入力されているセルD2のみを固定するため、「$」を付けて絶対参照にします。
セルE4の値を最終行まで、オートフィルを活用してコピーします。

このようにして、基準を合格点とした数値を入力することができました。

データバーの作成

次に、先ほど値を入力したセルにデータバーを作成していきます。

まず初めに、先ほど入力した[データバー]の項目を全選択し、[ホーム]タブの中の[条件付き書式]、[新しいルール]を選択します。

表示された画面にて、以下の設定を行います。

・ルールの種類：セルの値に基づいて…
・書式のスタイル：[データバー]を選択
・棒のみ表示：チェック（セルの値を表示しない設定）
・種類：共に[数式]を選択
・値：[最小値]には「=-$D$2」、[最大値]には「=100-$D$2」を入力
→セルD2を直接選択することで、「$D$2」と入力されます。

[値]の[最小値]と[最大値]に、100点満点の場合に、合格点が0点となるように調整した数式を入力しています。

次に、[負の値を軸]を選択し、負の値の表示内容を設定します。

表示された画面にて、[軸の設定]を[変更しない]にします。

この設定を行うことで、セルの左側の端を最小値として、データバーを表示することができます。

必要に応じて、データバーの色などを設定し確定します。
確定すると、以下のように合格点の値を基準にデータバーの色を変更することができます。

以下の表のステータスは、「着手中、完了」のリストになっています。
「着手中」を選択すると背景色を黄色に、「完了」を選択すると行全体をグレーになるように条件付き書式を設定しています。

このステータスの項目に関して、ダブルクリックするだけで、「着手中→完了→空白→…」と切り替えられるようにする方法について解説していきます。
※こちらで開発したファイルは、記事の最後にて配布しています。

1. 開発準備

特定のシート内のセルをダブルクリックすることで、何かしらの処理を実行するには、シートモジュールを活用します。

[開発]タブの中の[Visual Basic]を選択します。

次の画面にて、該当するシートをダブルクリックします。

プロジェクトの画面が表示されていない場合は、「Ctrl+R」もしくは、[表示]タブから表示することができます。

開かれたエディタの上の[General]と書かれているリストから[Worksheet]を選択し、その後、隣のリストから[BeforeDoubleClick]を選択します。
※この時、「Option Explicit」が表示されていなくても問題ございません。

リストから選択することで表示された「Private Sub Worksheet_BeforeDoubleClick(…」内にコードを入力します。

2. コードの記述

以下のコードを入力します。

このプロシージャは、該当するシートのセルがダブルクリックされた時に、自動で実行される特殊なプロシージャ（イベントプロシージャ）になります。

実行される時に、引数の「Target」にダブルクリックされたセルの情報が渡されます。
そのため、以下のコードにて、３行目以上かつ４列目の場合という条件を指定し、ステータスの範囲に関してのみ、処理が実行されるように分岐させています。

「Cancel = True」は、セルをダブルクリックした後の入力モードを取り消すためのものになります。
このコードを入れることによって、ダブルクリックした後に、セルが編集されることなく、値のみを切り替えることができます。

最後に、以下のIF文で、ダブルクリックしたセルの値に応じて、そのセルの値を切り替えるようにしています。

・「着手中」→「完了」
・「完了」→空白
・「着手中、完了」以外→「着手中」

以上の設定を行うことで完成です。

▼サンプルファイル▼

表の中にセル結合されている箇所が存在すると、正しく集計ができないことがあります。
セル結合は便利な機能ですが、表の中のセルを結合してしまうと、絞り込みや並べ替えといった操作が正しくできなくなります。

例えば、以下のような表に対して絞り込みをしてみます。
絞り込みをすると、以下の画像のように、正しく絞り込むことが出来ていないことが分かります。

セルの結合がされている場合、そのセルに入力されている値は、結合範囲の先頭のセルの値として管理されます。
そのため、上図のように先頭のみが絞り込みされたということになります。

では、並べ替えについても確認していきます。
先ほどと同じ表で並べ替えを行うと、以下のように警告が表示されます。

以上のように、結合されているセルは集計には向かないことが確認できます。

そのため、結合されているセルが表の中に存在する場合は、その結合を取り除く必要があります。
そこで今回は、結合されているセルを自動で色付けする機能の開発方法について解説していきます。

開発する具体的な内容としては以下のようになります。

「選択されている範囲内で、セルが結合されている場合に、その対象のセルを色付けする」

では、開発していきます。

開発手順

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

表示された以下の画面にて、好みの名前を入力し、[作成]を選択します。
こちらでは、”セル結合色付け”と入力しています。

表示された画面にて、以下のように入力します。

先頭のIf文で、選択範囲のセルの数が10000より多い場合は、メッセージボックスを表示させ、Endで処理を強制終了させています。
この処理はメインの処理ではありませんが、選択範囲が多いと、セル結合のチェックに処理時間がかなりかかってしまう可能性もあるため、選択範囲のセルの数に制限を設けています。

「For Each ～ Next r」の処理で、選択範囲(Selection)のセルを１つ１つ順番に変数rに格納して繰り返します。
For文の中のIf文で、変数rに格納されたセルが結合されているかどうかを確認しています。
結合されている場合は、「r.Interior.Color」にてセルの背景色を黄色（RGB(255, 255, 0)）にしています。

こちらの処理を、以下のシートの範囲に対して実行してみます。
実行は、[開発]タブの中の[マクロ]から作成したマクロを選択して、[実行]を選択する、もしくは、開発画面（VBE）の再生ボタン（右向きの三角マーク）を選択することで行えます。

このように、自動で色を設定することができました。
今回は、色を設定するという機能でしたが、必要に応じて、「結合を解除して、先頭のセルの値を全てのセルに格納する」というような処理にしても良いかと思います。

IF関数などを使う時に、TRUEやFALSEという言葉が出てきます。
TRUEは「正しい[真]」という意味で、FALSEは「正しくない[偽]」という意味になります。
IF関数の場合は、以下のように、論理式がTRUEかFALSEかによって返す値が異なります。

=IF(論理式,[値がTRUE(真)の場合],[値がFALSE(偽)の場合])
// 論理式が正しい場合は、TRUEの場合を返す、正しくない場合は、FALSEの場合を返す

このTRUEとFALSEの実体を理解することで、数式を作成する際に役立ちます。

TRUEとFALSEの実体

まずは、TRUEとFALSEの実体について確認していきます。
TRUEやFALSEという値をINT関数を用いて、数値として確認します。

=INT(数値)
// 数値を切り捨てて整数にした値を返す

INT関数の引数には、通常は”数値”でないとエラーになります。

しかし、TRUEとFALSEは、数値として認識されます。

このように、TRUEは「1」、FALSEは「0」として返されました。

このことを踏まえると、IF関数の論理式に数値を指定しても動作することが考えられます。
実際に、IF関数の論理式に数値を指定した時に返される値を確認します。

実際に確認すると、論理式に数値を割り当てた場合は問題なく動作し、「0」の時のみFALSEになることが確認できました。

通常の論理式に関しても、INT関数で囲って、返ってくる値を確認してみます。

論理式に関しても、比較結果がTRUEかFALSEで返ってくるため、実体としては、「0」と「1」になります。

AND関数とOR関数が不要！？

このことを踏まえると、以下のようにAND関数やOR関数を使わなくても、同じ意味の数式を作ることができます。

=AND(論理式1, [論理式2], [論理式3], … )
// 引数の論理式が全てTUREの時にTUREを返す

=OR(論理式1, [論理式2], [論理式3], … )
// 引数の論理式のいずれかがTUREの時にTUREを返す

=IF(AND(B3>=60,C3>=60,D3>=60),"合格","不合格")
=IF((B5>=60)*(C5>=60)*(D5>=60),"合格","不合格")

=IF(OR(B3>=60,C3>=60,D3>=60),"合格","不合格")
=IF((B5>=60)+(C5>=60)+(D5>=60),"合格","不合格")

AND関数とOR関数を複数使う場合に関しては、特にオレンジ色のような数式にした方が見やすくなるかと思います。
また、こちらでは細かな解説は省略しますが、FILTER関数で複数条件を指定する場合は、AND関数とOR関数が使えないため、オレンジ色のような数式で指定する必要があります。

＞FILTER関数の使い方

IF関数も不要！？

さらに、数式によっては、IF関数を使わずに表現することもできます。
例えば、以下の数式（残業時間を求める数式）を、IF関数を使わずに表現してみます。

=IF(C5>$C$2,C5-$C$2,0)

こちらの数式の場合は、論理式がFALSEの場合に「0」を返しています。
考え方によっては、以下のようにも言えます。

TRUEの場合は、計算結果に対し「1」を掛ける
FALSEの場合は、計算結果に対し「0」を掛ける

実際に数式で表現すると、以下のようになります。

=(C7-$C$2)*(C7>$C$2)

以上のように、TRUEとFALSEについて理解すると、数式で出来ることが広がります。