- ベストアンサー
エクセルのフィルタオプションで注文増加分を抽出する方法
- エクセルのフィルタオプションを使用して、特定の時間帯に注文された増加分のデータを抽出する方法を紹介します。
- 注文データのスナップショットを含むエクセルファイルに対して、特定の時間帯の注文増加分のみをフィルタリングして表示することができます。
- 詳しい手順としては、フィルタオプションを開き、日付と時間の列に対して条件を指定し、増加分のデータのみを表示させることができます。
- オフィス系ソフト
- 回答数7
- ありがとう数2
- みんなの回答 （7）
- 専門家の回答
質問者が選んだベストアンサー
たびたびすみません。レポート表作成の原案ですがデータの吸い上げは日に2回を前提にし、一回目と二回目を比較し、その増加件数を表示するものです。 元データはSheet1、レポートはSheet2外日付ごとに作成します。 Sheet1のＡ列からＥ列まではフィルターと同じです。 Ｆ２に =IF(B2=B1,IF(C2=C1,F1,"(2)"),"(1)") Ｇ２に =CONCATENATE(B2,F2) Ｈ２に =C2 と入力後全行にコピーしてください。 次にレポート表です。 ヘッダーとして、Ｂ２に“日付” Ｃ２に“から” Ｄ２に“まで” その下の行 Ｂ3には抽出対象日を20080602（これは任意のセル） Ｃ３に =VLOOKUP(CONCATENATE($B$2,"(1)"),Sheet1!G:H,2,0) Ｄ３に =VLOOKUP(CONCATENATE($B$2,"(2)"),Sheet1!G:H,2,0) 抽出データ表示欄として Ａ列にレポート用の名称、Ｂ列に増加件数、印刷しない集計用として Ｅ列にＡ列に対応する名称、Ｆ列に検索用文字列を作成します。 Ｂ５に=COUNTIF(Sheet1!E:E,CONCATENATE($B$2,$D$2,E5))-COUNTIF(Sheet1!E:E,CONCATENATE($B$2,$C$2,E5)) Ｆ５に =CONCATENATE($B$2,$D$2,E5) と入力して全行コピーです。 結果 Ａ Ｂ Ｃ Ｄ Ｅ Ｆ １ 日付 から まで ２ 20080602 085500 0910000 ３ ４ 名称 件数 ５ ○○○ １ おもちゃ… 2008060… ６ ××× ２ おもちゃ… 2008060… ７ △△△ ０ 日用品 洗剤 2008060… ８ ☆☆☆ ０ 日用品 ゴミ袋 2008060… ～ ～ という様なイメージです。
その他の回答 (6)
- yukz8sss
- ベストアンサー率52% (9/17)
No3です。 ごめんなさい。記入誤りでした。私のテストデータの関数をそのまま張り付けたためエラーですね。列を確認しましょう。 例題では A B C D No 日付 時間 品名 ですから Ｅ列が =CONCATENATE(B2,C2,D2) Ｆ列が =IF(E2=E1,F1+1,1) Ｇ列が =IF(C2="091000",IF(F2>COUNTIF(E:E,CONCATENATE(B2,"085500",D2)),1,0),0)です。大変失礼しました。 それと、時間が日によって異なると使えません。必ず８時台と９時台とというようにすればよろしいですかね。長くなりますのでとりあえず訂正のみにさせてください。 も一つ、レポートとしてお使いのようなら、フィルターよりも集計表のほうがよろしいかもしれませんね。
- yukz8sss
- ベストアンサー率52% (9/17)
No3です。 補足ありがとうございます。 例題でいうところの”品名”が多いのであればフィルターのほうが良いかもしれませんね。 入力が簡単で、ほかの方が見てもわかり易いようにと、考えてみましたので、お試しください。 まずE列に =CONCATENATE(A2,B2,C2) と入力し全行コピーし、昇順にソート、 F 列に =IF(D2=D1,E1+1,1) G 列に =IF(B2="091000",IF(E2>COUNTIF(D:D,CONCATENATE(A2,"085500",C2)),1,0),0) と入力し全行コピーします。 後は、Ｇ列の”１”をフィルターすれば抽出できそうですが、いかがでしょうか。 30件ほどでテストしてみましたがＯＫでした。 Ｅ、Ｆ列を見えなくしておけば、見栄えも悪くないと思いますが、データの作成時間が3つ以上あると対応できませんね。 お気づきの点がありましたら、補足してください。
補足
ありがとうございます。 データの作成時刻なのですが、3つ以上あります…。 というのも、実行日が同じであれば秒単位まで一致するのですが 実行日が異なると、秒数が微妙に変わってきます。 20080610 は 8:55:02と9:10:05 20080611 は 8:55:05と9:10:07 というようにです。 同じ日付内で変わると言う事はありません。 先に補足しておくべきでした。 申し訳ありません。 また、=IF(D2=D1,E1+1,1)の部分なのですが、 D2=D1が真となるときE1+1が全て『＃VALUE!』となります。 これはこのまま無視してよいのでしょうか？
次を試してみて、不具合があればお知らせください。 下のデータ例ではＯＫだが、他データの場合ＮＧとなるなら、該当する他データを示してください。 A B C D E 1 年月日 時刻 種類 品物 増分 2 20080601 085500 おもちゃ ミニカー 3 20080601 091000 おもちゃ ミニカー 4 20080602 085500 おもちゃ ミニカー 5 20080602 091000 おもちゃ ミニカー 6 20080602 091000 おもちゃ ミニカー ○ 7 20080602 091000 おもちゃ プラモデル ○ 8 20080603 085500 日用品 洗剤 9 20080603 091000 日用品 洗剤 10 20080603 091000 日用品 ゴミ袋 ○ 11 20080603 091000 おもちゃ プラモデル ○ E2: =IF(B2="091000",IF(OR(AND(SUMPRODUCT((A$1:A1=A2)*(B$1:B1="085500")*(C$1:C1=C2)*(D$1:D1=D2))=1,SUMPRODUCT((A$1:A1=A2)*(B$1:B1="091000")*(C$1:C1=C2)*(D$1:D1=D2))=1),AND(SUMPRODUCT((A$1:A1=A2)*(B$1:B1="085500")*(C$1:C1=C2)*(D$1:D1=D2))=0,SUMPRODUCT((A$1:A1=A2)*(B$1:B1="091000")*(C$1:C1=C2)*(D$1:D1=D2))=0)),"○",""),"")
お礼
なるほど。 増分に対して○をつけておいて、オートフィルタで絞ってやれば 求める一覧が出せそうですね。 ちょっとやってみます。 ありがとうございました。
- yukz8sss
- ベストアンサー率52% (9/17)
はじめまして。 excel表のデータから、指定日付の、指定する品目の、ある時間からある時間の間に 増加したデータを抽出したいと理解しました。 その場合増加した件数だけでよろしければ、COUNTIF関数等で出せると思いますが、 恐らく例題のように一覧にしなければならないのでしょうから、もう少し考えてみます。 ただ、実際の使用状況がよくわかりませんので、少し補足していただきたいのですが、 対象表のボリューム 日付ごとの表への分割の可否 増加分として抽出されるデータの最大件数 品名の件数 抽出した後の使用方法等です。 お仕事の中身がわかりませんが、仮に 5 20080602 091000 おもちゃ ミニカー というデータがずらずら表示されたら、その後どうなさるのかなと気になりましたもので 何とか解決しましょう。よろしかったら補足して下さい。
補足
＞対象表のボリューム 今のところ100列程度ですが、今後増えていく事が予想されます。 ＞日付ごとの表への分割の可否 最終的に理想の形になるのであれば、ワークスペースとして途中で違う形になるのは構いません。 ＞増加分として抽出されるデータの最大件数 元データ100行に対し、10件程度です。 こちらも元データの増加に伴い増えていく事が予想されます。 ＞品名の件数 これは多いです。 実は、一般の方が読んでイメージしやすいようにと、『商品の注文』という表現をしましたが、実際にはWeb系システムの性能レポート的なデータとなっています。 例で示した一覧表は、応答に4秒以上を要した性能の悪いプログラムを抽出したものです。 なので、おもちゃやミニカーなどといったデータ部分には、 EFOP001、ItOpenといった、プログラム名とリクエスト名と呼ばれるものが入ってきます。なので非常に多くの品目があります。 そして、抽出したあとの使用方法ですが、レポートとしてお客様に提出します。 お客様にしてみれば「EFOP001」などと言われても何の事であるかわかるはずも無いので、別のシートに予め用意してある、プログラム一覧からVLOOKUP関数で「ログイン処理」などの言葉に置き換えたいと考えています。 なので、件数だけでなく、「EFOP001」などのデータがどうしても必要なのです。 以上、よろしくお願い致します。
[ANo.1この回答への補足]が良く呑み込めてはいないのですが… 質問文中の「元のデータ」は何等かの規則でソートされているようにみえるのですが、「元のデータは以下のような感じで入」ることもありますか？ A B C D 1 20080601 085500 おもちゃ ミニカー 2 20080601 091000 おもちゃ ミニカー 3 20080602 085500 おもちゃ ミニカー 4 20080602 091000 おもちゃ プラモデル 5 20080602 091000 おもちゃ ミニカー 6 20080602 091000 おもちゃ ミニカー 7 20080603 085500 日用品 洗剤 8 20080603 091000 おもちゃ プラモデル 9 20080603 091000 日用品 ゴミ袋 10 20080603 091000 日用品 洗剤 ～
補足
わかりづらい文章しか書けず、申し訳ありません。 ご質問の件ですが、有り得ます。 B列の時刻はSQLを実行した時刻が入っていますが 元データとしては実際に注文が入った順に並んでいますので、 ミニカーよりもプラモデルのほうが先に注文されている場合は ご提示頂いたような表になります。 データをDBからcsvに落とし、エクセルで編集しています。 この際、特にソートはしていません。注文が入った順に、日付＞時刻で最初からソートされた状態です。 ちょっとわかりづらいようなので、簡単な例をお出しします。 A君は大きな袋を持っています。 A君が8時55分に袋を覗き見たところ『青い玉』が1つ入っていたので、それを以下のように書き留めました。 20080605,085500,青い玉 暫くして、9時10分にもう一度袋を覗くと、袋の中の状況は、青い玉が三つと赤い玉が2つになっていたので、先ほど書き留めた紙に状況を追記しました。 20080605,085500,青い玉 --------------------------------- (追記分) 20080605,091000,青い玉 20080605,091000,青い玉 20080605,091000,青い玉 20080605,091000,赤い玉 20080605,091000,赤い玉 ※書き込む順番は実際に袋に追加された順になります。 ※この例ではA君には追加された順番はわかる由もないので、ちょっと例としては上手くないです。すみません。 後ほど、書き込んだメモをみて、8時55分から9時10分の15分間での増加分だけを、違うメモに残したいと考えます。 20080605,085500,青い玉 20080605,091000,青い玉 20080605,091000,青い玉 20080605,091000,青い玉 20080605,091000,赤い玉 20080605,091000,赤い玉 メモはこのようになっています。 1行目の青い玉は8時55分時点で既にあった青い玉で、2行目の青い玉も同じもののはずですので増加分とはなりません。なので抽出対象は下から4行で 20080605,091000,青い玉 20080605,091000,青い玉 20080605,091000,赤い玉 20080605,091000,赤い玉 となります。 というような事がしたいのです。 最初のSQLでやれ、というのは大変もっともな意見なのですが、体制的な問題であまりやりたくないのです…。
「増加分」とは何に対して仰っているのですか？ 》 3,4のミニカーは相殺 》 よって増加分は 》 『2008年6月2日 9時10分におもちゃのミニカー 3、4、5 は同じデータですが、3 と 4 は「相殺」（この意味も不明）して、5 を「増加分」とするのですか？ 同じデータを相殺したり増加分としたりする規則が理解できません。
補足
分かりづらくてすみません。 増加分とは8時55分から9時10分の間に増えた注文済みデータのことです。 8時55分の段階で注文済み、となっているデータを取得します。 また、9時10分でも同様に取得します。 とすると、8時55分～9時10分に注文された増加分は 『9時10分に取得したデータには含まれているが、8時55分のデータには含まれていないもの』となります。 3 20080602 085500 おもちゃ ミニカー は、8時55分時点ですでに注文済みとDBに登録されているデータです。 また、 4 20080602 091000 おもちゃ ミニカー 5 20080602 091000 おもちゃ ミニカー 6 20080602 091000 おもちゃ プラモデル は9時10分時点で注文済みとなっているデータです。 3のデータが9時10分時点で注文済みとなっているのは当然なので これは8時55分から9時10分の間に注文済みとなったデータではないので 相殺（つまり抽出対象外）と表現しました。 3,4,5は一見同じデータに見えますが、 3は8時00分に注文済みとなったデータ、4も同じデータと推測できます。 ですが、5は8時55分時点に取得したデータには含まれていなかったので 8時55分以降、9時10分までの間に入った注文と推測できます。
お礼
凄い！ありがとうございました。 多少手を加えましたが、ほぼご教授頂いた内容で完成しました。 感謝です！