Noocyada la magacaabay ayaa faa'iido leh, laakiin inta badan aan laga faa'iidaysan, sifo Microsoft Excel ah . Heerarka la magacaabay waxay fududayn karaan qaababka loo fahmo (iyo wax laga beddelo), fududeeyo abuurista xaashiyaha adag, oo fududeeya macroskaaga.

Qiyaasta la magacaabay waa kaliya kala duwanaansho (ama hal unug, ama unugyo kala duwan) oo aad magac u dhiibto. Waxaad markaa u isticmaali kartaa magacaas meel tixraacyada unugga caadiga ah ee qaacidooyinka, macros, iyo si aad u qeexdo isha garaafyada ama xaqiijinta xogta.

Isticmaalka magac kala duwan, sida TaxRate , oo lagu beddelo tixraaca unugga caadiga ah, sida Sheet2 !$C$11, waxay fududayn kartaa fahamka xaashida xaashida oo la tirtiro/baaro.

Isticmaalka Magacyada Range ee Excel

Tusaale ahaan, aan eegno foomka dalabka fudud.  Faylkayaga waxaa ku jira foom dalabka la buuxin karo oo hoos u dhigaya si loo doorto habka rarida, iyo xaashi labaad oo leh shax kharashyada dhoofinta, iyo heerka cashuurta.

Nooca 1 (iyada oo aan la magacaabin) waxay isticmaashaa tixraacyada unugyada A1-style caadiga ah ee qaacidooyinkeeda (oo lagu muujiyey baarka caanaha ee hoose).

Nooca 2 wuxuu adeegsadaa kala duwanaansho la magacaabay, taasoo ka dhigaysa qaacidooyinkeeda mid aad u fudud in la fahmo. Noocyada la magacaabay ayaa sidoo kale fududeynaya gelitaanka qaacidooyinka, maadaama Excel uu soo bandhigi doono liis magacyo ah, oo ay ku jiraan magacyada shaqada, oo aad ka dooran karto, mar kasta oo aad bilowdo inaad ku qorto magaca qaacidada.  Laba jeer ku dhufo^{(Double-click)} magaca ku jira liiska xulashada si aad ugu darto caanahaaga.

Furitaanka daaqadda Maareeyaha Magaca ee tabka ^{(Name Manager)}Formulas waxa ay soo bandhigaysaa liiska magacyada kala duwan ee ay tixraacayaan.

Laakiin noocyada la magacaabay ayaa sidoo kale leh faa'iidooyin kale. Tusaalaha faylalkayaga, habka dhoofinta waxaa lagu doortaa iyadoo la isticmaalayo hoos u dhac (aqoonsiga xogta) ee unugga B13 ee Sheet1 . Habka la doortay ayaa markaa loo isticmaalaa in lagu eego kharashka rarida ee Sheet2 .

Iyada oo aan la magacaabin, xulashada hoos u dhaca waa in gacanta lagu geliyo maadaama ansaxinta xogta aysan kuu ogolaan doonin inaad doorato liiska isha xaashi kale. Markaa dhammaan xulashooyinka waa in laba jeer la geliyo: hal mar liiska hoos u dhaca, iyo mar labaad Sheet2 . Intaa waxaa dheer, labada liis waa inay iswaafaqaan.

Haddii qalad lagu sameeyo mid ka mid ah liisaska ku jira, markaas qaacidada kharashka rarida waxay dhalin doontaa qalad #N/A marka doorashada khaldan la doorto. U magacaabista liiska xaashida2^(Sheet2) sida Habka Shipping^{(ShippingMethods)} - ka waxay meesha ka saaraysaa labada dhibaato.

Waxaad tixraaci kartaa kala duwanaansho la magacaabay markaad qeexayso ansaxinta xogta liiska hoos u dhaca adoo si fudud u galaya =ShippingMethods gudaha goobta isha, tusaale ahaan. Tani waxay kuu ogolaaneysaa inaad isticmaasho liiska xulashooyinka ku yaal xaashi kale.

Oo haddii hoos u dhigistu ay tixraacayso unugyada dhabta ah ee loo isticmaalo raadinta (ee qaacidada kharashka rarida), markaa doorashadu waxay had iyo jeer la mid noqon doontaa liiska raadinta, ka fogaanshaha #N/A khaladaadka.

Ku samee Range Magacaaban gudaha Excel

Si aad u abuurto tiro la magacaabay, si fudud u dooro unugga ama tirada unugyada aad rabto in aad magacowdo, ka bacdi ku dhufo sanduuqa Magaca^{( Name Box)} (halkaas oo ciwaanka gacanta ee la doortay uu caadi ahaan ka muuqdo, oo ka bidixda ka xigta Formula Bar ), ku qor magaca aad rabto in aad isticmaasho. , oo taabo Gelida^(Enter) .

Waxa kale oo aad samayn kartaa kala duwan oo la magacaabay adigoo gujinaya badhanka cusub^(New) ee daaqada Magaca Manager . Tani waxay furaysaa daaqad Magaca Cusub^{(New Name)}  oo aad geli karto magaca cusub.

Sida caadiga ah, kala duwanaanshaha la magacaabayo waxa loo dejiyay nooc kasta oo kala duwan oo la doortay marka aad gujiso badhanka Cusub^(New) , laakiin waxaad wax ka beddeli kartaa xadkaas ka hor ama ka dib badbaadinta magaca cusub.

Ogsoonow in magacyada kala duwanaanshuhu aysan ku jiri karin meelo bannaan, inkastoo ay ku jiri karaan hoosta iyo xilliyo. Guud ahaan, magacyadu waa inay ku bilowdaan xaraf ka dibna ka kooban yihiin xarfo, tirooyin, muddooyin, ama hoosta.

Magacyadu maaha kiis xasaasi ah, laakiin isticmaalka xarkaha kelmado waaweyn, sida TaxRate ama December2018^{(December2018Sales)} Sales , ayaa ka dhigaysa magacyada mid sahlan in la akhriyo oo la aqoonsado. Ma isticmaali kartid magac kala duwan oo u ekaysiinaya tixraac unug ansax ah, sida Dog26 .

Waxaad wax ka beddeli kartaa magacyada kala duwan eeada ama waxaad bedeli kartaa kala duwanaanta ay tixraacayaan addoo isticmaalaya daaqada Maareeyaha Magaca.^(Manager)

Ogow sidoo kale in tiro kasta oo la magacaabay uu leeyahay baaxad qeexan. Caadi ahaan, baaxaddu waxay u dhigantaa Buug^(Workbook) -hawleed , taas oo macnaheedu yahay magaca baaxadda waxaa laga tixraaci karaa meel kasta oo ka mid ah buugga shaqada. Si kastaba ha ahaatee, waxa kale oo suurtogal ah in lagu yeesho laba ama in ka badan oo isku magac ah oo ku yaal xaashiyo kala duwan, laakiin gudaha buug-shaqo isku mid ah.

Tusaale ahaan, waxaa laga yaabaa inaad haysato faylka xogta iibka oo leh xaashi kala duwan Janaayo^(January) , Febraayo^(February) , Maarso^(March) , iwm. Xaashi kastaa wuxuu lahaan karaa unug (magac ah kala duwan) oo la yiraahdo MonthlySales , laakiin sida caadiga ah baaxadda mid kasta oo ka mid ah magacyadaas waxay ahaan doontaa kaliya xaashida ka kooban waa.

Haddaba, qaacidada =ROUND(MonthlySales,0) waxay siinaysaa iibka Febraayo^(February) , oo lagu soo koobo dhammaan doollarka ugu dhow, haddii qaacidadu ay ku taal xaashida Febraayo^(February) , laakiin iibinta Maarso haddii ay ku taal xaashida ^(March)Maarso^(March) , iwm.

Si looga fogaado jahawareer ku jira buugaagta shaqada oo leh noocyo badan oo xaashiyo kala duwan leh oo isku magac ah ama buugaag shaqo oo dhib badan oo leh daraasiin ama boqolaal magacyo kala duwan leh, waxa ay noqon kartaa mid waxtar leh in lagu daro magaca xaashida qayb ka mid ah magac kasta oo kala duwan.

Tani waxay sidoo kale ka dhigaysaa magac kasta oo kala duwan mid gaar ah, si ay dhammaan magacyada u yeelan karaan baaxadda Buugga -Shaqada. ^(Workbook)Tusaale ahaan, Janaayo_Bilaha Iibka^{(January_MonthlySales)} , Feebarwari_Bilaha ^{(February_MonthlySales)}Iibka^{(Budget_Date)} , Taariikhda_ Miisaaniyada, Taariikhda Dalbashada^(Order_Date) , iwm.

Laba digniinood oo ku saabsan baaxadda safafka la magacaabay:^{(Two cautions regarding the scope of named ranges:)} (1) Ma beddeli kartid baaxadda tirada la magacaabay ka dib marka la sameeyo, iyo (2) waxa kaliya oo aad cayimi kartaa baaxadda tiro cusub oo la magacaabay haddii aad ku abuurto adigoo isticmaalaya badhanka cusub^(New) daaqada Maareeyaha Magaca^{( Name Manager)} .

Haddii aad abuurto magac cusub oo kala duwan adiga oo ku qoraya sanduuqa^(Box) Magaca , baaxaddu waxa ay ahaan doontaa mid ka mid ah Buug^(Workbook) -hawleedka (haddii aanu jirin tiro kale oo isku magac ah), ama xaashida meesha magaca lagu abuurayo. Sidaa darteed, si aad u abuurto magac cusub oo baaxaddiisu ku kooban tahay xaashi gaar ah, isticmaal badhanka "New" Maareeyaha Magaca.

Ugu dambeyntii, kuwa qora macros, magacyada kala duwan si fudud ayaa loogu tixraaci karaa code VBA iyadoo si fudud loo gelinayo magaca kala duwan ee dhexda. Tusaale ahaan, halkii aad ka heli lahayd buug-shaqeedkan. Sheets^{(ThisWorkbook.Sheets)} (1) . Unugyada(2,3) waxaad si fudud u isticmaali kartaa [ SalesTotal ] haddii magacaas uu tixraacayo unuggaas.

Bilow inaad^(Start) isticmaasho noocyo magac leh oo ku jira xaashiyahaaga shaqada ee Excel waxaadna si dhakhso ah u ogaan doontaa faa'iidooyinka! Ku raaxayso!

Why You Should Be Using Named Ranges in Excel

Named ranges are a usеfυl, but often underutilized, feature of Microsoft Excel.  Named rangeѕ can make formulas easier to understand (and debug), simplify the creation of cоmplicated spreadsheеts, and simplify your macros.

A named range is just a range (either a single cell, or a range of cells) to which you assign a name.  You can then use that name in place of normal cell references in formulas, in macros, and for defining the source for graphs or data validation.

Using a range name, like TaxRate, in place of a standard cell reference, like Sheet2!$C$11, can make a spreadsheet easier to understand and debug/audit.

Using Named Ranges in Excel

For example, let’s look at a simple order form.  Our file includes a fillable order form with a dropdown to select the shipping method, plus a second sheet with a table of shipping costs, and the tax rate.

Version 1 (without named ranges) uses normal A1-style cell references in its formulas (shown in the formula bar below).

Version 2 uses named ranges, making its formulas much easier to understand.  Named ranges also make it easier to enter formulas, since Excel will display a list of names, including function names, that you can pick from, whenever you start to type a name in a formula.  Double-click the name in the pick list to add it to your formula.

Opening the Name Manager window from the Formulas tab displays a list of the range names and the cell ranges they reference.

But named ranges have other benefits also.  In our example files, the shipping method is selected using a dropdown (data validation) in cell B13 on Sheet1.  The selected method is then used to lookup the shipping costs on Sheet2.

Without named ranges, the dropdown choices must be manually entered since data validation will not allow you to select a source list on a different sheet.  So all of the choices must be entered twice: once in the dropdown list, and again on Sheet2.  In addition, the two lists must match.

If an error is made in one of the entries in either list, then the shipping cost formula will generate an #N/A error when the erroneous choice is selected.  Naming the list on Sheet2 as ShippingMethods eliminates both problems.

You can reference a named range when defining the data validation for a dropdown list by simply entering =ShippingMethods in the source field, for example.  This allows you to use a list of choices that are on another sheet.

And if the dropdown is referencing the actual cells used in the lookup (for the shipping cost formula), then the dropdown choices will always match the lookup list, avoiding #N/A errors.

Create a Named Range in Excel

To create a named range, simply select the cell or range of cells you want to name, then click in the Name Box (where the selected cell address is normally displayed, just left of the Formula Bar), type the name you want to use, and press Enter.

You can also create a named range by clicking the New button in the Name Manager window.  This opens a New Name window where you can enter the new name.

By default, the range to be named is set to whatever range is selected when you click the New button, but you can edit that range before or after saving the new name.

Note that range names cannot include spaces, although they can include underscores and periods.  Generally, names should begin with a letter and then contain only letters, numbers, periods, or underscores.

Names are not case-sensitive, but using a string of capitalized words, such as TaxRate or December2018Sales, makes the names easier to read and recognize.  You cannot use a range name that mimics a valid cell reference, such as Dog26.

You can edit your range names or change the ranges they refer to using the Name Manager window.

Note also that each named range has a defined scope.  Normally, the scope will default to Workbook, which means the range name can be referenced from anywhere within the workbook.  However, it is also possible to have two or more ranges with the same name on separate sheets, but within the same workbook.

For example, you might have a sales data file with separate sheets for January, February, March, etc.  Each sheet could have a cell (named range) called MonthlySales, but normally the scope of each of those names would only be the sheet containing it.

Thus, the formula =ROUND(MonthlySales,0) would give February sales, rounded to the nearest whole dollar, if the formula is on the February sheet, but March sales if on the March sheet, etc.

To avoid confusion in workbooks having multiple ranges on separate sheets with the same name or simply complicated workbooks with dozens or hundreds of named ranges, it can be helpful to include the sheet name as part of each range name.

This also makes each range name unique, so that all the names can have a Workbook scope.  For example, January_MonthlySales, February_MonthlySales, Budget_Date, Order_Date, etc.

Two cautions regarding the scope of named ranges: (1) You cannot edit the scope of a named range after it is created, and (2) you can only specify the scope of a new named range if you create it using the New button in the Name Manager window.

If you create a new range name by typing it in the Name Box, the scope will default to either Workbook (if no other range with the same name exists), or to the sheet where the name is being created.  Therefore, to create a new named range whose scope is limited to a particular sheet, use the Name Manager “New” button.

Finally, for those who write macros, range names can be easily referenced in VBA code by simply placing the range name within brackets.  For example, instead of ThisWorkbook.Sheets(1).Cells(2,3) you can simply use [SalesTotal] if that name refers to that cell.

Start using named ranges in your Excel worksheets and you will quickly being to appreciate the benefits! Enjoy!

Related posts

• Sida loo tirtiro khadadka banaan ee Excel

• Sida loo Isticmaalo Tilmaamaha Unugyada Hadalka ee Excel

• Sida loo Geliyo Xaashida Shaqada ee Excel Word Doc

• Sida loo isticmaalo Excel's What-If Analysis

• Sida Loo Hagaajiyo Safka gudaha Excel

• Sida loo Hagaajiyo #N/A Khaladaadka gudaha Foomamka Excel sida VLOOKUP

• Sida Loo Raadiyo Excel

• Sida Loo Mideeyo Unugyada, Tiirarka & Safafka gudaha Excel

• Sida loo qoro IF Formula/Bayaanka gudaha Excel

• Sida Loo Dhaqaajiyo Columns gudaha Excel

• Si degdeg ah ugu qor nambarada Word iyo Excel

• Sida loo isticmaalo Flash Fill ee Excel

• Sida Loo Abuuro VBA Macro Ama Script gudaha Excel

• Xarunta Xogta Xaashida Shaqada ee Excel si loo daabaco

• Sida Loo Wadaago Faylka Excel Si Fudud Layskula Shaqeeyo

• Sida Loo Isticmaalo Shaqada PMT ee Excel

• Sida loo Isticmaalo If iyo Nsted Haddii Odhaahyada Excel

• Goorta la Isticmaalayo Tusaha-Match Bedelkii VLOOKUP ee Excel

• Sida loo Furo Word iyo Excel si Badbaado ah

• Farqiga u dhexeeya Microsoft Excel Online iyo Excel ee Desktop