GoogleスプレッドシートでXLOOKUPを使って条件に合うデータを抽出する際、入力セルが空の場合でも「データなし」を表示したいケースがあります。ここでは、既存の数式を修正して空セル対応にする方法をご紹介します。
1. 現状の数式の確認
現在使用している数式は以下です。
=ARRAYFORMULA(XLOOKUP(1,('データベース'!$B:$B=E5)*('データベース'!$D:$D=E6)*('データベース'!$G:$G=E7)*(COUNTA(E5:E7)=3),'データベース'!$T:$T,"データなし"))
この数式はE5:E7がすべて入力されている場合のみ検索し、条件に合致しない場合は「データなし」を返す仕組みになっています。
2. 空セル対応のポイント
問題は、E5:E7が空のときに検索処理が行われず、意図した「データなし」が表示されない点です。COUNTA(E5:E7)=3の条件を調整することで、空セルの場合にも「データなし」を返すことが可能です。
3. 改良版の数式例
空セルにも対応させるにはIFを組み合わせます。
=IF(COUNTA(E5:E7)=0,"データなし",ARRAYFORMULA(XLOOKUP(1,('データベース'!$B:$B=E5)*('データベース'!$D:$D=E6)*('データベース'!$G:$G=E7),'データベース'!$T:$T,"データなし")))
ポイントは、最初にE5:E7がすべて空かどうかをIFで判定し、空の場合は直接「データなし」を返すことです。入力がある場合のみXLOOKUPで検索します。
4. 注意点と応用
- ARRAYFORMULAは複数行対応を意図している場合に有効です。1行のみの検索なら不要です。
- 空セルを含む場合の検索条件をさらに柔軟にしたい場合は、IFやIFERRORでカスタマイズ可能です。
まとめ
E5:E7が空のときも「データなし」を表示するには、IFで空セル判定を行い、XLOOKUPの前に制御する方法が有効です。これにより、すべての入力状態に対応した安定した数式が作れます。
コメント