14. Hàm VLOOKUP hay còn gọi là hàm tham chiếu cột – Khác với hàm if, hàm vlookup hay còn gọi là hàm tham chiếu cột, nó lấy thông tin từ bảng phụ với điều kiện dò tìm ở bảng chính để điền vào cột nào đó của bảng chính nếu thỏa mãn điều kiện dò tìm. Chú ý: Bài toán về hàm vlookup thường cho ra khi bắt ta tham chiếu cột từ một bảng phụ cho trước. Cú pháp hàm excel nâng cao – vlookup: =VLOOKUP(lookup_value,table_array,row_index_num,range_lookup) Giải thích cú pháp: lookup_value: là giá trị để tham chiếu có thể là một ký tự, chuỗi ký tự, tham chiếu hay giá trị nào đó của bảng chính. table_array: vùng chứa dữ liệu của bảng phụ (lấy vùng bằng cách dùng chuột quét cả bảng phụ). Luôn phải để ở dạng địa chỉ tuyệt đối bằng cách quét xong rồi nhấn F4 để có dấu $ đằng trước nhé row_index_num: giá trị cần lấy ra thuộc cột mấy trong bảng phụ range_lookup: muốn tìm chính xác hay tương đối (0 là chính xác, 1 là tương đối) thường giá trị này là 0 nhé. Ví dụ hàm vlookup trong excel – Dưới đây chỉ là những ví dụ cơ bản hàm vlookup thôi nhé bạn nên nhớ cần học thuộc hàm và biết vận dụng trong những trường hợp cụ thể nhé! Ví dụ 1: Hãy điền tên vào cột tên hàng dựa vào ký tự của cột mã hàng lấy tham chiếu từ bảng phụ. Hàm CHOOSE tìm chuỗi kí tự Hàm CHOOSE tìm chuỗi kí tự Tóm tắt ngắn ví dụ: nghĩa là bạn nhìn cột mã hàng ở bảng chính nó sẽ trùng với mã hàng ở cột bảng phụ bây giờ mình phải làm thế nào để từ dữ liệu cột mã hàng bảng chính tham chiếu xuống bảng phụ cho nó hiểu G =”Gà”, V=”Vịt”, C=”Chó” để điền vào cột tên hàng ở bảng chính. Từ ví dụ ta có công thức =VLOOKUP(C6,$B$11:$C$14,2,0) Bạn nhìn màu từ công thức trong hình và xem mình lấy hay quét dữ liệu như thế nào. C6 (lookup_value): giá trị để tham chiếu nó là 1 ký tự trùng với ký tự từ bảng phụ $B$11:$C$14 (table_array): vùng chứa dữ liệu bảng phụ quét cả bảng phụ và ấn F4 để ở dạng địa chỉ tuyệt đối nhé 2 (row_index_num): do lấy dữ liệu cột thứ 2 của bảng phụ lên là ,2. Nếu lấy cột thứ 3 thì , 3 hoặc ,4 ,5 ,6… 0 (range_lookup): dò tìm chính xác bạn cứ ,0 cho mình nhé. Lưu ý: Bài toán ít khi cho ở dạng ví dụ 1 mà thường cho ở dạng ví dụ 2 sau đây thường kết hợp với một hàm khác nữa như hàm left, hàm right, hàm mid… thì mới tham chiếu được ký tự và điền vào bảng chính được. 15. Hàm dò tìm theo hàng: HLOOKUP Hàm HLOOKUP là hàm excel nâng cao dùng dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nó sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy trên hàng mà chúng ta chỉ định. Hàm HLOOPUP thường dùng để điền thông tin vào bảng dữ liệu lấy từ bảng dữ liệu phụ. Cú Pháp: Hlookup (giá trị đem dò, bảng giá trị dò, số thứ tự hàng cần lấy, phạm vi tìm kiếm) Giải thích: Giá trị tìm kiếm: giá trị dùng để tìm kiếm, có thể là một chuỗi ký tự, một tham chiếu, một giá trị nào đó của bảng chính. Giá trị này phải có tên trong vùng dữ liệu tìm kiếm. Vùng dữ liệu tìm kiếm: Vùng chứa dữ liệu cần tìm. Vùng chứa dữ liệu tìm kiếm này thường nằm ở bảng phụ. Lưu ý: trong vùng dữ kiệu tìm kiếm này phải chứa giá trị tìm kiếm Hàng trả về giá trị tìm kiếm: giá trị lấy ở hàng thứ mấy trong bảng phụ Tham số: muốn tìm chính xác hay tương đối. Tham số này điền “0” nếu bạn muốn dò tìm chính xác, điền số “1” nếu muốn dò tìm giá trị tương đối. Hàm dò tìm theo hàng: HLOOKUP Hàm dò tìm theo hàng: HLOOKUP. 16. Hàm Counif Cú pháp: COUNTIF(range, criteria) Tác dụng: Đếm số lần xuất hiện của 1 nội dung trong 1 vùng Ứng dụng: Đếm số thứ tự dòng nghiệp vụ phát sinh trong sổ Nhật ký chung theo số chứng từ Trong đó: Range: Cột số chứng từ trong sổ NKC Criteria: số chứng từ trên từng dòng dữ liệu HEO Countif NKC 17. Hàm SUMIF, SUMIFS Cấu trúc: SUMIF(range, criteria, [sum_range]) Tác dụng: Tính tổng số phát sinh theo 1 điều kiện Ứng dụng: Tính số phát sinh của các tài khoản trong bảng cân đối số phát sinh HEO sumif bang cdps Kết chuyển số dư cuối kỳ của các tài khoản trong sổ Nhật ký chung HEO Ket chuyen voi ham sumif Nâng cao: Có thể sử dụng hàm SUMIFS để tính tổng theo nhiều điều kiện hơn so với hàm SUMIF. Ví dụ như tính số phát sinh bên Nợ của Tài khoản 111 trong thời gian từ 01/01 đến 31/03 (chỉ tính cho 3 tháng trong khi sổ NKC có dữ liệu đủ 12 tháng). Khi đó có thể kiểm tra bảng CDPS cho bất kỳ khoảng thời gian nào đều được. 18. Hàm HOUR, MINUTE, SECOND – Công thức: = HOUR(ô chứa thời gian) ; =MINUTE(ô chứa thời gian) ; =SECOND(ô chứa thời gian) – Công dụng: Hàm này xuất ra kết quả là giờ, phút, giây trong ô chứa thời gian. 19. Hàm TEXT Cấu trúc: TEXT(value, format_text) Tác dụng: Trình bày lại 1 giá trị (số hoặc ngày tháng) vào trong 1 đoạn text mà vẫn giữ được định dạng của loại dữ liệu đó Ứng dụng: Viết lại thông tin liên quan tới thời gian lập các báo cáo kế toán tổng hợp HEO ham Text Bang CDPS 20. Hàm MAX Cú pháp: MAX(number1, [number2], …) Tác dụng: Tìm ra số có giá trị lớn nhất trong các số Ứng dụng: Tính số dư cuối kỳ của các tài khoản trong Sổ cái, Sổ chi tiết, Bảng cân đối số phát sinh HEO Ham Max CDPS 21. Hàm SUMPRODUCT Cú pháp: SUMPRODUCT(array1, [array2],…) Tác dụng: Tính tổng / đếm theo nhiều điều kiện, theo mảng. Hàm này thay thế cho hàm COUNTIFS, SUMIFS trong hầu hết các trường hợp Ứng dụng: Tính số phát sinh trong kỳ của các tài khoản trong bảng Cân đối số phát sinh, không phân biệt tài khoản cấp 1 hay cấp 2. Thay thế cho hàm SumIf HEO Sumproduct KTTH 22. Hàm LEFT/ RIGHT – Cú pháp: LEFT(text, [num_chars]) – Tác dụng: Xác định phần bên trái của 1 đoạn text, 1 chuỗi ký tự Ứng dụng: Kết hợp với hàm LEN để xác định số tài khoản cấp 1, cấp 2 trong danh mục tài khoản. Sử dụng trong hàm Sumproduct để tính cho bất kỳ loại tài khoản nào dựa theo mảng là phần bên trái của cột tài khoản Nợ, tài khoản Có trong Nhật ký chung Cú pháp hàm Right: Right(text, num_chars) Trong đó: Text: Biểu thức ký tự Num_chars: số lượng ký tự cần lấy ra từ bên phải của Text Chức năng: Kết quả là biểu thức ký tự được lấy ra từ bên phải của Text với số lượng ký tự lấy ra được xác định bằng num_chars. Ví dụ: Phân loại Số hiệu nhân viên theo Mã nhân viên Tại ô D2 ta thực hiện: =Right(C2,3) 23. Hàm IF – Cú pháp hàm: IF(điều kiện,”GIÁ TRỊ ĐÚNG”,”GIÁ TRỊ SAI”). – Công dụng: Kiểm tra điều kiện đúng. Ví dụ: Chúng ta muốn kiểm tra xem ô H có thỏa mãn điều kiện lớn hơn 7 hay không, thỏa mãn điều kiện trả về giá trị Correct, không thỏa mãn điều kiện trả về giá trị Incorrect các hàm excel 24. Hàm AND – Cú pháp hàm: IF(AND(điều kiện),“giá trị đúng”,“giá trị sai”) – Công dụng: Hàm AND cho phép bạn kiểm tra điều kiện và cho ra kết quả đúng. Nếu điều kiện được thỏa mãn, hàm sẽ xuất ra giá trị sai khi có bất kỳ điều kiện nào đó sai. Trở lại ví dụ đã lấy với hàm IF, bạn có thể kiểm tra đồng thời 2 điều kiện điểm XDD lớn hơn 6 và QLCVĐXH lớn hơn 7, Nếu cả 2 giá trị đúng hàm trả về giá trị Correct, nếu giá trị sai hàm trả về Incorrect. các hàm excel 25. Hàm OR – Cú pháp hàm: =IF(OR(điều kiện),“giá trị đúng”,“giá trị sai”) – Công dụng: Đây là một trong các hàm trong excel cho ra các kết quả đúng hoặc sai. Tuy nhiên, ngược lại với hàm AND, hàm excel OR cho về giá trị đúng nếu như trong điều kiện có bất kỳ điều kiện nào đúng, và trả về giá trị sai nếu như tất cả các điều kiện đều sai. Vẫn quay trở lại ví dụ bên trên, khi kiểm tra lại điều kiện với hàm OR. Sử dụng công thức: =IF(OR(H3>6,I3>7),”correct”,”incorrect”) các hàm excel 26. Hàm DATEDIF – Công thức hàm: =DATEDIF(thời gian 1, thời gian 2,“d”) – Công dụng: Hàm dùng để tính số khoảng cách ngày, tháng, năm trong trang tính excel. Phần đối số ở vị trí thứ 3 chính là số mà bạn muốn xuất ra. Các hàm trong excel cơ bản quy định “d” biểu thị ngày, “m” biểu thị tháng, “y” biểu thị năm. Bạn có thể thay các đối số để đưa ra kết quả bạn muốn.