みんなの「教えて(疑問・質問)」にみんなで「答える」Q&Aコミュニティ

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

解決済みの質問

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

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

       項目1  項目2  項目3  項目4  項目5
サンプル1  ○          ○     ○
サンプル2       ○               ○

これらの右に列を設け、○の数をカウントします。
カウント1には項目1と項目3の列のみ。
カウント2には項目2と項目4と項目5のみの○をカウントしたいとします。
つまり以下のようにしたいわけです。


       項目1  項目2  項目3  項目4 項目5  カウント1 カウント2
サンプル1  ○          ○     ○         2      1
サンプル2       ○               ○     0     2


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

投稿日時 - 2008-02-24 19:36:47

QNo.3806233

困ってます

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

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

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

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

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

投稿日時 - 2008-02-24 21:33:08

お礼

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

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

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

投稿日時 - 2008-02-24 22:41:42

ANo.5

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

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

回答(7)

ANo.7

#6です。

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

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

投稿日時 - 2008-02-24 23:55:25

お礼

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

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

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

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

投稿日時 - 2008-02-25 00:42:48

ANo.6

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

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

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

●丙案
項目名で指定する

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

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

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

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

以上ご参考まで。

投稿日時 - 2008-02-24 23:37:29

お礼

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

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

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

投稿日時 - 2008-02-25 00:33:32

ANo.4

項目1はB列、項目2はC列…項目5はF列でいいですか

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

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

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

投稿日時 - 2008-02-24 21:23:48

お礼

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

投稿日時 - 2008-02-24 22:17:47

ANo.3

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

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

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

いかがですか?

投稿日時 - 2008-02-24 21:04:58

お礼

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

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

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

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

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

投稿日時 - 2008-02-24 22:10:51

ANo.2

私の知る限り無理です。

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

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

投稿日時 - 2008-02-24 20:09:44

お礼

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

投稿日時 - 2008-02-24 20:25:50

ANo.1

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

投稿日時 - 2008-02-24 20:08:52

お礼

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

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

投稿日時 - 2008-02-24 20:35:23

あなたにオススメの質問