스프레드시트 계산을 개선하는 새로운 Excel 수식

들어가기 전에

Microsoft Excel은 다양한 분야에서 오랫동안 사용된 도구로, 데이터 분석과 보고를 간소화하는 광범위한 기능을 제공합니다. 최근에는 새로운 수식이 도입되면서 Excel은 더욱 강력해져서 복잡한 작업을 손쉽게 처리할 수 있게 되었습니다. 최근 추가된 함수를 포함한 다섯 가지 혁신적인 Excel 수식과 AI 기반 함수를 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft Excel MVP · Excel 솔루션 프로바이더 · 작가

이미지: 아이엑셀러 닷컴

 

※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

---

• 원문:NEW Excel Formulas to improve your spreadsheet calculations
• URL: https://www.geeky-gadgets.com/new-excel-spreadsheet-formulas/

---

1. CHOOSECOLS로 열 선택 간소화

CHOOSECOLS 수식은 테이블에서 특정 열을 선택할 때 촉매제 역할을 합니다. 이 수식을 사용하면 필요한 데이터를 빠르고 쉽게 추출하여 데이터 분석 프로세스를 간소화할 수 있습니다. CHOOSECOLS를 SORT 및 TAKE와 같은 다른 함수와 결합하면 데이터를 효율적으로 구성하고 제한하여 귀중한 시간과 노력을 절약할 수 있습니다.

이런 시나리오를 생각해 보세요. 이름, 주소, 전화번호, 이메일 주소를 포함한 고객 정보가 들어 있는 대규모 데이터 세트가 있습니다. 그러나 현재 분석에서는 고객 이름과 이메일 주소만 필요합니다. CHOOSECOLS를 사용하면 다음을 수행할 수 있습니다.

• 데이터가 포함된 테이블 범위를 지정합니다.
• 선택하려는 열 번호 또는 이름을 표시합니다(예: 이름과 이메일 주소의 경우 1 및 4).
• 원하는 열을 새 테이블이나 범위로 즉시 추출합니다.

 

CHOOSECOLS를 사용하면 열을 수동으로 복사하여 붙여넣을 필요가 없으므로 특정 데이터 하위 집합을 다루는 프로세스가 간소화됩니다.

 

2. TEXTSPLIT과 TEXTJOIN을 사용한 텍스트 제어

Excel에서는 텍스트 조작이 일반적인 작업이며, TEXTSPLIT 및 TEXTJOIN 수식을 사용하면 그 어느 때보다 쉽게 ​​텍스트 데이터를 분할하고 결합할 수 있습니다.

TEXTSPLIT은 지정된 구분 기호를 기준으로 텍스트를 열로 구분하는 강력한 도구입니다. 이 기능은 CSV 파일의 데이터로 작업할 때나 이름과 주소를 개별 구성 요소로 구분해야 할 때 특히 유용합니다. 예를 들어, "성, 이름" 형식의 전체 이름이 포함된 열이 있는 경우 TEXTSPLIT을 사용하여 다음을 수행할 수 있습니다.

• 전체 이름이 포함된 셀 또는 범위를 지정합니다.
• 구분 기호(이 경우 쉼표)를 표시합니다.
• 성과 이름을 별도의 열로 나눕니다.

 

TEXTJOIN을 사용하면 여러 열의 데이터를 지정된 구분 기호를 사용하여 단일 텍스트 문자열로 결합할 수 있습니다. 이는 연결된 목록을 만들거나 다른 소스의 데이터를 병합할 때 특히 유용합니다. 예를 들어, 이름, 성, 이메일 주소에 대한 별도의 열이 있는 경우 TEXTJOIN을 사용하여 다음을 수행할 수 있습니다.

• 원하는 구분 기호(예: 세미콜론)를 지정합니다.
• 결합하려는 열이 포함된 범위를 지정합니다.
• 결합된 데이터로 단일 텍스트 문자열을 만듭니다(예: "이름 성; 이메일").

 

TEXTSPLIT과 TEXTJOIN을 활용하면 텍스트 데이터를 효율적으로 조작하여 시간을 절약하고 수동 데이터 입력과 관련된 오류 위험을 줄일 수 있습니다.

3. REGEX로 패턴 매칭의 힘 활용하기

정규 표현식 또는 REGEX는 고급 패턴 매칭의 힘을 Excel로 가져옵니다. REGEX 함수를 사용하면 복잡한 데이터 패턴을 찾아 추출하여 데이터를 검증하고 분석하기가 더 쉬워집니다.

REGEX의 일반적인 사용 사례 중 하나는 비밀번호 검증입니다. 비밀번호 열이 특정 기준(예: 대문자 하나, 숫자 하나, 특수 문자 하나)을 충족하는지 확인해야 하는 경우 REGEX가 이 프로세스를 자동화할 수 있습니다. 다음을 수행할 수 있습니다.

• REGEX 구문을 사용하여 유효한 비밀번호 패턴을 정의합니다.
• 비밀번호가 포함된 열에 REGEX 공식을 적용합니다.
• 기준을 충족하는 비밀번호와 업데이트가 필요한 비밀번호를 즉시 식별합니다.

 

REGEX는 이메일 주소나 전화번호와 같은 텍스트 문자열에서 특정 데이터를 추출하는 데도 매우 귀중합니다. 적절한 REGEX 패턴을 정의하면 원하는 정보를 빠르게 분리하고 추출하여 데이터 분석 프로세스를 간소화할 수 있습니다.

4. GROUPBY를 사용한 데이터 집계 간소화

GROUPBY 함수는 대규모 데이터 세트를 요약하고 분석하는 강력한 도구입니다. GROUPBY를 사용하면 지정된 기준에 따라 데이터를 빠르게 그룹화하고 결과 그룹에 SUM, AVERAGE 또는 COUNT와 같은 집계 함수를 수행할 수 있습니다.

여러 지역과 제품 범주에 대한 판매 데이터가 포함된 대규모 데이터 세트가 있다고 가정해 보겠습니다. GROUPBY를 사용하면 다음을 수행할 수 있습니다.

• 그룹화할 열을 지정합니다(예: 지역 및 제품 범주).
• 적용할 집계 함수를 표시합니다(예: 매출 합계).
• 지역 및 제품 범주의 고유한 조합에 대한 총 매출을 보여주는 요약 표를 만듭니다.

 

GROUPBY를 사용하면 머리글을 포함하고 총계를 제거하는 등 출력을 사용자 정의할 수 있어 특정 요구 사항에 맞춰 전문적인 보고서를 쉽게 만들 수 있습니다.

5. 고급 기능을 통해 AI의 힘 활용

ChatGPT 추가 기능에서 제공하는 것과 같은 AI 기반 기능은 Excel의 데이터 추출 기능을 새로운 차원으로 끌어올립니다. 이러한 기능은 인공 지능을 활용하여 텍스트 데이터에서 다음과 같은 주요 정보를 식별하고 추출합니다.

• 텍스트 문자열에 언급된 국가 및 대륙 식별
• 비정형 데이터에서 이메일 주소 또는 전화번호 추출
• 감정이나 주제에 따라 고객 피드백 분류

 

AI 기반 기능을 Excel 워크플로에 통합하면 복잡한 데이터 추출 작업을 자동화하여 시간을 절약하고 정확도를 높일 수 있습니다. 이는 고객 리뷰나 소셜 미디어 게시물과 같은 대량의 비정형 데이터를 처리할 때 특히 유용합니다.

 

마치며

이 가이드에서 논의하는 새로운 Excel 수식(CHOOSECOLS, TEXTSPLIT, TEXTJOIN, REGEX, GROUPBY 및 AI 기반 함수)과 DATEDIF 함수는 스프레드시트 기능을 강화하기 위한 강력한 툴킷을 제공합니다. 이러한 수식을 숙지하고 워크플로에 통합하면 데이터 분석을 간소화하고, 데이터 조작을 개선하고, 전반적인 생산성을 높일 수 있습니다. Excel 사용에 대한 추가 정보가 필요하면 Microsoft 지원 웹사이트를 참고하세요.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.