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