エクセル入門
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)

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

LAMBDA以降の新関数の問題と解説(ヘルパー関数編)


2022年にLAMBDA関数とLAMBDAヘルパー関数群、そして、TEXT処理の関数群と、配列操作関数群が追加となりました。
LAMBDA関数とヘルパー関数の問題と解説です。


LAMBDA関数とヘルパー関数、そしてその後の新関数についての詳細は以下を参照してください。

LAMBDA以降の新関数について
・LAMBDA関数とLAMBDA関数を引数に指定できるヘルパー関数群 ・TEXT処理関数 ・配列操作関数群

ツイッターで【エクセル問題】として、LAMBDA以降の関数を使った問題を出し、それに解答する形で解説していきます。
本ページは、このツイートのまとめです。
解答なしの問題だけはこちら:LAMBDA以降の新関数の問題集


目次

LAMBDA踊るぞ編

出題

【エクセル問題】※LAMBDA踊るぞ編
A2:A11は消費税込み金額です。
税抜き本体価格を計算してください。
B2に数式を入れB11までスピルさせます。
LAMBDAに対して引数(A2:A11,10%)を与えてください。。
税抜き処理は(1+税率)で割り算した結果を円単位に切り上げ。
↑税込み金額/(1+税率)↑
※LAMBDAの練習です。

LAMBDA以降 新関数 問題と解説


※行番号が間違っていました
※ A2:A11は消費税込み金額です。
税抜き本体価格を計算してください。
B2に数式を入れB11までスピルさせます。
LAMBDAに引数(A2:A11,10%)を与えてください。


解答・解説

【解答数式】
=LAMBDA(x,y,ROUNDUP(x/(1+y),0))(A2:A11,10%)
切り上げはCEILINGでも良いです。
まずはLAMBDAに慣れるための練習です。

LAMBDA以降 新関数 問題と解説

LAMBDA以降 新関数 問題と解説


=LAMBDA(x,y,ROUNDUP(x/(1+y),0))
この数式を名前定義に登録することで、セルで使用可能なカスタム関数とすることができます。

LAMBDA以降 新関数 問題と解説

LAMBDA以降 新関数 問題と解説


LAMBDA踊るぞ編2

出題

【エクセル問題】※LAMBDA踊るぞ編2
A2:A11は消費税込み金額です。
B2に数式を入れてD11までスピル
B列:税抜き金額
C列:消費税(税抜き金額×税率※円未満切り捨て)
D列:B列税抜き金額+C列消費税-A列税込み金額
数式は、
B2=LAMBDA(...)(A2:A11,10%)
このように作成。
※LAMBDA練習&HSTACKもね。

LAMBDA以降 新関数 問題と解説


解答・解説

【解答数式】
=LAMBDA(税込,税率,LET(
税抜,ROUNDUP(税込/(1+税率),0),
税額,ROUNDDOWN(税抜*税率,0),
差異,税抜+税額-税込,HSTACK(税抜,税額,差異))
)(A2:A11,10%)
これは順番通りにやっていけばできますね。
LAMBDAを使う必要はないのですが、使い慣れるための出題でした。

LAMBDA以降 新関数 問題と解説


秋華賞ガミったぞ記念編

出題

【エクセル問題】※秋華賞ガミったぞ記念編
A列B列は、ある着順の前後の着順を示してします。
A列の馬の次の着順は同一行B列の馬です。
例).プレサージュリフト → ライラック → ウインエクレール
A2:B16を入力することで、C2以下に全着順通りに並べてください。
※データは画像ALT

LAMBDA以降 新関数 問題と解説
競争馬名 次の着順の馬 着順
プレサージュリフト ライラック
アートハウス エグランタイン
サウンドビバーチェ ストーリア
ライラック ウインエクレール
ストーリア プレサージュリフト
ウインエクレール ウォーターナビレラ
ウォーターナビレラ エリカヴィータ
タガノフィナーレ ブライトオンベイス
メモリーレゾン アートハウス
ナミュール スターズオンアース
ラブパイロー タガノフィナーレ
スタニングローズ ナミュール
スターズオンアース メモリーレゾン
エグランタイン サウンドビバーチェ
エリカヴィータ ラブパイロー


解答・解説

【解答数式】
=LAMBDA(ar,
LET(a,TAKE(ar,,1),b,TAKE(ar,,-1),
f,FILTER(a,COUNTIFS(b,a)=0),
s,SCAN(f,a,LAMBDA(x,y,XLOOKUP(x,a,b))),
VSTACK(f,s)
順に辿れば良いのは明らかですね。
とにかく1着または最下位の馬を探すところから。
これには方法がいろいろありそうです。

LAMBDA以降 新関数 問題と解説


A列にしかいない馬が1着
B列にしかいない馬が最下位
ただし1着馬を探した方が気分的に良いですかねw
先の数式ではCOUNTIFSを使いました。
VSTACK+UNIQOU(1回だけ出現)で先頭が1着という方法もあります。
また順に辿る方法でも、深さがはっきりしない場合は再帰を使う方法もあります。


LAMBDAで再帰を作成して名前定義に登録します。
NEXTHORSE=LAMBDA(x,a,b,LET(z,XLOOKUP(x,a,b,""),IF(z="",x,VSTACK(x,NEXTHORSE(z,a,b)))))
(もう少し上手く書けそうですが…)
後は先と同じで1着馬を引数で渡してやるだけです。
※引数および1着馬の取得は最初とは違う方法にしました。

LAMBDA以降 新関数 問題と解説

LAMBDA以降 新関数 問題と解説


MAP編

出題

【エクセル問題】※MAP編(使ってねw)
A1=SEQUENCE(10,10)
素数は特別らしい。
どう特別かわからないので、前後の数値と素数だけ残して他の数値を消してみることにしました。
23は素数なので前後の22,24と共に残す。
25,26,27は自身も前後も素数ではないので消します。
※素数判定は適当に探して😅

LAMBDA以降 新関数 問題と解説


解答・解説

【解答数式】
=LET(a,A1#,
p,LAMBDA(x,MIN(MOD(x,SEQUENCE(SQRT(x+1),1,2)))),
MAP(a,LAMBDA(x,LET(xp,x+1,xm,x-1,IF(OR(p(
x),p(xp),p(xm)),x,"")))))
素数判定は本題ではないのでお好きなようにと言う事で。これは効率は良くないですね。
あくまでLAMBDAとMAPの練習問題として出題しました。

LAMBDA以降 新関数 問題と解説


単純な計算ではMAP関数を使うまでもなく、単なるスピル数式で済んでしまいます。
スピル数式を書いていて、スピルしなくなったらこういう関数の出番だという事を理解してもらえれば良いと思います。


REDUCE編

出題

【エクセル問題】※REDUCE編
500円玉貯金をすることにしました。
月曜は500円、火曜は1,000円,...金曜は2,500円
そして土日は前日の貯金額の10%(500円単位で切り捨て)を使う事にします。
開始日を入力し、その年の年末大晦日終了時点でいくらの貯金がたまっているか計算してください。
※ALTに補足

LAMBDA以降 新関数 問題と解説
入力はA1である必要はありません。
画像E:Fは作業セルでの検算です。
月曜:500
火曜:1000
水曜:1500
木曜:2000
金曜:2500
そして、土日は前日貯金額の10%を500円単位(500円未満切り捨て)で使うという事です。


解答・解説

こちら解答出し忘れていました。
【解答数式】
=LAMBDA(開始日,単位,使用率,
LET(期間,SEQUENCE(DATE(YEAR(開始日)+1,1,1)-開始日,,開始日),
REDUCE(0,期間,LAMBDA(x,y,LET(w,WEEKDAY(y,2),IF(w>=6,x-FLOOR(x*使用率,単位),x+w*単位)))))
)(A1,500,10%)

LAMBDA以降 新関数 問題と解説


SEQUENCE関数を使って期間を出して、その後は検証数式でやっているように順に足し上げていきます。
REDUCE関数は、このように配列を使って足し上げる処理に使います。
REDUCE内のLAMBDAの数式で各種条件に沿った数式を組み立てます。


山手線営業の旅編

出題

【エクセル問題】※山手線営業の旅編
山手線沿線を営業して回りました。
秋葉原を出発、それぞれの駅で下車して営業して、再び同じ駅に乗ります。
降車駅ごとに清算します。
それでは運賃の一覧を出力してください。
※画像参照
※データは各画像ALT
※サンプルは正しいか不明😅
山手線って安いのね😇

すみません、ちょっと情報不足がありました。
自分では書いたつもりになってました。
東京の人は知ってるかもだけど…
※1km未満は1km単位で切上です。

LAMBDA以降 新関数 問題と解説

LAMBDA以降 新関数 問題と解説

LAMBDA以降 新関数 問題と解説
出発駅 秋葉原 運賃
清算降車して、
再び同じ駅で乗車
御徒町
日暮里
田端
駒込
池袋
新宿
代々木
原宿
田町
新橋
秋葉原

出発駅 到着駅 距離(km)
東京 神田 1.3
神田 秋葉原 0.7
秋葉原 御徒町 1
御徒町 上野 0.6
上野 鶯谷 1.1
鶯谷 日暮里 1.1
日暮里 西日暮里 0.5
西日暮里 田端 0.8
田端 駒込 1.6
駒込 巣鴨 0.7
巣鴨 大塚 1.1
大塚 池袋 1.8
池袋 目白 1.2
目白 高田馬場 0.9
高田馬場 新大久保 1.4
新大久保 新宿 1.3
新宿 代々木 0.7
代々木 原宿 1.5
原宿 渋谷 1.2
渋谷 恵比寿 1.6
恵比寿 目黒 1.5
目黒 五反田 1.2
五反田 大崎 0.9
大崎 品川 2
品川 高輪ゲートウェイ 0.9
高輪ゲートウェイ 田町 1.3
田町 浜松町 1.5
浜松町 新橋 1.2
新橋 有楽町 1.1
有楽町 東京 0.8

下限Km 上限Km 金額
1 3 140
4 6 160
7 10 170
11 15 200
16 20 270
21 25 350
26 30 420
31 35 490


解答・解説

(再)リプ先間違え
【解答数式】
乗降駅から距離を求める時に悩むのが、一覧の下にある駅から上にある駅への移動です。
いろいろやり方はありますが、広く使える方法で、一覧を2つつなげる方法を使いました。
そういえば数珠順列の輪の時も使いました。
https://excel-ubara.com/excel5/EXCEL89903.html
※数式は画像ALT

LAMBDA以降 新関数 問題と解説
=LAMBDA(乗降駅,駅距離,運賃表,
LET(
乗車駅,DROP(乗降駅,-1),
降車駅,DROP(乗降駅,1),
乗車i,MATCH(乗車駅,INDEX(駅距離,0,1),0),
降車i,MATCH(降車駅,INDEX(駅距離,0,2),0),
降車ii,降車i+ROWS(駅距離)*(乗車i>降車i)/2,
区間距離,MAP(乗車i,降車ii,LAMBDA(x,y,SUM(TAKE(DROP(INDEX(駅距離,0,3),x-1),y-x+1)))),
VLOOKUP(CEILING(区間距離,1),運賃表,3,TRUE)
))(B1:B12,VSTACK(E2:G31,E2:G31),I2:K9)

乗車駅>降車駅
この判定をして、この場合は一覧のインデックイをずらしています。
後はその間の距離を合計すれば良いです。
一覧から必要な範囲を合計する方法はいろいろとおると思います。
順序よくやれば無難しい処理ではないのですが、スピルさせようとすると数式の組み方が難しいかもしれません。


BYROW,BYCOL編

出題

【エクセル問題】※BYROW,BYCOL編
A2:F12に個人別の5教科(英語、国語、数学、理科、社会)の点数一覧があります。
全科目が平均点以上の人だけをH2以下に一覧出力してください。
※未受験科目は0点として入力するものとします。
※データは画像ALT
そうそうたる受験生ですね・・・

LAMBDA以降 新関数 問題と解説


追記
「平均点」とは、各科目ごとの平均点です。


解答・解説

【解答数式】
=FILTER(A2:F12,
BYROW(
B2:F12>=BYCOL(B2:F12,LAMBDA(x,AVERAGE(x))),
LAMBDA(y,AND(y))))
BYROWとBYCOLの練習問題です。
BYCOLで科目ごとの平均を算出。
BYROWで個人点と平均点の比較をANDで集約。
これは元配列と同行数のTRUE/FALSEの縦配列です。
これをFILTERの「含む」に指定。

LAMBDA以降 新関数 問題と解説




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

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


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

数字(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)
WRAPROWS関数(1次元配列を指定数の行で折り返す)|エクセル入門(2022-11-08)
EXPAND関数(配列を指定された行と列に拡張する)|エクセル入門(2022-11-07)
TAKE関数(配列の先頭/末尾から指定行/列数を取得)|エクセル入門(2022-11-06)
DROP関数(配列の先頭/末尾から指定行/列数を除外)|エクセル入門(2022-11-06)


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

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.マクロって何?VBAって何?|VBA入門
10.ExcelマクロVBAの基礎を学習する方法|エクセルの神髄




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


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



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