Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
データを別シートに抽出する
投稿日時: 19/10/26 11:38:27
投稿者: 桃太郎7

宜しくお願いします。
入力データシート1からID指定データ内容をシート2へ一覧表示させたいです。
 1)Vlookupでシート1にデータ入力データを A2からA200までを対象にパート時給計算をしてます。
 【例題】
    A B C   D   E   F    G H I J K L M
 1 ID 名前 日付 出勤 退社 10時休憩 昼休憩 15時休憩 勤務時間 分換算 実働時間 時給 支払
 2 1 A 10/26 8:00 17:20 0:15 1:00 0:15 9:19 559 469 840 6566
 3
 4
 5
 .
 .
200
  以上がシート1のVlookupデータです。
 
 お尋ねしたい例題は、シート2へ1シートからデータ 「ID1」すべてを対象範囲 2行〜200までを
 抽出してシート2へコピーをしたいです。勿論シート2はシート1と同じ表です。
 
 作成したい内容 1)パートさん別勤務表 2)月度パートさん勤務明細一覧表
 
 現在の状況は、シート1の入力は出来ています 例題
 現在の問題点、私なりにシート2へVlookupでA2を求めましたら頭1行目しか表示されません。
        A3にA2行をコピーしたらA2行目と同じで、「ID1」の2行目以降入力16行あるが表示なし
 
               例題表の書き方が悪くて列番間隔が出来ませんので見にくいですが宜しくお願いします
    質問の仕方に不十分な点がありましたら追加説明を致しますので宜しくご指導ください。
 
     
        
 

回答
投稿日時: 19/10/26 11:51:22
投稿者: WinArrow
投稿者のウェブサイトに移動

VLOOKUP関数では指定した検索キーの1件目だけ対応します。
関数で対応するのは、工夫が必要です。
 
IDが複数件ある場合は、
オートフィルタを使いましょう。
この方が楽です。

回答
投稿日時: 19/10/26 12:24:43
投稿者: んなっと

 [データ]
→[テーブルまたは範囲から]
→PowerQueryが起動するのするので、[すぐに閉じて読み込む]
→日付と時刻の表示形式を設定しなおす
 
としてからフィルターやスライサーを使えば、別シートに簡単に抽出できます。

投稿日時: 19/10/26 14:02:07
投稿者: 桃太郎7

WinArrow さんの引用:
VLOOKUP関数では指定した検索キーの1件目だけ対応します。
関数で対応するのは、工夫が必要です。
 
IDが複数件ある場合は、
オートフィルタを使いましょう。
この方が楽です。

*******************************
ご回答ありがとうございます。
すみません!説明不足で申し訳ないです。
入力フォームの作成・・・シート1をオートフィルでID指定してデータを求めシート2へコピー貼り付け
            この操作をマクロ登録してボタンでデータ取得貼り付けをしています。
  問題1、データクリアして追加データを再度マクロボタンで実行すると前回のデータで表示される。
  問題2 オートフィルの検索範囲を、A2:A200までの中から抽出が出来ないでしょうか?
  問題3 シート2データをクリアしてマクロ登録でA2:A200までのID1を貼り付けしたいです
     現在は、データが入力されている実績をマクロ登録は出来まして、その後追加データ入力を
     した部分が表示しない・・・・・データがあるすべてを検索コピー貼り付けしたい

回答
投稿日時: 19/10/26 15:03:19
投稿者: んなっと

現状はこんな感じですか?
 
●Sheet1
 
   A   B   C   D   E
 1 ID 名前  日付 出勤  退社
 2  1   A 10/26 8:00 17:19
 3  2   B 10/26 8:00 17:32
 4  3   C 10/26 8:00 17:49
 5  4   D 10/26 8:00 17:32
 6  5   E 10/26 8:00 17:25
 7  1   A 10/27 8:00 17:15
 8  2   B 10/27 8:00 17:21
 9  3   C 10/27 8:00 17:20
10  4   D 10/27 8:00 17:58
11  5   E 10/27 8:00 17:09
12  1   A 10/26 8:00 17:19
13  2   B 10/26 8:00 17:32
14  3   C 10/26 8:00 17:49
15  4   D 10/26 8:00 17:32
16  5   E 10/26 8:00 17:25
 
 ↓フィルター
 
   A   B   C   D   E
 1 ID 名前  日付 出勤  退社
 2  1   A 10/26 8:00 17:19
 7  1   A 10/27 8:00 17:15
12  1   A 10/26 8:00 17:19
 
 ↓マクロ
 
●Sheet2
 
  A   B   C     D   E   F
1 ID 名前 日付 実働時間 時給 支払
2  1                  ←VLOOKUP関数でB列から右を取得
3  1                  ←ここから下が問題
4  1                 
 
...VBAが苦手なら、先に書いたPowerQueryを使いましょう。
 PowerQueryエディタで不要な列の削除などもできます。

投稿日時: 19/10/26 15:26:25
投稿者: 桃太郎7

んなっと さんの引用:
現状はこんな感じですか?
 
●Sheet1
 
   A   B   C   D   E
 1 ID 名前  日付 出勤  退社
 2  1   A 10/26 8:00 17:19
 3  2   B 10/26 8:00 17:32
 4  3   C 10/26 8:00 17:49
 5  4   D 10/26 8:00 17:32
 6  5   E 10/26 8:00 17:25
 7  1   A 10/27 8:00 17:15
 8  2   B 10/27 8:00 17:21
 9  3   C 10/27 8:00 17:20
10  4   D 10/27 8:00 17:58
11  5   E 10/27 8:00 17:09
12  1   A 10/26 8:00 17:19
13  2   B 10/26 8:00 17:32
14  3   C 10/26 8:00 17:49
15  4   D 10/26 8:00 17:32
16  5   E 10/26 8:00 17:25
 
 ↓フィルター
 
   A   B   C   D   E
 1 ID 名前  日付 出勤  退社
 2  1   A 10/26 8:00 17:19
 7  1   A 10/27 8:00 17:15
12  1   A 10/26 8:00 17:19
 
 ↓マクロ
 
●Sheet2
 
  A   B   C     D   E   F
1 ID 名前 日付 実働時間 時給 支払
2  1                  ←VLOOKUP関数でB列から右を取得
3  1                  ←ここから下が問題
4  1                 
 
...VBAが苦手なら、先に書いたPowerQueryを使いましょう。
 PowerQueryエディタで不要な列の削除などもできます。

 
**************************************************************************
ご回答ありがとうございます。
>>[quote="んなっと"]現状はこんな感じですか?
 
現在のシート内容です。・・・・ ←ここから下が問題・・・・
これを解決したいと思っています
PowerQueryエディタは初めてのアプリですから勉強をして見ます。
結果報告を後程致しますので宜しくお願いします。
 
 
 
 
 
 
 
 
 
 

回答
投稿日時: 19/10/26 15:42:36
投稿者: んなっと

こんな方法も。
 
●Sheet1 右のほうあいているところ(下の例ではN列)に作業列
 
N2
=SUM(N1,SUBTOTAL(3,A2))
下方向・↓
 
   A   B   C   D   E   F   G   H   I  J   K   L   M  N
 1 ID 名前  日付 出勤  退社 10休 昼休 15休 勤務  分 実働 時給 支払 No.
 2  1   A 10/26 8:00 17:19 0:15 1:00 0:15 9:19 559  469  840 6566  1
 3  2   B 10/26 8:00 17:32 0:15 1:00 0:15 9:32 572  482  841 6756  2
 4  3   C 10/26 8:00 17:49 0:15 1:00 0:15 9:49 589  499  842 7003  3
 5  4   D 10/26 8:00 17:32 0:15 1:00 0:15 9:32 572  482  843 6772  4
 6  5   E 10/26 8:00 17:25 0:15 1:00 0:15 9:25 565  475  844 6682  5
 7  1   A 10/27 8:00 17:15 0:15 1:00 0:15 9:15 555  465  845 6549  6
 8  2   B 10/27 8:00 17:21 0:15 1:00 0:15 9:21 561  471  846 6641  7
 9  3   C 10/27 8:00 17:20 0:15 1:00 0:15 9:20 560  470  847 6635  8
10  4   D 10/27 8:00 17:58 0:15 1:00 0:15 9:58 598  508  848 7180  9
11  5   E 10/27 8:00 17:09 0:15 1:00 0:15 9:09 549  459  849 6495  10
12  1   A 10/26 8:00 17:19 0:15 1:00 0:15 9:19 559  469  840 6566  11
13  2   B 10/26 8:00 17:32 0:15 1:00 0:15 9:32 572  482  841 6756  12
14  3   C 10/26 8:00 17:49 0:15 1:00 0:15 9:49 589  499  842 7003  13
15  4   D 10/26 8:00 17:32 0:15 1:00 0:15 9:32 572  482  843 6772  14
16  5   E 10/26 8:00 17:25 0:15 1:00 0:15 9:25 565  475  844 6682  15
 
  ↓フィルター
 
   A   B   C   D   E   F   G   H   I  J   K   L   M  N
 1 ID 名前  日付 出勤  退社 10休 昼休 15休 勤務  分 実働 時給 支払 No.
 2  1   A 10/26 8:00 17:19 0:15 1:00 0:15 9:19 559  469  840 6566  1
 7  1   A 10/27 8:00 17:15 0:15 1:00 0:15 9:15 555  465  845 6549  2
12  1   A 10/26 8:00 17:19 0:15 1:00 0:15 9:19 559  469  840 6566  3
 
 
●Sheet2
 
  A   B   C   D   E   F
1 ID 名前  日付 実働 時給 支払
2  1   A 43764  469  840 6566
3  1   A 43765  465  845 6549
4  1   A 43764  469  840 6566
 
A2
=IFERROR(INDEX(Sheet1!$1:$200,MATCH(ROW(A1),Sheet1!$N:$N,0),MATCH(A$1,Sheet1!$1:$1,0)),"")
右方向・→下方向・↓

回答
投稿日時: 19/10/26 15:47:52
投稿者: んなっと

Sheet2の構成次第では、ピボットテーブルもあるかもしれません。

回答
投稿日時: 19/10/26 15:50:36
投稿者: んなっと

今回はSheet2の具体例が示されなかったのがよくなかったと思います。
↓これではまるで不十分でした。

引用:
作成したい内容 1)パートさん別勤務表 2)月度パートさん勤務明細一覧表

 
今後気を付けてください。

回答
投稿日時: 19/10/26 17:30:27
投稿者: WinArrow
投稿者のウェブサイトに移動

>入力フォームの作成・・・シート1をオートフィルでID指定してデータを求めシート2へコピー貼り付け
            この操作をマクロ登録してボタンでデータ取得貼り付けをしています。
 
このマクロの明代があると思います。
 
マクロの記録で作成したコードには、汎用性がありません。
例えば、データ行は可変であるが、マクロの記録では固定のセル範囲になっています。
 
この辺りををカスタマイズしないと、実用に適さないと思います。
 
>1)パートさん別勤務表 2)月度パートさん勤務明細一覧表
このような回答者に理解できない説明は不要です。

回答
投稿日時: 19/10/26 18:35:27
投稿者: んなっと

とにかく「データを別シートに抽出する」ときは、まずはPowerQueryです。
それほど優れています。
 
細かい不満点があるときに、別の方法を考えていきましょう。

投稿日時: 19/10/27 17:36:01
投稿者: 桃太郎7

んなっとさん ご回答ありがとうございます。
>まずはPowerQueryです。
 
スライサーの挿入でスライサーが出来ました、ID番号を1:10まで作成しましたのでスライサーのID1を
クリックすると、ID1のデータを絞り込みができています。
 
ここで質問ですが、Sheet2 へこのID1のデートを貼り付けるにはそのつどコピー貼り付けをするのですか
マクロでボタンクリックして、Sheet3、Sheet4へとボタンでコピーをする方法は出来ないですかねー!
やはり!マクロ操作では無理ですかね、また他の方法があれば教えてください。
 
 未熟な私ですが、データ抽出〜コピー〜貼り付けを簡単に出来ないものかと思いまして!
 
 宜しくお願いします

投稿日時: 19/10/27 21:49:21
投稿者: 桃太郎7

んなっと さんの引用:
とにかく「データを別シートに抽出する」ときは、まずはPowerQueryです。
それほど優れています。
 
細かい不満点があるときに、別の方法を考えていきましょう。

**********************************************************************
別の方法についてご指導ください。
PowerQueryで抽出したデータを別Sheet2へコピーできる方法を教えてください。
 
宜しくお願いします。

回答
投稿日時: 19/10/27 23:28:54
投稿者: んなっと

あれもこれもと欲張って質問しないように。
いったんスレッドを閉じましょう。

回答
投稿日時: 19/10/28 06:03:42
投稿者: んなっと

引用:
PowerQueryで抽出したデータを別Sheet2へコピーできる方法を教えてください

これだけ回答しておきます。
「閉じて読み込む」

投稿日時: 19/10/28 09:37:03
投稿者: 桃太郎7

ご回答ありがとうございました。
 あとは、皆さんからのご指導を自分で参考にして解決します。