Squareで公演チケットのオンライン販売～DLデータ活用編（後）～
ここは、小～中劇場規模の舞台公演（主に演劇）で制作やチケット管理業務をしてきた私が培ってきた事を綴るnoteです。
※追記(2024/5/17)※
この記事執筆時から、ダウンロードの方式・書式が変更になっていますが備忘として下記残しておきます！
前回の記事でエクセル関数、TEXTBEFORE/AFTER を紹介しました。
今回はXLOOKUPに複数条件を設定し、数行にわたる1件の申し込みを、1行にまとめる方法です。
販売データのダウンロード手順で、書式が違うとお伝えしましたが、このように
(１) [ホーム]→[オーダー]→データをエクスポート
1行毎に購入者情報（名前、mailアドレス等）が記載されているのに対し、
(２) [ホーム]→[オンラインビジネス]→[オーダー]→データをエクスポート
こちらは、1行目に購入者情報、2行目以降の購入商品の行には名前等は記載されません。
（unique関数とxlookup(vlookup)を使用して1行にまとめる方法はこちら）
ただこれだけだと1回の申し込みで1公演のみの予約でしたら良いですが、一般とU25など複数券種の予約や、複数公演を購入される方がいた場合、2商品目を抽出することが出来ません。
xlookupの複数条件を設定し、2商品目以降も抽出する方法を説明します。
1.COUNTIFで行数を数える
まずはCOUNTIFを使用して行数を数えます。
COUNTIFは指定した範囲内にある特定の文字がいくつあるかを数える関数です。基本的な使用方法はこちら。
この関数を使用して、同じ注文番号の行数を数えます。
その際にこのように指定範囲をすべて同じにしてしまうと、
=COUNTIF(A:A,A2)
全ての行に同じ数字がはいってしまいますので、今回は指定範囲をその行より下の行を含まないように設定します。
=COUNTIF($A$2:A2,A2)
範囲指定の起点のみ絶対参照します。これを下にコピペしていけばOKです。
これで、同じ注文番号の何行目かを表示することができました。
2.XLOOKUPを複数条件設定する。
xlookupの式は
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
です。（[ ]は省略可）
条件を追加したい場合は、＆ を使用します。
=XLOOKUP(検索値１&検索値2, 検索範囲１&検索範囲2, 戻り配列)
なので上記表Aの購入データを取り出したい場合は、
検索範囲1=注文番号（A列）
検索範囲2=count（I列）
を組み合わせます。
検索値はUNIQUE関数で取り出した注文番号（L列）と、数値「2」を入れて、商品オプション(E列)を取り出します。
=XLOOKUP($L2&2,$A:$A&$I:$I,E:E)
更にTEXT関数を組み合わせて、公演日時を取り出します。
=TEXTBEFORE(TEXTAFTER(XLOOKUP($L2&2,$A:$A&$I:$I,E:E),":",1),",")
種類1もXLOOKUPとTEXT関数を組み合わせて取り出します。
枚数はXLOOKUPだけでOKです。
3.値が見つからない場合
同じように検索値2に数値「3」を入れれば3行目にある2商品目の値が取り出せます。
しかし、鈴木さんは1種類しか購入していないので、上記[表A]のcountに3行目が存在しないためエラー表示となります。
なので、XLOOKUPの[見つからない場合]に値をいれます。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
少し強引ですが、TEXT関数の検索値に使用したコロン(：)とカンマ( , )を利用して、":-," と入れます。
これで3行目がない場合は、この値（:-,）をTEXT関数が処理し、ハイフン( - )が表示されます。
=TEXTBEFORE(TEXTAFTER(XLOOKUP($L2&3,$A:$A&$I:$I,E:E,":-,"),":",1),",")
同じように、種類、枚数も申し込みがない場合にはハイフンが表示されるようにします。
種類2
=TEXTAFTER(XLOOKUP($L2&3,$A:$A&$I:$I,E:E,"::-"),":",2)
枚数2
=XLOOKUP($L2&3,$A:$A&$I:$I,F:F,"-")
ちなみにエラーが出た場合の処理方法は、IFERROR関数もあります。
=IFERROR(値,エラーの場合の値)
=IFERROR(TEXTBEFORE(TEXTAFTER(XLOOKUP($L2&3,$A:$A&$I:$I,E:E),":",1),","),"-")
同じ結果が表示されます。
数式は長くなりますが、列ごとに値を変えなくてよいのでシンプルといえばシンプルです。
これで、注文番号ごとのデータを一行にまとめることができました。
4.COUNTIFで購入件数を数える
3種類以上の席種や公演日時を同時購入するような公演でなければ大体これで賄えるのではないでしょうか。
念のためチェックできるようにcountif関数で購入商品件数を表示してみます。表A一行目は個人データのみなので、-1をすれば購入した商品数が表示されます。
=COUNTIF(A:A,L2)-1
このほか、電話番号やメールアドレスなど欲しい項目を取り出していけば申込一覧が作成でき、集計や帳票出力に使用することができます！
今回はこのようにしましたが、商品名を「券種」にして、バリエーションを「公演日時」だけにしても良いかと思います。
そもそもデータのエクスポート方法を（１）にすればシンプルに活用できるかと思います。
数式例を増やすためにこのようにしてみましたが、それぞれの公演に合ったよりよい登録方法で設定してみてください。