Excel名前定義で値が可変する問題

このQ&Aのポイント
  • Excelの名前定義で定義している値が自動的に可変してしまう現象が発生しています。
  • シートをクリックすると、定義している関数の値が変化してしまいます。
  • 名前の定義に関して、詳しい説明を求めています。どのセルをクリックしても、名前の定義が正しく範囲を保持するようにしたいと考えています。
回答を見る
  • ベストアンサー

名前定義で定義している値が、自動的に可変してしまう

Excel の質問です。 名前の定義について、教えて下さい。 僕が仕様を理解していないのか、関数の記述が間違っているのでしょうか ■現象 シートクリック時に、定義している関数の値が可変してしまう ■データ ワークシート[Sheet1]上データ  |A     |B ------------------------------------ 1|りんご   | 2|みかん   | 3|バナナ   | 4|パイナップル| 5|もも    | A1 ~ A5 は、データを直接入力 ■名前の定義 (1).数式リボン> 名前の管理> 新規作成 (2).以下 名前: 最終行番号 範囲: Sheet1 参照範囲: =MAX(INDEX((Sheet1!A:A<>"")*ROW(Sheet1!A:A),0)) (3).D10 に、「=最終行番号」と入力 ■結果 ・入力直後、D10 は、5 となるが、どこかのセルをクリック後、名前の定義を見ると「A:A」になっていない。  ・異なる列範囲になっている  ・規則性不明 ■期待結果 ・上記の場合、どのセルをクリックしても、名前の定義で定義した範囲が「A:A」のままであって欲しい。 ■現象確認 Excel2010 ■その他 ・参照範囲で設定している記述が、参照ではないからでしょうか?(自己定義関数を、名前の定義で呼び出している感じで、間違った使い方をしているから?)

質問者が選んだベストアンサー

  • ベストアンサー
  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.1

名前定義の数式を =MAX(INDEX((Sheet1!$A:$A<>"")*ROW(Sheet1!$A:$A),0)) のように「絶対参照」で作成する事で、どこで操作してもA列を取得します。 #もっとも その計算式ではあまりに重たすぎるので、A列が言葉だけ記入しているなら =MATCH("",$A:$A,-1) ぐらいで計算したほうがお勧めです。

moheji-henoheno
質問者

お礼

keithin さん、早速の回答、ありがとうございます。 なるほど、絶対参照にすれば良かったのですね。 ※ 本記述に至ったのは、B 列相当の値が数値で、大小様々な為です。 (B 列に値を定義して、質問すれば良かったですね。質問の意図をわかりやすくしようと、シンプルにしてしまいました。) ネットサーフィンの結果、これが良さそうな感じで使用していました。(INDEX 内の意図がわかっていないのに、使っているのは良くないですが・・・) ですが、何かの機会、文字列だけの場合には、keithin さんに教えて頂いたものを使用させて頂きます。 複雑に考えすぎ、本式を採用するこになっていると思うので、もっと簡単な記述で対応出来る事まで、教えて頂きありがとうございました。

関連するQ&A

  • エクセルで名前の定義を自動化したい

    いつもお世話になっております。 添付のようにエクセルシートのA列に社名を入力したら、社名を名前にして行方向に「名前の定義」の範囲をD列からR列まで設定するようにしたいのですが。 これまでは私が都度やっていたのですがここでいろいろ教えてもらっているうちにだんだん欲が出てきた次第です。 目的は別シートの入力セルへの入力作業をこのシートに入力することでリストからの選択にしたいのです。 NETで調べてみたら数行のマクロで出来そうなのですがその数行が悲しいかな出来ないのでHELPです。

  • Excel Pivot 名前の定義の値をマクロで取得

    こんにちは。いつも参考にさせていただいています。 マクロでPivotを自動更新する記述をしています。 データが入力されているシートに 名前の定義をして"データ"と名前をつけ、参照範囲を以下のとおり設定しました。 =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1)) Pivotが表示されるシートのマクロに下記のように記述して、 自動的に更新ができることを確認できました。 Private Sub Worksheet_Activate() Range("A1").Select ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "データ" End Sub マクロ自体は問題なく動くのですが、 目でも確認をとりたいため、MsgBoxを追加して、現在選択されているデータ範囲を表示させたいと考えています。 例) 「データ範囲は$A$1:$D$150に変更されました」 そこで、 MsgBox "データ範囲は" & データ & "に変更されました" という一文を上記に追加してみましたが、データの部分でセルの範囲を取得できませんでした。 "データ"としてもダメで、名前の定義で指定された範囲の値を取得する方法がわかりません。 マクロで可能な仕様なのかもわからないのですが、 どなたか解決法をご存知でしたら教えていただけないでしょうか。 よろしくお願いします。

  • INDIRECTと名前の定義でリストを作成

    どなたか分かる方教えください。 A1のセルにコードを入力、B1のセルに商品 を入力したいと考えています。 但し、B1の商品はA1で指定したコードの商品郡を入力するようにしたいのです。 コードと商品郡は別シートに作成しています。 別シート)    A  B  C  D  E  1  2 A  B  C  D  E   3 ・  ・  ・   ・   ・  4 ・  ・  ・   ・   ・ 2行が全てコード、3行以降がA~Eの商品郡 になります。 3行目以降のセルには関数を入れて 別シート_2 より集計したものになります。 商品郡の数はコードによってバラバラです。 2行目の記入セルを「名前の定義」で「コード」としてあります。 各列を 「名前の定義」、「A」 {範囲:A3:A150}                「B」 {範囲:B3:B150}のようにしました。※今後増減するため、セルは多く設定しました 入力シートの A1にリスト範囲"=コード" B1に"=INDIRECT(A1)"  リストはでたのですが、空白セルが多いため使いづらく 空白セルを表示させないために 別シート!A1 に ="別シート!A3:A"&COUNTA(A3:A150) と入力し自分なりに可変するような式を作ったのですが、 入力シート B1に リスト範囲"=A"とすると 望むようなリストが出来たのですが、            リスト範囲"=INDIRECT(入力シート!A1)" とすると、 元の値はエラー・・・とエラー表示がでて、リストが表示されません。 どのようにしたらリストが表示されるようになりますでしょうか。 もしくは、増減するセル範囲を空白なしに表示させるにはどのようにしたらいいでしょうか。 質問の説明が不十分すぎて申し訳ないですが、分かる方教えていただけたら嬉しいです。

  • エクセル名前の定義で行挿入で追従させたい

    修正中 名前の範囲を可変にしたい 例えば、C4セル~E17セルに「あああ」という名前を定義しており、続いてC19セル~E32セルに「いいい」という名前を定義し、さらにC34セル~E47セルに「ううう」という名前を定義していたとして、この中のいずれかの行を増やすことがあるとします。 例えばC4セルからE17の間に1行増やしたとすると、通常名前の範囲が意図した所より1行ずれてしまいますが、自動で名前の参照範囲を変更して、C4セル~E18セルが「あああ」、C20~E33セルが「いいい」、C35~E48セルが「ううう」など自動で追従させるにはどうすればよろしいでしょうか? さらにもし、自動で追従させることができたとして、名前を定義したシートは、1週間毎にシートを増やしていくのですが、ブックレベルで名前を定義しておけばよいのでしょうか?ブックの中には、元データのようなシートも存在しており、そちらには名前の定義は必要ないのですが、ブックレベルにした場合悪影響がでないかな?というのがあります。かといって、シートレベルで名前を定義しておくと、シートを増やす度に名前の定義が増えていくので、それもどうかな?と思っています。 状況によると思いますが、名前がどんどんシート毎に増えていくのは好ましくない気がするんで アクティブシートでしか名前の定義を使用しないなら、ブックレベルで名前を定義しておけばよいですよね? お手数をおかけいたしますが、よろしくお願いいたします。

  • エクセルの名前の定義について。

    ご覧いただきありがとうございます。 エクセルの名前の定義についての質問です。 (1)【sheet1】 A2:A6に『aa』、 B2:B10に『bb』、 C2:C6に『cc』という名前を付けたとします。 ちなみにそれぞれのセルには文字列が入力されています。 (名前の定義の《範囲》はブックではなく、sheet1) (2)【sheet2】 B2:D6に『aa』、 B7:D15に『bb』、 B16:D20に『cc』と、sheet1と同じ名前を付けました。 それぞれB列・C列・D列には数字が入力されています。 (名前の定義の《範囲》はブックではなく、sheet2) ちなみにA列には、sheet1に入力されている文字列と同じものが入力されています。 A2:A6にはsheet1で『aa』と名前をつけた範囲と同じ文字列。 A7:A15にはsheet1の『bb』と同じ文字列。 A16:A20には『cc』と同じ文字列です。 ここからが本題です! (3)【sheet3】 A2に何らかしら関数を入れて、【sheet1】で作った名前(aaやbbやcc)が1つ表示されるようにしたとします。 そしてB2には、A2で表示された名前の中の文字列が1つ表示されるようにします。 (例えばA2に『aa』と表示されたら、B2には◯◯) 更にC2には、B2に表示された◯◯という文字列を使って、【sheet2】の名前『aa』の中のB列と、A列にある◯◯とが交差するセルに入力されている数字(図でいう(5))を表示させる関数を入力したいのです。(INDEXやINDIRECT、MATCH関数など…) 他に例えば、A2に『bb』(sheet1の名前)、B2に★★と表示されたら、C2には【sheet2】の名前『bb』の中のB列と、A列にある★★が交差するセルに入力されている数字を表示させたいです。 同じように、今度は、D2には、B2に表示された◯◯という文字列を使って、【sheet2】の名前『aa』のC列と、A列にある◯◯の交差するセルに入力されている数字を表示させたいです。 長々とややこしくてすみません… 説明分かりますでしょうか?? つまりは【sheet3】のC2・D2・E2に入力する関数が知りたいです。 自分なりに関数を入力してみたのですが、A2で表示された名前がsheet1での名前なので、sheet2の情報を引き出す事が出来ません。 わざわざsheet1とsheet2でシートを分けたのは、同じシート内で同じ名前が付けられないと思ったからです! どなたか分かりましたら教えて下さい! どうぞよろしくお願い致します。 ※ちなみに、貼付した図の『・』は以下省略の意味で使ってます。

  • 名前の定義を自動でできる方法ってないでしょうか?

    名前の定義を自動でできる方法ってないでしょうか? セルA1に 定義する名前 セルA2に 範囲(はじまり) セルA3に 範囲(終わり) ドッロップリストにて定義した名前を表示する。 例えば、A1 野菜:B1 肉 A2 キャベツ:B2 豚 A3 レタス :B3 牛 D1にドロップダウンリストで範囲をA1:B1で選択 E2でドロップダウンリストで範囲をINDIRECT(D1)で選択 名前の定義を 範囲(A1:A3)名前を(上端行) 範囲(B1:B3)名前を(上端行) としていた場合。 B1を魚、B2を鯛、B3を鯵に変えた場合、自動的に名前の定義(名前)が「肉から魚」に変わり 鯛や鯵をドロップダウンリストに表示したいのですが? 出来るのでしょうか?

  • セル範囲に名前を定義する

    セル範囲に名前を定義して、計算式の中で使いたいのですが、 例えば、A列全体にAという名前、B列全体にBという名前を付けて、 =Counta(A)と記述すれば、A列にあるデータの個数が出てきます。 =Counta(B)と記述すれば、B列にあるデータの個数が出てきます。 一つの計算式にCounta関数が10個以上あって、AとBを書き換えるのが面倒なのですが、 特定のセルC1にAと入力したらCounta(A)、Bと入力したらCounta(B)になるようには出来ないでしょうか? =Count(C1)と入力してもダメでした。

  • INDIRECTと名前の定義セル参照拡張範囲

    只今エクセル勉強中です。よろしくお願いします。 画像の貼り付けがよくわからなかったので シート画面のコピーを添付データにしています。 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使用です。 よろしくお願いします。

  • エクセル関数で名前定義を引用

    エクセル2002使用です。 IF関数で、名前の定義を利用したいのですがうまくいきません。 A1に数値「1」を入力するかしないかとします。 (A1:C1)を名前の定義で「範囲1」とします 各セルに文字が入力されています。 (D1:F1)を名前の定義で「範囲2」とします。 各セルはブランクのままです。 A1セルに数値「1」が入力されて時に、「範囲2」に「範囲1」がコピーされるようにしたいのですがどうすればいいのでしょうか? 範囲2=IF(A1=1,範囲1) ではだめですよね。。。。 よろしくお願いします。

  • エクセル 名前の定義・管理について

    エクセル2007の名前の定義・管理について教えてください。 あるシート(Aシート)のセルの入力規則に他シート(Bシート)の範囲(B2:B4に赤・青・白を入力されている)を定義した名前(名前を色とします)を使用しています。 別途違う目的のためBシートをコピーし、若干修正してCシートを作り、また、その後、Cシートをコピーし、修正してDシートを作りました。ただし、Cシート、Dシートの名前を定義した範囲(赤・青・白)は全く修正していません。この時点で名前の管理から定義された名前を見ると「色」という同じ名前が3つ作成されております。(参照範囲は、それぞれB~Dシートのものと異なっています。) で今回、Dシートが一番最新のもので利用しているので、Dシートの名前を定義した範囲(B2:B4の赤・青・白)を(B2:B5の赤・青・白・緑)と変更したところ、Aシートの入力規則の名前は色ですが、(赤・青・白)しかリストにでてきません。(緑が出てこない)。色という名前が3つあるのがいけないのだと思い、Dシートを参照している以外の「色」の名前を削除したら、Aシートの入力規則は色と入力されているにもかかわらず、全く機能しなくなってしまいました。 いろいろ調べてみたのですが、対処法がわからないので教えてください。なぜ、Dシートを参照している名前の「色」は、入力規則に入力されているにもかかわらず、リンクしてくれないのでしょうか? そもそもシートをコピーしても色という名前が3つできても、最初にリンクしているBシートの名前の「色」しか参照してくれないのでしょうか?なにか方法はありますか? 上手く文章で伝えることができずわかりづらいかとは思いますが、 よろしくお願いします。

専門家に質問してみよう