두 줄짜리 표 타이틀 한줄로 변경방법( VLOOK UP, MATCH 등)적용법

함수/공식
작성자
마운틴
작성일
2021-12-09 16:09
조회
203
엑셀버전 : 엑셀2019

운영체제 : 윈도우10

안녕하세요?

좌측 처럼 되어 있는 표를 우측 처럼 바꾸고 싶습니다

VLOOK UP이나  MATCH 함수 등을 어떻게 써야할 지 조언 부탁드리옵니다~

(예제 엑셀파일 첨부)

참고로 마트명 과일명은 순서가 바뀐다거나 추가 삭제된다거나

고정되어 있지 않고 변경될 수도 있습니다

회원등급 : 10레벨
포인트 : 704 EP
총질문 : 40 개 (마감율 : 45%)
채택답변 : 0 개
전체 15

  • 2021-12-09 16:15

    원본 자료의 내용은 바뀌더라도 항상 두 줄로 표시 된다는 전제하에

    행번호를 나타내는 row( ) 함수를 쓰면 간단히 해결될 것 같습니다.


  • 2021-12-09 16:28

    첫행 자료의 식

    [F2]=OFFSET(기준점, (ROW()-2)*2, COLUMN()-COLUMN($F:$F))

    두번째 행의 자료에 대하여

    [J2]=OFFSET(기준점, (ROW()-2)*2+1, COLUMN()-COLUMN($I:$I))

    이후 같은색 셀까지 끌어채우기하면 됩니다.

    도움 되기를 바라며 최적해일 때 [답변채택] 클릭하시면 더욱 고맙습니다.

    첨부파일 : 과일문의_해법제안.xlsx


    • 2021-12-09 17:37

      @김학동 님 감사합니다 실제 적용해 보겠습니다


  • 2021-12-09 16:36

     

    이런식도 가능하겠습니다.

    g3= OFFSET($A$3,SUMPRODUCT(($B$2:$D$3=G$2)*(ROW($B$2:$D$3)-1))+MATCH($F3,$A$4:$A$9,0)-1,

    SUMPRODUCT(($B$2:$D$3=G$2)*(COLUMN($B$2:$D$3)-1)))

    우로 아래로 드래그


    • 2021-12-09 17:42

      @bobo 님 감사합니다

      예제파일 G3에 =OFFSET($A$3,SUMPRODUCT(($B$2:$D$3=G$2)*(ROW($B$2:$D$3)-1))+MATCH($F3,$A$4:$A$9,0)-1,SUMPRODUCT(($B$2:$D$3=G$2)*(COLUMN($B$2:$D$3)-1)))를 적용해 보니 과일값이 모두 0이 됩니다 왜 그런지 모르겠네요


      • 2021-12-09 18:13
        채택된 답변

        @마운틴 님 이상없는데요^^

        첨부파일 : offset-sumproduct-row-column-match.xlsx


        • 2021-12-09 19:05

          @bobo 님 앗~문제가 없네요 제가 잘못 입력했나 봅니다^^

          감사합니다


          • 2021-12-09 23:00

            @마운틴 님 bobo님 만들어 주신 함수식을 응용해 실전 적용해  보려하니 어렵네요

            에러가 자꾸나는데 첨부파일 한번 봐주시면 감사하겠습니다

            Sheet1의 자료를 sheet2 자료양식으로 변환시키려고 합니다

            급여1.JPG

            첨부파일 : 급여대장_문의.xls


            • 2021-12-11 10:26

              @마운틴 님 각 함수의 인수별로 이해해보세요

               

              첨부파일 : 급여대장_문의.xlsx


              • 2021-12-11 14:34

                @bobo 님 bobo님

                잘 적용이 되네요

                대단히 감사합니다

                그런데 내용이 어려워서 앞으로 공부를 많이 해야할 듯합니다

                감사합니다


              • 2021-12-11 21:22

                @bobo 님 bobo님

                지급합계 차인지급액까지 다 추출이 되도록 sheet1의 범위지정을

                2줄->3줄이 되게 sheet1!$C$6:$O$7---> sheet1!$C$6:$O$8으로

                함수를 변경했는데 지급합계 차인지급액이 추출이 안되네요

                죄송하지만 어떤 부분을 추가수정해야 할 지 다시 한번 여쭙숩니다


                • 2021-12-11 22:35

                  @마운틴 님 항목명 확인해보세요

                  지급액?

                  누락된 항목 추가해야 지급액이 맞을 것 같습니다


                  • 2021-12-12 00:18

                    @bobo 님 지급합계와 차인지급액은 위 sheet1 사진 노란색 부분 3번째 줄에서 볼 수 있는 것 처럼 이미 행타이틀에 항목이 있고 그 아래에는 계산된 결과값이 이미 제시도어 있어서 추출만 하면 됩니다 (추출값을 모으는 Ssheet2에 지급합계와 차인지급액은 별도 추가 예정입니다)

                    다만 범위지정이 기존에 2줄이었는데 지급합계와 차인지급액 행타이틀이 포함되도록 위 사진 노란색처럼 3줄로 범위를 지정하는 방법을 몰라서요

                     


                    • 2021-12-12 00:27

                      @마운틴 님 행번호 7을 8로 수정하고

                      열범위도 확인해보세요


                      • 2021-12-12 19:27

                        @bobo 님 말씀해 주신 대로 다시 해보았더니 잘 적용이 됩니다

                        대단히 감사합니다


전체 5,132
번호 카테고리 제목 작성자 작성일 추천 조회
알림
하루 딱 "1분", 진짜 쓰는 엑셀 꿀팁 👉 "오빠두엑셀" 인스타그램 오픈! (28)
트로피 오빠두엑셀 | 2021.10.12 | 추천 22 | 조회 752
트로피 오빠두엑셀 2021.10.12 22 752
공지사항 공지사항
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (21)
오빠두엑셀 | 2021.10.28 | 추천 28 | 조회 2341
트로피 오빠두엑셀 2021.10.28 28 2341
26058 기능/도구
New 자동필터 복사 범위 풀림 오류
허영진 | 10:29 | 추천 0 | 조회 3
허영진 10:29 0 3
26050 함수/공식
New 행과 열의 합의 함수를 알고 싶어요! (1)
Goldhajin | 00:45 | 추천 0 | 조회 32
Goldhajin 00:45 0 32
26041 VBA
New 일정시간이 지나면 MsgBOX로 알림창이 뜨게 하고싶습니다. (2)
을지지뢰 | 2022.01.17 | 추천 0 | 조회 36
을지지뢰 2022.01.17 0 36
26035 함수/공식
New 특정날짜와 시간 고정해서 데이터 추출방법 도와주세요 (4)
박현욱 | 2022.01.17 | 추천 0 | 조회 38
박현욱 2022.01.17 0 38
26033 함수/공식
New 필터 함수를 인덱스 매치 함수로 변환하는 어려움 첨부파일 (1)
미르 | 2022.01.17 | 추천 0 | 조회 25
미르 2022.01.17 0 25
26030 함수/공식
New 원본표에서 일정 조건의 내용을 추출하여 새로운 표에 자료 넣기 첨부파일 (2)
알면서 | 2022.01.17 | 추천 0 | 조회 22
알면서 2022.01.17 0 22
26029 기능/도구
New 필터에서 상하위 % 추출 안됨 오류 첨부파일 (2)
알면서 | 2022.01.17 | 추천 0 | 조회 17
알면서 2022.01.17 0 17
26026 함수/공식
New 한 셀에 입력된 문장 중 조건에 만족하는 문장 개수 함수 (4)
삐약이 | 2022.01.17 | 추천 0 | 조회 27
삐약이 2022.01.17 0 27
26025 기능/도구
New 셀 내용을 지우면 특정 셀 내용도 같이 삭제되는 기능? 첨부파일 (2) 답변완료
올리주인 | 2022.01.17 | 추천 1 | 조회 33
올리주인 2022.01.17 1 33
26023 VBA
New VBA 사용자정의폼 show 0 명령어 (1)
병욱 | 2022.01.17 | 추천 0 | 조회 29
병욱 2022.01.17 0 29
26022 VBA
New 거래처별 파일 나누기 첨부파일
silli**** | 2022.01.17 | 추천 0 | 조회 33
silli**** 2022.01.17 0 33
26015 함수/공식
New 혹시 Crtl + F 찾기 같은 함수와 있나요 (1)
태혁 | 2022.01.17 | 추천 0 | 조회 49
태혁 2022.01.17 0 49
26014 차트/그래프
New 해당 스크린샷의 그래프를 어떻게 만드는지 알려주세요 (1)
민석 | 2022.01.17 | 추천 0 | 조회 23
민석 2022.01.17 0 23
26005 함수/공식
New 한 숫자값에 대한, 홀수/짝수값을 나열하고 싶습니다. (6) 답변완료
땅콩구리아빠 | 2022.01.17 | 추천 0 | 조회 36
땅콩구리아빠 2022.01.17 0 36
26000 VBA
New Rng.find 함수로 찾은 값을 DB안에 넣고싶습니다. (2)
가을전어 | 2022.01.16 | 추천 0 | 조회 33
가을전어 2022.01.16 0 33
25994 함수/공식
New 날짜 입력하면 당일 주야간 합구하는 함수나 방법을 배우러 왔습니다. (1)
현주아빠 | 2022.01.16 | 추천 0 | 조회 32
현주아빠 2022.01.16 0 32
25993 VBA
New workday 함수를 vba로.. (1)
Hong | 2022.01.16 | 추천 0 | 조회 30
Hong 2022.01.16 0 30
25989 차트/그래프
New 표 자동 입력 첨부파일 (2) 답변완료
미르 | 2022.01.16 | 추천 0 | 조회 43
미르 2022.01.16 0 43
25983 함수/공식
New 년도에 따른 출장 일수 구하고 싶은데 도움 부탁드립니다. (1)
kjin1020@naver.com | 2022.01.16 | 추천 0 | 조회 25
kjin1020@naver.com 2022.01.16 0 25
25982 함수/공식
New 입력하면 옆 셀에 자동으로 '1'이 입력되는 방법 (1) 답변완료
올리주인 | 2022.01.16 | 추천 0 | 조회 52
올리주인 2022.01.16 0 52
25973 함수/공식
New index match로 불러온 여러 목록의 사진을 다시 취합해서 불러오고 싶습니다. 첨부파일
배고픈콩콩 | 2022.01.16 | 추천 0 | 조회 38
배고픈콩콩 2022.01.16 0 38
25969 VBA
New 검색한 셀의 가로열 전부를 복사하는 방법이 알고 싶습니다. vba
ㅈㄱㄹ | 2022.01.16 | 추천 0 | 조회 25
ㅈㄱㄹ 2022.01.16 0 25
25945 함수/공식
New if 함수 중첩 (3)
shheee | 2022.01.15 | 추천 0 | 조회 55
shheee 2022.01.15 0 55
25938 기능/도구
New 수식이 포함된 시트에서 수식이 입력된 셀을 제외한 셀을 선택하는 방법을 알고자 합니다 (1)
yya**** | 2022.01.15 | 추천 0 | 조회 36
yya**** 2022.01.15 0 36
25932 문서서식
안녕하세요...도움을 요청합니다. 첨부파일 (1)
violet**** | 2022.01.15 | 추천 0 | 조회 51
violet**** 2022.01.15 0 51
25919 함수/공식
중복일 경우 1개만 카운트 첨부파일 (3) 답변완료
s77**** | 2022.01.14 | 추천 0 | 조회 85
s77**** 2022.01.14 0 85
25918 함수/공식
대출 이자율을 구하는 함수가 있을까요? (3)
늦깎이 | 2022.01.14 | 추천 0 | 조회 61
늦깎이 2022.01.14 0 61
25909 문서서식
셀값에 보이지 않는 값이 있습니다 이게 뭔지 알려주세요ㅠㅠ 첨부파일 (2) 답변완료
닉으로 | 2022.01.14 | 추천 0 | 조회 64
닉으로 2022.01.14 0 64
25908 함수/공식
혹시 매주 월요일마다 자동 작성되게 할 수 있나요? (5)
태혁 | 2022.01.14 | 추천 0 | 조회 90
태혁 2022.01.14 0 90
25905 대시보드
데이터 관리 때문에 질문 드립니다. 첨부파일 (4)
콜럼버스 | 2022.01.14 | 추천 0 | 조회 49
콜럼버스 2022.01.14 0 49