txheem
Qee lub sij hawm nws yuav siv sij hawm ntev heev los ua tej yam. Tab sis thaum lawv tau TAM SIM NO tau tsim, tom qab qhov tseeb lawv zoo li pom tseeb thiab txawm tias banal. Los ntawm cov series "dab tsi, nws ua tau?".
Los ntawm cov thawj versions, cov xwm txheej bar nyob hauv qab ntawm Microsoft Excel qhov rais ib txwm tso tawm tag nrho rau cov hlwb xaiv:
Yog tias xav tau, nws tseem tuaj yeem nyem rau ntawm cov txiaj ntsig no thiab xaiv los ntawm cov ntawv qhia zaub mov raws nraim uas peb xav pom:
Thiab tsis ntev los no, nyob rau hauv qhov tseeb Excel hloov tshiab, Microsoft cov neeg tsim khoom ntxiv qhov yooj yim tab sis ingenious feature - tam sim no thaum koj nyem rau ntawm cov txiaj ntsig no, lawv tau theej rau hauv cov ntawv teev cia!
Kev zoo nkauj.
Tab sis dab tsi txog cov uas tseem tsis tau (los yog twb?) muaj xws li ib tug version ntawm Excel? Nov yog qhov yooj yim macro tuaj yeem pab.
Luam cov lej ntawm cov hlwb uas tau xaiv mus rau Cov Ntawv Teev Npe uas siv cov macro
Qhib hauv tab tsim tawm (Tus tsim tawm) editor Visual Basic los yog siv cov keyboard shortcut no Alt+F11. Ntxig tshiab khoob module ntawm cov ntawv qhia zaub mov Ntxig - Module thiab luam cov cai hauv qab no rau hauv:
Sub SumSelected() Yog TypeName(Selection) <> "Range" Ces Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection Sub board).Put Nws logic yog yooj yim:
- Ua ntej los ntawm "kev tiv thaiv los ntawm cov neeg ruam" - peb xyuas qhov tseeb yog qhov tseem ceeb. Yog tias tsis yog cov hlwb raug xaiv (tab sis, piv txwv li, daim ntawv qhia), ces tawm ntawm macro.
- Tom qab ntawd siv cov lus txib Getobject peb tsim cov ntaub ntawv tshiab uas peb cov lej ntawm cov cell xaiv yuav muab khaws cia tom qab. Ib tug ntev thiab tsis to taub alphanumeric code yog, qhov tseeb, ib tug txuas mus rau lub Windows sau npe ceg uas lub tsev qiv ntawv nyob. Microsoft Forms 2.0 Object Library, uas tuaj yeem tsim cov khoom zoo li no. Qee lub sij hawm qhov kev dag no kuj hu ua implicit lig khi. Yog tias koj tsis siv nws, ces koj yuav tsum ua qhov txuas rau lub tsev qiv ntawv no hauv cov ntaub ntawv los ntawm cov ntawv qhia zaub mov Cov cuab yeej - Cov ntaub ntawv.
- Cov lej ntawm cov cell xaiv tau suav tias yog ib qho lus txib WorksheetFunction.Sum(xaiv), thiab tom qab ntawd cov txiaj ntsig tau muab tso rau ntawm daim ntawv teev lus nrog cov lus txib PutInClipboard
Rau kev yooj yim ntawm kev siv, koj tuaj yeem, tau kawg, muab cov macro no rau cov keyboard shortcut siv lub pob Ntsig tab tsim tawm (Tus tsim tawm - Macros).
Thiab yog tias koj xav pom dab tsi raws nraim tau theej tom qab khiav lub macro, koj tuaj yeem qhib lub vaj huam sib luag Clipboard siv tus xub me nyob rau sab xis sab xis ntawm pawg sib tham ntawm lub ntsiab (Tsev) tab:
Tsis yog tus nqi xwb
Yog tias, ntxiv rau qhov nyiaj banal, koj xav tau lwm yam, ces koj tuaj yeem siv ib qho ntawm cov haujlwm uas cov khoom muab rau peb. WorksheetFunction:
Piv txwv li, muaj:
- Sum - sum
- Nruab nrab - arithmetic mean
- Suav - cov xov tooj ntawm tes nrog cov lej
- CountA - tus naj npawb ntawm cov cell puv
- CountBlank - tus naj npawb ntawm cov hlwb khoob
- Min – yam tsawg kawg nkaus tus nqi
- Max - tus nqi siab tshaj
- Median - nruab nrab (tus nqi nruab nrab)
- … Lwm yam.
Yuav ua li cas yog cov kab los yog kab yog muab zais (manually los yog lim) nyob rau hauv lub xaiv ntau yam? Yuav kom tsis txhob coj lawv mus rau hauv tus account hauv tag nrho, peb yuav tsum tau hloov kho me ntsis peb cov cai los ntawm kev ntxiv rau cov khoom. xaiv vaj tse SpecialCells(xlCellTypeVisible):
Sub SumVisible() Yog TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}").SetText WorksheetFunction.Sum(Selection.Special.SpecialCell). PutInClipboard End With End Sub Hauv qhov no, kev suav ntawm txhua qhov haujlwm tag nrho yuav raug siv rau cov hlwb pom nkaus xwb.
Yog tias koj xav tau cov qauv ua neej nyob
Yog tias koj npau suav txog, koj tuaj yeem tuaj nrog cov xwm txheej thaum nws zoo dua los luam tsis yog tus lej (tsis tu ncua), tab sis cov qauv nyob rau hauv qhov tsis, uas suav tag nrho peb xav tau rau cov hlwb xaiv. Nyob rau hauv rooj plaub no, koj yuav tsum tau muab cov mis los ntawm cov khoom tawg, ntxiv rau nws tshem tawm cov cim nyiaj daus las thiab hloov lub cim (uas yog siv los ua qhov sib cais ntawm qhov chaw nyob ntawm ntau qhov kev xaiv hauv VBA) nrog lub semicolon:
Sub SumFormula() Yog TypeName(Selection) <> "Range" Ces Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Selection. Chaw nyob, ",", ";"), "$", "") & ")".PutInClipboard End With End Sub Summation nrog cov xwm txheej ntxiv
Thiab, thaum kawg, rau tag nrho maniacs, koj tuaj yeem sau ib qho macro uas yuav suav tsis tau tag nrho cov hlwb uas tau xaiv, tab sis tsuas yog cov uas txaus siab rau cov xwm txheej muab. Yog li, piv txwv li, macro yuav zoo li uas tso cov lej ntawm cov hlwb uas tau xaiv rau hauv Buffer, yog tias lawv cov txiaj ntsig ntau dua 5 thiab tib lub sijhawm lawv tau sau nrog cov xim:
Sub CustomCalc() Dim myRange As Range Yog TypeName(Selection) <> "Range" Ces Tawm Sub Rau Txhua Cell Hauv Kev Xaiv Yog cell.Value> 5 Thiab cell.Interior.ColorIndex <> xlNone Ces Yog tias myRange Tsis muaj dab tsi ces Teem myRange = Cell Lwm Teem myRange = Union(myRange, cell) Xaus Yog Xaus Yog Tom Qab Cell Nrog GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange).PutInClip Raws li koj tuaj yeem xav tau yooj yim, cov xwm txheej tuaj yeem teeb tsa kiag li txhua yam - mus txog cov qauv ntawm tes - thiab hauv txhua qhov ntau (xws li los ntawm kev sib txuas ua ke nrog cov tswv lag luam los yog thiab thiab). Muaj ntau chav rau kev xav.
- Hloov cov qauv rau qhov muaj nuj nqis (6 txoj hauv kev)
- Dab tsi yog macro, siv lawv li cas, qhov twg los ntxig Visual Basic code
- Cov ntaub ntawv muaj txiaj ntsig hauv qhov xwm txheej bar ntawm Microsoft Excel