스마트하게 일하기

"엑셀 나누기 함수 나머지값 구하기" 오늘은 아주 간단한 나눗셈에 대해 포스팅 해보겠습니다. A를 B로 나누고 난 후 몫을 제외한 나머지값을 구하는 방법입니다. 예를들어 53을 8로 나누면 답은 6.63 입니다. 엑셀 수식으로 표현하면, 이렇게 계산하면 소수점 자리수까지 계산이되어집니다. 그런데, 때때로 몫과 나머지를 구해야 할 경우가 있습니다. 53을 8로 나누면, 몫은 6이고 나머지는 5 입니다. 여기에서 몫은 ROUNDDOWN 함수로, 나머지는 MOD 함수로 구할 수 있습니다. 자주는 아니지만, 가끔은 꼭 필요한 몫과 나머지 구하기 포스팅 마칩니다.~

"문서의 끝라인 수 찾기" 매크로 중 For 구문 사용 시 반복횟수를 지정하는것이 아닌 특정 Sheet의 열의 개수만큼만 반복해야할 때가 있습니다. [02편 - For 구문 사용] https://howitworks.tistory.com/33 2편에서 생성한 매크로를 불러오면, --------------------------------------------------------------Sub 시트추가하기() Dim i as integer= i 라는 정수 변수를 선언 For i = 1 To 100 Step 1 = i 를 1부터 100까지 1단위로 증가하여 반복 Sheets.Add After:=Sheets(Sheets.Count) = 신규 Sheet를 추가하기 ActiveSheet.Name = "시트" & ..

"엑셀 매크로 For 구문 사용" 매크로를 쉽게 자동생성하는 방법은 아래 1편을 참고하세요. [01편 - 매크로 자동생성] https://howitworks.tistory.com/32 오늘은 1편에 이어서 특정 매크로를 원하는 횟수만큼 반복하는 For 구문을 사용해보겠습니다. 우선 1편에서 생성한 매크로 중 일부를 불러오면, --------------------------------------------------------------Sub 시트추가하기() Sheets.Add After:=Sheets(Sheets.Count) = 신규 Sheet를 추가하기 ActiveSheet.Name = "시트1" = 신규 Sheet의 이름을 "시트1"로 변경 End Sub -------------------------..

"엑셀 매크로(Macro) 기록기능 활용하기" 오늘은 엑셀 기능 중 매크로(Macro) 기능 활용에 대한 내용입니다. 매크로는 단순 반복작업이 필요할 때 유용합니다. 여러명의 작업자가 만든 개별파일들의 내용을 하나의 파일 또는 하나의 시트로 Merge(통합)할 경우나, 어떤 규칙에 의해 단순반복작업을 해야 할 경우 작업시간을 현저하게 단축시켜 줍니다. 단, Visual Basic 프로그래밍 언어의 이해와 엑셀 매크로에서 제공하는 함수에 대한 이해가 있어야 합니다. 이를 손쉽게 따라할 수 있는 방법을 얘기해보고자 합니다. 바로 "매크로기록" 기능입니다. 엑셀 상단 리본메뉴에서 "보기 > 매크로 > 매크로기록"을 선택합니다. 매크로기록을 선택한 이후의 모든 작업은 기록되고, 매크로 실행 시 동일한 작업이 자..

"엑셀 암기장(매크로 버전)" 초딩 중딩 딸들을 위해 만든, 엑셀 매크로 기능을 이용한 암기장입니다. (아래 링크) https://bottari7.tistory.com/186 사자성어 암기를 해야한다기에... 급하게 만들어줬지만~ 결국 영어 암기장까지 포함해서 업그레이드 했습니다. 영어 "숙어"를 하다보니... 4지선다를 해야겠기에 시험기능 추가~ 단어로 뜻맞추기~ 뜻보고 단어맞추기~ 이상 끝~ ^^ 자세한 설명은 아래 링크에서... https://bottari7.tistory.com/186

엑셀에서 COUNT 만큼 많이 사용하는 SUM 함수 입니다. SUM 함수는 SUM 자체로도 많이 사용하고, SUMIF도 많이 사용합니다. 빈도수는 낮지만, SUMIFS도 꼭 필요할때가 있습니다. [SUM (합계 구하기)] 이런 표가 있다고 가정하고, Score 의 SUM을 구하기 위해서는 엑셀 상단 리본메뉴에서 "수식 > 함수삽입"을 선택하여, 1. 함수 SUM을 선택합니다. 2. 합을 구하고자 하는 영역을 선택합니다. "=SUM(E3:D15)" 2-1. 위와같이 선택되어지고, 2-2. Score의 합계가 구해집니다. [SUMIF (조건부 합계 구하기)] 동일한 표에서, 국가별로 Score의 합계를 구하고자 하면, 3. 함수 SUMIF 를 선택합니다. 4. B열이 "일본"인 경우의 SUM을 구하는 수식입..

"엑셀 셀 안에 차트 그리기" 엑셀을 하다보면, 목록에 따른 차트를 그려야 할 때가 있습니다. 이런 표가 있다고 가정할때, 차트그리기로 그리면 한눈에 들어오고 차트도 이쁘게 그려집니다. 단점은, 표 안에 다른 정보가 있을 경우 표 따로 차트 따로 봐야한다는 단점 정도 되겠네요. 이 때 간단하게 셀 안에 차트를 그려넣을 수 있습니다. 1. 차트를 넣고자 하는 셀을 넓히고, 셀선택을 합니다. 2. 조건부서식 > 새규칙을 선택 합니다. 3. 서식 스타일을 "데이터 막대"로 선택 합니다. 종류 = 숫자, 백분율 등 선택 채우기, 색, 테두리 등을 조정 4. 셀 안의 수치에 따라 변하는 셀 안의 간단 차트가 그려집니다.

"엑셀 세로쓰기" 오늘 포스팅은, 엑셀의 한 셀 안의 텍스트를 세로쓰기 할 수 있는 방법입니다. 아래와 같은 표가 있다고 가정하고, 이런형태나, 이런형태로 변환이 가능합니다. 1. 세로로 바꾸려는 셀을 선택하고, 2. 마우스 우클릭 후 "셀 서식(F)"을 클릭합니다. 3. "맞춤" 탭에서 오른쪽에 있는 방향으로 텍스트의 기울기를 조정할 수 있습니다. 4. 마우스로 빨간점을 이동해서, 약 45도 정도로 기울이면, 5. 이렇게 기울어지고, 6. 마우스로 빨간점을 90도로 가져가면, 7. 90도로 글자가 기울어집니다.! 모르면 어렵지만~ 알면 쉬운!~ 간단한 엑셀 세로쓰기 팁이었습니다.~

"엑셀 파란글씨만 COUNT 하기" 갑자기 쌩뚱맞은 포스팅 입니다.~ 오늘 티스토리 통계를 보다가 발견한 유입로그 입니다. ^^ "엑셀 파란글씨만 COUNT" 라는 문구가 너무 재미있어서, 만들어봤습니다. 함수로 어찌어찌 해보려다가.... 어쩔 수 없이 엑셀 매크로를 사용했습니다. 절반의 성공이네요. 다음엔 함수로 한번 도전해보겠습니다.! 1. 위와같은 영역의 표가 있다고 가정합니다. 2. 아무셀에나 "=" 를 입력하고, my를 입력하면, 제가 만들어놓은 함수 세개가 보입니다. 일반 함수처럼 사용하면 됩니다. 1) =MyBoldC(A) 설명 : 선택된 영역 내 "굵은글씨"를 Count 합니다. - A : Count하고자 하는 범위 2) =MyPicCellC(A,B) 설명 : 선택된 영역 내 B와 같은 셀..

1편에 이은 엑셀 [필수함수 2편] 입니다. [필수함수] = 외우고 있어야 할 것 [유사/활용 함수] = 필수 함수로 유추 가능하거나, 연결하여 활용할 수 있는 것 [찾아쓰는 함수] = 외울필요 없고, 필요할 때 찾아쓰면 되는 것 필수함수 (2/2) 함수 함수설명 REPLACE 텍스트의 일부를 다른 텍스트로 바꿉니다. ISERROR 값이 오류(#N/A, #VALUE!, #REF!, #DIV/O!, #NUM!, #NAME? 또는 #NULL!)인지 확인하고 TRUE 또는 FALSE를 반환합니다. TRIM 텍스트의 양 끝 공백을 없앱니다. EXACT 두 텍스트 값이 같은지 검사하여 같으면 TRUE를 돌려주고, 다르면 FALSE를 돌려줍니다. 대/소문자를 구분합니다. RAND 0보다 크거나 같고 1보다 작은, ..

엑셀 [필수함수 1편] 입니다. 엑셀에는 약 400여종의 함수가 있습니다. 그 중 사용빈도가 높은 함수 16개를 추려서 정리해 봤습니다. [필수함수] = 외우고 있어야 할 것 [유사/활용 함수] = 필수 함수로 유추 가능하거나, 연결하여 활용할 수 있는 것 [찾아쓰는 함수] = 외울필요 없고, 필요할 때 찾아쓰면 되는 것 필수함수 (1/2) 함수 함수설명 IF 논리 검사를 수행하여 TRUE나 FALSE에 해당하는 값을 반환합니다. COUNTIF 지정한 범위 내에서 조건에 맞는 셀의 개수를 구합니다. SUM 인수들의 합을 구합니다. ROUND 수를 지정한 자릿수로 반올림합니다. MID 문자열의 지정 위치에서 문자를 지정한 개수만큼 돌려줍니다. LEN 텍스트 문자열 내의 문자 개수를 구합니다. VLOOKUP..

오늘 내용은, 엑셀 함수에 대한 얘기라기보다, 엑셀 셀서식에 대한 내용입니다. 두개의 노란색 셀에는 동일한 수식이 들어가 있습니다. 하지만 위에는 SUM이 계산되어서 "705"라는 값을 표시해주고 있고, 아래는 "=SUM(D3:D9)" 라는 수식이 텍스트로 표시되고 있습니다. 아래 노란색 샐을 선택하고, 마우스 우클릭을 한 후, "셀 서식"을 클릭 합니다. 셀 서식이 "텍스트"로 지정되어 있습니다. 셀서식이 "텍스트"인 경우 수식이나 숫자를 입력해도 엑셀은 텍스트(문자)로 인식합니다. 해결은 간단합니다. 셀서식을 "일반"이나 "숫자"로 선택하고, 확인을 누르면 됩니다. 그럼에도... 아직도 문자로 보입니다. "F2" 키를 눌러 편집모드로 들어간 상태에서 그냥 엔터를 한번 쳐줍니다. 수식이 정상적으로 계산..

엑셀의 모든 셀은 속성(서식)을 갖습니다. "일반" 일 경우 숫자면 숫자, 문자면 문자의 특성을 가지며, 숫자끼리 있는 셀에 대해 SUM 이나 AVERAGE 등의 함수를 사용하면, 총합이나 평균을 자동 계산해줍니다. 속성은 숫자, 통화, 날짜, 백분율 등 다양한 포맷으로 사용합니다. 오늘은 엑셀의 셀 서식 중 숫자와 문자 변환방법에 대해서 포스팅 하겠습니다. 위 표의 점수①과 점수②는 눈으로 보기에 동일한 값으로 보입니다. 노란색 셀에는 노란색 수식(SUM)이 들어가 있고, 주황색 셀에는 주황색 수식(AVERAGE)이 있습니다. 그런데 점수①은 SUM과 AVERAGE가 계산되어 숫자로 보이는데, 왜 점수②는 계산이 안된걸까요? 점수② 각 셀의 왼쪽 위에 보이는 녹색 세모뱃지는 "오류표시" 입니다. 오류가..

"엑셀 입력값 제한 (두개 셀 연동 응용)" 두개의 컬럼을 연동하여 입력값을 제한하고자 할 때가 있습니다. 위 표의 대분류에는 "금융", "기타"를 중분류에는 대분류가 "금융"일 때의 갑과, 대분류가 "기타"일 때의 값을 다르게 제한하고자 한다고 가정합니다. 1. 제한하고자 하는 값의 데이터 표를 만듭니다. 2. 리본메뉴에서 "수식 > 이름관리자"를 선택합니다. 3. 새로 만들기를 합니다. 4. 이름을 "대분류"로 하고 참조대상을 대분류 영역을 지정합니다. ※ 대분류라는 이름의 데이터셋이 만들어 졌습니다. 5. 새로만들기를 하여, 대분류명으로 이름을 지정합니다. "금융"을 이름으로 하고, "금융" 대분류 아래 중분류 값을 참조대상으로 영역을 지정합니다. 6. 동일한 방법으로 "기타"이름의 데이터셋도 만듭..

"엑셀 입력값 제한 (데이터 유효성 검사)" 엑셀에서 셀에 값을 입력하려고 할 때 "이 셀에 입력할 수 있는 값은 제한되어 있습니다." 라는 메시지를 접할 때가 있습니다. 해당 셀에 데이터 유효성 검사가 설정되어 있는 상태에서, 유효하지 않은 데이터를 입력할 때 나타납니다. 오류 설명을 위해~ 정상적인 유효성 검사 기능에 대해 우선 설명합니다. 노란색셀 부분에 임의의 데이터들이 입력되어 있는 자료가 있습니다. 보기에 불편하지 않게 노란색 셀에는, "금융", "기타" 두가지로만 입력받고 싶다고 가정합니다. 1. 입력을 제한하고자 하는 셀을 전체 범위를 선택 합니다. 2. 리본메뉴에서 "데이터 유효성 검사"를 선택합니다. ※ 유효성 조건 설정 팝업이 나타납니다. - 제한대상과 제한방법을 설정 3. 제한대상 ..

"엑셀 VLOOKUP 함수" 아래 [표1]의 원자료에서 [표2]의 국가코드(ISO)를 기준으로 국가명을 가져오고자 할 때 VLOOKUP을 사용합니다. 1. J3 셀을 선택 후 함수삽입을 합니다. 2. 함수 중 VLOOKUP을 선택합니다. 3. 네개의 변수를 입력받습니다. 4. 각각의 변수는 순서대로... - Lookup_value : 찾고자 하는 값 - Table_array : 참조하는 범위 - Col_index_num : 참조하는 범위의 가져올 값 순서 - Range_lookup : 0 또는 FALSE를 입력 ※ 보통 "정확하게 일치"하는 값을 가져옴 [위 입력값 해석] "I3"의 값을 기준으로 "B:C" 참조범위의 첫번째 열의 값과 비교해서, "I3"와 정확히 일치하면, 참조범위 두번째(2) 열의 값..

엑셀에서 가장 많이 사용하는 COUNT 함수 기능입니다. 사실 COUNT 관련 함수 중 가장많이 사용하는 함수는 그냥 COUNT 함수가 아닌, 1위 : COUNTIF 2위 : COUNTIFS 3위 : COUNTA 4위 : COUNT 순위일 것 같습니다. 위 네가지 COUNT의 차이를 설명하기 위해 아래 표를 먼저 준비해 봅니다. ※ "국가코드"(B열)부터 "지점수"(G열)까지입니다. 즉, "국가"는 C열임 이 표를 이용해서 네가지 COUNT 함수를 각각 사용해 보겠습니다. 엑셀 상단 리본메뉴에서 "수식 > 함수삽입"을 선택하여, "함수"를 선택합니다. 1. COUNTIF 함수 하나의 조건을 줄 수 있습니다. 위 표에서 ex) "일본의 금융기관은 몇개인가?" 범위(Range)와 기준값(Criteria) 두..

엑셀 자료에서 특정 열의 고유값이 무엇무엇이 있는지 알아야할 때 사용할 수 있는 "중복제거" 기능입니다. 이 자료는 국가, 기관별 자료가 있습니다. 몇개국 자료가 있는지는 "국가"컬럼을 보면 됩니다. 총 자료의 수가 수만 Line이라면, 쭉 내리면서 눈으로 보는건 불가능입니다. "중복제거"기능 하나로 몇개국인지 한번에 확인이 가능합니다. 1. 고유하게 보고싶은 자료를 모두 선택하고 Copy 합니다. 2. 복사한 모든 값을 빈 열(K열) 아무곳에나 놓고 붙여넣기 합니다. 상단 리본메뉴 중 3. "데이터 > 중복된 항목 제거"를 선택합니다. 4. 확인을 클릭합니다. 5. 완료!~ 친절하게도.... 제거한 중복된 값 개수와 남은 고유한 값 개수를 알려줍니다.~ 두 수치를 더하면 원자료 555개 입니다.~ 아래..

"엑셀 가로세로 행열바꾸기" 엑셀에서는 자료의 포맷에 따라 활용도 차이가 큽니다. 특히 가로포맷보다 세로포맷이 더 적합합니다. 예를들어, 위 그림과 같은 원천자료가 있을경우, 특수한 경우를 빼고는 필터도 피벗도 사용할 수 없습니다. 외부에서 받은자료나 보고서용 자료의 경우에는 저런 데이터가 생성되곤 하는데, 위와 같이 활용도 높은 세로포맷으로 변환해야할 경우가 종종 생깁니다. 엑셀은 이 작업을 아주 간단하게!! 한번에 할 수 있는 기능이 있습니다. 1. 원천자료 전체영역을 선택 후 복사(Ctrl + C)합니다. 2. 엑셀의 아무 셀이나 하나 선택한 후 마우스 우클릭을 하고, 3. 위 그림의 화살표 모양의 붙여넣기 옵션 "바꾸기"를 선택합니다. 4. 완료!~ 알고나니... 별거 아닌 기능이지만, 모르면 한..

병합되어 있는 셀을 분리하고, 빈셀에 값을 채워 넣어야 하는 경우나 원천자료 자체가 빈칸이 많아서 윗 셀의 값이 아래에 채워졌으면 하는 경우의 처리방법 입니다. 아래 사진과 같이 왼쪽 표 처럼 셀병합이 되어있는 상태를, 오른쪽 표 처럼 손쉽게 채울 수 있습니다. 작업순서를 설명하면, 1. 병합된 셀 영역을 선택합니다. 2. 마우스 우클릭 후 (화살표가 가리키는) "병합하고 가운데 맞춤" 아이콘을 해제합니다. 병합셀이 해제가 됩니다. 국가명은 맨위 하나만 값이 있고, 아래 셀들은 모두 빈칸입니다. 3. 해당영역 선택한 상태에서 "홈 > 찾기및선택 > 이동옵션"에서 빈 셀(K)을 선택하고 확인을 누릅니다. 회색으로 보이는 곳만 선택되어집니다. 빈셀만 선택된 것입니다. 4. '=' 키와 '↑' 키를 순서대로 ..