• ベストアンサー

EXCEL 結合されたセルを関数で参照する方法について

お世話になります。 EXCELにてDBとして入力されたものを必要な部分のみ抜き出したファイルを作成する為、 関数で元シートへのセル参照させるシートを作成したのですが 結合された部分の参照ができなく困っております。 <例>~~~~~~~~~~~~~~~~~~~~~~~~~ SheetA [A1~A10][A11~A20]が結合セル SheetB [A1]に「='SheetA'!A1」 [A2]に「='SheetA'!A2」 [A3]に・・・[A20]まで同様 とさせるとA1とA11のみSheet1の値が反映されてA2~A10、A12~A20が出力されない。 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ A2~A10をA1とさせるとすればいいことですが 実際には数百件のデータ入力があり、結合部分も複数存在しています。 今後DBが増えていくことを考え、関数式セル右下ひっぱりの連続データ作成で対応させたいのですが、 SheetBでの A2 が "Null" であったら上方向へ検索し文字入力があった部分を引用させるといった 関数をくむことが出来ませんでした。 なにかいい知恵をご教示頂けましたら幸いと思います。 ことばではわかり難いかもしれませんのでJPG添付もさせていただきます。

  • neky
  • お礼率40% (2/5)

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

  • ベストアンサー
  • okormazd
  • ベストアンサー率50% (1224/2411)
回答No.3

添付図の場合のように、 E10:=IF(B2<>"",B2,OFFSET(E10,-1,0)) F10:=C2 E10:F10を選択して下にフィルドラッグではダメですか。

neky
質問者

お礼

出来ました!まさにこれです! OFFSETとという関数自体使ったことが無いので一生懸命検索で何とかしようとしていました。 下に伸ばした時、結合セル数関係無しに反映された時に思わず鳥肌が立ちましたw 心より御礼を申し上げます!

その他の回答 (2)

  • bin-chan
  • ベストアンサー率33% (1403/4213)
回答No.2

#1です。大失敗。 modでは無く、intでした。 なので以下の通りに訂正。 1は、Row関数()で現在の行番号がわかります。 1行目なら参照するのはセルA1、2行目ならセル11です。 10行ワンセットなのでint関数で整数の商を求めますが途中で±1します。 式[=int((Row()-1)/10)+1]を作成してください。 1 → 1 2 → 11 3 → 21 が求められることを確認してください。 2は、セル参照を文字列で指定できるIndirect関数を使用します。 式[=indirect("SheetA!A"& int((Row()-1)/10)+1)]です。 これでSheetBのセルはSheetAのセルを10行おきに参照できます。 データが増えたら、SheetBのセルをドラッグでコピーして対応してください。

  • bin-chan
  • ベストアンサー率33% (1403/4213)
回答No.1

解決のために以下の2点を考えてみてください。 1.例示の10行でワンセットの場合、セットの先頭行を計算で求める。 2.数値で計算された位置をセル参照させる。 ※見出し行が無いものとして説明。実際には見出し行の分オフセットしてください。 1は、Row関数()で現在の行番号がわかります。 1行目なら参照するのはセルA1、2行目ならセル11です。 10行ワンセットなのでmod関数で剰余を求めますが途中で±1します。 式[=mod((Row()-1),10)+1]を作成してください。 1 → 1 2 → 11 3 → 21 が求められることを確認してください。 2は、セル参照を文字列で指定できるIndirect関数を使用します。 式[=indirect("SheetA!A"& mod((Row()-1),10)+1)]です。 これでSheetBのセルはSheetAのセルを10行おきに参照できます。 データが増えたら、SheetBのセルをドラッグでコピーして対応してください。

neky
質問者

補足

> bin-chan 様 ご回答ありがとうございます。 結合セルが固定数ならばうまくいく話だったのですが、そうではない為 いただきました回答では駄目でした。 ボトルネックなのが「DB増徴にともない出力も簡単に増やせること」でありまして 「連続データ作成」での対応が可能であることが必須でありますorz

関連するQ&A

  • 【エクセル】結合したセルからのリンクについて

    教えてください! エクセルのワークシート間のリンクについてです。 ワークシート(SheetA)内に結合したセルを作ってその中で計算式を作っているのですが、その計算された値を他の作業ワークシート(ここでは仮にSheetBとします)にリンクさせようとすると、SheetBのセル内に ='SheetA'!C1301:I1304 というように表示され、確定すると#VALUEとなってしまいます。また、リンク先のセルを選択するだけで固定された状態('SheetA'!$C$1301:$I$1304)になってしまいます。 毎回、:以降を消去して値をリンクさせているのですが、そのようなわずらわしい作業を行わずにリンクをさせたいのですがどのようにすればいいのでしょうか?設定で変更ができるようであればその方法を教えて下さい。 よろしくお願いします!

  • EXCEL セル結合したセルを参照したい!

    Sheet1 A1:B3 セル結合して「5」が入力されている Sheet2 A1:B3 セル結合して「5」が入力されている Sheet3 A1セルに上記の合計を出したい =Sheet1!A1:B3+Sheet2!A1:B3 となってしまって答えが#VALUEとなってしまいます。 結合されているセルはセル参照の計算は不可能なのでしょうか? 説明が下手ですいません。 おわかりの方教えてくださーい!

  • 結合したセルに参照式を・・・

    伝わりにくいかもしれませんが、ご容赦下さい。 sheet1にA1:A4を結合したセルを作って、そこにsheet2のA1を参照するように =sheet2!A1と式を入れます。 A5:A8の結合セルは、sheet2のA2、A9:A12の結合セルは、sheet2のA3を参照・・・ としたいので、A1:A4の結合セルを選択して、下方向にドラッグさせたのですが、A5:A8の セルの参照式が=sheet2!A5になってしまいます。 どうやらA2:A4もカウントしてしまっているように思います。 一つ一つ手入力で入れて行けば問題なくできますが、ここにこれから何百データと入れて行くので、 手入力は非常に面倒で避けたいです。 ドラッグでA5:A8はsheet2のA2、A9:A12はsheet2のA3・・・とさせる方法はないでしょうか? (要は、結合した中の最段以外の行はカウントしないように)

  • sheet1セルA1のデータ切り替えについて

    Excel2010に3つのシートsheetA、sheetB、sheetC があります。 それぞれセルA1のデータは,sheetAのA1=ブランク、sheetBのA1=b、sheetCのA1=c とします。 この状態で、 sheetBがActiveの時に、sheetAのA1=b sheetCがActiveの時に、sheetAのA1=c になるような関数、もしくはマクロを組み込みたいと思います。 どのようにしたら実現できますでしょうか? よろしくお願いします。

  • 結合したセルの左上以外のセルから値を参照したい

    エクセルで、セルA1:C3を結合しています。 =A1 として参照する場合には、結合セルの値を参照できますが、 =B1とか=A2とかのように参照すると、0が表示されてしまいます。 B1やA2のセルを指定した場合にも、結合セルの値を参照できるような シート関数はないのでしょうか。 =KANSU(B1)とか=KANSU(A2)とした場合に、 結合セルの値を得られるシート関数を教えてください!

  • Excelを使用して結合セルと結合していないセルの参照コピーがしたいです

    Excel2007を使用しています。 Sheet1にはA1:A3、A4:A6といった形で結合された表を作り、Sheet2のデータを参照させたいのですが、そのままリンク貼り付けなどのセル参照をすると、 Sheet1!A1(←A1:A3結合済)には、=Sheet2!A1 Sheet1!A4(←A4:A6結合済)には、=Sheet2!A4 になってしまいます。 これを、 Sheet1!A1(←A1:A3結合済)には、=Sheet2!A1 Sheet1!A4(←A4:A6結合済)には、=Sheet2!A2 にすることは、 できるのでしょうか? たくさんのデータがあるので、困ってます。 お力を貸してください!!

  • エクセルにて結合セルの範囲を参照するには?

       A B C D E F 1 |フォード| 2 |BMW | 3 |ホンダ | 各行A列~C列3つを結合入力 4 |ルノー | 5 |DFR  | 6 |ベンツ |  上記のようなエンジンメーカー表にて、A1~C1,A6~C6間にF1へエンジンを供給しているメーカー名がA~Cの3つのセルを結合して入力されているとします。  このような表を用いて別のワークシートから、このエンジンメーカーの入力セルの文字列を参照したいのですが、  フォードの場合ですと、=’エンジンメーカー表’!A1:C1と表示され、エラー値VAULEが返され、参照したい文字列が返りません。  結合されている3セルに範囲名をつけてINDIRECT関数で参照すれば出来るのですが、結合セルの項目が多過ぎて、いちいち、名前をつけていられないのです。  また、表のセル結合を崩せないのです。(同じフォーマットで作られている表が多数存在のため) このような単一セルでなく、複数のセル範囲を結合して入力されている文字列を簡単に=で参照する方法は無いでしょうか?

  • エクセルでのセルの参照

    sheet1にデータが入力されています。 そしてsheet2ではそのsheet1に入っているデータを 抽出するsheetとして使用しています。 そこで、sheet2にある一つのセルに3と入力したら sheet1で使用しているA3、B3、C3、D3・・・ のセルのデータを、 4と入力したら A4、B4、C4、D4・・・ のセルのデータをsheet2のそれぞれのセルに引っぱってこれるように 関数を書きたいと考えています。 INDIRECTが機能的に近いのかなと考え、式を試してみたのですが なかなかうまくいきません。 また、4と入力したときにA5、B5、C5、D5を参照するというように 数値をいじったりできるであればそれも教えていただきたいです。 よろしくお願いいたします。

  • Excel 別シートのセルを参照させる時の疑問

    エクセルであるシート(A)のセルに、別シート(B)の特定のセルを参照させたい場合、(A)のセルに= を入力し、続けて(B)の参照させたいセルを選択すると、=の後に選んだシート名+「!」+セル名が表示されます。 同じ作業をいくつか続けて行っているのですが、一カ所だけ「=」のあとのシート名が 「'」マークでくくられます。 問題なく選択したセルの参照は行えるのですが、この「'」は何を表しているのでしょうか。 分かりにくいので例を書きますと、たとえば「sheetA」の「A1」を参照させた場合、 ケースその1 : =sheetA!A1 ケースその2 : ='sheetA'!A1 このような違いがあります。 お分かりになる方がいらっしゃいましたら、恐れ入りますがよろしくお願いいたします。

  • 結合したセルのセル参照

    http://oshiete1.goo.ne.jp/qa2144305.html http://oshiete1.goo.ne.jp/qa1315043.html 上記の二つの質問を見ましたが、疑問が取れないので質問します。 知識が乏しいので、言葉の使い方がおかしかったらすみません。 ----- 別シート(シート名は漢字を使った和名に変更済)の 結合したセルにセル参照したい時のエラー(?)です。 ツールバーの「=」ボタン、もしくはキーボードから「=」を入力し カーソルで参照先のセルをクリックすると  ='シート名'!A1:B1 と入力されてしまい、エラーになります。 また、それと同じ操作をしているはずなのに  ='シート名'!A1 となる場合もあります。前者ではもちろんセル参照できませんので 後者のようにしたいのですが、何か設定があるのでしょうか? 前者のようになってしまうのは仕方がないことですか? 毎回「:B1」の部分を消すのは大変手間がかかります。 それとも、そもそもカーソルでセル参照をするやり方が悪いのでしょうか?