直積（クロス結合、交差結合）とピボット解除
LAMBDA以降の新関数の練習問題をツイッターで出題しています。
今回は、直積（クロス結合、交差結合）の作成とピボット解除についての2問です。
LAMBDA以降の新関数について
・LAMBDA関数とLAMBDA関数を引数に指定できるヘルパー関数群 ・TEXT処理関数 ・配列操作関数群
LAMBDA以降の新関数の使用例・2つの1次元配列から、それをクロス結合した結果を返す ・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力 ・名前定義を使わずにLAMBDA関数で再帰する方法 ・文字列を大文字小文字変換して大小文字の全組み合わせを出力 ・2つのテーブルの片方しかない行を縦に連結出力 ・3連単、3連複のフォーメーション買目を全て列挙する ・FILTER関数の出力行数・列数を指定する ・月が縦で日が横の表を、曜日（7列）で縦に折り返す ・文字列内の括弧（）（）の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・文字列の中から1文字削除の全パターン出力 ・クロスABC分析 ・月利が毎月変動する月複利の計算 ・レーベンシュタイン距離 ・文字列を数字と数字以外で分割 ・縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS） ・直積（クロス結合、…
縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）・出題のツイート ・解答ツイート ・INDEX+MATCH ・FILTER+MATCH ・CHOOSEROWS+MATCH ・無理矢理XLOOKUPを使ってみたら… ・総評
直積（クロス結合、交差結合）
出題
出題ツイートへのリンク
【エクセル問題】※最新関数の練習しないとね編
A列とB列の乱数から足し算（掛け算でも良い）のマトリックス表（10*10なら100ます計算）を作成してください。
※スピル数式一括縛り
※縦横はどっちでも良い
※添付2枚目は参考例
A列とB列の乱数から足し算（掛け算でも良い）のマトリックス表（10*10なら100ます計算）を作成してください。
※スピル数式一括縛り
※縦横はどっちでも良い
※添付2枚目は参考例
解答
解答ツイートへのリンク
表を3分割で作成します。
=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関数で順次変数に入れ、かつコメントをつけましたので数式が長くなっています。
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#)
場合によっては、このような方法が良い場合もあるかもしれません。
一番外側にLAMBDA関数を使用しているので、この数式はそのまま名前定義に入れることでカスタム関数として使用できます。
添付画像ではコメントを外して短めの数式にまとめています。
そのままでも良いのですが、改行が消えてしまうのでかえって見づらい気もしてしまいます。
=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枚目）
※こちらの続きです。
足し算のマトリックス表を縦に展開（アンピボット）してください。
結果は縦、横の順に並べ替えて出力してください。
※範囲指定は足し算の表全体を一括で（添付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#)
最初に3箇所の要素に分解
縦,DROP(CHOOSECOLS(x,1),1)
横,DROP(CHOOSEROWS(x,1),,1)
算,TOCOL(DROP(x,1,1))
そして関数「積」は、はけたさんより教わったこちらの数式を応用しました。
https://twitter.com/excelspeedup/status/1563025515329384448
T(N(y))
これは「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,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#)
これなら文字列化されないですし、少し数式も短くなってますね。
=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も使い勝手が良いです。
数値・文字どちらの配列も作成できるので重宝するように思います。
=LAMBDA(x,LET(
縦,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#)
EXPANDで""の配列作成の場合は、以下の部分だけ変更になります。
縦2,TOCOL(縦&EXPAND("",,COLUMNS(横),"")),
横2,TOCOL(横&EXPAND("",ROWS(縦),,"")),
出力,HSTACK(縦2,横2,算)*1,
どの問題でもそうですが、今回の問題もいろいろな数式の書き方ができますので試してみてください。
縦*値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,
どの問題でもそうですが、今回の問題もいろいろな数式の書き方ができますので試してみてください。
・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート
ワークシート関数の一覧（2010以降）・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft 365 Insider ・Microsoft「Office のサポート」
同じテーマ「エクセル入門」の記事
TEXTAFTER関数（テキストの指定文字列より後ろの部分を返す）
TEXTAFTER関数は、テキスト文字列から指定した区切り文字列の後ろに出現するテキストを返します。区切り文字列の後ろではなく前を取得する場合はTEXTBEFORE関数を使用します。TEXTAFTER関数の構文 =TEXTAFTER(text,delimiter,[instance_num],[match_mode…
LAMBDA以降の新関数の使用例・2つの1次元配列から、それをクロス結合した結果を返す ・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力 ・名前定義を使わずにLAMBDA関数で再帰する方法 ・文字列を大文字小文字変換して大小文字の全組み合わせを出力 ・2つのテーブルの片方しかない行を縦に連結出力 ・3連単、3連複のフォーメーション買目を全て列挙する ・FILTER関数の出力行数・列数を指定する ・月が縦で日が横の表を、曜日（7列）で縦に折り返す ・文字列内の括弧（）（）の中の文字を取り出して列挙 ・表内にあるセル内改行を複数行に展開 ・文字列の中から1文字削除の全パターン出力 ・クロスABC分析 ・月利が毎月変動する月複利の計算 ・レーベンシュタイン距離 ・文字列を数字と数字以外で分割 ・縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS） ・直積（クロス結合、…
縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）・出題のツイート ・解答ツイート ・INDEX+MATCH ・FILTER+MATCH ・CHOOSEROWS+MATCH ・無理矢理XLOOKUPを使ってみたら… ・総評
直積（クロス結合、交差結合）とピボット解除複数の文字列を検索して置換するSUBSTITUTE
・SUBSTITUTEのネスト ・TEXTSPLIT+CONCAT（またはTEXTJOIN） ・REDUCE+SUBSTITUTE ・複数の「検索文字列」「置換文字列」の実用例
LAMBDA以降の新関数の問題集・目次 ・LAMBDA踊るぞ編 ・LAMBDA踊るぞ編2 ・SCAN編 ・MAP編 ・REDUCE編 ・山手線営業の旅編 ・BYROW,BYCOL編 ・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
LAMBDA以降の新関数の問題と解説（ヘルパー関数編）・LAMBDA踊るぞ編 ・LAMBDA踊るぞ編2 ・秋華賞ガミったぞ記念編 ・MAP編 ・REDUCE編 ・山手線営業の旅編 ・BYROW,BYCOL編
LAMBDA以降の新関数の問題と解説（配列操作関数編）・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
PY関数（Pythonコードをセル内で実行）・PY関数の構文 ・PY関数の使い方 ・「Excel Labs」アドイン ・Python サンプルを試す ・pandas で説明 ・散布図を作成する ・Pythonの文法 ・PY関数で使えるライブラリ
GROUPBY関数（縦軸でグループ化して集計）GROUPBY関数は、行（縦）でグループ化し指定された関数によって値を集計します。行（縦）の軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベルに対応しています。総計・小計、並べ替え、フィルター処理もサポートされています。
PIVOTBY関数（縦軸と横軸でグループ化して集計）PIVOTBY関数は、行（縦）と列（横）でグループ化し指定された関数によって値を集計します。行（縦）と列（横）の2軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベル、複数の列グループレベルに対応しています。
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。