- ベストアンサー
Excel2007での名前の定義セル参照拡張範囲に関する問題
tom04の回答
- tom04
- ベストアンサー率49% (2537/5117)
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
関連するQ&A
- セル範囲に名前を定義する
セル範囲に名前を定義して、計算式の中で使いたいのですが、 例えば、A列全体にAという名前、B列全体にBという名前を付けて、 =Counta(A)と記述すれば、A列にあるデータの個数が出てきます。 =Counta(B)と記述すれば、B列にあるデータの個数が出てきます。 一つの計算式にCounta関数が10個以上あって、AとBを書き換えるのが面倒なのですが、 特定のセルC1にAと入力したらCounta(A)、Bと入力したらCounta(B)になるようには出来ないでしょうか? =Count(C1)と入力してもダメでした。
- ベストアンサー
- Excel(エクセル)
- 名前の定義を使ったリストを動的に
Excel2003を使っています。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」という形で名前の定義を行います。 入力規則のリストにこの名前を指定することで、後でSheet2にデータを付け加えても修正する必要のない動的なリストを作ることができますよね。 それともう一つ、入力規則のリストに「=INDIRECT(A1)」と指定することで、A1セル内に書かれてある文字の名前を指定したことになり、リストとして扱えますよね。 この二つの組み合わせはできないのでしょうか? 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」に「名前1」とつけたとし、Sheet1のA1セルに「名前1」と記入し、B1セルの入力規則に「=INDIRECT(A1)」を指定してもエラーとなってしまいます。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」という形でいくつか動的なリストを作り、Sheet1のA1セル内を書き換えることでB1セルのリストが動的に変わるということをしたいのですが上手くいきません。 「=OFFSET(Sheet2!$B$2,,,COUNTA(Sheet1!$B:$B)-1)」ではなく、「=Sheet2!$B$2:$B10」のように固定してしまえば上手くいくのですが、これだとデータを付け加えるたびに修正しないといけません。 二つを組み合わせてリストを使う方法はありませんか?
- ベストアンサー
- その他MS Office製品
- エクセル名前の管理
エクセルの名前の管理について質問です。 Aのシートでデータの入力規則に従い、別シート(B)で作成したリストを指定してプルダウンメニューで選択できるようにしています。 Bのシートでは列単位でリストを作成しています。 リストを追加すると自動でプルダウンメニューに追加するには、COUNTA関数を使って、入力の個数を戻り値としてとらえればできるのはわかったのですが、 名前の管理の参照範囲を =OFFSET(B!$R$3,0,0,COUNTA(B!$R:$R)-1,1) とすると上手くいくのですが、 COUNTAの範囲を「R列」全てではなく、R3~R100までとした時 =OFFSET(リスト!$R$3,0,0,COUNTA(リスト!R$3:R$100),1) なぜかエラーになってしまいます。 R2にデータが入っておりこれはカウントさせたくない為、上では戻り値-1させています。 これをR3~R100までのカウントにして-1を省いてみました。 セルに =COUNTA(リスト!R$3:R$100) として、R列のデータ入力されている個数を表示させるときちんと出るのですが、上の構文だとおかしいのでしょうか? また、気になるのは、名前の管理を保存して閉じ、再度変更した箇所を開くと =OFFSET(リスト!$R$3,0,0,COUNTA(リスト!XF$3:XF$100),1) と指定範囲が勝手に変わっています。 名前の管理の参照範囲が勝手に変わることなどあるのでしょうか? バグなのか、関数の指定がおかしいのかがわかりません。 どこが悪いのか、またどのようにすればいいのかわかる方ご教授お願い致します。 使用しているのはMSオフィスのエクセル2007です。
- ベストアンサー
- その他MS Office製品
- Excel(エクセル) 名前定義をしたリストから、indirect関数を利用して選択入力できるようにしたいのですが…
次のようなことができず困っています。 Sheet1とSheet2があり、Sheet2には"数字"、"英語"と名前定義された2つのリストがあります。Sheet1のセルA1には"数字"か"英語"どちらかの文字列を選択入力し、B1には、もしA1に"数字"を入力していればリスト"数字"から、もし"英語"を入力していればリスト"英語"から選択入力できるようにしたいのです。つまり、Sheet1のセルA1、B1の入力規則の種類をリストとして、元の値をそれぞれ、 ・Sheet1!A1には=数字,英語 ・Sheet1!B1には=indirect($A1) としています。 質問(1)) ここまでなら何の問題もないようですが、問題はSheet2の"数字"と"英語"の参照範囲です。 ・"数字"の参照範囲は=Sheet2!$A$1:$A$10,Sheet2!$C$1:$C$10 ・"英語"の参照範囲は=Sheet2!$B$1:$B$10,Sheet2!$D$1:$E$10 と複数範囲におよんでいる場合、Sheet1!B1にはうまくリストが表示されません。解決法、およびなぜ表示されないのか、どなたかご教示お願いいたします。 質問(2)) Sheet2の"数字"と"英語"の参照範囲を ・=OFFSET(Sheet2!$A$1,,,COUNTA(Sheet2!$A:$A)) ・=OFFSET(Sheet2!$B$1,,,COUNTA(Sheet2!$B:$B)) とした場合も同様にSheet1!B1にはうまくリストが表示されません。こちらの方も解決法と表示されない理由をお願いいたします。
- ベストアンサー
- オフィス系ソフト
- 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)" とすると、 元の値はエラー・・・とエラー表示がでて、リストが表示されません。 どのようにしたらリストが表示されるようになりますでしょうか。 もしくは、増減するセル範囲を空白なしに表示させるにはどのようにしたらいいでしょうか。 質問の説明が不十分すぎて申し訳ないですが、分かる方教えていただけたら嬉しいです。
- ベストアンサー
- オフィス系ソフト
- 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 "データ範囲は" & データ & "に変更されました" という一文を上記に追加してみましたが、データの部分でセルの範囲を取得できませんでした。 "データ"としてもダメで、名前の定義で指定された範囲の値を取得する方法がわかりません。 マクロで可能な仕様なのかもわからないのですが、 どなたか解決法をご存知でしたら教えていただけないでしょうか。 よろしくお願いします。
- 締切済み
- 会計ソフト
- エクセルの印刷範囲設定について
表を印刷したいのです。 あらかじめ罫線を多めに引いています(たとえばA1:E100) データ入力を数行(たとえば30行まで)入力した時点で その部分のみ印刷したい場合印刷範囲の設定で A1:E30 と指定すればよいのですが、 次に40行目まで入力したとき範囲設定を再設定したくなく、 常に入力された部分まで印刷範囲を自動設定することは可能でしょうか。 =OFFSET(sheet1!A1,0,0,COUNTA(sheet1!A:A),5) と入力しても、次に開いたときには A1:E30 になってしまいます。
- 締切済み
- オフィス系ソフト
- 名前定義で定義している値が、自動的に可変してしまう
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 ■その他 ・参照範囲で設定している記述が、参照ではないからでしょうか?(自己定義関数を、名前の定義で呼び出している感じで、間違った使い方をしているから?)
- ベストアンサー
- Excel(エクセル)
- EXCEL 名前の定義 VBA参照の方法
EXCELの名前の定義を、VBAから参照する方法を教えていただきたいのですが 名前を定義する シートA 名前:TEST 範囲:シートA シートB 名前:TEST 範囲:シートB シートC 名前:Pass 範囲:ブック それぞれをVBAから参照する場合 Range("TEST").Value: シートA内のVBA Range("Pass").Value では、参照できず。 Worksheets("シートA").Range("TEST").Value Worksheets("シートC").Range("Pass").Value にて、参照できました。 明示的なシート名入力が必要なのでしょうか? よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- 範囲に名前を付けたいのですが vba
vbaでA1から2列目の最終行までの範囲に名前を付けたいのですが ActiveWorkbook.Names.Add Name:="範囲の名前", RefersToR1C1:="=Sheet1!R1C1:INDEX(Sheet1!C2,COUNTA(Sheet1!C1))" これで出来るのですが、 R1C1方式じゃない書き方にしたいのですが ActiveWorkbook.Names.Add Name:="範囲の名前", RefersToRange:="=Sheet1!$A$1:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$A:$A))" だと、「名前付き引数が見つかりません」になります。 RefersToRangeの部分が違うのと思うのですが、どうすればいいでしょう???
- ベストアンサー
- オフィス系ソフト
お礼
tom04様 こんにちは、度々お忙しいところご丁寧にありがとうございました。 手とり足とりのご解説で、頭の固い私にもようやく理解できました。 私、OFFSET関数根本的に理解していなかったようですね。 OFFSET関数:=OFFSET(基準,行数,列数[,高さ][,幅]) この基準と言うものは、必ず1つのセルでありそこを基準に 行数、列数移動と思い込んでいました。 質問の状態 E2=パソコンなら =COUNTA(B:B)= OFFSET(A:A,,MATCH(E2,$3:$3,0)-1) B列全体と言う事なのですね。 OFFSETはここを基準にいくつかずれて、ずれた所からこれだけの範囲と しか頭に無かったです。この式は列全体を基準にする式だから高さや幅が不要なのですね。 ようやく理解できました。本当にありがとございます。 もうおひとりお付き合いを頂いているnishi6様の 基準に名前範囲ということも、B:B列全体と置き換えて 名前範囲全体となるのですね。 しかし皆さん凄いですね~知識力に柔軟性 エクセルを使いこんでいる方々からすればこんな事は初歩中の初歩ってところなのでしょうか まだまだ、マスターでききれていませんので九九の表でも利用してOFFSET関数 完全に理解を深めたいと思います。 お世話になりありがとうございました。