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

Вход

Регистрация

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

 

= Мир MS Excel/Объединение нескольких таблиц в Гугл Таблицах - Мир MS Excel

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

Excel 2013
Добрый день, помогите решить задачу в таблицах гугл. Весь интернет перерыл не получается ни как, спросить не у кого, на вас надеюсь!!!
Ситуация следующая есть 3 таблицы каждая в отдельной вкладке (название месяца) со списком людей и их ЗП,
нужно данные по фио объединить что бы были уникальные значения а рядом были столбцы по соответсвующим вкладкам месяцам и со значениями Зп.
скидываю ссылку на исходные данные https://docs.google.com/spreads....sharing
далее планируется ссылаться на эти вкладки и результат (в примере он вкладкой результат идет) размещать в другой гугл таблице.
Это примерно представляю как делать : с помощью функции =QUERY({IMPORTRANGE();IMPORTRANGE();IMPORTRANGE()..
 
Ответить
СообщениеДобрый день, помогите решить задачу в таблицах гугл. Весь интернет перерыл не получается ни как, спросить не у кого, на вас надеюсь!!!
Ситуация следующая есть 3 таблицы каждая в отдельной вкладке (название месяца) со списком людей и их ЗП,
нужно данные по фио объединить что бы были уникальные значения а рядом были столбцы по соответсвующим вкладкам месяцам и со значениями Зп.
скидываю ссылку на исходные данные https://docs.google.com/spreads....sharing
далее планируется ссылаться на эти вкладки и результат (в примере он вкладкой результат идет) размещать в другой гугл таблице.
Это примерно представляю как делать : с помощью функции =QUERY({IMPORTRANGE();IMPORTRANGE();IMPORTRANGE()..
Автор - Lastdony
Дата добавления - 28.02.2017 в 17:14
Gustav Дата: Понедельник, 06.03.2017, 16:59 | Сообщение № 2
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Для начала я бы сделал так:
1. Добавил на каждый месячный лист третий столбец "Месяц" (столбец C) со значениями 1, 2, 3 - по номеру месяца.
2. На листе "Результат" написал бы в A1 такую формулу (имеющаяся там почти правильная, но надо было чуть-чуть дотянуть + учесть 3-й столбец C):
[vba]
Код
=QUERY({'Январь'!A1:C; 'Февраль'!A2:C; 'Март'!A2:C}; "SELECT * WHERE Col1<>''")
[/vba]
3. Наконец, построил бы сводную таблицу (меню Данные \ Сводная таблица) со строками "ФИО", столбцами "Месяц" и значениями "ЗП".

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

Сообщение отредактировал Gustav - Понедельник, 06.03.2017, 17:01
 
Ответить
СообщениеДля начала я бы сделал так:
1. Добавил на каждый месячный лист третий столбец "Месяц" (столбец C) со значениями 1, 2, 3 - по номеру месяца.
2. На листе "Результат" написал бы в A1 такую формулу (имеющаяся там почти правильная, но надо было чуть-чуть дотянуть + учесть 3-й столбец C):
[vba]
Код
=QUERY({'Январь'!A1:C; 'Февраль'!A2:C; 'Март'!A2:C}; "SELECT * WHERE Col1<>''")
[/vba]
3. Наконец, построил бы сводную таблицу (меню Данные \ Сводная таблица) со строками "ФИО", столбцами "Месяц" и значениями "ЗП".
Автор - Gustav
Дата добавления - 06.03.2017 в 16:59
Gustav Дата: Вторник, 07.03.2017, 13:18 | Сообщение № 3
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Вариант 2 - более полный и законченный:
1. Добавим на каждый месячный лист третий столбец "Месяц" (столбец C) - на этот раз с ТЕКСТОВЫМИ названиями месяцев - "Январь", "Февраль", "Март".
2. На листе "Результат" напишем в A1 такую формулу (имеющаяся там почти правильная, но надо было чуть-чуть дотянуть + учесть 3-й столбец C):
[vba]
Код
=QUERY({'Январь'!A1:C; 'Февраль'!A2:C; 'Март'!A2:C}; "SELECT * WHERE Col1<>''")
[/vba]
3. Построим сводную таблицу (меню Данные \ Сводная таблица) со строками "ФИО", столбцами "Месяц" и значениями "ЗП". Для строк - удалить "галочку" "Отображение сводных данных" (чтобы не мешалась строка "Итого"). И ничего страшного, что текстовые названия месяцев в сводной отсортируются по алфавиту, а не по хронологии - это будет исправлено на след.рабочем листе.
4. Добавим еще один рабочий лист, назовем его "Финал".
5. В ячейку A1 нового листа поместим формулу, вытаскивающую ФИО из сводной (и эта формула в одной ячейке заполнит нам весь столбец A):
[vba]
Код
=UNIQUE('Сводная таблица'!A1:A)
[/vba]
6. В ячейку B1 нового листа поместим формулу с горизонтальным массивом названий месяцев (и эта формула в одной ячейке заполнит нам 12 ячеек строки 1 до ячейки M1):
[vba]
Код
={"Январь"\"Февраль"\"Март"\"Апрель"\"Май"\"Июнь"\"Июль"\"Август"\"Сентябрь"\"Октябрь"\"Ноябрь"\"Декабрь"}
[/vba]
7. Наконец, начиная с ячейки B2 листа "Финал", вправо и вниз протянем формулу, достающую из сводной значения ЗП на пересечении ФИО и месяца:
[vba]
Код
=INDEX('Сводная таблица'!$A:$G; MATCH($A2;'Сводная таблица'!$A:$A;0); MATCH(B$1;'Сводная таблица'!$1:$1;0))
[/vba]

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВариант 2 - более полный и законченный:
1. Добавим на каждый месячный лист третий столбец "Месяц" (столбец C) - на этот раз с ТЕКСТОВЫМИ названиями месяцев - "Январь", "Февраль", "Март".
2. На листе "Результат" напишем в A1 такую формулу (имеющаяся там почти правильная, но надо было чуть-чуть дотянуть + учесть 3-й столбец C):
[vba]
Код
=QUERY({'Январь'!A1:C; 'Февраль'!A2:C; 'Март'!A2:C}; "SELECT * WHERE Col1<>''")
[/vba]
3. Построим сводную таблицу (меню Данные \ Сводная таблица) со строками "ФИО", столбцами "Месяц" и значениями "ЗП". Для строк - удалить "галочку" "Отображение сводных данных" (чтобы не мешалась строка "Итого"). И ничего страшного, что текстовые названия месяцев в сводной отсортируются по алфавиту, а не по хронологии - это будет исправлено на след.рабочем листе.
4. Добавим еще один рабочий лист, назовем его "Финал".
5. В ячейку A1 нового листа поместим формулу, вытаскивающую ФИО из сводной (и эта формула в одной ячейке заполнит нам весь столбец A):
[vba]
Код
=UNIQUE('Сводная таблица'!A1:A)
[/vba]
6. В ячейку B1 нового листа поместим формулу с горизонтальным массивом названий месяцев (и эта формула в одной ячейке заполнит нам 12 ячеек строки 1 до ячейки M1):
[vba]
Код
={"Январь"\"Февраль"\"Март"\"Апрель"\"Май"\"Июнь"\"Июль"\"Август"\"Сентябрь"\"Октябрь"\"Ноябрь"\"Декабрь"}
[/vba]
7. Наконец, начиная с ячейки B2 листа "Финал", вправо и вниз протянем формулу, достающую из сводной значения ЗП на пересечении ФИО и месяца:
[vba]
Код
=INDEX('Сводная таблица'!$A:$G; MATCH($A2;'Сводная таблица'!$A:$A;0); MATCH(B$1;'Сводная таблица'!$1:$1;0))
[/vba]
Автор - Gustav
Дата добавления - 07.03.2017 в 13:18
Gustav Дата: Вторник, 07.03.2017, 16:05 | Сообщение № 4
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Вариант 3 - УРА! Всё очень красиво решается вводом одной-единственной следующей формулы в ячейку A1 любого пустого листа, например, "результат" - тут тебе и объединение, и сводная таблица, и переименование столбцов в одном флаконе (в столбцы "Месяц" следует опять вернуть числа 1, 2, 3):
[vba]
Код
=QUERY(QUERY({'Январь'!A2:D;'Февраль'!A2:D;'Март'!A2:D}; "select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3"); "label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март'")
[/vba]
Ну что ж, сочинение формул в таблицах Google, особенно в тех функциях, где еще не ступала нога Excel - очень даже увлекательно.

МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеВариант 3 - УРА! Всё очень красиво решается вводом одной-единственной следующей формулы в ячейку A1 любого пустого листа, например, "результат" - тут тебе и объединение, и сводная таблица, и переименование столбцов в одном флаконе (в столбцы "Месяц" следует опять вернуть числа 1, 2, 3):
[vba]
Код
=QUERY(QUERY({'Январь'!A2:D;'Февраль'!A2:D;'Март'!A2:D}; "select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3"); "label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март'")
[/vba]
Ну что ж, сочинение формул в таблицах Google, особенно в тех функциях, где еще не ступала нога Excel - очень даже увлекательно.
Автор - Gustav
Дата добавления - 07.03.2017 в 16:05
Gustav Дата: Пятница, 10.03.2017, 19:21 | Сообщение № 5
Группа: Админы
Ранг: Участник клуба
Сообщений: 2797
Репутация: 1161 ±
Замечаний: ±

начинал с Excel 4.0, видел 2.1
Вариант 4 - без добавления колонки "Месяц" на исходные месячные листы - номера месяцев включаем сразу в SELECT'ы (как 1,2,3):
[vba]
Код
=QUERY(QUERY(
{
QUERY('Январь'!A2:B; "select A,B,1");
QUERY('Февраль'!A2:B;"select A,B,2");
QUERY('Март'!A2:B;   "select A,B,3")
};
"select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3");
"label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март'")
[/vba]Т.е. вариант абсолютно неинвазивный по отношению к исходному материалу!

Интересно, ТС когда-нибудь заглянет сюда опять? А то я ему тут прямо целую лекцию...

P.S. Пользуясь еще открытой возможностью редактирования свежего сообщения (типа не накручиваем специально их количество :)),

Вариант 5 - с учётом ещё несозданных месячных листов (Апрель, Май, Июнь):
[vba]
Код
=QUERY(QUERY(
{
QUERY(IFERROR(INDIRECT("'Январь'!A2:B"); {"_"\0}); "select Col1, Col2, 1");
QUERY(IFERROR(INDIRECT("'Февраль'!A2:B");{"_"\0}); "select Col1, Col2, 2");
QUERY(IFERROR(INDIRECT("'Март'!A2:B");   {"_"\0}); "select Col1, Col2, 3");
QUERY(IFERROR(INDIRECT("'Апрель'!A2:B"); {"_"\0}); "select Col1, Col2, 4");
QUERY(IFERROR(INDIRECT("'Май'!A2:B");    {"_"\0}); "select Col1, Col2, 5");
QUERY(IFERROR(INDIRECT("'Июнь'!A2:B");   {"_"\0}); "select Col1, Col2, 6")
};
"select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3");
"label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март', Col5 'Апрель', Col6 'Май', Col7 'Июнь'")
[/vba]
Заметьте, что здесь все переменные фрагменты формул - текстовые, а значит могут быть заменены ссылками на другие ячейки, где эти текстовые значения могут быть заранее (и удобно!) подготовлены.

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

Сообщение отредактировал Gustav - Пятница, 10.03.2017, 20:26
 
Ответить
СообщениеВариант 4 - без добавления колонки "Месяц" на исходные месячные листы - номера месяцев включаем сразу в SELECT'ы (как 1,2,3):
[vba]
Код
=QUERY(QUERY(
{
QUERY('Январь'!A2:B; "select A,B,1");
QUERY('Февраль'!A2:B;"select A,B,2");
QUERY('Март'!A2:B;   "select A,B,3")
};
"select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3");
"label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март'")
[/vba]Т.е. вариант абсолютно неинвазивный по отношению к исходному материалу!

Интересно, ТС когда-нибудь заглянет сюда опять? А то я ему тут прямо целую лекцию...

P.S. Пользуясь еще открытой возможностью редактирования свежего сообщения (типа не накручиваем специально их количество :)),

Вариант 5 - с учётом ещё несозданных месячных листов (Апрель, Май, Июнь):
[vba]
Код
=QUERY(QUERY(
{
QUERY(IFERROR(INDIRECT("'Январь'!A2:B"); {"_"\0}); "select Col1, Col2, 1");
QUERY(IFERROR(INDIRECT("'Февраль'!A2:B");{"_"\0}); "select Col1, Col2, 2");
QUERY(IFERROR(INDIRECT("'Март'!A2:B");   {"_"\0}); "select Col1, Col2, 3");
QUERY(IFERROR(INDIRECT("'Апрель'!A2:B"); {"_"\0}); "select Col1, Col2, 4");
QUERY(IFERROR(INDIRECT("'Май'!A2:B");    {"_"\0}); "select Col1, Col2, 5");
QUERY(IFERROR(INDIRECT("'Июнь'!A2:B");   {"_"\0}); "select Col1, Col2, 6")
};
"select Col1, SUM(Col2) where Col1 <> '' group by Col1 pivot Col3");
"label Col1 'ФИО', Col2 'Январь', Col3 'Февраль', Col4 'Март', Col5 'Апрель', Col6 'Май', Col7 'Июнь'")
[/vba]
Заметьте, что здесь все переменные фрагменты формул - текстовые, а значит могут быть заменены ссылками на другие ячейки, где эти текстовые значения могут быть заранее (и удобно!) подготовлены.
Автор - Gustav
Дата добавления - 10.03.2017 в 19:21
MN Дата: Воскресенье, 30.04.2017, 22:25 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel для Mac
Gustav, здравствуйте.
подскажите, пжлст, как быть в моей ситуации? Задача, вроде как, похожая на разобранную выше.
Есть несколько постоянно редактируемых таблиц с остатками на разных листах, надо сделать таблицу с общими остатками единым списком без суммирования и тп, чтобы в общей таблице было видно, на каком из складов находится каждый товар.
Вот ссылка на таблицу https://docs.google.com/spreads....sharing
Возможно ли сделать аналогичную схему, если таблицы со складами будут в разных книгах?
Заранее благодарю.
 
Ответить
СообщениеGustav, здравствуйте.
подскажите, пжлст, как быть в моей ситуации? Задача, вроде как, похожая на разобранную выше.
Есть несколько постоянно редактируемых таблиц с остатками на разных листах, надо сделать таблицу с общими остатками единым списком без суммирования и тп, чтобы в общей таблице было видно, на каком из складов находится каждый товар.
Вот ссылка на таблицу https://docs.google.com/spreads....sharing
Возможно ли сделать аналогичную схему, если таблицы со складами будут в разных книгах?
Заранее благодарю.
Автор - MN
Дата добавления - 30.04.2017 в 22:25
Pelena Дата: Воскресенье, 30.04.2017, 23:02 | Сообщение № 7
Группа: Админы
Ранг: Местный житель
Сообщений: 19403
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
MN, прочитайте Правила форума и создайте свою тему

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеMN, прочитайте Правила форума и создайте свою тему
Автор - Pelena
Дата добавления - 30.04.2017 в 23:02
neon130 Дата: Понедельник, 09.12.2019, 01:52 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 1
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Здравствуйте у меня похоже тема помогите пожалуйста решить задачу вот ссылка https://docs.google.com/spreads....sharing
 
Ответить
СообщениеЗдравствуйте у меня похоже тема помогите пожалуйста решить задачу вот ссылка https://docs.google.com/spreads....sharing
Автор - neon130
Дата добавления - 09.12.2019 в 01:52
Pelena Дата: Понедельник, 09.12.2019, 07:47 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19403
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
neon130, если предложенное решение не подошло, создайте свою тему, прочитав предварительно Правила форума

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеneon130, если предложенное решение не подошло, создайте свою тему, прочитав предварительно Правила форума
Автор - Pelena
Дата добавления - 09.12.2019 в 07:47
linky Дата: Четверг, 16.01.2020, 10:42 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 4
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Сразу извиняюсь, через поиск не нашел ответа, если есть буду благодарен ссылки.

Есть одна таблица, которая рассылается 40 людям.
я так понимаю создаю 40 ссылок для каждого, каждый заполняет своб таблицу.
и мне нужно что бы все данные которые они заполняют в моей таблицы консолидировались ( суммировались )

к примеру B1 листа1 со всеми B1 листа1 всех таблиц ...

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

Есть одна таблица, которая рассылается 40 людям.
я так понимаю создаю 40 ссылок для каждого, каждый заполняет своб таблицу.
и мне нужно что бы все данные которые они заполняют в моей таблицы консолидировались ( суммировались )

к примеру B1 листа1 со всеми B1 листа1 всех таблиц ...

Понимаю что вопрос элементарный, но я решениях и по поиску не нашел, как это в гугл сделать можно ?
Автор - linky
Дата добавления - 16.01.2020 в 10:42
Pelena Дата: Четверг, 16.01.2020, 10:48 | Сообщение № 11
Группа: Админы
Ранг: Местный житель
Сообщений: 19403
Репутация: 4555 ±
Замечаний: ±

Excel 365 & Mac Excel
linky, Ваш вопрос не имеет отношения к этой теме. Прочитайте Правила форума и создайте свою тему. Эта тема закрыта

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщениеlinky, Ваш вопрос не имеет отношения к этой теме. Прочитайте Правила форума и создайте свою тему. Эта тема закрыта
Автор - Pelena
Дата добавления - 16.01.2020 в 10:48
Мир MS Excel » Вопросы и решения » Excel и другие приложения » Google Docs » Объединение нескольких таблиц в Гугл Таблицах (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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