![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?e8efa67)
ある条件が非適合の場合、参照範囲を可変して同じ条件で値を抽出する関数を探しています。
うまく説明できないかもしれないので、添付画像に沿って質問させていただきます。
**はじめに**
・列Bと列Cの関連性はありません。
・列Dは、「ある一定の条件(IF)に適合」した場合、「1」と表示させています。
**条件**
① 今、D16がある一定の条件に適合したため、「1」と表示されました。
② そこで、基準となる値としてB16を参照し、E16に「91.6」と表示されました。
③ 次に、C2からC15の間で、E16の「91.6」を超えている値を探します。
このとき、探す順番(方向)は、C15から上に向けて探します。
④ まずC15からC12は「0」なので、不適合です。
次に、C11が「91.4」ですが、E16の「91.6」を超えていないので、不適合です。
C9も同様に不適合となります。
⑤ そしてC6の「91.7」でようやく「91.6」を超える値があるので、
この値を抽出してF16に表示します。
**その他**
<1> C2もE16の「91.6」を超えていますが、このExcelシートを時系列と考えた場合、
今日(1月15日)から遡って、“最も近い日付”でE16の「91.6」を超えた値を参照します。
<2> そのため、C2も「91.6」を超えていますが、C6(1月5日の行)よりも古い行なので、
C6を優先して参照します。
Excelのバージョンによる互換性から、できればマクロを使わずに関数で作成したいと考えています。
恐れ入りますが、どなたかお分かりの方がおられましたら、
ご教授いただけましたら幸いです。
宜しくお願い申し上げます。
![「Excelで範囲を可変して参照する関数を」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/1032074_54e1663843a8d/M.png)
No.3ベストアンサー
- 回答日時:
No.2です。
前回の数式は「その行」から遡っています。
ご希望は「1行上」から遡って・・・ですよね?
前回の数式は無視してF2セルは↓の数式に変更してください。
=IF(E2="","",IF(COUNTIF(C$1:C1,">"&E2),INDEX(C$1:C1,MAX(IF(C$1:C1>E2,ROW(A$1:A1)))),"該当なし"))
前回同様配列数式です。
どうも失礼しました。m(_ _)m
No.1さんのご回答を試行錯誤して「ROW($C$2:$C15)」の後ろに「-1」を付け加え、
=IF($K16=1,INDEX($J$2:$J15,MAX(($J$2:$J15>$L16)*ROW($J$2:$J15))),"")
を
=IF($K16=1,INDEX($J$2:$J15,MAX(($J$2:$J15>$L16)*ROW($J$2:$J15)-1)),"")
にすることで解決しました。
また、No.2さんのご回答のうち、補足のNo.3の通り入力すると、希望通りの結果が出ました。
「ROW関数」はこのように使うことができるのだと知り、とても勉強になりました。
ありがとうございました。
No.2
- 回答日時:
こんばんは!
No.1さんと同じく配列数式になりますが・・・
D列に関数が入っていて、その値に基づいてE列にデータが表示されるというコトですので、
↓の画像ではF2セルに
=IF(E2="","",IF(COUNTIF(C$2:C2,">"&E2),INDEX(C$2:C2,MAX(IF(C$2:C2>E2,ROW(A$1:A1)))),"該当なし"))
配列数式なのでCtrl+Shift+Enterで確定してください。
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → F2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定してください。
最後にF2セルのフィルハンドルで下へコピー!
これで画像のような感じになります。
※ 余計なお世話かもしれませんが、「参照値」よりも大きな値が以前にない場合は
「該当なし」と表示するようにしています。m(_ _)m
![「Excelで範囲を可変して参照する関数を」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/4/667667_54e1ccb170bbd/M.jpg)
No.1
- 回答日時:
こんにちは。
F16に
=IF($D16=1,INDEX($C$1:$C15,MAX(($C$2:$C15>$E16)*ROW($C$2:$C15))),"")
この数式を入力して、Ctrl + Shift + Enter で確定して配列(CSE)数式にします。
数式バーの表示が
{=IF($D16=1,INDEX($C$1:$C15,MAX(($C$2:$C15>$E16)*ROW($C$2:$C15))),"")}
に変われば正しく設定出来ています。
あとはこの数式を上下にコピーすればいいです。
ご回答ありがとうございます。
教えていただいた通りに関数を入力し、Ctrl+Shift+Enterで確定して配列数式にしたのですが、
F16には「0」と表示されました。
本来は「91.7」と表示されるはずなのですが、
何度試してもうまくいかず、悩んでいます。
もしよろしければ、補足説明をお願いできましたら大変助かります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excelについて A1からA12まで、1月〜12月と入力し、 B1からB12の範囲に、C1とD1に 4 2022/05/26 22:48
- Excel(エクセル) エクセル・スプレッドシートで、一定数を超えたらゼロから再累計する方法 8 2022/05/28 03:52
- Excel(エクセル) Excel 表の作成について 3 2022/06/16 12:15
- Excel(エクセル) エクセルで、 A1セルに「A」という値、 B1セルに「B」という値が入っています。 どちらも表示形式 5 2023/02/22 23:05
- Excel(エクセル) Excel2007での条件付き書式について 6 2023/05/02 10:56
- Excel(エクセル) EXCEL関数(数式)を教えてください 2 2022/06/08 18:32
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) countif関数について質問 4 2022/06/14 12:11
- Excel(エクセル) ワードのマクロについて教えてください。 1 2023/03/11 13:50
- Excel(エクセル) Excel2019 列と列(2列)の数値の重複を調べたい 1 2023/05/11 13:35
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
Excelの表示についての質問
-
エクセルの数式バーのフォント...
-
エクセルで「ページレイアウト...
-
2列に入っているデータを1列...
-
Excel VBAで全ての矢印を赤色に...
-
【マクロ】毎回、ファイル名が...
-
エクセルでファイルの最終更新...
-
Excelについて
-
文字列になっている時間をVBAで...
-
【ExcelVBA】名前を付けて保存→...
-
excel2013 MonthDays 関数が使...
-
エクセルの問題です。絶対値の...
-
Excelの数式について教えてくだ...
-
スプレッドシートの関数につい...
-
Excel VBについての質問です。
-
F9キーについて。
-
REGEXREPLACE関数について、
-
エクセルでセルに標準で入力さ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報
ご回答ありがとうございます。
教えていただいた通りに関数を入力し、Ctrl+Shift+Enterで確定して配列数式にしたのですが、
F16には「0」と表示されました。
本来は「91.7」と表示されるはずなのですが、
何度試してもうまくいかず、悩んでいます。
もしよろしければ、補足説明をお願いできましたら大変助かります。