Google Sheets 技巧總結

(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

 

 

 


Posted by: Bobo Wei

Leave a Reply