Как заменить значения #N/A в Excel (с примерами)
Вы можете использовать следующий базовый синтаксис для замены значений #N/A в Excel нулями или пробелами:
#replace #N/A with zero =IFERROR(FORMULA, "0") #replace #N/A with blank =IFERROR(FORMULA, "") В следующем примере показано, как использовать этот синтаксис на практике для замены значений #Н/Д из ВПР нулями или пробелами.
Пример: замена значений #N/A в Excel
Предположим, у нас есть следующий набор данных в Excel:
И предположим, что мы используем функцию ВПР() для поиска очков на основе названия команды:
Обратите внимание, что некоторые значения, возвращаемые функцией ВПР(), являются значениями #Н/Д.
Мы можем превратить эти значения в нули с помощью функции ЕСЛИОШИБКА() следующим образом:
#replace #N/A with zero =IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "0") На следующем снимке экрана показано, как использовать эту функцию на практике:
В качестве альтернативы мы можем превратить значения #N/A в пустые с помощью функции ЕСЛИОШИБКА() следующим образом:
#replace #N/A with blank =IFERROR(VLOOKUP( A2 , $A$1:$B$11 , 2, FALSE), "") На следующем снимке экрана показано, как использовать эту функцию на практике:
Обратите внимание, что каждое значение, которое ранее было #N/A, теперь пустое.
Используя функцию ЕСЛИОШИБКА() , мы можем заменить значения #Н/Д любым значением, которое нам нужно.
В предыдущих примерах мы просто решили заменить значения #N/A нулями или пробелами, потому что это наиболее распространенные значения замены, используемые на практике.
ЗАМЕНИТЬ, ЗАМЕНИТЬБ (функции ЗАМЕНИТЬ, ЗАМЕНИТЬБ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование ЗАМЕНИТЬ и ЗАМЕНИТЬБ в Microsoft Excel.
Описание
Функция ЗАМЕНИТЬ заменяет указанное число символов текстовой строки другой текстовой строкой.
Функция ЗАМЕНИТЬ заменяет часть текстовой строки, соответствующую заданному числу байтов, другой текстовой строкой.
- Эти функции могут быть доступны не на всех языках.
- Функция ЗАМЕНИТЬ предназначена для языков с однобайтовой кодировкой, а ЗАМЕНИТЬБ — для языков с двухбайтовой кодировкой. Язык по умолчанию, заданный на компьютере, влияет на возвращаемое значение следующим образом.
- Функция ЗАМЕНИТЬ всегда считает каждый символ (одно- или двухбайтовый) за один вне зависимости от языка по умолчанию.
- Функция ЗАМЕНИТЬБ считает каждый двухбайтовый символ за два, если включена поддержка ввода на языке с двухбайтовой кодировкой, а затем этот язык назначен языком по умолчанию. В противном случае функция ЗАМЕНИТЬБ считает каждый символ за один.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ описаны ниже.
- Стар_текст Обязательный. Текст, в котором требуется заменить некоторые символы.
- Начальная_позиция Обязательный. Позиция символа в старом тексте, начиная с которого требуется выполнить замену новым текстом.
- Число_знаков Обязательный. Число символов в старом тексте, которые требуется ЗАМЕНИТЬ новым текстом.
- Число_байтов Обязательный. Число байтов старого текста, который требуется ЗАМЕНИТЬБ новым текстом.
- Нов_текст Обязательный. Текст, который заменит символы в старом тексте.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Заменить значения (Power Query)
В Power Query можно заменить одно значение другим значением в выбранном столбце. Вы можете заменить определенные значения или целое значение в ячейке. Замена значений в запросе никак не изменяет внешний источник данных.
Замена текстовых значений
По умолчанию текстовые значения выполняют поиск и замену определенной текстовой строки. Это поведение можно изменить.
- Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
- Выберите столбец с текстовым типом данных.
- Выберите Главная или Преобразовать >Заменить значение. Откроется диалоговое окно Заменить значения .
- В поле Значение для поиска введите значение для поиска.
- В поле Заменить на введите значение для замены.
- В разделе Дополнительные параметры выполните одно или несколько из следующих действий:
Замена числа, даты и времени или логических значений
По умолчанию для нетекстовых столбцов выполняется поиск и замена полного содержимого ячейки. Это поведение нельзя изменить.
- Чтобы открыть запрос, найдите ранее загруженный из Редактор Power Query, выберите ячейку в данных, а затем выберите Запрос >Изменить. Дополнительные сведения см. в статье Создание, загрузка и изменение запроса в Excel.
- Выберите столбец с числом, датой или временем или логическим типом данных.
- Выберите Главная или Преобразовать >Заменить значение. Откроется диалоговое окно Заменить значения .
- В поле Значение для поиска введите значение для поиска.
- В поле Заменить на введите значение для замены.
- Нажмите кнопку ОК.
Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
Давайте разберем пару примеров использования функции ПОДСТАВИТЬ для наглядности.
Замена или удаление неразрывных пробелов
При выгрузке данных из 1С, копировании информации с вебстраниц или из документов Word часто приходится иметь дело с неразрывным пробелом — спецсимволом, неотличимым от обычного пробела, но с другим внутренним кодом (160 вместо 32). Его не получается удалить стандартными средствами — заменой через диалоговое окно Ctrl + H или функцией удаления лишних пробелов СЖПРОБЕЛЫ (TRIM) . Поможет наша функция ПОДСТАВИТЬ, которой можно заменить неразрывный пробел на обычный или на пустую текстовую строку, т.е. удалить:
Подсчет количества слов в ячейке
Если нужно подсчитать количество слов в ячейке, то можно применить простую идею: слов на единицу больше, чем пробелов (при условии, что нет лишних пробелов). Соответственно, формула для расчета будет простой:
Если предполагается, что в ячейке могут находиться и лишние пробелы, то формула будет чуть посложнее, но идея — та же.
Извлечение первых двух слов
Если нужно вытащить из ячейки только первые два слова (например ФИ из ФИО), то можно применить формулу:
У нее простая логика:
- заменяем второй пробел на какой-нибудь необычный символ (например #) функцией ПОДСТАВИТЬ (SUBSTITUTE)
- ищем позицию символа # функцией НАЙТИ (FIND)
- вырезаем все символы от начала строки до позиции # функцией ЛЕВСИМВ (LEFT)
Ссылки по теме
- Зачистка текста от лишних пробелов, непечатаемых символов и т.д.
- Как подсчитать количество слов в ячейке
- Преобразование чисел-как-текст в полноценные числа