人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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期と表示されます。後者にしたいので、=を付けて式が成り立つようにお知恵をお貸し頂ければ幸いです。よろしくお願い致します。

●質問者: cilgis
●カテゴリ:コンピュータ
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● kimuram

以下の式を試してください。
なお、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) )


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) のように短い式ではできないでしょうか?

2 ● kimuram
ベストアンサー

そうでしたか、やはり、というか、バージョン書いてなかったのでもしやと思いながらでしたが、当方の環境はほぼ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を使用することの影響は免れません。
ファイル形式のこと、他にもメンテナンスの面等あるかと思います。

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


cilgisさんのコメント
ご丁寧にありがとうございます。VBAの式とか書けちゃう所が凄いですね。一度も使った事がないのと、VBAは全く分からないので。EXCELも新しいのだと色々進化しているのですね。今までそんなに難しい式使わなかったので2010で不便を感じた事がありませんでした。EXCELを使いこなしている方は2010は不便で戻れないのだと思います。今度最新バージョンを購入しようかとも検討します。 例えばですが、H2のセルに=A1と入力した場合、H1のセルに文字列でA1と入力されるような式は2010ではVBA以外できないのでしょうか?念を押すようですみません。 それと、EXCEL2016を購入した場合、最後の式のMYを取った =OFFSET(INDIRECT(formulatext(E1)),1,0) では無理なのでしょうか? E1には必ず、=A1 か =B1 か =C1 かを入力する感じです。 VBAはハードルが高いので、シンプルな式で出来なければ、とりあえず手入力で A1 とか B1 とか C1 と入力してやろうかと思います。 古いパソコンを立ち上げてまで検証して頂きありがとうございます。 よろしくお願い致します。

kimuramさんのコメント
2010でも不便ほどではないと思います。 要は新たな便利機能を使うケースがどれだけあるかどうかだと思います。 2007からはファイル形式変更とともにデータ制限が拡大されたことが大きく、さすがに2003以前のものは限界ですが、2007以降では通常使用ではどのバージョンでもほぼほぼどれでもよいくらいかと。 >例えばですが、H2のセルに=A1と入力した場合、H1のセルに文字列でA1と入力されるような式は2010ではVBA以外できないのでしょうか?念を押すようですみません。 ない、と思います。 >それと、EXCEL2016を購入した場合、最後の式のMYを取った =OFFSET(INDIRECT(formulatext(E1)),1,0) では無理なのでしょうか? formulatext関数は先頭に数式を示す「=」が付いた形で返されるので、残念ながらこのままではINDIRECT関数には指定できません。 この先頭の1文字を除去してあげる必要がある訳です。 OFFSET関数だけでなくINDIRECT関数も、そしてネストで使用するような使いこなしをされているので関数は相当使われているようなので、その延長としてもう少し関数を重ねることも大丈夫かと思うのですが。 (IF文の多重ネストとかはわかりにくいですが、使うケースは結構あると思ってます) ただ、条件を限定すればその分シンプルにはなりますね。 指定を「=B1」のように3文字に限定すれば、指定する文字数は1文字少なくした計算不要で2となり、 right関数で文字列の右側から2文字固定で取得したものをINDIRECT関数に与える、ことをするので =OFFSET(INDIRECT(right(formulatext(E1),2)),1,0) となる。 しかし、もっと広い範囲での利用を考えるとき、その桁数は可変としなければならず、 それに対応できるようにするために、 まず文字列長を求め、そこから計算式により1文字少なくした長さで右側より取得(つまり先頭の1文字を除外するようにright関数して)したものをINDIRECTに指定する(ただ、長さを求めるためのデータを求めるためにまた同じ関数を使うことになり複雑に見えてしまうが)、 =OFFSET(INDIRECT(RIGHT(FORMULATEXT(E1),LEN(FORMULATEXT(E1))-1)),1,0) というように要望に応じて段階的に複雑になったり簡単になったりします。 また、ここからは余計なこと、のようなことですが。 いろいろやってるうちに、関数だけでは無理なこともVBAなら出来るケースも出てきます。 そんなとき、VBAにばかり頼るようでは仇になることもあるが、限定的になら利用を考えるのも手です。 例えば今回の紹介したVBAは、ユーザー関数(Functionプロシジャ)で特殊なもの(通常はSubプロシジャ)ですが、 また余計なことですが、VBAの操作です。 (1)Excel起動中に「Alt+F11」を押すとVBエディターが起動します。 (2)「挿入」?「標準モジュール」と操作すると、モジュール画面が開き、カーソルが現れるので、 (3)VBA文を貼り付ける とするだけで、後はワークシート側の操作だけで済みます。 試してみては如何でしょうか? これを機会にVBAに触れてみるとか。

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

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

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ