エクセルにおけるセルの範囲指定について。

質問

仮に
=COUNTIF(A1:A5,1)+COUNTIF(A８:A10,1)＋COUNTIF(A13:A16,1)というような数式を入力したとして、このような場合、＋を省略して数式を一つにまとめるようなことはできないものでしょうか？

この場合、短いのでどうということもないのですが、このあと断続的にずっとカウントさせるとなると、ずっとCOUNTIFを続けていかねばならず、非常に手間がかかってしまいます。

上手に説明できず、素人じみた質問で申し訳無いのですが
方法があれば教えてください。申し訳ありません。

回答No.8

MarcoRossiItaly · ベストアンサー

INDEX 関数の第 4 引数（領域番号）に、単一の数値やセル参照を指定することは可能ですが、配列を指定することはできません。つまり第 1 引数で挙げた複数の領域の中から、1 つの領域しか選べません。

よく、独自の定義で、ユーザーの期待どおりに動作しないことをバグとか呼ぶ人もいますが、バグとは「人為的なプログラムのミス」のことであり、不満のある仕様とか合理的でない仕様といった話とは、全然異なる意味です。これもそうした類で、ソフト開発者のミスとかではないので、混同しないようにしましょう。念のため。

さて、脇道にそれましたが、話を元に戻しますね。

個人的に最もお勧めな対応の方法は、作業列に 0、1 などのフラグを立て、その条件に合う行だけを（COUNTIF ではなく）COUNTIFS で数えることです。Excel2003 以前なら SUMPRODUCT で代用します。すると、参照範囲を A1:A16 という 1 つにつながった範囲にして、数えたい行とそうでない行を B1:B16 に立てたフラグで区別する形となります。

第 2 案としては、「内訳」を表示するための作業列の各行に COUNTIF を入力し、それを SUM で合計することです。参照する範囲が複雑かつ多数であるなら、どこを計算しているのかシート上に表示されていたほうが分かりやすいでしょう。

第 3 案としては、次式です。

=sum(countif(indirect({"a1:a5","a8:a10","a13:a16"}),1))

第 4 案としては、「名前」の機能を併用することです。ただ、COUNTIF は、たとえ「単一の名前で定義された複数のセル範囲」であろうとも、それを第 1 引数に指定するとエラーになってしまいます。なので、ちょっと工夫が必要です。

数式タブ「名前の定義」ダイアログを開き、「参照範囲」ボックスに「=countif($A$1:$A$5,1)」と入力。「$」を抜かして入力するとおかしな現象が発生することがあるので、必ず入れます。この範囲の「名前」を例えば「計上範囲1」と入力します。残りの 2 範囲に対しても同様に名前を定義します。最後に、次式で「1」が入力されているセルの個数を求めます。

=sum(計上範囲1,計上範囲2,計上範囲3)

なお、今回はたまたま「1」の個数を数えようとしているので、もう少し簡単に処理することもできます。A1:A5、A8:A10、A13:A16 の 3 つの範囲を Ctrl キーを押しながら同時に選択します。その状態で、名前ボックス（普段セル番地が表示されている数式バーの左隣にあるボックス）を「範囲」などと上書き。すると 3 範囲に 1 つの名前が付けられます。あとは、「1」の個数であることから、次式で計算できます。

=sum(範囲)

回答No.9

MarcoRossiItaly

No.8 です、何度もすみません。説明に舌足らずな箇所があったので、補足します。

No.8 のいちばん最後の数式において、その式で計算できる理由について「「1」の個数であることから、次式で計算できます。」と述べましたが、「1」以外の行が「0」であることも前提となっていました。そうでないデータでは、個数に一致しませんね。すみません。

×　「1」の個数　→　○　「0」と「1」のみから成るデータのうち「1」の個数

回答No.7

cj_mover

＃４、ｃｊです。

一応、出来なくもない、ということで、数式も書いてみました（動作確認済）。
　　=SUM(COUNTIF(INDIRECT({"a1:a5","a8:a10","a13:a16"}),1))
セル範囲の指定を連続列挙できますが、文字列指定の配列定数です。
まぁそれでも多少、省力化を図ることは出来ているような、、、。
COUNTIF関数は
単矩形範囲（ひとつの連続した四角いセル範囲）しか扱えないので、
独自の工夫をしています。
（＃　マシな数式が出てくること祈りつつ、叩き台として。）
ご参考まで。

回答No.6

Nouble

WEBで確認したら行きなり間違えてました。汗

正記
INDEX((A1:A5,A8:A10,A13:A16),0,0,ROW(A1:A3))

誤記
INDEX((A1:A5,A8:A10,A13:A16),ROW(A1:A3),0,0)

お詫びの上、訂正致します。

回答No.5

Nouble

今確認できる環境にないので、
動作は不安なのですが、
INDEX((A1:A5,A8:A10,A13:A16),ROW(A1:A3),0,0)
で、飛び飛びの範囲を一括指定できるはずです。

そもそもの話、
COUNTIIFの挙動を信頼していないので
使用をお勧めしては、いないのですが
=COUNTIF(INDEX((A1:A5,A8:A10,A13:A16),ROW(A1:A3),0,0),1)
で、行けると思います。

場合によっては、
=COUNTIF(INDEX((A1:A5,A8:A10,A13:A16),0,0,0),1)
でも行けると思います。

また、
不参照行に規則性や特徴が有るなら、
これを利用して除外する方法もあります。

一例として、
除外する行に特定の文字列がある場合。
(※注：仮に除外行のZ列に、「除外」と言う文字列が入っているものとする。)
=SUMPRODUCT((A1:A16=1)*(Z1:Z16<>"除外"))
で、数えられると思います。

如何でしょうか？
お役に立てていたならば幸いです。

回答No.4

cj_mover

こんにちは。

数式の書き方による解決は難しいような（私も解りません）ので、
ＵＤＦ（ユーザー定義関数）を紹介しておきます。

' ' ＝＝＝＝＝＝＝＝＝ここから＝＝＝＝＝＝＝＝＝＝ 8123157

Function COUNTEQV(検査値, ParamArray 検査対象()) As Long
　　Dim p, v, n&
　　For Each p In 検査対象
　　　　Select Case TypeName(p)
　　　　Case "Range", "Variant()"
　　　　　　For Each v In p
　　　　　　　　If Not IsError(v) Then
　　　　　　　　　　If v = 検査値 Then n = n + 1
　　　　　　　　End If
　　　　　　Next
　　　　Case Else
　　　　　　If Not IsError(p) Then
　　　　　　　　If p = 検査値 Then n = n + 1
　　　　　　End If
　　　　End Select
　　Next
　　COUNTEQV = n
End Function

' ' ＝＝＝＝＝＝＝＝＝ここまで＝＝＝＝＝＝＝＝＝＝ cj

準備■
当該ブックがアクティブな状況から
　Alt + F11　→　Visual Basic Editor を表示
　Alt + I M（[挿入]メニュー[標準モジュール]）→　Module1（標準モジュール）を挿入
　→表示された標準モジュールの真っ新な編集画面に
　上記（＝ここから＝＝ここまで＝）をコピーし貼り付ける
　Alt + F4　（[閉じる]ボタン）→　Visual Basic Editor を終了、EXCELに戻る
以上で準備完了。

運用■
EXCELの関数を扱うのと同じようにセルに直接
　=COUNTEQV(1,(A1:A5,A8:A10,A13:A16))
または
　=COUNTEQV(1,A1:A5,A8:A10,A13:A16)
のように記入して、数式を普通に確定。

仕様■
　検査対象の中で、検査値と同値のものが幾つ含まれるかを、数値で返します。

　書式　　COUNTEQV(検査値, 検査対象,［検査対象2］,［検査対象3］,,,,［検査対象30 | 255］)

　引数
　　検査値　＿　必須。検査対象の中で照合（カウント）する値を指定します。
　　検査対象　＿　必須。検索するセル範囲（配列）、または任意の値（配列）を指定します。
　　検査対象2...30 | 255　＿　省略可。検査対象を拡張します。検査対象となる引数を指定します。
　　　　　　　指定できる引数（第二引数以下）の数（30 | 255）はEXCELのバージョンに依存します。

メモ□
　この関数で検査できるのは、同じ数値、または、文字列値の完全な一致、のみです。
　異なるデータ型間でも、同じ数値で表せるものは同値と看做します（COUNTIF同様）。
　　例えば、検査対象の中に 1900/1/1 のような日付シリアル値（数値=1）があるとして、
　　検査値に 1 を指定した場合、この関数はこの日付シリアル値を 1 と看做しカウントします。
　　（この例示の計算結果はEXCELの日付計算に関するオプション設定により変化します。）
　検査対象に含まれたエラー値は無視して計算ます。　検査値にエラー値は指定できません。

' ' ＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

範囲の指定方法は、最も簡易なものです。
EXCEL関数と同じように
　=COUNTEQV(1,
までタイプしたところで、セル範囲を直接選択し、
Ctrl キーを押しながらセル範囲を追加指定することもできます。
　=COUNTEQV(1,A1:A5,A8:A10,A13:A16)
の方が書き易いですけれど、
　=COUNTEQV(1,(A1:A5,A8:A10,A13:A16))
の方が微妙に計算が軽いです。この点も普通のSUM関数などと一緒。

ＵＤＦですから、ひとつの数式をドラッグして数千超のセルで計算
させるような使い方には向きません。この点はCOUNTIF関数などと一緒。

単機能な関数ですが、それ故他のEXCEL関数と組み合わせる可能性を残していると思います。

食わず嫌いで終ってしまう方も多いようですが、
質問投稿文を考えるよりは難しくない筈ですし、
面倒なことを避ける為に、少し手間を割くだけなのですけどね。
とりあえず、試してみてもらえれば、と思います。

以上です。

回答No.3

esupuresso

範囲が数か所に複数あるのでしたら

例えばセルB1に=SUM(COUNTIF(A1:A5,1),COUNTIF(A8:A10,1),COUNTIF(A13:A16,1))　とか…。

回答No.2

noname#204879

「カウントさせる」範囲以外には 1 がなければ
=COUNTIF(A:A,1)
でＯＫだけど・・・

回答No.1

FEX2053

その「とびとびの範囲」に名前を付ければ？

[Ctrl]キーを押しながらクリックすると、複数の範囲を同時に選択
できます。この状態で「範囲名」をつけることができますので、
後は、その「範囲名」をこんな風に参照すればいいだけかと

=COUNTIF(範囲名,1)

範囲名のつけ方は、バージョンによって違いますので、その辺は
「Excel2007 範囲名」とかで検索を、どぞ。