まりふのひと

竹炭の精錬度散布図のグラフデータを作る「マクロ精錬度グラフ表.xlsm」を完成させた

焚口は東(上)にある。
 竹炭の精錬度は、

  • 右図の同じ標高?の8ヶ所から適当に竹炭1本(長さ約12cm)採取し、
  • テスター で電気抵抗値(Ω)を測定し、
  • 精錬度[=log10(電気抵抗値)]に換算している。



 気抵抗値(測定データ)は

Excelに入力し、

布図を作るため、右のようなグラフデータを作っている。

  • 上から:上図の「焼落ち深さ」
  • 東西南北:測定場所(1〜8の繰り返し)
  • 底から:57cm−焼落ち深さ
  • 抵抗値
  • 平均抵抗値:E2=Average(D2:D9)
  • 平均精錬度:F2=Log(E2)



 散布図はデータ数が変わっても良いように

[名前の定義]で行っている。

 前置きが長くなったが、そんなに難しいVBAにはならないだろう‥‥ と思っていた。

  • 測定データの横に並んだ抵抗値を縦に並べるのは、貼り付けのオプション[行と列を入れ替え]にあるので、まずは[マクロの記録]を行ってみた。
    1. Range("B5:I5").Select
    2. Selection.Copy
    3. Sheets("グラフデータ").Select
    4. Range("D2").Select
    5. Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
     PasteSpecialの引数は、
    • Paste:=xlPasteAll:すべてを貼り付け
    • Operation:=xlNone:計算を伴う貼り付けの場合の計算方法なので「なし」ということだろう。
    • SkipBlanks:=False:クリップボードに含まれる空白のセルを貼り付けの対象にする。(規定値は False)
    • Transpose:=True: 行と列を入れ替えて貼り付ける。(規定値は False)

       他の項目もあることから、For 〜 Next で行うことにした。
  • グラフデータのセルE2 に計算式 "=AVERAGE(D2:D9)" を入れるところでつまづいた。
    計算式に cells() は使えないからだ。そこで、
    1. Cells(row,column).AAddress でアドレスを作り、
    2. Replace() で "$" を "" に置き換えた。


 一応完成したので、試すと

  • グラフデータはチャンと出来ている。
  • しかし、
    このワークシート内にある1つ以上の式の参照に問題が見つかりました』が出る。
  • 散布図を見ると‥‥ 壊れていたぁ〜

 もう一度、グラフデータを作り替えても同じで 
 「名前の定義が壊れた?」と思い、【名前の管理】ダイアログボックスを見ると、参照範囲が‥‥

  • 正常:=OFFSET(グラフデータ!$C$2,0,0,COUNTA(グラフデータ!$C:$C)-1,1)
  • 異常:=OFFSET(グラフデータ!#REF!,0,0,COUNTA(グラフデータ!$C:$C)-1,1)

セル$C$2のところが #REF! になっていた。
正常状態に修正し、再度、グラフデータを作ると‥‥ やはり壊れる 


 VBAをステップ実行してみると‥‥

  • グラフデータを作り替える前に古いデータを削除するため、グラフデータ・シートの2行目以下を行削除したのが原因だった。
  • セルの値を削除しても同じであった。

どうも 名前の定義で使っているセルを削除してはいけない ようだ。


 データのクリアは3行目以下とし、選択して、ClearContents に修正。

'グラフデータをクリアする。
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    If lastRow > 1 Then
            '2行目を残しておかないと、名前の管理が壊れる。
        Range(Cells(3, 1), Cells(lastRow, 6)).Select
        Selection.ClearContents
    End If

 成功!