Sub one Âñòàâêà óíèêàëüíûõ çíà åíèé ñòîëáöà äîëæíîñòü Ñîðòèðîâêà ïî àë

 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Sub one()
'
' Âñòàâêà óíèêàëüíûõ çíà÷åíèé ñòîëáöà "äîëæíîñòü"
' Ñîðòèðîâêà ïî àëôàâèòó
'
Range("D1:D73").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("A75"), Unique:=True
Range("A76:A102").Sort Range("A76")
'
' Ïîäñ÷¸ò ñðåäíåãî îêëàäà äëÿ êàæäîé äîëæíîñòè
'
Cells(75, 3) = "Ñðåäíÿÿ ç/ï"
Cells(76, 3).FormulaR1C1 = "=AVERAGEIF(R1C4:R71C10,RC[-2],R1C10:R71C10)"
Range("C76").Select
Selection.AutoFill Destination:=Range("C76:C102"), Type:=xlFillDefault
Cells(75, 2) = "îêëàä"
Dim i As Integer
For i = 76 To 102
Cells(i, 2).FormulaR1C1 = "=ROUND(RC[+1],0)"
Next
'
'Âñòàâêà ñèìâîëà ">" â íà÷àëî ÿ÷åéêè
'
Range("B76:B102") = [">"&b76:b102]
Columns(1).EntireColumn.AutoFit
Columns(2).EntireColumn.AutoFit
Columns(3).EntireColumn.AutoFit
End Sub
Sub two()
'
' Âñòàâêà ñïèñêà òåõ ñîòðóäíèêîâ, ó êîãî äîêëàä áîëüøå ñðåäíåãî â åãî äîëæíîñòè
'
Range("A1:M71").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("A75:B102"), CopyToRange:=Range("A106"), Unique:=False
End Sub
Sub three()
'
' Ïîäñ÷¸ò êîëè÷åñòâà ñîòðóäíèêîâ è äåòåé ñîòðóäíèêîâ äëÿ êàæäîé äîëæíîñòè
'
Cells(75, 8) = "Êîëè÷åñòâî ñîòðóäíèêîâ"
Cells(75, 9) = "Êîëè÷åñòâî äåòåé"
Range("I1:I71").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("G75"), Unique:=True
Range("H76").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(Database,RC[-1])"
Selection.AutoFill Destination:=Range("H76:H82"), Type:=xlFillDefault
Columns(8).EntireColumn.AutoFit
Columns(9).EntireColumn.AutoFit
Range("I76").Select
ActiveCell.FormulaR1C1 = "=SUMIF(R[-75]C[-1]:R[-5]C[1],R[-1]C[-2]:RC[-2],R[-75]C[1]:R[-5]C[1])"
Selection.AutoFill Destination:=Range("I76:I82"), Type:=xlFillDefault
End Sub
Sub four()
'
' "Ñðåäíèé âîçðñò ñîòðóäíèêîâ êàæäîãî îòäåëà"
'
Cells(1, 14) = "âîçðàñò"
Range("N2").Select
ActiveCell.FormulaR1C1 = "=ROUND(YEAR(TODAY()-RC[-6])-1900,0)"
Selection.AutoFill Destination:=Range("N2:N71"), Type:=xlFillDefault
Cells(75, 10) = "Ñðåäíèé âîçðàñò"
Range("J76").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIF(R[-75]C[-1]:R[-5]C[-1],R[-1]C[-3]:RC[-3],R[-75]C[4]:R[-5]C[4])"
Selection.AutoFill Destination:=Range("J76:J82"), Type:=xlFillDefault
Cells(75, 11) = "Ñðåäíèé âîçðàñò"
Range("K76").Activate
ActiveCell.FormulaR1C1 = "=ROUND(RC[-1], 0)"
Selection.AutoFill Destination:=Range("K76:K82"), Type:=xlFillDefault
Columns(11).EntireColumn.AutoFit
End Sub