- ベストアンサー
エクセルデータベースで抽出
エクセルでデータベースを作っています。 例えば、 全国の小学校の生徒を対象にアンケートをします。 アンケートを受ける受けないは自由です。 アンケートは毎月あります。 アンケートは一人一枚のマークシートで6問にABCで答えます。 返ってきたアンケートのみデータベースのシート1に入力します。(マークシート読み取り機から一括入力してます) 年月 学校名 学年 組 名前 (1)(2)(3)(4)(5)(6) 091 A学校 5 1 ああ A C A B C B 091 A学校 5 3 いい B B C B A A 091 A学校 5 4 うう C A A A A A 091 C学校 2 1 ええ A C A B C C 091 C学校 3 1 おお A A A B C B 091 E学校 4 4 あい C A A B B A 092 A学校 5 4 いう C A A A A A : : : たとえば、こんな感じで・・・ ここでシート2に、年月と学校名を抽出したいです。 年月 学校名 091 A学校 091 C学校 091 E学校 092 A学校 : : アンケートは毎月なので、このデータベースは毎月大きくなります。(想定では年に1万行位) シート2には毎月データ追加分が加算されるようにしたいです。 (シート2を毎回ゼロから作るとデータベースがでかくなったとき、結構時間がかかってしまう為) なんとかVBAで組もうと思ったのですが、私の腕が足りず出来ません。 どなたかVBAに詳しい方、教えて下さい。 宜しくお願い致します。
- messi37
- お礼率72% (32/44)
- その他MS Office製品
- 回答数6
- ありがとう数5
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
VBAを使わなくても次のようにすることで追加分がシート2に反映できますので何らの問題もないと思います。 シート1では2行目からデータがあり、お示しの表がK列まで使用されているとします。 L2セルには次の式を入力します。 =A2&B2 M2セルには次の式を入力します。 =IF(L2="","",IF(COUNTIF(L$2:L2,L2)=1,MAX(M$1:M1)+1,"")) L2セルとM2セルを範囲として選択してコピーします。 名前ボックスにはL2と表示されていることでしょうがL2:L20000と入力しEnterします。その後に貼り付けを行います。これで20000行まで式をオートフィルドラッグしたと同じ事になります。 次にシート2に移ります。 A2セルには次の式を入力します。 =IF(COUNTIF(Sheet1!$M:$M,ROW(A1))=0,"",INDEX(Sheet1!$A:$B,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1))) この式をB2セルまでオートフィルドラッグします。 コピーします。 名前ボックスにA2:A20000と入力し貼り付けをします。 これで、シート1に新たなデータが追加されても、自動的に年月と学校名が追加されて表示されることになります。
その他の回答 (5)
- cistronezk
- ベストアンサー率38% (120/309)
PCの環境によりますが、一般的には数万件のデータを関数のみで処理するのは無謀でしょう。 すでに回答が示されているピボットテーブルによる集計結果をシート2へ出力し、そのデータと後から説明が追加された「別データ」とやらをシート3に統合しはどうでしょう。シート2からシート3への変換は、単なる参照か簡単な関数で対応可能かと思います。
お礼
回答ありがとうございます。 返信遅れて申し訳ありません。 やはりはじめのデータベースの作り方がまずかったようです。 勉強になりました。 今までは小さなものしか作ったことが無かったものですから・・・。 一から勉強し直します。 またいろいろと教えて下さい。 宜しくお願い致します。 返信遅れて本当に申し訳ありませんでした。
- tag1701
- ベストアンサー率54% (67/123)
すみません。No4です。 適当に8,000件のデータで試したら、やっぱり使用に耐えられませんでした。 すみません。無視してください。
お礼
返信遅れて申し訳ありません。 回答ありがとうございます。 いろいろ試してみましたが、やはりはじめのデータベースの作り方が悪かったようで、うまくいきませんでした。 今は教わったクエリを勉強中です。 いろいろありがとうございます。 また一生懸命勉強します。 返信遅れて本当に申し訳ありませんでした。
- tag1701
- ベストアンサー率54% (67/123)
遅くなる原因(前段階って?)がよくわかりませんが、 抽出方法で別の考え方を紹介します。 エクセルシートを文字通りデータベースとして認識させ、 抽出させます。 1)入力してあるシートは既に保存されていると思いますので、 どこに保存したか認識しておいてください。 2)シート2の抽出したい最初のセルにカーソルをおいて 「データ」-「外部データの取り込み」-「新しいデータベースクエリ」 を選択。(ここでアドインのインストールが始まるケースもある ようですのでその際はOKでインストールしてください) 3)データソースの選択ウィンドウがでますので、Excel File*を選んでOK 4)ここで該当のエクセルファイルが左側のデータベース名の下の方に 現れるよう探す。 5)でたらそれを選んでOK この時クエリに出来るテーブルがありません等のメッセージが出た 場合、参照サイトを参照し、出現させてください。 6)左側のシート名の+を押して列名を表示させ、年月・学校名を 右側に移動させ、「次」をクリックします。 7)条件は無いので、「次」をクリックします。 8)並べ替え順序を上から年月・学校名を選択し、「次」をクリックします。 9)Microsoft Queryでデータの表示またはクエリの編集を行うを選択し「完了」をクリックします。 10)クエリの画面が出て今まで与えた条件の結果が出ています。 この画面の上側ボタン「SQL」を押します。 11)SQLステートメント画面に select~ORDER BY `'シート名$'`.年月, `'シート名$'`.学校名と あります。 12)この ORDER BYから下をコピーして下さい。 13)ORDER BYの前に貼り付けてください。 14)貼り付けたORDER BY を GROUP BYに書き換えてOKを押してください。 15)結果が一月・一校にまとまったと思います。そしたらこのクエリ 画面を×を押して消してください。 16)データを返す場所の確認ウィンドウが出ますので、よければOKを 押してください。 これで抽出完了です。今後は抽出シートの抽出場所最初セルにおいて 「データ」-「データの更新」を押せば、データが何件増えても全て 自動で範囲に入り再計算されます。 この機能はアクセスの一部の機能なので、多分集計スピードは速いと 思います。 興味があれば試してみてください。
- akina_line
- ベストアンサー率34% (1124/3287)
こんにちは。 お考えになっていることと多少違いますが、ピボットテーブルで集計表を作成したらどうでしょう。 一例として、下記のような集計表を作ります。 学年 1 2 3 4 5 年月 学校名 091 A学校 10 20 30 20 20 ←年月、学校、学年毎の人数 091 C学校 091 E学校 092 A学校 : : ピボットテーブルの作り方は下記サイトをご参照ください。 http://www.officepro.jp/exceltips/pivot/index1.html サンプルとほぼ同じ手順でいけると思います。 次に意図した表を作るために項目を設定します。 http://www.officepro.jp/exceltips/pivot/index2.html これはサンプルとは項目が全然違うので、以下のようになります。 ★フィールドリストには年月 学校名 学年 組 名前 (1)(2)(3)(4)(5)(6)が表示されているはずです。 ★「年月」のフィールドをドラッグして、A列の下の「ここに行のフィールドを。。。」と書いてある枠内にドロップします。 ★同様に「学校名」も「年月」の下にドロップします。 ★次に「学年」を3行目の「ここに列のフィールドを。。。」と書いてある枠内にドロップします。 ★最後に「名前」を「ここにデータアイテム。。。」と書いてある枠内にドロップします。 http://www.officepro.jp/exceltips/pivot/index3.html 以上で集計表が出来上がります。 では。
補足
早い回答ありがとうございます。 実はこのデータベースには先があって、このシート2には別のデータを付加しなくてはなりません。 どうしても抽出の方法を取りたかったのです。 質問の説明が不十分でした。 どうもすみません。 ピボットテーブルのサイト見ました。 すごくわかりやすく載っていて、大変勉強になります。 また、いろいろと教えて下さい。 宜しくお願い致します。
- tag1701
- ベストアンサー率54% (67/123)
ピボットテーブルを使っては出来ませんでしょうか? 毎回データ範囲の変更は必要ですが、難しいVBA等を使わなくても 良いのではないかと思いますが。。。
補足
早い回答ありがとうございます。 実はこのデータベースには先があって、このシート2には別のデータを付加しなくてはなりません。 どうしても抽出の方法を取りたかったのです。 質問の説明が不十分でした。 どうもすみません。
関連するQ&A
- データの抽出
再びエクセルの質問をします。 エクセルでデータベースを作っています。 データベースの合計金額を別シート(日報シート)に反映させたいのですがどんな関数を使えばいいのか分かりません。 データベースシートのB5~B204までには「現場名」が入力されていて、C5~AJ5までは費目別に合計金額が入力されています。C3~AJ3には費目(運搬費・施工図費・工事管理費など)が入力されています。 日報シートのA1~A26には費目が入力されていて、別シート(予算シート)のA1に現場名が入力されたら日報シートのB列に費目別に合計金額を反映させる関数を入力したいのですが・・・なにか良い関数がありましたらよろしくおねがいします。分かりづらい文章ですいません;;
- ベストアンサー
- オフィス系ソフト
- エクセルのデータベース化
説明が苦手なので、ずばりそのままお聞きします。 よろしければ教えてください。 中分類10項目、小分類5項目、計50項目のデータを 1つの決められたシートのフォーマットに入力し、 それを1回入力するだけで、中分類それぞれに決められたシートへ、 フォーマットで指定された日付の欄にコピーしたいのです。 入力用のシートには、中分類をA~J、小分類を6~10とし、 日付を、年がA1、月がB1、日がC1としています。 データベース用のシートには、A5~A35に1日からの日付、 B列には曜日、それとC5~L5を1日として35行目まで 1ヶ月単位としてあり、それが10シート分あります。 たとえば、日付を5日とすると、 入力用のシート50項目に入力したデータが、 各シートのC10~L10にコピーされるには どうすればいいんでしょうか? 思ったことをちょっとやってみようと思ったのですが、 何も思いつかなくて、お願いする次第です。 おわかりの方で、もしよろしければ教えていただきたいのですが。 説明、わかりづらいかもしれませんが、よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- EXCELのデータベース利用について(VBA)
VBA初心者の者です。 下記の様なEXCELファイルを作りたいのですが、どのようにすれば良いのか まったくわかりません。 ACCESSでの構築が簡単なのかもしれませんが、データ量がそれほど多くないことと 職場にACCESSがないため、可能であればEXCELで構築したいと考えています。 私自身、プログラムの知識がなく、EXCEL VBAのサイトを確認するのですが、いまいち どうすれば良いのかわかりません。 ぜひご教授の程よろしくお願いします。 3つのシートの構成は以下になります。 【入力シート】 A1セルに文字列(A~Z)入力欄 【○○データシート】 A列に A~Z の文字列 B列に 001~100までの数値 C列に 001~100までの数値 D列に 001~100までの数値 E列に 001~100までの数値 例) A列 B列 C列 D列 E列 1 A 001 2 B 001 002 003 004 3 C 003 4 D 002 003 【▽コマンドシート】 A列に 001~100までの一意の数字 B列に 文字列(コマンド) C列に 文字列(コマンド詳細) 例) A列 B列 C列 1 001 xx blank 2 002 xxx△ xxx 3 003 xxxx x○ 4 004 xxxxx xxx 【欲しいVBA】 1、入力シートのA1セルに○○データシートのA列に該当するA~Zの 文字列を入力。 2、○○データシートのB列~F列までの数値を参照 3、2の数値において▽コマンドシートのA列に記載ある番号と紐づけを行い、 ▽コマンドシートのB列、C列に記載がある文字列を入力シートのB列、C列にコピー ※コピー時は▽コマンドシートの書式や体裁情報も含めてコピー。 例としては以下になります。 入力シートの A1セルにDを入力した場合は 入力シート A列 B列 C列 1 D xxx△ xxx 2 xxxx x○ 3 以上、ご教授よろしくお願いします。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセル(2003・2007)でデータ抽出し、別シートを作成するための
エクセル(2003・2007)でデータ抽出し、別シートを作成するための関数を教えて下さい ●シート名:サンプルA ●シート名:サンプルB A B C D E・・ A B C 名前 日付1 日付2 備考 名前 日付1 備考 1 山田 2010/10/15 2010/10/10 ああ 1 鈴木 2010/10/18 うう 2 佐藤 いい 2 太田 2010/10/21 かか 3 鈴木 2010/10/18 うう → 4 井上 2010/11/20 ええ 5 石田 2010/8/7 2010/10/7 おお 6 太田 2010/10/21 かか 具体的に上記のデータから【B列が本日から10日間以内かつC列がスペース】のデータのA・B・D行を別シートに作成する関数を教えて下さい よろしくお願いします ※なぜか文字がずれてしまってて、すいません・・・
- ベストアンサー
- オフィス系ソフト
- Excelのデータベース活用で・・・
早速ですが、データベースの中から別シートに抽出をしたいのですが、これは可能ですか? できるならどうすればいいのか教えてください。 具体的に言うと、次のようなことがしたいんです。 [Sheet1]に以下のようなデータベースがあるとします。 A列:部署コード(4桁) B列:部署名 C列:社員コード(6桁) D列:社員名 この[Sheet1]のデータをもとに、[Sheet2]以下のシートには指定した部署のみの一覧を抽出したいんです。 例えば、A1セルにある部署コードを入力しておけば、指定したセルに該当する社員だけを表示したいということです。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- excelで指定文字間の繰り返し抽出の方法
excelの文字列でる特定の文字と文字(この場合、カンマ「,」)の間に存在する 文字だけを抽出する方法が分かりません。 シートAとシートBが存在します。 シートA セルA セルB 売上番号 商品名 0001 A,B,C,D,E 0002 A,B,E 0003 AAA 0004 ブランク=(NULL) 0005 xyz : シートB セルA セルB 商品番号 商品名 0001 A 0002 B 0003 C 0004 AAA 0005 E 0006 BBB 0007 D : そこで、シートAのセルBにある商品名が、シートBのセルBの商品名に 存在するかを検索し、すべて存在したらシートAの該当する行のセルC に、”〇”か”×”を設定したい。 関数での方法があればどなたか教えてください。 関数が無理でしたら、マクロ、vbaなどで教えてください。 よろしくお願いします。
- ベストアンサー
- その他(業務ソフトウェア)
- 【エクセル・VBAコードの書き方】データの抽出
1位 2位 3位 4位 5位 A B 25 C 30 18 D E 11 VBAの初心者であります。 上のエクセルの表において、以下の作業を自動的に行なえるようなVBAのコードを教えて頂けると有り難く思います。 まず表の説明からしますと、 A~Eは人の名前を表しています。 1~5位は彼らの順位(ある競争)です。 数値が四つ(25、30、18、11)ありますが、 この四つの数値のなかで、20以上のものだけが、別のシートに自動的に入力されるようにしたいです。 かつ、その20以上の数値の位置(例えば25であれば、B、2位)もそのシートに入力されるようにしたいのです。 例えば別のシートでは以下のようにデータを表したいです。 名前 順位 数値 B 2位 25 C 3位 30 感覚的にはif 文とLoop文を用いると思うのですが、、うまくコードが組めません。 そもそもこれらの作業をVBA(マクロ)に組むことができますでしょうか。 宜しくお願いいたします。
- 締切済み
- その他MS Office製品
- エクセルの順位抽出について
Sheet1のA列にコード、B列に商品名、C列に分類 D列に売上数、 E列に売上金額を入力した一覧表があります。 A_1:1000 B_1:牛肉 C_1:食品 D_1:20 E_1:3000 A_2:1050 B_2:お茶 C_2:飲料 D_2:18 E_2:3010 A_3:2000 B_3:鉛筆 C_3:文具 D_3:12 E_3: 900 A_4:2050 B_4:お米 C_4:食品 D_4:12 E_4:9010 A_5:3000 B_5:牛乳 C_5:飲料 D_5:25 E_5:2000 A_6:3050 B_6:定規 C_6:文具 D_6:28 E_6: 700 中略 A_300:10000 B_300:肉まん C_300:食品 D_300:38 E_300:9000 1.食品対象で売上金額の上位20のコードをSheet2のA列に表示 2.全商品対象で売上金額の上位20のコードをSheet3のA列に表示 上記の様な抽出をしたいのですがどの様な関数を使用すれば良いでしょうか? (オートフィルタを使用してのコピペという手作業をなくすためにSheet1の内容が更新されれば自動でSheet2,3の内容も更新されている のが希望です) よろしくお願いします。
- ベストアンサー
- その他MS Office製品
- エクセルVBA/抽出・貼付け
下記を行いたいのですが、どのようなコードになるのでしょうか? シート001(入力用) (1)A1~A50、B1~B50、C1~C50、D1~D50 に数値、E1~E50に文字列 (2)F1~F50、G1~G50、H1~H50、I1~I50 に数値、J1~J50に文字列 ※空白行混在 シート002(計算用) シート001に作ったコマンドボタン:クリックにより、 シート002を表示させ、A1~E100に、 シート(1)のA1~E50とF1~J50の空白行以外を連続して 反映させたい。並べ替え用など別シートを用いずに、 VBAコード内で処理したい。
- ベストアンサー
- オフィス系ソフト
- エクセル抽出に関して教えて下さい。
エクセル初心者なんですが、抽出について教えて下さい! 例 シート(1) A B C D E 1 8 2 ■ シート(2) A B C D E 1 4 5 6 7 8 2 1 1 3 7 2 3 5 6 7 1 2 シート(1)のB2に シート(1)のA1に入った数字を シート(2)のA1~E1の中で探して 該当した数字の列の3行目をシート(1)のB2に表示させる。 すみません、意味不明だったらごめんなさい。 ご理解いただける方、よろしくお願い致します。
- 締切済み
- オフィス系ソフト
補足
回答ありがとうございます。 御礼遅れて申し訳ありません。 バッチリできました! なるほど横に番号を振って行くんですね。 ただ・・・ちょっと問題が・・・。 すみません、実際にはデータベースがかなり大きくて、この処理を加えたところ、すごく遅くなってしまいました。(前段の処理で5分以上) すみません、結果はばっちりだったんですが、時間がかかり過ぎちゃって。 はじめのベータベースの作り方が悪いと思うのですが、何かいい方法はないでしょうか?