既存Excel関数だけでA√B

質問

既存Excel関数だけを使用した数式で、入力した整数Nに対して、A√Bの形に直した場合の整数A、Bを求める方法はないでしょうか。要はNを割り切る最大の平方数が返せればいいのですが、これが意外とExcelでは難しく、現在お手上げ状態です。
なお、Nの最大値N_Mがわかっている場合には√N_M以下の整数について大きい方からひとつひとつ割り切れるかをIF文で調べていく方法は考えつきましたが、可能ならもっとシンプルに、Nの大きさにかかわらず一律に適用できる数式が構築したいと思っています。
また、マクロを使用する方法はここでは対象外とします。何かアイディアがある方がいらっしゃいましたら是非ご教示ください。

回答No.7

SI299792 · ベストアンサー

これは、配列を使ているので説明が難しいです。
Microsoft365を使って説明させていただきます。お持ちでない場合、こちらをご覧ください。
https://1drv.ms/x/s!AnfEM367OeSdkU_7aTo3KnAsF7mO?e=Jps7oo

√200 の場合 1～14の 2乗で割っていき、余りが 0になる最大を求めればいいわけです。

E2: ="1:"&INT(SQRT(A2))
「1:14」の文字列ができます。
F2: =ROW(INDIRECT(E2))
1 ～14の配列が生成されます。
G2: =A2/F2#^2
２乗で割ります。
H2: =MOD(G2#,1)
余りを出します。
I2: =F2#*(H2#=0)
余りが 0以外の場合 0にします。
J2: =MAX(I2#)
最大値を求めます。

これらを纏めると、
=MAX(ROW(INDIRECT("1:"&INT(SQRT(A2))))*(MOD(A2/ROW(INDIRECT("1:"&INT(SQRT(A2))))^2,1)=0))
になります。

このままでは、Excel2021 かMicrosoft365でないと使えできません。
旧バージョンでもでも使えるようにするには
・Ctrl＋Shift ＋Enter で配列数式にする。
・INDEX を付ける。（INDEX は配列関数ととしての機能があるので、計算可能になります）
今回、後者の方法を取りました。

＞この関数を他のExcelファイルで使おうとするとVBEを立ち上げてインポートしなければならない
これは、マクロを入れたファイルをOneDrive等にアップロードして、他の人にダウンロードしてもらえばいいのでは❓。マクロを入れたワークブックを渡せば、VBE 等の操作は不要ですし、元々xlsmなので、上書き保存すればそれでいいです。
今回、既存関数でできましたが、できないものを渡すこともありうると思います。

お礼

こちらのわがままなリクエストにお答えいただいて詳細なご説明ありがとうございます！助かります！そして勉強になりました！

Powered by GRATICA

補足

お礼の補足です。配列数式が「{○○}」と記述しなくてもよくなっていたとは存じ上げませんでした！最近Excelが新しくなったばかりだったので全然気づいてませんでした。いただいた数式を見て配列っぽいとは思っていたのですが…
そして、使われているひとつひとつの関数は知っていたのですがこのような使い方は目から鱗でした。私の知っている配列数式の作り方は実在するセル範囲に対して行うもので、ご教示いただいたように仮想の配列のような使い方ができることに驚きです。これは使いこなせれば今後かなり多くの応用ができること間違いありません。まだまだ勉強が足りませんでした。頑張ります。
また、VBAについてもアドバイスをいただき、恐縮です。ただ、…職場環境的にxlsmを開いただけで最初のメッセージにあたふたする方々の中にいますので、大変なのです…ですので数式をいただけて本当に助かりました。
ありがとうございました。

回答No.8

chayamati

回答№6で=INT(SQRT(C11))とお答えしました
添付図をご覧ください

1.Aを求める。
　A=INT(SQRT(N))
2.Bを求める
　B=N-A^2

回答No.6

chayamati

int()関数の入れ子にするのはどうでしょう
=INT(SQRT(C11))

補足

ご回答ありがとうございます。ですが、√Nの整数部分とは異なりますので残念ながらご教示の方法では求めたいものは得られません…

回答No.5

noname#258506

ごめんパートナーが勝手にアカウント削除してしまった
noname#258475
なんか素人目せんだけど
A列B列が1だったりすること多い気がするけど
√16=4√1は√16=2√4もありなんじゃないかと思う。違ったらごめん○○なので許して
他の解で片方が1の場合でも他に解があるかもしれないぞということになってそれなら他の1じゃないのも1でいいんじゃね計算しなくていいんじゃね

〇〇な素人はそう思うのであります
ちゃちゃでごめん

補足

ルートの中が最も小さい形、言い換えるとA√BがNを割り切る最大の平方数の√ということを想定しています。

回答No.4

noname#258475

外野からごめん
整数N=A√B
の整数Aと整数Bが知りたいということではない？
√N=A√B
√8は整数なの？？？

補足

「整数Nに対して、√NをA√Bの形に直した場合」が正しかったです。おっしゃるとおりA√Bの整数Aと整数Bをエクセルの既存の関数で求める方法が知りたいという質問です。

回答No.3

SI299792

A2: 整数を入力
B2: =MAX(INDEX(ROW(INDIRECT("1:"&INT(SQRT(A2))))*(MOD(A2/ROW(INDIRECT("1:"&INT(SQRT(A2))))^2,1)=0),))
C2: =A2/B2^2

画像はＡ列、Ｃ列に、表示形式、ユーザー定義 ｢√0｣を指定
確認の為下へオートフィルしてあります。

お礼

すごい！いただいた数式をそのままコピペしたらできました！ありがとうございます！

Powered by GRATICA

補足

今後のため数式を解析しようと思ったのですが、つまづいてしまいました。もしよろしければどういう数式のつくりなのか簡単にご説明いただくことは可能でしょうか？

回答No.2

NuboChan

個人的には、既存Excel関数だけを使用した数式では、無理だと思います。

Fanction形式なので関数定義と同様に利用できるので
VBAの知識の無い人でも利用上困らないと思いますが？

求める事と違って既に「実はVBAを使用する方法では解決済」なら
他の方の回答をお待ちください。

補足

やはり既存関数では無理なのかもしれませんね。念のため他の方の回答をもう少し待ってみます。

Function形式で私も作ってみました。問題なく作動はするのですが、他の人がこの関数を他のExcelファイルで使おうとするとVBEを立ち上げてインポートしなければならないことや、ファイル形式をxlsmにしなければならないことを考えると可能なら既存Excel関数を駆使して実現できないかと思ったわけです。

誤解を招かないようにマクロを対象外と書いたのですが、もっと詳細に事情を書いた方がわかりやすかったのかもしれません。

回答No.1

NuboChan

以下のような事でしょうか　？

Option Explicit

Function Sqrt(N As Long) As String
Dim i As Long
For i = Sqr(N) To 1 Step -1
If N Mod (i ^ 2) = 0 Then
Sqrt = i & "√" & N / (i ^ 2)
Exit Function
End If
Next i
End Function

お礼

ご回答ありがとうございます。ですが、質問文にあるとおり、既存のExcel関数で実現する方法がないでしょうか？

Powered by GRATICA

補足

実はVBAを使用する方法では解決済です。しかし、VBAの知識がない人でもファイルを共有できるようにするため、マクロを使わないで実現する方法を模索している次第です。わかりにくかったとしたらすみません。