엑셀에서 잘못된 날짜, 숫자 찾고 변환하기
다음 자료에서 '작업일'과 '작업시간'은 눈으로 보면 문제가 없어 보이지만 일부 날짜와 숫자가 텍스트 형식으로 되어 있습니다. 어떤 데이터가 텍스트 형식으로 되어 있는지 찾아서 날짜와 숫자 형식으로 변환해 보겠습니다. 1) …
엑셀중급강좌
엑셀 중급 강좌
수식으로 한 번에 텍스트 나누기
다음과 같이 A열에 슬래쉬, 쉼표, 공백 등으로 구분된 텍스트가 입력되어 있을 때 여러 열에 나누어서 값을 가져오려면 텍스트 나누기로 해도 되지만 TRANSPOSE, FILTERXML 함수를 사용한 수식으로 간단히 해결할 수 있습니다. …
엑셀 조건부서식의 모든 것
엑셀의 조건부 서식을 사용하면 셀에 입력된 값이 정해진 조건을 만족하면 원하는 서식(배경색, 폰트 색상, 아이콘 등)으로 강조 표시를 할 수 있습니다. 조건부 서식을 활용하면 중요한 데이터를 빠르게 파악하거나, 잘못 입력된 …
엑셀 조건부서식 - 셀 강조 규칙
엑셀의 조건부 서식을 사용하면 셀에 입력된 값이 조건을 만족하면 원하는 모양으로 강조 표시를 할 수 있습니다. 조건부 서식은 아래와 같이 미리 정의된 것이 5개 종류가 있고 필요 시 사용자가 새로 …
엑셀 조건부서식 - 상위/하위 규칙
엑셀의 조건부 서식을 사용하면 셀에 입력된 값이 조건을 만족하면 원하는 모양으로 강조 표시를 할 수 있습니다. 조건부 서식은 아래와 같이 미리 정의된 것이 5개 종류가 있고 필요 시 사용자가 새로 …
엑셀 조건부서식 - 데이터 막대
엑셀의 조건부 서식을 사용하면 셀에 입력된 값이 조건을 만족하면 원하는 모양으로 강조 표시를 할 수 있습니다. 조건부 서식은 아래와 같이 미리 정의된 것이 5개 종류가 있고 필요 시 사용자가 새로 …
엑셀 조건부서식 - 색조
이번 글에서는 엑셀의 조건부 서식 중에서 '색조'를 알아보겠습니다. 색조를 사용하면 셀 값의 크기에 따라 배경색이 달라집니다. 예를 들어 셀 값이 크면 배경색을 진하게 작으면 연하게 표시하여 값의 크기, 값의 변화 …
엑셀 조건부서식 - 아이콘 집합
이번 글에서는 엑셀의 조건부 서식 중에서 '아이콘 집합' 알아보겠습니다. 아이콘 집합에는 다음과 같이 방향 화살표, 신호등, 깃발 등 여러 모양이 있습니다. 아이콘 집합을 사용하면 셀 값의 크기에 따라 아이콘의 …
수식으로 조건부서식 지정
엑셀의 조건부 서식은 다음과 미리 정의된 다양한 규칙이 있으므로 셀을 선택하고 원하는 규칙을 고르기만 하면 손쉽게 사용할 수 있습니다. 편리하게 사용할 수는 있지만 복잡한 규칙을 적용하기에는 한계가 있습니다. 이런 경우 …
엑셀 순환참조 문제 해결하기
누군가로부터 엑셀 파일을 받아서 열었는데 다음과 같이 순환참조가 있다는 경고가 나타날 때가 있습니다. 이번 글에서는 왜 순환참조가 발생하고 어떻게 해결하는 지 알아 보겠습니다. 1. 순환참조가 발생하는 이유 순환 참조는 …
수식 계산 단계 알아보기
이번 글에서는 엑셀의 '수식 분석' 기능에 포함되어 있는 '수식 계산' 기능을 이용하여 수식이 계산되는 단계를 알아보겠습니다. 이 기능을 이용하면 복잡한 수식이 내부적으로 어떻게 계산되는지 단계별로 따라가면서 쉽게 확인할 수 있습니다 …
엑셀 수식 오류 검사하고 추적하기
이번 글에서는 엑셀의 '수식 분석' 기능에 포함되어 있는 '오류 검사' 기능을 이용하여 수식의 오류를 검사하고 오류를 추적하는 방법을 알아보겠습니다. 1. 오류 검사 아래의 엑셀 시트에는 5개의 셀에 오류가 있습니다. …
엑셀에서 참조 셀 추적하기
다음 그림과 같이 엑셀의 '수식' 탭에는 '수식 분석'이라는 기능이 있습니다. 한 번쯤은 눌러보고 왠지 어려울 것 같고 딱히 필요하지도 않을 것 같아서 잘 안쓰는 기능 중에 하나인 것 같습니다. '수식 …
직관적인 엑셀 표 구조적 참조
이번 글에서는 엑셀 표 기능의 구조적 참조에 대해 알아보겠습니다. ※ 엑셀 표의 기본적인 기능은 아래 링크를 참조하세요. [ 엑셀 표 기능으로 자료 관리하기 ] 엑셀의 표는 다음과 같은 구조로 되어 …
엑셀 표 기능으로 자료 관리하기
엑셀에서 데이터를 입력하고 관리하는 일반적인 모습을 보면 대략 다음과 같을 겁니다. 엑셀 시트에 데이터 입력하고, 수식을 입력하고, 참조하는 범위가 바뀌면 참조 범위 고치고, 열이 추가되면 서식을 다시 적용하고... 등등 상당히 …
엑셀 동적 범위에 이름 정의하기
오늘은 이름으로 지정한 범위가 계속 변하는 경우에 필요한 '동적 범위에 이름을 정의'하는 방법을 알아 보겠습니다. 1. 고정된 범위에 이름 정의하기 실습: [상품정보] 시트, [고정범위참조] 시트 동적 범위에 이름을 정의하기 …
엑셀 이름표, 이름 정의
이번 강좌에서는 엑셀 수식을 읽기 쉽게 만드는 이름에 대해 알아보겠습니다. 보통은 셀 또는 범위에 이름을 정의하여 많이 사용하지만 숫자, 텍스트, 수식에도 이름을 정의 할 수 있습니다. 이름은 간단하게 만들 수 …
엑셀 고급필터로 다양하게 자료 필터링하기
엑셀을 이용하여 전체 자료에서 원하는 자료를 골라내고 싶다면 필터 기능을 이용하면 됩니다. 필터 기능에는 두 가지가 있는데, [데이터] 탭 → [정렬 및 필터] 그룹에서 [필터]와 [고급] 두 가지 메뉴를 확인할 …
정렬의 또 다른 방법, 사용자 지정목록/색으로 정렬
엑셀에서 자료를 정렬할 때 값 기준으로 '오름차순' 또는 '내림차순'으로 정렬을 많이 하지만 내가 원하는 기준으로 정렬한다든지 셀에 표시한 색을 기준으로 정렬을 해야 할 때도 있습니다. 이번 글에서는 알아두면 요긴한 사용자 …
엑셀 오류데이터 처리하기
엑셀을 이용하여 업무 처리시 엑셀이 아닌 다른 프로그램에서 만든 자료(PDF, 웹, HWP, Word, PowerPoint등)에서 복사하여 붙여넣거나 시스템에서 다운로드 받은 자료를 가져와서 처리하는 경우가 많습니다. 이런 데이터는 엑셀에서 만든 것이 아니라서 …
파워 쿼리(Power Query) 소개
1. 파워 쿼리(Power Query)란? 파워 쿼리는 여러 종류의 외부 데이터 소스에서 데이터를 가져온 후, 필요에 따라 데이터를 정제하거나, 변환 및 재구성할 수 있는 도구입니다. 파워 쿼리는 엑셀 및 파워BI Desktop등 …
파워쿼리로 외부 데이터 가져오기
엑셀을 이용하여 업무 처리를 하다 보면 엑셀로 만든 자료만 이용하는 것이 아니라 다른 형식으로 만들어진 외부 데이터를 활용해야 하는 경우가 있습니다. 외부 데이터 형식은 CSV, 텍스트, PDF , 웹 등 …
엑셀에서 중복 데이터 제거하기
'엑셀 데이터 처리 - 텍스트 나누기' 글에서도 설명을 했지만 엑셀을 제대로, 쉽게 사용하려면 데이터가 먼저 정리되어 있어야 합니다. 데이터가 정리되지 않은 상태에서는 수식을 복잡하게 꼬거나 난해한 방법으로 문제를 해결해야 하는 …
엑셀 텍스트 나누기의 모든 것
엑셀을 제대로, 쉽게 사용하려면 데이터가 먼저 정리되어 있어야 합니다. 데이터가 정리되지 않은 상태에서는 수식을 복잡하게 꼬거나 난해한 방법으로 문제를 해결해야 하는 경우가 많으므로 가능하면 데이터를 미리 정리하는 것이 좋습니다. 데이터를 …
이동옵션으로 원하는 셀 한번에 선택하고 처리하기
1. 이동 옵션이란? 엑셀의 '이동 옵션'은 '원하는 셀을 한번에 선택'하는 기능입니다. '이동 옵션'이라는 이름만으로는 이 기능이 하는 일을 떠올리기가 쉽지 않습니다. 영문 버전 엑셀에서는 'Go To Special'이라고 되어 있네요. 점점 …
데이터 유효성 검사로 상위목록에 종속되는 하위목록 만들기
이번 글에는 엑셀의 '데이터 유효성 검사' 기능을 이용하여 상위목록에 종속되는 하위목록을 만드는 방법을 알려드리겠습니다. 데이터 유효성 검사에 대한 전반적인 내용은 다음 링크를 참고하세요. [ 엑셀에서 허용된 데이터만 입력하기 ] …
사용자 지정 수식으로 데이터 유효성 검사하기
엑셀에서 셀에 잘못된 데이터가 입력되지 않도록 하고자 할 때 데이터유효성 검사라는 기능을 이용하여 입력값을 체크합니다. 보통은 입력 가능한 목록을 만들어 놓고 체크하거나 숫자 또는 날짜 값만 입력받는 방식으로 체크를 합니다. …
데이터 유효성 검사로 허용된 데이터만 입력
엑셀에서 데이터를 처리할 때 잘못된 데이터가 입력되지 않도록 하는 것이 중요합니다. 숫자필드에 문자가 입력되거나 허용되지 않은 값이 입력되면 문제가 되겠죠. 데이터유효성 검사라는 기능을 이용하면 다음과 같이 잘못된 데이터가 입력되지 않도록 …
엑셀 데이터 다루기 첫 단추는 데이터 구조화
엑셀에서 '데이터 구조화'라고 하면 뭔가 어려울 것 같은 느낌이지만 그렇게 어렵지 않습니다. 약간의 노력을 들여서 알아두면 엑셀의 새로운 세계를 만날 수 있습니다. 과거부터 사용하던 복잡한 엑셀 양식 때문에 수식이 복잡하게 …
엑셀 수식 알아보기(수식의 입력, 수정, 구성)
엑셀의 '수식'을 한마디로 정의하면 어떤 결과를 구하기 위해 셀에 입력하는 '계산식'이라고 할 수 있습니다. 수식은 다음과 같이 등호(=)를 먼저 입력하고 값(직접 입력하는 상수), 참조, 함수, 연산자, 괄호 등을 입력하여 만들 …
엑셀 수식 연산자와 연산 우선순위
수식에 사용되는 연산자 이번 글에서는 엑셀 수식의 구성, 입력, 편집에 이어서 수식에 사용되는 연산자에 대해 살펴보겠습니다. 엑셀 연산자는 크게 4종류가 있으며 연산자별로 계산이 실행되는 순위가 정해져 있습니다. 산술 연산자 …
엑셀에서 와일드카드가 뭔가요?
엑셀에서 다음과 같이 COUNTIF 함수를 사용하면 [B5:B11] 범위에서 정확히 '노트'인 것만 찾아서 COUNT합니다. =COUNTIF(B5:B11,"모나미") 만약 '모나미볼펜', '모나미형광펜'등 유사한 값을 한 번에 찾고 싶다면 와일드카드를 사용하면 됩니다. 와일드카드는 다음 3가지가 있습니다. …
엑셀 배열수식 제대로 이해하기
이번 글에서는 엑셀의 배열수식을 알아보겠습니다. 배열수식을 사용하면 복잡한 문제를 간단히 해결할 수도 있고 여러 방면으로 쓸모가 있지만 이해하기가 쉽지 않고 사용하기도 까다로우므로 꼭 필요한 경우에만 사용하는 것이 좋습니다. 배열이란? …
엑셀의 새로운 기능, 동적 배열 수식
Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다. 동적 배열 수식은 사용하기 간편하고 오류의 가능성도 적고 여러 가지 면에서 장점이 많습니다. …
동적 배열 수식으로 복잡한 수식을 단순하게
Microsoft 365, Excel 2021 버전부터 기존의 배열 수식(레거시 배열수식이라고 함)과는 다른 동적 배열 수식을 사용할 수 있습니다. 동적 배열 수식을 사용하면 기존의 복잡한 수식을 단순하게 만들 수 있고, 절대참조, 혼합참조를 사용하지 …
엑셀에서 값을 입력하고 표시하는 원리
일상생활에서 숫자나 날짜는 동일한 값이라도 표시되는 방식이 다른 경우가 많습니다. 숫자의 경우 천 단위 쉼표를 붙여서 표시하기도 하지만 붙이지 않는 경우도 있고, 날짜의 경우 '2021-09-10', '9월 10일' 또는 '9/10'등 동일한 …
셀서식을 지정하여 원하는 형태로 표시하기
엑셀에서 숫자나 날짜를 원하는 형태로 표시하려면, 예를 들어 숫자 10000을 10,000으로 표시하거나 6/3을 입력하고 2019-06-03으로 표시하려면 셀서식을 지정하거나 TEXT 함수를 사용하는 방법이 있습니다. 이번 글에서는 셀서식을 사용해서 원하는 형태로 값을 …
엑셀에서 복사하고 붙여넣기의 모든 것
엑셀에서 값을 복사하고 붙여 넣을 때 Ctrl+C, Ctrl+V 만 사용하시나요? 아니면 '붙여넣기 옵션'을 이용해서 다양한 방법으로 사용하고 계신가요? 엑셀에는 값을 복사해서 붙여넣을 때 우리가 잘 모르는 '붙여넣기 옵션'이 많이 있습니다. …
엑셀 자동 채우기의 모든 것
엑셀은 데이터를 효율적으로 빠르게 입력할 수 있는 여러가지 방법을 제공합니다. 그 중에서 '자동 채우기' 기능은 반복적으로 입력하는 값의 패턴을 인식하여 자동으로 값을 입력해 주는 기능입니다. 이 기능을 이용하면 단순, 반복적인 …
엑셀 상대참조/절대참조/혼합참조 완벽 정리
엑셀의 상대참조/절대참조/혼합참조를 알아보기 전에 '참조'의 개념을 간단히 살펴보겠습니다. 엑셀에서 '참조'란 값을 직접 입력하는 것이 아닌 '어딘가를 가리키는 것'을 말합니다. 아래 그림과 같이 [A1] 셀에 숫자 1000이 입력되어 있을 때 [A3]셀에 …
엑셀에서 다른 시트 참조하기
엑셀에서 다른 시트를 참조하기 엑셀에서는 참조를 할 때 같은 시트의 내용 뿐만 아니라 다른 시트의 내용도 참조할 수 있습니다. 지역별, 월별 자료를 각각 다른 시트에 관리하고 있고 이 자료를 하나의 …
엑셀에서 3차원 참조로 한번에 자료 집계하기
엑셀에서 3차원 참조란? 엑셀에서 3차원 참조는 다음 그림과 같이 여러 시트에 있는 값을 하나의 시트로 집계할 때 참조하는 방식입니다. 만약 3차원 참조를 사용하지 않고 집계를 한다면 다음과 같이 시트별로 …
다른 엑셀 파일을 연결하여 한번에 실적 집계하기
엑셀에서 자료를 집계할 때 '다른 시트'의 내용을 참조해서 자료를 집계하는 방법을 이용할 수도 있지만 '다른 파일'을 연결해서 자료를 집계하는 방식도 있습니다. 엑셀 파일을 목적별로 나누어서 관리하고 집계용 파일을 따로 만들어서 …
엑셀 피벗테이블 디자인(스타일 적용/수정/새로 만들기)
엑셀의 피벗테이블 "디자인" 메뉴의 "피벗 테이블 스타일"에서는 피벗테이블의 내용은 손대지 않고 원하는 대로 스타일을 다양하게 바꿀 수 있다. 만약 기존의 스타일이 마음에 들지 않을 경우에는 스타일을 수정하거나 새로 만들 수 …
엑셀 피벗테이블 디자인(압축형식,개요형식,테이블형식으로 표시)
이번 강에서는 엑셀 피벗테이블 디자인메뉴에서 "보고서 레이아웃"을 조정하는 방법을 알아보자. 보고서 레이아웃은 압축형식,개요형식,테이블형식으로 조정할 수 있고, 그룹 항목의 레이블을 반복하거나 그룹 항목 사이에 빈 행을 삽입할 수도 있다. 피벗테이블 디자인 …
엑셀 피벗테이블 디자인(부분합,총합계 설정)
이번 강에서는 엑셀 피벗테이블 디자인메뉴에서 "부분합"과 "총합계"를 설정하는 방법을 알아보자. 피벗테이블 디자인 메뉴 엑셀에서 피벗테이블을 만들고 난 후, "디자인" 메뉴에서 부분합, 총합계를 포함하여 많은 부분을 바꿀 수 있다. 피벗테이블 …
엑셀 피벗테이블 - 기본사용법
들어가기 엑셀의 피벗테이블은 데이터 집계, 분석 등 아주 강력한 기능을 제공한다. 사용하기 쉬운데도 불구하고 많은 사람들이 어려워 하는데 사실 그렇지 않다. 조금만 익히면 피봇테이블이 선사하는 신세계를 경험할 수 있으며 많은 …
엑셀 오류 유형(#DIV/0!, #N/A, #NAME?, #REF! 등)과 해결방법
엑셀에서 함수나 수식을 입력하다 보면 가끔 #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #VALUE!, #SPILL!, #CALC! 와 같이 "#"이 붙으면서 이상한 값이 표시되는 경우가 있습니다. 내가 입력한 수식에 문제가 있다는 뜻입이다. 각각의 오류를 …