• ベストアンサー

【現在処理時間45分】EXCEL関数での時間短縮をさせたい。

お世話にまります。 ■相談概要 AccessからADO接続で下記のDBシートと、計算シートの作業者と作業者番号作成させますここまでの処理は問題はないのですが ■問題点 計算シートで計算させる時に時間が約45分くらいかかります 原因は計算シートに700列ぐらいあり行数は55あります 計算すると38500のセルに関数を入れているのが原因です。 関数は=SUMPRODUCT((DB!$C$1:$C$60000=$C21)*(DB!F$1:$F$60000=$F$17)*(DB!E$1:$E$60000))です 関数はVBAで自動に作成させています ■規則 計算シートは決められたフォーマットなので変更したくてもできません DBシート 日付|管理番号|作業者番号|数量 6/3 |00100001|01 |1 6/4 |00100001|01 |2 6/3 |00100001|04 |2 6/3 |00100002|04 |3 6/5 |00100001|08 |3 6/6 |00100001|08 |1 6/8 |00100001|08 |6 6/3 |002F000 |01 |1 計算後シート 作業者番号|01 　|04 |08 | 作業者　 ｜太郎|次郎｜三郎| 管理番号 ｜ 00100001 ｜3　｜2 ｜10 | 00100002 ｜ 　｜3 | | 002F001 ｜ 　｜ | |　　 00105006 ｜ 　｜ | | 002F000 ｜1　｜ | | どなたか計算の時間短縮等わかる方ご教授よろしくお願い申し上げます。

質問者が選んだベストアンサー

  • ベストアンサー
  • hotosys
  • ベストアンサー率67% (97/143)
回答No.5

Sub sample() Dim srcSheet As Worksheet Dim dstSheet As Worksheet Dim srcRow As Long Dim dstRow As Integer Dim dstColumn As Integer Dim rng As Range Set srcSheet = Sheets("DBシート") Set dstSheet = Sheets("計算後シート") dstSheet.Range(dstSheet.Range("B4"), dstSheet.Range("A1").End(xlDown).Offset(0, dstSheet.Range("A1").End(xlToRight).Column - 1)).Clear For srcRow = 2 To srcSheet.Range("B2").End(xlDown).Row Set rng = dstSheet.Columns("A:A").Find(srcSheet.Cells(srcRow, 2), LookIn:=xlValues, lookat:=xlWhole) If rng Is Nothing Then MsgBox "管理番号:" & srcSheet.Cells(srcRow, 2) & " がありません" Exit Sub End If dstRow = rng.Row Set rng = dstSheet.Rows("1:1").Find(srcSheet.Cells(srcRow, 3), LookIn:=xlValues, lookat:=xlWhole) If rng Is Nothing Then MsgBox "作業者番号:" & srcSheet.Cells(srcRow, 2) & " がありません" Exit Sub End If dstColumn = rng.Column dstSheet.Cells(dstRow, dstColumn) = dstSheet.Cells(dstRow, dstColumn) + srcSheet.Cells(srcRow, 4) Next End Sub

P3mania
質問者

補足

お返事ありがとうございます ぐおおおおおお～～～感激しました。 できるものでね、すげ～ ありがとうございます もうひとつだけお願いしたいのですが ■解読できたところ（管理番号） 列の場所を変えるには下記を変更しただけでOKでしたが Set rng = dstSheet.Columns("C:C") ・厳密はC21より下へ ■解らないところ（作業番号） 作業番号をF:17より右へBH:17まで ■計算された値の貼り付け 値の貼り付け開始はF21より しつこくてすみません最後にしますので、ご教授お願いしますｍ（＿＿）ｍ

その他の回答 (6)

  • hotosys
  • ベストアンサー率67% (97/143)
回答No.7

管理者番号がC21から下へある場合 Set rng = dstSheet.Columns("C:C").Find(... でもいいが、最大がわかっているなら（または適当に） Set rng = dstSheet.Range("C21:C250").Find(... でもいい。 ちゃんと有効データで出すなら、 Set rng = dstSheet.Range(dstSheet.Range("C21"), dstSheet.Range("C21").End(xlDown)).Find(... ただし、これは最低dstSheet.Range("C22")まではデータがある事が前提 Set rng = dstSheet.Range(dstSheet.Range("C21"), dstSheet.Range("C65536").End(xlup)).Find(... がExcel2007までは正しかったが、Excel2007の最大行にも対応させると Set rng = dstSheet.Range(dstSheet.Range("C21"), dstSheet.Range("C" & dstSheet.Rows.Count).End(xlUp)).Find(... になるが、どれでも好きな物を。 作業番号はF:17より右へBH:17までと決まっているなら Set rng = dstSheet.Range("F17:BH17").Find(... で求められる。 管理番号がC21からで作業者番号がF17からなら、計算された値の貼り付けはF21になるはずなので、他の変更は無いと思う。 後はクリアの部分を直す必要がある 面倒なので、 dstSheet.Range("F21:BH65536").Clear などと適当にクリアしてもいい。

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.6

No3です。 ちょっと思ったのですが、 ＞関数は=SUMPRODUCT((DB!$C$1:$C$60000=$C21)*(DB!F$1:$F$60000=$F$17)*(DB!E$1:$E$60000))です ＞関数はVBAで自動に作成させています なぜにVBAで作成する必要がなるのでしょうか、先に計算後シートに 数式入れて、計算を手動にしておいて、必要時に再計算すると良いように思うのですが？ VBAのコードで既に回答が出ていますので理解して運用してください。 計算後シートに特別な理由があるのであれば、 別途、ピボットテーブル案で所定シートのセルに値を入れるシートですが 先にピボットテーブル作成しておいて 計算後シート 　　　A　　　　B　　　C　　D 1　作業者番号|01 　|04 |08 | 2　作業者　 ｜太郎|次郎｜三郎| 3　管理番号 ｜ 4　00100001 5　00100002 B4セルに　=INDEX(ピボットシート!$1:$65536,MATCH($A4,ピボットシート!$A:$A,FALSE),MATCH(B$1,ピボットシート!$4:$4,FALSE)) で右フィル、下フィルして速度の確認してみてください。 エラー処理はしていません。 VBAで計算する方法 SUMPRODUCT関数を入れておいて再計算させる方法 ピボットテーブルの値から引っ張ってくる方法 処理の速い方法選んでみてください。

  • imogasi
  • ベストアンサー率27% (4737/17068)
回答No.4

関数の計算が処理時間的に大変であるのだったら、VBAでやればよいでしょう。 しかし、＞=SUMPRODUCT((DB!$C$1:$C$60000=$C21)*(DB!F$1:$F$60000=$F$17)*(DB!E$1:$E$60000))です は何をやっているのか。回答者はテストを受けているのではないので、読解させず、文章で質問文において説明すること。 ーー 関数式を見ると 第１行－第６００００行に渉って、各行において、３条件をAND条件的に判定しているだけでしょう。 VBAでやれば６００００行読み終わると完了し、３比較などメモリ内の変数比較３回するだけでしょう。足し算１回と。すぐ終ると思う。 VBAが判るなら、すぐ実現する。 コード数は約１０行の見込み。 ＞原因は計算シートに700列ぐらいあり行数は55あります と式の６００００行の関係がわからない。 ２００７ですか。バージョンを書いてない。

P3mania
質問者

お礼

お返事ありがとうございます

P3mania
質問者

補足

お返事ありがとうございます 関数においての補足等に付きましては申し訳ありません お答えしていただいたとおりです。 60000行に関してはDBシートにどれくらいの値が入るかわからないので とりあえず60000行にしてみました。 又、ＥＸＣＥＬのバーションは2003です ＶＢＡで、できますか？ よろしければご教授ねがいませんか？ 言葉ではなくＶＢＡコードで願います

  • hallo-2007
  • ベストアンサー率41% (888/2115)
回答No.3

ご存知かと思いますが、SUMPRODUCTの様な配列関数は多用するとパソコンの負担が増え、動作が遅くなります。 現状のVBAでが、上記の関数を書き加えるたび再計算しているのでしょう。 VBAで処理するのであれば、DBシートを順に検索して 条件によって、計算後の各セルに振り分けて足していく方法に変える手法もありそうですが。 表を見る限り、ピボットテーブルを一度作成すると一発の様な気がします。 2度目からは、ピボットテーブルのデータの更新　をクリックするだけです。この方法ではダメなのでしょうか。

P3mania
質問者

お礼

お返事ありがとうございます

P3mania
質問者

補足

当方もピボットテーブルが使えれば問題は解決するのですが仕様が決まっております。 できればＮｏ１様の言うＶＢＡコードで作れれば理想的なのですが・・ どなたか救世主様がいらっしゃれば・・・・・泣 これにかれこれ3ヶ月ぐらいは悩んでます

  • Masa2072
  • ベストアンサー率51% (94/182)
回答No.2

ANo.1さんの回答に補足 「計算方法」の変更はVBA内で行えます。 計算シートの処理に入る前に Application.Calculation = xlCalculationManual マクロ終了前に Application.Calculation = xlCalculationAutomatic を行います。

P3mania
質問者

お礼

お返事ありがとうございます

P3mania
質問者

補足

お返事ありがとうございます はい、計算時期は当方も考えました。 自動での計算をキャンセルし必要なときに計算させています 説明少なくてすみません 今の時点ではＶＢＡでの処理が、一番有力かと思い始めてきました。

  • sippo06
  • ベストアンサー率25% (7/27)
回答No.1

こんばんわ EXCELはセル１個の値が変わるたびに、シート内の関数をすべて再計算してしまうようです。 そこで、以下で多少改善されるかもしれません。 （これは、EXCEL2000、2003の方法なので、そのほかのバージョンだったらごめんなさい） 1.マクロを実行する前に該当のファイルを開く 2.メニューの「ツール」-「オプション」で表示されるダイアログの「計算方法」を選択 3.「手動」のオプションを選択し、「OK」を押す 4.VBAを実行 5.3のダイアログで「再計算を実行」ボタンをクリック

P3mania
質問者

お礼

お返事ありがとうございます

P3mania
質問者

補足

お返事ありがとうございます 自動計算処理は実行しています 説明少なくてすみません

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • エクセル マクロ 計算時間を短縮したい

    =SUMPRODUCT(min((f:f>=s2)*(f:f<s2+730)*r:r)) 上記の関数を、オートフィルで数百行下までコピーするというマクロを作りました。 しかしかなり計算時間がかかります。 計算時間を短縮する方法があれば教えてください。 計算結果が同じで時間が短縮するなら上記の関数を使わなくてもいいです。 f:fとr:rの範囲はこのマクロを使うファイルによって異なりますが、 最上行から最下行まで空白はありません。 エクセル2010です。 cpuはcore2duo E6400です。

  • エクセル関数

    お世話になります データシートから 集計シートに集計させる関数あれば関数をお教えください ＊フォーマットが決められているためＰＶＴ等は使用できません データシート1 ID|管理番号|使用者番号|数量|日付 01|12E22100|12 |1 |2008/01/01 02|12345678|01 |5 |2008/01/01 03|12E22100|12 |1 |2008/01/01 04|12Y22100|12 |1 |2008/01/01 05|001234T7|015 |4 |2008/01/01 06|12E22100|12 |1 |2008/01/01 集計シート2.xls 管理番号|01|2|3|4|12|015| 12E22100| | | |3 |　 | 001234T7| | | | |4 | 12345678|5 | | | | | 上記が結果です 管理番号に対して使用者番号ごとに合計数量を出したいのですが かのうですか？ わかるかたよろしくお願いします

  • エクセル　シート　関数

    エクセル2003にて、シート番号の関数についてです。 現在sheet1(0)をコピーして必要分だけsheet1(1)、sheet1(2)としています、 日によってsheet1(50)ぐらいまで作成することがあります、これを利用して=IF(ISERROR(INDIRECT("'Sheet1 (1)'!c3")),"-----",INDIRECT("'Sheet1 (1)'!c3"))このような関数を管理シートで使用しています。 問題として関数内のsheet1(1)が連番なので表作成時(1)の部分の1を連番として2、3・・51、52と関数内でsheet1(51)自動で入力するための関数を教えください。

  • エクセル関数２

    シート(1) A B C D 作業列　　　　コード　　　　顧客名　　　　　金額 3 　　　　　　 300001　　　　　A 10,000 3 　　　　　　 300002 　　B 15,000 3 　　　　　　 300003 　　C 20,000 3 　　　　　　 300004 　　D 25,000 3 　　　　　　 300005 　　E 30,000 4 　　　　　　 400001 　　F 35,000 4 　　　　　　 400002 　　G 40,000 　4 400003 H 45,000 4 400004 I 50,000 4 400005 J 55,000 　　　　　　　 シート(2) A B C D E F 「1」 「2」 「3」 「4」 「5」 「6」 10,000 35,000 15,000 40,000 20,000 45,000 25,000 50,000 30,000 55,000 ※　シート(1)にコード、顧客名、金額を入力すると、シート(2)にあるように該当するコードの先頭行の列に表示するようにしたいと思っています。 シート１で作業列を作りその作業列を基にシート２でVLOOKUP関数を使い‥シート２の10,000のセルの場合、 VLOOKUP(C$2,sheet1!$A$3,$D$12,4,false)として、下にコピーすると同じ金額になってしまいます。 セルが空白にならず、シート１の金額が上から順番にシート２に表記されるようにするにはどうしたらよいでしょうか。マクロはできませんので、関数で詳しく教えて下さい。宜しくお願いします。

  • エクセル「SUMPRODUCＴ関数」で困っています

    SUMPRODUCT関数 初心者です。 項目数１５くらい、約１００００件のデータベースから、3項目の条件でSUMPRODUCT関数を使って２０種別×15種別の表を作成してみようと思いました。 …が、結果すべて０になってしまいます。 SUMPRODUCT関数の使い方（考え方）はあっていると思います。（小さい表で使ってみると正しく計算されるため） たしかに、SUMPRODUCT関数は沢山計算しているので、大きな？表を集計するのに向かないのでしょうか？ そのあたりがわかりません。 ただ、ピボットテーブルで同じものを作成すると、さくさく完成します。 こんな漠然とした質問でお答えいだけるのか不安ですが、どなたかお詳しい方よろしくお願いします。

  • エクセル関数のことで困っています。

    今、簡単な表計算の表を作っていますが、先に進まず困っています。 A1～A200のセルに1～200の番号が昇順で入っています。そしてデータとして,B2,C2,D2,E2 に各々a、ｂ、ｃ、ｄが入っており,,B3,C3,D3,E3 にa1、ｂ1、ｃ1、ｄ1・・・・・・・・・・・・B200,C200,D200,E200にｗ,ｘ,ｙ,ｚのようにB,C,D,E列に適当な数が入っています。 そこで、E列の値で昇順　　SMALL（D$1:D$200,A1)　　に並べ替え、同時にB,C,D,Eの値も返したいのです。つまりエクセルの並べ替え機能を関数で自動で行いたいのです。 今は、関数の、ROW,SUMPRODUCT,COUNT,INDEX,LARGE,COUNTIF,COLUMN,などを使い関数バーに5行ほどになり、処理にとても時間がかかります。 何とか、簡単で早い計算式はないでしょうか。 よろしくおねがいします。

  • Excelの関数について

    sheet1で作成した表の合計数(使用関数：sumproduct)を sheet2で作成している表に反映させたいのですが、 VLOOKUPを使うと必ずエラーになってしまいます。 VLOOKUPと同じような方法で条件ごとに検索して データを引き出す事が可能な関数はないでしょうか？ 解り辛いかもですが、よろしくお願いします。

  • excel vbaの関数の使い方

    excel VBAでsumproduct関数を使いたいがでますのですがどうしても実行できません 何が原因でしょうか、どなたか教えてください。 ------------------------------------------------- 下記コードは実行できます。 Worksheets("集計").Range("j4").Value = WorksheetFunction.Sum(Worksheets("日常").Range("ｈ4:ｈ13")) 下記コードは型が違うとのコメントがでます。 Worksheets("集計").Range("j4").Value = WorksheetFunction.SumProduct((Worksheets("日常").Range("c4:c13") >= j2) * Worksheets("日常").Range("c4:c13") <= k2) * Worksheets("日常").Range("d4:d13") = l2 * Worksheets("日常").Range("f4:f13") = m2 * Worksheets("日常").Range("h4:ｈ13") --------------------------------------------------------------------- ワークシートは、日常、集計、の二つがあります。 「日常」には、Ｃ４：Ｈ１３にデータがあります。 c列に年月日、d列にコード番号、e列に購入箇所、f列にコード番号、g列に商品、h列に金額 が入力されています。 「集計」には、j2に開始日、k2に終了日、l2にd列のコード番号、m2にｆ列のコード番号、 が入力されています。 環境は、windows10 使用しています。 ----------------------------------------------------------- 以上の状況ですがvba でsumproduct関数を使いたいのですが実「実行」できません,たかどなたか教えて頂けませんか。

  • Excelについて 適した関数探してます

    シート１に下のようなデータがあり、 A1～A10が番号 B1～B10が○ C1～C10が△ D1～D10が□ E1～E10が× シート２に、A1に番号を入力すると B2に○ C3に△ D3に□ E3に× (○△□×は、数字やデータがはいってます) 解りづらかったらすいません。 要は、シート２のA1に、番号をいれると、シート２のB2～E3に、シート１のデータがでるようにしたいのです。 このような事をしたいのですが、VLOOKUPでできますか？ 他に適した関数、式の立て方はありますでしょうか？ 挑戦してるのですが、なかなかできません。 XPで、Excel2003です。 良かったらご意見くださいm(_ _)m

  • エクセル　VBAで関数

    こんばんわ。いつもお世話になっております。 エクセルでデータを加工しているのですが、関数で加工するととても遅くなってしまうので なんとかVBAで作業できないかと模索中です。 Sheet1の、Ａ列＝取引先　Ｂ列＝支店名　となっており、それが3,000行ほどあります。 Sheet2も、Ａ列＝取引先　Ｂ列＝支店名　となっており、 Sheet1のＣ列に、Sheet1のＡ列のＢ列という支店が、Sheet2にあるか確認したいのです。 現在は、Sheet1のＣ列に、SUMPRODUCT関数でカウントさせているのですが、大変重いです。 ほかのSheetで、VBAを使って関数のような働きをさせている部分があります（前任者作成） そのように、なんとかVBAを使用したいのですが、なかなかうまく出来ません・・・。 どうか、お力を貸してください！！ うまく説明できなくて、わかりづらかったらごめんなさい。 よろしくお願いいたします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する