Jak w programie Excel mogę się upewnić, że zduplikowane numery faktur nie zostaną wprowadzone w określonej kolumnie programu Excel?
W programie Excel 97 można w tym celu użyć nowej funkcji sprawdzania poprawności danych. W naszym przykładzie numery faktur są wprowadzane do kolumny A. Oto jak ustawić to dla pojedynczej komórki:
- Następną komórką do wprowadzenia jest A9. Kliknij komórkę A9 i wybierz z menu Dane> Walidacja.
- W menu „Zezwalaj” wybierz „Niestandardowe”.
- Wpisz tę formułę dokładnie tak, jak wygląda:
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE)) - Kliknij kartę Alert błędu w oknie dialogowym Sprawdzanie poprawności danych.
- Upewnij się, że pole „Pokaż alert” jest zaznaczone.
- W polu Styl: wybierz Stop
- Wpisz tytuł „Nieunikalna wartość”
- Wpisz komunikat „Musisz podać niepowtarzalny numer faktury”.
- Kliknij OK"
Możesz to przetestować. Wprowadź nową wartość, powiedzmy 10001 w komórce A9. Nie ma problemu. Ale spróbuj powtórzyć wartość, powiedzmy 10088, a pojawi się:
Ostatnią rzeczą do zrobienia jest skopiowanie tej weryfikacji z komórki A9 do innych komórek w kolumnie A.
- Kliknij kolumnę A i wybierz Edytuj> kopiuj, aby skopiować komórkę.
- Wybierz duży zakres komórek w kolumnie A. Być może A10: A500.
- Wybierz Edytuj, Wklej specjalnie. W oknie dialogowym Wklej specjalnie wybierz „Walidacja” i kliknij OK. Reguła sprawdzania poprawności wprowadzona z komórki A9 zostanie skopiowana do wszystkich komórek aż do A500.
Jeśli klikniesz komórkę A12 i wybierzesz opcję Sprawdzanie poprawności danych, zobaczysz, że program Excel zmienił formułę weryfikacji na =ISNA(VLOOKUP(A12,A$1:A11,1,FALSE))To wszystko, co musisz wiedzieć, aby to działało. Dla tych z Was, którzy chcą wiedzieć więcej, wyjaśnię po angielsku, jak działa formuła.
=ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))
Siedzimy w celi A9. Mówimy funkcji Vlookup, aby wzięła wartość komórki, którą właśnie wprowadziliśmy (A9) i spróbowała znaleźć dopasowanie w komórkach z zakresu od A 1 do A8. Następny argument, 1, mówi Vlookup, że gdy zostanie znalezione dopasowanie, przekazuje nam dane z pierwszej kolumny. Wreszcie fałsz w przeszukiwaniu mówi, że szukamy tylko dokładnych dopasowań. Oto sztuczka nr 1: Jeśli funkcja WYSZUKAJ.PIONOWO znajdzie dopasowanie, zwróci wartość. Ale jeśli nie znajdzie dopasowania, zwróci specjalną wartość „# N / A”. Zwykle te wartości # N / A są złe, ale w tym przypadku CHCEMY # N / A. Jeśli otrzymamy # N / A, to wiesz, że ten nowy wpis jest unikalny i nie pasuje do niczego powyżej. Łatwym sposobem sprawdzenia, czy wartość to # N / A, jest użycie funkcji ISNA (). Jeśli coś wewnątrz ISNA () zwróci się do # N / A, otrzymasz PRAWDA. Więc,kiedy wprowadzą nowy numer faktury i nie zostanie znaleziony na liście powyżej komórki, vlookup zwróci # N / A, co spowoduje, że ISNA () będzie prawdziwe.
Druga sztuczka tkwi w drugim argumencie funkcji Vlookup. Uważałem, aby określić A 1: A8. Znak dolara przed 1 mówi programowi Excel, że kiedy kopiujemy tę walidację do innych komórek, zawsze powinno zacząć szukać w komórce bieżącej kolumny. Nazywa się to adresem bezwzględnym. Byłem równie ostrożny, aby nie postawić znaku dolara przed ósemką na A8. Nazywa się to adresem względnym i informuje program Excel, że kiedy kopiujemy ten adres, powinien przestać szukać w komórce tuż nad bieżącą komórką. Następnie, kiedy kopiujemy walidację i patrzymy na walidację dla komórki A12, drugi argument w vlookup poprawnie pokazuje A $ 1: A11.
Z tym rozwiązaniem wiążą się dwa problemy. Po pierwsze, nie będzie działać w programie Excel 95. Po drugie, walidacje są wykonywane tylko na komórkach, które się zmieniają. Jeśli wprowadzisz unikalną wartość w komórce A9, a następnie wrócisz do komórki A6 i edytujesz tę samą wartość, którą wprowadziłeś w A9, logika sprawdzania poprawności w A9 nie zostanie wywołana i w arkuszu zostaną zduplikowane wartości.
Staromodna metoda zastosowana w programie Excel 95 rozwiązuje oba te problemy. W starej metodzie logika walidacji byłaby umieszczona w tymczasowej kolumnie B. Aby to ustawić, wprowadź następującą formułę w komórce B9: =ISNA(VLOOKUP(A9,A$1:A8,1,FALSE))Skopiuj tę formułę z B9. Wklej go w komórkach B2: B500. Teraz, gdy wprowadzisz numery faktur w kolumnie A, w kolumnie B pojawi się PRAWDA, jeśli faktura jest unikalna, a FAŁSZ, jeśli nie jest unikalna.
