親子におすすめの新型プラネタリウムとは?

エクセルで、
sheet1に
   A   B   C
7  鈴木     早番
8  高橋     遅番
9  後藤     早番
10  佐藤     遅番
11  中田     遅番

という表を作成しているのですが、
sheet2に
  A   B
5     鈴木
6     後藤
7
8
というようにB5~B8のセルに順番にsheet1で早番となっている人を抽出していきたいです。
関数でこのようなことはできますでしょうか。
宜しくお願いいたします。

質問者からの補足コメント

  • この式の最後の方の「-6」というところの6はどこから来ていますでしょうか?
    また、最後のROW(A1)はどこからきていますでしょうか?

    No.1の回答に寄せられた補足コメントです。 補足日時:2015/12/10 00:39

A 回答 (2件)

No.1です。



補足の件について
まず簡単な方から・・・
>最後のROW(A1)はどこからきていますでしょうか?
これはSMALL関数の「順位」の部分になります。
ROW(A1)
はどこの列でも構いません(Z1やAA1でもOK)
どこか使っていないセルに
=ROW(A1)
と入れてみてください、「1」が表示されますので「1番小さい順位」となります。
それをフィルハンドルで下へコピーすると、2・3・・・となり、2番目・3番目・・・というコトです。

さて、このSMALL関数はINDEX関数の「行番号」の部分に入れていますので、
INDEX関数の範囲をA7~A100としました。
結果が欲しいのは範囲指定した何行目か?というコトです。
それについては↓の画像で説明した方が判りやすいと思います。
画像ではE7セルに
=C7="早番"
として下へコピーしています。
これで「早番」の行だけが「TRUE」となり、前回の数式
>C$7:C$100="早番",ROW(A$7:A$100)
の部分でA7~A100の行番号を掛けていますので、行番号の7・9の行が欲しい行となります。

ただし、INDEX関数でA7以降を範囲指定していますので、
実際は範囲指定した1・3行目が結果として欲しい訳です。
その行合わせのために -6 としています。

最終的に前回の配列数式の結論として
INDEX関数で範囲指定した「早番」の行の小さい順にその行のA列を表示させている!
というコトになります。

※ 今回の質問は7行目以降が実データのようでしたので前回のような数式にしましたが、
1行目が項目行でデータは2行目以降にあるか、1~6行目に「早番」というデータがない場合
当然1行目から範囲指定し

=IFERROR(INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!C$1:C$100="早番",ROW(A$1:A$100)),ROW(A1))),"")

という配列数式で大丈夫です。

以上長々と書きましたが
この程度でよろしいでしょうか?m(_ _)m
「sheet1のデータをsheet2に抽出」の回答画像2
    • good
    • 0

こんばんは!



一気にやる方法です。
Sheet2のB5セルに

=IFERROR(INDEX(Sheet1!A$7:A$100,SMALL(IF(Sheet1!C$7:C$100="早番",ROW(A$7:A$100)-6),ROW(A1))),"")

配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → B5セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
最後にB5セルのフィルハンドルで下へずぃ~~~!っとコピー!

これでお望みどおりになると思います。m(_ _)m
この回答への補足あり
    • good
    • 0
この回答へのお礼

簡単にできました。
ありがとうございます。

お礼日時:2015/12/10 00:05

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルでsheet2からsheet1へデータを抽出し必要な部分だけを抽出する方法を教えてください。

データベースからエクセルファイルのデータにしたものから
必要データだけを表示できるように編集したいのですが、日々20~30件増えます。
今までは列ごと削除して作成していましたが、A列のみをコピーしてそれを参照して
必要列のみを表示させるにはどうすればいいでしょうか。

すべてのデータをsheet2に貼り付け、そこから必要項目をsheet1に表示させたいです。
A列を参照して表示させるようにデータ部分の数式を↓に方向にドラッグコピーして使用するには、
おぼろげにVLOOKUPを使うのでは?と思うのですが方法がわかりません。

画像を見ていただきアドバイスをいただけないでしょうか。
よろしくお願いします。

Aベストアンサー

こんにちは!

両SheetともA列の並びは一緒なのですね?

Sheet1のB2セルに
=IF(OR($A2="",B$1=""),"",INDEX(Sheet2!$A:$Z,ROW(),MATCH(B$1,Sheet2!$1:$1,0)))
という数式を入れフィルハンドルで列・行方向にコピーしてみてください。
(Sheet2のセル全体を範囲指定しても良かったのですが、無意味のようなのでA~Z列を範囲としてみました)

※ 両SheetのA列並びが異なる場合は
=IF(OR($A2="",B$1=""),"",INDEX(Sheet2!$A:$Z,MATCH($A2,Sheet2!$A:$A,0),MATCH(B$1,Sheet2!$1:$1,0)))
という数式にしてください。m(_ _)m

Qエクセルでsheet1のデータをそのままsheet2に移す時

sheet1のデータをsheet2の対応するセルにそのまま移そうと思い、sheet2のA1に 
=sheet1!A1 と入力しそれを表全体にコピーしました。

この方法だとsheet1にデータが入力されているセルはそのままデータが移りますが、sheet1にデータが入力されていないセルでは、対応するsheet2のセルに0が入力されてしまいます。
データのないセルをそのまま空白にしておくには、どうすればよろしいでしょうか。

Aベストアンサー

シートの値をコピーするなら皆さんの回答でOKです。

質問者さんの質問の目的がわからないのですが、
sheet1への入力をsheet2に常に自動で反映させたい場合もあるかと思うので、
>データのないセルをそのまま空白にしておくには、どうすればよろしいでしょうか。
に答えます。

<その1:ゼロは表示しない>
「ツール」-「オプション」から「表示」タブの「ゼロ値」のチェックを外すと0が表示されなくなります。ただ、これだとsheet1に0がある場合にその0も表示されなくなってしまいます。これじゃ困る場合、その2へ。

<その2:入力されてないセルを判定する>
最初にやったsheet2のA1への入力に「=sheet1!A1」ではなく「=IF(Sheet1!A1<>"",Sheet1!A1,"")」と入力して全てのセルにコピーしてみてください。
#これはIF関数という奴で「sheet1のA1が入力されていたらsheet1のA1を、入力されてなければ空白を表示」という意味になります。


人気Q&Aランキング