先ほど下記質問をして回答を得ましたが欲がでて追加で行いたい事が出てきましたがいっこうに進みません。
先ほどの質問
ほぼ毎日最終行の所にA列に日付 B/C/D/列に数値を加えているという作業を繰り返しています。従って下の方が最近入力した値になっている状態です。
私がしたいのはこの最近入力した(最終行)行から5行上までの各列の合計や平均値をそれぞれ1番上の方に自動で表示したいのですがいろいろと調べても分かりません。(行を追加するたび範囲を指定し直すというのがつらいです。)
・今回は皆さんの回答の中から=SUM(OFFSET(B2,COUNTA(B2:B65536)-5,0,5,1))とう式を使って下から5行上まで合計しました。
追加質問
F列の数値条件に合致したものだけを同様に下から5行上まで合計したいです。
SUMIFを使って見ましたが実力のなさからうまくいきません。
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
こんばんは。
#1 のWendy02です。>B1にいれてみましたが84となり79とはなりませんでした。
B1 に入れることは予想外でしたが、今、こちらでやってみましたが、79 で、84 というか、「5」は足されません。ただ、私の数式のひとつの問題点は、一番、最後の65536行目まで、探しているのですね。だから、ちょっと下のほうも調べてみてください。
それで、調べる方法ですが、
LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5))
数式の上記の範囲を、マウスでドラッグして取り、F9 を押すと、配列データの中身{数字,数字,・・・} で、出てきます。
それで、順番や、どこで「5」が足されているか出てくるはずです。
なお、[ESC]を押せば解除できます。
なお、2行目からなら、以下のほうが良いかもしれません。別にそのままでもエラーは出ないはずですが。
=SUMPRODUCT(LARGE((INDIRECT("F2:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F2:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))
No.4
- 回答日時:
A B C D E F
1 21 条件
2 5 a
3 8
4 6 a *
5 2 a *
6 1
7 3 a *
8 4 a *
9 5
10 6 a *
B1=SUMPRODUCT((F2:F100="a")*(ROW(B2:B100)>=LARGE((F2:F100="a")*ROW(F2:F100),5)),B2:B100)
★*印が、F列が「a」の下から「5つ」の対象です
No.2
- 回答日時:
質問の意味が、前回も(したがって今回も)判りにくいのですが、具体例を挙げて説明しては。
私流の理解では(極端にそぎ落とすと)A列 B列
氏名成績
a11
s2
d3
f43
g5
h12
i18
j3
k21
ーー
A2:B30までがデータ入力可能性のある範囲としてます。
B列の数に対する条件を10にしてます。
それ以外のセルに
=SUMIF(OFFSET(B2,COUNT(B1:B30)-5+1,0,5,1),">10",OFFSET(B2,COUNT(B1:B30)-5+1,0,5,1))
と入れる。
上記例では 51<=21+18+12(B6:B10のうち10以上の数を加えた)
式は、CountでわかるB列数字行の下から5行目
COUNT(B1:B30)-5+1
の範囲を対象に、>10の条件で抜き出し、同じ範囲の数を足している。
OFFSET関数という、範囲を指定できる関数を使っていますが、
たとえ質問の意図を採り間違えていなくても、#1のご回答や私のは
関数上級編で理解も難しいのではないですか(特に#1は)。
どこか
回答ありがとうございます。
また質問の意味が分かりづらくご迷惑をおかけしました。
勉強したいなぁと気持ちが大きくなりましたが正直今は理解難しいです。
しかしここまで来たのでなんとかできるようになればと思っています。
No.1
- 回答日時:
こんばんは。
質問の用件がわかりにくいのですが、こういうことでしょうか?
B列・・F列
11 a
12
13 a ↓
14 a ↓
15
16 a ↓
17 a ↓
18
19 a ←最終行 ここまで。
合計は、「79」ということですか?
>今回は皆さんの回答の中から
?
ご質問者さんは、私の書いた式は何も見ていないような気がしましたが、前回、私が書いたものを応用してみました。数式の得意な人はいっぱいいるでしょうから、間違いましたら、ご容赦のほどを……。
検索値条件: "a"
最終行の数値を取るのは、B列(数値の最終行-文字列の場合は、書き方が少し変わります)
COUNTやCOUNTA と違うのは、間が空いていても取れること。
=SUMPRODUCT(LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))
こんばんは
1度ならず2度までも分かりづらい質問に回答いただきありがとうございます。
また1度目の失礼申し訳ありません。数日間の悩みに解決が見え気焦っていました。
私の言いたかった質問はWendy02さんの言われる通りのことです。
B列・・F列
11 a
12
13 a
14 a
15
16 a
17 a
18
19 a
F列のaを条件として合致する行のB列の数字を下から5行分の合計(今回は79)をB1の位置に表示させたいのです。
但し今は19行ですが毎日追加していくので行の追加にも対応希望です。
回答を頂いてから自分のファイルでうまくいかず、上記例を作ってみて=SUMPRODUCT(LARGE((INDIRECT("F1:F"&MATCH(10^15,B:B,1))="a")*ROW(INDIRECT("F1:F"&MATCH(10^15,B:B,1))),ROW($1:$5)))と
B1にいれてみましたが84となり79とはなりませんでした。
なぜ+5されるのか分かりません。
自分ではまったく解決する光がまったく見えない状態です。
かなり背伸びしている質問になっていますがよろしくお願いします。
できましたらアドバイスお願いします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・14歳の自分に衝撃の事実を告げてください
- ・架空の映画のネタバレレビュー
- ・「お昼の放送」の思い出
- ・昨日見た夢を教えて下さい
- ・ちょっと先の未来クイズ第4問
- ・【大喜利】【投稿~10/21(月)】買ったばかりの自転車を分解してひと言
- ・メモのコツを教えてください!
- ・CDの保有枚数を教えてください
- ・ホテルを選ぶとき、これだけは譲れない条件TOP3は?
- ・家・車以外で、人生で一番奮発した買い物
- ・人生最悪の忘れ物
- ・【コナン30周年】嘘でしょ!?と思った○○周年を教えて【ハルヒ20周年】
- ・ハマっている「お菓子」を教えて!
- ・最近、いつ泣きましたか?
- ・夏が終わったと感じる瞬間って、どんな時?
- ・10秒目をつむったら…
- ・人生のプチ美学を教えてください!!
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・都道府県穴埋めゲーム
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
「段」と「行」の違いがよくわ...
-
列方向、行方向の定義
-
Excelの行数、列数を増やしたい...
-
エクセルで離れた列を選択して...
-
VLOOKUPの列番号の最大は?
-
VBA 指定した列にある日時デー...
-
Excel文字列一括変換
-
LEFT関数とIF関数の組み合わせ...
-
Excel 2007で複合グラフ(折線...
-
CSVファイルの「0落ち」にVBA
-
Alt+Shift+↑を一括で行うには、...
-
エクセルで複数列の検索をマク...
-
エクセルの行を65536以上に増や...
-
エクセルで飛び飛びのセルの計算
-
データシートビューのタイトル...
-
エクセルマクロの組み方
-
エクセル マクロ 範囲の値を上...
-
(VBA)Excelの特定の範囲にデー...
-
☆Excel VBAでAVERAGE関数を使う...
-
エクセルマクロでオートフィル...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
「段」と「行」の違いがよくわ...
-
エクセルで離れた列を選択して...
-
VLOOKUPの列番号の最大は?
-
列方向、行方向の定義
-
LEFT関数とIF関数の組み合わせ...
-
Excelの行数、列数を増やしたい...
-
エクセル マクロ 範囲指定で...
-
VBA 指定した列にある日時デー...
-
エクセルのソートで、数字より...
-
Excel文字列一括変換
-
VBAで結合セルを転記する法を教...
-
エクセルで最初の行や列を開け...
-
エクセルマクロの組み方
-
エクセルで複数列の検索をマク...
-
最近急にVBAの処理速度が遅くな...
-
エクセルマクロPrivate Subを複...
-
エクセル マクロ 範囲の値を上...
-
リストからデータを紐付けしたい
-
Excel 区切り位置指定ウィザー...
-
データシートビューのタイトル...
おすすめ情報