Excel: Как оставить ячейку пустой (вместо 0), когда VLOOKUP не соответствует?

У меня есть книга Excel с двумя листами: странами и компаниями. Рабочий лист компаний частично заполняется через VLOOKUP в листе стран следующим образом:

страны:

     A       B
1 COUNTRY HOF_LTO
2 Belgium    4
3 Japan      5
4 Spain
5 Sweden     0

компании

    A         B       C
1 COMPANY  COUNTRY HOF_LTO
2 Belgacom Belgium    4
3 Onkyo    Japan      5
4 Sony     Japan      5
5 ATTM     Spain      0
6 Nokia    Sweden     0

Я заполняю столбец C, используя формулу:

=VLOOKUP($B2;countries!$A$1:$C$5;MATCH(companies!B$1;countries!$1:$1;0);FALSE)

Что не так: даже если Spain не имеет значения HOF_LTO в странах, формула ставит 0 как соответствующее значение в ячейке companies!C5. Вместо этого он должен оставить ячейку пустой и поставить только нуль (0), когда он говорит 0 на вкладке стран, как это происходит в случае с Швецией.

Как мне нужно отредактировать формулу, чтобы она оставила ячейки пустыми, когда нет значения страны, чтобы соответствовать?

Ответы

Ответ 1

Если на вашем страновом листе все страны присутствуют только после того, как вы могли использовать следующее вместо:

=IF(ISNUMBER(OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0));
    OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0);
    "")

Ответ 2

Это должно решить это:

=VLOOKUP(...) & ""

Это заставит Excel сделать ссылку на ячейку текстовым значением, тем самым предотвращая превращение заготовок в нули.

Взято из https://superuser.com/a/906954/222835

Ответ 3

=CONCATENATE(VLOOKUP($B2;countries!$A$1:$C$5;MATCH(companies!B$1;countries!$1:$1;0);FALSE))

Concatenate гарантирует, что данные обрабатываются как строка, а не число. Если значение представляет собой только пустую ячейку, вы просто получите пустую ячейку.

Ответ 4

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

Ответ 5

Простое решение: найдите и замените полный массив исходной рабочей книги ваших стран, заменив пустые ячейки на @. (убедитесь, что "Искать только целые ячейки" проверяется при выполнении Заменить все.) Затем вернитесь и запустите vlookups. Скопируйте и вставьте значения, чтобы избавиться от vlookups и сохранить только результаты. Теперь найдите и замените символы @на ничего в целевом файле. Вуаля.

Ответ 6

Вот вариант ответа на конкатенацию VLOOKUP +, который гарантирует, что числовые значения не преобразуются в строки:

=IF(LEN(VLOOKUP(args) & "")=0,"",VLOOKUP(args))

Ответ 7

Самый чистый способ, который я нашел для текста (каламбур), это использовать функцию CLEAN. :)

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

Просто оберните ваш VLOOKUP функцией CLEAN следующим образом:

=CLEAN(VLOOKUP($B2,countries!$A$1:$C$5,MATCH(companies!B$1,countries!$1:$1,0),FALSE))

Еще лучше, оберните все это в IFERROR чтобы убедиться, что вы не получите #N/A или страшный #REF! и т.д. назад; вот так:

=IFERROR(CLEAN(VLOOKUP($B2,countries!$A$1:$C$5,MATCH(companies!B$1,countries!$1:$1,0),FALSE)),"")

Бонус: ИСПРАВЛЕНО! Все ваши формулы неправильно использовали точки с запятой вместо запятых между аргументами функции, и я довольно удивлен, что никто не понял этого. >. <

Общий:

=IFERROR(CLEAN(VLOOKUP(lookup_value, table_array, col_index_num,FALSE)),"") - для текста =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num,FALSE),"") - для значений =IFERROR(VALUE(VLOOKUP(lookup_value, table_array, col_index_num,FALSE)),"") - для принудительных значений

Ответ 8

Другое решение - использовать (*) UDF (пользовательскую функцию) vlookup2 которая значительно упрощает типичное использование vlookup (и vlookup хорошую производительность и более читаемые формулы).

Таким образом, использование будет в данном примере:

=VLOOKUP2(
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 )  ;  "" )

вместо например (на примере красивого наволочки):

=VLOOKUP( 
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 ) ; FALSE) 
   & ""

в других случаях (где, например, критерии поиска $B2 могут уже быть #NV или подобными, может иметь смысл следующее:

=VLOOKUP2(
   $B2  ;  countries!$A$1:$C$5  ;  MATCH( companies!B$1 ; countries!$1:$1 ; 0 ) ; "" ; "")

Код для vlookup2 будет:

'simpler to use than vlookup
'only exact matches and 0 subst value support
'https://stackoverflow.com/a/55743343/1915920
'
Function vlookup2( _
  ByRef searchCriteria As Variant, _
  ByRef matrix As Variant, _
  ByRef columnIndex As Variant, _
  Optional zeroSubst As Variant, _
  Optional errSubst As Variant )

    Dim val As Variant:  val = Application.WorksheetFunction.VLookup( _
                                 searchCriteria,  matrix,  columnIndex,  False )

    If Not IsMissing(errSubst) Then On Error GoTo EH  'EH = error handler
    If Not IsMissing(zeroSubst) And val = 0 Then val = zeroSubst

    vlookup2 = val
    Exit Function

EH:
    vlookup2 = errSubst        
End Function

(*): если вы ничего не знаете об этом, это так просто:

  1. нажмите CTRL + F11 => Откроется окно Microsoft Visual Basic for Applications
  2. вставьте новый модуль и вставьте приведенный выше код в нужный редактор enter image description here
  3. закрыть только что открытое окно

Для немецкой версии можно добавить еще одну:

'https://stackoverflow.com/a/55743343/1915920
Function SVerweis2( _
    ByRef Suchkriterium As Variant, _
    ByRef Matrix As Variant, _
    ByRef SpaltenIndex As Variant, _
    Optional NullZahlSubst As Variant, _
    Optional FehlerSubst As Variant)

    SVerweis2 = vlookup2( Suchkriterium, Matrix, SpaltenIndex , NullZahlSubst, FehlerSubst )
End Function'