忘れないように記録しとこ

カバの樹

PhpSpreadsheetで隣のセルの入力値でドロップダウンリスト内容を変更する方法

完全版を表示する

はじめに

以前PhpSpreadsheetでドロップダウンリストを実装する方法についての記事を書きました。

https://www.kabanoki.net/3029

 

今回は前の入力値でドロップダウンリストの内容が変わる方法を記事にしたいと思います。

 

環境

PhpSpreadsheet: 1.6

 

隣のセルの値でドロップダウンリストの内容が変わるとは？

連続して紐付いたデータというものがあります。

例えば、住所を入力するとして、

都道府県
↓
市区町村

のようなデータです。

 

使い方

エクセルの準備

まず以下のような3つのシートがあるエクセルを準備します。

 

Sheet1

入力用のシートです。

 

Sheet2

企業のマスターデータです。

 

Sheet３

施設のマスターデータです。

 

ソースコード

プログラムソースは以下になります。

Sheet1（入力用）

use PhpOffice\PhpSpreadsheet\NamedRange;

// 「Sheet1」シートを取得
$sheet = $spreadsheet->getSheet(0);
// セルB3を取得
$CellB = $sheet->getCell('B3')->getDataValidation();
$CellB->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
$CellB->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
$CellB->setAllowBlank(false);
$CellB->setShowInputMessage(true);
$CellB->setShowErrorMessage(true);
$CellB->setShowDropDown(true);
$CellB->setErrorTitle('入力エラー');
$CellB->setError('値がリストの中にありません');
$CellB->setPromptTitle('選択してください。');
// 「Sheet2」シートのC列のC2以降の値を持つセルの一覧を取得する
$CellB->setFormula1("=OFFSET(Sheet2!\$C\$2,0,0,COUNTA(Sheet2!\$C:\$C)-1,1)");

// セルC3を取得
$CellC = $sheet->getCell('C3')->getDataValidation();
$CellC->setType( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST );
$CellC->setErrorStyle( \PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_INFORMATION );
$CellC->setAllowBlank(false);
$CellC->setShowInputMessage(true);
$CellC->setShowErrorMessage(true);
$CellC->setShowDropDown(true);
$CellC->setErrorTitle('入力エラー');
$CellC->setError('値がリストの中にありません');
$CellC->setPromptTitle('選択してください。');
// 「Sheet3」シートの範囲の名前に従ってリストを取得する
$CellC->setFormula1("=INDIRECT(INDEX(Sheet2!\$D:\$D,MATCH(\$B3,Sheet2!\$C:\$C,0),1))");

 

範囲の名前定義

企業に紐付いた施設を取得するのに範囲の名前定義を利用します。

$spreadsheet->addNamedRange( new NamedRange("Facility_1", $sheet, "C2:C4"));
$spreadsheet->addNamedRange( new NamedRange("Facility_2", $sheet, "C3:C3"));
$spreadsheet->addNamedRange( new NamedRange("Facility_10", $sheet, "C4:C4"));
$spreadsheet->addNamedRange( new NamedRange("Facility_11", $sheet, "C5:C5"));

 

解説

今回重要なのは、以下２つです。

  1. C列のsetFormula1の値
  2. 範囲の名前定義

 

C列のsetFormula1の値

B列のsetFormula1の値については以下の記事で解説しています。

https://www.kabanoki.net/3029

 

C列のsetFormula1の値は以下になります。

=INDIRECT(INDEX(Sheet2!\$D:\$D,MATCH(\$B3,Sheet2!\$C:\$C,0),1))

これはPHP用にエスケープしてるので、実際には以下になります。

=INDIRECT(INDEX(Sheet2!$D:$D,MATCH($B3,Sheet2!$C:$C,0),1))

 

まずMATCH関数が処理されます。

今回はB3セルで「株式会社テスト法人1（ID:1）」を選択したことを想定します。

MATCH($B3,Sheet2!$C:$C,0)

値は「２」になります。

 

つまりプログラム上では以下になります。

=INDIRECT(INDEX(Sheet2!$D:$D,2,1))

 

次にINDEX関数が処理されます。

INDEX関数は指定のセルを返す関数です。

値は「Facility_1」になります。

 

なのでプログラム上は以下になります。

=INDIRECT(Facility_1)

 

最後にINDIRECT関数が処理されます。

INDIRECT関数は対象を取得できる関数です。

セルだろうが範囲だろうが取得してくれる凄いヤツです。

今回は範囲の名前で取得させます。

 

「Facility_1」の範囲は以下になります。

 

なのでプログラム上は以下になります。

C2:C4

 

よってドロップダウンリストには「C2:C4」が表示されます。

 

 

範囲の名前定義

範囲の名前定義については以下を参考にしてください。

https://www.kabanoki.net/2984

 

まとめ

ドロップダウンリストや範囲の名前定義など過去の記事を総動員して実装できました。

ぜひ試してみてください。

 

 

  • B!