Блог

Главная - Вопросы по программированию - Google Sheet arrayformula, ссылающаяся на предыдущую строку того же столбца: циклическая ссылка на ошибку

Google Sheet arrayformula, ссылающаяся на предыдущую строку того же столбца: циклическая ссылка на ошибку

#google-sheets #google-sheets-formula

Вопрос:

Попытка создать формулу массива, ссылающуюся на предыдущую строку. Продолжайте получать ошибку #REF:

 Circular dependency detected. To resolve with iterative calculation, see File > Spreadsheet Settings.

Видел много других постов с аналогичным вопросом, но ни один из них, казалось, не решил его для меня.

Пользователь вводит данные в колонку A, а в колонке B должно отображаться начало/занято/конец. Например.

 |A              | B            |
|---------------|------------- |
|[heading A]    | [heading B]  |
|start          | start        |
|something      | busy         |
|something else | busy         |
|end            | end          |
|something      |              |
|something else |              |
|start          | start        |
|something      | busy         |
|end            | end          |
|start          | start        |
|end            | end          |
|...            | ...          |

Попробовал в B2 и получил ошибку круговой ссылки:

 =arrayformula(if((A2:A="start") (A2:A="end"),A2:A, if((A1:A="start") (B1:B="busy"),"busy","") ))

=arrayformula(if((A2:A="start") (A2:A="end"),A2:A, if((A1:A="start") (offset(B2:B,-1,0)="busy"),"busy","") ))

Это отображается busy в первой строке после начальных строк (из-за A1:A="start" теста), но не для других «занятых» строк (так что: indirect("B"amp;row(B2:B)-1)="busy" тест не работает).

 =arrayformula(if((A2:A="start") (A2:A="end"),A2:A, if((A1:A="start") (indirect("B"amp;row(B2:B)-1)="busy"),"busy","") ))

Для этого, когда я заменяю "" на "B"amp;row(B2:B)-1 , он отображает косвенную ссылку на ячейку, как я и ожидал, например, в B4 нем отображается «B3».

Предложения, как заполнить начальные/занятые/конечные значения с помощью arrayformula?

Ответ №1:

Попробуйте это в камере B1 :

=arrayformula({"[Heading B]";if(A2:A="start","start",if(A2:A="end","end",if(COUNTIFS(A2:A,"start",ROW(A2:A),"<="amp;ROW(A2:A))=COUNTIFS(A2:A,"end",ROW(A2:A),"<="amp;ROW(A2:A)),,"busy")))})

введите описание изображения здесь

Примечания:

Я проверяю наличие следующих условий:

А) последовательность слова «начать»

=arrayformula(if(A2:A<>"",COUNTIFS(A2:A,"start",ROW(A2:A),"<="amp;ROW(A2:A)),))

Б) последовательность слова «конец»:

=arrayformula(if(A2:A<>"",COUNTIFS(A2:A,"end",ROW(A2:A),"<="amp;ROW(A2:A)),))

В) являются ли А) и Б) одинаковыми для каждой строки. Там, где они одинаковы, элемент является либо «концом», закрывающим «начало», либо «чем-то другим», что нам нужно игнорировать.

COUNTIFS(A2:A,"start",ROW(A2:A),"<="amp;ROW(A2:A)) = COUNTIFS(A2:A,"end",ROW(A2:A),"<="amp;ROW(A2:A))

D) Окончательная логика идентифицирует «что-то еще «после» конца » и использует его:

=arrayformula(if(A2:A="start","start",if(A2:A="end","end",if(xxx,,"busy")))) где xxx — является ли пункт C) выше истинным.

введите описание изображения здесь

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

1. да, это работает! Совсем другой маршрут, чем я ожидал. С нетерпением жду заметок. Легко ли объяснить, почему более очевидный маршрут терпит неудачу?

2. Ваша начальная формула в col B ссылается на col B, так что это вызовет круговую проблему.

3. отличная работа! Таким образом, ключевой частью, по-видимому, является countifs ограничение диапазона, на который нужно смотреть. Все еще не уверен, что именно было причиной круговой ссылки.

4. Да, если количество запущенных строк «начало» равно количеству запущенных строк «конец», это либо «конец», закрывающий «начало», либо условие «что-то», которое вы хотите игнорировать.: -)

Метки: ,