Xin chào mọi người,
Tôi đang cố gắng thực hiện vlookup nhưng không thành công để trả về ngày sớm nhất và mới nhất trong cột K và cột N, nơi nó lấy ngày trong tab Áp dụng thanh toán . Một số khách hàng sẽ có nhiều ngày nhưng tôi chỉ cần ngày sớm nhất và mới nhất được điền vào cột K và N. Ngoài ra để tính chênh lệch ngày so với Ngày thanh toán [cột J] và để điền vào cột L và O. Khi nó có số ngày chênh lệch, nếu nó có chênh lệch 1-5 ngày để đặt 2%, 5-15 để đặt 5% và 15 trở lên là 10%, hãy điền vào cột M và P. Nếu ai đó có thể vui lòng giúp đỡ với một công thức để thực hiện công việc này, tôi sẽ đánh giá rất cao. Cảm ơn bạn trước.
Trễ thanh toán [tab]
Thanh toán được áp dụng [tab]
Để dễ hiểu hơn, hãy chuyển đổi Nhật ký của bạn thành Bảng Excel [CTRL t] với các tiêu đề. Tôi đặt tên cho nó là tLog
Sau đó, trong Tổng quan, bắt đầu từ ô C2, đặt công thức này và kéo nó xuống
= INDEX[XLOOKUP[A2,INDEX[SORTBY[tLog,tLog[Date Contacted],-1],,1],INDEX[SORTBY[ tLog,tLog[Date Contacted],-1 ],,]],1,{2,7,5,8}]
Trong đó A2 là tên của người bạn đang tra cứu. {2,7,5,8} là danh sách các cột bạn muốn truy xuất từ tLog. [NB. nếu bạn để trống, nó sẽ lấy tất cả các cột. ]
Điều này khó đọc, vì vậy bạn có thể đơn giản hóa nó và tăng tốc thời gian tính toán bằng cách xác định Phạm vi được đặt tên trong tab Công thức
Bạn có thể đặt tên cho bảng sắp xếp _sTable và đặt nó theo công thức này
=SORTBY[ tLog, tLog[Date Contacted], -1 ]
Điều đó sẽ cho phép bạn đặt một công thức dễ đọc hơn và nhanh hơn nhiều trong C2
= INDEX[XLOOKUP[A2,INDEX[_sTable,,1],INDEX[_sTable,,]],1,{2,7,5,8}]
Kết quả sẽ như thế này
Cách thức hoạt động của tính năng này là nó tạo ra một phiên bản mới của bộ nhớ Đăng nhập được sắp xếp theo Ngày của Liên hệ Tiếp theo và sau đó thực hiện tìm kiếm XLOOKUP trên bảng này thay vì bảng Nhật ký của bạn
Công thức thay đổi và bây giờ bắt đầu và kết thúc bằng dấu ngoặc nhọn, đừng tự nhập các ký tự này. Chúng tự động xuất hiện
Giải thích công thức mảng trong ô C5
Bạn có thể làm theo nếu bạn chọn ô C5 và chuyển đến tab "Công thức" trên ruy-băng rồi nhấp vào nút "Đánh giá công thức". Nhấp vào nút "Đánh giá", được hiển thị trên hộp thoại, để chuyển sang bước tiếp theo
Bước 1 - Tìm giá trị bằng với giá trị tra cứu
C3=A8. A14
trở thành
"EE"={"AA";"CC";"EE";"BB";"EE";"VV";"EE"}
và trả về
{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE}
Bước 2 - Chuyển đổi giá trị boolean thành ngày tương ứng
NẾU[C3=A8. A14, B8. B14]
trở thành
IF[{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE} ,B8. B14]
trở thành
IF[{FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE} , {40152; 40156; 40323; 40068; 40149; 40312; 40195}]
và trả về
{FALSE;FALSE;40323;FALSE;40149;FALSE;40195}
Bước 3 - Trả về giá trị lớn nhất
=MAX[IF[C3=A8. A14, B8. B14]]
trở thành
=MAX[{FALSE;FALSE;40323;FALSE;40149;FALSE;40195}]
và trả về 40323 được định dạng là 2010-05-25
Tra cứu một giá trị và tìm ngày tối đa [Pivot Table]
Các công thức được trình bày trong bài viết này có thể quá chậm hoặc chiếm quá nhiều bộ nhớ nếu bạn làm việc với lượng dữ liệu khổng lồ. Bảng Pivot là một lựa chọn tuyệt vời trong những trường hợp như vậy, nó rất nhanh ngay cả khi có nhiều dữ liệu
Cách thiết lập Bảng tổng hợp
- Chọn dãy ô chứa dữ liệu
- Chuyển đến tab "Chèn" trên ruy-băng
- Nhấp vào nút "Bảng tổng hợp"
- Một hộp thoại xuất hiện.
Tôi thường đặt Pivot Table trên một trang tính mới để nó không ẩn các phần của tập dữ liệu trong khi lọc, v.v.
- Nhấp vào Giá trị và kéo đến trường Bộ lọc, xem mũi tên màu xanh ở trên
- Nhấp vào Ngày và kéo đến trường Giá trị
- Nhấp vào "Đếm ngày"
- Nhấp vào "Cài đặt trường giá trị. "
- Nhấp vào "Max" để chọn nó
- Nhấp vào nút "Định dạng số"
- Nhấp vào danh mục "Ngày" và chọn một loại
- Nhấp vào nút OK
- Nhấp vào nút OK
Bấm vào ô B1 để lọc ngày mới nhất dựa trên giá trị đã chọn, hình trên hiển thị giá trị EE đã chọn và ngày mới nhất dựa trên giá trị đó là 25/5/2010
Tra cứu tất cả các giá trị và tìm ngày mới nhất [sớm nhất]
Công thức sau tìm trong cột C để biết ngày gần đây nhất cho mỗi giá trị trong cột B
Công thức trong ô D3
=IF[MAX[INDEX[[B3=$B$3. $B$14]*$C$3. $C$14,]]=C3,"Mới nhất",""]Công thức mảng trong ô D3
=IF[MAX[IF[B3=$B$3. $B$14, $C$3. $C$14]]=C3, "Mới nhất", ""]Công thức trong ô D3
=IF[MAXIFS[$C$3. $C$14,$B$3. $B$14,B3]=C3,"Mới nhất",""]Cách sao chép công thức mảng
- Sao chép ô C2
- Chọn dãy ô C3. C8
- Dán
Tra cứu và tìm ngày cuối cùng bằng nhiều điều kiện
Công thức trong ô H3
=MAX[INDEX[[C2. C29=H1]*[D2. D29=H2]*E2. E29,]]Công thức mảng trong ô H3
=MAX[NẾU[[C2. C29=H1]*[D2. D29=H2],E2. E29,""]]Tra cứu và tìm ngày mới nhất trên nhiều sheet
Hình ảnh sau đây cho bạn thấy một sổ làm việc có 4 trang tính. Công thức trong ô B3 tìm kiếm ngày mới nhất trong cả ba trang tính bằng cách sử dụng điều kiện trong ô B2
Công thức mảng trong ô B3
=MAX[IF[B2=Tháng 1. $B$2. $B$10, tháng 1. $A$2. $A$10, ""], NẾU[B2=Tháng 2. $B$2. $B$10, tháng 2. $A$2. $A$10, ""], NẾU[B2=Tháng 3. $B$2. $B$10, tháng 3. $A$2. $A$10, ""]]Tra cứu và tìm ngày mới nhất, trả về giá trị tương ứng trên cùng một hàng
Nhập một phần tư vào ô G3.
Công thức mảng trong ô G3
Nếu bạn thích một công thức thông thường trong G3
=MAX[INDEX[[B3. B19=G2]*C3. C19,]]
Công thức trong ô G4
=INDEX[$D$3. $D$19, TÓM TẮT[[B3. B19=G2]*[G3=C3. C19]*MATCH[ROW[B3]. B19], HÀNG[B3. B19]]]]Lập chỉ mục & So khớp để tìm giá trị theo ngày mới nhất trong Microsoft Excel
Nếu bạn đang tìm công thức tìm giá trị tra cứu & giá trị mới nhất theo ngày thì bài viết này thực sự hữu ích cho bạn. Trong bài viết này, chúng ta sẽ tìm hiểu cách tìm giá trị khớp và sau đó công thức sẽ kiểm tra đầu ra sau khi kiểm tra ngày gần nhất
Thí dụ. Tôi cần công thức để tra cứu từng sản phẩm cụ thể sau đó tìm giá của sản phẩm vào ngày gần nhất
Sau đây là ảnh chụp nhanh dữ liệu tôi. e. cột A có Hóa đơn số. , cột B có Ngày, cột C có Sản phẩm & cột D có Giá
Công thức mà chúng tôi đang tìm kiếm trước hết phải kiểm tra Sản phẩm từ cột C và sau đó trả lại Giá cho ngày gần nhất
Chúng tôi sẽ sử dụng kết hợp các hàm INDEX, MATCH & MAX để trả về đầu ra
Trong ô G3, công thức là
{=INDEX[$D$2. $D$10,MATCH[1,INDEX[[$C$2. $C$10=$F$3]*[$B$2. $B$10=MAX[IF[$C$2. $C$10=F3,$B$2. $B$10]]],0],0]]}Công thức trên đã sử dụng hàm Max để xác định ngày mới nhất từ tất cả các ngày đã cho cho Sản phẩm phù hợp từ cột C. Để kiểm tra công thức trên nếu chúng ta thay đổi ngày ở ô B7 thì sẽ được kết quả như mong muốn. Tham khảo ảnh chụp bên dưới. Bằng cách này, chúng tôi có thể cung cấp giá của sản phẩm với ngày mới nhất
Đây là tất cả các ghi chú quan sát sử dụng công thức trong Excel
Ghi chú.
Đây là một công thức mảng. Do đó, chúng ta cần nhấn tổ hợp phím CTRL + SHIFT + END để có kết quả chính xác
Hy vọng bài viết này về Cách tra cứu hoặc tìm giá trị với Ngày cuối cùng trong Excel là giải thích. Tìm thêm các bài viết về tính giá trị và công thức Excel liên quan tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ chúng với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại info@exceltip. com
Những bài viết liên quan
Cách lấy giá mới nhất trong Excel. Việc cập nhật giá trong bất kỳ hoạt động kinh doanh nào là điều phổ biến và việc sử dụng giá mới nhất cho bất kỳ giao dịch mua hoặc bán nào là điều bắt buộc. Để lấy giá mới nhất từ một danh sách trong Excel, chúng ta sử dụng hàm LOOKUP. Hàm LOOKUP lấy giá mới nhất
Hàm VLOOKUP tính điểm trong Excel. Để tính điểm IF và IFS không phải là các hàm duy nhất mà bạn có thể sử dụng. VLOOKUP hiệu quả và năng động hơn cho các phép tính có điều kiện như vậy. Để tính điểm bằng VLOOKUP, chúng ta có thể sử dụng công thức này
17 điều về Excel VLOOKUP. VLOOKUP được sử dụng phổ biến nhất để truy xuất các giá trị khớp nhưng VLOOKUP có thể làm được nhiều hơn thế. Dưới đây là 17 điều về VLOOKUP mà bạn nên biết để sử dụng hiệu quả
Tra cứu văn bản đầu tiên từ một danh sách trong Excel. Hàm VLOOKUP hoạt động tốt với các ký tự đại diện. Chúng ta có thể sử dụng điều này để trích xuất giá trị văn bản đầu tiên từ một danh sách nhất định trong excel. Đây là công thức chung
Tra cứu ngày với giá trị cuối cùng trong danh sách. Để truy xuất ngày chứa giá trị cuối cùng, chúng ta sử dụng hàm LOOKUP. Hàm này kiểm tra ô chứa giá trị cuối cùng trong một vectơ rồi sử dụng tham chiếu đó để trả về ngày
Các bài viết phổ biến
50 phím tắt Excel để tăng năng suất của bạn. Thực hiện nhanh hơn các nhiệm vụ của bạn trong Excel. Những phím tắt này sẽ giúp bạn tăng hiệu quả làm việc trong Excel
Cách sử dụng Hàm VLOOKUP trong Excel. Đây là một trong những hàm phổ biến và được sử dụng nhiều nhất trong excel được sử dụng để tra cứu giá trị từ các phạm vi và trang tính khác nhau.
Cách sử dụng Hàm IF trong Excel. Câu lệnh IF trong Excel kiểm tra điều kiện và trả về một giá trị cụ thể nếu điều kiện là TRUE hoặc trả về một giá trị cụ thể khác nếu FALSE
Cách sử dụng Hàm SUMIF trong Excel. Đây là một chức năng thiết yếu khác của bảng điều khiển. Điều này giúp bạn tổng hợp các giá trị trên các điều kiện cụ thể
Cách sử dụng Hàm COUNTIF trong Excel. Đếm các giá trị có điều kiện bằng chức năng tuyệt vời này. Bạn không cần lọc dữ liệu của mình để đếm các giá trị cụ thể. Chức năng Countif là điều cần thiết để chuẩn bị bảng điều khiển của bạn