2020年のExcelの新機能（XLOOKUP、FILTER、スピル等）について

はけた＠できるExcel2021 @excelspeedup

@KotorinChunChun ありがとうございます。 Insiderでなくても、使えるPCもありそうなんですね。 それにしても、スピルを追い越して、xlookup関数が先に来るのは驚きです。 スピルとvlookupの相性が悪いから、相性のいい関数を事前配布したのだと勝手に邪推していますが。まったく根拠はありません。

ちゅん🐤 @KotorinChunChun

@excelspeedup これまでの経験上、Microsoftが誤って一般ユーザーにXLOOKUPを配信しちゃったぜ☆って可能性もありますよ。 慌てて止めたのだけどすでに反映されてしまった人に巻き戻しが出来ていないとか。 スピル、話題になってから1年くらいになるのに配布されませんねー。

ちゅん🐤 @KotorinChunChun

おうちのメインPCはこうなってた。 そして、XLOOKUP使えなくなってる。 一昨日、使えるようになってるー！って感動した覚えがあるんだけど夢だったのかな。 pic.twitter.com/SYkeeUoNtO

拡大

ちゅん🐤 @KotorinChunChun

@excelspeedup 今ためしたら、使えてた気がするPCでもXLOOKUP使えませんでした。 twitter.com/KotorinChunChu…

はけた＠できるExcel2021 @excelspeedup

これは、どうなんでしょうね。 本当に軽くなるのか要検証な気がします。 もちろん、計算内容によるんですけれど。 感覚的には、INDEX+MATCHでスピルよりは、スピルを使わずINDEX＋MATCHの数式の入れ方を工夫したほうが圧倒的に早くなりそうな気がします。 twitter.com/Excel_labotomi…

Excelラボとみ君 @Excel_labotomi

あと、今まで「Excelって関数遅いだろ」とか言われてた状況が変わったってことボラ。 100万行×500列とかの実務的な取引データがあるとき INDEX・MATCHだと5億セルに数式が入ってたわけ。 （たぶん地獄の重さ） スピルを使えば、1セルにしか数式を書かなくて良いわけ。軽 だからパラダイムシフト

2019-12-05 19:06:26

石宮鏡太郎@かがみ @isimiya9

@excelspeedup まずExcelで100万行使うくらいならAccess使えよってところからですね。

はけた＠できるExcel2021 @excelspeedup

@isimiya9 ド正論を😅 とはいえ、個人的には、エクセルの限界にチャレンジしたいですね。最近、結構速くなっているみたいですし。

はけた＠できるExcel2021 @excelspeedup

・INDEX＋MATCHでスピル ・INDEX+MATCHのまったく同じ数式で非スピル だと、どっちが速いんでしょうね？ 実は、あまり変わらない予感もしますが。実験してみないとわからないですね。

はけた＠できるExcel2021 @excelspeedup

とりあえず、50000行×78列で、もう反応が返ってこない😅 百万行なんて夢のまた夢ですね。

はけた＠できるExcel2021 @excelspeedup

10000行×104列だと再計算は一瞬だけれど、数式改変時に固まって反応が返ってこなくなる。 こんなに、動作が遅かったですかね？

はけた＠できるExcel2021 @excelspeedup

10000行×104列のINDEX関数で比較したら、スピルのほうが圧倒的に速いですね。 スピルのほうは、VBAでいうRangeに一括で書き込みをするイメージ、通常のほうは、1セルずつ書き込むようなイメージで動作してるんですかね？

はけた＠できるExcel2021 @excelspeedup

ただ、データ量が増えると、スピルは、メモリオーバーで動かないこともあるようです。 とはいえ、動作速度重視なら、基本的にはスピルを使ったほうがよさそうな雰囲気ですね。

はけた＠できるExcel2021 @excelspeedup

アルゴリズムを工夫せずに、何も考えずにindex、matchをスピルで入れた場合も超高速。 これは、すごいかも。

はけた＠できるExcel2021 @excelspeedup

でも、通常通りVLOOKUP関数を入れるのも、まあまあ我慢できる範囲ですね😅（4、5秒待つ感じ） ということは、あのINDEX関数の遅さは、かなり異常な状態な気がします。

はけた＠できるExcel2021 @excelspeedup

今のところの実験結果は、 INDEX+MATCH（スピル）＞＞VLOOKUP(一部スピル）＞＞＞VLOOKUP（通常）＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞＞INDEX+MATCH（通常） という感じ。 ただ、INDEX+MATCH（通常）で、足を引っ張っているのはINDEX関数。これは、明らかにおかしいですね。

はけた＠できるExcel2021 @excelspeedup

通常のindex、matchもやり方を変えれば速くなるみたいですね。 INDEX+MATCH（スピル）＞＞VLOOKUP(一部スピル）＞＞INDEX+MATCH（通常）＞VLOOKUP（通常） くらいにはなりそう。 私の直感と動作速度が随分違いましたが、実験してみないとわからないものですね。

はけた＠できるExcel2021 @excelspeedup

参考のために。 断トツで一番遅かったINDEX+MATCH（非スピル）。 B列：MATCHで行番号を抽出 C列～CZ列：INDEXで該当行 なぜ、このパターンが断トツで遅いのか、理由がよくわかりません。 pic.twitter.com/n0B6fKgFYF

拡大

はけた＠できるExcel2021 @excelspeedup

よく見かける通常のINDEX+MATCH（非スピル） B～CZ列：INDEX＋MATCHで該当行を抽出 これだと、かなり速いです。 理論的には、さっきのほうが速いような気もするんですが。 実際に試すと、この数式のほうが圧倒的に速いです。 pic.twitter.com/Sb4qidyve2

拡大

はけた＠できるExcel2021 @excelspeedup

今度は、列単位にスピルをしています。 --- B2セルの数式で、B3セル以下にスピル C2セルの数式で、C3セル以下にスピル ～ CZ2セルの数式で、CZ3セル以下にスピル --- 1つ前のツイートのものと比べて、速度は、それほど変わらないか、若干速いかな？という感じ。 pic.twitter.com/AaOUIZ82TZ

拡大

はけた＠できるExcel2021 @excelspeedup

今回、試した中で一番速かったパターン。 B2セルの数式が全範囲にスピルされています。 1行目に列番号を入れて、B2セルの数式からそれを参照する形にしています。 1行目に列番号を入れないといけないのが難点ですが、明らかに速いです。 pic.twitter.com/OKNrLDqMNc

拡大

ちゅん🐤 @KotorinChunChun

知らないことがいっぱいだ。 Excelのスピル，動的配列数式 そのバージョン間の互換性 - kamocyc’s blog kamocyc.hatenablog.com/entry/2019/12/…

VBA100本ノックとエクセル問題 @yamaoka_ss

@KotorinChunChun おーここは詳しく書かれていますね、紹介ありがとうございます。 「暗黙的なインターセクション演算子」これは知らなかった！