Skip to content

Instantly share code, notes, and snippets.

@Kyeongrok

Kyeongrok/sales_report.md

Created January 20, 2018 07:28
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Kyeongrok/7205a1de869b77c1e9acdf45871ed094 to your computer and use it in GitHub Desktop.
Save Kyeongrok/7205a1de869b77c1e9acdf45871ed094 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
sales_report.md 
Sub main()
    db시트명 = Sheets("report").Range("a2").Value
    Call init(db시트명, "filter")
    '조건 입력
       
    lastRow = 1
    spaceRow = 7
       
    lastRow = filterAndCopy(db시트명, "1. Top 20 Model Sales", 7, 20, "D2", "", "E2", "", 1)
    lastRow = filterAndCopy(db시트명, "2. Top 20 LCD Model Sales", lastRow + spaceRow, 20, "D2", "*LCD*", "E2", "", 1)
    lastRow = filterAndCopy(db시트명, "3. Top 10 OLED Model Sales", lastRow + spaceRow, 10, "D2", "OLED", "E2", "", 1)
    lastRow = filterAndCopy(db시트명, "4. Top 5 70↑ Model Sales", lastRow + spaceRow, 10, "E2", "*70?↑*", "f2", "", 2)
    lastRow = filterAndCopy(db시트명, "5. Top 5 65↑ Model Sales", lastRow + spaceRow, 10, "E2", "*65?↑*", "f2", "", 2)
    lastRow = filterAndCopy(db시트명, "6. Top 5 55↑ Model Sales", lastRow + spaceRow, 10, "E2", "*55?↑*", "f2", "", 2)
    lastRow = filterAndCopy(db시트명, "7. Top 5 48↑ Model Sales", lastRow + spaceRow, 10, "E2", "*48?↑*", "f2", "", 2)
    lastRow = filterAndCopy(db시트명, "8. Top 5 40↑ Model Sales", lastRow + spaceRow, 10, "E2", "*40?↑*", "f2", "", 2)
    lastRow = filterAndCopy(db시트명, "9. Top 5 4000↑ Model Sales", lastRow + spaceRow, 10, "H2", "4000↑", "D2", "", 2)
    lastRow = filterAndCopy(db시트명, "10. Top 5 3999↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=3999", "I2", ">3499", 2)
    lastRow = filterAndCopy(db시트명, "11. Top 5 3499↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=3499", "I2", ">2999", 2)
    lastRow = filterAndCopy(db시트명, "12. Top 5 2999↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=2999", "I2", ">2499", 2)
    lastRow = filterAndCopy(db시트명, "13. Top 5 2499↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=2499", "I2", ">1999", 2)
    lastRow = filterAndCopy(db시트명, "14. Top 5 1999↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=1999", "I2", ">1499", 2)
    lastRow = filterAndCopy(db시트명, "15. Top 5 1499↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=1499", "I2", ">999", 2)
    lastRow = filterAndCopy(db시트명, "16. Top 5 999↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=999", "I2", ">899", 2)
    lastRow = filterAndCopy(db시트명, "17. Top 5 899↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=899", "I2", ">799", 2)
    lastRow = filterAndCopy(db시트명, "18. Top 5 799↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=799", "I2", ">699", 2)
    lastRow = filterAndCopy(db시트명, "19. Top 5 699↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=699", "I2", ">599", 2)
    lastRow = filterAndCopy(db시트명, "20. Top 5 599↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=599", "I2", ">499", 2)
    lastRow = filterAndCopy(db시트명, "21. Top 5 499↓ Model Sales", lastRow + spaceRow, 10, "H2", "<=499", "I2", "", 2)
    

'    Sheets("report").Range(targetRange).NumberFormatLocal = "0"
    With Sheets("report").Range("E:E")
        .NumberFormatLocal = "0"
        .Style = "Comma [0]"
    End With
    
End Sub
Sub toNumberAndNumberFormat(sheetName, targetRange, numberFormat)
    Sheets(sheetName).Range(targetRange).TextToColumns _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
End Sub

Sub init(db시트명, filter시트명)
    
    Sheets("report").Cells.Font.Name = "Arial"
    
    'db시트명을 입력 받고 filter시트에 column명들을 복사 한다.
    If db시트명 = "" Then
        MsgBox ("report!a2 에 db시트명을 입력해주세요")
        Sheets("report").Range("a2").Select
        Exit Sub
    End If
    
    
    toNumberTargetRange = "h2:h" & getLastRow("raw", 8)
    Call toNumberAndNumberFormat("raw", toNumberTargetRange, "0")
    Call copyFilterColumn(filter시트명)
    Call changeViewField(filter시트명)
End Sub


Function filterAndCopy(db시트명, 리포트제목, startRow, numberOfRows, targetCell, filterCase As String, targetCell2, filterCase2 As String, 총합계타입)
    lastRow = startRow + numberOfRows
    Sheets("report").Range("a" & startRow - 1).Value = 리포트제목
    
    Sheets("filter").Range("a2:m4").ClearContents
    
    Dim case1 As String
    case1 = filterCase
    
    Sheets("filter").Range(targetCell).Value = case1
    Sheets("filter").Range(targetCell2).Value = filterCase2
    
    Call advancedFilter(db시트명, "filter", 7)
    Call 정렬(getLastRow("filter", 1), "I", xlAscending)
    Call copyDataToReportFromFilter("report", startRow, numberOfRows)
    
    Dim toRow As Integer
    toRow = numberOfRows / 2
    Call 음영넣기(Range("A" & (startRow + 1) & ":E" & startRow + toRow))
    
    Call ttlSales넣기(startRow, numberOfRows)
    Call 브랜드별개수세기(startRow, numberOfRows)
    
    
    If 총합계타입 = 1 Then
        Call 총합계구하기1("filter", startRow, numberOfRows)
    Else
        Call 총합계구하기2("filter", startRow, numberOfRows)
    End If
    Call marketPortion구하기(startRow, numberOfRows)
    
    filterAndCopy = lastRow
    
End Function

Sub 총합계구하기1(sheetName, startRow, numberOfRows)
    lastRow = startRow + numberOfRows
    Application.CutCopyMode = False
    Sheets("report").Range("B" & lastRow + 1).Value = "총합계"
    Sheets("report").Range("C" & lastRow + 1).FormulaR1C1 = "=SUM(R" & startRow + 1 & "C3:R" & lastRow & "C3)"
    Sheets("report").Range("D" & lastRow + 1).FormulaR1C1 = "=SUM(R" & startRow + 1 & "C4:R" & lastRow & "C4)"
End Sub

Sub 총합계구하기2(filterSheetName, startRow, numberOfRows)
    lastRow = startRow + numberOfRows
    
    Application.CutCopyMode = False
    Range("B" & lastRow + 1).Value = "총합계"
    
    'filter에서 구해가지고 온다
    filterSheetLastRow = getLastRow(filterSheetName, 3)
    Sheets(filterSheetName).Range("C" & filterSheetLastRow + 1).Value = "=SUM(R" & 8 & "C3:R" & filterSheetLastRow & "C3)"
    Sheets(filterSheetName).Range("D" & filterSheetLastRow + 1).Value = "=SUM(R" & 8 & "C4:R" & filterSheetLastRow & "C4)"
    
    categoryAmountSum = Sheets(filterSheetName).Range("C" & filterSheetLastRow + 1).Value
    cateGoryQuentitySum = Sheets(filterSheetName).Range("D" & filterSheetLastRow + 1).Value
    
    Sheets("report").Range("C" & lastRow + 1).FormulaR1C1 = categoryAmountSum
    Sheets("report").Range("D" & lastRow + 1).FormulaR1C1 = cateGoryQuentitySum
    
    Sheets("report").Range("C" & lastRow + 1).Style = "Comma [0]"
    Sheets("report").Range("D" & lastRow + 1).Style = "Comma [0]"
    
    
End Sub

Sub ttlSales넣기(startRow, numberOfRows)
    lastRow = startRow + numberOfRows
    With Sheets("report")
        .Range("B" & lastRow + 2).Value = "TTL Sales"
        .Range("C" & lastRow + 2).Value = Sheets("report").Range("B2").Value
        .Range("D" & lastRow + 2).Value = Sheets("report").Range("C2").Value
        
        .Range("C" & lastRow + 2).Style = "Comma [0]"
        .Range("D" & lastRow + 2).Style = "Comma [0]"
    End With
End Sub
Sub 음영넣기(targetRange As Range)
        With targetRange.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Sub copyDataToReportFromFilter(target시트명, startRow, numberOfRecords)
    fromRow = 7
    
    With Sheets(target시트명).Range("a" & startRow & ":e" & startRow + numberOfRecords)
        .Value = Sheets("filter").Range("a" & fromRow & ":e" & fromRow + numberOfRecords).Value
        .Style = "Comma [0]"
    End With
    
End Sub

Sub copyFilterColumn(filter시트명)
    Sheets(filter시트명).Range("a1:m1").Value = Sheets("raw").Range("a1:m1").Value
    Sheets(filter시트명).Range("a7:m7").Value = Sheets("raw").Range("a1:m1").Value
End Sub

Sub marketPortion구하기(startRow, numberOfRows)
    lastRow = startRow + numberOfRows
    
    Sheets("report").Range("B" & lastRow + 3).Value = "Market Portion"
    Sheets("report").Range("C" & lastRow + 3).FormulaR1C1 = "=R" & lastRow + 1 & "C3/R" & lastRow + 2 & "C3"
    Sheets("report").Range("D" & lastRow + 3).FormulaR1C1 = "=R" & lastRow + 1 & "C4/R" & lastRow + 2 & "C4"
    

    Sheets("report").Range("C" & lastRow + 3).Style = "Percent"
    Sheets("report").Range("D" & lastRow + 3).Style = "Percent"
    
End Sub

Sub 브랜드별개수세기(startRow, numberOfRows)
    With Sheets("report")
        .Range("g" & startRow + 1).Value = "Brand"
        .Range("g" & startRow + 2).Value = "LG"
        .Range("g" & startRow + 3).Value = "SO"
        .Range("g" & startRow + 4).Value = "PA"
        .Range("h" & startRow + 2).Value = "SS"
        .Range("h" & startRow + 3).Value = "PH"
        .Range("h" & startRow + 4).Value = "Other"
    End With
    
    With Sheets("report")
        .Range("j" & startRow + 1).Value = "SKU"
        .Range("j" & startRow + 2).Value = "0"
        .Range("j" & startRow + 3).Value = "0"
        .Range("j" & startRow + 4).Value = "0"
        .Range("k" & startRow + 2).Value = "0"
        .Range("k" & startRow + 3).Value = "0"
        .Range("k" & startRow + 4).Value = "0"
    End With
    
    Dim toRow As Integer
    toRow = startRow + numberOfRows / 2
    
    Range("J" & startRow + 2).FormulaR1C1 = "=COUNTIF(R" & startRow + 1 & "C1:R" & toRow & "C1,RC[-3])"
    Range("J" & startRow + 2).AutoFill Destination:=Range("J" & startRow + 2 & ":K" & startRow + 2), Type:=xlFillDefault
    Range("J" & startRow + 2 & ":K" & startRow + 2).AutoFill Destination:=Range("J" & startRow + 2 & ":K" & startRow + 4), Type:=xlFillDefault
    
End Sub

Sub changeViewField(filter시트명)
    With Sheets(filter시트명)
        .Range("a7").Value = Sheets(filter시트명).Range("b1").Value
        .Range("b7").Value = Sheets(filter시트명).Range("c1").Value
        .Range("c7").Value = Sheets(filter시트명).Range("k1").Value
        .Range("d7").Value = Sheets(filter시트명).Range("j1").Value
        .Range("e7").Value = Sheets(filter시트명).Range("f1").Value
        .Range("i1").Value = Sheets(filter시트명).Range("h1").Value
    End With

End Sub


Sub 정렬(lastRow, columnAlphabet, orderBy)
    Sheets("filter").Range("A8:M" & lastRow).Sort key1:=Sheets("filter").Range(columnAlphabet & "8:" & columnAlphabet & lastRow), _
        order1:=orderBy, Header:=xlNo
End Sub

Sub main_advancedfilter()
    Call advancedFilter("raw", "filter", 7)
End Sub

Sub advancedFilter(db시트명, filter시트명, viewFieldRow)
    
    '데이터 범위 구하기
    열개수 = Sheets(db시트명).Range("a1", Sheets(db시트명).Range("a1").End(xlToRight)).Columns.Count
    행개수 = Sheets(db시트명).Range("a1", Sheets(db시트명).Range("a1").End(xlDown)).Rows.Count
    열주소 = Chr(64 + 열개수)
    데이터범위 = "a1:" & 열주소 & 행개수
    
    '조건범위 구하기
    조건행개수 = 조건행개수구하기(3, 열개수)
    Sheets(db시트명).Range(데이터범위).advancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Range(filter시트명 & "!A1:" & 열주소 & 1 + 조건행개수), CopyToRange:=Range(filter시트명 & "!A" & viewFieldRow & ":" & 열주소 & viewFieldRow), Unique:=False
End Sub

Function 조건행개수구하기(p_최대조건줄수, p_조건열개수)
    조건범위 = 0
    
    열의데이터개수 = 0
    For 행 = 2 To p_최대조건줄수
        For 열 = 1 To p_조건열개수
            'Sheets("filter").Cells(행, 열).Select
            If Sheets("filter").Cells(행, 열).Value <> "" Then
                열의데이터개수 = 열의데이터개수 + 1
            End If
        Next
        
        If 열의데이터개수 > 0 Then
            조건범위 = 조건범위 + 1
        End If
        열의데이터개수 = 0
    Next
    
    조건행개수구하기 = 조건범위
End Function

Private Function getLastRow(sheetName, column) As Integer
    'Finds the last non-block cell in column A(1)
    getLastRow = Sheets(sheetName).Cells(Rows.Count, column).End(xlUp).Row
End Function

Sub 지우기()
    Sheets("report").Range("a3:z10000").Clear
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment