샘플링 검사 (KS Q ISO 2859-1) 1회 샘플링 함수 만들기

Excel - VBA

샘플링 검사 (KS Q ISO 2859-1) 1회 샘플링 함수 만들기

EGTools 2023. 3. 20. 19:00

여기 소개된 함수들은 EGTools 추가기능에 추가되었습니다. (v3.7.0)

ISO 2859-1 및 KS Q ISO 2859-1에 규정된 샘플링 검사에 대해서 계수형 샘플링 검사에 가장 많이 사용되는

1회 샘플링 검사 방식의 내용을 함수로 만들어 쉽게 사용할 수 있게 합니다.

샘플링방법(ISO-2859-1)_v1.2.xlsm

0.07MB

함수의 사용법은 아래를 참고하세요.

SamplingLabel 함수 : https://cafe.naver.com/egtools/94
SamplingSize 함수 : https://cafe.naver.com/egtools/93
SamplingAc 함수 : https://cafe.naver.com/egtools/95
SamplingRe 함수 : https://cafe.naver.com/egtools/96

가장먼저 샘플문자를 확인하는 함수로, LotSize와 검사수준을 지정하여 찾습니다.

Function SamplingLabel(ByVal LotSize As Long, Optional sampleType As String = "G2")
  Dim idx As Long, sType As String
  Dim sizeArray As Variant: sizeArray = Array(2, 9, 16, 26, 51, 91, 151, 281, 501, 1201, 3201, 10001, 35001, 150001, 500001)
  Dim oDict As Object: Set oDict = CreateObject("Scripting.Dictionary")
  oDict("S1") = "AAAABBBBCCCCDDD"
  oDict("S2") = "AAABBBCCCDDDEEE"
  oDict("S3") = "AABBCCDDEEFFGGH"
  oDict("S4") = "AABCCDEEFGGHJJK"
  oDict("G1") = "AABCCDEFGHJKLMN"
  oDict("G2") = "ABCDEFGHJKLMNPQ"
  oDict("G3") = "BCDEFGHJKLMNPQR"
  
  If LotSize < 2 Then SamplingLabel = CVErr(xlErrNum): Exit Function
  sType = Replace(sampleType, "-", "")
  If InStr(1, "S1,S2,S3,S4,G1,G2,G3", sType, vbTextCompare) = 0 Then SamplingLabel = CVErr(xlErrValue): Exit Function
  idx = Application.Match(LotSize, sizeArray, 1)
  SamplingLabel = Mid(oDict(UCase(sType)), idx, 1)
End Function

그 다음은 3가지 검사방법(보통 검사, 수월한 검사, 까다로운 검사)의 주샘플링표를 배열에 담아야 합니다.

샘플링표에서 ↓로 표시된 것은 "N"으로 ↑으로 표시된 것은 "P"로 지정하였고,

2차원 배열의 첫번째 열(r, 0)에 샘플크기를 넣고 나머지 배열을 채웠습니다.

까다로운 검사에만 있는 "S"문자는 별도로 추가합니다.

샘플문자를 기준으로 AQL에서 숫자가 기록된 행을 찾아 샘플수와 Ac/Re 갯수 확인

Private Function makeAQLTable(Optional Inspection As Variant = 0)
    Dim vT As Variant, vAC As Variant, r As Long, c As Long
    Const N = "N": Const P = "P"
    ReDim vT(0 To 15)
    If Inspection > 0 Then  '' 까다로운 검사
      vT(0) = Array(2, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, 1, 2, 3, 5, 8, 12, 18, 27)
      vT(1) = Array(3, N, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, 27, 41)
      vT(2) = Array(5, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, 27, 41, P)
      vT(3) = Array(8, N, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, 27, 41, P, P)
      vT(4) = Array(13, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, 27, 41, P, P, P)
      vT(5) = Array(20, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P)
      vT(6) = Array(32, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P)
      vT(7) = Array(50, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P)
      vT(8) = Array(80, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P)
      vT(9) = Array(125, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P)
      vT(10) = Array(200, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P)
      vT(11) = Array(315, N, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(12) = Array(500, N, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(13) = Array(800, N, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(14) = Array(1250, N, 0, N, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(15) = Array(2000, 0, P, N, 1, 2, 3, 5, 8, 12, 18, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
          
    ElseIf Inspection = 0 Then  ''보통 검사
      vT(0) = Array(2, N, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 7, 10, 14, 21, 30)
      vT(1) = Array(3, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, 30, 44)
      vT(2) = Array(5, N, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, 30, 44, P)
      vT(3) = Array(8, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, 30, 44, P, P)
      vT(4) = Array(13, N, N, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, 30, 44, P, P, P)
      vT(5) = Array(20, N, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P)
      vT(6) = Array(32, N, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P)
      vT(7) = Array(50, N, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P)
      vT(8) = Array(80, N, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P)
      vT(9) = Array(125, N, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P)
      vT(10) = Array(200, N, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P)
      vT(11) = Array(315, N, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(12) = Array(500, N, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(13) = Array(800, N, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(14) = Array(1250, 0, P, N, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(15) = Array(2000, P, P, 1, 2, 3, 5, 7, 10, 14, 21, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      
    ElseIf Inspection < 0 Then  ''수월한 검사
      vT(0) = Array(2, N, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, N, N, 1, 2, 3, 5, 7, 10, 14, 21, 30)
      vT(1) = Array(2, N, N, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 5, 7, 10, 14, 21, 30)
      vT(2) = Array(2, N, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, 14, 21, P)
      vT(3) = Array(3, N, N, N, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, 14, 21, P, P)
      vT(4) = Array(5, N, N, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, 14, 21, P, P, P)
      vT(5) = Array(8, N, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P)
      vT(6) = Array(13, N, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P)
      vT(7) = Array(20, N, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P)
      vT(8) = Array(32, N, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P)
      vT(9) = Array(50, N, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P)
      vT(10) = Array(80, N, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P)
      vT(11) = Array(125, N, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(12) = Array(200, N, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(13) = Array(315, N, 0, P, N, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(14) = Array(500, 0, P, P, N, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      vT(15) = Array(800, P, P, P, 1, 2, 3, 4, 6, 8, 10, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P, P)
      
    End If
    ReDim vAC(0 To IIf(Inspection > 0, 16, 15), 0 To 26)
    For r = 0 To 15: For c = 0 To 26
        vAC(r, c) = vT(r)(c)
    Next c: Next r
    If Inspection > 0 Then vAC(r, 0) = 3150: vAC(r, 3) = 1
    makeAQLTable = vAC
End Function

이제 본 함수로 시료문자와 AQL을 기준으로 각각의 검사방법에 따라 시료수와 Ac(합격판정 불량수)를 찾는 본 함수를 작성합니다. Private으로 한 이유는 본함수를 시료수 찾는 함수와 Ac 찾는 함수로 나누어 쓰기 위해서입니다.

Private Function getAQLValue(ByVal LotSize As Long, AQL As Double, Optional sampleType As String = "G2", Optional Inspection As Variant = 0, Optional AcQty As Boolean = 0)
    Dim L As Long, sLabel As Variant, sType As String
    Dim vAQL As Variant, vACT As Variant, idx As Variant
    
    If TypeName(Inspection) = "Range" Then Inspection = Inspection.Cells(1, 1).Value2
    If IsNumeric(Inspection) Then  '//숫자로 입력한 경우 처리
      If Inspection < 0 Then Inspection = -1
      If Inspection > 0 Then Inspection = 1
    Else                          '// 혹시 문자로 입력한 경우 처리
      If InStr(1, Inspection, "Normal", vbTextCompare) Then Inspection = 0
      If InStr(1, Inspection, "Tight", vbTextCompare) Then Inspection = 1
      If InStr(1, Inspection, "Reduce", vbTextCompare) Then Inspection = -1
      If InStr(1, Inspection, "보통", vbTextCompare) Then Inspection = 0
      If InStr(1, Inspection, "까다로운", vbTextCompare) Then Inspection = 1
      If InStr(1, Inspection, "수월한", vbTextCompare) Then Inspection = -1
    End If
    
    sType = Replace(sampleType, "-", "")	'//중간에 하이픈(-)을 넣은 경우 삭제
    If InStr(1, "S1,S2,S3,S4,G1,G2,G3", sType, vbTextCompare) = 0 Then getAQLValue = CVErr(xlErrValue): Exit Function
    
    sLabel = SamplingLabel(LotSize, sampleType)
    If IsError(sLabel) Then getAQLValue = sLabel: Exit Function
    
    L = InStr(1, "ABCDEFGHJKLMNPQRS", sLabel, vbTextCompare) '// 배열의 행
    If L = 0 Then getAQLValue = CVErr(xlErrValue): Exit Function
    
    vAQL = Array(0.01, 0.015, 0.025, 0.04, 0.065, 0.1, 0.15, 0.25, 0.4, 0.65, 1#, 1.5, 2.5, 4#, 6.5, 10#, 15#, 25#, 40#, 65#, 100#, 150#, 250#, 400#, 650#, 1000#)
    idx = Application.Match(AQL, vAQL, 1)                  '//배열의 열
    If IsError(idx) Then getAQLValue = CVErr(xlErrValue): Exit Function
    
    vACT = makeAQLTable(Inspection)
    
    Do While Not IsNumeric(vACT(L - 1, idx))  '//숫자가 아니면 N, P에 따라 이동
        If vACT(L - 1, idx) = "N" Then L = L + 1 Else L = L - 1
    Loop
    
    If AcQty Then	'// 함수 종류에 따라 결과 반환
        getAQLValue = vACT(L - 1, idx)
    Else
        getAQLValue = vACT(L - 1, 0)
        '// 만약 샘플크기가 로트크기 이상이면 전수검사 한다.
        If LotSize < getAQLValue Then getAQLValue = LotSize
    End If
    
  
End Function

마지막으로 실제 Excel에서 사용할 샘플시료수를 찾는 함수와 합격판정불량수(Ac)를 찾는 함수를 만듭니다.

LotSize가 1인 것은 표준에 없는 것이지만, 검사수가 동일하도록 추가합니다.

Rem////////////////////////////////////////////////////////////////////////////////////////
Rem
Rem Function  : SamplingSize(), SamplingAc()
Rem Describe  : Sampling size or Acceptible NG q'ty based on ISO2859-1 standard
Rem Version   : 1.2
Rem Author    : EGTools (egexcelvba@gmail.com)
Rem Date      : 2023-05-19
Rem License   : MIT License
Rem Arguments :
Rem   LotSize     - LOT or Batch size q'ty.
Rem   AQL         - Acceptable Quality Level.
Rem   sampleType  - Sampling Level like G-1,G-2,G-3,S-1,S-2,S-3,S-4.
Rem   Inspection  - Sampling methos 'Normal/Tightened/Reduced Inspection'
Rem Returns   : Sample size or Acceptible NG Q'ty
Rem
Rem////////////////////////////////////////////////////////////////////////////////////////

Function SamplingSize(ByVal LotSize As Long, AQL As Double, Optional sampleType As String = "G2", Optional Inspection As Variant = 0)
    If LotSize = 1 Then SamplingSize = 1: Exit Function
    SamplingSize = getAQLValue(LotSize, AQL, sampleType, Inspection, False)
End Function

Function SamplingAc(ByVal LotSize As Long, AQL As Double, Optional sampleType As String = "G2", Optional Inspection As Variant = 0)
    If LotSize = 1 Then SamplingAc = 0: Exit Function
    SamplingAc = getAQLValue(LotSize, AQL, sampleType, Inspection, True)
End Function

2회샘플링 방식 및 다회 샘플링 방식으로 확장한 함수는 아래를 참고 바랍니다.

https://egtools.tistory.com/entry/MultiSampling

'Excel - VBA' 카테고리의 다른 글

Selenium Basic 사용중 Unexpected Alert Open Error (0)	2023.03.28
표준시간을 얻어 오는 함수 (0)	2023.03.27
Array로부터 1bit 단일색상 BMP 파일 만들기 (0)	2023.03.11
Excel로 바코드 발행하는 추가기능 EGBarcode (v2.8) - Code 128, Code 39, 2of5 Interleaved, EAN13, Datamatrix, QR Code, PDF417, Aztec Code, GS1-128, GS1-Datamatrix (3)	2023.03.06
Array값중 Empty 값이 Null로 변경되는 오류 (0)	2023.02.28

현재글샘플링 검사 (KS Q ISO 2859-1) 1회 샘플링 함수 만들기

EGTools

EGTools 추가기능(Excel Add-in)은 Excel 사용에 도움을 주는 여러 기능과 함수를 제공합니다.

KS Q ISO 2859-1, 음력변환, EAN13, ToLunar, Excel 2019, add-in, EGTools, UDF, google sheets, VBA, 구글시트, Excel 2021, 신규함수, 호환함수, M365, M365 신규함수, Code128, Excel, Microsoft 365 Excel, Excel 2021 신규함수,

Today :
Yesterday :

EGTools