ある条件が非適合の場合、参照範囲を可変して同じ条件で値を抽出する関数を探しています。
うまく説明できないかもしれないので、添付画像に沿って質問させていただきます。
**はじめに**
・列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のバージョンによる互換性から、できればマクロを使わずに関数で作成したいと考えています。
恐れ入りますが、どなたかお分かりの方がおられましたら、
ご教授いただけましたら幸いです。
宜しくお願い申し上げます。
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
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について教えてください
-
【Excel】数字を3倍にし、なお...
-
エクセルVBA、別ブックへ転記す...
-
エクセル マクロ チェックボックス
-
Excel分析ツールでのポアソン回...
-
【マクロ】顧客番号にて一致さ...
-
【マクロ】名前を保存する際に...
-
エクセルのデーターが2か月前の...
-
UNIQUE関数が使えないバージョ...
-
エクセル共有したが、アクセス...
-
Excelでセルの値が同じか...
-
(マクロ)データをAブックからB...
-
Excel フィルターを掛けた状態...
-
エクセル②
-
エクセルを使っていて2024/5/15...
-
Googleスプレッドシートでファ...
-
エクセルで不等号記号(≠)が上に...
-
Win10でExcel VBA GetPhonetic...
-
Excelで縦軸の書式を0:00形式の...
-
エクセル:一覧表に存在する文...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
ご回答ありがとうございます。
教えていただいた通りに関数を入力し、Ctrl+Shift+Enterで確定して配列数式にしたのですが、
F16には「0」と表示されました。
本来は「91.7」と表示されるはずなのですが、
何度試してもうまくいかず、悩んでいます。
もしよろしければ、補足説明をお願いできましたら大変助かります。