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

Вход

Регистрация

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

 

= Мир MS Excel/вставка результата формулы из другого документа - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » вставка результата формулы из другого документа (Формулы/Formulas)
вставка результата формулы из другого документа
Dimansh Дата: Суббота, 07.10.2017, 13:43 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
Есть необходимость вставить из другого документа Spreadsheets результат вычисления формулы. А именно такой:
[vba]
Код
=Sumif($Q$10:Q;Q10;$F$10:$F)
[/vba]
Она считает сумму трат для определенного пользователя. Таких листов в другом документе около 20.

И нужно теперь просуммировать все эти листы и вставить в другой документ. как импортировать range я знаю, но это не то - надо выполнить формулу на листах другого документа и вставить ее значение в главный документ

Есть подсказки?
 
Ответить
СообщениеЕсть необходимость вставить из другого документа Spreadsheets результат вычисления формулы. А именно такой:
[vba]
Код
=Sumif($Q$10:Q;Q10;$F$10:$F)
[/vba]
Она считает сумму трат для определенного пользователя. Таких листов в другом документе около 20.

И нужно теперь просуммировать все эти листы и вставить в другой документ. как импортировать range я знаю, но это не то - надо выполнить формулу на листах другого документа и вставить ее значение в главный документ

Есть подсказки?

Автор - Dimansh
Дата добавления - 07.10.2017 в 13:43
Gustav Дата: Четверг, 12.10.2017, 19:03 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Вариант типа "зашёл в один документ, посмотрел (запомнил) результат (или скопировал), зашёл в другой - вставил (или набрал вручную)", как я понимаю, не рассматривается.

Автоматизированных же вариантов возможно два:
* либо с использованием функции IMPORTRANGE,
* либо - скриптом.

В случае IMPORTRANGE всю вычислительную работу можно/нужно выполнить в файле-источнике: на 20 листах написать 20 формул с SUMIF, на 21-м - одну формулу с SUM, суммирующую 20 результатов SUMIF. Затем в принимающем файле написать формулу с IMPORTRANGE со ссылкой на эту одну итоговую формулу с SUM с 21-го листа файла-источника.

В случае скрипта - пишем скрипт в принимающем файле. В скрипте обращаемся к файлу-источнику, получаем в нём нужный лист, нужную ячейку (range), читаем значение и помещаем его в нужную ячейку принимающего файла при помощи оператора типа:
[vba]
Код
rangeПриёмника.setValue( rangeИсточника.getValue() );
[/vba]
Далее данный скрипт можно запускать - либо командой меню, либо - по одному из событий ( открытие файла, изменение данных, истечение периода таймера ).

Так Вы что хотели бы применить?


Мой tip box - яд 41001663842605
 
Ответить
СообщениеВариант типа "зашёл в один документ, посмотрел (запомнил) результат (или скопировал), зашёл в другой - вставил (или набрал вручную)", как я понимаю, не рассматривается.

Автоматизированных же вариантов возможно два:
* либо с использованием функции IMPORTRANGE,
* либо - скриптом.

В случае IMPORTRANGE всю вычислительную работу можно/нужно выполнить в файле-источнике: на 20 листах написать 20 формул с SUMIF, на 21-м - одну формулу с SUM, суммирующую 20 результатов SUMIF. Затем в принимающем файле написать формулу с IMPORTRANGE со ссылкой на эту одну итоговую формулу с SUM с 21-го листа файла-источника.

В случае скрипта - пишем скрипт в принимающем файле. В скрипте обращаемся к файлу-источнику, получаем в нём нужный лист, нужную ячейку (range), читаем значение и помещаем его в нужную ячейку принимающего файла при помощи оператора типа:
[vba]
Код
rangeПриёмника.setValue( rangeИсточника.getValue() );
[/vba]
Далее данный скрипт можно запускать - либо командой меню, либо - по одному из событий ( открытие файла, изменение данных, истечение периода таймера ).

Так Вы что хотели бы применить?

Автор - Gustav
Дата добавления - 12.10.2017 в 19:03
Dimansh Дата: Четверг, 12.10.2017, 20:40 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
постараюсь подробнее объяснить что я хочу получить, тогда Вы посоветуете - что лучше применить.

вот примерный вариант таблицы

То есть существует список людей с какими-то суммами. в столбце Е они суммируются и видно какая общая сумма у конкретного человека. и так каждый месяц новый листик в документе А.

теперь в документе Б создан лист в котором надо отобразить сумму всех месяцев для _конкретного_ человека. То есть нужно чтобы формула/скрипт прошерстила все листы документа А и нашла все общие суммы для, например "Николая" и вывел(а) общую сумму со всех листов.

Отмечу, что в каждом листе документа А имена повторяются и нет какого-то определенного их количества. Другими словами, нельзя с помощью IMPORTRANGE (по моему мнению) создать в исходном листе какую-то формулу и ее результат суммировать, ведь имена идут вразнобой и нужна сумма каждого человека.
 
Ответить
Сообщениепостараюсь подробнее объяснить что я хочу получить, тогда Вы посоветуете - что лучше применить.

вот примерный вариант таблицы

То есть существует список людей с какими-то суммами. в столбце Е они суммируются и видно какая общая сумма у конкретного человека. и так каждый месяц новый листик в документе А.

теперь в документе Б создан лист в котором надо отобразить сумму всех месяцев для _конкретного_ человека. То есть нужно чтобы формула/скрипт прошерстила все листы документа А и нашла все общие суммы для, например "Николая" и вывел(а) общую сумму со всех листов.

Отмечу, что в каждом листе документа А имена повторяются и нет какого-то определенного их количества. Другими словами, нельзя с помощью IMPORTRANGE (по моему мнению) создать в исходном листе какую-то формулу и ее результат суммировать, ведь имена идут вразнобой и нужна сумма каждого человека.

Автор - Dimansh
Дата добавления - 12.10.2017 в 20:40
Gustav Дата: Пятница, 13.10.2017, 11:30 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Dimansh, а создайте еще пару-тройку листов типа "Лист1" с данными и добавьте еще один итоговый лист, как будто бы он находится в файле B, и покажите как на нем как должны быть представлены суммовые данные с остальных листов. И остальные листы - это вообще что? Какой категории объекты? Месяцы года или еще что-то? И эти листы будут добавляться до бесконечности или сразу будет известно какое-то фиксированное их количество (типа 12 на год)?


Мой tip box - яд 41001663842605
 
Ответить
СообщениеDimansh, а создайте еще пару-тройку листов типа "Лист1" с данными и добавьте еще один итоговый лист, как будто бы он находится в файле B, и покажите как на нем как должны быть представлены суммовые данные с остальных листов. И остальные листы - это вообще что? Какой категории объекты? Месяцы года или еще что-то? И эти листы будут добавляться до бесконечности или сразу будет известно какое-то фиксированное их количество (типа 12 на год)?

Автор - Gustav
Дата добавления - 13.10.2017 в 11:30
Dimansh Дата: Пятница, 13.10.2017, 20:18 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
Создал.

Документ, где идут листы по месяцам: ТУТ
Документ, где находится итоговый лист: ТУТ

Остальные листы, это каждый месяц (прошлый, архивный, так сказать). Я не совсем понял вопрос про категорию объектов. Листы будут добавляться до бесконечности. Имена в каждом документе идут без определенного порядка, а по мере прихода денег от указанных людей
 
Ответить
СообщениеСоздал.

Документ, где идут листы по месяцам: ТУТ
Документ, где находится итоговый лист: ТУТ

Остальные листы, это каждый месяц (прошлый, архивный, так сказать). Я не совсем понял вопрос про категорию объектов. Листы будут добавляться до бесконечности. Имена в каждом документе идут без определенного порядка, а по мере прихода денег от указанных людей

Автор - Dimansh
Дата добавления - 13.10.2017 в 20:18
Gustav Дата: Воскресенье, 15.10.2017, 13:39 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Dimansh, а нет желания всё это хозяйство вести на одном листе, добавив еще один столбец, в котором будет нынешнее название листа (типа Январь, Февраль, Март и т.д.) ? Просто, если создавать месячные листы до бесконечности, то если предположить, что имеется некий суммирующий лист, построенный на формулах (а это можно сделать), то каждый следующий месячный лист будет вызывать необходимость изменять формулы на результирующем листе, тупо дописывая в формулы диапазоны с нового месячного листа... Получится какая-то совершенно искусственная проблема, созданная непонятно во имя чего...

Каковы вообще масштабы задачи? Сколько примерно записей будет получаться на одном месячном листе? А если будет единый лист, то в нём всегда же можно включить фильтр по конкретному месяцу и записи отобразятся, как если бы они были на одном месячном листе. Что скажете? Формулы SUMIF на едином листе, считающие внутри конкретного месяца я Вам обеспечу. И даже могу рядом сделать SUMIF по всем месяцам или годам.

И собирать данные для документа Б с единого листа значительно удобнее - хоть скриптом, хоть с помощью importrange.


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Воскресенье, 15.10.2017, 13:42
 
Ответить
СообщениеDimansh, а нет желания всё это хозяйство вести на одном листе, добавив еще один столбец, в котором будет нынешнее название листа (типа Январь, Февраль, Март и т.д.) ? Просто, если создавать месячные листы до бесконечности, то если предположить, что имеется некий суммирующий лист, построенный на формулах (а это можно сделать), то каждый следующий месячный лист будет вызывать необходимость изменять формулы на результирующем листе, тупо дописывая в формулы диапазоны с нового месячного листа... Получится какая-то совершенно искусственная проблема, созданная непонятно во имя чего...

Каковы вообще масштабы задачи? Сколько примерно записей будет получаться на одном месячном листе? А если будет единый лист, то в нём всегда же можно включить фильтр по конкретному месяцу и записи отобразятся, как если бы они были на одном месячном листе. Что скажете? Формулы SUMIF на едином листе, считающие внутри конкретного месяца я Вам обеспечу. И даже могу рядом сделать SUMIF по всем месяцам или годам.

И собирать данные для документа Б с единого листа значительно удобнее - хоть скриптом, хоть с помощью importrange.

Автор - Gustav
Дата добавления - 15.10.2017 в 13:39
Dimansh Дата: Воскресенье, 15.10.2017, 14:39 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
По моему мнению, соединение всех в один лист, приведет к невозможности работы. 99% работы с документов проходит с мобильных устройств. ЛТЕ или 4Ж у нас тут на хуторе нету. Поэтому, превышение листов больше, чем 5 ведет к неимоверно долгой загрузке документа и применению изменений. Одновременно работают до 3 человек с документом онлайн.
А записей на 1 месячном листе порядка 15 000.

Да, я понимаю, что раз в месяц надо будет дописать к 1 формуле новый диапазон и протянуть ее по таблице либо в скрипте дописать. Но и скорость доступа к таблице тоже важна

Если у Вас есть вариант как это все разместить без соединения всех листов на 1 документе - я буду только рад
 
Ответить
СообщениеПо моему мнению, соединение всех в один лист, приведет к невозможности работы. 99% работы с документов проходит с мобильных устройств. ЛТЕ или 4Ж у нас тут на хуторе нету. Поэтому, превышение листов больше, чем 5 ведет к неимоверно долгой загрузке документа и применению изменений. Одновременно работают до 3 человек с документом онлайн.
А записей на 1 месячном листе порядка 15 000.

Да, я понимаю, что раз в месяц надо будет дописать к 1 формуле новый диапазон и протянуть ее по таблице либо в скрипте дописать. Но и скорость доступа к таблице тоже важна

Если у Вас есть вариант как это все разместить без соединения всех листов на 1 документе - я буду только рад

Автор - Dimansh
Дата добавления - 15.10.2017 в 14:39
Gustav Дата: Воскресенье, 15.10.2017, 22:40 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
записей на 1 месячном листе порядка 15 000
Ok! Учитывая, что в одной книге (файле) на всех листах может быть не более двух миллионов ячеек, с удалением из месячных листов всех пустых столбцов и оставляя на каждом из них всего 4 заполненных столбца, как сейчас, получаем 15000 * 4 * 12 = 720 тысяч ячеек в год. Т.е. одного файла хватит на около трех лет эксплуатации. Это устраивает? Имена листов будут содержать год после месяца, типа "Январь 2017"? Или один документ А ограничится одним годом?


Мой tip box - яд 41001663842605
 
Ответить
Сообщение
записей на 1 месячном листе порядка 15 000
Ok! Учитывая, что в одной книге (файле) на всех листах может быть не более двух миллионов ячеек, с удалением из месячных листов всех пустых столбцов и оставляя на каждом из них всего 4 заполненных столбца, как сейчас, получаем 15000 * 4 * 12 = 720 тысяч ячеек в год. Т.е. одного файла хватит на около трех лет эксплуатации. Это устраивает? Имена листов будут содержать год после месяца, типа "Январь 2017"? Или один документ А ограничится одним годом?

Автор - Gustav
Дата добавления - 15.10.2017 в 22:40
Dimansh Дата: Понедельник, 16.10.2017, 02:53 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
всего 4 заполненных столбца

Столбцов заполнено 20. но остальные особо не играют роли в нашей дискуссии.
Это устраивает?

Боюсь, что я не понимаю. Я говорил выше, что наличие многих листов в одном документе отягощает работу, и для этого был вынесен рабочий лист на отдельный документ. Наверное не будем продолжать. Слишком сложно для непонимающего. Будем суммировать по старинке фильтром и калькулятором.
 
Ответить
Сообщение
всего 4 заполненных столбца

Столбцов заполнено 20. но остальные особо не играют роли в нашей дискуссии.
Это устраивает?

Боюсь, что я не понимаю. Я говорил выше, что наличие многих листов в одном документе отягощает работу, и для этого был вынесен рабочий лист на отдельный документ. Наверное не будем продолжать. Слишком сложно для непонимающего. Будем суммировать по старинке фильтром и калькулятором.

Автор - Dimansh
Дата добавления - 16.10.2017 в 02:53
Dimansh Дата: Понедельник, 16.10.2017, 02:57 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
Но все равно спасибо за помощь. Вы безумно сильно помогли в других темах и это очень упростило жизнь.
 
Ответить
СообщениеНо все равно спасибо за помощь. Вы безумно сильно помогли в других темах и это очень упростило жизнь.

Автор - Dimansh
Дата добавления - 16.10.2017 в 02:57
Gustav Дата: Понедельник, 16.10.2017, 21:30 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Наверное не будем продолжать. Слишком сложно для непонимающего. Будем суммировать по старинке фильтром и калькулятором.

Dimansh, ну как хотите, конечно. Но я таки покажу, как я примерно собирался Вам помочь. Может, на что-то натолкнет.

Итак, я создал копию "Документа А", добавив в него лист "Свод по месяцам". На этом листе есть блоки "Январь", "Февраль", "Март" с формулами в ячейках A2, C2, E2 соответственно. Формулы - с функцией QUERY и запросом, похожим на SQL:
Код
=QUERY('Январь'!B2:C;"select B, sum(C) where B <> '' group by B")

Такая формула вводится в одну ячейку, а результаты отображает в диапазоне из нескольких ячеек, расширяясь вниз и вправо на столько, на сколько формуле это потребуется. Также я заготовил блоки для трёх следующих месяцев (Апрель, Май, Июнь), но формулы в ячейки G2, I2 и K2 пока не введены, потому что еще не заведены листы под эти месяцы. Зато блоки под них уже учтены в формуле ячейки M2 блока "По всем месяцам":
Код
=QUERY({A3:B;C3:D;E3:F;G3:H;I3:J;K3:L};"select Col1, sum(Col2) where Col1 <> '' group by Col1")

От души желаю Вам во всём этом разобраться и подняться на новый уровень владения таблицами Гугл. Функция QUERY - очень полезна и даже пока не имеет аналога в Excel. В помощь - ещё одна тема с моим участием, в которой у меня получился довольно интересный (и не думаю хвастаться!) монолог-лекция: ТС там, задав первоначальный вопрос, так и пропал, а я в свою очередь "растекся мыслью по древу".


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Понедельник, 16.10.2017, 21:33
 
Ответить
Сообщение
Наверное не будем продолжать. Слишком сложно для непонимающего. Будем суммировать по старинке фильтром и калькулятором.

Dimansh, ну как хотите, конечно. Но я таки покажу, как я примерно собирался Вам помочь. Может, на что-то натолкнет.

Итак, я создал копию "Документа А", добавив в него лист "Свод по месяцам". На этом листе есть блоки "Январь", "Февраль", "Март" с формулами в ячейках A2, C2, E2 соответственно. Формулы - с функцией QUERY и запросом, похожим на SQL:
Код
=QUERY('Январь'!B2:C;"select B, sum(C) where B <> '' group by B")

Такая формула вводится в одну ячейку, а результаты отображает в диапазоне из нескольких ячеек, расширяясь вниз и вправо на столько, на сколько формуле это потребуется. Также я заготовил блоки для трёх следующих месяцев (Апрель, Май, Июнь), но формулы в ячейки G2, I2 и K2 пока не введены, потому что еще не заведены листы под эти месяцы. Зато блоки под них уже учтены в формуле ячейки M2 блока "По всем месяцам":
Код
=QUERY({A3:B;C3:D;E3:F;G3:H;I3:J;K3:L};"select Col1, sum(Col2) where Col1 <> '' group by Col1")

От души желаю Вам во всём этом разобраться и подняться на новый уровень владения таблицами Гугл. Функция QUERY - очень полезна и даже пока не имеет аналога в Excel. В помощь - ещё одна тема с моим участием, в которой у меня получился довольно интересный (и не думаю хвастаться!) монолог-лекция: ТС там, задав первоначальный вопрос, так и пропал, а я в свою очередь "растекся мыслью по древу".

Автор - Gustav
Дата добавления - 16.10.2017 в 21:30
Dimansh Дата: Понедельник, 16.10.2017, 21:50 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
Формулы - с функцией QUERY и запросом, похожим на SQL

Да, крутая штука. я попробую как-то это прикрутить к тому, что есть. может быть придется где-то дополнять раз в месяц или что то еще, но это намного лучше, чем ничего.
ещё одна тема с моим участием

я ее видел, но там уж очень мудрено. когда буду творить свое, то загляну туда за подсказками.

От души желаю Вам во всём этом разобраться и подняться на новый уровень владения таблицами Гугл

Спасибо, но у меня все еще остался один вопрос. и я не сильно верю в то, что ответа/возможности нет:

Неужели нету "формулы", которая бы работала по схеме: =SUM <ячейка в локальном документе> + <ячейка в документе по ссылке на другой гуглодок типа "https://docs.google.com/spreadsheets/d/1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y/edit#gid=1542409022" > ??? если перефразировать, то сумма локальной ячейки с ячейкой из вообще другого документа.

?
 
Ответить
Сообщение
Формулы - с функцией QUERY и запросом, похожим на SQL

Да, крутая штука. я попробую как-то это прикрутить к тому, что есть. может быть придется где-то дополнять раз в месяц или что то еще, но это намного лучше, чем ничего.
ещё одна тема с моим участием

я ее видел, но там уж очень мудрено. когда буду творить свое, то загляну туда за подсказками.

От души желаю Вам во всём этом разобраться и подняться на новый уровень владения таблицами Гугл

Спасибо, но у меня все еще остался один вопрос. и я не сильно верю в то, что ответа/возможности нет:

Неужели нету "формулы", которая бы работала по схеме: =SUM <ячейка в локальном документе> + <ячейка в документе по ссылке на другой гуглодок типа "https://docs.google.com/spreadsheets/d/1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y/edit#gid=1542409022" > ??? если перефразировать, то сумма локальной ячейки с ячейкой из вообще другого документа.

?

Автор - Dimansh
Дата добавления - 16.10.2017 в 21:50
Gustav Дата: Вторник, 17.10.2017, 00:32 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Неужели нету "формулы", которая бы работала по схеме: =SUM <ячейка в локальном документе> + <ячейка в документе по ссылке на другой гуглодок типа "https://docs.google.com/spreadsheets/d/1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y/edit#gid=1542409022" > ??? если перефразировать, то сумма локальной ячейки с ячейкой из вообще другого документа

Так Вы же вроде в стартовом посте провозглашали:
как импортировать range я знаю, но это не то

Это именно то. Делается именно через функцию IMPORTRANGE. Я добавил примеры в своей копии на листе "Свод по месяцам" - там сумма Сережи суммируется с некой ячейкой D3 из другого моего файла-примера:
Код
=СУММ(N7; IMPORTRANGE("https://docs.google.com/spreadsheets/d/10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!D3"))

или только с одним Id файла, без начального "http...":
Код
=N7 + IMPORTRANGE("10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!D3")


Другое дело, что по одной ячейке дёргать из другого документа - накладно. Раньше вообще существовало ограничение на количество использованных функций IMPORTRANGE - не более 50 в файле. Но теперь это ограничение, вроде, снято.

Имеет смысл создать в принимающем документе отдельный лист и получать на него ОДНОЙ ФОРМУЛОЙ сразу многоячеечный диапазон с нужными данными из документа-источника. И далее в принимающем документе обрабатывать полученный материал уже более простыми локальными формулами. А "ОДНУ ФОРМУЛУ" можно ввести на чистый лист в ячейку A1 и получить прямоугольный массив данных на этом листе:
Код
=IMPORTRANGE("10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!A1:E34")

Добавил в свой пример лист "Импорт Рабочая" с этой формулой.


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Вторник, 17.10.2017, 00:50
 
Ответить
Сообщение
Неужели нету "формулы", которая бы работала по схеме: =SUM <ячейка в локальном документе> + <ячейка в документе по ссылке на другой гуглодок типа "https://docs.google.com/spreadsheets/d/1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y/edit#gid=1542409022" > ??? если перефразировать, то сумма локальной ячейки с ячейкой из вообще другого документа

Так Вы же вроде в стартовом посте провозглашали:
как импортировать range я знаю, но это не то

Это именно то. Делается именно через функцию IMPORTRANGE. Я добавил примеры в своей копии на листе "Свод по месяцам" - там сумма Сережи суммируется с некой ячейкой D3 из другого моего файла-примера:
Код
=СУММ(N7; IMPORTRANGE("https://docs.google.com/spreadsheets/d/10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!D3"))

или только с одним Id файла, без начального "http...":
Код
=N7 + IMPORTRANGE("10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!D3")


Другое дело, что по одной ячейке дёргать из другого документа - накладно. Раньше вообще существовало ограничение на количество использованных функций IMPORTRANGE - не более 50 в файле. Но теперь это ограничение, вроде, снято.

Имеет смысл создать в принимающем документе отдельный лист и получать на него ОДНОЙ ФОРМУЛОЙ сразу многоячеечный диапазон с нужными данными из документа-источника. И далее в принимающем документе обрабатывать полученный материал уже более простыми локальными формулами. А "ОДНУ ФОРМУЛУ" можно ввести на чистый лист в ячейку A1 и получить прямоугольный массив данных на этом листе:
Код
=IMPORTRANGE("10L6NC7alcyr9mDm91j6u6IKyBVonSHZZ3sSkeEGbxqA";"Рабочая!A1:E34")

Добавил в свой пример лист "Импорт Рабочая" с этой формулой.

Автор - Gustav
Дата добавления - 17.10.2017 в 00:32
Dimansh Дата: Вторник, 17.10.2017, 01:03 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 38
Репутация: 0 ±
Замечаний: 0% ±

Google Docs
Это именно то

Я глуп и не понимал этого еще 5 дней назад =)

теперь вроде все должно получится. я отпишусь чуть позже, когда сделаю/не сделаю это. спасибо за помощь Вам.
 
Ответить
Сообщение
Это именно то

Я глуп и не понимал этого еще 5 дней назад =)

теперь вроде все должно получится. я отпишусь чуть позже, когда сделаю/не сделаю это. спасибо за помощь Вам.

Автор - Dimansh
Дата добавления - 17.10.2017 в 01:03
Gustav Дата: Суббота, 21.10.2017, 13:28 | Сообщение № 15
Группа: Друзья
Ранг: Старожил
Сообщений: 1475
Репутация: 559 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Dimansh, у меня хорошая новость - я написал суммирующий скрипт для документа Б!

Скрипт берет все исходные данные (имя, сумма) со всех месячных листов документа А, пропуская ненужные ("немесячные") листы. Список ненужных листов, если они вообще есть, задается в массиве исключений. Т.е. для очередного добавленного месячного листа в скрипте ничего перенастраивать не придётся.

Скрипт группирует все данные по именам людей и складывает значения элементарных сумм (не SUMIF). Окончательный список выводится в отсортированном алфавитном порядке по именам.

[vba]
Код
function sumAllMonthsGroupByAllPeople() {
  
  var idThat = "1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y";  // ID файла с месячными листами
  
  var ssThat = SpreadsheetApp.openById( idThat ); // ТОТ файл (другой) - там месячные листы
  var ss     = SpreadsheetApp.getActive(); // этот файл - тут итоговый лист
  
  // имена листов, исключенных из обработки (не месячные, "ненужные")
  // (нестрашно, если в списке будет имя несуществующего листа)
  var sheetsExcluded = ["Сумма","Лист1","Лист2", "Свод по месяцам", "Импорт Рабочая", "Ссылки"];
  
  // читаем данные только месячных листов, пропуская ненужные  
  var allData = [];  
  for (var sh in ssThat.getSheets()) {
    var sheet = ssThat.getSheets()[sh];
    if (sheetsExcluded.indexOf(sheet.getName()) == -1) {
      var range  = sheet.getRange("B2:C"+sheet.getLastRow());
      var values = range.getValues();
      for (var row in values) {
        // очередную непустую пару значений "имя-сумма" добавляем как единый объект в общий массив
        if (values[row][0]) allData.push( {Id: values[row][0], qty: values[row][1]} );
      }            
    }
  }
  
  // группируем по именам людей с суммированием
  // подход к группировке заимствован отсюда:
  // https_://stackoverflow.com/questions/29364262/how-to-group-by-and-sum-array-of-object-in-jquery
  var groupBy = [];
  allData.reduce(function (res, value) {
    if (!res[value.Id]) {
      res[value.Id] = {Id: value.Id,
                       qty: 0};
      // дьявольский код с reduce! - почему это один раз добавляется в массив groupBy мне понятно,
      // но за счёт чего оно потом в массиве groupBy еще и обновляется без явного указания
      // на массив - не понимаю совершенно :((( не иначе как с какими-нибудь "замыканиями" связано
      groupBy.push(res[value.Id]);
    }
    res[value.Id].qty += value.qty;
    return res;
  }, {});
  
  // сортируем по возрастанию (по имени, по алфавиту)
  groupBy.sort(function(a,b) {return (a.Id > b.Id) - (b.Id > a.Id)});
  
  // формируем массив для вывода на лист Сумма текущего файла
  var outputValues = [];
  for (var i=0; i<groupBy.length; i++) outputValues[i] = [groupBy[i].Id, groupBy[i].qty];    
  
  // выводим на лист текущего файла, предварительно стерев суммы с прошлого раза
  sheet = ss.getSheetByName("Сумма");
  sheet.getRange("A2:B").clearContent();
  range = sheet.getRange("A2:B"+(outputValues.length+1));
  range.setValues( outputValues );
}
[/vba]


Мой tip box - яд 41001663842605

Сообщение отредактировал Gustav - Суббота, 21.10.2017, 13:48
 
Ответить
СообщениеDimansh, у меня хорошая новость - я написал суммирующий скрипт для документа Б!

Скрипт берет все исходные данные (имя, сумма) со всех месячных листов документа А, пропуская ненужные ("немесячные") листы. Список ненужных листов, если они вообще есть, задается в массиве исключений. Т.е. для очередного добавленного месячного листа в скрипте ничего перенастраивать не придётся.

Скрипт группирует все данные по именам людей и складывает значения элементарных сумм (не SUMIF). Окончательный список выводится в отсортированном алфавитном порядке по именам.

[vba]
Код
function sumAllMonthsGroupByAllPeople() {
  
  var idThat = "1HH0nRzlZ-y4qMc2yNbKgF0Z81QM-6rLxEunTlqQVq-Y";  // ID файла с месячными листами
  
  var ssThat = SpreadsheetApp.openById( idThat ); // ТОТ файл (другой) - там месячные листы
  var ss     = SpreadsheetApp.getActive(); // этот файл - тут итоговый лист
  
  // имена листов, исключенных из обработки (не месячные, "ненужные")
  // (нестрашно, если в списке будет имя несуществующего листа)
  var sheetsExcluded = ["Сумма","Лист1","Лист2", "Свод по месяцам", "Импорт Рабочая", "Ссылки"];
  
  // читаем данные только месячных листов, пропуская ненужные  
  var allData = [];  
  for (var sh in ssThat.getSheets()) {
    var sheet = ssThat.getSheets()[sh];
    if (sheetsExcluded.indexOf(sheet.getName()) == -1) {
      var range  = sheet.getRange("B2:C"+sheet.getLastRow());
      var values = range.getValues();
      for (var row in values) {
        // очередную непустую пару значений "имя-сумма" добавляем как единый объект в общий массив
        if (values[row][0]) allData.push( {Id: values[row][0], qty: values[row][1]} );
      }            
    }
  }
  
  // группируем по именам людей с суммированием
  // подход к группировке заимствован отсюда:
  // https_://stackoverflow.com/questions/29364262/how-to-group-by-and-sum-array-of-object-in-jquery
  var groupBy = [];
  allData.reduce(function (res, value) {
    if (!res[value.Id]) {
      res[value.Id] = {Id: value.Id,
                       qty: 0};
      // дьявольский код с reduce! - почему это один раз добавляется в массив groupBy мне понятно,
      // но за счёт чего оно потом в массиве groupBy еще и обновляется без явного указания
      // на массив - не понимаю совершенно :((( не иначе как с какими-нибудь "замыканиями" связано
      groupBy.push(res[value.Id]);
    }
    res[value.Id].qty += value.qty;
    return res;
  }, {});
  
  // сортируем по возрастанию (по имени, по алфавиту)
  groupBy.sort(function(a,b) {return (a.Id > b.Id) - (b.Id > a.Id)});
  
  // формируем массив для вывода на лист Сумма текущего файла
  var outputValues = [];
  for (var i=0; i<groupBy.length; i++) outputValues[i] = [groupBy[i].Id, groupBy[i].qty];    
  
  // выводим на лист текущего файла, предварительно стерев суммы с прошлого раза
  sheet = ss.getSheetByName("Сумма");
  sheet.getRange("A2:B").clearContent();
  range = sheet.getRange("A2:B"+(outputValues.length+1));
  range.setValues( outputValues );
}
[/vba]

Автор - Gustav
Дата добавления - 21.10.2017 в 13:28
Manyasha Дата: Вторник, 24.10.2017, 20:31 | Сообщение № 16
Группа: Модераторы
Ранг: Старожил
Сообщений: 2031
Репутация: 844 ±
Замечаний: 0% ±

Excel 2010, 2016
    // дьявольский код с reduce! - почему это один раз добавляется в массив groupBy мне понятно,
    // но за счёт чего оно потом в массиве groupBy еще и обновляется без явного указания
    // на массив - не понимаю совершенно :((( не иначе как с какими-нибудь "замыканиями" связано

Попробую вклиниться с объяснениями))
reduce и замыкания здесь ни при чем. Просто в массив groupBy передается ссылка на объект res, а не его значение (аналог Set в VBA). Подробнее тут.

[vba]
Код
var x=[], y={id: "11"};
x.push(y);
console.log(x[0].id);//получим 11
y.id=12;
console.log(x[0].id);//получим 12

//Если нужна не ссылка а значение, то так можно
var x=[], y={id: "11"};
x.push({id: y.id});
console.log(x[0].id);//получим 11
y.id=12;
console.log(x[0].id);//получим 11
[/vba]


marinamorozova_box@mail.ru
ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщение
    // дьявольский код с reduce! - почему это один раз добавляется в массив groupBy мне понятно,
    // но за счёт чего оно потом в массиве groupBy еще и обновляется без явного указания
    // на массив - не понимаю совершенно :((( не иначе как с какими-нибудь "замыканиями" связано

Попробую вклиниться с объяснениями))
reduce и замыкания здесь ни при чем. Просто в массив groupBy передается ссылка на объект res, а не его значение (аналог Set в VBA). Подробнее тут.

[vba]
Код
var x=[], y={id: "11"};
x.push(y);
console.log(x[0].id);//получим 11
y.id=12;
console.log(x[0].id);//получим 12

//Если нужна не ссылка а значение, то так можно
var x=[], y={id: "11"};
x.push({id: y.id});
console.log(x[0].id);//получим 11
y.id=12;
console.log(x[0].id);//получим 11
[/vba]

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

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