Dzidza kuti mapurisa angaita sei kushandura basa muExcel
Mhando yakasiyana kana boka rehutano hwehutano hunoenderana. Mumapurogiramu epadheseti akadai seExcel neGoogle Spreadsheets, hutsika muhuwandu hunowanzochengetwa mumasero ari pedyo.
Zvishandiso zveSimba
Nzira dzinogona kushandiswa mumashandisirwo maviri (array formula) uye semafungiro emabasa akadai semhando dzemhando dze LOOKUP uye INDEX mabasa.
Mitauro yeArrays
Pane mavara maviri emakamuri muExcel:
- Rimwe-dimensional array, inozivikanwa sevector kana vector array. Iyo data iripo:
- mune imwe musara (imwe-dimensional horizontal array).
- muchikamu chimwe chete (imwe-dimensional vertical array).
- Mbili-dimensional array, inozivikanwawo sechibvumirano
- iyo data iri mune dzakawanda ma column kana mitsetse.
- Table Array - Tafura yefurafura ndechimwe chemashoko anoshandiswa muExcel's lookup mabasa, akadai saVLOOKUP neHLOOKUP.
- IYOOKUP mabasa inotsvaga tafura yacho kuti iwane ruzivo rwakananga.
- Nokuda kweVLOOKUP (vertical lookup), tafura_array inofanira kunge ine zvinyorwa zviviri zvemashoko .
- Nokuda kweHLOOKUP (horizontal lookup), tafura_array inofanira kuva nemitsetse miviri yedata.
Ronga Formula Overview
Nzira yakasiyana-siyana ndeye chirevo chinowedzera kuverenga - zvakadai sekuwedzera, kana kuwedzera - pane hutsika mune imwe kana mamwe magwaro panzvimbo yechinhu chimwe chete chekuverenga.
Nzira dzakasiyana-siyana:
- shandisa izwi rimwechete sezvinowanzoshandiswa (ivo vose vanotanga nechiratidzo chakaenzana (=) somuenzaniso).
- shandisai masvomhu akafanana nevashandi .
- uye kutevera mutemo iwoyo wekushanda.
Array Formula uye Excel Mabasa
Vakawanda veExcel yakavakwa mumabasa - akadai seSUM, AVERAGE, kana COUNT - inogonawo kushandiswa mumutsetse wemhando.
Panewo mabasa mashomanana - akadai seTV TRANSPOSE function - iyo inofanira kugara yakaiswa seyakagadzirira kuitira kuti iite zvakanaka.
Kubatsira kwemabasa mazhinji akadai se INDEX ne MATCH kana MAX uye IF inogona kuwedzerwa nekuvashandisa pamwe chete mumutsetse wemhando.
CSE Formulas
MuExcel, mazano akazara akakomberedzwa ne "curly braces" " {} ". Izvozvi hazvikwanisi kungopindirwa asi zvinofanira kuwedzerwa pane imwe nzira kuburikidza neCtrl, Shift, uye Enter keys shure kwekunyorera fomu iri mumasero kana masero.
Nokuda kwechikonzero ichi, mazano akafanana dzimwe nguva anotumidzwa seCSE formula muExcel.
Kusiyana nemurairo uyu ndeye apo kusungurudzika kwekasa kunoshandiswa kuisa mhando senharo yebasa iro rinowanzo rine chinhu chimwechete kana sero reference .
Semuenzaniso, mumudzidzi wezasi unoshandisa VLOOKUP uye CHOOSE basa kuita sarudzo yekuchera mazano, mhando yakagadzirwa nokuda kweCOOSE function Index_num argument nekunyora mabhesi akakomberedzwa.
Matanho ekugadzira Mhando yeMapurisa
- Pinda mazano.
- Tora pasi Ctrl uye Shift keys pane keyboard.
- Dhinda nekusunungura pikicha yekupinda kuti ugadzire rondedzero yakafanana.
- Bvisa Ctrl uye Shift keys.
Kana yakaitwa nenzira yakarurama, chirevo chacho chichakomberedzwa ne-curly braces uye sero rimwe nerimwe rakabata fomu richava nemugumisiro wakasiyana.
Kugadzirisa Array Formula
Chero nguva iyo nzira yakagadziriswa yakagadziriswa maziso anonyangarika anonyangarika kubva kumativi ose.
Kuti uvadzose shure, mazano ekugadzirisa anofanira kuiswa nekusvitsa Ctrl, Shift, uye Enter keys zvakare sezvakangoita apo mazano ekutanga akatanga.
Mitauro yeArray Formulas
Kune marudzi maviri emhando yekufananidzira:
- Multi-cell array formula, umo maitiro akafanana ari mune imwe kero rimwechete mufashiti yekushanda ;
- Single cell array formula, iyo imwe nzira inotora kuverenga kwakawanda mune imwe sero yebasa reeseji.
Multi-Cell Array Formulas
Kufanana nezita ravo rinoratidzira, mazita aya akawanikwa ari mumasero akawanda ekushanda mapepa uye anodzorerawo mhinduro semhinduro.
Mune mamwe mazwi, maitiro akadaro ari mune maviri kana kupfuura masero uye anodzorera mhinduro dzakasiyana mune imwe sero.
Iyo inoita izvi ndeyokuti kopi imwe neimwe kana chiitiko chemuchina wehutano unoita kuenzana kwakafanana mune imwe sero iri irimo, asi imwe neimwe yeyomurairi inoshandisa data yakasiyana muhuwandu hwayo uye, naizvozvo, chiitiko chimwe nechimwe chinounza mhinduro dzakasiyana.
Muenzaniso we multiple multiple cell formula ndiwo:
{= A1: A2 * B1: B2}
Single Cell Array Formulas
Rudzi rwechipiri rwemhando dzakashandisa basa - rakadai seSUM, AVERAGE, kana COUNT - kubatanidza kubudiswa kwema-multi-cell array formula kuva chinhu chimwe chete mune imwe sero.
Muenzaniso wekamuri imwechete yemazana yaizove:
{= SUM (A1: A2 * B1: B2)}