함수 사용 시 특정 셀 텍스트 추출 하는 경우

함수/공식
작성자
구찌갱
작성일
2022-10-05 13:10
조회
161
엑셀버전 : 엑셀2016

운영체제 : 윈도우10

질문 요약 : 1.함수 사용 시 특정 셀 텍스트 추출 하는 경우
2.행과 열 번호를 사용하여 셀 범위를 추출하는 함수

안녕하세요. 오늘도 질문 드리네요.. 실력 향상을 위해 최대한 이것저것 해보다가 또 다시 예시 시트를 작성하여 찾아옵니다.

엑셀 함수 적용 시 해당 셀을 클릭하면, 함수의 셀 주소가 나오지 않습니까.

셀 주소 대신 클릭된 셀의 텍스트를 반환하여 계산 함수에 적용하고자 합니다.

텍스트를 반환하려고 CELL함수 → "conetents"를 써보기도 했으나 수식에 문제가 있다고 합니다.

※ F열의 수식은 =ADDRESS(ROW(G9),COLUMN(G9),3)입니다.

Address를 이용하여 셀의 주소를 추출한 뒤 추출된 텍스트를 함수에 적용할 때엔 어떤 방법을 사용해야 하나요??

또한 Address함수가 아니더라도 해당 셀의 행과 열의 번호를 사용하여 셀 범위를 추출하는 함수는 없을까요?

(실무에서 데이터가 많아서, 클릭하지 않고 함수로 반환하고자 합니다, 이후에는 VBA까지 작업을 하려고 해서,, 최대한 드래그를 사용하지 않으려고 합니다.)

늘 감사드립니다.

스크랩
공유
전체 6

  • 2022-10-05 13:13

    @구찌갱 님 =INDIRECT(ADDRESS(~~~))

    범위도 똑같이 INDIRECT안에 넣으면 됩니다. 텍스트 형식으로 넣을 수 있습니다.

    =INDIRECT("Sheet1!"&"$A$1"&":"$B$30")

    텍스트이므로 마음대로 쪼개고 따로 계산해서 &로 합쳐주기만하면 되고,

    시트 이름은 넣어도 되고 생략도 되고,

    행번호 숫자로 계산해서 넣어도

    열번호가 숫자면 address로 넣으면 되고

    다른 셀에 시트이름, 행, 열 번호를 넣어놓고 참조로 넣어도 되고

    참조하고 있는 셀이나 시트가 사라져도 수식이 REF로 바뀌지 않고 유지됩니다


    • 2022-10-05 14:55

      @춘식 님 안녕하세요 선생님, 빠른 답변 감사드려요.

      덕분에 Indirect 함수 강의 듣고 적용하였습니다.

      범위를 텍스트로 출력까지 하였습니다.

      다만 MIN(INDIRET~~)적용을 못하겠네요.

      MIN함수 내에 또 다른 INDIRECT 함수 적용이 안되는 건가요?


      • 2022-10-06 09:01

        @구찌갱 님 저렇게 쓰려면 앞 뒤 주소를 한번 더 indirect 안에 넣어서 묶어야 할거 같은데요.. 텍스트형이랑 주소는 다른겁니다.

        =F13 으로 입력하면 F13에 있는 내용이 나오지만 (주소)

        ="F13" 이렇게 입력하면 그냥 F13이라는 글자가 나오겠죠 (텍스트)

        지금 하신건 MIN함수에 아래쪽 텍스트로 넣으신겁니다.


      • 2022-10-06 09:09
        채택된 답변

        @구찌갱 님 이렇게요. INDIRECT 안에만 주소가 텍스트로 들어갈 수 있고, 나머지 함수에 들어가는 주소는 주소형태여야 합니다.

        INDIRECT에 텍스트형태로 주소를 넣으면, INDIRECT가 그걸 주소 그 자체로 변환해주고 그걸 다른 함수에서 쓸 수 있다고 생각하면 됩니다.

         

        예제처럼 대상이 셀 1개가 아니라 셀 2개 이상의 범위면 =INDIRECT만 쓴 상태에서는 VALUE 에러가 날텐데,

        이건 셀 1개에 여러 셀을 표현해달라고 하는거라 당연히 생기는 에러이므로 문제 없습니다.

        그걸 통째로 다른 함수에 넣으면 범위로 정상 작동합니다.

         

        +

        2중으로 쓰인 INDIRECT를 제거하고, 그냥 $G6, $G14가 쓰여있는 셀을 넣는다면

        =MIN(INDIRECT(F13&":"&F14))

        이렇게만 하셔도 -4가 나오고

        그거조차 안 쓴다면

        =MIN(INDIRECT(ADDRESS(ROW(G6),COLUMN(G6),3)&":"&ADDRESS(ROW(G14),COLUMN(G14),3)))

        이렇게해도 -4가 나옵니다.

        이렇게 수정하면 셀 1개 내에서 행 열을 계산해서 고정이 아닌 유동적으로 집어넣을 수 있죠

        2022-10-06-090528.PNG


      • 2022-10-06 09:17

        @구찌갱 님 INDIRECT만 쓴 상태에서는 VALUE 에러가 나는걸 좀 더 파고들면

        일반 함수가 아닌 배열함수로 처리하면 그 상태에서도 VALUE에러가 안나게 할 수 있습니다.

        =INDIRECT(ADDRESS(ROW(G6),COLUMN(G6),3)&":"&ADDRESS(ROW(G14),COLUMN(G14),3))

        수식을 다 쓴뒤에 ENTER로 완료하지말고, CTRL+SHIFT+ENTER로 완료시키면 수식 앞 뒤에 {}가 붙는데

        이렇게 하면 VALUE 에러가 아닌 범위의 첫번째 값인 1이 나오고

        그 셀 오른쪽 아래 모서리 점을 잡고 아래로 끌면 그 범위에 있는 내용들 1,2,3,-3,-4....가 순서대로 나옵니다. 범위가 끝나서 출력할 값이 없으면 0이 나오죠

        이 방법으로 INDIRECT가 1개셀에 대해서만 동작하는게 아니라 제대로 여러셀을 나타내는 범위주소도 반환하고 있다는 걸 알 수 있습니다.


        • 2022-10-06 09:40

          @춘식 님 아.. 상세한 설명 대단히 감사드려요.

          실수를 바로잡아주시면서 개념 설명까지 말씀해주시니 하나하나 모두 읽어보겠습니다. 덕분에 잘 이해가 갑니다.

          좋은 하루 보내시길 빌어요.

          감사드립니다.


전체 8,690
번호 카테고리 제목 작성자 작성일 추천 조회
알림
🎉 오피스 분야 30주 연속 1위! - 「 진짜쓰는 실무엑셀 」 전자책이 출간되었습니다! (37)
오빠두엑셀 | 2022.09.28 | 추천 9 | 조회 3292
오빠두엑셀 2022.09.28 9 3292
공지사항 공지사항
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (110)
오빠두엑셀 | 2021.10.28 | 추천 137 | 조회 8821
오빠두엑셀 2021.10.28 137 8821
45457 함수/공식
New 두 셀 문자를 한셀로 가져오면서 줄바꿈과 괄호가 삽입되게 할 수 있을까요? 엑셀파일첨부파일 (2) 답변완료
초코우유 | 2022.12.09 | 추천 0 | 조회 31
초코우유 2022.12.09 - 31
45456 피벗테이블
New 피벗테이블 설문조사 문항별로 모든 사람의 5점척도 응답값들을 가져오고 싶습니다. 엑셀파일 (2) 답변완료
dissim**** | 2022.12.09 | 추천 0 | 조회 17
dissim**** 2022.12.09 - 17
45452 함수/공식
New 자동으로 조건별 정렬이 되도록 보고싶어요 엑셀파일첨부파일 (6)
짱구는옷말려 | 2022.12.09 | 추천 0 | 조회 41
짱구는옷말려 2022.12.09 - 41
45451
New 매크로 파일 버튼이 작동을 안합니다. 첨부파일 (1)
강홍구 | 2022.12.09 | 추천 0 | 조회 26
강홍구 2022.12.09 - 26
45449 VBA
New 엑셀(EXCEL) Worksheet_SelectionChange VBA관련 엑셀파일첨부파일
woooooo.m | 2022.12.09 | 추천 0 | 조회 32
woooooo.m 2022.12.09 - 32
45448 VBA
New VBA 에서 Call Shell로 링크를 끌어오려하는데 현재 Month -1 에 해당하는 데이터를 불러오고 싶어요
Taee | 2022.12.09 | 추천 0 | 조회 20
Taee 2022.12.09 - 20
45447 VBA
New 값을 찾아올때, 음영색깔도 그대로 가져오기 엑셀파일 (1)
RED HERO | 2022.12.09 | 추천 0 | 조회 34
RED HERO 2022.12.09 - 34
45446 함수/공식
New 2개의 조건과 일치하는 대상자들의 중앙값구하기 엑셀파일 (2) 답변완료
willow**** | 2022.12.09 | 추천 0 | 조회 28
willow**** 2022.12.09 - 28
45445 함수/공식
New QR코드함수 관련 한글깨짐 현상 엑셀파일첨부파일 (2)
쿠키바삭 | 2022.12.09 | 추천 0 | 조회 21
쿠키바삭 2022.12.09 - 21
45437 함수/공식
New 특정단어 입력시 시트2에 표기법 엑셀파일 (3)
자유 | 2022.12.09 | 추천 0 | 조회 34
자유 2022.12.09 - 34
45436 함수/공식
New 조건에 따른 평가점수의 중앙값을 구하는 방법을 알고싶어요 엑셀파일 (2) 답변완료
willow**** | 2022.12.09 | 추천 1 | 조회 19
willow**** 2022.12.09 1 19
45435 함수/공식
New 엑셀 속도 관련 질문입니다. (용량이 큰 엑셀파일을 어떻게 관리해야할까요?) (2)
정지훈 | 2022.12.09 | 추천 0 | 조회 29
정지훈 2022.12.09 - 29
45433 기능/도구
New 체크박스와 필터링 문제 엑셀파일첨부파일 (1)
꽃받침 | 2022.12.09 | 추천 0 | 조회 29
꽃받침 2022.12.09 - 29
45432 함수/공식
New 월별 매출액 자동 합산 엑셀파일 (3) 답변완료
더데빌 | 2022.12.09 | 추천 0 | 조회 27
더데빌 2022.12.09 - 27
45429 파워쿼리/피벗
New 네이버 메일 열기 첨부파일 (2)
옥산이 | 2022.12.09 | 추천 0 | 조회 23
옥산이 2022.12.09 - 23
45422 함수/공식
New SUMPRODUCT 행열 조건식 덧셈 사용시 #VALUE 문제 엑셀파일 (4) 답변완료
김동연 | 2022.12.09 | 추천 0 | 조회 23
김동연 2022.12.09 - 23
45421 함수/공식
New 공백제외하고 셀의 내용을 합치는 함수 알고싶습니다.(두줄당하나로) 첨부파일 (3)
고독한술한잔 | 2022.12.09 | 추천 0 | 조회 28
고독한술한잔 2022.12.09 - 28
45418 함수/공식
New 일자별로 합계구하고싶어요 엑셀파일첨부파일 (2)
엑셀잘할래요 | 2022.12.09 | 추천 0 | 조회 33
엑셀잘할래요 2022.12.09 - 33
45416 함수/공식
New sumifs와 sumproduct 사용법 질문입니다. 첨부파일
PQDL | 2022.12.09 | 추천 0 | 조회 22
PQDL 2022.12.09 - 22
45413 VBA
New 매크로 기능을 사용하면 매크로 파일(XLSM)이 갑자기 켜집니다 (1)
바네 | 2022.12.09 | 추천 0 | 조회 28
바네 2022.12.09 - 28
45412 함수/공식
New 절대값 음수 시간 합계 표시 질문입니다. 엑셀파일첨부파일 (7)
힙합매냐 | 2022.12.09 | 추천 0 | 조회 32
힙합매냐 2022.12.09 - 32
45410 함수/공식
New indirect 함수 상대참조 엑셀파일첨부파일 (1)
마마마 | 2022.12.08 | 추천 0 | 조회 27
마마마 2022.12.08 - 27
45409 함수/공식
New 다중조건 첨부파일 (1)
김미영 | 2022.12.08 | 추천 0 | 조회 30
김미영 2022.12.08 - 30
45399 함수/공식
New 제시간에 퇴근하고싶어요 엑셀파일 (2)
taegshin kim | 2022.12.08 | 추천 0 | 조회 63
taegshin kim 2022.12.08 - 63
45398 기능/도구
New 목록명이 다른 데이터 통합방법 첨부파일 (2)
will12**** | 2022.12.08 | 추천 0 | 조회 33
will12**** 2022.12.08 - 33
45397 함수/공식
New 산출식이 들어 있는 값으로 표시 엑셀파일첨부파일 (2)
바른나무 | 2022.12.08 | 추천 0 | 조회 31
바른나무 2022.12.08 - 31
45394 함수/공식
New 여러수식의 범위값을 연동해서 변경하는 방법 있을까요 (4) 답변완료
하이롭 | 2022.12.08 | 추천 0 | 조회 37
하이롭 2022.12.08 - 37
45390 함수/공식
New 조건에 맞는 값 구하기 첨부파일
노갈 | 2022.12.08 | 추천 0 | 조회 45
노갈 2022.12.08 - 45
45389 문서서식
New 기존에 인쇄 되어있는 용지를 넣고 출력할때 원하는 위치에 글씨 입력하는 방법 (1)
willow123 | 2022.12.08 | 추천 0 | 조회 21
willow123 2022.12.08 - 21
45388 함수/공식
New 여러가지 조건 함수 첨부파일 (7)
지니 | 2022.12.08 | 추천 0 | 조회 40
지니 2022.12.08 - 40