<style>
.markdown-body table{
display: unset;
}
</style>

# 使用 Microsoft Excel 繪製帶有誤差槓的 XY 散布圖

> 作者：王一哲
> 第1版：2021年11月9日
> 第2版：2024年1月15日，加上B類、組合不確定度

<br />

## 前言

這是配合本校探究與實作B課程活動**紙製直升機**而寫的講義，以下的實驗數據是以紙製直升機的質量 $m$ 作為操作變因、滯空時間 $t$ 作為應變變因，其中 $m$ 取了 5 個不同的數值，測量 $t$ 各 20 次。測試的環境為 Windows 10 家用版，軟體為 Office 365 Excel。
<br />

## 不確定度與有效數字運算原則

以下只說明計算的方法與取數字的原則，不解釋數學、統計上的原理。

### A類不確定度

假設對某個物理量$x$、共測量$N$次，可以取平均值為

$$
\overline x = \frac{1}{N} \sum_{i=1}^{N} x_i
$$

加上貝索修正 (Bessel's correction) 的標準偏差為

$$
S(x) = \sqrt{\frac{1}{N-1} \sum_{i=1}^{N} (x_i - \overline x)^2}
$$

A類不確定度為

$$
u_A(x) = \frac{S(x)}{\sqrt N}
$$

### B類不確定度

B類不確定度理論上要以儀器說明書為準。但如果沒有說明書，且假設要測量的值為$\mu$，使用的測量工具最小刻度為$a$，可能測得的數值落在以下的範圍中機率為均匀分布

$$
\mu - \frac{a}{2} \leq x \leq \mu + \frac{a}{2}
$$

則將儀器最小刻度除以 $2 \sqrt 3$ 當作 B 類不確定度，也就是

$$
u_B(x) = \frac{a}{2 \sqrt 3}
$$

### A、B類不確定度組合

若某個物理量有A、B類不確定度，可以組合為

$$
u = \sqrt{u_A^2 + u_B^2}
$$
<br />

### 有效數字

不確定度一般保留2位有效數字原則上無條件進位，遇0時不進1，例如
$$
\begin{gather}
u = 0.03617 ~\mathrm{s} ~\Rightarrow~ u = 0.037 ~\mathrm{s} \\
u = 0.03607 ~\mathrm{s} ~\Rightarrow~ u = 0.036 ~\mathrm{s}
\end{gather}
$$

測量的最佳估計值為平均值，位數與不確定度對齊並4捨5入，例如
$$
\bar t = 3.518 42 ~\mathrm{s} ~~~ u = 0.037 ~\mathrm{s} ~\Rightarrow~ \bar t = 3.518 ~\mathrm{s}
$$

測量結果表示為
$$
\begin{gather}
\mathrm{待測量 = 最佳估計值 \pm 不確定度}\\
X = \bar x \pm \Delta x = \bar x \pm u
\end{gather}
$$
例如
$$
t = (3.518 \pm 0.037) ~\mathrm{s}
$$
<br />

## 使用 Excel 處理實驗數據及作圖

### 輸入資料

開啟空白活頁簿，於各欄位中輸入資料。試算表軟體中，直的部分稱為**欄** (column)，以英文字母命名；橫的部分稱為**列** (row)，以數字命名；例如儲存格**C2**的內容為**1.01**。可以利用凍結視窗的功能，凍結第1列，這樣檢視資料時會比較方便。假設實驗如據如下，操作變因為質量 $m ~\mathrm{(g)}$，應變變因為滯空時間 $t ~\mathrm{(s)}$，每個操作變因的參數測量20次滯空時間。輸入資料時，習慣上會將同一個物理量放在同一欄或同一列，先寫上物理量及單位，儲存數據的格子裡只有數字，才不會被軟體當成文字。

<div style="text-align:center">

| | A | B | C | D | E | F |
| - | ------------------| ---- | ---- | ---- | ---- | ---- |
| 1 | $m ~\mathrm{(g)}$ | 1.42 | 2.93 | 4.31 | 5.72 | 6.91 |
| 2 | $t_1 ~\mathrm{(s)}$ | 1.50 | 1.01 | 1.04 | 1.14 | 0.64 |
| 3 | $t_2 ~\mathrm{(s)}$ | 1.45 | 1.44 | 1.05 | 1.01 | 0.74 |
| 4 | $t_3 ~\mathrm{(s)}$ | 1.54 | 1.21 | 0.94 | 0.64 | 0.69 |
| 5 | $t_4 ~\mathrm{(s)}$ | 1.53 | 1.31 | 0.84 | 0.77 | 0.84 |
| 6 | $t_5 ~\mathrm{(s)}$ | 2.01 | 1.24 | 1.21 | 1.24 | 0.94 |
| 7 | $t_6 ~\mathrm{(s)}$ | 1.56 | 1.49 | 1.13 | 1.13 | 0.84 |
| 8 | $t_7 ~\mathrm{(s)}$ | 1.57 | 1.33 | 0.79 | 0.87 | 0.91 |
| 9 | $t_8 ~\mathrm{(s)}$ | 2.15 | 1.51 | 0.84 | 0.81 | 1.02 |
| 10 | $t_9 ~\mathrm{(s)}$ | 1.60 | 1.46 | 1.15 | 0.76 | 0.83 |
| 11 | $t_{10} ~\mathrm{(s)}$ | 2.18 | 1.51 | 0.91 | 0.73 | 0.79 |
| 12 | $t_{11} ~\mathrm{(s)}$ | 1.70 | 1.05 | 1.13 | 0.84 | 0.81 |
| 13 | $t_{12} ~\mathrm{(s)}$ | 1.78 | 1.14 | 1.29 | 0.74 | 0.90 |
| 14 | $t_{13} ~\mathrm{(s)}$ | 1.90 | 1.00 | 1.29 | 0.69 | 1.13 |
| 15 | $t_{14} ~\mathrm{(s)}$ | 1.71 | 1.03 | 0.85 | 0.80 | 0.73 |
| 16 | $t_{15} ~\mathrm{(s)}$ | 1.78 | 0.84 | 1.14 | 0.71 | 0.55 |
| 17 | $t_{16} ~\mathrm{(s)}$ | 1.85 | 1.03 | 0.94 | 0.82 | 0.53 |
| 18 | $t_{17} ~\mathrm{(s)}$ | 1.74 | 1.43 | 1.06 | 0.83 | 0.61 |
| 19 | $t_{18} ~\mathrm{(s)}$ | 2.01 | 1.29 | 1.04 | 0.74 | 0.71 |
| 20 | $t_{19} ~\mathrm{(s)}$ | 1.70 | 1.64 | 1.03 | 0.75 | 0.73 |
| 21 | $t_{20} ~\mathrm{(s)}$ | 1.84 | 1.19 | 0.94 | 0.77 | 0.63 |

</div>
<br />

### 計算不確定度、有效數字

計算平均值、A類不確定度、B類不確定度、組合不確定度的步驟如下：

1. 平均值：在儲存格 B22 按 = ，輸入公式 **AVERAGE(B2:B21)** 計算時間的平均值 $t_{avg} ~\mathrm{(s)}$，再利用自動填滿的功能計算儲存格 C22 到 F22。
2. A類不確定度：在儲存格 B24 按 = ，輸入公式 **STDEV(B2:B21)/SQRT(COUNT(B2:B21))** 計算時間的 A 類不確定度 $u_A ~\mathrm{(s)}$，再利用自動填滿的功能計算儲存格 C24 到 F24。於B25 到 F25 填入取有效數字後的A類不確定度。
3. B類不確定度：在儲存格 B26 按 = ，輸入公式 **0.01/(2\*SQRT(3))** 計算時間的 B 類不確定度 $u_B ~\mathrm{(s)}$，再利用自動填滿的功能計算儲存格 C26 到 F26。於B27 到 F27 填入取有效數字後的B類不確定度。
4. 組合不確定度：在儲存格 B28 按 = ，輸入公式 **SQRT(B25\^2+B27\^2)** 計算時間的組合不確定度 $u ~\mathrm{(s)}$，再利用自動填滿的功能計算儲存格 C28 到 F28。於B29 到 F29 填入取有效數字後的組合不確定度。
5. 由於不確定度取完2位有效數字後皆取到小數點下第3位，時間平均值需要4捨5入到小數點下第3位，於 B23 到 F23 填入對應的數值。

<img height="60%" width="60%" src="https://imgur.com/fNs50UK.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">計算時間平均值及 A 類不確定度</div>
<br /><br />

### $t$ - $m$ 關係圖

選取儲存格 A1 到 F1 以及 A23 到 F23，插入只有數據點的 XY 散布圖。由於 Excel 預設的圖表格式相當醜，需要調整一下格式，並且加上縱軸、横軸物理量標籤及單位。

<img height="70%" width="70%" src="https://imgur.com/Y6M2KM2.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">插入只有數據點的 XY 散布圖</div>
<br />

<img height="60%" width="60%" src="https://imgur.com/D0rfJ7N.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">只有數據點的 <i>t</i> - <i>m</i> 關係圖</div>
<br /><br />

接下來將 B29 到 F29 的不確定度用**誤差槓** (error bar) 加入數據圖中。在圖上點擊滑鼠左鍵，再按右上角的 + 號，將滑鼠滑標移到選單中**誤差線**右方的三角形，再點選**其他選項**。
<img height="100%" width="100%" src="https://imgur.com/geBkpwG.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">插入誤差線</div>
<br /><br />

點選圖上的 X 軸誤差線，按鍵盤上的 Delete 刪除 X 軸誤差線。點選誤差線選項最下方的**指定值**，正、負錯誤值皆為儲存格 B29 到 F29。
<img height="100%" width="100%" src="https://imgur.com/2Z7GUwo.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">誤差線選項</div>
<br />

<img height="30%" width="30%" src="https://imgur.com/AZqDICB.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">自訂誤差線</div>
<br />

<img height="60%" width="60%" src="https://imgur.com/zavKxhS.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">包含誤差槓的 <i>t</i> - <i>m</i> 關係圖</div>
<br /><br />

在圖上點擊滑鼠左鍵，再按右上角的 + 號，將滑鼠滑標移到選單中**趨勢線**右方的三角形，再點選**其他選項**。
<img height="100%" width="100%" src="https://imgur.com/cQXouyN.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">插入趨勢線</div>
<br /><br />

趨勢線選項中有6種不同的函數類型，這裡我選擇**多項式**，冪次為2，如果勾選最下方的**在圖表上顯示方程式**，可以得到擬合後的函數。我通常會將函數記錄在其它地方，圖上只保留趨勢線。
<img height="100%" width="100%" src="https://imgur.com/xByKoEs.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">趨勢線選項</div>
<br />

可以用內建的函式計算最接近直線的斜率、縱軸截距、決定係數 $R^2$，語法分別為

SLOPE(縱軸資料, 橫軸資料)
INTERCEPT(縱軸資料, 橫軸資料)
RSQ(縱軸資料, 橫軸資料)

<br />

為了使數據點不要集中在圖上的某一區，可以適當地調整坐標軸範圍。
<img height="30%" width="30%" src="https://imgur.com/SjfWpxA.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">調整坐標軸範圍</div>
<br />

<img height="60%" width="60%" src="https://imgur.com/Ew5wNcw.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">只保留趨勢線、調整坐標軸範圍的 <i>t</i> - <i>m</i> 關係圖</div>
<br /><br />

### $\log t$ - $\log m$ 關係圖

從 $t$ - $m$ 關係圖可以看出兩者的關係不是線性的，若假設 $t^a \propto m^b$，則
$$
\log (t^a) = \log (m^b) ~\Rightarrow~ a \log t = b \log m ~\Rightarrow~ \log t = \frac{b}{a} \log m
$$
可以從 $\log t$ - $\log m$ 關係圖的最接近直線斜率得到 $b/a$。

在儲存格 B30 按 = ，輸入公式 **LOG10(B1)** 計算儲存格 B1 資料以 10 為底的對數值；在儲存格 B31 按 = ，輸入公式 **LOG10(B23)** 計算儲存格 B23 資料以 10 為底的對數值；再利用自動填滿的功能計算儲存格 C30 到 F31。
<img height="60%" width="60%" src="https://imgur.com/qmngWxT.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">計算 log <i>t</i> 和 log <i>m</i></div>
<br /><br />

仿照前面的作法，將儲存格 A30 到 F31 的資料作圖，再加上線性擬合的結果。由於上圖的最接近直線斜率約為 -0.51，我們可以推測 $t \propto m^{-1/2}$。
<img height="60%" width="60%" src="https://imgur.com/lMKMtn4.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">log <i>t</i> - log <i>m</i> 關係圖</div>
<br /><br />

### $t$ - $m^{-1/2}$ 關係圖

在儲存格 B32 按 = ，輸入公式 **B1^(-1/2)** 計算儲存格 B1 資料開根號、取倒數的量值，再利用自動填滿的功能計算儲存格 C32 到 F32 對應的資料。
<img height="60%" width="60%" src="https://imgur.com/A3QkOy2.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center">計算 <i>m</i>^-1/2</div>
<br /><br />

仿照前面的作法，將儲存格 A32 到 F32 以及 A23 到 F23 的資料作圖，再加上線性擬合的結果，斜率約為 2.1435，縱軸截距約為 -0.0265，決定係數約為 0.9953。
<img height="60%" width="60%" src="https://imgur.com/a9hAUlE.png" style="display: block; margin-left: auto; margin-right: auto;"/>
<div style="text-align:center"> <i>t</i> - <i>m</i>^-1/2 關係圖</div>
<br /><br />

## 結語

Excel 計算擬合直線或曲線時，只會考慮資料點本身，不會考慮縱軸誤差槓，如果想要得到更符合物理科標準的擬合結果，請改用 [SciDAVis](http://scidavis.sourceforge.net/) 這類更專業的工具，詳細的作法請參考〈[SciDAVis 教學 1：XY 散佈圖基本使用方法](https://hackmd.io/@yizhewang/r1dFK0NTH)〉、〈[SciDAVis 教學 2：作圖技巧及化直](https://hackmd.io/@yizhewang/BJYxS4rTH)〉。
<br /><br />

---

###### tags:`Excel`