正解Because the data is sorted in descending order by the sales figures in column B before the running total is taken, column I (cumulative sales composition ratio) grows larger the further down the rows you go; it is a monotonically increasing value. So whether a product falls «within the top 70%» can be judged by whether that row's cumulative value I2 is 0.7 (70%) or less. In the same way, «within the top 90%» is judged by whether I2 is 0.9 or less. A nested IF function is evaluated starting from the outer condition, so the condition with the smaller boundary (≦0.7) must be placed on the outside, the next strictest condition (≦0.9) on the inside, and whatever matches neither becomes C. The choice that has this form is IF(I2≦0.7,'A',IF(I2≦0.9,'B','C')), which is «ア»; copying it to J3 through J11 correctly derives each row's rank from its cumulative value.
×Incorrectイ
When the outer condition I2≦0.9 is true it returns «C». A cumulative value of 0.9 or less should be A or B, so the judgment is reversed. Furthermore, the inner IF(I2≦0.7,'A','B') is evaluated only when I2 is greater than 0.9, so not a single product ends up as A.
×Incorrectウ
The direction of comparison is reversed: it assigns «A» when I2≧0.7. A cumulative value of 0.7 or more means the product has gone beyond the top 70%, so it should be B or C, not A. The inner I2≧0.9 is reversed in the same way, so the higher-ranking products get pushed toward C.
×Incorrectエ
Placing I2≧0.9 on the outside and returning «C» does happen to coincide with the range of C, but the inner part returns «A» when I2≧0.7, which is backwards. Products whose cumulative value is 0.7 or more but under 0.9 should be B yet are judged A, while products with a cumulative value under 0.7, which should be A, are judged B.
ここがポイント
After sorting by sales in descending order, the cumulative composition ratio increases monotonically, so rank judgment in ABC analysis is done with an upper-bound comparison: is the cumulative value at or below the threshold?
A nested IF function is evaluated starting from the outer condition, so conditions must be arranged from the smallest (strictest) boundary value outward; otherwise the inner condition becomes unreachable or the judgments get swapped.