- FLATTEN関数を利用して横持ちの表を縦持ちに変換することができます。
手順
FLATTEN関数を用いる例
変換の対象となっているのはB3:E6の範囲のデータで、これに対しG3セルに式を入力することで変換を実現しています。
G3セル
=QUERY(ARRAYFORMULA(SPLIT(FLATTEN(B3:B6&"_"&C3:E6),"_")),"WHERE Col2<>''")
「B3:B6&"_"&C3:E6」の部分によりB列の値とC~E列の値を「_」でつないた文字列を取得し、FLATTEN関数により値を縦1列に並べ、SPLIT関数で2列に分割し、最後にQUERY関数により2列目が空白であるものを除いています。データそのものに「_」が含まれているとうまくいきませんので、その場合は別の文字に適宜変更してください。
（注：データが数値の場合は「<>''」を「IS NOT NULL」にしてみてください）
最初の「B3:B6&"_"&C3:E6」の部分が何をやっているかわかりにくい感じですが、ここだけ実行すると次のようになります。これで以降の部分も理解しやすいと思います。
CONCATENATE関数等を用いる例
こちらは文字列結合関数を駆使してなんとかしてみた例です。
上記のFLATTEN関数が利用できるようになったため、もはや実験的な意味しかないものの一応残しておきます。
G3セルに入力されている式は次の通りです。
=QUERY(TRANSPOSE({SPLIT(CONCATENATE(ARRAYFORMULA(REPT(B3:B6&",",COLUMNS(C3:E6)))),",");SPLIT(TEXTJOIN(",",,C3:E6),",",,)}),"WHERE Col2<>''") ちょっと長いですが補足しますと、波カッコ（{）からセミコロン（;）の前までは同じ氏名を繰り返し横に並べて出力している部分です（ここでは結果的に4名×3回。全体で横12セル）。セミコロンの後から波カッコ（}）までは資格の名称を並べて出力している部分です（横12セル）。これらをセミコロンで上下につないでからTRANSPOSEで転置して縦長にし、QUERYで資格名が空の行を除いています。
値の走査を簡略化するため全体的に文字列結合関数（CONCATENATEやTEXTJOIN）に頼るアプローチになっています。しかし、これらの関数により結合される文字列の長さが合計5万字ほどに達するとエラーになります。膨大なデータの処理には向きません。
また、文字列操作において区切り文字としてカンマを使っていますので、対象文字列にカンマが含まれる場合は「","」（4か所）を別の記号に変える必要があります。
「COLUMNS(C3:E6)」の部分は繰り返し部分の列数を測っているだけですので、即値にすれば（この場合は3）少し短くできます。
なお、逆の変換（縦持ち→横持ち）については次の記事をご覧ください。
