Named Range in Origin 2022

はじめに

Origin 2022 offers a new feature called “Named Range”. If you are an Excel user, you may be familiar with the concept. Well, now we have them in Origin. For those who are unfamiliar with Named Ranges, they are essentially like a variable that points to a range of cells in a worksheet making it easier to refer to the range. That variable can then be used in many places where formula are used including:

  • Set Column Values formula.
  • Cell formula.

Some features of named ranges (Names) include:

  • Assign name to a cell, a range of cells, or a column
  • Names can be defined with scope set to sheet, book or project level
  • Use the defined names in cell formula and column formula for calculations
  • Names can also be used for Reference Lines in graphs
  • Name Manager dialog to mange defined names
  • Use auto-complete to quickly find and insert functions and range names to complete your formula
  • View list of Named Ranges in Object Manger. Double-click to open Named Range Manager dialog.

How to Define and Manage a Name

Defining a Name is simple. There are three methods:

First Method

 

 

Select a single or contiguous range of cells and, from the Data menu, choose Define Name… . In the dialog, provide a name for the Name and define its scope. The Name must consist of a minimum of four ASCII alphanumeric characters (A-Z,a-z,0-9), start with a letter,  and contain no spaces or punctuation marks. The scope can be one of Sheet, Book, or Project.

Second Method

Select a single or contiguous range of cells and, from the Formula Bar, enter a Name in the leftmost edit box. When defining the Name in this way, the scope is automatically set to Book.

Third Method

The third method for creating a Name is to use the Mini Toolbar when selecting a single cell. This method only works for single cell selection.

A Cool Trick

Here’s a cool trick: Let’s say you want to define a Name based on a cell and there is a text identifier in the cell just to the left of the selected cell. When defining the Name, the dialog will automatically pick up that identifier as the default name of the Name. Watch:

Managing Named Ranges

Defined Names can be managed via the Name Manager found under the Data menu. The dialog supports changing a Name, Scope, the Refers To cell range, and adding a Comment. Right-clicking allows you to Insert a new Name or Delete an existing one.

Named ranges of active book can also be viewed in Object Manager. Different Icons are used to represent named ranges of different levels. Click on it. It will select the corresponding range in the sheet. Double click it will open Name Manager for further editing.

Using Named Ranges

As I said in the intro, Names can be used in a number of places, most notably in various types of formula. They are very simple to use — just put them into the formula like this …

total(MyName) + stddev(MyName)

… where MyName is a Name pointing to a range of cells in a column.

Remember scope! The Name is only valid for the scope it is defined for! In fact, we have found that setting Names to Book-level scope is the most practical and useful scope.

Bonus Feature- Names as Functions

Lastly, Names can be used as “lookup functions” for XY column pairs. The idea is that, if you define a Name on an entire Y column, you have the option to Define as Function that Name. Origin will then associate that Y column’s X column forming a “lookup mechanism” for values in the Y column. By default it’s doing linear interpolation. But you can also enter MyFunc(C, bspline) for bspline interpolation.

In the following animation, I Define as Function a Name based on column B. Then, I use that Name as a function in column D to look up values from B based on values in C. It’s easiest to watch!

まとめ

Well that’s a quick introduction to Named Range in Origin 2022. Thanks for reading!

About Chris Drozdowski

Chris Drozdowski is a Product Support Engineer at OriginLab. He loves to talk to customers and educate them. He particularly relishes diagnosing and solving difficult, edge-case issues. As well, he contributes code to help solve problems or enhance user experience. In his down time at work, he likes to research and write about esoteric product features. Outside of work, he enjoys spending time with his family, having fun with C++, working on his aquarium, and exploring craft beers.

View all posts by Chris Drozdowski →

2 Comments on “Named Range in Origin 2022”

  1. Great feature!
    What value does a name defined as a function return: the closest value, the interpolated value or something else?

    1. Hi Andrey,
      It’s linear interpolation by default.
      But in the coming beta3, user can enter 2nd parameter e.g. MyFunc(C, bspline) to do bspline interpolation.

      Thanks, Snow

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です