Vài thủ thuật nhỏ khi làm việc với Google Sheets
Một trong những task quan trọng của QA là quản lý documents, số liệu TCs để report, tracking Schedule ... Bạn chắc hẳn sẽ gặp tình trạng lặp đi lặp lại thao tác cập nhật nếu số liệu thay đổi hoặc bất cập trong quá trình sử dụng Google Sheets.
Sau đây mình xin chia sẻ đến các bạn 1 số thủ thuật nhỏ giúp việc quản lý documents trở nên dễ thở hơn, cuối bài mình sẽ cung cấp 1 số templates áp dụng các thủ thuật này vừa để các bạn có thể thử vọc vạch tìm hiểu thêm vừa có thể dùng chung cho các dự án.
1. Đếm dữ liệu với hàm [COUNTIF]
Đây là hàm dùng khá là thường xuyên, trả về biến số đếm dựa trên điều kiện của 1 vùng dữ liệu. QA thường dùng trong trường hợp muốn đếm tổng số lượng TCs hoặc số TCs theo kết quả PASSED/FAILED/PENDING.
Cách dùng:
=COUNTIF(A1:A10,">20")
=COUNTIF(A1:A10,"PASSED")
=COUNTIF(A1:A10,"PASSED")
Cú pháp:
=COUNTIF(range, criterion)
range - Phạm vi áp dụng điều kiện
criterion - điều kiện để áp dụng cho phạm vi
1 số lưu ý với criterion:
- Nếu range có dữ liệu text thì criterion phải là một string. criterion có thể chứa các ký tự đại diện bao gồm ? để phù hợp với bất cứ ký tự đơn hoặc * để phù hợp với không hoặc nhiều ký tự liên tiếp nhau.
Ex:
=COUNTIF(A1:A100;"*") => Đếm tổng số hàng có dữ liệu bất kỳ từ row 1 đến row 100. Thường được sử dụng để đếm tổng số TCs có trong 1 sheet.
=COUNTIF(A1:A100;"*") => Đếm tổng số hàng có dữ liệu bất kỳ từ row 1 đến row 100. Thường được sử dụng để đếm tổng số TCs có trong 1 sheet.
- Nếu range chứa số, criterion có thể là một chuỗi hoặc một số. Nếu một số được cung cấp, mỗi cell trong range được so sánh giá trị với điều kiện để trả về kết quả.
Ex: COUNTIF(A1:A10,">20") => đếm tổng số hàng có giá trị lớn hơn 20
2.Tham chiếu dữ liệu của một cell trong sheet khác của cùng một document
Giả sử bạn có nhiều sheet trong cùng một tài liệu và bạn muốn tổng kết dữ liệu của một số cell trong từng sheet vào cùng 1 sheet.
Cách dùng:
=Login!G5 => lấy giá trị của cell G5 ở sheet “Login”
_Cú pháp: _
=[sheetname]![cell]
sheetname - tên sheet chứa dữ liệu cần tham chiếu
cell - chỉ định cell chứa dữ liệu cần tham chiếu
3. Tính tổng theo các dòng chẵn/lẻ
Đôi lúc bạn sẽ gặp tình huống cần tính toán riêng kết quả theo các dòng chẵn hoặc lẻ. Ví dụ như ở bên dưới:
Ở đây mình có thể dùng công thức:
=SUMPRODUCT((MOD(ROW(E17:E76);2)=1)*(E17:E76)) => tính tổng các dòng lẻ trong range từ E17 tới E76
=SUMPRODUCT((MOD(ROW(E17:E76);2)=0)*(E17:E76)) => tính tổng các dòng chẵn trong range từ E17 tới E76
4.Tham chiếu dữ liệu từ documents khác
Bạn cần lấy dữ liệu từ nhiều documents khác nhau ? Đơn cử như bạn cần 1 file để tracking tiến độ thực hiện TCs hàng ngày, số liệu lấy từ file TCs tổng .
IGM example (updating)
Cách dùng:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1e72XRAL8esYPsWKiDitkn2SPPXpauvCZmkNdl7UqXsA/edit#gid=0","Overview!D17") => Lấy dữ liệu của file tài liệu có URL https://docs.google.com/xxx , phạm vi dữ liệu cần lấy là cell D17 của sheet Overview
Cú pháp:
IMPORTRANGE(spreadsheet_key, range_string)
spreadsheet_key - URL của tài liệu nơi mà dữ liệu sẽ được tham chiếu.
- Lưu ý: giá trị của spreadsheet_key phải được để trong dấu ngoặc kép hoặc phải được tham chiếu tới 1 cell có chứa dữ liệu URL
range_string - Là một chuỗi, theo format "[sheet_name!]range" (e.g. "Sheet1!A2:B6" hay "A2:B6") để chỉ định rõ phạm vi dữ liệu cần tham chiếu
Big Big TIP:
Có 1 vấn đề là công thức trên chỉ tham chiếu được 1 ô được chỉ định trong chuỗi (Ex: "Overview!D17") , nếu bạn làm việc với các documents lớn cần lấy dữ liệu tới nhiều cell như D18, D19....D100~ .... bạn không thể sử dụng chức năng copy công thức trên bằng cách chọn cell D17 rồi kéo xuống như thông thường được, nó vẫn sẽ copy chính nội dung của cell D17 cho các cell ở dưới.
IGM example (updating)
Các bạn phải thay đổi công thức một chút:
=importrange("https://docs.google.com/spreadsheets/d/1e72XRAL8esYPsWKiDitkn2SPPXpauvCZmkNdl7UqXsA/edit#gid=0",address(row()+3,column()-2,,,"Overview"))
Bạn cần thay thế địa chỉ của cell cần tham chiếu bằng địa chỉ của row và column.
Ở đây bạn cần lưu ý :
Ở đây bạn cần lưu ý :
row(): số row mà bạn cần tham chiếu
column(): số column mà bạn cần tham chiếu
[+3, -2]: tham số để chỉ định chính xác cell bạn cần lấy giá trị ở document khác.
(Ex: Bạn copy công thức này vào cell D17 trong tài liệu mới để tham chiếu dữ liệu của cell B20 thì ở column bạn phải -2 => D-2=B và ở row bạn phải +3 => 17+3=20 )
(Ex: Bạn copy công thức này vào cell D17 trong tài liệu mới để tham chiếu dữ liệu của cell B20 thì ở column bạn phải -2 => D-2=B và ở row bạn phải +3 => 17+3=20 )
Bây giờ chúng ta có thể copy công thức bằng cách kéo xuống và nó sẽ làm việc một cách hoàn hảo. Hàng () và cột () thay đổi khi chúng ta kéo công thức xuống.
IGM example (updating)
Với vài thủ thuật nhỏ ở trên hi vọng sẽ phần nào giúp bạn có thể nắm bắt và sử dụng Google Sheets hiệu quả hơn trong công việc hằng ngày của mình.
Link templates
File TCs template (bao gồm sheet Overview để sum-up TC ở các sheet và 1 sheet tạo TCs mẫu):
https://docs.google.com/spreadsheets/d/1e72XRAL8esYPsWKiDitkn2SPPXpauvCZmkNdl7UqXsA/edit?usp=sharing
File tracking tiến độ thực thi TCs (tham chiếu đến file TCs tổng ở trên, form điền số TCs execute theo kế hoạch/thực tế mỗi ngày):
https://docs.google.com/spreadsheets/d/1J2d5-tVfZvtbQJng3BCjjyw1wsvBVro8vkiEsxcUEF8/edit?usp=sharing
- Bài viết được chia sẽ trên trang www.tuhocexcel.net , Nếu chia sẽ vui lòng ghi rõ nguồn gốc bài đăng.
- Mọi thắc mắc, góp ý vui lòng mail về địa chỉ : tuhocexcel2018@gmail.com
hoặc liên hệ qua : http://www.tuhocexcel.net/p/lien-he-tu-hoc-excel.html
