Ma tebulo a Smart mu Excel

Video

Kupanga vuto

Tili ndi tebulo lomwe timayenera kugwirira ntchito nthawi zonse (kusintha, zosefera, kuwerengera china chake) ndi zomwe zili mkati mwake zomwe zimasintha nthawi ndi nthawi (onjezani, chotsani, sinthani). Chabwino, osachepera, mwachitsanzo - apa zili motere:

Kukula - kuyambira makumi angapo mpaka mizere mazana angapo - sikofunikira. Ntchitoyo ndi kufewetsa ndi kupangitsa moyo wanu kukhala wosavuta mwanjira iliyonse potembenuza ma cellwa kukhala tebulo "lanzeru".

Anakonza

Sankhani selo iliyonse patebulo ndi pa tabu Kunyumba (Kunyumba) onjezerani mndandanda Pangani ngati tebulo (Monga ngati tebulo):

 

Pamndandanda wotsikira pansi wa masitayelo, sankhani njira iliyonse yodzaza ku kukoma ndi mtundu wathu, ndipo pazenera lotsimikizira zamitundu yomwe mwasankha, dinani. OK ndipo timapeza zotsatira zotsatirazi:

Zotsatira zake, pambuyo pakusintha kotereku kwa "smart" Table (ndi zilembo zazikulu!) tili ndi zosangalatsa izi (kupatula kapangidwe kabwino):

  1. analengedwa Table amapeza dzina Gulu 1,2,3 etc. zomwe zingasinthidwe kukhala zokwanira kwambiri pa tabu Constructor (Kapangidwe). Dzinali litha kugwiritsidwa ntchito pamitundu iliyonse, mindandanda yotsikira pansi, ndi magwiridwe antchito, monga gwero la data pa tebulo la pivot kapena mndandanda wazoyang'ana ntchito ya VLOOKUP.
  2. Adapangidwa kamodzi Table imangosintha kukula powonjezera kapena kuchotsa deta kwa izo. Ngati muwonjezera izi Table mizere yatsopano - idzatambasula m'munsi, ngati muwonjezera mizati yatsopano - idzawonjezeka m'lifupi. M'munsi kumanja ngodya Matebulo mutha kuwona cholembera chamalire chomwe chikuyenda chokha ndipo, ngati kuli kofunikira, sinthani malo ake ndi mbewa:

     

  3. Mu chipewa Matebulo basi AutoFilter imayatsa (akhoza kukakamizidwa kuletsa pa tabu Deta (Tsiku)).
  4. Powonjezera mizere yatsopano kwa iwo basi mafomu onse amakopedwa.
  5. Mukapanga ndime yatsopano ndi fomula - imakopera yokha pamndandanda wonse - palibe chifukwa chokoka chilinganizo chokhala ndi mtanda wakuda womaliza.
  6. Pamene scrolling Matebulo pansi mitu (A, B, C…) yasinthidwa kukhala mayina agawo, mwachitsanzo, simungathenso kukonza mutu wamtundu monga kale (mu Excel 2010 palinso zosefera):
  7. Potsegula bokosi loyang'anira Onetsani mzere wonse (Mzere wonse) tsamba Constructor (Kapangidwe) timapeza mzere wokwanira wokwanira kumapeto Matebulo ndi kuthekera kosankha ntchito (chiwerengero, avareji, kuwerengera, ndi zina) pagawo lililonse:
  8. Ku data mu Table akhoza kuyankhidwa pogwiritsa ntchito mayina a zinthu zake payekha. Mwachitsanzo, kuti muwerenge manambala onse omwe ali mugawo la VAT, mutha kugwiritsa ntchito fomula =SUM(Table1[VAT]) m'malo mwake = SUM (F2: F200) komanso osaganizira za kukula kwa tebulo, kuchuluka kwa mizere ndi kulondola kwa magawo osankhidwa. Ndikothekanso kugwiritsa ntchito ziganizo zotsatirazi (poganiza kuti tebulo lili ndi dzina lokhazikika Gulu 1):
  • =Gome1[#Onse] - ulalo ku tebulo lonse, kuphatikiza mitu yamndandanda, deta ndi mzere wonse
  • =Table1[#Data] - ulalo wa data-okha (palibe mutu wamutu)
  • =Table1[#Mitu] - Lumikizani pamzere woyamba wa tebulo wokhala ndi mitu yazazambiri
  • =Table1[#Totals] - ulalo ku mzere wonse (ngati waphatikizidwa)
  • =Table1[#Mzerewu] - ponena za mzere wamakono, mwachitsanzo, njira =Table1[[#Mzerewu];[VAT]] itanthauza mtengo wa VAT kuchokera pamzere wapatebulo wapano.

    (M'Chingerezi, ogwiritsa ntchito awa azimveka, motsatana, monga #All, #Data, #Headers, #Totals ndi #This mzere).

PS

Mu Excel 2003 panali china chake chofanana ndi matebulo "anzeru" oterowo - amatchedwa List ndipo adapangidwa kudzera pamenyu. Deta - Mndandanda - Pangani Mndandanda (Deta - Mndandanda - Pangani mndandanda). Koma ngakhale theka la magwiridwe antchito apano panalibe nkomwe. Mabaibulo akale a Excel analibe zimenezo.

Siyani Mumakonda