Excelの「XLOOKUP」って何が便利？ 「LOOKUP関数」とOffice スクリプトの基礎

VLOOKUPやXLOOKUP、SUMIF関数など、Excelには数値の算出やデータ分析を容易にする関数やマクロが多数あります。今回は「VLOOKUPとXLOOKUPはどう違うの？」など関数、マクロの疑問に対して、利用例を基に解説します。

[三沢友治，富士ソフト]

　Excelで作業をしている時に、「同じ作業を何度も繰り返すのは面倒」「計算式が長すぎる」と感じた経験はありませんか。そんな時に皆さんは、関数やマクロで効率化しようと考えるでしょう。しかし、中には「関数はよく分からないからいつもの計算式で十分」「マクロは使い方が分からない」「セキュリティ面で危険なのでマクロは使用しない」など、関数やマクロの利用を敬遠する人もいるようです。

　一時期、世界的に「マクロやVBA（Visual Basic for Applications）は危険」と思われた時代がありました。当時の状況を知っている人は、マクロを忌避していることでしょう。今回は、Excelの関数とマクロ、そして2021年5月にリリースされた「Microsoft 365」のOffice スクリプト（Excel on the webのOffice スクリプト）の活用法を解説します。

著者紹介：三沢友治

2007年頃に「Microsoft SharePoint Server」の導入に携わり、それ以後数多くのMicrosoft製品の導入案件を担当。最近は「Microsoft 365」の導入や活用支援、さらにはMicrosoft 365の機能を活用したカスタマイズ開発の指揮も担う。ミドルウェアのバージョンアップにおけるカスタム機能の互換性維持に苦慮した経験から、継続開発が続けられるシステム環境としてMicrosoft製品群に期待を寄せる。2017年には「Microsoft MVP Award」を受賞。

SUMIF関数、SUMIFS関数で条件を指定して算出する方法

　Excelの関数は、数値や文字変換といった簡単なものから、統計やデータ分析に使われる複雑な計算式をまとめたものまでさまざまです。Excelには便利な関数が随時追加されています。

　まず、簡単な関数から見ていきましょう。合計を求める「SUM関数」は皆さんもよくご存じでしょう。その他、指定した条件に合うデータの合計を求める「SUMIF関数」や、指定した複数の条件に合うデータの合計を求める「SUMIFS関数」などがあります。

　図1の表（B2:D8）の中で、販売された全ての製品の合計（個数）を求める場合は「=SUM(D3:D8)」と書けばSUM関数で算出できます。指定した販売場所（"東京-1"）で販売された製品の合計（個数）を求める場合は、「=SUMIF(D3:D8, B3:B8, "=東京-1")」とSUMIF関数によって算出できます。

　販売場所と製品を指定して個数の合計を求める場合は、「=SUMIFS(D3:D8,B3:B8,"="&G2,C3:C8,"="&G3)」と書いてSUMIFS関数で算出できます（以下参照）。

「SUMIFS関数」の基本構文

=SUMIFS(合計範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ……）

販売場所と製品を指定して個数の合計を求める場合（図1参照）

=SUMIFS(D3:D8,B3:B8,"="&G2,C3:C8,"="&G3)

合計対象範囲：　D3:D8（個数）

条件範囲1　 ：　B3:B8（販売場所）

条件1　　　 ：　"="& G2（集計する販売場所の指定）

条件範囲2　 ：　C3:C8（製品）

条件2　　　 ：　"="& G3（集計する製品の指定）

図1　Excelの関数（SUMIFS：複数条件の合計）（出典：筆者によるキャプチャー画像）

　東京で販売された製品を集計する場合、販売場所にワイルドカードを使用して「"東京*"」と指定することで、「"東京-1"」と「"東京-2"」の2カ所の販売個数を集計できます。また、指定する条件を計算式にすると、販売場所や製品のデータの内容が変わっても数値が崩れることはありません。指定する条件を計算式にすると、スピーディーな集計が可能です。最適な関数を選び、関数を使用する際にちょっとした工夫をするだけで作業の大幅な効率化につながります。

指定のデータを検索したい　VLOOKUPとXLOOKUPは何が違う？

　関数は計算だけではなくデータ検索も得意です。「VLOOKUP関数」はデータ検索でよく利用される関数の一つで、「Excel 2021」以降のバージョンでは「XLOOKUP関数」が追加されました。

　まずは、以下の図を基にVLOOKUP関数を使ってテーブルの中から特定のデータの検索方法を見ていきましょう。

図2　VLOOKUPでExcelのリストからデータを探す（出典：筆者によるキャプチャー画像）

　以下は、VLOOKUP関数を使って図3の表（D4:E26）のキー項目となる1列目の国名（D列）から2列目の首都名（E列）を取り出して、首都名（B2）に表示する関数の書き方です。キー項目と取り出す情報は1つの表になっている必要があります。

「VLOOKUP関数」の基本構文

=VLOOKUP(検索値, 検索範囲, 列番号, 検索方法)

※「検索方法」は省略可能だが、その場合「TRUE」と判断される。

VLOOKUPで「マダガスカル」の首都名を取り出して表示する方法（図3参照）

= VLOOKUP (A2, D4:E26, 2, FALSE)

検索値　：　A2

検索範囲：　$D$4:$E$26（国名と首都名の表）

列番号　：　2（検索範囲の左から2番目の列のデータ）

検索方法：　FALSE（FALSE（完全一致）／TRUE (近似一致)）

図 3 XLOOKUPでExcel内のリストからデータを探す（出典：筆者によるキャプチャー画像）

　XLOOKUP関数は、キー項目と取り出したい情報が1つの表になっている必要はありません。例ではVLOOKUPの例と比較して国名と首都名の列を入れ替えています。Excelの国名の表（E4:E26）から検索したい国名（A2）を探し、首都名の表（D4:D26）の同行にある値を取り出して首都名（B2）に表示します。「国名表の13個目が検索値と同等だったため、首都名表の13個目の値が戻ってきた」と考えるといいでしょう。そのため、検索は行方向に並んだ表である必要がなく、列方向に並んだ表でもよく、数値さえ間違っていないければ縦横が混ざっていても検索できます。

「XLOOKUP関数」の基本構文

=XLOOKUP(検索値, 検索範囲, 戻り範囲)

=XLOOKUP(A2, $E4:$E26, $D$4:$D$26)

検索値　：　A2

検索範囲：　$E$4:$E$26（国名の表）

戻り範囲：　$D$4:$D$26（首都名の表）

　Webアプリでもこの仕組みは可能です。このように関数によって簡単かつスピーディーにデータを探すことができます。

マクロとVBAは便利だが、セキュリティリスクに要注意

　時には、関数では対応できないケースもあります。そんな時に使えるのが、マクロやVBAです。

　マクロはExcelの操作を記録して再現する機能です。作業の簡略化や操作ミスの削減に役立ちます。「マクロの記録」を使うことで高度なプログラミング知識がなくてもマクロを簡単に作成でき、特に操作の繰り返し作業には使える機能です。しかし、セルの状態によって動作を変化させるような操作には向いていません。複雑な操作を自動化するにはVBAでプログラムを書いてマクロを作成する必要があります。

　VBAが生まれてから30年近くが経過し、その間にさまざまな機能が追加されました。ExcelデータだけでなくPCのファイルも操作でき、新しいファイルを作成したり、複数のファイルからデータを読み取ってExcelや別の仕組みに引き渡したりといった高度な操作にも対応しています。

　高度な操作が可能になる一方で、使い方によっては悪用もできるため要注意です。例えばPCに保存されたファイルを勝手に別のユーザーにメールで送るといったことも可能で、ウイルスの配布方法の一つとして悪用されたケースもあります。マクロにはセキュリティ面でリスクがあるとして、利用を制限する組織もあります。

　最新のExcelではマクロを簡単に実行できないように制限することもでき、解除するためには複数の操作が必要になります。マクロにはセキュリティリスクがあることを理解して、利用に当たっては十分な注意が必要です。

図4　マクロやVBAを使うにはファイルごとに許可が必要（出典：筆者によるキャプチャー画像）

図5　情報メニューにもセキュリティの警告表示（出典：筆者によるキャプチャー画像）

マクロ初心者でも安全に使える「Office スクリプト」

　ExcelのマクロやVBAはこうしたセキュリティリスクを伴うため、プログラミングに慣れていないユーザーが利用する場合は注意が必要です。そこでMicrosoft 365版のOfficeでは、Excelファイル外へのアクセスを制限しながら、自動化部分はそのまま利用できるOffice スクリプトという機能が用意されています。

図6　自動化タブはMicrosoft 365 Apps特有の機能（出典：筆者によるキャプチャー画像）

　Office スクリプトはTypeScriptというプログラム言語で記述できます。JavaScriptと互換性が高いため、JavaScriptを利用したことがあれば理解しやすく、すぐに利用できるでしょう。

Office スクリプトの使い方

　Office スクリプトを利用するとどのようなことができるのでしょうか。サンプルスクリプトで動作を見てみましょう。

　まず「自動化」タブから「すべてのスクリプト」ボタンをクリックします。

図7　自動化タブ内には作成したスクリプトも表示（出典：筆者によるキャプチャー画像）

　右側の作業ウィンドウに「コード エディター」が表示されるので、上部にある「サンプル」をクリックします。今回は「ピボットテーブルを使って売り上げ概要を生成する」を選択します。

図8　スクリプトのサンプル表示（出典：筆者によるキャプチャー画像）

　スクリプトの概要が表示されるので、「実行」を押してサンプルスクリプトを動かします。

図9　Office スクリプトの概要。実行ボタンでスクリプトを実行（出典：筆者によるキャプチャー画像）

　サンプルスクリプトを実行すると、新しいシートに表として用意されたデータとそのデータを使ったピボットテーブルが作成されます。

図10　サンプルスクリプトで表として用意されたデータ（出典：筆者によるキャプチャー画像）

図11　サンプルスクリプトで作成されたピボットテーブル（出典：筆者によるキャプチャー画像）

　あっという間にデータが作成できました。サンプルスクリプトは、図9の「実行」ボタンの右にある「編集」ボタンを押すことで確認できます。

　下図のサンプルスクリプトの内容を簡単に解説すると、「"function main"」と書かれた箇所（6行目）からOffice スクリプトが動き始めます。8行目の「"SetUpSample(workbook);」 "」で、サンプルデータを新しいシート（Sheet4）に作成します（図10参照）。15行目から、Sheet4の「"sampleSheet.getRange("A1:E21");"」を基に新しいシート（Sheet5）を作成してピボットテーブルを作成します（図11参照）。

図12　TypeScriptで書かれるOffice スクリプトは可読性が高い（出典：筆者によるキャプチャー画像）

図13　サンプルスクリプトを理解することは早期把握の秘訣（ひけつ）（出典：筆者によるキャプチャー画像）

　この他にもさまざまなサンプルスクリプトが用意されています。動作を見ながらスクリプトを確認することで、Office スクリプトを書く時の参考になります。必要に応じてチェックしてみるといいでしょう。Microsoftのトレーニングサイトには基本スクリプトとして幾つかのパターンが掲示されているので、こちらも確認しておくとよいいいでしょう。

関連リンク

ExcelでのOffice スクリプトの基本的なスクリプト-Office Scripts（Microsoft Learn）

Excel for the Web でスクリプト作成を自動化

　「プログラミング経験がないのでTypeScriptはよく分からない」と思った方は、ExcelをWebブラウザで利用できる「Excel for the Web」を利用してみてください。Excel for the Webには、Office スクリプトを自動作成する機能「操作を記録」が用意されています。Excel for the Webに移動するにはExcelファイルを「Microsoft OneDrive」や「Microsoft SharePoint」に保存した上で、情報メニューから「パスのコピー」ボタンを押し、コピーされたURLをWebブラウザに貼り付けます。

図14　パスのコピーを利用してファイルのURLをコピー（出典：筆者によるキャプチャー画像）

　Excel for the Webが起動したら「自動化」タブの「操作を記録」ボタンをクリックします。

図15　「操作を記録」機能はExcel for the Web特有の機能（出典：筆者によるキャプチャー画像）

　すぐに操作を記録できる状態になるので、自動化したい操作をExcel for the WebのUIから動かします。操作完了後に「停止」ボタンを押すとOffice スクリプトが自動的に生成されます。

図16　「操作を記録」が表示されたら記録開始（出典：筆者によるキャプチャー画像）

　Excel for the WebのOffice スクリプトはExcelと同じようにスクリプトを見ることができます。

　「編集」ボタンをクリックして記録されたOffice スクリプトを確認します。目指す動作はどのようにスクリプトを記述すべきか、参考になるでしょう。

図17　記録したOffice スクリプトはすぐに確認可能。アクティブシートのセルG5:H8に"あ”と入力するスクリプト（出典：筆者によるキャプチャー画像）

Office スクリプトを使ってタスクを自動化するには？

　Office スクリプトによってExcel内部の操作を簡単に自動化できます。しかし、それだけでは十分に動作しない（自動化しない）こともあります。その場合には「タスクの自動化」を利用します。

図18　タスクの自動化の実体は Power Automate（出典：筆者によるキャプチャー画像）

　まず「自動化」タブにある「タスクの自動化」をクリックします。

図19　Power Automateフローのサンプル（出典：筆者によるキャプチャー画像）

　「タスクの自動化」に、「Microsoft Power Automate」を利用してOffice スクリプトを動作させるテンプレートが表示されます。サンプルのように「Microsoft Forms」に何か投稿された時に、Office スクリプトを起動して入力された情報を加工するなどの外部連携も可能です。VBAと異なり、Power AutomateはPCに保存されたファイルを直接操作できないため、安全性の高い自動化が可能になります。

　今回は、Excelの関数やマクロ、Office スクリプトを使った自動化について活用方法を紹介しました。特に、セキュリティリスクがあると言われるマクロについては、安全性に考慮したOffice スクリプトが新たに用意されているので、機能の置き換えも可能です。

　Excelには操作の自動化にも活用できる新しい関数や機能が随時追加されています。もちろん、レガシーと言われる既存の仕組みやシステムを利用した方が効率化できるケースもあります。既存の機能やツールと新しい機能や仕組みを適材適所で使い分けて、作業の自動化、業務の効率化を検討してみてはいかがでしょうか。