[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『INDIRECTを使って別シートからの参照の展開を上手に』（ばじご）
すみません初めてご質問申し上げます。お教えくださいませ。
A1にはシート名、B1にはカラムを示す英文字 を記入し、
=INDIRECT("'" & $A$1 & "'!"& $B$1&1) として参照、これ下方向に沢山（200程）
=INDIRECT("'" & $A$1 & "'!"& $B$1&2)
=INDIRECT("'" & $A$1 & "'!"& $B$1&3)
=INDIRECT("'" & $A$1 & "'!"& $B$1&4)
=INDIRECT("'" & $A$1 & "'!"& $B$1&5)
*
*
とすることで参照シート先と同じ縦並びを参照していますが、行削除等してしまうと参
照の規則性がくずれてしまうので、なんとかもっと単純に2行目以降は「1行上の参照先
セルの1行下を参照」という表現で書く方法を探しております。良い方法がありました
らどなたかご教示頂ければ幸いです。
Excel2003,XP SP3
数字を使わず行番号を参照するようにする。
=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B1))
(wisemac21)
お教えくださいませ。その場合、2行目以降はどう記述することになるのでしょうか？ ちなみにB1に"H"等のカラム名として使いたい英文字が入っているだけで、B2以降は空欄、 つまり上記2行目以降への縦方向の参照はH1,H2,H3と展開したいのです。なので、2行目以 降は「1行上の参照先セルの1行下を参照」としたいのですが、、、 (ばじご)
式を下へコピーする（オートフィル） (wisemac21)
=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B1))
=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B2))
=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B3))
が入力されるわけですが、残念ながらこれは意図する参照が行われません。(B2,B3はブランクのため。B1に列名の"H"の文字列があるのみ)
実現したいのは、
=INDIRECT("'" & $A$1 & "'!"& $B$1&H1)
=INDIRECT("'" & $A$1 & "'!"& $B$1&H2)
=INDIRECT("'" & $A$1 & "'!"& $B$1&H3)
と同一の結果を
=INDIRECT("'" & $A$1 & "'!"& $B$1&H1)
=上記別シート参照先セルの一行下のセル
=上記別シート参照先セルの一行下のセル
=上記別シート参照先セルの一行下のセル
というアプローチで単純表現で得ることなのですが、、、
ちなみにH2,H3,H4としたくない理由は行削除等された場合に参照が欠落してしまうため
で、上記アプローチであれば常に一行上との関係から参照してくるのでその危険を回避、かつ単純な記述になると考えています。
ご説明が未熟ですみません。よろしくお願いいたします。（ばじご）
おかしいですね。wisemac21さんの数式で出来るはずですが？ ROW(B1)は、B1セルの行番号を返す関数なので、 1,2,3 と変化します。従って、H1,H2,H3と変わっていくはずです。 B2、B3セルの内容には関係ありません。 (最初の数式と上の数式が違いますけど？)
それはともかく、、
wisemac21さんの数式では、例えば50行を削除すると
=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B50)) がエラーになるのでは？
ばじごさん、 =INDIRECT("'" & $A$1 & "'!"& $B$1&1) は、
どのセルに入ってますか？
（純丸）(o^-')b
純丸さん、ありがとうございます。
●シートA：
------------------------------------------------------
[A] [B]
[1] シートB H
[2]
[3]
[4]
-
-
[10] =INDIRECT("'" & $A$1 & "'!"& $B$1&1)
[11] =INDIRECT("'" & $A$1 & "'!"& $B$1&2)
[12] =INDIRECT("'" & $A$1 & "'!"& $B$1&3)
-----------------------------------------------------
●シートB： -----------------------------------------------------
[A] [B] -- [H]
[1] ホンダ
[2] トヨタ
[3] 日産
-----------------------------------------------------
という配置になっております。例えばここでシートAの11行目が操作ミス等で行削除されると
当然ながらシートBからの参照が一行分消えてなくなるのですが利用者のユーザーはこれに
気づかないまま集計ミスにつながる事があるもので、この対策が発端です。
また、上記は実際のシートを単純化したものなのですが上記と全く同じシートを作成して
wisemac21さんにご教示頂いた「ROW(B1)のオートフィル」を試してご報告させて頂きたいと存じます。
(ばじご)
純丸さん、 wisemac21さん、ありがとうございます。 上記試しました結果、
●シートA：
------------------------------------------------------
[A] [B]
[1] シートB H
[2]
[3]
[4]
-
-
[10] =INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B1))
[11] =INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B2))
[12] =INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B3))
-----------------------------------------------------
が入力され、表示としてはwiseman21さんの仰る通り下記を得ることができました。
前回の返信は私の操作ミスによるものでした。申し訳ございません。
●シートA：
------------------------------------------------------
[A] [B]
[1] シートB H
[2]
[3]
[4]
-
-
[10] ホンダ
[11] トヨタ
[12] 日産
-----------------------------------------------------
元の課題に戻りまして、「シートAの11行目を利用者が行削除して"トヨタ"が集計から消える」事態を防ぐために [A][10]-[A][12]の式はすべて同一の式で「1行上の参照先セルの1行下を参照」という表現を探しております。 これができればシートAの途中の行が削除されても常に相対的に参照が保たれているのでシートBからもれなく参照できる と考えました。この点に関して何かアドバイス頂ければ幸いです。
例：[A][11]の式として「1行上(A10)の参照先セル(=INDIRECT("'" & $A$1 & "'!"& $B$1&ROW(B1)))の1行下(シートBのH2)を参照」
何度もお願いで恐縮ですが、よろしくお願い致します。
(ばじご)
A9セルが空白の場合、
A10 =INDIRECT("'" & $A$1 & "'!"& $B$1&COUNTA($A$9:$A9))
A9セルが空白ではない場合、
A10 =INDIRECT("'" & $A$1 & "'!"& $B$1&COUNTA($A$9:$A9)-1)
これでどうでしょう？ （純丸）(o^-')b
こんにちは。 私は行削除しても連番になるようにしたいときには、以下のようにROW()を使っています。
○【シートB】シート [R/C] [A] [H] [1] _ ホンダ [2] _ トヨタ [3] _ 日産
○【シートA】シート ※「_」セルは未入力または"" [R/C] [A] [B] [1] シートB H [2] _ _ [9] _ _ [10] ホンダ _ [11] トヨタ _ [12] 日産 _
A10 =INDIRECT($A$1 & "!"& $B$1&ROW()-9) →A10:A12フィルコピー
なお、シートAの1〜9行目を削除してしまった場合は、A10が#REF!になりますので、 さすがに気付くかと思います。^^ （コタ）
純丸さん、コタさん、ありがとうございます！Waoです！
頂きました両案を試させて頂きましたところ、得られる計算表示も期待通り、且つ、行削除にも耐えますね。 素晴らしい解決策を頂きました。
wisemac21さん、純丸さん、コタさん、本当にありがとうございました。素晴らしい学校に感謝です！ (ばじご)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.