Excelをデータベースのようにして使用している場合について教えて欲しいことがあります。
・Sheetを2つ作成したとします。
・Sheet1はたくさんのデータが入っているシートです。
・Sheet1の名前を仮に『Data』とします。
・Sheet2はSheet1から必要なデータを抽出してきて並べるシートです。
・Sheet2の名前を仮に『Report』とします。
・『Data』のA列には1~200までの数字が入力されています。
・その数字は ランダムである上に 重複していることもあります。
・例えばA1には「1」が入力されていて A2には「2」が入力されています。
・しかし その後A3にもA4にもA5にも「2」が入力されています。
・同様にA6~A15までは「3」が入力されているのです。
・このようにA列の数字は 同じ数字が何度も重複しており その重複には規則性はありません。
・『Report』シート上で『Data』シートからデータを抽出したいのですが A列に「1」と入力されているデータだけを取り出すのなら「VLOOKUP」でもできますよね・・・・
・しかし A列に「2」と入力されているデータを「VLOOKUP」関数で選んでも その先頭行のデータしか読み込んできません。
・『Data』シートのA列に入力している数字から 必要な数字が入力されている行のデータだけを 全て『Report』シートに並べようとした場合 手作業で行うとすると・・・
『Data』でA列が「1」のデータだけを選び,それを『Report』シートに貼り付ける。次は「2」で実行する。・・・・・という作業を200回繰り返すことになりますが
簡単に行うにはどのようにすればいいのでしょうか?
マクロについては あまり詳しくないので
できれば関数を使いたいのですが 無理でしょうか?
マクロを使うのであれば 分かりやすく教えていただきたいのですが・・・
勝手なお願いで申し訳ありませんが よろしくお願いします。
No.4ベストアンサー
- 回答日時:
#3です。
Sheet1に足した連番(D列)は1をスタートにして、2,3,4・・と振っています。
M6から1をまず探すにはROW()ーー>6ですから、Row()-5に書き換えて最初が1になるように数を調整してください。すると
Sheet2の
M列6行目 6-5=1 -->Sheet1のT列で、1をMATCH関数で探す
M列7行目 7-5=2 -->〃 2を探す
M列8行目 8-5=3 -->〃 3を探す
・・
となります。
お礼が遅くなって申し訳ありません。
ありがとうございます。
ついでに・・・ あと一つ教えていただけませんか?
データシートのデータはA6~R275までです。
Sheet2には
M6からデータを書き込むようにしたいのです。
つまり Sheet1のA列~R列までを Sheet2のM列~AD列に書き込みたいのです。
このような場合は 式のどの部分を書き換えればいいのでしょうか?
よろしくお願いします。
No.3
- 回答日時:
質問が、冗長すぎる。
回答者は初心者ばかりではない。くだくだ書かなくても、「A列に重複データがあります」だけでわかる。本来
エクセルは
操作
関数
VBA
などの解決法があるが、本筋は「操作」だ。それを輪売れないこと。
データーフィルターフィルタオプションの設定などが適当ではないかな。
データベースクエリのご紹介もあるが、アクセスなどの経験がないと難しいのでは。
関数だけで抜き出すご回答も有るが、私の自称imogasi方式を書いてみる。
#2のご回答の式の意味が理解できれば、下記は読まなくても良い。
ーーーー
Sheet1
A2:B2 A,B列データ D列作業列
A列 B列 D列
1a1
1b2
2c
3d
1e3
3f
2g
1h4
4i
2j
ーーー
D2の式は
=IF(A2=Sheet2!$A$1,MAX($D$1:D1)+1,"")
下方向に式を複写
ーー
Sheet2
A1に1、や2.3の抜き出す条件の値を入れる。
A2に =INDEX(Sheet1!$A$2:$B$100,MATCH(ROW()-1,Sheet1!$D$2:$D$100,0),COLUMN())
右へ式を複写。
あ2:b2を下方向に式を複写。
結果
1
1a
1b
1e
1h
1を2に変えると即座に
2c
2g
2j
#N/A#N/A
このエラーを出さない方法は、Googleでimogasi方式を照会すればでてくる私の回答の中に載っている。長くなるので略。
Sheet1のD列のMAXの値より大なら空白にするIF関数を前にかぶせる。
ご回答ありがとうございます。
すみません。長々とした質問で・・・
こちらがシロウトなもので
こんな表現しかできませんでした。
結局 このimogasi方式というのを 使わせていただくことにしました。
しかし 関数の意味が理解できていないので(スミマセン 初心者で)
ちょっと うまくできない部分がありました。
データシートのA6~R275までがデータなので
作業列はT6~T275までつくりました。
T6には =IF(A6=Sheet2!$A$1,MAX($T$5:T5)+1,"") でうまくいくと思います。
しかし
Sheet2のJ5に抜き出す条件の値を入れるようにして
M6からデータを書き込むようにするには
M6に =INDEX(Sheet1!$A$6:$R$275,MATCH(ROW()-1,Sheet1!$T$6:$T$275,0),COLUMN()) では うまくいきません。
どこがダメなのでしょうか?
No.2
- 回答日時:
ここ1,2ヶ月の間にこのサイトで見た回答を参考にさせていただいております。
オリジナルのURLを記録してなかったので、再現してみました。シート名、データベースのデータ範囲はご自分の環境に合わせて修正願います。
データベースのシート
.......A.......B.......C.......D
..1.................1.....あ.......a
..2.......2.......2.....い.......b
..3................6.....う.......c
..4.................1.....え.......d
..5.......5.......2.....お.......e
..6................3.....か.......f
..7................4.....き.......g
A1の式=IF(B1=Sheet1!$A$1,ROW(),"")、以下下方に複写
データはB列以降に置く
検索するシート
............A...............B...............C
..1...............2.............い...............b
..2..............................お...............e
..3........................#NUM!.......#NUM!
..4........................#NUM!.......#NUM!
..5........................#NUM!.......#NUM!
A1:検索する値
B1の式=INDEX(Sheet2!$B$1:$Z$7,SMALL(Sheet2!$A$1:$A$7,ROW(A1)),COLUMN(B1))
以下、列方向、行方向に複写
#NUMが気に入らない場合は(普通は嫌ですね..)条件付書式で白色フォントにする等してください。
式にエラー処理を盛り込みたければ、ご自分でなさって下さい。
No.1
- 回答日時:
下記は検討されたのでしょうか?A列の値だけで抽出なら、関数で可能でしょうが、より複雑な条件になるとこれらの出番になると思います。
1.フィルタオプション
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …
2.データベースクエリ
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filte …
(Officeインストール時にMS Queryをインストールしてある、または、追加でインストールする必要あり)
ご回答ありがとうございます。
フィルタオプションを使えば 確かに抽出ができました。
しかし A列の数字でデータを抽出して表に表す場合
何度も 実行させるためには マクロが必要ですね。
個人的には VLOOKUP関数のように
特定のセルに数値を入力すると 瞬時に表示してくれるのを期待していたのですが・・・
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) vbaエクセルマクロ RemoveDuplicatesについて RemoveDuplicatesを使 3 2023/02/28 01:13
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Excel(エクセル) Excelで、別シートの表のステータスに伴った動的な自動転記をしたいです。 2 2023/06/14 15:56
- Excel(エクセル) Excel 売上管理シートに入力した売上データを、日報に自動反映させたいと考えています。 売上管理シ 3 2023/04/29 18:08
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Visual Basic(VBA) excelにて、特定の列に数字入力してあれば、入力してある行コピーして 別ファイルに張り付ける 2 2022/08/11 05:33
- Excel(エクセル) Excelで日報を自動で作成したい 売上管理シートに入力した売上データを、日報に自動反映させたいと考 1 2023/04/29 18:07
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで日付の入ったセルの...
-
エクセルでセルに「氏名を入力...
-
Excel ウインドウ枠の固定をす...
-
Excelファイルの「数式」タブ→...
-
関数を教えて下さい。
-
スマートな関数を教えて下さい。
-
EXACT関数とIF関数の組み合わせ...
-
スプレッドシートの関数VLOOKUP...
-
Excelのif関数で文字が見えなく...
-
Excel 2019 のピボットテーブル...
-
Excel 2019 は、SPILL機能があ...
-
各ページの1番上の表示について
-
写真のコピー
-
条件付き書式を教えてください
-
INDEX関数やMATCH関数で値を取...
-
エクセル グラフ軸について
-
excelの不要な行の削除ができな...
-
Excelで行をコピー、同じ行内に...
-
エクセル関数に詳しい方、教え...
-
Excel 改ページの文字色の変更...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報