Намерете липсващи стойности в Excel

В тази статия ще научим как да търсим липсващите стойности от таблицата, използвайки формулата в Excel.

Например, имаме списък със стойности и се нуждаем от потвърждение относно местонахождението на стойността в списъка. За това ще разгледаме ситуацията и ще предложим някои формули, за да направим същото. Ще използваме различни функционални операции в зависимост от лекотата на масива table_array.

Ще се използва следната функция. Затова се запознайте малко с тях, преди да го използвате.

IF функция
COUNTIF функция
ISNA функция
Функция VLOOKUP
MATCH функция

Ще изградим формула от нея. Първо, стойността за търсене се търси в конкретната колона на масива от таблици. Тогава съответстващите стойности ще ни дадат потвърждение с помощта на функцията IF. Функцията IF връща потвърждението, използвайки стойностите „Има ли“ и „Липсва“.

Първият метод: Използване на COUNTIF и IF функция

Използвайте общата формула

= IF (COUNTIF (списък, cell_value), "Има ли", "Липсва")

Обяснение:

Функцията COUNTIF запазва броя на cell_value в списъка и връща номера на функцията IF.
IF функцията счита 0 за FALSE и всяко друго цяло число, различно от 0 за TRUE.
IF функцията връща „Има ли“ като Стойност, ако е вярна, и „Липсваща“ като стойност, ако е невярна.

Пример:

Нека приключим с това, като използваме формулата в пример.

Тук имаме таблица и трябва да извлечем информация от това.

Тук се нуждаем от потвърждение от идентификационния списък. Затова използваме именования диапазон за списъка с идентификатори. документ за самоличност именован диапазон за C2: C14.

Така че ще използваме формулата, за да получим общата сума

= АКО (COUNTIF (ID, G4), "Има ли", "Липсва")

Обяснение за формулата:

Функцията COUNTIF запазва броя на ID 900 в списъка и връща броя на функцията IF.
IF функцията счита 0 за FALSE и всяко друго цяло число, различно от 0 за TRUE
IF функцията връща „Има ли“ като Стойност, ако е вярно, и „Липсващо“ като стойност, ако е невярно.

Тук аргументните масиви към функцията са дадени като препратка към клетка.

Както можете да видите, формулата връща стойностите за идентификационния номер 807 и 953. Но връща липсващо за идентификационния номер 900.

Втори метод: Използване на функцията ISNA & VLOOKUP.

Синтаксис на формулата:

= IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Липсва", "Има ли")

Обяснение за формулата:

Функцията VLOOKUP търси стойността на клетката в първата колона на списъка table_array. Функцията връща стойността, ако е намерена или иначе връща грешка #N/A.
Функцията ISNA улавя грешката #N/A и връща TRUE, ако грешка #N/A съществува или иначе връща FALSE.
IF функцията връща "Има ли" като Стойност, ако FALSE и "Липсва" като стойност, ако TRUE.

Както виждате от горната снимка. Формулата връща „Има ли“ за съответстващия ID 807 & 953. Но връща „Липсващ“ за несравнения ID 900.

Трети метод: Използване на функцията ISNA & MATCH.

Синтаксис на формулата:

= IF (ISNA (MATCH (cell_value, list, 0)), "Липсва", "Има ли")

Обяснение за формулата:

Функцията MATCH търси стойността на клетката в списъка table_array. Функцията връща стойността, ако е намерена или иначе връща грешка #N/A.
Функцията ISNA улавя грешката #N/A и връща TRUE, ако грешка #N/A съществува или иначе връща FALSE.
IF функцията връща "Има ли" като Стойност, ако FALSE и "Липсва" като стойност, ако TRUE.

Гореописаните 3 примера за намиране на липсващите стойности в списъка в excel. И трите формули работят добре, но има някои точки, които трябва да се търсят.

Бележки:

Функцията VLOOKUP не гледа вляво в таблицата_масив.
Функцията COUNTIF поддържа заместващи знаци ( * , ? ), което помага при извличането на стойности, съдържащи фрази.
Нецифрените стойности трябва да се предоставят в двойни кавички ("стойност") или използвайте cell_reference …
Вижте целия списък, тъй като функцията връща стойности, където съвпада.
Аргументът на масива към функцията може да бъде даден като препратка към клетка или именувани диапазони.
Можете да персонализирате тези формули според нуждите, като използвате друга функция на Excel.
Функцията връща сумата от стойностите, отговарящи на всички условия.

Надявам се, че сте разбрали как да намерите липсващи стойности в Excel. Разгледайте още статии за формулите на функциите на Excel тук. Моля, не се колебайте да заявите вашето запитване или обратна връзка за горната статия. Ние ще ви съдействаме.

INDEX-MATCH в Excel

VLOOKUP Множество стойности

VLOOKUP с Dynamic Col Index

Частично съвпадение с функцията VLOOKUP

Преглед по дата в Excel

17 неща за Excel VLOOKUP

Популярни статии

50 Преки пътища в Excel за повишаване на вашата производителност

Редактиране на падащ списък

Абсолютна справка в Excel

Ако с условно форматиране

Ако с заместващи символи