It's been quite some time since I last wrote here. I recently came back to Excel after a hiatus, so first I checked the release history of the insider beta channel that I subscribe to. As usual there were a lot of security and technical fixes, not much of user interest. The newer areas that might interest some readers are Copilot and Python, which are beyond my current reach and ambition, as I think they are for most of you. At some point I will test them, but for now I am too busy looking for ways to promote the xlegant principles with the tools I already use.
The new features are presented in the Excel Labs Add-In, along with AFE (Advanced Formula Environment), on which I have expounded at length in this blog, and to which I return now to highlight some tips on function and array design. I was a big fan of Name Manager already, and AFE has become a stable and proven enhancement of this invaluable tool. It adds transparency, integrity and elegance, minimizing the risk hidden in cell formulas.
Pick and Sort Columns
Most people have a cheat sheet, or several, as a handy reference for common, relatively limited and static data sets. You might have a spreadsheet with lists of contacts, or products, or locations with distance. Maybe you have a list of metric conversions in your kitchen or workshop. You might even have created a startup file, like the virtual Swiss army knife I've described, to store them all. If so, kudos, that's an excellent step towards transparent organization.
Name Manager with the AFE interface offers an elegant alternative to store lists as array constants. By placing them in a LAMBDA function, you can build in column selection, filtering and sorting to minimize cell manipulation. One easily readable, commentable, auditable function in AFE does all the work and delivers the result to a single (array) cell.
To illustrate, I downloaded a list of the fifty US states, with four columns: state name, postal abbreviation, capitol city, and year admitted to the Union. With header, that's a 51x4 cell range. To turn it into an array constant, I use ARRAYTOTEXT(range,1), where range= the data range and 1 =strict formatting, giving me the proper punctuation. This is in a cell to the side of the range, which I can then open to edit (F2), calculate (F9), and copy (Ctrl+C) to put the array on the clipboard. The formula cell and the data range have served their purpose and can be deleted. The clipboard contents are pasted to a name, "states," in module "a" for arrays in AFE and saved.
Now when the name is entered in a single cell, the 51x4 data array is recreated. In itself this has not accomplished much, and it's a matter of preference whether you like to keep the data in a sheet or in a name. If I had left the data in the sheet and formatted it as a table, I could filter and sort and reference it directly. As an array the data can also be filtered but are defined by the top-left cell and can therefore not be sorted directly. It's also not easy to reference. Now, if I add optional arguments to the named array with LAMBDA, I can produce a variety of outputs, for example, St and Year sorted by Year, or lookup Capitol by State entry. Making all arguments optional, I can still get the defined array by omitting arguments. I quickly see ways to make the name method more powerful and elegant than the sheet table method.
First, as I approach the challenge of choosing multiple columns with a single argument, what comes to mind is a concatenation of single-digit column indices, which will then be split to an array for CHOOSECOLS. For example, to pick the column sequence 3,2,4, I would enter 324. This works for up to nine columns. What if there are more than nine columns? Of course, I could add an optional argument for each column, but a cheat sheet with that many columns is improbable, and I accept the limitation price of the simplicity value. So how to split characters to an array?
Using MID(text, start_num, num_chars) I can pick one character at a time while defining the start_num as an array itself, using a SEQUENCE of members equal to the length of the input.
To make this split function useful itself, I'll make it a separate LAMBDA, "split" in module "f" for my general functions. I want to be able to output either a vertical or horizontal array of alphanumeric characters. By trial and error, I find it most convenient and reliable to default vertical and provide a horizontal option via Boolean argument, and likewise to default text and provide a values option via Boolean argument.
I have in fact taken this further, to enable array input (splitting the members in the opposite dimension) and right-align of the output arrays, but for now this is sufficient for the task at hand.
I'm ready to return to the states array and convert it to LAMBDA. The sort task is straightforward and will not require an intermediate process, except to remove and hold the header during sorting. I'll add a third option to drop the header in the output.
The basic format change from array constant to LAMBDA is shown here with the list folded. Upon save, if you have used the name in the sheet, it will err because the usage syntax changes. When used in the sheet the name must now be followed by parentheses, whether any arguments are filled or not.
The efficient way to process optional arguments is one by one building on the previous one, naming the steps logically and commenting as you go, and always passing the data unchanged if the argument is omitted. The columns are selected first, and if omitted are all passed. Sorting is second, and if omitted returns the array as entered. Finally, the header is replaced or not based on the dropheader argument.
As with all functions, there are a few rules to learn:
[columns] cannot contain a digit which is greater than the number of data columns
[columns] can be in any order
[sort] specifies the output column, not the input column. It therefore cannot contain a digit which is greater than the number of columns chosen. If [columns]=24 i.e. St, Year, [sort]=2 sorts by year, [sort]=3 is invalid.
[sort] sorts ascending if positive, descending if negative
The point of this example is to show the utility and efficiency of storing frequently referenced lists as named arrays, with optional arguments for manipulation.
Coming Soon
Next time I'll show
more convenient arrays
how to customize the display of numbers without changing the cell format
how to pass multiple Boolean switches in a single argument
your requests?
Commenti