Здравствуйте, подскажите пожалуйста, имеется таблица с большим количеством данных, которые заполняются через гугл формы ежедневно. Нужно подводить итоги, учитывая введенные данные Задача №1 подводить итоги на основе введенной даты или отметки по времени, а также учитывая цифру класса, введенного в одной из строк формы. Но тут возникает проблема, что заполняющие должны указывать цифру и букву класса (напр, 9А, 7 "Б") и так далее. Я понял, как вычленять цифру класса из этой строки, я понял как суммировать на основе даты, но никак у меня не получается совместить эти параметры Формула получается вида
Код
=SUMIFS('Ответы на форму (1)'!E:E;'Ответы на форму (1)'!B:B;"20.10";REGEXREPLACE('Ответы на форму (1)'!B:B;"[^0-9]";"");"<5")
Гугл выдает на этот код ошибку массива Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется
Задача №2. Как-то при суммировании учитывать возможный человеческий фактор при отправке 2х и более одинаковых ответов, проверяя совпадения в строках даты, класса и ФИО заполняющего, но опять же если где-то получится лишний пробел или что-то иное, оно не зачтется как лишние данные. Может кто сможет подсказать, как в это случае лучше поступить?
Скопировал гугл таблицу для тех, кто хочет понять идею, но подчистил ПДн Таблица
Здравствуйте, подскажите пожалуйста, имеется таблица с большим количеством данных, которые заполняются через гугл формы ежедневно. Нужно подводить итоги, учитывая введенные данные Задача №1 подводить итоги на основе введенной даты или отметки по времени, а также учитывая цифру класса, введенного в одной из строк формы. Но тут возникает проблема, что заполняющие должны указывать цифру и букву класса (напр, 9А, 7 "Б") и так далее. Я понял, как вычленять цифру класса из этой строки, я понял как суммировать на основе даты, но никак у меня не получается совместить эти параметры Формула получается вида
Код
=SUMIFS('Ответы на форму (1)'!E:E;'Ответы на форму (1)'!B:B;"20.10";REGEXREPLACE('Ответы на форму (1)'!B:B;"[^0-9]";"");"<5")
Гугл выдает на этот код ошибку массива Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется
Задача №2. Как-то при суммировании учитывать возможный человеческий фактор при отправке 2х и более одинаковых ответов, проверяя совпадения в строках даты, класса и ФИО заполняющего, но опять же если где-то получится лишний пробел или что-то иное, оно не зачтется как лишние данные. Может кто сможет подсказать, как в это случае лучше поступить?
Скопировал гугл таблицу для тех, кто хочет понять идею, но подчистил ПДн Таблицаtoxasmrus
=ARRAYFORMULA( СУММ(ЕСЛИ(('Ответы на форму (1)'!B:B="20.10")*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1>=5)*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1<=10);'Ответы на форму (1)'!E:E;0)))
Код
=ARRAYFORMULA( СУММ(ЕСЛИ(('Ответы на форму (1)'!B:B="20.10")*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1>=5)*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1<=10);'Ответы на форму (1)'!E:E;0)))
Попытался подсократить формулу - очень хотелось остаться с одним REGEXREPLACE. Получилось - не сильно короче, но посложнее для восприятия. И почему ни Excel, ни Google не заведут наконец оператор BETWEEN в вычислениях?!
Вариант через QUERY: [vba]
Код
=ArrayFormula(QUERY({'Ответы на форму (1)'!B:E\--REGEXREPLACE('Ответы на форму (1)'!D:D;"\D";)};"select sum(Col4)where Col1='20.10'and Col5>=5and Col5<=10label sum(Col4)''"))
[/vba]Интересно, что ключевые слова запроса (where, and, label) требуют обязательного пробела только после себя.
Вариант через LOOKUP: [vba]
Код
=ArrayFormula(SUM(('Ответы на форму (1)'!B2:B="20.10")*LOOKUP(--REGEXREPLACE('Ответы на форму (1)'!D2:D;"\D";);{0\5\11};{0\1\0})*'Ответы на форму (1)'!E2:E))
[/vba]Здесь диапазоны начинаются со 2-й строки, чтобы не заморачиваться с первой строкой заголовков, тексты которой без спецобработки портят общее умножение.
Попытался подсократить формулу - очень хотелось остаться с одним REGEXREPLACE. Получилось - не сильно короче, но посложнее для восприятия. И почему ни Excel, ни Google не заведут наконец оператор BETWEEN в вычислениях?!
Вариант через QUERY: [vba]
Код
=ArrayFormula(QUERY({'Ответы на форму (1)'!B:E\--REGEXREPLACE('Ответы на форму (1)'!D:D;"\D";)};"select sum(Col4)where Col1='20.10'and Col5>=5and Col5<=10label sum(Col4)''"))
[/vba]Интересно, что ключевые слова запроса (where, and, label) требуют обязательного пробела только после себя.
Вариант через LOOKUP: [vba]
Код
=ArrayFormula(SUM(('Ответы на форму (1)'!B2:B="20.10")*LOOKUP(--REGEXREPLACE('Ответы на форму (1)'!D2:D;"\D";);{0\5\11};{0\1\0})*'Ответы на форму (1)'!E2:E))
[/vba]Здесь диапазоны начинаются со 2-й строки, чтобы не заморачиваться с первой строкой заголовков, тексты которой без спецобработки портят общее умножение.Gustav
Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется
toxasmrus, мой не очень большой опыт работы с "Ответами на формы" уже давно подсказал мне, что этот лист лучше не тревожить. На нём происходят таинственные и иногда неожиданные вещи. Поэтому пусть всё, что на нём происходит, будет на совести Гугл. А мы пристроимся сбоку, выдернув с этого листа формулами нужные нам данные, которые и обработаем в сторонке (на другом листе)
Я добавил в вашу таблицу лист "ПредОбр" ("Предварительная обработка"), на который вывел нужные для суммирования колонки и добавил колонку превращения номера класса в число. Формул на листе всего две:
в ячейке A2 - "вырезка" четырех последовательных колонок - c B по E (колонка C "Куратор" нам сейчас не нужна, но, с другой стороны, она и не мешает): [vba]
Код
=FILTER('Ответы на форму (1)'!B2:E;'Ответы на форму (1)'!B2:B<>"")
[/vba]
в ячейке E2 - добавленная (к "вырезке" справа) колонка выделения и превращения номера класса в число (с саморасширяющейся формулой массива): [vba]
Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется
toxasmrus, мой не очень большой опыт работы с "Ответами на формы" уже давно подсказал мне, что этот лист лучше не тревожить. На нём происходят таинственные и иногда неожиданные вещи. Поэтому пусть всё, что на нём происходит, будет на совести Гугл. А мы пристроимся сбоку, выдернув с этого листа формулами нужные нам данные, которые и обработаем в сторонке (на другом листе)
Я добавил в вашу таблицу лист "ПредОбр" ("Предварительная обработка"), на который вывел нужные для суммирования колонки и добавил колонку превращения номера класса в число. Формул на листе всего две:
в ячейке A2 - "вырезка" четырех последовательных колонок - c B по E (колонка C "Куратор" нам сейчас не нужна, но, с другой стороны, она и не мешает): [vba]
Код
=FILTER('Ответы на форму (1)'!B2:E;'Ответы на форму (1)'!B2:B<>"")
[/vba]
в ячейке E2 - добавленная (к "вырезке" справа) колонка выделения и превращения номера класса в число (с саморасширяющейся формулой массива): [vba]
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
