Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Суммирование по нескольким условиям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Суммирование по нескольким условиям (Формулы/Formulas)
Суммирование по нескольким условиям
toxasmrus Дата: Среда, 27.10.2021, 15:53 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Здравствуйте, подскажите пожалуйста, имеется таблица с большим количеством данных, которые заполняются через гугл формы ежедневно. Нужно подводить итоги, учитывая введенные данные
Задача №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
Дата добавления - 27.10.2021 в 15:53
aliramora191 Дата: Среда, 27.10.2021, 18:22 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 6 ±
Замечаний: 0% ±

2016
Код
=ARRAYFORMULA( СУММ(ЕСЛИ(('Ответы на форму (1)'!B:B="20.10")*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1<5);'Ответы на форму (1)'!E:E;0)))

в таблице в ячейке L9 на листе Итоги
 
Ответить
Сообщение
Код
=ARRAYFORMULA( СУММ(ЕСЛИ(('Ответы на форму (1)'!B:B="20.10")*(REGEXREPLACE('Ответы на форму (1)'!D:D;"[^0-9]";"")*1<5);'Ответы на форму (1)'!E:E;0)))

в таблице в ячейке L9 на листе Итоги

Автор - aliramora191
Дата добавления - 27.10.2021 в 18:22
toxasmrus Дата: Пятница, 29.10.2021, 15:56 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

в таблице в ячейке L9 на листе Итоги

Спасибо за помощь!
 
Ответить
Сообщение
в таблице в ячейке L9 на листе Итоги

Спасибо за помощь!

Автор - toxasmrus
Дата добавления - 29.10.2021 в 15:56
toxasmrus Дата: Пятница, 29.10.2021, 17:55 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

aliramora191, а не подскажите ещё момент, а как в вашей формуле реализовать диапазон на выборку 5<=X<=11?
 
Ответить
Сообщениеaliramora191, а не подскажите ещё момент, а как в вашей формуле реализовать диапазон на выборку 5<=X<=11?

Автор - toxasmrus
Дата добавления - 29.10.2021 в 17:55
aliramora191 Дата: Пятница, 29.10.2021, 18:12 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 6 ±
Замечаний: 0% ±

2016
Код
=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)))

Автор - aliramora191
Дата добавления - 29.10.2021 в 18:12
Gustav Дата: Пятница, 29.10.2021, 23:17 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1952
Репутация: 784 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Попытался подсократить формулу - очень хотелось остаться с одним 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-й строки, чтобы не заморачиваться с первой строкой заголовков, тексты которой без спецобработки портят общее умножение.


Мой tip box - яд 41001663842605
 
Ответить
СообщениеПопытался подсократить формулу - очень хотелось остаться с одним 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
Дата добавления - 29.10.2021 в 23:17
Gustav Дата: Суббота, 30.10.2021, 01:31 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 1952
Репутация: 784 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется

toxasmrus, мой не очень большой опыт работы с "Ответами на формы" уже давно подсказал мне, что этот лист лучше не тревожить. На нём происходят таинственные и иногда неожиданные вещи. Поэтому пусть всё, что на нём происходит, будет на совести Гугл. А мы пристроимся сбоку, выдернув с этого листа формулами нужные нам данные, которые и обработаем в сторонке (на другом листе)

Я добавил в вашу таблицу лист "ПредОбр" ("Предварительная обработка"), на который вывел нужные для суммирования колонки и добавил колонку превращения номера класса в число. Формул на листе всего две:

в ячейке A2 - "вырезка" четырех последовательных колонок - c B по E (колонка C "Куратор" нам сейчас не нужна, но, с другой стороны, она и не мешает):
[vba]
Код
=FILTER('Ответы на форму (1)'!B2:E;'Ответы на форму (1)'!B2:B<>"")
[/vba]

в ячейке E2 - добавленная (к "вырезке" справа) колонка выделения и превращения номера класса в число (с саморасширяющейся формулой массива):
[vba]
Код
=ArrayFormula(--REGEXREPLACE(C2:INDEX(C:C;MAX(1;ROW(A:A)*(A:A<>"")));"\D";))
[/vba]

После получения такого "view" дальнейшее конструирование суммирующих формул превращается в приятную рутину под чашечку кофе.


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
Я бы сделал это, добавив в конце каждой строки на формуле, учитывающей только номер класса, но при добавлении новых ответов, строка в таблице полностью заменяется

toxasmrus, мой не очень большой опыт работы с "Ответами на формы" уже давно подсказал мне, что этот лист лучше не тревожить. На нём происходят таинственные и иногда неожиданные вещи. Поэтому пусть всё, что на нём происходит, будет на совести Гугл. А мы пристроимся сбоку, выдернув с этого листа формулами нужные нам данные, которые и обработаем в сторонке (на другом листе)

Я добавил в вашу таблицу лист "ПредОбр" ("Предварительная обработка"), на который вывел нужные для суммирования колонки и добавил колонку превращения номера класса в число. Формул на листе всего две:

в ячейке A2 - "вырезка" четырех последовательных колонок - c B по E (колонка C "Куратор" нам сейчас не нужна, но, с другой стороны, она и не мешает):
[vba]
Код
=FILTER('Ответы на форму (1)'!B2:E;'Ответы на форму (1)'!B2:B<>"")
[/vba]

в ячейке E2 - добавленная (к "вырезке" справа) колонка выделения и превращения номера класса в число (с саморасширяющейся формулой массива):
[vba]
Код
=ArrayFormula(--REGEXREPLACE(C2:INDEX(C:C;MAX(1;ROW(A:A)*(A:A<>"")));"\D";))
[/vba]

После получения такого "view" дальнейшее конструирование суммирующих формул превращается в приятную рутину под чашечку кофе.

Автор - Gustav
Дата добавления - 30.10.2021 в 01:31
toxasmrus Дата: Понедельник, 01.11.2021, 11:19 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Спасибо всем большое, все получилось!
 
Ответить
СообщениеСпасибо всем большое, все получилось!

Автор - toxasmrus
Дата добавления - 01.11.2021 в 11:19
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Суммирование по нескольким условиям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования Jobsora jooble
© 2010-2021 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!