教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows

 

 

Excel2010:範囲から近似値を求めるには

 

 


 

 

範囲から近似値を求める方法をご紹介します。↓緑の範囲から、基準値の近似値を出してみましょう。

 

◆ 近似値を求める(基準値を超えない場合)

【基準値以下の数の中で最も大きい値】を返します。例:5が基準値なら5以下の数字(4,3,2)の中で1番大きい4が近似値)MAX関数とIF関数を使います。

 

= MAX(IF(A1:C6<=E2,A1:C6,"")) と入力し、CTRL+SHIFT+Enterキーを押す(※配列数式)

{=MAX(IF(A1:C6<=E2,A1:C6,"")) }

 

 

◆ 近似値を求める(基準値を超える場合)

【基準値以上の数の中で最も小さい値】を返します。(例:5が基準値なら5以上の数字(6,7,8)の中で1番小さい6が近似値)MIN関数とIF関数を使います。
 

= MIN(IF(A1:C6>=E2,A1:C6,"")) と入力し、CTRL+SHIFT+ENTERキーを押す(※配列数式)

{= MIN(IF(A1:C6>=E2,A1:C6,"")) }

 

 

 --- 補足1:範囲の中で近似値に色を付けるには(↑の例の場合) --- 

  1. 範囲選択し(A1:C6)「ホーム」タブ−「条件付き書式」−「新しいルール」をクリック

  2. 「数式を利用して書式設定するセルを決定」を選択し「=A1=$F$2」として色を設定し「OK」
    (数式のセルを指定する)

 

 --- 補足2:VLOOKUP関数でも基準値を超えない近似値を出せます。(昇順に並べ替えが必要)---

下図でB2(基準値)を超えない近似値を返すにはVlookup関数で検索方法の引数を1(True)にします。

=VLOOKUP(B2,A2:A8,1,1)

  • Vlookup関数では、4番目の引数(検索方法)を1(TRUE)にすれば検索値を超えない近似値を含めて返せます。Vlookup(検索値,範囲,列番号,検索方法)

  • 範囲を昇順に並べ替えておく必要があります。(上図でいうA列)

 

 

 

教えて!HELPDESK      Excel     Word      Outlook      Power Point      Windows