直積（クロス結合、交差結合）とピボット解除
LAMBDA以降の新関数の練習問題をツイッターで出題しています。
今回は、直積（クロス結合、交差結合）の作成とピボット解除についての2問です。
直積（クロス結合、交差結合）
出題
A列とB列の乱数から足し算（掛け算でも良い）のマトリックス表（10*10なら100ます計算）を作成してください。
※スピル数式一括縛り
※縦横はどっちでも良い
※添付2枚目は参考例
解答
=LAMBDA(x,y,
LET(
r,TOCOL(x),縦配列に,,
c,TOROW(y),横配列に,①,
rc,r+c,足し算のマトリックス作成,②,
crc,VSTACK(c,rc),横見出しと計算結果を縦に,②①,
rh,VSTACK("",r),縦見出しの先頭に空白追加,③,
↓③の横に②①を配置,関数戻り値,
HSTACK(rh,crc))
)(A1#,B1#)
LET関数の使い方についてはこちらを。
https://excel-ubara.com/excel1/EXCEL636.html
=LAMBDA(x,y,LET(
r,VSTACK("",TOCOL(x)),
c,HSTACK("",TOROW(y)),
LAMBDA(x,y,IFS(x&y="","",x="",y,y="",x,TRUE,x+y))(r,c))
)(A1#,B1#)
場合によっては、このような方法が良い場合もあるかもしれません。
添付画像ではコメントを外して短めの数式にまとめています。
そのままでも良いのですが、改行が消えてしまうのでかえって見づらい気もしてしまいます。
ピボット解除
出題
足し算のマトリックス表を縦に展開（アンピボット）してください。
結果は縦、横の順に並べ替えて出力してください。
※範囲指定は足し算の表全体を一括で（添付2枚目）
※こちらの続きです。
解答
まずは作成した数式です。
=LAMBDA(x,LET(
縦,DROP(CHOOSECOLS(x,1),1),
横,DROP(CHOOSEROWS(x,1),,1),
算,TOCOL(DROP(x,1,1)),
積,LAMBDA(x,y,TOCOL(x&T(N(y)))),
出力,HSTACK(積(縦,横),積(横,縦),算)*1,
SORTBY(出力,CHOOSECOLS(出力,1),,CHOOSECOLS(出力,2),)
))(D1#)
縦,DROP(CHOOSECOLS(x,1),1)
横,DROP(CHOOSEROWS(x,1),,1)
算,TOCOL(DROP(x,1,1))
そして関数「積」は、はけたさんより教わったこちらの数式を応用しました。
https://twitter.com/excelspeedup/status/1563025515329384448
これは「y」を含む数式で結果が""になれば何でも良いです。
数値限定ならT(y)だけで良いのですが今後の使い回しを考慮。
IF(y=y,"")
LEFT(y,0)
これらでも同じ。
そしてHSTACKで3列を結合
積(縦,横)→N列
積(横,縦)→O列
算→P列
*1は積で文字列になってしまう為。
最後にSORTして完成です。
=LAMBDA(x,LET(
縦,DROP(CHOOSECOLS(x,1),1),
横,DROP(CHOOSEROWS(x,1),,1),
算,TOCOL(DROP(x,1,1)),
積,LAMBDA(x,TOCOL(IF(縦=横,x,x))),
出力,HSTACK(積(縦),積(横),算),
SORTBY(出力,CHOOSECOLS(出力,1),,CHOOSECOLS(出力,2),)
))(D1#)
これなら文字列化されないですし、少し数式も短くなってますね。
縦*値1の横配列
横*値1の縦配列
と言うものが多かったようです。
数値の配列作成にはSEQUENCEが便利ですが、EXPANDも使い勝手が良いです。
数値・文字どちらの配列も作成できるので重宝するように思います。
縦,DROP(CHOOSECOLS(x,1),1),
横,DROP(CHOOSEROWS(x,1),,1),
算,TOCOL(DROP(x,1,1)),
縦2,TOCOL(縦*EXPAND(1,,COLUMNS(横),1)),
横2,TOCOL(横*EXPAND(1,ROWS(縦),,1)),
出力,HSTACK(縦2,横2,算),
SORTBY(出力,CHOOSECOLS(出力,1),,CHOOSECOLS(出力,2),)
))(D1#)
縦2,TOCOL(縦&EXPAND("",,COLUMNS(横),"")),
横2,TOCOL(横&EXPAND("",ROWS(縦),,"")),
出力,HSTACK(縦2,横2,算)*1,
どの問題でもそうですが、今回の問題もいろいろな数式の書き方ができますので試してみてください。
同じテーマ「エクセル入門」の記事
TEXTAFTER関数（テキストの指定文字列より後ろの部分を返す）
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）
直積（クロス結合、交差結合）とピボット解除
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説（ヘルパー関数編）
LAMBDA以降の新関数の問題と解説（配列操作関数編）
PY関数（Pythonコードをセル内で実行）
GROUPBY関数（縦軸でグループ化して集計）
PIVOTBY関数（縦軸と横軸でグループ化して集計）
イータ縮小ラムダ（eta reduced lambda）
新着記事NEW ・・・新着記事一覧を見る
イータ縮小ラムダ（eta reduced lambda）｜エクセル入門(2023-11-20)
PIVOTBY関数（縦軸と横軸でグループ化して集計）｜エクセル入門(2023-11-19)
GROUPBY関数（縦軸でグループ化して集計）｜エクセル入門(2023-11-18)
PY関数（Pythonコードをセル内で実行）｜エクセル入門(2023-11-17)
画像「セルに配置」のVBAについて（365の新機能）｜VBA技術解説(2023-11-13)
スピルのゴーストの範囲を選択するVBA｜ツイッター出題回答 (2023-10-31)
エクセル試験3：月間の所定労働時間｜エクセル練習問題(2023-10-04)
エクセル試験2：所得税の計算｜エクセル練習問題(2023-10-04)
エクセル試験1：曜日別の平均客単価｜エクセル練習問題(2023-10-04)
列全体を指定する時のRangeとColumnsの違い｜ツイッター出題回答 (2023-09-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.変数宣言のDimとデータ型｜VBA入門
6.条件分岐（IF）｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。