エクセルに関して

質問

エクセルに関して質問させていただきます。

どなたかわかる方がいらっしゃいましたら、どうか力をお貸しください。

説明に至らない点があるかもしれません。そのときはご指摘頂けるとありがたいです。

表
列 B N T
行1 1 W 4
行2 2 W 3
行3 1 L 5
行4 1 L (空白)
行5 3 L 1
行6 3 L 1
行7 1 W 1
行8 1 W 1
行9 2 W 1
行10 1 L 1

(1)列Ｂの数値（ここでは１，２，３、ちなみにこの数値は４，５・・・とどんどん増えていっても対応させたい、できればアルファベットにも対応して欲しい）をあるセルに入力すると、数値を入力した下のセルに、列Ｔの数値かつ列ＮのＷから列Ｔの数値かつ列ＮのＬを引いた値を出したい。空白の場合は１とする。

例

あるセルに１と入力した場合。

1 1 W 4
3 1 L 5
4 1 L (空白)
7 1 W 1
8 1 W 1
10 1 L 1

となるので、結果は-１となる。

(2)さらに結果を表示させたセルの下のセルに

Ｗの数を表示させたい。ここでは３となる

(3)さらにＷの数を表示させたセルの下のセルに

Ｌの数を表示させたい。ここでは３となる

(4)さらにＬを表示させたセルの下のセルに

ＷとＬを足した数を表示させたい。ここでは６となる

(5)さらにＷとＬを足した数を表示させたセルの下のセルに

Ｗ÷（Ｗ＋Ｌ）の数を表示させたい。ここでは０．５となる

回答No.8

don9don9

>年月日を指定してもうまく求められません。
>例、２００１年１月から２００１年３月まで範囲指定すると無反応で
>２００１年１月から２００１年４月以降を範囲指定すると全指定したときと同じ数値が出てきます。
>これはどうすればいいのでしょうか？

「無反応」とはどういうことでしょうか？
「全指定したときと同じ」とは違う結果なのでしょうか？

Excelでは、日付データはシリアル値と言って
1900/1/1を1として、ここから数えて何日目かにあたるかの数値として処理されます。
これによって不等号で大小を判断したり、引き算で日数を計算したりできるわけです。

うまく計算できない、ということであれば日付データの中に
データ型が文字列のものが含まれているのではないか、と思います。
（日付の「2000/1/1」と文字列の「2000/1/1」は見かけは同じでも内部的には別ものです）
セルの書式設定が「標準」になっている場合は「2000/1/1」と入力すれば
自動的に日付と判断され、シリアル値（数値）として処理されますが
セルの書式設定「文字列」になっているセルに「2000/1/1」と入力すると
それは文字列として処理されます。
文字列として処理されているデータが混じっていないかを調べてみてください。

現在、式の結果がどうなっているのかがよくわからないので
今のところ言えるのはこのくらいです。
正直、実際のファイルを見ないで原因を突き止めるのは難しいです。

お礼

今、どうにかできないか自分なりに試行錯誤しています。

ご指摘してくださった点について答えさせて頂きます。

＞「無反応」とはどういうことでしょうか？

無反応とはｗの個数が表示されるセルに０と表示され状態のことです。この状態が範囲指定２００１年１月１日～２００１年３月２８日まで続いてその後の日付を入力すると。全範囲の結果が表示されます。

＞「全指定したときと同じ」とは違う結果なのでしょうか？

全指定したときの結果と日付の範囲指定を４月以降が一緒になります。
例えば、全範囲が２００１年１月１日～２００２年１２月３１日だとします。
このとき範囲指定を２００１年１月１日～２００１年４月１日にすると上記と同じ結果になります。手計算とは違う値になっています。２００１年１月～２００１年４月の範囲には求めたい数値がないにもかかわらず個数０になりません。

補足

補足させていただきます

＞「無反応」とはどういうことでしょうか？

結果に１と表示されなければならないところ、０と表示されます。

無反応とはｗの個数が１と表示されなければならないところに０と表示される状態のことです。この状態が範囲指定２００１年１月１日～２００１年３月２８日まで続いてその後の日付を入力すると。全範囲の結果が表示されます。

回答No.7

don9don9

>日付の表示は、一つのセルに2000/1/1のようになっています。

ということであれば、検索条件として年、月、日をそれぞれ別々のセルに入力する場合
DATE関数を組み込む必要があります。

No.6の回答で

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(日付>=開始日)*(日付<=終了日)*(T1:T10))

とした部分は、仮に日付データがU列に入っているとしたら

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(U1:U10>=DATE(年,月,日))*(U1:U10<=DATE(年,月,日))*(T1:T10))
（※年、月、日は検索条件となる年、月、日を入力するセルの番地になります）

>あと、あるセルに入力する範囲を全てにする場合はどうすればいいでしょうか？
>教えて頂いた数式は範囲を限定することができます。
>その限定されている範囲を全部にしたときの数値を求めたいです。

これは何をおっしゃりたいのでしょうか？
私が例示した式が10行分のデータの集計しかしていない、ということでしょうか？
それについてはNo.2の回答で「SUMPRODUCT関数は、Excel2003以前では列全体を範囲指定できない」
と書きました。データ件数が多い場合にどうすればいいか、ということまで含めて。

大変失礼ですが、私がこれまでに書いてきた回答の内容を理解しようとされていますか？

お礼

申し訳ありません。２度同じことを質問していたようです。

教えて頂いた数式を当てはめたところ、また前と同じ現象になってしまいました。新規シートでは成功して集計したいシートではうまくいきません。

年月日を指定してもうまく求められません。例、２００１年１月から２００１年３月まで範囲指定すると無反応で２００１年１月から２００１年４月以降を範囲指定すると全指定したときと同じ数値が出てきます。これはどうすればいいのでしょうか？

よろしくお願いします。

回答No.6

don9don9

B列、N列、T列以外の列に、日付が入力されている列があり
その日付を条件にさらに絞り込みたい、ということでしょうか？
そうであれば以下の点を補足してください。

日付の列はどの列で、データはどのようになっていますか？
一つのセルに「2000/1/1」のような日付の形式で入っていますか？
それとも「2000」「1」「1」のように年月日でセルが分かれていますか？

SUMPRODUCT関数の条件指定は、例えば

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(T1:T10))

という式の場合「(B1:B10=A1)」「(N1:N10="W")」がそれぞれ一つの条件で
これに条件を追加する場合は「*」でつなげばいいので

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(日付>=開始日)*(日付<=終了日)*(T1:T10))

のような式になりますが、日付の入力の仕方次第でこの部分は変わってしまいます。
もし年月日が分かれて入力されているようだと、ちょっと一工夫が必要になるかもしれません。

お礼

日付の表示は、一つのセルに2000/1/1のようになっています。

あと、あるセルに入力する範囲を全てにする場合はどうすればいいでしょうか？

教えて頂いた数式は範囲を限定することができます。その限定されている範囲を全部にしたときの数値を求めたいです。

説明がわかりづらいかもしれません。その時は、ご指摘頂けると有難いです。

回答No.5

don9don9

一つ訂正を。

もし、質問者様の言う「空白セル」が数式の結果""（長さ0の文字列）が入っているだけで
実際にはブランクではない、ということであれば、ISBLANK関数は使えません。
つまり回答No.1の(1)の式は使えなくなります。

T列に入っている数式の""を返す部分を1を返すように変更して
(1)の式を

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(T1:T10))-SUMPRODUCT((B1:B10=A1)*(N1:N10="L")*(T1:T10))
（Ctrl＋Shift＋Enterでの入力をしない）

とするか
もし、T列で空白を返すべきところに1が入るとまずい、ということであれば
T列の式はそのままにしておいて、作業列を別に１列準備して（仮にU列とします）

=IF(T1<>"",T1,1)

という式を入れて、これを最終行までコピー＆ペーストし
(1)の式を

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(U1:U10))-SUMPRODUCT((B1:B10=A1)*(N1:N10="L")*(U1:U10))
（こちらも、Ctrl＋Shift＋Enterでの入力をしない）

とするしかないかもしれません。

作業列が邪魔であればセルのフォントカラーを白に変えておけば文字は見えません。
列を非表示にする、という方法もありますが、データ件数が増えることを想定するとお勧めできません。

お礼

Ｔ列の””で空白にしている部分を教えて頂いた通り”１”にしました。そうしたら計算できました。

最初の質問は解決できました。ありがとうございます、本当に助かりました。これでかなり作業時間を短縮することができます。

ところで、新しく質問させて頂いてもよろしいでしょうか？

この、教えて頂いた数式に付け足したいことがあります。

それは日付の範囲を指定した場合の値を求めたいのです。例えば２０００年１月１日から２０００年２１月３１日までの値を求めたいということです。

例

A1（入力セルは何処でもいい） 　　　B1　　　　C1
年指定（範囲の最初）　　　　　　　　月指定 日指定

A2 （入力セルは何処でもいい） 　　　　　　　　B2 　　C2
年指定（範囲の最後）　　　　　　　　　　　　　月指定　　　日指定

実際の入力例
A1 　　　　　　　B1 　　　　　　　C1
２０００（年）　　　１（月）　　　　　５（日）
A2 B2 C2
２００１（年）　　　３（月）　　　　　２０（日）

このように入力すると、この範囲での値が求められるようにしたいのです。

入力はリストボックスにしたいのです。できますでしょうか？

回答No.4

don9don9

数式が入っているからダメなのではありません。
数式の結果に数値以外が含まれているのがダメなのだと思います。

T列に入っている数式は、結果に必ず数値を返していますか？
例えばIF関数の条件判断の結果によって、""（長さ0の文字列）を返したりしていませんか？
""は、一見セルには何も入っていないように見えますが
何も入っていないブランクのセルとは違って、値が数値ではありませんので
集計すると#VALUE!エラーが出てしまいます。

これを回避する場合は、数式の""を返す部分を0に変えることです。
ただこうすると、今までセルに何も表示されていなかった部分に
0が表示されるようになりますので、それで見栄えが悪いようなら
条件付書式で「セルの値が0の場合、フォントカラーを白とする」
という設定をすればいいと思います。

回答No.3

don9don9

#VALUE!エラーは、式で使っている値のデータ型がおかしいときに出るエラーです。
T列の集計範囲内に数値以外のデータがあるのではないでしょうか。

特に怪しいのは空白のセルです。
一見、空白に見えても、スペースが入っているとか制御コードが入っているとか
外部データをExcelに取り込んだ場合に、そういうことが時々あります。

まず全部の空白のセルにカーソルを合わせてDeleteキーを押してみてください。
スペースが入っている場合はそれで消せます。

それでダメなら、CLEAN関数を使って制御コードを消します。
空いている列に（仮にU列とすると）
U1に

=CLEAN(T1)

と入力してこれを最終行までコピー＆ペーストし
このU列の式が入っているセルをコピーして、T列に貼り付けます。
このとき、右クリックで「形式を選択して貼り付け」で「値」を選択します。

お礼

Ｔ列の数式を消去したら新規シートと同じように計算できました。

T列には数式が入っています。その数式で求めた数値を計算したいのです。Ｔ列に数式が入っていては教えて頂いたものを使用することはできないのでしょうか？

よろしくお願いします。

回答No.2

don9don9

No.1です。

すみません、最初からA1が入力用のセルという前提で回答していたのですが…
私がNo.1の(1)～(5)に書いた式はA1を直接・間接的に参照しているため
これらの式をA1自身に入れたら循環参照になってしまいます。
(1)～(5)の式をA2～A6に入れてみてもダメでしょうか？

SUMPRODUCT関数は、Excel2003以前では列全体を範囲指定することができません。
（列全体を範囲指定した「=SUMPRODUCT((B:B=A1)*(N:N="W"))」のような式はNG）
ですから行数が増える場合の対応としては、式中の「10」の部分を
あらかじめ大きな数字にしておくくらいしか方法がありません。
場合によってはExcelの最大行数（2003なら65536行）でもいいです。

Excel2007以降なら上記のような列全体を範囲指定した式でもOKですが
2007以降なら集計はSUMIFS関数、カウントはCOUNTIFS関数を使った方がいいです。

お礼

新規シートでは思う通りにできました。ですが集計したい数値があるシートでは＃ＶＡＬＵＥ！とでます。同じにしているつもりですが片方では成功し、もう片方では失敗します。これはどこが至らないのでしょうか？

ご忠告いただけると有難いです。

回答No.1

don9don9

質問が分かりづらいのですが、要するに
複数の条件に合致する行の値の集計、及び件数カウントの方法
を知りたいのではないかとお見受けします。

Excel2007以降だとCOUNTIFS関数やSUMIFS関数があるので
こちらを使いたいところですが、一応2003以前でも使える
SUMPRODUCT関数を使った方法を紹介します。

「あるセル」をA1とすると

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W"))

で、B列の値がA1セルの内容と合致し、かつN列の値が"W"である行の件数

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*(T1:T10))

で、B列の値がA1セルの内容と合致し、かつN列の値が"W"である行の、T列の値の合計

が計算できます。
※いずれも10行の場合。実際の行数に合わせて「10」の部分は変えてください。

ここで厄介なのが「空白の場合は１とする」です。
Excelの計算式では通常、空白は０とみなされます。
これを１とするためには、ISBLANK関数を組み合わせる必要があります。
対象が１つのセルだけなら

=IF(ISBLANK(T1),1,T1)

でいいのですが、T1:T10のように複数セル範囲の場合は
例えば合計を計算する場合

=SUM(IF(ISBLANK(T1:T10),1,T1:T10))

と入力して、Ctrl＋Shift＋Enterで配列数式にする必要があります。

以上、前置きが長くなりましたが、ここまでをまとめて式を組み立てると

(1)は

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W")*IF(ISBLANK(T1:T10),1,T1:T10))-SUMPRODUCT((B1:B10=A1)*(N1:N10="L")*IF(ISBLANK(T1:T10),1,T1:T10))

と入力してCtrl＋Shift＋Enterで配列数式にする

(2)は

=SUMPRODUCT((B1:B10=A1)*(N1:N10="W"))

(3)は

=SUMPRODUCT((B1:B10=A1)*(N1:N10="L"))

(4)は

=(2)の式が入っているセル+(3)の式が入っているセル

(5)は

=(2)の式が入っているセル/((2)の式が入っているセル+(3)の式が入っているセル)

お礼

お答え頂きありがとうございます。わからないところがあるので教えてください。

教えて頂いた数式をA1に入力しました。すると循環参照されていると出ました。わかりません、よろしくお願いします。

表
列 B N T
行1 1 W 4
行2 2 W 3
行3 1 L 5
行4 1 L (空白)
行5 3 L 1
行6 3 L 1
行7 1 W 1
行8 1 W 1
行9 2 W 1
行10 1 L 1

(1)列Ｂの数値（ここでは１，２，３は４，５・・・とどんどん増えていく、できればアルファベットにも対応して欲しい）をあるセルに入力すると、数値を入力した下のセルに、列Ｔの数値かつ列ＮのＷから列Ｔの数値かつ列ＮのＬを引いた値を出したい。空白の場合は１とする。

例

あるセルに１と入力した場合。

1 1 W 4
3 1 L 5
4 1 L (空白)
7 1 W 1
8 1 W 1
10 1 L 1

となるので、結果は-１となる。

(2)さらに結果を表示させたセルの下のセルに

Ｗの数を表示させたい。ここでは３となる

(3)さらにＷの数を表示させたセルの下のセルに

Ｌの数を表示させたい。ここでは３となる

(4)さらにＬを表示させたセルの下のセルに

ＷとＬを足した数を表示させたい。ここでは６となる

(5)さらにＷとＬを足した数を表示させたセルの下のセルに

Ｗ÷（Ｗ＋Ｌ）の数を表示させたい。ここでは０．５となる

～～～～～補足させて頂きました～～～～～

例２
あるセルに２と入力した場合

行2 2 W 3
行9 2 W 1

となるので結果は４となります。

表
列 B N T
行1 1 W 4
行2 2 W 3
行3 1 L 5
行4 1 L (空白)
行5 3 L 1
行6 3 L 1
行7 1 W 1
行8 1 W 1
行9 2 W 1
行10 1 L 1

上記の表が、下記のように増えていく可能性があります。

表
列 B N T
行1 1 W 4
行2 2 W 3
行3 1 L 5
行4 1 L (空白)
行5 3 L 1
行6 3 L 1
行7 1 W 1
行8 1 W 1
行9 2 W 1
行10 1 L 1
行11 1A L 1
行12 1A L 1
行13 1B L 1
行14 1C W 1
行15 1B W 1
行16 1A W 1
行17 1B L 5

この表であるセルに1Aと入力すると。

行12 1A L 1
行13 1B L 1
行16 1A W 1

となり、結果はー１となります。

仮に「あるセル」をA1とした場合
A1（１）－１ （Ｗであった場合のＴ列にある数の合計－Ｌであった場合のＴ列にある数）
A2（２）１　（Ｗの数）
A3（３）２　（Ｌの数）
A4（４）３　（ＷとＬの個数の合計）
A5（５）０．３３　（全体からのＷの割合）

このように表示させたいです。

補足

申し訳ありません。間違いました。

お礼の分の最後のあたりを修正致しました。

（誤）

仮に「あるセル」をA1とした場合
A1（１）－１ （Ｗであった場合のＴ列にある数の合計－Ｌであった場合のＴ列にある数）
A2（２）１　（Ｗの数）
A3（３）２　（Ｌの数）
A4（４）３　（ＷとＬの個数の合計）
A5（５）０．３３　（全体からのＷの割合）

（正）

仮に「あるセル」、つまり入力するセルをA1とした場合
A2（１）－１ （Ｗであった場合のＴ列にある数の合計－Ｌであった場合のＴ列にある数）
A3（２）１　（Ｗの数）
A4（３）２　（Ｌの数）
A5（４）３　（ＷとＬの個数の合計）
A6（５）０．３３　（全体からのＷの割合）

修正後は行が１つずつずれています。

このように表示させたいです。