Як в Excel я можу переконатися, що дублікати номерів рахунків-фактур не вводяться в певний стовпець Excel?
Для цього в Excel 97 можна використовувати нову функцію перевірки даних. У нашому прикладі номери рахунків-фактур вводяться в стовпець А. Ось як налаштувати його для однієї комірки:
- Наступною коміркою, яку потрібно ввести, є A9. Клацніть у комірці A9 та виберіть у меню Дані> Перевірка.
- У спадному вікні "Дозволити:" виберіть "Спеціально"
- Введіть цю формулу саме так, як вона виглядає:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - У діалоговому вікні Перевірка даних натисніть вкладку Повідомлення про помилку.
- Переконайтеся, що встановлено прапорець "Показувати сповіщення".
- Для стилю: виберіть Зупинити
- Введіть заголовок "Не унікальне значення"
- Введіть повідомлення "Ви повинні ввести унікальний номер рахунку-фактури".
- Натисніть "OK"
Ви можете перевірити це. Введіть нове значення, скажімо, 10001 у комірці A9. Без проблем. Але спробуйте повторити значення, скажімо 10088, і з’явиться таке:
Нарешті, потрібно скопіювати цю перевірку з комірки A9 в інші комірки стовпця A.
- Клацніть у стовпці A та виберіть Edit> copy, щоб скопіювати комірку.
- Виділіть великий стовпець комірок у стовпці А. Можливо 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: якщо VLOOKUP знайде збіг, він поверне значення. Але якщо він не знайде збігу, він поверне спеціальне значення "# N / A". Зазвичай ці значення # N / A - це погані речі, але в цьому випадку ми ХОЧЕМИ # N / A. Якщо ми отримаємо # N / A, то ви знаєте, що цей новий запис є унікальним і не відповідає нічого вище. Простий спосіб перевірити, чи має значення # N / A, - це використовувати функцію ISNA (). Якщо щось всередині ISNA () оцінюється як # N / A, ви отримуєте TRUE. Так,коли вони вводять новий номер рахунка-фактури, і його не знайдено у списку над коміркою, vlookup поверне # N / A, що призведе до того, що ISNA () буде істинним.
Другий біт обману - у другому аргументі функції Vlookup. Я був обережним, вказавши A $ 1: A8. Знак долара перед знаком 1 говорить Excel, що коли ми копіюємо це підтвердження в інші комірки, він завжди повинен починати шукати в комірці поточного стовпця. Це називається абсолютною адресою. Я був однаково обережний, щоб не поставити знак долара до 8 у A8. Це називається відносною адресою і говорить 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 відображатиме TRUE, якщо рахунок-фактура унікальний, і FALSE, якщо він не унікальний.
