Как связать два гугл документа (Excel)?

Question

[ZorX]

Всем привет!
Помогите связать две гугл таблицы. ( не листа, а именно два разных документа)
Есть одна таблица, условно назовем ее "Склад" и вторая таблица с уловным названием "Отправки", как выглядят таблицы, видно на скринах.
Возможно ли сделать так, что бы при вводе артикула товара в таблице "Отправки", одна единица товара автоматически удалялась из таблицы "Склад" из колонки "Количество"
К примеру есть отправка подушки, на складе у нас 10 подушек, мы вводим эту отправку в таблицы, и во второй таблице в колонке "Количество" остается 9.

Буду рад любой помощи=)

Comments

[Oleg]

Если в "Отравке" всегда идет речь про одну шт., то можно через счетесли (countif) посчитать кол-во, а через importrange - по сути связать таблицы.

[ZorX]

Oleg, нет, может быть и (3 подушки) и (3 подушки, 2 одеяла).

[ZorX]

Oleg, ну суть мне в принципе ясна. Если можешь, помоги теперь применить это на практике)

[ZorX]

В общем ребятушки. Спасибо Олегу, он подтолкнул меня в нужном направлении.
Значит, что я сделал. (Распишу, может кому-то полезным будет, + у меня еще остался вопрос)
1. В таблице "Склад" отдельно от глаз создал колонку и назвал ее "Уехало"
В эту колонку я поместил формулу

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123;"12.2019!G1:G1000")

=IMPORTRANGE - функция которая переносит значения из диапазона одной таблицы, в диапазон ячеек в другой таблице
12.2019 - название моего листа в таблице "Отправки"
G1:G1000 - диапазон ячеек, в которых я указываю код отправленного товара.

2. Дальше я создал еще одну колонку в таблице "Склад" (P)(называйте ее как хотите, она будет скрыта)
В эту колонку я поместил следующую формулу:
=COUNTIF(=COUNTIF(Q1:Q1000;A3)")
=COUNTIF - функция проверяет диапазон ячеек на наличие там указанного значения
Q1:Q1000 - это наша колонка, в которую мы внесли коды товаров в пункте 1.
A3 - Ячейка содержащая код моего товара на складе
Читается примерно так "Если диапазон ячеек от Q1 до Q1000 содержит значение находящееся в ячейке А3 то в ячейку с формулой добавляется +1"

3. Дальше в таблице склад у меня есть две колонки с количеством товара
"Количество" - буква колонки - "F"
"Количество2" - буква колонки - "E"
Первая называется "Количество2" - эта колонка скрыта, мы ее не видим, нужна она нам для того, что бы изменять количества товара не изменяя формулу, например у вас поступление товара вы можете его вручную изменить не затрагивая формулы.
Вторая колонка называется "Количество" - эта колонка видна для глаз, в ней отображается количество по факту, с вычетом из таблицы "Отправки"
Итак в колонку "Количество"(F) мы прописываем формулу
=E3-P3
Где E3 - количество товара на складе
P3 - ячейка в которой суммируется количество товара с содержанием кода товара.

Очень сложно расписать это все в тексте, если кому-то нужно будет, пишите, помогу.

Из минусов:
1. Если ячейка в "Отправки" содержит два кода товара, например "1001, 1002" то формула не выполняется.
2-ой минус в документе "Отправки" на каждый месяц у меня создается новый лист, а формула не позволяет брать значения из разных листов.

Помогите, пожалуйста ,решить эти минусы=)

[Oleg]

По 1, лучше сделать, что бы одна строка = один код. Если так совсем нельзя, то можно попробывать через регулярное выражение (через REGEXEXTRACT, в качестве шаблона прописываем ссылку на код) вытащить код. Но это уже огород городим.
По п2, вым ничего не мешает в одной строке прописать несколько IMPORTRANGE. В фигурных скобках, через ; прописываете очередной IMPORTRANGE. Оберну это все в формулу QUERY, можете избавиться от пустых ячеек.
Попробуйте так. Не имея доступ к таблицам, помочь могу только теорией

[ZorX]

Oleg,

={IMPORTRANGE("https://docs.google.com/spreadsheets/ссылка";"12.2019!G1:G1000");IMPORTRANGE("https://docs.google.com/spreadsheets/ссылка";"11.2019!G1:G1000";)}

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

И можешь пожалуйста подробней о QUERY? Как ее засунуть в эту формулу?

[ZorX]

Oleg, с формулой разобрался, в конце была лишняя ;
А вот с QUERY еще не разобрался)

[ZorX]

Вылез еще один подводный камень
Формула =COUNTIF(Q1:Q1000;A3)
Я ее не могу растянуть
В Q1:Q1000 у меня содержатся коды товаров с Отправок
A3 - код товара на складе
Вот мне нужно, что бы когда я тянул формулу, А3 менялось на А4 и т.д., а вот Q1:Q1000 оставалось неизменным)
Такое вообще реально?=)

UPD:
Решилось следующим образом
=COUNTIF($Q$1:$Q$1000;A3)

[Oleg]

т.к. одна ячейка товара, может содержить разное кол-во, то нужно использовать sumif. Про counif, он считает кол-во, а не сумму, и эту формулу предлагал, т.к. ранее писали что одна строчка = 1 значение. Вобщем, это проверьте, что больше подоидет sumif - countif. Но опять таки вопрос, в первой таблице вижу только код товара, и вы его тянете через importrange, но я не вижу кол-во, хотя ранее вы писали, что может быть 2, 3, и т.д. кол-во. Как вы из первой таблицы понимаете, какое кол-во прописано?
По query, там много разных возможностей. В данном случае к примеру можно исключить пусты строки из формулы importrange (вы берете с 1 по 1000 строку, и не все могут быть заполнены, соответственно следующий importrange, выведет данные на 1001 строке). Можно написать query({importrange;importrange}; "select* where Col1 is not null")
В importrange, в первом аргументе можно указывать просто id таблицы, а не полный путь. Так будет компактнее.

[ZorX]

Oleg, Большое спасибо! Очень помогли мне)

Answer 1

[Tormido]

А как товар попадает на склад?) (это типа намек, что текущее состояние склада должно быть результирующей таблицей от таблицы прихода и таблицы расхода)