Как в Excel я могу убедиться, что повторяющиеся номера счетов не вводятся в конкретный столбец Excel?
В Excel 97 для этого можно использовать новую функцию проверки данных. В нашем примере номера счетов вводятся в столбец A. Вот как настроить его для одной ячейки:
- Следующая ячейка, которую нужно ввести, - это A9. Щелкните ячейку A9 и выберите в меню Данные> Проверка.
- В раскрывающемся списке «Разрешить:» выберите «Пользовательский».
- Введите эту формулу именно так, как она выглядит:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Щелкните вкладку «Предупреждение об ошибке» в диалоговом окне «Проверка данных».
- Убедитесь, что установлен флажок «Показать предупреждение».
- Для стиля: выберите Стоп
- Введите заголовок "Неуникальное значение"
- Введите сообщение «Вы должны ввести уникальный номер счета».
- Нажмите "ОК"
Вы можете это проверить. Введите новое значение, скажем, 10001 в ячейку A9. Нет проблем. Но попробуйте повторить значение, скажем 10088, и появится следующее:
Последнее, что нужно сделать, - скопировать эту проверку из ячейки A9 в другие ячейки в столбце A.
- Щелкните столбец A и выберите «Правка»> «Копировать», чтобы скопировать ячейку.
- Выберите большой диапазон ячеек в столбце A. Возможно, A10: A500.
- Выберите Правка, Специальная вставка. В диалоговом окне «Специальная вставка» выберите «Проверка» и нажмите «ОК». Правило проверки, введенное вами из ячейки A9, будет скопировано во все ячейки до A500.
Если щелкнуть ячейку A12 и выбрать «Проверка данных», вы увидите, что Excel изменил формулу проверки на « =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))Это все, что вам нужно знать, чтобы она работала». Для тех из вас, кто хочет знать больше, я объясню на английском, как работает формула.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Мы сидим в камере А9. Мы говорим функции Vlookup взять значение ячейки, которую мы только что ввели (A9), и попытаться найти совпадение в ячейках в диапазоне от A $ 1 до A8. Следующий аргумент, 1, сообщает Vlookup, что при обнаружении совпадения мы сообщаем нам данные из первого столбца. Наконец, False в vlookup говорит, что мы ищем только точные совпадения. Вот трюк №1: если функция ВПР находит совпадение, она возвращает значение. Но, если он не найдет совпадения, он вернет специальное значение «# N / A». Обычно эти значения # Н / Д - плохие вещи, но в этом случае мы ХОЧЕМ # Н / Д. Если мы получим # N / A, значит, вы знаете, что эта новая запись уникальна и не соответствует ничему выше. Простой способ проверить, является ли значение # N / A, - использовать функцию ISNA (). Если что-то внутри ISNA () оценивается как # N / A, вы получаете TRUE. Так,когда они вводят новый номер счета и его нет в списке над ячейкой, vlookup вернет # N / A, что приведет к тому, что ISNA () будет истинным.
Вторая хитрость заключается во втором аргументе функции Vlookup. Я был осторожен, чтобы указать A $ 1: A8. Знак доллара перед 1 сообщает Excel, что когда мы копируем эту проверку в другие ячейки, он всегда должен начинать поиск в ячейке текущего столбца. Это называется абсолютным адресом. Я был так же осторожен, чтобы не поставить знак доллара перед 8 на А8. Это называется относительным адресом и сообщает Excel, что когда мы копируем этот адрес, он должен перестать искать в ячейке чуть выше текущей ячейки. Затем, когда мы копируем проверку и смотрим на проверку для ячейки A12, второй аргумент в vlookup правильно показывает A $ 1: A11.
У этого решения есть две проблемы. Во-первых, это не будет работать в Excel 95. Во-вторых, проверки выполняются только для изменяющихся ячеек. Если вы введете уникальное значение в ячейку A9, а затем вернетесь назад и отредактируете ячейку A6, чтобы она соответствовала значению, которое вы ввели в A9, логика проверки в A9 не будет вызвана, и вы получите повторяющиеся значения на вашем листе.
Старомодный метод, используемый в Excel 95, решит обе эти проблемы. В старом методе логика проверки находилась бы во временном столбце B. Чтобы настроить это, введите следующую формулу в ячейку B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Скопируйте эту формулу из B9. Вставьте его в ячейки B2: B500. Теперь, когда вы вводите номера счетов в столбце A, в столбце B будет отображаться ИСТИНА, если счет уникален, и ЛОЖЬ, если он не уникальный.
