[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『複数抽出した値の羅列』（の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 9/25 A 2 あ 15
8 9/30 9/26 G 2 い
9 10/1 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)と言う式が入っています
どの様にしたらよいかお教え下さい。 (過去ログを検索するとCOUNTIF等を使う様ですが思いつきません。)
＜ 使用 Excel：Excel2003、使用 OS：WindowsXP ＞
簡単なのは
元データのG列に =E2&COUNTIF(E$2:E2,E2) 必要行フィルドラッグ。
一覧表のB1〜 1,2,3,・・・と値だけ入力して B2セルに =IF(COUNTIF(Sheet1!$E:$E,$A2)<COLUMN(A2),"",SUMIFS(Sheet1!$F:$F,Sheet1!$G:$G,$A2&B$1)) 必要範囲にフィルドラッグ。 （HANA） 2014/09/02(火) 20:38
マクロ案 だけど例題が良くわかんない。 「あ」に0が含まれるのは9/20に対して9/20だからわかるけど、 「う」は9/20に対して9/18だから、-2か""なんじゃないの？？ 下記の例は0より大きいで作ってあります。
練習用に作成
Sub タケノコプター()
Dim 商社一覧, 商社 As String
Dim 商社範囲 As String, 超過範囲 As String
Dim 出力
Dim APP As Application
Dim i As Long
Set APP = Application
商社一覧 = APP.Transpose(APP.Transpose(Filter(APP.Transpose( _
[IF(IF(ROW(1:1000),COUNTIF(OFFSET(Sheet1!E2:E1000,,,ROW(1:1000)),Sheet1!E2:E1000))=1,Sheet1!E2:E1000,"-")] _
), "-", False)))
商社範囲 = "Sheet1!$E$1:" & Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Address
超過範囲 = Evaluate("SUBSTITUTE(""" & 商社範囲 & """,""$E$"",""$F$"")")
With Sheets.Add(after:=Sheets(Sheets.Count))
.Range("A2").Resize(UBound(商社一覧)).Value = APP.Transpose(商社一覧)
For i = 1 To UBound(商社一覧, 1)
商社 = 商社一覧(i)
On Error Resume Next
出力 = APP.Transpose(APP.Transpose(Filter(APP.Transpose( _
Evaluate("IF(" & 商社範囲 & "=""" & 商社 & """,IF(" & 超過範囲 & ">0," & 超過範囲 & ",""-""),""-"")")) _
, "-", False)))
If Err = 0 Then .Cells(i + 1, "B").Resize(, UBound(出力)).Value = 出力
On Error GoTo 0
Next i
End With
End Sub
（稲葉） 2014/09/03(水) 10:03
Sheet2の
B2 =IF(COUNTIF(Sheet1!$E:$E,$A2)<COLUMN(A1),"",INDEX(Sheet1!$F:$F,SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*10^16+ROW(A$2:A$100),),COLUMN(A1))))
※A列も自動で出すの？ （GobGob） 2014/09/03(水) 10:21
※Excel2003でしたね。。。。勘違いで解答削除しました。 （GobGob） 2014/09/03(水) 10:31
HANAさん、GobGobさんありがとうございます。
説明が足りませんでした。 １，Sheet1の「納期超過日数」については、納期通りor納期より早く納入された物は”Good”なので数字は出していません。 ２，別Sheet(Sheet2)の超過日数の表示は”Good”の分も表示したいです。(何回納品があって何回”Good”が有ったかも見たい) ３，商社名はすでに入力済で日数だけ自動表示させたいです。
お二方のを試行した所、HANAさんのは『#NAME』というｴﾗｰが出ました。 GobGobさんのは数字を拾ってくれましたが、説明が不足していたせいで上記補足説明２，が有りませんでした。 ”Good”の分も”0”として表示させたいです。
よろしくお願いします。
(のBI太)
2003でしたね、すみません。
SUMIFS(Sheet1!$F:$F,Sheet1!$G:$G,$A2&B$1) の所が SUMPRODUCT(N(Sheet1!$G$1:$G$100=$A2&B$1),Sheet1!$F$1:$F$100) でどうでしょう。 （HANA） 2014/09/04(木) 15:28
HANAさん早々のご対応ありがとうございます。 結果としてはGobGobさんと同じで”Good”の分の”0”が表示されないです。
よろしくお願いします。
（のBI太） 2014/09/04(木) 16:16
何も制限かけてないので、Sheet1のF列に入力が無ければ「0」が表示されるはずですが。
Sheet1のG2セルは「あ1」となっていますか？ そして、Sheet2のB2セルはどの様になっていますか？ 別の値が表示されているとか、何も表示されていない とか。。。
もしかして、 シートの設定で「0」を表示しない設定にしてあったり 条件付き書式で「0」の文字色を変えていたり しませんか？
直接セルに「0」を入力したら、きちんと表示されますか？ （HANA） 2014/09/04(木) 16:30
文字列になるけど
B1 =IF(COUNTIF(Sheet1!$E:$E,$A2)<COLUMN(A1),"",TEXT(INDEX(Sheet1!$F:$F,SMALL(INDEX((Sheet1!$E$2:$E$100<>$A2)*10^16+ROW(A$2:A$100),),COLUMN(A1))),"標準"))
※HANAさんの一番最初の解答。SUMIFSをSUMIFにするだけ？
＞B2セルに =IF(COUNTIF(Sheet1!$E:$E,$A2)<COLUMN(A2),"",SUMIFS(Sheet1!$F:$F,Sheet1!$G:$G,$A2&B$1))
=IF(COUNTIF(Sheet1!$E:$E,$A2)<COLUMN(A2),"",SUMIF(Sheet1!$G:$G,$A2&B$1,Sheet1!$F:$F))
（GobGob） 2014/09/05(金) 13:26
HANAさん、GobGobさん、しょーもないミスしてました。 ワードで投稿用の表を作成して、回答を頂いてからExcelへコピペして試行用の表を作成する時に うっかり空白を削除漏れしてました。 ご迷惑をお掛けして申し訳ありませんでした。
空白を削除したらどちらのやり方でも最初の数式できれいに表示されました。
本当にありがとうございました。
（のBI太） 2014/09/05(金) 16:08
一度完成したリストですが新たに発注中で未納品も表示が必要になりました。 又、一覧表へも納期の遵守率(納期どれだけ守っているか)を表示する事になりました。 (見本のリストへ追加しました)
教えていただいた関数のままだと未納品分も"0"として表示してしまう為、見本の一覧表通りになりません。 未納品分は表示しない様にするには教えていただいた関数式へ条件設定するのだと思いましたが分かりません。
どの様にすればよいか教えて下さい。
（の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 9/25 A 2 あ 15
8 9/30 9/26 G 2 い
↓
まだまだ続きます。
A B C D E F 1 商社 超過1 超過2 超過3 超過4 超過5 →まだまだ続きます 2 あ 0 2 15 3 い 5 4 う 0 18
・・・ 質問表を修正しちゃうと以降の回答の辻褄が合わなくなるよ。 他の人が参考でみたらこんがらがっちゃうね。
ってことで、今後は修正でなく「新たな発言で追加」にしたほうがいいよん。 （GobGob） 2014/10/16(木) 12:09
たとえば。
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 あ 1 ← ？ 10 10/6 B 5 い
こんなこともありえるの？ （途中に未納発生） （GobGob） 2014/10/16(木) 13:06
初めて追加質問したので要領が分かりませんでした。 GobGobさんご指導通り有り難うございます。
GobGobさんのご質問に有ります様に、取引先の都合で途中に未納が発生します。
（のBI太） 2014/10/16(木) 15:13
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列以降の結果は文字列 （GobGob） 2014/10/16(木) 16:38
GobGobさん有り難うございます。 見本のリストで実施すると完璧でした。 ですが実際のリストはおまけの列が有って実際は下記の位置に必要なセルが有ります。 式内の列記号を色々いじってみましたがうまく表示できません。 すみませんがどこを直せばよいか教えて下さい。
A,B C D E,F,G H I J K,L,M,N,O,P Q R,S,T,U,V,W,X Y
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 あ 1
10 10/6 B 5 い
又、超過日数の「平均」と「最大」も出来れば表示したいと思ったのですが、『結果は文字列で表示』という事は不可能なんでしょうか?
↓ ↓
A B C D E F G H I
1 商社 平均 最大 遵守率 1 2 3 4 5 →まだまだ続きます
2 あ 5.67 15 33% 0 2 15
3 い 5.00 5 100% 5
4 う 9.00 18 50% 0 18
（のBI太） 2014/10/17(金) 16:05
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.