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の引数、動きをまとめると以下になります。

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

■TAKE関数

■DROP関数

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つは結局同じ結果を返します。つまり、

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ま、割と簡単ですね。

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

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

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

TAKE活用例

DROP活用例

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

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

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

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

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

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

配列にも使える関数だと、TAKEの先頭からの処理だけであれば、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の代替式、作れそうでしょうか？

↓↓
ここから回答です。

↓↓

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

 =TAKE(A1:D7,4,2)

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

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

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

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

このようになります。

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

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

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

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

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

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

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

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

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

とすれば良いですね。

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

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

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

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

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

下の2つの条件式を  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) がつくので、この部分だけ条件式を使っておきましょう。

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

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

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

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

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

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

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

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

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

TAKE 代替式 【完成版】

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

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

DROP 代替式 【完成版】

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

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

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

まとめです。

TAKE の代替式

DROP の代替式

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

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

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

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

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

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

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

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

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

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

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

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

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

■このシリーズの次の記事