図書室の本の検索で分類番号も出したい。

質問

EXCEL初心者です。
宜しくお願いします。

以前　図書室の本の検索でお聞きしました。
さらに追加してお聞きします。

シート１
C1に書籍名を入力すると
D1に「図書室にはあります」「図書室にはありません」を表示する。
方法を教えてもらいました。
使わせてもらっています。

今度はさらに
E1に「図書室に本がある場合」は分類番号を表示したい。
「図書室に本がない場合」はE1に「＊＊＊」を表示したい。

シート２
B２～B15000には番号
C２～C15000には書籍名
D２～D15000には著者名
E２～E１5000には分類番号
が、入っています。

宜しくお願いたします。

回答No.7

K Kazz（@JazzCorp） · ベストアンサー

ソレ、シート２でやると、トッテモ分かり易くなって、シワァワセ、、、

お礼

ありがとうございました。

回答No.6

kagakusuki

>D1に「図書室にはあります」「図書室にはありません」を表示する。

との事ですが、該当する書籍が蔵書にある場合には、あるという事だけを表示させるよりも、著者名を表示させた方が宜しいのではないでしょうか？
　その方法は以下の通りです。

　まず、D1セルに次の数式を入力して下さい。

=IF($C$1="","",IF(COUNTIF($C$2:INDEX($C:$C,ROWS($C:$C)),$C$1),VLOOKUP($C$1,$C$2:INDEX($D:$D,ROWS($C:$C)),2,FALSE),"(図書室にはありません)"))

　次に、E1セルに次の数式を入力して下さい。

=IF($C$1="","",IF($D$1="(図書室にはありません)","***",VLOOKUP($C$1,$C$2:INDEX($E:$E,ROWS($C:$C)),3,FALSE)))

　次に以下の操作を行って下さい。

【ExcelのバージョンがExcel2007よりも前のバージョンの場合】
D1セルを選択
　　↓
メニューの[書式]ボタンをクリック
　　↓
現れた選択肢の中にある[条件付き書式]をクリック
　　↓
現れた「条件付き書式の設定」ダイアログボックスの左端の欄をクリック
　　↓
現れた選択肢の中にある「セルの値が」をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しい]をクリック
　　↓
「条件付き書式の設定」ダイアログボックスの左から3番目の欄に

(図書室にはありません)

と入力
　　↓
「条件付き書式の設定」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[パターン]タブをクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式の設定」ダイアログボックスの[OK]ボタンをクリック

【ExcelのバージョンがExcel2007以降のバージョンの場合】
Excelウィンドウの上の方にある[ホーム]タブをクリック
　　↓
D1セルを選択
　　↓
選択されているセル範囲を変えないまま、「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[新しいルール]をクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[指定の値を含むセルだけを書式設定]をクリック
　　↓
現れた「次のセルのみを書式設定」という箇所の左端の欄をクリック
　　↓
現れた選択肢の中にある[セルの値]をクリック
　　↓
現れた「次のセルのみを書式設定」という箇所の左から2番目の欄をクリック
　　↓
現れた選択肢の中にある[次の値に等しい]をクリック
　　↓
「次のセルのみを書式設定」という箇所の左から3番目の欄に

(図書室にはありません)

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた色のサンプルの中にある赤色の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック

　以上です。

お礼

御丁寧に色々アドバイスありがとうございました。

利用者の視点に立ったアドバイスをいただき
ありがとうございました。

大いに参考にさせていただきます。

補足

言葉が足りなくて申し訳ありません
シートの指定をしなくてよいのですか。

SHEET１は「検索」
SHEET２は「一覧」とシートの名前をつけています。

SHEET１　「検索」
C２に書籍名を入力すると
D２に「図書室にあります」「図書室にはありません」を表示する。(すでにできています。）

今度はさらに
E２に「図書室に本がある場合」は
SHEET２「一覧」から分類番号を持ってきて、表示したい。
「図書室に本がない場合」はE２に「＊＊＊」を表示したい。

SHEET２「一覧」には

番号、書籍名、著者名、分類番号の
データが入っています。
B２～B1500１　には番号
C２～C1500１　には書籍名
D２～D1500１　には著者名
E２～E１500１　には分類番号
が、入っています。

宜しくお願いします。

回答No.5

K Kazz（@JazzCorp）

早速訂正、
=IF(D1="図書室にはあります",VLOOKUP(C1,$C$2:$E$15000,3,FALSE),IF(C1="","","***"))

補足

言葉が足りなくて申し訳ありません
シートの指定をしなくてよいのですか。

SHEET１は「検索」
SHEET２は「一覧」とシートの名前をつけています。

SHEET１　「検索」
C２に書籍名を入力すると
D２に「図書室にあります」「図書室にはありません」を表示する。(すでにできています。）

今度はさらに
E２に「図書室に本がある場合」は
SHEET２「一覧」から分類番号を持ってきて、表示したい。
「図書室に本がない場合」はE２に「＊＊＊」を表示したい。

SHEET２「一覧」には

番号、書籍名、著者名、分類番号の
データが入っています。
B２～B1500１　には番号
C２～C1500１　には書籍名
D２～D1500１　には著者名
E２～E１500１　には分類番号
が、入っています。

宜しくお願いします。

回答No.4

kagakusuki

申し訳御座いません、回答:No.2における「データの入力規則」ダイアログボックスの[設定]タブの「元の値」欄に入力する数式、

=OFFSET(C$2,,,MATCH("*?",$D:$D,-1)-ROW($C$2)+1)

にはバグがありました。
　ですから、次の数式と差し替えて下さい。

=OFFSET(C$2,,,MATCH("*?",OFFSET($D$2,,,ROWS($D:$D)-ROW($C$2)+1),-1)-ROW($C$2)+1)

回答No.3

K Kazz（@JazzCorp）

そのＤ１がどんな按配になってるのか？、分からんですが、、、

=IF($D$1="図書室にはあります",VLOOKUP($C$1,$C$2:$E$15000,3,FALSE),IF($D$1="図書室にはあります","","***"))

回答No.2

kagakusuki

尚、E1セルに入力する数式を次の様なものにしますと、リストの行数が何行であるのかを気にする事無く、後からでも自由に書籍データを追加する事が出来ます。

=IF($D$1="図書室にはあります",VLOOKUP($C$1,$C$2:INDEX($E:$E,ROWS($C:$C)),3,FALSE),IF($D$1="図書室にはありません","***",""))

或いは

=IF($D$1="図書室にはあります",VLOOKUP($C$1,$C$2:INDEX($E:$E,MATCH("*?",$D:$D,-1)),3,FALSE),IF($D$1="図書室にはありません","***",""))

　それから、以下の様な操作を行って、C1セルに入力規則を設定しますと、書籍の入力が楽になります。

【ExcelのバージョンがExcel2007よりも前のバージョンの場合】
C1セルを選択
　　↓
「メニュー」バーの[データ]ボタンをクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に次の数式を入力

=OFFSET(C$2,,,MATCH("*?",$D:$D,-1)-ROW($C$2)+1)
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所をクリックして、チェックを外す
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

【ExcelのバージョンがExcel2007以降のバージョンの場合】
C1セルを選択
　　↓
Excelウィンドウの上の方にある[データ]タブをクリック
　　↓
現れた[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
現れた「元の値」欄に次の数式を入力

=OFFSET(C$2,,,MATCH("*?",$D:$D,-1)-ROW($C$2)+1)
　　↓
「データの入力規則」ダイアログボックスの[エラーメッセージ]タブをクリック
　　↓
現れた「無効なデータが入力されたらエラーメッセージを表示する」と記されている箇所をクリックして、チェックを外す
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック

　尚、上記の

=IF($D$1="図書室にはあります",VLOOKUP($C$1,$C$2:INDEX($E:$E,MATCH("*?",$D:$D,-1)),3,FALSE),IF($D$1="図書室にはありません","***",""))

や

=OFFSET(C$2,,,MATCH("*?",$D:$D,-1)-ROW($C$2)+1)

という数式は、どちらもD列に文字列が入力されている最下段の行が何行目になるのかを、自動的に調べて、その行までを検索対象やリストの表示範囲とするものですので、最も下に入力されている書籍データの著者名が入力されていなかった場合や、最も下に入力されている書籍データの著者名欄に文字ではなく数値が入力されていた場合には、最も下にある書籍データを取り扱う事が出来なくなりますので注意して下さい。（要するに、著者名は必ず入力して下さいという事）

回答No.1

kagakusuki

E1セルに次の数式を入力されると良いと思います。

=IF($D$1="図書室にはあります",VLOOKUP($C$1,$C$2:$E$15000,3,FALSE),IF($D$1="図書室にはありません","***",""))