데이터베이스(DB)를 언급할 때 가장 먼저 떠오르는 것은 물론 SQL, Oracle, 1C 또는 최소한 Access와 같은 모든 종류의 유행어입니다. 물론, 이들은 많은 데이터를 가진 크고 복잡한 회사의 작업을 자동화할 수 있는 매우 강력하고 대부분의 경우 비용이 많이 드는 프로그램입니다. 문제는 때때로 그러한 힘이 단순히 필요하지 않다는 것입니다. 귀하의 비즈니스는 규모가 작고 비즈니스 프로세스가 비교적 단순할 수 있지만 자동화도 원할 수 있습니다. 소규모 회사의 경우 이것이 종종 생존의 문제입니다.
먼저 TOR를 공식화합시다. 대부분의 경우 회계용 데이터베이스(예: 클래식 판매)는 다음을 수행할 수 있어야 합니다.
- 유지 테이블에 상품(가격), 완료된 거래 및 고객에 대한 정보를 표시하고 이러한 테이블을 서로 연결합니다.
- 편안하다 입력 양식 데이터(드롭다운 목록 등 포함)
- 일부 데이터 자동 채우기 인쇄된 양식 (결제, 청구서 등)
- 필요한 발행 보고서 관리자의 관점에서 전체 비즈니스 프로세스를 제어
Microsoft Excel은 약간의 노력으로 이 모든 것을 처리할 수 있습니다. 이것을 구현해 봅시다.
1단계. 테이블 형태의 초기 데이터
우리는 제품, 판매 및 고객에 대한 정보를 XNUMX개의 테이블에 저장할 것입니다(같은 시트에 있든 다른 시트에 있든 상관 없음). 자동 크기 조절 기능이 있는 "스마트 테이블"로 변환하여 향후에 그것에 대해 생각하지 않도록 하는 것이 기본적으로 중요합니다. 이것은 명령으로 수행됩니다. 표로 형식 지정 탭 홈 (홈 — 표 형식). 그러면 나타나는 탭에서 건설자 (디자인) 필드에 테이블을 설명하는 이름 지정 테이블 이름 나중에 사용하기 위해:
총 XNUMX개의 "스마트 테이블"을 가져와야 합니다.
표에는 명확한 추가 데이터가 포함될 수 있습니다. 예를 들어 우리의 가격각 제품의 카테고리(제품군, 포장, 무게 등)에 대한 추가 정보와 표가 포함되어 있습니다. Client — 각각의 도시 및 지역(주소, TIN, 은행 세부 정보 등).
작업대 세일즈 나중에 완료된 거래를 입력하는 데 사용됩니다.
2단계. 데이터 입력 양식 만들기
물론 판매 데이터를 그린 테이블에 직접 입력할 수 있습니다. 세일즈, 그러나 이것은 항상 편리한 것은 아니며 "인적 요인"으로 인한 오류 및 오타의 출현을 수반합니다. 따라서 다음과 같이 별도의 시트에 데이터를 입력하기 위한 특수 양식을 만드는 것이 좋습니다.
B3 셀에서 업데이트된 현재 날짜-시간을 가져오려면 함수를 사용하십시오. TDATA (지금). 시간이 필요하지 않다면 대신 TDATA 기능을 적용할 수 있습니다 오늘 (오늘).
B11 셀에서 스마트 테이블의 세 번째 열에서 선택한 제품의 가격을 찾습니다. 가격 기능을 사용하여 VPR (조회). 이전에 본 적이 없다면 먼저 여기에서 비디오를 읽고 시청하십시오.
B7 셀에는 가격 목록의 제품이 포함된 드롭다운 목록이 필요합니다. 이를 위해 명령을 사용할 수 있습니다 데이터 – 데이터 검증 (데이터 유효성 검사), 제약 조건으로 지정 명부 (목록) 그런 다음 필드에 입력하십시오. 출처 (출처) 칼럼 링크 성함 우리의 스마트 테이블에서 가격:
마찬가지로 클라이언트가 있는 드롭다운 목록이 생성되지만 소스는 더 좁아집니다.
=INDIRECT(“고객[고객]”)
함수 간접적 인 (간접) 불행히도 Excel은 원본 필드의 스마트 테이블에 대한 직접 링크를 이해하지 못하기 때문에 이 경우 필요합니다. 그러나 동일한 링크가 함수에서 "래핑"됨 간접적 인 동시에, 그것은 강타와 함께 작동합니다(이에 대한 자세한 내용은 콘텐츠가 있는 드롭다운 목록 생성에 대한 기사에 있음).
3단계. 판매 항목 매크로 추가
양식을 작성한 후 입력한 데이터를 테이블 끝에 추가해야 합니다. 세일즈. 간단한 링크를 사용하여 양식 바로 아래에 추가할 줄을 만듭니다.
저것들. 셀 A20에는 =B3에 대한 링크가 있고 셀 B20에는 =B7에 대한 링크가 있는 식입니다.
이제 생성된 문자열을 복사하여 Sales 테이블에 추가하는 2줄 기본 매크로를 추가해 보겠습니다. 이렇게하려면 조합을 누르십시오 Alt + F11 또는 버튼 Visual Basic을 탭 개발자 (개발자). 이 탭이 표시되지 않으면 먼저 설정에서 활성화하십시오. 파일 – 옵션 – 리본 설정 (파일 — 옵션 — 리본 사용자 정의). 열리는 Visual Basic 편집기 창에서 메뉴를 통해 새 빈 모듈을 삽입합니다. 삽입 – 모듈 거기에 매크로 코드를 입력하세요.
Sub Add_Sell() Worksheets("입력 양식").Range("A20:E20").Copy '양식 n에서 데이터 라인 복사 = Worksheets("Sales").Range("A100000").End(xlUp) . Row '는 테이블의 마지막 행 번호를 결정합니다. Sales Worksheets("Sales").Cells(n + 1, 1).PasteSpecial Paste:=xlPasteValues '다음 빈 줄에 붙여넣기 Worksheets("Input Form").Range("B5,B7,B9"). ClearContents '종료 하위 양식 지우기 이제 드롭다운 목록을 사용하여 생성된 매크로를 실행하는 버튼을 폼에 추가할 수 있습니다. 끼워 넣다 탭 개발자 (개발자 — 삽입 — 버튼):
그린 후 마우스 왼쪽 버튼을 누른 상태에서 Excel에서 할당해야 하는 매크로를 묻습니다. 매크로를 선택합니다. 추가_판매. 버튼을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 버튼의 텍스트를 변경할 수 있습니다. 텍스트 변경.
이제 양식을 작성한 후 버튼을 클릭하기만 하면 입력된 데이터가 자동으로 테이블에 추가됩니다. 세일즈, 그런 다음 새 거래를 입력하기 위해 양식이 지워집니다.
4단계 테이블 연결
보고서를 작성하기 전에 나중에 지역, 고객 또는 범주별로 매출을 빠르게 계산할 수 있도록 테이블을 함께 연결하겠습니다. 이전 버전의 Excel에서는 여러 기능을 사용해야 했습니다. VPR (조회) 가격, 카테고리, 고객, 도시 등을 테이블에 대체하기 위해 세일즈. 이를 위해서는 우리의 시간과 노력이 필요하며 많은 Excel 리소스를 "먹는" 것입니다. Excel 2013부터는 테이블 간의 관계를 설정하여 모든 것을 훨씬 더 간단하게 구현할 수 있습니다.
이렇게 하려면 탭에서 Data (데이트) 클릭 처지 (처지). 나타나는 창에서 버튼을 클릭하십시오 만들기 (뉴) 연관되어야 하는 테이블과 열 이름을 드롭다운 목록에서 선택합니다.
중요한 점: 테이블은 이 순서로 지정되어야 합니다. 즉, 연결된 테이블(가격)는 키 열(성함) 테이블에서 발생하는 중복 제품 세일즈. 즉, 연결된 테이블은 다음을 사용하여 데이터를 검색할 테이블이어야 합니다. VPR사용된 경우.
물론 테이블도 비슷한 방식으로 연결 세일즈 테이블 Client 공통 열로 빠른 :
링크를 설정한 후 링크 관리 창을 닫을 수 있습니다. 이 절차를 반복할 필요가 없습니다.
5단계. 요약을 사용하여 보고서 작성
이제 판매를 분석하고 프로세스의 역학을 추적하기 위해 예를 들어 피벗 테이블을 사용하여 일종의 보고서를 생성해 보겠습니다. 활성 셀을 테이블로 설정 세일즈 리본에서 탭을 선택합니다. 삽입 – 피벗 테이블 (삽입 - 피벗 테이블). 열리는 창에서 Excel은 데이터 원본(예: 테이블 세일즈) 및 보고서를 업로드할 위치(가능한 새 시트에):
중요한 점은 확인란을 활성화해야 한다는 것입니다. 이 데이터를 데이터 모델에 추가 (데이터 모델에 데이터 추가) Excel이 현재 테이블뿐만 아니라 모든 관계를 사용하여 보고서를 작성하고 싶다는 것을 이해하도록 창 맨 아래에 있습니다.
클릭 후 OK 패널이 창의 오른쪽 절반에 나타납니다. 피벗 테이블 필드링크를 클릭하는 곳 All현재의 것 뿐만 아니라 책에 있는 모든 "스마트 테이블"을 한 번에 볼 수 있습니다. 그런 다음 클래식 피벗 테이블에서와 같이 관련 테이블에서 필요한 필드를 해당 영역으로 끌어다 놓기만 하면 됩니다. 필터, 행, 스톨브초프 or 마케팅은: – Excel은 시트에 필요한 모든 보고서를 즉시 작성합니다.
피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 주기적으로(소스 데이터가 변경될 때) 업데이트해야 한다는 것을 잊지 마십시오. 업데이트 및 저장 (새로 고치다), 자동으로 수행할 수 없기 때문입니다.
또한 요약에서 아무 셀이나 선택하고 버튼을 누르면 피벗 차트 (피벗 차트) 탭 Analysis (분석) or 파라미터 (옵션) 계산된 결과를 빠르게 시각화할 수 있습니다.
6단계. 인쇄물 작성
데이터베이스의 또 다른 일반적인 작업은 다양한 인쇄된 양식 및 양식(송장, 송장, 행위 등)을 자동으로 채우는 것입니다. 나는 이미 이것을 하는 방법 중 하나에 대해 썼습니다. 여기에서는 예를 들어 계좌 번호로 양식을 작성하는 것을 구현합니다.
셀 C2에 사용자가 숫자(테이블의 행 번호 세일즈, 실제로) 그런 다음 이미 친숙한 기능을 사용하여 필요한 데이터를 가져옵니다. VPR (조회) 및 기능 INDEX (인덱스).
- VLOOKUP 함수를 사용하여 값을 조회하고 조회하는 방법
- VLOOKUP을 INDEX 및 MATCH 함수로 바꾸는 방법
- 테이블의 데이터로 양식 및 양식 자동 채우기
- 피벗 테이블로 보고서 만들기