[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『INDEX関数、MATCH関数、SUMIF関数の組み合わせについて』（えるふ）
元データ（Sheet1）
a b c 2018/9/1 1 1 2018/9/2 1 2018/9/3 1 2018/9/4 1 2018/9/1 1 1 2018/9/2 1 5 1 2018/9/3 1 2018/9/4 1 1
日別シート (Sheet2)
a b c 2018/9/1 2 2 2018/9/2 1 6 1 2018/9/3 1 1 2018/9/4 1 1 1
日別シートのB2には以下の関数を入れてますが、
数値が正しく表示されません。 。。
=SUMIF(元データ!$A$1:$A$10,$A2,INDEX(元データ!$B$2:$D$10,,MATCH(B$1,元データ!$B$1:$D$1,0)))
アドバイスをいただけると助かります。
よろしくお願いいたします。
＜ 使用 Excel：Excel2007、使用 OS：Windows7 ＞
>元データ!$A$1:$A$10 2行目からではないのか？ =SUMIF(元データ!$A$2:$A$10,$A2,INDEX(元データ!$B$2:$D$10,,MATCH(B$1,元データ!$B$1:$D$1,0))) こんな式でも。 =SUMPRODUCT((元データ!$A$2:$A$9=$A2)*(元データ!$B$1:$D$1=B$1)*元データ!$B$2:$D$9) （ねむねむ） 2018/09/27(木) 10:59
=SUMIF(元データ!$A$2:$A$9,$A2,元データ!B$2:B$9) （SS） 2018/09/27(木) 11:00
ただ、追加でご質問となり申し訳ないのですが、
元データの選択範囲(列、行共に)が可変的な時はどのようにしたらよいのでしょうか・・・？
（えるふ） 2018/09/27(木) 15:23
範囲の最大が決まっていればあらかじめその最大範囲を指定しておけばいい。 例えば最大で500行、Z列までだとすれば =SUMPRODUCT((元データ!$A$2:$A$500=$A2)*(元データ!$B$1:$Z$1=B$1)*元データ!$B$2:$Z$500) で。 （ねむねむ） 2018/09/27(木) 15:40
元データには以下データ以外に空白行を挟んで別の表が入っております。
a b c 2018/9/1 1 1 2018/9/2 1 2018/9/3 1 2018/9/4 1 2018/9/1 1 1 2018/9/2 1 5 1 2018/9/3 1 2018/9/4 1 1 （えるふ） 2018/09/27(木) 15:47
指定した元データの範囲内に、#DIV/0!エラーのセルがないだろうか？ もしあった場合、そのエラーをなくしてみてくれ。 （ねむねむ） 2018/09/27(木) 15:51
もしそれが不可能な時は式を
=SUM((元データ!$A$2:$A$500=$A2)*(元データ!$B$1:$Z$1=B$1)*IFERROR(元データ!$B$2:$Z$500,0))
として、ShiftキーとCtrlキーを押しながらEnterキーで式を確定（確定後、式が{}で囲まれればOK）それから
フィルコピーしてみてくれ。
（ねむねむ） 2018/09/27(木) 15:56
早速のご回答ありがとうございます！
元データに#DIV/0!エラーはなかったので、SUM関数で試してみました！
確定後、式が{}で囲まれたのですが、「#VALUE!」のエラーとなってしまいます・・・・
（えるふ） 2018/09/27(木) 18:14
その式で #VALUE! エラーになるということは、 元データ!$B$2:$Z$500 に文字列があるのでは？（数式の空白文字列「""」も含めて）
ところで ↓ の「SUMIF」はどうなったんですか？ ＞=SUMIF(元データ!$A$2:$A$10,$A2,INDEX(元データ!$B$2:$D$10,,MATCH(B$1,元データ!$B$1:$D$1,0)))
SUMIFなら文字列があってもエラーにはなりません。
しかも、1行目を検索して、該当する列しか計算しませんから、セル総当たりに比べて効率がいいです。 「圧倒的に」と言ってもいい。
それと、1行目にある項目の並び順は2つのシートで違うんですか？ 例示では同じですよね（a、b、c）
同じなのか違うのか、まずはそこをはっきりしましょう。
それとも元シートには同じ項目名が複数の列にあって、 Sheet2は項目名の重複はないとか？
それも説明してください。
■まとめると 1）SUMIFを試した結果はどうなったのか？（具体的に） 2）元シートのB列以降には数式が入っているのか？（数式で空白にしているのか？） 3）2つのシートで、項目名の並び順は同じなのか？ 4）元シートは同じ項目名が重複しているのか？
以上です （笑） 2018/09/27(木) 22:49 追記9/28(金) 1:23
ついでに確認 5）元シートの数値はすべて正の数なのか？ （マイナスはないのか？）
以上です （笑） 2018/09/28(金) 01:40
試しに =SUM((元データ!$A$2:$A$500=$A2)*(元データ!$B$1:$Z$1=B$1)*IFERROR(元データ!$B$2:$Z$500*1,0)) ではどうなるかやってみてくれ。 ただ、元の式で、#DIV/0!エラー（割り算で割る数が0の場合のエラー）は参照範囲内にそのエラーがないと発生しないはずなので その点が謎だが。 （ねむねむ） 2018/09/28(金) 09:05
1）SUMIFを試した結果はどうなったのか？（具体的に）
⇒ 0で返ってきます。。
2）元シートのB列以降には数式が入っているのか？（数式で空白にしているのか？）
⇒ 数値、数式、文字列が入っております。
3）2つのシートで、項目名の並び順は同じなのか？
⇒並び順は同じではありません！！
項目名の一致で抽出できればと考えております。
4）元シートは同じ項目名が重複しているのか？
1つの行に中に項目名の重複はありません。
但し、Sheet内には抽出対象のデータの表と他にも表があるため、項目名は重複してます。
例：元データのSheet内のデータ
a b c
2018/9/1 1 1
2018/9/2 1
2018/9/3 1
2018/9/4 1
2018/9/1 1 1
2018/9/2 1 5 1
2018/9/3 1
2018/9/4 1 1 ←ここのデータが抽出したいものでデータ量(行・列)が可変的
a b c
あ 1
い 1 1
う
5）元シートの数値はすべて正の数なのか？ （マイナスはないのか？）
全て正の数です。
（えるふ） 2018/09/28(金) 13:55
コメントありがとうござます！！！
＞ =SUM((元データ!$A$2:$A$500=$A2)*(元データ!$B$1:$Z$1=B$1)*IFERROR(元データ!$B$2:$Z$500*1,0))
＞ ではどうなるかやってみてくれ。
こちら試してみたところ「#VALUE!」のエラーとなりました。。。
※ちなみに選択範囲は広げておりません。
（えるふ） 2018/09/28(金) 13:59
実際に入力している式をコピーして貼り付けてもらえないだろうか？ （ねむねむ） 2018/09/28(金) 14:53
=SUM((元データ!$B$9:$B$64=$A22)*(元データ!$C$8:$BA$8=B$21)*IFERROR(元データ!$C$9:$BA$64*1,0))
↑こちらです。
（えるふ） 2018/09/28(金) 15:12
一つ確認。 式を入力する際に >ShiftキーとCtrlキーを押しながらEnterキーで式を確定 は行っているだろうか？ （ねむねむ） 2018/09/28(金) 15:25
1つの行に項目名の重複がないんだったら、SUMIF でできますよね。
↓ はどうなるんですか？
=SUMIF(元データ!$B$9:$B$64,$A22,INDEX(元データ!$C$9:$BA$64,0,MATCH(B$21,元データ!$C$8:$BA$8,0)))
＞0で返ってきます。。 「0」が返るとというのは、すべて「0」になるのか、 一部だけ「0」のはずがないところが「0」になるのか、どっちですか？
数値ではなく「文字列の数字」になっていることはありませんか？ 元表の「数値」は手入力と数式の結果と2種類あるんですかね？ 元表の数式とはどんな式ですか？
＞但し、Sheet内には抽出対象のデータの表と他にも表があるため、項目名は重複してます。
これは上下に別の表が並んでいるということですよね？
例示の左端のデータは、上の表が日付、下の表が文字列、と全く別物ですが、 実際の表もそうなんですか（重複しないんですか）？
それと並んでいるのは2つの表だけ？
以上です （笑） 2018/09/28(金) 15:39
返事が遅くなってすいません！！
>ShiftキーとCtrlキーを押しながらEnterキーで式を確定
こちらが漏れており、問題なくできました！
本当に本当ににありがとうございます！！！！
また、追加で申し訳ございませんが、下記の場合どうなりますでしょうか。
■元データ
a b c
001_abc_link 1 2 0
001_abc_link 1 0 2
002_abc_ストーリー 0 1 1
002_abc_video 1 0 1
002_abc_video_0702 1 1 0
003_abc_linkvideo_0702 0 5 1
001_abc_link 0 0 1
002_abc_video_ストーリーズ_0801 1 0 1
006_abc_linkvideo_180706_0831 1 1 2
■別シート
a b c
abc_link
abc_ストーリー
abc_video
abc_linkvideo
上記のように元データのA列に別シートAの文字列が含まれている場合の数値をもってくるようにしたいです。
ワイルドカードでやってみましたがうまくいきません。。。
実際の式はこちらです。
｛=SUM(('?ASet Ad元データ'!$B$3:$B$30="*"&$A6&"*")*('?ASet Ad元データ'!$C$2:$BA$2=B$4)*IFERROR('?ASet Ad元データ'!$B$4:$BA$30*1,0))｝
#N/A で返ってきてしまいます。
（えるふ） 2018/10/02(火) 17:13
=SUM(COUNTIF(INDIRECT("'?ASet Ad元データ'!$B"&ROW($3:$30)),"*"&$A6&"*")*('?ASet Ad元データ'!$C$2:$BA$2=B$4)*IFERROR('?ASet Ad元データ'!$C$3:$BA$30*1,0))
ではどうだろうか？
Shift+Ctrl+Enterは忘れずに。
（ねむねむ） 2018/10/03(水) 15:51
なお、問題点としては 1. ワイルドカードが使えるのは一部の関数での条件でのみで、単純なA=Bというような比較式では使えない。 2.えるふさんの式で見出しがB3:B30、C2:BA2であれば対応するデータ部分はB4:BA30ではなくC3:BA30となる。 （ねむねむ） 2018/10/03(水) 15:52
修正し忘れていた。 ?Aは◯付きの2で。 （ねむねむ） 2018/10/03(水) 15:53
連続ですまない。 一応"-"で区切られた単語で区別（abc_linkvideoはabc_linkでは数えない)するということなのだろうか？ 上記式ではabc_linkでabc_linkvideoも数えている。 （ねむねむ） 2018/10/03(水) 16:04
abc_linkでabc_linkvideoを数えないもの。
=SUM((COUNTIF(INDIRECT("'2Set Ad元データ'!$B"&ROW($3:$30)),"*_"&$A6&"_*")+COUNTIF(INDIRECT("'2Set Ad元データ'!$B"&ROW($3:$30)),$A6&"_*")+COUNTIF(INDIRECT("'2Set Ad元データ'!$B"&ROW($3:$30)),"*_"&$A6))*('2Set Ad元データ'!$C$2:$BA$2=B$4)*IFERROR('2Set Ad元データ'!$C$3:$BA$30*1,0))
で。
Shift+Ctrl+Enterを忘れずに。
（ねむねむ） 2018/10/03(水) 16:55
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.