Tìm số gần nhất

Nội dung

Trong thực tế, rất thường xuyên có những trường hợp khi bạn và tôi cần tìm giá trị gần nhất trong một tập hợp (bảng) liên quan đến một số nhất định. Nó có thể là, ví dụ:

Tính chiết khấu tùy theo khối lượng.
Tính số tiền thưởng tùy theo việc thực hiện kế hoạch.
Cách tính cước phí vận chuyển tùy theo khoảng cách.
Lựa chọn các loại container phù hợp cho hàng hóa, v.v.

Hơn nữa, có thể yêu cầu làm tròn cả lên và xuống, tùy thuộc vào tình huống.

Có một số cách - hiển nhiên và không quá rõ ràng - để giải quyết một vấn đề như vậy. Hãy xem xét chúng một cách tuần tự.

Để bắt đầu, hãy tưởng tượng một nhà cung cấp giảm giá khi bán buôn và tỷ lệ phần trăm chiết khấu phụ thuộc vào số lượng hàng hóa được mua. Ví dụ: khi mua nhiều hơn 5 chiếc sẽ được chiết khấu 2% và khi mua từ 20 chiếc - đã là 6%, v.v.

Làm thế nào để tính phần trăm chiết khấu khi nhập số lượng hàng đã mua một cách nhanh chóng và đẹp mắt?

Phương pháp 1: IF lồng nhau

Một phương pháp từ loạt bài “có gì để suy nghĩ - bạn cần phải nhảy!”. Sử dụng các hàm lồng nhau IF (NẾU) để kiểm tra tuần tự xem giá trị ô có rơi vào từng khoảng thời gian hay không và hiển thị chiết khấu cho phạm vi tương ứng. Nhưng công thức trong trường hợp này có thể rất phức tạp:

Tìm số gần nhất

Tôi nghĩ rằng việc gỡ lỗi một “con búp bê quái vật” như vậy hoặc cố gắng thêm một vài điều kiện mới vào nó sau một thời gian là điều rất thú vị.

Ngoài ra, Microsoft Excel có giới hạn lồng cho hàm IF - 7 lần trong các phiên bản cũ hơn và 64 lần trong các phiên bản mới hơn. Nếu bạn cần thêm thì sao?

Phương pháp 2. VLOOKUP với chế độ xem khoảng thời gian

Phương pháp này nhỏ gọn hơn nhiều. Để tính toán phần trăm chiết khấu, hãy sử dụng hàm huyền thoại VPR (Vlookup) ở chế độ tìm kiếm gần đúng:

Tìm số gần nhất

Ở đâu

B4 - giá trị của số lượng hàng hóa trong giao dịch đầu tiên mà chúng tôi đang tìm kiếm chiết khấu
$ G $ 4: $ H $ 8 - một liên kết đến bảng chiết khấu - không có "tiêu đề" và với các địa chỉ được cố định bằng dấu $.
2 - số thứ tự của cột trong bảng chiết khấu mà từ đó chúng ta muốn lấy giá trị chiết khấu
TRUE - đây là nơi chôn cất "con chó". Nếu là đối số hàm cuối cùng VPR chỉ định nói dối (SAI) hoặc 0, sau đó hàm sẽ tìm kiếm trận đấu nghiêm ngặt trong cột số lượng (và trong trường hợp của chúng tôi, nó sẽ báo lỗi # N / A, vì không có giá trị 49 trong bảng chiết khấu). Nhưng nếu thay vào đó nói dối viết TRUE (THẬT) hoặc 1, thì hàm sẽ không tìm kiếm chính xác, nhưng nhỏ nhất gần nhất giá trị và sẽ cung cấp cho chúng tôi phần trăm chiết khấu mà chúng tôi cần.

Nhược điểm của phương pháp này là phải sắp xếp bảng chiết khấu theo thứ tự tăng dần theo cột đầu tiên. Nếu không có cách sắp xếp như vậy (hoặc nó được thực hiện theo thứ tự ngược lại), thì công thức của chúng ta sẽ không hoạt động:

Tìm số gần nhất

Theo đó, cách tiếp cận này chỉ có thể được sử dụng để tìm giá trị nhỏ nhất gần nhất. Nếu bạn cần tìm giá trị lớn nhất gần nhất, thì bạn phải sử dụng một cách tiếp cận khác.

Phương pháp 3. Tìm giá trị lớn nhất gần nhất bằng cách sử dụng hàm INDEX và MATCH

Bây giờ chúng ta hãy nhìn nhận vấn đề của chúng ta từ một khía cạnh khác. Giả sử chúng tôi bán một số mẫu máy bơm công nghiệp có công suất khác nhau. Bảng bán hàng bên trái hiển thị điện năng theo yêu cầu của khách hàng. Chúng ta cần chọn một máy bơm có công suất lớn nhất hoặc bằng gần nhất, nhưng không nhỏ hơn yêu cầu của dự án.

Hàm VLOOKUP sẽ không giúp ích gì ở đây, vì vậy bạn sẽ phải sử dụng hàm tương tự của nó - một loạt các hàm INDEX (MỤC LỤC) và THÊM ĐƯỢC TIẾP XÚC (CUỘC THI ĐẤU):

Tìm số gần nhất

Ở đây, hàm MATCH với đối số cuối cùng là -1 hoạt động ở chế độ tìm giá trị lớn nhất gần nhất và hàm INDEX sau đó trích xuất tên mô hình mà chúng ta cần từ cột liền kề.

Phương pháp 4. Chức năng mới XEM (XLOOKUP)

Nếu bạn có phiên bản Office 365 với tất cả các bản cập nhật được cài đặt, thì thay vì VLOOKUP (Vlookup) bạn có thể sử dụng tính năng tương tự của nó - chức năng XEM (XTRA CỨU), mà tôi đã phân tích chi tiết:

Tìm số gần nhất

Đây:

B4 - giá trị ban đầu của số lượng sản phẩm mà chúng tôi đang tìm kiếm chiết khấu
$ G $ 4: $ G $ 8 - phạm vi mà chúng tôi đang tìm kiếm các kết quả phù hợp
$ H $ 4: $ H $ 8 - phạm vi kết quả mà bạn muốn trả lại chiết khấu
đối số thứ tư (-1) bao gồm tìm kiếm số nhỏ nhất gần nhất mà chúng tôi muốn thay vì kết hợp chính xác.

Ưu điểm của phương pháp này là không cần sắp xếp bảng chiết khấu và khả năng tìm kiếm, nếu cần, không chỉ giá trị nhỏ nhất gần nhất mà cả giá trị lớn nhất gần nhất. Đối số cuối cùng trong trường hợp này sẽ là 1.

Tuy nhiên, thật không may, không phải ai cũng có tính năng này - chỉ những chủ sở hữu hài lòng của Office 365.

Phương pháp 5. Truy vấn nguồn

Nếu bạn chưa quen với phần bổ trợ Power Query mạnh mẽ và hoàn toàn miễn phí dành cho Excel, thì bạn đang ở đây. Nếu bạn đã quen thuộc, sau đó chúng ta hãy thử sử dụng nó để giải quyết vấn đề của chúng ta.

Trước tiên, hãy làm một số công việc chuẩn bị:

Hãy chuyển đổi bảng nguồn của chúng tôi thành động (thông minh) bằng cách sử dụng phím tắt Ctrl+T hoặc đội Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng).
Để rõ ràng, chúng ta hãy đặt tên cho chúng. Bán hàng и Giảm giá chuyển hướng xây dựng (Thiết kế).
Lần lượt tải từng bảng vào Power Query bằng nút Từ Bảng / Phạm vi chuyển hướng Ngày (Dữ liệu - Từ bảng / phạm vi). Trong các phiên bản Excel gần đây, nút này đã được đổi tên thành Với những chiếc lá (Từ trang tính).
Nếu các bảng có tên cột khác nhau với số lượng, như trong ví dụ của chúng tôi (“Số lượng hàng hóa” và “Số lượng từ…”), thì chúng phải được đổi tên trong Power Query và được đặt tên giống nhau.
Sau đó, bạn có thể quay lại Excel bằng cách chọn lệnh trong cửa sổ trình soạn thảo Power Query Trang chủ - Đóng và tải - Đóng và tải vào… (Trang chủ - Đóng & Tải - Đóng & Tải vào…) và sau đó là tùy chọn Chỉ cần tạo kết nối (Chỉ tạo kết nối).
Sau đó, thú vị nhất bắt đầu. Nếu bạn có kinh nghiệm về Power Query, thì tôi cho rằng dòng suy nghĩ khác nên theo hướng hợp nhất hai bảng này bằng một truy vấn nối (hợp nhất) a la VLOOKUP, như trường hợp của phương pháp trước. Trên thực tế, chúng ta sẽ cần hợp nhất trong chế độ thêm, điều này thoạt nhìn không rõ ràng chút nào. Chọn trong tab Excel Dữ liệu - Nhận dữ liệu - Kết hợp các yêu cầu - Thêm (Dữ liệu - Lấy dữ liệu - Kết hợp truy vấn - Nối) và sau đó là bàn của chúng tôi Bán hàng и Giảm giá trong cửa sổ xuất hiện:
Sau khi nhấp vào OK các bảng của chúng ta sẽ được dán thành một tổng thể duy nhất - dưới nhau. Xin lưu ý rằng các cột có số lượng hàng hóa trong các bảng này nằm dưới nhau, bởi vì. họ có cùng tên:
Nếu trình tự ban đầu của các hàng trong bảng bán hàng là quan trọng đối với bạn, thì để sau tất cả các lần chuyển đổi tiếp theo, bạn có thể khôi phục lại nó, hãy thêm một cột được đánh số vào bảng của chúng tôi bằng lệnh Thêm một cột - Cột chỉ mục (Thêm cột - Cột chỉ mục). Nếu trình tự các dòng không quan trọng đối với bạn, thì bạn có thể bỏ qua bước này.
Bây giờ, bằng cách sử dụng danh sách thả xuống trong tiêu đề của bảng, hãy sắp xếp nó theo cột Số Lượng Tăng dần:
Và thủ thuật chính: nhấp chuột phải vào tiêu đề cột Giảm giá chọn một đội Điền vào (Điền vào). Các ô trống với vô giá trị tự động điền các giá trị chiết khấu trước đó:
Nó vẫn để khôi phục trình tự ban đầu của các hàng bằng cách sắp xếp theo cột Chỉ số (bạn có thể xóa nó một cách an toàn sau này) và loại bỏ những dòng không cần thiết bằng bộ lọc vô giá trị theo cột Mã giao dịch: