質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

88.83%

コンボボックス(フォームコントロール)のリストに可変するデータの空白セルを除いて表示させたい。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 1
  • VIEW 2,090

7snow

score 12

前提・実現したいこと

Excelで、コンボボックス(フォームコントロール)を使い、同一ブック内の別のシートにあるA列のデータをリスト表示させたい。
ただし、A列のデータは可変するため、空白セルを除いてリスト化したい。

下記の試したことに書いてますが、名前の定義を活用して作成しようしています。しかし、上記が実現できるのであれば、VBAで記述する方法でも問題ありません。

お手数かけますが、ご回答よろしくお願いします。

試したこと

可変するデータに対応するために、名前の定義で参照範囲にOFFSET関数を使用して、空白セルを除いて参照できるようにした。
続いて、コンボボックスのコントロールの書式設定のコントロールタブの入力範囲に、上で作成した名前を記述したが、エラー「参照が正しくありません。」が発生した。

補足情報(FW/ツールのバージョンなど)

Excel 2016

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • ttyp03

    2019/05/07 13:54

    名前の定義で範囲を動的にすることができているのでしたら、現状何が出来ていないのでしょうか?

    キャンセル

  • 7snow

    2019/05/08 22:39

    セルの入力定義のリストへ、定義した名前を指定すれば、データに応じて可変することが確認できたのですが、やりたいことは、コンボボックス(フォームコントロール)で動的に変化する値のリストを表示させることです。
    製造履歴をコピーしたときに、履歴によって型名が1から50種類ぐらいに可変する、数件しかない場合に、空白行が目立ち見栄えが悪くなることからリストを可変させたく思っています。
    尚、コンボボックス(フォームコントロール)への名前の設定は、コントロールの書式設定のコントロールタブの入力範囲に入力しようとしていますが、入力すると名前で指定できたないため、別の手段で実現させる方法がないか教えて頂きたく思ってます。
    解決策がわかりましたら、ご教示いただきたく、よろしくお願いします。

    キャンセル

  • ttyp03

    2019/05/09 08:57

    例えばA列を名前の定義で「ComboItems」と定義しておくとします。コンボボックスの書式設定を開いて「入力範囲」に「ComboItems」と入れれば名前の定義で設定した範囲でアイテムとして出てきますが、それではダメということでしょうか?

    キャンセル

  • 7snow

    2019/05/11 12:52

    ttyp03さんにコメントいただき、再確認したところttyp03さん同様に問題なくできたため、エラー原因を調査したところ私のミスということが判明しました。
    原因は、名前の定義で参照先に指定したOFFSET関数の結果がエラーになる場合(※)があり、エラーしているときにコンボボックス(フォームコントロール)で名前を指定すると「参照が正しくありません。」という結果になっていました。お恥ずかしい結果ですが、おかげさまで解決することができました。ありがとうございました。
    ※エラーになる場合
    A列を参照先にしていましたが、下記関数としていた為、A列のタイトルをカウントから外すためOFFSETの高さを-1していました。しかし、A2以降のデータがないとき高さが0になってしまい、OFFSET関数がエラーを返していました。A2列以降にデータが入っていればコンボボックス(フォームコントロール)で名前を指定してもエラーになりませんでした。
    【修正前】=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
    【修正後】=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)  ・・・ -1を削除

    キャンセル

回答 2

check解決した方法

0

コンボボックス(フォームコントロール)の入力範囲に、名前を使用することができました。
(名前の定義で指定したOFFSET関数がエラーしているため、設定できないと勘違いしていました。)
※詳細は、ttyp03さんへの回答内容を参照ください。

Sheet1のA列にリスト化したいデータがある場合、名前の定義の参照範囲に下記を記述することで可変データに対応したリスト表示ができます。

手順1.名前の定義の参照範囲に下記を入力(「ComboItems」という名前で定義しました。)

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1)

イメージ説明

手順2.コンボボックス(フォームコントロール)のコントロールの書式設定の入力範囲に手順1で定義した名前を記述
イメージ説明

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

0

Excelのバージョンが2013以降であれば、テーブルが有効ですね。
名前の定義が自動で変動してくれる強力な機能です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/05/11 12:56

    テーブルを使う方法があるということ、知りませんでした。可変するリストを作る時の1手段にしたいと思います。ご回答ありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 88.83%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • Excelに関する質問
  • コンボボックス(フォームコントロール)のリストに可変するデータの空白セルを除いて表示させたい。