EXCELで

Question

cilgis

6

0もっと見る

コンピュータ

EXCELで

　　A 　B　 C　　　D　　　E　
1　１期　　２期　３期
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人5回まで

登録：2018/05/17 12:51:15
終了：2018/05/21 10:21:39

No.1

きむむ27112018/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)  )

=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

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

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

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

リクエスト送信済

回答リクエストを送信したユーザーはいません

きむむ · Accepted Answer · 2018-05-18T15:41:12+09:00

No.2

きむむ27112018/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件のコメントを見る

2010でも不便ほどではないと思います。
要は新たな便利機能を使うケースがどれだけあるかどうかだと思います。
2007からはファイル形式変更とともにデータ制限が拡大されたことが大きく、さすがに2003以前のものは限界ですが、2007以降では通常使用ではどのバージョンでもほぼほぼどれでもよいくらいかと。

＞例えばですが、H2のセルに＝A1と入力した場合、H1のセルに文字列でA1と入力されるような式は2010ではVBA以外できないのでしょうか？念を押すようですみません。

ない、と思います。

＞それと、EXCEL2016を購入した場合、最後の式のMYを取った
=OFFSET(INDIRECT(formulatext(E1)),1,0)
では無理なのでしょうか？

formulatext関数は先頭に数式を示す「=」が付いた形で返されるので、残念ながらこのままではINDIRECT関数には指定できません。
この先頭の１文字を除去してあげる必要がある訳です。
OFFSET関数だけでなくINDIRECT関数も、そしてネストで使用するような使いこなしをされているので関数は相当使われているようなので、その延長としてもう少し関数を重ねることも大丈夫かと思うのですが。
（IF文の多重ネストとかはわかりにくいですが、使うケースは結構あると思ってます）

ただ、条件を限定すればその分シンプルにはなりますね。
指定を「=B1」のように３文字に限定すれば、指定する文字数は１文字少なくした計算不要で２となり、
right関数で文字列の右側から2文字固定で取得したものをINDIRECT関数に与える、ことをするので
=OFFSET(INDIRECT(right(formulatext(E1),2)),1,0)
となる。
しかし、もっと広い範囲での利用を考えるとき、その桁数は可変としなければならず、
それに対応できるようにするために、
まず文字列長を求め、そこから計算式により1文字少なくした長さで右側より取得（つまり先頭の１文字を除外するようにright関数して）したものをINDIRECTに指定する（ただ、長さを求めるためのデータを求めるためにまた同じ関数を使うことになり複雑に見えてしまうが）、
=OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0)
というように要望に応じて段階的に複雑になったり簡単になったりします。

また、ここからは余計なこと、のようなことですが。
いろいろやってるうちに、関数だけでは無理なこともVBAなら出来るケースも出てきます。
そんなとき、VBAにばかり頼るようでは仇になることもあるが、限定的になら利用を考えるのも手です。
例えば今回の紹介したVBAは、ユーザー関数（Functionプロシジャ）で特殊なもの（通常はSubプロシジャ）ですが、
また余計なことですが、VBAの操作です。
（１）Excel起動中に「Alt＋F11」を押すとVBエディターが起動します。
（２）「挿入」－「標準モジュール」と操作すると、モジュール画面が開き、カーソルが現れるので、
（３）VBA文を貼り付ける
とするだけで、後はワークシート側の操作だけで済みます。
試してみては如何でしょうか？
これを機会にVBAに触れてみるとか。

2018/05/19 18:14:46

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

2018/05/19 18:27:15

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

2018/05/21 10:26:53

きむむ · Accepted Answer · 2018-05-18T15:41:12+09:00

No.2

きむむ27112018/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件のコメントを見る

2010でも不便ほどではないと思います。
要は新たな便利機能を使うケースがどれだけあるかどうかだと思います。
2007からはファイル形式変更とともにデータ制限が拡大されたことが大きく、さすがに2003以前のものは限界ですが、2007以降では通常使用ではどのバージョンでもほぼほぼどれでもよいくらいかと。

＞例えばですが、H2のセルに＝A1と入力した場合、H1のセルに文字列でA1と入力されるような式は2010ではVBA以外できないのでしょうか？念を押すようですみません。

ない、と思います。

＞それと、EXCEL2016を購入した場合、最後の式のMYを取った
=OFFSET(INDIRECT(formulatext(E1)),1,0)
では無理なのでしょうか？

formulatext関数は先頭に数式を示す「=」が付いた形で返されるので、残念ながらこのままではINDIRECT関数には指定できません。
この先頭の１文字を除去してあげる必要がある訳です。
OFFSET関数だけでなくINDIRECT関数も、そしてネストで使用するような使いこなしをされているので関数は相当使われているようなので、その延長としてもう少し関数を重ねることも大丈夫かと思うのですが。
（IF文の多重ネストとかはわかりにくいですが、使うケースは結構あると思ってます）

ただ、条件を限定すればその分シンプルにはなりますね。
指定を「=B1」のように３文字に限定すれば、指定する文字数は１文字少なくした計算不要で２となり、
right関数で文字列の右側から2文字固定で取得したものをINDIRECT関数に与える、ことをするので
=OFFSET(INDIRECT(right(formulatext(E1),2)),1,0)
となる。
しかし、もっと広い範囲での利用を考えるとき、その桁数は可変としなければならず、
それに対応できるようにするために、
まず文字列長を求め、そこから計算式により1文字少なくした長さで右側より取得（つまり先頭の１文字を除外するようにright関数して）したものをINDIRECTに指定する（ただ、長さを求めるためのデータを求めるためにまた同じ関数を使うことになり複雑に見えてしまうが）、
=OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0)
というように要望に応じて段階的に複雑になったり簡単になったりします。

また、ここからは余計なこと、のようなことですが。
いろいろやってるうちに、関数だけでは無理なこともVBAなら出来るケースも出てきます。
そんなとき、VBAにばかり頼るようでは仇になることもあるが、限定的になら利用を考えるのも手です。
例えば今回の紹介したVBAは、ユーザー関数（Functionプロシジャ）で特殊なもの（通常はSubプロシジャ）ですが、
また余計なことですが、VBAの操作です。
（１）Excel起動中に「Alt＋F11」を押すとVBエディターが起動します。
（２）「挿入」－「標準モジュール」と操作すると、モジュール画面が開き、カーソルが現れるので、
（３）VBA文を貼り付ける
とするだけで、後はワークシート側の操作だけで済みます。
試してみては如何でしょうか？
これを機会にVBAに触れてみるとか。

2018/05/19 18:14:46

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

2018/05/19 18:27:15

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

2018/05/21 10:26:53

EXCELで

ベストアンサー

きむむ27112018/05/18 15:41:12

その他の回答（1件）

きむむ27112018/05/17 15:54:39

きむむ27112018/05/18 15:41:12ここでベストアンサー

コメント（0件)

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