첨부파일 수식삭제방지.xlsx 파일 다운로드 엑셀 식을 사용하다 보면 미리 식을 입력해두는 경우가 있지요. 이런 경우 시트의 다른 데이터들은 삭제해도 식은 삭제되지 않도록 보호 조치를 취해둘 필요가 있습니다. 다음의 예제 시트를 보세요. 주문번호, 수량, 단가까지 입력하면 금액은 자동 표시됩니다. 이것은 금액 셀들에 계산식을 미리 입력해두었기 때문입니다. E5 셀에 다음의 식을 입력한 후, 채우기 핸들을 잡고 E16 셀까지 드래그합니다. =IF( D5="", "", C5*D5 ) 이 식은 D5 셀에 아무것도 없으면 E5 셀에 아무것도 표시하지 않고 아니면 즉, D5 셀에 내용이 입력되면 C5*D5의 결과 값을 표시합니다. 다시 말해서 단가가 비었으면 금액도 비어있고 단가가 입력되면 비로소 금액이 계산되어 표시되는 것입니다. 엑셀에서 자동화 서식을 만들 때 많이 사용되는 식의 형식입니다. 이런 식은 한 가지 조심할 것이 있습니다. 시트의 내용을 지울 때 주문번호, 수량, 단가는 지워도 되지만 식이 들어 있는 금액 셀들은 지우면 안 되지요. 특히 시트를 개발하는 사람과 사용하는 사람이 다른 경우 이런 사고가 자주 발생합니다, 금액 셀들을 절대 삭제하지 못하게 하려면 [시트 보호]를 해두면 됩니다. [시트 보호]는 다음과 같은 2가지 사항을 기억해야 합니다. 모든 셀은 기본적으로 [잠금] 상태이다. [시트 보호]는 [잠금] 상태의 셀에만 ...

첨부파일 부분일치.xlsx 파일 다운로드 다음의 예제 시트를 봅시다. 왼쪽의 데이터 목록에서 B열에 기술된 지역 이름은 같은 지역이라도 동일하지 않습니다. 그러나 "서울", "부산", "광주"와 같이 핵심적인 지역 이름은 모두 포함하고 있습니다. 그림과 같이 G2 셀에 "서울"이라는 핵심 지역 이름을 입력하면 "서울"의 데이터가 표시되어야 합니다. 우리는 VLOOKUP, INDEX, FILTER 등 데이터를 찾는 3개의 함수를 동원하여 작업해 볼 것입니다. 이렇게 텍스트를 구성하는 문자 중 일부 문자만을 언급할 때 "부분 문자열"이라는 용어를 사용합니다. 부분 문자열을 처리할 때는 대개 와일드카드 문자(Wildcard Character)인 "*"를 사용합니다. "*" 문자는 0개 이상의 임의의 문자를 의미합니다. 서울* : "서울"로 시작하는 모든 텍스트. "서울"만 있어도 됨. *서울 : "서울"로 끝나는 모든 텍스트. "서울"만 있어도 됨. *서울* : "서울"이 있는 모든 텍스트. "서울"만 있어도 됨 하지만 엑셀 함수는 모두 "*" 문자의 사용을 허용하지는 않습니다. 그래서 우리의 작업에서도 VLOOKUP 함수에서만 "*" 문자를 사용했습니다. 또한 VLOOKUP 함수를 사용하면 일치하는 제일 첫 번째 값만 반환한다는 한계가 있습니다. 이 시트에는 다음과 같이 이름이 정의되어 있습니다. F5 셀에 다음의 식을 입력합니다. =V...

첨부파일 복수목록.xlsx 파일 다운로드 동일한 구조의 데이터 목록이 여러 개 있고 그 데이터 목록들 중 하나를 선택하여 특정 값을 찾아오는 작업은 필자가 이전에도 포스팅한 적이 있습니다. 그 포스트들은 이 포스트 제일 뒤에 링크를 걸어두겠습니다. 이 포스트에서는 살짝 다른 문제를 해결하는 경우를 살펴보면서 또한 엑셀 2021, 365 버전에 새로 제공되는 함수들을 활용하는 방법을 살펴볼 것입니다. 이 작업에 사용되는 FILTER 함수는 엑셀 2021 버전부터 제공되며, CHOOSECOLS 함수는 엑셀 365 버전부터 제공됩니다. INDIRECT 함수와 VLOOKUP 함수는 모든 버전에서 사용할 수 있습니다. 다음의 예제 시트를 보세요. B열과 C열에는 지역별로 3개의 데이터 목록이 있습니다. E열과 F열에 "구분"과 "지역"을 입력하면 해당 데이터 목록을 찾아가 "인원"을 가져와야 합니다. 여기서는 이 작업을 크게 3가지 함수식으로 해결했는데 먼저 위쪽 테이블부터 봅시다. 위쪽 테이블은 아래와 같이 3개의 이름만 정의해서 함수식을 작성했습니다. 이 이름들은 각 데이터 목록 전체에 부여된 이름입니다. G3 셀에 다음의 식을 입력하고 채우기 핸들을 더블클릭합니다. =FILTER(CHOOSECOLS(INDIRECT(F3),2), CHOOSECOLS(INDIRECT(F3),1)=E3) 이 식을 분해해 봅시다. INDIRECT(F3) IND...

바로 이전 포스트에서 엑셀 셀에 기록된 텍스트 내에 포함되어 있는 숫자와 일부 문자의 개수를 세는 작업을 했습니다. 이전 포스트에서는 {0,1,2,3,4,5,6,7,8,9}와 같이 배열 상수를 직접 기술하면서 식을 작성했습니다. 이전 포스트를 보신 분이 이런 질문을 하셨습니다. "텍스트 내의 알파벳 문자의 개수를 세려면 {"a", "b", "c", ... ,"z"}와 같이 모든 알파벳 문자를 기술해야 하는가?" "대소문자는 어떻게 처리하는가?" 그래서 좀 더 확장해서 대문자, 소문자, 공백, 특수문자까지 개수를 세는 작업을 해보았습니다. 내친김에... 하다 보니... 숫자를 세는 방법도 다른 방법을 사용해 보고... 이전 포스트를 보지 않으시고 이 포스트를 보시는 분은 가능한 이전 포스트를 먼저 보실 것을 권합니다. 이전 포스트에서는 배열상수를 직접 기술했지만 여기서는 아스키코드(Ascii Code)를 활용하는 방법을 사용합니다. 다음의 예제 시트를 보세요. B열의 텍스트를 대상으로 대문자, 소문자, 숫자, 공백, 특수문자의 개수를 구했습니다. 숫자의 개수를 구하는 작업은 이전 포스트에서도 했으나 여기서는 배열상수를 직접 기술하는 대신 식이 다소 복잡하지만 아스키코드를 사용하는 방법을 보여드리고자 했습니다. 이 작업을 위해서 미리 살펴봐야 할 것들이 있습니다. 예제 파일의 "Sheet2"를 참고하세요. 첫 번째는 "" 문자의 의미입니...