質問させていただきます。
バージョンは2003です。
一つのBOOK内に、マスターデータsheet(以下、マスター)が一つあります。2sheet目以降は同じフォーマットで個人毎の区分でsheet分け(以下、個人シート)しており、マスターから個人名にてデータを抽出し、個人シートに一覧表を作成したいと考えております。またレギュレーションとしては、BOOKを開いた時には自動で計算されていることです。
いろいろな関数で試しても構築できずに困っております。
またVBAについては完全に素人ですが、領域的にはVBAであろうと覚悟はしております。
関数でもVBAでも構いません。何卒ご教授くださいませ。
よろしくお願いいたします。
■マスターデータ
A B C
1鈴木 商品1 外注1
2山田 商品2 外注2
3田中 商品3 外注3
4鈴木 商品4 外注1
5鈴木 商品5 外注3
例えば“鈴木用”個人シートであれば、1,4,5行にある3データを個人シート上に一覧表として表示させたいです。
参考までに情報量としては、マスターデータでは29列×3000行。個人シートは100シート程度あります。
よろしくお願いいたします。
No.1ベストアンサー
- 回答日時:
マスタシート:
A B C D列
1 検索キー 名前 商品 項目
2 鈴木-0001 鈴木 商品1 外注1
3 山田-0001 山田 商品2 外注2
4 田中-0001 田中 商品3 外注3
5 鈴木-0002 鈴木 商品4 外注4
6 鈴木-0003 鈴木 商品5 外注5
:
A2:
=IF(B2="","",B2&TEXT(COUNTIF($B$2:B2,B2),"-0000")
以下コピー
鈴木シート:
A B C D
1 鈴木 3件
2 番号 名前 商品 項目
3 1 鈴木 商品1 外注1
4 2 鈴木 商品4 外注4
5 3 鈴木 商品5 外注5
B1:
=COUNTIF(マスタ!B:B,A1)
A3:
=IF(ROW(A1)>$B$1,"",ROW(A1))
以下コピー
B3:
=IF($A3="","",VLOOKUP($A$1&TEXT($A3,"-0000"),マスタ!$A:$AZ,COLUMN(),FALSE))
右にコピー,下にコピー
#マスタシートにどうしても絶対検索キー列を用意したくないときは,右端の邪魔にならない列なり別シートなりに作成しても構いませんが,その時はVLOOKUP関数は使えないのでINDEX(MATCH())関数などで代用します。
#メンバーシートのA1セルやB1セルは,このシートが「鈴木」のシートだと指示するためにと計算の高速化のために用意していますが,必要に応じて何なりと工夫して作成して構いません。
#想定データ数が多いので,良く寄せられるご相談ですが回答に出てくる配列数式では重すぎて,実用には適しません。
keithinさま
早速のお返事、どうもありがとうございました。
お陰様で解決いたしました。
本日朝から格闘していたものですから、ちょっと涙が出そうです。
本当にありがとうございました。
今回初めてOKWaveに登録&質問をさせて頂いたのですが、このように知識・技術の共有を実感させて頂けたことも新たな発見でした。
keithinさま、重くて実用には適さないとのことですが、諸々工夫してみたいと思います。
ありがとうございました。
No.2
- 回答日時:
簡単なマクロ(VBA)を体感してみて下さい。
先ずは個人シートの作成だけですが以下の手順になります。
マスタには見出し行ありとしています(見出し行が無い場合、先頭に挿入して下さい)
(1)仮に個人シートのA1にマスタの担当者列同様の文字列(仮に担当者)、A2に担当者名を入力
(2)ツール→マクロ→マクロの記録開始→ショートカットキー登録(後々の操作が楽になる)
(3)データ→フィルタ→フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にマスタシートの抽出列sheet1!A:C(とびとびは駄目です)、検索条件範囲欄に$A$1:$A$2,抽出範囲欄にA3→OK
(4)ツール→マクロ→マクロの記録終了
後は自動ではありませんが、各個人シートで(1)の操作とショートカットキー押下で個人別情報が抽出できます。
因みにalt+F11キー押下→VBE画面の欄にある標準モジュール下のModule1をダブルクリックで以下の様なコードが表示されます。
AdvancedFilterで検索すれば解説サイトがあります。
Sub Macro1()
Sheets("Sheet1").Columns("A:C").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:A2"), CopyToRange:=Range("A3"), Unique:=False
End Sub
mu2011
遅くにご回答頂いておりまして恐縮です。
ありがとうございました。
教えて頂いたことやってみました!
完璧に抽出できますね!
ありがとうございます!
ただこれだと、マスタの内容が変化した時に自動更新できないので、本件にては適用できないのですが、これはデータ量の多い情報処理などで非常に使いやすいテクニックだと思いますので、めちゃくちゃ助かります!今後是非使っていきたいと思います。
僕も今回の事を機にVBAの領域に踏み込もうと思っております。
また何か伺うことがあるかもしれませんが、
その際はよろしくお願いいたします。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) VBAで同フォルダ内の別ブックを開かず参照して条件の一致する行の指定セルを抽出するには? 1 2022/07/21 19:29
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- Excel(エクセル) Excelマクロの差分抽出のコードを教えていただきたいです。 2 2023/03/14 11:40
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- Excel(エクセル) Excel M365 データチェックの仕掛けを作りたい 2 2023/06/03 23:54
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「.movpkg」「.m3u8」ファイル...
-
YahooのIDがロックされてしまい...
-
ホームページビルダーのバージ...
-
ゆうちょ認証アプリが使えません
-
グーグルクロムにダウンロード...
-
拡張子が異なりファイル名が同...
-
Kritaについて
-
STEAM版BF4が起動しない
-
MP3Studio You tube Downloader...
-
中古のパソコン購入 オフィスの...
-
Windows Media PlayerでCDが焼...
-
写真の印刷レイアウトについて
-
Macのミュージックデータの格納...
-
Winrarの移行
-
先日からDigiBestTVで「デバイ...
-
CMYKのペイントソフトを探して...
-
jtrim 1.53cが正常な動きをし...
-
プレイやに入れたいのですが、...
-
プータブルUSB起動について
-
サポートの終了したソフトから...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
YahooのIDがロックされてしまい...
-
「.movpkg」「.m3u8」ファイル...
-
ゆうちょ認証アプリが使えません
-
サポートの終了したソフトから...
-
Windows Media PlayerでCDが焼...
-
STEAM版BF4が起動しない
-
クリスタルディスクインフォは...
-
ATOKをお使いの方、今後もVer U...
-
CMYKのペイントソフトを探して...
-
アドビの値上げについて
-
Bluerayディスクのオーサリング...
-
jtrim 1.53cが正常な動きをし...
-
住所もしくは緯度経度で道のり...
-
自分で描いた下手な絵を綺麗に...
-
googleスプレッドシートのキャ...
-
新バージョンPDFでのスナップシ...
-
i Tune でCDからハードディスク...
-
先日からDigiBestTVで「デバイ...
-
Winrarの移行
-
gimpで文字の細工をしたい
おすすめ情報