使用 Google 試算表繪製 XY 散佈圖

作者：王一哲
日期：2020/3/28

前言

我之前已經寫過兩篇使用 SciDAVis 繪製 XY 散佈的文章：〈SciDAVis 教學 1：XY 散佈圖基本使用方法〉、〈SciDAVis 教學 2：作圖技巧及化直〉，但是大多數的學生在電腦課只用過 Microsoft Excel 這類的試算表軟體，相較之下 SciDAVis 的使用方法比較沒有那麼直觀，不過我又不想要在課堂上教商用軟體。因此，我們改用免安裝的 Google 試算表取代 Excel，雖然 Google 試算表的功能比較少一點，但仍然可以處理數據、繪製圖形。

匯入資料檔

我們以木星的衛星資料為例，先從網路上找到 NASA 公布的資料 Jovian Satellite Fact Sheet，將資料依照我們的需求編輯後儲存成 csv 檔，這是我編輯後的資料檔下載連結，先將這個檔案上傳到自己 Google 雲端硬碟中。

上傳資料檔至 Google 雲端硬碟

在 Google 雲端硬碟中新增 Google 試算表檔案，瀏覽器會自動跳到試算表的編輯頁面。

新增 Google 試算表檔案

從選單中依序選取檔案 ⇒ 匯入，點選之前上傳的資料檔，再按下選取。

檔案 ⇒ 匯入

匯入檔案視窗

由於試算表裡沒有任何資料，我們可以選擇取代目前工作表將資料直接覆蓋在目前的工作表上，分隔符類型選取逗號，將文字轉換成數字、日期和公式選取是，最後按下匯入資料。

匯入檔案選項

匯入資料後的工作表

繪製 XY 散佈圖

我們想要使用匯入的資料驗證克卜勒第三行星定律，假設有數個天體繞著同一個天體公轉，其平均軌道半徑、也就是橢圓軌道的半長軸 $a$，與公轉週期$T$的關係為

$$\frac{a^3}{T^2}=定值$$

我之前有寫過另一篇文章〈克卜勒第三行星運動定律〉，但是文章中的圖片是用 Python 及 Matplotlib 套件繪製的，現在我們使用 Google 試算表試著畫出同樣的圖。

平均軌道半徑 a - 公轉週期 T 關係圖

先用滑鼠左鍵點選 B 欄，接著按住 Ctrl 鍵再用滑鼠左鍵點選 D 欄，從選單依序選取插入 ⇒ 圖表，但是預設的 XY 散佈圖會以左側的欄位資料作為橫軸，以右側的欄位資料作為縱軸，剛好和我想要繪製的圖表相反，需要手動修正。

選取 B、D 兩欄的資料

插入 ⇒ 圖表

預設的 XY 散佈圖格式

在圖表編輯器中找到 X 軸，點選欄位右方的三個直排的點，選取編輯，將選取資料範圍由 B1:B80 改成 D1:D80 再按下 確定，此時兩個軸都採用 D 欄的資料。

編輯 X 軸資料

編輯 X 軸選取資料範圍

修改橫軸資料後的圖表

在圖表編輯器中找到系列，點選欄位右方的三個直排的點，選取編輯，將選取資料範圍由 D1:D80 改成 B1:B80 再按下 確定，我們終於將兩個軸對應的資料互換。

編輯系列（縱軸）資料

編輯系列（縱軸）選取資料範圍

修改系列（縱軸）選取資料範圍後的圖表

接著修改圖表標題，先點選圖表中的標題，右方的圖表編輯器會自動跳到對應的欄位，在此處可以修改標題文字、標題字型、標題字型大小、標題格式以及標題文字顏色。

圖表標題格式

編輯圖表標題格式

接著用同樣的方式修改縱軸、橫軸標題內容及文字，再修改標籤文字格式。

編輯縱軸標題格式

編輯縱軸標籤文字格式

編輯橫軸標題格式

編輯橫軸標籤文字格式

最後點選圖表右上角的三個點，可以將圖表匯出成圖檔，下圖是匯出成 png 格式的 a - T 關係圖。

a - T 關係圖

log a - log T 關係圖

為了找出 $a$ 與 $T$ 在幾次方時兩者成正比，我們需要分別對兩者取對數值 (log)。在這類的試算表軟體中有內建三個相關的函數，分別是

1. LOG(值, 底數)，例如 LOG(8, 2) 就是以 2 為底數對 8 取對數值。
2. LOG10(值)，以 10 為底數取對數值，例如 LOG10(100) 就是以 10 為底數對 100 取對數值。
3. LN(值)，以歐拉數 e = 2.718... 為底數取對數值，例如 LN(100) 就是以 e 為底數對 100 取對數值。

在此我們使用 LOG10 即可。首先在儲存格 E1 輸入欄位標題 log T，再於儲存格 E2 輸入

=LOG10(D2)

按下 Enter 完成此儲存格的計算，接著將滑鼠游標移到儲存格 E2 的右下角，當游標變成黑色十字時，按住滑鼠左鍵向下拖曳到儲存格 E80，試算表會自動計算儲存格 D3 到 D80 對應的 LOG10 數值並分別存入儲存格 E3 到 E80。接著用同樣的方法，在 F 欄計算 B 欄資料對應的 LOG10 數值。

於儲存格 E2 輸入公式

完成計算後的 E、F 欄

我們將 $a$ - $T$ 關係圖複製、貼上，再分別修改縱軸、橫軸對應的欄位至 F 欄及 E 欄，並於圖表編輯器中勾選趨勢線、類型為線性，我們通常不會在圖表上顯示最接近直線的方程式及 $R^2$ 值，這樣圖表看起來很亂。

加上趨勢線

log a - log T 關係圖

我們另外挑一個儲存格，輸入計算最接近直線斜率的公式

=SLOPE(F2:F80,E2:E80)

公式中逗號前是縱軸資料，逗號後是橫軸資料。如果需要計算最接近直線的 Y 軸截距則改為

=INTERCEPT(F2:F80,E2:E80)

如果需要計算最接近直線的 $R^2$ 值則改為

=RSQ(F2:F80,E2:E80)

以下是 $\log ~a - \log ~T$ 關係圖線性擬合結果

$斜率 = 0.6664692962$
$截距 = 2.4605536302$
$R^2 = 0.9999548795$

最接近直線的斜率很接近$\frac{2}{3}$，因此我們可以推測 $a^3 \propto T^2$。

a³ - T² 關係圖

假設衛星的質量為 $m$、木星的質量為 $M$，衛星繞木星的公轉軌道接近圓形，軌道半徑為 $a$，衛星公轉所需的向心力由木星與衛星之間的萬有引力提供，因此

$$m \cdot \frac{4 \pi^2 a}{T^2} = \frac{GMm}{a^2} ~\Rightarrow~ \frac{a^3}{T^2} = \frac{GM}{4 \pi^2}$$

從網路上可以找到 NASA 公布的資料 Jupiter Fact Sheet，木星質量為 $1.89819 \times 10^{27} ~\mathrm{kg}$，將木星質量代入上式可得

$$\frac{a^3}{T^2} \approx 3.20705 \times 10^{15}$$

單位為 SI 制。為了驗證這個想法，我們先將 D、B 兩欄的資料單位換成 SI 制，再分別取平方及三次方，將計算後的資料分別存入 G、H 欄。於儲存格 G2 輸入的公式為

=(D2*24*60*60)^2

於儲存格 H2 輸入的公式為

=(B2*1000000)^3

計算 T² 及 a³ 值並存入 G、H 欄

接下來畫出 $a^3$ - $T^2$ 關係圖並加上最接近直線，線性擬合結果為

$斜率 = 3.20624 \times 10^{15}$
$截距 = 1.62670 \times 10^{28}$
$R^2 = 0.99618$

斜率與理論值 $3.20705 \times 10^{15}$ 相當接近。

a³ - T² 關係圖

結語

我之前也有寫過類似的文章〈最接近直線〉，使用的軟體是 LibreOffice Calc、SciDAVis、Python，但是許多同學在安裝軟體時會遇到困難。這次改用 Google 試算表不需要安裝，只要使用比較新的瀏覽器就能開啟，建議使用 Google Chrome、FireFox 或是以 Chromium 為基礎改寫的 Microsoft Edge。

參考資料

1. Jovian Satellite Fact Sheet https://nssdc.gsfc.nasa.gov/planetary/factsheet/joviansatfact.html
2. Google 試算表說明書 LOG https://support.google.com/docs/answer/3093495?hl=zh-Hant
3. Google 試算表說明書 LOG10 https://support.google.com/docs/answer/3093423?hl=zh-Hant
4. Google 試算表說明書 LN https://support.google.com/docs/answer/3093422?hl=zh-Hant
5. Google 試算表說明書 SLOPE https://support.google.com/docs/answer/3094048?hl=zh-Hant
6. Google 試算表說明書 INTERCEPT https://support.google.com/docs/answer/3093632?hl=zh-Hant
7. Google 試算表說明書 RSQ https://support.google.com/docs/answer/3094099?hl=zh-Hant
8. Jupiter Fact Sheet https://nssdc.gsfc.nasa.gov/planetary/factsheet/jupiterfact.html

---

HackMD 版本連結：https://hackmd.io/@yizhewang/rJiA3V38U