エクセルの数式は、ほとんど何でもできます。今回は、microsoftexcelの数式と条件付き書式の威力について、3つの便利な例を使って学びます。

マイクロソフトエクセルの徹底研究

これまでにも、Excelを使って独自のカレンダーテンプレートを作成したり、プロジェクト管理ツールとして使用するなど、Excelをより有効に活用するためのさまざまな方法をご紹介してきました。

表計算ソフト**にどんなデータがあっても、Excelの数式やルールを使えば、データや情報を自動的に操作することができます。

ここでは、数式などを使ってMicrosoft Excelをよりよく使う方法を詳しく見ていきましょう。

エクセル数式の条件付き書式設定

あまり使われていないツールのひとつに、条件付き書式があります。もし、あなたがMicrosoft Excelの条件付き書式についてより高度な情報をお探しなら、Sandyの記事「Microsoft Excelで条件付き書式を使ってデータをフォーマットする」を必ずご覧ください。

Excelの数式やルール、あるいはごく簡単な設定により、スプレッドシートを自動ダッシュボードに変換することができます。

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

条件付き書式の下には多くのオプションがあります。この記事の範囲外ですが、ほとんどは、セル内のデータに基づいてセルを強調したり、色をつけたり、色合いを変えたりするものです。

より小さい、より大きいという数式を使ってセルを赤くするなど、条件付き書式の最も一般的な使い方だろう。ExcelでのIF文の使い方を詳しくご紹介します。

あまり使われていない条件付き書式設定ツールの一つに、アイコンセットのオプションがあります。このオプションは、Excelのデータセルをダッシュボード表示のアイコンに変換するために使える、素敵なアイコンセットを提供します。

ルールの管理」をクリックすると、「条件付きフォーマットのルール管理」が表示されます。

アイコンセットを選択する前に選択したデータに応じて、マネージャーウィンドウに表示されているセルと、今選択したアイコンセットが表示されます。

ルールの編集」をクリックすると、ダイアログボックスが表示され、そこでマジックが行われます。

ここでは、目的のダッシュボードアイコンを表示するための論理式や方程式を作成することができます。

このダッシュボードの例では、異なるタスクに費やされた時間と、予算で決められた時間とが表示されます。予算の半分を超えると、黄色信号が表示されます。完全に予算オーバーになると、赤色になります。

ご覧のように、このダッシュボードではタイムバジェットを表示することに成功していません。

ほぼ半分の時間が予算を超える金額で費やされた。

今こそ、集中力を高め、時間を上手に管理する時なのです!

1 vlookup関数を使用する

より高度なmicrosoftexcelの機能を使いたい方は、こちらもどうぞ。

VLookup関数は、リスト中のある列から特定の項目を検索し、その項目と同じ行の別の列のデータを返すことができる関数で、皆さんもよくご存じでしょう。

残念ながら、この機能では、リストで検索する項目が左の列に、検索するデータが右の列にあることが必要ですが、それらが入れ替わっていたらどうでしょう？

以下の例で、2018年6月25日に行ったタスクを以下のデータから探したい場合はどうでしょうか。

この例では、右側の値を検索し、左側の対応する値を返したい。これは、VLookupの通常の動作とは逆である。

microsoftexcelproのユーザーフォーラムを読むと、VLookupでは不可能で、インデックスとマッチ関数を組み合わせて使用しなければならないという意見が多く見受けられます。これは一概にそうとは言えません。

VLookupの中にCHOOSE関数を入れ子にすることで、このように動作させることができます。この例では、Excelの計算式は以下のようになります。

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

この関数は、ルックアップリストの中から日付6/25/2013を探し出し、それに対応する値を列インデックスから返したいという意味である。

この例では、列のインデックスが「2」になっていますが、ご覧の通り、上の表の列は実際には「1」ですよね？

はい、でも「CHOOSE」機能でやることは、この2つのフィールドを操作することだけです。

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

つまり、VLookup関数に「2」と入力すると、実際にはCHOOSE関数のインデックス番号2が参照されるわけです。

そこで、VLookupはDate列を使用し、Task列のデータを返すようにしました。Taskは左側にあるにもかかわらずです。

この豆知識を知ったからには、他に何ができるか想像してみてください。

その他の高度なデータ検索を行う場合は、Excelでデータを検索するための検索関数の使い方に関するDannの全記事をぜひご覧ください。

2 文字列解析のための入れ子式

ここで、さらにクレイジーなExcelの計算式をご紹介しましょう。

区切り文字列で構成された外部ソースからMicrosoft Excelにデータを取り込むことができる場合があります。

データを導入したら、データを個々の構成要素にパースする必要があります。以下は、氏名、住所、電話番号の情報を「;」文字で区切った例です。

ここでは、Excelの数式を使ってこの情報を解析する方法を紹介します（この狂気を精神で追いかけることができるかどうか）。

最初のフィールドでは、一番左の項目（人名）を抽出するために、数式にLEFT関数を使用するだけです。

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

このロジックの仕組みはこうだ。

• A2からの文字列検索
• 区切り記号の「;」を探す
• 1を引くと、文字列セグメントの正しい終了位置が示される
• 左端のテキストをそのポイントにつかみます

この例では、左端のテキストが「Ryan」となっており、タスクは完了しています。

iii. エクセルでの入れ子式

しかし、それ以外の部分はどうでしょうか？

もっと簡単な方法もあるでしょうが、今回は（実際に機能する）最もクレイジーなネストしたExcel式を作成してみたいので、ユニークな方法を使用することにします。

右側の部分を取り出すには、複数の右関数を入れ子にして、最初の"; "記号までの部分を取り込み、それに対して再び左関数を実行する必要がある。以下は、住所の番地部分を抽出する方法です。

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

見た目はクレイジーですが、組み立てるのは難しくありません。私がしたのは、この機能を作ることだけです。

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

左の関数で「A2」があるところなら、**どこにでも置いてください。

これで、文字列の2番目の部分が正しく抽出されます。

文字列の後続部分はそれぞれ別のネストを作成する必要があります。つまり、あとは前節のために作ったおかしな「右」式を、新しい右式に渡して、「A2」と表示されているところに右式を貼り付けるだけです。このような感じです。

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

そして、この数式を取り出し、「A2」がある限り、元の左の数式に入れる。

最後に頭を悩ませるのは、次のような数式である。

"=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, I 04076 "を正しく抽出します。

次の部分を取り出すには、もう一度この作業を繰り返してください。

Excelの数式は円形になるかもしれませんが、やっていることは長い数式を切り貼りして、実際に機能する長いネストを生成しているに過ぎないのです。

しかし、正直なところ、同じことを関数で行うには、もっと簡単な方法があります。

分離されたデータを含む列を選択し、「データ」メニュー項目で「テキストを列に」を選択するだけです。

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

数回のクリックで、上記のクレイジーな数式と同じことができるのです。

クレイジー・アウト・ユー・マイクロソフト・エクセル・フォーミュラ

というわけで、これにて終了です。上の数式は、microsoftexcelで数式を作成する際に、いかに大げさな作業ができるかを証明しています。

Excelの計算式が、実は一番簡単（または一番良い）方法ではないことがあります。ほとんどのプログラマーは、Excelの数式は他の数式と同じように簡単であることを教えてくれます。Powerquery などの組み込み関数を使用することもできます。

Excelを本格的に使いたい方は、「Microsoft Excel入門」をお読みください。Excelで生産性を向上させるために必要なことがすべて書かれています。その後、「Excel基本機能メモシート」をご覧ください。