表計算ソフトの常識を覆す 「スピル」とは何か
ファイルをダウンロード
▢ スピルの機能
① 図1の様にＢ1に数式を入れただけで、図2の様にＢ2以降のセルに
計算結果を入力する機能
XLOOKUP関数
▢ XLOOKUP関数の特徴
① 検索範囲と参照範囲を別々にできる。
② VLOOKUP関数とHLOOKUP関数の両方を兼ねている
③ もう列位置を変更しなくていい
④ 列全体の参照が簡単にできる
⑤ 値では無くセルを返す
［XLOOKUP関数］検索範囲と参照範囲を別々にできる。
ファイルのダウンロード
▢ VLOOKUPでは出来なかった、B列（名前）で検索をしA列（数値）を参照ができる。
「設定方法」
１． テーブルの設定
① A3:B9を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２． D4に名前のリストを設定
① D4セルを選択し「データ」－「データの入力規則」－「設定」タブー「リスト」
② 「元の値」欄で、B4:B9を選択
３． E4に計算式を入力
① E4セルを選択
② =XLOOKUP(D4,記号,数値)を入力
［XLOOKUP関数］VLOOKUP関数とHLOOKUP関数の両方を兼ねる
ファイルのダウンロード
▢ XLOOKUPはスピる。
A4に名前を入れ、B4に式を入れるとC4:E4に該当のデータが表示される。
※ B4に式を入れるとC4:E4は式がスピルする。
［設定方法］
１． テーブルの設定
① A6:E10を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
これで、全ての列にテーブルがセットされる。
２．A4に名前のリスト設定
① A4セルを選択し「データ」－「データの入力規則」－「設定」タブー「リスト」
② 「元の値」欄で、A7:A1０を選択
③ A4セルで”広瀬”を選択
３．E4に計算式を入力
① B4セルを選択
② =XLOOKUP(A4,名前,日付:数値) を入力
すると、C4:E4の計算式が自動でスピル
［XLOOKUP関数］エラー時の引数が追加された。
ファイルのダウンロード
▢ XLOOKUPは、VLOOKUPではできなかったエラー処理ができる。
VLOOKUPでは、名前が無い時に、＃N/Aエラーとなる為、
従来は、IFERROR関数でエラー時の対応をする必要があったが、
XLOOKUP関数では、エラー処理の引数が追加された。
［設定方法］
１． テーブルの設定
① A3:C7を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
これで、全ての列にテーブルがセットされる。
２．検索用の名前の入力
① E4：E6にそれぞれ佐々木を入力
３． 計算式の入力
① VLOOKUPのみ ・・・=VLOOKUP(E4,名前:数値,3,FALSE)
② エラー処理にIFERRORを追加
・・・=IFERROR(VLOOKUP(E5,名前:数値,3,FALSE),”誤”)
③ XLOOKUPを使用 ・・・=XLOOKUP(E6,名前,数値,”誤”)
［XLOOKUP関数］検索値を下から探せる。
ファイルのダウンロード
⃞ XLOOKUPは、VLOOKUPで、できなかった下から検索ができる。
VLOOKUPでは、名前が2件以上ある場合でも上からのみの検索であったが、
XLOOKUP関数では一致モード、検索モードが追加され下から検索が可能となった。
［設定方法］
１． テーブルの設定
① A3:B9を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．D4に”田中”を入力（任意）
３．計算式を入力
① E4セルを選択
② 上から検索 ’=XLOOKUP(D4,名前,数値,””,0,1)
③ 下から検索 ’=XLOOKUP(D4,名前,数値,””,0,-1)
［XLOOKUP関数］スピルので列全体から参照できる。
ファイルのダウンロード
⃞ XLOOKUPは、スピるので列全体が簡単に参照できる。
XLOOKUPは、HLOOKUPと同じことができる＋スピるため簡単に列単位で参照できる。
［設定方法］
１． テーブルの設定
① A3:D7を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．F3に”商品B”を入力（任意）
３．計算式を入力
① E4セルを選択
② =XLOOKUP(F3,A3:D3,商品A:商品D)
③ 自動で”F5:F17”の計算式はスピル
［XLOOKUP関数］引数に数式が使える
ファイルのダウンロード
⃞ XLOOKUP（検索値,検索範囲,戻り値）の引数にそれぞれ数式が使える
［設定方法］
１． テーブルの設定
① A3:C9を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．E4に”田中”を入力（任意）
３．計算式を入力
① G4セルを選択
② =XLOOKUP(E4&F4,名前&記号,数値)
［XLOOKUP関数］簡単に大文字、小文字を区別できる
ファイルのダウンロード
⃞ XLOOKUPは、検索セルのセル参照ができ、かつ数式が使えるため、
CODE関数を使い大文字、小文字の判別できる。
［設定方法］
１． テーブルの設定
① A3:B9を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．D4に”B”を入力（任意）
D5に”b”を入力（任意）
３．計算式を入力
① E4セルを選択
② =XLOOKUP(CODE(D4),CODE(記号),数値)
［XLOOKUP関数］値ではなく、セル情報を返す
ファイルのダウンロード
⃞ VLOOKUP関数はセルの値のみであったが、XLOOKUP関数は、
セル参照ができる為、様々な検索が可能となった。
⃞ 例題 フリガナで検索する。
［設定方法］
１． テーブルの設定
① A3:B8を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．D4に”E”を入力（任意）
３．計算式を入力
① E4セルを選択
② =PHONETIC(XLOOKUP(D4,記号,名前))
［XLOOKUP関数］問題 考えてみよう！
ファイルのダウンロード
⃞ 問題 E4の名前をB列から探し、その名前に挟まれた範囲の
C列の数値の合計をF4に入れる。また、G4にその間の日数を入れる。
［回答］
１． テーブルの設定
① A3:C17を選択し「数式」－「選択範囲から作成」－「上端行」にチェック
２．E4に”井出”を入力（任意）
３．計算式を入力
① F4セルを選択
② =SUM(XLOOKUP(Extract,名前,数値):XLOOKUP(Extract,名前,数値,””,0,-1))
③ G4セルを選択
④ =XLOOKUP(E4,名前,日付,””,0,-1)-XLOOKUP(E4,名前,日付)
▢ 追加問題
B列の名前は重複している為、普通にリストをしても使えない。
そのため、新関数の「UNIQUE」関数を使い解決する。
① D4セルを選択
② =UNIQUE(名前)
※ D4:D9に名前リストができる。
③ データの入力規則を活用しE4に名前リストを作成
コメント