Tags

(This is a repost from initiumlab.com by Chao Tianyi, click the link to read the original: Google Sheets 技巧總結)

與 Excel 一樣，Google Sheets 也是常用的數據清洗和分析工具，不同的是， Google Sheets 還支持在線協操和實時保存功能。當需處理的數據量級不是特別大，或是分析難度不是特別高時，Google Sheets 可以算作最順手、最高效的工具了。以下，我們總結出6個 Google Sheets 的使用技巧，幫助大家更加熟練地掌握它。

Copy and Paste Special

在 Google Sheets 的每個格子裡，數據存在的格式並不僅僅是文字或數字這麼簡單，有時它可能是一串公式，有時又會是邏輯判斷。所以，當需要使用複製粘貼功能時，別忘記確認你粘貼過去的內容是什麼。如果你需要的只是格子裡的值（value，即數字或文字）而非一串公式，那你需要在粘貼時點選「Paste Special」－「Values Only」（或者採用 Command+Shift+V），這樣，就可以防止粘貼一堆亂碼去新的表格了。

paste-special.png

Transpose

Transpose 能將橫向排列的數據改為縱向排列，或是將縱向排列的數據改為橫向排列。適用於原始數據擺放方式與預期不同的情況。使用方法很簡單，=transpose(X) 即可。

transpose.png

Split

當需要拆分「列」時，可以使用 Split。

=split(X, “?”, true)

split 函數的使用前提是，被拆分的列有一定規律，在你需要提取的信息和其他信息之間，有「-」、「,」這類符號或特定的文字。可作為拆分時的指示符號。

split-1.png

split-2.png

Concatenate / Join

學會了如何拆分，再來看一下如何合併吧。join 能夠將一系列數值合併，並以固定的分隔符號連結。

= join (“?”, X1, X2, X3,…..)

join-1.png

join-2.png

concatenate 也有類似功能，不過 concatenate 只會將值簡單拼接，任何符號在 concatenate 的作用下，都會失去意義，只是純粹的字符而已。

= concatenate (X1, “?”, “??”, X2, “???”,…)

concatenate-1.png

concatenate-2.png

Pivot Table

提起 Google Sheets 和 Excel 這類表格工具，必學技能一定包括數據透視表 (Pivot Table)——它可以快速匯總數據，支持動態的數據分析。在 Google Sheets 中，選中需要分析的區域，點選菜單欄「Data」－「Pivot Table」即可生成，之後便可在「Report Editor」中定義根據哪部分信息對原始數據作加總。

pt1.png

我們也可以利用 Pivot Table 來比較兩組數值的差異。首先將 A 和 B 兩組數據放進一張表格的同一列，並為該列取名「Data」，再在右側新增一列，填入左側數據來自 A 組或 B 組，取名「Source」。接著，創建「Pivot Table」，在「Report Editor」中，「Row」一項選擇「Data」,「Column」一項選擇「Source」，「Value」一項選擇任意項目，但需要在「Summarized by」菜單中選擇「COUNTA」——這樣，無論原始表格中的數據格式是數字或字符，都會被計數。而此時，Pivot Table 的結果會顯示，每一個值在 A 組和 B 組中出現的次數，藉此，即可清晰地比較兩組數據的異同。

pt2.png

Vlookup

vlookup 是一個按列進行查找和引用的工具。假設你有一本字典，你需要對照著這本字典，為某一篇文章裡出現的所有字作出解釋，這時，你需要做的，是在字典裡找到這些字，再將字典裏的解釋原封不動地搬運過來。這即是 Google Sheets 中 vlookup 函數運作的過程。

當使用 vlookup 時，你的手邊會出現兩張表格，表格1是你正在操作的那張，表格2是你即將搜尋的區域，相當於上述例子中的「字典」。你希望 Google Sheets 根據表格1中第A列的數值M，去表格2中找到M對應的數值N，並返回到表格1中的對應區域，即表格1的第B列。

在你希望返回數值 N 的地方輸入 vlookup 函數，=VLOOKUP(search_key, range, index, is_sorted)

search_key：即為你搜尋的依據，是兩張表格裡都出現的值，在上述例子中，應為 M；
range：是你搜尋的區域，在上述例子中，應為表格2；需特別注意的是，搜尋依據必須位於這個區域的第一列；
index：是指你期待的查詢結果，在搜尋區域中，處在第幾列，index 必須為整數。在上述例子中，應為表格2中，這個數字應為：N所在列－M所在列＋1；
is_sorted：如果你希望 vlookup 能夠返回精確的查詢結果，就填入 FALSE；如果你期待模糊查詢結果，則填入 TRUE。大多數情況下，這一欄都會填上 FALSE。

vlookup-1.png最終，敲下回車鍵，N 就會出現了。

與其他函數一樣，vlookup 的結果也可以在列的維度自動填充。不過，需要特別檢查原函數中「數據區域」的表達，必要時記得加上「＄」符號，鎖定區域，以免出現亂碼。

vlookup-2.png