![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
No.4ベストアンサー
- 回答日時:
もし、A列等に出席番号等の「同じ列の中に、同じ値が複数入力されている事があり得ないデータ」が入力されている場合には、次の様な方法もあります。
今仮に、
>エクセルの入門書などによくある学校の成績表で、縦軸B3からB25までに氏名、横軸C3からCQまで科目名があるとします。
の成績表が存在しているシートのシート名がSheet1であり、Sheet2に「Sheet1のJの列にある科目」で100点を取った生徒のデータのみを表示させるものとします。
まず、Sheet2のA27セルに次の関数を入力して下さい。
=Sheet1!$A$2
次に、Sheet2のA28セルに次の関数を入力して下さい。
=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$A:$A,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$A:$A,ROWS(Sheet1!$A:$A)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(A27,Sheet1!$A:$A,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$A:$A)),0)))
次に、Sheet2のB28セルに次の関数を入力して下さい。
=IF($A28="","",VLOOKUP($A28,Sheet1!$A:$B,2,FALSE))
次に、Sheet2のA28~B28の範囲をまとめてコピーして、Sheet2のA29~B51の範囲に貼り付けて下さい。
以上です。
因みに、もし、生徒の中に同姓同名の生徒が存在しない事が保障されている場合には、A列を表示させる事なしに、Sheet2のB28セルに次の様な関数を入力してから、Sheet2のB28セルをコピーして、Sheet2のB29~B51の範囲に貼り付けるだけで済みます。(Sheet2のB27セルに、Sheet1のB2セルと同じ値を入力しておく必要はあります)
=IF(ROWS($28:28)>COUNTIF(Sheet1!$J:$J,"=100"),"",INDEX(INDEX(Sheet1!$B:$B,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$B:$B,ROWS(Sheet1!$B:$B)),MATCH(100,INDEX(Sheet1!$J:$J,MATCH(B27,Sheet1!$B:$B,0)+1):INDEX(Sheet1!$J:$J,ROWS(Sheet1!$B:$B)),0)))
![「エクセルの参照結果を空欄を詰めて表示させ」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/8/1235215_5497f2ecdae84/M.jpg)
No.5
- 回答日時:
こんにちは!
質問ではJ列限定のようですが、科目名が変わっても対応できるようにしてみました。
元データはSheet1にあり、Sheet2に表示するようにしています。
↓の画像で左側がSheet1とし、右側がSheet2でSheet2のB1セルに検索科目名を入力すると
100点の人の名前を表示します。
Sheet2のA4セルに
=IFERROR(INDEX(Sheet1!B$1:B$50,SMALL(IF(OFFSET(Sheet1!B$1:B$50,,MATCH(B$1,Sheet1!B$2:CQ$2,0)-1,,1)=100,ROW(A$1:A$50)),ROW(A1))),"")
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面からコピー&ペーストする場合は上記数式をドラッグ&コピー → Sheet2のA4セルを選択 → 数式バー内に貼り付け
→ そのまま(編集可能なまま)Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
このA4セルをオートフィルで下へコピー!
これで画像のような感じになります。
※ 質問では別SheetのB28セルから表示したいというコトですが、セル配置は実状に合わせてください。
※ IFERROR関数を使用していますので、Excel2007以降のバージョンで可能です。m(_ _)m
![「エクセルの参照結果を空欄を詰めて表示させ」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/667667_5497ebcd6bdf5/M.jpg)
この回答へのお礼
お礼日時:2014/02/24 13:30
初めての質問だったのですが、こんなに早く回答が来て大変感激しました。
しかも科目も選べるようになっていてすごいと思いました。
助かりました。ありがとうございました。
No.2
- 回答日時:
自分なら点数が入力されているシートに作業列を設け、そこにマークを入れ、そのマークを頼りに表示させるようにします。
解答には参照するセルのシートを指定する”Sheet1!”の記述を省いて記述しますので、質問者さんの環境に合わせて追加してください。
(記述すると見づらくなって、何をしているのか分かりにくくなるんですよ)
あと、自分はすべて全角文字で入力していますので、本文をコピーするときは注意してください。
(半角文字だとメールで文章が途切れるんですよ)
で、本題。
CR列が空いているとして、CR3セルへ
=COUNTIF(J$3:J3,”=100”)
と入力しCR25セルまでコピーします。
これで100点を取った人に対応する行で数字が加算されます。
そしてこれが1から1ずつ増えている行番号をMATCH関数で拾えばOK。
あとはINDEX関数で対応するB列を参照すればいいです。
=INDEX(B$3:B$25,MATCH(ROW(A1),CR$3:CR$25))
こんな感じ。
で、あとはこれを23行分コピーです。
なお、エラーに関して何も処置していませんので、
100点を取った人の数が23人より少ない時 #N/A のエラーが返ってきます。
そこは自身で工夫してみてください。
汎用性を持たせるためにJ列をINDIRECT関数で指定するようにすると、他の列を検索することが楽になるかもしれません。
どこかのセルに”J”と入れたらJ列・・・と言うようにね。
(結構めんどくさいことをしなければならないので割愛ですw)
この回答へのお礼
お礼日時:2014/02/24 13:29
初めての質問だったのですが、こんなに早く回答が来て、かつこんなに親切な文面に大変感激しました。
助かりました。ありがとうございました。
No.1
- 回答日時:
>基本的な関数を使って、100点を取った人のみ抽出して表示させることはできる
では同様に基本的な関数だけを使って。
別シートのA3に
=IF(成績表!J3=100,ROW(J3),"")
と記入、以下コピー
#参考
上述は別に3行目から始める必要は全くありません。
B28に
=IF(ROW(B1)>COUNT(A$3:A$25),"",INDEX(成績表!B:B,SMALL(A$3:A$25,ROW(B1))))
と記入、以下コピー。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Excel(エクセル) エクセルの数式について教えて下さい。 8 2023/05/27 12:17
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Excel(エクセル) エクセルで”入力シート”の文字書式の変更を”出力シート”で同じ文字書式で印刷したいです。VBA希望 4 2023/04/24 11:07
- Excel(エクセル) エクセルで、2つのセルに検索値を入れて結果を一覧表示させる 1 2023/07/10 10:26
- その他(プログラミング・Web制作) python コードについて(初学者です) 3 2023/07/20 14:44
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) 【エクセル」 特定のセルで条件抽出した列を、別シートに上から詰めて表示したい。 8 2022/04/08 16:00
- Excel(エクセル) エクセルで最初に値が入っているセルを見つける方法はありますか? 2 2023/07/18 14:58
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
エクセルの数式バーのフォント...
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelに詳しい方! B列が「日...
-
西暦や和暦の表示をyyyymmdd表...
-
Excelで50個のセルに同じ文字を...
-
Excelの数式について教えてくだ...
-
Excel VBAで全ての矢印を赤色に...
-
エクセル関数を使って
-
祝日と土曜、日曜の合計をカウ...
-
xlsxファイルを保存する際にPDF...
-
Excelについて
-
エクセルVBA、別ブックへ転記す...
-
エクセルで「ページレイアウト...
-
2列に入っているデータを1列...
-
【ExcelVBA】名前を付けて保存→...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報