Luam cov qauv yam tsis muaj kev hloov pauv

txog cov teeb meem

Piv txwv tias peb muaj ib lub rooj yooj yim zoo li no, uas cov nyiaj tau suav rau txhua lub hlis hauv ob lub nroog, thiab tom qab ntawd tag nrho cov nyiaj tau hloov mus rau hauv euros ntawm tus nqi los ntawm lub xov tooj daj J2.

Luam cov qauv yam tsis muaj kev hloov pauv

Qhov teeb meem yog tias yog tias koj luam qhov ntau D2: D8 nrog cov qauv nyob rau lwm qhov ntawm daim ntawv, ces Microsoft Excel yuav kho qhov txuas hauv cov qauv no, txav mus rau qhov chaw tshiab thiab tsis suav:

Luam cov qauv yam tsis muaj kev hloov pauv

Kev Ua Haujlwm: luam cov ntau yam nrog cov qauv kom cov qauv tsis hloov thiab nyob twj ywm tib yam, khaws cov txiaj ntsig ntawm kev suav.

Txoj Kev 1. Kev sib txuas tsis raug

Raws li koj tuaj yeem pom los ntawm daim duab dhau los, Excel hloov tsuas yog cov txheeb ze txuas. Qhov tseeb (nrog $ kos npe) siv rau lub xov tooj daj $J$2 tsis tau tsiv mus. Yog li ntawd, rau qhov kev luam tawm ntawm cov qauv, koj tuaj yeem hloov pauv ib ntus tag nrho cov ntaub ntawv hauv txhua tus qauv mus rau qhov tseeb. Koj yuav tsum xaiv txhua tus qauv hauv lub formula bar thiab nias tus yuam sij F4:
Luam cov qauv yam tsis muaj kev hloov pauv
Nrog rau ntau lub hlwb, qhov kev xaiv no, ntawm chav kawm, ploj mus - nws ua haujlwm dhau lawm.

Txoj Kev 2: Tshem tawm cov qauv ib ntus

Txhawm rau tiv thaiv cov qauv los ntawm kev hloov pauv thaum luam tawm, koj yuav tsum (ib ntus) kom paub tseeb tias Excel tsis kho lawv li cov qauv. Qhov no tuaj yeem ua tau los ntawm kev hloov cov cim sib npaug (=) nrog rau lwm cov cim uas tsis nquag pom nyob rau hauv cov qauv, xws li lub cim hash (#) lossis ib khub ampersands (&&) rau lub sijhawm luam. Rau qhov no:

  1. Xaiv qhov ntau yam nrog cov qauv (hauv peb qhov piv txwv D2: D8)
  2. Nyem Ctrl + H ntawm lub keyboard lossis ntawm lub tab Tsev – Nrhiav thiab xaiv – Hloov (Lub Tsev - Nrhiav & Xaiv - Hloov)

    Luam cov qauv yam tsis muaj kev hloov pauv

  3. Nyob rau hauv lub dialog box uas tshwm sim, sau dab tsi peb tab tom nrhiav thiab dab tsi peb hloov nrog, thiab nyob rau hauv tsis (Kev xaiv) tsis txhob hnov ​​qab qhia meej Nrhiav Scope – Formulas. Peb nias Hloov tag nrho (Hloov tag nrho).
  4. Luam qhov tshwm sim ntau yam nrog cov qauv deactivated mus rau qhov chaw zoo:

    Luam cov qauv yam tsis muaj kev hloov pauv

  5. Hloov # on = rov qab siv tib lub qhov rais, rov qab ua haujlwm rau cov qauv.

Txoj Kev 3: Luam ntawm Notepad

Txoj kev no yog sai thiab yooj yim dua.

Nias lub keyboard shortcut Ctrl + Ua los yog khawm Qhia cov qauv tab mis (Formulas — Qhia cov qauv), tig rau cov qauv tshuaj xyuas hom - tsis yog cov txiaj ntsig, cov hlwb yuav tso tawm cov qauv uas lawv suav nrog:

Luam cov qauv yam tsis muaj kev hloov pauv

Luam peb ntau D2: D8 thiab muab tso rau hauv tus qauv Phau ntawv:

Luam cov qauv yam tsis muaj kev hloov pauv

Tam sim no xaiv txhua yam pasted (Ctrl + A), luam rau hauv daim ntawv teev lus dua (Ctrl + C) thiab muab tso rau hauv daim ntawv hauv qhov chaw koj xav tau:

Luam cov qauv yam tsis muaj kev hloov pauv

Nws tseem tsuas yog nias lub khawm Qhia cov qauv (Show Formulas)rov qab Excel rau hom qub.

Nco tseg: txoj kev no qee zaum ua tsis tiav ntawm cov rooj sib txuas nrog cov hlwb sib koom ua ke, tab sis feem ntau nws ua haujlwm zoo.

Txoj kev 4. Macro

Yog tias koj feem ntau yuav tsum ua qhov kev luam tawm ntawm cov qauv yam tsis muaj kev hloov pauv cov ntaub ntawv, ces nws ua rau kev txiav txim siab siv macro rau qhov no. Nias lub keyboard shortcut Alt + F11 los yog khawm Visual Basic tab tsim tawm (Tus tsim tawm), ntxig ib qho tshiab los ntawm cov ntawv qhia zaub mov Ntxig - Module  thiab luam cov ntawv ntawm no macro muaj:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Xaiv cov hlwb nrog cov qauv los luam.", _ "Copy formulas raws nraim", Default:=Selection.Address, Hom := 8) Yog tias copyRange Tsis muaj dab tsi Ces Tawm Sub Set pasteRange = Application.InputBox("Tam sim no xaiv qhov muab tshuaj txhuam." & vbCrLf & vbCrLf & _ "Qhov ntau yam yuav tsum sib npaug ntawm qhov loj me rau qhov qub" & vbCrLf & _" ntau yam ntawm cov hlwb to the copy." , "Copy formulas certainly", _ Default:=Selection.Address, Type:=8) Yog pasteRange.Cells.Count <> copyRange.Cells.Count Ces MsgBox "Copy and paste ranges vary in size!", vbExclamation, "Copy error" Exit Sub End Yog tias pasteRange Tsis muaj dab tsi ces Exit Sub Lwm pasteRange.Formula = copyRange.Formula Xaus Yog Xaus Sub

Koj tuaj yeem siv lub pob kom khiav lub macro. Ntsig tab tsim tawm (Tus tsim tawm - Macros) los yog keyboard shortcut Alt + F8. Tom qab khiav lub macro, nws yuav hais kom koj xaiv qhov ntau yam nrog cov qauv qub thiab cov kab ntxig thiab yuav luam cov qauv txiav:

Luam cov qauv yam tsis muaj kev hloov pauv

  • Yooj yim saib cov qauv thiab cov txiaj ntsig tib lub sijhawm
  • Vim li cas R1C1 siv cov qauv xav tau hauv Excel cov qauv
  • Yuav ua li cas kom sai nrhiav tag nrho cov hlwb nrog cov qauv
  • Cov cuab yeej luam tawm cov qauv tseeb los ntawm PLEX add-on

 

Sau ntawv cia Ncua