エクセルの表です。
A B C
1 車番 計量値
2 2008 1200
3 2008 1300
4 2008 1250
5 1008 1250
6 1008 1250
7 1008 1300
8 2112 1300
9 2112 1350
10 2008 1450
この表の車番毎の積載量の平均を連続で求めて別のシートに表示したいのです。この場合車番2008はA2からA4で平均値をもとめ、A10は別に求めA列の車番順に表示して積載量の平均値を一覧にしたいのです。
欲張りな質問ですがどなたか教えて下さい。
No.9ベストアンサー
- 回答日時:
No2です。
>私の希望している結果が得られていると思いますが、A1:B4に
>上書きされてしまいます。
一部シート名を省略した書き方でマクロを作成していますが、元のご質問文のほうに
>別のシートに表示したいのです。
とありましたので、別シートに集計が出ているはずですが?
(画像は同じシートになっていますが)
元のシートはそのままのはずなので、上書きではないはずなんですが…
D列に集計を出すのなら、No7様がヒントを書いてくださっていますが、記入する列の指定をNo7の回答のようにして、かつ、新規シートを作成する以下の1行を削除すれば、同じシートのD列に記入されます。
Worksheets.Add Before:=ActiveSheet '←この行を削除
また、集計の記入開始を2行目からにしたければ、rOutが記入する行を示す変数になっていますので、その初期値を2に変えれば、2行目から表示されます。
rOut = 1 ’← これを rOut = 2 に変更
>>列方向もデータの最後まで計算させる方法を教えてください。
各列について、同じように平均値を求めるということでしょうか?
列方向にも集計のループをいれれば可能ですが、現状は集計対象が1列だけだったので、車番のチェック(同じ番号かどうか)と集計を同時並行で行っています。
列が多数の場合は、別々に行った方が混乱が少なくなりますね。
やるとすればこんな手順。(集計部分の手順のみ)
1)同じ車番の範囲を先にチェック。
同時にその範囲の列数の最大値を求めておく
(↑行によって列数が変わる場合)
2)上で求めた範囲(=行の範囲)で各列を集計するループ
(2列目から最大列まで)
1つの列について、対象行の数値(ブランクは除くなどして)の合計と、
セル数をカウントしておいて、平均値を求める。
計算もマクロで行うなら↑のような手順になりますが、これだと少々面倒なので、1)で同じ車番の範囲を調べたら、集計表にはその範囲を利用して
=AVERAGE(範囲)
みたいな式を定義してしまう方が、簡単そうですね。
式を定義する方法だと、こんな感じになります。
1行目(タイトル行?)は、もとのシートのものをコピーします。
また、タイトル行の最大列までを対象として、式を代入します。
(集計は、新しいシートに行われます)
Sub test()
Dim st As Worksheet, dst As Worksheet, code As String
Dim rw1 As Long, rw2 As Long, rmx As Long, rOut As Long
Dim col As Long, colmx As Long
Set st = ActiveSheet
rmx = Cells(Rows.Count, 1).End(xlUp).Row
colmx = Cells(1, Columns.Count).End(xlToLeft).Column
Worksheets.Add Before:=ActiveSheet
Set dst = ActiveSheet
st.Rows(1).Copy dst.Rows(1)
rOut = 2
rw1 = 2
While rw1 <= rmx
rw2 = rw1
code = st.Cells(rw1, 1).Value
If code <> "" Then
While st.Cells(rw2 + 1, 1).Value = code
rw2 = rw2 + 1
Wend
dst.Cells(rOut, 1).Value = code
For col = 2 To colmx
dst.Cells(rOut, col).Formula = "=AVERAGE(" & st.Name & "!" _
& Cells(rw1, col).Address & ":" & Cells(rw2, col).Address & ")"
Next col
rOut = rOut + 1
End If
rw1 = rw2 + 1
Wend
End Sub
No.8
- 回答日時:
[回答番号:No.5この回答への補足]へのコメント、
》 車番はA列の順番通りに出てきて欲しいのです。
それはピボテでは不可能です。精々次のように手動で行なうぐらいですね。
平均 / 計量値
車番 計測回 合計
2008 1 1250
2 1450
1008 1 1250
2 1250
2112 1 1325
2 1300
》 後で時間順にソートするなどは可能でしょうか。
♪こういう具合にしやしゃんせ♪と具体例を表で示したらどうです?
この回答への補足
後からupした画像に示しましたように車番はA列に出てきた車番順であること。
車番ごとに計量値の平均を求めるが、同じ車番でも1回目と2回目は分けて平均値を求めること。
計量値は70項目程度あること。
などが条件です。
No.7
- 回答日時:
えっと つづけて#2さんのマクロについて
>マクロはほとんど分かりませんが、取りあえずコピーして実行してみました。
>私の希望している結果が得られていると思いますが、A1:B4に上書きされてしまいます。
ならば
>Cells(rOut, 1).Value = code
>Cells(rOut, 2).Value = rslt / rCnt
を
Cells(rOut, 4).Value = code
Cells(rOut, 5).Value = rslt / rCnt
にしてみたらいかがかな?
※コードの意味がわからないでも実行できるってのはびっくりだけどね
この回答への補足
回答ありがとうございます。
面倒でなければもう少し教えて下さい。
この表は行数はデータが多ければ1500行程度、列数は70行程度のかなり大きなデータ数の表です。
行方向はこのままで良さそうですが、列方向もデータの最後まで計算させる方法を教えてください。
No.6
- 回答日時:
#1です。
回答時の環境では画像が見えなかったので D列に補助値 と書きましたが、C列にするのが適切なようですね。
補助値があなたの希望する同一車番でも別平均とするキーにするものです。手入力してください。
提示された例では
1~9行目:1
10~15行目:2
数値を例にしましたが補助値はABCなどでもかまいません。
ピボットの行で
車番/補助値の順とし、それぞれ小計をなしにすれば見やすいでしょう
この回答への補足
回答ありがとうございます。
お二人の方から同じ回答を頂きましたので同じ補足をさせて頂きますが、車番はA列の順番通りに出てきて欲しいのです。
実はこの表には時間のデータもあるのですが、集計したいのは車番毎の平均値なので車番を行ラベルにする必要があると思うのですが、後で時間順にソートするなどは可能でしょうか。
No.5
- 回答日時:
A B C
1 車番 計測回 計量値
2 2008 1 1200
3 2008 1 1300
4 2008 1 1250
5 1008 1 1250
6 1008 1 1250
7 1008 1 1250
8 2112 1 1300
9 2112 1 1350
10 2008 2 1450
11 1008 2 1200
12 1008 2 1300
13 2112 2 1250
14 2112 2 1300
15 2112 2 1350
上に示すように手入力による「計測回」を設けたら如何ですか?
下のようにピボットテーブルで簡単に求まりますよ。
(「合計」欄は平均値を示しています)
平均 / 計量値
車番 計測回 合計
1008 1 1250
2 1250
2008 1 1250
2 1450
2112 1 1325
2 1300
この回答への補足
回答ありがとうございます。
お二人の方から同じ回答を頂きましたので同じ補足をさせて頂きますが、車番はA列の順番通りに出てきて欲しいのです。
実はこの表には時間のデータもあるのですが、集計したいのは車番毎の平均値なので車番を行ラベルにする必要があると思うのですが、後で時間順にソートするなどは可能でしょうか。
No.4
- 回答日時:
初心者は初心者らしく・・・
A B C D E F
1 1008 2008 2112 ←車番
2 車番 計量値 1267 1300 1325 ←平均値
3 2008 1200 1200
4 2008 1300 1300
5 2008 1250 1250
6 1008 1250 1250
7 1008 1250 1250
8 1008 1300 1300
9 2112 1300 1300
10 2112 1350 1350
11 2008 1450 1450
C2: =ROUND(AVERAGE(C3:C11),0)
C3: =IF($A3=C$1,$B3,"")
この回答への補足
回答ありがとうございます。
早速試してみましたが私の質問の仕方が悪かったので、
マクロを使用する方法以外のどなたの回答も車番2008が全て累計されて計算されます。
初めに出てきた2008は3回計量して1車分の積載ですし、後に出てくる2008は1回の計量で1車分になります。
また、2112は2回計量で1車分になります。
ここでは1車分ずつ計量値の平均値を求めたいので、車番は同じでも初めの2008と後の2008は別の車の扱いにして、車番順というのはA列に上から出てくる車番順に並べて取り出したいのです。
No.3
- 回答日時:
こんばんは!
すでに回答は出ていますが・・・
他の方法の一例です。
>A10は別に求め・・・と書いてあるので、10行目を除いた平均です。
↓の画像で
E2セルを =SUMIF($A$2:$B$9,D2,$B$2:$B$9)/COUNTIF($A$2:$A$9,D2)
としてオートフィルでコピーします。
以上、参考になれば幸いです。m(__)m
この回答への補足
回答ありがとうございます。
この方法では同じ車番が出てくる度に検索範囲を変更して繰り返すことになるのでしょうか。
D列の車番をA列に出てくる順番通りに、自動で取得したいのですが。
No.2
- 回答日時:
マクロでの例です
不明部分は適当に作成していますので、適宜修正を…
Sub test()
Dim st As Worksheet, code As String, rslt
Dim rw As Long, rmx As Long, rOut As Long, rCnt As Long
Set st = ActiveSheet
rmx = Cells(Rows.Count, 1).End(xlUp).Row
Worksheets.Add Before:=ActiveSheet
rOut = 1
rw = 1
While rw <= rmx
rw = rw + 1
code = st.Cells(rw, 1).Value
If code <> "" Then
rCnt = 1: rslt = 0
If IsNumeric(st.Cells(rw, 2).Value) Then rslt = st.Cells(rw, 2).Value
While st.Cells(rw + 1, 1).Value = code
rw = rw + 1: rCnt = rCnt + 1
If IsNumeric(st.Cells(rw, 2).Value) Then rslt = rslt + st.Cells(rw, 2).Value
Wend
Cells(rOut, 1).Value = code
Cells(rOut, 2).Value = rslt / rCnt
rOut = rOut + 1
End If
Wend
End Sub
この回答への補足
回答ありがとうございます。
マクロはほとんど分かりませんが、取りあえずコピーして実行してみました。
私の希望している結果が得られていると思いますが、A1:B4に上書きされてしまいます。
私のやり方が悪いのかよく分かりません。
もう少し、教えて下さい。
No.1
- 回答日時:
D列に補助値を入力し、ピボットテーブルで平均にすればよいと思います。
>車番2008はA2からA4で平均値をもとめ、A10は別に求めA列の車番順に表示して積載量の平均値を一覧にしたいのです
ピボットの行で車番を優先すれば希望の形になります。
この回答への補足
返答ありがとうございます。
早速試してみましたが、私の質問の仕方が悪かったので、マクロを使用する方法以外のどなたの返答も車番2008が全て累計されて計算されます。
初めに出てきた2008は3回計量して1車分の積載ですし、後に出てくる2008は1回の計量で1車分になります。
また、2112は2回計量で1車分になります。
ここでは1車分ずつ計量値の平均値を求めたいので、車番は同じでも初めの2008と後の2008は別の車の扱いにして、車番順というのはA列に上から出てくる車番順に並べて取り出したいのです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
- ・ゆるやかでぃべーと タイムマシンを破壊すべきか。
- ・歩いた自慢大会
- ・許せない心理テスト
- ・字面がカッコいい英単語
- ・これ何て呼びますか Part2
- ・人生で一番思い出に残ってる靴
- ・ゆるやかでぃべーと すべての高校生はアルバイトをするべきだ。
- ・初めて自分の家と他人の家が違う、と意識した時
- ・単二電池
- ・チョコミントアイス
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelの警告について
-
タイムスタンプとテキストから...
-
シートの情報を別のシートへま...
-
マクロの処理が遅くなった
-
Excelでの文字色
-
ワークシートに出現したこの画...
-
EXCELの散布図で日付が1900年に...
-
OFFSET関数を使用したいのです...
-
エクセルでファイルの最終更新...
-
エクセルの文字が途中から消える
-
エクセルデーターから必要な項...
-
Excel 大小比較演算子による「...
-
SUBTOTALは、参照された数字で...
-
エクセルの数式バーのフォント...
-
エクセルの「条件付き書式」を...
-
Excelについて教えてください。...
-
エクセルVBA 月の中で、月~土...
-
Excelの数字の前に入っている空...
-
Excelの関数について このよう...
-
セルの数を求めたい
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報