指定範囲から任意の近似値を探し、セル場所表示したい

質問

http://okwave.jp/qa/q8625875.html
で、一旦解決を押したのですが
実は解決していませんでしたので、申し訳ないですが、もう一回書きます。

入力値に数字を入れて、近似値を出し、その場所を知りたいのですが
40000だと正常にでるのに対して
http://gyazo.com/30b213fbc5d575c53a561c9b3dd08e68

100000だとうまく動いてくれません。
http://gyazo.com/b5fcbcd3a9d76509c1586020fe2aa552

H54には
=ADDRESS(SUMPRODUCT(($D$43:$G$60=H52)*ROW($D$43:$D$60)),SUMPRODUCT(($D$43:$G$60=H52)*COLUMN($D$43:$G$43)),4)

が入っています。どんな値でも取り出せるようにするにはどうしたらいいでしょうか？
（本当はH54は必要なく、I54・J54の情報だけ取り出したいのですが・・・）

回答No.3

keithin · ベストアンサー

＞どんな値でも取り出せるようにするには

104816が２か所に記入されているので、今のご相談内容ではどっちの番地を取り出したらいいのか決められません。

その意味では
=IF(COUNTIF(D43:G60,H52)>1,"複数あります",ADDRESS(SUMPRODUCT((D43:G60=H52)*ROW(D43:G60)),MATCH(J54,42:42,0),4))
といった数式が、一番ご相談に合致した回答ということになります。

まぁ無理すれば
=SUBSTITUTE(CELL("address",INDIRECT(TEXT(MIN(IF(D43:G60=H52,ROW(D43:G60)*100+COLUMN(D43:G60))),"!R0!C00"),FALSE)),"$","")
と記入し、コントロールキーとシフトキーを押しながらＥｎｔｅｒとかでもいいです。

お礼

すいません、式をコピーする際になにかいじってしまってセル番号がズレていただけでした。
無事表示できました、本当にありがとうございます

補足

上記は
エラー: 関数 INDIRECT のパラメータ 1 の値が「!R58!C04」です。これは無効なセル/範囲の参照です。

下記は
エラー: 関数 ADDRESS のパラメータ 1 の値が 0 です。1 以上で指定してください。

とでてしまいます。

重複する値は、両方取り出す必要はなく、どちらか１つだけ（指定なし）取り出すものでも十分なのですが
難しいでしょうか

回答No.2

tom04

こんばんは！
前回回答した者です。

前回の質問サンプルでは重複データがなかったので、
H54の数式で対応できたはずです。
今回の質問では重複データが存在していますよね？
これではかなり厄介です。

そこでユーザー定義関数を作ってみました。
Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペーストしてください。

Function 番地(範囲 As Range, 近似値 As Range) 'この行から
Dim c As Range, str As String
For Each c In 範囲
If c = 近似値 Then
str = str & c.Address(False, False) & ","
End If
Next c
番地 = Left(str, Len(str) - 1)
End Function 'この行まで

次にH54セルを選択　→　アドレスバーの右側のfxのアイコンをクリック　→　関数の分類の右側▼で「ユーザー定義」を選択
→　「番地」というユーザー定義関数が選択されている状態になりますので　→　OK
これで関数の引数ダイアログボックスが表示されます。
「範囲」の窓で対象セルを範囲指定　→　「近似値」の窓で近似値が入っているセルを選択　→　OK
これで近似値が複数あっても複数のセル番地が表示されると思います。

尚、データが複数ある場合「行」・「列」の表示はどちらを表示するのかという問題が出てきますので
今回は割愛します。

ひとつだけでよいのであれば簡単だと思いますが、複数表示したい場合は一筋縄ではいかないと思います。

とりあえず今回はこの程度で・・・m(_ _)m

お礼

ご丁寧に、ありがとうございます。
ただ、今回使用しているのがgoogleスプレッドシートなので、多分使用できないのではないかと思われます。最初に明記しておくべきでした

回答No.1

noname#204879

素朴な感想だけど…
元の質問に提示された表中の数値データは
上から下に、および、左から右に昇順に並んでおり、重複データはありませんね。
先の回答者は当該規則に基づいて難解な式を提示してくれたのです。

ところが、「実は解決していませんでした」と仰っている表は当該規則に外れているどころか，例えば43行目と45行目は全く同じデータですよね。

それから、「本当はH54は必要なく、I54・J54の情報だけ取り出したいのですが」なんてながっかりさせるようなことはナシにすべきです。

ご一考ください。

お礼

すいません、前提条件をもっと細かに書くべきでしたね。
当方の知識不足から、最初に例に挙げた表で全てまかなえるだろうと思いこんでおりました。