- ベストアンサー
Excel2007での名前の定義セル参照拡張範囲に関する問題
tom04の回答
No.1です。 補足の件について・・・ 前半部分は仰る通りで間違いありません。 後半部分の ＞その後の ＞COUNTA (OFFSET(A:A,,MATCH(E2,$3:$3,0)-1)) ＞ここがよく解っていません。 ＞OFFSETの基準が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
関連する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)」に「名前１」とつけたとし、Sheet1のA1セルに「名前１」と記入し、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) なぜかエラーになってしまいます。 R２にデータが入っておりこれはカウントさせたくない為、上では戻り値-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には"数字"、"英語"と名前定義された２つのリストがあります。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で指定したコードの商品郡を入力するようにしたいのです。 コードと商品郡は別シートに作成しています。 別シート） Ａ Ｂ Ｃ Ｄ Ｅ 1 2 A B C D E 3 ・ ・ ・ ・ ・ 4 ・ ・ ・ ・ ・ ２行が全てコード、３行以降がＡ～Ｅの商品郡 になります。 ３行目以降のセルには関数を入れて 別シート_２ より集計したものになります。 商品郡の数はコードによってバラバラです。 ２行目の記入セルを「名前の定義」で「コード」としてあります。 各列を 「名前の定義」、「Ａ」 ｛範囲：A3：A150｝ 「Ｂ」 ｛範囲：B3：B150｝のようにしました。※今後増減するため、セルは多く設定しました 入力シートの A1にリスト範囲"=コード" B1に"=INDIRECT（A1）" リストはでたのですが、空白セルが多いため使いづらく 空白セルを表示させないために 別シート!A1 に ="別シート!A3:A"&COUNTA(A3:A150） と入力し自分なりに可変するような式を作ったのですが、 入力シート B1に リスト範囲"=Ａ"とすると 望むようなリストが出来たのですが、 リスト範囲"=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 ------------------------------------ １|りんご | ２|みかん | ３|バナナ | ４|パイナップル| ５|もも | 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でＡ１から２列目の最終行までの範囲に名前を付けたいのですが 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の部分が違うのと思うのですが、どうすればいいでしょう？？？
- ベストアンサー
- オフィス系ソフト