Momwe Mungachotsere Mizere Yonse Yopanda kanthu mu Excel

M'nkhaniyi, ndifotokoza chifukwa chake kuchotsa mizere yopanda kanthu mu Excel pogwiritsa ntchito onetsani ma cell opanda kanthu > chotsani mzere Ndi lingaliro loipa, ndipo ndikuwonetsani njira ziwiri zofulumira komanso zolondola zochotsera mizere yopanda kanthu popanda kuwononga deta. Njira zonsezi zimagwira ntchito mu Excel 2, 2013, ndi mitundu yakale.

Ngati mukuwerenga nkhaniyi, ndiye kuti nthawi zonse mukugwira ntchito ku Excel yokhala ndi matebulo akulu. Mukudziwa kuti mizere yopanda kanthu imapezeka nthawi ndi nthawi pakati pazidziwitso, ndikuchepetsa ntchito ya zida zambiri zapa tebulo la Excel (kusanja, kuchotsa zobwereza, ma subtotals, ndi zina zotero), kuwalepheretsa kudziwa bwino kuchuluka kwa deta. Ndipo nthawi iliyonse muyenera kufotokozera malire pamanja, apo ayi zotsatira zake zidzakhala zotsatira zolakwika komanso nthawi yambiri yokonza zolakwika.

Pali zifukwa zambiri zomwe mizere yopanda kanthu imawonekera. Mwachitsanzo, munalandira buku la Excel kuchokera kwa munthu wina kapena chifukwa chotumiza kuchokera ku database yamakampani, kapena zosafunika m'mizere zidafufutidwa pamanja. Mulimonsemo, ngati cholinga chanu ndikuchotsa mizere yonseyo ndikukhala ndi tebulo loyera komanso laudongo, tsatirani njira zosavuta zili pansipa:

• Osachotsa mizere yopanda kanthu ndi kusankha ma cell opanda kanthu
• Kuchotsa mizere yopanda kanthu pogwiritsa ntchito mzere waukulu
• Kuchotsa mizere yopanda kanthu patebulo popanda mgawo waukulu

Osachotsa mizere yopanda kanthu ndi kusankha ma cell opanda kanthu

Pa intaneti yonse, mupeza malangizo osavuta omwe akuti amakulolani kuchotsa mizere yopanda kanthu:

• Sankhani deta kuchokera ku selo yoyamba mpaka yotsiriza.
• Press F5kuti mutsegule kukambirana Pitani ku (Kusintha).
• Mu bokosi la zokambirana, dinani batani Wapadera (Kuwonetsa).
• Mu dialog box Pitani ku Special (Sankhani gulu la maselo) chongani bokosi Makumbidwe (Maselo opanda kanthu) ndikudina OK.
• Dinani kumanja pamaselo aliwonse osankhidwa ndikudina Chotsani (Chotsani).
• Mu dialog box Chotsani (Chotsani ma cell) sankhani Mzere wonse (mzere) ndikusindikiza OK.

Iyi ndi njira yoyipa kwambiri., ingochitani izi ndi matebulo osavuta kwambiri okhala ndi mizere khumi ndi iwiri yokwanira pazenera limodzi, kapena kuposapo - musachite zimenezo konse! Chifukwa chachikulu ndi chakuti ngati mzere wokhala ndi deta yofunika uli ndi selo imodzi yopanda kanthu, ndiye mzere wonse udzachotsedwa.

Mwachitsanzo, tili ndi tebulo lamakasitomala lomwe lili ndi mizere 6 yonse. Tikufuna kuchotsa mizere 3 и 5chifukwa alibe kanthu.

Chitani zomwe zanenedwa pamwambapa ndikupeza zotsatirazi:

Line 4 (Roger) nayenso anasowa chifukwa selo D4 mgawo Magalimoto kunakhala opanda kanthu

Ngati tebulo lanu silili lalikulu mudzawona kutayika kwa data, koma m'matebulo enieni okhala ndi mizere masauzande mutha kuchotsa mizere yofunikira mosadziwa. Ngati muli ndi mwayi, mupeza kutayika mkati mwa maola ochepa, bwezeretsani bukulo kuchokera pazosunga zobwezeretsera, ndikupitiliza kugwira ntchito. Nanga bwanji ngati mulibe mwayi ndipo mulibe zosunga zobwezeretsera?

Pambuyo pake m'nkhaniyi, ndikuwonetsani njira ziwiri zachangu komanso zodalirika zochotsera mizere yopanda kanthu pamasamba a Excel.

Kuchotsa mizere yopanda kanthu pogwiritsa ntchito mzere waukulu

Njirayi imagwira ntchito ngati tebulo lanu liri ndi gawo lomwe limathandiza kudziwa ngati gawo lomwe likufunsidwa liribe kanthu kapena ayi (mndandanda wachinsinsi). Mwachitsanzo, ikhoza kukhala ID yamakasitomala kapena nambala yoyitanitsa, kapena zina zofananira.

Ndikofunikira kuti tisunge dongosolo la mizere, kotero sitingathe kungosanja tebulo ndi gawoli kuti tisunthire mizere yopanda kanthu pansi.

1. Sankhani tebulo lonse, kuyambira mzere woyamba mpaka womaliza (dinani Ctrl + Panyumba, Kenako Ctrl + Shift + End).
2. Onjezani zosefera patebulo. Kuti muchite izi, dinani pa tabu Deta (data) dinani fyuluta (Sefa).
3. Ikani zosefera pazambiri Cust#. Kuti muchite izi, dinani batani la mivi pamutu wamutu, osasankha zomwe mwasankha Sankhani zonse (Sankhani Zonse), yendani pansi mpaka kumapeto kwa mndandanda (pochita, mndandandawu ukhoza kukhala wautali) ndipo chongani bokosilo. Makumbidwe (Chopanda) m'munsi mwa mndandanda. Dinani OK.
4. Sankhani mizere yonse yosefedwa: dinani Ctrl + Panyumba, kenako muvi wopita pansi kuti musunthire pamzere woyamba wa data, kenako dinani Ctrl + Shift + End.
5. Dinani kumanja pa selo iliyonse yomwe mwasankha ndikusankha kuchokera pazosankha Chotsani mzere (Chotsani mzere) kapena kungodinanso Ctrl + -(minus sign).
6. Pawindo lomwe likuwoneka ndi funso Chotsani mzere wonse wamasamba? (Chotsani mzere wonse wamasamba?) dinani OK.
7. Chotsani zosefera zomwe zayikidwa: pa tabu Deta (data) dinani Chotsani (Chotsani).
8. Zabwino kwambiri! Mizere yonse yopanda kanthu imachotsedwa kwathunthu, ndi mzere 3 (Roger) akadali m'malo (yerekezerani ndi zotsatira za kuyesa koyambirira).

Kuchotsa mizere yopanda kanthu patebulo popanda mgawo waukulu

Gwiritsani ntchito njirayi ngati tebulo lanu lili ndi maselo ambiri opanda kanthu omwe amwazikana m'mizere yosiyana, ndipo muyenera kuchotsa mizere yokhayo yomwe ilibe maselo aliwonse omwe ali ndi deta.

Pamenepa, tilibe mzati waukulu wotithandiza kudziwa ngati chingwecho chilibe kanthu kapena ayi. Chifukwa chake, timawonjezera gawo lothandizira patebulo:

1. Pamapeto a tebulo, onjezani ndime yomwe yatchulidwa Makumbidwe ndi kumata fomula ili mu selo yoyamba ya ndime:

   =COUNTBLANK(A2:C2)

   =СЧИТАТЬПУСТОТЫ(A2:C2)

   Fomula iyi, monga momwe dzina lake likunenera, imawerengera ma cell opanda kanthu mumndandanda womwe waperekedwa. A2 и C2 ndi maselo oyambirira ndi otsiriza a mzere wamakono, motsatira.

   

2. Lembani fomula ku gawo lonse. Momwe mungachitire izi - onani malangizo a pang'onopang'ono Momwe mungayikitsire fomula yomweyo m'maselo onse osankhidwa nthawi imodzi.
3. Tsopano tebulo lathu lili ndi gawo lofunikira! Ikani zosefera pazambiri Makumbidwe (pamwambapa pali kalozera wa sitepe ndi sitepe momwe mungachitire izi) kuti mungowonetsa mizere yokhala ndi mtengo wokwanira (3). Nambala 3 zikutanthauza kuti maselo onse mumzerewu alibe kanthu.
4. Kenako, sankhani mizere yosefedwa ndikuchotsa kwathunthu. Momwe mungachitire izi zafotokozedwa pamwambapa. Zotsatira zake, mzere wopanda kanthu (mzere 5) udzachotsedwa, mizere ina yonse (yokhala kapena yopanda maselo opanda kanthu) idzakhalabe m'malo awo.
5. Tsopano gawo lothandizira litha kuchotsedwa. Kapena mutha kugwiritsa ntchito fyuluta ina kuti muwonetse ma cell omwe ali ndi selo imodzi kapena angapo opanda kanthu. Kuti muchite izi, sankhani mzere ndi mtengo 0 (zero) ndikusindikiza OK.