Использование VLOOLUP вместе с QUERY и IMPORTRANGE
Подскажите пожалуйста, я делал отчёт используя SUMIF (ячейка C3), но сейчас понял, что если все листы за каждый месяц вставлять в один документ, то это будет не сильно практично и превышу лимит. Поэтому решено на каждый месяц создавать отдельный документ и импортировать данные для расчёта. Но оказалось, что SUMIF не работает с IMPORTRANGE :( Изучив чат, понял что нужно делать через QUERY Начал создавать формулу квери в ячейке H3, вроде бы в WHERE прописал все нужные условия, но выдаёт ошибку. С чем это связанно, что сделал не так?
https://docs.google.com/spreadsheets/d/1Vb0cJbkjja537yl6HDYVcGiUUGZUgVAUiYcvstihMds/edit
Данные берутся от сюда:
https://docs.google.com/spreadsheets/d/1WHuE9dYyCCtrcKrQmq4i4wx_KhimiPIo5KBLNNAsDSw/edit
Ответы (1 шт):
Пояснение к вопросу
IMPORTRANGE - является функцией импорта. Чем может сковать действия пользователя не только своими паузами по времени исполнения, но и отсутствием какого-либо представления об импортируемых данных.
QUERY, VLOOKUP и SUMIF - формулы другой категории, нежели IMPORTRANGE. И они в свою очередь разбирают типы данные по разному.
Пояснения про типы
Типов в Таблицах немного. Основную проблему представляют "нечисла". Т.к. между числами и датами есть сильное сходство. А еще между форматами чисел и строк при бездумном форматировании ячеек может исчезнуть визуальная разница.
Как использовать IMPORTRANGE
Ошибки из вопроса
Если взять "кусок" данных из примера и убрать форматирование, то видно, что в колонках смешанный тип данных. Строки, представляющие числа, числа и строки не числа.
Вот так работает SUM (взято для простоты формулы):
QUERY
VLOOKUP для точного поиска чисел
Как видно из примеров, формулы не только ломаются на смешанных типах, но и могут возвращать разные ошибки.
Особенность IMPORTRANGE
Пример переноса формата. Над таким иногда можно и голову поломать.
Формула старается точно перенести типы, которые встречает. А также она переносит формат данных. Неожиданное поведение, которое, кстати, приводит к неожиданным результатам
Использование
Нет никакой необходимости в особенном использовании IMPORTRANGE, но существуют общепринятые рекомендации
- Импортируйте на отдельный лист. Разделяйте ответственность каждого листа, чтобы быстро отслеживать типы данных, расчеты и представления.
- Делайте "жадное импортирование". Откажитесь от
IMPORTRANGEдля колонок или небольших диапазонов. Лучше разобрать результат импорта уже в текущей Таблице - По возможности, не импортируйте данные из листов "новогодних ёлок". Каша, полученная от "накидывания" и "раскрашивания" данных может сломать что-то, что будет после импорта. Создавайте наборы данных, которые однозначно показывают, какой тип на листе, сколько точек после запятой, это дата или дата-время.
- Если вы ожидаете далее рассчитывать в
QUERY, то необходимо позаботиться о консистентности типов данных в колонках - формула не любит смешивание типов.
Ответ
Разберитесь с типами данных в источнике.
Формулы на картинках отсюда https://docs.google.com/spreadsheets/d/1i9KfzdVhB42XS4_vI3XLE8lUpiF1-IB9cP_zl5yVj-M/edit?usp=sharing