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

#google-sheets #google-sheets-query

#google-таблицы #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)

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

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

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

Редактировать: обновленная формула теперь работает идеально, но я пытаюсь добавить в запрос еще один столбец (E), и когда я добавляю его, я получаю ошибку, нет Col7 Как я могу добавить столбец 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(20amp;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)amp;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", ""))

Ответ №1:

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

 =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(20amp;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)amp;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", ""))

Ответ №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(20amp;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)amp;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(20amp;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)amp;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", ""))

1. Я получил тот же результат;(

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

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

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

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

Вопрос:

Ответ №1:

Ответ №2:

Комментарии:

Вам также может понравиться

Создание пулов соединений в Glassfish

Версия версии продукта Android для сборки NoClassFound

Настройка условий доступа к разным переменным для разных хостов