네이버

엑셀 RANDARRAY 함수는 엑셀 2021 버전과 엑셀 365 버전부터 제공되는 동적배열함수입니다. 엑셀 RANDARRAY 함수는 엑셀 2021 버전 이전의 엑셀 버전부터 제공되는 RAND 함수와 RANDBETWEEN 함수에 비해 더 많은 옵션과 기능을 제공하는 최신 버전의 난수 함수라고 할 수 있습니다. 엑셀 RANDARRAY 함수는 동적배열 함수이기 때문에 1개의 식에만 함수식을 입력하면 여러 개의 셀에 여러 개의 결과를 얻을 수 있다는 장점이 있습니다. 이 함수와 유사한 동적배열함수로 SEQUENCE 함수가 있지요. RANDARRY 함수는 규칙성이 없는 여러 개의 난수를 구할 때 사용하고 SEQUENCE 함수는 규칙성이 있는 여러 개의 숫자를 구할 때 사용합니다. 한 번에 여러 개의 숫자를 구할 수 있는 동적배열함수라는 점에서 두 개 함수가 유사하나 함수가 돌려주는 숫자들이 규칙성이 있어야 할 때는 SEQUENCE 함수를 사용하고 규칙성이 없어야 할 때는 RANDARRAY 함수를 사용합니다. RANDARRY 함수는 소수나 정수를 반환하기 때문에 이 함수를 사용해서 여러 개의 날짜나 시간을 구할 수도 있습니다. 엑셀 내부에서 날짜는 1 이상의 정수이며, 시간은 0에서 1 미만의 소수로 표현되기 때문에 일련의 규칙성 있는 또는 규칙성 없는 여러 개의 날짜나 시간을 구할 때도 RANDARRAY 함수를 사용할 수 있지요. 이 강의에서...

엑셀 SEQUENCE 함수는 엑셀 2021 버전과 엑셀 365 버전부터 제공되는 동적 배열 함수(Dynamic Array Function)입니다. 동적 배열 함수는 동적 배열식을 처리하는 기능을 포함하고 있어서 이전 버전의 엑셀에서 중괄호 안에 기술해야 했던 식을 중괄호 없이 그냥 사용할 수 있지요. 사용자 입장에서는 1개의 셀에만 식을 입력하면 여러 개의 셀에 여러 개의 값을 한 번에 돌려주는 식이라고 간단히 생각해도 됩니다. SEQUENCE 함수도 첫 번째 셀에만 식을 입력하면 인수에 지정된 값들을 참고하여 여러 개의 셀에 일련의 규칙을 가진 값들을 한꺼번에 반환합니다. SEQUENCE 함수는 일련의 연속적인 숫자를 얻기 위해서 사용되지만 대개 홀로 사용되기보다는 다른 함수들과 결합하여 그 전체 식에 일련의 숫자를 제공하는 용도로 많이 사용됩니다. 이 함수는 정수뿐만 아니라 소수점이 있는 소수와 엑셀 내부에서 숫자로 취급되는 날짜, 시간 등도 처리할 수 있어 여러 개의 날짜나 시간을 필요로 할 때도 유용합니다. SEQUENCE 함수 강의 예제와 영상은 다음과 같습니다. 첨부파일 SEQENCE 함수.xlsx 파일 다운로드 동적 배열 함수에 대한 자세한 설명이 필요하면 여기를 클릭하시면 됩니다. 동적 배열 함수는 반환하는 값을 기록할 셀의 개수가 부족하면 "#SPILL!" 에러가 발생합니다. 이 경우는 값이 반환될 셀들을 확보해 주면...

첨부파일 그룹내순서.xlsx 파일 다운로드 다음의 예제 시트를 보세요. B3:C14 셀의 데이터 목록에는 연습실에 입실한 순서대로 반과 이름이 기술되어 있습니다. 우리는 이 데이터 목록에서 특정 데이터에 대해 "반별 입실 순서"를 구해야 합니다. 오른쪽의 표의 F열에 반, G열에 이름이 주어지면 그 학생이, 전체가 아닌, 자기 반 학생들 중에서 몇 번째로 입실했는지를 구해야 합니다. 만일 해당되는 데이터가 없다면 "없음"을 표시해야 합니다. 이 작업을 하려면 먼저 F열의 "반" 정보를 참조하여 데이터 목록에서 반별로 데이터를 추출하는 작업이 필요할 것이고, 그 추출된 반별 데이터에서 G열의 이름이 몇 번째에 위치하는지를 알아내야 할 것입니다. 우리가 완성해야 할 것은 G열입니다. 이 시트에는 다음과 같이 이름이 정의되어 있습니다. B3:B14 셀 : 반 C3:C14 셀 : 이름 G3 셀에 다음의 함수식을 입력한 후, G3 셀의 채우기 핸들을 잡고 G6 셀까지 드래그합니다. =IFERROR( XMATCH(F3,FILTER(이름,반=E3),0),"없음" ) 이 식에서 제일 바깥에 있는 IFERROR 함수식은 XMATCH 함수가 에러를 반환하면 "없음"을 표시하기 위해 사용되었습니다. 핵심적인 작업은 XMATCH와 FILTER 함수가 실행합니다. FILTER 함수는 데이터를 걸러내는 역할을 하고, XMATCH 함수는 일치하는 값의 순서번호...

첨부파일 문자와단어.xlsx 파일 다운로드 엑셀의 텍스트에서 특정 단어나 특정 문자를 찾는 작업은 FIND 함수나 SEARCH 함수를 사용하면 무난히 해결됩니다. 기본 기능은 FIND 함수와 SEARCH 함수가 동일하나 SEARCH 함수는 대소문자를 구분하지 않으며, 와일드카드 문자를 사용할 수 있어 더 유연하다는 특징이 있지요. 그런데... FIND 함수도, SEARCH 함수도 도움이 안 되는 경우가 있습니다. 다음의 예제 시트를 보세요. 이 그림의 2개 테이블에는 각기 텍스트1과 텍스트2가 있습니다. 텍스트1은 1개의 단어만 있고, 텍스트2에는 콤마로 구분된 여러 개의 단어가 있습니다. 이 텍스트에 "차"라는 단어가 있으면 "있음"이라고 표시하고 "차"라는 단어가 없으면 아무것도 표시하지 않아야 합니다. 그림에서 보면 FIND 함수와 SEARCH 함수를 사용하면 올바로 표시되지 않습니다. 이 함수들은 어느 위치에든지 "차"라는 문자가 있기만 하면 무조건 "있음"을 표시합니다. 그러나 MATCH 함수나 XMATCH 함수를 사용하면 FIND 함수나 SEARCH 함수와는 다르게 독립적인 "차"라는 단어만을 찾아냅니다. FIND 함수와 SEARCH 함수는 "차"라는 문자가 있으면 모두 찾아내고 MATCH 함수나 XMATCH 함수는 차"라는 독립적인 단어만 찾아냅니다. 이 사실을 기억해 둘 필요가 있습니다. 여기서는 "차"라는 1개 문자로...

첨부파일 데이터분리.xlsx 파일 다운로드 엑셀 시트에 기록되어 있는 하나의 데이터 목록에서 데이터를 처음부터 하나씩 추출해서 2개의 다른 데이터 목록을 만들거나 또는 3개의 데이터 목록을 만드는 작업을 살펴봅니다. 다음의 예제 시트를 보세요. B3:B22 셀의 데이터 목록에 20개 데이터가 있으며 이 셀범위에는 "상품"이라는 이름이 정의되어 있습니다. D열의 "그룹1"과 E열의 그룹2"는 B열의 데이터들을 하나씩 번갈아가며 가져와서 2개의 데이터 목록을 만든 것입니다. 또한 G열, H열, I열에도 B열의 데이터들을 하나씩 번갈아가며 가져와서 3개의 데이터 목록을 만들었습니다. 이런 식으로 하나의 데이터 목록으로 여러 개의 데이터 목록을 만드는 함수식을 살펴볼 것입니다. 이 작업을 위한 함수식에서 ROW 함수가 중요한 역할을 하기 때문에 먼저 ROW 함수를 살펴봅니다. ROW 함수는 행번호를 반환하는 함수이며, 인수가 없이 사용할 수도 있고 인수를 기술할 수도 있지요. 다음 그림은 인수가 없이 사용한 예입니다. 앞의 그림의 B열에서 보듯이 인수 없이 ROW 함수를 사용하면 함수가 기술된 해당 셀의 행번호를 반환합니다. B2 셀에 2가 기록된 것은 B2 셀에 =ROW() 함수가 기술되었기 때문이지요. 그러나 E열에서 보듯이 =ROW(A1)와 같이 함수에 인수 A1을 기술하면 A1 셀의 행번호를 반환하기 때문에 E2 셀에 1이 기록됩니다...

첨부파일 번갈아.xlsx 파일 다운로드 2개의 엑셀 데이터 목록에서 처음부터 끝까지 하나씩 데이터를 번갈아 가져와서 하나의 데이터 목록을 만드는 함수식을 작성해 봅니다. 여기서는 2개의 데이터 목록을 대상으로 하는 함수식을 만들었으나 이 함수식을 응용하면 더 많은 개수의 데이터 목록을 대상으로 작업하는 함수식도 작성할 수 있습니다. 다음의 예제 시트를 보세요. "A 클래스"라는 데이터 목록에는 홀수 번호와 점수가 기술되어 있고 "B 클래스"라는 데이터 목록에는 짝수 번호와 점수가 기술되어 있습니다. 이 2개의 데이터 목록을 순서대로 합쳐서 "결과" 데이터 목록을 만들었습니다. "결과" 목록을 기준으로 보면 "A 클래스"와 "B 클래스"를 번갈아 참조하여 데이터를 하나씩 가져와야 합니다. 우선 2개의 데이터 목록에서 데이터를 가져오려면 다음과 같이 2개의 INDEX 함수가 필요할 것이라고 추정할 수 있지요. INDEX(A 클래스), INDEX(B 클래스) 그런데... 이 2개 INDEX 함수를 번갈아 실행하려면?? IF 함수를 사용해서 TRUE, FALSE에 따라 선택하게... IF ( INDEX(A클래스), INDEX(B클래스) .... 이제 함수식의 큰 틀은 만들어졌으니 각 부분을 구체적으로 기술하면 될 것 같습니다. 이 시트에는 다음과 같이 이름이 정의되어 있습니다. B3:C12 - A클래스 E3:F12 - B클래스 H3 셀에 다...

첨부파일 숫자결합.xlsx 파일 다운로드 엑셀로 작성한 문서 양식에서 숫자를 1개 셀에 1자리씩 기술하는 예가 종종 있지요. 거래명세서나 각종 청구서 등에서 흔히 볼 수 있을 것입니다. 이 포스트에서는 이런 별도로 기술된 숫자를 모두 연결하여 하나의 엑셀 숫자로 변환하는 방법을 알아봅니다. 다음의 예제 시트를 보세요. B, C, D, E 열에는 1자리 숫자가 4개 있습니다. 현재는 숫자들이 숫자 서식이나 이 숫자들이 텍스트이어도 상관없습니다. 우리는 이 1자리 숫자들을 연결하여 하나의 숫자를 만드는 작업을 3가지 방법으로 합니다. 만일 이 숫자들에 특별한 숫자 서식을 지정해야 한다면 아래 그림과 같이 완성된 결과에 별도로 서식을 지정하야 합니다. 앞의 그림은 완성된 결과에 "1000 단위 구분 기호"를 지정하는 작업을 보이고 있습니다. 또는 결과가 기록될 셀들에 미리 이 서식을 지정해두고 작업을 시작해도 됩니다. "&" 연산자 사용 G3 셀에 다음의 식을 입력한 후 채우기 핸들을 잡고 G9 셀까지 드래그합니다. =IFERROR(VALUE(B3&C3&D3&E3),"") B3&C3&D3&E3 식은 4개 셀에 있는 1자리 숫자들을 모두 연결합니다. "&" 연산자는 텍스트 연산자이기 때문에 이 결과는 텍스트입니다. VALUE 함수는 텍스트 인수를 엑셀이 인식할 수 있는 숫자로 변환합니다. 따라서 다음 식은 연결된 텍스트 숫자를 완전한 숫자로...

첨부파일 선발하기.xlsx 파일 다운로드 행과 열로 구성된 엑셀 2차원 테이블에서 임의의 2개 데이터만을 선택하는 함수식을 알아봅시다. 이 포스트에서 제시하는 예제는 학년별 반별 명단 테이블에서 2명을 선발하는 경우를 다루고 있습니다. 이 경우 함수식을 작성하기 위해 생각해야 할 핵심은 "임의"와 "선택"이라는 단어입니다. "임의"에서 RAND 계열의 함수를 유추할 수 있으며, 2차 테이블의 데이터를 "선택"하려면 INDEX 함수나 VLOOKUP 함수를 생각할 수 있지요. 난수를 반환하는 RAND 계열의 함수는 그 함수를 사용한 엑셀 파일이 열릴 때마다 새로운 난수를 반환하기 때문에 여기서 보이는 그림의 결과와 여러분이 파일을 열었을 때의 결과가 다르다는 점을 유의하세요. 엑셀 365, 2021 버전 학년별, 반별 데이터 테이블의 C3:F5 셀에 범위를 지정하고 이름 상자에 "데이터"라는 이름이 입력하여 이름을 정의합니다. 이름을 정의해서 사용하면 그 이름을 다른 시트에서도 공유할 수 있어 좋기도 하지만, 의미 있는 이름을 함수식에서 사용할 수 있어서 복잡한 함수식의 이해가 수월하고, 1개의 셀에 식을 입력한 후 그 셀의 채우기 핸들을 드래그해서 나머지 셀들의 식을 완성할 때 드래그에 따른 셀주소의 변화를 신경 쓰지 않아도 되기 때문에 편리합니다. H3 셀에 다음의 함수식을 입력합니다. =INDEX(데이터, RANDARRAY(2, 1...

첨부파일 임의날짜지정.xlsx 파일 다운로드 일련의 데이터가 기록되어 있는 엑셀 데이터 목록에 오늘부터 또는 특정일로부터 n 개월 이내의 날짜를 지정하는 함수식을 알아봅시다. 다음의 예제 시트를 보세요. 직원 이름이 있고 그 옆에 오늘부터 2개월 이내의 출장일이 기록되어 있습니다. 예제 파일의 출장일은 "오늘부터" 2개월 이내이기 때문에, 또한 출장일을 구하는 함수식에서 난수를 사용하기 때문에 여러분이 예제 파일을 열었을 때는 이 포스트에서 보이는 출장일과는 다르다는 것을 알아두세요. 엑셀 365, 2021 버전에서는 새로 추가된 RANDARRAY 함수를 사용합니다. 엑셀 구버전에서는 RAND 함수를 사용합니다. C5 셀에 다음의 식을 입력합니다. =TEXT( RANDARRAY(10,1,TODAY(),EDATE(TODAY(),2),TRUE), "MM월 dd일" ) 오늘부터 2개월 이내의 10개 날짜를 반환 날짜에 서식을 지정 이 식에서 TEXT 함수의 역할은 RANDARRAY 함수가 반환하는 10개의 날짜에 "MM월 DD일" 서식을 지정하는 것입니다. 엑셀 365, 2021 버전부터 제공되는 RANDARRAY 함수는 난수 목록을 반환하는 함수로 여기서의 사용 형식은 다음과 같습니다. RANDARRAY(10,1, 10개 행 1개 열에 TODAY(), 오늘 날짜부터 시작해서 EDATE(TODAY(),2), 오늘부터 2개월 후의 날짜 사이...

첨부파일 날짜와 시간.xlsx 파일 다운로드 엑셀 시트에 일련의 날짜나 시간을 기록하는 함수식을 알아볼 것입니다. 날짜나 시간을 일정한 간격으로 여러 개 기록하면서 자신이 원하는 서식으로 표시하기 위한 기능도 추가해 봅시다. 이 작업의 핵심은 일정한 간격으로 날짜나 시간을 생성하기 위해 SEQUENCE 함수나 ROW 함수를 사용하는 것입니다. 자신이 원하는 서식을 지정하는 작업은 TEXT 함수를 추가하면 간단히 해결되지요. 우리는 엑셀 365, 2021 버전의 함수식과 그 이전 버전의 함수식을 함께 살펴볼 것입니다. 일련의 날짜 기록하기 엑셀 365, 2021 버전 B4 셀에 다음의 함수식을 입력합니다. =TEXT(TODAY() + SEQUENCE(10, 1, 0, 1), "yyyy-mm-dd") 숫자에 서식을 지정해서 텍스트로 변환하는 TEXT 함수를 사용하여 TODAY() + SEQUENCE(10, 1, 0, 1) 식의 결과에 "yyyy-mm-dd" 서식을 지정합니다. TODAY 함수는 오늘의 날짜를 반환합니다. SEQUENCE 함수는 10개 행, 1개 열에 0부터 1씩 증가하는 숫자를 반환합니다. 즉, 10개 행에 0부터 9까지의 숫자를 반환합니다. 따라서 TODAY() + SEQUENCE(10, 1, 0, 1) "yyyy-mm-dd"은 오늘부터 연속적으로(1일 간격으로) 10일간의 날짜를 반환하고 TEXT 함수는 그 날짜들을 ...

첨부파일 한칸건너.xlsx 파일 다운로드 엑셀에서 연속적인 숫자나 일정 구간의 홀수만 또는 짝수만 표시하는 방법은 비교적 간단합니다. 여기서는 숫자들을 수직 또는 수평으로 기록하되 1칸씩 또는 2칸, 3칸씩 건너서 짝수나 홀수 또는 3의 배수만 기록하는 함수식을 알아볼 것입니다. 엑셀 365, 엑셀 2021 버전의 함수식과 그 이전의 엑셀 구 버전에서 사용할 수 있는 식들을 모두 살펴봅니다. 홀수만 1칸씩 건너서 기록하려면 (엑셀 365, 2021 버전 사용) B4 셀에 다음의 식을 입력합니다. =IF(MOD(SEQUENCE(10), 2) = 1, SEQUENCE(10), "") SEQUENCE 함수는 연속적인 숫자 배열을 만들지요. 여기서 사용된 SEQUENCE(10) 은 1에서 10까지 10개의 숫자를 수직으로 반환합니다. MOD 함수는 나머지를 구하는 함수입니다. 그래서 MOD(SEQUENCE(10), 2) = 1 식은 SEQUENCE(10)에 의해 1에서 10까지 숫자가 만들어진 후 그 10개의 숫자를 모두 2로 나누어 나머지가 1인가를 즉, 홀수인가를 검사하여 홀수이면 TRUE, 아니면 FASLE가 반환됩니다. 이제 식은 다음과 같이 변합니다. =IF({TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}, SEQUENCE(10), "") ↓ =IF( {TRUE;FALSE;TRU...

첨부파일 동적목록.xlsx 파일 다운로드 예제 시트 살펴보기 B3 셀에서 정렬 순서를 선택하고, B6 셀에 원하는 개수를 입력해서 조건을 설정하면 D 열에 그 조건에 맞게 도시 이름이 나열됩니다. B3 셀의 드롭다운 목록에서 정렬을 새로 선택하고, B6 셀에 개수를 새로 입력하면 그 조건에 맞춰 D열에 표시되는 목록이 수정됩니다. 여기서 표시되는 도시 이름은 총 10개입니다. 만일 B6 셀에 입력되는 값이 10을 넘거나 0 이하이면 D3 셀에 "재설정"이라는 메시지가 표시됩니다. 정렬 드롭다운 목록 만들기 이 작업을 위해서는 제일 먼저 B3 셀에 표시되는 정렬 목록부터 만들어야 합니다. B3 셀의 선택한 상태에서 [데이터][데이터 도구][데이터 효율성 검사]를 선택하고 [데이터 유효성] 창이 표시되면 [설정][제한 대상]에서 "목록"을 선택하고 [원본]에 "오름차순,내림차순"을 입력한 후 [확인]을 클릭합니다. 함수식 분석하기 D3 셀에 다음의 식을 입력합니다. =IF(OR(B6>10,B6<=0),"재설정",SORT(CHOOSE(SEQUENCE(B6),"서울","부산","여수","목포","울산","군산","속초","주문진","포항","제주"),1,IF(B3="오름차순",1,-1))) 이 식은 IF 함수가 추가되어 복잡해 보이지만 제일 외곽의 IF 함수를 제외하면 SORT 함수식이 주요 식입니다. 먼저 제일 외곽의 IF 함수식을 중심으...

첨부파일 근무일.xlsx 파일 다운로드 토요일, 일요일은 당연히 쉬겠지요. 그런 주말 이외에도 공휴일이나 회사 창립일과 같은 특별한 휴일도 있습니다. 여기서는 시작 날짜와 마지막 날짜, 그리고 휴일을 참고하여 그 기간 동안의 근무일 목록을 작성하는 작업을 합니다. 다음의 예제 시트를 보세요. 근무일1과 근무일1-1은 엑셀 구 버전에서 실행할 수 있는 함수를 사용했습니다. 근무일2와 근무일2-1은 엑셀 365, 엑셀 2021 버전에 새로 추가된 함수들을 사용했습니다. 두 버전 모두 기본 함수식과 조금씩 더 세련된 함수식을 보이고자 했습니다. 엑셀 365, 엑셀 2021 이전 버전 사용 D3 셀에 다음의 함수식을 입력한 후, 채우기 핸들을 잡고 D11 셀까지 드래그합니다. =WORKDAY($B$3, ROW(D1)-1, $B$9:$B$10) 아주 간단하게 WORKDAY 함수만을 사용한 이 함수식의 의미는 다음과 같습니다. =WORKDAY( $B$3, B3 셀부터 (절대 주소이라서 아래로 드래그해도 B3 셀은 변하지 않음) ROW(D1)-1, 이만큼의 날짜가 지난 근무일을 반환한다 $B$9:$B$10 주말 외에 이 휴일들도 제외한다 ) 이 식에서 ROW(D1)-1 식은 다음과 같이 실행됩니다. ROW 함수는 인수의 행번호를 반환하지요. 앞의 그림에서 보듯이 ROW 함수는 함수가 기술된 셀의 위치와 상관없이 ROW 함수의 인수(이 식의 경우 ...

첨부파일 매주수요일.xlsx 파일 다운로드 다음의 예제 시트를 보세요. 오늘부터 또는 2024년 9월 1일부터 이후 10개의 수요일의 날짜를 구했습니다. 엑셀 365, 엑셀 2021 이전 버전의 구버전 함수들로 구한 것이 왼쪽의 목록이고 오른쪽 목록은 엑셀 365, 엑셀 2021에 새로 제공되는 SEQUENCE 함수를 이용했습니다. 왼쪽의 구버전 목록은 2개의 식이 필요하나 오른쪽 목록은 1개의 식으로 해결된다는 점이 다릅니다. B4 셀에 다음의 식을 입력합니다. =TODAY() + 7 - WEEKDAY(TODAY() - 4) B5 셀에 다음의 식을 입력한 후, 채우기 핸들을 잡고 B13 셀까지 드래그합니다. =B4+7 C4 셀에 다음의 식을 입력합니다. =DATE(2024,9,1) + 7 - WEEKDAY(DATE(2024,9,1) - 4) C5 셀에 다음의 식을 입력한 후 채우기 핸들을 잡고 C13 셀까지 드래그합니다. =C4+7 "오늘부터" 10개의 수요일을 구할 때는 오늘의 날짜를 반환하는 TODAY 함수를 사용했으며 "2024-09-01부터" 10개의 수요일을 구할 때는 숫자로 날짜를 만드는 DATE 함수를 사용했다는 점만 다를 뿐 앞의 2가지 작업은 동일한 작업입니다. TODAY 함수는 항상 이 엑셀 파일이 열리는 그날을 의미하기 때문에 TODAY 함수를 사용한 결과는 필자가 보여주는 날짜와 다를 것입니다. C4 셀의 다음...

첨부파일 이메일도메인.xlsx 파일 다운로드 이 메일 주소는 다음과 같이 구성됩니다. 사용자이름@도메인.최상위도메인 예를 들면 다음과 같습니다. our21@microsoft.com 그런데 도메인 부분이 다음과 같이 2개 파트로 구성되기도 하지요. dong12@google.co.kr 우리는 이런 이메일 주소에서 최상위 도메인(Top Level Domain;TLD)과 도메인을 추출하는 작업을 해볼 것입니다. 다음 그림은 "최상위 도메인"을 추출한 결과입니다. 앞의 3개 이메일 주소는 "." 문자가 1개 있고 뒤의 3개 이메일 주소는 "." 문자가 2개 있습니다. 다음 그림은 "도메인"을 추출한 결과입니다. 엑셀 365 이전 버전에서 최상위 도메인 추출 우선 눈으로 보면서 논리적으로 따져봅시다. 제일 마지막에 있는 최상위 도메인만을 추출하려면 이메일 주소의 제일 오른쪽에서 시작해서 첫 번째 "." 문자 이전까지를 추출하면 됩니다. 이와 같은 논리를 함수들을 이용해서 구체적으로 표현한 식이 다음과 같은 C3 셀의 식입니다. C3 셀에 이 식을 입력한 후 채우기 핸들을 잡고 C8 셀까지 드래그합니다. =RIGHT(B3,LEN(B3)-FIND("*",SUBSTITUTE(B3,".","*",LEN(B3)-LEN(SUBSTITUTE(B3,".",""))))) 크게 보면 이 식은 RIGHT 함수식이며, B3 셀의 텍스트를 오른쪽부터 빨간색의 식이 반...

첨부파일 여러 시트.xlsx 파일 다운로드 다음의 예제 파일을 보세요. 이 파일에는 "10월", "11월", "12월", "4분기" 등 총 4개의 시트가 있습니다. 3개월간의 입고된 제품의 색상과 개수에 대한 정보가 3개 시트에 정리되어 있습니다. 우리는 이 3개 시트의 데이터 목록을 대상으로 아래와 같이 "4분기" 시트를 작성해야 합니다. 3개 시트에 있는 "색상" 정보를 참조하여 색상을 하나씩 추출하고 각 색상이 몇 개 입고되었는지를 계산해야 합니다. 우리는 색상별 입고 개수를 2가지 함수식으로 구했습니다. 이 파일에는 아래와 같이 이름이 정의되어 있습니다. 대개 이렇게 여러 개의 시트에 있는 셀범위를 참조할 때는 의미 있는 이름을 정의해서 사용하는 것이 함수식을 작성하는데 도움이 됩니다. 먼저 3개의 시트로부터 색상 정보를 가져와 중복되지 않게 하나씩 색상을 추출하는 작업부터 생각해 봅시다. B4 셀에 다음의 식을 입력합니다. =UNIQUE(VSTACK(색상10,색상11,색상12)) 이 식은 VSTACK 함수를 사용하여 3개 시트의 색상을 가져와 수직으로 연결한 후 그 결과에 대해 UNIQUE 함수를 실행하여 중복 없이 색상을 추출하여 반환합니다. VSTACK 함수만 실행해 보면 다음과 같습니다. 그림에서 다 보이지 않지만 "10월", "11월", "12월"의 색상을 그대로 가져와 순서대로 수직으로 연결해서 B34 셀까지 길게 ...

첨부파일 자동 구구단.xlsx 파일 다운로드 엑셀로 구구단을 만들 수는 없을까? 내가 원하는 단수를 입력하면 그 단의 구구단이 쫘악... 다음의 예제 시트를 보세요. B열의 구구단은 엑셀 365, 엑셀 2021 버전에서 사용할 수 있는 함수식으로 만들어진 구구단이고 D열의 구구단은 모든 엑셀 버전에서 사용할 수 있는 식으로 만들어진 구구단입니다. B열의 구구단은 엑셀 365, 엑셀 2021 버전부터 추가된 SEQUENCE 함수를 사용했으며 D열의 구구단은 배열 상수를 활용했습니다. 또한 셀 서식을 이용하기도 했습니다. 앞의 그림과 같이 B2 셀에 "단"자 없이 그냥 숫자 7을 입력하면 7단의 구구단이 표시됩니다. 이것은 D2 셀도 마찬가지이지요. B2 셀과 D2 셀에는 다음과 같이 셀서식을 지정했습니다. [셀서식][표시 형식][범주]에서 "사용자 지정"을 선택하고 [형식]을 다음과 같이 지정했습니다. #,##0"단" 이 서식 코드는 천단위 콤마가 있는 숫자와 그 뒤에 "단"자를 표시합니다. 그래서 B2 셀이나 D2 셀에 표시되는 내용은 "5단"과 같이 "단"자가 있지만 수식 입력줄에 보듯이 실제 기억된 내용은 숫자 5입니다. 이런 식으로 서식 코드를 활용하는 방법을 기억해두면 함수식을 작성할 때 도움이 될 때가 많습니다. 엑셀 365, 엑셀 2021 버전 사용 B3 셀에 다음의 식을 입력합니다. =B2&" * "&SEQUENCE(9,...

첨부파일 도메인 추출.xlsx 파일 다운로드 아래 시트를 보세요. 인터넷 주소에서 도메인 이름만 추출하는 작업을 합니다. "도메인 1"은 엑셀 365 버전의 새로운 함수를 사용했습니다. "도메인 2"는 엑셀 365와 이전 버전에서 사용할 수 있는 함수들을 사용했습니다. 엑셀 356와 버전 사용 C3 셀에 다음의 식을 입력한 후 채우기 핸들을 잡고 C12 셀까지 드래그합니다. =TEXTBEFORE( TEXTAFTER(B3,"//") ,"/" ) 이 식은 TEXTAFTER 함수가 반환하는 값을 TEXTBEFORE 함수가 인수로 사용하고 있습니다. TEXTAFTER 함수는 특정 문자를 찾아서 그 문자 뒤에 있는 텍스트를 반환합니다. C3 셀의 식을 TEXTAFTER 함수만 남게 수정하면 다음과 같습니다. 식의 의미는 다음과 같습니다. TEXTAFTER( B3, "//" ) B3 셀에서 // 문자 뒤의 텍스트를 추출한다. 이 함수가 반환한 텍스트를 C3 셀에서 볼 수 있습니다. 이제 우리는 C3 셀의 텍스트에서 첫 번째 "/" 문자 앞의 텍스트를 추출하면 될 것입니다. 그래서 다음과 같이 TEXTBEFORE 함수를 추가합니다. 식의 의미는 다음과 같습니다. =TEXTBEFORE( TEXTAFTER(B3,"//"), "/" ) TEXTAFTER 함수가 반환한 결과에서 / 문자 앞의 텍스트를 반환한다 엑셀 365 이전 버전 사용 D3 셀에 다음...

첨부파일 가입자.xlsx 파일 다운로드 이 포스트에서는 여러 개의 조건을 충족하는 데이터의 개수를 세는 작업을 합니다. 그런데 이 조건이 텍스트로 구성되어 있고 우리는 그 조건 텍스트에서 숫자를 추출해서 계산을 해야 합니다. 아래의 예제 시트를 봅시다. 왼쪽의 데이터 목록을 참조하여 오른쪽에 2개의 값을 구해야 합니다. H2 셀에 "남"이나 여"와 같이 성별을 입력하면 H3 셀에 그 성별의 인원수가 표시되어야 합니다. H6:H10 셀과 I6:I10 셀에는 연령 구간별로 인원을 표시하되 H2 셀의 성별에 대해서만 인원을 세야 합니다. 다시 말해서, H6:H10 셀과 I6:I10 셀에 표시되는 인원수는 "성별"이라는 조건과 "연령"이라는 조건을 충족시키는 인원 수이지요. "인원1"은 엑셀 365 이전 버전에서도 사용할 수 있는 함수식을 사용했으며 "인원2"는 엑셀 365 버전에서만 사용할 수 있는 함수식을 사용했습니다. 이 시트에는 다음과 같이 이름이 정의되어 있습니다. B3:B12 : 가입일 D3:D12 : 생년월일 E3:E12 : 성별 엑셀 365 이전 버전 사용 제일 먼저 H3 셀의 값을 구하는 작업부터 해봅시다. H3 셀은 단순히 "남"과 "여"를 구분해서 숫자를 세는 작업이므로 조건을 만족하는 값(데이터)만 개수를 세는 COUNTIF 함수로 간단히 처리할 수 있습니다. H3 셀에 다음의 식을 입력합니다. =COUNTIF( E3:...

첨부파일 부분문자.xlsx 파일 다운로드 텍스트 목록의 데이터를 처리하는 다음의 예제 시트를 보세요. 이 시트의 왼쪽에는 텍스트 데이터 목록이 있습니다. 텍스트 목록은 "기부 단체"와 "기부금" 정보가 기술되어 있는데, 기부 단체는 "행정구역, 단체이름"으로 구성됩니다. 우리는 이 텍스트 목록을 대상으로 "단체 이름"별 기부금의 집계와 "행정 구역"별 기부금을 집계하는 작업을 할 것입니다. 이 작업은 콤마를 기준으로 콤마 앞의 텍스트와 콤마 뒤의 텍스트를 추출하는 작업과 그 추출된 텍스트를 중복 없이 유일하게 하나씩만 다시 추출하는 작업이 필요하며 추출된 항목별로 기부금을 더하는 작업도 필요합니다. 엑셀 365 또는 엑셀 2021 버전 먼저 "단체 이름"을 중복 없이 추출하는 작업부터 생각해 봅시다. 다행히도 콤마 뒤의 "단체 이름"은 모두 3 글자로 동일합니다. 이럴 때는 모두 오른쪽에서 3개의 문자를 추출하면 되니까 RIGHT 함수가 필요할 것이고, 추출된 텍스트를 중복 없이 정리하려면 UNIQUE 함수가 동원될 수 있지요. E3 셀에 다음의 식을 입력합니다. =UNIQUE( RIGHT(B3:B12,3) ) RIGHT 함수가 B3:B12 셀의 텍스트에서 오른쪽 3개 문자를 추출해서 UNIQUE 함수에게 넘깁니다. UNIQUE 함수는 넘겨받은 10개의 텍스트를 검사해서 중복 없이 하나씩만 존재하도록 조절해서 반환합니다. UNIQUE...