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

Вход

Регистрация

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

 

= Мир MS Excel/Настройка консолидации (объединение данных) Google Таблиц - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, китин  
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Настройка консолидации (объединение данных) Google Таблиц (Формулы/Formulas)
Настройка консолидации (объединение данных) Google Таблиц
rownong27 Дата: Среда, 18.05.2016, 15:16 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Была задача седлать консолидацию Google Таблиц (объединение данных с нескольких таблиц в одну).

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

Вот описание.

Есть Таблица 1 (руководителя) и Таблица 2 (оператора), Таблица 3 (оператора).

Таблица 1: https://goo.gl/NmzPrX
Таблица 2: https://goo.gl/3nCejF
Таблица 3: https://goo.gl/rSgQbh

Данные из Таблицы 2 и Таблицы 3 поступают в Таблицу 1.
Т.е. настроено так, что если в Таблице 2 и Талице 3, будут создаваться/заполняться дополнительные строки, то в Таблице 1 эти строки тоже будут создаваться/заполняться.
Причем реализовано, таким образом, что если данные Таблицы 2 и Талицы 3, в Таблице 1 стоят вплотную друг к другу, они не будут наезжать друг на друга, при добавлении новых данных (строк), в таблицах Таблица 2 и Талица 3. Скриншот: https://goo.gl/os03HW

Сейчас, в данных файлах, консолидация работает исправно.

Но, когда я создал дополнительный файл (аналог Таблицы 1), и настроил в нем консолидацию с текущих Таблицы 2 и Таблицы 3, столкнулся с ошибкой (хотя, на мой взгляд, настройки произвел идентично).

Видео-демонстрация проблемы: https://youtu.be/zKZ5sk-oZfg

Дополнительный файл (аналог Таблицы 1) с которым работаю: https://goo.gl/IklMQK

В чем я допускаю ошибку, и как настроить консолидацию в «своих» файлах, по аналогии, чтобы она потом работала? Напишите, пожалуйста.
 
Ответить
СообщениеБыла задача седлать консолидацию Google Таблиц (объединение данных с нескольких таблиц в одну).

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

Вот описание.

Есть Таблица 1 (руководителя) и Таблица 2 (оператора), Таблица 3 (оператора).

Таблица 1: https://goo.gl/NmzPrX
Таблица 2: https://goo.gl/3nCejF
Таблица 3: https://goo.gl/rSgQbh

Данные из Таблицы 2 и Таблицы 3 поступают в Таблицу 1.
Т.е. настроено так, что если в Таблице 2 и Талице 3, будут создаваться/заполняться дополнительные строки, то в Таблице 1 эти строки тоже будут создаваться/заполняться.
Причем реализовано, таким образом, что если данные Таблицы 2 и Талицы 3, в Таблице 1 стоят вплотную друг к другу, они не будут наезжать друг на друга, при добавлении новых данных (строк), в таблицах Таблица 2 и Талица 3. Скриншот: https://goo.gl/os03HW

Сейчас, в данных файлах, консолидация работает исправно.

Но, когда я создал дополнительный файл (аналог Таблицы 1), и настроил в нем консолидацию с текущих Таблицы 2 и Таблицы 3, столкнулся с ошибкой (хотя, на мой взгляд, настройки произвел идентично).

Видео-демонстрация проблемы: https://youtu.be/zKZ5sk-oZfg

Дополнительный файл (аналог Таблицы 1) с которым работаю: https://goo.gl/IklMQK

В чем я допускаю ошибку, и как настроить консолидацию в «своих» файлах, по аналогии, чтобы она потом работала? Напишите, пожалуйста.

Автор - rownong27
Дата добавления - 18.05.2016 в 15:16
Gustav Дата: Среда, 18.05.2016, 17:34 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Дополнительный файл (аналог Таблицы 1) с которым работаю: https://goo.gl/IklMQK

По этой ссылке - таблица без формулы (типа как 2 и 3)

Скопировал сюда свой лист "Тест" с формулой, тянущей данные аж из 3-х табличек, страшенные коды таблиц прописал в отдельные ячейки сбоку, поэтому формула стала очень благообразной и понятной (тэг не использую сознательно):

={
FILTER(IMPORTRANGE(J4;"Клиенты!B2:G");IMPORTRANGE(J4;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J3;"Клиенты!B2:G");IMPORTRANGE(J3;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J2;"Клиенты!B2:G");IMPORTRANGE(J2;"Клиенты!B2:B")<>"")
}


В чем я допускаю ошибку, и как настроить консолидацию в «своих» файлах, по аналогии, чтобы она потом работала?

Судя по сообщению, которое там мельком возникает на видео, как-то неправильно скопирован "страшенный код" таблицы. Я поигрался в ячейках J2:J4 на своем листе "Тест", удаляя символ-другой - получил такое же сообщение.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 18.05.2016, 17:35
 
Ответить
Сообщение
Дополнительный файл (аналог Таблицы 1) с которым работаю: https://goo.gl/IklMQK

По этой ссылке - таблица без формулы (типа как 2 и 3)

Скопировал сюда свой лист "Тест" с формулой, тянущей данные аж из 3-х табличек, страшенные коды таблиц прописал в отдельные ячейки сбоку, поэтому формула стала очень благообразной и понятной (тэг не использую сознательно):

={
FILTER(IMPORTRANGE(J4;"Клиенты!B2:G");IMPORTRANGE(J4;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J3;"Клиенты!B2:G");IMPORTRANGE(J3;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J2;"Клиенты!B2:G");IMPORTRANGE(J2;"Клиенты!B2:B")<>"")
}


В чем я допускаю ошибку, и как настроить консолидацию в «своих» файлах, по аналогии, чтобы она потом работала?

Судя по сообщению, которое там мельком возникает на видео, как-то неправильно скопирован "страшенный код" таблицы. Я поигрался в ячейках J2:J4 на своем листе "Тест", удаляя символ-другой - получил такое же сообщение.

Автор - Gustav
Дата добавления - 18.05.2016 в 17:34
rownong27 Дата: Среда, 18.05.2016, 18:58 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Gustav, спасибо за оперативный ответ.

Проверял сейчас.

Взял свои реальные файлы.
Файл руководителя: https://goo.gl/X1Lzsm
Файл оператора 1: https://goo.gl/TUCzNl
Файл оператора 2: https://goo.gl/FcvPdr

В формуле поменял:
- ссылку на реальные файлы операторов
- диапазоны в этих файлах
и формулу вставил в файле руководителя (скриншот: https://goo.gl/Owd1Y4 ),

Почему-то снова не работает.
Можете посмотреть и написать почему?


P.S. Хоть в файлах операторов данные заполнены начиная с A1, в файле руководителя, я специально поставил заполнение со столба B1 (мне так удобнее).

P.S. В формуле оставьте ссылку на файлы (и диапазоны в них) в исходном виде, так как дальше буду работать с таким форматом формулы.


Сообщение отредактировал rownong27 - Среда, 18.05.2016, 19:00
 
Ответить
СообщениеGustav, спасибо за оперативный ответ.

Проверял сейчас.

Взял свои реальные файлы.
Файл руководителя: https://goo.gl/X1Lzsm
Файл оператора 1: https://goo.gl/TUCzNl
Файл оператора 2: https://goo.gl/FcvPdr

В формуле поменял:
- ссылку на реальные файлы операторов
- диапазоны в этих файлах
и формулу вставил в файле руководителя (скриншот: https://goo.gl/Owd1Y4 ),

Почему-то снова не работает.
Можете посмотреть и написать почему?


P.S. Хоть в файлах операторов данные заполнены начиная с A1, в файле руководителя, я специально поставил заполнение со столба B1 (мне так удобнее).

P.S. В формуле оставьте ссылку на файлы (и диапазоны в них) в исходном виде, так как дальше буду работать с таким форматом формулы.

Автор - rownong27
Дата добавления - 18.05.2016 в 18:58
Gustav Дата: Среда, 18.05.2016, 19:30 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Не знаю. У меня формулы работают и в IE, и в Хроме. На скриншоте визуально увидел пробел в конце первого "страшенного адреса", но, вроде, и в формуле так же, и работает. Ну, попробуйте его убрать... А так больше идей нет, так как нет проблемы (у меня).


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеНе знаю. У меня формулы работают и в IE, и в Хроме. На скриншоте визуально увидел пробел в конце первого "страшенного адреса", но, вроде, и в формуле так же, и работает. Ну, попробуйте его убрать... А так больше идей нет, так как нет проблемы (у меня).

Автор - Gustav
Дата добавления - 18.05.2016 в 19:30
rownong27 Дата: Среда, 18.05.2016, 19:46 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Не знаю. У меня формулы работают и в IE, и в Хроме. На скриншоте визуально увидел пробел в конце первого "страшенного адреса", но, вроде, и в формуле так же, и работает. Ну, попробуйте его убрать... А так больше идей нет, так как нет проблемы (у меня).

что то у меня вся эта конструкция через раз работает (то заработает, то через некоторое время опять ошибку пишет),
постоянно жалуется на фильтр, скриншот https://goo.gl/7FaVbv

Можете еще подсказать ответы на вопросы, которые сейчас для меня загадкой остаются:
1. Для чего продублирована формула, если она и в одинарном исполнении работает?
Скриншот: https://goo.gl/6HIEgt
2. Зачем нужна эта функция FILTER?
3. Вопрос на скриншоте: https://goo.gl/5wqLjh
 
Ответить
Сообщение
Не знаю. У меня формулы работают и в IE, и в Хроме. На скриншоте визуально увидел пробел в конце первого "страшенного адреса", но, вроде, и в формуле так же, и работает. Ну, попробуйте его убрать... А так больше идей нет, так как нет проблемы (у меня).

что то у меня вся эта конструкция через раз работает (то заработает, то через некоторое время опять ошибку пишет),
постоянно жалуется на фильтр, скриншот https://goo.gl/7FaVbv

Можете еще подсказать ответы на вопросы, которые сейчас для меня загадкой остаются:
1. Для чего продублирована формула, если она и в одинарном исполнении работает?
Скриншот: https://goo.gl/6HIEgt
2. Зачем нужна эта функция FILTER?
3. Вопрос на скриншоте: https://goo.gl/5wqLjh

Автор - rownong27
Дата добавления - 18.05.2016 в 19:46
rownong27 Дата: Среда, 18.05.2016, 19:47 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

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

Автор - rownong27
Дата добавления - 18.05.2016 в 19:47
Gustav Дата: Среда, 18.05.2016, 20:47 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Можете еще подсказать ответы на вопросы, которые сейчас для меня загадкой остаются

Показываю на своей прозрачной формуле (тэги не использую сознательно):

={
FILTER(IMPORTRANGE(J4;"Клиенты!B2:G");IMPORTRANGE(J4;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J3;"Клиенты!B2:G");IMPORTRANGE(J3;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J2;"Клиенты!B2:G");IMPORTRANGE(J2;"Клиенты!B2:B")<>"")
}


1. Фигурные скобки задают консолидацию, т.е. всё что внутри них объединяется в единую таблицу, подтягиваясь из разных источников консолидации (в данном случае - трех).

2. Каждый FILTER задаёт некоторые записи из очередного источника, не все, а именно те, которые удовлетворяют фильтру.

3. Если бы записи из источников тянулись без фильтрации, то формула выглядела бы проще:

={
IMPORTRANGE(J4;"Клиенты!B2:G");
IMPORTRANGE(J3;"Клиенты!B2:G");
IMPORTRANGE(J2;"Клиенты!B2:G")
}


4. Функция FILTER в минимальном виде выглядит так (читаем-читаем документацию):

FILTER(диапазон; условие)

В нашем случае:

"диапазон" - это IMPORTRANGE(J4;"Клиенты!B2:G") - т.е. массив данных из нескольких колонок B:G источника консолидации;

"условие" - IMPORTRANGE(J4;"Клиенты!B2:B")<>"" - т.е. проверка на непустоту значений из одной колонки B источника.


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 18.05.2016, 20:53
 
Ответить
Сообщение
Можете еще подсказать ответы на вопросы, которые сейчас для меня загадкой остаются

Показываю на своей прозрачной формуле (тэги не использую сознательно):

={
FILTER(IMPORTRANGE(J4;"Клиенты!B2:G");IMPORTRANGE(J4;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J3;"Клиенты!B2:G");IMPORTRANGE(J3;"Клиенты!B2:B")<>"");
FILTER(IMPORTRANGE(J2;"Клиенты!B2:G");IMPORTRANGE(J2;"Клиенты!B2:B")<>"")
}


1. Фигурные скобки задают консолидацию, т.е. всё что внутри них объединяется в единую таблицу, подтягиваясь из разных источников консолидации (в данном случае - трех).

2. Каждый FILTER задаёт некоторые записи из очередного источника, не все, а именно те, которые удовлетворяют фильтру.

3. Если бы записи из источников тянулись без фильтрации, то формула выглядела бы проще:

={
IMPORTRANGE(J4;"Клиенты!B2:G");
IMPORTRANGE(J3;"Клиенты!B2:G");
IMPORTRANGE(J2;"Клиенты!B2:G")
}


4. Функция FILTER в минимальном виде выглядит так (читаем-читаем документацию):

FILTER(диапазон; условие)

В нашем случае:

"диапазон" - это IMPORTRANGE(J4;"Клиенты!B2:G") - т.е. массив данных из нескольких колонок B:G источника консолидации;

"условие" - IMPORTRANGE(J4;"Клиенты!B2:B")<>"" - т.е. проверка на непустоту значений из одной колонки B источника.

Автор - Gustav
Дата добавления - 18.05.2016 в 20:47
rownong27 Дата: Среда, 18.05.2016, 21:26 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Gustav, спасибо за ответ.
Теперь все стало ясно!
Сейчас займусь сведением свои реальных файлов, посмотрю как работает.


Сообщение отредактировал Serge_007 - Среда, 18.05.2016, 21:30
 
Ответить
СообщениеGustav, спасибо за ответ.
Теперь все стало ясно!
Сейчас займусь сведением свои реальных файлов, посмотрю как работает.

Автор - rownong27
Дата добавления - 18.05.2016 в 21:26
rownong27 Дата: Четверг, 19.05.2016, 02:41 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Gustav, появился вопрос, написал вам ЛС.
[moder]Значит п5о Правил форума Вы не читали![/moder]


Сообщение отредактировал Manyasha - Четверг, 19.05.2016, 11:06
 
Ответить
СообщениеGustav, появился вопрос, написал вам ЛС.
[moder]Значит п5о Правил форума Вы не читали![/moder]

Автор - rownong27
Дата добавления - 19.05.2016 в 02:41
rownong27 Дата: Четверг, 19.05.2016, 11:29 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Модератор:
Значит п5о Правил форума Вы не читали!

Да не обратил внимание. Извиняюсь. В преть буду знать.
[moder]Куда? :D


Сообщение отредактировал _Boroda_ - Четверг, 19.05.2016, 11:37
 
Ответить
Сообщение
Модератор:
Значит п5о Правил форума Вы не читали!

Да не обратил внимание. Извиняюсь. В преть буду знать.
[moder]Куда? :D

Автор - rownong27
Дата добавления - 19.05.2016 в 11:29
rownong27 Дата: Четверг, 19.05.2016, 11:52 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Модератор:
Куда?

Не понял вопрос.
[moder]Да ладно, не обращайте внимания -
"Не вынесла душа поэта"
(М. Ю. Лермонтов)


Сообщение отредактировал _Boroda_ - Четверг, 19.05.2016, 13:09
 
Ответить
Сообщение
Модератор:
Куда?

Не понял вопрос.
[moder]Да ладно, не обращайте внимания -
"Не вынесла душа поэта"
(М. Ю. Лермонтов)

Автор - rownong27
Дата добавления - 19.05.2016 в 11:52
rownong27 Дата: Четверг, 19.05.2016, 11:52 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Показываю на своей прозрачной формуле

Получается сейчас консолидация (отправка информации со строки Оператора в файл Руководителя) запускается, только если появляется какая-то запись в столбце «A» файла оператора.

Как сделать, чтобы отправка информации со строки Оператора в файл Руководителя запускалась, при появлении в любом из столбцов «A-X» (но экспортировались полностью пустые строки в этом диапазоне). Т.к. заполнение строки в файле оператора не всех начинается со столбца «A».
[moder]Обратите, пожалуйста, внимание на ВСЕ пункты Правил форума.
Нарушение п 5j! Пост поправила.[/moder]


Сообщение отредактировал Manyasha - Четверг, 19.05.2016, 11:57
 
Ответить
Сообщение
Показываю на своей прозрачной формуле

Получается сейчас консолидация (отправка информации со строки Оператора в файл Руководителя) запускается, только если появляется какая-то запись в столбце «A» файла оператора.

Как сделать, чтобы отправка информации со строки Оператора в файл Руководителя запускалась, при появлении в любом из столбцов «A-X» (но экспортировались полностью пустые строки в этом диапазоне). Т.к. заполнение строки в файле оператора не всех начинается со столбца «A».
[moder]Обратите, пожалуйста, внимание на ВСЕ пункты Правил форума.
Нарушение п 5j! Пост поправила.[/moder]

Автор - rownong27
Дата добавления - 19.05.2016 в 11:52
rownong27 Дата: Воскресенье, 22.05.2016, 16:54 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 36
Репутация: 1 ±
Замечаний: 60% ±

Есть файл Руководителя, куда выводится информация: https://goo.gl/dx16Do
Есть файл Оператора, откуда экспортируется информация: https://goo.gl/8YwPTi

Формула выглядит таким образом:
={FILTER(IMPORTRANGE("1bHVaQtTk18By7YlxOPI0ya50eQVfaG1r6Cew0O4UHeQ";"Клиенты!A1:X");IMPORTRANGE("1bHVaQtTk18By7YlxOPI0ya50eQVfaG1r6Cew0O4UHeQ";"Клиенты!A1:A")<>"")}

Функция FILTER(диапазон; условие)

В этом случае задает:

"диапазон" - это IMPORTRANGE(ссылка-на-документ;"Клиенты!A1:X") - т.е. массив данных из нескольких колонок A:X источника консолидации;

"условие" - IMPORTRANGE(ссылка-на-документ;"Клиенты!A1:A")<>"" - т.е. проверка на непустоту значений из одной колонки A источника.

Вопрос:
В текущих документах, и с текущей формулой, эскорт данных из файла Оператора в файл Руководителя начинается, только если в файле Оператора, в строке была заполнена ячейка в столбце «A». Скриншот: https://goo.gl/hGPiya

Но если Оператор заполняет любую ячейку строки, не заполнив ячейку столбца «A», то данные не отправляются. Скриншот: https://goo.gl/a5LUj2

Так вот, как сделать (как поменять формулу), чтобы если в файле Оператора заполняется любая ячейка, в любой строке в диапазоне A-X, то информация бы отправлялась в файл Руководителя. Скриншот: https://goo.gl/8tbMsd
 
Ответить
СообщениеЕсть файл Руководителя, куда выводится информация: https://goo.gl/dx16Do
Есть файл Оператора, откуда экспортируется информация: https://goo.gl/8YwPTi

Формула выглядит таким образом:
={FILTER(IMPORTRANGE("1bHVaQtTk18By7YlxOPI0ya50eQVfaG1r6Cew0O4UHeQ";"Клиенты!A1:X");IMPORTRANGE("1bHVaQtTk18By7YlxOPI0ya50eQVfaG1r6Cew0O4UHeQ";"Клиенты!A1:A")<>"")}

Функция FILTER(диапазон; условие)

В этом случае задает:

"диапазон" - это IMPORTRANGE(ссылка-на-документ;"Клиенты!A1:X") - т.е. массив данных из нескольких колонок A:X источника консолидации;

"условие" - IMPORTRANGE(ссылка-на-документ;"Клиенты!A1:A")<>"" - т.е. проверка на непустоту значений из одной колонки A источника.

Вопрос:
В текущих документах, и с текущей формулой, эскорт данных из файла Оператора в файл Руководителя начинается, только если в файле Оператора, в строке была заполнена ячейка в столбце «A». Скриншот: https://goo.gl/hGPiya

Но если Оператор заполняет любую ячейку строки, не заполнив ячейку столбца «A», то данные не отправляются. Скриншот: https://goo.gl/a5LUj2

Так вот, как сделать (как поменять формулу), чтобы если в файле Оператора заполняется любая ячейка, в любой строке в диапазоне A-X, то информация бы отправлялась в файл Руководителя. Скриншот: https://goo.gl/8tbMsd

Автор - rownong27
Дата добавления - 22.05.2016 в 16:54
Gustav Дата: Понедельник, 23.05.2016, 08:22 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
как сделать (как поменять формулу), чтобы если в файле Оператора заполняется любая ячейка, в любой строке в диапазоне A-X, то информация бы отправлялась в файл Руководителя


Уйти от функции FILTER к функции QUERY - счастье должно забрезжить:
[vba]
Код
=QUERY({
IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора2;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора3;"Клиенты!A2:X")
};"select * where
Col1 <>'' or Col2 <>'' or Col3 <>'' or Col4 <>'' or Col5 <>'' or  
Col6 <>'' or Col7 <>'' or Col8 <>'' or Col9 <>'' or Col10<>'' or  
Col11<>'' or Col12<>'' or Col13<>'' or Col14<>'' or Col15<>'' or  
Col16<>'' or Col17<>'' or Col18<>'' or Col19<>'' or Col20<>'' or  
Col21<>'' or Col22<>'' or Col23<>'' or Col24<>''")
[/vba]

ВАЖНО: Обращаю внимание, что перед тем как вставить эту формулу в новую созданную таблицу, надо сначала "подружить" новый лист (руководителя) с другими уже существующими таблицами (операторов). Для этого надо для всех участвующих в консолидации источников вставить на новый лист временные формулы вида:
Код
=IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X")

После возникновения ошибки нужно открыть ее комментарий и нажать в нём кнопку "Открыть доступ". После открытия доступа ко всем таблицам временные формулы можно удалить и, наконец, вставить нашу консолидирующую формулу.

ПРИМЕЧАНИЯ:
1. Заголовки колонок (первая строка) в импорте не участвуют и должны быть созданы вручную - и в файле руководителя, и в файлах операторов.
2. Если в каком-то источнике консолидации (файле оператора) совсем нет данных (только строка заголовков), то от него, тем не менее, в файл руководителя приходит одна пустая строка (что гораздо лучше поведения функции FILTER - у нее в аналогичной ситуации c ошибкой валится вся консолидация).


МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Понедельник, 23.05.2016, 09:08
 
Ответить
Сообщение
как сделать (как поменять формулу), чтобы если в файле Оператора заполняется любая ячейка, в любой строке в диапазоне A-X, то информация бы отправлялась в файл Руководителя


Уйти от функции FILTER к функции QUERY - счастье должно забрезжить:
[vba]
Код
=QUERY({
IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора2;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора3;"Клиенты!A2:X")
};"select * where
Col1 <>'' or Col2 <>'' or Col3 <>'' or Col4 <>'' or Col5 <>'' or  
Col6 <>'' or Col7 <>'' or Col8 <>'' or Col9 <>'' or Col10<>'' or  
Col11<>'' or Col12<>'' or Col13<>'' or Col14<>'' or Col15<>'' or  
Col16<>'' or Col17<>'' or Col18<>'' or Col19<>'' or Col20<>'' or  
Col21<>'' or Col22<>'' or Col23<>'' or Col24<>''")
[/vba]

ВАЖНО: Обращаю внимание, что перед тем как вставить эту формулу в новую созданную таблицу, надо сначала "подружить" новый лист (руководителя) с другими уже существующими таблицами (операторов). Для этого надо для всех участвующих в консолидации источников вставить на новый лист временные формулы вида:
Код
=IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X")

После возникновения ошибки нужно открыть ее комментарий и нажать в нём кнопку "Открыть доступ". После открытия доступа ко всем таблицам временные формулы можно удалить и, наконец, вставить нашу консолидирующую формулу.

ПРИМЕЧАНИЯ:
1. Заголовки колонок (первая строка) в импорте не участвуют и должны быть созданы вручную - и в файле руководителя, и в файлах операторов.
2. Если в каком-то источнике консолидации (файле оператора) совсем нет данных (только строка заголовков), то от него, тем не менее, в файл руководителя приходит одна пустая строка (что гораздо лучше поведения функции FILTER - у нее в аналогичной ситуации c ошибкой валится вся консолидация).

Автор - Gustav
Дата добавления - 23.05.2016 в 08:22
Gustav Дата: Среда, 25.05.2016, 07:47 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2697
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Ну, и на сладкое приготовил еще скриптовую версию импорта, без формул.

Суть в следующем. В меню файла Руководителя внедряется пункт "Импорт от Операторов", при клике на котором последовательно обходятся все интересующие таблицы Операторов. Данные из этих таблиц собираются в единый виртуальный массив, из которого потом удаляются полностью пустые строки. Уплотненный массив окончательно вставляется на лист Руководителя, предварительно очищенный от предыдущего импорта. Строки заголовков (первые) во всех файлах готовятся заранее (вручную) и импортом не затрагиваются.

При таком подходе, в отличие от формул, у Руководителя появляется возможность активной работы с импортированными данными - редактирование, сортировка и т.п. Правда, не знаю, будет ли в данном конкретном случае это достоинством или недостатком. Само собой, любые изменения на листе Руководителя, сделанные в диапазоне импорта, живут до ближайшего повторного импорта. Поэтому если нужна какая-то долговременная фиксация данных, их следует скопировать в безопасное место (на другой лист).

[vba]
Код
function onOpen() {
  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Моё меню')
    .addItem('Бросить якорь в C56', 'setAnchor')
    .addItem('Импорт от Операторов', 'importFromOperators')
    .addToUi();
}

function importFromOperators() {
  
  // подготовка места вставки данных в файле Руководителя
  var ssBoss = SpreadsheetApp.getActive(); // файл Руководителя (текущий)
  var bsSheet = ssBoss.getSheetByName('Лист2'); // название листа в файле Руководителя
  
  var bsLastRow = Math.max(2, bsSheet.getLastRow());
  
  var bsRange = bsSheet.getRange('B2:Y'+bsLastRow); // диапазон на листе в файле Руководителя
  bsRange.clear(); // очищаем перед новой загрузкой
  
  var values = bsRange.getValues(); // инициализируем массив одной пустой строкой (потом удалим)
  
  // массив идентификаторов файлов Операторов
  var operIds = [ '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_1' ,
                  '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_2' ,
                  '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_3' ];
  
  // читаем данные из файлов Операторов (цикл по файлам)
  for(var i=0; i<operIds.length; i++) {
    var ssOper = SpreadsheetApp.openById(operIds[i]); // очередной файл Оператора
    var opSheet = ssOper.getSheetByName('Клиенты'); // название листа в файле Оператора
  
    var opLastRow = Math.max(2, opSheet.getLastRow());
  
    var opRange = opSheet.getRange('A2:X'+opLastRow); // диапазон на листе в файле Оператора
    
    values = values.concat(opRange.getValues()); // добавляем очередные значение в общий массив
  }
  
  // удаляем абсолютно пустые строки (массив уплотняется)
  for (i=values.length-1; i>=0; i--) {
    if (values[i].toString() == values[0].toString()) {
      values.splice(i, 1);
    }
  }
  
  // вставляем консолидированные данные на лист Руководителя
  if (values.length) bsRange.offset(0, 0, values.length).setValues(values);  
}
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеНу, и на сладкое приготовил еще скриптовую версию импорта, без формул.

Суть в следующем. В меню файла Руководителя внедряется пункт "Импорт от Операторов", при клике на котором последовательно обходятся все интересующие таблицы Операторов. Данные из этих таблиц собираются в единый виртуальный массив, из которого потом удаляются полностью пустые строки. Уплотненный массив окончательно вставляется на лист Руководителя, предварительно очищенный от предыдущего импорта. Строки заголовков (первые) во всех файлах готовятся заранее (вручную) и импортом не затрагиваются.

При таком подходе, в отличие от формул, у Руководителя появляется возможность активной работы с импортированными данными - редактирование, сортировка и т.п. Правда, не знаю, будет ли в данном конкретном случае это достоинством или недостатком. Само собой, любые изменения на листе Руководителя, сделанные в диапазоне импорта, живут до ближайшего повторного импорта. Поэтому если нужна какая-то долговременная фиксация данных, их следует скопировать в безопасное место (на другой лист).

[vba]
Код
function onOpen() {
  
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Моё меню')
    .addItem('Бросить якорь в C56', 'setAnchor')
    .addItem('Импорт от Операторов', 'importFromOperators')
    .addToUi();
}

function importFromOperators() {
  
  // подготовка места вставки данных в файле Руководителя
  var ssBoss = SpreadsheetApp.getActive(); // файл Руководителя (текущий)
  var bsSheet = ssBoss.getSheetByName('Лист2'); // название листа в файле Руководителя
  
  var bsLastRow = Math.max(2, bsSheet.getLastRow());
  
  var bsRange = bsSheet.getRange('B2:Y'+bsLastRow); // диапазон на листе в файле Руководителя
  bsRange.clear(); // очищаем перед новой загрузкой
  
  var values = bsRange.getValues(); // инициализируем массив одной пустой строкой (потом удалим)
  
  // массив идентификаторов файлов Операторов
  var operIds = [ '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_1' ,
                  '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_2' ,
                  '1K6bWtfmL2DXhTL9-JiW8y38Nh3_файл_Оператора_3' ];
  
  // читаем данные из файлов Операторов (цикл по файлам)
  for(var i=0; i<operIds.length; i++) {
    var ssOper = SpreadsheetApp.openById(operIds[i]); // очередной файл Оператора
    var opSheet = ssOper.getSheetByName('Клиенты'); // название листа в файле Оператора
  
    var opLastRow = Math.max(2, opSheet.getLastRow());
  
    var opRange = opSheet.getRange('A2:X'+opLastRow); // диапазон на листе в файле Оператора
    
    values = values.concat(opRange.getValues()); // добавляем очередные значение в общий массив
  }
  
  // удаляем абсолютно пустые строки (массив уплотняется)
  for (i=values.length-1; i>=0; i--) {
    if (values[i].toString() == values[0].toString()) {
      values.splice(i, 1);
    }
  }
  
  // вставляем консолидированные данные на лист Руководителя
  if (values.length) bsRange.offset(0, 0, values.length).setValues(values);  
}
[/vba]

Автор - Gustav
Дата добавления - 25.05.2016 в 07:47
BcjPrj Дата: Понедельник, 02.07.2018, 20:19 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 17
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Сколько таблиц можно объединять в одну таким способом
Код
=QUERY({
IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора2;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора3;"Клиенты!A2:X")
};"select * where
Col1 <>'' ...............or Col24<>''")
?
 
Ответить
СообщениеСколько таблиц можно объединять в одну таким способом
Код
=QUERY({
IMPORTRANGE(ссылка-на-документ-Оператора1;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора2;"Клиенты!A2:X");
IMPORTRANGE(ссылка-на-документ-Оператора3;"Клиенты!A2:X")
};"select * where
Col1 <>'' ...............or Col24<>''")
?

Автор - BcjPrj
Дата добавления - 02.07.2018 в 20:19
contributorpw Дата: Вторник, 08.10.2019, 14:30 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 21
Репутация: 9 ±
Замечаний: 0% ±

Excel 2016
Сколько таблиц можно объединять в одну таким способом


Общее количество возвращаемых ячеек не должно первышать
Код
5*10^6 - [колличество уже используемых в Таблице ячеек]


Всё о Google Таблицах contributor.pw
 
Ответить
Сообщение
Сколько таблиц можно объединять в одну таким способом


Общее количество возвращаемых ячеек не должно первышать
Код
5*10^6 - [колличество уже используемых в Таблице ячеек]

Автор - contributorpw
Дата добавления - 08.10.2019 в 14:30
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Настройка консолидации (объединение данных) Google Таблиц (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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