エクセル入門
直積(クロス結合、交差結合)とピボット解除

Excelの初心者向け入門解説
最終更新日:2022-09-22

直積(クロス結合、交差結合)とピボット解除


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枚目は参考例

Excel エクセル LAMBDA以降の新関数

Excel エクセル LAMBDA以降の新関数


解答

解答ツイートへのリンク

表を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#)

Excel エクセル LAMBDA以降の新関数

Excel エクセル LAMBDA以降の新関数


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関数を使用しているので、この数式はそのまま名前定義に入れることでカスタム関数として使用できます。
添付画像ではコメントを外して短めの数式にまとめています。
そのままでも良いのですが、改行が消えてしまうのでかえって見づらい気もしてしまいます。

Excel エクセル LAMBDA以降の新関数

Excel エクセル LAMBDA以降の新関数


ピボット解除

出題

出題ツイートへのリンク

【エクセル問題】※最新関数「完全に理解した」編
足し算のマトリックス表を縦に展開(アンピボット)してください。
結果は縦、横の順に並べ替えて出力してください。
※範囲指定は足し算の表全体を一括で(添付2枚目)
※こちらの続きです。

Excel エクセル LAMBDA以降の新関数

Excel エクセル LAMBDA以降の新関数


解答

解答ツイートへのリンク

【解答】
まずは作成した数式です。
=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#)

Excel エクセル LAMBDA以降の新関数


最初に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,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,
どの問題でもそうですが、今回の問題もいろいろな数式の書き方ができますので試してみてください。



ワークシート関数一覧

・文字列関数 ・日付と時刻の関数 ・情報関数 ・論理関数 ・検索/行列関数 ・数学/三角関数 ・統計関数 ・財務関数 ・エンジニアリング関数 ・データベース関数 ・アドイン/オートメーション ・キューブ ・Microsoft Office のサポート
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021より後の365で追加されたワークシート関数 ・Microsoft「Office のサポート」



同じテーマ「エクセル入門」の記事

WRAPROWS関数(1次元配列を指定数の行で折り返す)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
LAMBDA以降の新関数の問題集
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)
LAMBDA以降の新関数の問題と解説(配列操作関数編)


新着記事NEW ・・・新着記事一覧を見る

セル値でパス・ブック・シート名を指定|Power Query(M言語)入門(2023-02-07)
別ブックのシートを列可変で取り込む|Power Query(M言語)入門(2023-02-06)
ExcelとVBAの入門解説|エクセルの神髄(2022-12-14)
文字列のプロパティ名でオブジェクトを操作する方法|VBA技術解説(2022-12-14)
数字(1~50)を丸付き数字に変換するVBA|VBA技術解説(2022-11-15)
TEXTAFTER関数(テキストの指定文字列より後ろの部分を返す)|エクセル入門(2022-11-14)
TEXTBEFORE関数(テキストの指定文字列より前の部分を返す)|エクセル入門(2022-11-14)
TEXTSPLIT関数(列と行の区切り記号で文字列を分割)|エクセル入門(2022-11-12)
LAMBDA以降の新関数はVBAで使えるか|VBA技術解説(2022-11-11)
WRAPCOLS関数(1次元配列を指定数の列で折り返す)|エクセル入門(2022-11-08)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.マクロって何?VBAって何?|VBA入門
7.並べ替え(Sort)|VBA入門
8.Excelショートカットキー一覧|Excelリファレンス
9.エクセルVBAでのシート指定方法|VBA技術解説
10.Range以外の指定方法(Cells,Rows,Columns)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ