マーベル映画シリーズの伝説の始まり『アイアンマン』を紐解く! >>

画像のようなものを作ろうと思っています。
ですが、vlookupやindex・match関数では私の力ではうまい具合にはいきませんでした。

どのように数式を入れるとよろしいのでしょうか?

「指定範囲から任意の近似値を探したい」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (7件)

こんにちは!



G12セルに
=MIN(IF(B3:E15>G8,B3:E15))
これは配列数式になりますので、Ctrl+Shift+Enterで確定!

G14セルに
=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:B15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E3)),4)

という数式を入れてみてください。m(_ _)m
    • good
    • 4
この回答へのお礼

ありがとうございます、両方の式で私の期待していた値を出すことができました。
今回は、こちらをベストアンサーとさせてください。

お礼日時:2014/06/06 12:55

回答No.3です。


検索範囲を勘違いしていたようです。
検索範囲をB3~E15とする場合は次の式を使ってください。

=SUMPRODUCT(MIN((B3:E15>G8)*B3:E15+(B3:E15<=G8)*1000000))

また、検出された値のセルのアドレスを算出する数式を忘れていました。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)
    • good
    • 2
この回答へのお礼

ありがとうございます、目当ての値を出すことができました。

お礼日時:2014/06/06 13:00

近似値が複数あった場合の事を考えると、セル番地を表示するよりは条件付き書式で表のセル色を変えた方が良くないですか。


別な用途の為にセル番地を表示しようとしているのでしたらごめんなさい。
「指定範囲から任意の近似値を探したい」の回答画像6
    • good
    • 0
この回答へのお礼

ありがとうございます、これ単体で使うのではなく
これを利用して別のものを作ろうとしています。
ですが、たしかに色分けして見易くしたほうが確認も容易ですね、取り入れます。
お気遣いありがとうございます

お礼日時:2014/06/06 13:02

No.4です。



投稿後気づきました。

No.1さん・No.2さんの回答とほぼダブっています。
どうも失礼しました。m(_ _)m
    • good
    • 0

質問の内容でVLOOKUP関数は一致する値またはより小さい値を検出することができます。


INDEX関数とMATCH関数の組み合わせでは同様な結果になりますがMATCH関数の返り値に1を加算すれば目的に合う結果になるでしょう。
他の方法としてはSUMPURODUCT関数とMIN関数を使って行番号を検出してINDEX関数に渡せば目的の値が得られます。

=INDEX(D3:D15,MATCH(G8,D3:D15)+1,1)
=INDEX(D1:D15,SUMPRODUCT(MIN((D3:D15>G8)*ROW(D3:D15)+(D3:D15<=G8)*1000)),1)

この回答への補足

回答なされた関数は予めD列にあることが分かっているものではありませんか?
今回は画像のものですと、B3:E15のものが対象です。

上は入力値を変えてみるとエラーがでて、
下は循環依存エラーがでてしまいました。

補足日時:2014/06/06 11:46
    • good
    • 0

近似値を表示するG12セルには以下の式を入力します。



=MIN(INDEX((B3:E15<G8)*100000+(B3:E15-G8),))+G8

セル番地を表示する目的がよくわかりませんが(おそらくもっと合理的な処理があると思いますが)、どうしてもセル番地を表示したいならG14セルには以下の式を入力します。

=ADDRESS(SUMPRODUCT((B3:E15=G12)*ROW(B3:E15)),SUMPRODUCT((B3:E15=G12)*COLUMN(B3:E15)),4)

この回答への補足

上の方の値、残念なことに配列の値が見つからないとvalueエラーがでてしまいました。

下のものは期待通りの値がでました、ありがとうございます。

補足日時:2014/06/06 12:52
    • good
    • 1

以下の様に入力し、CtrlとShiftキーを押しながらEnterキーで確定



=MIN(IF(D3:D15>G8,D3:D15,""))


又は、D2、G7にそれぞれ同じ見出し(例えば、「数値」とか)を入れ、
G8は「>1180」といれ、以下の式でもできます。


=DMIN(D2:D15,1,G7:G8)
    • good
    • 2
この回答へのお礼

ありがとうございます、こんな単純な式でよかったのですね。期待していた値になってくれました。

お礼日時:2014/06/06 12:48

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q最も近い数値のあるセルを探す

お願いいたします。
エクセルVBAです。
例えばC列に10000行以上にわたって数値が不作為に
並んでいるばあい、347.398に一番近い数値の
あるセルの行を求めるコードはどのようになりますか?
ご教示お願いいたします。

Aベストアンサー

#03です。一応マクロも書きました。
Sub Macro()
Dim idx, MinR As Long
Dim TargetVal, MinVal As Single
Dim ws
 TargetVal = 347.398 '← 目標値
 With Worksheets("Datum") '← ワークシート名
  Set ws = .Range("A1")
  MinVal = 10 ^ 6
  For idx = 1 To .Range("C65536").End(xlUp).Row
   If IsNumeric(ws(idx, 3)) Then
    If Abs(ws(idx, 3) - TargetVal) < MinVal Then
     MinVal = Abs(ws(idx, 3) - TargetVal)
     MinR = idx
    End If
   End If
  Next idx
  MsgBox ("目的の行は" & MinR & "行目です")
 End With
End Sub

機種にもよりますが、10000行でも2秒程度でしょう。

>これがVBAでコード化可能であれば
とのことですが、#03の方法ではワークシートに式を入れておけば済む話ですからこの式をVBAの中で実現するのは意味がないと思います。

#03です。一応マクロも書きました。
Sub Macro()
Dim idx, MinR As Long
Dim TargetVal, MinVal As Single
Dim ws
 TargetVal = 347.398 '← 目標値
 With Worksheets("Datum") '← ワークシート名
  Set ws = .Range("A1")
  MinVal = 10 ^ 6
  For idx = 1 To .Range("C65536").End(xlUp).Row
   If IsNumeric(ws(idx, 3)) Then
    If Abs(ws(idx, 3) - TargetVal) < MinVal Then
     MinVal = Abs(ws(idx, 3) - TargetVal)
     MinR = idx
    End If
  ...続きを読む

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcelで一番ゼロに近い値を求めるには

Excelで一番ゼロに近い値(正負)を求めるにはどうしたらよいでしょうか?

希望は、条件書式で設定したいのです。
もし駄目なら列を挿入して計算式用の列を作っての方法をご教授ください。

例えば、

A1  0


A5  0.5
A6  -0.3
A7  0.1
A8    (空白)
A9  -0.12

セルA5からA9の間で一番ゼロに近い値を求めたいです。
その時にセルA8の空白は考えないようにしたいです。

条件書式か計算式でできるでしょうか?
どうぞよろしくお願いいたします。

Aベストアンサー

配列数式にせざるをえないのかな
=MIN(IF((A2:A10<>"")*(A2:A10<>0),ABS(A2:A10),99999))
99999は、A2:A10で5桁の数字以下しかない場合の式。
例データ
0

5
0.3
0.11
1

21
-1
  で
結果
0.11
===
それとか、ユーザー関数を定義するとか。5桁以内の数で空白と0は対象外。
標準モジュールに
Function sabs(a)
x = 99999
For Each cl In a
If cl <> "" And cl <> 0 Then
If Abs(cl) < x Then x = Abs(cl)
End If
Next
sabs = x
End Function
ーー
シートで=Sabs(A1:A10)と入れる
結果
0.11

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

QVBA 指定値に最も近い値のセルの行番号は?

A列に数値が昇順で並んでます。指定の値に最も近い値が並んでるセルの行番号を知りたいのです。

WorksheetFunction.vlookupを使うと、値は分っても行番号はわからない筈です。
Cells.Find().Row を使うと、行番号はわかりますが「最も近い」値はわからない筈です。

マクロを組め!との御指摘があるかも知れませんが、便利な関数があるのならそれで済ませたいです。何かありませんか?無ければ諦めます。

Aベストアンサー

とりあえず、一案です。
B1セルに指定の値が入っているとして、
B2セルにでも
=MIN(ABS(A1:A2000-B1))
とでも入れて、Ctrl+Shift+Enterで配列関数にすると
もっとも誤差が少ない数値が出ます。
B3セルにでも(エクセル2013の場合)
=IFERROR(MATCH(B1+B2,A:A,0),MATCH(B1-B2,A:A,0))
とすれば、その値が入っている行番号がでますが
プラス側、マイナス側で誤差が全く同じ場合にどちらの値を優先にするか
それを決めなくてはなりませんね。

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

QExcel 表から値をさがして隣のセルの値を返す

お願いします、教えて下さい。
VLOOKUPのようにいまから左端に番号などを入れる手間無く、値を返したいです。

一つの目のbook     2つ目のbook
 A列  B列      A列   B列
顧客   担当    木下麻衣
小野博美 佐藤          ↑
田中香織 吉岡       ここに関数を入れて、B列の金田を返す
木下麻衣 金田   

同シート上で表をふたつ作っても構いません、どなたか教えて下さい。 

Aベストアンサー

質問者さんの意図にあっているかどうかわかりませんが。

内容を見る限りでは2つ目のbookのA列が検索値に
なるようでしたら、

=VLOOKUP(A1,Sheet1!A1:B5,2)

では如何でしょうか?
1つ目のbookをsheet1、2つ目のbookをsheet2で考えてます。
また、左上よりA1としてますので確認ください。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング