본문 바로가기

엑셀

VBA에서 판단문(If,Select ~ Case), 순환문(Do ~ Loop) 사용하기 읽으시기 전에 추천 한번 눌러 주시면 많은 힘이 됩니다.~ ㅎㅎ 오늘은 판단문과 순환문에 관해서 알아보겠습니다. 판단문이란? 말 그대로 서브 프로시저처럼 명령어를 한 줄씩 순차적으로 실행하는 것이 아니라 특정 조건 일 때 다양한 작업을 할 수 있도록 구성하는 구문을 말하고, 순환문이란 동일한 조건일 때 연속적으로 어떤 명령어를 실행하는 것을 말합니다. 판단문(If문, Select Case문), 순환문(Do~ Loop문) 을 예시를 통해서 살펴 보기에 앞서 기본 구조를 보겠습니다. 아래 예시를 통해서 판단문과 순환문을 동시에 활용해 보도록 하겠습니다. 우선 노란색 부분의 값을 채울 건데요 E열의 품사필드의 값이 a, v, n, ad 일때 F열의 결과필드에 형용사, 동사, 명사, 부사 이렇게 들어가도록 만들.. 더보기
Sub 프로시저란 무엇인가요? 자~ 추천 한번 눌러주시고 ^^ 시작합니다. VBA에서는 실행 명령어를 프로시저라고 합니다. 그리고 가장 일반적인 프로시저의 형식이 명령을 순차적으로 실행하는 Sub 프로시저 입니다. 아래 그림과 같이 목적에 따라 다양한 형태의 프로시저가 존재하고 기본적인 Sub프로시저의 구조를 확인 할 수가 있습니다. 자 그럼 예제를 한번 보겠습니다. 1. 엑셀을 시작한 다음 단축키 ALT + F11 을 누릅니다. 2. VB편집기 메뉴에서 삽입>모듈메뉴를 선택하고 아래 그림과 같이 입력하고 (열려있는 엑셀 파일의 A1필드에 100이라는 값을 넣으라는 명령) 3. 실행버튼(동그라미부분)을 눌러 결과를 확인합니다. (반드시 코드창의 Sub ~ End Sub 사이에 커서를 위치한 상태에서 실행버튼을 눌러야함) 4. 엑셀파일.. 더보기
SQL문으로 동일한 필드의 개수 구하기 자~ 추천 한번 눌러주시고 ^^ 시작합니다. 이제 SQL문을 활용하여 테이블 데이터를 활용하여 원하는 결과값을 표현하는 방법을 알아볼까요~ 아래 데이터에서 "구"별로 정렬해서 개수가 얼마가 되는지에 대해 알아 보겠습니다. 엑셀을 사용하여 여차여차 구할 수도 있지만.. 쿼리를 사용하면 한번에 결과값이 나오는 것을 보여드리죠~ ㅎ 이제 만들기>쿼리디자인 으로 들어가서 SQL을 입력 할 수 있는 창을 연 후 아래의 SQL문을 SELECT 구, count(*) as 개수 FROM 서울특별시 GROUP BY 구; 짠~ 결과값입니다. 강남구는 21,513개 강동구는 19,229개 등 등 이렇게 깔끔하게 정리되어 나오죠? 엑셀 함수를 사용하여 값을 구하는것과는 비교도 안되게 빠른 결과물을 볼 수 있죠? 문제는 기본 .. 더보기
SQL로 엑세스에서 자료 업데이트하기 자~ 추천 한번 눌러주시고 ^^ 시작합니다. SQL은 데어터베이스를 다룰 때 사용되는 일종의 규칙 또는 언어(?)라고 간단히 말할 수 있겠습니다. 엑세스에서는 기본적으로 쿼리마법사를 통해 SQL을 직접 입력하지 않아도 기본적인 제어는 가능하지만 세밀하게 또는 어떤 의미로 SQL이 사용되는지를 알면 훨씬 수월하게 데이터를 주무를 수가 있게 되겠죠? ^^ 본론에 들어가서 이제 엑세스에서 쿼리마법사가 아닌SQL을 이용하여 필드 값들을 제어하는 법에 대해 알아보겠습니다. 서울특별시 테이블에서 "동"필드의 값 중 청운동 → 청운으로 변경(업데이트)해 보겠습니다. 만들기 메뉴에서 쿼리디자인 메뉴를 선택합니다. 그러면 모든 테이블이 목록에 나타나고 목록 하나를 선택하고(하나밖에 없죠? ㅎ 여러개가 있음 목록에 주~욱.. 더보기
VBA를 이용해 엑셀파일 다루기 VBA를 사용하면 엑셀의 아주 세밀한(?) 부분까지도 제어 할 수 있습니다. 잘만 사용한다면 간단하게 사용할 프로그램도 만들 수가 있고 업무능률은 당연한거고 이번에는 간단하게 엑셀 파일에서 각 시트를 어떻게 제어하는가는 살펴보겠습니다. 여러 방법으로 엑셀 셀의 A1에 1000을 입력해 볼까요? 빈 엑셀 파일을 하나 열어두고 ALT키 + F11를 눌러 작업창을 엽니다. 그 후 직접 실행창에 Range("A1").Value = 1000을 입력후에 엔터키를 누릅니다. 엔터를 누름과 동시에 열려있던 빈 엑셀파일의 Sheet1의 A1필드 값이 자동으로 바뀌는게 보이죠. 그러면 Sheet2의 A1필드에 값을 넣는 법은? Worksheets("Sheet2").Range("A1").Value = 1000 파일이 두개 .. 더보기
엑셀Tip-자릿수에 맞춰서 0으로 값 채우기 특정한 값을 원하는 자릿수에 맞추어서 0값을 채우는 방법입니다. =CONCATENATE(REPT(0,4-LEN(B6)),B6) (4자리수에 맞게 0으로 채운다.) 예) "1"일때 "0001" "102"일때 "0102" =CONCATENATE(REPT(#,5-LEN(B6)),B6) (5자리수에 맞게 #으로 채운다.) 예) "1"일때 "####1" "102"일때 "##102" 더보기
VBA - 간단한 엑셀시트합치기 아주 유용한 VBA 함수 ^^ 여러 엑셀 파일을 하나의 sheet에 합칠 수가 있습니다. Sub 파일병합() Dim Files As Variant Dim FileName As Variant Dim sht As Worksheet Dim wb As Workbook Dim rngTarget As Range Files = Application.GetOpenFilename(filefilter:="total Files(*.*),*.*", Title:="파일선택", MultiSelect:=True) 'Files = Application.GetOpenFilename(filefilter:="Excel Files(*.csv),*.csv", Title:="파일선택", MultiSelect:=True) '여러개 파일이 선택가.. 더보기
엑세스강좌-업데이트강좌 업데이트쿼리는 데이터의 추가 쿼리와는 다르게 존재하는 데이터의 정보를 갱신(업데이트)하기 위한 쿼리입니다.. 1. 시군구의 필드 값이 종로로 되어 있는 것을 종로구로 업데이트 해 보겠습니다. 쿼리디자인 메뉴를 클릭합니다. 2. 그럼 테이블 표시가 나타나죠? 테이블을 선택하고 추가 버튼을 클릭합니다. 3. 업데이트 메뉴를 선택하면 아래 입력창에 업데이트란이 생깁니다. 업데이트(바뀔 내용) : 종로구, 조건 : 종로, 를 입력하고 실행버튼을 누릅니다. 아래와 같이 ① → ② → ③ 의 순서로 따라하시면 됩니다. 4. 실행버튼을 누르면 아래와 같이 업데이트를 할 것인가 묻는 창이 나타납니다. 예(Y)버튼을 누릅니다. 그러면 101개의 행이 업데이트가 됩니다. 5. 결과를 한번 볼까요? 아래와 같이 종로가 종로.. 더보기
엑세스강좌-삭제 쿼리 만들기 쿼리 디자인으로 작업가능한 간단한 쿼리들 중에 삭제쿼리는 기본테이블의 레코드중에 어떤 조건과 일치하는 레코드 정보를 삭제하기 위한 쿼리입니다. 쿼리 디자인 메뉴로 쉽게 작성할 수가 있습니다. ○ 메뉴>만들기 클릭 후 쿼리디자인 클릭 합니다. ○ 그럼 테이블 표시가 아래와 같이 나타나고, 삭제할 데이터가 있는 테이블을 선택하고, 추가를 누릅니다. ○ 선택된 서울특별시의 사본 테이블이 나타나고 테이블의 모든 필드를 눈금영역에 옮겨 놓습니다. (전체 선택 후 드래그 또는 각각의 필드를 더블클릭) ○ 메뉴에서 삭제를 클릭한 후 우리가 삭제를 원하는 도로명을 도로명테이블의 조건 영역에 입력합니다. (우린 "자하문로 36길"을 입력했습니다.) 그런 후 실행 메뉴를 누릅니다. ○ 자하문로 36길의 값을 가진 레코드 .. 더보기
VLOOKUP 사용 예제 데이터를 조회해서 어떤 특정한 값을 빠르게 찾을 수가 있습니다. 엑셀 작박업하면서 아주 많이 쓰는 기능중에 하나죠. 두시트를 비교한다던지... 특정값을 확인한다던지... 등등 처음보면 생소한 함수지만 정말 편리하다는걸 느낄 수 있을 껍니다.~~ VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)range_lookup : false→정확한 값만 true →정확한 값이나 근사값 ○ 정확히 일치하는 값을 사용하여 목록 열에서 값 찾기 ○ 일부만 일치하는 값을 사용하여 목록 열에서 값 찾기 더보기
엑세스강좌-쿼리마법사를 이용한 중복데이터 검색 ① 쿼리 마법사에 있는 중복데이터 검색 쿼리를 이용하면 데이터베이스에서 중복으로 저장되어 있는 자료를 쉽게 검색 할 수가 있습니다. 서울특별시 테이블에서 도로명에 관하여 중복된 값을 검색해 볼까요~? 일단 아래의 붉은 테두리의 쿼리 마법사를 클릭합니다. ② 중복 데이터 검색 쿼리 마법사를 선택 후 확인을 누릅니다. 1 ③ 중복을 검색할 테이블을 선택합니다. 서울특별시를 선택 후 다음을 클릭합니다. ④ 선택한 테이블의 사용가능한 필드 목록에 아래와 같이 표시됩니다. 검색을 원하는 중복된 필드를 "사용 가능한 필드"에서 더블클릭해서 추가시킵니다. 그리고 다음을 클릭합니다. ⑤ 중복검색결과의 쿼리를 원하는 이름으로 입력합니다. 그리고 마침을 누릅니다. 1 1 ⑥ 아래와 같은 결과값이 나옵니다. 아주~ 간단 하.. 더보기
엑세스강좌-다중검색(and, or)조건 설정하기 간단한 다중 검색 방법입니다. ^^ ① 메뉴에서 쿼리디자인 아이콘을 클릭합니다. ② 테이블 표시가 뜨고, 관련 테이블들을 선택 후에 추가 버튼을 누릅니다. ③ 선택된 필드가 나타나죠? 그러면 선택된 테이블에서 나중에 결과 값으로 보여질 데이터 필드를 선택합니다. ④ 조건 , 또는 필드에 아래와 같이 입력한니다. 조건필드에서 나란히 입력된 필드의 값들은 결과값을 얻을 때 AND(모두포함)조건으로 데이터를 검색하구요. (ex 수성구, 달구벌대로는 모두 포함되어 있어야 됨) 아래, 위로 입력된 값들은 OR(조건중에 하나만 맞으면 됨) 조건으로 검색됩니다. (ex 수성구, 중구중에 하나만 포함되어 있으면 됨) ⑤ 자이제 실행을 누릅니다. 두둥~~ ⑥ 결과 값이 나오조~ 대구광역시 테이블에서 수성구 이고(AND).. 더보기
엑셀 텍스트 함수 총정리 ○ ASC(text) －설명 : 문자열에서 영문 전자(더블바이트)나 가타가나 전자를 반자(싱글바이트)로 바꿉니다. －구문 : text 변경할 텍스트 또는 텍스트가 포함된 셀의 값 ○ BAHTTEXT(number) －설명 : 숫자를 태국어 텍스트로 변환하고 "Baht" 접미사를 추가합니다. －구문 : number 통화 형식을 사용하여 숫자를 텍스트로 변환합니다. ○ CHAR(number) －설명 : 코드 번호에 해당하는 문자를 반환합니다. －구문 : number 원하는 문자를 지정하는 코드 번호로서 1에서 255 사이의 숫자입니다. 이 문자는 컴퓨터에서 사용하는 문자 집합에 속합니다. ○ CLEAN(text) －설명 : 인쇄할 수 없는 문자를 텍스트에서 모두 제거합니다. －구문 : text 인쇄할 수 없.. 더보기
엑셀 함수 활용-대소문자바꾸기 엑셀서식에서 대소문자를 바꾸어야 하는 경우가 종종 있죠.. 간단하게 3가지 함수만 알면 아주 쉽게 작업할 수 있습니다. 한번 확인해 볼까요.. 대문자를 모두 소문자로 바꾸는 경우(lower), 소문자를 모두 대문자로 바꾸는 경우(upper), 단어의 첫째 문자와 영문자가 아닌 문자 다음에 오는 영문자를 대문자로 변환하는 경우(proper), (나머지 문자들은 모두 소문자로 변환합니다.) LOWER(text) text : 소문자로 변환할 문자열입니다. 텍스트에서 글자가 아닌 문자는 변환되지 않습니다. UPPER(text) text : 대문자로 변환할 텍스트입니다. text는 참조 또는 텍스트 문자열일 수 있습니다. PROPER(text) text : 따옴표로 묶인 텍스트, 텍스트를 반환하는 수식 또는 일부.. 더보기
엑셀 함수 활용-MATCH 지정된 값을 지정된 순서로 일치시키는 배열에서 항목의 상대 위치를 반환합니다. 항목 자체가 아니라 범위에서의 항목 위치가 필요한 경우 LOOKUP 함수 중 하나를 사용하는 대신 MATCH 함수를 사용합니다. ○ 구문 MATCH(lookup_value,lookup_array,match_type) lookup_value 테이블에서 찾으려는 값입니다. lookup_array 찾으려고 하는 값이 포함된 인접한 셀들의 범위입니다. lookup_array는 배열 또는 배열 참조여야 합니다. match_type -1, 0, 1 세 가지가 있습니다. 1 : 이면 MATCH는 lookup_value보다 작거나 같은 값 중에서 최대값을 찾습니다. 0 : MATCH는 lookup_value와 같은 첫째 값을 찾습니다. -1.. 더보기
엑셀 함수 활용-SUBSTITUTE 문자열에서 old_text를 new_text로 바꿀때, 문자열의 특정 텍스트를 바꾸려면 SUBSTITUTE를 사용합니다. 문자열의 특정 위치에 있는 텍스트를 바꾸려면 REPLACE 함수를 사용합니다. ○ 구문 SUBSTITUTE(text,old_text,new_text,instance_num) text 문자를 대체할 텍스트가 포함된 셀의 참조 또는 텍스트입니다. old_text 바꿀 텍스트입니다. new_text old_text를 대신할 텍스트입니다. instance_num text에서 몇 번째에 있는 old_text를 new_text로 바꿀 것인지를 지정하는 수입니다. instance_num을 지정하면 해당하는 위치에 있는 old_text만 바뀝니다. 그렇지 않으면 모든 old_text가 new_tex.. 더보기
엑셀 함수 활용-REPT 어떤 문자나 값을 반복해서 사용할 때 어떻게 하나요? 개수대로 복사? 뭐 그래도 되지만... 여기 간단한 함수 사용으로 반복된 값을 아주 쉽게 나타냅니다. ○ 수식 REPT(text,number_times) text 반복할 텍스트입니다. number_times 텍스트를 반복할 횟수를 지정하는 양수입니다. ○ 예제 더보기
엑셀 함수 활용-EXACT 문자열끼리 비교를 할때 사용하면 유용한 함수입니다. 우리가 눈으로 보기에는 같아 보이는 필드도 정확하게 확인 할 수 있죠. 앞 뒤 공백까지 포함해서 비교 하기때문에 우선 trim함수로 공백을 모두 제거한 후 비교 하는게 더 정확하게 결과값을 얻겠죠. ○ 수식 EXACT(text1,text2) text1 첫 번째 텍스트 문자열입니다. text2 두 번째 텍스트 문자열입니다. ※ EXACT 함수 대신 이중 등호(==) 비교 연산자를 사용하여 두 문자열이 정확하게 일치하는지 비교할 수도 있습니다. 예를 들어 =A1==B1을 사용하면 =EXACT(A1,B1)을 사용할 때와 같은 결과를 얻을 수 있습니다. 자 이제 간단한 예제문을 볼까요~ ○ 예제 더보기
엑셀 함수 활용-TRIM 단어 사이에 있는 공백 하나를 제외하고 텍스트의 공백을 모두 삭제할 때 사용합니다. 다른 응용 프로그램에서 받은 텍스트에 불필요한 공백이 있을 때 TRIM을 사용합니다. TRIM(text) text 공백을 제거할 텍스트입니다. 다시 간단한 예제를 볼까요? 더보기
엑셀 함수 활용-CLEAN 인쇄할 수 없는 문자를 텍스트에서 모두 삭제합니다. CLEAN을 사용하면 다른 응용 프로그램에서 가져온 텍스트 중에서 현재 사용 중인 운영 체제에서 인쇄할 수 없는 문자를 모두 삭제할 수 있습니다. 예를 들어 CLEAN을 사용하여 주로 데이터 파일의 시작과 끝에 있으며 인쇄할 수 없는 컴퓨터 코드를 삭제할 수 있습니다. CLEAN(text) text 인쇄할 수 없는 문자를 제거할 워크시트 정보입니다. 간단 예제~ ! 더보기
VLOOKUP과 HLOOKUP 함수 사용하기 VLOOKUP과 HLOOKUP 함수는 표의 가장 왼쪽 열이나 첫 행에서 특정 값을 찾아서 지정한 열이나 행에서 같은 행이나 열에 있는 값을 표시할 때 사용합니다. 동일 값을 비교 할 때나 찾을 때 다른 함수와 중첩해서 많이 활용됩니다. VLOOKUP 함수의 구문을 한번 알아 볼까요? VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) ○ Lookup_value : 찾을 값을 넣습니다. 영역 또는 문자열을 지정할 수 있습니다. ○ Table_array : 찾을 값이 포함되어 있는 데이터의 범위입니다. ○ Col_index_num : 비교값과 같은 행에 있는 값을 표시할 table_array의 열 번호입니다. 찾은 값이 있는 행의 열번호입니다, 열번호.. 더보기

티스토리툴바