Yaliyomo
Je! una majedwali yenye data katika Excel ambayo inaweza kubadilishwa ukubwa, yaani, idadi ya safu (safu) inaweza kuongezeka au kupungua wakati wa kazi? Ikiwa saizi za meza "zinaelea", basi itabidi ufuatilie kila wakati wakati huu na urekebishe:
- viungo katika fomula za ripoti zinazorejelea jedwali letu
- safu za awali za jedwali egemeo ambazo zimejengwa kulingana na jedwali letu
- safu za awali za chati zilizojengwa kulingana na jedwali letu
- masafa kwa menyu kunjuzi zinazotumia jedwali letu kama chanzo cha data
Haya yote kwa jumla hayatakuacha uchoke 😉
Itakuwa rahisi zaidi na sahihi kuunda safu ya "mpira" yenye nguvu, ambayo itarekebisha kiotomati kwa idadi halisi ya safu na safu wima za data. Ili kutekeleza hili, kuna njia kadhaa.
Njia ya 1. Jedwali la Smart
Angazia safu yako ya visanduku na uchague kutoka kwa kichupo Nyumbani - Umbizo kama Jedwali (Nyumbani - Fomati kama Jedwali):
Ikiwa hauitaji muundo wa milia ambao umeongezwa kwenye meza kama athari ya upande, basi unaweza kuizima kwenye kichupo kinachoonekana. Mjenzi (Design). Kila jedwali linaloundwa kwa njia hii hupokea jina ambalo linaweza kubadilishwa na rahisi zaidi katika sehemu moja kwenye kichupo Mjenzi (Design) katika uwanja Jina la jedwali (Jina la Jedwali).
Sasa tunaweza kutumia viungo vinavyobadilika kwa "meza mahiri" yetu:
- Meza 1 - kiungo kwa jedwali zima isipokuwa safu ya kichwa (A2:D5)
- Jedwali1[#Yote] - kiungo kwa meza nzima (A1:D5)
- Jedwali 1[Petro] - marejeleo ya safu wima bila kichwa cha kisanduku cha kwanza (C2:C5)
- Jedwali1[#Vichwa] - kiungo kwa "kichwa" kilicho na majina ya safuwima (A1:D1)
Marejeleo kama haya hufanya kazi vizuri katika fomula, kwa mfano:
= SUM (Jedwali 1 [Moscow]) - hesabu ya jumla ya safu "Moscow"
or
=VPR(F5;Meza 1;3;0) - tafuta katika jedwali la mwezi kutoka seli F5 na utoe jumla ya St. Petersburg kwa ajili yake (VLOOKUP ni nini?)
Viungo kama hivyo vinaweza kutumika kwa mafanikio wakati wa kuunda majedwali ya egemeo kwa kuchagua kwenye kichupo Ingiza - Jedwali la Egemeo (Ingiza - Jedwali Egemeo) na kuingiza jina la jedwali mahiri kama chanzo cha data:
Ikiwa unachagua kipande cha meza hiyo (kwa mfano, safu mbili za kwanza) na kuunda mchoro wa aina yoyote, basi wakati wa kuongeza mistari mpya, wataongezwa moja kwa moja kwenye mchoro.
Wakati wa kuunda orodha kunjuzi, viungo vya moja kwa moja vya vipengee mahiri vya jedwali haviwezi kutumika, lakini unaweza kuvuka kizuizi hiki kwa urahisi kwa kutumia hila ya busara - tumia chaguo la kukokotoa. INDIRECT (KIASILI), ambayo hugeuza maandishi kuwa kiungo:
Wale. kiungo cha jedwali mahiri katika mfumo wa mfuatano wa maandishi (katika alama za nukuu!) hugeuka kuwa kiungo kamili, na orodha kunjuzi huitambua kwa kawaida.
Njia ya 2: Safu inayobadilika yenye jina
Ikiwa kugeuza data yako kuwa jedwali mahiri hakupendezi kwa sababu fulani, basi unaweza kutumia njia ngumu zaidi, lakini iliyofichika zaidi na yenye matumizi mengi - tengeneza safu inayobadilika inayoitwa katika Excel ambayo inarejelea jedwali letu. Kisha, kama ilivyo kwa jedwali mahiri, unaweza kutumia kwa uhuru jina la safu iliyoundwa katika fomula, ripoti, chati, n.k. Wacha tuanze na mfano rahisi:
Kazi: tengeneza safu inayobadilika yenye jina ambayo inaweza kurejelea orodha ya miji na kunyoosha kiotomatiki na kupungua kwa ukubwa wakati wa kuongeza miji mipya au kuifuta.
Tutahitaji vitendaji viwili vilivyojengewa ndani vya Excel vinavyopatikana katika toleo lolote - POICPOZ (MECHI) kuamua seli ya mwisho ya masafa, na INDEX (INDEX) ili kuunda kiungo chenye nguvu.
Inatafuta kisanduku cha mwisho kwa kutumia MATCH
MATCH(thamani_ya_tafuta, masafa, aina_ya_kulingana) – chaguo la kukokotoa ambalo hutafuta thamani fulani katika safu (safu mlalo au safu wima) na kurudisha nambari ya odinal ya kisanduku ambapo ilipatikana. Kwa mfano, fomula ya MATCH(“Machi”;A1:A5;0) itarudisha nambari 4 kama matokeo, kwa sababu neno “Machi” liko katika kisanduku cha nne katika safu wima A1:A5. Hoja ya mwisho ya utendakazi Match_Type = 0 inamaanisha tunatafuta inayolingana kabisa. Ikiwa hoja hii haijabainishwa, basi chaguo la kukokotoa litabadilika hadi hali ya utafutaji kwa thamani ndogo iliyo karibu zaidi - hii ndiyo hasa inaweza kutumika kwa mafanikio kupata kisanduku cha mwisho kilichokaliwa katika safu yetu.
Kiini cha hila ni rahisi. MATCH hutafuta visanduku katika safu kutoka juu hadi chini na, kwa nadharia, inapaswa kukoma inapopata thamani ndogo iliyo karibu zaidi na ile iliyotolewa. Ukibainisha thamani ambayo ni wazi kuwa ni kubwa kuliko yoyote inayopatikana kwenye jedwali kama thamani inayotakiwa, basi MATCH itafikia mwisho kabisa wa jedwali, bila kupata chochote na kutoa nambari ya mfuatano wa kisanduku kilichojazwa mwisho. Na tunaihitaji!
Ikiwa kuna nambari tu katika safu yetu, basi tunaweza kutaja nambari kama dhamana inayotaka, ambayo ni wazi kuwa ni kubwa kuliko yoyote kwenye jedwali:
Kwa dhamana, unaweza kutumia nambari 9E + 307 (9 mara 10 kwa nguvu ya 307, yaani 9 na zero 307) - idadi ya juu ambayo Excel inaweza kufanya kazi kwa kanuni.
Ikiwa kuna maadili ya maandishi kwenye safu yetu, basi kama sawa na nambari kubwa iwezekanavyo, unaweza kuingiza ujenzi REPEAT ("i", 255) - kamba ya maandishi yenye herufi 255 "i" - herufi ya mwisho ya Alfabeti. Kwa kuwa Excel hulinganisha misimbo ya wahusika wakati wa kutafuta, maandishi yoyote katika jedwali letu yatakuwa "ndogo" zaidi kuliko mstari mrefu kama "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy."
Tengeneza kiungo kwa kutumia INDEX
Sasa kwa kuwa tunajua nafasi ya kipengele cha mwisho kisicho tupu kwenye jedwali, inabakia kuunda kiungo cha safu yetu nzima. Kwa hili tunatumia kazi:
INDEX(fungu; nambari_safu; nambari_safu)
Inatoa yaliyomo ya seli kutoka safu kwa safu kwa safu na nambari ya safu, yaani, kwa mfano, kazi = INDEX(A1:D5;3;4) kwenye jedwali letu na miji na miezi kutoka kwa njia ya awali itatoa 1240 - yaliyomo. kutoka safu ya 3 na safu ya 4, yaani seli D3. Ikiwa kuna safu wima moja tu, basi nambari yake inaweza kuachwa, yaani formula INDEX(A2:A6;3) itatoa "Samara" katika picha ya skrini ya mwisho.
Na kuna nuance moja isiyo dhahiri kabisa: ikiwa INDEX haijaingizwa tu kwenye seli baada ya = ishara, kama kawaida, lakini inatumika kama sehemu ya mwisho ya rejeleo la safu baada ya koloni, basi haitoi tena. yaliyomo kwenye seli, lakini anwani yake! Kwa hivyo, fomula kama $A$2:INDEX($A$2:$A$100;3) itatoa rejeleo kwa masafa A2:A4 kwenye pato.
Na hapa ndipo kitendakazi cha MATCH kinapoingia, ambacho tunachoingiza ndani ya INDEX ili kuamua kwa nguvu mwisho wa orodha:
=$A$2:INDEX($A$2:$A$100; MECHI(REP(“I”;255);A2:A100))
Unda safu yenye jina
Inabakia kuifunga yote katika nzima moja. Fungua kichupo formula (Mfumo) Na bonyeza Jina la Meneja (Meneja wa Jina). Katika dirisha linalofungua, bonyeza kitufe Kujenga (mpya), ingiza jina na fomula yetu ya safu kwenye uwanja Mbalimbali (Rejea):
Inabakia kubofya OK na safu iliyo tayari inaweza kutumika katika fomula zozote, orodha kunjuzi au chati.
- Kwa kutumia kitendakazi cha VLOOKUP kuunganisha majedwali na thamani za utafutaji
- Jinsi ya kuunda orodha kunjuzi inayojaza kiotomatiki
- Jinsi ya kuunda jedwali la egemeo ili kuchambua idadi kubwa ya data