Excelで条件によってデータを抽出する方法とは?
- Excelの条件検索ではなく、関数やマクロを使用してデータを抽出する方法を知りたい。
- データベースとして表形式でデータを管理し、曜日ごとに希望する種目を抽出したい。
- 具体的には、月曜日の表には平泳ぎとクロール希望者の情報を表示したい。
- ベストアンサー
今日平泳ぎとクロールを泳ぐ人は誰か求めたい。
エクセルで条件によって抽出しデーターを引っ張りたいのですが 条件検索じゃなく出来れば関数で、おそらくマクロが必要でしょうができる限り自動化でやりたいと考えています。 sheet1にデーターベースとして下記のような表を 月 火 水 木 金 土 平泳ぎ クロール 岡田 遥 ● ● 小泉 伸 ● ● 酒井 まどか ● ● ● 当然3行だけじゃなく何行にもなるデーターです そのデーターをsheet2に 月曜日 sheet3に 火曜日・・・・土曜日とつくり その中でも下の表のように・・・・ "水曜日の表" 平泳ぎ希望者 クロール希望者 酒井 まどか 酒井 まどか と、その曜日に来る希望している種目が誰と誰なのかを求めたいのです。 このような事は知識のある方は簡単でしょうが私には難しすぎで・・・ お忙しいとは思いますがわかり易く教えていただければ幸いです。 宜しくお願いします。
- syou_yuu20
- お礼率60% (63/105)
- オフィス系ソフト
- 回答数6
- ありがとう数6
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
水曜日に●を付けている人が,平泳ぎやクロールに丸を付けているかどうかだけの問題です。 数式はムズカシイですが,やり口自体はごくFAQです。 水曜日シートのA3: =INDEX(Sheet1!$A:$A,SMALL(IF((Sheet1!$D$1:$D$100="●")*(Sheet1!$J$1:$J$100="●"),ROW(Sheet1!$J$1:$J$100),9999),ROW(A1)))&"" と記入してコントロールキーとシフトキーを押しながらEnter 下向けにコピー 水曜日シートのB3: =INDEX(Sheet1!$A:$A,SMALL(IF((Sheet1!$D$1:$D$100="●")*(Sheet1!$K$1:$K$100="●"),ROW(Sheet1!$K$1:$K$100),9999),ROW(A1)))&"" と記入してコントロールキーとシフトキーを押しながらEnter 下向けにコピー それぞれ,シート1のD列(Sheet1!$D$1:$D$100)が水曜日,J列が平泳ぎに●の人,K列がクロールに●の人です。
その他の回答 (5)
- KURUMITO
- ベストアンサー率42% (1835/4283)
No5です。シート2以外のシートについてもシート2をコピーして使うことで良いでしょう。あとは、各シートのA1セルの曜日を変えることだけです。
- KURUMITO
- ベストアンサー率42% (1835/4283)
シート1でのデータがA2セルから下方に氏名が、B1セルからG1セルには月、火、・・と土までが入力されているとします。またH1セルには平泳ぎ、I1セルにはクロールの文字が入力されているとします。その他の泳法を入力することを考慮して作業列としてM2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。 =IF(OR($A2="",COLUMN(A1)>COUNTA($H$1:H$1)),"",IF(OR(COUNTIF($B2:$G2,"●")=0,H2=""),"",MATCH("●",$B2:$G2,0)*1000+COUNTIF(M$1:M1,">="&MATCH("●",$B2:$G2,0)*1000)-COUNTIF(M$1:M1,">="&MATCH("●",$B2:$G2,0)*1000+1000)+1)) 次にシート2についてはA1セルに月と入力してB1セルには曜日の表とでも入力します。 A3セルには平泳ぎ希望者、B3セルにはクロール希望者、さらに別の泳法の希望者を入れる場合にはその横にシート1と同じ順序で項目を並べます。 次にA4セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。 =IF(COUNTIF(Sheet1!M:M,MATCH($A$1,Sheet1!$B$1:$G$1,0)*1000+ROW(A1))=0,"",INDEX(Sheet1!$A:$A,MATCH(MATCH($A$1,Sheet1!$B$1:$G$1,0)*1000+ROW(A1),Sheet1!M:M,0))) これで泳法が5種類までの種目について表を作ることができます。より多くの種目についても同様に作成すればよいでしょう。
お礼
外出しておりお返事がおそくなりました。 皆さんから教えていただきありがたく思います。 今から取り掛かります。 厚かましいですが躓いたら教えてください。 ありがとうございます。
- imogasi
- ベストアンサー率27% (4737/17068)
一般的にこういう課題は、エクセルの関数では表の組み換えタイプで、適していない。 元データがエクセル向き(特に関数で処理向き)ではないのだ。 エクセルは、元データはリスト形式といって 岡田 年月日 競技 のような表でも作って、それから質問のような表を作れるか考えるのだ。 初心者が、先の見通しももたず、思いつきでデータを勝手に作って、したい形式も勝手に作って、人頼みで質問するなどおかしいのだ。 自由にやろうとするなら、プログラムでも組めないと十分には出来ない。 ここはクイズ問題を出す場ではないので、自分でやってみて、わからない点のヒントをもらうのがこのコーナーだ。 難しい式をコピペして、出来ました、ありがとう、なんておかしい。自分で途中まででも考えること。 条件で抜き出しそのものが難しい。 Googleで「imogasi方式」で照会すれば、抜き出し問題の質問が多数出て、作業列を使わない、長く難しい回答が出ている。参考までに見てください。 難しさは 曜日の位置が不定なのが難しくする シートを分けることが難しくする 複数競技出場があるのが難しくする。 1日に1競技に複数出場者がいる こういう点が難しくすする点だ。 ーーーーーー 一応imogasi方式でやっておく Sheet1で A1:I7 (その右列は下記) 氏名 曜日 作業列 月 火 水 木 金 土 岡田 遙 月 月1 ● 小泉 伸 火 火1 ● 酒井 まどか 水 水1 ● 佐藤 健 月 月2 ● 山田 進 木 木1 ● 鈴木 次朗 月 月3 ● B,C列を列挿入(別のデータの無い空き列でも良い。ただし式は変わるが)) B列(B2)の式は =INDEX($D$1:$I$1,1,MATCH("●",D2:I2,0)) 下方向に式複写 C列(C2)の式は =B2&COUNTIF($B$2:B2,B2) 下方向に式を複写 ーー J1:M7は(上記の右列続き) 平泳ぎ クロール 背泳 テニス ● ● ● ● ● ● ● ーーーーー Sheet2のA1に「月}を入れておく。(Sheet3の場合は「火」をいれる、手動入力) 第1行目はSheet1と同じものを複写しておく(作業グループで入力も良いかも) A2の式は =INDEX(Sheet1!$A$1:$L$100,MATCH($A$1&ROW()-1,Sheet1!$C$1:$C$100,0),COLUMN()) 右方向に式を複写。A2:M2を範囲指定して、下方向に式を複写。 結果 月 月 火 水 木 金 土 平泳ぎ クロール 背泳 テニス 岡田 遙 月 月1 ● ● 佐藤 健 月 月2 ● ● 鈴木 次朗 月 月3 ● ーーー ● 本当は0のセルがあるが E2:M7を範囲指定して、書式ーセルーユーザー定義ー [=0]" " で0が見えなくなる。 ーーー 平泳ぎ希望者 岡田 遙 酒井 まどか と出すのは関数では難しく、技巧が要るので、ここで止めておく。 考え方では「平泳ぎ作業列」を作って、名前と同じように「平泳ぎ1」「平泳ぎ2」,・・・と振って、氏名と同じような手法で 抜き出せば出来る。ただ競技ごとに1列余分な作業列が要る。 ーー 私なら、こういう課題があれば、VBAで処理する。
お礼
すいませんでした。
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは! 一例です。 余計なお世話かもしれませんが、平泳ぎ・クロール希望者それぞれのマークを変えてみました。 ↓の画像のようにSheet1に平泳ぎ希望者は「●」・クロール希望者は「○」を入れるとします。 Sheet2以降か各曜日のSheetとします。 各SheetのA1セルにはそのSheetの曜日を入力し、●・○のマークを入れておきすべて同じ配列にしておきます。 Sheet2の月曜日を開きShiftキーを押しながら最後に曜日のSheet見出しをクリックします。 これで月曜日~土曜日のSheetがグループ化されましたので、数式は表示されているSheetに入力するだけで すべて同じ数式が入ります。 画像の月曜日SheetのA4セルに =IF(COUNTIF(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$G$1,0),,1),A$3)<ROW(A1),"",INDEX(Sheet1!$A$2:$A$100,SMALL(IF(OFFSET(Sheet1!$A$2:$A$100,,MATCH($A$1,Sheet1!$B$1:$G$1,0),,1)=A$3,ROW($A$1:$A$99)),ROW(A1)))) これは配列数式になってしまいますので、この画面からコピー&ペーストしただけではエラーになると思います。 A4セルに貼り付け後、F2キーを押します。 編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定! 数式の前後に{ }マークが入り配列数式になります。 これを列方向と行方向にオートフィルでコピーすると画像のような感じになります。 最後にSheet見出し上で右クリック → 「作業グループ解除」 これで完了です。 何とか希望に近い形にならないでしょうか?m(__)m
お礼
ありがとうございます。 少し自分のレベルでは高度みたいですが、ひとつ一つ理解してやってみたいと思います。 本当に親切、丁寧に回答頂きありがとうございます。
- Cupper-2
- ベストアンサー率29% (1342/4565)
(´-ω-`)んー… 週に一回だけ? 週に二回、平泳ぎとクロールを交互にやりたいというニーズには、その元にする表では応えられませんよね。 伊藤 □■ □□ □□ □■ □□ □□ 黒田 □□ ■□ □□ □□ □■ □□ 三条 □□ □□ □■ □■ □□ □□ 山縣 □□ □□ □□ □□ ■□ □■ 松方 □□ □□ □□ ■■ □□ □□ ※左は平泳ぎ、右はクロール などと、始めから曜日の中にメニューを入れ込んだ方が見やすくて応用もできて確実と思います。 それにコレなら質問者さんでも何とかいじれるようになるんじゃないですか。
お礼
早速ありがとうございます。 少し考え方変えてみます。
関連するQ&A
- excelでボタン一つでシートからシートへのコピーの方法。
例) シート1に日付と商品名と単価と個数、それに特別の項目があります。 下図のような表。 =============== 日付 商品 単価 個数 4/1 A 100 1 特別 4/1 D 0 3 ←1行空いてます。 4/2 B 120 2 4/2 C 130 1 ←1行空いてます。 4/3 A 120 1 4/3 C 130 3 特別 4/3 D 0 5 ←1行空いてます。 4/4 定休日 : : 4/30 =============== このような表があるとします。 これをボタン一つでシート2にコピーしたいのです。 (簡単に言えばシート1がデータ。シート2が印刷用です。) <条件> シート2のセルの大きさは変えられませんが、それ以外はOK シート2にコピーする際に日付指定が出来ること。(1日から5日までのデータを印刷するなど。) 長々とした文になりましたが、よろしくお願いします。 また、わからないことがあれば補足いたします。
- 締切済み
- オフィス系ソフト
- Excel ランキング表へ一括してリンク貼付けしたい!
スイミングスクールでコーチをしています。 【生徒ごとの種目別タイムシート】にある各種目の自己ベストタイムを、同一ブック内の【生徒間の種目別ランキング表シート】にある 特定の各セルへリンク貼付けしたいのですが、一括してリンク貼付けする方法はないものでしょうか? 手入力でひとつひとつリンク貼付けすると非常に煩雑な作業になってしまうので、困っています。 関数を使うことで問題が解決できれば最善なのですが、他にも解決できる方法があれば教えていただきたいのです。 どうか宜しくお願い致します。 各シートのおおまかな入力例は下記のとおりです。 【生徒ごとの種目別タイムシート】 生徒の氏名をシート名にして作成してあります。 A B C D ... 1 氏名:徳川家康 2 テスト実施日 自由形25m 自由形50m 背泳ぎ25m 3 2008/12/27 0:19.45 4 2009/02/25 0:35.40 5 2009/04/22 0:20.20 ・ ・ ・ 30 自己ベストタイム 0:19.45 0.35.40 (1)テスト実施日に行った種目のタイムを入力します。 ※各列30行目の自己ベストタイムは、列ごとにMIN関数を使用して自動表示しています。 【生徒間の種目別ランキング表シート】 自由形・背泳ぎ・平泳ぎなどの泳法別にシートを作成しています。 A B C D E F G H 1 自由形 2 順位 25m 50m 3 1 タイム 氏名 学年 タイム 氏名 学年 4 2 0:19.45 徳川家康 小6 0:31.00 大隈重信 小3 5 3 0:21.20 豊臣秀吉 中2 0:35.40 徳川家康 小6 ・ ・ ・ (1)【タイムシート】の自己ベストタイムを【ランキング表シート】の各々の氏名に該当するB列、F列・・・へリンク貼付けしていきます。 (2)各々の種目で データ→並べ替え の方法でタイム列を最優先にして昇順で順位を並べ替えします。
- ベストアンサー
- オフィス系ソフト
- エクセルのマクロ作成で困ってます
マクロ初心者です。 ”Sheet1にあるデータについて、複数条件で抽出後、Sheet2の表に転記” という動作をマクロで組みました。 ここまでは良かったのですが、Sheet2に転記する際、下記の様にさらに条件を付加したいと思っています。 やりたいこと(ア) Sheet1で条件によりたとえば10個のデータを抽出できたとし、 1~6個目までは、Sheet2の1行~6行に転記、 7~10個目までは、Sheet2の11行~14行に転記。 ※”6個目まで1~6行に転記”というのは決まっているのですが、 この10個というのは、例であって、5個だったり、14個だったり、 抽出したデータにより異なるので、変数です。 やりたいこと(イ) Sheet1から、さらに別の条件で抽出したデータを、 やりたいこと(ア)の10個目を転記した行のあとすぐ(例でいうと15行目)から転記したい。 ※やりたいこと(ア)で抽出した条件が6個以下だった場合、11行目から転記。 以上の2つです。 条件の抽出までは出来るのですが、 (ア)の7個目から別のところに転記 (イ)の別の条件で抽出したデータを(ア)の次(6個以下の場合は11行目)から転記 をどうやって組んだら良いかわかりません。 もし分かる方がいらしたら、 どのようなコマンド(?)を使ったら良いのか、 ご教示いただけると助かります。 よろしくお願いします!
- 締切済み
- オフィス系ソフト
- 10個のブックからの一覧表作成について
区域別に分けた10のブックに、同一規格で作成したその区域の集計表シートがあり、複数の条件別集計としているため1シートあたり約30万のデータ数となっています。 現状は、ある条件に該当するものがどうであるのか、を見るために各ブックを開いて該当シートの同一行を別シートにメモ的にコピーし10個のデータを並べて比較している、という極めて原始的なやりかたをしています。 ある条件に合致するデータがどうであるのか、を全区域一覧で比較できるものを作成したいのですが、単純計算すると60列、5万行くらいの表ができることになり、どう作業をすすめるべきか思案しております。 イメージとしては、各ブックの2行目が10行並び、次に3行目が10行並び・・・となるのですが、自分のレベルで考えつくのは、各ブックから1行ずつコピーするか、各ブックの該当行を参照する式を入れて10行ずつコピー、参照先を修正、これを延々と繰り返す。完成したら条件別にシートを分割して見やすいものにする。 この程度しかできないのですが、このレベルでも可能な方策があるでしょうか。
- ベストアンサー
- その他MS Office製品
- 平泳ぎから覚える?クロールから覚える?
40代男性、カナヅチです。健康維持のため、ひごろの運動習慣の選択肢のひとつとしてスイミングを取り入れようと考えています。 そのような場合は平泳ぎから覚えるべきでしょうか?クロールから覚えるべきでしょうか? ※今後通う予定のスイミングスクールで習得した泳法は任意で選択出来るようになっています。
- ベストアンサー
- 水泳
- クロールと平泳ぎどっちがいいのでしょうか?
健康のため定期的にプールで泳いでいます。 水泳は全身運動だから良いなどと聞きますが、クロールと平泳ぎ、どちらで泳いだほうがいいのでしょうか? クロールは25m泳いだだけで苦しいですが、平泳ぎだといくらでも泳げてしまいます。平泳ぎのほうが体の負担というよりは息継ぎが楽なんです。この楽という点が運動としては駄目なんじゃないかとちょっと気になりました。周りはみんなクロールで泳いでいますし。 よろしくお願いします。
- ベストアンサー
- その他(ダイエット・フィットネス)
- 【エクセル】シートからシートへの差し込み
エクセルの使い方でわからないので教えてください。 初心者ですので、VBAやマクロといったことはまったくわかりません。。。 シート1に顧客データ約400件、シート2に表があります。 シート1の中の 銀行名、支店名、種目、口座番号、口座名義、フリガナ、振込金額を シート2の振込送金依頼書に15件づつ差込していきたいのです。 顧客データは1行に1顧客分なのですが、 振込送金依頼書は1件分が2行になっていて、 銀行名、支店名、種目、口座番号、振込金額の欄は2行のセルを結合していて、口座名義とフリガナ部分が2行に配置されており、 セルのコピー機能ではうまくできません。 この、口座名義とフリガナ部分を簡単に差込していきたいのです。 説明が下手で申し訳ないのですが、お分かりになる方教えてください。
- 締切済み
- その他MS Office製品
- エクセルでデータを表に貼り付け、印刷、次の行を……を連続で行いたいのです
データの入力されたシートがあります。 a b c d 1 あ い う え 2 お か き く 3 け こ さ し まず「1」の行の「あ・い・う・え」を別シートの表に飛ばし、印刷します。 次に「2」の行のデータを、同じく別シートの表に飛ばし印刷、次に「3」の行のデータを…… と繰り返していきたいのです。 ご教示いただきたいのは、「1」行のデータを飛ばして印刷した後、 「2」行のデータを飛ばして(上書き)印刷……というループ作業の、VBAでの記述方法です。 過去の質問を色々と見たのですが、どうにも見つかりませんでした。 どうしたら一行ずつずらして別シートの表に移行できるのでしょうか。 ご回答、どうぞよろしくお願いします。
- 締切済み
- その他(インターネット・Webサービス)
- VBA マクロを使って、コピー ソートしたいです
画像でシート1の状態を、マクロでシート2のようにしたいです。 なにぶん初心者なので、効率のいいスクリプトがかけません。 よろしくお願いします。 条件としては シート1には大きく分けて3つ表がありますが、実際は10個あります。 10個目までシート2上でデータがつながっている感じです。 B,G,L列の連番はデータの個数です。 シート1にあるように、表ごとに入ってる個数はばらつきます。 シート2 のA列はシート1のB,G,Lの2行目の番号が入っています。
- ベストアンサー
- オフィス系ソフト
- ランダムに出てくるデータの抽出
こんにちは。 マクロ初心者です。 A B C D E F G 1 あ あ う う う え お 2 あ あ い い い お 3 い 4 5 う お 6 う お 7 8 あ お え え え お 9 あ い う え お 10 お い い い え 上記のような表があるとき。 1の行には「あ・う・え・お」 2の行には「あ・い・お」 3の行には「い」 4の行には「データなし」 5の行には「う・お」 6の行には「う・お」 7の行には「データなし」 8の行には「あ・え・お」 9の行には「あ・い・う・え・お」 10の行には「い・え・お」 このように、『行ごとに何のDataが存在しているかを取り出す』ようなマクロを組みたいのです。 関数でも可能だと思うのですが、出来るだけエクセルシートの容量を減らしたいのでマクロで組みたいと考えています。 このデータは、上記の例では5個ですが、1000個ぐらいあり全てランダムに出てきます。 何か条件が付いているデータの抽出という質問は、よく見かけるのですが、 このように何を条件とすれば良いのかわからない場合は、どのようにすれば良いのでしょうか。 どのようにマクロを組んだら良いかわからず困っています。 何卒アドバイスをお願いします。
- ベストアンサー
- Visual Basic
お礼
ありがとうございます。 先ほどの方と数式がまた違い、考え方がいろいろあると関心させられてます。 出来るだけ理解して考えますので、わからないところがあればまた質問させてください。 ありがとうございました。
補足
ありがとうございました。正確に表記できました。 最後にROW(Sheet1!$J$1:$J$100),9999)の9999は何を表してるのか教えてください。 宜しくお願いします。