今日平泳ぎとクロールを泳ぐ人は誰か求めたい。

質問

エクセルで条件によって抽出しデーターを引っ張りたいのですが
条件検索じゃなく出来れば関数で、おそらくマクロが必要でしょうができる限り自動化でやりたいと考えています。

sheet1にデーターベースとして下記のような表を

　　　　　　　　　　　月　　火　　水　　木　　金　　土　　　　平泳ぎ　　　クロール

岡田　遥　　　　　 ●　　　　　　　　　　　　　　　　　　　　　　　●
小泉　伸　　　　　　　　　●　　　　　　　　　　　　　　　　　　　　　　　　　　　●
酒井　まどか　　　　　　　　　　●　　　　　　　　　　　　　　　　●　　　　　　●

当然3行だけじゃなく何行にもなるデーターです

そのデーターをsheet2に　月曜日　sheet3に　火曜日・・・・土曜日とつくり
その中でも下の表のように・・・・

"水曜日の表"

平泳ぎ希望者　　　　　　　　　クロール希望者

酒井　まどか　　　　　　　　　　酒井　まどか　　　

と、その曜日に来る希望している種目が誰と誰なのかを求めたいのです。

このような事は知識のある方は簡単でしょうが私には難しすぎで・・・
お忙しいとは思いますがわかり易く教えていただければ幸いです。
宜しくお願いします。

回答No.3

keithin · ベストアンサー

水曜日に●を付けている人が，平泳ぎやクロールに丸を付けているかどうかだけの問題です。
数式はムズカシイですが，やり口自体はごくFAQです。

水曜日シートのＡ３：
=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
下向けにコピー

水曜日シートのＢ３：
=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
下向けにコピー

それぞれ，シート１のＤ列（Sheet1!$D$1:$D$100）が水曜日，Ｊ列が平泳ぎに●の人，Ｋ列がクロールに●の人です。

お礼

ありがとうございます。
先ほどの方と数式がまた違い、考え方がいろいろあると関心させられてます。
出来るだけ理解して考えますので、わからないところがあればまた質問させてください。
ありがとうございました。

補足

ありがとうございました。正確に表記できました。

最後にROW(Sheet1!$J$1:$J$100),9999)の9999は何を表してるのか教えてください。
宜しくお願いします。

回答No.6

KURUMITO

No5です。シート2以外のシートについてもシート2をコピーして使うことで良いでしょう。あとは、各シートのA1セルの曜日を変えることだけです。

回答No.5

KURUMITO

シート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種類までの種目について表を作ることができます。より多くの種目についても同様に作成すればよいでしょう。

お礼

外出しておりお返事がおそくなりました。
皆さんから教えていただきありがたく思います。
今から取り掛かります。
厚かましいですが躓いたら教えてください。
ありがとうございます。

回答No.4

imogasi

一般的にこういう課題は、エクセルの関数では表の組み換えタイプで、適していない。
元データがエクセル向き（特に関数で処理向き）ではないのだ。
エクセルは、元データはリスト形式といって
岡田　年月日　競技
のような表でも作って、それから質問のような表を作れるか考えるのだ。
初心者が、先の見通しももたず、思いつきでデータを勝手に作って、したい形式も勝手に作って、人頼みで質問するなどおかしいのだ。
自由にやろうとするなら、プログラムでも組めないと十分には出来ない。
ここはクイズ問題を出す場ではないので、自分でやってみて、わからない点のヒントをもらうのがこのコーナーだ。
難しい式をコピペして、出来ました、ありがとう、なんておかしい。自分で途中まででも考えること。
条件で抜き出しそのものが難しい。
Ｇｏｏｇｌｅで「imogasi方式」で照会すれば、抜き出し問題の質問が多数出て、作業列を使わない、長く難しい回答が出ている。参考までに見てください。
難しさは
曜日の位置が不定なのが難しくする
シートを分けることが難しくする
複数競技出場があるのが難しくする。
１日に１競技に複数出場者がいる
こういう点が難しくすする点だ。
ーーーーーー
一応imogasi方式でやっておく
Ｓｈｅｅｔ１で
Ａ１：Ｉ７　（その右列は下記）
氏名 曜日 作業列 月 火 水 木 金 土
岡田　遙 月 月1 ●
小泉　伸 火 火1 ●
酒井　まどか 水 水1 ●
佐藤　健 月 月2 ●
山田　進 木 木1 ●
鈴木　次朗 月 月3 ●
Ｂ，Ｃ列を列挿入（別のデータの無い空き列でも良い。ただし式は変わるが））
Ｂ列（Ｂ２）の式は　=INDEX($D$1:$I$1,1,MATCH("●",D2:I2,0))
下方向に式複写
Ｃ列（Ｃ２）の式は　=B2&COUNTIF($B$2:B2,B2)
下方向に式を複写
ーー
Ｊ１：Ｍ７は（上記の右列続き）
平泳ぎ クロール 背泳 テニス
●
●
● ●
●
●
●
ーーーーー
Ｓｈｅｅｔ２のＡ１に「月｝を入れておく。（Ｓｈｅｅｔ３の場合は「火」をいれる、手動入力）
第１行目はＳｈｅｅｔ１と同じものを複写しておく（作業グループで入力も良いかも）
Ａ２の式は　=INDEX(Sheet1!$A$1:$L$100,MATCH($A$1&ROW()-1,Sheet1!$C$1:$C$100,0),COLUMN())
右方向に式を複写。Ａ２：Ｍ２を範囲指定して、下方向に式を複写。
結果
月 月 火 水 木 金 土 平泳ぎ クロール 背泳 テニス
岡田　遙 月 月1 ● ●
佐藤　健 月 月2 ● ●
鈴木　次朗 月 月3 ●
ーーー ●
本当は0のセルがあるが
Ｅ２：Ｍ７を範囲指定して、書式ーセルーユーザー定義ー [=0]" "
で0が見えなくなる。
ーーー
平泳ぎ希望者
岡田　遙　　　　　　　　　
酒井　まどか　
と出すのは関数では難しく、技巧が要るので、ここで止めておく。
考え方では「平泳ぎ作業列」を作って、名前と同じように「平泳ぎ1」「平泳ぎ2」,・・・と振って、氏名と同じような手法で
抜き出せば出来る。ただ競技ごとに１列余分な作業列が要る。
ーー
私なら、こういう課題があれば、ＶＢＡで処理する。

お礼

すいませんでした。

回答No.2

tom04

こんばんは！
一例です。

余計なお世話かもしれませんが、平泳ぎ・クロール希望者それぞれのマークを変えてみました。
↓の画像のように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

お礼

ありがとうございます。
少し自分のレベルでは高度みたいですが、ひとつ一つ理解してやってみたいと思います。
本当に親切、丁寧に回答頂きありがとうございます。

回答No.1

Cupper-2

(´-ω-`)んー… 週に一回だけ？

週に二回、平泳ぎとクロールを交互にやりたいというニーズには、その元にする表では応えられませんよね。

　伊藤　□■ □□ □□ □■ □□ □□
　黒田　□□ ■□ □□ □□ □■ □□
　三条　□□ □□ □■ □■ □□ □□
　山縣　□□ □□ □□ □□ ■□ □■
　松方　□□ □□ □□ ■■ □□ □□
　　　　　　　　　※左は平泳ぎ、右はクロール

などと、始めから曜日の中にメニューを入れ込んだ方が見やすくて応用もできて確実と思います。
それにコレなら質問者さんでも何とかいじれるようになるんじゃないですか。

お礼

早速ありがとうございます。
少し考え方変えてみます。