Поиск диапазона счетчика Excel, возвращающий #N / A

Я использую Excel для поиска нескольких дней (мои диапазоны) в расписании в течение нескольких часов работал (мой HLOOKUP читает значение под номером dept) множественными членами штата для различных отделов.

Я придумал следующую формулу, которая работает для большей части моего листа, за исключением случаев, когда значение ячейки поиска (AB9) не найдено в первом диапазоне ($B$9:$E$10). Для других это работает (если значение не найдено в более поздних диапазонах, оно не возвращает #N / A), только если значение не найдено в первом ряде.

=SUM(IF(COUNTIF($B:$E,AB9),HLOOKUP(AB9,$B:$E,2,FALSE),""))+(IF(COUNTIF($F:$I,AB9),HLOOKUP(AB9,$F:$I,2,FALSE),""))+(IF(COUNTIF($J:$M,AB9),HLOOKUP(AB9,$J:$M,2,FALSE),""))+(IF(COUNTIF($N:$Q,AB9),HLOOKUP(AB9,$N:$Q,2,FALSE),""))+(IF(COUNTIF($R:$U,AB9),HLOOKUP(AB9,$R:$U,2,FALSE),""))+(IF(COUNTIF($V:$Y,AB9),HLOOKUP(AB9,$V:$Y,2,FALSE),""))

может ли кто-нибудь объяснить мне, почему это не удается, и если я не должен использовать COUNTIF для этого, что еще я должен использовать и почему.

В идеале у меня была бы моя формула, ищущая уникальные номера (отделы) в диапазонах, сообщая о них, а затем ниже перечисляя часы работы для каждого, но это немного выше моего опыта (в настоящее время)! 😉

9
задан HaydnWVN
17.12.2022 0:17 Количество просмотров материала 3118
Распечатать страницу

2 ответа

формулы Excel обрабатывают специальные значения ошибок #N/A и #DIV/0 иначе, чем другие (реальные) значения. В зависимости от формул, которые вы используете, они возвращаются в результате независимо от того, где они отображаются. Таким образом, ваш HLOOKUP возвращает #N/a, когда он не может найти значение, и даже если вы пытаетесь пропустить оценку HLOOKUP в операторе IF и вернуть только"", Excel по-прежнему оценивает его и возвращает #N/A, как только он будет оценен.

также, потому что вы работаете при использовании значений, а не текста, возвращаемое значение должно быть равно 0, а не "".

вместо внешнего оператора IF в каждой сумме следует использовать IFNA следующим образом:

IFNA(HLOOKUP(AB9,$B:$E,2,FALSE),0)

формула IFNA возвращает значение в первом параметре, если он действительно работает и создает значение, и возвращает"", если он не работает.

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

Итак, если я читаю, что вы пытаетесь сделать правильно, вся формула должна быть:

=SUM(IFNA(HLOOKUP(AB9,$B:$E,2,FALSE),0),IFNA(HLOOKUP(AB9,$F:$I,2,FALSE),""),IFNA(HLOOKUP(AB9,$J:$M,2,FALSE),""),IFNA(HLOOKUP(AB9,$N:$Q,2,FALSE),""),IFNA(HLOOKUP(AB9,$R:$U,2,FALSE),""),IFNA(HLOOKUP(AB9,$V:$Y,
1
отвечен cathoo 2022-12-18 08:05

мы с коллегой придумали лучшее решение, чем вложенность всех IF вместе.

мы придумали =SUMIF($B7:$AI7,AB9,$B8:$AI8) который работает гораздо лучше, так как нет необходимости в нескольких вложенных IF ' s.

я расширил его еще больше позволяет отдел по умолчанию и включить S (больной) и H (праздник) время, которое добавляет к общему количеству часов для отдела по умолчанию.

=SUMIF($B7:$AI7,AL7,$B8:$AI8)+IF($AJ7=AL7,SUMIF($B7:$AI7,"S",$B8:$AI8)+SUMIF($B7:$AI7,"H",$B7:$AI7),0)

Я иду дальше, как будет включать почасовые ставки и сверхурочные в следующем. Пожелать мне удачи!

0
отвечен HaydnWVN 2022-12-18 10:22

Постоянная ссылка на данную страницу: [ Скопировать ссылку | Сгенерировать QR-код ]

Ваш ответ

Опубликуйте как Гость или авторизуйтесь

Имя
Мы используем cookie. Это позволяет нам анализировать взаимодействие посетителей с сайтом и делать его лучше. Продолжая пользоваться сайтом, вы соглашаетесь с использованием файлов cookie. Политика конфиденциальности
Согласен
Вверх