Koperani mafomu opanda ulalo kusintha

vuto

Tiyerekeze kuti tili ndi tebulo losavuta monga ili, momwe ndalamazo zimawerengedwera mwezi uliwonse m'mizinda iwiri, ndiyeno chiwerengerocho chimasinthidwa kukhala ma euro pamtengo kuchokera ku selo lachikasu J2.

Vuto ndilakuti ngati mungakopere mtundu wa D2:D8 wokhala ndi mafomu kwinakwake patsamba, ndiye kuti Microsoft Excel imangowongolera maulalo awa, kuwasunthira kumalo atsopano ndikusiya kuwerengera:

Ntchito: koperani mndandanda ndi ma formula kuti ma formula asasinthe ndikukhalabe ofanana, kusunga zotsatira zowerengera.

Njira 1. Maulalo amtheradi

Njira 2: Imitsani ma formula kwakanthawi

Pofuna kupewa mafomu kuti asinthe pokopera, muyenera (kwakanthawi) kuwonetsetsa kuti Excel ikusiya kuwatenga ngati mafomula. Izi zitha kuchitika posintha chizindikiro chofanana (=) ndi zilembo zina zilizonse zomwe sizipezeka m'ma formula, monga chizindikiro cha hashi (#) kapena ma ampersand (&&) pa nthawi yojambula. Za ichi:

1. Sankhani mtundu wokhala ndi mafomu (muchitsanzo chathu D2:D8)
2. Dinani Ctrl + H pa kiyibodi kapena pa tabu Kunyumba - Pezani ndikusankha - Bwezerani (Kunyumba - Pezani&Sankhani - Bwezerani)

   

3. Mu bokosi la zokambirana lomwe likuwonekera, lowetsani zomwe tikufuna ndi zomwe timasintha, ndi mkati magawo (Zosankha) osayiwala kumveketsa Kusakasaka - Mafomula. Timasindikiza Bwezerani zonse (Sinthani zonse).
4. Koperani mndandanda wazotsatira zomwe zidazimitsidwa ndikufika pamalo oyenera:

   

5. Sinthanitsani # on = bwererani pogwiritsa ntchito zenera lomwelo, kubwezera magwiridwe antchito ku mafomu.

Njira 3: Koperani kudzera pa Notepad

Njirayi ndiyofulumira komanso yosavuta.

Dinani njira yachidule ya kiyibodi Ctrl+E kapena batani Onetsani mafomu tsamba chilinganizo (Mafomula - Onetsani mafomu), kuyatsa njira yowunikira - m'malo mwazotsatira, ma cell awonetsa ma fomu omwe amawerengedwa:

Lembani mtundu wathu D2: D8 ndikuyiyika muyeso Notebook:

Tsopano sankhani chilichonse choyikidwa (Ctrl + A), koperaninso pa bolodi (Ctrl + C) ndikuyiyika papepala pamalo omwe mukufuna:

Zimangokhala kukanikiza batani Onetsani mafomu (Onetsani Mafomula)kuti mubwerere ku Excel kumayendedwe abwinobwino.

Zindikirani: njirayi nthawi zina imalephera pamatebulo ovuta omwe ali ndi maselo ophatikizidwa, koma nthawi zambiri zimagwira ntchito bwino.

Njira 4. Macro

Ngati nthawi zambiri mumayenera kuchita kukopera kotereku popanda kusuntha maumboni, ndiye kuti ndizomveka kugwiritsa ntchito macro pa izi. Dinani njira yachidule ya kiyibodi Alt + F11 kapena batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu), ikani gawo latsopano kudzera pa menyu Ikani - Module  ndikukopera zolemba za macro apa:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range on Error Resume Next Set copyRange = Application.InputBox("Sankhani ma cell okhala ndi mafomu oti mukopere.", _ "Koperani ma formula ndendende", Default:=Selection.Address, Type := 8) Ngati copyRange Si Kanthu Ndiye Tulukani Sub Set pasteRange = Application.InputBox("Tsopano sankhani phala." & vbCrLf & vbCrLf & _ "Mtundu uyenera kukhala wofanana ndi kukula " & vbCrLf & _" ma cell kukopera." , "Koperani mafomu ndendende", _ Default:=Selection.Address, Type:=8) Ngati pasteRange.Cells.Count <> copyRange.Cells.Count Kenako MsgBox "Koperani ndi kumata milingo imasiyanasiyana kukula!", vbExclamation, "Copy error" Tulukani Mapeto Ngati PasteRange Palibe Kanthu Ndiye Tulukani Zina PasteRange.Formula = copyRange.Formula End Ngati End Sub

Mutha kugwiritsa ntchito batani kuyendetsa ma macro. Macros tsamba Woyambitsa (Wopanga - Macros) kapena njira yachidule ya kiyibodi Alt + F8. Mukatha kugwiritsa ntchito macro, idzakufunsani kuti musankhe mtundu womwe uli ndi mafomu oyambira komanso momwe mungayikitsire ndipo mudzakopera mafomuwo okha:

• Kuwona koyenera kwa mafomula ndi zotsatira zake nthawi imodzi
• Chifukwa chiyani mawonekedwe a R1C1 amafunikira mu ma fomula a Excel
• Momwe mungapezere mwachangu maselo onse okhala ndi ma formula
• Chida chokopera mafomu enieni kuchokera pazowonjezera za PLEX