只今エクセル勉強中です。よろしくお願いします。
画像の貼り付けがよくわからなかったので
シート画面のコピーを添付データにしています。
A列に分類(データA4:A5)
B・C列に商品というデータがあるとします。
名前の定義 範囲から作成でA3:C5を上端行基準で
分類・パソコン・プリンタと名前をつけます。
E2セルにデータの入力規制で
=分類
とします。
E2の入力値に対してF2の入力範囲を切替えたいと思います。
そこでF2の入力規則で
=INDIRECT(E2)
とします。
ここまではOKなのですが、分類・パソコン・プリンタの種類が増える事を
想定し名前範囲を拡張したいと思います。
そこで名前の定義で
分類の範囲を
=OFFSET(Sheet1!$A$4,0,0,COUNTA(Sheet1!$A:$A)-1,1)
に変更
パソコンの範囲を
=OFFSET(Sheet1!$B$4,0,0,COUNTA(Sheet1!$B:$B)-1,1)
プリンタ範囲も同様に変更しました。
そして6行目以降に項目を追加すると
E2セルではプルダウンに追加したものが反映されます。
しかしF2ではプルダウン項目がなくなりました。
何が間違っているのでしょうか?
Excel2007使用です。
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
No.1です。
補足の件について・・・
前半部分は仰る通りで間違いありません。
後半部分の
>その後の
>COUNTA (OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))
>ここがよく解っていません。
>OFFSETの基準がA:Aというのはどういう事なのでしょうか?
>A列全体??セルに=A:A は0になりますが・・・???
に関してですが、
ココで一番重要なのは
COUNTA関数の「範囲」を決定してやらなければならないコトです。
仮にその範囲が B列と判っている場合は
=COUNTA(B:B)
でOKなのですが、今回の質問の場合はE列入力規則のデータによって列が決まってきます。
それを判定するために
A列を基準として何列右側の列をCOUNTA関数の範囲にするか?という数式です。
それをMATCH関数で判定しています。
結局上記数式の「B:B」部分が
前回投稿した数式の
>OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)
の部分となります。
すなわち A列全体を基準列として、MATCH関数でE2と一致する列番号を取得します。
仮にそればB列にあれば「2」という結果になりますが、A列から2列右ではC列になってしまいますので
「-1」としてB列全体がCOUNTA関数の「値1」の範囲となります。
以上長々と書きましたがこの程度でよろしいでしょうか?m(_ _)m
No.4
- 回答日時:
NO.1・3です。
たびたびごめんなさい。
No.1の数式で少し抜けている部分がありました。
おそらくリストで余計な空白が一つ表示されていたと思います。
数式を↓に変更してください。
=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))-1)
数式でなぜCOUNTA関数が必要か?という説明が抜けていました。
リスト表示させる際に余分な空白セルを表示させないためのCOUNTA関数です。
No.3の説明で列決定方法は理解していただいたとおもいますが、
OFFSET関数の「高さ」部分にCOUNTA関数を使用していますので、
検索した列のデータ数だけをリスト候補に表示させるために上記のような数式にしています。
そして、COUNTA関数で得られたデータ数は「項目」も含まれていますので、
OFFSET関数で項目行の1行下を基準として、その列のデータ数より「-1」の高さ分だけをリスト表示させています。
何度も失礼しました。m(_ _)m
tom04様
こんにちは、度々お忙しいところご丁寧にありがとうございました。
手とり足とりのご解説で、頭の固い私にもようやく理解できました。
私、OFFSET関数根本的に理解していなかったようですね。
OFFSET関数:=OFFSET(基準,行数,列数[,高さ][,幅])
この基準と言うものは、必ず1つのセルでありそこを基準に
行数、列数移動と思い込んでいました。
質問の状態
E2=パソコンなら
=COUNTA(B:B)= OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)
B列全体と言う事なのですね。
OFFSETはここを基準にいくつかずれて、ずれた所からこれだけの範囲と
しか頭に無かったです。この式は列全体を基準にする式だから高さや幅が不要なのですね。
ようやく理解できました。本当にありがとございます。
もうおひとりお付き合いを頂いているnishi6様の
基準に名前範囲ということも、B:B列全体と置き換えて
名前範囲全体となるのですね。
しかし皆さん凄いですね~知識力に柔軟性
エクセルを使いこんでいる方々からすればこんな事は初歩中の初歩ってところなのでしょうか
まだまだ、マスターでききれていませんので九九の表でも利用してOFFSET関数
完全に理解を深めたいと思います。
お世話になりありがとうございました。
No.2
- 回答日時:
質問内容はこういうことでしょうか。
【参考】
EXCEL 入力規則・リスト応用編 いろいろ
http://note.chiebukuro.yahoo.co.jp/detail/n149222
ポイント:大分類(例では「種類」)リストを、「入力データ数に応じて範囲を変更する」方法で名前を付けた場合の2層目リスト表示方法
・「元の値」に、「=INDIRECT(A2)」と入力しても、リストは表示されません
・名前ボックスに表示されない名前は、「=INDIRECT(A2)」では表示されません
とあります。本当にINDIRECTを使うと名前ボックスに表示されませんね。
対応を考えてみました。(なぜダメかは考えないようにしました)
●範囲名の設定
分類: =OFFSET(Sheet1!$A$4,,,COUNTA(Sheet1!$A$4:$A$11))
パソコン: =Sheet1!$B$3:$B$100
プリンタ: =Sheet1!$C$3:$C$100
「分類」は質問通りの設定で、「パソコン」と「プリンタ」の範囲名は行数を多目にしています。【参考】にあるように「2層目リスト」にINDIRECTを使うとダメということなのでこうしました。
●入力規則の元の値
E2の入力規則の元の値
=分類
F2の入力規則の元の値
=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)
「パソコン」と「プリンタ」の範囲名は行数を多目に設定していますが、「入力規則の元の値」の設定でデータ数にしています。質問にあった範囲名の設定に近い算式です。これを「元の値」の設定で使っています。添付図のように不要なスペースはなくなるはずです。
参考URL:http://note.chiebukuro.yahoo.co.jp/detail/n149222
この回答への補足
nishi6様
こんにちは、先日はありがとうございました。
ご回答お礼で
=OFFSET(INDIRECT(E2),
ここのOFFSETの基準が名前範囲?
というのが理解できていません。
これはどういった意味になるのでしょう?
こちらの分ですが、もうおひとりのご回答を頂いていた
tom04様のご解説により基準を範囲指定するって事が理解できました。
F2の入力規則の元の値
=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)
すなわち、名前範囲のパソコン(Sheet1!$B$3:$B$100)から1行ずれて
B4からパソコン範囲分のCOUNTAで4そこから-1で3行分で
結果B4:B6の範囲が指定される訳ですね。
私、OFFSET関数根本的に理解していなかったようです
お世話になりありがとうございました。
nishi6様
こんにちは、早々にご回答頂きありがとうございました。
ご返答が遅くなりました。申し訳ございませんでした。
お示し頂いたサイト等で悪戦苦闘しておりました。
範囲を広範囲にしておきながら、COUNTAで整えるなど
知識もさることながら、柔軟な頭おもちですね~
素晴らしいです。ありがとうございました。
ただ、こちらでももし良かったらお伺いしてよろしいでしょうか?
まず、参考でお示し頂いたサイトの
I.リストから入力したデータに応じた、リストを表示させる
ここの分ですが
肉の名前の範囲を
可変に
=Sheet1!$B$2:OFFSET(Sheet1!$B$2,,,COUNTA(Sheet1!$B:$B)-1)
変更し説明どおり
2層目リスト表示方法
=OFFSET(INDIRECT(A2),,,COUNTA(INDIRECT(A2)),)
の入力規則設定でもやっぱりプルダウンが選択できなくなるのですが
説明通りになりますか?
あと、ご説明いただいた
F2の入力規則の元の値
=OFFSET(INDIRECT(E2),1,0,COUNTA(INDIRECT(E2))-1,1)
ここがよく解っていません。
COUNTA(INDIRECT(E2))-1
ここでのINDIRECT(E2)
はCOUNTAですから名前”プリンタ”範囲のデータ数-1
で宜しいのですよね。
=OFFSET(INDIRECT(E2),
ここのOFFSETの基準が名前範囲?
というのが理解できていません。
これはどういった意味になるのでしょう?
結果はおかげをもちましてOKですが、もしよろしかったらお暇な時にでも
またご教授下さい。
No.1
- 回答日時:
こんばんは!
E列のリストの「元の値」の欄はそのままで
F列のリストの「元の値」の欄の数式を
=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)))
としてみてはどうでしょうか?
※ お示しの数式は余計な空白セルをリスト表示させないためだと思います。
Excel2010で試したらOKでしたが、もしExcel2007でダメでしたら
名前定義の範囲をデータ数以上にして、少し空白を表示させる方法になるかと思います。m(_ _)m
Tom04様
こんにちは、早々にご回答頂きありがとうございました。
結果から申しますと、お示し頂きました分で2007でもできました。
すばらしいです。ありがとうございました。
ただ、知識不足でなぜ?どういうこと?となかなか数式が理解できず
ご返答が遅くなりました。申し訳ございませんでした。
もし、お時間ありましたらで結構なのですが、
お示し頂きました数式
=OFFSET(INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))),1,,COUNTA(OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)))
INDIRECT(ADDRESS(3,MATCH(E2,$3:$3,0))ここの部分は
3で3行目
E2の値をMATCH関数で3行目全体から抽出で2
よってB2基準で1つ下がってまでは理解できます。(合っていますよね?)
その後の
COUNTA (OFFSET(A:A,,MATCH(E2,$3:$3,0)-1))
ここがよく解っていません。
OFFSETの基準がA:Aというのはどういう事なのでしょうか?
A列全体??セルに=A:A は0になりますが・・・???
結果はおかげをもちましてOKですが、もしよろしかったらお暇な時にでも
またご教授下さい。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel ドロップダウンリスト(入力規則)に関してです データの入力規則で元データ79000行のド 3 2023/07/17 10:06
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Excel(エクセル) Excelのプルダウンメニューの内容を人によって可変する方法 2 2023/03/28 14:52
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) 条件付き書式 ある範囲で色がついているセルと同行の別のセルに色を付けたい 4 2022/04/20 07:04
- Excel(エクセル) 別シートの表の値を参照したい 2 2022/03/30 15:11
- Excel(エクセル) 単価シートから単価をエクセル関数で自動取得する方法 1 2023/07/02 22:00
- Visual Basic(VBA) Excelのマクロコードについて教えてください。 1 2022/03/27 13:25
- Excel(エクセル) 条件付き書式の色付きセルのカウント方法について 2 2022/10/21 14:51
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excel 2019 のピボットテーブル...
-
Excelのセルを飛ばして入力する
-
【マクロ】エクセルにかいてあ...
-
Excelのオートフィル
-
Excel初心者です。 詳しい方、...
-
スプレッドシート クエリ関数 1...
-
MOS365 Excel Expert / Excel R...
-
西暦や和暦の表示をyyyymmdd表...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセルの数式で教えてください。
-
スプレッドシートの関数VLOOKUP...
-
エクセルでセルに「氏名を入力...
-
エクセルで指定した日付、店舗...
-
【Excel】セル内の時間帯が特定...
-
Excelのグラフ軸について
-
Excel 2019 は、SPILL機能があ...
-
関数を教えて下さい。
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報