EXCELで

   A  B  C   D   E 
1 1期  2期  3期
2 ¥500 ¥550 ¥560
3 ¥620 ¥300 ¥820

E1のセルには=A1と入力したり、=B2と手動で入力したりして参照場所を変更します。
E2のセルには=OFFSET(INDIRECT(E1)、1,0)と入力します。

この場合、E1セルにA1やB1など=を付けないで、文字列として入力した場合はこの式でA1の場合¥500が参照されるのですが、=を付けた場合ははエラーになってしまいます。

=を付けないとE1のセルにA1の場合A1と表示されますが、=を付けた場合1期と表示されます。後者にしたいので、=を付けて式が成り立つようにお知恵をお貸し頂ければ幸いです。よろしくお願い致します。
  

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2018/05/17 12:51:15
  • 終了:2018/05/21 10:21:39

ベストアンサー

id:kimuram No.2

kimuram回答回数19ベストアンサー獲得回数72018/05/18 15:41:12

そうでしたか、やはり、というか、バージョン書いてなかったのでもしやと思いながらでしたが、当方の環境はほぼ2016にしてきていたので。
でも古い使い物にならないくらいのノートPCでExcel2010が残っていたので、とろとろと試してみました。
やはり2010ではformulatext関数は使えないですね。
2013からのようです。

となると、手がないです。

で、関数が用意されていなければ、作るしかない、ですね。
VBAユーザー関数で試してみた。
何とか実現できるようなので、以下に紹介します。
試してみてください。

まず、Excelファイル形式はxlsmにします。(VBAマクロを含むので)
そしてVBE(VBエディター)で標準モジュールを準備して、以下をコピー貼り付けする。


Function myFormulaText(prmCell)
wkformula = prmCell.Formula
If Left(wkformula, 1) = "=" Then
myFormulaText = Right(wkformula, Len(wkformula) - 1)
Else
myFormulaText = wkformula
End If
End Function


そして、ワークシートの数式には以下の内容を設定する

=OFFSET(INDIRECT(myformulatext(E1)),1,0)


これでどうでしょうか?
数式の内容はご希望に近いでしょう。
ただ、VBAを使用することの影響は免れません。
ファイル形式のこと、他にもメンテナンスの面等あるかと思います。

そのうえで、期待通りの動きとなると思います。
=を付けた数式でも、単にセル名でも可能です。
(数式で絶対アドレス($付き)にしても動作しました)

他2件のコメントを見る
id:kimuram

≪補足≫xlsm形式ファイルで保存の方法に関して
(1)「ファイル」メニューから「名前を付けて保存」
(2)ファイル種類から「Excelマクロ有効ブック(*.xlsm)」を選択
(3)「保存」を実行
(※Excel2010では若干表現が異なるかも)

2018/05/19 18:27:15
id:cilgis

とても分かりやすい解説、そしてVBAの使い方まで教えて頂けて感動しました!VBAはなんとなく避けていましたが、これを機会にトライしてみようかと思います。エクセルは使い始めると面白いですね。分からない計算式になると一日使っても自分では解決できなかったりしますが、解決できるとスッキリします。本当にありがとうございました!!

2018/05/21 10:26:53

その他の回答(1件)

id:kimuram No.1

kimuram回答回数19ベストアンサー獲得回数72018/05/17 15:54:39

以下の式を試してください。
なお、Excel2016で確認しております。


=IF(ISERROR(FORMULATEXT(E1)), OFFSET(INDIRECT(E1),1,0), OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0) )


式内部の前半は条件として数式指定でない場合であり、後半は数式指定の場合であり、どちらの指定でも可能な形にしてあります。

また、ISFORMULA関数があるので、条件式はこれを使う方が、Then/Elseが逆になるけど、
この方がスマートでわかりやすいかも。

=IF(ISFORMULA(E1), OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0), OFFSET(INDIRECT(E1),1,0) )

id:cilgis

=IF(ISERROR(FORMULATEXT(E1)),OFFSET(INDIRECT(E1),1,0),OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0))
の場合#REF

=IF(ISFORMULA(E1),OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0),OFFSET(INDIRECT(E1),1,0))
の場合#NAME

というエラーになりました。
EXCEL2010です。

ちなみにOFFSET(INDIRECT(E$1)、1,0)
のように短い式ではできないでしょうか?

2018/05/17 22:55:10
id:kimuram No.2

kimuram回答回数19ベストアンサー獲得回数72018/05/18 15:41:12ここでベストアンサー

そうでしたか、やはり、というか、バージョン書いてなかったのでもしやと思いながらでしたが、当方の環境はほぼ2016にしてきていたので。
でも古い使い物にならないくらいのノートPCでExcel2010が残っていたので、とろとろと試してみました。
やはり2010ではformulatext関数は使えないですね。
2013からのようです。

となると、手がないです。

で、関数が用意されていなければ、作るしかない、ですね。
VBAユーザー関数で試してみた。
何とか実現できるようなので、以下に紹介します。
試してみてください。

まず、Excelファイル形式はxlsmにします。(VBAマクロを含むので)
そしてVBE(VBエディター)で標準モジュールを準備して、以下をコピー貼り付けする。


Function myFormulaText(prmCell)
wkformula = prmCell.Formula
If Left(wkformula, 1) = "=" Then
myFormulaText = Right(wkformula, Len(wkformula) - 1)
Else
myFormulaText = wkformula
End If
End Function


そして、ワークシートの数式には以下の内容を設定する

=OFFSET(INDIRECT(myformulatext(E1)),1,0)


これでどうでしょうか?
数式の内容はご希望に近いでしょう。
ただ、VBAを使用することの影響は免れません。
ファイル形式のこと、他にもメンテナンスの面等あるかと思います。

そのうえで、期待通りの動きとなると思います。
=を付けた数式でも、単にセル名でも可能です。
(数式で絶対アドレス($付き)にしても動作しました)

他2件のコメントを見る
id:kimuram

≪補足≫xlsm形式ファイルで保存の方法に関して
(1)「ファイル」メニューから「名前を付けて保存」
(2)ファイル種類から「Excelマクロ有効ブック(*.xlsm)」を選択
(3)「保存」を実行
(※Excel2010では若干表現が異なるかも)

2018/05/19 18:27:15
id:cilgis

とても分かりやすい解説、そしてVBAの使い方まで教えて頂けて感動しました!VBAはなんとなく避けていましたが、これを機会にトライしてみようかと思います。エクセルは使い始めると面白いですね。分からない計算式になると一日使っても自分では解決できなかったりしますが、解決できるとスッキリします。本当にありがとうございました!!

2018/05/21 10:26:53

コメントはまだありません

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません