• ベストアンサー
  • 困ってます

結合セルからの参照

excel2007で、別シートの結合セル(縦方向に3行結合)から値を参照したのですが、3列分含まれてしまい、エラーが出てしまいます。 数式の内容から余計な部分を削除すれば問題ないのですが、作業量が増えてしまいます。 一発でエラー無く参照できる方法があれば教えて下さい。 よろしくお願いいたします。

共感・応援の気持ちを伝えよう!

  • 回答数5
  • 閲覧数1234
  • ありがとう数5

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

  • ベストアンサー
  • 回答No.4

私も同じ問題で悩まされています。解決方法がないのでマウス操作で参照した後、式を修正して対応しています。 この問題についてマイクロソフトは「エクセルの問題として確認している」と言っていますのでバグもしくはプログラムが完璧ではないためだと思います。 つぎのページを参照してください。 https://support2.microsoft.com/default.aspx?scid=kb;ja;410985 以下抜粋 ーーーーーーーーーーーーーーーーーーーー この現象は以下のいずれかの条件を満たし、さらに参照先が結合セルの場合に発生します。 参照先のシート名、もしくはファイル名に空白が含まれている 参照先のシート名の先頭に数字が使用されている 参照先のシート名に、以下のいずれかの記号が含まれている ! + - = ; # $ @ % & ' " ~ ^ | ( ) { } < > , ーーーーーーーーーーーーーーーーーーーー

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございました。 シート名の修正で解決いたしました。 シート名も適当には付けられないものなのですね。

その他の回答 (4)

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

>別シートの結合セル(縦方向に3行結合)から値を参照したのですが、3列分含まれてしまい、エラーが出てしまいます。 参照元(結合されているセルを含んでいる)のシートの「シート名」に問題があります。 ダメなシート名: Sheet2(1) などのように、シート名の中に(や)を含んでいる 上手くいくシート名: Sheet5 とか Sheett2_1 とか 手順: 結合セルを含むシートのシート名を正しく修正する 数式を記入するシートから、改めて結合セルをクリックして数式を記入させてみる

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございました。 前出の方をベストといたしましたが、シート名の例がとても参考になりました。

  • 回答No.3

 後、例えばA1セルに項目名が入力されていて、その下にA2:A4で1組、A5:A7で1組、という具合に縦3個ずつ結合されたセルが続いていて、B5セル、B6セル、B7セルの3つのセルに設定した関数内で、A5セルに入力されている値を参照したいという場合、もし参照する値が数値の場合にはB5セルにおける参照先のセルを指定する箇所にA5と入力する代わりに SUM(A3:A5) と入力しておきますと、A3:A4には値が存在しておりませんので、A5セルに入力されている数値が返されます。  又、この関数をB6セルにコピーした場合、参照値として使用する該当箇所は SUM(A4:A6) となり、この場合もA4セルとA6セルには値が存在しておりませんので、A5セルに入力されている数値が返されます。  同様に、B7セルにコピーした場合には SUM(A5:A7) となりますので、A5セルに入力されている数値が返されます。  又、もし参照する値が文字列の場合にはB5セルにおける参照先のセルを指定する箇所にA5と入力する代わりに A3&A4&A5 としておけば良い事になります。  但し、このままではB3セルにコピーした場合に A1&A2&A3 となって、A2セルの値ではなく、「A1セルに入力されている項目名」と「A2セルに入力されている文字列」が連結された文字列を参照する事になってしまいますし、 B2セルにコピーした場合には行番号が0以下の行のセルを参照しなければならなくなるため#REF!エラーとなってしまいます。  その様なバグを避けるためにはもう少し工夫をしまして、B2セルに入力する関数内の参照値の所を次の様にします。 SUM(INDEX(A:A,MAX(ROW()-2,ROW(A$1)+1)):A2) IF(ROW()-2>ROW(A$1),INDEX(A:A,ROW()-2),"")&IF(ROW()-1>ROW(A$1),INDEX(A:A,ROW()-2),"")&A2 或いは SUM(OFFSET(A2,MAX(-2,ROW(A$1)+1-ROW()),):A2) IF(ROW()-2>ROW(A$1),OFFSET(A2,-2,),"")&IF(ROW()-1>ROW(A$1),OFFSET(A2,-1,),"")&A2  尚、上記の方法で基点となるセルをA2セルとせずに、A1セルとしているのは何故かと申しますと、A2セルはデータが入力されているセルであるため、そこに入力されているデータがもしも不要になった際に、セルに入力されているデータのみが消去されるだけなら良いのですが、場合によってはデータを消すためにセルごと削除されてしまう恐れがあり、その様な場合、A2セルを基点としていた場合には#REF!エラーとなってしまうからです。  又、セルの削除が行われた場合には、関数内の参照先と、関数が入力されているセルとの位置関係がずれてしまう恐れもありますので、なるべくなら上記の参照先の数式内において A2 となっている箇所を INDEX($A:$A,ROW()) としておいた方が良いかも知れません。  又、別のやり方としてMOD関数を利用する次の様な方法もあります。(数値/文字列共通) INDEX(A:A,ROW()-MOD(ROW()-ROW(A$1)-1,3)) 或いは OFFSET(A2,-MOD(ROW()-ROW(A$1)-1,3),)

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございました。 いろいろな方法があるのですね。 試してみます!

  • 回答No.2
  • bunjii
  • ベストアンサー率43% (3576/8212)

>別シートの結合セル(縦方向に3行結合)から値を参照したのですが、3列分含まれてしまい、エラーが出てしまいます。 状況が理解できません。 3行結合のセルを参照して「3列分含まれて」と言う表現はどのような状況でしょうか? 「3列分含まれて」を「3行分含まれて」に置き換えてもあり得ません。 貼付画像のようになるのはExcelの仕様なので別の方法を考えてください。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございました。

  • 回答No.1

 結合されたセルの場合、値が存在しているのは結合されたセル範囲の中で左上の隅にあたるセルだけなのですから、参照する数式を作成するする際に、キーボード入力でセルを指定する事によって、左上の隅のセルだけを参照する様な数式にすれば、「複数セルを参照する事によるエラー」となる事はありません。  又、どうせ数式を入力する際には、関数名や数値、文字列等を入力するためにキーボード入力をしなければならないのですから、セルやセル範囲を指定する際にも、マウスを使わずにキーボード入力だけとした方が、キーボードとマウスを一々持ち帰るよりも作業量が少なくなります。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございます。 そこまで楽は出来ないということですね。 キーボードとマウスを持ち変えるのがエクセルの難点なんですよね。

関連するQ&A

  • エクセル:別シートのセル参照でセルが結合されているときエラーになる

    エクセル:別シートのセル参照でセルが結合されているときエラーになる 別シートのセル参照でセルが結合されているとき結果がエラーで返ってきます。 エラー:='A-4'!V339:AD339 「:」以降を削除すれば正常値になります。 1000個くらい参照をするのですが、いちいち「:」以降を削除するのは手間なので ・一括で削除する方法 ・もっと簡易にできる別の方法 があれば教えてください。

  • Excel2007 参照先セルが結合済み

    よろしくお願いいたします。 現在は 参照先がR1C1~R3C1を結合したセルの場合、 参照元(表示セル)の数式は"=[参照先シート]!R1C1:R3C1"となります。 自分の記憶ですと、結合セルを参照した場合、 一番左上のセルのみを参照し 参照元(表示セル)の数式が"=[参照先シート]!R1C1"となる こともあったかと思います。 もしこれら2つを使い分ける方法があれば、 教えていただけたら幸いです。

  • Excel(2010)で結合セル内で一部縦書

    Windows7、Excel(2010)で3つのセルを結合して3行の文書で2行目だけを縦書にしたいのですが、フォントの前に「@」をつけて縦書にしても180度回転(天地逆)になるだけで、縦方向に向いてくれません。 実例としては以下の様な文字列です。 1999.12  ~ ←これを縦方向にしたい 2013/10 セルを結合しなければ良い話なのですが、結合したままでデータを作成する必要があるので、どなたかご存知でしたらご教示をお願いします。

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

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

  • 【EXCEL】 結合されたセルを参照した式

    1.EXCELで、数式で計算する時に元になるデータ(Sheet1のセル)に結合されたセルを指定しています。 2.Sheet2に上記の結合セルを参照した数式を作成しようと考えています。  a.セルに = (イコール)を入力し  b.Sheet2の目標セル(B1:C1)をクリック  c.Enterキーを押すと  入力された数式が  ='Sheet1'!B1:C1  となってしまい、エラー(#VALUE!)になってしまいます。  入力後に数式を手で修正して   ='Sheet1'!B1  とすれば正常に値が表示されますが、他のシートはこんな修正をしなくても   ='SheetXX'!B1  のように表示されます。 どこを修正(確認)したらいいのかわからない状態です。 どなたかご存知の方がおりましたらよろしくお願いいたします。

  • excel単一セルから結合セルに複写

    単一セルのものを結合セルに複写したいのですが、うまくいきません。 データーが約700行あるのです。 Aシートが単一セルでBシートが結合セル(縦に結合)になっていますが、 そのままコピーすると、当然結合が解除され、貼り付けられてしまいます。 「形式を選択して貼り付け・・」で、数式又は値等で貼り付けると、一行飛ばしで 貼り付けされてしまいます。 うまく貼り付ける方法がありましたら、教えていただきたいのですが・・・

  • EXCEL 2003 数式中の参照セルについて

    数式であるシート(シート1とします)中の値として、 別シート(シート2とします)のセルを参照するとします。 (例a) シート1中の値 =シート2!J36 ※ J36は例 ここまではよいのですが、 上記の行番号「36」の部分を 更に別の参照値から引っ張ってくることは可能でしょうか? やりたいことのイメージ例として以下に記載します。 (例b) =シート2!J(=A1) ※ここの「A1」はシート2中の参照するセル番号を指定するための シート1中に設けたセルとします。 ※上記の数式は実際にはエラーになり入りません。 最初の例aでは、シート2中の参照するセル番号を数式編集して 指定しないといけないため、参照箇所が多いと大変ですが、 もし、例bのような指定ができればシート2中の参照するセル番号の 入力は一箇所ですみ楽になりありがたいと考えています。 わかりにくい文章ですみません。よろしくお願いします。

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

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

  • セルの結合をして縦方向の位置について

    最近のエクセルでもセルの結合をしても縦方向は中央にならないですか?

  • 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添付もさせていただきます。