エクセルに関して質問させていただきます。
どなたかわかる方がいらっしゃいましたら、どうか力をお貸しください。
説明に至らない点があるかもしれません。そのときはご指摘頂けるとありがたいです。
表
列 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となる
A 回答 (8件)
- 最新から表示
- 回答順に表示
No.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」と入力すると
それは文字列として処理されます。
文字列として処理されているデータが混じっていないかを調べてみてください。
現在、式の結果がどうなっているのかがよくわからないので
今のところ言えるのはこのくらいです。
正直、実際のファイルを見ないで原因を突き止めるのは難しいです。
この回答への補足
補足させていただきます
>「無反応」とはどういうことでしょうか?
結果に1と表示されなければならないところ、0と表示されます。
無反応とはwの個数が1と表示されなければならないところに0と表示される状態のことです。この状態が範囲指定2001年1月1日~2001年3月28日まで続いてその後の日付を入力すると。全範囲の結果が表示されます。
今、どうにかできないか自分なりに試行錯誤しています。
ご指摘してくださった点について答えさせて頂きます。
>「無反応」とはどういうことでしょうか?
無反応とは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になりません。
No.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以前では列全体を範囲指定できない」
と書きました。データ件数が多い場合にどうすればいいか、ということまで含めて。
大変失礼ですが、私がこれまでに書いてきた回答の内容を理解しようとされていますか?
申し訳ありません。2度同じことを質問していたようです。
教えて頂いた数式を当てはめたところ、また前と同じ現象になってしまいました。新規シートでは成功して集計したいシートではうまくいきません。
年月日を指定してもうまく求められません。例、2001年1月から2001年3月まで範囲指定すると無反応で2001年1月から2001年4月以降を範囲指定すると全指定したときと同じ数値が出てきます。これはどうすればいいのでしょうか?
よろしくお願いします。
No.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))
のような式になりますが、日付の入力の仕方次第でこの部分は変わってしまいます。
もし年月日が分かれて入力されているようだと、ちょっと一工夫が必要になるかもしれません。
日付の表示は、一つのセルに2000/1/1のようになっています。
あと、あるセルに入力する範囲を全てにする場合はどうすればいいでしょうか?
教えて頂いた数式は範囲を限定することができます。その限定されている範囲を全部にしたときの数値を求めたいです。
説明がわかりづらいかもしれません。その時は、ご指摘頂けると有難いです。
No.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での入力をしない)
とするしかないかもしれません。
作業列が邪魔であればセルのフォントカラーを白に変えておけば文字は見えません。
列を非表示にする、という方法もありますが、データ件数が増えることを想定するとお勧めできません。
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(日)
このように入力すると、この範囲での値が求められるようにしたいのです。
入力はリストボックスにしたいのです。できますでしょうか?
No.4
- 回答日時:
数式が入っているからダメなのではありません。
数式の結果に数値以外が含まれているのがダメなのだと思います。
T列に入っている数式は、結果に必ず数値を返していますか?
例えばIF関数の条件判断の結果によって、""(長さ0の文字列)を返したりしていませんか?
""は、一見セルには何も入っていないように見えますが
何も入っていないブランクのセルとは違って、値が数値ではありませんので
集計すると#VALUE!エラーが出てしまいます。
これを回避する場合は、数式の""を返す部分を0に変えることです。
ただこうすると、今までセルに何も表示されていなかった部分に
0が表示されるようになりますので、それで見栄えが悪いようなら
条件付書式で「セルの値が0の場合、フォントカラーを白とする」
という設定をすればいいと思います。
No.3
- 回答日時:
#VALUE!エラーは、式で使っている値のデータ型がおかしいときに出るエラーです。
T列の集計範囲内に数値以外のデータがあるのではないでしょうか。
特に怪しいのは空白のセルです。
一見、空白に見えても、スペースが入っているとか制御コードが入っているとか
外部データをExcelに取り込んだ場合に、そういうことが時々あります。
まず全部の空白のセルにカーソルを合わせてDeleteキーを押してみてください。
スペースが入っている場合はそれで消せます。
それでダメなら、CLEAN関数を使って制御コードを消します。
空いている列に(仮にU列とすると)
U1に
=CLEAN(T1)
と入力してこれを最終行までコピー&ペーストし
このU列の式が入っているセルをコピーして、T列に貼り付けます。
このとき、右クリックで「形式を選択して貼り付け」で「値」を選択します。
T列の数式を消去したら新規シートと同じように計算できました。
T列には数式が入っています。その数式で求めた数値を計算したいのです。T列に数式が入っていては教えて頂いたものを使用することはできないのでしょうか?
よろしくお願いします。
No.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関数を使った方がいいです。
新規シートでは思う通りにできました。ですが集計したい数値があるシートでは#VALUE!とでます。同じにしているつもりですが片方では成功し、もう片方では失敗します。これはどこが至らないのでしょうか?
ご忠告いただけると有難いです。
No.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)の式が入っているセル)
この回答への補足
申し訳ありません。間違いました。
お礼の分の最後のあたりを修正致しました。
(誤)
仮に「あるセル」を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つずつずれています。
このように表示させたいです。
お答え頂きありがとうございます。わからないところがあるので教えてください。
教えて頂いた数式を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の割合)
このように表示させたいです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) Excelにの以下の設定方法について教えてください! C列にデータ入力の設定をしています。(出、入を 3 2022/06/22 01:33
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/01/18 17:23
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
excelで小数点0を表示させる方法
-
エクセルで0.0と表示したい
-
指定した範囲内のセルが全て入...
-
Googleスプレッドシートで合計...
-
【EXCEL】関数で計算された数字...
-
エクセルで同じ数字を入力・・...
-
Excelで合計の金額を違うセルに...
-
エクセルで089と数字を入れ...
-
エクセル 絶対値の合計
-
Excelで方眼紙を作り、そこに手...
-
ローマ字入力で「トゥ」を入力...
-
チケット何回もエラーは高額請求?
-
「未使用」と「不使用」ってど...
-
このような迷惑メールが一日に...
-
すべてのシートを選択してエク...
-
自動改札のエラーって…
-
西暦の2桁表記に使うアポスト...
-
至急お願いします!! Wordで、...
-
iMacキーボードの入力 小文字の...
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
excelで小数点0を表示させる方法
-
エクセル 絶対値の合計
-
エクセルで0.0と表示したい
-
【EXCEL】関数で計算された数字...
-
他シートのある列を検索して一...
-
vlookupで返された値が空白だっ...
-
エクセルの文字列を結合しても...
-
Excelで合計の金額を違うセルに...
-
セルの数式を計算させないで文...
-
エクセルで網掛けをしたセルを...
-
エクセル .50以下で切り捨て、...
-
エクセルで文字を180度回転...
-
Googleスプレッドシートで合計...
-
numbersで累計を計算するには
-
EXCEL VBA 選択範囲をTargetに
-
エクセル 背景色のついたセル位...
-
Excel VBAにてUserFormのTextBo...
-
Excelで小数点の位置を揃えるの...
-
EXCELで上位10個のデータを平均...
-
-EXCEL- 突然、関数が無効に...
おすすめ情報