頭がおかしくなりそうなExcelの数式活用術3選

2020.08.04

カテゴリー：
JOB/BUSINESS

頭がおかしくなりそうなExcelの数式活用術3選 — Image: Princess_Anmitsu / Shutterstock

Excelの数式は大体どんなことでもできます。今回は、MicrosoftのExcelの数式と条件付き書式にできることを、便利な参考例と共にご紹介していきます。

MicrosoftのExcelを徹底研究

カレンダーのテンプレートを作成する、プロジェクト管理ツールとして使うなど、これまで様々なExcelの利用方法をたくさんご紹介してきました。

Excel能力の根底には、スプレッドシートに挿入するデータに関わらず、データや情報を自動的に処理することができる数式やルールがあります。

MicrosoftのExcelを上手に使いこなすために、数式やツールの使い方を徹底的に研究しましょう。

Excelの数式を使った条件付き書式

｢条件付き書式｣は、Excelであまり使われていないツールのうちのひとつです。

Excelの条件付き書式に関する、より詳細な情報を探している人は、Sandyの｢Microsoft Excelの条件付き書式を使ったデータ書式｣の記事をチェックしてください。

Excelの数式やルールやごく簡単な設定を使うだけで、スプレッドシートを自動化されたダッシュボードに変えることができます。

条件付き書式を使うには、｢ホーム｣をクリックし、ツールバーの｢条件付き書式｣のアイコンをクリックします。

条件付き書式の中には、たくさんのオプションがあります。

この記事ではほとんど紹介できませんが、中でも主要なのは、セルの値に基づいて強調表示したり、特定の色に塗りつぶしたり、網掛けをしたりするものです。

これは、おそらく条件付き書式でもっともよく使われていると思います。ある数値よりも大きい、もしくは小さい場合はセルを赤色にするというようなものです。

このような条件付き書式の使い方についてもっと知りたい人はこちらをどうぞ。

あまり使われていない条件書式のうちのひとつに、｢アイコンセット｣があります。Excelのセルのデータをダッシュボード表示のアイコンに変える、素晴らしいアイコンセットがあるのです。

｢ルールの管理｣をクリックすると、条件付き書式のルールの管理が開きます。

アイコンセットを選ぶ前に選択したデータを元に、管理画面にアイコンセットが表示され、その中から選ぶだけです。

｢ルールの編集｣をクリックすると、その条件の設定をする画面が表示されます。

ここで、希望通りにダッシュボードのアイコンを表示させるために、論理式や方程式を作成することができます。

以下の例では、ダッシュボードに様々なタスクにかかった時間と、予定していた時間が表示されます。予定時間の半分を超えると黄色のライト、完全に超えていると赤のライトが表示されます。

このダッシュボードを見てわかるように、予定の時間を守れていません。

ほぼ半数以上が予定した時間を超えています。

時間をもう一度意識して、もっと上手に時間を管理しましょう！

1. VLOOKUP関数を使う

Microsoft Excelのもっと上級者向けの機能を使いたい人には、こちらをどうぞ。

そういう人は、VLOOKUP関数を使ったことがあるのではないでしょうか。ある列の特定の値を検索し、指定した列と同じ行にある別の値を返します。

残念ながら、この関数には、検索する項目が左側の列にあり、検索している値が右側になければなりませんが、入れ替えた場合はどうなるでしょうか？

例えば以下の表で、2018年6月25日に作業したタスクを、次のデータから探したい場合はどうすればいいでしょうか？

この場合は、右側で値を検索し、左側で一致する値を返したいので、通常のVLOOKUP関数とは逆のやり方です。

Microsoft Excel のプロユーザーの掲示板を見ると、VLOOKUP関数では不可能だから、これをやるにはINDEX関数とMATCH関数の組み合わせを使わなければならない、と言う人が多いです。これは完全に正しいわけではありません。

CHOOSE関数をネストさせて、VLOOKUP関数でこれをやることができます。この場合、Excelの数式は以下のようになります。

"=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)"

この関数は、2018年6月25日のデータをリストから探し、それからインデックスの列から一致する値を返すという意味です。

この場合、インデックスの列は｢2｣ですが、見てわかるように、実際の表の列は｢1｣です。

確かにそうなのですが、CHOOSE関数ではその2つのフィールドを処理します。

データの範囲に参照するインデックスの番号を割り当てます。日付を割り当てるインデックスの番号は｢1｣で、タスクを割り当てるインデックスの番号は｢2｣です。

それで、VLOOKUP関数で｢2｣と入力すると、実際にはCHOOSE関数でインデックスの番号｢2｣を参照するのです。

これで、VLOOKUP関数が日付の列を使って、タスクの列が左にあってもタスクの列からデータを返します。

これはほんの触り程度なので、他にもどんなことができるか想像してみてください！

もっと高度なVLOOKUP関数を使いたい場合は、Dannの｢ExcelでVLOOKUP関数を使ってデータを探す｣という記事をチェックしてみてください。

2. 文字列を解析するネスト（入れ子）した関数

もうひとつExcelのすごい数式をご紹介しましょう。

外部ソースの区切られた文字列で構成されているデータから、Microsoft Excelにデータをインポートする場合があります。

データを取り込んだら、そのデータを各コンポーネントに入れて解析します。以下は、｢;（セミコロン）｣で区切った、名前、住所、電話番号の情報の例です。

Excelの数式を使って、この情報を解析するやり方は以下の通りです。（この頭がおかしくなりそうなやり方を続けられるか、精神状態を確認してから試してください）

最初のフィールドでは、一番左の項目（名前）を抽出するために、簡単なLEFT関数を使います。

"=LEFT(A2,FIND(";",A2,1)-1)"

このロジックの仕組みは以下の通りです。

A2から文字列を検索する。
区切っている記号｢;｣を探す。
その文字列のセクションの終わりを適切な位置にするために１を引く。
一番左の文字列をその位置まで持ってくる。

この場合は、一番左の文字列｢Ryan｣です。これで任務完了です。

3. Excelのネストした関数

しかし、他のセクションについてはどうでしょう？

これを実行するもっと簡単な方法があるかもしれませんが、頭がおかしくなりそうなネスト関数（実際に機能するもの）をできるだけ使おうとしているので、今回は独自のやり方でいきます。

右側の部分を抽出するには、最初の｢;｣記号までの文字のセクションを取得するために、複数のRIGHT関数をネストし、もう一度LEFT関数を実行しなければなりません。

住所の番地部分を抽出するための関数は、以下のようになります。

"=LEFT((RIGHT(A2,LEN(A2)-FIND(";",A2))),FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))),1)-1)"

頭がおかしくなりそうですが、つなぎ合わせるのは大変ではありません。実行したのはこの関数だけです。

RIGHT(A2,LEN(A2)-FIND(";",A2))

それから、これを｢A2｣がある上記のLEFT関数の各所に挿入しました。これで、文字列の2番目のセクションが正しく抽出できます。

次の文字列のセクション毎に、別のネストを作成しなければなりません。

ですから、前のセクションで作成した、頭がおかしくなりそうなRIGHTの方程式を使って、｢A2｣があるところに貼り付けた前のRIGHTの数式と一緒に、新しいRIGHTの数式を渡します。

(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2))))))

それから、その数式を使って、｢A2｣のあるところに元のLEFT関数を入れます。

最終的な関数は、以下のように目がクラクラしそうなものになります。

"=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),FIND(";",(RIGHT((RIGHT(A2,LEN(A2)-FIND(";",A2))),LEN((RIGHT(A2,LEN(A2)-FIND(";",A2))))-FIND(";",(RIGHT(A2,LEN(A2)-FIND(";",A2)))))),1)-1)"

この数式を用いれば、元の文字列から｢Portland, ME 04076｣という住所が正しく抽出できます。