Excelで2次元検索して画像を表示する

今回のネタは、Excelで文字列を2次元検索（1つのシートの全範囲を検索）する方法と、検索して見つかった文字列の下に貼られた画像を表示する方法を組合わせて、簡易的な画像検索＆表示を行う実装例である。
私も実業務で使用しており、そこそこ便利なので今回紹介しようと思う。

マクロは使わず関数のみで実現している。ただし、makearrayやbycol, xmatchなど比較的最近導入された関数を使用しているので、本記事の内容をご自身で試す前に自分のExcelでそれらの関数が利用可能かを確認していただきたい。

なお、上述の通り、①文字列を2次元検索　②指定したセル位置の画像を別の場所に表示　という2つの方法を組合わせて実現しており、それぞれ個別でも応用手段はあると思うので、本記事を参考に色々とカスタマイズして応用していただければと思う。

まず、このExcelは、「画像集」と「画像検索」の2シート構成になっている。

「画像集」シートは検索したい多数の画像を貼りつけるシートであり、「画像検索」シートは検索した画像を表示するためのシートである。

以下、それぞれのシートを順に説明する。

1. 「画像集」シート
   
   このシートには、画像名とサイズ、画像自体を貼っていく。
   ルールとして、画像名の場所は任意の場所で良く、その1つ下の行に画像のサイズ（行数と列数）、更にその下の行に画像自体を貼る。
   
   以下が、作成例である。
   なお、この例では、無料の写真素材・AI画像素材「ぱくたそ」から以下の2つの画像を使用させていただいた。（ありがとうございます！！）
   ・へっくしょん（犬）の無料の写真素材
   ・チューリップフェアに散歩へ来た柴犬の無料の写真素材
   
   セルが判別しやすいように罫線は濃くし、赤字でカウント用の数字を入れているが、いずれも説明用のものなので実際には不要である。また、必須ではないが、列幅はあらかじめ全て同じ値に（セルがほぼ正方形に）にしていた方が扱いやすい。
   
   
   
   ここで、「へっくしょん（犬）の無料の写真素材」が画像名で、15, 18がそれぞれ画像のサイズ（行数と列数）である。
   
   このような感じで、何枚でも適応な場所に画像を貼っていけばよい。
   （ここで気付いた人がいるかも知れないが、ここで画像名や画像を貼る位置をA列などに固定とし、全画像を縦に並べていく方法にすれば、「①文字列を2次元検索」は1次元検索でよくmatch関数による単純な検索で代替できる。ただ、全ての画像を縦1列に貼っていくというのは、画像集シートの作成の自由度という点ではかなり劣ると思うので本記事では2次元検索と組合わせている。）
   
   
   
2. 「画像検索」シート
   
   まず、作成例を示す。
   
   
   1～3行目までが ①文字列を2次元検索 の実装部分、5行目以降が ②指定したセル位置の画像を別の場所に表示 の実装部分である。
   以下に、それぞれについて説明する。
   
   ①文字列を2次元検索

まず、①であるが、B3セルに以下の数式を記載している。（全て1行）

=MAKEARRAY(1,4,LAMBDA(x,y,LET(行max,120,列max,100,行unmatch,210,列unmatch,1,画像名,$B$1,列array,BYROW(OFFSET(画像集!$A$1,0,0,行max,列max),LAMBDA(z,IFNA(XMATCH(画像名,z),行max+1))),列min,MIN(列array),列番,IF(列min<=列max,列min,列unmatch),行番,IF(列min<=列max,XMATCH(列min,列array,0),行unmatch),高さ,OFFSET(画像集!$A$1,行番,列番-1),幅,OFFSET(画像集!$A$1,行番,列番),IF(y=1,行番,IF(y=2,列番,IF(y=3,高さ,IF(y=4,幅,"")))))))

これだとさすがに解り難いので、 以下に改行とインデントを付け★印で説明を記載する。

=MAKEARRAY(1,4, ★ MAKEARRAY関数で1行×4列を宣言
  LAMBDA(x,y, ★ y=1～4 で実行した結果が各列にセットされる
    LET(
      行max,120, ★ 「画像集」シートの最大検索範囲（行）
      列max,100, ★ 「画像集」シートの最大検索範囲（列）
      行unmatch,210, ★ 検索でアンマッチの場合の表示画像の行番
      列unmatch,1, ★ 検索でアンマッチの場合の表示画像の列番
      画像名,$B$1, ★ 検索する画像名が入ったセル
      列array,BYROW(OFFSET(画像集!$A$1,0,0,行max,列max),
        LAMBDA(z,IFNA(XMATCH(画像名,z),行max+1))), ★ 後述
      列min,MIN(列array), ★ 検索結果の配列の最小値（行番の候補）
      列番,IF(列min<=列max,列min,列unmatch), ★ 行番を確定
      行番,IF(列min<=列max,XMATCH(列min,列array,0),行unmatch),
            ★ 列番を確定
      高さ,OFFSET(画像集!$A$1,行番,列番-1), ★ 画像の高さを取得
      幅,OFFSET(画像集!$A$1,行番,列番), ★ 画像の幅を取得
      IF(y=1,行番, ★ y=1～4 それぞれの場合の値を設定
        IF(y=2,列番,
          IF(y=3,高さ,
            IF(y=4,幅,""
              )
            )
          )
        )
      )
    )
  )

MAKEARRAY関数を使ってB3～E3の4つのセルを一気に表示している。（いわゆる スピル 機能である。）
各セルの値の意味は2行目の表題通りであるが、「行番」「列番」が、B2セルの画像名で「画像集」シートを検索して得られたシート内の位置である。

BYROW関数は説明が難しいので、以下のサイトを参照いただきたい。
Officeのチカラ by きたみあきこ さん - BYROW関数 / BYCOL関数 ● LAMBDA関数に配列の各行/各列を渡して計算する

BYROW関数は、検索エリアの行数×列数1 の1次元配列を返し、それが 列array という変数に格納される。配列の各セルには、その行で画像名が見つかった場合はその列番号、見つからなかった場合はIFNA関数により検索エリアの最大列数+1 が設定される。その後、

列min,MIN(列array),

で見つかった列番号（のうち最小のもの）を 列min に設定、

XMATCH(列min,列array,0)

で、その列番号が入ったセルの位置（行番号）を取得している。

②指定したセル位置の画像を別の場所に表示

以下の手順で行う。

1) 数式 -> 名前の管理で、以下の式に「画像参照」という名前を割り当てる。
　

=OFFSET(画像集!$A$1,画像検索!$B$3+1,画像検索!$C$3-1,画像検索!$D$3,画像検索!$E$3)

2) 「画像集」シートの中から任意の画像を囲むセルを選択した状態（下図参照）で Cntl-C でコピー

3) 「画像検索」シートの中で画像を表示したい位置で、右クリック -> 形式を選択して貼り付け -> 「リンクされた図」のアイコンをクリックして図を貼り付ける。貼り付けた後、図の位置やサイズを見やすいように変更する。

4) 図を選択した状態で、図の数式を =画像参照 に変更する。
　

　
以上で完了である。

これで、「画像検索」シートのB2セルに画像名称をセットすれば、指定した画像が表示されるようになる。

では。