Hai bài trước chúng ta đã có khái niệm cơ bản về Excel, các kiểu dữ liệu và định dạng bảng làm việc trong Excel. Trong bài này, chúng ta sẽ tìm hiểu các bước cơ bản để giải quyết các bài toán trong Excel. Ở bài viết này, tôi sẽ không nói cụ thể về hàm trong Excel, trong bài này chỉ hướng dẩn khái quát làm sao đó cho bạn có cách tư duy nhanh các bài toán mà bạn muốn giải quyết. Giải quyết một yêu cầu trong Excel là sự kết hợp các hàm, phép toán và dữ liệu đã có để.
Để có thể giải quyết một bài toán trên Excel, đầu tiên bạn phải biết đó là phân tích:
- Kết quả cuối cùng bạn muốn đạt được là gì?
- Các yêu cầu để đạt được kết quả là gì?
- Các dữ liệu bạn đã có, dữ liệu cần tham chiếu, so sánh , tính toán.
Từ các phân tích trên bạn có thể tìm kiếm các hàm phù hợp, xử lý các phép toán để đạt được kết quả. Về hàm, có thể bạn đã biết hoặc bạn có thể tra hàm sử dụng công cụ có sẵn trong Excel:
Chúng ta sẽ đi vào ví dụ cụ thể để dễ hình dung hơn, ta có dữ liệu như sau:
Bạn sẽ đi giải quyết các yêu cầu đưa ra kết quả cho những ô trong vùng tô màu như sau:
- Tên hàng: dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1.
- Mã QG: dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1.
- Xuất xứ: dựa vào Mã QG vừa tìm ra tra với BẢNG 2
- Trị giá: Số lượng * Đơn giá. Biết rằng đơn giá dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1, kết hợp với Loại hàng để lấy giá trị hợp lý.
- Phí vận chuyển: Số lượng * Giá VC. Biết rằng, giá vận chuyển dựa vào Mã QG, tra trong BẢNG 2.
- Thành tiền: Trị giá + Phí vận chuyển. Nếu bán ra trong tháng 5 thì giảm 5% Thành tiền.
Chúng ta lần lượt đi phân tích và giải quyết từng yêu cầu:
Yêu cầu 1: Tên hàng: dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1. Vậy:
Tra trong BẢNG 1 để lấy ra Tên hàng, như vậy là sẽ có hàm tham chiếu dữ liệu, ở đây bảng phụ trình bày theo chiều dọc, vậy chúng ta sẽ dùng hàm VLOOKUP, VLOOKUP(tham số tham chiếu, vùng dữ liệu, cột trả về,FALSE).
- Vùng dữ liệu sẽ là $A$14:$E$16, ở đây ta dùng dấu $ do vùng dữ liệu này là cố định.
- Giá trị trả về chính là cột trả về là cột 2 của BẢNG 1
- Vậy ta sẽ có công thức VLOOKUP(giá trị tham chiếu,$A$14:$E$16,2,FALSE)
Ở đây, ta thấy là chưa có giá trị tham chiếu. Theo yêu cầu giá trị tham chiếu chính là dựa vào 4 ký tự đầu trong mã hàng của bảng dữ liệu chính, mà mã này lại chứa 5 ký tự nhưng ta chỉ cần có 4 ký tự đầu, vậy ta sẽ lấy từ trái qua 4 ký tự, như vậy ta sẽ dùng hàm Left, LEFT (A3,4), để trích các ký tự cần so sánh.
Sau khi có giá trị tham chiếu, ta sẽ có công thức:
=VLOOKUP(LEFT(A3,4),$A$14:$E$16,2,FALSE)
Yêu cầu 2: Mã QG: dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1. Ở yêu cầu này tương tự như Yêu cầu 1, nhưng giá trị trả về là cột thứ 3 của BẢNG 1.
Kết quả ta có:
=VLOOKUP(LEFT(A3,4),$A$14:$E$16,3,FALSE)
Yêu cầu 3: Xuất xứ: dựa vào Mã QG vừa tìm tra với BẢNG 2. Tương tự như Yêu cầu 1 và Yêu cầu 2, nhưng ở đây, chúng ta tra cứu dữ liệu trong BẢNG 2, bảng này được định dạng ngang nên chúng ta dùng hàm HLOOKUP.
Kết quả ta có:
=HLOOKUP(C3,$H$12:$J$14,2,FALSE)
Yêu cầu 4: Trị giá: Số lượng * Đơn giá. Biết rằng đơn giá dựa vào 4 ký tự đầu của Mã hàng, tra trong BẢNG 1, kết hợp với Loại hàng để lấy giá trị hợp lý. Ở đây:
Số lượng là dữ liệu đã có sẵn.
Đơn giá sẽ dựa vào 2 giá trị tham chiếu là 4 ký tự đầu của Mã hàng, LEFT(A3,4), cho biết đó là mặt hàng nào. Ký tự cuối , RIGHT(A3,1), cho biết chất lượng của mặt hàng đó. Một mặt hàng có hai giá tri:
- Cao cấp tương ứng với C trong Mã hàng,
- Thường tương ứng với T trong Mã hàng
Dựa vào bảng số liệu và sự phân tích ta thấy Đơn giá của Mặt hàng sẽ thay đổi dựa vào C và T.
- Nếu ký tự cuối là C thì lấy số lượng nhân cho Đơn giá Cao Cấp
- Nếu ký tự cuối là T thì lấy số lượng nhân cho Đơn giá Thường.
- Để lấy được Đơn Giá Cao cấp trong BẢNG 1 dựa vào 4 ký tự đầu của Mã hàng: VLOOKUP(LEFT(A3,4),$A$14:$E$16,4,FALSE)
- Để lấy được Đơn Giá Thường trong BẢNG 1 dựa vào 4 ký tự đầu của Mã hàng: VLOOKUP(LEFT(A3,4),$A$14:$E$16,5,FALSE)
Ở đây chúng ta thấy, chỉ có 2 điều kiện là C và T, vậy ta sẽ đặt điều kiện để tính tính:
- Nếu ký tự cuối mã hàng là C, RIGHT(A3,1)=”C”, thì lấy E3 nhân với VLOOKUP(LEFT(A3,4),$A$14:$E$16,4,FALSE)
- Ngược lại thì lấy E3 nhân với VLOOKUP(LEFT(A3,4),$A$14:$E$16,5,FALSE)
Kết quả ta có:
=IF(RIGHT(A3,1)=”C”,E3*VLOOKUP(LEFT(A3,4),$A$14:$E$16,4,FALSE),E3*VLOOKUP(LEFT(A3,4),$A$14:$E$16,5,FALSE))
Yêu cầu 5: Phí vận chuyển: Số lượng * Giá VC. Biết rằng, giá vận chuyển dựa vào Mã QG, tra trong BẢNG 2.
Số lượng đã có trong bảng dữ liệu.
Vậy ta cần tham chiếu Mã QG trong bảng dữ liệu chính và BẢNG 2 để lấy ra giá trị Giá VC. Do BẢNG 2 là bảng dữ liệu ngang nên chúng ta sẽ dùng hàm HLOOKUP: HLOOKUP(C3,$H$12:$J$14,3,FALSE)
=E3*HLOOKUP(C3,$H$12:$J$14,3,FALSE)
Yêu cầu 6: Thành tiền: Trị giá + Phí vận chuyển. Nếu bán ra trong tháng 5 thì giảm 5% Thành tiền.
- Thành tiền = G3+H3
- 5% của Thành tiền =(G3+H3)*5%
- Tháng 5= MONTH(F3)=5
Như vậy, Nếu là Tháng 5 thì Thành tiền là Thành tiền trừ 5% Thành tiền và ngược lại Thành tiền bằng Thành tiền.
Kết quả ta có:
=IF(MONTH(F3)=5,(G3+H3)-((G3+H3)*5%),G3+H3)
Sau khi hoàn thành, ta được kết quả như sau:
Trong quá trình xử lý các yêu cầu, bạn có thể sử dụng các vùng trống bên ngoài để kiểm tra các công thức nhỏ, trước khi ghép nối nó thành một công thức hoàn chỉnh. Việc xử lý từng yêu cầu nhỏ có thể sẽ giúp bạn xử lý yêu cầu lớn chính xác và hiệu quả hơn.
