excel 名前付きセルの名前（セルに定義した名前）数式で表示するには？

あらかじめ名前を定義したセルがあるとします
（例：セルA1の名前を「へのへのもへじ」とする）
この名前は、VBAであればRange("A1")の .Name プロパティとして扱えるのでしょうが

VBAでなくExcelのワークシート上で
数式バーに記入する「数式」では
どのように表記し、どのように扱えば良いのでしょうか？

例えば
セルB1に、セルA1を参照する数式を作ると
数式バーに
＝＄A$1ではなく
=へのへのもへじ
と表示されます（へのへのもへじは青色文字になっていて”参照”である事が分かります）

この時
セルB1には、その時にセルA1に入力されている値（セルA1入力値）が表示されます。
ここで
セルB1に、セルA1入力値 ではなく、セルA1の名である文字列”へのへのもへじ”を表示させるには
数式バーにどういう式を書けばよいか？
という問いです

※
=CELL(検査項目,参照セル)　で出来るのではないかと思いましたが
CELL関数の検査項目中に、セルの名前 は 残念ながら、無いようで…

宜しくお願い致します

質問者からの補足コメント

• 言葉が下手ですみません。
  中継セルなしでやる、といいましょうか…
  0が表示されているB1のその場所に
  0でなくて"へのへのもへじ"を表示させる事が
  出来るような式が有るか？
  というのが知りたい事です

  No.4の回答に寄せられた補足コメントです。 補足日時：2021/02/19 10:12

No.1 ベストアンサー 回答者： aouze_ya 回答日時： 2021/02/18 19:26

=MID(FORMULATEXT(B1),2,30)

この回答へのお礼

aouze_ya様
早速のご回答を、どうも有難うございます
いや、ビックリ！
コレ、自分の頭では絶対に出て来ない発想でした
A1のセル名称を、B1を中継して、更にB3で表示させているのですね！
スゴイです・・・

ただ、
ひとつだけ、この方法の欠点としては
上図で0が表示されている「B1の位置」に「へのへのもへじ」を表示したい
という場合は、諦めなければいけないですね

もしも、
それも出来るよ！という方法を御存じの方がいらしたら、お教えいただきたいので、まだ質問を閉じずに、引き続き回答募集を続けております。

お礼日時：2021/02/18 22:50

No.7 回答者： oniwabangairu 回答日時： 2021/02/20 08:51

実に非実用的ですが、無理矢理では、

この回答へのお礼

oniwabangairu様
凄い高度な技をお教えいただきまして
有難うございます
ですが、すみません
私の知能（低いCPU）では全貌を解読することが
出来ませんでした
悪しからず

お礼日時：2021/02/23 15:33

No.6 回答者： oniwabangairu 回答日時： 2021/02/19 18:07

No.5です。

　回答に思い違いがありました。
一見してできたように思ったのは、間違いです。
どこかで　名前定義した名前を使った数式をつくって、その数式のあるセルを参照するだけのことでした。　ごめんなさい。

この回答へのお礼

oniwabangairu様
No5.
No6.
あわせまして
ご回答・ご訂正
どうも有り難うございます

お礼日時：2021/02/19 21:09

No.5 回答者： oniwabangairu 回答日時： 2021/02/19 11:13

vbaやマクロ(定義関数を含む)を使うのが簡単でしょうが、あくまでEXCEL関数だけでというのなら、できないことではないようです。

=MID(FORMULATEXT(OFFSET(A1,0,1)),25,FIND(",0,1)",FORMULATEXT(OFFSET(A1,0,1)))-25)

No.4 回答者： chonami 回答日時： 2021/02/19 06:57

>上図で0が表示されている「B1の位置」に「へのへのもへじ」を表示したい

中継セルを変えればいいだけですよね？

この回答へのお礼

先に補足コメントを書いてしまいました
先ずは御礼を申し上げるべきでした
どうも
ご回答有り難うごさいました。

お礼日時：2021/02/19 21:06

No.3 回答者： ママチャリ 回答日時： 2021/02/18 23:05

VBAを理解されていて、さらに.Nameプロパティで扱えるのを確認されているのであれば、その値を返すユーザ定義関数を作っちゃうのが早そうですね。

試しに作ってみたのですが、RangeオブジェクトのNameプロパティでは目的の値を取得できませんでした。NameオブジェクトのNameプロパティを参照する必要があるようです。ご参考までに！！

この回答へのお礼

ママチャリ様
ご回答ありがとうございます。
○○○.Name.Name
っていう変な書き方（笑）をする奴のことですね！

お礼日時：2021/02/18 23:31

No.2 回答者： Qchan1962 回答日時： 2021/02/18 23:00

こんばんは、

数式で入力されているセルを対象にその数式で使われている（名前）を取得する方法は、既に回答がありますが、

名前定義を設定しているセルを直接参照して設定の名前を数式で取得するのは、ユーザー定義関数を作るしかないのでは無いかと思います。
（正しい情報かどうかについては、自信はありませんが、、）

ユーザー定義関数？

結局VBAかと思われるかと思いますが、一応サンプルです。

Function CN(TargetCell As Range) As String
Dim Qn As Name
　For Each Qn In ActiveWorkbook.Names
　　If Qn Like "*" & ActiveSheet.Name & "*" Then
　　　If Not Intersect(TargetCell, Range(Qn)) Is Nothing Then
　　　　CN = Qn.Name
　　　End If
　　End If
　Next
End Function

=cn(A1) で

If Qn Like "*" & ActiveSheet.Name & "*" Then　は、
複数シートに名前定義がある場合やFORMULATEXT関数など
使用時の不具合対策です。

名前定義の範囲に含まれているセルでも名前が返されます。
取得範囲を広げた場合、1セルでも名前定義に入っていれば、その名前が、
複数の名前定義に掛かっていれば、最後に該当する名前が返されます。

以前確認用で作った物なので、
こんな仕様では使えない場合は、ご存知の通り＝Range.Nameで

この回答へのお礼

Qchan1962様
ご回答ありがとうございます。
丁寧な式を頂けまして恐縮です

やはり、結局VBAでなければムリ！
という感じですかねぇ・・・

お礼日時：2021/02/18 23:33