Как преобразовать данные через формулу?

Question

[KirillRez]

Добрый день!
У меня в таблицу Google-sheets "заливаются" данные из MySQL .


Столбцы B и C - это данные из MySQL, в столбцах E и F - у меня прописаны формулы datevalue (B3) (чтобы привести дату в нужный формат) и функции ЕСЛИ и SPLIT (=if ( C3=0 ; 0 ; split ( C3 ; " . " ; ; )) чтобы вычленить число секунд до " . "

Из этих данные (ФИО, дата и количество секунд) на другом листе через формулу sumifs суммируются данные по критериям:
- ФИО
- Дата звонка
- Продолжительность

Можно ли в формулу sumifs сразу указать диапазон и формат нужных данных, чтобы не делать дополнительные столбцы вычислений и оставить только чистую выгрузку sql, т.е. столбцы E и F вообще бы убрать?

Comments

[Alexander Ivanov]

Скорее всего это можно сделать. Пришлите пример Таблицы с валидными данными.

[KirillRez]

Alexander Ivanov, https://docs.google.com/spreadsheets/d/1DRAhjWRdLk... исходные данные на вкладке "statistics_voximplant", в вкладке "Июнь 2022" формула sumifs в диапазоне K9:K29 и R9:R29.
Вот в эту формулу вставить диапазон суммирования и сразу чтобы данные конвертировались (как в столбцах E и F на вкладке "statistics_voximplant",

Answer 1

[Alexander Ivanov]

Эту задачу можно вообще одной формулой решить

=ARRAYFORMULA(IFNA(
  VLOOKUP(
    B9:B29;
    QUERY(
      FILTER({DATEVALUE(statistics_voximplant!B:B)\--IFERROR(REGEXEXTRACT(statistics_voximplant!C:C;"(.*?)\.");0)};statistics_voximplant!A:A=J1);
      "select Col1, sum(Col2)/86400 group by Col1";
    0);
    2;0);0
))

Comments

[KirillRez]

Спасибо за подсказку. Попробовал реализовать по Вашей формуле подсчет количества значений, удовлетворяющих 2м критериям (тот же файл, вкладка "Пример").
Подскажите пжл как в Вашу формулу прописать диапазон значений, а не только одно как показано на скрине?

[Alexander Ivanov]

KirillRez, думаю, можно и проще, чем

=ARRAYFORMULA(IFNA(VLOOKUP(E6:E8&F5:H5;QUERY({C6:C13&B6:B13};"select Col1, count(Col1) group by Col1";0);2;);0))

В следующий раз задавайте новый вопрос, чтобы соответствовать правилам сервиса.

[KirillRez]

Alexander Ivanov, спасибо за ответ. На будущее принял по созданию новой темы.
Подскажите в рамках этого же вопроса, у меня есть только данные диапазона для выбора дат (F5:H5), т.е. как правильно будет выглядеть формула, если данных в диапазоне (F6:H8) нет, только исходные данные?

[Alexander Ivanov]

KirillRez, я там накидал пару идей через отдельный фильтр и через QUERY-pivot

[KirillRez]

Alexander Ivanov, наблюдал за Вашими действиями в файле. Сразу мне тяжело разобраться. Не очень корректно описал кейс скорее всего, можно ли одной формулой прописать результат подсчета в столбец L6:L8 по ФИО сотрудников, а вот даты для критериев подсчета взять из (I5:K5)?
Я потом возьму эту формулу и смогу изменить диапазон дат только