- ベストアンサー
[EXCEL]AVERAGEでブランクを飛ばした計算式
みなさん、こんばんは。 [EXCEL]AVERAGEでブランクを飛ばした計算式を教えてください。 よろしくお願いします。 まず A列1~13に 10 20 30 40 50 60 (空白/ブランク) 10 20 30 40 50 60 と数字が入っています。 B列6行に =IF(A6="","",AVERAGE(A1:A6))と式を入れ、この式をB列13まで延ばすと、 B列6~13は 35 38 36 34 32 30 35 となります。 私の希望は7行目のブランクを無視して数字があるところだけを指定範囲(今回は範囲は6行)計算したいのです。 結果としてB列6~13は 35 35 35 35 35 35 35 となるのが希望です。 B列6、13は、連続していますので、そのままの平均なので35ですが、 他はブランクがありますので、そのブランク1つ分を計算範囲に入れず、 範囲先頭部分をひとつ増やして計算させたいのです。 B列8ならA7を除く、A2~A6,A8の平均(AVERAGE(A2:A6,A8)) B列10ならA7を除く、A4~A6,A8~A10の平均(AVERAGE(A4:A6,A8:A10)) という感じにです。 よろしくお願いします。
- g-touch
- お礼率89% (61/68)
- オフィス系ソフト
- 回答数4
- ありがとう数4
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
結構面倒な課題ですね。 B6に =IF(A6="","",AVERAGE(OFFSET(A1,0-COUNTBLANK(A1:A6),0,6+COUNTBLANK(A1:A6),1))) と入れて、B13まで式を複写する。 結果 A1:B13 10 20 30 40 50 60 35 10 35 20 35 30 35 40 35 50 35 60 35 となりましたが。 何しろ小数例のテストですので、この式や結果を良く吟味をお願いします。
その他の回答 (3)
- koko88okok
- ベストアンサー率58% (3839/6543)
AVERAGE関数は空白を計算しないので、A1に項目を設けて、A2以下に同じデータを入力します。(A8が空白セルになります。)その上で、 A7セルに「=IF(A7="","",AVERAGE(A1:A7))」を入力して、下にオートフィルすれば、ご希望のように表示されます。 ただし、A1セルは文字列であることが条件です。
お礼
7行の間にブランク1つという条件では、スリムな計算式でいいですね! ご回答ありがとうございました。
- ka_na_de
- ベストアンサー率56% (162/286)
VBAでユーザー定義関数を作ってみました。 以下のコードを標準モジュールに貼り付けたうえで、 B6に =IF(A6="","",MyAve(A1:A6)) と入れて、B13まで式を複写して下さい。 ブランクが二つ以上あっても、上方向にデータを探して 平均をだします。 '===========標準モジュールに記述================================ Function MyAve(OrgRng As Range) On Error GoTo Err Dim OrgRngRowsCnt As Long, NewRngRowsCnt As Long Dim NewRng As Range OrgRngRowsCnt = OrgRng.Rows.Count Set NewRng = OrgRng Do Until WorksheetFunction.Count(NewRng) = OrgRngRowsCnt If NewRng.Row = 1 Then GoTo Err Set NewRng = NewRng.Resize(NewRng.Rows.Count + 1).Offset(-1) Loop MyAve = WorksheetFunction.Average(NewRng) Set NewRng = Nothing Exit Function Err: MyAve = "error" Set NewRng = Nothing End Function
お礼
VBA、まだ私は使ったことありませんが、 「ブランクが二つ以上あっても、上方向にデータを探して平均をだします。」 すばらしいですね!時間があるときに挑戦みようという気になりました。 式まで新規に作っていただきありがとうございました。
- ASIMOV
- ベストアンサー率41% (982/2351)
ちょっと、違うような気もしますが、一応結果は35になるので >B列6行に >=IF(A6="","",AVERAGE(A1:A6))と式を入れ、 ここで、7行を行選択して、一端「行削除」します そして >この式をB列13まで延ばすと、 としてから、7行を選択して、「行挿入」で、結果は35に... どうでしょう?
お礼
ご回答ありがとうございます。 ブランクも式によって導き出していますので、削除はできません。 私の説明不足でした、失礼しました。
関連するQ&A
- Excel2007でのAverage
例えばA列のA1からA30間での数値平均を出したい場合、 あるセルに=AVERAGE(A1:A30)と書けばよいのは分かるのですが このA列に何も数字が書いてない場合(0除算のエラー)になり セルには#####と表示されてしまいます。 このA列に何も数字が書いてない場合、0と表示させるには どんな式を書けばよいのか教えてください。
- ベストアンサー
- オフィス系ソフト
- ☆Excel VBAでAVERAGE関数を使うとき・・・
こんにちは。VBA初心者です。 VBAでAVERAGE関数を使いたいのですが、参照範囲を最終行まで指定したい場合、 どのようプログラムすれば良いのでしょうか? A B C 1 5000 2 2000 3 3000 4 1000 5 6000 6 : 7 : ← A列の値を平均する。 A列には膨大な行が存在すると仮定します。 VBAに詳しい方、教えてください。 どうか、よろしくお願いしますm(_ _)m
- ベストアンサー
- その他MS Office製品
- AVERAGE(IF
EXCEL2003ですが、2行目にB列からK列までに数値が入っています。 その中で100以下の数値の平均をL2に表示させるべく、下記式をL2に貼り付けましたが、#VALUE!の表示です。 =AVERAGE(IF(B2:K2<101,B2:K2,"")) 下記式でも同様でした。 =AVERAGE(IF(B2:K2<101,B2:K2)) 加減乗除は分かるのですが、IF関数は良く分かりません。 どうぞ宜しくご教示下さい。
- ベストアンサー
- オフィス系ソフト
- excel2003で複数条件
A列にA~Gのアルファベットかブランク、B列に数字(0~5)かブランクが入っています。 A列にアルファベット、B列に0が入っている行を抽出し、 A列とB列がAと0、BとO、CとO・・・の行がそれぞれABCごとに何個あるかカウントしたいのですが、 どのような論理式を作ればよいでしょうか? なおexcel2003を使用しておりますのでcountifとsumproductは使うことはできますが、countifsを使うことはできません。 よろしくお願い致します。。
- ベストアンサー
- Excel(エクセル)
- EXCELで予測値を導き出す方法
みなさま、よろしくお願いします。 A列1~9行に 50 70 30 60 10 90 50 60 20 という数字があるとします。 (列はアルファベット、行は数字) 現在A列10行は数字が出てないので空白です。 B列10行目に以下のような式を入れます。 =AVERAGE(A1:A9;C10) 次にC列10行目に48を入れますと、48.8になります。 次に48を49に変えますと、48.9となります。 つまり、48だと=AVERAGE(A1:A9;C10)を超えませんが、 49だと=AVERAGE(A1:A9;C10)をはじめて超えます。 現在のやり方だと、少しずつ数字を増減させて数字を導き出しているのですが、 このはじめて超える数字、この場合だと49を一発で出す関数式はどのようにすればいいのでしょうか? 現在、A列は9行目までですが、A列に数字が増えていけば、 =AVERAGE(A1:A10;C11)、=AVERAGE(A1:A11;C12)と増えていきますので、 OFFSETにできるのでしたら、どのようになるのかもあわせてお願いしたいと思います。 どうかよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 【Excel】複数条件のAVERAGE
ある特定したいくつかの要素の合計を、セル「I5」にだすとします。 たとえば表2のH2の「年」、同表のH3の「店舗」、同表のG5以下の「種目」からそれぞれ具体的な条件を、たとえば表1のA列より「2016年」、同表のE列より「店舗(1)」、同表のB列より「アスパラガス」を探し出すようにする場合関数式は以下のような式となりますが、 =SUMIFS($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3) これを合計ではなく平均値で出す場合、「SUMIFS」のみ「AVERAGE」に入れ替え =AVERAGE($D$2:$D$596,$A$2:$A$596,$H$2,$B$2:$B$596,$G5,$E$2:$E$596,H$3) という式を「I5]に入れると確かに平均値はあっていたのですが、この式をコピー(セルのコピー)しても、正常に反映されず、I5の回答がそのままコピーされてしまいます。 何が原因なのでしょうか?
- ベストアンサー
- Excel(エクセル)
- エクセルの関数(AVERAGE)について質問です。
エクセルの関数(AVERAGE)について質問です。 エクセル2007を使ってこのような、表を作りました。 A B C D 1 入院日 退院日 在院日数 区分 2 2010/3/2 2010/5/8 68 (1) 3 2010/4/18 2010/5/9 22 (5) 4 … と、100行分まで表があります。 Cの『在院日数』は「退院日-入院日+1」で計算します。 計算式は『=IF(B1="","",B1-A1+1)』といれてみました。 在院日数を求めるのはこれでうまくいくのですが、 問題は、この在院日数の平均を求めることがうまくいかないのです。 以前、エクセル2003を使っていたときは 普通に『=AVERAGE(C2:C101)』でうまくいっていたと思うのですが エクセル2007にしてからは 空白のセルも、計算式が入っているせいか、数値があると認識してしまうらしく 80行までしか入力されていない時は、81から100行までの分を0と考え 1行目から100行目までの値を足し、100でで割られた値が出てきてしまいます。 どうしたら、空白のセルは計算せずに平均値を出すことが出来ますか? それと、もう一つ質問があり 区分では、(1)~(8)を入力するのですが この、区分の(1)と(3)だけを選んで平均値を出したい ということもしたいのですが どうしたらよいでしょうか? (1)だけなら、AVERAGEIFを使えばと思うのですが (1)と(3)という時は、どういう風にしたらいいのか教えて下さい。
- ベストアンサー
- オフィス系ソフト
- エクセルでB列にタイトルと数字が以下のように入っています。
エクセルでB列にタイトルと数字が以下のように入っています。 A列 B列 1行目 ピーク値 2行目 10.5 3行目 20.2 4行目 30.4 5行目 46.4 平均を出すならAVERAGE(B1:B5)とすれば良いのですが、 毎回その行数が変化します。 B列に数字が入っている範囲だけ平均を出すにはどうすればよいのでしょうか?
- ベストアンサー
- オフィス系ソフト
- EXCELの計算式の解除方法について
EXCEL2003を使っています。 例えば2行B列に100と数字が入れてあって 2行A列に数式=2b*0.7と入れてあって、70と記載されているとします。 2行B列の数字を変更しても、2行A列の数字70を残したいのですが、 計算式の答えに対して、計算式の解除ができますでしょうか。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセル AVERAGEを取るときに
平均値を取りたいのですが 範囲が少し複雑だったのでどのように式を作ったらいいのか わかりません。 シート1に A列 りんご みかん ばなな このようにデータがありそれぞれシート2にある りんご の個数の平均値をB列に求めたいのですが シート2には A列 B列 りんご 3 8 9 みかん 5 9 10 このように果物の種類の項目名が1番上にしかなく 個数はそれに対して必ず3行あるので VLOOKUPなどを使うとりんごと書かれている真横のセルしか 参照しないので常にその項目の横のセルから3行を参照したい のですが どうしたらよいでしょうか? 教えてください。よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
お礼
私が使っているパターンすべてを試しきれてませんが、 数パターンやってみたところ私が希望していた通りの式となっておりました。 無事問題が解決できました!ありがとうございました。