IFERROR、SMALL関数について

こんにちは。画像をご参照ください。
まず、単価マスタのA列(CHK)で、1が立っている商品番号(K列)を対象に、単価(AE列)の有無※によって、単価有シート／単価無シートのG14セル以降へ層別表示できるような、関数をG14以降へ埋め込みたいと思っています。

※1円以上→単価有、0円or空白→単価無

単価無シートのG14セルへは、=IFERROR(SMALL(IF((単価マスタ!AE2:AE1000=0)*単価マスタ!A2:A1000,単価マスタ!K2:K1000),ROW(A1),"")

単価有シートのG14セルへは、=IFERROR(SMALL(IF((単価マスタ!AE2:AE1000*単価マスタ!A2:A1000,単価マスタ!K2:K1000),ROW(A1),"")

とそれぞれ入力してみたのですが、期待した結果が出ず困っています。
どのように関数を直せばよいか、アドバイスをください。お願いします。

No.1 ベストアンサー 回答者： 銀鱗 回答日時： 2022/08/23 02:09

期待する結果が返らないのは当然だと思う。

閉じ括弧の数が足りないよね。

それ以前に、どうみてもIF関数までしか完結していない。
SMALL関数やIFERROR関数が機能していないんだ。

他のやり方もありますが、質問者さんが調べたと思われる方法を採用するという形で説明してみましょう。
以下、長いよ。マジで覚悟してください。


・・・ってことで自分も覚悟を決めたので本題・・・

とりあえず作りかけの数式は忘れて、条件を整理してみましょう。

1つ目の式
　A列が「1」、且つ、AE列が空欄またはゼロ。このとき、K列の値を返す。
　それ以外は空欄を返す。
2つ目の式
　A列が「1」、且つ、AE列が空欄またはゼロでない、このとき、K列の値を返す。
　それ以外は空欄を返す。
ですね。

条件式はそれぞれ
　(A2=1)*(AE2=0)
　(A2=1)*(AE2<>0)
こんな簡単な数式になります。
等号、不等号を使って条件を明示的に示すことを薦めます。
後で見たときに理解しやすい。
理解しやすいという事は間違いにくいという事です。
（こんな時に一般的に使うAND関数を採用しないのは後に「配列数式」にするためです。AND関数内には配列を設定できないのです）

これをIF関数に組み込んでみましょう。
　IF((A2=1)*(AE2=0),ROW(A1),"")
　IF((A2=1)*(AE2<>0),ROW(A1),"")
これで、目的の文字列がある(行番号-1)の値が返ってきます。

2行目から6行目までの範囲に値があると想定すると、
「なし」の数式は
　IF((A2=1)*(AE2=0),ROW(A1),"")
　IF((A3=1)*(AE3=0),ROW(A2),"")
　IF((A4=1)*(AE4=0),ROW(A3),"")
　IF((A5=1)*(AE5=0),ROW(A4),"")
　IF((A6=1)*(AE6=0),ROW(A5),"")
「あり」の数式は
　IF((A2=1)*(AE2<>0),ROW(A1),"")
　IF((A3=1)*(AE3<>0),ROW(A2),"")
　IF((A4=1)*(AE4<>0),ROW(A3),"")
　IF((A5=1)*(AE5<>0),ROW(A4),"")
　IF((A6=1)*(AE6<>0),ROW(A5),"")
試しにサンプルを自身で作って一つずつ動作を確認しながら読み進めると良いでしょう。
同じシート内で完結するように説明します。

次に ”めんどくさい”「配列数式」を考えます。
配列数式は、
　＝IF((A2:A6=1)*(AE2:AE6=0),ROW(A1:A5),"")
のように連続する範囲をまとめて一つの数式にしてしまう技です。
例えば、【G14セルからG18セルを選択して】この数式を選択した一番上のセルに入力し、
　Ctrl+Shift+Enter
という、3つのキーを同時に押すことで確定させると
　{=IF((A2:A6=1)*(AE2:AE6=0),ROW(A1:A5),"")}
と、「{」「}」が式の前後に自動で挿入され「配列数式」として機能を始めます。
単にEnterを押して数式を確定した場合、
　エラーになるか、
　数式内の配列の一番初めの値で演算した結果、
が返ってきます。
手入力で「{」「}」を挿入しても「配列数式」にはなりません。
エラーになるだけです。
数式の編集中に範囲選択を忘れてしまった場合は、数式入力後にEnterキーを押して普通の数式として確定してください。
その後、範囲を選択し直して【F2キーを押し】数式を編集状態にしてから
　Ctrl+Shift+Enter
で改めて「配列数式」として確定しましょう。

ちなみに、ここまでの数式を入力すると、条件を満たす(行番号-1)の数値と空白が表示されます。

そしてSMALL関数を考える。
この関数はリスト（本当は ”配列” なのですが紛らわしいので以下 ”リスト” と表現します）から小さい順から数えた値を拾い出します。
拾うのは数値です。
　{=IF((A2:A6=1)*(AE2:AE6=0),ROW(A1:A5),"")}
の数式は、条件が成立した時に(行番号-1)の値を返します。
条件が成立していないときは空白が返ります。
ですので、空白を飛ばして小さい順の(行番号-1)の値を取得するのです。
　=SMALL(IF((A2:A6=1)*(AE2:AE6=0),ROW(A1:A5),""),ROW(A1:A5))
（「配列数式」で確定させて下さい）
上から順番に空白を飛ばして、条件が成立している(行番号-1)の値がならび、その下にエラーが並びます。
このエラーは無視してください。あとで処理をします。

これで目的の値があるセルを順に指定することが可能になりました。
さて、どうやって値を指定するか。
質問文には含まれていない関数を使います。
　INDEX関数　
です。
この関数はリストの先頭から指定した番号の値を返します。
この場合のリストは。
　K2:K6
の範囲を指定します。
そして先ほどの「配列数式」で得られた値を使って目的の値を拾います。
　=INDEX(K2:K6,SMALL(IF((A2:A6=1)*(AE2:AE6=0),ROW(A1:A5),""),ROW(A1:A5)))
（「配列数式」で確定させて下さい）
これで目的はほぼ達成。

最後に、エラーになったら空白にするというIFERROR関数を作ってやれば完成。
（もちろん「配列数式」で確定させて下さい）

以上です。

ここまで理解できれば、あとは他のシートを参照する形式で範囲を含めて数式を書き直せばOK。
がんばってください。


・・・余談・・・

最後のIFERROR関数を使った数式を書かない所がポイント。使い方が分からない場合は調べましょう。
難しいことではありませんよね。
自身で考えるという事は必要ですからね。

2500字以上の回答をしたのは久しぶりです。
手元のExcelで動作確認をしながら回答の文章を作るのに約1時間。
なお、細かい説明や注意まで含めると回答の上限である4000字でも足りません。
必要なら追加で解説しますので、理解できない点があれば「お礼」または「補足」に疑問点を書いてください。
気づいてたタイミングで説明しますよ。

この回答へのお礼

大変ご丁寧な回答、および、ご指導をいただき、ありがとうございました。繰り返し読み込み、理解を深めてまいります。取り急ぎとなりますが、お礼までです。

お礼日時：2022/08/23 21:15

No.2 回答者： d-q-t-p 回答日時： 2022/08/23 06:37

> どのように関数を直せばよいか

何をやっているのか理解しないまま書いているように見えます。
SMALL関数で K列の値が返るわけないでしょう。括弧の数すら合ってないし。

そもそも Excelのバージョンはいくつなんでしょう。

2010以降だったら AGGREGATEを使えよばいいし 逆に 2021や for365だっ
たら問答無用で FILTER関数を使えばいいです。

=IFERROR(INDEX(単価マスタ!K:K,AGGREGATE(15,6,ROW($2:$6)/(単価マスタ!A$2:A$6=1)/(単価マスタ!AE$2:AE$6<>""),ROW(A1))),"")

あと数年もすればこんなやり方は誰も使わなくなりますから 無理して意味を
理解する必要もないんでしょうけど……