ProPlus機能編

表計算ソフトの常識を覆す 「スピル」とは何か

ファイルをダウンロード

▢ スピルの機能

  ① 図1の様にB1に数式を入れただけで、図2の様にB2以降のセルに
    計算結果を入力する機能

XLOOKUP関数

▢ XLOOKUP関数の特徴

  ① 検索範囲と参照範囲を別々にできる。
  ② VLOOKUP関数とHLOOKUP関数の両方を兼ねている
  ③ もう列位置を変更しなくていい
  ④ 列全体の参照が簡単にできる
  ⑤ 値では無くセルを返す

[XLOOKUP関数]検索範囲と参照範囲を別々にできる。

ファイルのダウンロード

▢ VLOOKUPでは出来なかった、B列(名前)で検索をしA列(数値)を参照ができる。   

「設定方法」

  1. テーブルの設定
    ① A3:B9を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
  2. D4に名前のリストを設定
    ①  D4セルを選択し「データ」-「データの入力規則」-「設定」タブー「リスト」
    ② 「元の値」欄で、B4:B9を選択
  3. E4に計算式を入力
    ① E4セルを選択
    ② =XLOOKUP(D4,記号,数値)を入力

[XLOOKUP関数]VLOOKUP関数とHLOOKUP関数の両方を兼ねる

ファイルのダウンロード

▢ XLOOKUPはスピる。
  A4に名前を入れ、B4に式を入れるとC4:E4に該当のデータが表示される。
  ※ B4に式を入れるとC4:E4は式がスピルする。

[設定方法]

 1. テーブルの設定
   ①  A6:E10を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
     これで、全ての列にテーブルがセットされる。
 2.A4に名前のリスト設定
   ① A4セルを選択し「データ」-「データの入力規則」-「設定」タブー「リスト」
   ② 「元の値」欄で、A7:A10を選択
   ③ A4セルで”広瀬”を選択
 3.E4に計算式を入力
   ① B4セルを選択
   ② =XLOOKUP(A4,名前,日付:数値) を入力
     すると、C4:E4の計算式が自動でスピル

[XLOOKUP関数]エラー時の引数が追加された。

ファイルのダウンロード

▢ XLOOKUPは、VLOOKUPではできなかったエラー処理ができる。
  VLOOKUPでは、名前が無い時に、#N/Aエラーとなる為、
  従来は、IFERROR関数でエラー時の対応をする必要があったが、
  XLOOKUP関数では、エラー処理の引数が追加された。

[設定方法]

 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関数では一致モード、検索モードが追加され下から検索が可能となった。

[設定方法]

 1. テーブルの設定
   ① A3:B9を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
 2.D4に”田中”を入力(任意)
 3.計算式を入力
   ① E4セルを選択
   ② 上から検索    ’=XLOOKUP(D4,名前,数値,””,0,1)
   ③ 下から検索    ’=XLOOKUP(D4,名前,数値,””,0,-1)

[XLOOKUP関数]スピルので列全体から参照できる。

ファイルのダウンロード

⃞ XLOOKUPは、スピるので列全体が簡単に参照できる。
  XLOOKUPは、HLOOKUPと同じことができる+スピるため簡単に列単位で参照できる。

[設定方法]

 1. テーブルの設定
   ① A3:D7を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
 2.F3に”商品B”を入力(任意)
 3.計算式を入力
   ① E4セルを選択
   ② =XLOOKUP(F3,A3:D3,商品A:商品D)
   ③ 自動で”F5:F17”の計算式はスピル

[XLOOKUP関数]引数に数式が使える

ファイルのダウンロード

⃞ XLOOKUP(検索値,検索範囲,戻り値)の引数にそれぞれ数式が使える       

[設定方法]

 1. テーブルの設定
   ① A3:C9を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
 2.E4に”田中”を入力(任意)
 3.計算式を入力
   ① G4セルを選択
   ② =XLOOKUP(E4&F4,名前&記号,数値)

[XLOOKUP関数]簡単に大文字、小文字を区別できる

ファイルのダウンロード

⃞ XLOOKUPは、検索セルのセル参照ができ、かつ数式が使えるため、
  CODE関数を使い大文字、小文字の判別できる。
       

[設定方法]

 1. テーブルの設定
   ① A3:B9を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
 2.D4に”B”を入力(任意)
   D5に”b”を入力(任意)
 3.計算式を入力
   ① E4セルを選択
   ② =XLOOKUP(CODE(D4),CODE(記号),数値)

[XLOOKUP関数]値ではなく、セル情報を返す

ファイルのダウンロード

⃞ VLOOKUP関数はセルの値のみであったが、XLOOKUP関数は、
  セル参照ができる為、様々な検索が可能となった。
   

⃞ 例題 フリガナで検索する。

[設定方法]

 1. テーブルの設定
   ① A3:B8を選択し「数式」-「選択範囲から作成」-「上端行」にチェック
 2.D4に”E”を入力(任意)
 3.計算式を入力
   ① E4セルを選択
   ② =PHONETIC(XLOOKUP(D4,記号,名前))

[XLOOKUP関数]問題 考えてみよう!

ファイルのダウンロード

⃞ 問題 E4の名前をB列から探し、その名前に挟まれた範囲の
  C列の数値の合計をF4に入れる。また、G4にその間の日数を入れる。

[回答]

 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に名前リストを作成


  

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です