Nhóm Hàm Tham Chiếu Trong Excel (Bài 5)

--- Bài mới hơn ---

  • Cách Làm Việc Với Nhiều Trang Worksheet Trong Excel 2003
  • Cách Viết X Ngang Trong Excel
  • Cách Viết X Bình Phương Trong Excel 2007
  • Cách Dùng Hàm Đếm Ô Có Dữ Liệu Trong Excel Chi Tiết Nhất
  • Cách Lặp Lại Tiêu Đề Trong Excel 2007, 2010 Và 2013 Khi In
  • Trong bài học này, chúng tôi sẽ trình bày 3 hàm trong nhóm hàm tham chiếu gồm hàm VLOOKUP, hàm HLOOKUP và hàm INDEX. Công dụng, cách sử dụng và ví dụ chúng tôi sẽ trình bày ngay bên dưới.

    Nhóm hàm tham chiếu trong excel – Hàm VLOOKUP

    Hàm VLOOKUP được sử dụng để trích xuất thông tin từ bảng tham chiếu theo chiều dọc. Hãy quan sát bảng tham chiếu 1 (Bảng chức vụ) ở hình bên trên để thấy rõ cách thể hiện thông tin của bảng này.

    Cú pháp hàm VLOOKUP trong excel

    VLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số cột tham chiếu, Cách dò)

    Trong đó: Trị dò thuộc bảng chính; Chỉ số cột tham chiếu tính từ trái qua phải và bắt đầu từ 1; Cách dò là 0 nếu Trị dò là chuỗi hoặc ngày tháng năm, Cách dò là 1 nếu Trị dò là số.

    Ví dụ sử dụng hàm VLOOKUP trong excel (Lập công thức cho cột CHỨC VỤ của bảng Danh sách nhân viên công ty ABC) dựa vào bảng CHỨC VỤ

    B9 là Mã chức vụ ( Trị dò); B3:C6 là bảng chức vụ ( Bảng tham chiếu luôn luôn dùng địa chỉ tuyệt đối); 2 là Chỉ số của cột tham chiếu (cột Chức vụ trong bảng chức vụ); vì B9 chứa chuỗi nên Cách dò là 0.

    Kết quả sau khi hoàn thành

    Nhóm hàm tham chiếu trong excel – Hàm HLOOKUP

    Nếu VLOOKUP dò tìm theo chiều dọc thì HLOOKUP dò tìm theo chiều ngang (Bảng khu vực)

    Cú pháp hàm HLOOKUP trong excel

    HLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số dòng tham chiếu, Cách dò)

    Ví dụ sử dụng hàm HLOOKUP trong excel (Lập công thức cho cột KHU VỰC của bảng Danh sách nhân viên công ty ABC) dựa vào bảng KHU VỰC

    Kết quả sau khi hoàn thành

    Nhóm hàm tham chiếu trong excel – Bài tập thực hành

    Cho bảng tính như hình và lập công thức cho những cột tô màu vàng

    Câu 1: SỐ NGÀY THUÊ = NGÀY ĐI – NGÀY ĐẾN

    Câu 2: TIỀN PHÒNG = Đơn giá phòng (Tra trong bảng ĐƠN GIÁ PHÒNG) * SỐ NGÀY THUÊ

    Câu 3: Dựa vào 2 ký tự cuỗi của MÃ THANH TOÁN và tra trong bảng ĐƠN GIÁ KHẨU PHẦN ĂN để lập công thức cho cột ĐƠN GIÁ KHẨU PHẦN ĂN

    Câu 4: TIỀN ĂN = SỐ NGÀY THUÊ * ĐƠN GIÁ KHẨU PHẦN ĂN

    Câu 5: THÀNH TIỀN = TIỀN PHÒNG + TIỀN ĂN

    Câu 6: TỶ LỆ GIẢM dựa vào SỐ NGÀY THUÊ và tra trong bảng TỶ LỆ GIẢM

    Câu 7: PHẢI TRẢ = THÀNH TIỀN – TỶ LỆ GIẢM

    --- Bài cũ hơn ---

  • Những Giá Trị Khác Biệt Và Duy Nhất Trong Excel: Cách Tìm Kiếm, Lọc, Lựa Chọn Và Đánh Dấu
  • Hướng Dẫn Tìm Kiếm Và Thay Thế Trong Excel Từ Cơ Bản Đến Nâng Cao
  • Cách Tìm Kiếm Và Thay Thế Trong Excel
  • Cách Tìm Giá Trị Thứ N Trong Excel (Lớn Nhất, Lớn Thứ 2, Nhỏ Thứ 2…)
  • Những Cách Tìm Số Ô Trống Trong Excel Đơn Giản Nhất
  • Cách Sử Dụng Hàm Vlookup Để Tham Chiếu Nhiều Kết Quả Cùng Lúc Trên Excel

    --- Bài mới hơn ---

  • Cách Sử Dụng Hàm Xlookup Trong Excel
  • Hướng Dẫn Sử Dụng Hàm Index Và Match Sự Thay Thế Tuyệt Vời Cho Vlookup
  • Hàm Vlookup Và Hlookup Trong Excel.
  • Cách Sử Dụng Hàm Hlookup Và Vlookup Chi Tiết Nhất
  • Cách Sử Dụng Hàm If Kết Hợp Vlookup Để Dò Giá Trị Theo Điều Kiện
    • Hàm IF: Là hàm điều kiện có chức năng trả về kết quả A nếu như đáp ứng được một điều kiện cụ thể nào đó, còn nếu không đáp ứng được điều kiện ấy thì thay vào đó nó sẽ trả về kết quả B.
    • Hàm SMALL: Trả về giá trị nhỏ nhất nằm ở vị trí thứ “k” trong chuỗi
    • Hàm INDEX: Trả về yếu tố chuỗi dựa trên số lượng cột và hàng bạn sử dụng
    • Hàm ROW: Trả về số thứ tự theo hàng
    • Hàm COLUMN: Trả về số thứ tự theo cột
    • Hàm IFERROR: Cố định vị trí sai số.

    =IFERROR(INDEX($B$3:$B$13, SMALL(IF(D$2=$A$3:$A$13, ROW($B$3:$B$13)-2,””), ROW()-2)),””)

    Hoặc

    =IFERROR(INDEX($B$3:$B$13,SMALL(IF(D$2=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,””), ROW()-2)),””)

    Công thức đầu tiên có thể trông gọn nhẹ hơn nhưng công thức thứ hai lại được sử dụng phổ biến hơn do ít phải điều chỉnh (cấu trúc cú pháp cũng như cách thức hoạt động của nó sẽ được giải thích cụ thể hơn ở phần sau)

    Kết quả ta sẽ có hình như sau:

    Nếu tìm thấy kết quả giống, hàm ROW sẽ trả về kết quả là số thứ tự theo cột của ô trống đầu tiên trong phạm vị trả kết quả (B3:B13), sau đó bạn phải tính toán 1 con số để trừ bớt đi sao cho khoảng cách giữa khu vực cần tìm và khu vực trả về kết quả là bằng 1. Lý do là bởi ta đang đi tìm vị trí tương đối của yếu tố đầu tiên trong chuỗi. Với ví dụ minh họa, ta cần trừ bớt đi 2 bởi vì phần khu vực trả về kết quả nằm bắt đầu ở hàng thứ 3. Nếu khu vực đó nằm ở hàng thứ 2 thì bạn phải trừ bớt đi 1, và tương tự như vậy.

    Hoặc cách khác bạn có thể làm là sử dụng cách diễn đạt sau: ROW(lookup_column)-MIN(ROW(lookup_column))+1, khi đó kết quả trả về vẫn giống tương tự nhưng không cần phải điều chỉnh gì thêm mặc cho vị trí của khu vực trả về kết quả là ở đâu. Đối với ví dụ minh họa, ta sử dụng cấu trúc sau: ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1.

    Nếu không tìm được kết quả giống, thì kết quả trả về sẽ có dạng string rỗng (“”)

    Tiếp theo, hàm SMALL(tập hợp, k) được sử dụng để xác định vị trí cần trả về của từng kết quả tham chiếu.

    Với thông số “tập hợp” đã được xác định từ trước, việc cần quan tâm là xác định số thứ tự “k” , đồng nghĩa với giá trị nhỏ nhất thứ “k” được trả về. Để làm được điều đó, bạn cần phải sử dụng một bộ đếm số dưới dạng hàm ROW()-n, trong đó n là số thứ tự hàng của ô tính đầu tiên trừ đi 1. Trong ví dụ này, ta điền công thức vào dải ô D3:D7, cho nên ROW()-2 trả về kết quả là 1 đối với ô D3 (hàng thứ 3 trừ đi 2 đơn vị) và trả về kết quả là 2 đối với ô D4 (hàng thứ 4 trừ đi 2 đơn vị), và lặp lại tương tự như vậy.

    Kết quả là, hàm SMALL sẽ đặt giá trị nhỏ nhất của tập hợp trong ô D3, giá trị nhỏ thứ nhì vào ô D4, và cứ tiếp diễn như thế. Từ đó, công thức dài loằng ngoằng phức tạp như ban đầu được giản lược đi thành dạng đơn giản hơn như sau:

    Hàm này hoạt động khá đơn giản. Hàm này được dùng để lấy giá trị của một thành phần trong tập hợp dựa trên số thứ tự theo hàng của nó

    Cuối cùng, công thức được kết thúc bằng hàm IFERROR để xử lý một số lỗi có thể xảy ra, điều này là không thể tránh khỏi bởi vì bạn không thể tính được có bao nhiều kết quả tham chiếu giống nhau được trả về cho từng giá trị khác nhau, từ đó có thể trong quá trình sao chép công thức sang các ô tính khác bạn không thể biết chính xác nên sao chép sang bao nhiêu ô là đủ. Để tránh xuất hiện những cảnh báo lỗi không cần thiết, hàm này sẽ trả về các chuỗi ký tự dạng string rỗng (“”) để thay thế.

    Lưu ý: Bạn cần phân biệt cách sử dụng tham chiếu tuyệt đối và tham chiếu tương đối trong các công thức Excel. Tất cả tham chiếu sẽ không bị thay đổi ngoại trừ cột tham chiếu tương đối của giá trị cần tham chiếu (D$2), điều này là yêu cầu bắt buộc trong quá trình sao chép công thức sang các cột bên cạnh, để từ đó cho ra các kết quả phù hợp với từng cột cụ thể.

    Sau khi tổng hợp lại, ta có được công thức tổng quát dành cho hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo cột trong Excel:

    Trong đó:

    • m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
    • n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị

    Lưu ý: Trong ví dụ trên, cả m và n đều có giá trị bằng 2 bởi vì khu vực trả kết quả và khu vực chứa công thức đều bắt đầu từ hàng thứ 3. Tùy theo từng bảng tính cụ thể mà con số này có thể khác nhau.

    Trong tr=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,””), COLUMN()-4)),””) ường hợp bạn vẫn muốn tham chiếu nhiều kết quả cùng lúc nhưng các kết quả trả về phải nằm trên cùng 1 hàng thay vì cột thì ta sử dụng công thức như sau: =IFERROR(INDEX($B$3:$B$13,SMALL(IF($D3=$A$3:$A$13,ROW($A$3:$A$13)-MIN(ROW($A$3:$A$13))+1,””),COLUMN()-4)), “”)

    Hoặc

    Cách thức hoạt động của nó tương tự như ví dụ trước, ngoại trừ 1 điều là bạn sử dụng hàm COLUMN thay vì hàm ROW nhằm xác định vị trí trả về kết quả giống với giá trị tham chiếu: COLUMN()-n. Trong đó n là số thứ tự theo cột của ô tính đầu tiên chứa công thức, trừ đi 1 đơn vị. Trong ví dụ này công thức được nhập vào dải ô E2:H2. Với việc cột E nằm ở thứ tự số 5 thì khi đó n sẽ bằng 4.

    Lưu ý: Để có thể sao chép chính xác công thức sang các hàng khác, lưu ý hãy để thông số tham chiếu dạng tuyệt đối theo cột và tương đối theo hàng, chẳng hạn: $D3

    Sau khi tổng hợp lại, ta có được công thức tổng quát dành cho hàm Vlookup tham chiếu nhiều giá trị cùng lúc theo hàng trong Excel:

    Trong đó:

    m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị

    Dùng Vlookup để tham chiếu nhiều kết quả cùng lúc với điều kiện ràng buộc

    n là số thứ tự theo cột của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị

    Bạn có thể đã quen với việc sử dụng Vlookup để tra cứu kết quả trong Excel thông qua 1 điều kiện ràng buộc. Nhưng nếu như bạn cần phải tham chiếu nhiều kết quả dựa trên nhiều hơn 1 tiêu chí? Chẳng hạn với ví dụ vừa rồi, nếu như có thêm 1 cột tiêu chí về “Tháng” được bổ sung vào, làm cách nào để bạn có thể phân loại được danh sách các mặt hàng mà một nhà phân phối cung cấp theo từng tháng?

    IFERROR(INDEX(return_range, SMALL(IF(1=((-(lookup_value1=lookup_range1)) * ( -(lookup_value2=lookup_range2))), ROW(return_range)-m,””), ROW()-n)),””)

      Trường hợp trả về nhiều kết quả theo cùng 1 cột

    Trong đó

    • m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
    • n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị

    =IFERROR(INDEX($C$3:$C$30, SMALL(IF(1=((-($E$3=$A$3:$A$30)) * (-($F$3=$B$3:$B$30))), ROW($C$3:$C$30)-2,””), ROW()-2)),””)

    Giả sử danh sách nhà phân phối (lookup_range1) nằm ở dải ô A1:A30, danh sách các tháng (lookup_range2) nằm ở dải ô B1:B30, nhà phân phối cần quan tâm (lookup_value1) nằm ở ô tính E3 và tháng cần quan tâm (lookup_value2) nằm ở ô tính F3, khi đó ta có công thức sau:

      Trường hợp trả về nhiều kết quả theo cùng 1 hàng

    Nếu bạn muốn lấy nhiều kết quả tham chiếu cùng lúc nhưng chịu phụ thuộc vào nhiều điều kiện ràng buộc khác nhau, lời khuyên là nên sử dụng bố cục hàng ngang để kết quả tham chiếu được sẽ được bố trí theo cùng 1 hàng. Hãy sử dụng công thức sau:

    • m là số thứ tự theo hàng của ô tính đầu tiên của khu vực trả kết quả, trừ đi 1 đơn vị
    • n là số thứ tự theo hàng của ô tính chứa công thức ban đầu, trừ đi 1 đơn vị

    Cách thức hoạt động

    Và đây là kết quả thu được:

    Tương tự, bạn vẫn có thể dùng hàm Vlookup để tham chiếu kết quả với 3, 4 hay nhiều điều kiện ràng buộc hơn.

    Về cơ bản, công thức tham chiếu nhiều kết quả cùng lúc bằng hàm Vlookup ràng buộc bởi nhiều điều kiện vẫn có cách thức hoạt động giống như cách giải thích ở ví dụ đầu tiên. Điểm khác biệt duy nhất nằm ở chỗ hàm IF bây giờ có nhiệm vụ phải xử lý nhiều điều kiện ràng buộc hơn.

    Kết quả của mỗi quá trình so sánh 2 biến lookup_value=lookup_range là một chuỗi các kết quả logic dạng TRUE (nếu đúng) hoặc FALSE (nếu sai). Dấu gạch nối đôi (-) được dùng để biến cách kết quả logic trên thành dạng ký tự số 1 và 0. Bởi vì bất cứ số nào nhân với 0 cũng cho ra kết quả là 0, bạn sẽ chỉ thấy các số 1 minh họa các thành tố đáp ứng được điều kiện ràng buộc xuất hiện trong chuỗi kết quả. Giờ việc bạn cần làm đơn giản chỉ là so sánh chuỗi kết quả cuối cùng với 1 sao cho hàm ROW trả về kết quả là số lượng các hàng đáp ứng được tất cả điều kiện ràng buộc, còn không kết quả sẽ là một ký tự rỗng.

    Chú ý quan trọng: Tất cả các công thức Vlookup trong bài viết này đều ở dạng công thức dành cho tập hợp. Vì thế, công thức sẽ lặp lại các thành tốt của chuỗi mỗi lần dữ liệu gốc bị thay đổi, hoặc trang tính được tính toán lại. Đối với các bảng tính lớn với số lượng hàng và cột lên đến hàng trăm thì quá trình này sẽ làm giảm tốc độ phản hồi của Excel một cách tương đối.

    Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

    Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học.

    --- Bài cũ hơn ---

  • Cách Sử Dụng Hàm Vlookup Kết Hợp Hàm Left Trong Excel
  • Sửa Lỗi #n/a Trong Các Hàm Dò Tìm Vlookup Và Hlookup
  • Hướng Dẫn Sử Dụng Hàm Vlookup Lồng Hlookup Trong Excel
  • Hàm Tìm Kiếm Vlookup Và Hlookup Trong Excel Phổ Biến
  • Hàm Vlookup, Cách Sử Dụng Hàm Vlookup Trong Excel.
  • Hàm Offset Trả Về Tham Chiếu Trong Excel

    --- Bài mới hơn ---

  • Hàm Sumproduct Trong Excel: Nâng Cao & Nhiều Điều Kiện (Pro Excel)
  • Hướng Dẫn Cách Dùng Hàm Sumproduct Trong Excel Qua Ví Dụ
  • Hàm Sumproduct Google Sheet Trong Excel: Cách Sử Dụng
  • 4 Cách Sử Dụng Hàm Hyperlink Trong Excel Không Phải Ai Cũng Biết
  • Hàm Hlookup Trong Excel, Cú Pháp Và Cách Dùng
  • Mô tả

    Hàm OFFSET() trả về tham chiếu tới một vùng, cách một ô hoặc phạm vi ô với số hàng và số cột được các bạn chỉ định.

    Tham chiếu trả về có thể là một ô hoặc một phạm vi ô mà các bạn có thể chỉ rõ số hàng và số cột cần trả về.

    Cú pháp

    =OFFSET(reference, rows, cols, height, width)

    Trong đó:

    reference: là vùng tham chiếu mà các bạn muốn dựa vào đó để tạo vùng tham chiếu mới. Reference phải được tham chiếu tới một ô hoặc phạm vi ô liên tiếp nhau, nếu không hàm sẽ trả về giá trị lỗi.

    rows: là số hàng, lên trên hoặc xuống dưới reference, tính từ ô đầu tiên của reference. Số hàng có thể là số dương (phía dưới reference) hoặc số âm (phía trên reference).

    cols: là số cột, bên trái hoặc bên phải reference, tính từ ô đầu tiên của reference. Số cột có thể là số dương (bên phải reference) hoặc số âm (bên trái reference).

    height: là chiều cao được tính bằng số hàng mà các bạn muốn có tham chiếu trả về.

    width: là độ rộng được tính bằng số cột mà các bạn muốn có tham chiếu trả về.

    Ghi chú

    – Height và width phải là số dương.

    – Nếu bỏ qua height và width thì hàm OFFSET() sẽ mặc định là height và width của reference.

    – Nếu rows và cols làm cho vùng tham chiếu vượt ra khỏi phạm vi của bảng tính thì hàm OFFSET() sẽ báo lỗi #REF!.

    – Hàm OFFSET() không di chuyển hay làm thay đổi bất kỳ phần nào được chọn vì hàm chỉ trả về một tham chiếu. Các bạn có thể sử dụng hàm OFFSET() kết hợp với các hàm cần đến một đối số tham chiếu.

    Ví dụ

    1. Trong ô B8 trả về giá trị của ô E6 thông qua ô tham chiếu cơ sở B5.

    – Tại ô B8 các bạn nhập công thức: =OFFSET(B5,1,3,1,1)

    Ví dụ tính tổng lương và thưởng của nhân viên Nguyễn Thị Dung bắt đầu từ ô B6 (lương của nhân viên đầu tiên).

    Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2)) các bạn sẽ được kết quả như sau:

    --- Bài cũ hơn ---

  • Hướng Dẫn Về Hàm Logic Trong Excel: If, And, Or, If Lồng Và Not
  • Nhóm Hàm Logic Trong Excel (Bài 2)
  • Hàm Logic Cơ Bản Trong Excel, Tổng Hợp Các Hàm Lý Luận Trong Excel
  • Định Dạng Dữ Liệu Sử Dụng Custom Format
  • Cách Sử Dụng Hàm Count, Countif, Counta Và Countblank Trong Excel 2010
  • Các Kiểu Tham Chiếu Trong Excel

    --- Bài mới hơn ---

  • Hàm Row Và Rows Trong Excel: Định Nghĩa, Cú Pháp, Cách Sử Dụng
  • Hàm Rand Và Cách Dùng Hàm Rand Trong Excel Kèm Ví Dụ
  • Hàm Random Trong Excel Hay Hàm Rand
  • Hướng Dẫn Cách Sử Dụng Hàm Random Trong Excel
  • Hàm Random Trong Excel (Hàm Rand), Cách Sử Dụng Hàm Random Và Ví Dụ
  • Lượt Xem:2070

    Các kiểu tham chiếu trong Excel

    Kiểu A1 là dạng tham chiếu Excel phổ biến nhất và là kiểu mặc định.

    Kiểu tham chiếu này được tạo thành từ một chữ cái và một số, đại diện cho tham chiếu cột và số hàng tương ứng.

    Các ô A1-D4 được gắn nhãn trong bảng tính bên dưới:

    Kiểu tham chiếu R1C1 được tạo thành chữ R theo sau là một số hàng và chữ cái C theo sau là một số cột.

    Các ô R1C1-R4C4 được gắn nhãn trong bảng tính bên dưới:

    Nếu số hàng hoặc cột bị bỏ qua, điều này sẽ cho Excel biết sử dụng số hàng hoặc cột hiện tại.

    Ví dụ: nếu ô hiện tại là R3C3 (hoặc ô C3 kiểu ô A1), thì:

    R - -đề cập đến ô R5C5 (hoặc ô kiểu A1 E5)

    (thêm 2 hàng và 2 cột vào ô hiện tại)

    RC C1- -đề cập đến ô R5C1 (hoặc ô A5 kiểu A5)

    (thêm 2 hàng vào ô hiện tại; sử dụng cột tuyệt đối 1)

    Kiểu tham chiếu R1C1

    Lưu ý rằng bạn cần phải cho Excel biết kiểu tham chiếu nào bạn đang sử dụng. Điều này được đặt bằng cách chọn hoặc bỏ chọn tùy chọn tham chiếu R1C1 tùy chọn trong menu tùy chọn Excel.

    Trong các phiên bản Excel hiện tại (Excel 2010 trở lên):

    Trong menu Tệp , trong Tùy chọn → Công thức

    Trong menu Excel chính (truy cập bằng cách nhấp vào Biểu trưng Excel ở trên cùng bên trái của bảng tính), trong Excel Opt i ons → Công thức

    Trong T ools trình đơn thả xuống, dưới O ptions → chung

    Theo mặc định, Excel sử dụng kiểu tham chiếu A1 và các cột trong bảng tính của bạn được gắn nhãn bằng chữ cái. Tuy nhiên, nếu bạn chọn tùy chọn R1C1, bạn sẽ nhận thấy rằng các nhãn ở đầu các cột Bảng tính của bạn hiển thị số, thay vì chữ cái.

    Các bài viết mới

    Các tin cũ hơn

    --- Bài cũ hơn ---

  • Cách Khắc Phục Công Thức Excel Bị Thay Đổi (R1C1 Referencing)
  • Cách Đổi Tên Dòng Cột Trong Excel 2003 2007 2010 2013
  • Cách Sử Dụng Hàm Pmt Tính Số Tiền Thanh Toán Hàng Kỳ Cho Khoản Vay Trong Excel
  • Hàm Today Và Hàm Now Trong Excel
  • Npv Là Gì? Giá Trị Hiện Tại Thuần
  • Cách Sử Dụng Hàm Index Để Lấy Mảng Tham Chiếu Trong Excel

    --- Bài mới hơn ---

  • Cách Sửa Lỗi Ngày Tháng Của Dữ Liệu Được Trích Xuất Từ Phần Mềm Ra Excel Chi Tiết Nhất
  • Hướng Dẫn Cách Làm Tròn Số Trong Excel Đơn Giản, Hiệu Quả
  • Làm Cách Nào Để Làm Tròn / Làm Tròn Lên / Làm Tròn Thời Gian Đến Một Phần Tư / Nửa Giờ Gần Nhất Trong Excel?
  • Làm Thế Nào Để Làm Tròn Thời Gian Đến Giờ / Phút / Giây Gần Nhất Trong Excel?
  • Hướng Dẫn Định Dạng Giờ Phút Giây Trong Excel
  • Hàm INDEX sẽ trả về kết quả được tham chiếu đến một phần tử của một hoặc nhiều mảng. Và khi nhìn vào hàm INDEX thì bạn sẽ biết vị trí của phần tử trong mảng được sử dụng.

    Cách sử dụng hàm INDEX trong Excel

    Bây giờ bạn có thể mở Excel lên và sử dụng lệnh =INDEX( thì bạn sẽ thấy 2 cú pháp được hiển thị. Như vậy, trong Excel hàm INDEX có 2 cách sử dụng sau đây:

    Cú pháp

    =INDEX(array; row_num; : là vị trí cột trong array

    Mô tả

    Khi sử dụng dạng mảng thì hàm INDEX sẽ trả về một phần tử trong mảng, kết quả trả về là của giao điểm row_num; area_num)

    👉Trong đó:

    • reference: tham chiếu đến một hay nhiều Range giá trị.
    • row_num: là vị trí hàng trong reference.
    • thuộc Range nào.

    Mô tả:

    Dạng tham chiếu của hàm INDEX cho phép chọn nhiều Range nếu các Range đó không liền kề nhau. Kết quả trả về là giao điểm row_num = 6

  • area_num = 2, tức là Range A7:F8. Trong reference bạn có thể thêm nhiều Range giá trị và Range đầu tiên là 1.
  • 👉Kết quả là giao điểm hàng thứ 2 và cột thứ 6 trong Range A7:F8

    2) Ngoài ra bạn có thể kết hợp hàm INDEX với các hàm khác để tính toán trong các Range riêng. Ví dụ: sử dụng hàm =SUM(INDEX((A2:F4;A7:F8);0;6;1)) sẽ tính được tổng giá Range thứ nhất của reference hoặc =SUM(INDEX((A2:F4;A7:F8);0;6;2)) để tính tổng giá Range thứ 2 của reference.

    Trong cả 2 dạng của hàm INDEX thì nếu row_num =0 hoặc =0 thì hàm sẽ trả về kết quả là #VALUE!. Vì vậy sau khi nhập hàm xong bạn sử dụng tổ hợp phím[CTRL + SHIFT + ENTER] thì hàm sẽ trả về giá trị đầu tiên trong mảng.

    OK! Qua bài viết về cách sử dụng hàm INDEX thì mình hy vọng sẽ giúp các bạn giải quyết được vấn đề đang gặp phải.

    --- Bài cũ hơn ---

  • How To Find Column Index Number From Table In Excel
  • Hướng Dẫn Giải Bài Tập Dùng Hàm Index Kết Hợp Hàm Match Có File Mẫu
  • Hướng Dẫn Cách Dùng Hàm Countif Trong Công Việc Kế Toán Trên Excel
  • Công Thức Excel: Đếm Các Ô Bằng X Và Y
  • Làm Cách Nào Để Đếm Tần Suất Của Một Văn Bản / Số / Ký Tự Trong Cột Excel?
  • Cách Tạo Vùng Tham Chiếu “Động” Trong Excel 2010

    --- Bài mới hơn ---

  • Tìm Và Lọc Dữ Liệu Trùng Nhau Trong Excel 2003, 2007, 2010
  • Hướng Dẫn 3 Cách Lọc Dữ Liệu Trùng Nhau Trong Excel Đơn Giản, Hiệu Quả
  • Cách Sử Dụng Excel 2022 – Tự Học Cách Share File Excel Qua Mạng Cho Nhiều Người Cùng Làm Việc
  • Cách Sử Dụng Excel 2022 – Tìm Kiếm Dữ Liệu Với Go To Special
  • Hướng Dẫn Thực Hành Solver
  • Xin chào các bạn,

    Khi muốn tham chiếu đến một vùng dữ liệu trong Excel bạn thường đặt tên cho nó.

    Ví dụ:

    Tôi có một danh sách tên alias của các thành viên trong team như sau:

    Danh sách này hiện nay cố định trong vùng $A$5: $A$19

    Để đặt tên cho vùng này tôi chọn vùng rồi nhấn chọn vào vùng đặt tên gõ tên vùng là “Danhsach”.

    Công việc này đã giúp tôi định nghĩa nhanh vùng dữ liệu $A$5: $A$19 có tên gọi là Danhsach.

    Sau này khi cần tham chiếu đến vùng dữ liệu $A$5: $A$19 thì tôi chỉ việc gõ chữ “Danhsach” là xong.

     

     

     

     

     

     

     

     

    Để kiểm tra lại tôi vào thanh tab Formulas chọn chức năng Name Manager

    Lưu ý là tên vùng Danhsach được gán với vùng =Sheet1!$A$5:$A$19 cố định

    Điều này có nghĩa là khi bạn thêm một tên mới trong vùng dữ liệu này thì Danhsach sẽ không được cập nhật.

    Cách giải quyết:

    Thay vì đặt vùng tham chiếu là cố định như trên bạn thay vào đó là hàm OFFSET, hàm này có cấu trúc như sau:

    =OFFSET(Reference, rows, cols, )

    • Reference: là vị trí tham chiếu. Trong trường hợp này ta chọn cell A5 (vì danh sách sẽ bắt đầu từ đây)
    • Rows: vị trí dòng bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo dòng thì bỏ trống giá trị này, nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển xuống 2 dòng nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển lên 2 dòng nữa)
    • Cols: vị trí của cột bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo cột thì bỏ trống giá trị này. Nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển sang phải 2 cột nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển sang trái 2 cột nữa)
    • Height: chiều cao của vùng dữ liệu sẽ lấy kể từ vị trí tham chiếu.
    • Width: chiều rộng của vùng dữ liệu kể từ vị trí tham chiếu

    Như vậy trong hàm này, 2 đối số quan trọng nhất là Height và Width. Tôi sẽ làm như sau để các bạn tham khảo

    Height: tôi sẽ đưa công thức này vào COUNTA(A:A). hàm COUNTA cho phép đếm tất cả các cell trong cột A có chứa dữ liệu và sẽ trả về giá trị số nguyên.

    Width: giá trị số nguyên biểu diễn số lượng cột cần lấy. Nếu theo trường hợp của bài này thì bạn có thể bỏ qua hoặc gõ số 1

    Vậy công thức cuối cùng cho hàm OFFSET sẽ như sau:

    =OFFSET(Sheet1!$A$5,,,COUNTA(Sheet1!$A:$A))

    Trong trường hợp bạn muốn mở rộng vùng tham chiếu cho cột thì cũng chỉ cần bổ sung vào hàm OFFSET thông số cuối cùng cho width như sau:

    =OFFSET($A$4,,,COUNTA(Sheet1!$A:$A),COUNTA($4:$4))

    Hy vọng thủ thuật nhỏ này sẽ giúp ích nhiều cho các bạn trong công việc

    Nguyễn Thế Đông

    Share this:

    • Chia sẻ

    Like this:

    Số lượt thích

    Đang tải…

    --- Bài cũ hơn ---

  • Cách Chèn Thêm Hàng, Thêm Cột Trong Excel 2003 2007 2010 2013
  • Hướng Dẫn Cách Ẩn Sheet Excel
  • Sqrt Là Gì. Hàm Căn Bậc 2 Trong Excel: Công Thức Và Cách Sử Dụng
  • Các Hàm Excel Mới Trong Phiên Bản Excel 2022 Và Ví Dụ – Hkt Consultant
  • Các Hàm Excel Thông Dụng
  • Cú Pháp Và Mô Tả Các Hàm Tìm Kiếm Và Tham Chiếu Trong Excel

    --- Bài mới hơn ---

  • Các Hàm Tìm Kiếm Có Điều Kiện Trong Excel Hiệu Quả Trong Công Việc
  • Hướng Dẫn Cách Dùng Hàm Concatenate Để Nối Chuỗi, Nối Ô Trong Excel
  • Sử Dụng Hàm Nối Chuỗi Trong Excel
  • Dùng Hàm Vlookup Để Nối Gộp Hai Bảng Excel Với Nhau
  • Các Hàm Thời Gian Ngày Tháng Trong Excel
  • ADDRESS (row_num, column_num, abs_num, a1, sheet_text): Hàm lấy địa chỉ của một ô trong một trang tính, với số hàng và số cột đã cho.

    AREAS (reference): Hàm trả về số vùng tham chiếu trong một tham chiếu, mỗi vùng tham chiếu có thể là một ô hay một dãy các ô liên tục trong bảng tính.

    CHOOSE (num, value1, value2): Hàm lấy một giá trị trong danh sách đối số đầu vào.

    COLUMN (reference): Hàm trả về số thứ tự cột của tham chiếu ô nhất định.

    COLUMNS (reference): Hàm trả về tổng số cột của một mảng hay vùng tham chiếu.

    GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,…): Hàm trả về dữ liệu được lưu giữ trong PrivotTable, hàm truy xuất dữ liệu tóm tắt từ PrivotTable, với điều kiện là dữ liệu tóm tắt đó có thể thấy trong báo cáo.

    HLOOKUP (lookup_value, table_array, row_index_num, range_lookup): Hàm tìm kiếm một giá trị trong hàng trên cùng của bảng hoặc mảng giá trị, sau đó trả về giá trị trong cùng cột từ hàng mà bạn chỉ định trong bảng hoặc mảng.

    HYPERLINK (link_location, friendly_name): Hàm tạo một kết nối, một liên kết để mở một tài liệu.

    INDEX (reference, row_num, column_num, area_num): Tìm một giá trị trong một bảng hoặc một mảng nếu biết vị trí của nó, dựa vào số thứ tự hàng và số thứ tự cột.

    INDIRECT (ref_text, a1): Hàm trả về một tham chiếu từ chuỗi ký tự, các tham chiếu có thể được đánh giá ngay tức thì để hiển thị nội dung của chúng. Sử dụng hàm khi các bạn muốn thay đổi tham chiếu tới một ô trong một công thức mà không thay đổi chính công thức đó.

    LOOKUP (lookup_value, lookup_vector, resulf_vector): Tìm kiếm dạng Vecto. Hàm tìm kiếm trên một dòng hoặc một cột, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (hoặc cột) được chỉ định. Sử dụng dạng Vecto khi có một danh sách gồm nhiều giá trị cần tìm hoặc khi các giá trị có thể thay đổi theo thời gian.

    LOOKUP (lookup_value, array): Tìm kiếm dạng mảng. Hàm tìm kiếm trên dòng (cột) đầu tiên của một mảng giá trị, nếu tìm thấy sẽ trả về giá trị của ô cùng vị trí trên dòng (cột) cuối cùng trong mảng đó. Sử dụng dạng tìm kiếm mảng khi có một danh sách gồm ít các giá trị và các giá trị đó không đổi theo thời gian.

    MATCH (lookup_value, lookup_array, match_type): Hàm tìm kiếm một mục đã xác định trong một phạm vi ô rồi trả về vị trí tương đối của mục trong phạm vi đó.

    OFFSET (reference, rows, cols, height, width): Hàm trả về tham chiếu đến một vùng nào đó được tính bằng một ô hay một dãy ô với số dòng hoặc số cột được chỉ định.

    ROW (reference): Hàm trả về số thứ tự hàng của một tham chiếu.

    ROWS (reference): Hàm trả về tổng số dòng của vùng được tham chiếu.

    TRANSPOSE (array): Hàm trả về một phạm vi các ô dọc dưới dạng một phạm vi ngang hoặc ngược lại. Hàm phải được nhập dưới dạng công thức mảng trong một phạm vi có cùng số hàng và số cột tương ứng với số cột và số hàng trong phạm vi nguồn.

    VLOOKUP (lookup_value, table_array, col_index_num, range_lookup): Hàm dùng để dò tìm một giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên một dòng với giá trị tìm thấy trên cột mà các bạn chỉ định.

    --- Bài cũ hơn ---

  • Hướng Dẫn Hàm Cộng Ngày Tháng Trong Excel 2022
  • Làm Việc Với Các Hàm Vba Trả Về Một Mảng Trong Excel 2022
  • Cách Sử Dụng Hàm Left Trong Excel Để Cắt Chuỗi Ký Tự Bên Trái
  • Cách Sử Dụng Hàm Left Trong Excel Kế Toán
  • Các Hàm Thống Kê Thường Dùng Trong Excel
  • Cách Tạo Vùng Tham Chiếu “động” Trong Excel 2010

    --- Bài mới hơn ---

  • Cách Lọc Các Giá Trị Trùng Nhau Trên Excel 2010 Nhanh Chóng
  • Hướng Dẫn Cách Lọc Dữ Liệu Trùng Nhau Trong Excel
  • Hướng Dẫn Sử Dụng Solver Add
  • Merge And Center In Microsoft Excel
  • Cách Gộp Ô Trong Excel Cực Đơn Giản & Không Mất Dữ Liệu
  • Xin chào các bạn,

    Khi muốn tham chiếu đến một vùng dữ liệu trong Excel bạn thường đặt tên cho nó.

    Ví dụ:

    Tôi có một danh sách tên alias của các thành viên trong team như sau:

    Danh sách này hiện nay cố định trong vùng $A$5: $A$19

    Để đặt tên cho vùng này tôi chọn vùng rồi nhấn chọn vào vùng đặt tên gõ tên vùng là “Danhsach”.

    Công việc này đã giúp tôi định nghĩa nhanh vùng dữ liệu $A$5: $A$19 có tên gọi là Danhsach.

    Sau này khi cần tham chiếu đến vùng dữ liệu $A$5: $A$19 thì tôi chỉ việc gõ chữ “Danhsach” là xong.

    Lưu ý là tên vùng Danhsach được gán với vùng =Sheet1!$A$5:$A$19 cố định

    Điều này có nghĩa là khi bạn thêm một tên mới trong vùng dữ liệu này thì Danhsach sẽ không được cập nhật.

    Thay vì đặt vùng tham chiếu là cố định như trên bạn thay vào đó là hàm OFFSET, hàm này có cấu trúc như sau:

    =OFFSET(Reference, rows, cols, )

    • Reference: là vị trí tham chiếu. Trong trường hợp này ta chọn cell A5 (vì danh sách sẽ bắt đầu từ đây)
    • Rows: vị trí dòng bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo dòng thì bỏ trống giá trị này, nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển xuống 2 dòng nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển lên 2 dòng nữa)
    • Cols: vị trí của cột bắt đầu kể từ vị trí tham chiếu (nếu chúng ta không cần dịch chuyển vị trí tham chiếu theo cột thì bỏ trống giá trị này. Nếu giá trị là số dương, vi dụ như +2 thì có nghĩa là từ vị trí tham chiếu dich chuyển sang phải 2 cột nữa, nếu là giá trị là số âm ví dụ như -2 thì có nghĩa là từ vị trí tham chiếu dịch chuyển sang trái 2 cột nữa)
    • Height: chiều cao của vùng dữ liệu sẽ lấy kể từ vị trí tham chiếu.
    • Width: chiều rộng của vùng dữ liệu kể từ vị trí tham chiếu

    Như vậy trong hàm này, 2 đối số quan trọng nhất là Height và Width. Tôi sẽ làm như sau để các bạn tham khảo

    Height: tôi sẽ đưa công thức này vào COUNTA(A:A). hàm COUNTA cho phép đếm tất cả các cell trong cột A có chứa dữ liệu và sẽ trả về giá trị số nguyên.

    Width: giá trị số nguyên biểu diễn số lượng cột cần lấy. Nếu theo trường hợp của bài này thì bạn có thể bỏ qua hoặc gõ số 1

    Vậy công thức cuối cùng cho hàm OFFSET sẽ như sau:

    Trong trường hợp bạn muốn mở rộng vùng tham chiếu cho cột thì cũng chỉ cần bổ sung vào hàm OFFSET thông số cuối cùng cho width như sau:

    =OFFSET($A$4,,,COUNTA(Sheet1!$A:$A),COUNTA($4:$4))

    Hy vọng thủ thuật nhỏ này sẽ giúp ích nhiều cho các bạn trong công việc

    About dongnguyenthe

    Luôn tìm cách cân bằng giữa cuộc sống và công việc. Ngoài thời gian làm việc bận rộn, tôi thích dành nhiều thời gian cho bọn trẻ con, giúp chúng học và giải trí, tập luyện thể thao. Về cá nhân, tôi rất coi trọng việc rèn luyện và phát triển các kỹ năng cá nhân, thích đọc sách, viết lách, du lịch mạo hiểm và chụp ảnh.

    Bài này đã được đăng trong 09-Office 14. Đánh dấu đường dẫn tĩnh.

    --- Bài cũ hơn ---

  • Mặc Định Font Chữ Trong Excel 2007 2010 2003 (Chỉnh Cỡ Chữ
  • Hướng Dẫn Cách Cố Định Hàng/cột Trong Google Sheets
  • Cách Sử Dụng Hàm Countif (Cơ Bản
  • Lỗi Excel Bị Ẩn Sheet Không Nhìn Thấy Và Cách Hiện Sheet Trong Excel
  • Cách Ẩn Và Hiện Các Hàng Trong Excel
  • Hướng Dẫn Cách Dùng Hàm Row Trả Về Vị Trí Hàng Của Tham Chiếu Trong Excel

    --- Bài mới hơn ---

  • Cú Pháp, Ví Dụ Và Cách Vận Dụng
  • Hàm Tính Độ Lệch Chuẩn Trong Excel
  • Cách Sử Dụng Hàm Countif Trong Excel (Có Ví Dụ)
  • Tổng Các Ô Theo Màu Sắc Với Định Dạng Có Điều Kiện Trong Excel?
  • Các Phím Tắt Trong Excel Kế Toán Thường Dùng
  • Trong bài trước chúng ta đã tìm hiểu về hàm COLUMN, trong bài này chúng ta sẽ tìm hiểu về hàm ROW, hàm được sử dụng để xác định vị trí hàng của tham chiếu.

    Cách dùng và cú pháp hàm ROW

    Hàm ROW rả về vị trí hàng của tham chiếu

    Công thức của hàm ROW như sau:

    =ROW(tham chiếu)

    Nếu tham chiếu rỗng, ROW sẽ trả về vị trí hàng tại ô nhập công thức vào

    Tham chiếu là một ô, ROW sẽ trả về vị trí hàng của ô đó

    Trong trường hợp tham chiếu là một vùng, ROW sẽ trả về vị trí hàng của ô đầu tiên trong vùng đó

    Lưu ý: Trong trường hợp hàm được nhập vào dưới dạng công thức mảng dọc cho một vùng, giá trị trả về sẽ tăng tiến dần tới hết vị trí dòng của vùng đó.

    Ví dụ: Xác định vị trí hàng tại ô nhập công thức

    Xác định vị trí hàng của ô E4

    Xác định vị trí hàng của vùng B5:E10

    Một số ứng dụng hay của hàm ROW

    Cũng như hàm COLUMN, ta có thể sử dụng hàm ROW thiết lập dãy giá trị tăng tiến theo hàng dọc.

    Công thức trong trường hợp này, tại ô A3 là:

    =$A$2*(ROW()-1)

    Thật đơn giản phải không nào?

    Hướng dẫn hàm CELL tra cứu thông tin ô trong Excel

    Thiết lập Column Titles và Row Titles

    Đánh giá bài viết này

    --- Bài cũ hơn ---

  • Cách Sử Dụng Hàm Or Và Ví Dụ Cụ Thể
  • Irr Là Gì – Hướng Dẫn Cách Dùng Và Ý Nghĩa Của Hàm Irr Trong Excel
  • Cách Dùng Hàm Sumif – Hàm Tính Tổng Có Điều Kiện
  • Hướng Dẫn Cách Dùng Hàm Days Trong Excel Qua Ví Dụ
  • Cách Tra Cứu Kí Tự Với Hàm Char Và Code Trong Excel
  • Hàm Vlookup Trong Excel, Cách Sử Dụng Hàm Vlookup Trong Excel.

    --- Bài mới hơn ---

  • Các Hàm Cơ Bản Trong Excel Và Cách Sử Dụng
  • Tổng Hợp Các Hàm Trong Excel Thường Dùng Cho Dân Văn Phòng
  • Hàm Và Công Cụ Excel Dùng Trong Quản Lý Cấp Phát Đồ Dùng Văn Phòng
  • Sử Dụng Custom Views Để Xem Bố Cục Bảng Tính Cụ Thể Trong Excel
  • Hướng Dẫn Cách Hủy Lệnh In Trong Word, Excel 2022, 2013, 2010, 2007, H
  • Hàm Vlookup trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột), nó giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Đây là một trong những hàm phổ biến và hữu ích nhất trong Excel, nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.

    1. Chức năng của hàm Vlookup trong Excel.

    Chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.

    2. Cú pháp hàm vlookup trong excel.

    • Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.
    • Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.
    • Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.
    • Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.
      • Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.
      • Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.

    3. Ví dụ về hàm VLOOKUP trong Excel.

    3.1. Các trường hợp sử dụng hàm Vlookup trong Excel.

    Hàm Vlookup trong Excel có 2 cách sử dụng. Khi khi lập công thức ta có thể lựa chọn 1 trong 2 bằng cách gán giá trị cho Range_lookup là TRUE (1) hoặc FALSE (0).

    • Hàm Vlookup để tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi. VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.
    • Hàm Vlookup để tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. VD: Xếp loại học lực của học sinh theo điểm tổng kết.

    3.2. Ví dụ về hàm Vlookup để tìm kiếm chính xác.

    VD: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?

    Để điển thông tin quê quán cho nhân viên, tại ô E4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(B4,$B$17:$D$26,2,0).

    Trong đó:

    • B4: Là giá trị cần đối chiếu.
    • $B$17:$D$26: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.
    • 2: Số thứ tự cột dữ liệu trên bảng dò tìm.
    • 0: Kiểu tìm kiếm chính xác.

    Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.

    Ảnh mình hoạ sử dụng hàm Vlookup để tìm kiếm chính xác trong Excel.

    Chúng ta cũng thực hiện tương tự như vậy vậy để điền trình độ nhân viên, với công thức ở ô F4 là: =VLOOKUP(B4,$B$17:$D$26,3,0)

    Trong đó:

    Quan sát hình bên dưới để hiểu cách tìm kiếm và lấy giá trị của hàm Vlookup khi tìm kiếm chính xác.

    Tìm kiếm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy.

    VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:

    Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.

    Tại ô E4, ta nhập vào công thức là: =VLOOKUP(D4,$B$17:$C$20,2,1)

    Trong đó:

    Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.

    Dễ thấy, khi không do tìm được kết quả nào trùng khớp với giá trị dò tìm thì hàm Vlookup sẽ lấy giá trị lớn nhất kế tiếp nhỏ hơn nó.

    4. Những điều cần biết khi sử dùng hàm Vlookup trong Excel.

    4.1. Một số lưu ý khi sử dụng hàm Vlookup trong Excel.

    • Khi tạo công thức hàm bạn cần cố định Table_array (vùng cần tìm kiếm) bằng cách thêm kí tự $ vào địa chỉ của vùng ( VD: $A$18:$B$21) để khi ta copy công thức xuống các hàng khác thì vùng cần tìm kiếm không bị thay đổi theo.
    • Hàm Vlookup trong Excel chỉ có thể tìm ở cột ngoài cùng bên trái của vùng ta chọn. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index kết hợp Match.
    • Hàm Vlookup trong Excel không phân biệt được chữ hoa và thường.
    • Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được xếp theo thứ tự tăng dần (A-Z) từ trái sang phải.
    • Khi dùng hàm Vlookup để tìm kiếm tuyệt đối nhưng vùng cần tìm kiếm lại không chứa giá trị đó thì kết quả trả về sẽ là #N/A!.

    4.2. Hướng dẫn sử dụng dấu chấm dấu phẩy.

    Bạn có đang gặp vấn đề trong việc dùng dấu chấm “.” dấu dấu chấm phẩy “;” trong việc ngăn cách cách thành phần trong hàm, hay dấu chấm “.” dấu phẩy “,” trong việc phân cách chữ số thập phân?

    Trong Excel người ta thường dấu phẩy “,” trong việc ngăn cách thành phần trong hàm và phân cách các hàng nghìn, triệu, tỷ trong số. Dấu chấm “.” để phân cách chữ số thập nhân. Nhưng trong ở một số bản phần mềm Excel khác lại dùng dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm. Điều này khiến người dùng dễ nhầm lẫn khi dùng dấu chấm và dấu phẩy trong Excel.

    Trong Excel có 2 kiểu quy ước:

    • Quy ước kiểu phương Tây: Dấu chấm “.” là dấu để phân cách chữ số thập phân, còn phân cách các hàng nghìn, triệu, tỷ và các thành phần khi viết hàm là dấy phẩy “,”.
    • Quy ước của Việt Nam: Dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm.

    Vậy làm thế nào để biết Excel của mình dùng tuần theo quy ước nào, bạn có thể thử cách sau:

    • Nhập thử một số thập phân tại ô nào đó, nếu Excel không dịch số đó về bên phải thì dấu ngăn cách thập phân là dấu “.” (chấm).
    • Nếu dấu ngăn cách thập phân là dấu “,” thì dấu ngăn cách phần ngàn là dấu “.” và dấu ngăn cách trong công thức là dấu “;” (chấm phẩy).

    Khi Excel của bạn tuần theo quy ước của Việt Nam bạn cần đề ý hơn khi nhập dữ liệu, lập công thức, tra cứu công thức để phù hợp.

    Nhưng để tiện sử dụng và tra cứu bạn nên đổi sang quy ước của phương Tây bằng cách:

    4.3. Hướng dẫn sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup trong Excel.

    Trong excel có 3 loại địa chỉ:

    • Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).
    • Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)
      • Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.
      • Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.
    • Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.
      • Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.
      • Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.

    Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.

    4.4. Đặt tên cho bảng trong Excel.

    Địa chỉ các ô, các bảng, các vùng trong Excel được lưu bằng những kí tự và số rất khó nhớ, điều này gây khó khăn và tồn thời gian cho chúng ta khi phải gọi ra các bảng, các vùng dữ liệu từ nhiều nơi. Để đơn giản hoá việc này chúng ra có thể đặt tên cho các vùng dữ liệu. Khi cần sử dụng đến chúng ta chỉ cần gọi tới vùng đó thông qua tên đã đặt.

    Đặt tên cho vùng dữ liệu dữ liệu bằng cách:

    B1: Chọn vùng muốn đặt tên, vùng này có thể là 1 ô hoặc 1 vùng tuỳ theo mục đích sử dụng của bạn.

    B2: Tại ô địa chỉ của vùng dữ liệu (góc bên trên phía bên tay trái, ngay trên cột A) ta nhập tên cho vùng dữ liệu và Enter.

    Sau khi đặt tên cho vùng dữ liệu ta chỉ cần gọi vùng đó ra thông qua tên mà không lo địa chỉ bị sai khi copy công thức từ dòng này sang dòng khác.

    Khi sử dụng hàm Vlookup ta có thể đặt tên cho cùng cần tìm kiếm mà không cần quan tâm tới địa chỉ tuyệt đối của nó.

    VD: Ở ví dụ bên trên sau khi đã đặt tên cho bảng quy định xếp loại.

    Từ công thức: =VLOOKUP(D4,$B$17:$C$20,2,1).

    Cả 2 công thức này đều tương đương nhau nhưng với công thức mà vùng tìm kiếm được đặt tên giúp ta dễ dàng copy cho những hàng khác và đơn giản, dễ hình dung.

    Video hướng dẫn:

    Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

    Hàm VLOOKUP nâng cao trong ExcelHàm VLOOKUP 2 điều kiện trong ExcelHàm VLOOKUP kết hợp hàm IF trong ExcelHàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel

    --- Bài cũ hơn ---

  • Bài Tập Mẫu Hàm Vlookup Trong Excel (Có Lời Giải)
  • Các Sử Dụng Hàm Vlookup Trong Excel Khi Làm Kế Toán
  • Hàm Vlookup Trong Excel Đơn Giản Dễ Hiểu Nhất
  • Hàm Vlookup Trong Excel, Hướng Dẫn Sử Dụng Chi Tiết Và Có Ví Dụ Cụ Thể
  • Hướng Dẫn Sử Dụng Hàm Value Trong Excel
  • Web hay
  • Links hay
  • Push
  • Chủ đề top 10
  • Chủ đề top 20
  • Chủ đề top 30
  • Chủ đề top 40
  • Chủ đề top 50
  • Chủ đề top 60
  • Chủ đề top 70
  • Chủ đề top 80
  • Chủ đề top 90
  • Chủ đề top 100
  • Bài viết top 10
  • Bài viết top 20
  • Bài viết top 30
  • Bài viết top 40
  • Bài viết top 50
  • Bài viết top 60
  • Bài viết top 70
  • Bài viết top 80
  • Bài viết top 90
  • Bài viết top 100