함수 조언구합니다.

함수/공식
작성자
엑셀공부중
작성일
2022-09-14 21:54
조회
68
엑셀버전 : 엑셀2016

운영체제 : 윈도우10

오른쪽 노란색 색칠된 값에 아이템 값을 불러오고싶습니다. 조건3개가 충족해야합니다.

 

스크랩
공유
전체 13

  • 2022-09-15 11:02
    채택된 답변

    @엑셀공부중 님 데이터 열이 오름차순 정리가 안된 상태에서

    해당 값 보다 큰 조건을 만족하는 첫번째 행의 값을 찾는 일반적인 INDEX MATCH SMALL이 아니라

    해당값보다 크지만 제일 작은 값을 찾는 것으로 보입니다

    그 경우 시설이 나눠져있으면 비교적 간단하게 풀 수 있습니다.

    {=INDEX($C$4:$C$99,MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(4,MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS(99,MATCH(L3,$D$3:$F$3,0)+3,1,1)))>H3,INDIRECT(CONCATENATE(ADDRESS(4,MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS(99,MATCH(L3,$D$3:$F$3,0)+3,1,1),"")))),INDIRECT(CONCATENATE(ADDRESS(4,MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS(99,MATCH(L3,$D$3:$F$3,0)+3,1,1))),0),1)}

    예제처럼 시설은 정리가 되어있다면, 모든 4와 99를 해당 시설의 행번호 시작과 끝을 보조열로 넣고해당 셀의 주소로 바꾸면 동작합니다

    시설까지 수식에 넣으려면, 배열을 넣기전에 시설이 안맞을 경우 0으로 바꿔버려서 넣으면 될거 같은데 그 부분은 시간이 없어서 다음 분께 넘깁니다.

     

    2022-09-15-105716.PNG


    • 2022-09-15 12:28

      @춘식 님 너무 복잡하군요..

      ㅜ 일단 감사합니다.


      • 2022-09-15 12:56

        @엑셀공부중 님 예제 파일 작성해보았습니다.

        첨부는 보조열을 썼지만,

        J3, K3은 단순히 MATCH와 COUNTIF이기 때문에 아래와 같이 보조열 없이 한 셀에서 처리 할 수도 있습니다.

        =IFERROR(INDEX(INDIRECT(CONCATENATE(ADDRESS(MATCH(I3,B:B,0),3,1,1),":",ADDRESS((MATCH(I3,B:B,0)+COUNTIF(B:B,I3)-1),3,1,1))),MATCH(MIN(IF(INDIRECT(CONCATENATE(ADDRESS(MATCH(I3,B:B,0),MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS((MATCH(I3,B:B,0)+COUNTIF(B:B,I3)-1),MATCH(L3,$D$3:$F$3,0)+3,1,1)))>H3,INDIRECT(CONCATENATE(ADDRESS(MATCH(I3,B:B,0),MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS((MATCH(I3,B:B,0)+COUNTIF(B:B,I3)-1),MATCH(L3,$D$3:$F$3,0)+3,1,1),"")))),INDIRECT(CONCATENATE(ADDRESS(MATCH(I3,B:B,0),MATCH(L3,$D$3:$F$3,0)+3,1,1),":",ADDRESS((MATCH(I3,B:B,0)+COUNTIF(B:B,I3)-1),MATCH(L3,$D$3:$F$3,0)+3,1,1))),0),1),"없음")

         

         

        2022-09-15-125505.PNG

        첨부파일 : 도움요청-2.xlsx


        • 2022-09-15 13:14

          @춘식 님 offset으로는 풀수없나요?? 감사합니다.


  • 2022-09-14 21:56

    직접 만들다가 머리 터져버릴거같아서 조언요청드립니다. SMALL과 INDEX MATCH활용도 해봤고

    OFFSET MATCH해서 동적값가져와서 SAMLL입혀보기도 했지만.. 잘 안되네요


  • 2022-09-14 22:53

    @엑셀공부중 님 이런식도 가능하겠습니다.

    배열 ctrl+shift+enter  아래로 드래그

    j3셀

    = IFERROR(INDEX($C$4:$C$11,SMALL(IF(($B$4:$B$11=I3)*(OFFSET($C$4:$C$11,0,MATCH(K3,$D$3:$F$3,0))>H3),ROW($C$4:$C$11)-3),1)),"없음")


    • 2022-09-15 09:14

      @bobo 님 무슨이유인지 안먹히네용..

       


    • 2022-09-15 09:23

      @bobo 님 이건 row때문에(1,2,3,4나열) 숫자중에 small값 첫번째로 작은값을 가져오는데,, 이게아니라 실제 값중에 작은 값을 가져와야합니다ㅠ


      • 2022-09-15 09:34

        @엑셀공부중 님 row는 범위에서 조건위치 번호를 반환하기 위함입니다. 실제값은 index함수의 1인수에서 불러옵니다.

        ctrl+shift+enter하게 되면 수식의 맨앞과 뒤에 { }가 자동 생성됩니다. 순서대로 눌러주는 것이 아니고 ctrl과 shift를 누르고 있는 상태에서 enter하시면 됩니다.

        테스트 하고 댓글 올린겁니다.


        • 2022-09-15 10:03

          @bobo 님 이거 한번 다시 부탁드려도될까용 버전은 상위버전꺼로 하고있어서 컨쉽엔터 안해도됩니당ㅎㅎ 감사합니다.

          첨부파일 : 도움요청.xlsx


          • 2022-09-15 10:15

            @엑셀공부중 님 수식에서 범위를 수정하시면 될 것 같습니다.


            • 2022-09-15 11:28

              @bobo 님 범위 수정해도 안먹힙니다..ㅠ이게 아래 춘식님 말이 맞습니다.. 제가 질문을 디테일하게 못했네요 죄송합니다.

               

              조언 주셔서 감사합니다!


  • 2022-09-21 15:18

    @엑셀공부중 님 너무 복잡해서 한다고 해봤는데도 아직 제 실력으로는 따라가질 못하겠어요... 더 노력 하겠습니다.


전체 7,795
번호 카테고리 제목 작성자 작성일 추천 조회
알림
🎉다양한 이벤트와 함께 진행하는, PPT 보고서 디자인 특강 안내 - 9/24 (토) 오후 9시~ (15)
오빠두엑셀 | 2022.09.08 | 추천 9 | 조회 2402
오빠두엑셀 2022.09.08 9 2402
공지사항 함수/공식
[신규 기능 업데이트!] 👉 이제 게시글 작성시 스크린샷 복/붙이 가능합니다! 😎 (3)
오빠두엑셀 | 2022.08.04 | 추천 4 | 조회 1655
오빠두엑셀 2022.08.04 4 1655
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (95)
오빠두엑셀 | 2021.10.28 | 추천 124 | 조회 7402
오빠두엑셀 2021.10.28 124 7402
42561 함수/공식
New 날짜 검색 수식 관련 질문
마루모토 | 18:09 | 추천 0 | 조회 4
마루모토 18:09 - 4
42559 기능/도구
New 행x열 표를 한 열로 쉽게 바꾸는 방법 문의 엑셀파일첨부파일 (1)
mac**** | 17:31 | 추천 0 | 조회 12
mac**** 17:31 - 12
42558 VBA
New 매크로로 특정셀이 공란일 때 특정셀 내용을 다 지우는 방법 (1)
디엠 | 17:09 | 추천 0 | 조회 13
디엠 17:09 - 13
42555 문서서식
New A1 A3 A4 값을 열로 자동채우기 (7) 답변완료
chri**** | 16:13 | 추천 0 | 조회 24
chri**** 16:13 - 24
42554 차트/그래프
New 피벗차트의 제목 연결 만들기 첨부파일 (1)
하늘보기119 | 16:06 | 추천 0 | 조회 19
하늘보기119 16:06 - 19
42552 함수/공식
New 날짜 검색 조건 질문 (3) 답변완료
마루모토 | 15:56 | 추천 0 | 조회 18
마루모토 15:56 - 18
42551 함수/공식
New 데이타 유효성 검사문제 엑셀파일 (2)
redi**** | 14:57 | 추천 0 | 조회 22
redi**** 14:57 - 22
42549 VBA
New VBA 뉴스기사 크롤링 관련 질문입니다. 첨부파일 (1)
jjang**** | 14:49 | 추천 0 | 조회 22
jjang**** 14:49 - 22
42547 VBA
New 복사붙여넣기 매크로 (도와주세요 ㅠㅠㅠ) (재질문) 엑셀파일첨부파일
wldud**** | 14:40 | 추천 0 | 조회 22
wldud**** 14:40 - 22
42542 함수/공식
New 엑셀 VLookup 함수에서 두 곳 중 한 곳에만 결과값이 안나오는 이유가 무엇일까요? 엑셀파일 (7)
김기태 | 12:21 | 추천 0 | 조회 42
김기태 12:21 - 42
42540 피벗테이블
New 피벗테이블 특정항목만 X2, X3 자동 곱하기 되게 엑셀파일첨부파일
알럽고구마 | 12:18 | 추천 1 | 조회 22
알럽고구마 12:18 1 22
42533 함수/공식
New 숫자 추출 후 더하기 및 곱하기 엑셀파일첨부파일 (3) 답변완료
하나포 | 10:55 | 추천 0 | 조회 24
하나포 10:55 - 24
42531 문서서식
New data 값이 없을 때는 어떤 값을 입력하면 괜찮은가요? (1)
twou**** | 10:38 | 추천 0 | 조회 27
twou**** 10:38 - 27
42526 VBA
New 복사붙여넣기 매크로 (도와주세요 ㅠㅠㅠ) 엑셀파일첨부파일
wldud**** | 09:43 | 추천 0 | 조회 44
wldud**** 09:43 - 44
42523 기능/도구
New 엑셀에 카메라 기능이 안됩니다. 첨부파일 (4)
무럭무럭예찬이 | 08:42 | 추천 0 | 조회 39
무럭무럭예찬이 08:42 - 39
42516 함수/공식
New 날짜에 대한 IF함수 엑셀파일 (2)
sky3**** | 2022.09.27 | 추천 0 | 조회 47
sky3**** 2022.09.27 - 47
42515 함수/공식
New 집계현황에 각시트의 해당되는 값을 찾는 함수가 필요합니다. 엑셀파일 (2)
낮은자 | 2022.09.27 | 추천 0 | 조회 29
낮은자 2022.09.27 - 29
42514 함수/공식
New 작업순서 변경 시 하나 바꿀 때 나머지 작업순서도 연동되어 바꾸는 방법 (생산관리, 생산계획) 첨부파일 (2)
스키마타 | 2022.09.27 | 추천 0 | 조회 34
스키마타 2022.09.27 - 34
42509 VBA
New MsoScroll 지우는 방법. 첨부파일 (2)
033cola | 2022.09.27 | 추천 0 | 조회 36
033cola 2022.09.27 - 36
42507
New subtotal + 필터적용시 오류 문의 입니다. 엑셀파일 (2)
1003po**** | 2022.09.27 | 추천 0 | 조회 39
1003po**** 2022.09.27 - 39
42503 파워쿼리/피벗
New 피벗테이블 / 파워커리 행 늘리는 방법 문의 엑셀파일첨부파일 (1)
주태훈 | 2022.09.27 | 추천 0 | 조회 41
주태훈 2022.09.27 - 41
42501 파워쿼리/피벗
New 파워피벗 열조건 비율 계산 엑셀파일첨부파일 (1)
bstm**** | 2022.09.27 | 추천 0 | 조회 43
bstm**** 2022.09.27 - 43
42495 함수/공식
New 문제관련 (7)
제두진 | 2022.09.27 | 추천 0 | 조회 45
제두진 2022.09.27 - 45
42488 VBA
New 엑셀 고급필터 자동화 질문드립니다. (부탁드립니다 ㅠㅠ) (재질문) 엑셀파일 (2) 답변완료
wldud**** | 2022.09.27 | 추천 0 | 조회 57
wldud**** 2022.09.27 - 57
42485 함수/공식
New 여기 엑셀 고급 11강 - FILTER에 대해서 엑셀파일 (5) 답변완료
구름나그네 | 2022.09.27 | 추천 0 | 조회 48
구름나그네 2022.09.27 - 48
42482 문서서식
New 중복값 표시하기가 이해가 안됩니다. 할 수 있는 방법 좀 알려주세요 첨부파일 (1)
엑셀마스터가되고싶다 | 2022.09.26 | 추천 0 | 조회 38
엑셀마스터가되고싶다 2022.09.26 - 38
42478 VBA
New 특정한 값이 있는 행 추출(작성된 VBA 수정) 엑셀파일첨부파일 (1)
007 | 2022.09.26 | 추천 0 | 조회 49
007 2022.09.26 - 49
42476 파워쿼리/피벗
New 여러개의 외부 데이터에서 특정 값 기준으로 계산한 결과값을 보여주는 쿼리 엑셀파일 (5)
로보트태권브이 | 2022.09.26 | 추천 0 | 조회 34
로보트태권브이 2022.09.26 - 34
42468 피벗테이블
New 피벗테이블 데이터 갱신 오류 엑셀파일첨부파일 (1)
퓨리시아 | 2022.09.26 | 추천 0 | 조회 33
퓨리시아 2022.09.26 - 33
42466 VBA
New 검색어 입력시 영어 소문자... 첨부파일 (3)
조지아 칸타타 | 2022.09.26 | 추천 0 | 조회 35
조지아 칸타타 2022.09.26 - 35