[[20160523083402]] 『空白セルを除いて連番を任意な数字からスタートさせた』(60<) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『空白セルを除いて連番を任意な数字からスタートさせたい』(60<)

教えてください。
30万行を超えるデータがあります。

=IF(A1<>"",COUNT($A$1:A1),"")で最終行まで実行するとエクセルが落ちます。

データを分割(5万〜10万行)して連番を任意な数字からスタートさせたいのですが書式をアドバイスくださいませ。

   A B             
1  ○  1
2
3  ○  2
・
・
・
5万  ○ 4500

     A B  
120000 ○  6000←ここの数値を任意にしたいのです。
120001  ・
・    ・  
121000  ○  7500
・    ・
・    ・
150000 ○  10500   

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 >=IF(A1<>"",COUNT($A$1:A1),"")で最終行まで実行するとエクセルが落ちます。

 (1)B1セルに任意の連番用の初番を入力(下のサンプルでは 900 )

 (2)B2セル =IF(A2="","",LOOKUP(9999999999,B$1:B1)+1)
     下にコピー

     これでもエクセルが落ちますか?(当方ではテストしておりませんけど)

 <結果図>
  行  _A_  _B_  ______C______
   1   50  900  ←任意の数値 
   2                         
   3   20  901               
   4                         
   5   40  902               
   6   50  903               

(半平太) 2016/05/23(月) 10:43


半平 様

書式ありがとうございました。

>これでもエクセルが落ちますか?(当方ではテストしておりませんけど)
→10万行オーバーで落ちました。

データを分割して教えて頂いた書式で対応したいと思います。

(60<) 2016/05/23(月) 16:08


 >30万行を超えるデータがあります。
 数式なんて常識外れ、ここはVBAでしょ。おーい、エキスパートさーん。

(とおりすがり) 2016/05/23(月) 16:19


  >>これでもエクセルが落ちますか?(当方ではテストしておりませんけど) 
  > →10万行オーバーで落ちました。 

  ありゃ! そうですか? 

  では、作業列を使う案

  B1セルに任意の初番(サンプルでは 900 )

  D1セル =B1

  D2セル =IF(A2="",D1,D1+1)
   下にコピー

  B2セル =IF(A2="","",D2)
   下にコピー

  <結果図>
  行  _A_  _B_  ______C______  _D_
   1   50  900  ←任意の数値   900
   2                           900
   3   20  901                 901
   4                           901
   5   40  902                 902
   6   50  903                 903
   7                           903
   8                           903

  テストしていないですけど、これは落ちないです。

(半平太) 2016/05/23(月) 16:38


 こんばんわ。

 見た目だけで良いなら、
 B2セル =IF(A2="",B1,B1+1)
 下にオートフィル

 B列全体を選択して、条件付き書式→数式に
 =A1=""
 書式のフォントを白色

 でどうでしょうか?

(sy) 2016/05/23(月) 18:07


半平 様

おはようございます。

>作業列を使う案
→30万+αで落ちませんでした。

ありがとうございます。助かりました。

図々しいお願いですが

今回の作業列を使う案と=IF(A1<>"",COUNT($A$1:A1),"")とでは何が違うのでしょうか?

教えて頂けないでしょうか。

(60<) 2016/05/24(火) 07:59


 >今回の作業列を使う案と=IF(A1<>"",COUNT($A$1:A1),"")とでは何が違うのでしょうか? 

 計算量に雲泥の差があります。

 ・・と言っても、一介のユーザーなんで、以下ざっとした「目の子計算」です。

 前提として「空白とデータの割合を4:1」、「全行数が30万」で考えてみます。

 >COUNT($A$1:A1)
   ↑
 この関数は簡単に見えますが、実はすんごく無駄の多いものです。
 いつも1行目からチェックを開始するんです。すると・・・

 全30万行なら、平均15万個のセルをしらみつぶしにチェックしていることになります。

 その計算をするセルの個数は、30万×(1/5)= 6万個
 その計算をしないで済むセルが30万×(4/5)=24万個

 総計算量は  6万×15万個 + 24万 = 90億24万 (12:35に訂正)

 一方、作業列の各数式は1個のセルしかチェックしていないです。
 総計算量は、30万×1個 × 2列  = 60万

 つまり、0.007%しかないです。(約1万分の1)

 ラフ過ぎる考察であることは認めます (^_^;)
 でも、計算量が桁違いであることは間違いないです。

(半平太) 2016/05/24(火) 10:50


半平 様

おはようございます。

>ラフ過ぎる考察であることは認めます
→いえ々そんなことはありません、具体的に教えて頂きまして
 感謝いたします。

今回の件では半平様、他の皆様にもお世話になりました。

ありがとうございました。
(60<) 2016/05/25(水) 07:33


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.