가장 가까운 전화번호 찾기 – 내 주변의 건강식품

내용

실제로, 당신과 내가 주어진 숫자와 관련하여 집합(표)에서 가장 가까운 값을 찾아야 하는 경우가 매우 자주 있습니다. 예를 들면 다음과 같습니다.

수량에 따른 할인 계산.
계획 구현에 따른 보너스 금액 계산.
거리에 따른 배송비 계산.
상품 등에 적합한 용기 선정

또한 상황에 따라 위아래로 반올림이 필요할 수 있습니다.

이러한 문제를 해결하는 방법에는 여러 가지가 있습니다. 순차적으로 살펴보겠습니다.

먼저 도매상 할인을 제공하는 공급자를 상상해 봅시다. 할인 비율은 구매한 상품의 수량에 따라 다릅니다. 예를 들어 5개 이상 구매 시 2% 할인이 제공되며, 20개 이상 구매 시 이미 6% 등입니다.

구매한 상품의 수량을 입력할 때 할인율을 빠르고 아름답게 계산하는 방법은 무엇입니까?

방법 1: 중첩 IF

"무엇을 생각할 것인가 - 뛰어야 한다!" 시리즈의 방법. 중첩 함수 사용 IF (만약) 셀 값이 각 구간에 속하는지 순차적으로 확인하여 해당 범위에 대한 할인을 표시합니다. 그러나 이 경우 공식은 매우 복잡할 수 있습니다.

가장 가까운 숫자 찾기

그런 "괴물 인형"을 디버깅하거나 잠시 후에 몇 가지 새로운 조건을 추가하는 것이 재미있는 것은 분명하다고 생각합니다.

또한 Microsoft Excel에는 IF 함수에 대한 중첩 제한이 있습니다. 이전 버전에서는 7배, 최신 버전에서는 64배입니다. 더 필요하면 어떻게 합니까?

방법 2. 간격 보기가 있는 VLOOKUP

이 방법은 훨씬 더 간결합니다. 할인율을 계산하려면 legend 함수를 사용하십시오. VPR (조회) 대략적인 검색 모드에서:

가장 가까운 숫자 찾기

어디에

B4 – 할인을 찾고 있는 첫 번째 거래의 상품 수량 값
$G$4:$H$8 – 할인 테이블에 대한 링크 – "헤더"가 없고 주소가 $ 기호로 고정되어 있습니다.
2 — 할인 값을 얻으려는 할인 테이블의 열 서수
TRUE – 이것은 "개"가 묻힌 곳입니다. 마지막 함수 인수인 경우 VPR 지정하다 거짓말하는 (거짓) 또는 0, 함수는 다음을 찾습니다. 엄격한 일치 수량 열에 입력합니다(저희의 경우 할인 테이블에 값 49가 없기 때문에 #N/A 오류가 발생합니다). 하지만 만약 대신 거짓말하는 쓰다 TRUE (진실) 또는 1, 함수는 정확한 것을 찾지 않지만 가장 가까운 가장 작은 가치와 우리가 필요로 하는 할인의 비율을 줄 것입니다.

이 방법의 단점은 할인 테이블을 첫 번째 열을 기준으로 오름차순으로 정렬해야 한다는 것입니다. 이러한 정렬이 없으면(또는 역순으로 수행됨) 공식이 작동하지 않습니다.

가장 가까운 숫자 찾기

따라서 이 접근 방식은 가장 가까운 가장 작은 값을 찾는 데만 사용할 수 있습니다. 가장 가까운 가장 큰 것을 찾아야 하는 경우에는 다른 접근 방식을 사용해야 합니다.

방법 3. INDEX 및 MATCH 함수를 사용하여 가장 가까운 최대값 찾기

이제 반대편에서 우리의 문제를 살펴보자. 다양한 용량의 산업용 펌프의 여러 모델을 판매한다고 가정합니다. 왼쪽의 판매 테이블은 고객이 필요로 하는 전력을 보여줍니다. 가장 가까운 최대 또는 동일한 전력의 펌프를 선택해야 하지만 프로젝트에 필요한 것보다 적지는 않습니다.

VLOOKUP 기능은 여기에서 도움이 되지 않으므로 아날로그를 사용해야 합니다. (인덱스) 그리고 더 많이 노출 (성냥):

가장 가까운 숫자 찾기

여기서 마지막 인자가 -1인 MATCH 함수는 가장 가까운 가장 큰 값을 찾는 모드로 동작하고, INDEX 함수는 인접한 열에서 필요한 모델명을 추출한다.

방법 4. 새로운 기능 VIEW(XLOOKUP)

모든 업데이트가 설치된 Office 365 버전이 있는 경우 VLOOKUP 대신 (조회) 당신은 그것의 아날로그를 사용할 수 있습니다 – VIEW 기능 (X룩업), 이미 자세히 분석했습니다.

가장 가까운 숫자 찾기

이리:

B4 – 우리가 할인을 찾고 있는 제품 수량의 초기 값
$G$4:$G$8 – 일치하는 항목을 찾고 있는 범위
$H$4:$H$8 – 할인을 반환하려는 결과 범위
네 번째 인수(-1)에는 정확히 일치하는 대신 원하는 가장 가까운 가장 작은 숫자에 대한 검색이 포함됩니다.

이 방법의 장점은 할인 테이블을 정렬할 필요가 없으며 필요에 따라 가장 가까운 가장 작은 값뿐만 아니라 가장 가까운 가장 큰 값도 검색할 수 있다는 것입니다. 이 경우의 마지막 인수는 1이 됩니다.

그러나 불행히도 아직 모든 사람이 이 기능을 사용하는 것은 아닙니다. Office 365의 행복한 소유자만 해당됩니다.

방법 5. 파워 쿼리

강력하고 완전 무료인 Excel용 파워 쿼리 추가 기능에 아직 익숙하지 않다면 여기 있습니다. 이미 친숙한 경우 이를 사용하여 문제를 해결해 보겠습니다.

먼저 몇 가지 준비 작업을 수행해 보겠습니다.

바로 가기 키를 사용하여 소스 테이블을 동적(스마트)으로 변환해 보겠습니다. Ctrl 키+T 또는 팀 홈 – 테이블 형식 (홈 — 표 형식).
명확성을 위해 이름을 지정하겠습니다. 세일즈 и 할인 탭 건설자 (디자인).
단추를 사용하여 각 테이블을 차례로 파워 쿼리에 로드합니다. 테이블/레인지에서 탭 Data (데이터 — 테이블/범위에서). 최신 버전의 Excel에서는 이 버튼의 이름이 잎으로 (시트에서).
이 예에서와 같이 테이블의 열 이름이 수량과 다른 경우("상품 수량" 및 "...의 수량") 파워 쿼리에서 이름을 바꾸고 이름을 동일하게 지정해야 합니다.
그런 다음 Power Query 편집기 창에서 명령을 선택하여 Excel로 다시 돌아갈 수 있습니다. 홈 — 닫기 및 로드 — 닫기 및 로드… (홈 — 닫기&로드 — 닫기&로드…) 그런 다음 옵션 연결만 만드세요 (연결만 생성).
그런 다음 가장 흥미로운 것이 시작됩니다. 파워 쿼리에 대한 경험이 있는 경우 이전 방법의 경우와 같이 VLOOKUP에서 조인 쿼리(병합)로 이 두 테이블을 병합하는 방향으로 더 생각해야 한다고 가정합니다. 사실, 언뜻 보기에는 전혀 명확하지 않은 추가 모드에서 병합해야 합니다. Excel 탭에서 선택 데이터 – 데이터 가져오기 – 요청 결합 – 추가 (데이터 — 데이터 가져오기 — 쿼리 결합 — 추가) 그리고 우리 테이블 세일즈 и 할인 나타나는 창에서:
클릭 후 OK 우리 테이블은 서로 아래에 하나의 전체로 접착됩니다. 이 표의 상품 수량 열은 서로 아래에 있으므로 주의하십시오. 그들은 같은 이름을 가지고 있습니다:
판매 테이블의 원래 행 순서가 중요한 경우 모든 후속 변환 후에 복원할 수 있도록 다음 명령을 사용하여 테이블에 번호가 지정된 열을 추가하십시오. 열 추가 – 인덱스 열 (열 추가 — 인덱스 열). 행 순서가 중요하지 않은 경우 이 단계를 건너뛸 수 있습니다.
이제 테이블 헤더에 있는 드롭다운 목록을 사용하여 열별로 정렬합니다. 수량 오름차순 :
그리고 주요 트릭: 열 머리글을 마우스 오른쪽 버튼으로 클릭 할인 팀을 선택 채우기 – 아래로 (채우기 — 아래로). 빈 셀 null로 이전 할인 값으로 자동 채워짐:
열을 기준으로 정렬하여 원래의 행 순서를 복원해야 합니다. 색인 (나중에 안전하게 삭제할 수 있음) 필터로 불필요한 줄을 제거하십시오. null로 열별 거래 코드: