Excelindex関数

質問

よろししくお願いします。sしheet1に
店名 月 品名 単価
A-1 1月 柿 100
A-1 1月 りんご 200
A-1 1月 みかん 300
A-1 2月 りんご 400
A-1 3月 バナナ 500
A-2 1月 みかん 600
A-2 2月 りんご 700
A-2 3月 バナナ 800
A-3 1月 りんご 900
A-3 2月 りんご 1000
A-3 3月 りんご 1100
というsheetがあり、sheet2には
りんご 1月 2月 3
A-1
A-2
A-3
というsheetがあります。
そこで、単を引っ張ってきたいのですが、色々と勉強して
index関数とmatch関数を使いたいのです
が、サイト見る限り式が書いてありますが、関数を入力するボックスの入力が少しわかりにくいので、お手数ですが教えて頂く時、これから私が試したこと書きますので訂正お願いします。
(1)sheet2のB2にIFA2=¨¨、¨¨
(2)IF関数の偽の場合の所にIFError関数
の値にinx関数を入力して、調べたい場所のD行を絶対参照で
(3)indexの行番号にmatch関数を入力して
(4)検査値をsheet2の$A2＆B$1＆$A1$と入力して
(5)検査範囲はsheet1の$A:$A＆$B:$B＆$C:$Cと入力して
(6)照合の所は0と入力して
(7)IFErrorのエラーの場合の値も0にしてやってオートフィルしたのですが全て0になります。
index関数とIF関数とIFError関数を開くと計算結果200と書いてあるのに、表示されません。今困ってるので詳しい方お願いします

回答No.3

bunjii · ベストアンサー

>index関数とIF関数とIFError関数を開くと計算結果200と書いてあるのに、表示されません。
「(5)検査範囲はsheet1の$A:$A＆$B:$B＆$C:$Cと入力して」この処理が配列の戻り値になるため数式全体を確定するときにCtrlとShiftキーを押しながらEnterキーを打鍵する必要があります。

=IFERROR(INDEX(Sheet1!$A:$D,MATCH($A2&B$1&$A$1,Sheet1!$A:$A&Sheet1!$B:$B&Sheet1!$C:$C,0),4),"")
　　　　↓
=IFERROR(INDEX(Sheet1!$A:$D,MATCH($A2&B$1&$A$1,INDEX(Sheet1!$A:$A&Sheet1!$B:$B&Sheet1!$C:$C,0),0),4),"")
上記のように数式を変更すればEnterキーのみで確定してもエラーになりません。
内側のINDEX関数は＆演算子で文字列を連結するときに配列値を他の関数に渡すときに行番号または列番号を0または無指定にします。
今回は＆演算子での処理ですが、四則演算、論理演算、組込み関数等で配列値を前処理したいときにINDEX関数内で実行すると考え易くなります。

お礼

ご回答有り難うございます。遅くなってすいません。今日拝見して、index関数とmatch関数での条件1つでのやり方しかやったことなかったので中ほどのindex使い方知りませんでした。有り難うございます。勉強になりました。

回答No.5

178-tall

列) A　　B　C　　　D　　　E
　　↓　↓　↓　　↓　　　↓
>店名,,月,,品名,,単価　　
A-1,1月,柿,100　　　　　A-11月柿
A-1,1月,りんご,200　　　…
A-1,1月,みかん,300　　　…
A-1,2月,りんご,400
A-1,3月,バナナ,500
A-2,1月,みかん,600
A-2,2月,りんご,700
A-2,3月,バナナ,800
A-3,1月,りんご,900
A-3,2月,りんご,1000
A-3,3月,りんご,1100

この乱雑な 3 次元配列を、シート関数のみで「行儀良い」2 次元配列にまとめるのに、もとの 3 次元配列のデータだけでやろうとすると、何らかの「超絶スキル」を編みだしたくなる。
そんな無駄な労働はお嫌いなら、たとえば上表の E 列 / 1 行に　=A1&B1&C1　と書き込んで、A-11月柿　など 1 次元配列を表示しておけば、あとが楽になります。

お目当ての「行儀良い」2 次元配列。

列)　G　 H　 I　　J　　K　　L　　M
　　↓　 ↓　↓　 ↓　　　↓
>りんご, 1月,2月,3月
A-1
A-2
A-3

この 2 次元配列にて、K 列 / 2 行に　=$G2&H$1&$G$1　と書き込み、これを　K2:M4　に貼り付ければ、参照用の 2 次元配列が表示される。
このあと、H 列 / 2 行に　=INDEX($D$1:$D$11,MATCH(K2,$E$1:$E$11,0))　と書き込み、これを　H2:J4　に貼り付ければ、単価の 2 次元配列が表示される。(該当なしセルは　#N/A　表示)

このテーブルを sheet 2 に移動したけりゃ、G1:M4　を切り取り、sheet 2 のお好みの場所へ貼り付け。

お礼

ご回答有り難うございます。色々考えて下さり有り難うございます。ご回答して下さった3条件を減らすやり方が思いつきませんでした。勉強になりました。有り難うございます。

回答No.4

kagakusuki

>結果的にできませんでした

　失礼致しました。
　COUNTIFS関数とSUMIFS関数のSheet1!$A:$Aに対する条件として設定している参照先のセルを間違えて$A1セルとしてしまっておりましたが、正しくは$A2セルでした。

=IF(OR($A$1="",$A2="",B$1=""),"",IF(COUNTIFS(Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1,Sheet1!$C:$C,$A$1),SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,$A2,Sheet1!$B:$B,B$1,Sheet1!$C:$C,$A$1),"無し"))

お礼

ご回答有り難うございます。返事遅くなりすいません。仕事で急いでたので、このやり方を確認するの遅くなりました。やり方は1つではないのですね。自分も思いつくように勉強します。有り難うございました。

回答No.2

msMike

添付図の左を Sheet1、右を Sheet2 とします。
Sheet2!B2: =SUMPRODUCT((Sheet1!$A$2:$A$12=$A2)*(Sheet1!$B$2:$B$12=B$1)*(Sheet1!$C$2:$C$12=$A$1)*(Sheet1!$D$2:$D$12))

お礼

ご回答有り難うございます。返事遅くなりすいません。こんなに色々なやり方があるとは思いませんでした。勉強になりました。有り難うございました。

回答No.1

kagakusuki

Sheet2のB2セルには、index関数とmatch関数ではなく、次の様な関数を入力されると良いと思います。

=IF(OR($A$1="",$A2="",B$1=""),"",IF(COUNTIFS(Sheet1!$A:$A,$A1,Sheet1!$B:$B,B$1,Sheet1!$C:$C,$A$1),SUMIFS(Sheet1!$D:$D,Sheet1!$A:$A,$A1,Sheet1!$B:$B,B$1,Sheet1!$C:$C,$A$1),"無し"))

補足

ご回答有り難うございます。すいません。投稿した関数も試行錯誤の上解いてやったのですが、結果的にできませんでしたが、関数入力するボックスが慣れてません。のでお手数ですが少し入力解説いれてもらえないでしょうか？自分の知識ではカウントイフスは複数条件の数を、数える関数でサムイフは条件にあった合計ぐらいのちしきしかありません。ボックス入力手順よろしくお願いします