パターンがない表の値計算 MS excel2003で動作する、マクロを使用しない関数の組み合わせで次

パターンがない表の値計算
MS excel2003で動作する、マクロを使用しない関数の組み合わせで次のような表内の値の合計を計算したいのですが、簡単でスマートな方法が思いつかず、質問させて頂きたいです。
　　A　　　　　B　　　　　C　　　　　D　　　　　E　　　　　F　　　　　G　　　　　H
1　リンゴ　　　20　　　　　　　　　　　　　　　　ブドウ　　　20　　　　　　　　　　　　
2　リンゴ　　　50　　　　柿　　　　　10　　　　　リンゴ　　　-20　　　　　　　　　　　　　　　
3　みかん　　　20　　　　みかん　　　30　　　　　　　　　　　　　　　　　　　　　　　　　
4　リンゴ　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　みかん　　　-50　
5　リンゴ　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　柿　　　　　-20　　　
6　リンゴ　　　　　　　　さくらんぼ　　-20　　　　　　　　　　　　　　　　　　　　　　　　　

この表は奇数列に品名がランダムに入っていてその隣の列に数量が入力されています。
どの行のどの列にどんな品名入力されているかは予想できません。
この表から入力されている品名を抽出してきて、それぞれの品名に対して値が＋のものを集めての合計と、値がーのものを集めての合計とをそれぞれの出したいのです。
マクロを使っては出来たのですが、数式、関数だけを使って、そのコピペだけで他の同様の表にも対応出来る必要があります。
どうかいいアイディアをお願いします。

質問者からの補足コメント

• 表が正しくなかったです。
  　　A　　　　　B　　　　　C　　　　　D　　　　　E　　　　　F　　　　　G　　　　　H
  1　リンゴ　　　20　　　　　　　　　　　　　　　　ブドウ　　　20　　　　　　　　　　　　
  2　リンゴ　　　50　　　　柿　　　　　10　　　　　リンゴ　　　-20　　　　　　　　　　　　　　　
  3　みかん　　　20　　　　みかん　　　30　　　　　　　　　　　　　　　　　　　　　　　　　
  4　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　みかん　　　-50　
  5　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　柿　　　　　-20　　　
  6　　　　　　　　　　　　さくらんぼ　　-20　　　ブドウ　　　　-30

    補足日時：2016/12/27 16:48

No.4 回答者： ママチャリ 回答日時： 2016/12/30 10:41

とりあえず、集計範囲をA1:H100とした場合、次の式でいけると思います。

K1、K2セルに次の数式を入力し、下にオートフィルで引っ張ってください。
集計範囲を拡張することは可能ですが、安易に広くすると時間がかかって使い物になりません。

【K1セル】=SUMPRODUCT(($A$1:$G$100=$J1)*1,($B$1:$H$100<0)*1,$B$1:$H$100)
【K2セル】=SUMPRODUCT(($A$1:$G$100=$J1)*1,($B$1:$H$100>=0)*1,$B$1:$H$100)

この回答へのお礼

実例とパフォーマンスに及ぶことまで言及くださりありがとうございます。

お礼日時：2017/01/06 15:12

No.3 回答者： tom04 回答日時： 2016/12/27 18:20

こんばんは！

VBAでやれば簡単に出来そうですが、関数での方法をご希望だというコトですので一例です。
元データはSheet1の1行目からあり、Sheet2に表示するとします。

まずSheet1にデータ分の列数だけ作業用の表を作成します。
これは「品名」を重複なしに表示するための作業用の表になります。

↓の画像ではJ1セルに
=IF(AND(ISTEXT(A1),COUNTIF($A$1:A1,A1)=1),ROW()*1000+COLUMN(A1),"")
という数式を入れ列（元データと同じ列数）・行方向にコピーしています。

そしてSheet2のA2セルに
=IF(COUNT(Sheet1!J:Q)<ROW(A1),"",INDEX(Sheet1!A:H,INT(SMALL(Sheet1!J:Q,ROW(A1))/1000),MOD(SMALL(Sheet1!J:Q,ROW(A1)),1000)))

B2セルに
=IF(A2="","",SUMPRODUCT((Sheet1!A$1:G$1000=A2)*(Sheet1!B$1:H$1000>0),Sheet1!B$1:H$1000))

C2セルに
=IF(A2="","",SUMPRODUCT((Sheet1!A$1:G$1000=A2)*(Sheet1!B$1:H$1000<0),Sheet1!B$1:H$1000))

という数式をそれぞれ入れ、A2～C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

この回答へのお礼

私には全く思いもしなかった方法でとても勉強になりました。

お礼日時：2017/01/06 15:14

No.2 回答者： fujillin 回答日時： 2016/12/27 17:18

こんにちは

すでに回答が出ていますが・・・
対象列数がそれほど多くなさそうなので、各列の組で求めた値を加算すればよさそうですね。

以下はA列の値が「リンゴ」かつB列が「正の値」のものを合計します。
　=SUMPRODUCT((A:A="リンゴ")*(B:B>0)*(B:B))

あとはこれを応用することで、必要な値を求めることができるでしょう。
どうして求められるのかを書いていると長くなるので、検索などして調べてみてください。
（アンケート等の集計をする際にも便利な方法です）

この回答へのお礼

ありがとうございます。簡潔で非常に分かりやすかったです。

お礼日時：2017/01/06 14:28

No.1 回答者： 銀鱗 回答日時： 2016/12/27 16:06

A列とB列、C列とD列、E列とF列、G列とH列でそれぞれ集計した値を最後に合計すれば良いんじゃない？

またコピペ云々と言うことですので別シートで集計すればいい。
そこで参照するデータがあるシート名を変えれば良いような作りにすれば解決します。

・・・
さて、この手順の中で分からないことがありますか。
１つずつ解決を試みてください。