「Googleスプレッドシートから見た！」Excel 14の新関数 -4 TAKE / DROP

mir

2022年12月25日 14:40

Excelに追加された 14の新関数を Googleスプレッドシートからの視点で検証する記事 4回目です。

関数の特徴
Excelでのメリット、デメリット、活用
Googleスプレッドシートの機能、関数との違い
Googleスプレッドシートでは無い機能をどう補うか

主にこの 4つの視点で検証していきます。

前回の記事

今回で 14の新関数のうち7つ、ようやく半分が検証完了です。

なんか新関数多すぎないか・・・

EXCEL関数擬人化をやってる 「関数ちゃん」も（キャラ追加するとしたら）大変じゃない？

今回追加の配列操作系は 2つセットが多いんで、前髪で左目隠れてるのと、右目隠れてるみたいな 双子キャラ が一気に増える感じでしょうかｗ

大多数の（SUM程度は使える）初心者と、一部のマニアの 関数格差がますます広がっていく感じ。

EXCEL 14の新関数 TAKE / DROP

Excel 14の新関数 4回目は SHIFT選択系の TAKE、DROPを取り上げます。

この2つの関数は、TAKEが残す範囲を選択する関数、DROPが落とす（捨てる）範囲を選択する関数で、陰と陽というか白黒アンジャッシュというか、反対の動きをする関数です。もちろん多目的に使えますw

今回も2つまとめて検証していきましょう。

TAKE / DROPの特徴

EXCEL新関数シリーズ 1回目に取り上げた EXPANDは対象の範囲（配列）を拡張する関数でしたが、TAKE、DROP は逆にどちらも 範囲（配列）を縮小（小さく）する関数です。

EXPANDについては、以下を参照ください。

TAKE、DROPの引数、動きをまとめると以下になります。

■TAKE　残す範囲を指定する関数
=TAKE(array,rows,columns)
rows　・・・　上から（マイナス指定時は下から）残す行数
columns　・・・　左から（マイナス指定時は右から）残す行数

■DROP　捨てる範囲を指定する関数
=DROP(array,rows,columns)
rows　・・・　上から（マイナス指定時は下から）捨てる行数columns　・・・　左から（マイナス指定時は右から）捨てる行数

array ・・・　対象となるセル範囲、または配列データ

それぞれ EXCEL上での動きを見てみましょう。

■TAKE関数

=TAKE(A1:D7,3) 　　※上から3行を残す
=TAKE(A1:D7,4,2) 　※上から4行、左から2列を残す

=TAKE(A1:D7,-3) 　　※下から3行を残す
=TAKE(A1:D7,-4,-2)　※下から4行、右から2列を残す

rows、columns は省略した場合は全て残す

■DROP関数

=DROP(A1:D7,3) 　　※上から3行を捨てる
=DROP(A1:D7,4,2) 　※上から4行、左から2列を捨てる

=DROP(A1:D7,-3) 　　※下から3行を捨てる
=DROP(A1:D7,-4,-2)　※下から4行、右から2列を捨てる

rows、columns は省略した場合は全て残す扱い

TAKE、DROP どちらも引数は一緒ですね。
「残す」と「捨てる」の違いが、わかったでしょうか？

ちなみに rows,columns の引数は省略できますが、

=TAKE(A1:D7)　や　=DROP(A1:D7)　では機能しません。

=TAKE(A1:D7,) 、=DROP(A1:D7,) ならOKですが、これはどちらも単に A1:D7をそのまま返すだけです。

（余談）TAKEとDROPはそれぞれで代替できる

ちなみにDROPの引数を調整すれば、TAKEと同じことができます。逆も然りで、TAKEでDROPの代替も出来ます。

たとえば A1:D7という 7行4列のデータがあった時、

上から3行を残す
下から4行を捨てる

この2つは結局同じ結果を返します。つまり、

=TAKE(A1:D7,3)
=DROP(A1:D7,-4)　
※ -4は先頭から残す行数 -　配列の行数

このように DROPで TAKEと同じ結果を得ることができます。

=TAKE(A1:D7,4,2)
=DROP(A1:D7,-3,-2)
※ -2 は左から残す列数 - 配列の列数

列方向についても同じことが言えます。

ただ、だからと言って今回の場合は TAKEだけあれば DROP不要じゃない？とは思いません。

配列（範囲）の行数を取得して差分を・・・と手間を考えれば、直観的に簡単に配列操作ができるという点で、TAKEとDROPの2つを状況に応じて使い分けるのが良いかなと思います。

ちなみに mirが Shift選択系と呼んでいるのは、次回検証する予定の CHOOSEROWS、CHOOSECOLS がとびとびで選べる Ctrl 選択っぽい指定なのに対して、こちらは先頭もしくはお尻から連続した行（列）を選択するShift選択っぽい動作だからです。

もちろん公式な区分ではなく、勝手にそう呼んでるだけです。

TAKE、DROPの詳しい解説はおなじみ オフィスタナカさんを参考に

Excelでのメリット、デメリット、活用

メリットは範囲（配列）から 必要な部分だけを取得する、もしくは不要な部分を削除する といった配列加工が簡単に出来るという点です。

一方デメリットは、先頭・もしくはお尻から何行（何列）でしか指定できない、という点でしょうか。

要は 10行5列のデータがあった時に、

・上から 3行左から 2行を残す
・下から5行右から 3列を残す
・上から1行（先頭行）を捨てる
・右から2列を捨てる

といった端からの処理はできますが、10行5列のデータの 2行目から7行目までを取得、もしくは 2列目から4列目を捨てる（除外）といった、開始行（列）の指定が出来ません。

文字列操作関数で言えば LEFTやRIGHT関数的な処理は出来るけど、MID関数的な処理が出来ないって感じでしょうか。

（余談）範囲・配列の中間部分を取り出す

ただ、これも一工夫すればできます。

TAKEよりもDROPをネストした方が簡単かなと思います。

■7行4列のデータ範囲に対して
4行目から6行目までを取り出したい → 上から3行、下から1行を捨てる
=DROP(DROP(A1:D7,3),-1)

3行目から6行目までの2列目から3列目を取得したい
→ 上から2行、左から1列を捨てて、さらに下から1行、左から1列を捨てる
=DROP(DROP(A1:D7,2,1),-1,-1)

ま、割と簡単ですね。

活用場面としては複雑な配列処理の途中、もしくは仕上げでの利用でしょう。なんとなく先頭行（最左列）の取得（除外）、最終行（最右列）の取得（除外）で使うことが多そう。

GAS（JavaScript）の配列処理でいうところの shift 、popメソッド的な使い勝手と言えるかもしれません。

「いきなり答える備忘録」さんの活用例も、素因数分解やナップザック問題など、今まではプログラミングが必要だった処理を新関数をフル活用してシート関数で突破する複雑な処理の中で、部分的に使っていますね。

TAKE活用例

DROP活用例

DROPは、REDUCEを使った配列プッシュ式で最後に先頭の空行（空列）を削除するのに活用できるんですね。

複雑な処理では必要になるけど、一般ユーザーはあまり使わなそうって感じでしょうか。そもそも配列処理自体が、普通の人はあまりやらなでしょうが・・・。

Googleスプレッドシートの機能、関数との違い

Googleスプレッドシートには残念ながら、TAKE、DROPに該当する関数はありません。

まず配列ではなくセル範囲に対しての処理なら OFFSET関数で TAKEと同じ処理が出来ます。起点が先頭行、左端の列でなくても良いという点を考えると、TAKE以上に柔軟性があるかも。

ただ、残念ながら OFFSETは配列に対しては使用できません。セル範囲に対してのみ有効となります。ぶっちゃけ OFFSETが配列に対して使えたら色々すんなり解決できるケースは多いんですけどね。

配列にも使える関数だと、TAKEの先頭からの処理だけであれば、ARRAY_CONSTRAIN という配列を縮小する関数で代替出来ます。

ARRAY_CONSTRAIN(範囲, 行数, 列数)

ARRAY_CONSTRAIN は EXCELには無い関数ですが、TAKEと違って引数の行数や列数を省略することが出来ません。また、引数をマイナスにすることで最下行（お尻）から取得といった機能もないです。

そもそもGoogleスプレッドシートは、引数でマイナスを指定して逆（最終行・最終列）から処理するといった動きは現状では出来ないです。

うーん、TAKEの方がARRAY_COSTRANIの上位互換って感じですかね。

配列から指定範囲を削除する（落とす）動きをする DROPに近い関数は、Googleスプレッドシートには見あたりません。

Googleスプレッドシートでは無い機能をどう補うか

今回も使うのは FILTER関数です。

FILTER関数、出番多くない？って思いませんか？
そうなんです配列操作では、FILTER関数が悪魔的に強いんです。

きゅんです。

ではマイナスの引数の際の動きも含めて、FILTER関数でどのようにTAKE、DROP関数の動きを実現する式をつくるか？

ここからQAでいってみましょう。

Q. Googleスプレッドシートで、 TAKE と同じ結果を返す式を作れるか？

自力で挑戦してみる方は、以下のサンプルデータを対象としてみてください。7行４列のアルファベット配列です。

ちなみに DROPに関しては TAKEが作れれば、それをアレンジするだけで作れます。

A	B	C	D
E	F	G	H
I	J	K	L
M	N	O	P
Q	R	S	T
U	V	W	X
Y	Z

TAKE自作式　作成する式の条件
　array　・・・　対象とするセル範囲または配列
　r　・・・　上から（マイナス指定時は下から）残す行数
　c　・・・　左から（マイナス指定時は右から）残す行数

マイナスの時の動きを考慮しなければ比較的簡単です。
難しいなと感じたら、まずはマイナスの条件抜きで組み立ててみてください。

どうでしょう？ TAKEの代替式、作れそうでしょうか？

↓↓
ここから回答です。

↓↓

A. Googleスプレッドシートの既存関数で TAKE を再現する

今回もいきなり答えるではなく、順を追って式を作ってみましょう。

配列（セル範囲）を行・列番号で操作する際の基本

配列（セル範囲）を〇行目、もしくは〇列目といった行番号、列番号で操作するには、まずその配列の高さ（行数）と幅（列数）を取得し、行と列に番号を振る必要があります。

※実際に番号を振る（出力する）のではなく、バーチャルな式内での処理の話です。

ROW関数やCOLUMN関数を使いたくなりますが、シート上の行番号、列番号を使おうとすると開始位置（範囲の左上の起点）を考慮する必要がありますし、そもそもセル範囲ではない配列には使えません。

使うべきは ROWS関数、COLUMNS関数 です。

この2つのは範囲だけでなく配列にも使えるので、配列操作の際には必須となる関数です。

これで取得できる行数、列数を SEQUENCE関数を組みわせることで、行インデックスと列インデックスを対象の配列（範囲）に付与することができます。

array ・・・　対象のセル範囲（または配列）

配列の高さ = 行の長さ（行数）　ROWS(array)
↓
行に番号を振る SEQUENCE(ROWS(array))

配列の幅 = 列の長さ（列数）　COLUMNS(array)
↓
列に番号を振る SEQUENCE(1,COLUMNS(array))　

FILTER関数で、行方向、列方向の両方を絞り込む

FILTER関数を使う際、行方向（縦）の条件による抽出だけではなく、列方向（横）でも絞り込みたい場合はどうすればよいでしょうか？

FILTERは行・列どちらか一方向での絞り込みしか出来ないので、両方で絞り込む場合は FILTERを入れ子にする必要があります。

要は FILTERで行を絞り込んだ結果を、さらに列で絞り込むって感じです。

マイナスの時の動きを考慮しなければ、そんなに難しい式ではありません。

たとえば A1:D7 の範囲に対して、Excelの

=TAKE(A1:D7,4,2)

と同じ結果を返す式を Googleスプレッドシートで作る場合は、

=FILTER(A1:D7,SEQUENCE(ROWS(A1:D7))<=4)

このように、まずは行番号が 4以下という条件で行を絞り込んだものを

=FILTER(FILTER(A1:D7,SEQUENCE(ROWS(A1:D7))<=4),SEQUENCE(1,COLUMNS(A1:D7))<=2)

さらに列方向に 列番号が 2以下という条件で絞り込む。
という式になります。

これを LAMBDAって（ラムって） TAKEの引数に該当する部分を外に出すと

=LAMBDA(array,r,c,FILTER(FILTER(array,SEQUENCE(ROWS(array))<=r),SEQUENCE(1,COLUMNS(array))<=c))(A1:D7,4,2)

このようになります。

省略時、マイナス指定時のロジックを整理する

それでは省略時（0の時）、マイナスの時の動きはどう処理すれば良いか？を整理しましょう。

ちなみに r（行）を省略した場合は演算子における rは０という扱いになりますが、実際の TAKE,DROPだと行数または列数で 0を指定した場合はエラーになります。この点は注意。

列に関しては行と同じ挙動なんで、あとで付け足すとして、まずは行だけに注目した簡略化した式

=LAMBDA(array,r,FILTER(array,SEQUENCE(ROWS(array))<=r))(A1:D7,0)

※最後の 0の箇所が行指定

↑ これをベースに検証しましょう。

↓それぞれのパターンはこちら。

r>0 の時
=LAMBDA(array,r,FILTER(array,SEQUENCE(ROWS(array))<=r))(A1:D7,4)

r=0 の時（省略時も含む）
=LAMBDA(array,r,FILTER(array,SEQUENCE(ROWS(array))>r))(A1:D7,0)
※以下でもＯＫ
=LAMBDA(array,r,FILTER(array,SEQUENCE(ROWS(array))))(A1:D7,0)

r<0 の時（マイナスの時）
=LAMBDA(array,r,FILTER(array,SEQUENCE(ROWS(array))>ROWS(array)
+r))(A1:D7,-2)

r=0（省略時含む）はわかりますよね？
全て返す（除外なし）とすればよいだけです。

マイナスの時は、例えば rが -2 で配列の行数が７だった場合は、行番号の最後から2つ（行番号 6,7）を返せばよいわけですから、

行番号 > 配列の行( ７ ) ＋ r( - 2 )

とすれば良いですね。

今回も単体条件に対して配列を返す式なので、残念ながら IFSだとエラーになります。

そうすると思いつくのが IFの入れ子ですが、

=LAMBDA(array,r,FILTER(array,IF(r=0,SEQUENCE(ROWS(array)),IF(r>0,SEQUENCE(ROWS(array))<=r,SEQUENCE(ROWS(array))>ROWS(array)+r))))
(A1:D7,4)

うーん、イマイチ。もうちょい数学的な式にして短くしましょう。

条件部分を数学的（算数的）に考える

r>0 の時
SEQUENCE(ROWS(array)) -r <= 0

r=0 の時（省略時も含む）
SEQUENCE(ROWS(array)) -r > 0

r<0 の時（マイナスの時）
SEQUENCE(ROWS(array)) -r - ROWS(array) > 0

↑　FILTERの条件部分だけ取り出し、左辺に変数を寄せたものです。
数学というより小学校の算数でやる範囲ですよね？確か。

下の2つの条件式を r> 0 のケースの式に近づけていきます。

r>0 の時
(SEQUENCE(ROWS(array)) -r) *r <= 0

r=0 の時（省略時も含む）
(SEQUENCE(ROWS(array)) -r) * r <= 0

r<0 の時（マイナスの時）
(SEQUENCE(ROWS(array)) -r - (ROWS(array) +1) ) *r <= 0

ここが少し難しいかもしれませんが、 0以下かどうかを判別する式なので、
r>0であれば、 SEQUENCE(ROWS(array)) -r の結果に r を乗算しても「0以下か？」という結果に影響はありません。

r=0 の時は 0をかけていることになるので全ての要素が 0
つまり 全て <= 0を満たす となります。

r<0 の時は少し複雑です。ただ、考え方としては >=0 を満たしていた数値の配列は、マイナスをかけて反転させれば <=0 を満たすのを利用しています。

また、登場する数値は全て整数なので、行数に +1 することで <0 の部分を <=0 で成立するように調整しています。

この r<0 の条件の時だけ - (ROWS(array) +1) がつくので、この部分だけ条件式を使っておきましょう。

- (ROWS(array) +1) * (r<0)

こうすることで、 r<0を満たすときは TRUEとなり *1扱い、それ以外は FALSEとなって *0 で 0となるので、計算に影響をあたえません。

これで FILTERの条件部分を rの正負による IF分岐なしで、一つの式にまとめることが出来ました。

(SEQUENCE(ROWS(array)) -r - (ROWS(array) +1) *( r<0 ) ) *r <= 0

この辺りの算数的な話は説明が難しい・・・。

あと、式は短いですが演算子をフル活用すると、他の人から見るとなんだかよくわからない、引き継いだ人がメンテナンスできないというデメリットもあります。

ただ、今回のような汎用的な処理をするパーツ的な自作式であれば、その後のメンテナンスは考慮しなくても良いかと思います。

この条件を行を抽出するベースの式に当てはめると以下のようになり、

=LAMBDA(array,r,FILTER(array,(SEQUENCE(ROWS(array))-r-(ROWS(array)+1)*(r<0))*r<=0))(A1:D7,3)

さらに列方向にも同じ FILTER処理を重ねて

=LAMBDA(array,r,c,FILTER(FILTER(array,(SEQUENCE(ROWS(array))-r-(ROWS(array)+1)*(r<0))*r<=0),(SEQUENCE(1,COLUMNS(array))-c-(COLUMNS(array)+1)*(c<0))*c<=0))(A1:D7,3,2)

こんな感じの式になりました。TAKE代替式完成です。

これでも、色々ためした中では一番短い式だったりしますｗ

TAKE 代替式【完成版】

完成版の TAKE代替式 の動きをテストしてみましょう。

=LAMBDA(array,r,c,FILTER(FILTER(array,(SEQUENCE(ROWS(array))-r-(ROWS(array)+1)*(r<0))*r<=0),(SEQUENCE(1,COLUMNS(array))-c-(COLUMNS(array)+1)*(c<0))*c<=0))(A1:D7,3,2)

省略時は全行（全列）を返す扱いになり、マイナス時は下（右端）から数えた行、列までを残すという TAKEと同じ動きになっていることが確認できました。

DROP 代替式【完成版】

DROPの方は、TAKEの式をアレンジしたものです。
すいませんが、作成過程は省略で。（そんな面白くもないので）

=LAMBDA(array,r,c,FILTER(FILTER(array,(SEQUENCE(ROWS(array))-r-1-(ROWS(array)-1)*(r<0))*r>=0), (SEQUENCE(1,COLUMNS(array))-c-1-(COLUMNS(array)-1)*(c<0))*c>=0))(A1:D7,3,2)

こちらは指定した行（または列）までを DROP（落とす）動きなのがわかりますね。マイナス時、省略時の動きも求めている結果となりました。

TAKE / DROP Googleスプレッドシート代替式まとめ

まとめです。

TAKE の代替式

=LAMBDA(array,r,c,FILTER(FILTER(array,(SEQUENCE(ROWS(array))-r-(ROWS(array)+1)*(r<0))*r<=0),(SEQUENCE(1,COLUMNS(array))-c-(COLUMNS(array)+1)*(c<0))*c<=0))( 範囲, 行, 列)

DROP の代替式

=LAMBDA(array,r,c,FILTER(FILTER(array,(SEQUENCE(ROWS(array))-r-1-(ROWS(array)-1)*(r<0))*r>=0), (SEQUENCE(1,COLUMNS(array))-c-1-(COLUMNS(array)-1)*(c<0))*c>=0))( 範囲, 行, 列)

マイナス時を考慮した全パターンを網羅すると上記のように複雑な式になってしまいますが、実際の場面ではケースに応じて式を変えた方が簡単ですね。

FILTER を行番号、列番号を条件にすることで配列の縦方向、横方向どちらも操作できる、これだけ理解しておけば十分です。

先頭行のみ最終行のみの実践的なケースの式

利用シーンとして多い 先頭行のみ、最終行のみを残す（捨てる） という配列操作に絞った場合は FILTER以外の関数を使った方がシンプルです。

取り出すのが 1行だけなら INDEXです。

INDEXは OFFSETと違って セル範囲、配列どちらにも使えます。

■先頭行のみを取り出す
=LAMBDA(array,INDEX(array,1,))(A2:D8)

■最終行のみを取り出す
=LAMBDA(array,INDEX(array,ROWS(array),))(A2:D8)

先頭行だけ捨てる、最終行だけ捨てるの場合は、残す方が複数行となるので、ここは FILTERを使う必要があります。

最終行だけ捨てるは、先頭から行数 -1 残すと同じなので、序盤に少し触れた ARRAY_COSTRANI を使ってもいいですが、列の方も指定が必要なので記述は長くなります。

■先頭行のみを捨てる=LAMBDA(array,FILTER(array,SEQUENCE(ROWS(array))>1))(A2:D8)

■最終行のみを捨てる
=LAMBDA(array,FILTER(array,SEQUENCE(ROWS(array))<ROWS(array)
))(A2:D8)

または

=LAMBDA(array,ARRAY_CONSTRAIN(array,ROWS(array)-1,COLUMNS(array)))(A2:D8)

列方向は上記のアレンジなんで割愛します。

範囲に対してならOFFSETを使う方法もありますし、要は色々知っておいた上で状況に応じて関数を使い分けるのが一番ってことですかね。

今回の検証は以上となります。

来週は年末年始期間の為、シリーズ記事の更新はお休み させていただきます。

鎌倉殿は終わりましたが、年明け 2023年も EXCEL殿の14の新関数シリーズは まだまだ続きます！

「Googleスプレッドシートから見た！」Excel 14の新関数 -4 TAKE / DROP