お世話になっております。

図表のように、入金額データ表から月間ごとに集計したものです。
SUM関数では問題ありませんが、SUMIFS関数を使うと「#VALUE!」エラーが出ます。

=SUMIFS(C$6:C$505,$C$5:$BZ$5,"="&C$1)
「#VALUE!」

どなたか教えてください。m(__)m



※補足説明

・「年月」の項目の表示形式は、年頭の「2017/1/1」と入力してユーザー定義で
「yyyy"年"m"月"」と設定し
 2月以降は、「2017/2/1」と打ち込み、それをユーザー定義「m"月"」で設定
 シリアル値
・C列のデータ件数 500件 $C$6:$C$505

「月間集計 SUMIFS関数」の質問画像

A 回答 (1件)

参照する範囲が不適切です。



合計する範囲を6行から505行までとしているのに、その条件がC列からBZ列なのがいけない。
行と列を一致させなければエラーになるのは当然です。

この場合、
 =SUM(D6:D505)
でそのまま合計が求まると思うんですけどいかがでしょう。
    • good
    • 1
この回答へのお礼

>参照する範囲が不適切です。

>合計する範囲を6行から505行までとしているのに、その条件がC列からBZ列なのがいけない>行と列を一致させなければエラーになるのは当然です。

エラーになる理由がわかりました。

>=SUM(D6:D505)

この方法ですることに致しました。

銀鱗さま、教えてくださりありがとうございました。
またの機会がありましたら
ご教授お願い致します。m(__)m

お礼日時:2017/06/14 12:59

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

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

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

Q「年月」ごとにカウント

お世話になっております。

C列には、『初回入会年月日』として入力しております。
E列から右方向へ、「2017年1月」を始めとし「年月」を表記しております。
この「年月」ごとの欄に、C列に列記している「初回入会年月日」を検索して
該当する「年月」の件数を表示させる仕組みです。

例えば、図表から
C列の「初回入会年月日」には、

「2017年1月17日」
「2017年1月19日」
「2017年1月17日」
「2017年1月31日」

1月には4件あり、それをE2に、「4」と表示させるといった感じです。

C列の「初回入会年月日」を検索し、該当する「年月」の件数を表示させる関数式を教えてください。
m(__)m

※補足説明
・C列の表示形式は、「2017/○/○」と打込み、ユーザー定義「yyyy"年"m"月"d"日"」で設定しております。
・「年月」の項目の表示形式は、年頭の「2017/1/1」と入力してユーザー定義で
「yyyy"年"m"月"」と設定し、
 2月以降は、「2017/2/1」と打ち込み、それをユーザー定義「m"月"」で設定
・C列のデータ件数 500件 $C$2:$C$501

お世話になっております。

C列には、『初回入会年月日』として入力しております。
E列から右方向へ、「2017年1月」を始めとし「年月」を表記しております。
この「年月」ごとの欄に、C列に列記している「初回入会年月日」を検索して
該当する「年月」の件数を表示させる仕組みです。

例えば、図表から
C列の「初回入会年月日」には、

「2017年1月17日」
「2017年1月19日」
「2017年1月17日」
「2017年1月31日」

1月には4件あり、それをE2に、「4」と表示させるといった感じです。

C列の「初...続きを読む

Aベストアンサー

No.2・3です。

まずNo.2の方に関して・・・
SUMPRODUCT関数の範囲内に「文字列」が含まれているというコトはないですか?
文字列が含まれている場合はエラーになります。

No.3に関して・・・
C列と1行目の日付はどちらもシリアル値になっているのですよね?
そして1行目の日付はすべて各月の1日のシリアル値だというコトなのであのような数式にしました。

お示しの画像通りの表を手元のExcelで確認し、投稿した数式です。

こちらではE2セルにちゃんと「4」が表示されています。

※ 502行目以降に数値データがあるととんでもない結果が返るコトがあります。
No.3の数式の範囲を501行目までに限定し
=COUNTIFS($C2:$C501,">="&E1,$C2:$C501,"<"&EDATE(E1,1))

としたらどうなりますか?m(_ _)m

Qexcelで条件に合うよう、複数のセルの合計を求めたい

例えば、次のように並んでいるセルの数値があるとします。

1515
2748
540
5509
2195
680
7142
305
5042
530
667
325
9950
4800

その合計が30000以上で、かつ、最小の数字となるよう、複数のセルを選択したいと思いますが、これを実現できる関数はありますか?

Aベストアンサー

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積和

で、全16384通りの計算が行われるので、A4:末尾を選択して並べ替えすると、
合計が30002となる、
1515
2748
2195
680
7142
305
667
9950
4800
が確認できるとか。

--
もっと数値の数が増えると、この方法では厳しいので、

ナップザック問題をExcelで解く
http://www.geocities.co.jp/SiliconValley-Oakland/8139/

みたいなプログラムで解くような事になります。


条件が違うのでプログラムはそのまま使えませんが、似た質問。

エクセルで、「袋詰め問題」を解きたい - Excel(エクセル) 解決済 | 教えて!goo
https://oshiete.goo.ne.jp/qa/1255891.html

いわゆる「ナップザック問題」と呼ばれる種類の問題になると思います。

14個を選択する/しないなら、総当りしても16384個ですから、力技でも行けるかも。
画像を参考に、
1行目にデータの数値を横並び。
2行目に0~13の固定値を右から
3行目に2の0乗~2の13乗(8192)の固定値を右から
A列に0~16383の固定値
で、

B4:O16387の範囲に、
B4:=MOD(INT($A4/B$3),2)
をコピペして2進数の各桁の値を

P4:P16387の範囲に、
P4:=SUMPRODUCT($B$1:$O$1,B4:O4)
をコピペして、2進数の各桁のビットとデータの数値の積...続きを読む

Qエクセルで特定の数字だけ勝手に変換されてしまいます

エクセル2010で48,609.793と入力するのですが
入力後のセルを見てみると48,609.792999999と
勝手に変換されてしまいます。
48,609.792や48,609.794ではそのままなのですが
なぜなのでしょうか?
計算上で入力した数値を使用したく困っています。

Aベストアンサー

10進数をコンピュータ内部では2進数の形で保持することによる誤差だそうです。
なるべくこの誤差が出ないようにExcelは工夫されているそうですが、
特定の数字を入力すると「99999…」が現れるのだとか。
この質問を見て調べるまで全然知りませんでした。
https://answers.microsoft.com/ja-jp/msoffice/forum/msoffice_excel-mso_other/excel/bbaaa7f0-5e73-4d7c-aa05-6709a3357d26
https://support.microsoft.com/ja-jp/help/813530

計算式には数字を丸める関数を入れると対処できると思います。
ROUNDやROUNDUP関数で引数を3にしてみてください。
http://kokoro.kir.jp/excel/round.html

QEXCEL関数、別シートを参照して値を返したい。。

計算式をセルH3~H14に入れたいです。

セルE3にロジックパターンを1~5まで入力。

関数でセルH3~H14の順位を出したいのですが、関数がわかりません。
セルH3~H14まで同じロジックパターンで順位を出します。

H3の場合、ロジックパターンは「1」、サインNoはG3にあり「7」
ロジックパターンsheetのB列を参照すると、「7」はセルB8にあり、
横にスライドしてA列の順位をみると「5位」です。

この5位という結果をセルH3に出るようにしたいのです。
サインNOは変動します。
(前回の質問を見ていただけるとわかるのですが、G3~G15にも関数が入ってます)

そして、隣のセルに、
1位~4位は★★★
5位~8位は★★
9位~12位は★  の表示をしたいのですが、どうしたらいいでしょうか?

Aベストアンサー

H3にはコレを
=MATCH($G3,OFFSET(ロジックパターン!$A$4,,$E$3,12,),0)

I3にはコレを
=LEFT("★★★",4-ROUNDUP(H3/4,0))

J3にはコレを
=MATCH($G3,OFFSET(ロジックパターン!$A$4,,$E$4,12,),0)

MATCH関数でターゲットとなるG3の数値をロジックパターンから探し、見つかったセルの高さを表示しています。
高さが5のため5位でよいのかと思います。5位と表示したいようであれば表示形式を変えたりCONCATENATEを使ったりしてください。
検索範囲が可変となるため、OFFSETで列をずらしています。

QEXCEL関数、判別?

12星座の順位付けをしたいです。
まず、基点となる星座が、日によって変わります。
1牡羊座
2牡牛座
3双子座
4蟹座
5獅子
6乙女座
7天秤座
8蠍座
9射手座
10山羊座
11水瓶座
12魚座
の順の日もあれば、

1牡牛座
2双子座
3蟹座
4獅子
5乙女座
6天秤座
7蠍座
8射手座
9山羊座
10水瓶座
11魚座
12牡羊座

の日もあります。
牡羊座を基点とする日は、魚座は12番目、牡牛座を基点とする日は、魚座は11番目です。
基点となる星座が1、その次の星座が2となり、以降順繰りに回すだけなんですが
いちいちコピーしたりずらしたりするのに疲れました。

基点星座を選んだら、自動的に判別してくれる関数はないでしょうか?
ちなみに、今作ってるファイルの一部を添付します。

どうぞよろしくお願いいたします。

Aベストアンサー

>基点星座を選んだら、自動的に判別してくれる関数はないでしょうか?
セルG3に =INDEX(Sheet2!$A$2:$L$13,ROW(A2)-1,MATCH($B$3,Sheet2!$A$1:$L$1,0)) の式を入れて、下方向まで式をオートフィルコピーします。
一応あなたが示された画像をそのまま作成し、(Sheet2も)実際やってみましたのでお試しください。

※セルB3の起点星座をいちいち手入力しているのでしたら、次の方法でドロップダウン出来るようにしてみると、もう少し楽になるかも。
ドロップダウンの作り方。
①セルB3をクリックしておきます
②「データ」タブから「データツールグループ」内の「データの入力規則」と書いてあるところの上のアイコンをクリック
③「設定」タブより「入力値の種類」の「すべての値」の右側の▼をクリックして「リスト」を選択
④「元の値」の枠内をクリックしてSheet1の12星座を入力している範囲を選択(F3~F14まで)
⑤選択し終わったら「OK」を押します。
これでセルB3をクリックすると▼の表示が現れドロップダウンで各星座を選択できるようになります。
星座をいろいろ選択することで、順番も自動で変わります。

>基点星座を選んだら、自動的に判別してくれる関数はないでしょうか?
セルG3に =INDEX(Sheet2!$A$2:$L$13,ROW(A2)-1,MATCH($B$3,Sheet2!$A$1:$L$1,0)) の式を入れて、下方向まで式をオートフィルコピーします。
一応あなたが示された画像をそのまま作成し、(Sheet2も)実際やってみましたのでお試しください。

※セルB3の起点星座をいちいち手入力しているのでしたら、次の方法でドロップダウン出来るようにしてみると、もう少し楽になるかも。
ドロップダウンの作り方。
①セルB3をクリックしておきます
②...続きを読む

Qエクセル 関数 教えてください!

添付画像のようにしたいのですが、エクセルでそんなことが出来るのか分かりません。
賢者の皆様、知恵をお貸しください。

詳細はこんな感じです。

①②③は条件付書式ということはなんとなく分かります!
僕なりに一生懸命悩んでしましたが、浅知恵のためお手上げ状態です。。

①=予想と結果が1番で一致したときに、予想列の該当セルを黄色に塗りつぶす
②=予想と結果が2番で一致したときに、予想列の該当セルを赤色に塗りつぶす
③=予想と結果が3番で一致したときに、予想列の該当セルを緑色に塗りつぶす
④=予想A、予想B合計の統計点数を出す(1番予想が3点、2番予想は2点、3番予想は1点)
⑤=④を数字の大きいもの順に抽出する(同じ値は表示順で抽出)
⑥=1番予想が予想通りになった確率を求める
⑦=2番予想が予想通りになった確率を求める
⑧=3番予想が予想通りになった確率を求める

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

Aベストアンサー

こんばんは!

①~③は大丈夫というコトなので・・・

④~⑤についてだけ
まず④は
↓の画像のように作業用の表を作成するのが簡単だと思います。
「作業表1」は1番~3番の「得点?」の表を作成しておきます。
K2セルに
=IF(B9="",0,VLOOKUP(B9,$O$2:$P$4,2,0))+IF(F9="",0,VLOOKUP(F9,$O$2:$P$4,2,0))

という数式を入れ列・行方向にフィルハンドルでコピー!

⑤について
「作業表2」のO11セルに
=COUNTIF(K$2:K$8,">"&K2)+COUNTIF(K$2:K2,K2)

という数式を入れフィルハンドルで右へ3列分・下へ7行コピーしておきます。
そしてK11セルに
=INDEX($J$2:$J$8,MATCH(ROW(A1),O$11:O$17,0))

という数式を入れ列・行方向にフィル&コピー!
これで画像のような感じになります。

※ 作業用の表が目障りであれば遠く離れた列にするか
非表示にしておいてください。

⑥~⑧については
0%か100%のどちらかになってしまいますよね?
それでも良いのであれば・・・
B17セル(%表示にしておく)に
=(VLOOKUP($A17,$A$3:$D$5,COLUMN(),0)=INDEX($A$9:$A$15,MATCH($A17,B$9:B$15,0)))*1

という数式を入れフィルハンドルで列・行方向にコピー!

B17セルを選択 → B17セルの四辺にマウスポインタを移動させ上下左右の小さな矢印になったところで
Ctrlキーを押しながらF17セルまでドラッグ&ドロップ
数式内の「COLUMN関数」だけに手を加え
=(VLOOKUP($A17,$A$3:$D$5,COLUMN(B1),0)=INDEX($A$9:$A$15,MATCH($A17,F$9:F$15,0)))*1

という数式にして、これも列・行方向にフィル&コピー!

こんな感じではどうでしょうか?m(_ _)m

こんばんは!

①~③は大丈夫というコトなので・・・

④~⑤についてだけ
まず④は
↓の画像のように作業用の表を作成するのが簡単だと思います。
「作業表1」は1番~3番の「得点?」の表を作成しておきます。
K2セルに
=IF(B9="",0,VLOOKUP(B9,$O$2:$P$4,2,0))+IF(F9="",0,VLOOKUP(F9,$O$2:$P$4,2,0))

という数式を入れ列・行方向にフィルハンドルでコピー!

⑤について
「作業表2」のO11セルに
=COUNTIF(K$2:K$8,">"&K2)+COUNTIF(K$2:K2,K2)

という数式を入れフィルハンドルで右へ3列分・下へ7行コピーしておきま...続きを読む

QExcelで名前の7文字組みの方法を教えてください

Excelデータで名前をスペースで入れて7文字組みに変換させたいのですが、
関数またはVBAなどの方法を教えていただけませんでしょうか?
今は関数で個別でMID関数を使い分けてしています。

例) 
林一    → 林     一     田中一   → 田 中   一
林太郎   → 林   太 郎     田中太郎  → 田 中 太 郎
林孝太郎  → 林   孝太郎     田中孝太郎 → 田 中 孝太郎

佐々木一  → 佐々木   一
佐々木太郎 → 佐々木 太 郎
佐々木孝太郎→ 佐々木 孝太郎

Aベストアンサー

板汚しすみません。まだ半角スペース残ってた
=CHOOSE(LEN(A2),,LEFT(A2,1)&REPT(" ",5)&RIGHT(A2,1),
REPLACE(LEFT(A2,B2),2,0,REPT(" ",B2-1))&REPT(" ",3)&REPLACE(RIGHT(A2,3-B2),2,0,REPT(" ",2-B2)),
IF(MOD(B2,2),LEFT(A2,B2)&REPT(" ",3)&RIGHT(A2,4-B2),
REPLACE(LEFT(A2,2),2,0," ")&" "&REPLACE(RIGHT(A2,2),2,0," ")),
IF(OR(B2=1,B2=4),LEFT(A2,B2)&REPT(" ",2)&RIGHT(A2,5-B2),
REPLACE(LEFT(A2,B2),2,0,REPT(" ",3-B2))&" "&REPLACE(RIGHT(A2,5-B2),2,0,REPT(" ",B2-2))),
LEFT(A2,B2)&" "&RIGHT(A2,6-B2),A2)

Qエクセル SUMPRODUCT関数について

SUMPRODUCT関数を使い、以下の合計を件数を出したいのですが、いろいろ調べてもよくわかりませんのでご教示をお願いいたします。(エクセル2010使用)

【やりたいこと】
以下の表を使用し、「みかん」と「ばなな」を購入した件数を月ごとに合計して表示する関数を作りたい。4月の合計件数はB8セル、5月はB9セル、6月はB10セルに表示できるようにします。

     (A列)  (B列)
(行) 種類  購入日
 1  みかん 4月3日
 2  ばなな 4月15日
 3  りんご 5月6日
 4  りんご 5月30日
 5  ばなな 6月7日
 6  みかん 6月9日
 7
 8 【結果】 4月 2
 9      5月 0
10      6月 2

よろしくお願いします。

Aベストアンサー

こんにちは!

今回の質問の場合は「OR」条件になりますので、足し算にする必要があります。

↓の画像ではA8~A10セルの表示形式をユーザー定義から
0月
とし、単に4とか5という数値のみを入力しています。

B8セルに
=SUMPRODUCT((MONTH(B$1:B$6)=A8)*(A$1:A$6="みかん"))+SUMPRODUCT((MONTH(B$1:B$6)=A8)*(A$1:A$6="ばなな"))

という数式を入れフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

Q1つのSUMIFSをSUMで括った数式の意味について

エクセル2010を使っている者です。

会社で使っているファイルで
=SUM(SUMIFS(合計!J:J,合計!G:G,{"1000","9000"},合計!H:H,"<=160",合計!F:F,5000))
という数式を使っていました。

SUMIFS関数はSUM(合計)の意味を含んでおり、SUMで括る意味がわかりませんでした。
(SUMの中に複数のSUMIFSを入れているのであれば、SUMIFS(・・・)+SUMIFS(・・・)という
意味になり理解できるのですが)

また、{}の意味もわかりません。

どなたか、ご教示願います。

Aベストアンサー

SUMIFS(合計!J:J,合計!G:G,{"1000","9000"},合計!H:H,"<=160",合計!F:F,5000)
を数式バー内で選択して[F9] 配列の結果が返ります。1行2列
それを合計するSUMです。
G列が1000 または 9000の場合で、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計しなさい

G列が1000、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計したものと
G列が9000、かつ
H列が160以下で かつ
F列が5000 であるJ列を合計したものを足しなさい
でもよいです

Q【追加質問】SUMIFとOFFSETの組み合わせについて 先日、下記にて質問をさせていただき解決した

【追加質問】SUMIFとOFFSETの組み合わせについて

先日、下記にて質問をさせていただき解決したのですが、一部ご教授いただいた数式で反映されない部分があり、原因がわからず再度質問させてください。

https://oshiete.goo.ne.jp/qa/9797530.html

前回質問添付画像参照。
合計の表のE68数量、E69売上、E70仕入、E73粗利についてはご教授頂いたSUMIFの式を、E71売上単価、E72仕入単価については平均単価を求めたいのでSUMIFをAVERAGEIFに変えて入力しましたが、反映されない部分がありました。
数量だけ反映されて、売上以下が数値が反映されなかったり、全て反映されなかったりと原因がわかりかねている状況です。
何が原因で反映されないのか?ご教授いただければ幸いです。

Aベストアンサー

>添付頂いた②の画像(前の商品のあと1行はさんで次の商品となります)が希望する表です。
となりますと、-10を-9に修正すればよいということでしょうか?

はい、そうなります。
算出したい行(合計の数量の行)の9行前が最後の商品の数量になりますので、-9になります。


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

人気Q&Aランキング

おすすめ情報