Связанные типы данных впервые выпущены в Excel для Microsoft 365 июня 2018 г., и по этой мере другие функции могут не распознать их. Это особенно актуально, если вы хотите использовать другие функции для условного определения того, содержит ли ячейка связанный тип данных. В этой статье рассказывается о некоторых обходных решениях, которые можно использовать для определения связанных типов данных в ячейках.
Примечание: Связанные типы данных доступны только для клиентов с несколькими клиентами по всему миру (стандартные Microsoft 365 учетные записи).
Формулы
Вы всегда можете писать формулы, ссылаясь на типы данных. Однако если вы хотите извлечь текст ячейки со связанным типом данных с помощью функции ТЕКСТ, вы получите #VALUE! ошибку "#ВЫЧИС!".
В качестве обходного решения можно использовать функцию FIELDVALUE и указать поле Имя для field_name поля. В следующем примере, если ячейка A1 содержала тип данных акций, формула возвращает имя акции.
=FIELDVALUE(A1;"Имя")
Однако если ячейка A1 не содержит связанного типа данных, функция ПОЛЕ.В ЭТОМ СЛУЧАЕ возвращает ошибку #FIELD!.. Если вы хотите определить, содержит ли ячейка связанный тип данных, воспользуйтесь следующей формулой, в которой функция ЕERROR используется для проверки, возвращает ли функция FIELDVALUE ошибку.
=ЕСЛИ(ЕERROR(FIELDVALUE(A2;"Имя"));"Эта ячейка не имеет связанного типа данных";"Эта ячейка имеет связанный тип данных")
Если формула возвращает ошибку, она возвращает текст "Эта ячейка не имеет связанного типа данных", в противном случае она возвращает текст "Эта ячейка имеет связанный тип данных".
Если вы просто хотите скрыть #FIELD! вы можете использовать:
=ЕСЛИERROR(FIELDVALUE(A1;"Имя");"")
Которая возвращает пустую ячейку при ошибке.
Условное форматирование
Ячейку можно условно отформать в зависимости от того, имеет ли она связанный тип данных. Сначала выберем ячейки, для чего требуется условное форматирование, а затем перейдите на домашнее > Условное форматирование > новое правило > Использовать формулу... Для формулы следует использовать следующее:
=НЕ(ЕERROR(FIELDVALUE(A1;"Имя")))
Ячейка A1 является верхней ячейкой диапазона, который вы хотите высмеять. Затем применим нужный формат.
В этом примере, если ячейка A1 содержит допустимые имена полей для имени "Имя", формула возвращает истина и форматирование будет применено. Если ячейка A1 не содержит связанного типа данных, формула возвращает false и форматирование не применяется. Если вы хотите выделить ячейки, которые не содержат допустимых связанных типов данных, удалите not.
VBA
Существует несколько способов VBA (Visual Basic для приложений, которые можно использовать для определения того, содержит ли ячейка или диапазон связанные типы данных. В первой процедуре используется свойство HasПечатьDataType.
В обоих этих процедурах вам будет предложено выбрать диапазон ячеек для оценки, а затем вернуть окно сообщения с результатами.
Sub IsLinkedDataType()
Dim c As Range
Dim rng As Range
Dim strResults As String
Set rng = Application.InputBox("Select a range to check for linked data types", Type:=8)
For Each c In rng
' Check if the HasRichDataType is TRUE or FALSE
If c.HasRichDataType = True Then
' The cell holds a linked data type
strResults = strResults & c.Text & " - Linked data type" & vbCrLf
Else
strResults = strResults & c.Text & " - Not a linked data type" & vbCrLf
End If
Next c
MsgBox "Your range contains the following details" & vbCrLf & vbCrLf & strResults, vbInformation + vbOKOnly, "Results"
End Sub
В следующей процедуре используется свойство LinkedDataTypeState.
Sub IsLinkedDataTypeState()
Dim c As Range
Dim rng As Range
Dim strResults As String
Set rng = Application.InputBox("Select a range to check for linked data types", Type:=8)
For Each c In rng
' Check if the LinkedDataTypeState is 1 (TRUE) or 0 (FALSE)
If c.LinkedDataTypeState = 1 Then
' The cell holds a linked data type
strResults = strResults & c.Text & " - Linked data type" & vbCrLf
Else
strResults = strResults & c.Text & " - Not a linked data type" & vbCrLf
End If
Next c
MsgBox "Your range contains the following details" & vbCrLf & vbCrLf & strResults, vbInformation + vbOKOnly, "Results"
End Sub
Этот фрагмент кода является пользовательской функцией (UDF), на который ссылается пользователь, как на любую другую Excel формулу. Просто введите =fn_IsLinkedDataType(A1),где ячейка A1 является ячейкой, для которой нужно вывести результат.
Public Function fn_IsLinkedDataType(c As Range)
' Function will return TRUE if a referenced cell contains a linked data type
If c.HasRichDataType = True Then
fn_IsLinkedDataType = "Linked data type"
Else
fn_IsLinkedDataType = "Not a linked data type"
End If
End Function
Чтобы использоватьлюбой из этих примеров, нажмите ALT+F11, чтобы открыть редактор Visual Basic (VBE), затем перейдите на вкладку Вставка модуля > и вставьте код в новое окно, которое открывается справа. После этого вы можете вернуться к Excel С помощью ALT+Q. Чтобы выполнить один из первых двух примеров, перейдите на вкладку Разработчик> Код> Макрос> выберите нужный макрос из списка и выберите выполнить.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.