Amathebula ahlakaniphile ku-Excel

Ividiyo

Ukwakhiwa kwenkinga

Sinetafula okufanele sisebenze ngalo njalo (ukuhlunga, ukuhlunga, ukubala okuthile kulo) kanye nokuqukethwe okushintsha ngezikhathi ezithile (engeza, susa, hlela). Nokho, okungenani, ngokwesibonelo - nakhu kufana nalokhu:

Ubukhulu - kusukela emashumini ambalwa kuya emigqeni eyizinkulungwane ezingamakhulu amaningana - akubalulekile. Umsebenzi uwukwenza lula futhi wenze ukuphila kwakho kube lula ngazo zonke izindlela ngokuphendula la maseli abe itafula “elihlakaniphile”.

Isixazululo

Khetha noma iyiphi iseli kuthebula nakuthebhu Ikhaya (Ikhaya) nwebisa uhlu Fometha njengetafula (Fometha njengethebula):

Ohlwini oluya phansi lwezitayela, khetha noma iyiphi inketho yokugcwalisa ekunambithekeni kwethu nombala, futhi efasiteleni lokuqinisekisa lebanga elikhethiwe, chofoza OK futhi sithola okukhiphayo okulandelayo:

Ngenxa yalokho, ngemva kokuguqulwa okunjalo kwebanga libe "smart" Ithebula (nosonhlamvukazi!) sinezinjabulo ezilandelayo (ngaphandle kwedizayini enhle):

1. Wadala Ithebula uthola igama Ithebula 1,2,3 njll. engashintshwa iye kwenele kakhulu kuthebhu umakhi (Umklamo). Leli gama lingasetshenziswa kunoma imaphi amafomula, uhlu lokudonsela phansi, nemisebenzi, njengomthombo wedatha wethebula le-pivot noma uhlu lokubheka lomsebenzi we-VLOOKUP.
2. Idalwe kanye Ithebula ilungisa ngokuzenzakalelayo usayizi lapho wengeza noma ususa idatha kuyo. Uma wengeza kulokho Ithebula imigqa emisha - izokwelula phansi, uma ungeza amakholomu amasha - izokwandisa ububanzi. Ekhoneni elingezansi kwesokudla Amathebula ungabona umaka womngcele ohamba ngokuzenzakalelayo futhi, uma kunesidingo, ulungise indawo yawo ngegundane:

    

3. Esigqokweni Amathebula ngokuzenzakalelayo I-AutoFilter iyavula (ingaphoqeleka ukuthi ikhubaze kuthebhu Idatha (Usuku)).
4. Lapho wengeza imigqa emisha kubo ngokuzenzakalelayo wonke amafomula akopishiwe.
5. Lapho udala ikholomu entsha ngefomula - izokopishwa ngokuzenzakalelayo kuyo yonke ikholomu - asikho isidingo sokuhudula ifomula enesiphambano sokuqedela ngokuzenzakalela esimnyama.
6. Uma uskrola Amathebula phansi izihloko zekholomu (A, B, C…) zishintshwa zibe amagama ezinkambu, okusho ukuthi awusakwazi ukulungisa unhlokweni wobubanzi njengangaphambili (ku-Excel 2010 kukhona nesihlungi esizenzakalelayo):
7. Ngokunika amandla ibhokisi lokuhlola Bonisa umugqa ophelele (Ingqikithi yomugqa) ithebhu umakhi (Umklamo) sithola umugqa wesamba ozenzakalelayo ekugcineni Amathebula ngekhono lokukhetha umsebenzi (isamba, isilinganiso, ukubala, njll.) kukholomu ngayinye:
8. Kudatha ku Ithebula kungabhekwana nayo usebenzisa amagama ezakhi zawo ngazinye. Isibonelo, ukuhlanganisa zonke izinombolo kukholamu ye-VAT, ungasebenzisa ifomula =IMALI(Ithebula1[VAT]) esikhundleni = SUM (F2: F200) futhi ukungacabangi ngosayizi wethebula, inani lemigqa kanye nokunemba kwezigaba zokukhetha. Kungenzeka futhi ukusebenzisa izitatimende ezilandelayo (kucatshangwa ukuthi ithebula linegama elijwayelekile Ithebula 1):

• =Ithebula1[#Konke] - isixhumanisi setafula lonke, okuhlanganisa izihloko zekholomu, idatha kanye nomugqa ophelele
• =Ithebula1[#Idatha] - isixhumanisi sedatha kuphela (ayikho ibha yesihloko)
• =Ithebula1[#Izihloko] – xhumanisa kuphela umugqa wokuqala wethebula onezihloko zekholomu
• =Ithebula1[#Amanani] - isixhumanisi kumugqa ophelele (uma ufakiwe)
• =Ithebula1[#Lo mugqa] — ireferensi yomugqa wamanje, isibonelo, ifomula =Ithebula1[[#Lo mugqa];[VAT]] izobhekisela enanini le-VAT kumugqa wethebula lamanje.

  (Enguqulweni yesiNgisi, lawa ma-opharetha azozwakala, ngokulandelana, njengokuthi #All, #Data, #Headers, #Totals kanye nokuthi #Lo mugqa).

PS

Ku-Excel 2003 kwakukhona into efana nokude namatafula “ahlakaniphile” anjalo - yayibizwa ngokuthi Uhlu futhi yadalwa ngemenyu. Idatha - Uhlu - Dala Uhlu (Idatha - Uhlu - Dala uhlu). Kodwa ngisho nengxenye yokusebenza kwamanje yayingekho nhlobo. Izinguqulo ezindala ze-Excel nazo zazingenakho lokho.