New Text and Array Functions for Excel(techcommunity.microsoft.com) |
New Text and Array Functions for Excel(techcommunity.microsoft.com) |
I kind of wish they went for the matlab cell array style where a function can return an array, but it just becomes a data structured stored within a single cell.
So TEXTSPLIT (which is great, finally), would return an object like ARRAY("I", "SAW","A","CAT") and if you wanted to unpack it you could drag a formula that was something like =$A$1?0, =$A$1?1, =$A$1?2, etc.
Or maybe just one single black magic affects-nearby-cells function called "UNPACK"
Reshaping is something could have used many times in the past. I used to pull data into PERL first to reshape it but that took a bit of code, then I learned about numpy doing it in one line. But I still have to import back into Excel. The fact that it is in there now is useful to me.
I 1000% agree with your matlab retval suggestion however. I hate how Excel fudges array return values by just blasting a range of cells one time!
My excel knowledge has been somewhat stagnant in the past decade. Have they added an ARRAYFUNC() like in Google Sheets, or do I still need to hit "ctrl-shift-enter" to designate one?
I had a go at writing a DLL plugin for Excel that did this years ago. I ended up with a kind of SQL, where each cell has a result set of records. The purpose was to make a functional language for consultants starting with a familiar environment to them. I even integrated a system where you clicked the cell and a pop up would show the data records. It was an ugly proof-of-concept, using strings that just identified each result set, and using custom functions. Excel is beautifully functional, with some nice parallels with SQL, and your data flow/dependencies are naturally visible. Excel is far less scary to most consultants than imperative programming is. I wanted to be able to model the data flows, use sheets for consultants to define custom pure functions for our system, and the final outcome was a reactive data system where data updates could flow (push) into outputs. I failed to get it delivered because I failed to get the COM interfaces working working: I failed to tie together Excel automation as a library engine (Excel COM API), Excel custom functions (plug in DLL), Delphi 7, and my own code.
https://www.lifewire.com/excel-single-cell-array-formula-312...
I guess this warrants linking to https://shinesolutions.com/2018/01/08/falsehoods-programmers....
Another one that is massively overdue: take multiple arrays as arguments and return the distinct values, sorted (kind of like the remove duplicate button, but that doesn't require to click a button). [edit] actually it was introduced in 2021 ("UNIQUE" function)
Also take multiple arrays and returns the values that are in common (like an inner join). Use case: you want to align two time series by creating a 3rd time series made of the dates common to both original time series.
Then you can have all sort of finance related function. Validate the checksum for an ISIN, CUSIP, SEDOL, etc.
Excel should also come with the most common holiday list (all the major cities at least).
You just have to add a column and you are an XLOOKUP and a quick filtering away from the result you want. It’s a fairly common operation.
> Excel should also come with the most common holiday list (all the major cities at least).
It’s the same. You just have to add a table and do a lookup.
Yeah you can do that by introducing multiple columns or creating a VBA UDF. My point isn’t that it cannot be done (like TEXTSPLIT or XLOOKUP, there were more convoluted ways to do that already). It’s more that it is something common enough that there should be a simpke function for that.
On the holiday lists I mean the list of bank holidays by major city. I believe right now you need to provide that yourself, but it is something microsoft could build and maintain centrally instead of everyone reinventing the wheel. They do that for timezones/time change in the OS already.
[0] https://www.youtube.com/watch?v=ICp2-EUKQAI [1] https://www.youtube.com/watch?v=xubbVvKbUfY
Pair this with Alt+Enter (line break) and adding 4 spaces at the start and you can nest functions in a familiar, albeit manual, way.
https://www.microsoft.com/en-us/garage/profiles/advanced-for...
If you want to do lots of reshaping data and performing operations on tables of data, you are better using ETL tools (Extract Transform Load) that were designed for this task. For example: Easy Data Transform, Tableau Prep or Alteryx.
"Let's force the user to employ data-management best practices" is, for better or for worse, very much not the design philosophy of Excel. (More to the point, if you must consume the data that someone else produces, then you'd like very much to be able to deal with their less-than-best practices.)
Users really like to highlight rows, or use coloring to track their progress, or do some insane multi-color-mixed-with-other-formatting system to indicate complex statuses.
I’d like to be able to work with that terrible data.
Next day… CTRL+F… text not found … what do you mean, I’m looking right at an example of this string…
Making it a substantially different color outline or something would be a nice feature. Maybe a 2032 feature.
I had become my go-to for all sorts for all sorts of data munging in excel.
There are already ugly, kludgey ways of doing it (or doing it outside of Excel altogether) but this will be faster and more elegant
Why do we have AVERAGEIF, but not AVERAGEAIF or GEOMEANIF, MAX and MAXIFS, but not MAXIF, etc?
I can’t find logic in that.
(https://support.microsoft.com/en-us/office/excel-functions-b...)
Ideally, the “IF” would be a separate function that filters out cells, so that it could be used with VBA functions taking multiple cells, too.
While at it, these IF functions should use lambdas for specifying the criteria instead of strings such as “>5”
https://support.microsoft.com/en-us/office/if-function-69aed...
Yes, you can create new cells that compute the Boolean, then use IF to populate a new range with the values that pass the test, and then call Foo on the new range, but avoiding such ‘pollution’ of spreadsheets is the whole reason these functions exist.
Apparently Microsoft is creating a plugin.
(Not affiliated)
I had an idea Excel had something as well but maybe not?
The point I wanted to make is that sometimes you can just get results quickly by some manual manipulation like sorting in complement to formula.
Full Name | Nickname
Smith, John | Johnny
Ayers, Danielle | Dani
McDougle, Jack | Jack
Jason, Charles |
James, Trent |
Myers, Jessica |
David, Katherine |
This is known as flash fill and already implemented: https://support.microsoft.com/en-us/office/using-flash-fill-.... Except AFAIK it still uses hard-coded pattern recognition. This is exactly the type of thing ML is good for!
function REVERSE_STRING(input) {
return input.split("").reverse().join("");
}
[0] https://www.reddit.com/r/IAmA/comments/m22uc6/we_are_the_mic...