エクセルSUMの計算を効率的に行う方法
- エクセルのSUM関数を使用して、一番右の列の数値を自動的に計算する方法について教えてください。
- 一括計算するために、SUM関数ではなく、動的な式を使用する方法があるか知りたいです。
- エクセルの表で、合計すべきセルの数が変化する場合に効率的な計算方法を教えてください。
- ベストアンサー
エクセルSUMの計算を効率的に行う
いろいろ自分で考えてみたのですが、やはり思いつかなくて、こちらに質問させていただきました。 添付のような表があるとします。 A,B,C、、、社とあり、それぞれに果物の名前が入っており、数が記載されています。 一番右の列にある数量は、A,B,C社それぞれのくだものの数の合計です。 この一番右の列のようなSUMの計算を自動的に行う関数を考えています。 いつも固定のセルのSUMでは簡単ですが、今回の場合、A社いくつ、B社いくつ、という果物の数は決まっておりません。つまり、合計すべきセルの数は変化する、ということです。 なので、考えたのは、一番左の列にA、B、C社と記載するので、その記載が出てくる前の行までを合計させるようにするしかないか、、など、案はあったのですが、実際にエクセル関数が思いつきません。 もしいい案がありましたら何卒お願いいたします。
- ami0607
- お礼率64% (159/245)
- Windows系OS
- 回答数8
- ありがとう数6
- みんなの回答 (8)
- 専門家の回答
質問者が選んだベストアンサー
回答番号:ANo.2を投稿した時点では動作確認が出来ていなかったのですが、SUMPRODUCT関数の代わりに、MATCH関数を使用した方が、数式が短くなります(=データが少なくて済む)し、処理速度も速くなります。 =IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&MATCH("゛",$A$1:$A1,-1)&":C"&ROW())),"") 例えば、この数式をD1セルに入力してから、D1セルをコピーして、D4セルに貼り付けると、 MATCH("゛",$A$1:$A1,-1) の部分は、 MATCH("゛",$A$1:$A4,-1) という具合に変換されますが、これは、MATCH関数の照合の型に「-1」を指定して、 「゛」(濁点のみ)という、A列に存在しない文字列が、 A1~A4の範囲の何番目に存在するのかを検索させると、 検索範囲内で、文字列データが存在するセルの中で、最も下にあるセルの位置(A1~A4の範囲では3)が返される という現象を利用しています。 この現象は、「色々試している内に発見したもの」で、何故そうなるのかは、私にも解りません。 又、検索する文字列は、検索範囲内のセルに存在しない(入力される筈がない)文字列であれば何でも構いません。 尚、上記の数式を、行と列のどちらを削除した場合にも対応する様に改良すると、次の様になります。 =IF(AND(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW()+1)=""),SUM(INDIRECT("R"&MATCH("゛",OFFSET(INDIRECT("R1C"&COLUMN($A:$A),FALSE),,,ROW()),-1)&"C"&COLUMN($C:$C)&":R"&ROW()&"C"&COLUMN($C:$C),FALSE)),"")
その他の回答 (7)
- kagakusuki
- ベストアンサー率51% (2610/5101)
>このCOUNTIF(OFFSET($A$1,,,4),"><")についてですが、 >私の理解では、A1から右にも左にも動かず4つだけ下に動く。 >なので、A5のセルのことを指すと思っていました。 それは違います。 OFFSET関数は、 OFFSET(基準,行数,列数[,高さ][,幅]) という形式です。 ですから、「A1から右にも左にも動かず4つだけ下に動く」数式は OFFSET($A$1,,4) という具合に、OFFSET関数内の2つ目の「,」の後ろに、「4」が来る数式になります。 OFFSET($A$1,,,4) は、2つ目ではなく、3つ目の「,」の後ろの所(セル範囲の高さを指定する箇所)に、「4」が来ていて、行方向の移動量を指定する1つ目の「,」の後ろの所と、列方向の移動量を指定する2つ目の「,」の後ろの所には、何も入力されていませんから、 この数式が表しているのは、A1セルに対して、行方向にも列方向にも移動しないセル、即ちA1セルを基準(セル範囲の中で最も左上にあるセル)として、下方向に4マスの高さ(縦幅)があるセル範囲になります。 尚、セル範囲の幅を指定する4つ目の「,」とその後ろの数値が省略されていますから、セル範囲の横幅は1マスのみになります。
お礼
そうでしたか。OFFSETは基準値からの’範囲’だったのですね。 基準値から動いたその位置(セルひとつ)と勘違いしておりました。 (もちろん使い方によってはそうなるのでしょうが) ありがとうございます。このことをふまえて、教えていただいた関数を今一度 見返してみたいと思います。 丁寧にありがとうございます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
>(1=2)とあるのですが、(このあとも1=2)(2=2) と続きますが、この前者の数字(=より前の数字)がどうやって出てきたのかがわかりません。 4番目の COUNTIF(OFFSET($A$1,,,ROW($A4)),"><") の箇所を例として説明させて頂きます。 ROW関数は括弧内で指定しているセルの行番号を返す関数です。 ですから、、ROW($A4)の括弧内にはA4セルが指定されていますから、ROW($A4)の表す値は4になります。 因みに、 ROW() という関数は、その数式が入力されている行の行番号を返します。 従って、 COUNTIF(OFFSET($A$1,,,ROW($A4)),"><") =COUNTIF(OFFSET($A$1,,,4),"><") という事になります。 OFFSET関数は、 OFFSET(基準,行数,列数[,高さ][,幅]) という形式ですから、 OFFSET($A$1,,,4) は、A1セルを先頭(1番左上のセル)とする、縦方向が4行に渡るセル範囲を示す関数です。(幅が指定されていないため、横幅は1列のみになります) 従って、 OFFSET($A$1,,,ROW($A4))=$A$1:$A4 COUNTIF(OFFSET($A$1,,,ROW($A4)),"><") =COUNTIF(OFFSET($A$1,,,4),"><") =COUNTIF($A$1:$A4,"><") という事になります。 $A$1:$A4の範囲で空欄ではないセルは、A1とA3の2つだけですから、 COUNTIF(OFFSET($A$1,,,ROW($A4)),"><") の値は2になります。 それから、申し訳御座いませんが、ANo.5で記した数式は、Excel2007よりも前のバージョンのExcelでは使えない事が判りました。 Excelにはネストという概念があるのですが、それは関数や括弧の中に、更に関数を入れ子にした数式を作成した場合において、関数や括弧が何重の入れ子になっているのかを表す数です。 例えば、 =COUNTIF($A$1:$A4,"><") は単純な関数ですから、ネストレベル1、 =COUNTIF(OFFSET($A$1,,,4),"><") はCOUNTIF関数の範囲を指定するのに、OFFSET関数を使用した複合的な関数ですから、ネストレベル2、 COUNTIF(OFFSET($A$1,,,ROW($A4)),"><") はCOUNTIF関数の中に、OFFSET関数があり、更にその中にROW関数がありますから、ネストレベル3になります。 Excel2007より前のバージョンでは、ネストレベル7までの関数しか扱っていないため、ネストレベルが8以上の関数は入力する事が出来ないのです。 そのため、Excel2007より前のバージョンでは、ANo.5で記した数式を入力する事が出来ません。(Excel2007以降のバージョンでは、ネストレベル64まで可能) ですから、ANo.6の数式を御使用下さい。
お礼
大変、お世話になります。ありがとうございました。 私はどうもOFFSET関数を理解していないのでしょうか。。今回ももう一度OFFSETをいろいろ勉強してみたのですが、こちらがまだ理解できません。 =COUNTIF(OFFSET($A$1,,,4),"><") =COUNTIF($A$1:$A4,"><") このCOUNTIF(OFFSET($A$1,,,4),"><")についてですが、 私の理解では、A1から右にも左にも動かず4つだけ下に動く。 なので、A5のセルのことを指すと思っていました。 たしかにOFFSETで範囲を指定できるのも勉強したのですが、この場合、幅にあたる数字がないことから、A5のセルのみを指すと考えてしまいました。 そして、いろいろ調べたのですが、やっぱりわかりません。 これはどう考えたらよいか、お手すきのときで良いので教えていただけませんか。
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答番号:ANo.2,3,4です。 >この部分’=IF(AND(ISNUMBER($C1),$C2=""),’ の、$C2のところを、動かないようにしたいです。 どういう意味かというと、列を削除したとき、他の部分は列を削除しても関数がずれずに保っているのですが、この$C2のところだけは#REFとなってしまいます。 仰る意味が良く解らないのですが、#REFエラーが出たという事は、参照先のセルが存在しないという事です。 $C2の所に#REFエラーが出たという事は、C2セルが削除されているという事になりますが、列ごと削除してC2セルが無くなるのは、C列を削除した場合のみです。 しかし、C列を削除したのであれば、$C1の部分も#REFエラーとなる筈ですが、#REFエラーが出ているのは、C2の所だけだと仰っておられます。 もしかすると、削除したのは列ではなく、行なのではないでしょうか? 取り敢えず、行と列のどちらを削除した場合にも対応する様に改良した数式を記しておきます。 =IF(AND(ISNUMBER(INDEX($C:$C,ROW())),INDEX($C:$C,ROW()+1)=""),SUM(INDIRECT("R"&SUMPRODUCT(ROW(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE))*(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE)<>"")*(COUNTIF(OFFSET(INDEX($A:$A,1),,,ROW(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE))),"><")=COUNTIF(INDIRECT("R1C"&COLUMN($A:$A)&":R"&ROW()&"C"&COLUMN($A:$A),FALSE),"><")))&"C"&COLUMN($C:$C)&":R"&ROW()&"C"&COLUMN($C:$C),FALSE)),"") 尚、上記の数式では、列の削除や挿入に対応するためにINDIRECT関数の中の参照形式をR1C1形式にしています。 又、1行目が削除される場合に備えて、OFFSET関数によらない対処をしています。
お礼
そうです。大変申し訳ありません。行のことでした。下記間違えました。 行の削除はすることが多いので、OFFSET関数を入れてみたのですが、 なぜか上記のようなエラーになってしまい。。 (今までこういうことがあればOFFSETを使ってうまくいっていたのですが、 他の部分が難しくてOFFSETがうまく入れられていなかったせいか、どうやってもエラーになってしまいました) 教えていただいたもので、また、やってみたいと思います。
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答番号:ANo.3の続きです。 SUMPRODUCT関数は本来は配列の積の和を計算するための関数なのですが、これを応用しますと、 SUMPRODUCT((判定式1)*(判定式2)*セル範囲) という様な形式とする事で、複数条件に該当するセルの値のみの合計値を求める事も出来ます。 例えば、 =SUMPRODUCT((A2:A5=3)*(B3:B6>0)*C2:C5) という数式は、パソコンの内部で =(A2=3)*(B2>0)*C2 +(A3=3)*(B3>0)*C3 +(A4=3)*(B4>0)*C4 +(A5=3)*(B5>0)*C5 という計算が行われています。 尚、 (A2=3) という部分は、 A2セルの値が3である場合には、数値の1と同様に扱われ、 A2セルの値が3ではない場合には、数値の0と同様に扱われます。 SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><"))) という数式は、それを更に応用したものです。 例えば、D1セルをコピーして、D4セルに貼り付けると、その部分は SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><"))) という様に変換されますが、これは ROW($A1)*($A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A1)),"><")=COUNTIF($A$1:$A1,"><")) + ROW($A2)*($A2<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A2)),"><")=COUNTIF($A$1:$A2,"><")) + ROW($A3)*($A3<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A3)),"><")=COUNTIF($A$1:$A3,"><")) + ROW($A4)*($A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A4)),"><")=COUNTIF($A$1:$A4,"><")) と同じ意味になりますから、 =1*("A社"<>"")*(1=2) + 2*(""<>"")*(1=2) + 3*("B社"<>"")*(2=2) + 4*(""<>"")*(2=2) =1*1*0+2*0*0+3*1*1+4*0*1 =3 という計算が行われます。 文章で説明しますと、 A1~A4のセル範囲で、 A列のセルが空欄ではなく、 且つ A1セルからそのセルまでの範囲にある、空欄ではないセルの個数が、A1~A4の範囲にある空欄ではないセルの個数と等しい という条件を満たすセルが存在する行の行番号を合計する という数式です。 この条件を満たすのは、A1からそのセルまでの範囲にある、空欄ではないセルの個数が、2となる最初の行である3行目のみであるため、計算結果は3行目の行番号である3になります。 ROW() の値は、その数式が入力されている行の行番号になりますから、 INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A4)*($A$1:$A4<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A4)),"><")=COUNTIF($A$1:$A4,"><")))&":C"&ROW()) =INDIRECT("C"&3&":C"&4) =INDIRECT("C3:C4") =$C3:$C4 という事になります。
補足
どうもありがとうございます。 申し訳ありません。ここでつまづいてしまいました。 =1*("A社"<>"")*(1=2) + 2*(""<>"")*(1=2) +................. のところです。 (1=2)とあるのですが、(このあとも1=2)(2=2) と続きますが、この前者の数字(=より前の数字)がどうやって出てきたのかがわかりません。申し訳ありません。 もう少し考えてみますが、もしこちらの質問に気づかれましたら教えてください。
- kagakusuki
- ベストアンサー率51% (2610/5101)
回答番号:ANo.2です。 >ざっくりでいいので中身(意味)を教えていただけないでしょうか。 =IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())),"") の中の ISNUMBER($C1) の部分は、C1セルに数値データが入っているか否かを判定する関数です。(数値の場合は成り立っていると見做す) ですから、 =IF(AND(ISNUMBER($C1),$C2=""),真の場合,偽の場合) という形式の関数は、C1セルに数値データが入っている事と、C2セルが空欄である事の、 両方がともに成り立っている場合には、真の場合の部分に入力した値を表示し、 どちらか一方でも成り立っていない場合には、偽の場合の部分に入力した値を表示する という関数になります。 D1セルの数式では、偽の場合の部分が "" となっていますから、数式が入力されているセルがあるのと同じ行の、C列のセルに数値が入力されていて、尚且つ、1つ下の行のC列のセルには、何も入力されていない場合には、真の場合の部分に入力した数式によってきまる値が表示され、 それ以外の場合には、何も表示しない という数式になります。 そして、真の場合の部分に入っている SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())) という部分ですが、SUM( )は括弧内に入力されているセル範囲内の数値を合計する関数です。 INDIRECT( )は、括弧内に入力されている文字列と同じ名前のセル、又はセル範囲を参照する関数です。 例えば、 =INDIRECT("Sheet1!A"&B1+C1) という数式があって、B1セルに2、C1セルに3が入力されていた場合には、括弧内の文字列は Sheet1!A という文字列の後に、2と3を足し合わせた数値である 5 を結合した Sheet1!A5 という文字列になりますから、Sheet1!A5に入力されているデータを参照する数式になります。 =Sheet1!A5 と同様に思えるかも知れませんが、B1セルやC1セルの値によって、参照するセルを変更する事が出来ます。(OFFSETとは違って、参照するSheetも変える事が可能) ですから、 INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW()) という数式の、INDIRECT( )で囲まれた部分は "C"& という部分と SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><"))) と &":C"& と ROW() という部分から成り立っていますから、 C列における SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><"))) の計算結果と同じ数の行番号から、 ROW() の結果と同じ数の行番号まで のセル範囲を表します。 そろそろ、回答欄の入力可能な文字数の限界を超えるため、このサイトの規則には少々反しますが、残りは後で投稿させて頂きます。
お礼
こんなにたくさん教えてくださって(この後の投稿も含め)ありがとうございます! このあとじっくり読んでみます。また、お礼と併せてご連絡させていただきます。
- kagakusuki
- ベストアンサー率51% (2610/5101)
列を追加しなくても良い方法です。 今仮に、「A社」と入力されているセルがA1セル、「27」と入力されているセルがD13セルだとします。 まず、D1セルに次の数式を入力して下さい。 =IF(AND(ISNUMBER($C1),$C2=""),SUM(INDIRECT("C"&SUMPRODUCT(ROW($A$1:$A1)*($A$1:$A1<>"")*(COUNTIF(OFFSET($A$1,,,ROW($A$1:$A1)),"><")=COUNTIF($A$1:$A1,"><")))&":C"&ROW())),"") 次に、D1セルをコピーして、D2以下に貼り付けて下さい。 以上です。
お礼
すみません、あとひとつ教えていただきたいことがあり、こちらに記載しました。 こちらの関数ですが、はじめのこの部分’=IF(AND(ISNUMBER($C1),$C2=""),’ の、$C2のところを、動かないようにしたいです。 どういう意味かというと、列を削除したとき、他の部分は列を削除しても関数がずれずに保っているのですが、この$C2のところだけは#REFとなってしまいます。 なので、OFFSET関数で、いつもこのC2の部分を参照するようにしようと思ったのですが、他の部分が難しいですが、うまくOFFSET関数が入ってくれません。 ご教授いただけないでしょうか。
補足
ありがとうございます!すごいです。 ただ、自分でどういう仕組みか調べてみましたが、やっぱり途中でわからなくなりました。今後の参考に、ざっくりでいいので中身(意味)を教えていただけないでしょうか。
- acha51
- ベストアンサー率41% (436/1042)
B列を追加A列をコピーして必要分ドラッグしてコピー、 (B列は印刷時は表示⇒表示しないで隠す) E1に=sumif(b$1:b$14,A1,D$1:D$14) E3に=sumif(b$1:b$14,A3,D$1:D$14) E6に=sumif(b$1:b$14,A6,D$1:D$14) です
関連するQ&A
- エクセル2003 計算されないようにしたい
例 A列 B列 1 =A1+A2 2 =A2+A3 3 =A3+A4 (A4は空白です) となっている場合に B1=3 B2=5 B3=3 になると思うんですが この時に空白のセルと計算結果を出すときは0もしくは 計算されないようにしたいです。 最終的にB列にSUM関数を使って合計を出したいのですが A列に求めたい数字が片方しか入ってない場合 (数字が入っているセル+空白セル)はSUM関数の合計に入れたくないです。 なので上記の例では B列にSUM関数をした場合11ではなく8にしたいです。 それには文字列を入力して#VALUE!にするしかないですか? でもその状態にしてSUM関数で合計を出したときも#VALUE!になってしまいます。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- Excel2003でif関数を使うとSUM関数で反映されません
A1セルに金額を入力すると、B1に1と表示させる為に=IF(A1,"1","")といった数式を入れております。 さらにC1にBセルの合計値を表示させる為に=SUM(B1)C2には=SUM(B1:B2)と入力しているのですが、B列のセルにIF関数を使った数字『1』が表示されてもC列セルに反映されないので困っています。 ここで質問ですが、A列セルに金額が入力されるとB列セルに数字『1』が表示され、更にC列セルにB列セルの合計値が表示される様な関数等はありますでしょうか?解り難い説明ですいません。 因みにA、B、C列共に1~31までのセルがあります。
- ベストアンサー
- オフィス系ソフト
- エクセルの計算で・・・
A列に50個ほどの商品の個数 B列にその単価 があります。 A列×B列の全体の答えの合計をC1に入れたいのです。 普通は、A1×B1をC1に入れてC51にSUM するのが簡単なんですが、依頼者の都合上できないのです。 関数1つでC1に合計が入りませんかね~。 この説明で意味お分かりでしょうか? もしお分かりの方がいらっしゃいましたら お助けください。
- ベストアンサー
- オフィス系ソフト
- エクセル イレギュラーなSUM関数
会社で使うエクセル表を作っていますが困っています。 A列B列があり A5にはA1~A4の合計のSUM関数が入っています。 B5にもSUM関数を入れたいのですが B1~B4はイレギュラーな事があった場合のみ数字を入力し B5にはB1~B4に入力された数字の合計と B列に何も入力されていない隣のA列の合計を表示させたいのです。 例えるなら A1~A4に3を入力 A5はSUM関数がはいっているので12が表示されているとして B列はB1~B3は未入力 B4に7が入力された場合 B5にはA1~A3とB4の合計の16が表示されるようにしたいのです。 B4に7を入力したので隣のA4の3は合計しないという事です。 どうぞ宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- SUM関数の範囲に#N/Aがあっても合計してくれる計算式
A列に氏名、B列に金額(計算式が入っている)があります。 B列の最終行に合計金額を出したいのですが、 B列に、#N/Aとなるセルがあり、 SUM関数を使うと結果がエラーになります。 なんとかエラー値のセルは無視して合計金額を出す計算式は ないでしょうか。 よろしく御指導下さい。
- ベストアンサー
- オフィス系ソフト
- excelの計算式のコピーが出来なくなった。
Excelで表を作成して、A列の下部にSUMで合計を出して、選択をして 右にドラッグして、B,C,D,E,の合計を出していたのですが、 突然、計算式のコピーが出来なくなり、A列の合計が そのまま B,C,D,Eにコピーされるのです。 B列に移動したときはB列のSUMとなっているのですが、確定しますとAの値になります。 理解できなくて? 助けてください。
- ベストアンサー
- その他MS Office製品
- こんな関数お願いします
こんな関数お願いします ドングリを拾った数の合計 Excel2003です、A列 月日 B列 氏名 C列 数量 行は2~60 氏名はA君B君C君D君です、 シート2のA列に氏名 B列に合計数量です。 B列の合計数量セルの関数式をお願いします、 シート1に記入するとシート2のB列に合計数量が出る方法A君でお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル(excel)の計算式(関数)について
エクセル(excel)の計算式(関数)でよいアイディアがありましたら教えてください。 1行目は項目行です。 セルA1から右に15列=セルO1まで、 a | b | c | d | e | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 と入っています。 2行目からデータとして、 セルA2から右に5列=セルE2まで、 4 | 2 | 2 | 1 | 1 と入力したとします。(データ例(1)) あるいは、 セルA3から右にセルE3まで、 6 | 3 | 0 | 0 | 0 と入力したとします。(データ例(2)) 1つのデータの5個の数字のルールは2つで、 「合計で10以下である。」 「左から順に小さくなるか、同じ数字となる。」 です。 (目的は、) このとき、F列からO列にかけて、 データ例(1)のケースでは、 a | a | a | a | b | b | c | c | d | e データ例(2)のケースでは、 a | a | a | a | a | a | b | b | b | と表示されるように、 つまり、項目行の下にある数だけ、その列の1行目の記号を 1(F列)から右に向かって順に埋めていくような、 F列からO列までの2行目以下に入れる適当な計算式(関数)は ないでしょうか。 拙い説明で申し訳ありません。どなたかよい考えをお持ちの方がいらっしゃいましたらと存じます。 どうぞよろしくお願い致します。
- ベストアンサー
- その他MS Office製品
- 続 エクセル イレギュラーなSUM関数
先程こちらでhttp://oshiete1.goo.ne.jp/qa5428194.html イレギュラーなSUM関数を質問しお答えいただきましたが 新たに問題が出ましたのでもう一度質問いたします *さきほどの質問の内容はこちらです* A列B列があり A5にはA1~A4の合計のSUM関数が入っています。 B5にもSUM関数を入れたいのですが B1~B4はイレギュラーな事があった場合のみ数字を入力し B5にはB1~B4に入力された数字の合計と B列に何も入力されていない隣のA列の合計を表示させたいのです。 例えるなら A1~A4に3を入力 A5はSUM関数がはいっているので12が表示されているとして B列はB1~B3は未入力 B4に7が入力された場合 B5にはA1~A3とB4の合計の16が表示されるようにしたいのです。 B4に7を入力したので隣のA4の3は合計しないという事です。 *回答はこちらです* 回答1 配列関数で =SUM(IF(B1:B4="",A1:A4,B1:B4)) と入力して、Ctrl+Shift+Enterで決定したら、式が{}でくくられて配列関数になります。 式の意味は B1~B4が空白の場合は、A1~A4の値を、それ以外はB1~B4の値を出して合計 回答2 =SUMPRODUCT(NOT(B1:B4)*A1:A4+B1:B4) あたりですかね。not関数のかわりにisblank関数の方が判りやすい かもしれないけど。 TRUE/FALSEの論理値は、四則演算にぶち込むと1/0の数値として扱 われます。また、空は四則演算にぶち込むと0の数値として扱われ ます。だから、「B列が空である」がFALSEならA列とのかけ算はゼ ロになりB列の値が加算され、TRUEならA列の値に1をかけて0を足し たことになります。後はそれを1行目から4行目にわたって配列とし て計算して合計してくれるsumproduct関数に放り込むだけ。 問題はこの方法だと B列に何も入力しない場合、A列の合計がB5に出て来てしまいます。 B列に何も入力しない場合、B5にも何も入力しないようにしたいのです。 ちなみに先程は記述しませんでしたが B列には自動で数字に〔〕が付くようにユーザー定義で指示していて B5はB列が未入力で合計が0でも〔0〕と表示されないように ユーザー定義で "〔"#"〕";;;が入っていてます。 やりたい事をまとめると ●B列には自動で数字に〔〕を付ける ●B1~B4に数字が入力された場合はB5に B1~B4の合計とB列に何も入力されていない隣のA列の合計を表示させたい がB列が未入力ならB5に何も表示しない ややこしくて申し訳ありません。 そして確認不足で申し訳ありませんでした。 どうぞ宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 条件付のsum,max,min関数の書き方
sum関数なので、合計欄のセルに ex.=SUM(B1:B6)などと書くときに、 a列が"1"の場合のみ合計欄のセルに加算したい場合は どうやって書けばいいでしょうか? __ A B 1 1 100 2 0 200 3 1 300 4 0 400 5 1 500 6 0 600 合計欄の値=900にしたいのですが.... もしも書き方があるのであれば、 MAX,MINの場合も同じでしょうか?
- ベストアンサー
- オフィス系ソフト
お礼
R1C1形式ですね。やっとわかりました。それでもまだ全部は意味がわかっていいないのですが、、(すみません) でも、大まかなところはわかりました。 このたびは大変丁寧に教えていただきありがとうございました。 また、お礼が遅くなり申し訳ございませんでした。
補足
まことにありがとうございます。 こちらの中に出てくる ”R"とは何でしょうか?