- 积分
- 3411
好友
记录
日志
相册
回帖0
主题
分享
精华
威望 旺
钢镚 分
推荐 人
|
本帖最后由 baisenes 于 2023-1-26 13:31 编辑
% u; o+ t% M- G
/ P$ A2 e8 R t场景2:已知管理汇总表A,要根据一定的规则(如密接1、2、4、7,次密接1、7,浦东判的次密1、5、7,徐汇判的次密2、6、7,黄浦判的次密1、2、3、4、5、6、7,松江判的密接7,不要问我为什么管理口径这么五花八门且SB),筛选出部分对象,输出采样单B0 v Q' s' c: E* l
; m* Q0 B% O9 w7 z首先我们设置了一个形如此的工作台:
; W+ u8 @8 Q9 U- K. T0 l( K# z
9 |* i, L; M, `! ^. w, x6 o! F从H4:P13范围分别设置目标管控类型,输出到哪张表,采样日期是管理开始的第几天
# [! s* |/ F4 u, C' b然后我们的汇总表是这样的(演示信息均非真实居民信息):% ]. L; K& z7 u
# e5 I# G# R, h6 [7 S在工作簿中有一个隐藏的空采样单,调用生成采样单的脚本时会复制一份以便于粘贴信息,总的采样单生成脚本是这样的:
; ?; H7 U' I. @' n- Sub CreateRnaTestForm(Optional ByVal filename As String = "")6 Q! |+ K$ n) g( q2 d% y' e
- '2 q! b2 P; u g8 T: Y1 o0 ~" D
- '根据当前Selection创建采样单,如选区为空则使用全部筛选项目创建
: w* @2 u' w2 G Y - '
. u$ U* V& u! ]; A - Application.StatusBar = "正在根据当前选区创建采样单"
1 @3 T0 U4 o; e- N J- Q - Excel.Application.Calculation = xlCalculationManual# |2 D9 Y3 R( P. o) b
- 8 l! A/ X! ~$ W7 ~
: Z) @. e& f; y- Sheets("纳管汇总表").Activate
% \: y& {" `1 O# e0 | - SelectAll = False% f$ N+ b* F# a2 ]; U+ v
- If Selection Is Nothing Then) n2 c' @0 f+ i& Z3 p
- SelectAll = True( [7 z' z4 D0 r/ d3 A, _
- Else9 `8 V) ]5 C" F! @2 o; s
- Set CCloumn = Range("C:C")
. Q# Z9 P4 l9 t$ ?4 w- P% j: J - Set Rng = Application.Intersect(CCloumn, Selection)7 B0 J7 K" P$ M, d. l2 J
- If Rng Is Nothing Then4 h0 i$ _6 x9 t5 k: M
- SelectAll = True
8 B$ k+ _' s" J" M3 m - ElseIf Not Rng.address = Selection.address Then. O- ^4 C$ H- q
- SelectAll = True1 k+ X9 |. V( |& L$ G( j
- End If
- \/ }/ d. ^# @0 y - End If% f- U6 B2 g" D M, L
- 9 d! V2 F" @! n- ~" U: ]
- If SelectAll Then" X! D9 u5 _4 ]1 G ~
- Range("C1").Select! C8 K& V( {! J9 L* v
- Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
8 d8 A: P% [3 Y3 @! E. V w- h; x5 O - If Selection.Count = 1 Then
8 ~6 ~% `7 U/ q - Application.StatusBar = "选区为空创建失败"7 K$ m+ U3 |4 H6 L/ b( P: a6 h
- Exit Sub: f) a$ V& h( Z. C
- End If
+ ~' {; l4 D# p - Range("C1").Select
8 i# i* P$ z4 I! `6 n - Selection.Offset(1, 0).Select3 {& c" |' ?' z L6 G% `/ @
- Range(Selection, Selection.End(xlDown)).SpecialCells(xlCellTypeVisible).Select
* D" |: _. x: u4 X6 P - End If
' y" M) s1 w' C - + w7 p2 ^6 c/ g7 e5 a) z
- ' 将目标选区存入变量4 l' O) |: I0 p% {, V* [& s
- Dim Collection As Range '目标选区
7 ^. f2 b1 L* ^$ i. p - Dim NewTestForm As Workbook '采样单区- `7 I7 p ~- Q7 `6 d4 n
- Set Collection = Selection7 F( G! A5 ]. ~) u) Q( B' d
- Set TotalWB = ActiveWorkbook
# @ R% e) a$ ^4 y0 A -
( G& k* { V3 f9 [9 x9 Q$ h - If filename <> "" Then
* _/ V4 P; n5 S, x) K6 |* L - For Each wb In Workbooks
5 E# s' Y, U% k( Y" }" I( D0 B - For Each sh In wb.Sheets
- p3 Q. _0 E- [6 B0 Y& O - If sh.Name = filename Then0 T( ^ ?$ J6 H/ w" G0 |7 s9 y
- Set NewTestForm = wb
) a$ l! C9 J p* @1 G3 E- n; @ - wb.Activate S: G/ |1 e7 G* F% {; a
- End If* ]; G& z' \0 P4 O
- Next
+ t$ q4 Q' w B% W4 T5 Z3 a% D9 _ - Next$ Z7 I6 C# H7 E- A% B+ x; ]
- Else/ b, q6 D* a6 _4 G6 Q+ I$ B
- filename = "采样单"8 i/ S& v& T; a% ~3 g/ x
- End If5 H- A& ], b/ n
-
5 _1 O+ j8 T2 P0 A F/ y" K - If NewTestForm Is Nothing Then$ h& J3 d# a: F7 L2 w5 h
- '复制一份空白采样单
z+ a, D( o1 Q, s - Sheets("采样单模板").Visible = True
% X4 u' l2 i, ?8 A. u" V k T - Sheets("采样单模板").Select
+ ~, [0 K$ @" Y" {% Z - Sheets("采样单模板").Copy8 E8 K# \ z9 N v1 b# @
- Sheets("采样单模板").Select
1 r- }& r2 y. \ - Sheets("采样单模板").Name = filename3 A4 B2 U6 u4 }8 ~) j3 G
- Set NewTestForm = ActiveWorkbook
/ a: B, y" k* r' S8 K9 f3 x - End If1 j# \- X0 X- b7 i+ C5 U; D
- 2 v/ O1 p y7 W% v4 e& C# Y0 q
- 4 J* V8 Q" [* Z& C) t# A! \
- Application.StatusBar = "复制姓名列": T3 F* m. A; ?1 C N# t9 ^
- Collection.Copy
+ f2 G# k$ z8 A" A - Dim startCell As Range
5 ]5 ]; s6 }* r, Q - If Range("C1").End(xlDown).Row = 2 Then
" A# ~. m' E+ B0 A. x - If IsEmpty(Range("C1").End(xlDown)) Then- @3 q$ O. J. z, O% u
- Set startCell = Range("C1").End(xlDown)& ?5 U4 d( K) E
- Else! @6 j6 S E( T
- Set startCell = Range("C1").End(xlDown).Offset(1, 0)
/ V$ g, H. q& K7 J0 B - End If2 @( R2 M: ^6 [& y+ t W$ `$ [
- Else# P, C6 t0 Q5 k, H7 I
- Set startCell = Range("C1").End(xlDown).Offset(1, 0)" C3 Z) y! U& u
- End If
: [: t( n8 D" ~ |0 S( ?& W- Q - startCell.Select
( u p3 s0 U6 S8 h3 W2 o - Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False8 H9 |5 d& S2 R* A* T
-
& h- l3 k* y5 K+ |6 F( y - Application.StatusBar = "复制地址列"
( N! s- ^3 x/ `6 i) K0 G5 n3 n - Collection.Offset(0, 5).Copy
! s i" Y( q( }4 L! u, j) L - Sheets(filename).Select
o# f. k; `/ p% L% l) A3 | - startCell.Offset(0, 6).Select) t$ `& E: a. Z2 w
- Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
) ]/ \! v) @& A. f - 0 s9 a: |3 n t, h
- Application.StatusBar = "复制联系方式列"
4 B; P/ F' d: e - Collection.Offset(0, 6).Copy
) p2 {- Y6 \+ i - Sheets(filename).Select
4 m/ z6 a; t# y - startCell.Offset(0, 7).Select' G0 e* T+ q, H9 z; |" Z
- Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
/ ~! h* F* j. K; E) ^" H8 _1 k - # ?' ?7 K$ \, e9 o |
- Application.StatusBar = "复制身份证号列"
. u; \( b/ I6 k" n5 O - Collection.Offset(0, 3).Copy" M# y; B# y0 |* U
- Sheets(filename).Select
1 }7 ~$ v8 }& |# t7 Y/ r' U - startCell.Offset(0, 3).Select5 D0 J3 f& ^+ ~; z* t8 E
- Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False& B" g, e6 F: b# b
-
0 B9 P& b. y" x: G - Application.StatusBar = "复制管控类型列"
* W$ @ d1 a" i7 S - Collection.Offset(0, -1).Copy
# v" {8 j8 P4 j - Sheets(filename).Select
M. Y0 y, p1 W w+ p2 }4 b - startCell.Offset(0, 4).Select7 J2 ]. Q' Z& U+ E
- Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
6 b+ D2 `% M7 Q& N& a - 1 T% C* i& m; U& ?, B$ i
- Application.StatusBar = "复制隔离开始时间列"
- C% r; g; x; r* j& o: ^ k$ q; ] - Collection.Offset(0, 8).Copy @ `/ g; N( {
- Sheets(filename).Select7 n% ?* Q$ q) T
- startCell.Offset(0, 9).Select
- T4 L# g8 C: ?3 {( G - Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False! z$ U8 I' q ?1 m1 A$ h9 N
- 4 z8 `3 I# M9 M0 l n
- Application.StatusBar = "复制社区居委列"
8 V" r3 Q, o0 q+ V \9 B4 i( k1 s - Collection.Offset(0, 4).Copy) }5 o0 @, x! i; C2 u& f* i
- Sheets(filename).Select+ }9 S( B! n* e6 }! T9 d
- startCell.Offset(0, 8).Select
/ u# A% z, R) N2 @2 s - Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False( }% q/ @: v9 v# l1 N2 Z
-
+ w9 m# Z U% ~% N6 b -
6 e ]! n; F+ A* q6 L$ ?( o( F - TotalWB.Sheets("采样单模板").Visible = False
* d/ q) p9 g0 p* N- D - TotalWB.Sheets("纳管汇总表").Activate; @6 c# A+ j% _
- NewTestForm.Sheets(filename).Activate& }! {' E5 l. _3 C2 Z4 c
- 5 f9 Z' t( b4 }) Y$ S
- Excel.Application.Calculation = xlCalculationAutomatic9 o! U! l/ w+ k a
- Application.StatusBar = "创建采样单完毕"/ Z1 q# T9 R6 O/ P4 ~ F
- End Sub
. V# I8 q" k/ r - 6 n' w4 {$ s1 W C( E/ A" G
- Private Sub CreateRnaTestPlanByManageType(calculatedDate)
: c( p @ e8 O; Y4 [4 y - Application.StatusBar = "根据管理类型生成采样单"
3 w7 \6 s# X6 b7 a - Set plan = Range(Selection.Offset(0, 2), Selection.End(xlToRight))
) I9 ]$ Y3 F& w9 k0 s; Z+ A1 Y0 ] -
2 l: [0 t0 ]5 H% ~; a/ m* k9 U - Dim filename As String( W% v$ h w$ k( h! D2 \
- Dim manageType As String6 q: d9 u0 ?; X0 g8 h6 r6 d
- Dim arrayForDate() As Variant: T- Y) @- U% Z: ^$ s
-
3 g+ ?/ N" ^. x9 s0 k* K -
) e- w5 t: |+ L# J - filename = "【采样计划】" & (Selection.Offset(0, 1).Value) & Format(calculatedDate, "mmdd")
1 g' O$ o" V( x% N! d - manageType = Selection.Value/ }. E$ K$ o0 f. L+ }/ k" T
- '计算筛选器的纳管日期勾选+ h; h9 P+ S# H5 Z) {
- Count = plan.Count * 2 - 11 s; {, z8 w( S0 h
- ReDim arrayForDate(Count)$ H6 y, U& N+ x E+ e; @3 a
- For i = 0 To plan.Count - 1' f5 ?! I j6 O* Z4 y
- arrayForDate(i * 2) = 2
" [+ L* c9 e% U( |" k8 b6 _ - targetDate = calculatedDate + 1 - plan.Cells(i + 1).Value% ^. k( x5 I. Z
- arrayForDate(i * 2 + 1) = targetDate
( }/ Q u% Z6 a" i" V4 E - Next( p1 f8 U, C% s- P4 ]) c3 c8 C
- $ G( F, d# { g3 Y6 R$ Z
- Application.StatusBar = "根据管理类型和日期创建筛选"' ? P9 H! f) ~% Q" x+ R
- Excel.Application.Calculation = xlCalculationManual4 g# b3 F0 A6 g8 t0 t
- '重置筛选5 y1 ^: r/ q0 O) J" _0 w0 a$ o
- Sheets("纳管汇总表").Activate' S1 c( d- y- S+ w
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=1 ^! x4 d8 \/ R3 p9 p
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=2, Criteria1:=manageType
5 V+ t" w( q: Y) P, k6 d% V - ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=3; O2 F: y" c: X, D, m
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=4& [1 o; C& ^$ @- ]( p8 A
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=5
4 A8 s7 j8 z' E! V7 Y - ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=6
+ m1 A% B% U: N: D& q - ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=7; T. s" D4 c& ]) d. x
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=8
* T# L* X0 w1 w) _% P - ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=9' W& t) R m. A O* E& Z
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=10
# b" F9 a) o. m - ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=11, Operator:=xlFilterValues, Criteria2:=arrayForDate1 Y7 Y) q I, B
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=12' \' b I. G) U& N
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=13, Criteria1:="="- Z2 i6 J- k: \2 _4 l) Q
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=14* |! N6 n% d) |
- ActiveSheet.ListObjects("表2").Range.AutoFilter Field:=154 D! y: P7 ?# b. i+ F7 @, q' @: S
- Excel.Application.Calculation = xlCalculationAutomatic% R2 @* ], s3 [: Y6 H( i- q
- $ t/ X' g1 a" `( U. q
- Range("O1").Select
0 V, _/ m* X; J* G/ H; | - Call CreateRnaTestForm(filename)$ s8 }4 u" H4 ~4 G' }& L
- End Sub
4 c% b$ `; E! d3 O1 ?1 w - 6 v0 q% B1 r$ @: {
- Sub CreateRnaTestPlanBySelectedManageType()
6 d/ a @" U# Z) ] - Dim calculatedDate
, |6 n/ S, Z+ h/ w - Set magicSheet = ActiveSheet: \' h" `4 e% w8 B
- If IsDate(Range("M14").Text) Then- M9 F4 o6 A% H
- calculatedDate = CDate(Range("M14").Text)
8 o6 t" }+ ?3 J9 S4 l - MsgBox "将要生成" & Range("M14").Text & "的采样计划"
; f) b3 z! I, r2 } - Else
! F) F+ C( H" l% A( a* G, U - MsgBox "M14单元格输入的内容无法转换为日期"0 C, q! D* _3 l9 {
- Exit Sub
7 J2 E( |! U1 T - End If) i' q1 y0 D9 s$ ?' Z
-
* ~ u2 X; n% G8 w6 l! k( D X; l0 z - Call CreateRnaTestPlanByManageType(calculatedDate)) L" J5 W9 Q: U8 U5 ^
- End Sub( `2 E9 D0 U. m6 C& r/ e
- & P% G; T$ F3 b1 k7 t
- Sub CreateRnaTestPlayBySelectedDate()3 O: W S* C3 A1 F' _
- Dim calculatedDate$ C; h2 q2 T- t4 w- L$ B
- Set magicSheet = ActiveSheet8 R* i7 n+ W& S7 K) b* M: w/ Z( f
- If IsDate(Range("M14").Text) Then r& A6 x* f5 \8 Q- S8 J" S
- MsgBox "将要生成" & Range("M14").Text & "的采样计划"
' X! ~: L! t! | T2 T7 }# F - calculatedDate = CDate(Range("M14").Text)6 R( r. L; [1 q0 H" o
- Else
& g% A" g9 {$ W# E5 C- N/ _ - MsgBox "M14单元格输入的内容无法转换为日期"
- b9 k$ {; U. u% X - Exit Sub# @' ]# N; j; t: G- b' y9 D
- End If
8 b5 X1 p- s- t l7 l) M/ O4 q9 T -
2 z7 D3 [) H. [3 k2 n7 h - Set target = Range("H4", Range("H4").End(xlDown)); p2 m0 ]( z6 F# |% U' o
- For i = 1 To target.Count
) R6 j3 p9 Y6 F% y; W% y - magicSheet.Activate0 N( `( k7 z4 r: H- W
- target.Cells(i).Select* `/ M1 O- q- ~# C9 _
- CreateRnaTestPlanByManageType (calculatedDate)5 R& C6 Q5 s! l: y( H) `- ]/ A
- Next6 _; i- F8 t+ J8 V3 a: |. ?) Y9 _) R
- End Sub
( I* Q, T! C2 k8 D! ^3 R4 ]1 y( B
7 z) G, u( a9 m+ ]- Sub SetM14ToYesterday()
" ^' ~% q9 H" n2 E6 N% L7 Z7 b - Range("M14").Value = Date - 1
" N% z, n1 D: D3 s" i3 I& d2 S - End Sub3 c1 H3 D9 a! }5 X+ [* V
/ r. w* y( P) b6 e2 p) {/ u- Sub SetM14ToToday()- H4 m) i9 h2 r, P* Y. I+ Z
- Range("M14").Value = Date
8 M: v- d. v0 |' U1 ^+ |& Z: [9 Y - End Sub7 O$ p9 ]: q! X: D3 ?
! p0 t6 @1 n6 q9 _: M, V) U! G- Sub SetM14ToTomorrow()9 p X# `( [ r) O. h( ]2 Y
- Range("M14").Value = Date + 1 M: R6 l( g% _0 @. E
- End Sub
复制代码
2 D1 w5 g3 X% M% U* W根据上述代码,我们既可以先在M14单元格设置日期,然后点击生成特定的采样计划,又可以生成全部采样计划,也可以在纳管汇总表中选择特定的对象,生成特定人的采样单。6 J/ x3 a: n+ G7 Y5 H7 D
) x' n0 ~2 }1 N" s4 D尽管这些代码用于安排采样,但事实上转运、转单、派流调单,本质上都是大同小异的。' B9 {' }6 @0 c' m' {7 c
6 Q3 {% }3 t O, G3 h- J' o
$ I/ T* c3 M$ P1 S5 \
3. 关于没有编程经验,VBA该如何上手?
, |) P6 I# ~8 y! M1 f/ _! S( _5 I* @! C) F6 X
首先,请克服远离舒适圈的恐惧感。不要将编写VBA联想为编程,想象它是SPSS或者SAS,把EXCEL工作簿和工作表想象成数据集,VBA是一种“特殊”的统计脚本,我们只是在做本专业的东西,并非编程。 C6 \4 }4 \6 p h$ \- s0 s2 E, Z
0 x z; Y& } E$ y0 G" v其次,OFFICE套件内置宏录制功能,许多复杂的操作微软可以帮你自动生成VBA代码,我们只需要按照需求,对自动生成的代码进行Ctrl+CV,以及小修小补,绝大多数时候无需大动干戈。关于如何使用宏录制自动生成代码,可以参考如下教程:# h8 C) G: F o' X$ `6 j2 {" {7 U
“录制简单的宏”章节
% K* w! j* {5 s4 d% H' f( o4 Lhttps://www.w3cschool.cn/excelvba/excelvba-what.html
1 R# @) R3 Q( N1 n# m$ {1 r. O+ t q4 j2 L
4. 有哪些需要注意的?1 B# g' ]% I; ^# B( `" E( d
Excel的数据类型非常灵活,允许单元格中存储各式各样的内容并可能导致错误,例如最常见的身份证号变成科学计数法数字,还有固定电话带区号的丢失起始字段的0,因此将单元格数据类型明确设置是一个好习惯(无论你是否使用VBA自动处理),建议使用Excel的表格功能(Ctrl+T,也叫超级表),可以降低筛选、排序、以及数据复制粘贴时出错的可能。8 q/ I' v# D4 Q% t" h/ l
) j0 ?1 ^$ t- u0 `7 R9 f4 K/ u
最后,特别强调,程序绝对不是万能的。VBA可以将我们从繁琐机械的报表中解放出来,但是我们依然需要监督VBA正确工作,务必在自动生成报表后追加人工抽查、质控。 |
|