Chia sẻ công thức Excel tìm kiếm từ dưới lên

 Trong thực tế nhiều tình huống bố cục dữ liệu có giá trị trùng lặp hoặc lấy số liệu từ bảng chi tiết thì người dùng cần lấy giá trị cuối cùng từ trên xuống hoặc giá trị đầu tiên từ dưới lên. 

CÔNG THỨC EXCEL TÌM KIẾM LẤY GIÁ TRỊ CUỐI CÙNG

Ví dụ tình huống: 1 sản phẩm nhiều dòng và giá bán khác nhau

Trường hợp 1: Tìm kiếm lấy dữ liệu dòng cuối cùng thỏa mãn 1 điều kiện

Ví dụ: Lấy giá SPA ở dòng cuối cùng

Công thức tìm kiếm lấy giá trị ở dòng cuối cùng áp dụng với mọi phiên bản Office:

=LOOKUP(2,1/($A$2:$A$13=F2),$C$2:$C$13)

Công thức tìm kiếm lấy giá trị ở dòng cuối cùng áp dụng với Office 365

=XLOOKUP(F2,$A$2:$A$13,$C$2:$C$13,,0,-1)

Trường hợp 2: Tìm kiếm lấy dòng dữ liệu cuối cùng thỏa mãn 2 điều kiện trở lên

Ví dụ: Lấy giá của SPA, loại 1 ở dòng cuối cùng

Công thức tìm kiếm lấy giá trị ở dòng cuối cùng áp dụng với mọi phiên bản Office

=LOOKUP(2,1/($A$2:$A$13=F2)/($B$2:$B$13=G2),$C$2:$C$13)

Công thức tìm kiếm lấy giá trị ở dòng cuối cùng áp dụng với Office 365

=XLOOKUP(F2&G2,$A$2:$A$13&$B$2:$B$13,$C$2:$C$13,,0,-1)

Trường hợp 3: Tìm kiếm lấy dữ liệu theo theo ngày gần nhất

Tham khảo công thức TẠI ĐÂY

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp

{Đt Zalo} - 038 696 1334

Xem chi tiết

Ứng dụng hàm SORT trong các tình huống báo cáo chi tiết

 Hàm SORT nếu dùng độc lập thì tương tự như sử dụng tính năng Sort và Advance Sort của Excel. Tuy nhiên trong thực tế khi kết hợp hàm SORT với một số hàm mảng của Office 365 để làm báo cáo thì sẽ giúp báo cáo trở lên linh hoạt hơn rất nhiều và công thức cũng khá ngắn gọn

Dữ liệu tình huống: 

HƯỚNG DẪN KẾT HỢP HÀM SORT VỚI FILTER ĐỂ LẬP BẢNG KÊ TRÍCH LỌC ĐIỀU KIỆN

Giả sử ô H1 nhập 1 mã hàng hóa bất kỳ thì hàm FILTER tại ô G3 là:

=FILTER(A3:D15,C3:C15=H1)

Khi đó coi hàm FILTER là 1 mảng kết quả thì sẽ lồng hàm SORT bên ngoài và điều kiện sắp xếp là cột ngày tăng dần thì công thức là:

=SORT(FILTER(A3:D15,C3:C15=H1),1,1)

Kết quả, chỉ cần thay đổi điều kiện tại ô H1 thì sẽ được bảng kê chi tiết theo mã hàng

HƯỚNG DẪN KẾT HỢP HÀM SORT VỚI HÀM INDEX VÀ SEQUENCE ĐỂ LẤY RA CÁC DÒNG LỚN VÀ NHỎ

Giả sử cần lấy 5 dòng có số lượng lớn nhất thì dùng hàm SORT để sắp xếp theo thứ tự giảm dần

=SORT(A3:D15,4,-1)

Thực hiện lồng thêm hàm INDEX và kết hợp hàm SEQUENCE để lấy ra 5 dòng dữ liệu đầu tiên

=INDEX(SORT(A3:D15,4,-1),SEQUENCE(5),SEQUENCE(,4))

Tương tự như vậy nếu lấy 5 dòng có số lượng nhỏ nhất thì công thức là:

=INDEX(SORT(A3:D15,4,1),SEQUENCE(5),SEQUENCE(,4))

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm SORT trong Excel 365

 Hàm SORT là một hàm rất linh hoạt trong Office 365, giúp người dùng sắp xếp lại dữ liệu và báo cáo theo nhiều tiêu chí

HƯỚNG DẪN SỬ DỤNG HÀM SORT TRONG OFFICE 365

Cấu trúc hàm SORT: =SORT(array,sort_index,sort_order,by_col)

Trong đó: 

- array: mảng hoặc vùng giá trị

- sort_index: cột thứ mấy trong mảng cần sắp xếp (tính từ trái sang phải)

- sort_order: sắp xếp tăng dần hay giảm dần. Trong đó: 1 là sắp xếp tăng dần và -1 là sắp xếp giảm dần

- by_col: tham số này mặc định là False tức sắp xếp theo dòng

Tình huống ví dụ: Bảng dữ liệu từ A3:D15 đang là dữ liệu lộn xộn không sắp xếp theo thứ tự

Trường hợp số 1: Sắp xếp theo 1 tiêu chí. Ví dụ cột ngày sắp xếp tăng dần thì công thức là:

=SORT(A3:D15,1,1)

Trường hợp số 2: Sắp xếp theo 2 tiêu chí. Ví dụ: Cột ngày sắp xếp tăng dần, nếu trong cùng 1 ngày mà có nhiều phiếu thì phiếu nhập (PN) đứng trước phiếu xuất (PX)

=SORT(A3:D15,{1,2},{1,1})

Trong đó: {1,2} là 2 cột số 1 và số 2 tương ứng với cột ngày và cột số phiếu

Tương tự như vậy nếu đưa nhiều cột hơn thì hàm SORT sẽ sắp xếp theo nhiều tiêu chí hơn. Lưu ý, số phần tử ở tham số thứ 2 đưa vào phải bằng số phần tử ở tham số thứ 3

Ví dụ: =SORT(A3:D15,{1,2,4},{1,1,-1})

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn hàm FILTER trong Excel 365

 Filter là một tính năng được sử dụng rất phổ biến và gần như ai cũng dùng khi nhắc đến Excel. Nhiều trường hợp muốn tách riêng số liệu của 1 đối tượng nào đó sang 1 sheet khác thì phải Filter sau đó copy và Paste Value kết quả. 

Trong Office 365, hàm FILTER sẽ giúp người dùng linh hoạt hơn trong truy vấn số liệu, lập báo cáo khi trả về mảng kết quả

HƯỚNG DẪN SỬ DỤNG HÀM FILTER TRONG OFFICE 365

Số liệu ví dụ mẫu:

Cấu trúc hàm FILTER: =FILTER(array,include,if_empty)

Trong đó:

- array: Mảng giá trị hoặc vùng (range)

- include: Điều kiện filter

- if_empty: Nếu không có giá trị thỏa mãn thì kết quả trả về là gì. Nếu tham số này bỏ trống mà không có giá trị thỏa mãn điều kiện lọc thì hàm FILTER trả về lỗi #CALC!

Ví dụ 1: Trích lọc số liệu của mã hàng HH006

Công thức: =FILTER(A2:E15,C2:C15="HH006")

Ví dụ 2: Trích lọc số liệu của mã hàng HH006 có số lượng >5

Công thức: =FILTER(A2:E15,(C2:C15="HH006")*(D2:D15>5))

Ví dụ 3: Trích lọc số liệu của mã hàng HH006, HH007, HH008 và số lượng >=10

Công thức 1:

=FILTER(A2:E15,((C2:C15="HH006")+(C2:C15="HH007")+(C2:C15="HH008"))*(D2:D15>=10))

Công thức 2: 

=FILTER(A2:E15,ISNUMBER(MATCH(C2:C15,{"HH006","HH007","HH008"},0))*(D2:D15>=10))

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn hàm UNIQUE trong Excel 365

 Ở các bản Office cũ thì việc lấy ra danh sách duy nhất (ở 1 cột hoặc nhiều cột) thì các cách thường dùng là: Remove Duplicate, Pivot Table, Công thức (nhưng khá phức tạp). Thì trong Office 365 được bổ sung thêm hàm UNIQUE hỗ trợ rất hiệu quả cho việc lấy ra danh sách duy nhất theo dạng mảng, tốc độ nhanh và công thức rất ngắn gọn

HƯỚNG DẪN SỬ DỤNG HÀM UNIQUE TRONG OFFICE 365

Cấu trúc hàm UNIQUE: =UNIQUE(array,by_col,exactly_once)

Trong đó:

- array: mảng hoặc vùng

- by_col: TRUE/FALSE. TRUE - Theo cột, FALSE - Theo dòng

- exactly_one: TRUE/FALSE. TRUE - Lấy ra danh sách chỉ xuất hiện 1 lần (bỏ trùng hoàn toàn), FALSE - Lấy ra danh sách duy nhất

Tình huống 1: Hàm UNIQUE tham số truyền vào là 1 cột

- Nếu công thức là: =UNIQUE(C2:C10,FALSE,FALSE) thì trả về danh sách duy nhất (như hình ảnh)

- Nếu công thức là: =UNIQUE(C2:C10,FALSE,TRUE) thì trả về là SPD và SPF => Vì 2 sản phẩm này chỉ xuất hiện 1 lần

Tình huống 2: Hàm UNIQUE tham số truyền vào là nhiều cột

Thì kết quả khi dùng hàm UNIQUE với công thức:

=UNIQUE(C1:E14,FALSE,TRUE)

Tương tự như vậy khi áp dụng hàm UNIQUE lấy duy nhất khi chạy theo cột

Liên hệ tư vấn khóa học Excel cho người đi làm & đặt hàng đào tạo tại doanh nghiệp
{Đt Zalo} - 038 696 1334

Xem chi tiết

Hướng dẫn sử dụng hàm XLOOKUP trong Excel 365

 Hàm XLOOKUP là 1 hàm tìm kiếm rất mạnh có trong Microsofts Exel 365 với nhiều tùy chọn tìm kiếm rất thông minh. 

Cấu trúc hàm XLOOKUP:

=XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

Các tham số XLOOKUP:

- lookup_value: Giá trị tìm kiếm 

- lookup_array: tìm kiếm ở mảng nào

- return_array: kết quả ở mảng nào

- if_not_found: nếu không tìm thấy thì trả về kết quả thay thế là gì (thay thế cho hàm IFERROR)

- match_mode: Tham số này có 4 lựa chọn

  * 0: Exact match: Tìm kiếm tuyệt đối chính xác

  * -1: Exact match or next smaller item: Tìm kiếm chính xác, nếu không tìm đươc thì sẽ lấy giá trị gần nhất mà nhỏ hơn

  * 1: Exact match or next larger item: Tìm kiếm chính xác, nếu không tìm được thì sẽ lấy giá trị gần nhất mà lớn hơn

  * 2: wildcard character match: Tìm kiếm kiểu dùng ký tự đại diện *, ?, ~

- search_mode: Tham số này có 4 tùy chọn

 * 1: Search fist to last: Tìm kiếm từ trên xuống dưới (từ trái sang phải)

 * -1: Search last to fist: Tím kiếm từ dưới lên trên (từ phải sang trái)

 * 2: Binary search (sorter accending order): Sắp xếp theo thứ tự tăng dần rồi tìm kiếm

 * -2: Binary search (sorter descending order): Sắp xếp theo thứ tự giảm dần rồi tìm kiếm

Ví dụ tình huống dùng hàm XLOOKUP

Giả sử giá trị tìm kiếm lookup_value là 5.5 thì ta có các công thức sau:

=XLOOKUP(5.5,B2:B8,C2:C8,"Kiểm tra lại nhé",-1,1)

Liên hệ tư vấn khóa học Excel cho người đi làm hoặc đặt hàng đào tạo tại doanh nghiệp

{Đt+Zalo} - 038 696 1334

Xem chi tiết