エクセルで離れたセルを範囲指定

質問

お世話になります。
以下のような表があるとします。

　　　　　　　項目１　　項目２　　項目３　　項目４　　項目５
サンプル１　　○　　　　　　　　　　○　　　　　○
サンプル２　　　　　　　○　　　　　　　　　　　　　　　○

これらの右に列を設け、○の数をカウントします。
カウント１には項目１と項目３の列のみ。
カウント２には項目２と項目４と項目５のみの○をカウントしたいとします。
つまり以下のようにしたいわけです。

　　　　　　　項目１　　項目２　　項目３　　項目４　項目５　　カウント１　カウント２
サンプル１　　○　　　　　　　　　　○　　　　　○　　　　　　　　　２　　　　　　１
サンプル２　　　　　　　○　　　　　　　　　　　　　　　○　　　　　０　　　　　２

○をカウントするだけならCOUNTIF(B2:C4,"○")で出来ますが
今回のように飛び地を選択する方法がわかりません。
どのようにすれば多数の離れた範囲を選択できるのでしょうか？

回答No.5

zap35 · ベストアンサー

＃０４です。もしかしたら質問を読み違えたかもしれません。
実際のシートはもっと列が多いのかもしれませんね。
こんな式でもできますよ。

１行目のカウント１の式
=SUMPRODUCT({1,0,1,0,0}*(B1:F1="○"))

１行目のカウント２の式
=SUMPRODUCT({0,1,0,1,1}*(B1:F1="○"))

いずれも下方向にコピーです

お礼

zap35さん、再びありがとうございます。
ご推察の通り実際は列がもっと多い状況です。

この関数はすばらしいですね。
=SUMPRODUCT({1,0,1,0,0}*(B1:F1="○"))の場合ですと
項目１に○があれば1に、項目2には○があっても0に・・・
そしてそれらを合算する、という感じですね。

ただ、もしかすると他にも良いアイデアを頂けるかもしれませんので
しばらくは受付中にさせて頂きます。

回答No.7

noname#52504

#6です。

対象列を間違えました。
例題は、1,3,5と2,4ではなく、1,3と2,4,5でしたね。
読み替えてください。

それと、最初の２行ですが、
「標準的な処理として#5さんのご回答を支持します」
というニュアンスです。
嫌らしい意味にも解釈できそうなので、念のため。

お礼

わざわざ補足ありがとうございます。

私の疑問はとりあえず解決したので
締め切ろうと思います。

ポイントについては悩みますね。
皆様、良解答ばかりですから。

今回の場合はzap35さんの案を採用しようかと思います。
deus_ex_machinaさんの案は今後活かして行きたいと思います。
esupuressoさん、hidewoさんもいろいろとありがとうございました。
今後ともよろしくお願い致します。

回答No.6

noname#52504

私も#5さんと同様の回答をするつもりだったのですが、
既に出てしまったので別案を。

●乙案（甲案は#5さんのご回答ということで…）
列番号で指定する。

　カウント１
　　=SUMPRODUCT((B1:F1="○")*(COLUMN(B1:F1)={2;4;6}))
　カウント２
　　=SUMPRODUCT((B1:F1="○")*(COLUMN(B1:F1)={3;5}))

●丙案
項目名で指定する

　１行目に項目名が入力されているとします。
　カウント１
　　=SUMPRODUCT((B2:F2="○")*(B$1:F$1={"項目１";"項目３";"項目５"}))
　カウント２
　　=SUMPRODUCT((B2:F2="○")*(B$1:F$1={"項目２";"項目４"}))

※配列定数をセミコロン;で区切っている点に注意。

検索対象の列を列番号や項目名で指定できるので、
列が非常に多ければ利点があるかもしれません。

対象項目名をどこか別の場所に縦に書き出しておけば、
　=SUMPRODUCT((B2:F2="○")*(B$1:F$1=$X$2:$X$5))
のように参照することもできます。

以上ご参考まで。

お礼

なるほど、いろいろな方法があるのですね。
項目名で指定するのは後で見た場合に分かりやすいかもしれませんね。

大体わかってきましたが
SUMPRODUCT関数は奥が深いようですね。

いろいろありがとうございました。

回答No.4

zap35

項目１はB列、項目２はC列…項目５はF列でいいですか

１行目のカウント１の式
=(B1="○")+(D1="○")

１行目のカウント２の式
=(C1="○")+(E1="○")+(F1="○")

いずれも下方向にコピーでいけると思います

お礼

こんなカウントの仕方があるとは初めて知りました。
これでも式が煩雑にはなりますが関数よりはすっきりしますね。
参考にさせて頂きます。

回答No.3

hidewo

なるほど、思ったより複雑のようですね。
でも、解決方法はあると思います。

１．解決方法１
単に合計したい項目を並び替える（列の順番を入れ替える）ことはできないのですか？これが一番、簡単ですよね。
もし、カウント１とカウント２で重複するものがあれば、それを真ん中の列においておけば、カウントの範囲を重複して選択することも可能ですよね。

２．解決方法２
項目を並び替えたくないなら・・・
○を１に変換し、◎を1000に変換するのは？
足してみて、23035となれば、○が35個、◎が23個となります。
もし○の数1000以上あるなら、◎を10000にしてもいいでしょうね。

いかがですか？

お礼

hideoさん、再びありがとうございます。

かなり柔軟な思考を持ってらっしゃるようですね。
頭の固い私にはものすごく参考になります。

まず解決方法１の方ですが並び換えは困難な状況です。
また重複するものが一つとは限らないので。

解決方法２の方ですが、これは使えるかもしれませんね。
出てきた値を計算すればいけるかもしれません。

ただ、計算の分、式が長くなりそうなのが難点です。
でも他に方法がなければ検討したいと思います。

回答No.2

hidewo

私の知る限り無理です。

対応方法としては、=COUNTIF(B2:B4,"○")＋COUNTIF(D2:D4,"○")
などとするしかないです。
でも、これだと煩雑ですよね。

これは提案ですが、○を1などに一括置換してはいかがですか？
それならば単純なsum関数で
=sum(B2,D2,F2)
のように簡単になります。

お礼

ありがとうございます。
○を1にするとは発想の転換ですね。
いいアイデアです。
ただ、書いてなかったのですが実は記号には○以外にも◎なども使い、
○だけを飛び地で数えるなども行いたかったのでこの方法では無理そうです。
でも、参考になりました。

回答No.1

esupuresso

一行目に項目列「項目1」「項目2」「項目3」「項目4」「項目5」
【A】列にサンプル１」「サンプル２」「サンプル３」「サンプル４」とした時・・・
【G2】には　「=COUNTA(B2:B4,D2:D4)」　項目１と項目３
【H2】には　「=COUNTA(C2:C4,E2:E4,F2:F4)」項目２・４・５を数えます

お礼

ありがとうございます。
COUNTAはデータの個数を数えるというものですね。
私の書き方が悪かったのですが
○以外にも◎なども使い、それぞれの数をカウントしようともしています。
そのため
=COUNTIF(範囲,○)＋COUNTIF(範囲,◎)というのが浮かんだのですが
このうちの範囲が飛び地なのです。
なぜ○と◎に分けるかと言うと記号によって後で処理をするからです。
例えば◎のものは2倍にしたいとしたら
=COUNTIF(範囲,○)＋2*COUNTIF(範囲,◎)
の様に。
ただ、範囲が飛び地だとできないのでどうにかならないか、と思った次第です。
=COUNTIF(セル１,○)＋COUNTIF(セル２,○)＋・・・とやっても良いのでしょうが式が長くなりすぎるのでどうにかしたいと。
そういうことは可能でしょうか？

あと私が最初に式を書き間違えましたが
集計する際はサンプル１とサンプル２で別々に集計したいのです。
つまり最初の例で言うと
サンプル1には項目１・３・４に○があるので
カウント１は項目１と３の○の数なので２となるわけです。