“Alright, now I can explain without worrying.”
Saito began to enthusiastically explain the functions while writing them on the wall.
=ARRAYFORMULA(LEFT(A2:A20, 5))
“As for the combination of functions from earlier, what the user wanted to do was something like this. Apply the LEFT function to the value in column A and take the first 5 characters. Although it doesn’t matter how many characters there are, there are cases where this formula will have issues. Do you know where it is?”
“It’s when the values you want to process go for more than 20 rows.”
=ARRAYFORMULA(LEFT(A2:A, 5))
“I would write that like this. You could add something like IFERROR, but if it’s with LEFT, it will return a blank anyway, so there won’t be any errors and you don’t have to worry about it.”
“That’s right. When using ARRAYFORMULA, it’s important to know how to deal with dynamic range specifications. This usuer probably wanted to accurately specify the rows to be processed. Then, if it becomes necessary to deal with changes in the number of rows to be processed, I would write this.”
=ARRAYFORMULA(LEFT(INDIRECT(“A2:A”&COUNTA(A:A)), 5))
“I see. You count the number of values you want to process with COUNTA, convert it from a string to a cell range using INDIRECT, and then specify it as the processing range of ARRAYFORMULA.”
“That’s what is it. Finally, ROW.”
=ARRAYFORMULA(LEFT(INDIRECT(“A”&ROW(A2)&”:A”&COUNTA(A:A)),5))
“The weakness of INDIRECT is that it is vunerable to changes such as adding rows. If the number of header rows was increased to two, a gap would occur. Because of that, ROW is used to capture the starting row to be processed.”
“Is that so. So combinations like that Nue are common. But with this, you can’t deal with column changes, so wouldn’t it be better to use COLUMN to dynamically describe the columns?”
“Correct. If you want to take columns into consideration, there is another way to write that.”
=ARRAYFORMULA(LEFT(INDIRECT(CELL(“address”,A2)&”:”&ADDRESS(COUNTA(A:A),COLUMN(A2))),5))
“CELL is a function that obtains cell information and can be used to convert cells to strings, so it can be used to hold down the starting row. For the last row, we can get that number using COUNTA and combine it with ADDRESS. This then creates a range of strings.”
To be honest, I had never used CELL. In addition to functions not found in Excel, such as ARRAYFORMULA, there were still many functions I didn’t know of. While I was alive, I never had the chance to see sheets created by other Excel craftsmen, but as I completed quests like this as a worker, I came across combinations and uses of functions that I hand never thought of.
I realised how small the world I used to live in was. I not only had to increase my physical ability in order to fight functions, I also had to increase my knowledge. I couldn’t rely on Saito forever.
※Functions
CELL https://support.google.com/docs/answer/3267071
ADDRESS https://support.google.com/docs/answer/3093308
Author’s Note
It was pointed out to me on Twitter that I could have written it more simply using ARRAY_CONSTRAIN.
=ARRAYFORMULA(LEFT(ARRAY_CONSTRAIN(A2:A,COUNTA(A2:A),1),5))
わ、著者の方！
私はそこまでやるならこうした方が分かりやすいと思いました
=ARRAY_CONSTRAIN(ArrayFormula(LEFT(A2:A,5),COUNTA(A2:A),1)— かよ (@kayo_818) May 14, 2020
I am regretful that I ended up spreading a sub-optimal example, but I am comforted by the words ‘All knowledge will go through those who spread it’.
ARRAY_CONSTRAIN https://support.google.com/docs/answer/3267036?hl=ja