中小企業の働き方改革をサポート>>

いつもお世話になります。
SUMPRODUCT関数で集計したいのですが、OFFSET関数を組合せてA1に関数を入力し、右にひっぱるだけで36ヶ月分を集計したいと思い、下記の計算式を入力したのですが#VALUE!になり困ってます。

A1=SUMPRODUCT((OFFSET(data!$A$1,1,COLUMN(A1)*3-3,99,1)=$G$1)*(OFFSET(data!$B$1,1,COLUMN(A1)*3-3,99,1)=$H$1),(OFFSET(data!$C$1,1,COLUMN(A1)*3-3,99,1)))

dataシートには、A列:商品CD、 B列:営業所CD、 C列:売上金額
の3列のデータが、36か月分108列あります。

集計するシートのG1に商品CDを、H1に営業所CDを入力すると、A1~A36に集計結果を返したいのですが、教えてくださいませんか

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

A 回答 (2件)

>A1~A36に集計結果を返したい



でしたらCOLUMNじゃなくてROW関数を使わないといけません。
それとは別に,次のように細工します。

A1:
=SUMPRODUCT((OFFSET(data!$A$1,1,SUM(ROW(A1)*3-3),99,1)=$G$1)*(OFFSET(data!$B$1,1,SUM(ROW(A1)*3-3),99,1)=$H$1),OFFSET(data!$C$1,1,SUM(ROW(A1)*3-3),99,1))
以下コピー
    • good
    • 0
この回答へのお礼

早速のご教示ありがとうございます。

質問が間違ってました。
A1~A36ではなく、A1~AJ1でした。

教えて頂いたROWの部分を、COLUMNに代用する事で大丈夫でしょうか?
一度やってみます。

お礼日時:2011/11/22 14:54

A1セルで


=SUMPRODUCT((INDEX(data!$A$2:$DD$100,,ROW(A1)*3-2)=$G$1)*(INDEX(data!$A$2:$DD$100,,ROW(A1)*3-1)=$H$1),INDEX(data!$A$2:$DD$100,,ROW(A1)*3))
下へオートフィル

右にオートフィルならROWをCOLUMNにして
=SUMPRODUCT((INDEX(data!$A$2:$DD$100,,COLUMN(A1)*3-2)=$G$1)*(INDEX(data!$A$2:$DD$100,,COLUMN(A1)*3-1)=$H$1),INDEX(data!$A$2:$DD$100,,COLUMN(A1)*3))
ですが、G1セルとH1セルに対象が入力されているので、F1セルまでしか対応できませんよ
    • good
    • 0

このQ&Aに関連する人気のQ&A

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

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

このQ&Aを見た人が検索しているワード

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

QSUMPRODUCT関数 行が増えても自動で

SUMPRODUCT関数 行が増えても自動で最終行を取得するには?
エクセル2003です。

名前  サイズ
みかん  S
みかん  M
りんご  S

このような表があったら、
=SUMPRODUCT((A1:A4="みかん")*(B1:B4="S"))
で、複数条件の個数が取得できますが、
行がどんどん増えていったときに、手動で
A4・B4の部分を変更していくしかないのですか?

それとも大体の行を見越して、
=SUMPRODUCT((A1:A100="みかん")*(B1:B100="S"))
のようにするべきなのでしょうか?

=SUMPRODUCT((A:A="みかん")*(B:B="S"))
にしたらエラーになりました。

自動で最終行を取得したいのですが、できますか?

Aベストアンサー

こんばんは!

今後行が増えていくのであれば

>それとも大体の行を見越して、
>=SUMPRODUCT((A1:A100="みかん")*(B1:B100="S"))
>のようにするべきなのでしょうか?

と仰っているように多めの行の範囲で関数を作成しておいた方が良いと思います。

※ SUMPRODUCT関数は配列数式になってしまいますので、
データ量が極端に多い場合はPCに負担をかけ、計算速度が落ちてしまいます。
1000行程度であれば問題ないと思いますよ。m(_ _)m

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExcel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

MATCH関数で、検索した値が複数ある場合に1つしか出なくて困っています。
(例)
   A B C D
1 年月日 種類 番号 備考
2 4月1日 肉類 1
3 4月2日 野菜 2
4 4月3日 肉類 1
5 4月4日 果物 2
6 4月5日 野菜 1
7 4月6日 果物 3
8 4月7日 果物 2
9 4月8日 肉類 1

この表で、D9の備考の欄に「種類:肉類、番号:1」と同じものがあった時の日付を、新しい日付で取り出したいのです。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
としたのですが、本当は4月3日を取り出したいのですが、4月1日が出てきます。
どのようにしたら良いでしょうか?
Excel2003です。
よろしくお願いします。

Aベストアンサー

MATCH関数を使用しませんが、D9の書式を日付にして、
=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。

Qエクセル:マクロ「Application.CutCopyMode = False」って?

エクセルのマクロを記録していると

「Application.CutCopyMode = False」

というものがよく出てきますが、これは何でしょう?
どういう意味のものかわかりません。
削除しても差し支えないのもでしょうか?

Aベストアンサー

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
Range("A1").Select
Selection.Copy
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Paste ← ココでエラー
------------
ご自分で、セルをコピーしてみると分かると思いますが、コピーした範囲が点線で点滅されます。
「Application.CutCopyMode = False」をすると、
その点滅がなくなります。

「Application.CutCopyMode = False」の前で
セルのコピー、または切り取りを行っていると思います。
これは、その操作(セルのコピー、または切り取り)を無効にしているだけです。
------------
Range("A1").Select
Selection.Copy ← これを無効にしている
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
------------
上記の場合であれば、「Application.CutCopyMode = False」を削除しても問題ありませんが、
以下の場合、貼り付け処理でエラーになります。
------------
...続きを読む

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

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

QSub ***( ) と Private Sub ***( ) の違い

初歩的な質問で申し訳ありませんが・・・

自分でコードを書いていても、イベントが発生したりした時の処理で、コードのウィンドウで上のドロップダウンリストで選択できる時の処理などは自動的に[Private Sub Command1_Click( )]などと出てくるのでそのまま使っています。自分で別途プロシージャーを作成する時は[Sub ****( )]としています。
ですがその違いを理解しないまま、自分で作成する時は[Private Sub]ではなくて[Sub]を使っています。

Sub ***( ) と Private Sub ***( ) の違いは何なんでしょうか?
どなたか説明頂けませんか?
よろしくお願いします。

Aベストアンサー

「Sub」の部分にカーソルを置いて[F1]を押せばヘルプが起動します。
「指定項目」のところに「Public」と「Private」の説明がありますよ。
省略して「Sub hogehoge()」とした場合は「Public」とみなされます。

Publicは「すべてのモジュールから呼び出せるプロシージャ」ということになります。
Privateとすると「同じモジュールの中からしか呼び出せないプロシージャ」となります。

もしExcelをお持ちでしたらExcelのVBEで標準モジュールを追加し、「Sub Test1()」と「Private Sub Test2()」を作成してみてください。
そしてExcelの[ツール]-[マクロ]-[マクロ(Alt+F8)]でマクロ実行のダイアログを表示させてみるとわかります。
ここには実行できるプロシージャの一覧が表示されますが、Test1は表示されているけれどTest2は表示されません。
Test1はPublicで、Test2はPrivateだからです。

Q官公庁の呼称 貴局(省)?御局(省)?

国家公務員を目指しているものです。
志望官庁に手紙を出す機会があるのですが、
その際に、例えば経済産業省のことをさして、「御省」とか「貴省」という呼称を使用すればよいのでしょうか?
また出先機関で局がありますが、「御局」や「貴局」と言っても通じるのですか?
会社の場合は、「御社」とか使用されるように、官公庁でもあるのでしょうか?

教えてください。

Aベストアンサー

貴省 貴庁 貴局 等でしょう。

なお、一般企業の場合、文書では「貴社」で、会話では「御社」を使いますから、文書では「貴省」で、「御省」き使わないでしょう。


人気Q&Aランキング

おすすめ情報