連載
特集
カテゴリー
タグ
メディア

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

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

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

MicrosoftのExcelを徹底研究

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

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

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

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

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

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

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

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

Excelの条件付き書式
Image: MakeUseOf

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

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

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

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

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

条件付き書式のアイコンセット
Image: MakeUseOf

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

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

条件付き書式のルール管理
Image: MakeUseOf

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

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

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

ルールの編集
Image: MakeUseOf

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

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

ダッシュボード風のExcel
Image: MakeUseOf

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

1. VLOOKUP関数を使う

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

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

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

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

VLOOKUP関数を使うExcelの表
Image: MakeUseOf

この場合は、右側で値を検索し、左側で一致する値を返したいので、通常の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」です。

VLOOKUP関数を使う
Image: MakeUseOf

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

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

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

VLOOKUP関数を使う表
Image: MakeUseOf

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

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

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

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

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

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

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

セミコロンで区切った文字列
Image: MakeUseOf

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

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

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

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

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

この場合は、一番左の文字列「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」という住所が正しく抽出できます。

項目ごとに抽出後の文字列
Image: MakeUseOf

次のセクションを抽出するには、先ほどのプロセスをもう一度繰り返します。

本当に頭がおかしくなりそうなExcelの関数になっていますが、長い数式を同じものの中にカット&ペーストして、長いネスト関数にしているだけです。

これが「クレイジー」と呼ばれるものの要素を満たしているのはわかります。正直に言うと、1つの関数で同じことを実行するはるかに簡単な方法があります。

区切られたデータのある列を選び、「データ」のメニューにある「文字を列に」を選びます。

これで、任意の区切り文字で文字列を分割できるウィンドウが表示されます。

文字列を分割できるウィンドウ
Image: MakeUseOf

数クリックで、上記のような頭のおかしい関数と同じことができますが…それでは面白みがないですよね?

Microsoft Excelの数式でクレイジーなことをする

以上です。上記の数式は、特定のタスクを実行するのにMicrosoft Excelで数式を作成すると、どれくらい人は異常になれるかということを証明しています

このようなExcelの数式は、実際に何かを実行するの一番簡単な(もしくは最善の)方法ではないこともあります。

ほとんどのプログラマーは、シンプルにやりなさいと教えると思いますし、Excelの数式に限らず何事においてもそれがいいと思います。

Power Queryのようにビルトインの機能を使うこともできます。はじめてのMicrosoft Power Queryのスクリプト生成ガイドの記事を読むことから始めてみてください。

本当に真面目にExcelを使いたい人は、Microsoft Excelの初心者向けガイドを読んでください。Excelで生産性を高めるのに必要なことがすべて書いてあります。

その後は、必須のExcel関数早見表も参考にしてください。

あわせて読みたい

知らないともったいない。GoogleやExcelのショートカットまとめ

PDFファイルをExcel形式やテキストに変換してくれるサービス【今日のライフハックツール】

金融口座との連携も。Excelで楽に家計管理できるテンプレ「Money in Excel」とは?

Image: Princess_Anmitsu / Shutterstock

Original Article: 3 Crazy Excel Formulas That Do Amazing Things by MakeUseOf

訳:的野裕子

swiper-button-prev
swiper-button-next