본문 바로가기

Excel/엑셀 팁(TIP)84

엑셀] 함수로 시스템 정보 구하는 방법 엑셀 함수를 이용해서 시스템 정보를 얻는 방법. 엑셀의 info함수는 시스템과 운영체제에 대한 정보를 반환하는 함수이다. 함수 인수는 아래 그림과 같다. 사용예는 아래 그림이다. 그런데 실제로 사용해보면 도움말과 다르게 directory가 파일이 저장되어 있는 현재의 디렉토리가 아니라엑셀 옵션에 저장된 기본 로컬 파일 위치를 반환한다. (도움말에는 현재 디렉토리라고 해놓구서는....) (여기 있는 기본 로컬 파일 위치에 대한 값을 반환한다) 파일이 저장된 디렉토리에 대한 정보는 cell함수를 사용하는 것이 더 정확한듯하다. - 엑셀] 파일 경로와 파일이름 표시하기 위 info함수의 결과중에서 엑셀 버전 16.0은 엑셀2014 버전을 의미하고, 운영체제 pcdos는 .. 2016. 7. 2.
엑셀TIP] 자동필터에도 변경되는 일련번호(연번) 붙이기 엑셀 보고서에 일련번호(연번)을 주면서 자동필터나 숨기기로 행이 변경되더라도 자동으로 연번이 수정되도록 함수를 이용해보자. AGGREGATE 함수를 이용해서 숨겨진 행은 무시하고 계산하도록(counta) 해보자. 첫번째 인수인 기능(function)은 "3 - COUNTA"를 선택하고 두번째 인수인 옵션은 "5 - 숨겨진 행 무시"를 선택한다. 세번째 참조값은 "$B$2:B2"를 입력한다 (절대주소 B2로부터 카운트) 그리고 자동채우기 핸들을 제일 아래까지 드래그한다. 자동필터로 행정, 기술직만 조회해도 연번이 자동으로 조정된다. 연번 수정은 중간에 있는 행을 숨기기해도 자동으로 조정된다. - 엑셀] 병합된 셀에 일련번호 넣기 - 엑셀] 에러(오류) 값 제외하고 계산하기. (Aggrega.. 2016. 6. 30.
엑셀] 에러(오류) 값 제외하고 계산하기. AGGREGATE함수에 대해 알아본다. Aggregate함수는 숨겨진 행이나 오류값을 무시하고 계산할 수 있는 옵션을 주는 함수로 엑셀2010부터 제공된다. Subtotal함수의 진화형(?) 같은 함수. ^^ 일단 아래 그림을 보자. vlookup함수로 단가를 구하는 표인데 code_005를 찾을 수 없어서 #N/A에러가 난 상태이다. 이럴 경우 합계도 제대로 계산할 수 없다. aggregate함수로 오류값을 무시하고 합계를 구하도록 해보자. 합계를 구하는 셀에 "=aggregate("까지 입력하면 사용할 함수를 고를 수 있다. 여기서 9번 sum을 선택한다. 두 번째로 어떤 옵션으로 계산할 것인지 선택할 수 있다. 여기서는 6번 오류값을 무시를 선택한다. 오류값을 제외하고 합계를 계산할 수 있다. ag.. 2016. 6. 30.
엑셀TIP] 병합된 셀에 일련번호 넣기 엑셀을 이용해서 데이터를 관리할 때는 상관없지만, 보고서 등을 만들 때 중복되는 값은 병합해서 표를 작성하는 경우가 많다. 이 경우 연번(일련번호)을 작성할 때의 방법에 대해 알아본다. 아래 그림같이 보고서를 만들려고 할 때, 병합한 셀마다 행의 숫자가 다르다. 1번을 입력한 셀의 오른쪽 아래 자동채우기 핸들을 끌어서 작성해보려고 하면 다음과 같은 에러가 나타난다. 즉, 바꿔 말하면 병합한 셀의 행 숫자가 모두 같다면 자동채우기로 작성할 수 있다는 뜻이다. 아래 그림처럼 병합한 셀이 모두 3행씩 동일한 경우라면 자동채우기 핸들로 일련번호를 채울 수 있다. 다시 앞의 조건으로 돌아와서 COUNTA함수를 이용해서 일련번호를 작성해보자. 일련번호가 들어가는 전체 범위를 블록으로 잡고 수식을 "=COUNTA($.. 2016. 6. 29.
엑셀] 도형에 셀값을 텍스트로 입력하기 엑셀은 파워포인트처럼 자유롭지는 않지만 도형을 이용해서 시각적인 자료 작성이 가능하다. 이 도형을 텍스트 상자로 만들고 여기에 셀 값을 입력하는 것도 가능하다는 것은 모르는 사람이 더 많은 듯 하다. (알고 있지만 별로 쓰임새가 없는지도 모르겠다. ㅋ) 오늘은 도형에 수식 결과 값을 넣는 방법을 살펴보자 적당한 도형을 선택해서 배치한다. 도형에서 [오른쪽 버튼 - 텍스트 편집]을 선택한다. 텍스트 편집 상태가 되면 도형에 텍스트 입력 커서가 깜빡거린다. 상단 수식 입력줄에 "="을 입력하고 합계가 집계되는 F7셀을 클릭한다. 셀 주소는 자동으로 $F$7의 절대주소로 변경된다. 이제 F7의 계산값이 도형에 표시된다. 적당한 글꼴과 글자크기, 중앙정렬 시키면 된다. 표의 계산값이 달라지면 도형의 값도 함께 .. 2016. 6. 26.
엑셀] 셀에 파일 경로와 파일이름 표시하기 이 블로그 포스팅은 엑셀의 셀에 파일의 경로와 파일 이름을 표시하는 것데 대한 포스팅이다. 엑셀의 제목표시줄에 파일 경로를 표시하기 위한 내용은 아래 블로그 글 참조 - 엑셀] 제목표시줄에 파일 경로 표시하기 엑셀에서 파일의 경로 또는 파일이름을 표시하는 가장 간단한 방법은 이전 블로그 글처럼 머리글이나 바닥글에 표시하는 방법이다. - 엑셀] 머리말/꼬리말에 파일 경로 이름 표시하기 머리글이나 바닥글을 사용하지 않을 때는 cell 함수를 이용해서 파일 경로와 이름을 구하는 방법을 사용해야 한다. cell 함수 cell함수는 엑셀 파일, 엑셀 셀에 대한 정보를 구하는 함수이다. 사용방법은 cell(info_type, 참조영역)으로 cell함수가 정보를 구할 수 있는 옵션(info_type)은 다음과 같다... 2016. 6. 25.
엑셀TIP] 여러 워크시트에 데이터를 동시에 입력하기 엑셀 작업하면서 자주 있는 일은 아니지만 여러 워크시트에 데이터를 한 번에 입력하고 싶을 때가 있다. 하나의 시트에 원하는 양식을 만들어두고 그 양식을 복사해서 여러 워크시트를 사용하다 일괄적으로 수정해야 할 필요가 있을 때를 생각해보자. 아래 그림은 거래명세표를 엑셀로 만들어놓고 워크시트를 복사해서 날짜를 워크시트 이름으로 사용하는 경우다. 그런데 아뿔사~ 복사하는 원본에 "계산"이 "게산"으로 오타가 있다는걸 나중에 발견했다면???? ^^ 한번의 작업으로 모든 시트의 데이터를 한꺼번에 바꿔보자. 그리고 제목인 거래명세표의 글꼴도 궁서체로 바꿔보자. 진지하게. ^^;; 먼저 워크시트를 Ctrl키와 함께 하나씩 클릭해서 모두 선택한다. 워크시트를 모두 선택하면 제목표시줄에 [그룹]이라고 표시되면서 워크시.. 2016. 6. 17.
엑셀TIP] 여러 워크시트 한 화면에서 같이 보기 한 엑셀파일에서 워크시트를 여러개 만들어서 작업할 때 시트를 화면에 모두 띄워놓고 작업하는 방법 워크시트를 이동하는 단축키로 Ctrl + PgUP(앞쪽 워크시트로 이동), Ctrl + PgDN(뒤쪽 워크시트로 이동)을 사용하는 방법도 있지만 데이터를 확인하면서 작업해야 할 경우는 시트들을 한 화면에서 보는게 더 낫다. 아래 파일처럼 워크시트가 2개 있는 경우를 예를 들어보자. [보기 리본 - 새 창]을 클릭한다. 그러면 아래 그림처럼 엑셀 맨 위 제목표시줄에 "파일명:1", "파일명:2"라고 표시되면서 엑셀 창이 하나 더 열린다. (이 샘플의 엑셀 파일 이름이 test01이다) 드래그해서 적당한 위치에 놓고 작업하거나 [보기 리본 - 모두 정렬 - 세로]를 선택해서 화면 전체에 두 파일을 위치시킬 수도 .. 2016. 6. 16.
엑셀TIP] 복사해서 붙일 때 높이, 너비도 동일하게 엑셀에서 표를 복사해서 붙일 때 낭패스러울 때가 있다. 이쁘게 높이와 너비를 맞춰서 편집해놨는데 복사 붙이기를 하면 기본 셀 높이와 넓이에 데이터만 쑤셔넣은 것처럼 나타나기 때문이다. (텍스트는 글자가 잘리고 숫자는 자리가 모자라서 ######로 표시된다) 이번 블로그 글은 이런 경우를 위한 엑셀 팁. 높이와 너비도 같이 복사하는 방법이다. 높이만 맞추면 되는 경우는 복사할 때 행번호를 드래그해서 복사한다. (위 그림에서는 행번호가 아니라 데이터 영역만 드래그해서 복사한 경우이다) (행 높이는 복사 전 후 동일하다) 열 너비도 동일하게 복사하기 위해서는 붙여넣기 할 때 마우스 오른쪽 버튼을 클릭하고 [선택하여 붙여넣기 - 원본 열 너비 유지]를 선택한다. - 엑셀] 폭이 다른.. 2016. 6. 14.

티스토리툴바