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

締切り済みの質問

エクセルに関して

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


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

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




列 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)列Bの数値(ここでは1,2,3、ちなみにこの数値は4,5・・・とどんどん増えていっても対応させたい、できればアルファベットにも対応して欲しい)をあるセルに入力すると、数値を入力した下のセルに、列Tの数値かつ列NのWから列Tの数値かつ列Nの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

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


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

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

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

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

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

WとLを足した数を表示させたい。ここでは6となる

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

W÷(W+L)の数を表示させたい。ここでは0.5となる

投稿日時 - 2010-11-11 11:23:17

QNo.6311849

すぐに回答ほしいです

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

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

回答(8)

ANo.8

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

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

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

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

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

投稿日時 - 2010-11-23 10:05:24

補足

補足させていただきます


>「無反応」とはどういうことでしょうか?

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



無反応とはwの個数が1と表示されなければならないところに0と表示される状態のことです。この状態が範囲指定2001年1月1日~2001年3月28日まで続いてその後の日付を入力すると。全範囲の結果が表示されます。

投稿日時 - 2010-11-24 09:39:44

お礼

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

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

>「無反応」とはどういうことでしょうか?

無反応とはwの個数が表示されるセルに0と表示され状態のことです。この状態が範囲指定2001年1月1日~2001年3月28日まで続いてその後の日付を入力すると。全範囲の結果が表示されます。

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

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

投稿日時 - 2010-11-23 22:33:15

ANo.7

>日付の表示は、一つのセルに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以前では列全体を範囲指定できない」
と書きました。データ件数が多い場合にどうすればいいか、ということまで含めて。

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

投稿日時 - 2010-11-20 21:16:44

お礼

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

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

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

よろしくお願いします。

投稿日時 - 2010-11-22 23:44:24

ANo.6

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))

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

投稿日時 - 2010-11-15 20:55:27

お礼

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


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

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

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

投稿日時 - 2010-11-20 17:53:46

ANo.5

一つ訂正を。

もし、質問者様の言う「空白セル」が数式の結果""(長さ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列の式はそのままにしておいて、作業列を別に1列準備して(仮に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での入力をしない)

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

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

投稿日時 - 2010-11-14 23:17:15

お礼

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

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


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

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

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



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

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

実際の入力例
A1        B1        C1
2000(年)   1(月)     5(日)
A2 B2 C2
2001(年)   3(月)     20(日)

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

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

投稿日時 - 2010-11-15 14:13:38

ANo.4

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

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

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

投稿日時 - 2010-11-14 20:57:04

ANo.3

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

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

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

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

=CLEAN(T1)

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

投稿日時 - 2010-11-14 12:02:29

お礼

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

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

よろしくお願いします。

投稿日時 - 2010-11-14 12:53:38

ANo.2

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関数を使った方がいいです。

投稿日時 - 2010-11-12 08:43:33

お礼

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

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

投稿日時 - 2010-11-14 05:38:55

ANo.1

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

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」の部分は変えてください。

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

=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)の式が入っているセル)

投稿日時 - 2010-11-11 15:51:51

補足

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

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

(誤)

仮に「あるセル」をA1とした場合
A1(1)-1 (Wであった場合のT列にある数の合計-Lであった場合のT列にある数)
A2(2)1 (Wの数)
A3(3)2 (Lの数)
A4(4)3 (WとLの個数の合計)
A5(5)0.33 (全体からのWの割合)


(正)

仮に「あるセル」、つまり入力するセルをA1とした場合
A2(1)-1 (Wであった場合のT列にある数の合計-Lであった場合のT列にある数)
A3(2)1 (Wの数)
A4(3)2 (Lの数)
A5(4)3 (WとLの個数の合計)
A6(5)0.33 (全体からのWの割合)

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


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

投稿日時 - 2010-11-12 07:49:17

お礼

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

教えて頂いた数式を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)列Bの数値(ここでは1,2,3は4,5・・・とどんどん増えていく、できればアルファベットにも対応して欲しい)をあるセルに入力すると、数値を入力した下のセルに、列Tの数値かつ列NのWから列Tの数値かつ列Nの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

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


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

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

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

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

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

WとLを足した数を表示させたい。ここでは6となる

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

W÷(W+L)の数を表示させたい。ここでは0.5となる








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


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


行2 2 W 3
行9 2 W 1

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




列 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

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

仮に「あるセル」をA1とした場合
A1(1)-1 (Wであった場合のT列にある数の合計-Lであった場合のT列にある数)
A2(2)1 (Wの数)
A3(3)2 (Lの数)
A4(4)3 (WとLの個数の合計)
A5(5)0.33 (全体からのWの割合)

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

投稿日時 - 2010-11-12 07:41:22

あなたにオススメの質問