google листы находят по строкам и суммируют

#google-sheets

Вопрос:

Это упрощенная версия моих данных.

Я хочу автоматически рассчитать строку «Весь налог». Я имею в виду, что я ищу любую специальную формулу Google sheets, которая может искать те записи, которые равны «НАЛОГУ», и СУММИРОВАТЬ значения в соответствующем месяце.

1. в дополнение к использованию встроенных функций, если это ускоряет разработку, вы можете использовать QUERY() SQL-подобные запросы к базе данных, так как я вижу, что вы довольно много знаете о php/MySQL.

2. 4991 повторение (а теперь 5001 повторение после того, как я поддержал вас) действительно ли вам разрешено публиковать только изображение, а не электронную таблицу или код?

3. @BCLC Я думал, что изображения более постоянны, чем ссылки на электронные таблицы. Но обязательно в следующий раз я загружу ссылку на электронную таблицу.

Ответ №1:

В Google QUERY() функция листа действительно быстрее и масштабируемее. Вы можете использовать подстановочный знак, чтобы игнорировать начальные и конечные пробелы. Попробуйте приведенную ниже формулу.

 =SUM(QUERY($A$3:$D$16,"SELECT B WHERE LOWER(A) LIKE '%tax%'"))

За один раз получите сумму всех столбцов. Согласно моему скриншоту, приведенному ниже, формула помещается в B17 ячейку.

 =QUERY($A$3:$D$16,"SELECT SUM(B), SUM(C), SUM(D) WHERE LOWER(A) LIKE '%tax%' LABEL SUM(B) '', SUM(C) '', SUM(D) ''")

Ответ №2:

Вам следовало бы разместить электронную таблицу вместо изображения, чтобы узнать свой язык. Попробуйте в B17, предполагая, что НАЛОГУ не предшествуют пробелы

 =SUMPRODUCT($A$2:$A$16="TAX",B2:B16)

1. Что делать, если НАЛОГУ на самом деле предшествуют пробелы?

2. Затем замените "TAX" в формуле на " TAX" точное количество пробелов

3. @Salar не уверен, что это возможно, SUMPRODUCT но смотрите мое редактирование для подстановочных SUMIF знаков . Тем не менее, SUMPRODUCT он остается универсальным и мощным инструментом.

4. Я использовал функцию ОБРЕЗКИ для удаления лишних пробелов, и это хорошо сработало.

Ответ №3:

Рассмотрите возможность размещения сумм в верхней части каждого столбца, чтобы вы могли выбирать ячейки, используемые A2:A для остальной части столбца.

В любом случае, используйте =SUMIF($A$1:$A$15, "*tax", B1:B15) для строк, которые заканчиваются на налоге, регистр не учитывается. Селекторы подстановочных знаков предназначены * для строк подстановочных знаков и ? символов подстановочных знаков. Используйте ~ , чтобы сбежать.

1. Я думаю, что я также могу использовать функцию ОБРЕЗКИ для входного диапазона. но я не знаю сравнения производительности между * — символом или функцией ОБРЕЗКИ. есть идеи?

2. Хороший вопрос. ОБРЕЗКА, похоже, требует дополнительного хранилища, так TRIM($A$1:$A$15) как не работает для меня.

3. его интересует, почему SUMIF не работает с TRIM, но SUMPRODUCT работает.

4. Функция @Salar TRIM() замедлит работу вашей формулы для большого набора данных.

Ответ №4:

Вслед за Harun24HR, вот более гибкая формула, в которой вам не нужно указывать каждый столбец

 =QUERY({A3:D16},"SELECT SUM(Col"amp;arrayformula(textjoin("),SUM(Col",,COLUMN(B3:D)))amp;") WHERE LOWER(Col1) LIKE '%tax%' LABEL SUM(Col"amp;arrayformula(textjoin(")'', SUM(Col",,COLUMN(B3:D)))amp;") '' ")