Hôm nay Saunguyen.pro sẽ sử dụng một đề thi thực hành tin học để minh họa cách sử dụng hàm INDIRECT kết hợp Data Validation giải bài toán Excel phụ thuộc lẫn nhau giữa các bảng dữ liệu.
Để dể hiểu các bạn xem bài tập dưới đây.
- Đầu tiên tìm hiểu về cú pháp hàm hàm INDIRECT và Data Validation.
Hàm INDIRECT: Trả về một tham chiếu từ chuỗi ký tự.
Cú pháp:
=INDIRECT(ref_text [, a1])
- ref_text là tham chiếu tới một ô (có thể là dạng A1 hoặc dạng R1C1), là tên định nghĩa của một tham chiếu, hoặc là một tham chiếu dạng chuỗi.
- a1 là giá trị logic xác định dạng tham chiếu bên trong ref_text.
- a1 = TRUE (hoặc là 1, hoặc không nhập) là kiểu tham chiếu A1
- a1 = FALSE (hoặc là 2) là kiểu tham chiếu R1C1
- Nếu ref_text không hợp lệ, Indirect sẽ báo lỗi #REF!
- Nếu ref_text chứa tham chiếu đến một bảng tính khác thì bảng tính này phải đang mở, nếu không, Indirect cũng báo lỗi #REF!
Data Validation là tính năng của Excel dùng để cố định dữ liệu của những ô tính được chọn.
Người nhập chỉ có thể điền những dữ liệu đã được thiết lập cho ô tính đó giúp tránh sai sót và nhầm lẫn trong các phiếu điền thông tin, kiểm kê,... từ đó việc thống kê dữ liệu cũng trở nên dễ dàng hơn.
- Data Validation tạo danh sách dạng sổ xuống trong excel.
- Data Validation thiết lập thông báo kiểu dữ liệu cho ô được chọn.
- Data Validation báo lỗi khi nhập sai dữ liệu.
Dùng hàm INDIRECT kết hợp Data Validation giải bài toán Excel phụ thuộc qua bài thi dưới đây
Xem đề ở trên:
Câu *: Nhập dữ liệu và định dạng bảng như đề:
- Theo đề bạn sẽ định dạng Kiểu VND; USD và bảng tương ứng:
+ Bạn chọn dữ liệu sau đó vào Format Cells, chọn mục Custom và gõ:
- #,##0 "VND"
- #.##0 "USD"
Câu 1: Lấy dữ liệu vào cột "Nhóm hàng" từ bảng "Mã nhóm hàng", không nhập trực tiếp
Theo đề ta dùng tính năng Data Validation
- B1.1: Quét vùng cần tạo Data Validation (cụ thể vùng A5:A10)
- B1.2: Vào Data chọn Data Validation
- B1.3: Chọn kiểu List; nguồn vùng dữ liệu =$H$17:$H$20
Câu 2: Tên hàng: Ứng với nhóm hàng lấy tên mặt hàng tương ứng?
- Để làm được câu này ta sử dụng kết hợp hàm INDIRECT và Data Validation.
B2.1: Lần lược chọn các tên hàng và đặt tên giống bảng Mã nhóm hàng
- NhomQuanAo (Ghế Sôfa, Tràng kỷ, Cầu thang, Tủ quần áo, Kệ bếp)
- NhomLinhKien (CD-Rom, DVD, CDRW, COMBO, Monitor LCD)
- NhomGiaDung (Somi dài tay, Somi ngắn tay, Kaki nam, Quần tây nam)
- NhomSon (Sơn ngoài trời, Sơn trong nhà, Sơn lót, Bôi trét)
Làm tương tự cho 2 nhóm mặt hàng còn lại; vào Name Manager kiểm tra lại nhé.
B2.2: Tên hàng: Ứng với nhóm hàng lấy tên mặt hàng tương ứng
- B2.2.1: Quét vùng cần tạo Data Validation (cụ thể vùng A5:A10)
- B2.2.2: Vào Data chọn Data Validation
- B2.2.3: Chọn kiểu List; nguồn vùng dữ liệu =INDIRECT(A5)
Câu 3: Lấy dữ liệu vào cột Hãng SX/Cơ sở/Kích cỡ" Ứng với Nhóm hàng là Hãng SX/Cơ sở/kích cớ tương ứng của Nhóm hàng đó?
Ví dụ: chọn Nhóm Gia dụng thì cột C sẽ hiện thị các cơ sở như Nghệ Nam, Du Bắc, Kim Hồng.
B3.1: Bạn chọn vùng Tên sản phẩm cơ sở và đặt tên như bước B2.1
Tuy nhiên bạn phải đặt tên khác với tên các nhóm hàng và chứa các nhóm hàng:
Ví dụ: coso_NhomGiaDung, coso_NhomQuanAo,...
hoặc đơn giản hơn bạn có thể đặt như: NhomGiaDung01, NhomQuanAo01, NhomSon01, NhomThietBi01
B3.2: Cơ sở, kích thước: Ứng với nhóm hàng lấy tên cơ sở, kích tthước tương ứng với 3 bước sau:
- B3.2.1: Quét vùng cần tạo Data Validation (cụ thể vùng D5:D10)
- B3.2.2: Vào Data chọn Data Validation
- B3.2.3: Chọn kiểu List; nguồn vùng dữ liệu
=INDIRECT(A5&"01")
Vì sao công thức trong Data Validation là =INDIRECT(A5&"01")
A5: Lấy tên nhóm như cột A5, A6, A7,..
&"01": Tức là ta nối thêm ký tự "01" theo tên đã đặt bước B3.1
Thật đơn giản, bạn đã có đáp án hoàn chỉnh và chính xác.
Câu 4: Cột "Số lượng" nhập giá trị lớn hơn 0, nhập sai hiện thông báo như hình dưới
Câu 5: Điền đơn giá vào cột E, đơn giá được lấy ở bảng đơn giá ứng với mỗi Nhóm hàng, tên hãng, tên hàng.
Có thể hiểu đơn giản: Với nhóm Gia Dụng mà ở cở sở Nghệ Nam, sản phẩm Cầu thang giá là 2.340.000đ, nếu Kệ bếp thì giá 594.000đ; Nếu ở cơ sở Du Bắc thì giá Cầu thang giá là 1.980.000đ, nếu Kệ bếp thì giá 648.000đ;
- Nhóm sản phẩm khác nhau được lấy tương ứng với tên sản phẩm và cơ sở tương ứng.
- Để xử lý đơn giá ở câu này chút nâng cao về cách khéo léo sử dụng kết hợp nhiều hàm và cả tư duy của bạn. Các hàm Saunguyen.pro sẽ sử dung bao gồm: vlookup (hoặc Hlookup), match và if.
B5.1: Theo Saunguyen.pro để dể dàng lựa chọn, truy xuất dữ liệu từ các bảng Nhóm Hàng khác nhau, bạn nên đặt tên cho từng bảng Nhóm hàng đó. Do vậy theo đề bài ta có 4 bảng với 4 nhóm hàng ta sẽ đặt tên các đơn giá theo cú pháp trong Difine Name:
- dg_NhomGiaDung
- dg_NhomThietBi
- dg_NhomQuanAo
- dg_NhomSon
B5.2. Tiếp theo bạn hãy dành chút thời gian để xem lại cú pháp của 3 hàm được chúng tôi liệt kê ở cuối bà viết.
B5.3. Cột đơn giá bạn nhập công thức gợi ý như sau:
=IF(A5="NhomGiaDung";VLOOKUP(C5;dg_NhomGiaDung;MATCH(B5;$A$16:$F$16;0);0);IF(A5="NhomLinhKien";VLOOKUP(C5;gd_NhomLinhKien;MATCH(B5;$A$22:$F$22;0);0);IF(A5="NhomQuanAo";VLOOKUP(C5;dg_NhomQuanAo;MATCH(B5;$A$28:$E$28;0);0);IF(A5="NhomSon";VLOOKUP(C5;dg_NhomSon;MATCH(B5;$A$34:$E$34;0);0);""))))
Giải thích:
- =IF(A5="NhomGiaDung" : hàm if sẽ so sách và xác định chính xác nhóm hàm được lấy ở cột A
- =MATCH(B5;$A$16:$F$16;0);0 : Hàm match để xác định tọa độ, vị trí
- =VLOOKUP(C5;dg_NhomGiaDung;MATCH(B5;$A$16:$F$16;0);0) : Lấy đơn giá với các diều kiện tương ứng
Ví dụ tôi muốn lấy giá sản phẩm "Cầu thang" ở co sở "Nghệ Nam" trong "Nhóm gia dụng" thì lần lượt ta dùng các hàm.
- Hàm if để xem Nhóm sản phẩm nào đang được chọn
- Hàm vlookup tra cứu lấy giá Cầu thang ở cơ sở Nghệ Nam
- Hàm match tìm vị trí "Cầu thang" ở đâu trong các sản phẩm của nhóm hàng Gia dụng. (Ở đây là vị trí thứ 4 tính cả cột "Tên sản phẩm cơ sở" vì lựa chọn từ A2:F5.
Lồng ghép các hàm if ta có công thức hoàn chỉnh như sau:
=IF(A5="NhomGiaDung";VLOOKUP(C5;dg_NhomGiaDung;MATCH(B5;$A$16:$F$16;0);0);IF(A5="NhomLinhKien";VLOOKUP(C5;gd_NhomLinhKien;MATCH(B5;$A$22:$F$22;0);0);IF(A5="NhomQuanAo";VLOOKUP(C5;dg_NhomQuanAo;MATCH(B5;$A$28:$E$28;0);0);IF(A5="NhomSon";VLOOKUP(C5;dg_NhomSon;MATCH(B5;$A$34:$E$34;0);0);""))))
Và kết quả:
Câu 6: Thành tiền = số lượng * đơn giá * tỷ giá biết NhomQuanAo, NhomGiaDung giữ nguyên tỷ giá các nhóm còn lại 23.500, yêu cầu dùng công thức mảng?
- Theo đề các sản phẩm đơn giá là USD thì tỷ giá là 23.500 VNĐ
- Dùng công thức mảng: Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Bạn có thể hình dung mảng là một hàng hoặc một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất.
Công thức ở câu 6 tương đối đơn giản, bạn dùng if để so sách và công thức sẽ là:
{=IF((A5:A10<>"NhomLinhKien")*(A5:A10<>"NhomSon");D5:D10*E5:E10;D5:D10*E5:E10*23500)}Để sử dụng công thức mảng bạn phải làm như sau:
B6.1: Chọn vùng cần tính F5:F10
B6.2: Nhập công thức:
=IF((A5:A10<>"NhomLinhKien")*(A5:A10<>"NhomSon");D5:D10*E5:E10;D5:D10*E5:E10*23500)
B6.3. Nhấp tổ hợp phím: Ctrl+Shift+Enter
Công thức dạng mảng sẽ có 2 dấu ngoặc nhọn {}
Câu 7: Ngày tháng: yêu cầu nhập bất cứ thời gian nào trong năm 2020
- Như vậy bạn nhập ngày tháng trong năm 2020 là được.
Câu 8: Ghi chú: Nếu số lượng >=200 và có ngày lập phiếu trong tháng 5/ 2020 thì đánh dấu XXX còn lại để trống, yêu cầu dùng công thức mảng?
- Tương tự câu 6, câu 8 các bạn làm công thức so sách như sau:
{=IF((D5:D10>=200)*(MONTH(G5:G10)=5);"XXX";"")}
Câu 9: tô màu cho 3 dòng có "Số lượng" lớn nhất và "Ghi chú" là XXX?
Với câu 9 bạn dùng tính năng: Conditional formatting
- Tuy nhiên để đơn giản chọn các sản phẩm có số lượng mua xếp lớn nhất nên thêm 1 cột dùng hàm Rank
- Sau đó dùng tính năng Conditional formatting với công thức:
=AND(OR($J5=1;$J5=2;$J5=3);$H5="XXX")
Tìm hiểu một số hàm có sử dụng trong bài viết này.
1. Hàm IF trong Excel có cú pháp là
=IF(logical_test;[value_if_true];[value_if_false])
- - Logical_test là điều kiện kiểm tra;
- - Value_if_true là giá trị nếu điều kiện đúng;
- - Value_if_false là giá trị nếu điều kiện sai.
2. Công thức hàm VLOOKUP:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)Trong đó:
- Lookup_value: Giá trị cần dò tìm
- Table_array: Bảng giới hạn để dò tìm.
- Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
- Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.
- Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
- Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.
3. Hàm match là một hàm tìm kiếm vị trí của một dữ liệu trong một vùng dữ liệu, thực hiện công việc đưa ra số thứ tự vị trí của dữ liệu đó trong một khu vực cột hoặc hàng nào đó.
=MATCH(lookup_value, lookup_array, [match_type])
- Lookup_value: giá trị cần xác định vị trí trong mảng dữ liệu lookup_array
- Lookup_array: là mảng dữ liệu để xác định vị trí số thứ tự.
- Match_type: không cần thiết hay bắt buộc, có thể là -1, 0 hoặc 1,mặc định là 1.
- 1 hoặc bỏ trống: hàm Match sẽ tìm giá trị lớn nhất
- 0: hàm Match sẽ tìm chính xác
- -1: hàm Match sẽ tìm giá trị nhỏ nhất