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

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

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


LAMBDA以降の新関数の練習問題をツイッターで出題しています。
今回は、直積(クロス結合、交差結合)の作成とピボット解除についての2問です。

LAMBDA以降の新関数について
・LAMBDA関数とLAMBDA関数を引数に指定できる関数群 ・TEXT処理の3関数 ・配列操作関数群 ・LAMBDA以降の新関数の使用例
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,
どの問題でもそうですが、今回の問題もいろいろな数式の書き方ができますので試してみてください。



ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…
ワークシート関数の一覧(2010以降)
・Excel2010で追加されたワークシート関数 ・Excel2013で追加されたワークシート関数 ・Excel2016/Excel2019で追加されたワークシート関数 ・Excel2021で追加されたワークシート関数 ・Excel2021以降の365で追加されたワークシート関数



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

SEQUENCE関数(連続数値)
XLOOKUP関数(VLOOKUP関数を拡張した新関数)
XMATCH関数(MATCH関数を拡張した新関数)
LET関数(数式で変数を使う)
スピルと新関数の練習(XLOOKUP関数、LET関数、VBAまで)
LAMBDA以降の新関数について
LAMBDA以降の新関数の使用例
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)
直積(クロス結合、交差結合)とピボット解除
スピルとは:スピル入門の問題と解説
スピルとは:旧関数でスピルを使う問題と解説


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

スピルとは:旧関数でスピルを使う問題と解説|エクセル入門(2022-09-21)
スピルとは:スピル入門の問題と解説|エクセル入門(2022-09-16)
直積(クロス結合、交差結合)とピボット解除|エクセル入門(2022-09-08)
脱字メーカー(文字列から1文字削除)|ツイッター出題回答 (2022-09-05)
【VBA学習のお勧めコース】|VBA入門(2022-09-02)
振込手数料を先方負担にした時の振込金額と手数料の算出|ツイッター出題回答 (2022-09-01)
構成比を合計しても100%にならないと言われた…|ツイッター出題回答 (2022-09-01)
一覧から複数条件(部分一致、範囲)に合致するデータを抽出する|ツイッター出題回答 (2022-08-30)
縦横スピルしないXLOOKUP代替(MATCH+INDEX,FILTER,CHOOSEROWS)|エクセル入門(2022-08-27)
IF関数の論理式で比較演算子を省略したCOUNT系関数を書くのは|ツイッター出題回答 (2022-08-23)


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

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




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


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



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