#Ошибка не верная ссылка на ячейку! при работе со сводными таблицами

Ответ был переведён автоматически. В результате перевода возможны грамматические ошибки или необычные формулировки. 

Здравствуйте @Глеб Моисеев, 

Спасибо за подробное описание проблемы и предоставленные скриншоты.  Поведение, которое вы наблюдаете, является ожидаемым и соответствует тому, как функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ изначально предназначена для работы. 

Согласно документации Microsoft, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает данные только по тем элементам, которые в текущий момент существуют и отображаются в сводной таблице. 

«Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает видимые данные из сводной таблицы».  (Источник: документация Microsoft по функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ) 

Это означает, что при применении среза, фильтра отчёта, или фильтрации исходных данных (например, по полю «Название курса»), некоторые значения в поле оценка студента NPS (1–10) могут полностью отсутствовать в структуре сводной таблицы. 

Когда элемент сводной таблицы (например, оценка 10 или оценка 1) отсутствует после применения фильтрации: 

• соответствующий столбец или элемент удаляется из сводной таблицы; 
• функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ не может определить запрошенный элемент; 
• Excel возвращает ошибку #ССЫЛКА! (Неверная ссылка на ячейку), а не значение 0. 

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

Важно учитывать, что при работе со сводными таблицами: 

• «значение равно 0» и «значение не существует» - это разные ситуации; 
• функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ не возвращает 0, если элемент отсутствует; 
• в таком случае функция возвращает ошибку. 

Для расчётов показателей, таких как NPS, отсутствующие значения необходимо явно обрабатывать как нулевые, иначе выполнение вычислений становится невозможным. 

Для вашего сведения: Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Корректный и поддерживаемый подход 

Правильный и поддерживаемый Microsoft подход заключается в том, чтобы преобразовывать отсутствующие элементы сводной таблицы в нулевые значения до выполнения расчётов.  Это достигается оборачиванием каждого вызова функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в функцию ЕСЛИОШИБКА(…;0). 

 =( 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[10]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[9]" 
    ); 
    0 
  ) 
) 
- 
( 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[6]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[5]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[4]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[3]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[2]" 
    ); 
    0 
  ) 
  + 
  ЕСЛИОШИБКА( 
    ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
      "[Measures].[Число элементов в столбце оценка студента NPS]"; 
      $A$3; 
      "[Таблица1].[оценка студента NPS]"; 
      "[Таблица1].[оценка студента NPS].&[1]" 
    ); 
    0 
  ) 
) 
) 
/ 
ЕСЛИОШИБКА( 
  ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ( 
    "[Measures].[Число элементов в столбце оценка студента NPS]"; 
    $A$3 
  ); 
  0 
) 

Пояснение 

При применении среза или фильтра некоторые оценки NPS (например, 10 или 1) могут полностью отсутствовать в сводной таблице.  Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ в этом случае возвращает ошибку #ССЫЛКА!, а не значение 0. 

Оборачивание каждого вызова в ЕСЛИОШИБКА(…;0) преобразует ситуацию:  «элемент не существует» > 0, 

что предотвращает возникновение ошибки и позволяет корректно выполнять расчёт NPS при любых фильтрах и срезах. 

Надеюсь, это объяснение и предложенное решение помогут устранить проблему. Если потребуется дополнительная помощь, пожалуйста, оставьте комментарий. 

Если ответ оказался полезным, пожалуйста, нажмите «Принять ответ» и поставьте голос «за». Если у вас есть дополнительные вопросы по этому ответу, нажмите «Комментарий».

Примечание: Чтобы получать уведомления по электронной почте о новых сообщениях в этой теме, выполните шаги из нашей документации для включения уведомлений по электронной почте.