エクセルの関数について教えてください。

例えば氏名を入力したセルのとなりに性別を男、または女と入力された100人分のデータがあったとします。
この表から男、または女だけを抜き出した表を表示させたいのですが、ソートするのではなく、別のシートに一覧表として表示させるようなことはできますでしょうか。
VLOOKUPでできるかもしれないと思いましたが、これは該当するデータを1つ拾ってくる関数なので違うような気がします。
よろしくお願い致します。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2010/11/29 17:51:05
  • 終了:2010/12/03 12:33:48

ベストアンサー

id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692010/11/30 10:15:11

ポイント35pt

これは配列数式で1つの関数でできるのですよ。


例えば、名前がA列、B列に男女が書いてあって100行あるとして

C1に次の数式を入れて、Ctrl+Shift+Enterで配列数式にして下にコピーすれば男の名前が抽出されます。

=INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(B$1:B$100),101),ROW()))

配列数式とは何かというのはこちらを見てください。

http://pc.nikkeibp.co.jp/pc21/special/hr/

早い話が作業列を使わずに一発でやるテクニックというところかな。


コードの説明をすると

IF(B$1:B$100="男",ROW(B$1:B$100),101)

で、B列が男の場合その行番号、それ以外は101を返す。


SMALL(~,ROW())

その行番目に小さい値を返す。


INDEX(A$1:A$100,~)

A列の~番目の値を返す。

id:tacoru

分かりやすいご説明ありがとうございます。

正にこれです。

2010/11/30 17:05:58

その他の回答(1件)

id:nanntenn No.1

nanntenn回答回数15ベストアンサー獲得回数22010/11/29 23:22:57

ポイント35pt

(マクロで処理した方がすっきりしますが)

関数で処理したいということなので以下ではどうですか。

Sheet1(名前リスト)

A列;番号(1~100) 例では、1~10まで。

B列;名前

C列;性別


Sheet2(抜き出す表)

A列;=IF(Sheet1!C2="男",1,0)  '男であれば1

B列;=SUM($A$2:A2)       'カウント

C列;1~100           '番号1~100(例では1~10)


'男性の行番号

E列;=IF(ISERROR(VLOOKUP(C2,$B$2:$C$10,2,0)=TRUE),"",VLOOKUP(C2,$B$2:$C$10,2,0))

'男性の名前

F列;=IF(ISERROR(VLOOKUP(E2,Sheet1!$A$2:$B$11,2,0)=TRUE),"",VLOOKUP(E2,Sheet1!$A$2:$B$11,2,0))

http://cid-df56e5e5c62da2d4.office.live.com/browse.aspx/%e5%85%a...

id:tacoru

初歩的な質問で恐縮ですが、E列;=IF(ISERROR(VLOOKUP(C2,$B$2:$C$10,2,0)=TRUE),"",VLOOKUP(C2,$B$2:$C$10,2,0)) はE2のセルに、F列;=IF(ISERROR(VLOOKUP(E2,Sheet1!$A$2:$B$11,2,0)=TRUE),"",VLOOKUP(E2,Sheet1!$A$2:$B$11,2,0))

はF2にセルに

入れてみましたが何も起こりません。もう少し詳しくお教え頂ければ助かります。

2010/11/30 07:55:41
id:SALINGER No.2

SALINGER回答回数3454ベストアンサー獲得回数9692010/11/30 10:15:11ここでベストアンサー

ポイント35pt

これは配列数式で1つの関数でできるのですよ。


例えば、名前がA列、B列に男女が書いてあって100行あるとして

C1に次の数式を入れて、Ctrl+Shift+Enterで配列数式にして下にコピーすれば男の名前が抽出されます。

=INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(B$1:B$100),101),ROW()))

配列数式とは何かというのはこちらを見てください。

http://pc.nikkeibp.co.jp/pc21/special/hr/

早い話が作業列を使わずに一発でやるテクニックというところかな。


コードの説明をすると

IF(B$1:B$100="男",ROW(B$1:B$100),101)

で、B列が男の場合その行番号、それ以外は101を返す。


SMALL(~,ROW())

その行番目に小さい値を返す。


INDEX(A$1:A$100,~)

A列の~番目の値を返す。

id:tacoru

分かりやすいご説明ありがとうございます。

正にこれです。

2010/11/30 17:05:58
  • id:taknt
    フィルタをかけてコピーすれば完璧。
  • id:grankoyama
    グラ娘。 2010/11/29 21:40:23
    確かに一発ですけど、自動が望ましいんじゃないですか?どっちなんでしょ。
  • id:taknt
    自動だったら VBAになるだけ。

    というか そんな 関数はないよ。
  • id:grankoyama
    グラ娘。 2010/11/29 22:57:42
    おっしゃるとおりだと思います。
    でも、100人分とか件数が固定されていたら、いろいろ頑張ってVBA無しで作れたりしませんか?。
    もちろん、効率やら何やらであまり意味を成さないことは承知ですが、単なる好奇心からです。
    1セルに込めれる関数やら文字数やらの制限(あるのか無いのか知りませんが)に引っかかってアウトかなぁ。
  • id:taknt
    関数にするということは、たとえば 100人だったら 100ものセルに入れることになります。
    そして たとえば 100番目の人は 既に出てる99人とは別の人ということを 調べないといけません。
    というか 100人すべてが 他の人と だぶらない誰かを みつけないと 100人それぞれユニークにならないのです。

    それを どのようにして やるのでしょうか?
  • id:grankoyama
    グラ娘。 2010/11/29 23:15:19
    だいぶ、質問者さんの利益とはかけ離れた議論になって来てますのでここら辺でおいとまさせていただきます。
    お暇ならワタシのぶろぐ(あいまいもこ)になぞ、来訪くださいませ。
  • id:Silvanus
    フィルタでも良いでしょうし、マクロでも簡単にできますよね。私は
    拒否されてるのか回答できませんので他の方にお任せしますけど。
  • id:nanntenn
    回答入れたけど、こんなのではダメかな?

    http://cid-df56e5e5c62da2d4.photos.live.com/self.aspx/%ef%bc%b0%ef%bc%a3%e8%b3%aa%e5%95%8f/%e6%80%a7%e5%88%a5%e3%81%a7%e6%8c%af%e5%88%86%e3%81%91.jpg
  • id:SALINGER
    ユニークにしなくてはいけないと質問文のどこにも書かれていないので重複削除はしていません。
    また、ソートはしないということなので、順番を維持するようにしています。
    質問文では別のシートにということですが、わかりやすいように同一シートにしています。
    別のシートの場合は、Sheet1!とかを付けるだけです。
  • id:tacoru
    SALINGERさんの配列数式を応用すれば、
    元の表に値を入力してゆくだけで分類された集計表が出来上がっていきますね。
    さすがです。
  • id:tacoru
    SALINGERさん
    すみません。参考までに女を抜き出すときの式を教えて頂けますでしょうか。
    初心者なもので重ね重ねすみません。
  • id:tacoru
    SALINGERさん
    すみません。確定してませんでした!!
    自己解決しました。お騒がせしてすみません。
  • id:nanntenn
    SALINGERさんの回答はスマートな式ですね。勉強になりました。
    エラーの時に非表示にすると完璧ですね。

    =IF(ISERROR(INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(B$1:B$100),101),ROW()))),"",INDEX(A$1:A$100,SMALL(IF(B$1:B$100="男",ROW(B$1:B$100),101),ROW())))

    to tacoruさん
    私のは作業セルを使った場合です。リンク先にファイルを置いておいたのですが
    見なかったのかな? 
  • id:tacoru
    nanntenn さん
    ありがとうございます。さらに完璧になりました。

    ファイルの件、すみませんURLダミーだと勘違いしていました。
    ダウンロードさせて頂きました。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません