[[20170602095646]]　『SUMIFS関数の計算時間を短くしたいのですが』（つむつむ）　ページの最後に飛ぶ

『SUMIFS関数の計算時間を短くしたいのですが』（つむつむ）

日頃から拝見させて頂き有難うございます。

Sheet1はデーター貼付用のシートです。
Sheet2に下記の関数が設定されて降ります。
Sheet2 F11:I300 =SUMIFS(Sheet1!Q:Q,Sheet1!$E:$E,$C11,Sheet1!$G:$G,$E11,Sheet1!$B:$B,$C$3)
Sheet2のJ11:AR300まで別のSUMIFS関数が入っていますが省略しています。
Sheet2のC3に商品コードを入力しており、そのコードを変更し商品毎の状況を確認しております。
コード変更は100個ほどやります。変更のたびに計算が終了するまでに約6秒かかる為時間がかかって困っています。

この時間を短縮する方法は無いでしょうか

宜しくお願い致します。

Sheet1

      A     B         C          D        E        F          G    H    I     J    K    L    M    N     O    P   Q    R    S    T
  1                                                                05月 06月 07月 08月 09月 10月 11月 12月 01月 02月 03月 04月 05月
  2 地域 商品コード 商品名称 会社コード 会社名称 支店コード 支店名称 kg   kg   kg   kg   kg   kg   kg   kg   kg   kg   kg   kg   kg
  3 北海道 A0123    いも     123        札幌     789        中央    1    0    1    1    2    1    1    2    1    1    2    1    2
  4 北海道 B3210      鮭     123        札幌     789        中央    2    2    3    4    2    3    4    2    3    4    2    3    4
  5 北海道 C4567    牛肉     123        札幌     789        中央    0    2    3    4    5    6    7    8    9    8    5    2    3
  6 北海道 A0123    いも     123        札幌     800        栄町    3    0    5    3    4    5    3    4    5    3    4    5    3
  7 北海道 C4567    牛肉     123        札幌     800        栄町    4    4    0    4    4    5    4    4    5    4    4    5    4
  8 大阪   A0123    いも     456        梅田     111        北区    2    3    2    3    4    2    3    4    2    3    4    2    3
  9 大阪   B3210      鮭     456        梅田     111        北区    1    2    1    2    3    1    2    3    1    2    3    1    2
 10 大阪   C4567    牛肉     456        梅田     111        北区    3    3    4    3    3    4    3    3    4    3    3    4    3
 11 大阪   B3210      鮭     456        梅田     222        南区    6    7    8    6    7    8    6    7    8    6    7    8    6
 12 大阪   C4567    牛肉     456        梅田     222        南区    1    2    3    4    5    6    7    8    9    0    1    2    3								
Sheet2		
        A        B         C        D        E     F    G    H    I     J      K      L      AK      AL      AM      AN      AO     AP     AQ     AR
  1
  2                   商品コード
  3                   B3210
  4		
  5
  6
  7
  8
  9                                               02月 03月 04月 05月 5月1日 5月2日 5月3日 5月28日 5月29日 5月30日 5月31日 6月1日 6月2日 6月3日 6月4日
 10 地域   会社コード 会社名称 支店コード支店名称 kg   kg   kg   kg	
 11 北海道 123        札幌     789       中央     4     2    3    4
 12 北海道 123        札幌     800       栄町     0     0    0    0
 13 大阪   456        梅田     111       北区     2     3    1    2
 14 大阪   456        梅田     222       南区     6     7    8    6

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 一般的な方法としては
 =SUMIFS(Sheet1!Q:Q,Sheet1!$E:$E,$C11,Sheet1!$G:$G,$E11,Sheet1!$B:$B,$C$3) 
               ~~~~        ~~~~~             ~~~~~             ~~~~~
 下線部分を必要最低限範囲（Q3:Q100等）にする
 或いはSheet1をテーブル定義する（表の形が適しておりませんが。）

 ピボットテーブルを使って商品コードでフィルターする

 くらいしか思いつきませんねぇ。

（稲葉） 2017/06/02(金) 11:23

稲葉様
ご連絡有難うございます。

範囲指定(5:5000)をして見ましたが、あまり変化は無いようです。

ピボットテーブルの作成もやってみましたが、適当な形式で無い様で出来ませんでした。

やはりむりでしょうか
（つむつむ） 2017/06/02(金) 11:45

 >Sheet2のJ11:AR300まで別のSUMIFS関数
 こちらの方も範囲指定しているだろうか？
（ねむねむ） 2017/06/02(金) 11:55

ねむねむ様
ご連絡有難うございます。

Sheet2のJ11:AR300まで別のSUMIFS関数

同じように範囲設定(5:5000)してから実行致しましたが変化は感じられませんでした。
（つむつむ） 2017/06/02(金) 12:04

横から失礼するゾ～

Sheet1!$E:$E,$C11

この条件っているかゾ？
掲示されたデータなら商品コードと支店コードor支店名で行が決まるような気がするゾ。
（hm） 2017/06/02(金) 12:12

hm様
ご連絡有難うございます。

Sheet1!$E:$E,$C11

支店が中央で、会社が梅田となる場合が極まれにあるために必要なんです。
（つむつむ） 2017/06/02(金) 12:31

そんなことだろうとは思ってたけどならそれを掲示すべきダルルォ！？
レイアウト含め掲示して下さるのはありがたいけど可能な限り分岐に関わる条件を全て内包した形でお願いしますゾ。

非表示作業列作ってそれぞれの列をユニーク(商品コード&会社コード&支店コードみたいな？)にしてやってINDEX・MATCHで探してやるとかじゃだめかなぁ・・・
正味どっちが処理早いとかわからんのでこれで失敬するゾ～
（hm） 2017/06/02(金) 12:43

 もう一つあった
 先にキーを作ってはいかがでしょう？

 Sheet1の空いてる列（たとえばAA列）に
 =E3&"_"&G3&"_"&B3

 んで計算式を
 =SUMIF(Sheet1!AA3:AA50000,C11&"_"&E11&"_"&$C$3,Sheet1!Q3:Q50000)
                           ~~~~~~~~~~~~~~~~~~~~
 AA列のキーを探すみたいな。

 どうだろう？
（稲葉） 2017/06/02(金) 12:44

稲葉様
ご連絡有難うございます。

教えて頂きました、=E3&"_"&G3&"_"&B3　SUMIF関数で試してみました。
6秒が5秒になった様な気はしますが、大きな変化はございませんでした。
（つむつむ） 2017/06/02(金) 13:27

ふと思ったけど商品ごとにして月間が見れればいいんならわざわざシート間で引っ張らなくても商品名or商品コードでフィルター掛ければいいんじゃ？
そしたらSheet2は（何入ってるか知らんけど）J11:AR300だけ残して月間部分は削除できるから軽くなりそう(小並感)
（hm） 2017/06/02(金) 13:53

hm様
ご連絡有難うございます

データーは３種類ありそれをSheet2で一覧にしておりますので
削除出来る部分はございません。

（つむつむ） 2017/06/02(金) 15:22

意味がわかりません、削除できない理由になっていません。その形式から変更できない旨も聞いていません。
あまり解決する気がないように見えるのでもう何もいいません。どうぞ無駄に形に拘ってください。
（hm） 2017/06/02(金) 15:46

 Sheet1の並びは、地域順になったいるように見えますが、
 見方によっては会社コード順にも見えます。
 実際はどうなんでしょうか？

 仮に実際が地域順だとしても、会社コード順に変えることは出来ますか？
 それが出来れば、参照範囲が絞り込めるので、
 1秒程度に収められるかも知れないなぁと思っているんですけど。
 (全ての数式が全て行を参照する必要もなくなるので。)

 会社名と会社コードは一対一の関係ですね？

  参考までにお聞きしますが、現在のデータ行数と会社の数は、ざっと幾らあるんですか？

（半平太） 2017/06/02(金) 16:23

半平太様
ご連絡有難うございます

地域と会社コードに関連はございませんので会社コード順ではございません。

会社コード順に並び替える事は可能です。

会社名と会社コードは一対です。

データー行は、約3,500行です。会社の数は、200社　支店の数は1,000程です

宜しくお願い致します。
（つむつむ） 2017/06/02(金) 17:06

 素朴な疑問なんですが、支店が1000なのに、なんでコードは3ケタなんですか？
 増える予定なし？
 会社ごとに支店コードがあるから重複するってことですかね？
（稲葉） 2017/06/02(金) 18:27

 連投で申し訳ないですが、もう一つ
 Sheet2ってよく見ると、AR列までありますが、もしかしてもっと列数あります？
 J列以降の参照先は伺っていませんが、Sheet1には必要そうなデータないですよね？
 別ブック参照していたり、そっちのデータのほうが多いってことはないですか？
（稲葉） 2017/06/02(金) 18:33

稲葉様
ご連絡有難うございます。

会社コードや支店コードの実際は、6桁コードです。レイアウト作成を簡単にしようと短くしました。

Sheet1は月別実績です。13か月分のデーターです。
他のSheet別に日別実績（31日分のデーター）と予約データー（4日分のデーター）が有りそれぞれShee2にSUMIFSで計算しています。
Shee2のJ:ANが日別計算です。
Shee2のAO:ARが予約データーの計算です。

計算データーが多いのは、日別計算の方です。

日別も、予約もSUMIFS関数式は月別計算と同じ様な式ですので説明を省略しました。

宜しくお願い致します。
（つむつむ） 2017/06/02(金) 19:21

 それはもう再現のしようがないから、本当はどこで時間かかっているかわからないですね。
 日付データも日付が横なんですか？
 もしどこかから引っ張ってきたデータベースなのであれば、元は↓のような形のはずなので
 そうすればピボットで集計してから参照、のような使い方ができるのかなと思います。

     |[A]   |[B]       |[C]     |[D]       |[E]     |[F]       |[G]     |[H]      |[I] |[J] 
 [1] |地域  |商品コード|商品名称|会社コード|会社名称|支店コード|支店名称|年月     |数値|単位
 [2] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年05月|   1|kg  
 [3] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年06月|   0|kg  
 [4] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年07月|   1|kg  
 [5] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年08月|   1|kg  
 [6] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年09月|   2|kg  
 [7] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年10月|   1|kg  
 [8] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年11月|   1|kg  
 [9] |北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年12月|   2|kg  
 [10]|北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年01月|   1|kg  
 [11]|北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年02月|   1|kg  
 [12]|北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年03月|   2|kg  
 [13]|北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年04月|   1|kg  
 [14]|北海道|A0123     |いも    |       123|札幌    |       789|中央    |'16年05月|   2|kg  
 [15]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年05月|   2|kg  
 [16]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年06月|   2|kg  
 [17]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年07月|   3|kg  
 [18]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年08月|   4|kg  
 [19]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年09月|   2|kg  
 [20]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年10月|   3|kg  
 [21]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年11月|   4|kg  
 [22]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年12月|   2|kg  
 [23]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年01月|   3|kg  
 [24]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年02月|   4|kg  
 [25]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年03月|   2|kg  
 [26]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年04月|   3|kg  
 [27]|北海道|B3210     |鮭      |       123|札幌    |       789|中央    |'16年05月|   4|kg

 ピボット
    |[A]        |[B]     |[C]     |[D]      |[E]      |[F]      |[G]      |[H]      |[I]      |[J]      |[K]      |[L]      |[M]      |[N]      |[O]      |[P] 
 [1]|           |        |        |         |         |         |         |         |         |         |         |         |         |         |         |    
 [2]|           |        |        |         |         |         |         |         |         |         |         |         |         |         |         |    
 [3]|合計 / 数値|        |        |列ラベル |         |         |         |         |         |         |         |         |         |         |         |    
 [4]|行ラベル   |会社名称|支店名称|'16年01月|'16年02月|'16年03月|'16年04月|'16年05月|'16年06月|'16年07月|'16年08月|'16年09月|'16年10月|'16年11月|'16年12月|総計
 [5]|A0123      |札幌    |中央    |        1|        1|        2|        1|        3|        0|        1|        1|        2|        1|        1|        2|  16
 [6]|B3210      |札幌    |中央    |        3|        4|        2|        3|        6|        2|        3|        4|        2|        3|        4|        2|  38
 [7]|総計       |        |        |        4|        5|        4|        4|        9|        2|        4|        5|        4|        4|        5|        4|  54

 あくまで横データであるなら、申し訳ありませんが私には荷が重いです。
 途中で投げ出すようで大変心苦しいですが、ご検討ください。
（稲葉） 2017/06/02(金) 19:43

 またまた追記。
 ×日付データ　○日別データ

 更に、日別データに重複がなければ、SUMIFではなく、INDEX・MATCH（作業列にキーをつけて）で劇的に早くなると思いますが、
 日別データもやはり重複があるのですよね？
（稲葉） 2017/06/02(金) 19:46

 ＞会社コード順に並び替える事は可能です。

 以下、Sheet1が会社コード順に並んでいるとの前提です。

 Sheet2のBA：BB(作業列）に下記数式を入力して、下にコピー

 (1) BA11セル =IF($B10=$B11,BA10,MATCH($B11,Sheet1!$D:$D,0))
 (2) BB11セル =IF($B10=$B11,BB10,MATCH($B11,Sheet1!$D:$D))

 (3 )F11セル に下記数式を入力して、下、右にコピー
 =SUMIFS(INDEX(Sheet1!Q:Q,$BA11):INDEX(Sheet1!Q:Q,$BB11),INDEX(Sheet1!$E:$E,$BA11):INDEX(Sheet1!$E:$E,$BB11),$C11,INDEX(Sheet1!$G:$G,$BA11):INDEX(Sheet1!$G:$G,$BB11),$E11,INDEX(Sheet1!$B:$B,$BA11):INDEX(Sheet1!$B:$B,$BB11),$C$3)

 <Sheet2 結果図>　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　
  行 ___A___ _____B_____ ____C____ _____D_____ ____E____ _F_ _G_ _H_ _I_　：：：：： _BA_  _BB_　　　　
  10 地域    会社コード  会社名称  支店コード  支店名称  kg  kg  kg  kg              始行  終行
  11 北海道      123     札幌         789      中央       4   2   3   4                3     7
  12 北海道      123     札幌         800      栄町       0   0   0   0                3     7
  13 大阪        456     梅田         111      北区       2   3   1   2                8    12
  14 大阪        456     梅田         222      南区       6   7   8   6                8    12

 以上で、3500行を参照していたものが、20行くらいで済むので、いままで6秒かかっていたのなら、1秒も掛からなくなると言う理屈です。

 ただし、稲葉さんの推測の様に、別の要因があって6秒掛かっていたのなら、そんな効果はありません。

（半平太） 2017/06/02(金) 19:51

稲葉様
ご連絡有難うございます。

残念ながら、年月データーも日別データーも横表示です。
（つむつむ） 2017/06/02(金) 20:18

半平太様
ご連絡有難うございます。

本日は教えて頂きました内容を試す環境に有りませんので、月曜日にやって見ます。

結果ご報告致します。
（つむつむ） 2017/06/02(金) 20:24

 そもそも論！！

 実績は変更ないわけだから、重複あったらに事前に集計して値にしちゃダメなのかい？
 むしろすべき。んでINDEX　MATCHでもVLOOKUPでも参照型にするべきです！！

 予測データも重複必要ないよね？

 だから考えなくちゃいけないのは日別データの重複と考えます。

 考え方変えてみました。
 商品コードを変更するときに時間がかかるなら、時間をまとめて（＝すべての計算を先に行って）変更時はすぐに切り替わるようにする
 でも大丈夫ですかね？

 ついでにSUMIFSをなくす！
 日別データが並べ替えができると仮定した場合です。

 ※Sheet1（月別実績）を例にとりますが、日別シートも基本は同じ考えです。
 １）月別実績のU列に　=B3&"_"&E3&"_"&G3　↓にコピー（キーとする）
 ２）キーで並び替える
 ３）V列に　=IF($U3=$U4,H3+V4,H3)　下の行と同じなら足す（＝常に上の行が加算された値）
 ４）３）の計算式を必要な月数分列方向にコピーする（5月～5月？）
 ５）Sheet2のAA11に　=$C$3&"_"&C11&"_"&E11　としてキーを先に用意しておく
 ６）Sheet2の8行目にVLOOKUPで参照する列番号をあらかじめ入力しておく（Sheet1に同じ項目名がなければ、Matchでもよかったのですが　例：5月）
 ７）Sheet2のF11に　=VLOOKUP($AA11,Sheet1!$U:$AH,F$8,0)　と入力して必要な行・列にコピーする

 Sheet1
     |[U]            |[V] |[W] 
 [1] |               |05月|06月
 [2] |キー           |kg  |kg  
 [3] |A0123_札幌_中央|   1|   0
 [4] |B3210_札幌_中央|   2|   2
 [5] |C4567_札幌_中央|   0|   2
 [6] |A0123_札幌_栄町|   3|   0
 [7] |C4567_札幌_栄町|   4|   4
 [8] |A0123_梅田_北区|   2|   3
 [9] |B3210_梅田_北区|   1|   2
 [10]|C4567_梅田_北区|   3|   3
 [11]|B3210_梅田_南区|   6|   7
 [12]|C4567_梅田_南区|   1|   2

 Sheet2
     |[A]   |[B]       |[C]       |[D]               |[E]   |[F] |[G] |[H] |[I] 
 [1] |      |          |          |                  |      |    |    |    |    
 [2] |      |          |商品コード|                  |      |    |    |    |    
 [3] |      |          |B3210     |                  |      |    |    |    |    
 [4] |      |          |          |                  |      |    |    |    |    
 [5] |      |          |          |                  |      |    |    |    |    
 [6] |      |          |          |                  |      |    |    |    |    
 [7] |      |          |          |                  |      |    |    |    |    
 [8] |      |          |          |                  |列数→|  11|  12|  13|  14
 [9] |      |          |          |                  |      |02月|03月|04月|05月
 [10]|地域  |会社コード|会社名称  |支店コード支店名称|kg    |kg  |kg  |kg  |    
 [11]|北海道|       123|札幌      |               789|中央  |   4|   2|   3|   4
 [12]|北海道|       123|札幌      |               800|栄町  |#N/A|#N/A|#N/A|#N/A
 [13]|大阪  |       456|梅田      |               111|北区  |   2|   3|   1|   2
 [14]|大阪  |       456|梅田      |               222|南区  |   6|   7|   8|   6

（稲葉） 2017/06/03(土) 08:42

稲葉様
ご連絡有難うございます。

現在は、テスト環境が有りませんので月曜日に教えて頂きました内容で試してみます。

＞＞４）３）の計算式を必要な月数分列方向にコピーする（5月～5月？）
前年の同月を確認するために１３ヶ月分の実績としています。

結果ご連絡致します。
（つむつむ） 2017/06/03(土) 16:59

半平太様稲葉様

教えて頂きました方法でやってみました

半平太様の方法で、私の勘違い
＞会社コード順に並び替える事は可能です。
可能では有りませんでした。
見た目は数値と思って降りました会社コードや支店コードは文字でした。
000123など頭にゼロが付いているコードがあります。
そこで始行は、教えて頂きました関数でやり
終行は、最下段を検索する関数にしました。
その結果　　６秒から２秒になりました。
尚、Sheet2のA11:E300まで配列数式が設定されておりますので１秒にならなかったものと思われます。

稲葉様の方法では、６秒から３秒になりました。

お二人の発想のすばらしさに、改めて驚き感激して降ります。

今回は、半平太様に教えて頂きました方法でやります。

有難うございました。

（つむつむ） 2017/06/05(月) 11:44

コメント返信：

[ 一覧(最新更新順) ]

[[20170602095646]] 『SUMIFS関数の計算時間を短くしたいのですが』（つむつむ） ページの最後に飛ぶ

[[20170602095646]]　『SUMIFS関数の計算時間を短くしたいのですが』（つむつむ）　ページの最後に飛ぶ