200万を 超える、一次元配列連番の 生成。(修正版)

此に　先駆け、
http://oshiete.goo.ne.jp/qa/11060012.html
にて

質問を　立てさせて、
頂いたのですが、

不備が　認められましたので、
一旦　閉め、
修正し、

再質問を　させて、
頂きます。


さて、
お世話になります。


「,」や、「;」で、
区切られた、

定数配列や、配列数式上の、
一次元　等差連番数値を、
200万個以上、

VBAを　使わず、
生成したいのですが、

何か　良い方法は、
ありますでしょうか？


因みにですが、
例えば、

=row(a1:a10000000)*column(a1:e1)
等と　すれば、
連番生成　其れ自体は、
出来るのですが。


此を　一次元配列に、
並べ直す　方法が、
難しくて、
手を　焼いてます。


ほんの　一例として、

=SMALL(INDEX(row(a1:a10000000)*column(a1:e1),,),①)
等と　書こうとしても、

此の　①の部位が、
書けません。


書こうとすると、
1〜500万の　等差連続数が、
必要になり、

本末転倒、
入れ子の　無限繰り返し、
等に　陥りそうです。


如何したものでしょう？


宜しく　お願いします。


追記、
TRANSPOSE構文は、
思うようには　働かないように、
感じています。

質問者からの補足コメント

• 此の様に、
  
  7万を　超えた、
  添え字も　可能ですし、
  
  連判最大値が　500万を、
  超えさせる事も、
  
  一次元配列でさえ　なければ、
  容易い事ですし、
  
  
  抑も、
  2003ですら、
  セル値　有効桁数は、
  15けたですし、
  
  もう　十年以上、
  経つのでしょうか？
  
  2007ですら、
  最下行は　1,048,576行です。
  
  
  お心添えならば、
  同然　有難いのですが、
  
  自室無根の　誹謗なだけなら、
  お控え　頂いても、
  構いませんよ？
  
  迷惑ですから。

  
    補足日時：2019/04/05 01:17

• あぁ、失敬、
  私にも　間違いが、
  在りましたね、
  済みません。
  
  
  正しくは、
  =ROW(A1:A1000000)+(COLUMN(A1:E1)-1)*1000000
  
  でしたね。
  
  
  ご免なさいです。

  
    補足日時：2019/04/05 01:28

• あっと！
  又失礼、
  
  訂正します。
  
  
  現行、
  
  何度か　此方こそ
  
  改訂後、
  
  何だか　此方こそ、
  　　　　　　　以上、
  
  済みません。

    補足日時：2019/04/05 07:56

No.6 回答者： fujillin 回答日時： 2019/04/07 17:32

No.5です。

確認した訳ではありませんが、エクセルの関数の場合、一般的な計算ロジックをそのまま適応しているわけではなく、表のサイズに合わせてアレンジしてあるようですので、計算そのものにもサイズの制約が生じているものと推測します。

でも、幸いながら面的な使い方をすれば要素数は足りるので、これを利用したいというのがご質問の主旨と捉えました。
（人間の）概念には制約はありませんが、ツール（エクセル）には制約が存在するというところでしょうか。

質問者様の文章を読んでいると、「ツールに制約を越えさせる」ことをお望みのように思えますが、現状の条件の中でやりたいことを実現するには、人間が制約を越えれば良いだけで、ツールに越えることを強いても・・・と感じます。
いつかは大きなサイズのバージョンがでるかも知れませんけれど。
あくまでも、「ツールに制約を越えさせる」ことが目的でしたら、以下の文章はスルーしてください。

平たく言えば、「（ツール側の）多次元配列を（人間が）単次元として扱えばよい」ということではないでしょうか。
この逆の発想は、ままあることと思いますが、わざわざ不便（？）にするような発想なのであまり無いものかもしれません。
世の中は便利になって、（人間が）手間をかけなくともよくなっていますが、端的に言えば、マシンの内部計算では単次元（アドレス）の配列を利用して多次元計算をこなしていると言えると思います。

通常の1次元配列なら、array(n) でn番目の要素を取得できますが、今回の（面的な）配列をarray2とするなら、
　array2(n)=f(n)
として、
　f(n)=INDEX(array2, INT(n/c)+1, MOD(n-1, c)+1)
のように考えれば、単次元として利用できるものと思います。

一方で、この要素全部を一列に表示しようとしても、現状のエクセルでは行数が足らず、添字のnも行数等からの自動生成はできないのはご承知の通りです。
一列で足りなければ、やむを得ず二列、三列…を利用しようとするのは普通の考えだと思いますが、これは、概念の制約ではなく、あくまでもツールの制約と言えるでしょう。
その際に使用する列数は、要素を包含できる値であれば自由に設定できますし、これによって、セル数も足りるし、添字nも生成可能な状態になります。
とは言うものの、もともとのarray2の列数(c)も便宜的に設定したものにすぎませんので、無駄な計算を省く意味からも、表示のための列数(c')と最初から一致させておくのはもっともな措置であろうと考えます。

仮に「抽出」のような形で要素数を減ずることのできる処理を行うような場合であれば、結果を一列に出力することも可能になるでしょう。
抽出処理をg()とするなら、抽出後の各要素は
　g(f())(m)
のような形でリストアップが可能と思われます。
都合によって複数列に表示したいというような場合は、添字生成の計算をh()とすれば、
　g(f())(h(r, c))
のような式を作成することで可能になると考えられます。

※ 前回は、関数を扱う上でのテクニカルな視点から書きましたが、今回は、使用者側の概念的な側面から記してみました。
※ エクセル環境が手元になく、スマホからなので式等に正確性を欠いているかもしれませんが、意図は伝わると思いますので。

この回答へのお礼

お礼が　遅れ、
済みません。


〉ツールに制約を超させる

とも　言えるかも、
知れませんが、

其れ以外の　解決策も、
存在している　可能性を、
感じています。


先に　挙げた、
5列を　費やしてのものも、
そうですし、

又　別として、
=TEXTJOIN(";",1,COLUMN(A1:AAL1)+(ROW(A1:A100000)-1)*1000)
こう言った記載が　可能なようなので、

此を　定数配列に、
分断する、

そういった　方向性も、

もしかしたら、
あるかも　知れません。


Excelの　式を、
書かれていたり、
VBAに　携われている、
で　しょうから、

既に　お解りとは、
思いますが、

ゴールへの道は、
一筋では　無いので。


要するには、

其の内の　一筋が、
塞がれた、

だからと　言って、
たじろぎも、後ずさりも、
不要のように　思う、

此なのです。


ただ、
幾ら　考えても、
触発無しには、
現時点の　私では、

違う　ルートが、
見付けられそうも　ないので、

ご相談させて　頂いた、
次第なのです。


さて、
別例として、

通常では、
二次元配列の　配列数式を、
行毎、列毎、
結果を　出させる、
等とは、

なかなか　難しいのかも、
知れません、

とも　すれば、
諦め、
他の　ルートを、
探すでしょう。


しかし、
「Excel関数機能を　組み合わせる、」

そうした　ありもしない、
枠組みを　飛び越え、

数学的な　考察を、
持ち込み、
行列積を　行えば、

意図も　容易く、
行毎、列毎、

其れ所か、
場合によって　もしかしたら、

3次元配列の　スライス面毎の、
加工の後、

更に、
其の面の　配列をも、
続けて、

行毎、列毎に、
加工する事　までもが、

別ルート模索の　結果、
叶うやも　知れません。


此の様に　要するには、
「行数が　足りない、」
とは、

私に取っては　其れだけでは、

「全ルート　塞がれた、」
との　検証結果ら
たり得ないのです。


今までの、
最も　難易度の、
低いもので　あるだけ、
其れが　塞がれただけ、

と　お捉え頂き、

別手法を　探したり、
裏技を　探したり、
と、

道を　探して、
頂きたいのです。


可能でしょうか？

お礼日時：2019/04/08 10:25

No.5 回答者： fujillin 回答日時： 2019/04/05 10:37

こんにちは

＞TRANSPOSE構文は
＞思うようには　働かないように、
＞感じています。
MMULTもそうですが配列用の関数の割には、配列数式として定義しないと機能しない場合が多いように感じています。
想像するところ、元々は一つのセル内で配列計算をすることが目的ではなく、セル範囲内を配列と見立てて計算する時に使用するイメージで作成されているためではないかなと漠然と思います。

ご提示の例では、まず、MAX関数が引数の評価を期待したゆおには行ってくれていないということがあります。
　=MAX(ROW(A1:A10))　→　1
この点は、INDEX関数で明示的に配列を返すようにすることで対応できてはいますが、TRANSPOSEの場合はそれでも反応してくれないということですね。
ちなみに、ご提示の式をそのまま配列数式として設定すれば、全部の式が期待通り10を返してくれるようになります。


エクセルでの計算は、シートの範囲という制約がありそうなので、単純な1次元では大きな添え字の配列は扱えないようですね。
＞=row(a1:a10000000)*column(a1:e1)
最初に、上式を拝見した時は連番にならないと思ったのですが、訂正式の
＞=ROW(A1:A1000000)+(COLUMN(A1:E1)-1)*1000000
という方式なら理解できます。（私ならRowをn倍する発想になりますが…）

ただし、そこから1次元配列化なさろうと発想していらっしゃるようですが、そうすると結局のところ、添え字が限界を超えてしまうというジレンマから脱出できないように思います。
エクセル内で扱う限りは、添え字を2次元として押し通すしかないのではないでしょうか？
仮に、セルに配列の内訳を表示するにしたとしても、1列に表示しきれないのでn×mのセル範囲に表示させるしかないということになるでしょうから。

例えていうなら、（小型化して）
　=INDEX(INDEX(ROW($A$1:$J$10)*10 + COLUMN($A$1:$J$10)-10,,),ROW(A1),COLUMN(A1))
をフィルコピーすれば、（当たり前ですが）1～100の連番がセル範囲に展開されます。
（外側のINDEXを外した、内部のINDEXが連番配列のイメージ）

どのような計算に使用するおつもりかは不明ですが、この連番以外の値も同様に2次元化することで、加減算に関してはそのまま計算可能であったと思いますし、スカラー値であれば乗除算も可能だったように記憶しています。
配列どうしで、個別要素の単純な乗除算を行いたい場合には、何か工夫が必要になりそうです。
（できるのかどうか不明ですが…）

しばらくは時間が取れないので、お役には立てませんけれど、この辺りで失礼いたします。
（難しいご質問なので、時間が取れないとこれ以上は無理なので…）

この回答へのお礼

有難うございます。


〉想像するところ、

済みません、
配列数式を　扱ってきた、
経験から、

仰るに　思考か、
追いつきません。


〉TRANSPOSEの場合は…

其の手前迄の　時点で、
データが　期待通りに、
受け渡されて　いる事が、
確認できた　訳ですが、

其れでも　尚、
機能が　正しく、
果たされて　いない事が、

此等よりは、
見て　取れるように、
思います。


〉シートの範囲という制約が…

制約が　あるから、
と、
たじろぐものと、
克服するものが、
居る、

かと　思います。


確かに、
たじろぐのは　自由です。


が、
今回、
此の質問に　限定しては、
たじろぐ方は　無用です。


〉そこから1次元配列化なさろうと

いえいえ、

あれは、
但の　一例で、

又、
連番生成　其れ自体は、
可能で　ある事の、
立証で　あるだけで、

固執は　しておりませんよ。


自由な　発想を、
求めています。


〉次元として押し通すしかないのでは…

いえ、
其れでは、
此の質問の　回答、
たり得ませんよ、

たじろぎ、立ち止まるのは、
自由です。


ですが、
此の質問では　無用です。


〉どのような計算に使用する…

どの様な　計算も、
何も、

一次元連番生成は、
配列数式の、
基本中の　基本で、
根幹を　なすものです。


どの様な　式であろうとも、
式の　様式次第では
使うものだ、
と　考えます。

お礼日時：2019/04/05 23:00

No.4 回答者： WindFaller 回答日時： 2019/04/05 07:19

No.3で、間違いがありましたので、訂正させていただきます。

配列関数で限度があるのは、VBA側で、明らかにTRANSPOSE と INDEXは、34,464のようです。ワークシート側では直接関係がありません。興味あって、私は時間を割いてワークシートで試しながら発言したけれども、お気を悪くされたようで、申し訳ありません。ご質問で何に必要なのか、どう求められているのかは、私には分かりません。これ以上は静観させていただきます。失礼しました。

この回答へのお礼

あぁ、いえ、
何度か　此方こそ、
済みません。

お礼日時：2019/04/05 07:54

No.3 回答者： WindFaller 回答日時： 2019/04/05 00:05

私には、前回のご質問で、No.1の fujillin さんのご回答で、十分に情報は提供されているように感じています。

あえて、こちらが話を混ぜ返す必要はないのですが、私なりのコメントを加えました。

ワークシート上で巨大な配列?を求めても、展開しなければ利用しようがないように思うのです。また、Excelの配列数式は、いわば、省スペースだけの価値しかありませんから、VBAのようなプログラミングで生成される仕組みとは違います。つまり、1次元・2次元というのは、VBAなどに存在するもので、ワークシート上では、1次元というよりも、1列に展開することだと思います。そうしないと、通常は並べ替えが利きません。

1〜500万の　等差連続数（?等差数列)が、なぜ必要なのか分かりませんが、TRANSPOSEやINDEX関数など、配列を利用する関数は、コマ数(I添字)は、65536個までだったと思います。

あえて生成するなら、分割しかないのではないかと思いますし、本来、配列自体を展開してしまう、つまり、ワークシート上にセルひとつずつに該当する数字を入れていくのが一番早いのではないかと考えます。実務では、あえて配列を使わずにそうした展開をする例も多くあります。

要するに、数式の配列関数というのは、仮想メモリ上に、入れ物を作り、そこに仮の文字列を入れていくもので、65536個は、その割当てられたメモリの限界という認識をしています。VBAは、自分で大きさや次元を決められますが、表の上では、決められたものしかできません。

No.1さんもおっしゃっている
「Excelでそんなものを作って何をしようとしているのか、それに対する言及があれば……」
これには同感です。

=row(a1:a10000000)*column(a1:e1)
そして、これはできないはずです。A10000000(1千万)は存在しませんから。
=ROW(A1:A1000000)*COLUMN(A1:E1)　'範囲の配列式にする
は、可能ですが、これは、マトリックスで縦と横に使っているからで、500万セルには達成しています。しかし、以下の通り、等差数列にはなっておりません。

画像の画面は、500万個の数字ができていますが、この規模になると、500万個の数値を並べ替えることは、VBAでないと不可能ではないかと思いました。画面では、ごく限られた範囲内(A1:E100)での小さな数字からの並べ替えです。

この回答へのお礼

全て　貴方の、
誤解です。


〉fujillin さんのご回答で、十分に…

先の質問では、
私に　過失が
在り、

結果的に、
容易く　熟せる
内容の、
質問と　なってまして、

fujillin様においては、
何ら落ち度は、
無い訳ですが、


だからと　言って、
此の質問が　あの回答で、
終わっている訳では、
毛頭　ありません、

抑もの　論点からして、
違います。


あの質問は、
最大行数内での　連番生成、

此の質問は、
最大行数外での　連番生成、

まるで　意味が、
難易度が、
違うのですよ。


ご理解　頂けないで、
しょうかね？


〉展開しなければ利用しようが…

いいえ、


〉Excelの配列数式は、省スペース…

いいえ、

知らないものしか、
口に　出来ない、
言葉です。


些かながら、
気分を　害します。


判らないのに、
誤った　イメージを、
振り翳し、
大上段から　振り翳すのは、
お止めください。


〉そうしないと、通常は並べ替えが

いいえ、

低スキルだからこそ　そう感じるだけだ、
と　思います。


〉65536個までだったと思います。

いいえ。

2011は　非64bitの、
限定空間内で、
2019は　64bItネイティブで、
稼働します。


非ネイティブでは、
メモリーも　4GBすら、
使えませんし、
CPUも、
1〜2個程しか　扱いません、

が　ネイティブでは、
搭載、
全メモリー、全CPU、全リソース、
其れ等　全て、
あてがえます。


此の事を　示唆せず、
話されては、

全てが　間違いに、
なりますよ？


抑も、
では、

もし、
貴方の　申される通りなら、

現行　エクセルの、
最下行は、
6万有余所か、

百万を　優に、
超えている訳ですから、

=ROW(A:A)
と　するだけで、
エラーに　成るはずです。


試しに、
=MAX(INDEX(ROW(A:A),,))
は、
64bitでなく、16bIt稼働下ですら、

6万有余ではなく、
百万を　超える値が、

実際に　返されます。


此の点を　取っても、

貴方の　申し立ては、
間違いで　ある事が、
避けられませんよね？


ものを　述べられる際に、
確認を　怠ると、

質問者に　迷惑を、
かけますよ？


自戒くださいね。

お礼日時：2019/04/05 00:49

No.2 回答者： naoiz 回答日時： 2019/04/05 00:03

#1さんが言ってるのはマシンのスペックの話ではなく、Excelのソフトウェアとしての制限の話です。

https://support.office.com/ja-jp/article/excel-% …

この回答へのお礼

此の質問の　骨子は、

エクセル最下行限界を　超える、
連番生成を、
如何に　実現するか、
です。


言い変えれば、
ソフト制限の　最初の薄膜を、
超えた　使い方を、

どうやって　実現するか、
です。


ので、
ご指定を　もたらそうという、
ご配慮や、お言葉、
自体は　有難いのですが。


其の内容　自体には、
適正さか　伺えず、
残念に　感じます。

お礼日時：2019/04/05 00:55

No.1 回答者： fakeflake 回答日時： 2019/04/04 20:12

Excelで扱える行数の上限が104万8576行までですから、200万ってのがそもそも無理です。

仮に100万に制限したとしても、関数にすると1セルいじるたびに再計算が発生し、それは処理が重くて地獄になりそうな気がします。とっても扱いづらいものになる予感しかしません。Excelでそんなものを作って何をしようとしているのか、それに対する言及があれば誰かが知恵を出してくれるかも知れませんが。

この回答へのお礼

ああ、
其の辺りは、
此方で　対応しますので、
大丈夫ですよ、
ご安心くださいね。


最近、
2011から、2019に、
乗り換えたましてね。


リソース不足も　出なくなりましたし、
全ての　CPUも、
使ってくれますし、

今までの　あれは、
何だったんだ！！

て　感じで。


実際、
2011では、
抑も　計算すら、
出来なかった　Bookが、
数分かな？
計算が　終わりますよ。


多く、
型の　落ちた、
ノートで、
此だけの　パフォーマンスですから、

ワークステーション　再導入後は、
データが　幾らあっても、
気に　ならない、
と　思ってますよ。

お礼日時：2019/04/04 22:40