Проверьте, сколько раз шаблон не повторяется
В настоящее время я работаю над электронной таблицей Excel, где в столбце B у нас есть серийные значения каждого человека.
Вопрос в том, сколько псевдосерийных значений в наборе данных неверны?
Pserial представляет собой 8-значное значение, объединяющее 6-значное значение hserial и значение persnum (номер лица в домашнем хозяйстве) в виде 2-значного значения. например, для домохозяйства 1 порядковый номер: 10105101, где последние две цифры — личный номер, а первые 6 — порядковый номер. Однако было высказано опасение, что в данных могут быть некоторые ошибки, так что некоторые из этих значения pserial не следуют этому правилу и, следовательно, неверны.
Я не могу изменить или изменить какие-либо данные, даже если вы выявите ошибку. Это электронная таблица, как вы можете видеть, данные начинаются в строке 11, и есть еще данные до строки 9291.
Я не уверен, какой код использовать, но здесь я могу использовать либо макрос, либо формулу. Не стесняйтесь использовать любой из них.
Да, но это дает мне для первого, мне нужно подсчитать, сколько из них неверны. Это возвращает только TRUE
Так сделайте это для каждой строки, затем подсчитайте количество истинных...
@ irene1509 Я вижу, вы недавно опубликовали еще один вопрос , который все еще ожидает принятия ответа. Я не знаю, знаете ли вы об этом как новый участник. Пожалуйста, проверьте эту ссылку. Что делать, когда кто-то отвечает на мой вопрос?. Спасибо
Ответы 2
Эта формула проверяет, совпадает ли pserial с hersial и persnum как двузначное значение, и подсчитывает все неверные значения.
=SUM(($C$11:$C$20<>NUMBERVALUE($A$11:$A$20&IF(LEN($B$11:$B$20)=1;0&$B$11:$B$20;$B$11:$B$20)))*1)
Здесь вы можете проверить каждую строку, правильно ли сформированы значения столбцов pserial. В ячейку D2 можно ввести следующую формулу:
=IF(C2:C7=1*(A2:A7 & TEXT(B2:B7,"00")), TRUE, FALSE)
Вот результат:
Примечание. Вы можете использовать условное форматирование, чтобы выделить ошибки для диапазона D2:D7 следующим образом:
=C2:C7=1*(A2:A7 & TEXT(B2:B7,"00")) = FALSE
Предполагается, что значения столбца persnum в столбце pserial отформатированы как две цифры, а значения из столбца pserial являются числами (не текстом), поэтому мы умножаем на 1, чтобы выполнить диалог перед сравнением. Он использует логику, упомянутую вами в вопросе, для построения серийного номера из столбцов hserial и persnum.
Если вы хотите просто рассчитать общее количество ошибок, повторно используйте первую формулу следующим образом:
=LET(check, IF(C2:C7=1*(A2:A7 & TEXT(B2:B7,"00")), TRUE, FALSE),
ROWS(FILTER(check, check=FALSE,0)))
Пробовал первую формулу, использовал условное форматирование и все прошло успешно, ошибок не обнаружено. Однако использование последней формулы дает мне результат 1. Как возможно, что они дают мне разные ответы? заранее спасибо
Проверьте вводимые данные, возможно, вы вводите дополнительное пространство или что-то в этом роде. Этот расчет очень прост. Я только что проверил это с моими входными данными, и я получаю 2.
Проведите простой тест: =A11=left(c11,6)*1, а затем расширьте его до двух последних цифр.