月 4月 5月 6月 7月 ・・・・
りんご 20 25 30 35 ・・・
みかん 10 15 20 23・・・・
上記のような表を3カ月ごとに集計する関数を教えてほしいです。
→4月~6月分、7月~9月分 のような形
これを3年分、列で推移をだしているため、オートフィルでいれれば3か月分ずつ集計するようなまとめの表をいれたいです。
色々調べて下記のようなものをいれてみたのですが、違う数字が出てしまい・・・混乱しております。
何か良い方法を教えていただければと思います。
=SUMPRODUCT((MOD(COLUMN(K13:V13),3)=0)*(K13:V13))
No.1ベストアンサー
- 回答日時:
K13セルからV13セルまでの値を3列おきに合計を出したいということでしょうか。
W13セルからZ13セルまで3カ月おきに合計する場合は、
OFFSET関数を使ったほうが良いと思います。
この場合、まずはオートフィルを使わない場合、どうなるのかを考えます。
W13セル
=SUM(OFFSET(K13,0,0,1,3))
X13セル
=SUM(OFFSET(K13,0,3,1,3))
Y13セル
=SUM(OFFSET(K13,0,6,1,3))
Z13セル
=SUM(OFFSET(K13,0,9,1,3))
こんな感じになります。
OFFSET関数内で変わっている数字に注目すればオートフィルで何とかなりそうですね。
あとはこの数字をCOLUMN関数を使って引っ張ればいい。
COLUMN(A1)*3-3
とでもしましょう。
これならオートフィル後に、
1*3-3 → 0
2*3-3 → 3
3*3-3 → 6
4*3-3 → 9
となってくれます。
(本来は 「(COLUMN(A1)-1)*3」 としたほうが、何をしているのか分かりやすいんですけどね)
あとはOFFSET関数の基準のK13セルがずれないように「$K13」と列番号の前に $ を付けるだけ。
すると最終的に
W13セル
=SUM(OFFSET($K13,0,COLUMN(A1)*3-3,1,3))
となります。
…のように、1ステップずつ考えるようにしましょう。
いきなり数式を考えるのは慣れてからでよいでしょう。
・・・余談・・・
MOD(COLUMN(K13:V13),3)=0
これがどんな結果を返しているのかを確認してみましょう。
SUMPRODUCT関数の中で使われているため「(K13:V13)」が配列として認識されています。
MOD({11,12,13,14,15,16,17,18,19,20,21,22},3)=0
なんですね。
そのため、5月、8月、11月、2月の列のデータを拾うようになっていますよ。
考え方から教えてくださり、本当にありがとうございます。そこの部分が知りたかったんです!
A1を指定するのは、参照先の列設定が0になるからなんですね。
この掛け算したり引き算したりする意味の理解がずっと追いつかなかったので、これでようやくCOLUMNのセットの仕方の意味がわかりました。
おかげさまで応用できそうです!非常に助かりました。
No.2
- 回答日時:
添付図参照
Sheet2 において、
1.セル B1、B2 に下記の式を入力
 ̄ ̄B1: =OFFSET(Sheet1!$B1,,(COLUMN(A1)-1)*3)&"~"&OFFSET(Sheet1!$D1,,(COLUMN(A1)-1)*3)
 ̄ ̄B2: =SUM(OFFSET(Sheet1!$A$1,MATCH($A2,Sheet1!$A$2:$A$4,0),1+(COLUMN(A1)-1)*3,,3))
2.セル B2 を下方にオートフィル
3.範囲 B1:B4 を右方にズズーッとオートフィル
図でも示していただきありがとうございます!
範囲名も出てくるようにできるんですね。
MATCHとの組み合わせ方も参考にさせていただきます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- Excel(エクセル) DATEDIFで作成した勤務年数の並べ替えがうまくいかない 3 2023/07/31 17:09
- Visual Basic(VBA) VBA 連続する名前ごとに集計 3 2022/05/21 18:24
- Excel(エクセル) エクセルシートの合計の変動 5 2022/04/05 15:56
- Excel(エクセル) 数式→数値固定する方法ありますか? 5 2023/06/18 15:22
- Excel(エクセル) 週毎の集計 2 2023/08/04 16:56
- Excel(エクセル) Excel関数教えてください!! Excelで勤怠集計をしているのですが、部署別に雇用形態ごとの月間 2 2022/10/21 23:06
- Excel(エクセル) パワーポイントのエクセルグラフでの月の表示 1 2022/11/19 13:10
- Excel(エクセル) エクセルで年月ごとの売り上げを集計する方法を教えてください 7 2022/06/01 17:06
- Excel(エクセル) Excel ある日の45日後の計算 及び 3か月後の計算 6 2022/04/05 13:44
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
3列ごとに合計を出したい
Excel(エクセル)
-
Excel 3列毎のセルを別の表に抽出したい。
Excel(エクセル)
-
【Excel】4つとばしで合計する方法
Excel(エクセル)
-
-
4
5列ごとに合計したい
Excel(エクセル)
-
5
別シートのセルを3つ飛ばしで引用したいです
その他(Microsoft Office)
-
6
エクセルで数行おきにデータを抽出
Excel(エクセル)
-
7
EXCELで10行ごとの和を出したい
Excel(エクセル)
-
8
2行おきの合計、VBAでど表現するのですか?
Excel(エクセル)
-
9
【Excel】4行ごとの和を集計したいです
Excel(エクセル)
-
10
エクセルで1列おきのデータのみコピペする計算式
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelカスタム関数(アドイン登...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報