平均を求める関数についてと日付入力について

質問

Excel2000を使っています。
いくつか判らないことがあるのですが、どれか１つでもいいので
教えてください！

○平均を求める関数について
各支店の月々の光熱費を一覧表にまとめています。
横列に1月から12月まで列を作ってあり、6月と12月の横には半期ごとの小計列もあり最後に合計列があります。
合計列の隣に月平均を表示したいのですが、間に小計列があるせいで
AVERAGE関数が使えません。
とりあえず、COUNT関数を使ったりしてなんとかうまくいったのですが
もっといい方法あればと思い質問しました。

また、地域別小計の行もあり、その月平均を出したいのですが、
計算式が入っているので分母が常に12になり困っています。
0以外の合計とデータ個数で平均をだす方法はありませんか？
判りにくいと思いますがよろしくお願いいたします。

○日付入力について
今、3/13といれると→平成19年3月13日となります。
これを、本日以降の年月日でもっとも近い年度が現れるように
したいのです。
つまり、3/13といれると→平成20年3月13日と表示したいのです。
日付データとして残したいと思っています。
8/3/13といれるのが面倒なので。。。すみません。

○携帯電話番号の表示について
1つのセルに０９０１２３４５６７８と入力したら、表示で
０９０－１２３４－５６７８と表示したいのです。
書式設定でいろいろやってみたんですがうまくいきません。。。

いろいろ質問して申し訳ありませんがよろしくお願いいたします

回答No.2

nobu- · ベストアンサー

○日付入力について

>本日以降の年月日でもっとも近い年度が現れるように

1/2と入力すると平成20年であっても、平成21年１月２日と表示（？表示上のことだけ？）とするのでしょうか？

そうでなければ、判定基準があいまいなため無理だと思います。
例えば、2000年問題時の25/75のような、きちんとした判定基準があればできるとおもいますが・・・

お礼

すみません。補足まちがってますね。。。
20年1月1日に1/2と入力したら20年1月2日と表示したいのです。
19年12月31日に1/1といれたら、本日以降で最も近い年度の20年1月1日とだしたいです。よろしくお願いいたします

補足

ご回答ありがとうございます。
今日、19年10月26日に、1/2と入力したら平成20年1月2日と表示したいですし、20年1月1日に1/2と入力したら平成21年1月2日と表示してほしです。
もしくは、入力規則のところで日付の範囲を選べますが、そこで本日以降を表示させるように設定できないものでしょうか？もしくは期間を指定したりして。。。
めんどうな質問で申し訳ありません。・゜・(ノД`)・゜・。

回答No.3

Wendy02

こんにちは。

1.○平均を求める関数について
SUBTOTAL 関数を駆使してください。小計のところで、SUBTOTAL を使い、平均でも、SUBTOTALを使用します。

平均は、SUBTOTAL(1,範囲）
小計は、SUBTOTAL(9,範囲）

SUBTOTAL関数は、SUBTOTAL関数の部分を勘定に入れません。

2.○日付入力について

>本日以降の年月日でもっとも近い年度が現れるようにしたいのです。
マクロという方法もありますが、必ず、意識して（明示的に）入力してください。
そういうところで、手を抜くと、後でミスが必ず発生します。
あえて、マクロにするなら、

'シートモジュール

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myDate As Date
If Target.Column <> 1 Then Exit Sub '1列目以外は不可
If IsDate(Target.Text) = False Then Exit Sub
myDate = CDate(Target.Text)
If myDate < Date Then
　 myDate = DateSerial(Year(myDate) + 1, Month(myDate), Day(myDate))
　 Application.EnableEvents = False
　　 Target.Value = myDate
　 Application.EnableEvents = True
End If
End Sub

このぐらいの入力は、面倒でも手動でしてほしいですね。

3.○携帯電話番号の表示について

桁がきちんと決まっているなら、以下のようになります。
[<=9999999999]000-0000-0000

お礼

お礼が大変遅くなり申し訳ございません
ご指摘のとおりですね！
面倒くさがらずに入力したほうが速いですねぇ！
ＶＢＡは初心者ですのでこれから勉強したいと思います。
本当にありがとうございました！

回答No.1

nobu-

○平均を求める関数について
>AVERAGE関数が使えません。

関数の範囲指定するところで、CTRLキーを押しながら範囲指定をすることにより、離れた箇所でも出来ると思います。

例　AVERAGE(B10:B11,B12:B14)

○携帯電話番号の表示について
表示形式のユーザ設定で
000-0000-0000
といれてみては、いかがでしょうか？

お礼

ご回答ありがとうございます
そうなのですね！初歩的な質問すみませんでした。
それでは、例えば4ヶ月目まで入力してあって、すでに計算式の入っているセル（小計行）を範囲としたときに、
平均の分母（今回は12）にならないようにするにはどうすればよいのでしょうか？？？
もしお時間あれば教えてください！
よろしくお願いいたします

携帯の書式はまたやってみます！
ありがとうございました！