컴활 1급 엑셀 & 엑세스 함수 및 핵심 정리

[ Excel ]

• weekday(날짜형식, 옵션) : 지정한 날짜의 요일 값을 숫자로 반환
  • 옵션 2 = 월(1), 화(2), ..., 일(7)
  • 옵션 1 = 일(1), 월(2), ...
  • 옵션 3 = 월(0), 화(1), ...
• switch(조건1, 값1, 조건2, 값2, ..)
• 액셀의 조건부 서식에서 수식 작성할 때는 등호와 []기호 사용하고 필드명 사용하는 것이 아니라 셀의 주소를 참조하여 수식 작성해야 함
• 고급 필터에서 일반식 말고 논리식으로 조건 설정하면 조건명을 필드명과 다르게 설정해야 하고 셀 주소 첫번째 행이 참조되었는지 확인
• 사용자  지정 서식 형태 1 = [조건1]서식 ; [조건2]서식 ; 그 외 숫자 서식 ; 문자일 때 서식 
• 사용자  지정 서식 형태 2 = 양수일 때 서식; 음수일 때 서식; 0 서식; 문자일 때 서식
• 매크로 기록 문제에서 조건부 서식 설정할 때는 꼭 한번만 눌렀는지 확인하고, 여러번 서식 적용되었다면 마지막에 규칙관리에서 삭제하는 것 까먹지 말기
• left, right, mid함수는 넣는 인자가 숫자형이더라도 결과가 문자형으로 변환되기 때문에 *1을 해주는 등의 후처리 필요
• round(숫자, num_digit)에서 num_digit은 어떤 자릿수에서 반올림할지 지정하는 것이 아니라, 어떤 자릿수까지 표시할 것이지 결과에 해당하는 자릿수를 입력해야 한다. 추가로, 예를 들어 num digit을 -1로 설정하면 십의자리까지 표현되는데 즉, round(235.6 , -1)의 결과는 240이 되는 것.
• 부분합 하기 전에 정렬을 하였는지 필수적으로 먼저 점검
• [데이터-통합] 기능 사용할 때는 첫 행(열)의 셀 이름을 맞춰주었는지 점검
• 청회색, 황금색 같은 색은 일반 색상에 없고, [페이지 레이아웃]-[색]에서 Office 색 테마로 테마 변경 해야함
• 배열 상수(i.e {a;b;c} , {a,b,c}같은 배열 수식에서 사용되는 상수집합) : 범위 지정 먼저 하고 수식 작성
• 년 = (일수 / 365)의 몫(Quotient함수 사용) , 개월 = (일수 / 365)의 나머지(mod함수)를 다시 30또는 31로 나눠서 얻은 몫(Quotient)
• Rank.eq쓰지 않고 순위 (내림차순) 출력하는 법 : 배열 수식 원리 이용 = sum(IF((전체 참조값 > 나의 현재 값), 1))) + 1
• match함수의 마지막 match type 옵션은 비교할 참조 표의 행이나 열이 오름차순인지 내림차순인지에 따라 1과 -1중에 선택하고 값이 정확히 일치하는 것을 매칭할 때는 0 입력하는 것 까먹지 말기

 

VBA(Visual Basic Application)함수 정리

• unload me(액세스 프로시저에서는 docmd. close사용)
• date() : 오늘날짜 / today() : 오늘날짜 + 시간 출력
• 입력행 = Range("첫 셀").currentregion.rows.count + @
• 참조행 = 목록상자or콤보상자 개체명.listindex
• 컨트롤명.rousource = "참조영역" or 컨트롤명.additem "추가할 목록"
• for 반복변수 = 초기값 to 끝값(반복내용 : 합 = 합 + 값 / 개수 = 개수 + 1) next 반복변수
• Msgbox("메세지", 버튼옵션(vbOkOnly, vbYesNO + vbDefaultButton2(아니오가 기본적으로 선택되어있음), "메세지상자 제목") 

 

[ Access ]

• *(여러문자)이나 ?(한 글자)같은 와일드카드 사용시, 등호(=)로 식을 연결하는 것이 아니라 LIKE 사용
• 집계함수(sum, count, max, min, d-함수 등)는 쿼리 작성 시, 사용할 때 묶는 방법을 "식"으로 변경
• 입력마스크 = 입력형식; 문자 저장 여부(0입력해야 문자를 저장함) ; 기본값
  • # : 숫자, 공백, +/- 기호 허용하는 선택 옵션
  • 0 : 숫자 필수옵션
  • 9 : 숫자 선택옵션
  • L : 문자 필수옵션
  • ? : 문자 선택옵션
  • & : 아무 문자(영어,한글 등)나 공백 선택옵션
  • A : 문자 or 숫자 필수옵션 (웬만하면 L이나 0으로 상위 조건에서 처리해야 입력마스크 기능 구현됨)
• 형식 - 바이트(Byte) : 255 이하의 숫자 저장할 때 사용하는 형식/ 짧은 텍스트 : 255자까지
• 바운드 열은 인덱스가 0이 아니라 1부터 시작한다.
• 탭 인덱스와 같은 인덱스 형태는 일반적인 코딩 문법처럼 0부터 시작한다.
• 매크로의 이름을 직접 작성해야 할 경우, [속성]에서 매크로 작성기로 연결하지 말고,  [만들기]창에서 직접 매크로 만들어서 나중에 연결해야 함
• 액세스의 조건부 서식 조건 작성할 떄, 액셀과 다르게 등호로 시작하지 않고 필드명에 대괄호([])직접 추가하기
• 폼의 정렬기준은 그냥 폼 속성에서 정렬기준에 필드명 작성해도 되지만, 보고서의 정렬은 [그룹 및 정렬추가] 기능 사용해야 함
• 필드 형식 설정할 때, 입력마스크 사용하고 주변에 &같은 연결 연산자 사용 안해도 알아서 적용됨
• 콤보나 목록상자 열 안보이게 하려면 열 갯수는 원본에 맞게 바꿔주고 열 너비를 0으로 해서 바꾸는 식으로 해야함
• instr(필드명, "찾을 문자") : 찾을 문자가 존재하는 위치 반환
• string(반복할횟수, "반복할 문자")
• dlookup같은 집계함수의 criteria작성할 때, 비교할 컨트롤명이 보이지 않는다면? 콤보상자의 숨겨진 열에 존재하는지 확인하고 숨겨진 열이 맞다면, cmb~.column(존재하는 열 위치,인덱스=0부터 시작)으로 연결하고 이건 자동 처리가 안되기 때문에 큰따옴표("")안에서 &로 구분해서 연결해야함(필터링할때 조건쓰는 방식처럼)

 

프로시저 & 매크로 함수 정리

• docmd.close 개체명(acform, 등등) 저장옵션
• 폼이름.show
• date : 오늘 날짜 / now : 오늘날짜 + 시간
• docmd.openreport, opentable : 보고서나 테이블 열기
• Range("셀 주소").font.name/ size/ italic/ bold
• 옵션 단추의 초기화 상태는 ex) Op콜라 = True이런식으로 그냥 논리값으로 설정하면 초기값 지정 가능
• 목록 상자나 콤보상자처럼 다른 개체를 참조해서 끌어올 때, 초기화 상태를 첫번째 행으로 해야 한다면, LISTINDEX 속성 이용해서 개체명.listindex = 0으로 설정 ex) Cmb학과.listindex = 0
• 매크로 편집기에서 컨트롤명을 사용할 때는 경로를 [FORMS]![폼 이름]![컨트롤명] 과 같이 끌어와서 필터 조건 사용 가능
• RecordSource 속성
  • Me.RecordSource = "SQL select문(ex) SELECT * from 도서판매 where 도서명 like '*" & txt도서명&"*' order by 필드명 정렬 기준"
• RunSQL문 : docmd.RunSQL "SQL문 작성" / docmd.Requery(업데이트까지 적용해줘야됨)
• dateadd(형식, 더하거나뺄날짜(값), 적용할 날짜)
• datediff(형식, 시작일, 날짜일)
• <필터 조건 비교하는 용도의 함수 크게 아래의 3가지 방법>
• (1) docmd.applyfilter 필터이름, "조건"
• (2) Me.filter = " 필터 조건"  Me.filteron = True
• (3) Me.RecordSetClone.FindFirst "조건"  Me.bookmark = Me.RecordSetClone.bookmark