- ベストアンサー
Excelの非セル位置依存関数式の動作検証をお願いします
- MACOS10.12.3 Microsoft Excel for Mac 2011 Version14.7.2の環境で、Excelの非セル位置依存関数式の動作を検証したいです。異なるセルに式を入力すると、異なる値が戻ってきて困っています。この動作は環境依存の問題なのか、普遍的な動作なのか調べたいです。
- 具体的には、=SMALL(INDIRECT("A"&((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*ROW($A$2:$A$9)+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*20)),1)という式をF4に入力し、F5にフィルし、F6のセルに結果が違うという問題が発生しています。同じ式なのでスコープも変わらないはずですが、結果が異なるのはなぜでしょうか？他のバージョンでも検証できればと思っています。
- ご協力いただけると幸いです。Excelの非セル位置依存関数式の動作を詳しく検証し、バージョンによる違いがあるのか、環境依存の問題なのかを確認したいです。よろしくお願いします。
- Nouble
- お礼率91% (1698/1856)
- Excel（エクセル）
- 回答数4
- ありがとう数2
- みんなの回答 （4）
- 専門家の回答
質問者が選んだベストアンサー
>Office365上(ワンドライブ上)での、挙動 環境を用意していないので協力できません。 >では、此等も追加で、お願いしたいの、ですが構わないでしょうか？ =OFFSET(A1,{2;20;4;20;20;20;20;9}-1,0,1,1)は行番号や列番号を配列で指定しても先頭の1つの値しか参照されません。 =INDEX(A1:A20,{2;20;4;20;20;20;20;9},1)も行番号を配列値で与えても先頭の1つの値のみ参照され、後続の値は無視されます。 ご自身で確認された挙動で不都合でもExcelに予め組み込まれた関数は使い方に制約があると思います。 ユーザー定義の関数を組み込むことで解決できるかも知れませんが私は不得手なのでアドバイスできません。（ご自身で検索してください）
その他の回答 (3)
- bunjii
- ベストアンサー率43% (3589/8249)
>私の趣旨ではSMALLが、外ですので=INDIRECT("A"&{2;20;4;20;20;20;20;9})ですかね その数式ではINDIRECT("A2")だけしか返らないようです。 つまり、INDIRECT関数では配列値を返せないと言うことになります。 A2～A9の中からB2～B9とF1が一致して、且つ、C2～C9がF2と一致する行を対象に1番小さい値（2017/4/10）を抽出したいと言うことであれば配列演算を変更してINDIRECT関数を使わない数式にすることをお薦めします。 =SMALL((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*$A$2:$A$9+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*$A$20,1) この数式は配列数式なので確定時にCtrl+Shift+Enterの打鍵が必要になります。 Enterキーのみで確定したいときはINDEX関数の仲介が必要です。 =SMALL(INDEX((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*$A$2:$A$9+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*$A$20,0),1) MacOS X 10.6.8上のExcel 2004で検証しましたがWindows 10上のExcel 2013と同様な結果になりました。 従って、Excelのバグではないと思われます。
補足
>Excel 2004で検証しましたがWindows 10上のExcel 2013と >同様な結果になりました。 ご協力、有り難うございます。 後、出来れば で、良いのですが Office365上(ワンドライブ上)での、挙動 に、ついても お教え、頂ければ 幸いです。 >その数式ではINDIRECT("A2")だけしか返らないようです。 そうなのですね 辛いですね、其れは では、此等も 追加で、お願いしたい の、ですが 構わないでしょうか？ =OFFSET(A1,{2;20;4;20;20;20;20;9}-1,0,1,1) =INDEX(A1:A20,{2;20;4;20;20;20;20;9},1) お手数ですが、 宜しくお願いします。
- bunjii
- ベストアンサー率43% (3589/8249)
>ところで、其方の、環境での挙動は如何でしたで、しょうか？ MacOS 10.11.6はありますがExcel 2010が無いので同じ環境になりません。 当方の環境はWindows 10のExcel 2013です。 正常に目的の値を抽出できました。 但し、SMALL関数の使い方に誤りがあるため順位を2以上にするとエラーになります。 次のようにINDIRECT関数をSMALL関数の外側にすれば順位を変化させてもエラーになりません。 =INDIRECT("A"&SMALL(((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*ROW($A$2:$A$9)+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*20),3)) >映像の、通り挙動差が、出ておりバクの、可能性を感じた、次第です あなたが組み立てた数式を要素別に動作確認してみることをお薦めします。 目的はINDIRECT関数でB列の値がF1セルと一致して、且つ、C列の値がF2と一致する行番号を使って、A列の値から抽出することですよね？ 表を目視で確認し、手動でINDIRECT関数を簡単に書くと次のようになるでしょう。 =INDITECT("A"&2) 此処で2をどのような数式で導くかを考えれば次のような方法があります。（あなたの考案による論理） SMALL(((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*ROW($A$2:$A$9)+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*20),1) → 2 この数式は論理演算が４ヶ所と行番号１ヶ所で配列値が成り立っています。 SMALL関数の第1引数は配列値で第2引数は固定値の1です。 従って、SMALL関数へ配列値を引き渡すには数式の確定時にCtrl+Shift+Enterの打鍵が必要になります。（数式バーで数式を入力した状態でCtrl+Shift+Enterの打鍵で確定する） Enterキーのみの打鍵で確定したいときはINDEX関数を仲介させます。 =SMALL(INDEX(((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*ROW($A$2:$A$9)+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*20),0),1) 数式バーの左に有る「fx」ボタンをクリックするとSMALL関数の引数の編集と引数の計算結果の一部が表示されますので第1引数の配列の値が数式編集窓の右側で確認できます。（画像を添付します） 当方にはMacOS X 10.6.8上のExcel 2004があります。 時間が許せば検証に加えてみますので結果をお待ちください。
補足
>正常に目的の値を抽出できました。 有り難うございます 正しく 出る、環境も あるのですね 此の、際は SMALLが、外側だった の、ですよね？ >但し、SMALL関数の使い方に誤りがあるため 此所では 挙げるべき事 では、無い かも、知れませんが INDIRECTを、初めとして 参照関数が 単項で、無いと 扱えなく、なっている と、言う事 で、しょうか？ と、なると 条件に 一致する、セルだけ 多数、抽出する 此が 難しくないですか？ 此は 配列数式の、崩壊を 意味する と、思え 憂慮する所 と、なるのですが… 困ってしまいます ＞手動でINDIRECT関数を簡単に書くと次のようになるでしょう。 ＞=INDITECT("A"&2) 私の趣旨では SMALLが、外ですので =INDIRECT("A"&{2;20;4;20;20;20;20;9}) ですかね と、何はともあれ ご協力に 感謝、致します。
- bunjii
- ベストアンサー率43% (3589/8249)
論理的に誤りはありませんが、数式の確定時にCtrl+Shift+Enterの打鍵が必要なのではないでしょうか？ つまり、SMALL関数の第1引数は配列値なのでExcelの仕様で配列値を別の関数へ引き渡すときはCtrl+Shift+Enterの打鍵で数式を確定することになっています。 但し、INDEX関数で行番号および列番号を0または無指定にしたときはEnterキーのみで確定できる場合があります。 尚、SMALL関数とINDIRECT関数の位置関係が不適切のようです。 また、SMALL関数で順位が固定の1のときはMIN関数に置き換えできます。 =INDIRECT("A"&MIN(INDEX((($B$2:$B$9=$F$1)*($C$2:$C$9=$F$2))*ROW($A$2:$A$9)+SIGN(($B$2:$B$9<>$F$1)+($C$2:$C$9<>$F$2))*20,0)))
お礼
有難うございます bunjiiさん お世話になっております、 ところで、 其方の、環境での 挙動は 如何でした で、しょうか？ 一般的には セル位置関係に、よる 挙動差が、出ないもの ばかりを、選んだ 其の、つもり なのですが 映像の、通り 挙動差が、出ており バクの、可能性を 感じた、次第です 因みに、INDEXは 試したが、駄目 当方、ディスクトップの 環境依存の、挙動か？ と、 ワンドライブ上での、挙動テストも しましたが 此方も 挙動は、同じ と、出ました ので 当方環境、固有 では、無い との、可能性を 高く、感じた 次第です 故に、 ご協力を お願い、致しました 〉SMALL関数とINDIRECT関数の位置関係が不適切のようです 成るほど 複数の、値を INDIRECTに、与えない ですね ただ、此は 式作成上の、自由度を かなり削いで、しまいます よね？ 〉SMALL関数で順位が固定の1のときはMIN関数に置き換えできます 最初に MINを、使い 次に、此方をも 試しました 此方の方が 堅牢、且つ汎用 と、思いましたので 此方で と させて、頂きました
お礼
有り難うございます。 〉環境を用意していないので協力できません。 説明不足で、 誤解を 招き易い、表現を 現した、ばかりに 不遜に、なり ご気分を、害して しまい 申し訳、ありません。 ブラウザから 質問文中の、URLより ワンドライブ環境に 入って頂いた、状態も 映像と、相違無い？ との、質問 なのですが 不適切な、説明 済みませんでした 後、 追加で、お願いした 二つの、式に ご対応頂き 有り難うございます。 非連続セルへの、参照が 出来無い、事が お蔭様で 認知、できました 有り難うございます。 〉ユーザー定義の関数を組み込むことで 確かに、確認しないと… ですよね ご協力に、感謝致します。