ドロップ ダウン リスト 空白。 メンテナンスなし!可変範囲のプルダウンリスト(ドロップダウンリスト)の作り方

【最新版】Excelのプルダウンリストの使い方と空白を表示させない方法

ドロップ ダウン リスト 空白

ドロップダウンリストを1つ目の項目から2つ目にリンクさせる? ドロップダウンリストは入力の手間やミスを減らせる有用な機能です。 ただ、リストの項目の増減による対象範囲の指定なので今回は自動でリストの範囲が項目数によって変化する方法を見ていきます。 なお、今回の最大の利点は 項目を増やしたら自動的にリストの項目も可変していくことです。 リストが増えない場合は、INDIRECT関数のほうが簡単に作れます。 OFFSET関数を入れる前に 実際に入力規則にOFFSET関数で設定していく どんな時に使える? 基本的なドロップダウンリストの使い方や、OFFSET関数に関してはこちらをご覧ください。 今回も使用したファイルを貼っておきますのでご活用ください。 OFFSET関数を入れる前に ドロップダウンリストにするにはデータの入力規則からリストを選び、元の値 リスト範囲 を選択しますが、今回はその元の値にOFFSET関数を使います。 注意しなければならないのは、セルに関数を入れていく場合には「関数の挿入」ボタンを押したり関数を入れ始めれば関数式のヒントが表示されますが、ダイアログボックスに入力する時には関数のヒントが出てきません。 これを引数 引数 といいます。 先に リスト元になる表を作っておきます。 もちろんこれ以上のサイズにすることも可能です。 シート名は『元データ』とでもしておきましょう。 手順1、都道府県名に対して名前の定義をしておく 都道府県名を横書きまたは縦書きで並べたら、名前の定義で都道府県のリスト範囲を名前付けしておきます。 名前の定義に関してはこちらの手順5でも解説しています。 注意するのは 名前の参照範囲です。 ずばり以下のようにしてください。 1行目でセル情報 都道府県名 が入ったセルの個数分が範囲になるという意味ですね。 COUNTA関数に関してはこちらをどうぞ。 参照範囲の名前は『 都道府県』とわかりやすくしておきましょう。 手順2、別シートを用意しておく 先程作成した都道府県と市区町村が入ったシートとは別に、実際にドロップダウンリストを使うシートで以下のように枠を用意しておきましょう。 A2、A3セルに都道府県名が入っていく形になっています。 これは名前の定義によってリスト参照範囲が都道府県と名前を付けた範囲全体に指定されるからです。 A3セル以降に増やす場合はもちろんオートフィルが可能です。 これで都道府県の部分は完了しました。 難しいのはここからです。 今回のリストになる元データを再確認してみます。 市区の数が都道府県によって変わります。 ここでOFFSET関数の数式を思い出してください。 なので1行追加して高さ 市区町村の数 を表示させてしまう事にします。 追加した行で市区町村の数をカウントします。 ちなみにCOUNTA A:A は循環参照になってしまうため使用できません。 循環参照 今回のCOUNTAの場合は以下のように循環参照になります。 もちろん終わりの範囲は必要に合わせて変更できます。 あとは市区町村を実際にデータの入力規則でいれていく段階です。 そして、B2セルにデータの入力規則を設定していきます。 の都道府県が入っている行、 完全一致 -1セル分,INDEX 元データ! 私は頭の中で整理できない時は以下のように書き出しています。 さて、入力規則が完了しました。 実際にA2セルの都道府県の部分を変えて、リストが対象の都道府県毎に変化しているのか確認してみましょう。 完璧です。 きちんと絶対参照・相対参照していればもちろんオートフィルでリストをA3・B3、A4・B4…と増やしていくことも可能です。 どんな時に使える? 冒頭でも言いましたが、リスト範囲を変更しないならINDIRECT関数と名前の定義で作るほうが簡単です。 しかし INDIRECT関数には弱点もあります。 INDIRECT関数の弱点 1、名前の定義で 範囲が関数で変更になる場合は対応できない 2、空欄もリストに入ってしまう為、 リストに不必要な空欄が 表 示されてしまう 弱点1について すごく言葉だけで説明するのが難しいですが、以下のようになっています。 INDIRECT関数は該当するセル名の範囲データを取り出せますが、範囲が可変の状態では反応しません。 いろいろやったりググったりしましたが情報が出てこないで大変でした 弱点2について これは好みの問題ですが、リストのメリットの1つである「入力ミスをなくす」に反しているように感じます。 要は不要なものは、たとえ空欄であってもリストに載るべきではないです。 その点では 今回のOFFSET関数を使うやり方ならば、都道府県や市区町村が増えるとしても都道府県なら1行目、市区町村なら該当する列にただ値を入れるだけで自動的にリストが変化するので、最初の手間だけで済むのがメリットです。 今回は都道府県と市区町村でしたが、都道府県の軸を市区町村に変更して、市区町村の軸を店舗情報などにするなど変更すれば様々な使い方が出てくるので使いこなしていきましょう。

次の

EXCEL

ドロップ ダウン リスト 空白

エクセル2013です。 Sheet1にドロップダウンリストを作成し、Sheet2のC列を参照したいです。 Sheet2のC列は日々入力され行数が増えていくのでリストの範囲を多めにしたいです。 Sheet2のC列はC1003まで値が有ります。 この関数のどこがわるいのでしょうか? よろしくお願いします。 区分の為、空白行を所々に入れており ドロップダウンリストではこの途中空白は 出てくれないと困るそうで、 (私も使って見てそう感じました。 ) リスト内に行の挿入や追加、あるいはコピー貼り付けに自動的に範囲を拡張したいということなら、テーブルの機能を利用することをお勧めします。 このように設定しておけば(絶対参照の数式のように見えますが)テーブル内でデータの追加や削除、あるいは行の挿入などに対して、自動的にデータ範囲を変更してくれます。 Sheet2のC3~C20000を選択し 「テーブルとして書式設定」をクリックすると 色々な表みたいなものが出てきて これをどうしたらいいかよくわかりません とりあえず、1個選択して名前を「入力リスト」で登録。 ですがやはりドロップダウンリストの 途中から末端までが空白でした。 私のやり方が悪いと思うのですが どこがわるいかわかりません。 どうもありがとうございました。 こんばんは! No. 1さんが仰っているように途中の空白セルがある場合 入力規則の「リスト」も空白が表示されてしまいます。 そこでSheet2の使っていないセルにC列データを空白セルがないように表示させます。 D1セルのフィルハンドルでずぃ~~~!っと下へしっかりコピーしておきます。 このD列データを入力規則のリストとして表示させます。 そして、万一重複データがある場合等々、余計な心配が湧いてきます。 私は常にこのファイルをメンテできません。 女性は日々Sheet2のC列にコピペで行追加します。 行挿入する場合も、行削除する場合もあります。 別のエクセルファイルで、 同じくC列に10,000行の値が有るものがあり これをドロップダウンリストで検索してみたのですが 実際10,000行もあっても 探したい値はすぐ見つけれます。 値が検索しやすいように工夫してありました。 で、区分の為、空白行を所々に入れており ドロップダウンリストではこの途中空白は 出てくれないと困るそうで、 (私も使って見てそう感じました。 ) 一番困るのは私がメンテをしな久手いいようにする為 ドロップダウンリストのリスト範囲を、 これくらいあれば足りるだろうで20,000行までにした為 あの小さいドロップダウンリストにおいて 途中から末端までが全部空白になってしまい、 リストの横のバーを少し動かすだけですぐ 空白の所に行ってしまい大変だそうです。 今は2,000行まで入っているからリスト内には 2,000行まで表示して欲しい、 今は190行しかないから、リスト内は 190行目までだけ表示して欲しい という要求です。 途中の空白は、C列の値を区分をする為に使っているようで、 C列内途中空白の行削除は駄目だそうです。 リスト内に途中空白が有るのは好都合でして ドロップダウンリストが表示されて あの狭いリストの途中から末端までが空白 の方が作業がしにくいです。 今1,003行なら1,003行までだけ、今10,000行なら 10,000行までだけリストに出したいのです。 質問内容で詳細が足りず申し訳ありません。 >実際にこれほどのリストデータが必要なのか >どうか疑問がありますし、 >本当に必要な場合でも、 >その中から必要データを選び出すのも大変だと思います。 >そして、万一重複データがある場合等々、 >余計な心配が湧いてきます。 私もそう思ったのですが自分で使用してみると 10,000行あっても全然大丈夫です。 目的の物を即選択できます。 また重複データを選択してもなんら問題が無いのです。 それよりドロップダウンリストが表示されて あの狭いリストの途中から末端までが空白 の方が作業がしにくいです。 今1,003行なら1,003行までだけ、今10,000行なら 10,000行までだけリストに出したいのです。 どうもありがとうございました。 >Sheet2のC列は日々入力され行数が増えていく ご説明から、シート2のC列には「日々手でデータを入力している」という前提でご説明するとして。 途中の空白を上手に抜いた実データだけのリストをプルダウンしたいなら、更に別列にC列の実データだけ上から順に再掲したリストを作成し、そちらを入力リストのネタとするような方法にします。 これならサルにでも出来ますから。 あなたのやり方で表示される863個をよく見てみれば、そこまでの途中の空白がしっかり載ってる事が確認できます。 この回答への補足 会社の女性が使います。 私は常にこのファイルをメンテできません。 女性は日々Sheet2のC列にコピペで行追加します。 行挿入する場合も、行削除する場合もあります。 別のエクセルファイルで、 同じくC列に10,000行の値が有るものがあり これをドロップダウンリストで検索してみたのですが 実際10,000行もあっても 探したい値はすぐ見つけれます。 値が検索しやすいように工夫してありました。 で、区分の為、空白行を所々に入れており ドロップダウンリストではこの途中空白は 出てくれないと困るそうで、 (私も使って見てそう感じました。 ) 一番困るのは私がメンテをしな久手いいようにする為 ドロップダウンリストのリスト範囲を、 これくらいあれば足りるだろうで20,000行までにした為 あの小さいドロップダウンリストにおいて 途中から末端までが全部空白になってしまい、 リストの横のバーを少し動かすだけですぐ 空白の所に行ってしまい大変だそうです。 今は2,000行まで入っているからリスト内には 2,000行まで表示して欲しい、 今は190行しかないから、リスト内は 190行目までだけ表示して欲しい という要求です。 >ドロップダウンリストには Sheet2のC列のセルC863までしか表示がされません。 Sheet2のC列はC1003まで値が有ります。 さて本題ですが、リストの途中に空白セルがあるなら、この部分を削除しないと、提示された数式では空白セル以外のデータ分だけの行しか表示でいませんので、正しい範囲を取得できません。 数式を工夫すれば、空白セルをいれて関数で入力したデータの最終セルまでをリストに入れることができますが、あまりお勧めできません。 データの途中に空白セルがあるなら、この空白セルを一括削除することをお勧めします。 あるいは、データ範囲を選択して「データ」「並べ替え」で昇順などのようにして並べ替えるほうが、空白セルを除去できるだけなく、項目の選択も容易になると思います。 以前同じスレ内で追加質問して ひどく叱られたので 別スレにしました。 申し訳ありません。 C列の値に応じて、D列~AG列まで 色々なデータが入力されており それをVLOOKで引っ張り15シートに展開します。 途中の空白は、C列の値を区分をする為に使っているようで、 C列内途中空白の行削除は駄目だそうです。 それよりドロップダウンリストが表示されて あの狭いリストの途中から末端までが空白 の方が作業がしにくいです。 今1,003行なら1,003行までだけ、今10,000行なら 10,000行までだけリストに出したいのです。 どうもありがとうございました。 A ベストアンサー No. 2です。 補足拝見しました。 失礼しました。 この記述がSheet1の誤りでした。 名前を定義する数式の方は質問文に書いたとおりです。 あれ、おかしいですね。 エラーになってしまいましたか。 そのままコピーして貼り付ければ、私の環境ではエラーにはならないのですが…。 いろいろパターンを変えて試してみたのですが、こちらではそのようなエラーが出ないので、ちょっと原因がわかりません。 もう一度コピーした数式を見直していただけますでしょうか?数式の入力中に矢印キーなどを使うと、カーソルが移動せずに移動先のセル番地が入力されてしまい、数式が狂ってしまいますが、そのようなことはないでしょうか。 ただし、リストの一番下の項目の下に空白行がたくさんついてきてしまいますが…。 2です。 補足拝見しました。 失礼しました。 この記述がSheet1の誤りでした。 名前を定義する数式の方は質問文に書いたとおりです。 あれ、おかしいですね。 エラーになってしまいましたか。 そのままコピーして貼り付ければ、私の環境ではエラーにはならないのですが…。 A ベストアンサー 途中のセルに空白がある場合は、空白セルを除くのは難しいかと。 >調べたところ、名前の定義方法で実現できるみたいですが これは末尾のセルのことでしょう。 あとはvbaくらいしか思いつきませんが、、、 あまり便利にはなりませんが) A3に設定しています。 Sub test Dim r As Range Dim v For Each r In Range "a1:f1" If r. Value Next r With Range "A3". Validation. Delete. しかし、下記の方法ですと、空白のセル(参照セルに値が無かった場合に空白""が返されたセル)が空白のままドロップダウンリストに表示されてしまい、目的の氏名をリストから探す時に手間がかかってしまいます。 このように、数式が入力された結果空白になっているセルを、ドロップダウンリストで非表示にする方法はないでしょうか? どうぞよろしくお願い申し上げます。 以下の数式を入力したセルを参照して、氏名をドロップダウンリストから選べるようにしたいと考えております。 しかし、下記の方法ですと、空白のセル(参照セルに値が無かった場合に空白""が返されたセル)が空白のままドロップダウンリストに表示されてしまい、目的の氏名をリストから探す時に手間がかかってしまいます。 <想定しているシート> A B C D 1 2 3 氏名 物品 帳票... 2回目以降は""をセットする。 D4セルを必要数下へコピーします。 C4セルを必要数下へコピーします。 IFERROR関数はExcel 2007以降のバージョンに組み込まれています。 Excel 2003以前の場合は次の数式で対処してください。 2回目以降は""をセットする。 D4セルを必要数下へコピーします。 C4セルを必要数下... A ベストアンサー 同じ質問が結構よく出てますが、そんなに初歩的でもありません 別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。 後は、下方向、右方向にコピー。 A:A,LARGE INDEX Sheet1! というのが前提です。 複数のセルがHITすると関係ないセルの値が返るので、 場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。 動作の概要 【検査範囲】から【検査値】を探し、 最初にHITしたセルについて、右隣のセルの値を返す。 以上ご参考まで。 というのが前提です。 複数のセルがHITすると関係ないセルの値が返るので、 場... Q 下のような「得点」という名前のシートがあります。 (「田中」のセルがA1です。 ) [ 田中 ][ 10 ][ 200 ] [ 山田 ][ 21 ][ 150 ] [ 佐藤 ][ 76 ][ 250 ] [ 鈴木 ][ 53 ][ 350 ] 別のシートのA1セルに、「佐藤」と入力すると、 [ 佐藤 ] 「得点」シートから「佐藤」の列を見つけて、B1、C1に [ 佐藤 ][ 76 ][ 250 ] のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。 「得点」シートでは氏名が重複する事はありません。 IF文を使うと思うのですが、いまいち良く分かりませんでした。 よろしくおねがい致します。 Q 只今エクセル勉強中です。 よろしくお願いします。 画像の貼り付けがよくわからなかったので シート画面のコピーを添付データにしています。 A列に分類(データA4:A5) B・C列に商品というデータがあるとします。 名前の定義 範囲から作成でA3:C5を上端行基準で 分類・パソコン・プリンタと名前をつけます。 E2の入力値に対してF2の入力範囲を切替えたいと思います。 ここまではOKなのですが、分類・パソコン・プリンタの種類が増える事を 想定し名前範囲を拡張したいと思います。 そして6行目以降に項目を追加すると E2セルではプルダウンに追加したものが反映されます。 しかしF2ではプルダウン項目がなくなりました。 何が間違っているのでしょうか? Excel2007使用です。 よろしくお願いします。 只今エクセル勉強中です。 よろしくお願いします。 画像の貼り付けがよくわからなかったので シート画面のコピーを添付データにしています。 A列に分類(データA4:A5) B・C列に商品というデータがあるとします。 名前の定義 範囲から作成でA3:C5を上端行基準で 分類・パソコン・プリンタと名前をつけます。 E2の入力値に対してF2の入力範囲を切替えたいと思います。 ここまではOKなのですが、分類・パソコン・... A ベストアンサー No. 1です。 補足の件について・・・ 前半部分は仰る通りで間違いありません。 それを判定するために A列を基準として何列右側の列をCOUNTA関数の範囲にするか?という数式です。 それをMATCH関数で判定しています。 すなわち A列全体を基準列として、MATCH関数でE2と一致する列番号を取得します。 仮にそればB列にあれば「2」という結果になりますが、A列から2列右ではC列になってしまいますので 「-1」としてB列全体がCOUNTA関数の「値1」の範囲となります。 1です。 補足の件について・・・ 前半部分は仰る通りで間違いありません。

次の

【最新版】Excelのプルダウンリストの使い方と空白を表示させない方法

ドロップ ダウン リスト 空白

作成するドロップダウンリスト• COUNTIF関数、INDEX関数、OFFSET関数等を使って実現します。 今回は、E~F列を作業列とし、C列に入力された重複のあるランダムな値から、重複のないリストをA1セルに作成します。 まずは作ってみます C2セル以下に入力された値に対して、F2セルに下記の式を入れて下方へオートフィル(連続データ)します。 COUNTIF 範囲, 検索値 指定した範囲において、検索条件に一致するセルの個数を返す。 ROW [参照] 参照の行番号を返す。 参照が省略されている場合は自セルの行番号を返す。 の4つです。 これで、F列には、C列に入力された値が重複する場合でも、行番号が最も小さいセルのみの行番号が入ります。 参照が省略されている場合は自セルの行番号を返す。 COUNT 値1, 値2,… 範囲内において、数値が含まれるセルの個数を返す。 INDEX 配列, 行番号, [列番号] 行番号と列番号で指定される値またはセルの参照を返します。 SMALL 範囲, 順位 指定された範囲で、順位番目に小さな値を返します。 の5つです。 ROW E1 行番号が1。 下方へのオートフィルで1,2,3,…(E1,E2,E3,…)の連続データとするための便宜上。 必要なのは行番号なので、必ずしもE列である必要はないが、削除や移動のないセルであるよう注意。 以下、この ROW E1 ,ROW E2 ,ROW E3 ,… を n とする。 " -1,1 使用している関数は、 OFFSET 参照, 行数, 列数, [高さ], [幅] 参照から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返す。 COUNTIF 範囲, 検索値 指定した範囲において、検索条件に一致するセルの個数を返す。 の2つです。 " E列に、空白でないセルがいくつあるか。 " -1 E列において文字列を含むセルの全個数から、1行目(項目名セル)の個数1を差し引く。 " -1,1 E2セルを起点として、E列において項目名セル以外の文字列を含むセルの個数分の高さで幅が1の範囲を返す(幅は省略可能)。 以上、ご参考になれば幸いです。

次の