Excel возвращает #ЗНАЧ! ошибка, когда я использую ДВССЫЛ
Я новичок в Excel и пишу формулу Excel, которая подсчитывает состояние сотрудников за весь год (например: сколько раз у него был отпуск, пожертвованный буквой V) и мне удалось написать эту формулу
=SUMPRODUCT( COUNTIFS( INDIRECT("'" & {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"} & "'!$A$11:$A$38"), $B2, INDIRECT("'" & {"JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"} & "'!$B$11:$AF$38"), "V" ) )
и в листе сотрудников он начинается с идентификатора, затем имени, а затем пустой ячейки для штата (как показано в образце таблицы ниже).
в этом коде я ищу состояние V (отпуск) для первого сотрудника, расположенного в ячейке B2. У меня 12 рабочих листов по месяцам. Каждый лист аналогичен другому расположением фамилий сотрудников и ячеек для состояния дня.
В листе каждого месяца имена идут от А11 до А38. А территория штатов от B11 до AF38 (в этом квадрате). Пример таблицы ниже.
В листе статистики сотрудников от А2 до А23 указаны идентификаторы. А от B2 до B23 — это имена. А от C2 до V24 — это область для каждого штата, конечно, каждый сотрудник занимает ряд.
Я добавил тег имени (или как он там называется) из менеджера имен. использовать их в формуле, как вы можете видеть. но когда я использую формулу, в ячейке написано #ЗНАЧЕНИЕ!. Я попытался оценить формулу и посмотреть, где находится ошибка, но так и не смог увидеть, в чем проблема. Далее мне удалось сказать, что проблема связана с функцией (ДВССЫЛ), но я не знаю, как ее решить.
Ответы 1
В вашей формуле много проблем.
Во-первых, вы просто не можете поместить в COUNTIFS два диапазона критериев разной размерности. то есть диапазон имен равен A11:A38 (одномерный массив), а диапазон состояний равен
B11:AF38 (двумерный массив). два диапазона имеют разную форму, и в COUNTIFS это не работает.
Во-вторых, INDIRECT не работает с такими массивами, как {"JAN!A11:A38", FEB!A11:A38"}.
Я считаю, что вместо этого вам нужна 3D-ссылка: https://support.microsoft.com/en-us/office/create-a-3-d-reference-to-the-same-cell-range-on- Multiple-worksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6
Надеюсь, у вас есть Office365.
Потому что с Office365 вы можете использовать 3D-ссылку вместе с VSTACK, чтобы объединить данные за 12 месяцев в одну таблицу:
=VSTACK(JAN:FEB!A11:C13)
Затем вы можете использовать приведенную ниже информацию для подсчета отпусков для «Джона»:
=SUM(--(FILTER(B2:C7,A2:A7 = "John","") = "V")).
=FILTER(B2:C7,A2:A7 = "John","") отфильтровывает данные по запросу «Джон».
=SUM(--(FILTER(B2:C7) = "V") подсчитайте количество состояний = «V».
Отредактировано:
Для упрощения реализации вы можете поставить =VSTACK(JAN:FEB!$A$11:$C$38) в диспетчере имен. (Я назвал его «Консолидированный»). (Убедитесь, что вы используете абсолютную ссылку, т. е. не опускаете знак доллара).
Чтобы получить имена всех сотрудников за 12 месяцев, используйте =TAKE(Consolidated,,1). Чтобы получить все столбцы штатов за 12 месяцев, используйте =DROP(Consolidated,,1).
Итак, чтобы отфильтровать данные по сотруднику, используйте =FILTER(DROP(Consolidated,,1),TAKE(Consolidated,,1)=B2,"")
У вас нет проблем с использованием=VSTACK(JAN:DEC!A11:AF38) для консолидации данных в разных таблицах? тебе просто нужно объяснение FILTER()? @ZiadBarnawi
Я просто хочу знать, что мне нужно написать, чтобы получить общую формулу, которая работает для каждого сотрудника в списке сотрудников. Судя по тому, что я только что прочитал, VSTACK вернет листы за 12 месяцев один под другим. но я не знаю, как записать условие в (включающей) части фильтра. потому что условием является то, что оно соответствует имени сотрудника и имеет состояние работы в той же строке, что и имя сотрудника. и я не знаю, как узнать длину 12 массивов, расположенных один над другим, чтобы динамически привести его в состояние
=LET(stacked,VSTACK(JAN:FEB!A11:C38),names,TAKE(stacked,,1),states,DROP(stacked,,1),included,names=B2,filtered,FILTER(states,included,""),SUM(--(filtered = "V"))). Вы можете использовать =TAKE(stacked,,1), чтобы получить первый столбец (столбец имени) в VSTACK. =DROP(stacked,,1) чтобы получить столбцы состояний в VSTACK. ваше «включенное» условие будет TAKE(stacked,,1)=B2ОК, эта формула работает для каждого сотрудника, кроме последнего, в каждой ячейке его состояний указано 12, хотя они должны быть 0. Есть идеи, почему это так?
Что возвращает =LET(stacked,VSTACK(JAN:FEB!A11:C38),names,TAKE(stacked,,1),states,DROP(stacked,,1),included,names=B23,filtered,FILTER(states,included,""),filtered)? (это возвращает отфильтрованные строки для сотрудника B23)
Просто в написанных вами диапазонах была ошибка. Я исправил это, и теперь оно работает. Спасибо за помощь
не могли бы вы подробно рассказать мне, как использовать эту формулу и что означает каждый параметр, чтобы я мог включить ее в свои таблицы?