Недавно на работе я помог коллеге решить, казалось бы, сложную проблему с таблицами Google. Решение сработало «отлично», но возникла небольшая проблема; Диапазон не увеличивается автоматически при добавлении нового значения в строку. Таким образом, решение заключалось в том, что моему коллеге приходилось возвращаться к формуле, чтобы увеличивать диапазон всякий раз, когда добавлялось новое значение. Это не казалось таким идеальным решением, поэтому мне нужно было найти способы решить эту проблему. Я знаю, что кто-то сейчас может кричать, говоря: «Почему бы тебе просто не сделать А: А в качестве диапазона». Суть этой проблемы в том, что выполнение A: A будет подсчитывать пустые строки, что испортит наш результат.

Поиски решения привели меня к изучению некоторых действительно хороших и удобных функций, и я хочу поделиться тем, что я узнал.

Я обнаружил два способа решить эту конкретную проблему. В рамках этого руководства мы попытаемся вычислить сумму каждого диапазона, который имеет значение в столбце A.

  1. = sum (A: index (a: a, max (a: a ‹› «»))): эта формула сначала проверяет максимальное значение, значение которого не равно пустой строке. Проще говоря, он проверяет, какая строка имеет значение, формула индекса возвращает содержимое указанной ячейки. Вот как работает формула индекса: = index (cell_reference, [row], [column]). Ссылка на ячейку - это диапазон, в котором есть данные с нужным нам содержимым. Аргумент строки является необязательным, но если мы хотим вернуть конкретное значение из конкретной строки, которую нужно вернуть, мы укажем номер строки. В этом случае мы использовали формулу max, чтобы получить номер строки, в которой было значение, и сделали его вторым аргументом в формуле. Эта формула успешно расширит свой диапазон, чтобы суммировать все в ячейках, в которых есть значение.
  2. Еще одна интересная формула для использования: = sum (max (arrayformula (if (A1: A ‹› «», row (a: a), »»)))). Мы используем оператор if, чтобы проверить, не равно ли a1: a пустой строке, затем вернуть номер строки, иначе вернуть пустую строку. Мы можем вернуть номер строки, потому что используем формулу массива. Формула Row () хорошо работает с диапазоном, только если мы используем формулу массива, иначе она вернет номер строки первого значения в диапазоне. Например, строка (a2: a9) без формулы массива вернет 2. Формула max - это то, почему теперь мы можем вернуть значение.

Что, если бы мы хотели вернуть содержимое последней строки, а не просто номер ячейки? Мы можем немного подправить вторую формулу и добавить формулу косвенного (). Косвенный возвращает содержимое ссылки на ячейку, заданную в виде строки. = max (arrayformula (if (a1: a ‹› »», косвенный («a1: a»), »») Это вернет значение в последней строке. Мы можем заменить max на min и вернуть содержимое первой строка со значением.

Надеюсь, вы нашли эти формулы полезными. Если вам понравилось это читать, пожалуйста, оставьте аплодисменты.

Вы можете связаться со мной в твиттере.

Спасибо за прочтение.