14 New Excel Functions 'Seen from Google Sheets!' - 4 TAKE / DROP

This is the fourth article in a series verifying the 14 new functions added to Excel from the perspective of Google Sheets.

• Features of the functions

• Pros, cons, and usage in Excel

• Differences from Google Sheets features and functions

• How to compensate for features not in Google Sheets

We will verify these mainly from these four perspectives.

Previous article

With this, we have finally verified half of the 14 new functions, which is 7.

Aren't there just too many new functions...

Isn't it tough for 'Kansuu-chan' (who personifies Excel functions) to keep up (if we were to add more characters)?

The array manipulation functions added this time often come in sets of two, so I feel like we're suddenly getting a lot of twin characters, like one with their left eye covered by bangs and the other with their right eye covered lol

The function gap between the vast majority of beginners (who can use about SUM) and a few enthusiasts is widening even further.

14 New Excel Functions: TAKE / DROP

The fourth installment of the 14 new Excel functions covers the SHIFT selection type functions, TAKE and DROP.

These two functions are TAKE, which selects the range to keep, and DROP, which selects the range to drop (discard). They are like yin and yang, or perhaps Shirokuro Unjash, acting in opposite ways. Of course, they can be used for multi-purpose tasks lol

Let's verify these two together this time as well.

Features of TAKE / DROP

The EXPAND function, which I covered in the first installment of the Excel new function series, was a function to expand the target range (array), but TAKE and DROP are conversely both functions to shrink (make smaller) the range (array).

For EXPAND, please refer to the following.

The arguments and behavior of TAKE and DROP are summarized below.

Let's look at how each works in Excel.

■TAKE function

■DROP function

The arguments for both TAKE and DROP are the same, aren't they?
"Keep" and "Discard" - did you understand the difference?

By the way, while the rows and columns arguments can be omitted,

=TAKE(A1:D7) or =DROP(A1:D7) will not work.

=TAKE(A1:D7,) and =DROP(A1:D7,) are fine but both of these simply return A1:D7 as is.

(Aside) TAKE and DROP can each be substituted for the other

By the way, if you adjust the arguments for DROP, you can do the same thing as TAKE. The reverse is also true; you can substitute DROP with TAKE as well.

For example, when you have data in A1:D7 that is 7 rows by 4 columns,

• keep the top 3 rows

• discard the bottom 4 rows

These two ultimately return the same result. In other words,

In this way, you can get the same result as TAKE using DROP.

The same can be said for the column direction.

However, just because of that, I don't think in this case that TAKE is all you need and DROP is unnecessary.

Considering the trouble of getting the number of rows in an array (range) and calculating the difference, I think it is better to use TAKE and DROP differently depending on the situation because they allow for intuitive and easy array manipulation.

By the way, what I call Shift-selection type is because, while CHOOSEROWS and CHOOSECOLS (which I plan to verify next time) use a Ctrl-selection style of specification that allows for non-contiguous selection, this one performs a Shift-selection style operation that selects continuous rows (or columns) from the beginning or the end.

Of course, this is not an official classification; I just call them that on my own.

For a detailed explanation of TAKE and DROP, please refer to the familiar Office Tanaka.

Pros, cons, and usage in Excel

The advantage is that you can easily perform array processing such as getting only the necessary parts from a range (array), or deleting unnecessary parts.

On the other hand, the disadvantage is that you can only specify by the number of rows (or columns) from the beginning or the end, I suppose.

In short, when you have data with 10 rows and 5 columns,

you can perform processing from the edges like this, but you cannot specify a starting row (or column) to get rows 2 through 7 of 10-row, 5-column data, or to discard (exclude) columns 2 through 4.

In terms of string manipulation functions, it's like being able to perform processing similar to the LEFT or RIGHT functions, but not being able to perform MID function-like processing, I guess.

(Aside) Extracting the middle part of a range or array

However, this can also be done with a little ingenuity.

I think it's easier to nest DROP than TAKE.

Well, it's relatively simple.

As for use cases, it would be used in the middle of complex array processing, or for finishing touches. It seems it will be used often for getting (or excluding) the first row (leftmost column) or the last row (rightmost column).

In terms of GAS (JavaScript) array processing, it might be said to have the same usability as the shift and pop methods.

'Ikini Kotayeru Biboroku' (Memo for answering immediately)'s usage examples also use them partially in complex processes that break through with sheet functions by fully utilizing new functions for tasks that previously required programming, such as prime factorization and the knapsack problem.

TAKE usage example

DROP usage example

DROP can be used to delete the initial empty row (empty column) at the end in an array push formula using REDUCE.

It's necessary for complex processing, but general users probably won't use it much, I suppose. In the first place, array processing itself is not something normal people do much, though...

Differences between Google Sheets features and functions

Unfortunately, Google Sheets does not have functions equivalent to TAKE or DROP.

First, if you are processing a cell range rather than an array, you can perform the same processing as TAKE with the OFFSET function. Considering that the starting point does not have to be the first row or the leftmost column, it might be more flexible than TAKE.

However, unfortunately, OFFSET cannot be used on arrays. It is only valid for cell ranges. Honestly, if OFFSET could be used on arrays, there are many cases where things could be solved more easily.

For functions that can be used on arrays, if you only need to process from the beginning like TAKE, you can use ARRAY_CONSTRAIN, a function that shrinks arrays, as a substitute.

ARRAY_CONSTRAIN is a function not found in Excel, but unlike TAKE, you cannot omit the row or column arguments. Also, there is no feature to retrieve from the bottom (the end) by using negative arguments.

In the first place, Google Sheets currently cannot perform operations like specifying negative arguments to process from the reverse (last row/last column).

Hmm, I guess you could say TAKE is a superior version of ARRAY_CONSTRAIN.

I cannot find a function in Google Sheets that is close to DROP, which performs the action of deleting (dropping) a specified range from an array.

How to compensate for features not in Google Sheets

Once again, we will use the FILTER function.

Don't you think the FILTER function appears a lot? That's right, for array operations, the FILTER function is
diabolically powerful.

It's lovely.

So, including how it behaves with negative arguments, how do we create a formula with the FILTER function to achieve the behavior of TAKE and DROP functions?

Let's go through this with Q&A.

Q. Can you create a formula in Google Sheets that returns the same result as TAKE?

If you would like to try it yourself, please use the sample data below. It is a 7-row by 4-column array of letters.

By the way, as for DROP, if you can create TAKE, you can create it just by arranging that.

A	B	C	D
E	F	G	H
I	J	K	L
M	N	O	P
Q	R	S	T
U	V	W	X
Y	Z			

It is relatively simple if you do not have to consider the behavior when negative numbers are used.
If you find it difficult, please try building it without the negative conditions first.

How about it? Do you think you can create an alternative formula for TAKE?

↓↓
The answer starts here.

↓↓

A. Reproducing TAKE with existing Google Sheets functions

This time, rather than giving the answer immediately, let's build the formula step by step.

The basics of manipulating an array (cell range) by row and column numbers

To manipulate an array (cell range) by row number or column number such as the Nth row or Nth column, you first need to get the height (number of rows) and width (number of columns) of that array and assign numbers to the rows and columns.

* This is not about actually assigning (outputting) numbers, but about processing within a virtual formula.

ROW function and COLUMN function are tempting to use, but if you try to use the row and column numbers on the sheet, you need to consider the starting position (the top-left origin of the range), and in the first place, they cannot be used for arrays that are not cell ranges.

What you should use are the ROWS function, COLUMNS function.

Since these two can be used not only for ranges but also for arrays,they are essential functions when manipulating arrays.

By combining the number of rows and columns that can be obtained with this with the SEQUENCE function, you can assign row indices and column indices to the target array (range).

Narrowing down in both row and column directions using the FILTER function

When using the FILTER function, what should you do if you want to narrow down not only by conditions in the row direction (vertical) but also in the column direction (horizontal)?

Since FILTER can only narrow down in one direction, either rows or columns, you need to nest FILTERs to narrow down in both.

In short, it's like taking the result of narrowing down rows with FILTER and then narrowing it down further by columns.

If you don't have to consider the behavior when using negative numbers, it's not that difficult of a formula.

For example, for the range A1:D7, in Excel

=TAKE(A1:D7,4,2)

If you want to create a formula in Google Sheets that returns the same result as this,

Like this, first you narrow down the rows with the condition that the row number is 4 or less,

and then further narrow it down in the column direction with the condition that the column number is 2 or less.
That is the formula.

If you LAMBDA this and move the parts corresponding to the TAKE arguments outside,

It becomes like this.

Organizing the logic for omissions and negative number specifications

So, let's organize how to handle the behavior when omitted (when 0) and when negative.

By the way, if r (rows) is omitted, it is treated as 0 in operators, but in actual TAKE and DROP, if you specify 0 for the number of rows or columns, it results in an error. Please be aware of this point.

Since the behavior for columns is the same as for rows, I'll add that later. For now, here is a simplified formula focusing only on rows.

↑ Let's use this as a base for verification.

↓ Here are the patterns for each.

You understand r=0 (including when omitted), right?
You just need to return everything (no exclusions).

When it's negative, for example, if r is -2 and the number of rows in the array is 7, you just need to return the last two (row numbers 6, 7), so,

Row number > Array rows (7) + r (-2)

is what you should do.

This time as well, since it's a formula that returns an array for a single condition, unfortunately, IFS results in an error.

Then, what comes to mind is nested IFs, but,

Hmm, not great. Let's make it a bit more mathematical and shorter.

Think about the condition part mathematically (arithmetically).

↑ This is just extracting the condition part of FILTER and moving the variables to the left side.
It's more elementary school arithmetic than mathematics, right? I think.

We will bring the two conditional expressions below closer to the expression for the r > 0 case.

This part might be a little difficult, but since it is an expression to determine whether it is 0 or less,
if r > 0, even if you multiply the result of SEQUENCE(ROWS(array)) -r by r,it does not affect the result of 'is it 0 or less?'.

When r = 0, it means you are multiplying by 0, so all elements become 0,
in other words, all satisfy <= 0.

When r < 0, it is a bit complicated. However, the way to think about it is that we are using the fact that an array of numbers that satisfied >= 0 will satisfy <= 0 if you multiply by a negative number and invert it.

Also, since all the numbers that appear are integers, we adjust the part that is < 0 to be satisfied by <= 0 by +1 to the number of rows.

Since - (ROWS(array) +1) is only added when this r < 0 condition is met, let's use a conditional expression for this part only.

By doing this, when r < 0 is met, it becomes TRUE and is treated as *1, and otherwise it becomes FALSE and *0 so it becomes 0, which does not affect the calculation.

With this, we were able to combine the FILTER condition part into a single expression without IF branching based on the sign of r.

This kind of mathematical talk is difficult to explain...

Also, although the expression is short, if you make full use of operators, there is the disadvantage that it looks confusing to other people,and the person who takes it over cannot maintain it.

However, if it is a self-made formula like a part that performs general-purpose processing like this time, I think you don't have to worry about subsequent maintenance.

When this condition is applied to the base formula for extracting rows, it becomes as follows,

and by layering the same FILTER processing in the column direction as well,

it became an expression like this. The TAKE substitute formula is complete.

Even this is the shortest formula among the many I tried lol

TAKE Alternative Formula [Final Version]

Let's test the behavior of the final version of the TAKE alternative formula.

We have confirmed that it behaves the same as TAKE, where omitting the argument returns all rows (or columns), and using a negative value keeps rows or columns counted from the bottom (or right edge).

DROP Alternative Formula [Final Version]

DROP is an adaptation of the TAKE formula.
Sorry, but I will omit the creation process. (It's not that interesting anyway.)

You can see that this performs the DROP action up to the specified row (or column). The behavior for negative values and omissions also yielded the desired results.

TAKE / DROP Google Sheets Alternative Formula Summary

Here is the summary.

TAKE Alternative Formula

DROP Alternative Formula

Covering all patterns including negative values results in the complex formula above, but in actual scenarios, it is easier to change the formula depending on the case.

Using FILTER with row and column numbers as conditions allows you to manipulate the array both vertically and horizontally; understanding just this is sufficient.

Practical formulas for cases involving only the first or last row

For common use cases where you want to keep (or discard) only the first or last row, it is simpler to use functions other than FILTER when focusing on array manipulation.

If you only need to extract one row, use INDEX.

Unlike OFFSET, INDEX can be used for both cell ranges and arrays.

In cases where you want to discard only the first row or only the last row, the result will consist of multiple rows, so you must use FILTER here.

Discarding only the last row is the same as keeping the number of rows - 1 from the beginning, so you could use ARRAY_CONSTRAIN, which I touched on briefly at the beginning, but the description becomes longer because you also need to specify the columns.

I will omit the column-direction examples as they are just variations of the above.

For ranges, there is also a way to use OFFSET, and the point is that it is best to know various methods and use the functions appropriately depending on the situation.

This concludes this verification.

Due to the year-end and New Year holidays, there will be no updates to this series of articles next week.

The Kamakura-dono series has ended, but in the new year of 2023, the 14 New Excel Functions series will continue!

■Next article in this series