エクセルでこの抽出（？）はできますか？

質問

以下のことを実現したいのですが、良いやり方があれば教えていただきたいのですが。
シート1（在庫一覧表）　　　　シート2（日報）
｜A｜あ｜2｜　　　　　　　　　｜A｜う｜2｜
｜A｜う｜2｜　　　　　　　　　｜B｜こ｜3｜
｜B｜こ｜3｜　　　　←　　　　｜A｜あ｜2｜
｜C｜さ｜1｜　　　　　　　　　｜B｜こ｜3｜
　　　　　　　　　　　　　　　　　　｜C｜さ｜1｜
　　　　　　　　　　　　　　　　　　｜A｜あ｜2｜
シート2に日報として、毎日作業したものを入力していきます。ここでいう、アルファベット、ひらがな、数字の、３項目の組み合わせで、1つの品物になります。
そのひとつの組み合わせを、重複なく、シート1に一覧として出したいのですが。
いつも決まった品物を作業する仕事ではないので、品物の数は限りなく増えていきます。
※新しい始めての品物だとしても、日報に入力すれば、自動的にシート1に追加されるように・・・
アクセスを使えば良いのかもしれないのですが、パソコンにも不慣れな人も多数使用することになるので、シートを切り替えるだけで全体の流れがわかるようにしたいのです。
いろいろ調べたのですが、ぴったりのやり方が見つからなかったため、質問します。どうかよろしくお願いします。

回答No.6

nishi6 · ベストアンサー

ＰＣが復旧しました。補足について、少しコメントを・・・・

補足の内容からするとExcel向きの処理ではないと思います。１対１００位でAccess等のデータベースソフトを使用すべきでしょう。
入力などは専用のフォームを作れば対応できますし、必要なデータを抽出したり一覧表なども簡単に作成できます。それを目的にしたソフトですから当然でしょう。

近ごろは、Accessでデータ管理、抽出等を行い、Excelで製表したりユーザーにデータとして提供しています。これを全てExcelで行うことも可能でしょうがデータ管理の面ではExcelではやりたくないのが実感です。入出力、抽出等はかなりの作りこみが必要になってしまいます。

Excelのデメリットとしては、入出力の作りこみが必要だったり、ソートやマージの問題、普通は計算(四則演算やVlookUpなど)をデータ単位(行単位)に作る必要があることですね。入力の時に過去のデータがそのBookにあったりしてExcel自体が重くなり操作性も悪くなってきます。

データベースの解説本とかには最初に在庫管理があったりしませんか？最初は簡単な例からはじめていけば、お望みのものが完成できると思います。

参考に書いてみました。がんばって下さい。

お礼

遅くなりましたが、本当に何度もありがとうございます。
やっぱりそうですか～。今でも、予測して必要な大きさの表をまず作ってみたのですが、かなり重くて、一つの再計算をするのに３分ほどかかってしまいます。
とりあえず今は、これで、手動計算にしてしのごうと思います。
その間に、アクセス勉強して、がんばって作りたいと思います。
本当にありがとうございました！！！

回答No.5

hirono_ta

ようやく何がやりたいのかわかってきたような気がします。
｜商品コード１｜商品コード２｜商品コード３｜…｜数量｜…
｜文字列　　　｜文字列　　　｜文字列　　　｜…｜数値｜…
つまり、上のような見出しのリストがあって、商品コード１・２・３をまとめて検索キーにして、キーが重複することなく数量の合計を得たいということですよね？
それならば、フィルタオプションよりもピボットテーブルを使った方がいいですね。

まず、Sheet2で[データ]→[ピボットテーブルとピボットグラフ レポート]を選択。ピボットテーブル/ピボットグラフウィザードが開きます。
「Excelのリスト/データベース」「ピボットテーブル」にチェックが入っていることを確認し[次へ]をクリック。
Sheet2の日報のデータの入っている部分を指定して[次へ]をクリック。
（データの入っている範囲が自動的に選択されるはず）
ピボットテーブルの作成先をSheet1!$A$1と選択して[完了]をクリック。
Sheet1にピボットテーブルが作成され、ピボットテーブルツールバーが表示されます。
ピボットテーブルツールバーから商品コード１・商品コード２・商品コード３を順に行のフィールドへドラッグします。
数量をピボットテーブルの左上のフィールドへドラッグします。
すると、小計を含めたピボットテーブルが自動的に出力されますから、小計の部分を右クリックして[表示しない]を選択します。
下記のような表が出来上がったはずです。（表は等幅フォントで見て下さい）
┌────────────────────┬───────┐
│合計 : 数量 │ │
├──────┬──────┬──────┼───────┤
│商品コード１│商品コード２│商品コード３│ 計 │
├──────┼──────┼──────┼───────┤
│A │あ │2 │Aあ2の数量合計│
│ ├──────┼──────┼───────┤
│ │う │2 │Aう2の数量合計│
├──────┼──────┼──────┼───────┤
│B │こ │3 │Bこ3の数量合計│
├──────┼──────┼──────┼───────┤
│C │さ │1 │Cさ1の数量合計│
├──────┴──────┴──────┼───────┤
│総計 │総計 │
└────────────────────┴───────┘
数量ではなくデータの個数が必要であれば、左上「合計：数量」の部分をダブルクリックして、ピボットテーブルフィールドから「データの個数」を選択。すると、表は下記のようになります。
┌────────────────────┬────────┐
│データの個数 : 数量 │ │
├──────┬──────┬──────┼────────┤
│商品コード１│商品コード２│商品コード３│ 計 │
├──────┼──────┼──────┼────────┤
│A │あ │2 │Aあ2のデータ個数│
│ ├──────┼──────┼────────┤
│ │う │2 │Aう2のデータ個数│
├──────┼──────┼──────┼────────┤
│B │こ │3 │Bこ3のデータ個数│
├──────┼──────┼──────┼────────┤
│C │さ │1 │Cさ1のデータ個数│
├──────┴──────┴──────┼────────┤
│総計 │総計 │
└────────────────────┴────────┘
マクロで自動化も出来ますが、わたしもVBAを勉強中の身で、いまいち上手く組めないので、それは他の方におまかせします。

お礼

ありがとうございます！！
今までほとんどピボットテーブル使ったことがなかったのですが、こういう風に使えるんですね。このやり方を自動化できればこれもすごく見やすい表になると思いました。
しかし、自動化・・・今の私もできないので、マクロの記録で試してみようかと思いますが・・・。

補足

あと、具体的な方法の質問ではないのですが、もし、このような日々いろんなものが入庫し、（しかし、いつも違うものとも限定できないところが微妙）すぐに在庫がなくなるものもあればず～っとあるものもある。取引先、品物も限定されていないので品物にＩＤも付けられず・・・という在庫管理を、”詳しく知らない人”でも日報を入力したり、簡単に見たい品物のデータを閲覧したりということはアクセスの方が向いているのかということが知りたいのですが。（もちろん、がんばって設計すれば・・という前提ですが）
使い方がわがままなので、使う人が勉強してできるようになるというのが一番いいとはわかっているのですが、教えていただけたら・・・と思います。

回答No.4

nishi6

補足のとおりにしてみました。
シート３を作業用シートにして最終的にシート１にデータを移しています。
シートの構造が分かりませんので、シート１とシート２の対象３列の位置をモジュールに登録してください。最初の４行です。
それとシート名１，２，３は実際のシート名にしてください。Set の３行です。

マクロは、メニューから、ツール→マクロ→Visual Basic Editor でVBE画面に移り、
そのメニューから挿入→標準モジュールで標準モジュールを挿入し下記マクロを貼り付けます。

タイミング？はシート1に抽出したい時ですが・・・（質問の意味が分かっていない？）
頑張ってください。
なお、今から帰宅しますが自宅ＰＣの調子が悪いし、旅行に出かけますのでしばらく連絡できません。ご了承ください。

Public Sub FilterCopy()
　　Const s2Col1 = "F" '日報 対象3列の最初の列
　　Const s2Col3 = "H" '日報 対象3列の最後の列
　　Const s1Col1 = "B" '在庫管理表 対象3列の最初の列
　　Const s1Col3 = "D" '在庫管理表 対象3列の最後の列

　　Dim ws1 As Worksheet 'シート1
　　Dim ws2 As Worksheet 'シート2
　　Dim ws3 As Worksheet 'シート3
　　Dim rowMax As Long 'シート2の使用行数
　　　　Set ws1 = Worksheets("Sheet1") '在庫管理表 B:Dが対象3列としてあります
　　　　Set ws2 = Worksheets("Sheet2") '日報 F:Hが対象3列としてあります
　　　　Set ws3 = Worksheets("Sheet3") 'ワーク

　　'シート2のデータ範囲をつかむ。シート3をクリアする
　　ws2.Activate: Range(s2Col1 & "1").Select
　　　　rowMax = Range(s2Col1 & "65536").End(xlUp).Row
　　　　ws3.Cells.Clear

　　'フィルタをかけて、結果をシート3にコピーする
　　ws2.Activate: Range(s2Col1 & "1").Select
　　ws2.Range(s2Col1 & ":" & s2Col3).AdvancedFilter xlFilterInPlace, , , True
　　ws2.Range(s2Col1 & "1:" & s2Col3 & rowMax).SpecialCells(xlCellTypeVisible).Copy
　　ws3.Activate: Range("A1").Select: ActiveSheet.Paste

　　'シート3をソートする
　　Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
　　　　, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:=xlGuess

　　'フィルタを解除する
　　ws2.ShowAllData

　　'シート1の対象3列をクリアする
　　ws1.Range(s1Col1 & "2" & ":" & s1Col3 & Range(s1Col3 & "2").End(xlDown).Row).ClearContents

　　'シート3のデータのみコピーする
　　ws3.Activate
　　ActiveCell.CurrentRegion.Select
　　Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1, 3).Select
　　Selection.Copy 'シート3のデータ部分だけコピー

　　'シート1に貼り付ける
　　ws1.Activate
　　Range(s1Col1 & "2").Select: ActiveSheet.Paste

End Sub

お礼

ありがとうございます！！
うまくできました。とてもうれしいです。
とても丁寧に書いていただき、本当にありがとうございます。勉強になりました。
私の作りたい表はもしかしたら、根本から改善すべき点があるのかもしれませんが、とりあえず、今、少しでも仕事が効率よくなる表ができました。
とても困っていたので助かりました。

補足

あと、具体的な方法の質問ではないのですが、もし、このような日々いろんなものが入庫し、（しかし、いつも違うものとも限定できないところが微妙）すぐに在庫がなくなるものもあればず～っとあるものもある。取引先、品物も限定されていないので品物にＩＤも付けられず・・・という在庫管理を、”詳しく知らない人”でも日報を入力したり、簡単に見たい品物のデータを閲覧したりということはアクセスの方が向いているのかということが知りたいのですが。（もちろん、がんばって設計すれば・・という前提ですが）
使い方がわがままなので、使う人が勉強してできるようになるというのが一番いいとはわかっているのですが、教えていただけたらと思います。

回答No.3

nishi6

質問の意味をはかりかねています。前半ではシートから絞り込むように思えますが、後半では過去のデータに日報のデータを追加するようにも思えます。
とりあえず、シート2のデータを重複なしにシート1にかき出すモジュールを作ってみました。追加も応用すれば簡単でしょう。
データはシート2のA～C列にあり、1行目には表題があると仮定しています。
ご参考に。

標準モジュールに貼り付けます。(Excel97で作成しました)

Public Sub FilterCopy()
　　Dim ws1 As Worksheet 'シート1
　　Dim ws2 As Worksheet 'シート2
　　Dim rowMax As Long 'シート2の使用行数
　　　　Set ws1 = Worksheets("Sheet1")
　　　　Set ws2 = Worksheets("Sheet2")

　　'シート2のデータ範囲をつかむ。シート1をクリアする
　　ws2.Activate: Range("A1").Select
　　　　rowMax = Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
　　　　ws1.Columns("A:C").ClearContents 'シート1のデータを消去

　　'フィルタをかけて、結果をシート1にコピーする
　　ws2.Activate: Range("A1").Select
　　ws2.Range("A:C").AdvancedFilter xlFilterInPlace, , , True
　　ws2.Range("A1:C" & rowMax).SpecialCells(xlCellTypeVisible).Copy
　　ws1.Activate: Range("A1").Select: ActiveSheet.Paste

　　'ソートする
　　Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
　　　　, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:=xlGuess

　　'フィルタを解除する
　　ws2.ShowAllData
　　ws1.Range("A2").Select
End Sub

補足

すみません、自分でもうまく説明できていないと思ったのですが・・・。
日報を入力する前に、在庫管理表に、新規の品物を入力していくとすると、抽出しなくても良いのですが、複数の人が入力する可能性（そうすると、すぐにはそれが新規のものかどうかわからない）があるのと、もちろん、ある程度操作できる人なら、一度入力したものかどうか調べられるのですができない人もいるということで、ただ日報に作業したものをどんどん入れていけば、一枚目の在庫管理表シートに重複のない３項目の組み合わせが出せれば、それを検索条件にして、その時点での総在庫数が出せるかと思ったのですが・・・。

こういうものを作るにはＶＢＡできないとな～と思い、まだはじめたばかりでうまく応用できず、もし、やっていただけるならば、少しお願いしたいことがあるのですが・・・。
記述してある内容がぼんやりとしかつかめていないので、もうそのようになっているのかもしれないのですが、
　※　３項目の列のみフィルタをかけて、抽出で、在庫管理シートの３列のみに貼りつけしてもらいたいのですが。（他のセルにはその抽出されたものを検索条件にして日報の出荷数、入荷数などを集計するように作っているのですが）
　※　あと、このマクロの設定のしかた（どこに設定して、どのタイミングで使えるのか）を教えていただけませんか？
もっと勉強した上で使うのが良いとは思うのですが、会社の都合上、早く完成しないと・・・という状況です。
パソコンの導入も始めたばかり、一定の決まった商品を扱うだけですまない（単発の仕事多し＝種類多し）人員も少ない小さな会社なので、既製のものが使えなく困っていました。勉強したてでつたない質問ですが、よろしくお願いします。

回答No.2

yohsshi

日報のシート（シート名：日報）の一番左に列挿入します。
１行目が見出しだとして、Ａ列２行目に以下の数式を入れます
＝Ｂ２ ＆ Ｃ２ ＆ Ｄ２
この場合、Ａ２の表示結果は“Ａう２”
という形となります。
これを全ての行にコピーします

在庫一覧表（シート名：在庫一覧表）の方も同様に一番左側に列挿入し
＝Ｂ２ ＆ Ｃ２ ＆ Ｄ２
を全ての行にコピーします

ここで票の右側Ｅ列に個数を表示することとし、Ｅ２式は
＝ＣＯＵＮＴＩＦ（日報!Ａ：Ａ，Ａ２）
これを全ての行にコピーすれば各商品の在庫が自動計算されます。

また、在庫一覧表に銘柄を登録し忘れている可能性がありますからこれをチエックするために、空いているセルに
＝ＩＦ（ＣＯＵＮＴＡ（日報!Ａ：Ａ）＝ＳＵＭ（在庫一覧表!Ｅ：Ｅ）,”ＯＫ”，”銘柄の未登録があります”）
を入れておくとチエックになると思います。

短時間で作るならばこの方法でしょうか？

補足

そうですね～。途中までの、＆を使ってそれぞれの在庫を計算するまではなんとかできていたのですが（このことで１週間ほど考えた末）、その後、考えすぎて、どうしても自動的に新しいものを認識して登録させなければ！！と頭が煮詰まっていたのですが、未登録のものがありますよ！と、知らせるという手もあったのですね・・・
最後に、短時間で作るならということですが、手間をかければ、まだもう少し何とかなるでしょうか？エクセルの範囲でも・・・

回答No.1

hirono_ta

同じシート内であればフィルタオプションを使えばできますが、そのやり方で構わないでしょうか？

補足

ありがとうございます。
同一シート内でもかまわないのですが、この方法を使うとすると、フィルタオプションの設定と、その作業をマクロに記録したりできれば、エクセルをわからない人でも使えるのでしょうか？
すいません、あまりフィルタオプションを使ったことがないので、とんちんかんな補足になってるでしょうか？