こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Excelの非セル位置依存関数式の動作検証をお願…

お世話になります。

MACOS10.12.3
Microsoft Excel for Mac 2011
Version14.7.2
です

同式を
異セルに、入れたところ
違う値が、戻され
困って、しまった
の、ですが

此が、
当方環境依存の、動作か
普遍的、動作か
調べたく、思いました。

其処で、出来れば
以下に、ついて
お付き合い、頂ければ
幸いです。

宜しく
お願い、致します。


では、此所より

=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の、セル内に
文字列と、して
貼り付けた
の、ですが

結果が、違っています。

なのですが
ご覧、頂ける
通り
そもそも
同じ式、なので

当然ながら
スコープも、変わりません

しかし、
何度も、申します通り
結果が、変わります。

此は
私の、環境の
固有の、問題
なのでしょうか?

出来れば
より多くの、Version
での、検証が
叶えば…
と、考えております。

ご協力の、程を
宜しく
お願い、致します。
通報する

投稿日時 - 2017-04-19 03:19:50

QNo.9319211

困ってます

質問者が選んだベストアンサー

>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に予め組み込まれた関数は使い方に制約があると思います。
ユーザー定義の関数を組み込むことで解決できるかも知れませんが私は不得手なのでアドバイスできません。(ご自身で検索してください)

投稿日時 - 2017-04-19 21:42:40

お礼

有り難うございます。

〉環境を用意していないので協力できません。

説明不足で、
誤解を
招き易い、表現を
現した、ばかりに

不遜に、なり
ご気分を、害して
しまい
申し訳、ありません。

ブラウザから
質問文中の、URLより
ワンドライブ環境に
入って頂いた、状態も

映像と、相違無い?
との、質問
なのですが

不適切な、説明
済みませんでした

後、
追加で、お願いした
二つの、式に
ご対応頂き
有り難うございます。

非連続セルへの、参照が
出来無い、事が
お蔭様で
認知、できました
有り難うございます。

〉ユーザー定義の関数を組み込むことで

確かに、確認しないと…
ですよね

ご協力に、感謝致します。

投稿日時 - 2017-04-19 22:21:56

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

>私の趣旨では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のバグではないと思われます。

投稿日時 - 2017-04-19 18:42:34

補足

>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)

お手数ですが、
宜しくお願いします。

投稿日時 - 2017-04-19 19:18:19

ANo.2

>ところで、其方の、環境での挙動は如何でしたで、しょうか?
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
この数式は論理演算が4ヶ所と行番号1ヶ所で配列値が成り立っています。
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があります。
時間が許せば検証に加えてみますので結果をお待ちください。

投稿日時 - 2017-04-19 13:54:15

補足

>正常に目的の値を抽出できました。

有り難うございます
正しく
出る、環境も
あるのですね

此の、際は
SMALLが、外側だった
の、ですよね?


>但し、SMALL関数の使い方に誤りがあるため

此所では
挙げるべき事
では、無い
かも、知れませんが

INDIRECTを、初めとして
参照関数が
単項で、無いと
扱えなく、なっている

と、言う事
で、しょうか?

と、なると
条件に
一致する、セルだけ
多数、抽出する

此が
難しくないですか?

此は
配列数式の、崩壊を
意味する
と、思え

憂慮する所
と、なるのですが…

困ってしまいます


>手動でINDIRECT関数を簡単に書くと次のようになるでしょう。
>=INDITECT("A"&2)

私の趣旨では
SMALLが、外ですので
=INDIRECT("A"&{2;20;4;20;20;20;20;9})
ですかね


と、何はともあれ
ご協力に
感謝、致します。

投稿日時 - 2017-04-19 16:18:58

ANo.1

論理的に誤りはありませんが、数式の確定時に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)))

投稿日時 - 2017-04-19 08:11:00

お礼

有難うございます

bunjiiさん
お世話になっております、

ところで、
其方の、環境での
挙動は
如何でした
で、しょうか?

一般的には
セル位置関係に、よる
挙動差が、出ないもの
ばかりを、選んだ
其の、つもり
なのですが

映像の、通り
挙動差が、出ており
バクの、可能性を
感じた、次第です

因みに、INDEXは
試したが、駄目

当方、ディスクトップの
環境依存の、挙動か?
と、
ワンドライブ上での、挙動テストも
しましたが
此方も
挙動は、同じ
と、出ました
ので

当方環境、固有
では、無い

との、可能性を
高く、感じた
次第です

故に、
ご協力を
お願い、致しました


〉SMALL関数とINDIRECT関数の位置関係が不適切のようです

成るほど
複数の、値を
INDIRECTに、与えない
ですね

ただ、此は
式作成上の、自由度を
かなり削いで、しまいます
よね?


〉SMALL関数で順位が固定の1のときはMIN関数に置き換えできます

最初に
MINを、使い
次に、此方をも
試しました

此方の方が
堅牢、且つ汎用
と、思いましたので
此方で

させて、頂きました

投稿日時 - 2017-04-19 10:56:55