Excel trong Testing - Chapter 2

QA/QC Jan 28, 2021

Cùng chủ đề này trong chapter 1, mình đã chia sẻ về ứng dụng của hàm COUNTIF/COUNIFS cho việc thống kê bug.

Tiếp theo, mình sẽ giới thiệu thêm cho các bạn về một câu lệnh khá hay và tính ứng dụng cao. Đó chính là hàm "QUERY"

Đọc mang máng thì có vẻ như là query trong SQL, cơ bản thì cách sử dụng cũng khá giống đấy. Bạn nào đã biết sử dụng SQL trong Database thì cũng có thể sử dụng hàm này rất dễ dàng.

Đầu tiên mình giới thiệu qua về hàm "QUERY" trước đã nhé

Cú pháp

QUERY(data, query, [headers])

  • data - Phạm vi ô để thực hiện truy vấn.
  • query - Truy vấn cần thực hiện, được viết bằng Ngôn ngữ truy vấn API trực quan hóa của Google.(tham khảo link: https://developers.google.com/chart/interactive/docs/querylanguage)
  • headers - [TÙY CHỌN] - Số hàng tiêu đề ở đầu dữ liệu. Nếu bỏ qua hoặc đặt thành -1, giá trị được đoán dựa trên nội dung của dữ liệu.

Chi tiết hơn thì mọi người tham khảo link của bác Gu Gồ nhé

QUERY function - Docs Editors Help
Runs a Google Visualization API Query Language query across data.Sample UsageQUERY(A2:E6,“select avg(A) pivot B”)QUERY(A2:E6,F2,FALSE)SyntaxQUERY(data, query, [headers]) data

Còn bây giờ mình sẽ làm một ví dụ thực thế đã trải qua trong dự án.

Bài toán

Cần test chức năng ranking 100. Nghĩa là bảng xếp hạng 100 khách hàng có tích điểm cao nhất. Tuy nhiên điểm chỉ tính với bản ghi có ngày tạo lớn hơn ngày reset. Vì các bản ghi point có ngày tạo nhỏ hơn ngày reset sẽ không được tính.

Cách thực hiện test

B1: Tạo sẵn một file google sheet trống

B2: Mở database của dự án và thực hiện query để lấy dữ liệu từ bảng liên quan. Cụ thể lấy được toàn bộ bản ghi point có chứa các thông tin: [customer id, poin unit, created date] . Sau đó copy toàn bộ dữ liệu vừa lấy về vào file google sheet. Vậy là chúng ta đã có bảng dữ liệu gốc và đặt tên là [data point]

B3: Sau đó tiến hành lấy danh sách customer id, do bảng point có rất nhiều bản ghi trùng lặp customer id nên sẽ phả i dùng tính năng [Remove duplicate] để loại bỏ bản ghi trùng. Hãy copy cả cột customer_id sang một sheet khác, sau đó tiến hành loại bỏ bản ghi trùng theo hình bên dưới.

B4: Ta tạo tiếp sheet dùng để tính tổng point cho từng customer id với tên là [customer total point]. Copy danh sách customer id đã từ B3 vào cột A. đặt tên [Customer_id] ở ô A1, ô B1 đặt [Reset date], ô C1 đặt [total point]. như hình dưới

B5: Sử dụng hàm QUERY để tìm reset date cho từng customer

query(t_point_histories!$A$2:$E$1002, "SELECT E WHERE C=0 and B=" &A2 & "ORDER BY E DESC LIMIT 1", -1)

Trong đó
[t_point_histories!$A$2:$E$1002] phạm vi dữ liệu
[SELECT E WHERE C=0 and B=" &A2] sẽ lấy cột E, với điều kiện C = 0 và B = A2
[ORDER BY E DESC] sắp xếp từ lớn đến nhỏ với giá trị ở cột E
[LIMIT 1] chỉ lấy 1 bản ghi trên cùng

với câu lệnh trên ta sẽ tìm ra được ngày reset date mới nhất cho từng customer id

B6: Tính tổng point cho từng customer với điều kiện phải lớn hơn reset date

=IF(B2="",SUMIFS(t_point_histories!D:D,t_point_histories!B:B,A2),SUMIFS(t_point_histories!D:D,t_point_histories!B:B,A2,t_point_histories!E:E>B2))

Trong đó
[B2="",SUMIFS(t_point_histories!D:D,t_point_histories!B:B,A2)] B2 rỗng thì tính tổng point ở cột D trong sheet t_point_history với điều kiện B:B=A2

[SUMIFS(t_point_histories!D:D,t_point_histories!B:B,A2,t_point_histories!E:E>B2)] B2 khác rỗng thì sẽ tính tổng point thêm điều kiện create date của bản ghi phải lớn hơn reset date

B7: Cuối cùng, ta tạo tiếp một sheet nữa dùng để sắp xếp lại dữ liệu point từ cao xuống thấp. Đặt tên sheet = [Ranking], tạo cột xếp hạng từ 1 -> 100. Đặt tên cột tiếp theo = [customer id] và [point] rồi trỏ chuột vào ô B2 rồi tiến hành viết câu lệnh bên dưới

=QUERY('Total point'!A2:C185,"Select A, C order by C desc")

Trong đó:
['Total point'!A2:C185] phạm vi lấy dữ liệu
[Select A, C] chỉ lấy dữ liệu ở cột A, C
[order by C desc] sắp xếp dữ liệu theo cột C từ lớn đến nhỏ

Chạy lệnh trên là sẽ ra được kết quả mong muốn như hình dưới

Đã xong, cuối cùng hãy lấy danh sách trên so sánh với kết quả của Dev trên màn hình Ranking của hệ thống.

Chúc các bạn thành công, khi thực hiện test liên quan đến nghiệp vụ trên.

Hẹn gặp lại ở bài tiếp theo.

Hà Nội ngày 28/1/2021, Tác giả Vũ Lê Hoàng

Tags

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.