Tchati ndi selo losankhidwa

Tiyerekeze kuti inu ndi ine tikuyenera kuwona zomwe zili patebulo lotsatirali ndi mitengo yogulitsa magalimoto ndi mayiko osiyanasiyana mu 2021 (zambiri zenizeni zomwe zatengedwa pano, mwa njira):

Popeza kuchuluka kwa ma data (maiko) ndiakulu, kuyesa kuwayika onse mu graph imodzi nthawi imodzi kumatha kubweretsa "tchati cha spaghetti" choyipa kapena kupanga ma chart osiyanasiyana pamndandanda uliwonse, zomwe ndizovuta kwambiri.

Njira yabwino yothetsera vutoli ingakhale kupanga tchati pazambiri kuchokera pamzere wapano, mwachitsanzo, mzere womwe selo yogwira ili:

Kukhazikitsa izi ndikosavuta - mumangofunika mitundu iwiri yokha ndi imodzi yaying'ono m'mizere itatu.

Gawo 1. Nambala ya mzere wamakono

Chinthu choyamba chomwe timafunikira ndi mndandanda wa mayina omwe amawerengera nambala ya mzere pa pepala limene selo yathu yogwira ntchito ilipo tsopano. Kutsegula pa tabu Mafomula - Woyang'anira Dzina (Mafomula - Woyang'anira dzina), dinani batani Pangani (Pangani) ndipo lowetsani dongosolo ili pamenepo:

Gawo 2. Lumikizani ku mutu

Kuti tiwonetse dziko losankhidwa pamutu ndi nthano ya tchati, tifunika kupeza zolozera ku selo ndi dzina lake (dziko) kuchokera pamndandanda woyamba. Kuti tichite izi, timapanga malo ena (ie Area = shiti lapano, osati Bukhu!) mndandanda wamatchulidwe wokhala ndi njira iyi:

Apa, ntchito ya INDEX imasankha kuchokera pamndandanda womwe waperekedwa (gawo A, pomwe mayiko athu osaina ali) cell yokhala ndi nambala ya mzere yomwe tidasankha kale.

Gawo 3. Lumikizani ku deta

Tsopano, mofananamo, tiyeni tipeze ulalo wosiyanasiyana ndi deta yonse yogulitsa kuchokera pamzere wamakono, kumene selo yogwira ntchito tsopano ili. Pangani gulu lina lotchulidwa ndi njira iyi:

Apa, mkangano wachitatu, womwe ndi ziro, umapangitsa INDEX kubwezeretsanso mtengo umodzi, koma mzere wonsewo.

Gawo 4. Kusintha maulalo mu Tchati

Tsopano sankhani mutu wa tebulo ndi mzere woyamba wokhala ndi deta (range) ndipo pangani tchati potengera zomwe akugwiritsa ntchito Ikani - Ma chart (Lowetsani - Ma chart). Ngati musankha mzere wokhala ndi deta mu tchati, ndiye kuti ntchitoyo idzawonetsedwa mu bar ya formula Row (SERI) ndi ntchito yapadera yomwe Excel imangogwiritsa ntchito popanga tchati chilichonse kuti iwonetsere zomwe zidayambira ndi zilembo:

Tiyeni tisinthe mosamalitsa mfundo zoyamba (siginicha) ndi zachitatu (data) mu ntchitoyi ndi mayina amizere yathu kuyambira masitepe 2 ndi 3:

Tchaticho chiyamba kuwonetsa deta yogulitsa kuchokera pamzere wapano.

Gawo 5. Recalculation Macro

Kukhudza komaliza kumakhalabe. Microsoft Excel imawerengeranso mafomu pokhapokha ngati zomwe zili patsambalo zisintha kapena kiyi ikanikizidwa F9, ndipo tikufuna kuti kuwerengetsanso kuchitike pamene kusankha kusinthika, mwachitsanzo, pamene selo yogwira ntchito yasunthidwa pa pepalalo. Kuti tichite izi, tifunika kuwonjezera ma macro osavuta kubuku lathu lantchito.

Dinani kumanja pa tabu ya data ndikusankha lamulo gwero (Source kodi). Pazenera lomwe limatsegulidwa, lowetsani code ya macro-handler pakusintha kosankha:

Monga momwe mungaganizire mosavuta, zonse zomwe zimachita ndikuyambitsanso kuwerengeranso kwa tsamba nthawi iliyonse pomwe cell yogwira imasintha.

Gawo 6. Kuyang'ana Mzere Wamakono

Kuti mumveke bwino, mutha kuwonjezeranso lamulo lokhazikika kuti muwonetse dziko lomwe likuwonetsedwa patchati. Kuti muchite izi, sankhani tebulo ndikusankha Kunyumba - Mapangidwe Okhazikika - Pangani Lamulo - Gwiritsani Ntchito Fomula Kuti Mudziwe Maselo Kuti Muwapange (Kunyumba - Mapangidwe Ovomerezeka - Lamulo Latsopano - Gwiritsani ntchito fomula kuti mudziwe kuti ndi maselo ati):

Apa chilinganizo chimayang'ana pa selo iliyonse patebulo kuti nambala yake ya mzere ikufanana ndi nambala yosungidwa muzosintha za TekRow, ndipo ngati pali machesi, ndiye kuti kudzaza ndi mtundu wosankhidwa kumayambitsidwa.

Ndi zimenezo - zosavuta ndi zokongola, chabwino?

zolemba

• Pamatebulo akuluakulu, kukongola konseku kumatha kutsika - kupanga zokhazikika ndi chinthu chofunikira kwambiri, ndipo kuwerengeranso pazosankha zilizonse kumathanso kukhala kolemetsa.
• Kuti muteteze zambiri kuti zisazimiririke pa tchati pomwe selo lidasankhidwa mwangozi pamwamba kapena pansi pa tebulo, mutha kuwonjezera cheke ku dzina la TekRow pogwiritsa ntchito mawonekedwe a IF omwe ali patsambalo:

  =IF(CELL(“row”)<4,IF(CELL("row")>4,CELL(“row”)))
  

• Kuyang'ana mizati yomwe yatchulidwa mu tchati
• Momwe mungapangire tchati cholumikizirana mu Excel
• Kusankha Kogwirizanitsa