Lưu ý: Chúng tôi mong cung cấp cho bạn nội dung trợ giúp mới nhất ở chính ngữ điệu của bạn, nhanh nhất có thể có thể. Trang này được dịch tự động hóa nên rất có thể chứa các lỗi về ngữ pháp hoặc câu chữ không bao gồm xác. Mục đích của shop chúng tôi là khiến nội dung này trở đề xuất hữu ích cùng với bạn. Vui tươi cho công ty chúng tôi biết ở cuối trang này rằng thông tin có có lợi với bạn không? sau đây là nội dung bài viết bằng giờ đồng hồ Anh nhằm bạn tìm hiểu thêm dễ hơn.
Bạn đang xem: Hàm liệt kê các giá trị trong excel
Tóm tắt
Từng bước bài viết này tế bào tả phương pháp để tìm tài liệu trong bảng (hoặc phạm vi ô) bằng phương pháp dùng hàm dựng sẵn khác biệt trong Microsoft Excel. Chúng ta cũng có thể sử dụng công thức không giống nhau để có công dụng tương tự.
Tạo trang tính mẫu
Bài viết này cần sử dụng một trang tính chủng loại để minh họa Excel hàm dựng sẵn. Suy nghĩ ví dụ về tham chiếu thương hiệu từ cột A cùng trả về tuổi của người đó từ cột C. Để tạo ra trang tính này, hãy nhập dữ liệu dưới đây vào một trang tính Excel trống.Bạn đang nhập cực hiếm mà bạn muốn tìm vào ô E2. Chúng ta có thể nhập phương pháp trong ngẫu nhiên ô trống trong cùng một trang tính.
| A | B | C | D | E | ||
| 1 | Tên | Phòng | Tuổi thọ | Tìm giá trị | ||
| 2 | Henry | 501 | 28 | Mary | ||
| 3 | Stan | 201 | 19 | |||
| 4 | Mary | 101 | 22 | |||
| 5 | Larry | 301 | 29 |
Thuật ngữ định nghĩa
Bài viết này sử dụng các điều kiện sau đây để mô tả các hàm dựng sẵn Excel:
| Thuật ngữ | Định nghĩa | Ví dụ |
| Bảng mảng | Tra cứu cục bộ bảng | A2:C5 |
| Lookup_Value | Giá trị được kiếm tìm thấy trong cột đầu tiên của Table_Array. | E2 |
| Mảng kiếm tìm kiếm- hoặc -Lookup_Vector | Phạm vi ô tất cả chứa quý hiếm tra cứu rất có thể xảy ra. | A2: A5 |
| Col_Index_Num | Số cột vào Table_Array giá trị khớp sẽ được trả về cho. | 3 (thứ cha cột vào Table_Array) |
| Result_Array- hoặc -Result_Vector | Phạm vi chỉ chứa một hàng hay một cột. Nó phải gồm cùng kích cỡ như mảng tra cứu kiếm hoặc Lookup_Vector. | C2:C5 |
| Range_Lookup | Một cực hiếm lô-gic (TRUE hoặc FALSE). Nếu đối số này là TRUE hoặc được bỏ qua, thì hàm sẽ trả về kết quả khớp tương đối. Trường hợp FALSE, nó đang tìm hiệu quả khớp bao gồm xác. | FALSE |
| Top_cell | Đây là tham chiếu mà bạn muốn dựa vào quý giá bù trừ. Top_Cell đề xuất tham chiếu đến một ô hoặc phạm vi ô ngay tức thì kề. Ví như không, hàm OFFSET trả về #VALUE! . | |
| Offset_Col | Đây là số cột, bên trái hoặc bên phải, mà bạn muốn góc trên phía bên trái của kết quả để tham chiếu ô. Ví dụ, "5" làm cho đối số Offset_Col xác minh ô trên cùng phía trái trong tham chiếu là năm cột sinh hoạt bên phải của tham chiếu. Offset_Col có thể là tích cực và lành mạnh (có nghĩa là sinh hoạt bên buộc phải của tham chiếu bắt đầu) hoặc âm (nghĩa là ở bên trái của tham chiếu bắt đầu). |
Hàm
LOOKUP()
Hàm LOOKUP search một giá trị trong một hàng hay cột và khớp với nó cùng với một quý hiếm trong và một vị trí trong một cột hoặc mặt hàng khác nhau.Sau đây là một lấy ví dụ về cú pháp bí quyết tra cứu:
=LOOKUP(Lookup_Value,Lookup_Vector,Result_Vector)
Công thức sau search tuổi lâu của Mary trong trang tính mẫu:
=LOOKUP(E2,A2:A5,C2:C5)
Công thức áp dụng giá trị "Mary" vào ô E2 và tìm thấy "Mary" vào véc-tơ tra cứu (cột A). Bí quyết rồi khớp với giá trị trong đứng thảng hàng trong hiệu quả vector (cột C). Vị "Mary" trong sản phẩm 4, hàm LOOKUP trả về quý hiếm từ sản phẩm 4 trong cột C (22).Ghi chú: Hàm LOOKUP yêu ước là bảng được sắp xếp.
Để biết thêm thông tin về hàm tra cứu , hãy bấm số nội dung bài viết sau phía trên để xem nội dung bài viết trong cơ sở kiến thức của Microsoft:
Làm cố nào để sử dụng hàm tra cứu vớt trong Excel
VLOOKUP()
Hàm VLOOKUP hoặc dọc tra cứu vớt được thực hiện khi tài liệu được liệt kê vào cột. Hàm này tìm kiếm kiếm một quý hiếm trong cột ngoại trừ cùng phía trái và khớp cùng với nó với tài liệu trong một cột đã chỉ định trong thuộc hàng. Chúng ta có thể sử dụng VLOOKUP để tìm tài liệu trong một bảng được thu xếp hoặc ko được sắp đến xếp. Ví dụ tiếp sau đây dùng bảng với dữ liệu không được sắp tới xếp.Sau đó là một lấy ví dụ về cú pháp phương pháp VLOOKUP :
=VLOOKUP(Lookup_Value,Table_Array,Col_Index_Num,Range_Lookup)
Công thức sau search tuổi lâu của Mary trong trang tính mẫu:
=VLOOKUP(E2,A2:C5,3,FALSE)
Công thức sử dụng giá trị "Mary" trong ô E2 cùng tìm thấy "Mary" vào cột ko kể cùng bên trái (cột A). Phương pháp rồi khớp với mức giá trị trong cùng hàng trong Column_Index. Ví dụ này sử dụng "3" làm Column_Index (cột C). Vì chưng "Mary" trong sản phẩm 4, VLOOKUP trả về quý hiếm từ hàng 4 vào cột C (22).
Để biết thêm tin tức về hàm VLOOKUP , bấm số bài viết sau đây để xem nội dung bài viết trong cơ sở kỹ năng và kiến thức của Microsoft:
Làm thay nào để áp dụng hàm VLOOKUP hoặc HLOOKUP nhằm tìm cực hiếm khớp đúng đắn
INDEX() với MATCH()
Bạn hoàn toàn có thể dùng các hàm INDEX và MATCH cùng mọi người trong nhà để có tác dụng tương tự như áp dụng tra cứu hoặc VLOOKUP.
Sau đấy là một lấy ví dụ về cú pháp phối kết hợp hàm INDEX cùng MATCH để tạo ra kết quả tương từ như tra cứu với VLOOKUP trong lấy ví dụ như trước đó:
=INDEX(Table_Array,Match(Lookup_Value,Lookup_Array,0),Col_Index_Num)
Công thức sau search tuổi lâu của Mary vào trang tính mẫu:
=INDEX(A2:C5,MATCH(E2,A2:A5,0),3)
Công thức áp dụng giá trị "Mary" vào ô E2 với tìm thấy "Mary" vào cột A. Nó rồi khớp với giá trị trong thẳng hàng trong cột C. Vày "Mary" trong hàng 4, bí quyết trả về quý hiếm từ sản phẩm 4 vào cột C (22).
LƯU Ý: Nếu không có các ô vào mảng tìm kiếm kiếm khớp với Lookup_Value ("Mary"), công thức này sẽ trả về #N/A.Để biết thêm tin tức về hàm INDEX , bấm số bài viết sau trên đây để xem nội dung bài viết trong cơ sở kỹ năng của Microsoft:
biện pháp dùng hàm INDEX để tìm dữ liệu trong bảng
OFFSET() và MATCH()
Bạn có thể dùng các hàm OFFSET và MATCH cùng mọi người trong nhà để chế tạo ra ra hiệu quả tương trường đoản cú như các hàm trong ví dụ như trước.Sau đó là một lấy ví dụ như về cú pháp phối hợp hàm OFFSET cùng MATCH để tạo thành ra công dụng giống như VLOOKUPvà tra cứu :
=OFFSET(top_cell,Match(Lookup_Value,Lookup_Array,0),Offset_Col)
Công thức này sẽ tìm tuổi thọ của Mary vào trang tính mẫu:
=OFFSET(A1,MATCH(E2,A2:A5,0),2)
Công thức áp dụng giá trị "Mary" vào ô E2 và tìm thấy "Mary" trong cột A. Cách làm rồi khớp với giá trị trong cùng hàng nhưng hai cột bên đề nghị (cột C). Bởi "Mary" trong cột A, cách làm trả về quý giá trong sản phẩm 4 trong cột C (22).
Để biết thêm thông tin về hàm giá trị bù trừ , bấm số nội dung bài viết sau đây để xem nội dung bài viết trong cơ sở kiến thức của Microsoft:
Bạn đang cần tìm kiếm một hàm nâng cao trong Excel để liệt kê ra một danh sách các giá trị theo đk bạn đạt ra. Với phần đa hàm cơ phiên bản như hàm VLOOKUP tốt HLOOKUP sẽ xử lý vấn đề này không thật sự tốt. Cũng chính vì thế, Thuthuat
Office sẽ chuyển ra một số trong những hàm liệt kê list trong Excel mà chúng ta cũng có thể cần để xử lý các vấn đề của bản thân khi phối kết hợp chúng với các hàm cơ bạn dạng khác.
Các hàm liệt kê danh sách trong Excel
Có những hàm liệt kê list trong Excel từ dạng mảng, tham chiếu hoặc theo điều kiện. Thuộc Thuthuat
Office tra cứu hiểu chức năng và giải pháp sử dụng những hàm này bên dưới đây.
Hàm INDEX
Hàm INDEX trong Excel trả về quý giá tại một vị trí nhất quyết trong một phạm vi hoặc mảng. Bạn cũng có thể sử dụng INDEX nhằm truy xuất các giá trị trơ tráo hoặc tổng thể hàng cùng cột. INDEX thường được áp dụng với hàm MATCH, trong số đó MATCH định vị và cung ứng một vị trí mang đến INDEX.
Hàm INDEX bao gồm hai dạng: mảng và tham chiếu. Cả nhị biểu mẫu đều phải có cách trả về cực hiếm giống nhau là 1 trong những tham chiếu vào một mảng dựa vào vị trí hàng với cột duy nhất định.
Sự khác biệt là dạng tham chiếu của INDEX được cho phép tham chiếu nhiều hơn thế một mảng, cùng với một đối số tùy lựa chọn để lựa chọn mảng nào yêu cầu được sử dụng. Phần nhiều các bí quyết INDEX sử dụng dạng mảng, tuy vậy cả nhì dạng phần đa được trình bày sau đây để độc giả tham khảo.
Dạng mảng
Cú pháp của hàm INDEX như sau:
=INDEX (Array; row_num;
Trong đó:
Array: Dải ô hoặc mảng ô dữ liệu.row_num: Số hàngcol_num(tùy chọn): Số cộtNếu cả row_num và col_num hầu hết được cung cấp, INDEX trả về cực hiếm trong ô tại giao điểm của row_num và col_num.
Nếu row_num được đặt thành 0, INDEX trả về một mảng giá trị cho toàn cục cột. Để sử dụng những giá trị mảng này, bạn cũng có thể nhập hàm INDEX dưới dạng bí quyết mảng trong phạm vi ngang hoặc cung cấp mảng vào một hàm khác.
Nếu col_num được để thành 0, INDEX trả về một mảng cực hiếm cho cục bộ một hàng. Để sử dụng những giá trị mảng này, bạn có thể nhập hàm INDEX dưới dạng phương pháp mảng vào phạm vi dọc hoặc hỗ trợ mảng vào trong 1 hàm khác.
Dạng tham chiếu
INDEX dạng tham chiếu, tham số thứ nhất là tham chiếu mang lại một hoặc các phạm vi với đối số tùy lựa chọn thứ tư, area_num, được hỗ trợ để chọn phạm vi mê thích hợp. Cú pháp cho biểu mẫu mã tham chiếu của INDEX là:
=INDEX(reference; row_num;
Trong đó:
reference: mẫu mã tham chiếu.row_num: Số hàng.col_num(tùy chọn): Số cột.area_num: Đối số area_num được cung ứng dưới dạng một số vận động giống như 1 chỉ mục.Cũng hệt như dạng mảng của INDEX, dạng tham chiếu của INDEX trả về tham chiếu của ô tại giao điểm row_num cùng col_num. Sự khác hoàn toàn là đối số tham chiếu đựng được nhiều hơn một dải ô và area_num lựa chọn dải ô sẽ được sử dụng. Mảng đầu tiên phía bên trong tham chiếu là 1, mảng thứ hai là 2, v.v.
Hàm SMALL
Hàm SMALL của Excel trả về những giá trị số dựa vào vị trí của bọn chúng trong danh sách được xếp thứ hạng theo giá bán trị. Nói giải pháp khác, nó rất có thể truy xuất các giá trị “nhỏ nhất thiết bị n”, giá bán trị nhỏ nhất đầu tiên, giá chỉ trị nhỏ tuổi nhất sản phẩm công nghệ 2, giá trị nhỏ dại nhất sản phẩm công nghệ 3, v.v.
Hàm SMALL trả về giá chỉ trị nhỏ tuổi nhất đồ vật n vào mảng.
Cú pháp: =SMALL(Array; n)
Trong đó:
Array: là một trong những dải ô để trích xuất các giá trị nhỏ dại nhất.n: nhập vào đây một số trong những nguyên xác minh thứ tự giá bán trị nhỏ nhất đề xuất truy xuất.Hàm SMALL hữu dụng khi bạn có nhu cầu truy xuất giá trị thấp nhất lắp thêm n xuất phát từ 1 tập dữ liệu. Ví dụ, thời hạn nhanh nhất, thứ hai hoặc thứ cha trong một cuộc đua, như được thấy trong ảnh chụp screen ở trên.
Để thừa nhận giá trị khủng thứ n, hàm LARGE cũng tiến hành điều tương tự như như với vấn đề cho xác định giá trị lớn số 1 thứ n với cú pháp =LARGE(array; n).
Hàm IFERROR
Hàm IFERROR vào Excel trả về một kết quả thiết lập cấu hình khi một công thức tạo ra lỗi và một kết quả chuẩn chỉnh khi không tồn tại lỗi nào được phân phát hiện. IFERROR là 1 trong cách đương đại để bẫy và thống trị lỗi cơ mà không yêu cầu sử dụng những câu lệnh IF lồng nhau phức tạp hơn.
Hàm IFERROR giúp mồi nhử và cách xử trí lỗi cho các dữ liệu không xác minh hoặc lỗi cùng trả về các giá trị các bạn chỉ định cho các điều kiện lỗi.
Cú pháp: =IFERROR(value; value_if_error)
Trong đó:
value: giá bán trị, tham chiếu hoặc công thức bạn muốn kiểm tra lỗi.value_if_error: cực hiếm trả về trường hợp tìm thấy lỗi.Hàm IFERROR “bắt” lỗi trong bí quyết và trả về kết quả hoặc công thức sửa chữa thay thế khi lỗi được phát hiện.
Sử dụng hàm IFERROR để bả và xử lý những lỗi do những công thức hoặc hàm khác sản xuất ra. IFERROR kiểm tra những lỗi sau: # N / A, #VALUE !, #REF !, # DIV / 0 !, #NUM !, #NAME ?, hoặc #NULL !. Đối với lỗi ###### thực tế nó chưa phải là lỗi dữ liệu mà là lỗi hiển thị vào Excel đề nghị hàm IFERROR sẽ không bắt lỗi này.
Những ví dụ như sau để giúp bạn dễ tưởng tượng hơn về hàm IFERROR này.
Ví dụ: trường hợp A1 chứa 10, B1 trống và C1 chứa bí quyết = A1 / B1, cách làm sau đã bắt lỗi # DIV / 0! lỗi bởi vì chia A1 cho B1:
Cách liệt kê danh sách theo đk trong Excel
Bạn cũng hoàn toàn có thể sử dụng hàm IFERROR nhằm bắt lỗi #N/A vì hàm VLOOKUP tạo thành khi không tìm thấy cực hiếm tra cứu. Cú pháp gồm dạng như sau:
=IFERROR(VLOOKUP(value;data;column;0);”Không tra cứu thấy”)
Trong lấy ví dụ như sau đây, khi hàm VLOOKUP trả về một kết quả, hàm IFERROR đã trả về kết quả đó. Ví như hàm VLOOKUP trả về lỗi #N/A vì không kiếm thấy quý giá tra cứu, hàm IFERROR trả về “Không tìm kiếm thấy”.
Chú ý:
Nếu giá trị trống, nó được reviews là một chuỗi rỗng (“”) và không phải là 1 trong lỗi.Nếu value_if_error được cung ứng dưới dạng chuỗi trống (“”), không có thông báo như thế nào được hiển thị khi phát hiện nay lỗi.Nếu IFERROR được nhập dưới dạng công thức mảng, nó sẽ trả về một mảng công dụng với một mục cho mỗi ô có giá trị.Trong Excel 2013+, bạn có thể sử dụng hàm IFNA để mồi nhử và xử lý những lỗi #N/A nạm thể.
Trên phía trên là bài viết tổng hợp cho chính mình các hàm liệt kê danh sách trong Excel được sử dụng thông dụng trong việc tổng thích hợp dữ liệu. Nếu bạn sử dụng thành thạo với linh hoạt các hàm bên trên trong Excel, chắc chắn sẽ lấy lại cho bạn hiệu quả không hề ít trong các bước hằng ngày của chính bản thân mình như kiểm kê việc hàng hóa nhập xuất, v.v.
Xem thêm: Link Hướng Dẫn Cách Chế Tạo Đồ Trong Minecraft, 250+ Công Thức Chế Tạo Đồ Trong Minecraft
Nếu bạn đang sẵn có ý định tiếp xúc và cải tiến và phát triển thêm sự phát âm biết và thông thạo Excel có thể theo dõi một số nội dung bài viết khác dưới đây:
Nếu thấy bài viết hữu ích hãy để lại like & giới thiệu cũng như comment ở phần dưới nếu có bất kỳ thắc mắc cần giải đáp. Bạn đọc hoàn toàn có thể theo dõi tiếp tục các nội dung bài viết hữu ích không giống của Thuthuat
Office được đăng tải đa số đặn bên trên trang.