Google Таблицы Как отсортировать этот запрос по датам?

У меня есть этот запрос, который приносит информацию с некоторых вкладок

=QUERY({
IFERROR(QUERY(General!A2:P,"select 'General',K,G,H,I,J where not G contains 'N/A' label 'General''' ",0),{"","","","","",""});
IFERROR(QUERY(Salud!A2:P,"select 'Salud',K,G,H,I,J where not G contains 'N/A' label 'Salud''' ",0),{"","","","","",""});
IFERROR(QUERY(Parejas!A2:P,"select 'Parejas',K,G,H,I,J where not G contains 'N/A' label 'Parejas''' ",0),{"","","","","",""});
IFERROR(QUERY('Paz Hogar'!A2:P,"select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar''' ",0),{"","","","","",""});
IFERROR(QUERY(Sustento!A2:P,"select 'Sustento',K,G,H,I,J where not G contains 'N/A' label 'Sustento''' ",0),{"","","","","",""});
IFERROR(QUERY(Hijos!A2:P,"select 'Hijos',K,G,H,I,J where not G contains 'N/A' label 'Hijos''' ",0),{"","","","","",""});
IFERROR(QUERY(Educacion!A2:P,"select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion''' ",0),{"","","","","",""});
IFERROR(QUERY(Otros!A2:P,"select 'Otros',K,G,H,I,J where not G contains 'N/A' label 'Otros''' ",0),{"","","","","",""});
IFERROR(QUERY(NA!A2:P,"select 'NA',K,G,H,I,J where not G contains 'N/A' label 'NA''' ",0),{"","","","","",""});
IFERROR(QUERY(a!A2:P,"select 'a',K,G,H,I,J where not G contains 'N/A' label 'a''' ",0),{"","","","","",""})},
"select * where Col1 is not null and not Col1 contains 'NA' order by Col3 ",0)

Моя проблема в том, что не правильно сортирует по датам

Google Таблицы Как отсортировать этот запрос по датам?

Как я могу это сделать?

Вот демонстрационный лист

Редактировать: Обновленная формула теперь работает идеально, Но я пытаюсь добавить еще один столбец (E) в запрос, и когда я его добавляю, я получаю ошибку, нет Col7 Как я могу добавить Col E в 4-ю позицию формулы? Я был часы, без везения ;(

 =ARRAYFORMULA(SUBSTITUTE(QUERY(QUERY({QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0),
 ARRAYFORMULA(DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0)
, 2), MONTH(MID(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where E <> '-'  label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where E <> '-'  label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where E <> '-'  label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where E <> '-'  label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where E <> '-'  label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where E <> '-'  label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where E <> '-'  label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where E <> '-'  label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where E <> '-'  label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where E <> '-'  label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0),2)))}, 
 "select Col1,Col2,Col7,Col4,Col5,Col6 order by Col7", 0), 
 "format Col3 'dd-mmm-yy'", 0), "30-Nov-20", ""))
google-sheets google-sheets-query
09.04.2019 05:31
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
0
69
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

попробуйте так:

=QUERY(QUERY({QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0),
 ARRAYFORMULA(DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0)
, 2), MONTH(MID(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0),2)))}, 
 "select Col1,Col2,Col7,Col4,Col5,Col6 order by Col7", 0), 
 "format Col3 'dd-mmm-yy'", 0)

исправление пустой даты:

=ARRAYFORMULA(SUBSTITUTE(QUERY(QUERY({QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0),
 ARRAYFORMULA(DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0)
, 2), MONTH(MID(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(General!A2:P,     "select 'General',K,G,H,I,J   where not G contains 'N/A' label 'General'''",   0), {"","","","","",""});
 IFERROR(QUERY(Salud!A2:P,       "select 'Salud',K,G,H,I,J     where not G contains 'N/A' label 'Salud'''",     0), {"","","","","",""});
 IFERROR(QUERY(Parejas!A2:P,     "select 'Parejas',K,G,H,I,J   where not G contains 'N/A' label 'Parejas'''",   0), {"","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,H,I,J where not G contains 'N/A' label 'Paz Hogar'''", 0), {"","","","","",""});
 IFERROR(QUERY(Sustento!A2:P,    "select 'Sustento',K,G,H,I,J  where not G contains 'N/A' label 'Sustento'''",  0), {"","","","","",""});
 IFERROR(QUERY(Hijos!A2:P,       "select 'Hijos',K,G,H,I,J     where not G contains 'N/A' label 'Hijos'''",     0), {"","","","","",""});
 IFERROR(QUERY(Educacion!A2:P,   "select 'Educacion',K,G,H,I,J where not G contains 'N/A' label 'Educacion'''", 0), {"","","","","",""});
 IFERROR(QUERY(Otros!A2:P,       "select 'Otros',K,G,H,I,J     where not G contains 'N/A' label 'Otros'''",     0), {"","","","","",""});
 IFERROR(QUERY(NA!A2:P,          "select 'NA',K,G,H,I,J        where not G contains 'N/A' label 'NA'''",        0), {"","","","","",""});
 IFERROR(QUERY(a!A2:P,           "select 'a',K,G,H,I,J         where not G contains 'N/A' label 'a'''",         0), {"","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0),2)))}, 
 "select Col1,Col2,Col7,Col4,Col5,Col6 order by Col7", 0), 
 "format Col3 'dd-mmm-yy'", 0), "30-Nov-20", ""))

у меня такой же результат ;(

— SATH59 09.04.2019 06:06

Я добавил демонстрационный лист для целей тестирования, так как моя информация является конфиденциальной.

— SATH59 09.04.2019 07:10

@ SATH59 попробуй сейчас, я немного поправил

— player0 09.04.2019 11:18

это работает, не могли бы вы немного объяснить мне формулу, чтобы лучше понять?

— SATH59 10.04.2019 04:46

Когда у меня есть пустая ячейка в столбце G на любой из моих вкладок, на вкладке потока отображается как 30-Nov-20, мне бы хотелось, чтобы это было пусто, есть ли способ исправить?

— SATH59 10.04.2019 05:22

Формула @ SATH59 просто изолирует столбец даты от остальной части таблицы и преобразует даты в действительные даты вместо текстовых строк, а затем просто объединяет все в желаемый результат. Я обновил ответ, указав исправление для «30 ноября 20».

— player0 10.04.2019 13:50

После вашей благодарной помощи я отредактировал формулу в сообщении. Обновленная формула теперь работает отлично, но я пытаюсь добавить еще один столбец (E) в запрос, и когда я добавляю его, я получаю сообщение об ошибке, нет Col7 Как я могу добавить Col E на 4-й позиции в таблице? Я был часы, без везения ;(

— SATH59 11.04.2019 05:26

и, может быть, приказ Col E также будет в порядке

— SATH59 11.04.2019 05:31

попробуйте изменить все вхождения Col7 на Col8 и сообщите об ошибках, если они есть, и вставьте мне здесь формулу, которую вы используете: docs.google.com/spreadsheets/d/…

— player0 11.04.2019 11:17

Я изменил Col7 на Col8 ... Я вставил формулу в листы, спасибо

— SATH59 12.04.2019 08:21
09.04.2019 05:47
Ответ принят как подходящий

это должно работать:

=ARRAYFORMULA(SUBSTITUTE(QUERY(QUERY({QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "where Col1 is not null and not Col1 contains 'NA'", 0), DATE(20&RIGHT(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 2), MONTH(MID(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 4, 3)&1), LEFT(QUERY({
 IFERROR(QUERY(    General!A2:P, "select 'General',K,G,E,H,I,J   where not E contains '-'  label 'General'''",   0), {"","","","","","",""});
 IFERROR(QUERY(      Salud!A2:P, "select 'Salud',K,G,E,H,I,J     where not E contains '-'  label 'Salud'''",     0), {"","","","","","",""});
 IFERROR(QUERY(    Parejas!A2:P, "select 'Parejas',K,G,E,H,I,J   where not E contains '-'  label 'Parejas'''",   0), {"","","","","","",""});
 IFERROR(QUERY('Paz Hogar'!A2:P, "select 'Paz Hogar',K,G,E,H,I,J where not E contains '-'  label 'Paz Hogar'''", 0), {"","","","","","",""});
 IFERROR(QUERY(   Sustento!A2:P, "select 'Sustento',K,G,E,H,I,J  where not E contains '-'  label 'Sustento'''",  0), {"","","","","","",""});
 IFERROR(QUERY(      Hijos!A2:P, "select 'Hijos',K,G,E,H,I,J     where not E contains '-'  label 'Hijos'''",     0), {"","","","","","",""});
 IFERROR(QUERY(  Educacion!A2:P, "select 'Educacion',K,G,E,H,I,J where not E contains '-'  label 'Educacion'''", 0), {"","","","","","",""});
 IFERROR(QUERY(      Otros!A2:P, "select 'Otros',K,G,E,H,I,J     where not E contains '-'  label 'Otros'''",     0), {"","","","","","",""});
 IFERROR(QUERY(         NA!A2:P, "select 'NA',K,G,E,H,I,J        where not E contains '-'  label 'NA'''",        0), {"","","","","","",""});
 IFERROR(QUERY(          a!O2:P, "select 'a',K,G,E,H,I,J         where not E contains '-'  label 'a'''",         0), {"","","","","","",""})},
 "select Col3 where Col1 is not null and not Col1 contains 'NA'", 0), 2))}, 
 "select Col1,Col2,Col8,Col4,Col5,Col6 order by Col8", 0), 
 "format Col3 'dd-mmm-yy'", 0), "30-Nov-20", ""))
14.04.2019 02:25

Другие вопросы по теме

Группировать данные по разным именам с суммой их значений
Фильтровать ячейки, которые частично совпадают
Создание формулы, которая добавляет значение на основе данных в других ячейках
Умножить выбранное значение из ЗАПРОСА
Google Таблицы показывают только одну уникальную строку с использованием Query
Запрос Google Sheets - как сопоставить частичную строку
Как сделать запрос
Нормализовать данные в Google Sheets - ПЕРЕСТАВИТЬ несколько столбцов Fill Down
Невозможно импортировать данные с веб-страницы в таблицы Google
Google Таблицы - количество дней с последней даты диапазон ячеек

Похожие вопросы

Можно ли использовать Google Sheets в качестве «многопользовательской» базы данных?
Google Sheets VLOOKUP возвращает несколько значений
Как определить диапазон на нескольких листах с помощью COUNTIF?
Удалить нулевые строки из сводной таблицы Google Sheets
Возвращает уникальный список, который исключает другой список
Замените запятые в функции importdata
Использование Google Sheet в качестве веб-сервиса
Импорт Google Таблиц
Автоматически отправлять удаленные строки по электронной почте с помощью googlescript
Когда пользователь добавляет новую строку в определенный лист, он автоматически добавляет ее в другой лист Google