Как найти недостающие значения в excel
В этой статье описаны синтаксис формулы и использование функций НАЙТИ и НАЙТИБ в Microsoft Excel.
Описание
Функции НАЙТИ и НАЙТИБ находят вхождение одной текстовой строки в другую и возвращают начальную позицию искомой строки относительно первого знака второй строки.
Эти функции могут быть доступны не на всех языках.
Функция НАЙТИ предназначена для языков с однобайтовой кодировкой, а функция НАЙТИБ — для языков с двухбайтовой кодировкой. Заданный на компьютере язык по умолчанию влияет на возвращаемое значение указанным ниже образом.
Функция НАЙТИ при подсчете всегда рассматривает каждый знак, как однобайтовый, так и двухбайтовый, как один знак, независимо от выбранного по умолчанию языка.
Функция НАЙТИБ при подсчете рассматривает каждый двухбайтовый знак как два знака, если включена поддержка языка с БДЦС и такой язык установлен по умолчанию. В противном случае функция НАЙТИБ рассматривает каждый знак как один знак.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций НАЙТИ и НАЙТИБ описаны ниже.
Искомый_текст — обязательный аргумент. Текст, который необходимо найти.
Просматриваемый_текст — обязательный аргумент. Текст, в котором нужно найти искомый текст.
Начальная_позиция — необязательный аргумент. Знак, с которого нужно начать поиск. Первый знак в тексте «просматриваемый_текст» имеет номер 1. Если номер опущен, он полагается равным 1.
Замечания
Функции НАЙТИ и НАЙТИБ работают с учетом регистра и не позволяют использовать подстановочные знаки. Если необходимо выполнить поиск без учета регистра или использовать подстановочные знаки, воспользуйтесь функцией ПОИСК или ПОИСКБ.
Если в качестве аргумента «искомый_текст» задана пустая строка («»), функция НАЙТИ выводит значение, равное первому знаку в строке поиска (знак с номером, соответствующим аргументу «нач_позиция» или 1).
Искомый_текст не может содержать подстановочные знаки.
Если find_text не отображаются в within_text, find и FINDB возвращают #VALUE! значение ошибки #ЗНАЧ!.
Если start_num не больше нуля, то найти и найтиБ возвращает значение #VALUE! значение ошибки #ЗНАЧ!.
Если start_num больше, чем длина within_text, то поиск и НАЙТИБ возвращают #VALUE! значение ошибки #ЗНАЧ!.
Аргумент «нач_позиция» можно использовать, чтобы пропустить нужное количество знаков. Предположим, например, что для поиска строки «МДС0093.МесячныеПродажи» используется функция НАЙТИ. Чтобы найти номер первого вхождения «М» в описательную часть текстовой строки, задайте значение аргумента «нач_позиция» равным 8, чтобы поиск в той части текста, которая является серийным номером, не производился. Функция НАЙТИ начинает со знака 8, находит искомый_текст в следующем знаке и возвращает число 9. Функция НАЙТИ всегда возвращает номер знака, считая от левого края текста «просматриваемый_текст», а не от значения аргумента «нач_позиция».
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Найдите недостающие значения — Excel и Google Таблицы
Вы можете определить значения в одном списке, которые отсутствуют в другом списке. Есть несколько способов сделать это, и мы рассмотрим некоторые из них ниже.
Найдите недостающие значения с помощью COUNTIF
Один из способов найти пропущенные значения в списке — использовать функцию СЧЁТЕСЛИ вместе с функцией ЕСЛИ.
| 1 | = ЕСЛИ (СЧЁТЕСЛИ (B3: B7; D3); «Да»; «Отсутствует») |
Посмотрим, как работает эта формула.
Функция СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ подсчитывает количество ячеек, соответствующих заданному критерию. Если ни одна ячейка не соответствует условию, возвращается ноль.
| 1 | = СЧЁТЕСЛИ (B3: B7; D3) |
В этом примере «# 1103» и «# 7682» находятся в столбце B, поэтому формула дает нам 1 для каждого. «# 5555» нет в списке, поэтому формула дает нам 0.
ЕСЛИ Функция
Функция ЕСЛИ оценит любое ненулевое число как ИСТИНА, а ноль — как ЛОЖЬ.
Внутри функции ЕСЛИ мы выполняем подсчет, а затем выводим «Да» для ИСТИНА и «Нет» для ЛОЖЬ. Это дает нам нашу первоначальную формулу:
| 1 | = ЕСЛИ (СЧЁТЕСЛИ (B3: B7; D3); «Да»; «Отсутствует») |
Найдите недостающие значения с помощью ВПР
Другой способ найти пропущенные значения в списке — использовать функции ВПР и ISNA вместе с функцией ЕСЛИ.
| 1 | = ЕСЛИ (ISNA (ВПР (D3; B3: B7,1; FALSE)); «Отсутствует»; «Да») |
Давайте рассмотрим эту формулу.
Функция ВПР
Начните с выполнения vlookup с точным соответствием для значений в вашем списке.
| 1 | = ВПР (D3; B3: B7,1; ЛОЖЬ) |
Мы используем «ЛОЖЬ» в формуле, чтобы требовать точного совпадения. Если элемент, который вы ищете, есть в вашем списке, функция ВПР вернет этот элемент; если его нет, он вернет ошибку # Н / Д.
Функция ISNA
Вы можете использовать функцию ISNA для преобразования ошибок # N / A в TRUE, что означает, что эти элементы отсутствуют.
| 1 | = ISNA (E3) |
Все значения, не содержащие ошибок, приводят к ЛОЖЬ.
ЕСЛИ Функция
Затем преобразуйте результаты функции ISNA, чтобы показать, отсутствует ли значение. Если vlookup выдает ошибку, это значит, что элемент «отсутствует».
Найти недостающие значения
Если вы хотите выяснить, какие значения в одном списке отсутствуют из другого списка, вы можете использовать простую формулу, основанную на функции СЧЕТЕСЛИ.
Функция СЧЕТЕСЛИ подсчитывает ячейки, которые отвечают критериям, возвращая число найденных вхождений. Если такие ячейки не найдены, СЧЕТЕСЛИ возвращает ноль.
В показанном примере, формула в G5 является:
Где «список» является именованный диапазон, что соответствует диапазону B6: B11.
Функция ЕСЛИ требует логического теста, чтобы вернуть значение ИСТИНА или ЛОЖЬ. В этом случае, если значение найдено, положительное число возвращается СЧЕТЕСЛИ, который имеет значение ИСТИНА, в результате чего, если вернуть «ОК». Если значение не найдено, возвращается ноль, который имеет значение ЛОЖЬ, и ЕСЛИ возвращает «Отсутствует».
Количество пропущенных значений
Для подсчета значений в одном списке, которые отсутствуют в другом списке, вы можете использовать формулу, основанную на функциях СЧЕТЕСЛИ и СУММПРОИЗВ.
Функции СЧЕТЕСЛИ проверяет значения в диапазоне от критериев. Часто, только один критерий подается, но в этом случае мы поставляем больше чем один критерий.
Для диапазона, мы даем СЧЕТЕСЛИ именованному диапазону лист1 (B6: B11) и критериям мы обеспечиваем именованный диапазон лист2 (F6: F8).
Потому что мы даем СЧЕТЕСЛИ более чем один критерий, мы получим более одного результата в массиве, который выглядит следующим образом:
Мы хотим, чтобы рассчитывались только те значения, которые отсутствуют, которые по определению имеют счетчик, равный нулю, поэтому мы преобразуем эти значения ИСТИНА и ЛОЖЬ с «= 0» заявлением, что дает:
Тогда мы изменим значения ИСТИНА/ЛОЖЬ в 1 и 0 с двойным отрицательным оператором (-), который производит:
Наконец, мы используем СУММПРОИЗВ, чтобы сложить элементы в массиве и получить общее количество пропущенных значений.
Как найти недостающие элементы в столбце с последовательными номерами на листе Excel
Обычно на вашем листе Excel будет столбец с последовательными номерами. Однако по некоторым причинам некоторые данные могут быть удалены.ost, например копирование и вставка, или вы забыли собрать информацию о торговом представителе. Поэтому вам необходимо проверить рабочий лист в своей работе.
На этом листе первый столбец содержит идентификатор сотрудника. Они должны быть последовательными логически. Однако последний идентификатор сотрудника — «20150018», а соответствующая строка — 16. Это означает, что на этом листе отсутствуют 3 элемента.
Если вы проверяете числа вручную и видите числа одно за другим, вы потратите много времени и сил, особенно когда в столбце много элементов. Чтобы быстро и легко найти недостающие элементы, вы можете использовать три метода, описанные в следующих частях.
Метод 1: функция ЕСЛИ
В этом методе вам нужно будет использовать функцию ЕСЛИ для рабочего листа.
- Щелкните пустую ячейку на листе. В этом примере мы щелкнем ячейку E3 на листе. Вы также можете выбрать ячейку в соответствии с вашими потребностями.
- И затем введите эту формулу в ячейку:
= ЕСЛИ (A3 = A2 + 1», «», «потерять данные»)
Эта формула будет судить о взаимосвязи между двумя соседними ячейками.
- После этого нажмите кнопку «Enter» на клавиатуре. Если отсутствует элемент или отсутствует несколько элементов, вы увидите в ячейке результат «потерять данные». Иначе ничего не увидишь.
- Теперь снова щелкните ячейку E3.
- Затем дважды щелкните маркер заполнения этой ячейки. Таким образом, вы заполните всю колонку одной и той же формулой. И вы можете увидеть результат на рабочем листе.
Есть две ячейки с результатом «потерять данные». Таким образом, вы можете проверить по результату этой формулы. Вернитесь к соответствующим ячейкам в столбце A, вы узнаете, что недостающие элементы — «20150003», «20150010» и «20150011».
Способ 2: условное форматирование
Кроме функции ЕСЛИ, вы также можете использовать функцию с условным форматированием на листе.
- Выберите tarполучить столбец на листе.
- А затем нажмите кнопку «Условное форматирование» на панели инструментов.
- В выпадающем списке выберите опцию «Новое правило».
- В новом всплывающем окне выберите последний вариант использования формулы.
- Затем введите эту формулу в текстовое поле:
Эта формула также определяет, является ли второе число результатом первого числа плюс 1.
- После этого нужно установить формат. Здесь нажмите кнопку «Формат» в этом окне.
- И тогда вы увидите всплывающее окно «Формат ячеек». В этом окне можно задать специальный формат для tarполучить сотовый. Вы можете установить в соответствии с вашими предпочтениями. И здесь мы изменим цвет заливки для tarполучить сотовый.
- Когда вы закончите настройку в окне «Формат ячеек», нажмите кнопку «ОК» в окне.
- Затем в окне «Новое правило форматирования» продолжайте нажимать «ОК», чтобы сохранить настройку этого нового правила.
Теперь вы вернетесь к рабочему листу. Вы увидите, что если отсутствуют элементы, формат tarполучить ячейку изменится.
Здесь также меняется формат последней ячейки A16. Это потому, что в ячейке A17 нет значения. Поэтому вы можете просто игнорировать эту ячейку.
Способ 3: макросы VBA
За исключением двух вышеуказанных методов, вы также можете использовать макросы VBA для выполнения этой задачи.
- Нажмите сочетание клавиш «Alt + F11» на клавиатуре, чтобы открыть редактор Visual Basic.
- А затем вставьте новый модуль в редактор.
- Теперь скопируйте следующие коды и вставьте в модуль:
В этом макросе вам нужно изменить определенные элементы в соответствии с вашим фактическим рабочим листом. И отформатируем ячейку желтым цветом.
- На этом шаге вам нужно будет запустить этот макрос. Существуют различные способы запуска макросов в Excel. Вы можете нажать кнопку «F5» на клавиатуре, чтобы запустить макрос. И результат уже появится на листе.
- Теперь вернитесь к рабочему листу и проверьте результат.
Результат фактически такой же, как результат условного форматирования. А всего лишь нажав кнопку на клавиатуре, можно сразу получить результат. С другой стороны, нужно также обратить внимание на один момент. Если в этом столбце есть правило условного форматирования, результат условного форматирования будет охватывать результат макросов VBA. Следовательно, вам нужно очистить правило условного форматирования в том же диапазоне.
Сравнение трех методов
Когда вам нужно проверить столбец на листе, у вас могут возникнуть проблемы с выбором методов. В таблице ниже перечислены все преимущества и недостатки трех методов. Вы можете обратиться к нему, чтобы лучше понять эти методы.
Недостатки бонуса без депозита
В следующий раз, когда вам нужно будет найти недостающие элементы или проверить рабочий лист, вы будете знать, какой из них является лучшим для вас. Используя любой метод, вы можете легко и быстро завершить свою задачу без каких-либо ошибок.
Важность резервного копирования ваших файлов Excel
Все мы знаем, что данные и информация в файлах Excel очень важны. Поэтому вам нужно уделить особое внимание этим важным файлам. Есть много методов, которые вы можете использовать для защиты ваших файлов. Резервное копирование файлов также является одним из способов. Когда ваши файлы Excel по какой-то причине повреждены, у вас все еще есть резервная версия. Все данные и информация по-прежнему в безопасности. Однако иногда резервные копии также будут повреждены. На данный момент вам нужно восстановить файлы Excel. Вы можете использовать наш мощный инструмент восстановления Excel, чтобы исправить повреждение данных Excel xlsx. Благодаря резервным копиям файлов и этому инструменту восстановления ваши файлы наверняка будут в безопасности.