Пожалуйста, помогите решить следующее. Google таблица, 8 вкладок. В каждой вкладке 150 уникальных имен, напротив каждого имени в соседнем ряду стоит количество баллов. Имена на разных вкладках частично повторяются, но не полностью.
Как сделать вкладку со сводными данными - список всех имен без повторений и сумма всех баллов, которые получил человек, справа от имени.
Нашел как сделать в экселе, но все варианты по google таблице из интернета у меня почему-то не дают нужный результат.
Заранее большое спасибо
Добрый день
Пожалуйста, помогите решить следующее. Google таблица, 8 вкладок. В каждой вкладке 150 уникальных имен, напротив каждого имени в соседнем ряду стоит количество баллов. Имена на разных вкладках частично повторяются, но не полностью.
Как сделать вкладку со сводными данными - список всех имен без повторений и сумма всех баллов, которые получил человек, справа от имени.
Нашел как сделать в экселе, но все варианты по google таблице из интернета у меня почему-то не дают нужный результат.
Gustav, подскажите пожалуйста что добавить в формулу, чтобы она суммировала данные с новых листов таблицы? Сейчас при создании нового листа, в формулу приходится прописывать название данного листа.
Gustav, подскажите пожалуйста что добавить в формулу, чтобы она суммировала данные с новых листов таблицы? Сейчас при создании нового листа, в формулу приходится прописывать название данного листа.Ефгений
при создании нового листа, в формулу приходится прописывать название данного листа
Да, и это самый простой и быстрый способ. Трёхмерных ссылок, как в Excel, типа Лист1:Лист8!A1:B150 в таблицах Google, увы, пока нет.
Что можно сделать еще? Видятся такие варианты:
1. Насоздавать много пустых листов ("на вырост"), прописать их всех в формулу и дальше задействовать и переименовывать в процессе необходимости. Само собой, даже пустые, они будут участвовать в вычислениях и, возможно (а то и наверняка), будут понижать быстродействие. Но формулу вручную править не придётся.
2. Написать скрипт, который будет пересоздавать формулу после добавления новых листов. При этом листы в формулу буду добавляться скриптом, а не вручную. Вручную нужно будет только запустить скрипт (из пользовательского пункта меню или непосредственно из редактора скриптов).
3. Написать маленькую пользовательскую функцию типа SHEETNAMES, возвращающую список рабочих листов. Далее, используя эту функцию, создать формулу, формирующую ТЕКСТ рабочей формулы с учетом добавления новых листов. Наконец, в нужный момент вручную копировать сформированный текст формулы в ячейку с рабочей формулой, заменяя старый текст рабочей формулы на новый. Т.е. по сравнению с вариантом 2, который только скриптовый, здесь будет "разделение труда" между скриптом и формулой, но зато весь процесс будет более наглядный и контролируемый.
В общем, если Вам не мило простое ручное добавление нового листа в формулу, то выбирайте из трёх представленных вариантов. Как можно понять из вышесказанного, каждый из этих вариантов потребует некоторых начальных инвестиций трудозатрат. Ну, а какой выигрыш по времени (и по комфорту) в дальнейшем они обеспечат (и обеспечат ли) покажет только само время.
[p.s.]"Бонус": маленькая скриптовая функция, возвращающую список всех рабочих листов файла (начало работ по варианту 3):[/p.s.] [vba]
Код
function SHEETNAMES() { var sheets = SpreadsheetApp.getActive().getSheets(); return sheets.map(function(sh) { return sh.getSheetName() }); }
[/vba] Можно завести вспомогательный рабочий лист "СписокЛистов" и в его ячейку A1 поместить вызов этой маленькой функции: [vba]
Код
=SHEETNAMES()
[/vba] И тогда, начиная с этой ячейки с формулой, вниз по столбцу сформируется список рабочих листов текущего файла.
[p.s.]"Бонус-2": Подгоню тогда и примерную формулу.[/p.s.]И вообще лист "СписокЛистов" можно использовать как некое рабочее место (АРМ) для формирования обновленного текста формулы.
Итак, в соседнем столбце B напишем слово "нет" напротив имен тех листов, которые не должны участвовать в формуле.
Далее в любой ячейке листа "СписокЛистов" правее столбцов A и B (для них мы уже нашли применение), допустим, в ячейке D1 пишем такую формулу, формирующую текст нашей рабочей формулы: [vba]
Код
=LET( shList; FILTER('СписокЛистов'!A:A; 'СписокЛистов'!A:A<>""; 'СписокЛистов'!B:B<>"нет"); refs; MAP(shList; LAMBDA(sh; "'" & sh & "'!A1:B150")); "=QUERY({" & JOIN("; "; refs) & "}; ""select Col1, sum(Col2) group by Col1 label sum(Col2) ''"")" )
[/vba] Теперь всё, что останется сделать после добавления нового листа/листов - скопировать сформированный текст из ячейки D1 и заменить им рабочую формулу.
при создании нового листа, в формулу приходится прописывать название данного листа
Да, и это самый простой и быстрый способ. Трёхмерных ссылок, как в Excel, типа Лист1:Лист8!A1:B150 в таблицах Google, увы, пока нет.
Что можно сделать еще? Видятся такие варианты:
1. Насоздавать много пустых листов ("на вырост"), прописать их всех в формулу и дальше задействовать и переименовывать в процессе необходимости. Само собой, даже пустые, они будут участвовать в вычислениях и, возможно (а то и наверняка), будут понижать быстродействие. Но формулу вручную править не придётся.
2. Написать скрипт, который будет пересоздавать формулу после добавления новых листов. При этом листы в формулу буду добавляться скриптом, а не вручную. Вручную нужно будет только запустить скрипт (из пользовательского пункта меню или непосредственно из редактора скриптов).
3. Написать маленькую пользовательскую функцию типа SHEETNAMES, возвращающую список рабочих листов. Далее, используя эту функцию, создать формулу, формирующую ТЕКСТ рабочей формулы с учетом добавления новых листов. Наконец, в нужный момент вручную копировать сформированный текст формулы в ячейку с рабочей формулой, заменяя старый текст рабочей формулы на новый. Т.е. по сравнению с вариантом 2, который только скриптовый, здесь будет "разделение труда" между скриптом и формулой, но зато весь процесс будет более наглядный и контролируемый.
В общем, если Вам не мило простое ручное добавление нового листа в формулу, то выбирайте из трёх представленных вариантов. Как можно понять из вышесказанного, каждый из этих вариантов потребует некоторых начальных инвестиций трудозатрат. Ну, а какой выигрыш по времени (и по комфорту) в дальнейшем они обеспечат (и обеспечат ли) покажет только само время.
[p.s.]"Бонус": маленькая скриптовая функция, возвращающую список всех рабочих листов файла (начало работ по варианту 3):[/p.s.] [vba]
Код
function SHEETNAMES() { var sheets = SpreadsheetApp.getActive().getSheets(); return sheets.map(function(sh) { return sh.getSheetName() }); }
[/vba] Можно завести вспомогательный рабочий лист "СписокЛистов" и в его ячейку A1 поместить вызов этой маленькой функции: [vba]
Код
=SHEETNAMES()
[/vba] И тогда, начиная с этой ячейки с формулой, вниз по столбцу сформируется список рабочих листов текущего файла.
[p.s.]"Бонус-2": Подгоню тогда и примерную формулу.[/p.s.]И вообще лист "СписокЛистов" можно использовать как некое рабочее место (АРМ) для формирования обновленного текста формулы.
Итак, в соседнем столбце B напишем слово "нет" напротив имен тех листов, которые не должны участвовать в формуле.
Далее в любой ячейке листа "СписокЛистов" правее столбцов A и B (для них мы уже нашли применение), допустим, в ячейке D1 пишем такую формулу, формирующую текст нашей рабочей формулы: [vba]
Код
=LET( shList; FILTER('СписокЛистов'!A:A; 'СписокЛистов'!A:A<>""; 'СписокЛистов'!B:B<>"нет"); refs; MAP(shList; LAMBDA(sh; "'" & sh & "'!A1:B150")); "=QUERY({" & JOIN("; "; refs) & "}; ""select Col1, sum(Col2) group by Col1 label sum(Col2) ''"")" )
[/vba] Теперь всё, что останется сделать после добавления нового листа/листов - скопировать сформированный текст из ячейки D1 и заменить им рабочую формулу.Gustav
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
