Укладка нескольких формул запроса вызывает «Литература массива», в которой отсутствуют значения для одной или нескольких строк »

Я пытаюсь запустить несколько запросов, где он добавляет строки, где если F пуст, то он пропускает эти строки, затем снова запускает запрос и заменяет строку F на строку G.

У меня есть гораздо более сложная формула, делающая то же самое, но больше вариаций, столбцы D (пустое или нет) - E и F (пустое или нет) - G

Работает следующая формула

=SORT(ArrayFormula({
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA,
  "select A, B, C, D, F, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, E, F, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, D, G, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc")); 
 IFERROR(QUERY('6 Star Gear Sets (Hidden)'!A6:AA, 
  "select A, B, C, E, G, H, N, I, J, K, L, M 
   where A != ' ' AND D IS NULL AND F IS NULL order by F desc"))
 }),5,FALSE)

Но моя более простая версия той же формулы возвращает ошибку

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL")); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"))
 }),7,FALSE)

Ошибка: В ARRAY_LITERAL в массиве Array Literal отсутствовали значения для одной или нескольких строк.

Ссылка на копию листов google выглядит следующим образом:

https://docs.google.com/spreadsheets/d /1s6spmfCVP6P331Zr9xfE9kiei3Wly6FRQCdA8Y3e7tA /редактировать? УСП = drivesdk .


Дополнительный вопрос

ОБНОВЛЕННАЯ ФОРМУЛА исходной рабочей формулы

=SORT( ArrayFormula( { IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, F, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, F, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, G, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc"),{"","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, G, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NULL order by F desc"),{"","","","","","","",""}) } ) ,5,FALSE,4,FALSE)

WORK AROUND

В 6 Star Gear Sets (Скрытый) лист ссылается на формулы, есть строка с столбцом «zzzzz», и оба F и G пусты. Когда эта строка вызывается с использованием вышеуказанной ArrayFormula, я использую условный формат, чтобы скрыть все строки с помощью «zzzzz».

Вопрос

Если я удалю строку с «zzzzz» в столбце A из скрытого листа, я получаю сообщение об ошибке .. почему?

ДОПОЛНИТЕЛЬНАЯ ИНФОРМАЦИЯ

Прервать формулу в отдельный QUERY forumlas и удалить IFERROR, показывает, что третий запрос пуст и возвращает ошибку # N /A, но четвертый запрос также не возвращает никаких результатов и не дает такой ошибки. Я полагал, что заявление IFERROR справится с этим, но безрезультатно.

UPDATE

Найдено 4-й запрос не возвращается пустым. Он фактически возвращает пустые ячейки. Что опять же добавляет к списку вопросов. Потому что у меня есть функция IFERROR и where A != ' ' Я думал, что с этим справится. Разве это не там, где A NOT MATCH пусто?

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

ОБНОВЛЕНИЕ 2

По какой-то причине я сразу забыл часть о количестве пустых аргументов в инструкции IFERROR, чтобы соответствовать количеству столбцов, на которые ссылаются.

Тем не менее, = SORT больше не работает корректно, так как теперь есть пустая строка в верхней части новой таблицы, и у меня все еще нет хорошего объяснения того, почему четвертый запрос заполняет таблицу пустыми строками.

ОБНОВЛЕНИЕ 3

В столбце A моего листа «6 Star Gear Sets» у меня есть формула, которая показывает все в том порядке, в котором они появляются, если столбец B не пуст. По какой-то причине пустая строка, вставленная из-за = IFERROR (,), на самом деле не считается пустой.

В дополнение, my = SORT заказывает все в порядке убывания столбцом F, а затем E. Однако он рассматривает F и E в вставленной пустой строке как строку с наивысшим значением.

ОБНОВЛЕННАЯ ФОРМУЛА

=SORT( ArrayFormula( { IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, F, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NOT NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, F, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NOT NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, D, G, H, N, I, J, K, L, M where A != ' ' AND D IS NOT NULL AND F IS NULL order by F desc"),{"","","","","","","","","","","",""}); IFERROR( QUERY( '6 Star Gear Sets (Hidden)'!A6:AA, "select A, B, C, E, G, H, N, I, J, K, L, M where A != ' ' AND D IS NULL AND F IS NULL order by F desc"),{"","","","","","","","","","","",""}) } ) ,5,FALSE,4,FALSE)

ОБНОВЛЕННАЯ ССЫЛКА

Ниже приведена обновленная копия моей таблицы.

https://docs.google.com/spreadsheets/d /17Ev1_Scobnl16H9TuTrHK_es7fZGkMqHyvmpWWWVbow /редактировать? УСП = drivesdk

4 голоса | спросил Melly Lilly 2 Jam1000000amTue, 02 Jan 2018 08:15:49 +030018 2018, 08:15:49

2 ответа


4

При укладке массивов по вертикали они должны иметь одинаковое количество столбцов. В случае ошибки query выводит одиночную ячейку с #REF или # N /A или другим сообщением , Вы помещаете вокруг него обертку iferror, но это делает ее так, чтобы выход был одним из пустой . Проблема в том, что это одна ячейка, и вам нужно 8 столбцов для соответствия другому массиву.

Решение: добавьте пустую строку с 8 ячейками в качестве второго аргумента iferror, который будет использоваться в случае ошибок.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

В частности, ошибка возникает из-за отсутствия листа с названием «5 Star Gear Clubs (Hidden)». Предложение: когда формула выдает ошибку, введите ее части в отдельные ячейки (без обертки iferror), чтобы увидеть, что они делают.

ответил user135384 2 Jpm1000000pmTue, 02 Jan 2018 19:25:00 +030018 2018, 19:25:00
0

Введение

Я решил проблемы, которые были найдены в UPDATE 1 по 3 в исходном вопросе. Тем не менее, я чувствую, что формула немного запутана и что должен быть гораздо более простой ответ.

ОБЪЯСНЕНИЕ

Я добавил функцию IF и INDEX в формулу, которая решает предыдущую формулу, добавляющую ко многим пустым строкам, а также недействительные пустые строки, что вызывает проблему с сортировкой и другими формулами, проверяющими, является ли неверная пустая строка фактически пустой. Я добавил 1 функцию IF и INDEX для каждого запроса QUERY.

Я также изменил инструкции IFERROR на создание пустых ячеек, но вместо этого ссылаюсь на ячейки, которые являются истинными пробелами.

Оператор IF использует часть исходной формулы для проверки результатов формулы QUERY. Функция INDEX ограничивает проверку только первой строкой. Затем проверка запроса сравнивается с 12 пустыми столбцами и одной строкой.

IF ИСТИНА: Он просто ссылается на истинную пустую строку вместо создания своих собственных пустых строк, создание пустых строк заканчивается тем, что они не являются истинными пустыми строками, вызывая все проблемы до.

IF FALSE: Он запускает исходную формулу, добавляя в таблицу соответствующие ячейки из справочного листа.

БОЛЬШОЙ ЭФФЕКТ

Это не проблема для моих листов, но кто-то пытается реплицировать подобную формулу, может заметить, что каждый раз, когда запрос добавляет истинную пустую строку, вы не сможете ничего добавить в этих ячейках, потому что эта формула будет вызывать ошибку для пытаясь записать пустую ячейку в эту часть таблицы.

Я попробовал использовать функцию FILTER, чтобы возвращать только количество строк, которые фактически содержат данные в ссылочном листе, но это вызвало много ОЖИДАЕМЫХ ошибок.

ОБНОВЛЕННАЯ ФОРМУЛА

=SORT(
ArrayFormula(
    {
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, D, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NOT NULL AND F IS NOT NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, D, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NOT NULL AND F IS NOT NULL"
            )
        ); 
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NOT NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NOT NULL"
            )
        ); 
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, F, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, F, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NOT NULL AND E IS NULL"
            )
        );
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, D, G, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NOT NULL AND F IS NULL"
                    ),
                1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, D, G, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NOT NULL AND F IS NULL"
            )
        );
        IF(
            IFERROR(
                INDEX(
                    QUERY(
                        '6 Star Gear Sets (Hidden)'!A6:AA, 
                        "select A, B, C, E, G, H, N, I, J, K, L, M 
                        where A != ' ' AND D IS NULL AND F IS NULL"
                    ),
                    1,0
                ),
                {
                    '6 Star Gear Sets (Hidden)'!A1001,
                    '6 Star Gear Sets (Hidden)'!B1001,
                    '6 Star Gear Sets (Hidden)'!C1001,
                    '6 Star Gear Sets (Hidden)'!D1001,
                    '6 Star Gear Sets (Hidden)'!E1001,
                    '6 Star Gear Sets (Hidden)'!F1001,
                    '6 Star Gear Sets (Hidden)'!G1001,
                    '6 Star Gear Sets (Hidden)'!H1001,
                    '6 Star Gear Sets (Hidden)'!I1001,
                    '6 Star Gear Sets (Hidden)'!J1001,
                    '6 Star Gear Sets (Hidden)'!K1001,
                    '6 Star Gear Sets (Hidden)'!L1001
                }
            )
            ={"","","","","","","","","","","",""},
            {
                '6 Star Gear Sets (Hidden)'!A1001,
                '6 Star Gear Sets (Hidden)'!B1001,
                '6 Star Gear Sets (Hidden)'!C1001,
                '6 Star Gear Sets (Hidden)'!D1001,
                '6 Star Gear Sets (Hidden)'!E1001,
                '6 Star Gear Sets (Hidden)'!F1001,
                '6 Star Gear Sets (Hidden)'!G1001,
                '6 Star Gear Sets (Hidden)'!H1001,
                '6 Star Gear Sets (Hidden)'!I1001,
                '6 Star Gear Sets (Hidden)'!J1001,
                '6 Star Gear Sets (Hidden)'!K1001,
                '6 Star Gear Sets (Hidden)'!L1001
            },
            QUERY(
                '6 Star Gear Sets (Hidden)'!A6:AA, 
                "select A, B, C, E, G, H, N, I, J, K, L, M 
                where A != ' ' AND D IS NULL AND F IS NULL"
            )
        )
    }
),
5,FALSE,4,FALSE,3,FALSE
)
ответил Melly Lilly 4 Jam1000000amThu, 04 Jan 2018 04:38:03 +030018 2018, 04:38:03

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

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132