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

Excelの初心者向け入門解説
公開日:2022-10-24 最終更新日:2023-11-14

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+UNIQUE(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以降 新関数 問題と解説




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

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を使ってみたら… ・総評
直積(クロス結合、交差結合)とピボット解除
LAMBDA以降の新関数の練習問題をツイッターで出題しています。今回は、直積(クロス結合、交差結合)の作成とピボット解除についての2問です。LAMBDA以降の新関数について LAMBDA以降の新関数の使用例・2つの1次元配列から、それをクロス結合した結果を返す・A列が同じ行のB列の値を連結して、A列の一意な値ととも…
複数の文字列を検索して置換するSUBSTITUTE
・SUBSTITUTEのネスト ・TEXTSPLIT+CONCAT(またはTEXTJOIN) ・REDUCE+SUBSTITUTE ・複数の「検索文字列」「置換文字列」の実用例
LAMBDA以降の新関数の問題集
・目次 ・LAMBDA踊るぞ編 ・LAMBDA踊るぞ編2 ・SCAN編 ・MAP編 ・REDUCE編 ・山手線営業の旅編 ・BYROW,BYCOL編 ・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
LAMBDA以降の新関数の問題と解説(ヘルパー関数編)
LAMBDA以降の新関数の問題と解説(配列操作関数編)
・ここからはフリー問題だよ編 ・好きな関数を使ってくれ編 ・いろんな関数を使ってね編 ・魔球編 ・豚が木に登れば落ちることもある編 ・たまには100点とってみたいものだ編 ・これは何と言う処理なんだろ編 ・数字は嫌いなのだ編 ・外堀から埋めていくぞ編 ・なぜわざわざそうしたいのか編 ・かっこつけてんじゃないよ編 ・頭の体操だけど新関数の出番はあるか?編 ・定番だけど出してなかったよね編
PY関数(Pythonコードをセル内で実行)
・PY関数の構文 ・PY関数の使い方 ・「Excel Labs」アドイン ・Python サンプルを試す ・pandas で説明 ・散布図を作成する ・Pythonの文法 ・PY関数で使えるライブラリ
GROUPBY関数(縦軸でグループ化して集計)
GROUPBY関数は、行(縦)でグループ化し指定された関数によって値を集計します。行(縦)の軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベルに対応しています。総計・小計、並べ替え、フィルター処理もサポートされています。
PIVOTBY関数(縦軸と横軸でグループ化して集計)
PIVOTBY関数は、行(縦)と列(横)でグループ化し指定された関数によって値を集計します。行(縦)と列(横)の2軸に沿ったグループ化と、関連する値の集計がサポートされます。複数の行グループレベル、複数の列グループレベルに対応しています。
イータ縮小ラムダ(eta reduced lambda)
・LAMBDAヘルパー関数 ・イータ縮小ラムダ関数 ・イータ縮小ラムダ 使用例と解説


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

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)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


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



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