質問投稿でgooポイントが当たるキャンペーン実施中!!>>

Excel2010で、Sumifs関数を使って、2つの条件に一致するセルの合計値を出そうとしています。

「条件範囲1」(A列)の範囲に、図のような結合セルが含まれており、
「条件範囲2」(B列)の「実績」に該当する数値を合計したい場合、
どのような数式を組めばよいでしょうか。

=SUMIFS($C$2:$C$7,$A$2:$A$7,B13,$B$2:$B$7,C13)

上記の数式の場合、「条件2」を「実績」にすると、
「条件範囲1」は結合セルの下側のセル「0」を参照してしまい、うまく計算できません。
条件範囲1にOFFSET関数を使用し、1行下を参照させようとしましたが、解決できませんでした。

わかる方、教えて下さい!

「Excel2010 sumifs関数で、」の質問画像

このQ&Aに関連する最新のQ&A

A 回答 (5件)

こんにちは!



No.2さんがおっしゃっているように集計などを行う場合、極力セルの結合は避けた方が良いと思います。
どうしてもセル結合があってもやりたいときは
No.1さんの回答のように「条件範囲」と「条件」の行をずらすことで可能になります。
ただ結合セルがもっと多い場合は間違いの元ですので、
↓の画像のように作業用の列を設けてみてはどうでしょうか?

画像では作業列D2セルに
=IF(A2="",D1,A2)
という数式を入れフィルハンドルで下へコピー!
H2セルに
=SUMIFS(C:C,D:D,F2,B:B,G2)
として下へコピーしています。m(_ _)m
「Excel2010 sumifs関数で、」の回答画像3
    • good
    • 0
この回答へのお礼

回答ありがとうございます。
ただ単にD列に同じデータを手入力するのではなく、IF関数でA列が空白データだった場合の入力を自動化しているのですね。勉強になります。
D列を非表示にすれば、見た目も綺麗になりますね。ありがとうございます!

お礼日時:2017/02/02 01:38

データは同じくA2~C7にあるとして、E2~に条件1をF2~に条件2を入力し、G2~に合計を計算するとします。


G2に
=SUMPRODUCT((A$2:A$7=E2)*1,(INDIRECT("B"&MATCH(F2,B:B,0)&":B"&COUNTA(B$1:B$7)+MATCH(F2,B:B,0)-2)=F2)*1,INDIRECT("C"&MATCH(F2,B:B,0)&":C"&COUNTA(B$1:B$7)+MATCH(F2,B:B,0)-2))
と入力し、必要なだけコピーしてください。
データの範囲は自分で変更してください。
内容としてはほぼ同じなのですが、1つの結合セルが3行以上にわたる場合、条件範囲1を上にずらすと範囲外になり選択できなくなるので、条件範囲2と合計対象範囲を下にずらすことにしました。

G2の説明として
A2~7でE2に一致し、
B列の条件2がB列で一致する一番上の行(予定なら2、実績なら3 以降①とします)~B列に入力されているデータ(1行目含む)の数(この場合7)+①-2(つまり予定の場合7+2-2=7、実績の場合7+3-2=8 以降②とします)行でF2に一致する
という条件に当てはまる行の、
C列の①~②に入っているデータを合計したものを表示します。

長いですね…
これで条件が増えたりしても、各データが同じ行数ずつ入っている限り大丈夫です。
予定・実績・X・予定・実績・X・予定…といった感じで。

範囲をずらす式作るのに、なかなか思った式で作れなくて苦労しました(汗)
    • good
    • 0
この回答へのお礼

お礼が遅くなりすみません。
やはり、範囲をずらすのが良いやり方のようですね。
ありがとうございます!

お礼日時:2017/03/05 09:11

表に計算を合わせるんじゃなくて 計算に表を合わせるべきかと思います。


自分で計算できないなら特に。

=SUMIFS(C2:C7,A1:A6,B13,B2:B7,C13)
    • good
    • 0
この回答へのお礼

回答ありがとうございます。1行ずらすやり方ですね。

>表に計算を合わせるんじゃなくて 計算に表を合わせるべきかと思います。

頭が固くてなかなかそのような考え方にたどり着けませんでした。
ありがとうございます。

お礼日時:2017/02/02 01:38

結合やめちゃったらいいんじゃないですかね?


集計に使う表で結合するとか意味がわからないです。

たまにあるんですけど、「上司の命(趣味)で仕方なく…」みたいな事情があるなら、
一旦結合解除→空白セルをうめる→別のセルで結合セルを作る→そのセルをコピー→実際に結合したいセルに書式のみ貼付

で、見た目は結合でもどのセルにもデータが入った状態になります。
    • good
    • 0
この回答へのお礼

回答ありがとうございます。
下記の方法は知らなかったです!こんな方法もあるのですね。

一旦結合解除→空白セルをうめる→別のセルで結合セルを作る→そのセルをコピー→実際に結合したいセルに書式のみ貼付

結合しないのが一番なのですが、見栄え上、結合したかったのです。
ありがとうございます。

お礼日時:2017/02/02 01:29

SUMIFS($C$2:$C$7,$A$1:$A$6,B13,$B$2:$B$7,C13)


とすれば実績のみの合計を表示することはできます。
ただし、この場合条件2がどちらか分かった上で式を選ばないといけません。
両方の場合に同じ式で計算できるようにするのならば、A列の範囲を指定するところで、条件2が条件範囲2の何番目か(この例では1か2だけですが)を判定し、それに応じてA列の指定範囲を変更させる必要があります。
中身がややこしくなるので問題なければ先に書いた方法をお勧めします。
必要なら後で書いた方法の式も書きますが。
    • good
    • 0
この回答へのお礼

1行目のやり方でうまくいきました!1行ずらすだけで解決できるなんて思っていませんでした。ありがとうございます。

今後、条件が変わることもあるので、判定の方法も教えていただけますか?
よろしくお願いします!

お礼日時:2017/02/02 01:38

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Qセルを結合した時のエクセル集計について

以前にも同じような質問をさせていただきましたが、応用が利かないので教えてください。
エクセルでセルの結合を使用した場合の集計の仕方についてです。
現在、以下の様に入力をしており、A1~A3、A4~A7、A8~A9をセル結合しています。


          A          B
1        α社          5
2                    6
3                    7
4        β社           5
5                    6
6                     7
7                     8
8        α社          5
9                      6

この場合にα社のB列の集計を行ないたいのですが、本来29となるところが、それぞれ一番上の行の数値のみを集計し10となってしまいます。29にするにはどの様な式を入力すればよいでしょうか。因みに現在の式は以下の通りです。
=SUMIF(A1:A9,"α社",B1:B9)

以上よろしくお願い致します。

以前にも同じような質問をさせていただきましたが、応用が利かないので教えてください。
エクセルでセルの結合を使用した場合の集計の仕方についてです。
現在、以下の様に入力をしており、A1~A3、A4~A7、A8~A9をセル結合しています。


          A          B
1        α社          5
2                    6
3                    7
4        β社           5
5                ...続きを読む

Aベストアンサー

こんばんは!
集計などを行う場合は極力セルの結合はしない方が良いと思います。

しかし、すでにセルの結合をされていらっしゃるようなので
かなり強引な方法ですが、↓の画像のように作業用の列を設けて各行に社名を表示すれば
SUMIF関数で対応できると思います。

画像の作業列C2セルに
=IF(A2="",INDEX($A$2:A2,MAX(IF($A$2:A2<>"",ROW($A$1:A1)))),A2)
これは配列数式になりますので、この画面からC2セルにコピー&ペーストしただけではちゃんと表示されないと思います。
C2セルに貼り付け後、F2キーを押す、またはC2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
このC2セルをオートフィルで下へコピーすると各行にそれぞれの社名が表示されますので
C列を利用してSUMIF関数を使ってはどうでしょうか?

=SUMIF(C:C,"α社",B:B)
という数式でOKだと思います。

以上、参考になれば良いのですが
他に良い方法があればごめんなさいね。m(__)m

こんばんは!
集計などを行う場合は極力セルの結合はしない方が良いと思います。

しかし、すでにセルの結合をされていらっしゃるようなので
かなり強引な方法ですが、↓の画像のように作業用の列を設けて各行に社名を表示すれば
SUMIF関数で対応できると思います。

画像の作業列C2セルに
=IF(A2="",INDEX($A$2:A2,MAX(IF($A$2:A2<>"",ROW($A$1:A1)))),A2)
これは配列数式になりますので、この画面からC2セルにコピー&ペーストしただけではちゃんと表示されないと思います。
C2セルに貼り付け後、F2キーを押す、...続きを読む

QEXCEL 結合されたセルを関数で参照する方法について

お世話になります。

EXCELにてDBとして入力されたものを必要な部分のみ抜き出したファイルを作成する為、
関数で元シートへのセル参照させるシートを作成したのですが
結合された部分の参照ができなく困っております。

<例>~~~~~~~~~~~~~~~~~~~~~~~~~
SheetA [A1~A10][A11~A20]が結合セル
SheetB [A1]に「='SheetA'!A1」 [A2]に「='SheetA'!A2」 [A3]に・・・[A20]まで同様
とさせるとA1とA11のみSheet1の値が反映されてA2~A10、A12~A20が出力されない。
~~~~~~~~~~~~~~~~~~~~~~~~~~~

A2~A10をA1とさせるとすればいいことですが
実際には数百件のデータ入力があり、結合部分も複数存在しています。
今後DBが増えていくことを考え、関数式セル右下ひっぱりの連続データ作成で対応させたいのですが、
SheetBでの A2 が "Null" であったら上方向へ検索し文字入力があった部分を引用させるといった
関数をくむことが出来ませんでした。

なにかいい知恵をご教示頂けましたら幸いと思います。

ことばではわかり難いかもしれませんのでJPG添付もさせていただきます。

お世話になります。

EXCELにてDBとして入力されたものを必要な部分のみ抜き出したファイルを作成する為、
関数で元シートへのセル参照させるシートを作成したのですが
結合された部分の参照ができなく困っております。

<例>~~~~~~~~~~~~~~~~~~~~~~~~~
SheetA [A1~A10][A11~A20]が結合セル
SheetB [A1]に「='SheetA'!A1」 [A2]に「='SheetA'!A2」 [A3]に・・・[A20]まで同様
とさせるとA1とA11のみSheet1の値が反映されてA2~A10、A12~A20が出力されない。
~~~~~~~...続きを読む

Aベストアンサー

添付図の場合のように、
E10:=IF(B2<>"",B2,OFFSET(E10,-1,0))
F10:=C2
E10:F10を選択して下にフィルドラッグではダメですか。

QSUMIFS関数でOR条件を使いたい場合の関数

Excel2010を使用しています。
家計簿を作成しているのですが、SUMIFS関数でOR条件を使用したい場合どのように書けばよいでしょうか。

図は、明細と合計欄です。
ピンクのセルが円、それ以外はドルです。
合計欄のように、円とドルをそれぞれ分けて項目毎の合計額を出したいです。
セルE21のように、円の場合は「MUFJ」「ゆうちょ」ですので、MUFJとゆうちょのSUMIFS関数合計値を足しています。
これを、1行にまとめたいのですが、適切な関数はないでしょうか?
SUMIFSではOR条件が使えないようですので・・・・・・

明細行に円・ドルの列を追加すれば簡単なのですが、明細行の列はこれ以上増やしたくないので、できれば数式のほうで処理できればと思っています。

よろしくお願いいたします。

Aベストアンサー

例示のデータなら以下のような数式になります。

=SUM(SUMIFS($C$2:$C$14,$D$2:$D$14,{"MUFJ","ゆうちょ"},$B$2:$B$14,B21))

Qexcel2010 行ごとにデータがある最後のセルの1行目の値を取得したい

歯抜けのデータがあります。1行ごとに最後の値が入っている列の1行目の値を取得しZの列(最終)にセットしたいのですが、できればvbaではなく関数でできないものでしょうか。
毎回列数も行数も若干フォーマットも違うのでセルにいれる関数のほうがありがたいです。
   A B C D ・・・ Z
1  あ い う え   最終 
2  10 5 7 9    え
3  8   2      う  
4    6        い
5  3          あ

Aベストアンサー

No.10の式の一部の再度の修正
私がNo.9で提示した式
 =IF(COUNTA($M2:$FU2),INDEX($M$1:$FU$1,MAX(INDEX(($M2:$FU2<>"")*(COLUMN($M2:$FU2)-COLUMN($L1)),0))),"")

COUNTAの範囲がA列からになっていました。
添付画像の数式バーのものも同じ間違いのままでした。
検証するときに修正するのを忘れていました。

QExcelで2つ1組の数値の内、1つの数値を別の数値とセットにする方法

①のシートのA列に型番、B列にシリアルが入っており、②のシートのA列に型番のみが入っています。
①と②の型番は完全一致しているものもあれば、型番の末尾が途切れてしまい完全一致しないものもありますし、数文字しか一致しないものもあります。
②の型番の隣に①のシリアルを入れたいのですが、何かうまく関数を使えば簡単にできるでしょうか?

型番をそれぞれ昇順にして貼り付けようとしたのですが、①と②の型番が完全一致ではないため、同じ並びにならず、そのまま貼り付けることができません。
何かいい方法があれば教えて下さい。
宜しくお願い致します。

例】
①シート
A列 B列
ABC 1234
PQR 2345
STU 5678
XYZ 1209
②シート
A列 B列
TU
XYS
PQR
BC

分かりづらかったらすみません。。。
宜しくお願い致します。

Aベストアンサー

部分的に重複しているような型番もあるのでしょうか?
ABCとABDという型番があり、②に欠けたABという型番があった場合、ABCかABDか判断する方法が無いと思います。
また、②のXYSというのはXYZが間違えて?入力されているような場合を想定していると思われますが、それもどの程度の一致で判断するのでしょう?
②にXYSとあって、①にXYZとEYSが存在した場合、どちらと判定すればよいか分からないと思います。

何らかの判断基準を決めたとして、
まずVLOOKUPで完全一致を探し、一致したもののシリアルを取得します。
フィルタを使い、エラー表示のものだけ表示し、式の内容を変更します。

判断基準の優先度が高いものから順に試します。
場合によっては①のA列とB列の間に列を挿入し、判定用の補助セルとして使います。
例えば、前から5文字一致したら同一とみなす。とした場合、
VLOOKUPとLEFTにより、左から5文字抽出したものが一致した場合にシリアルを取得します。
先ほど同様にフィルタでエラーのものだけ表示させ、また別の条件の式を入力します。

例えば今度は右から5文字一致したら、とかでしょうか。
あるいは②の文字が欠けているとして、②の全てを含むセル。という条件もあるでしょう。
この場合SUMPRODUCTやFIND、INDIRECT、ROW等を使い、一致するものがある列の行番号を取得する必要があると思われます。

データの数にもよりますが、残り少なくなれば判定式を考えるより目で見た方が早いかもしれません。

自分が思いついたのはこの程度です。
人間が条件を指定してあげないと機械も判別できないですよ。
もっと上手いやり方もあるかもしれないので、他の回答者に期待。

部分的に重複しているような型番もあるのでしょうか?
ABCとABDという型番があり、②に欠けたABという型番があった場合、ABCかABDか判断する方法が無いと思います。
また、②のXYSというのはXYZが間違えて?入力されているような場合を想定していると思われますが、それもどの程度の一致で判断するのでしょう?
②にXYSとあって、①にXYZとEYSが存在した場合、どちらと判定すればよいか分からないと思います。

何らかの判断基準を決めたとして、
まずVLOOKUPで完全一致を探し、一致したもののシリアルを取得します。
...続きを読む

QExcel関数について教えてください

指定する複数のセルに、指定する文字列を含む場合、指定するセルの数字を表示する方法を教えてください。

例)
A1のセルに"aaa"という文字列が含まれ、
さらに、B1のセルに"bbb"という文字列が含まれる場合、
C1の文字列を表示する

どなたかご存知の方、教えてください。
宜しくお願いします。

Aベストアンサー

説明が不足しているように思いますが 書かれている文章だけで判断すること
にします。

=IF(COUNTIFS(A1,"*aaa*",B1,"*bbb*"),C1,"")

Q=ROW()-11を本日知りました。

そこで、一行目を-1~カウントされ、二行目が0三行目が1となります。
一行目1からカウント出来ないでしょうか?
よろしくお願いいたします。

Aベストアンサー

https://www.keisan.nta.go.jp/h28/syotoku/ta_iryouhi_form_download.jsp?taxYear=16
こちらですかね?
書いているURLですとうまくダウンロードできなかったので。

保存したファイルをOneDriveで開こうとしたのですが、パスワードで保護されているため開けませんでした。と出てコピーもできなかったので実際には確かめられていません。

タイトル行が9行目なので、
A10=1,A11=2…としたいのであれば、
=ROW()-ROW(A$9)
とすればいいですよ。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

QCOUNTIFSについて教えてください!

COUNTIFSを使って、集計をしようと思っていますが上手くいかない点が2つあります。
下記のような形で入力しております。
=COUNTIFS(E$4:E$23,">=50",E$4:E$23,"<60")

①年齢集計をしていますが、58歳や52歳といった○○歳という単位をつけるとカウントしてくれません。やはり単位は省いて数字のみを入力する方法しかないでしょうか?

②別シートのセルより数字を反映させて表を作成しましたが、反映させた数字にはCOUNTIFSがカウントしてくれません。反映方法は =sheet2!A3&"" といった感じで行っています。
反映した数字ではカウントは不可能でしょうか?

助言のほど、よろしくお願いします。

Aベストアンサー

1.指定した「数値」では無いから当然です。
  ”58歳”という「文字列」ですよね。
 No.1でも回答がありますが、「セルの表示形式」を「ユーザー定義」にして「0歳」と指定することで、
 「52」と数字を入力するだけで「52歳」と表示されるようになります。

2.やはりこれも「文字列」になっています。
 参照している値が”58歳”のような「文字列」でも正しくカウントされません。
 「数値」になるように工夫しましょう。
 この場合は
 =Sheet2!A3
 だけで十分です。
 &""を付けることで「文字列」になってしまいます。
 (逆に *1 を付けると「文字列」の数字は「数値」になります。文字列の数字は四則演算で計算が可能で数値として扱われ四則演算の結果は「数値」が返ります)

Qエクセルで生年月日と現在の日付を入力し現在の年齢を表示したいのですが、どのような関数をいれてやれば良

エクセルで生年月日と現在の日付を入力し現在の年齢を表示したいのですが、どのような関数をいれてやれば良いのですか⁉
例えばA1に生年月日、B1に現在の日付 C1に現在の年齢 って感じにしたいのですが宜しくお願い致します。

Aベストアンサー

参考までに。
http://www.moug.net/tech/exopr/0090039.html


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング