10 thủ thuật xây dựng Dashboard chuyên nghiệp với Google Sheets

Có thể bạn đã quen với việc sử dụng Google Sheets để sắp xếp và phân tích dữ liệu của mình. Nhưng bạn có biết rằng bạn có thể tạo một dashboard động để thực sự hiểu dữ liệu của mình dễ dàng bằng Google Sheets không?

Với một số kỹ thuật hữu ích mà chúng tôi liệt kê ngay bên dưới, bạn có thể tạo một số điểm hấp dẫn vào việc trình bày dữ liệu của mình. Dưới đây là 10 thủ thuật bạn nên thử khi tạo dashboard trong Google Sheets.

1. Thu thập thông tin của người dùng bằng Google form và sử dụng trên dashboard Google Sheets

Google form giúp bạn nhanh chóng và dễ dàng để thu thập dữ liệu. Các câu trả lời được thu thập trong Google Sheets có thể sử dụng để cung cấp thông tin cho dashboard.

Ví dụ: bạn có thể thực hiện một cuộc khảo sát về mức độ hài lòng của khách hàng hoặc báo cáo trạng thái từ các thành viên trong nhóm hoạt động của bạn, sau đó chuyển dữ liệu này thành một bản tóm tắt trực quan một trang, cung cấp cho bạn thông tin chi tiết tức thì về dữ liệu của mình. Hãy xem qua các bước thực hiệu siêu đơn giản sau.

Bước 1: Tạo form với Google

Tạo form Google trong Google Drive  bằng cách điều hướng đến:

Drive > New > More > Google Forms

Bước 2: Thiết lập Form

Tiếp theo, thiết lập Google Form của bạn bằng cách đặt tên cho biểu mẫu và thêm bất kỳ câu hỏi nào bạn có. Trong ví dụ này, chúng tôi đã tạo một biểu mẫu với một câu hỏi trắc nghiệm hỏi người dùng họ thích màu nào hơn (từ đỏ, xanh lam hoặc xanh lục):

Bước 3: Tạo dashboard Google Sheets

Hãy thu thập câu trả lời của bạn và thiết lập dashboard Google Sheets. Sau đó, chúng ta thêm một tab mới và tạo một bảng mới, sử dụng công thức countif để đếm số phiếu bầu cho mỗi màu và hiển thị số lượng này trong bảng. Sau đó, chúng ta sẽ thêm biểu đồ thanh và biểu đồ hình tròn để hiển thị số lượng một cách trực quan. Các bảng xếp hạng này sẽ cập nhật bất cứ khi nào các phiếu bầu mới được gửi.

2. Lấy dữ liệu bằng công thức LOOKUP

Nắm vững công thức tra cứu là một kỹ thuật quan trọng đối với nhiều dự án dữ liệu trong Google Sheets (và cả Excel). Công thức là trung tâm của  dashboard Google Sheets được hiển thị ở đầu bài đăng này và một kỹ thuật thật sự hữu ích, chúng tôi khuyên bạn nên đầu tư thời gian để thực hành kỹ thuật này. Bạn có thể sử dụng một số phương pháp bên dưới:

VLOOKUP là một công thức tra cứu theo chiều dọc tìm kiếm cột đầu tiên của một dải ô và khi nó tìm thấy trường hợp đầu tiên của kết quả (nếu có), nó trả về giá trị trong hàng đó từ cột của dải ô mà bạn chỉ định với giá trị chỉ mục, ví dụ:

=VLOOKUP(F1,A1:D20,4,FALSE)

Công thức này lấy cụm từ tìm kiếm trong ô F1, ví dụ như chuỗi “Kênh A” và tìm kiếm nó trong cột A. Ở lần so khớp đầu tiên, nếu nó tồn tại, (ví dụ: hãy tưởng tượng ô A10 chứa “Channel A”), nó trả về giá trị tương ứng với cột 4 của cùng hàng đó (trong trường hợp này là D10, có thể là con số bán hàng cho Channel A). Tìm kiếm thông qua số hoặc ngày trong cột tra cứu của bạn (cột đầu tiên) yêu cầu dữ liệu phải được sắp xếp để tránh trả về các giá trị không chính xác.

HLOOKUP là một hàm triển khai tra cứu theo chiều ngang của công thức vlookup. Tôi thấy nó hiếm khi được sử dụng nhưng cũng khá hữu ích để giữ tìm hiểu và sử dụng cho một số tình huống cụ thể.

INDEX & MATCH là hai công thức kết hợp với nhau để tạo ra các giải pháp tra cứu linh hoạt, mạnh mẽ. Chúng vượt trội hơn vlookup nhờ linh hoạt hơn và tránh một số cạm bẫy với vlookup. Tuy nhiên, chúng phức tạp hơn một chút để triển khai vì liên quan đến hai công thức lồng nhau.

Để tạo triển khai tương tự như chúng ta đã làm ở trên với vlookup, chúng ta có thể sử dụng công thức sau:

=INDEX(A1:D20,MATCH(F1,A1:A20,0),4)

Công thức tra cứu đa điều kiện (Multi-condition lookup formula): Đôi khi một công thức tra cứu đơn giản là không đủ. Ví dụ: bạn có thể cần tìm một kết quả dựa trên hai hoặc nhiều tham số (ví dụ: lưu lượng truy cập web từ một kênh cụ thể trong một tháng cụ thể). 

Giả sử chúng ta có bảng dữ liệu Google Analytics này và cần truy xuất số lượng kết quả Tìm kiếm vào tháng 1 năm 2015 (tức là câu trả lời của chúng ta phụ thuộc vào ba tiêu chí)

Giả sử chúng ta đã thiết lập một bảng phân giai đoạn cho các biểu đồ của chúng ta bên dưới. Để tra cứu giá trị mong muốn (trong trường hợp này là Tìm kiếm cho tháng 1 năm 2015):

Chúng ta có thể sử dụng công thức này trong ô C11:

=INDEX($D$2:$D$9,MATCH($A11&$B11&C$10,$A$2:$A$9&$B$2:$B$9&$C$2:$C$9,0))

Cho kết quả là 46,936 .

3. Áp dụng logic với các công thức có điều kiện

COUNTIF là công thức đếm các mục trong một phạm vi phù hợp với tiêu chí đã chỉ định. Nó hữu ích để thực hiện những việc như đếm các ô không trống trong một phạm vi hoặc đếm số lượng các mục cụ thể trong một phạm vi. Công thức là:

=COUNTIF(range, criterion)

COUNTIFS tương tự như công thức countif nhưng trả về kết quả dựa trên nhiều tiêu chí. Nói cách khác, nó đếm số lượng mục trong phạm vi đầu tiên phù hợp với tiêu chí đầu tiên VÀ cũng khớp với tiêu chí thứ hai trong phạm vi thứ hai VÀ một phạm vi thứ ba, v.v. Công thức hơi khác với công thức countif cơ bản, như sau:

=COUNTIFS(range1, criterion1, [range2, criterion2, ...])

SUMIF có cùng ý tưởng với countif, nhưng trả về tổng các giá trị. Có thể so khớp các tiêu chí trong một phạm vi, nhưng tính tổng các giá trị trong một phạm vi riêng biệt, đây là một tính năng thực sự hữu ích (ví dụ: hãy tưởng tượng một bảng có tên trong cột A và kết quả bán hàng trong cột B, sau đó công thức sumif có thể tính tổng các giá trị bán hàng cho tất cả các lần xuất hiện nói "Ben" từ danh sách tên). Công thức cho sumif là:

=SUMIF(range, criterion, [sum_range])

SUMIFS là phiên bản đa tiêu chí của sumif, vì vậy ý ​​tưởng giống nhau nhưng tổng được tính khi bạn khớp nhiều tiêu chí trong nhiều phạm vi. Một lần nữa, một công thức rất hữu ích:

=SUMIFS(sum_range, range1, criterion1, [range2, criterion2, ...])

4. Tự động hóa ngày tháng của bạn

Dashboard thường có thành phần ngày tháng, trong đó một biến số thay đổi theo thời gian và giá trị được minh họa trực quan trong dashboard. Có nhiều công thức / kỹ thuật khác nhau để tự động hóa quá trình này.

Công thức TODAY cung cấp ngày hiện tại, sẽ hiển thị ngày cuối cùng bảng tính được tính toán lại (ví dụ: khi bạn mở hoặc thực hiện thay đổi). Công thức là:

=TODAY()

Nếu bạn cũng muốn có yếu tố thời gian hiện tại trong bảng tính của mình, thì hãy sử dụng công thức NOW, công thức này trả về ngày và giờ bảng tính được tính toán lại lần cuối. Công thức là:

=NOW()

Cả hai hàm today và now đều có thể được đặt để cập nhật tự động, thay vì chỉ khi Sheets được tính toán lại. Đi tới File > Spreadsheet Settings và sau đó chọn “Thay đổi và hàng giờ” hoặc “Thay đổi và mỗi phút”.

Hãy cẩn thận khi chèn quá nhiều công thức này vào bảng tính của bạn vì  chung sẽ sẽ gây hại cho hiệu suất bảng tính của bạn.

Ví dụ về việc sử dụng công thức today sẽ là hiển thị tháng hiện tại trong dashboard của bạn, sử dụng sau :

=TEXT(TODAY(),"MMMM")

Đối với một ví dụ phức tạp hơn, hãy nghĩ đến việc thiết lập ngày bắt đầu và ngày kết thúc cho bảng dashboard, chúng t có thể nhập công thức bằng cách sử dụng hàm TODAY, đặt nó để cập nhật tự động, sau đó sử dụng thêm công thức để tạo ngày tháng khác.

Bạn có thể sử dụng công thức sau để tạo ngày đầu tiên của tháng trước ngày hiện tại :

=EOMONTH( TODAY(), -2 ) + 1

Sau đó, thể tiếp tục quay ngược thời gian, bằng cách thay đổi "-2" thành "-3" cho hai tháng trước, sau đó "-4", "-5" kêt thúc ở "-13", cho tháng hiện tại cộng với 12 tháng trước đó trong một bảng, bảng này sẽ tự động cập nhật khi chúng ta chuyển sang mỗi tháng mới.

Chúng ta cũng có thể lấy ngày đầu tiên của tháng hiện tại nhưng sớm hơn một năm, chẳng hạn như để so sánh số liệu bán hàng hiện tại với cùng kỳ năm ngoái, sử dụng công thức sau:

=EOMONTH( TODAY(), -13 ) + 1

Có thể có nhiều biến thể từ việc kết hợp công thức today, date, text và eomoth, để có được khoảng thời gian chính xác mà bạn muốn trong dashboard Google Sheets của mình và tự động cập nhật chúng để luôn cập nhật.

5. Thêm tương tác với xác thực dữ liệu

Sử dụng xác thực dữ liệu để thêm tính tương tác vào dashboard của bạn. Bạn có thể tạo menu thả xuống tiện lợi mà từ đó người dùng có thể chọn một thông số, ví dụ như kênh bán hàng hoặc thời gian cụ thể, sau đó thay đổi dữ liệu dựa trên lựa chọn này, vì vậy mọi biểu đồ sẽ tự động cập nhật. Đó là một kỹ thuật khá đơn giản nhưng lại có sức mạnh đáng kinh ngạc.

Trước tiên, hãy tạo một danh sách các lựa chọn để hiển thị cho người dùng, ví dụ: danh sách các kênh bán hàng, sau đó sử dụng tính năng Data > Validation trên danh sách các giá trị được đánh dấu, tạo menu người dùng nhập cho các kênh bán hàng:

Sau đó, người dùng có một menu thả xuống trong bảng tính của bạn, từ đó họ có thể chọn tham số mong muốn:

Dữ liệu trong bảng làm cơ sở cho biểu đồ được thay đổi dựa trên lựa chọn của người dùng từ menu thả xuống ở trên, bằng cách sử dụng một trong các công thức tra cứu từ bước 2.

6. Lập biểu đồ dữ liệu của bạn

Google có sẵn một bộ biểu đồ để sử dụng với dữ liệu của bạn. Được biết đến nhiều nhất là biểu đồ cột / thanh cũ đơn giản, biểu đồ hình tròn có nhiều dấu hiệu sai lệch, biểu đồ đường và biểu đồ phân tán. Ngoài ra, Google Sheets có khả năng tạo biểu đồ bản map, biểu đồ chuỗi thời gian tương tác,... cho phép bạn kết hợp các phương pháp trực quan hóa chuỗi dữ liệu khác nhau.

Ví dụ

Biểu đồ thanh đơn giản có thể được điều chỉnh thành biểu đồ thanh xếp chồng lên nhau, có thể được sử dụng để hình dung hai số liệu có liên quan, chẳng hạn như số lượng bán hàng đã được thực hiện cho đến nay, so với số lượng vẫn cần để đạt được mục tiêu.

Biểu đồ khu vực có thể được sử dụng để hiển thị so sánh dữ liệu, như được hiển thị trong ví dụ này về doanh số bán hàng tích lũy trong đợt bán hàng nhanh kỹ thuật số, hiển thị dữ liệu năm 2014 so với dữ liệu năm 2015:

7. Hiển thị xu hướng với biểu đồ sparklines (đừng gấp khúc)

Biểu đồ sparklines được tạo ra lần đầu tiên bởi nhà thống kê và trực quan hóa dữ liệu huyền thoại Edward Tufte. Chúng là những biểu đồ nhỏ, đơn giản không có trục, tồn tại bên trong một ô duy nhất. Chúng là một cách tuyệt vời, nhanh chóng để hiển thị kết quả một cách trực quan mà không cần đến sự phức tạp của một biểu đồ toàn diện. Biểu đồ này hoạt động tốt cho các tập dữ liệu dựa trên thang thời gian.

Một biểu đồ sparkline sẽ trông như thế này:

Công thức cho biểu đồ thu nhỏ trong Google Sheets là:

=sparkline(data,[options])

Trong đó data đề cập đến một loạt các giá trị để vẽ biểu đồ thu nhỏ. Đối số tùy chọn options được sử dụng để chỉ định những thứ như loại biểu đồ, màu sắc và các cài đặt cụ thể khác.

8. Áp dụng định dạng có điều kiện để hiển thị các thay đổi

Ẩn trong menu Custom Number Format là tùy chọn định dạng có điều kiện để đặt các định dạng khác nhau cho các số lớn hơn 0, bằng 0 hoặc nhỏ hơn 0.

Đây là một công cụ tuyệt vời để áp dụng cho các bảng trong dashboard Google Sheets của bạn, nơi dữ liệu đang thay đổi. Bằng cách thay đổi màu văn bản của ô trong bảng khi dữ liệu thay đổi, bạn có thể thu hút sự chú ý của người dùng.

Hãy xem xét bảng bán hàng sau có cột % thay đổi:

Bây giờ, hãy xem cùng một bảng với các màu và mũi tên được thêm vào để gọi ra cột % change 

Màu sắc giúp bảng của bạn trực quan hơn và làm nổi bậc các điểm cần lưu ý.

Cách thêm định dạng tùy chỉnh

1. Chọn một ô trong Sheets của bạn hoặc Sheets trống mới, hãy sao chép ba công thức CHAR sau (bạn có thể xóa chúng sau):

=char(A1)

=char(A2)

=char(A3)

Bây giờ, hãy sao chép và dán chúng dưới dạng giá trị trong Sheets của bạn để chúng trông giống như cột C và không còn là công thức nữa.

(Bạn sao chép dưới dạng các giá trị bằng cách sao chép, sau đó nhấp chuột phải vào một ô và chọn Paste special > Paste values only… )

Bạn sẽ cần sao chép chúng vào khay nhớ tạm của mình để có thể dán chúng vào công cụ Custom number format.

2. Đánh dấu cột% và chuyển đến menu định dạng số tùy chỉnh:

3. Thay đổi 0,00% trong hộp nhập Custom number formats thành:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

như thể hiện trong hình bên dưới

Những gì bạn đang làm là chỉ định định dạng số cho số dương trước tiên, sau đó là số âm và sau đó là giá trị 0, mỗi giá trị được phân tách bằng dấu chấm phẩy.

Sao chép các ký hiệu từ bước 1 (bạn sẽ phải thực hiện việc này riêng cho từng ký hiệu).

Sử dụng dấu ngoặc vuông để chỉ định màu bạn muốn, ví dụ [color50] cho màu xanh lục.

9. Định dạng như một người chuyên nghiệp!

Sau tất cả những nỗ lực đó để tìm ra những câu chuyện thực tế ẩn trong dữ liệu của bạn và làm cho chúng có thể truy cập được trong các biểu đồ và bảng, bạn nên nỗ lực một chút để tạo ra phiên bản cuối cùng. Hãy xem xét một số ý tưởng sau:

• Thay đổi màu của biểu đồ trong dashboard Google Sheets để phù hợp với thương hiệu của bạn
• Cung cấp cho tất cả các bảng một định dạng nhất quán , ví dụ: đường viền màu xám nhạt, hàng tiêu đề đậm với văn bản màu trắng và các hàng bóng mờ màu xám / trắng thay thế
• Loại bỏ các đường lưới . Tìm tùy chọn này trong menu Xem: View > Gridlines
• Thêm Logo của bạn vào đầu dashboard
• Ẩn tất cả các tab đang hoạt động ngoại trừ tab dashboard (không ảnh hưởng đến chức năng của dashboard)
• Sử dụng freeze panes, để khóa các hàng hoặc cột cụ thể, để nếu người dùng cuộn (các) hàng tiêu đề, chẳng hạn như sẽ bị khóa tại chỗ và tiêu đề và các tùy chọn nhập của người dùng sẽ luôn hiển thị. Nó được tìm thấy trong menu View:View > Freeze
• Xem dashboard ở chế độ toàn màn hình

10. Chia sẻ và xuất bản dashboard Google Sheets của bạn để mọi người cùng xem

Rất có thể bạn sẽ muốn chia sẻ dashboard của mình với đồng nghiệp, khách hàng và / hoặc thế giới. Có một số cách để làm điều này.

Trước tiên, bạn có thể nhấp vào nút  ở góc tShare rên cùng bên phải của màn hình, điều này sẽ mở ra ngăn tùy chọn chia sẻ:

Từ đây, bạn có thể nhập địa chỉ email để chia sẻ trực tiếp với đồng nghiệp hoặc bạn có thể lấy url chia sẻ và gửi email cho những người bạn muốn chia sẻ hoặc dán vào các kênh truyền thông xã hội.

Bạn có quyền kiểm soát quyền truy cập và liệu người nhận liên kết có thể xem, nhận xét hoặc chỉnh sửa dashboard hay không. 

Thứ hai, bạn có thể xuất bản dashboard Google Sheets của mình dưới dạng một trang web hoặc nhúng nó dưới dạng một thành phần trong một trang khác, bằng cách nhấp vào:

File > Publish to the web...

để đưa ra bảng tùy chọn này:

Việc xuất bản theo cách này sẽ hiển thị công khai dashboard Google Sheets của bạn. Ví dụ bên dưới là một dashboard được xuất bản dưới dạng web.

Hy vọng bài viết này đã cung cấp cho bạn những mẹo hữu ích mà bạn có thể ngay lập tức áp dụng vào dashboard của mình.

Để không bỏ lỡ những mẹo và thủ thuật tin học văn phòng tuyệt vời khác, hãy tham gia cộng đồng Gitiho ngay hôm nay.