Использование других функций для определения связанных типов данных

Связанные типы данных были впервые выпущены в Excel для Microsoft 365 в июне 2018 г. Поэтому другие функции могут не идентифицировать их. Это может быть особенно верно, если требуется использовать другие функции для условного определения того, содержит ли ячейка связанный тип данных. В этой статье описаны некоторые обходные пути, которые можно использовать для определения связанных типов данных в ячейках.

Формулы

Всегда можно написать формулы, ссылающиеся на типы данных. Однако если вы хотите извлечь текст ячейки со связанным типом данных с помощью функции TEXT, вы получите #VALUE! .

Обходной путь — использовать функцию FIELDVALUE и указать поле Name для аргумента field_name . В следующем примере, если ячейка A1 содержит тип данных stock, формула вернет имя запаса.

=FIELDVALUE(A1;"Name")

Однако если ячейка A1 не содержит связанный тип данных, функция FIELDVALUE вернет ошибку #FIELD!. Если вы хотите оценить, содержит ли ячейка связанный тип данных, можно использовать следующую формулу, в которой функция ISERROR используется для проверки того, вернет ли функция FIELDVALUE ошибку.

=IF(ISERROR(FIELDVALUE(A2;"Name")),"Эта ячейка не имеет связанного типа данных","Эта ячейка имеет связанный тип данных")

Если формула принимает ошибку, она вернет текст "Эта ячейка не имеет связанного типа данных", в противном случае возвращается сообщение "Эта ячейка имеет связанный тип данных".

Если вы просто хотите подавить #FIELD! ошибка, можно использовать:

=IFERROR(FIELDVALUE(A1;"Name"),"")

При возникновении ошибки возвращается пустая ячейка.

Условное форматирование

Можно условно отформатировать ячейку в зависимости от того, имеет ли она связанный тип данных. Сначала выделите ячейки, которым требуется условное форматирование, а затем перейдите на главную страницу>Условное форматирование>Новое правило>Использовать формулу... Для формулы используйте следующую команду:

=NOT(ISERROR(FIELDVALUE(A1;"Name")))

Где ячейка A1 — это верхняя ячейка диапазона, который требуется оценить. Затем примените нужный формат.

В этом примере, если ячейка A1 содержит допустимое имя поля "Имя", то формула возвращает значение TRUE и будет применено форматирование. Если ячейка A1 не содержит связанный тип данных, то формула возвращает значение FALSE и форматирование не будет применено. Если вы хотите выделить ячейки, не содержащие допустимые связанные типы данных, можно удалить значение NOT.

VBA

Существует несколько методов VBA (Visual Basic для приложений), которые можно использовать для определения того, содержит ли ячейка или диапазон связанные типы данных. В этой первой процедуре используется свойство HasRichDataType. 

В обеих этих процедурах будет предложено выбрать диапазон ячеек для оценки, а затем вернуть окно сообщения с результатами.

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), а затем перейдите в раздел Вставка>модуля и вставьте код в новое окно, которое откроется справа. Вы можете использовать ALT+Q , чтобы вернуться в Excel, когда все будет готово. Чтобы выполнить любой из первых двух примеров, перейдите на вкладку> Разработчик. Макросыкода> выберитемакрос>, который нужно запустить, в списке, а затем нажмите кнопку Выполнить.

Дополнительные сведения

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.