Here is a description of the function.
It would be very useful to have.
There is not such function in the Calc. But you can try to create it with an user defined Macro function - if you know how the Take() function works in the Excel.
INDEX, OFFSET, INDIRECT, …
Spreadsheets use to work since 1979 with calculated ranges.
Bug report tdf#150997
MS have since added many single functions that used to be done in other ways in my 2010 Excel version.
You can see the bug list: Bug 150900 (Excel-Functions) - [META] Excel Functions
我更建议内置具有lambda函数特性的一些高级函数，比如可以迭代数组的reduce，或者lambda的递归特性。
@changxin 但我更喜欢 KISS 方法并尝试使用最简单和最古老的函数来解决问题，这样不仅最新版本电子表格的所有者可以使用这些公式，而且没有这些最新程序的用户也可以使用。 有多少人——那么多意见。
But I prefer the KISS approach and try to use the simplest and oldest functions to solve the problem so that not only owners of the latest version of the spreadsheet can use the formulas, but also users who don’t have these latest programs. How many people - so many opinions.
1 Like
You already have it (as stated above).
Let’s illustrate this with an example from the presentation mentioned in your message.
The example constructs a formula for cell F3:
=TAKE(A3:D35;10;3)
Now we do it in Calc.
- Open the attached Take.xlsx file taken from the presentation.
- Enter the array formula in cell F3 (using the Ctrl+Shift+Enter keys):
=OFFSET(A3:D35;0;0;10;3)
- Enjoy!
Take.xlsx (13.4 KB)
2 Likes
“Very useful” is a bit of a stretch. As far as I can tell, OFFSET() can do practically the same, and more. The most significant differences are:
- OFFSET() creates a “transient object” (an intermediate result) which can be acted upon, while TAKE() outputs a more tangible object (a cell range).
- No error returned from OFFSET() for “out of bounds”.
- The “negative selector to start from end of table” requires a bit of extra work to implement with OFFSET().
I can see where TAKE() may work well with Excel database ranges, where OFFSET() usage becomes less “intuitive”. If you must have this, you must have MS Excel. This looks to be Microsoft’s EEE at work, which I thought they abandoned some 30 years ago. That abandon may have been only for web browsers.
2 Likes
I realize that Excel’s TAKE() function, like also SEQUENCE() and probably others, makes the outcome more “visual”, instead of the transient object created by OFFSET(). This visual representation comes at the cost of “grid real estate”; you need to make sure that the target cell range for the functions will not hold other significant data.
Within a “functional programming paradigm” (which is where spreadsheets belong), this kind of “function with side effects” (function making changes in “external objects” - other cells than where the formula is located) is frequently frowned upon. Granted, there are other functions, also in use by Calc, which does this, mostly when they “have to” (function returns multivalued result), and mostly to a consistent set of predefined cells.
Edit:
Also, as @erw2 pointed out, forcing “array output” from OFFSET() makes it even more like TAKE(). I keep forgetting the power of array formulas. Good to have that reminder!