[HN Gopher] New Text and Array Functions for Excel
___________________________________________________________________
New Text and Array Functions for Excel
Author : eDameXxX
Score : 42 points
Date : 2022-09-06 19:55 UTC (3 hours ago)
(HTM) web link (techcommunity.microsoft.com)
(TXT) w3m dump (techcommunity.microsoft.com)
| carabiner wrote:
| I'm surprised it's 2022 and they haven't embraced a multiline
| equation editor.
| Valgrim wrote:
| There is a microsoft garage project that helps you do it that
| way:
|
| https://www.microsoft.com/en-us/garage/profiles/advanced-for...
| leokennis wrote:
| Next challenge: make the find/replace dialog better than the
| confusing tabbed mess it is today. And make it non-modal for
| simple search/replace.
| spywaregorilla wrote:
| Array functions feel like they're just too far from the excel
| design thinking. A single function that affects nearby cells is
| hard for me to swallow.
|
| 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"
| evandwight wrote:
| Don't they have arrays in single cells?
|
| https://www.lifewire.com/excel-single-cell-array-formula-312...
| cm2187 wrote:
| No, these function use an aggregation function (like SUM). If
| you don't use an aggregation function the value of the cell
| is the top left element of the array. The parent suggests a
| cell which value is an array object, which can then be
| queried by another formula.
| evandwight wrote:
| Ah thanks for clearing
| spywaregorilla wrote:
| It's been a while, but I think these functions must be map-
| reduce expressions. You cannot return an array and pull out
| it's elements elsewhere. You cannot do operations with the
| resulting array. Like a merge or whatever.
| robocat wrote:
| > 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.
|
| 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.
| waynenilsen wrote:
| I am very surprised they have not yet embraced the dplyr/tidyr-
| style melt/cast gather/spread pivot/unpivot functions for arrays
| auxym wrote:
| I am not familiar with the R ecosystem, but have you tried
| PoweredQuery?
|
| I had become my go-to for all sorts for all sorts of data
| munging in excel.
| cm2187 wrote:
| 20 years overdue...
|
| 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).
| omarhaneef wrote:
| It's 2022 and how come they don't have GPT4 and some stable
| diffusion built in?
| armchairhacker wrote:
| seriously though, I think a copilot-like ML to improve flash
| fill is a genuinely amazing idea which could save insane hours,
| especially with people less familiar with complex
| formulas/coding
| evandwight wrote:
| Someone just created that!
|
| https://excelformulabot.com/
|
| Apparently Microsoft is creating a plugin.
|
| (Not affiliated)
| dhosek wrote:
| The functionality I want is a count-by-format function. I had to
| write my own in VBA and of course that means every time I open
| that sheet I have to approve its use of macros (and it also
| doesn't always catch format changes that impact the calculation)
| auxym wrote:
| Encoding data in cell formatting is questionable practice.
| JadeNB wrote:
| > Encoding data in cell formatting is questionable practice.
|
| "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.)
| function_seven wrote:
| Agreed, but I can't control half the sheets I interact with.
|
| 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.
| xnx wrote:
| All these new text functions and still no support for regex?
| Google Sheets has had regex support for years.
| auxym wrote:
| Agreed. You can do regex via VBA but I really want a built-in
| cell function for regex search, regex extract, and regex
| replace.
| guhidalg wrote:
| XLOOKUP has some simple simple regex patterns.
___________________________________________________________________
(page generated 2022-09-06 23:00 UTC)