Combine two data ranges into one range (Google Dri

2019-07-20 14:19发布

问题:

Hi there I am looking to combine two data ranges/arrays into one in order to feed them into excel FREQUENCY function.

Example: First data range - B5:F50 Second data range - J5:N50 Bins data range - I5:I16 Function definition - FREQUENCY(data_array; bins_array)

Basically I am lazy and I don't want to reshuffle my excel script to spit out both datasets side by side so that I can reference them using something like B5:K50 range. Is there any way I can combine both datasets into data_array using some kind of formula? Maybe to end up with something along the line of =FREQUENCY((B5:F50,J5:N50); I5:I16) ?

BTW: Either of

=FREQUENCY(B5:F50; I5:I16)
=FREQUENCY(J5:N50; I5:I16)

work just file on their own for me.

Update

Actual formula definition FREQUENCY(data, classes)

2013 MS Excel (unrelated)

回答1:

In MS Excel FREQUENCY function accepts a "union" as the first argument, i.e. a list of references separated by commas and enclosed in parentheses e.g.

=FREQUENCY((B5:F50,J5:N50),I5:I16)

Note: the "bins array" can also be a union if required

In "Google sheets" I don't think the same thing is possible - there may be a clever workaround, but I'm not aware of it



回答2:

The Using Arrays page has some details that worked for me: https://support.google.com/docs/answer/6208276?hl=en

It says:

"You can join multiple ranges into one continuous range using this same punctuation, which works the same way as VMERGE. For example, to combine values from A1-A10 with the values from D1-D10, you can use the following formula to create a range in a continuous column: ={A1:A10; D1:D10}"

I have two named ranges, so I was able to use {namedRange1;namedRange2} and it gave me one continuous range.