달력

3

« 2024/3 »

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
반응형
728x90
반응형

 

 

 

[ 엑셀VBA 13] 불량관리 프로그램 만들기 - SQL의 프로그램 적용하기

 

오랜만에 불량관리 프로그램 13강을 설명을 드리는데 연말이다 연초다 해서 강좌를 올릴 시간이 없어

이제서야 올리는 것을 미안스럽게 생각한다.

 

자 오늘을 본격적인 SQL의 프로그램 적용 여기서는 전체 입력항목중의 공정별 불량합계를 구해보도록

한다.

 

 

우선 상세내용을 먼저 설명해보면 아래의 입력된 데이터베이스로 부터 불량을 조회하여

 

 

 

 

아래 우측의 불량수량 항목에 공정1,공정2,공정3,공정4에 해당하는 불량수량을 합계 후

 

불량수량란에 자동으로 입력되는 프로그램이다.

 

물론, 집게되는 불량수량의 우측상단에 분석이라는 버튼을 누르면 실행이 되는 것이다.

 

 

 

이 프로그램을 만들기 위해서는  데이터베이스 함수인 SQL을 사용설정을 한 후

 

 VBA작성을 해서 데이터를 자동으로 받아 오는 것이다.

 

 

시트텝의 코드보기를 통해 아래과 같이 모듈1을 생성한다.

 

하단에 모듈1의 주기능을 설명하기로 한다.

 

 

 

 

 

Sub Analysis()          'Analysis라는 프로시져를 생성한다.
Dim db As Database   ' 데이터베이스를 설정한다.
Dim rs As Recordset   ' 레코드셋을 설정한다.
Dim str As String        ' SQL을 사용하기 위한 문장을 str로 설정한다.
Dim i, j, k                 ' 변수i,j,k를 설정한다.
Set db = OpenDatabase(ThisWorkbook.Path & "\" & ThisWorkbook.Name, False, True, "Excel 8.0;")

                              ' 엑셀로부터 가져오는 db를 설정한다.

 

For i = 1 To 4            '반복수를 4까지 증가 데이터를 공정1 부터 공정4까지 넣을 계획임.
   str = "SELECT  SUM(수량) "     ' 모든 수량을 선택
   str = str & " FROM [Data$]  " '  ' Data라는 이름을 가진 db가 경로이다.
   str = str & " WHERE 공정 = '" & ActiveSheet.Cells(4 + i, 7) & "'"   '공정은 셀의 값인데 공정1~ 4의 이름이

                                                                                         목적어 이다.
   str = str & " AND 처리결과 LIKE '*폐기*' "                                '처리결과는 폐기항목이다.
   Set rs = db.OpenRecordset(str)                                             '레코드셋은 str로 db에 연결한다.
   
   ActiveSheet.Select                   ' 현재시트를 선택하고
   With ActiveSheet
       .Cells(4 + i, 8).CopyFromRecordset rs ' 셀에다 1씩증가하면 값을 넣는다.(공정1의 값, 공정2의값...)
   End With
   rs.Close                                 '레코드셋을 닫고
   Set rs = Nothing                      '레코드셋을 없앤다.
Next i                                       'i에대한 루프 종료

   db.Close                                'db를 닫고
   Set db = Nothing                      'db를 없앤다.
End Sub

 

 

 

Q-분석_데이터베이스_2014.12.17.xlsm

 

 

 

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

 

728x90
반응형
:
Posted by mapagilove
728x90
반응형

 

 

 

[ 엑셀VBA 12 ] 불량관리 프로그램 만들기 - SQL 함수에 대하여

 

오늘은 SQL함수에 대하여 알아보고자 한다. 갑자기 이 시점에서 왜? SQL이냐면 엑셀을 DB로

해서 많은 용량의 데이터를 검색해서 조건을 추려 골라내고 계산을 하려면 DB전용 함수인

SQL 함수 + VBA가 필요하기 때문이다.

 

 

SQL이란 무엇인가?

SQL이란 구조화 질의어(Structured Query Language)의 줄임말이며, SQL은 DDL(데이터 정의 언어)과 DML(데이터 조작 언어) 명령으로 크게 나누어 볼 수 있다. DDL은 데이터베이스 개체 자체를 다루는 것으로, 새 데이터베이스를 만들거나, 필드 인데스를 작성하고 정의, 또는 삭제할 수 있으며 DML 명령을 사용해서 질의를 구성해 데이터베이스에서 우리가 원하는 데이터를 추출하거나 정렬하거나 삭제 또는 수정할 수 있다.

즉, SQL이란 데이터를 정의하거나 조작하는 데 사용하는 언어를 말하는 것이다.

 

SQL 절(절은 SQL 언어의 구성 요소)

절은 선택하거나 수정 또는 편집할 데이터를 정의할 때 사용하는 조건을 부여할 때 사용한다.

FROM : 데이터의 위치를 말하며 엑셀 고급필터에서 데이터가 들어 있는 목록 범위를 지정하는 것과 같다.

WHERE : 레코드가 선택되기 위해서 충족해야 하는 조건을 지정할 때 사용하며,  일반적으로 엑셀의 고급필터에서 조건 범위를 부여하는 것과 같다.

GROUP BY : 선택한 레코드를 특정 그룹으로 묶을때 사용한다.

HAVING : 그룹별로 만족될 조건을 지정할 때 사용

ORDER BY : 지정된 순서에 따라 선택된 레코드들을 오름차순 혹은 내림차순으로 정렬할 때 사용

 

SQL 명령(명령은 SQL 언어의 구성요소)

SQL 언어는 아주 단순한 몇 개의 명령으로 구성되어 있으며, 아래의 7가지가 가장 중요하다.

 

DDL 문

CREATE : 데이터베이스에서 새로운 무엇인가를 만들고 싶을 때 CREATE 문을 이용함.

DROP   : 데이터베이스에서 테이블이나 인덱스 등 무엇인가를 삭제할 때 DROP 문을 사용함.

ALTER   : 필드를 추가하거나 필드 정의를 변경할 때 사용함.


DML 문

SELECT : 특정 조건을 만족하는 데이터를 찾기 위해 데이터베이스에 질의할 때 사용함.

INSERT : 데이터를 데이터베이스에 한꺼번에 추가하려고 할 때 사용함.

UPDATE : 특정 레코드와 필드의 값을 변경할 때 사용함.

DELETE : 데이터베이스에서 레코드를 삭제할 때 사용함.

 

SQL 계산 함수(계산 함수는 SQL의 구성 요소 )

계산 함수는 레코드 그룹에 적용되는 단일 값을 반환하기 위해 레코드 그룹의 SELECT 절 안에서 사용함.

AVG : 평균을 구할 때 사용함.

COUNT : 레코드의 수를 구할 때 사용함.

SUM  : 특정필드의 모든 값의 합계를 구할 때 사용함.

MAX : 특정필드에서 가장 큰 값(최대값)을 구할 때 사용함.

MIN : 특정필드에서 가장 작은 값(최소값)을 구할 때 사용함.

 

 

SQL 연산자 (연산자는 SQL 언어의 한 구성 요소)

SQL 연산자는 크게 논리 연산자와 비교 연산자의 두 종류로 나눌 수 있다.

 

논리 연산자 : 이 논리 연산자는 WHERE 절 안에서 식을 연결할 때 사용함.

AND : 두 가지 조건이 모두 만족할 때 참을 반환하며,아닌경우는 거짓을 반환한다.

OR   : 두 가지 조건 가운데 어느 하나라도 만족하면 참을, 둘 다 만족하지 못하면 거짓을 반환한다.

NOT : 어떤 조건이 부정일 경우에만 참을 반환한다.

 

비교 연산자 : 비교연산자는 어떤 작업을 해야할 것인지 결정하기 위해 두 식의 상대 값을 비교할 때 사용함.

< 작다(미만)
<= 작거나 같다(이하)
> 크다(초과)
>= 크거나 같다(이상)
= 같다
<> 같지 않다
BETWEEN 두 범위 사이의 값
LIKE 패턴 매칭에 사용함.
IN  데이터베이스의 레코드를 지정할 때 사용함.

 

 


자 인제 위의 조건을 기본으로하여 엑셀 VBA을 활용한 불량관리 프로그램을 만들어 보도록 할 것이다.

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

 

728x90
반응형
:
Posted by mapagilove
728x90
반응형

 

 

 

[ 엑셀VBA 11 ] 불량관리 프로그램 만들기 - 데이터베이스 입력 행삽입 모듈 만들기

 

데이터베이스를 만들때 가장 중요한 것은 똑 같은 포멧으로 같은 방식으로 일정한 모드나 불량을 입력해

야 비슷한 모드들을 쉽게 모아서 내용을 분석하거나 하는데 사용할 수 있다.

 

그래서, 데이터베이스를 만드는 것은 목적에 맞게 모드나 유형을 지속적으로 정의 하고 분류하고 정리해

나가는 것이 좋은 데이터 베이스를 만드는 것이라 하겠다.

 

아래는 전 시간에 만들었던 유효성 검사를 통하여 만들었던 데이터베이스를 입력항목별로 선택하여 붙

여넣기 매크로 및 셀 선택 매크로를 사용하여 새로 입력할 행을 삽입하는 매크로를 만들어 본다.

 

 

 

 

 

먼저 시트를 선택한 다음 우측마우스 버튼을 눌러 코드보기를 선택한다.

 

 

왼쪽 모듈을 선택 후 우측마우스 버튼을 눌러 모듈을 추가한다.

 

insertRows 프로시져를 만든 다음

 

 

r = ActiveCell.Row                                                       ' r을 선태된 행으로 지정
ActiveCell.EntireRow.Insert                                            ' 전체 행을 삽입

ActiveSheet.Range(Cells(r - 1, 1), Cells(r - 1, 12)).Copy  ' 이전행을 복사
ActiveSheet.Range(Cells(r, 1), Cells(r, 12)).PasteSpecial Paste:=xlPasteFormats

                                                                              '형식을 새로 삽입한 행에 붙어넣기

ActiveSheet.Range(Cells(r - 1, 6), Cells(r - 1, 6)).Copy    ' 이전행을 6열 복사
ActiveSheet.Range(Cells(r, 6), Cells(r, 6)).PasteSpecial Paste:=xlPasteFormulas '6열의 다음에 수식붙이기

ActiveSheet.Range(Cells(r, 4), Cells(r, 4)).Select              ' 4번셀 선택
Application.CutCopyMode = False  복사 붙여넣기 모드 종료

 

 

insert.txt

 

 

 

다음은 버튼을 만들어 볼텐데요... 개발도구에서 삽입을 누른다.

 

개발도구가 없는 경우는 도구 기능에서 개발기능을 삽입한다.

 

 

삽입을 누르면 아래와 같은 양식 컨트롤 창이 나타난다.

 

 

하기와 같이 버튼을 만들고 행삽입이라고 이름을 입력한다.

 

행삽입 버튼을 선택한다음 우측마우스 버튼을 눌러 매크로지정을 선택한다.

 

 

insertRows 매크로를 선택한다.

 

 

자 인제 위의 매크로를 기준으로 위의 9열을 선택한 다음 행삽입 버튼을 누르면

 

9열에 위에서 입력한 서식과 유효성검사 목록, 수식만 복사하여 새로운

 

데이터를 입력할 수 있게 된다.

 

자세한 내용은 아래 첨부된 엑셀파일을 다운받아 확인해 보시길....

 

 

다음 강좌는 SQL을 활용한 분석 데이터 집게에 대하여 강의을 포스팅 할 예정이다.

 

 

Q-분석_데이터베이스_2014.12.17.xlsm

 

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

 

728x90
반응형
:
Posted by mapagilove
728x90
반응형

 

 

 

 

[ 엑셀VBA 10 ] 불량관리 프로그램 만들기 - 분석툴 만들기

 

 

대용량 데이터베이스를 만드는 목적은 모름지기 분석이다 대용량의 데이터를 통해서 어떠한 데이터를

얻느냐에 따라 회사에서 얻는 데이터의 분석을 통하여 새로운 부가가치를 창출할 수 있다.

 

아래는 기존에 만든 데이터베이스 프로그램의 불량 데이터베이스를 공정별로 분석할 수 있는 툴을

간단히 만들어 본 후 포스팅 해본다.

 

 

 

아래는 불량을 관리하는 기본 데이터 베이스이다.

 

 

분석시트에 아래와 같이 불량율을 구하기 위한

 

생산수량 및 공정별 수량을 집게할 수 있도록 란을 만든다.

 

 

해당 데이터 중 공정명과 불량율을 Ctrl키를 이용하여 선택한다.

 

x축에는 공정이 나오도록 행열전환을 눌러 아래와 같은 챠트가 나오도록 만든다.

 

 

 

입력란에 목표란을 만들어 다시 범위를 선태한다.

 

 

 

목표란의 경우는 챠트를 만든 후 챠트를 선챠트로 바꾸어 준다.

 

 

자, 그럼 생산수량 및 불량수량을 업데이트 하면 자동으로 불량율이

 

나오는 컨셉으로 목표를 초과하면 코멘트를 달아서 정보를 공유하도록 하는 컨셉이다.

 

 

위에서 마지막 컨셉의 경우 공정별로 불량수량과 불량율을 구하고

 

개별 불량에 따라 불량리포트를 하부에 표시하여

 

최종적으로

 

한시트에서 한눈에 불량율과 공정별 이상불량에 대하여

확인 할 수 있도록 하는 컨셉이다.

 

 

자, 기본 분석툴에 대한 설명을 이것으로 줄이고 다음 시간에는 이런 분석툴을

자동으로 실행하여 구할 수 있도록 기본적인 SQL에 대하여 설명하는 시간을 갖고자 한다.

 

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

728x90
반응형
:
Posted by mapagilove
728x90
반응형

 

 

 

 

 

[ 엑셀VBA 9 ] 불량관리 프로그램 만들기 - 엑셀 데이터베이스 사용 설정

 

 

대용량 데이터를 처리하기 위해서는 기본적이 함수의 이용으로는 수없이 많은 데이터를 통계적

트랜드의 분석에 적용한다거나 일일이 많은 데이터를 비교해서 데이터를 집게한다거나 하는 부

분이 전혀 만족스럽지 못하고 어려운 부분이 많다.

이런 경우 어떤 방법으로 데이터를 처리하거나 집게에 사용할 수 있을까? 바로 엑셀이나 엑세스

를 데이터베이스로 만들어서 통계나 집게함수로 쉽게 적용할 수 있는 부분이 있다.

바로 SQL이라는 대용량 데이터 처리를 위함 함수와 구문은 사용하는 것이다. 그러나, 이 부분을

적용할려면 기본적으로 아래의 4가지를 적용하여야 한다.

 

엑셀에 SQL적용 방법

1. 엑셀이나 엑세스에 데이터베이스 구성

2. 엑셀에서 데이터베이스 적용 참조 설정

3. 데이터 베이스 연결자 설정

4. SQL함수로 데이터베이스 적용

 

 

현재까지 엑셀로 데이터 베이스 구성을 하였고 이것을 입력을 계속 할 수 있도록 적용하는 

매크로적용외에 엑셀에서 데이터베이스 적용 참조설정을 하는 방법을 포스팅 해본다.

 

 

현재 파일을 열어 시트에서 우측마우스버튼을 클릭

코드보기를 선택한다.

 

 

VBA코드 입력화면이 나타나면 도구- 참조를 누른다.

 

 

참조에서 하기의 그림과 같이

Microsoft DAO 3.6 Object Library를 선택한다.

 

 

위와 같이 설정하면 SQL 함수 사용을 위한 기본 설정이 완료된다.

 

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

 

728x90
반응형
:
Posted by mapagilove
728x90
반응형

 

 

 

 

 

[ 엑셀VBA 8 ] 불량관리 프로그램 만들기 - 이중 유효성검사

 

 

데이터 베이스를 관리하기 위해서 여러가지 방법을 입력된 데이터를 일관성 있게 입력해야만

나중에 데이터베이스의 용도로 활용하기 위해서는 일관성있는 이름과 규칙으로 입력할 필요

가 있다.

이때, 사용할 수 있는것이 유효성 검사이다. 유효성 검사의 경우 지정된 목록과 지정된 목록

의 이중 조건으로 검색 조건을 지정하여 입력 규칙을 규제할 수가 있다.

 

만약, 공정을 선택하면, 공정1의 불량모드가 선택되고, 공정2를 선택하면 공정2의 불량모드

선택될 수 있도록 하는 어제에 이어 이중 유효성 검사를 하는 방법을 포스팅 해본다.

 

 

아래는 어제 적용한 IF함수와 OR함수를 이용한 그룹 설정이다.

 

 

 

자 인제 부터는 공정을 선택하면, 공정1의 불량모드가 선택되는 이중유효성 검사

설정하는 하는 방법을 정리해 본다.

 

먼저 공정 시트의 공정별불량모드를 아래와 같이 지정한다.

 

공정1의 불량모드 영역선택 후 이름을 공정1로 입력하듯

공정1의 불량모드, 공정2의 불량모드...형태로 입력한다.

 

 

인제 입력창으로 돌아가서 불량모드 셀을 선택 후

유효성 검사를 선택 후 아래와 같이 제한대상을 목록으로 선택 후

원본에 =INDIRECT(E2)로 입력한 후 확인을 누른다.

 

 

 

최종적으로 입력이 완료되면 아래와 같이 공정을 선택하면 불량모드가 다르게

선택 되는 것을 볼 수가 있다.

 

 

최종적으로 입력시트가 완성된 모습이다.

 

 

자 인제 입력 시트를 정해진 모드별로 입력하는 최종 시트가 완성되었고

각 불량을 날짜별로 입력하고 인제부터 본격적으로 쿼리를 이용한 데이터를 완성하기위한

공정별 챠트만들기와 쿼리적용을 위한 기본 적용에 대하여 포스팅 예정이다.

 

 

불량율관리 프로그램 만들기 시리즈

 

1. 매크로의 이해

2. 절대참조와 상대참조

3. 조건절 if

4. 반복문 for - next

 

5. 조건문 select case

6. 데이터베이스의 구성

7. 유효성검사를 통한 입력 규칙만들기

8. 이중유효성검사 적용

 

9. 엑셀 데이터베이스 사용설정 적용하기

10. 분석툴 만들기

11. 데이터입력 행삽입 모듈만들기

12. 대용량 DB사용을 위한 SQL함수 알아보기

13. SQL 합수 프로그램에 적용하기

728x90
반응형
:
Posted by mapagilove
반응형