In excel hacks 1

  • Published on
    19-Jul-2015

  • View
    224

  • Download
    13

Embed Size (px)

Transcript

  • Excel

    Hacks

    CCcc ttuuyytt cchhiiuuttrroonngg EExxcceell

    11

    Nguyn bn: EXCEL HACKS

    Tc gi: David Raina Hawley

    Lc dch v b sung: Ptm0412 Ttphong2007 BNTT

  • www.giaiphapexcel.com CC TUYT CHIU CA EXCEL

    2

    CCcc ttuuyytt cchhiiuu ttrroonngg EExxcceell

    PPHHNN 11:: CChhnngg 11 vv CChhnngg 22

    Chng 1. GIM BT NHNG CC NHC KHI LM VIC VI BNG TNH ................................ 4

    1. Ty bin ca s lm vic ca bng tnh ...............................................................................................10

    2. Nhp d liu ng thi vo nhiu Sheet .............................................................................................13

    3. Ngn chn ngi s dng thc hin mt s hnh ng no nht nh ........................16

    4. Ngn chn cc nhc nh khng cn thit ...........................................................................................20

    5. n Sheet sao cho ngi khc khng th dng lnh Unhide hin ra ................................23

    6. T thit k mt bng tnh mu (template) .........................................................................................24

    7. To ch mc cho cc Sheet trong Workbook .....................................................................................28

    8. Gii hn vng cun ca bng tnh .........................................................................................................32

    9. Kha v bo v nhng c cha cng thc ......................................................................................35

    10. S dng nh dng theo iu kin tm d liu trng ..............................................................39

    11. Tm d liu xut hin hai hoc nhiu ln bng cng c Conditional Formating ...............41

    12. To ring mt thanh cng c cho ring mt bng tnh c th .................................................42

    13. Sao chp cng thc gi nguyn tham chiu tng i (ging nh sao chp tham chiu tuyt i) ...................................................................................................................................44

    14. G b nhng lin kt ma ............................................................................................................................45

    15. Gim kch thc file Excel b phnh to bt thng ..........................................................................47

    16. Cu d liu t mt bng tnh b li ........................................................................................................50

  • MC LC

    3

    Chng 2. NHNG TH THUT VI CC TNH NNG C SN TRONG EXCEL ......................54

    17. S dng Data Validation khi danh sch ngun nm trong mt Sheet khc ........................55

    18. iu khin Conditional Formating bng checkbox ........................................................................57

    19. nh du nhng cha cng thc bng Conditional Formatting .........................................60

    20. m hoc cng nhng c nh dng c iu kin .........................................................61

    21. T mu dng xen k .....................................................................................................................................64

    22. To hiu ng 3D trong cc bng tnh hay cc ...............................................................................66

    23. Bt, tt chc nng Conditional Formatting bng mt checkbox .............................................71

    24. Dng nhiu List cho mt Combobox ....................................................................................................72

    25. To mt list xc thc thay i theo s la chn t mt list khc ..............................................74

    26. S dng chc nng thay th (Replace) g b cc k t khng mong mun ................77

    27. Chuyn i con s dng vn bn sang s thc ................................................................................78

    28. Trch xut d liu s trong mt chui bng VBA .............................................................................79

    29. Ty bin ch thch ca bng tnh ........................................................................................................82

    30. Sp xp theo th t da theo nhiu hn ba ct ..............................................................................85

    31. Sp xp ngu nhin ......................................................................................................................................86

    32. Thao tc trn d liu vi Advanced Filter ............................................................................................87

    33. To cc nh dng s cho ring bn ......................................................................................................93

    34. Tng thm s ln Undo cho Excel ...........................................................................................................99

    35. T to danh sch fill ............................................................................................................................. 100

    36. Lm ni cc Subtotal ca Excel ............................................................................................................. 102

    37. Chuyn i cc hm v cng thc trong Excel thnh gi tr ................................................... 105

    38. Thm d liu vo danh sch Validation mt cch t ng ...................................................... 107

    39. Ngy thng trong Excel ............................................................................................................................ 109

    40. Cho php s dng tnh nng Group and Outline trn bng tnh b kho ......................... 112

    41. By li trng d liu ............................................................................................................................. 114

    42. Gim danh sch x xung ca Validation sau khi chn mt ................................................... 116

    43. Thm cc danh sch c sn v c danh sch t to vo menu chut phi ....................... 117

  • www.giaiphapexcel.com CC TUYT CHIU CA EXCEL

    4

    CChhnngg 11

    GGIIMM BBTT NNHHNNGG CCCC NNHHCC KKHHII LLMM VVIICC VVII BBNNGG TTNNHH

    Trong qu trnh thc hin mt bng tnh, chng ta c th mc phi v s li, hoc c th mc phi nhng sai lm ng ngn, v thng khng ti nhng ci c th gip chng ta x l bng tnh nhanh hn, gn gng hn...

    Trc khi ni n cc Tuyt chiu ca Excel, xin ni s qua mt cht v mt s th thut v quy tc c bn, nhng iu s gip cc bn thc hin nhng Tuyt chiu c ni n trong cc phn sau cch d dng hn.

    Excel l mt phn mm bng tnh rt mnh, v bn c th lm c nhng iu khng th tin ni vi n. Nhng khng may, c nhiu ngi thit k bng tnh ca mnh vi qu nhiu s "lo xa", cn thn qu mc cn thit, v lm cho n tr nn phc tp hn l n ng b nh vy. Phn m u ny s mch cho bn mt s th thut v quy tc m nu bn lm theo, bn s thy chng rt hiu qu.

    Quy tc 80/20 C l vic quan trng nht khi thc hin mt bng tnh l dnh mt thi gian nghin cu, xem xt cch b tr d liu, tnh trc nhng vic mnh cn lm. V ng bao gi ngh rng sau khi hon thnh, ti s khng cn phi thm vo cc d liu hoc cc cng thc khc, v bn s lun lm ngc li.

    iu ny c ngha rng, bn nn dnh khong 80% thi gian ln k hoch cho mt bng tnh v khong 20% thi gian thc hin bng tnh .

  • Chng 1 GIM BT NHNG CC NHC KHI LM VIC VI BNG TNH

    5

    Cho d rng, vic ny c v khng cn thit vi nhng bng tnh nh, nhng bo m rng, nu bn quan tm n iu ti va ni trn, th v lu v di, bn s trnh c nhng cc nhc khi phi c phi sa i sa li bng tnh ca mnh, v d dng thc hin c nhng bng tnh ln sau khi dnh cho n mt thi gian ln k hoch thc hin. Nn nh rng, cc bng tnh phi lun mang li cho ngi dng nhng thng tin chnh xc, ch khng phi l thng tin ch chnh xc trong mt trng hp no .

    Nhng mo nh v cu trc mt bng tnh Mt trong nhng li m chng ta hay mc phi khi to mt bng tnh, l chng ta khng thit lp v trnh by d liu theo ci cch m Excel v cc tnh nng ca n mong i. Sau y l mt s li ph bin chng ta hay mc phi khi thit lp mt bng tnh:

    B tr d liu trong nhiu bng tnh (Workbook) khc nhau B tr d liu trong nhiu trang tnh (Worksheet) khc nhau B tr d liu trong nhiu bng (Table) khc nhau C nhng hng trng v ct trng trong khi d liu (database) B trng nhng c d liu ging nhau (cng chung mt ngy, cng chung mt

    n v tnh, v.v...)

    Ba im u tin trn y ch mun ni n mt iu: Bn nn lun lun c gng gi cc mi lin quan gia cc d liu c lin tc trong cng mt bng d liu. Ti thy rt nhiu cc bng tnh khng c trnh by theo ci quy tc rt n gin ny, v chnh v vy, cc bng tnh khng th tn dng c ti a cc tnh nng mnh m ca Excel nh PivotTable, SubTotal... Bn ch c th s dng cc tnh nng ny khi bn gom ht d liu ca bn vo trong mt bng tht n gin.

    Khng phi ngu nhin m Excel c 1.048.567 hng (65.536 hng trong Excel 2003 tr v trc) m li ch c 16.384 ct (256 ct trong Excel 2003 tr v trc). iu ny c ni rng, bn nn thit lp d liu ca mnh vi cc tiu ct nm hng trn cng, v cc d liu c lin quan th nm lin tc bn di tiu ca n.

    Nu nh bn c nhng d liu c lp li hai ln hoc nhiu ln trong cc hng ca cng mt ct (cc ngy thng, cc loi n v tnh chng hn), bn hy chng li s cm d b trng cc .

    Hy c gng sp xp (sort) d liu ca bn bt c khi no c th. Excel c rt nhiu nhng cng c tm kim v tham chiu cng thc, v mt s khng nh trong , i hi d liu phi c sp xp theo mt th t hp l. Vic phn loi cng s gip ch ng k cho tc x l ca mt s cc hm.

    Nhng mo nh khi nh dng Ngoi vic thit k cu trc ca bng tnh cho hp l, vic nh dng cho n cng l mt vn cn bn n. Mc d mt bng tnh nn c nh dng sao cho d c v d theo di, nhng chng ta t khi ngh n vic s dng thi gian cho c hiu qu. Hy lun lun nh dng tht n gin. Rt nhiu ngi lng ph thi gian vo vic nh dng mt bng tnh mc d khng nht thit phi lm nh vy, v chnh iu ny lm nh hng n hiu qu cng vic.

  • 6

    www.giaip

    Vknhnh

    MhAD

    Cnhtngsn

    ChvngVthch

    phapexcel.c

    Vic thng ch thc, vhng n li ht m bn n

    Mt gi nng ny s

    Advanced FiMAX, v.v...

    ng ng qhng d ling thay c khc rnggi khc k, nhng th sau ny. C

    h nh dno trong ccgay c khi b

    V ti t hnheo kiu texha cng th

    Hnh 1 - Chn

    com

    xuyn p dv cho d b

    c th l mnn p dng

    a, l nn b c dnilter, hoc d)

    quan tm u kiu s, vi n. Nu bg l d likhi tham cht ra n l teCh c tiu

    ng cc thec c bn ngh rnn na, bt kxt, cng s bc li b n

    Center Across S

    dng nhng ng tnh c

    mt ni s hg cho mt b

    b trng vng cho nhdng lm v

    n vic canv canh tribn thay iu kiu s h

    hiu n mext. Nu bn ca bng

    eo kiu textnh dng t

    ng ci bn nk no chb nh dngnh dng the

    Selection thay v

    nh dng a bn c thhi cho ngbng tnh, l

    i hng u ng tnh nng iu ki

    nh l cho d cho nhngi kiu canhhay d liu t , v nhin thay i k

    g tnh l

    t khi tht strc theo knhp vo l

    ha cng thg thnh kiuo kiu text,

    v Merge Cells

    phc tp chh trng gii khc. M mu en, m

    tin trnng nng cn cho cc

    liu. Theog d liu kih l, bn s

    kiu text; vu khi h skiu canh lc hng ng

    cn thit. kiu text sd liu kic tham chiu text. Ni cphi khng

    CC TUY

    ho bng tnhng nh mtt trong nhmu trng v

    n cng (t nhcao hn v

    cng thc

    o mc nhu text. V c th khn

    v n cn c tng rng mc nh, b

    goi l m th

    Bi v tt cb chuyn t

    u s hay diu n mchung, bn

    g.

    Trn cl mt cng thth chyc trtrn , nng Cenm Alignmetn dm ra htab AligBn s ca khuCenter A

    Hoc bclick rishortcutFormat Ctrl+1.

    YT CHIU C

    h cn lm ct tc phm

    hng kiu phv mu xm

    ht l 3 hn sau, chnx l d li

    h, Excel can l iu t

    ng xc nh th gy nhg d liu trobn s b nhhi.

    c nhng dthnh d li liu kiu nt cu c mu

    c (merge vn . Cc hoc may c vi n. Cho nbn hy

    enter acrosstrong H

    ent, nhn vi cng bhp thoi nment cdng thanh

    ung HorizonAcross Sele

    n cng ci chn Formt menu Cells)

    CA EXCEL

    cho n tngngh thut,hi mu tt

    m.

    ng). Nhngng hn nhu (DSUM,

    nh phi chott nht ri,

    h c ngayhm ln choong lhc u vi

    liu nhpu kiu text,ngy thng.c nh dngn nhng

    cells) cngC mt sacro khngnhng n, thay vdng chcs selection,Home vo ci min phi, sFormat vic chn sn.h trt dctal chn

    ection.

    th right-mat Cells t

    (Excel2003:hay nhn

    L

    g , t

    g ,

    o ,

    y o i

    p , .

    g

    g g c ,

    i i . c n

    - :

    n

  • Chng 1

    NMmtri

    Vhthbcth

    tnc20

    MtnkhOfTo

    GIM B

    hng moMt sai lm mng d liu

    iu, chc

    V d, gi sm tm kimhm d liu cc hng c th hn, lhm d liu

    l mt thnh tr nn n tham ching c c003 tr v tr

    Mt vn nnh ton rt huyn l hffice gc ools Option

    T NHNG C

    o nh khi drt ln khi

    u. iu nyc chn khn

    bn c mm ca Exce

    vo mng ca cc ct A1:H1048vo trong m

    i quen v ch, thm u ra khi cp nht tronc) hoc s

    na thng chm khi d

    y chuyn tri pha tr

    ns Calcula

    H

    CC NHC

    dng cng s dng c

    y lm cho Eng b st m

    t mng d lel trch rany, nn trong mng

    8567, v bmng.

    cng tai hch l khn

    cng thc, mng tham chi dng cc N

    xy ra vi d liu ci ch rn ca bn

    ations), ri ch

    Hnh 2 - Chn M

    KHI LM V

    thc ng thc l c

    Excel phi kt d liu n

    liu t A1 a nhng d chc n, bg ny. Khi n ngh rng

    i m bn phng chy nim li bo iu ca cngName ng.

    nhng bngc cp nht. tnh ton c

    ng tnh, ri hn Manual

    Manual Caculatio

    IC VI BN

    cho n thamkim tra hno.

    n H1000, liu cn thbn to tham, tham chig lm nh v

    hi lun trni. Bn vn m c ng thc, bn.

    g tnh ln, m gii qua Excel thchn Exce

    :

    on vi Excel 200

    NG TNH

    m chiu nng ngn , n

    v bn quyhit. Bi v m chiu troniu ca bn

    vy th bn s

    nh xa n. Bc th loi nhng d li

    ng cch dng

    m cu hnhuyt vn hnh dng Ml Options

    07

    n ton b cnu khng n

    yt nh s dbn cn ph

    ng cng thc th s l

    s khng lo

    Bi n s lmb nhng pu mi thmg Table (Lis

    h my li nhny, bn th

    Manual: Nh Formulas

    c ct trongni l hng

    dng nhnghi b sungc n ton A:H, hoclng g khi

    m cho bngphn khngm vo s tst trong Excel

    h, l Excelhng chn vo nt (Excel2003:

    7

    g g

    g g n c i

    g g l

    l c t :

  • 8

    www.giaip

    Tutnchckh

    Vchhocmkhrb

    Ctnhmn

    Excth

    phapexcel.c

    uy nhin, lnh th bao gh tnh top nht. Nuhng phi l

    Vy bn th hy chm lon ton vng thc ch

    m lm tip?hng ngn nng cn phin nn suy n

    ng thc mt khi tham hng dy d

    mng tham c dng hn

    xcel c nhi cp n trc . Ngoi hc trn nh

    com

    H

    i khuyn gi cng c on Manualu mun c c no bn c

    cn nhc xei, bn s so ci thngho n chy n? Hu nh ngi t bi t bng tngh n vi

    mng l mt chiu n liu ln, thiu n mlun, v b

    u hm chuong chiu thra, trong E

    ng bng d

    Hnh 3 - Chn M

    th khngnhng cng

    l, c th bnc thngcng nh i

    em: Nu nhsa li cn g tay? Nghnhanh hn,chng ti kng tnh canh ca mnc thit k l

    trong nhnnhng ri

    th hy s dmt dy d lin bt buc p

    uyn x l nh 79: Trn

    Excel Help c liu ln

    Manual Caculatio

    g hay cho lg thc tnh n s ch c g tin chnh iu ny!

    h cn thngthng chna l, khi b, hay l t khng bao ga h trong cnh trong chli bng tnh

    ng nguyn nng l. Nhng chng ciu ln, hiuphi dng c

    nhng d linh li #VALc mt s v trch ra k

    on vi Excel 200

    lm, m chton, nu bc nhngxc, bn ph

    g chn b kn, hay l gng tnh cn vo ch

    gi lm ich tnh tnh toh.

    nhn ca vng nu bncng t cngu sut ca b

    ch tnh t

    u ln thay UE! khi tnhv d rt hat qu da th

    CC TUY

    03

    l mang tn ang ch

    g thng tin chi nhn F9

    t v lm ch b ci tha bn chy tnh tou ny, nhh ton Manun Manual t

    n trn. Cn dng chg tt. Khi cbng tnh ston bng ta

    cho cng thh tng (SUM

    ay s gip bheo nhng t

    YT CHIU C

    nh i phy mt bngc mm, ch

    9. Nhng, ch

    ho chic xe hng chn ry chm, bnon bng tayng rt nhiual. Nu nhth mi lm

    Chng l mng tham mt s l b nh hay.

    hc mng, mM) hoc mbn cch tiu chun.

    CA EXCEL

    . Mt bngg tnh trong

    ha h chc chn l

    hi ca bni tin tngn s sa liy ri c thu ngi h bn thy

    m vic c,

    t la chnm chiu nng ln ccng, i khi

    m s cm (COUNT)

    dng cng

    L

    g g c

    n g i y ,

    n n c i

    c ) g

  • Chng 1 GIM BT NHNG CC NHC KHI LM VIC VI BNG TNH

    9

    Hoc bn c th s dng PivotTable, s c cp n trong phn th 4 ca lot bi ny. Mc d PivotTable th c v phc tp khi bn s dng n ln u tin, nhng chng ti thnh tht khuyn bn hy hc cch s dng n. Mt khi bn lm ch c PivotTable, ngy no bn s t hi rng lm sao ti c th sng m khng c PivotTable!

    Trc khi kt thc phn m u, xin n...