[[20141118165056]]　『複数抽出した値の羅列を行列入れ替え』（のBI太）　ページの最後に飛ぶ

『複数抽出した値の羅列を行列入れ替え』（のBI太）

 以下の発注入荷控えがあります。
 「納期超過日」は納期を過ぎて納入された物だけ過ぎた日数が表示されます。

    　 A       B        C        D       E       F 
    1   納期　 納入日 　品名     数量    商社  納期超過日
  2   8/20　　8/20　　　A　　　　1　　　あ　　 　
  3   8/20　　8/25　　　B　　　　5　　　い　　 　5
  4   9/20　　9/18　　　C　　　　3　　　う　　 　
  5   9/1 　　9/19　　　D　　　　3　　　う　　　18
  6   9/18　　9/20　　　B　　　　2　　　あ　　 　2
  7   9/10　　    　　　A　　　　2　　　あ　　　  
  8   9/30　　9/26　　　G　　　　2　　　い　　 　
  9  10/1 　　10/2      F　　　　2　　　あ      
 10  10/6               B        5      い

       ↓
 まだまだ続きます。

 上記の表から別Sheetに納期超過日数だけを商社ごとに抽出して下記の様な一覧表を作成する為
 ここで式を教えていただき完成する事が出来ました。

   　 A      B       C      D      E      F      G
 1   商社　遵守率　  1      2      3      4      5　→まだまだ続きます
 2   あ　　  33%     0 　   2     15
 3   い　　 100%     5
 4   う　　  50%     0      18
             ↑
 ここへは=COUNTIF(C3:G2,0)/COUNT(F2:G2)と言う式が入っています

 B2 =COUNTIF(C2:H2,0)/SUMPRODUCT((Sheet1!B$2:B$100<>"")*(Sheet1!$E$2:$E$100=$A2))
 下へコピー。

 C2 =IF(COUNTIF(Sheet1!$E$2:$E$100,$A2)<COLUMN(A1),"",INDEX(REPT((0&Sheet1!$F$2:$F$100)*1,Sheet1!$B$2:$B$100<>""),SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*10^16+ROW(A$2:A$100)-1,),COLUMN(A1))))

 C2をH2までコピー。 C2:H2 下へコピー。

 ※列増える場合はB列数式の範囲変更。
 ※一応100行目まで。
 ※C列以降の結果は文字列

 ところがこのまま使い続けているとIV列までしか使えず(約250個分)そのうち抽出できなくなりそうなので
 行と列を入れ替えて最上段にﾒｰｶｰ名、縦に3万以上のﾃﾞｰﾀｰの羅列が出来る様にしたく思いました。

   　 A      B       C      D
 1   商社　  あ       い     う　
 2   遵守率  33%    100% 　50%   
 3   1　　   0        5
 4   2　　   2                0
 5   3                       18
 6   4
 7   5
 8   6
      ↓
    まだまだ続きます

 単純に式の中の行記号と列記号をいじれば出来るかと思ってましたがそんな簡単な物では無く
 ちょっと私の頭では変更できそうにありません。

 何度もすみませんがどの様にしたらよいかお教え下さい。

＜使用 Excel：Excel2003、使用 OS：WindowsXP ＞

こんにちは

前回の流れは存じませんが

＞ここへは=COUNTIF(C3:G2,0)/COUNT(F2:G2)と言う式が入っています
＞B2 =COUNTIF(C2:H2,0)/SUMPRODUCT((Sheet1!B$2:B$100<>"")*(Sheet1!$E$2:$E$100=$A2))
この部分がどちらもB列を指しており不明です

とりあえず、提示された数式が難しいようであれば、作業列を使用した方法はいかがでしょうか

    　 A       B        C        D       E       F         G      　 H         I
  1   納期　 納入日 　品名     数量    商社  納期超過日         作業メーカ  作業期間
  2   8/20　　8/20　　　A　　　　1　　　あ　　 　                  あ1　　　　　0
  3   8/20　　8/25　　　B　　　　5　　　い　　 　5                 い1　　　　　5
  4   9/20　　9/18　　　C　　　　3　　　う　　 　                  う1　　　　　0
  5   9/1 　　9/19　　　D　　　　3　　　う　　　18                 う2　　　　 18
  6   9/18　　9/20　　　B　　　　2　　　あ　　 　2                 あ2　　　　　2
  7   9/10　　    　　　A　　　　2　　　あ　　  70                 あ3　　　　 70
  8   9/30　　9/26　　　G　　　　2　　　い　　   0                 い2　　　　　0
  9   10/1 　 10/2      F　　　　2　　　あ       1                 あ4　　　　　1
 10   12/1              B        5      い                         　 
 11

H、I列を作業列として
H2=IF(A2>TODAY(),"",E2&COUNTIF($E$1:E2,E2))
I2=IF(A2>TODAY(),"",IF(B2="",TODAY()-A2,MAX(0,B2-A2)))
それぞれコピーして下に貼り付け

     A       B    C    D
 1  商社     あ   い   う
 2 遵守率   25%  50%  50%
 3   1        0    5    0
 4   2        2    0   18
 5   3       70
 6   4        1
 7   5
 8   6
 9

B2=COUNTIF(B$3:B$50,0)/COUNT(B$3:B$50)
コピーして横に張り付け

B3=IF(COUNTIF(Sheet1!$H$2:$H$50,B$1&$A3)=0,"",VLOOKUP(B$1&$A3,Sheet1!$H$2:$I$50,2,FALSE))
コピーして表内の必要な範囲に張り付け

なお
＞※C列以降の結果は文字列
文字列で出す必要があるということでしょうか？

そうであれば
B2=COUNTIF(B$3:B$50,0)/COUNTIF(Sheet1!$H$2:$H$50,B$1&"*")　※結果が数値でも使用可
B3=IF(COUNTIF(Sheet1!$H$2:$H$50,B$1&$A3)=0,"",VLOOKUP(B$1&$A3,Sheet1!$H$2:$I$50,2,FALSE)&"")

（みねっと） 2014/11/19(水) 11:45

 みねっと様、返事が遅くなり申し訳ありません。
 ＞＞※C列以降の結果は文字列
 ＞文字列で出す必要があるということでしょうか？
 必要はないんですが式を作製して頂いた方では文字列になってしまう様でした。

 今回作製して頂いた式は数値で結果が出るので平均値等も出せて便利です。
 しかしながら見本上では完璧に作動しますが実際のファイルではエラーが発生する場合が有ります。

 実際の「発注入荷控え」及び「納期超過日数表」は同じサーバー内に有りリンクしている為か
 同時に開いてる時は良いですが「納期超過日数表」のみ開くと『リンクを更新する』『リンクを更新しない』どちらをを選択しても
 直前まで日数が表示されていた部分が全てが「#VALUE!」になってしまいます。
 その状態で「発注入荷控え」を後から開くと正しい数字表示になります。

 ※「発注入荷控え」の作業列I列は以下の様にしてますがこれが原因でしょうか？
 　(当方が土日・連休は受け取れない為、納期から除外する為です。)

 　=IF(B2="","",IF(B2="",TODAY()-A2,MAX(0,NETWORKDAYS(A2,B2,納禁日)-1)))

 よろしくお願いいたします。

 （のBI太）

 COUNTIF関数は、閉じられているブックを参照できませんので
 参照先のブックが閉じている時は「#VALUE!」が返されていると思います。

 COUNTIF関数でブック間参照になるのは
 ＞B3=IF(COUNTIF(Sheet1!$H$2:$H$50,B$1&$A3)=0,"",VLOOKUP(B$1&$A3,Sheet1!$H$2:$I$50,2,FALSE)) 
 の式ですか？

 これだけなら単純に
 　B3=IF(ISERROR(VLOOKUP(B$1&$A3,Sheet1!$H$2:$I$50,2,FALSE)),"",VLOOKUP(B$1&$A3,Sheet1!$H$2:$I$50,2,FALSE)) 
 としてみてはどうでしょう。
 　　
（HANA） 2014/11/29(土) 11:21

 この質問内の回答ってオイラがしたような覚えが…

 文字列にしたのは、数値がある行より以前の空白セルはゼロ、
 それ以降は空白にする。

 みたいな感じだったような、なかったような…

 回答ではなくてスンマセンww
（GobGob） 2014/12/02(火) 23:27

 HANA様、うまく動きました。
 COUNTIFがリンク出来ないというのを初めて知りました。

 今回行と列を入れ替えるタイミングで他者にも見て貰う必要が出たのでサーバーへ移す事にしました。

  HANA様、GobGob様　有り難うございました。
 (GobGob様、前回はお世話になりました。)

 （のBI太）

コメント返信：

[ 一覧(最新更新順) ]

[[20141118165056]] 『複数抽出した値の羅列を行列入れ替え』（のBI太） ページの最後に飛ぶ

[[20141118165056]]　『複数抽出した値の羅列を行列入れ替え』（のBI太）　ページの最後に飛ぶ