Ili kupanua matriki ya kiasi cha vipengee vya sanaa na kuweka kila kipengee katika safu yake ya laha ya kazi, orodhesha kwanza kila mchanganyiko wa kipengee na umbizo pamoja na kiasi limbikizi. Kisha tumia SEQUENCE na XLOOKUP kurudia lebo zake mara zinazohitajika. Mwongozo huu unatumia lebo za kazi za sanaa zilizobuniwa.
Kazi hii ni rahisi: geuza gridi fupi ya kupanga kuwa orodha ya vipengee. Kiasi cha mbili huzalisha safu mbili zenye kipengee na umbizo sawa; sufuri haizalishi safu. Mfano ulio hapa chini unaonyesha hesabu za kati ili uweze kuangalia matokeo kabla ya kuubadilisha.
Ni matoleo gani ya Excel ya Mac yanayotumia mbinu hii?
Tumia Excel for Microsoft 365 for Mac, Excel 2024 for Mac, au Excel 2021 for Mac. Nyaraka za Microsoft zinaorodhesha matoleo haya kwa SEQUENCE na XLOOKUP, na mwongozo wa vipengele vya Excel 2021 for Mac unaeleza wazi matriki zinazobadilika. SEQUENCE function, XLOOKUP function, What’s new in Excel 2021 for Mac.
Upatikanaji wa vitendakazi ni muhimu unaponakili fomula kutoka mafunzo mengine. Kwa mfano, Microsoft inaorodhesha VSTACK kwa Microsoft 365 na Excel 2024 kwenye Mac, lakini haiorodheshi kwa Excel 2021. Mbinu hii huepuka utegemezi huo. Microsoft pia inasema wazi kuwa XLOOKUP haipatikani katika Excel 2016 na Excel 2019, kwa hivyo matoleo hayo hayawezi kuendesha fomula zilizo hapa chini bila mabadiliko. VSTACK function, XLOOKUP function.
Sanidi matriki ya kiasi cha vipengee vya sanaa
Katika laha ya kazi tupu, weka data hii ya mfano iliyobuniwa kwenye A1:C4. Safu wima ya kwanza ina lebo za vipengee, safu ya vichwa ina miundo, na visanduku vilivyosalia vina kiasi.
| Kipengee | Ndogo | Kubwa |
|---|---|---|
| Kigae cha Tao | 2 | 0 |
| Kigae cha Nukta | 1 | 0 |
| Kigae cha Wimbi | 2 | 1 |
Kwa mfano huu, kila kiasi lazima kiwe namba kamili isiyo hasi. Weka sufuri wazi wakati hakuna kipengee kinachohitajika. Rekebisha visanduku vilivyo tupu, kiasi kilichoandikwa kama matini, thamani hasi, sehemu za namba, na hitilafu za visanduku kabla ya kuendelea. Fomula hizi hudhani kuwa hesabu ni sahihi; hazizihakiki wala kuzikaribisha.
Matokeo yanayotarajiwa yana safu sita za vipengee: Vigae viwili Vidogo vya Tao, Kigae kimoja Kidogo cha Nukta, Vigae viwili Vidogo vya Wimbi, na Kigae kimoja Kikubwa cha Wimbi. Lebo zinazorudiwa zimekusudiwa kwa sababu kila safu inawakilisha kipengee kimoja. Fomula huunda rekodi za laha ya kazi; haziundi wala kurudia faili za kazi za sanaa.
Geuza matriki kuwa orodha saidizi inayoweza kukaguliwa
Acha safu wima D tupu. Katika E1:I1, weka vichwa Nafasi, Kipengee, Umbizo, Kiasi, na Kipengee cha mwisho. Weka namba kuanzia moja hadi sita katika E2:E7, kwa mpangilio. Kuna safu saidizi sita kwa sababu vipengee vitatu vikizidishwa kwa miundo miwili vinatoa michanganyiko sita.
Orodha saidizi husoma kila safu ya chanzo kutoka kushoto kwenda kulia kabla ya kwenda chini: Kigae cha Tao Ndogo, Kigae cha Tao Kubwa, kisha michanganyiko miwili ya Kigae cha Nukta, ikifuatiwa na michanganyiko miwili ya Kigae cha Wimbi. Ni muhimu kudumisha mpangilio huu.
Pata lebo na kiasi cha kila mchanganyiko
Weka fomula hii katika F2:
=INDEX($A$2:$A$4,INT((E2-1)/2)+1)
Weka fomula hii katika G2:
=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)
Weka fomula hii katika H2:
=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)
Nakili F2:H2 kwenda chini hadi safu ya saba. Hizi ni fomula za kawaida za visanduku, zenye matokeo moja kwa kila kisanduku. INDEX huchukua thamani katika safu na safu wima maalum. Hapa, INT hufanya safu ya chanzo kusonga mbele baada ya kila nafasi mbili, huku MOD ikifanya safu wima ya chanzo kupishana kati ya miundo hiyo miwili. INDEX function, INT function, MOD function.
Kokotoa namba ya mwisho ya kipengee kwa kila mchanganyiko
Katika I2, weka fomula ifuatayo na uinakili kwenda chini hadi I7:
=SUM($H$2:H2)
Rejeleo lisilobadilika la mwanzo na rejeleo la mwisho linalosogea huunda jumla inayoendelea. SUM hujumlisha kiasi kutoka safu saidizi ya kwanza hadi safu ya sasa. SUM function.
Orodha saidizi iliyokamilika inapaswa kuonekana hivi:
| Nafasi | Kipengee | Umbizo | Kiasi | Kipengee cha mwisho |
|---|---|---|---|---|
| 1 | Kigae cha Tao | Ndogo | 2 | 2 |
| 2 | Kigae cha Tao | Kubwa | 0 | 2 |
| 3 | Kigae cha Nukta | Ndogo | 1 | 3 |
| 4 | Kigae cha Nukta | Kubwa | 0 | 3 |
| 5 | Kigae cha Wimbi | Ndogo | 2 | 5 |
| 6 | Kigae cha Wimbi | Kubwa | 1 | 6 |
Tafsiri Kipengee cha mwisho kama mpaka. Mchanganyiko wa kwanza unajumuisha namba za vipengee moja na mbili. Mchanganyiko wa tatu unajumuisha namba ya kipengee tatu. Wa tano unajumuisha nne na tano, na wa sita unajumuisha sita. Kiasi cha sufuri hakisogezi mpaka mbele, kwa hivyo hakipewi namba za vipengee.
Tengeneza safu moja ya matokeo kwa kila kipengee
Katika K1, weka Kipengee; katika L1, weka Umbizo. Acha visanduku vilivyo chini ya vichwa vyote viwili tupu. Weka matokeo katika visanduku vya kawaida vya laha ya kazi vilivyo nje ya jedwali la Excel: Microsoft inaeleza kuwa fomula za matriki zinazomwagika hazitumiki ndani ya jedwali. Dynamic array formulas and spilled array behavior.
Weka fomula hii kamili katika K2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))
Weka fomula hii kamili katika L2:
=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))
Thibitisha kila fomula kwa kubonyeza Return. Usinakili fomula hizi mbili kwenda chini: matokeo yake ya matriki hujaza safu jirani kiotomatiki. Microsoft huita upanuzi huu wa kiotomatiki kumwagika. Dynamic array formulas and spilled array behavior.
SEQUENCE huzalisha namba za vipengee kuanzia moja hadi jumla ya kiasi. Kwa kila namba, XLOOKUP hutafuta mipaka limbikizi. Hoja zake mbili za mwisho hubainisha ulinganifu kamili au thamani kubwa inayofuata, huku utafutaji ukifanywa kutoka ya kwanza hadi ya mwisho. Mwelekeo huo wa utafutaji huhifadhi mpaka wa kwanza unaolingana pale sufuri zinaposababisha jumla zinazojirudia. SEQUENCE function, XLOOKUP function.
IF ya nje hurejesha matini tupu wakati jumla ni sufuri. Katika hali hiyo, visanduku viwili vya msingi huonekana tupu na hakuna rekodi za vipengee za kutumia. Hii ni hali ya jumla-sufuri iliyokusudiwa, inayotumia IF kuchagua kati ya matokeo mawili. IF function.
Kwa matriki iliyotolewa, K1:L7 inapaswa kuwa na:
| Kipengee | Umbizo |
|---|---|
| Kigae cha Tao | Ndogo |
| Kigae cha Tao | Ndogo |
| Kigae cha Nukta | Ndogo |
| Kigae cha Wimbi | Ndogo |
| Kigae cha Wimbi | Ndogo |
| Kigae cha Wimbi | Kubwa |
Safu wima zote mbili za matokeo hutumia namba zilezile zilizozalishwa za vipengee na safu ileile ya mipaka. Ni safu ya thamani za kurejesha pekee inayobadilika: safu wima F hutoa lebo za vipengee, na safu wima G hutoa miundo. Weka ingizo hizo za utafutaji zikiwa zimesawazishwa unapohariri fomula.
Kagua matokeo kabla ya kupanua chanzo
Fanya ukaguzi huu mara tatu kwa data ya mfano:
- Linganisha jumla:
=SUM(B2:C4)na=SUM(H2:H7)lazima zote zirudishe sita. - Linganisha michanganyiko: matokeo lazima yawe na michanganyiko minne yenye kiasi kisicho sifuri na idadi iliyotajwa ya marudio.
- Badilisha
B2kutoka mbili hadi tatu: matokeo yanayotarajiwa yanakuwa safu saba, zikiwa na maingizo matatu ya Kigae cha Tao Ndogo. Baadaye irejeshe kuwa mbili.
Ukaguzi wa kwanza hubaini kiasi kilichoachwa au kurudiwa wakati wa kuunda orodha bapa. Wa pili hubaini lebo zisizolingana, jambo ambalo jumla pekee haiwezi kufichua. Wa tatu hukagua kama mabadiliko yanapitia orodha saidizi hadi kwenye matokeo.
Ongeza vipengee au miundo zaidi kwa makusudi
Kubadilisha kiasi ndani ya B2:C4 hutumia usanidi uliopo. Kuongeza vipimo vya matriki kunahitaji kubadilisha marejeleo na urefu wa orodha saidizi.
Kwa mfano kamili, panua chanzo kiwe vipengee vinne na miundo mitatu katika A1:D5. Hamisha au hifadhi eneo saidizi katika E:I, weka nafasi kuanzia moja hadi kumi na mbili katika E2:E13, na badilisha fomula katika F2:H2 kwa hizi:
=INDEX($A$2:$A$5,INT((E2-1)/3)+1)
=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)
=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)
Nakili fomula hizo hadi safu ya kumi na tatu, na uendeleze fomula ya jumla inayoendelea katika safu wima I hadi safu hiyo hiyo. Katika fomula zote mbili za matokeo, badilisha kila mwisho wa safu saidizi wa safu ya saba uwe wa safu ya kumi na tatu. Kigawanyo na msingi wa baki sasa ni tatu kwa sababu kila kipengee kina miundo mitatu. Kagua tena jumla ya chanzo na michanganyiko ya kila kipengee baada ya kufanya mabadiliko haya.
Tatua matokeo yasiyokamilika au yaliyozuiwa
Excel ikionyesha #SPILL!, kagua eneo lililokusudiwa kwa matokeo ili kuona kama lina maingizo yaliyopo au visanduku vilivyounganishwa, na uthibitishe kuwa fomula iko nje ya jedwali la Excel. Matokeo yanayovuka ukingo wa laha ya kazi pia hayawezi kumwagika. Microsoft inaeleza kila mojawapo ya hali hizi. How to correct a #SPILL! error.
Matokeo yakiwa na safu chache mno, kagua kiasi kabla ya kubadilisha mantiki ya utafutaji. Microsoft inabainisha kuwa SUM hupuuza matini katika safu zilizorejelewa. Kwa hiyo, kiasi kilichohifadhiwa kama matini kinaweza kuachwa kwenye jumla. Rekebisha thamani ya chanzo na urudie ukaguzi wa jumla. SUM function.
Lebo zikiwa si sahihi licha ya jumla kuwa sahihi, linganisha orodha saidizi na matriki kisanduku kwa kisanduku. Weka kipengee, umbizo, kiasi na mpaka limbikizi wa kila safu saidizi pamoja. Usipange upya safu wima ya orodha saidizi kivyake.
Jumla na michanganyiko ikikubaliana, tumia safu wima mbili za matokeo kama orodha yako ya vipengee. Hifadhi matriki na hesabu saidizi ili mabadiliko ya kiasi ya baadaye yaweze kufuatiliwa hadi kwenye safu zilizorudiwa.
Vyanzo
- What’s new in Excel 2021 for Mac — Microsoft Support.
- SEQUENCE function — Microsoft Support.
- XLOOKUP function — Microsoft Support.
- VSTACK function — Microsoft Support.
- INDEX function — Microsoft Support.
- INT function — Microsoft Support.
- MOD function — Microsoft Support.
- SUM function — Microsoft Support.
- IF function — Microsoft Support.
- Dynamic array formulas and spilled array behavior — Microsoft Support.
- How to correct a #SPILL! error — Microsoft Support.