Szukam formuły dla komórki podświetlania arkusza kalkulacyjnego Google, jeśli wartość jest zduplikowana w tej samej kolumnie
czy ktoś może mi pomóc w tym zapytaniu?
Szukam formuły dla komórki podświetlania arkusza kalkulacyjnego Google, jeśli wartość jest zduplikowana w tej samej kolumnie
czy ktoś może mi pomóc w tym zapytaniu?
Odpowiedzi:
Spróbuj tego:
Custom formula is
=countif(A:A,A1)>1
(lub zmień A
na wybraną kolumnę)A1:A100
.).Wszystko, co zostanie zapisane w komórkach A1: A100, zostanie sprawdzone, a jeśli istnieje duplikat (występuje więcej niż jeden raz), zostanie pokolorowane.
W przypadku ustawień regionalnych wykorzystujących przecinek ( ,
) jako separator dziesiętny separatorem argumentów jest najprawdopodobniej średnik ( ;
). To znaczy spróbuj: =countif(A:A;A1)>1
zamiast tego.
W przypadku wielu kolumn użyj countifs
.
;
powoduje dla mnie błąd „nieprawidłowej formuły”. Usunięcie tego wystarczyło. Zachowaj też ostrożność: komórka określona jako drugi argument countif
powinna być pierwszą komórką wybranego zakresu.
=countif(B:B,B2)>1
. Pozwala to na wykonanie dość zaawansowanego formatowania przy użyciu bezwzględnych lub względnych odniesień do komórek.
Chociaż odpowiedź Zolleya jest w sam raz na pytanie, oto bardziej ogólne rozwiązanie dla dowolnego zakresu oraz wyjaśnienie:
=COUNTIF($A$1:$C$50, INDIRECT(ADDRESS(ROW(), COLUMN(), 4))) > 1
Pamiętaj, że w tym przykładzie użyję zakresu A1:C50
. Pierwszy parametr ( $A$1:$C$50
) należy zastąpić zakresem, w którym chcesz wyróżnić duplikaty!
aby podświetlić duplikaty:
Format
>Conditional formatting...
Apply to range
wybierz zakres, do którego reguła ma być stosowana.Format cells if
wybierz Custom formula is
z menu rozwijanego.Dlaczego to działa?
COUNTIF(range, criterion)
, porówna każdą komórkę range
do tej criterion
, która jest przetwarzana podobnie do formuł. Jeśli nie zostaną podane żadne specjalne operatory, porówna każdą komórkę w zakresie z daną komórką i zwróci liczbę komórek pasujących do reguły (w tym przypadku porównanie). Używamy stałego zakresu (ze $
znakami), dzięki czemu zawsze oglądamy pełny zakres.
Drugi blok INDIRECT(ADDRESS(ROW(), COLUMN(), 4))
zwróci zawartość bieżącej komórki. Jeśli zostanie to umieszczone w komórce, dokumenty będą płakać z powodu zależności cyklicznej, ale w tym przypadku formuła jest oceniana tak, jakby była w komórce, bez jej zmiany.
ROW()
i COLUMN()
zwróci odpowiednio numer wiersza i numer kolumny podanej komórki. Jeśli nie zostanie podany żaden parametr, bieżąca komórka zostanie zwrócona (na przykład 1, B3
zwróci 3 dla ROW()
, a 2 dla COLUMN()
).
Następnie używamy: ADDRESS(row, column, [absolute_relative_mode])
do przetłumaczenia wiersza i kolumny numerycznej na odwołanie do komórki (np B3
. Pamiętaj, że gdy jesteśmy w kontekście komórki, nie wiemy, czy jest to adres LUB zawartość, i potrzebujemy zawartości, aby porównać). Trzeci parametr zajmuje się formatowaniem i 4
zwraca INDIRECT()
polubienia formatowania .
INDIRECT()
, pobierze odwołanie do komórki i zwróci jej zawartość. W takim przypadku zawartość bieżącej komórki. Następnie wróć do początku, COUNTIF()
przetestuje każdą komórkę w zasięgu względem naszej i zwróci liczbę.
Ostatnim krokiem jest sprawienie, że nasza formuła zwraca wartość logiczną, przez co czyni go logiczną wyrażenia: COUNTIF(...) > 1
. > 1
Jest używany, ponieważ wiemy, istnieje co najmniej jedna komórka identyczny do naszego. To jest nasza komórka, która jest w zasięgu, a zatem zostanie porównana do siebie. Aby wskazać duplikat, musimy znaleźć 2 lub więcej komórek pasujących do naszej.
Źródła:
*
) i kolejną kontrolę, tak jak w przypadku ((COUNTIF(...))*(NOT(ISBLANK(INDIRECT(...current cell...)))))
. To najlepsze, co mogę zrobić na telefonie komórkowym. :)
$A$1:$C$50
- zgodnie z kolumnami, o których mowa. Lubię to bardziej ogólne podejście niż zolley.
Odpowiedź @zolley jest prawidłowa. Wystarczy dodać Gif i kroki dla odniesienia.
Format > Conditional formatting..
Format cells if..
=countif(A:A,A1)>1
w poluCustom formula is
A
z własną kolumną.Z menu rozwijanego „Zawiera tekst” wybierz „Formuła niestandardowa to:” i napisz: „= countif (A: A, A1)> 1” (bez cudzysłowów)
Zrobiłem dokładnie tak, jak zaproponował Zolley , ale należy wprowadzić małą korektę: użyj „Formuła niestandardowa” zamiast „Zawiera tekst” . A wtedy renderowanie warunkowe będzie działać.
Text Contains
taka, że użytkownik zazwyczaj klikałby, aby uzyskać dostęp do menu rozwijanego.
=COUNTIF(C:C, C1) > 1
Objaśnienie: C1
Tutaj nie odnosi się do pierwszego wiersza w C. Ponieważ ta formuła jest oceniana przez regułę formatu warunkowego, zamiast tego, gdy formuła jest sprawdzana, czy ma zastosowanie, C1
efektywnie odnosi się do tego, który wiersz jest obecnie oceniany sprawdź, czy wyróżnienie powinno zostać zastosowane. ( Czyli bardziej INDIRECT(C &ROW())
, jeśli to coś dla ciebie znaczy! ). Zasadniczo, oceniając formułę formatu warunkowego, wszystko, co odnosi się do wiersza 1, jest oceniane względem wiersza, przeciwko któremu działa formuła. ( I tak, jeśli użyjesz C2, to poprosisz regułę o sprawdzenie statusu wiersza bezpośrednio poniżej tego, który jest obecnie oceniany ).
Oznacza to, że policz wystąpienia tego, co jest w C1
(bieżąca komórka poddawana ocenie), które znajdują się w całej kolumnie, C
a jeśli jest ich więcej niż 1 (tj. Wartość ma duplikaty), to: zastosuj wyróżnienie ( ponieważ formuła , ogólnie ocenia naTRUE
).
=AND(COUNTIF(C:C, C1) > 1, COUNTIF(C$1:C1, C1) = 1)
Objaśnienie: Podświetla się tylko wtedy, gdy oba COUNTIF
są TRUE
(pojawiają się w środku AND()
).
Pierwszy oceniany termin ( COUNTIF(C:C, C1) > 1
) jest dokładnie taki sam jak w pierwszym przykładzie; to TRUE
tylko wtedy, gdy to, co jest w C1
posiada duplikat. ( Pamiętaj, że C1
skutecznie odnosi się do sprawdzania bieżącego wiersza, aby sprawdzić, czy powinien zostać podświetlony ).
Drugi termin (COUNTIF(C$1:C1, C1) = 1
) wygląda podobnie, ale ma trzy zasadnicze różnice:
Nie przeszukuje całej kolumny C
(jak robi to pierwszy:), C:C
ale zamiast tego rozpoczyna wyszukiwanie od pierwszego wiersza: C$1
( $
zmusza go do dosłownego spojrzenia na wiersz 1
, a nie na którykolwiek wiersz jest oceniany).
A potem zatrzymuje wyszukiwanie w bieżącym wierszu, który jest analizowany C1
.
Wreszcie mówi = 1
.
Tak będzie, tylko TRUE
jeśli nie będzie duplikatów nad aktualnie ocenianym wierszem (co oznacza, że musi to być pierwszy z duplikatów).
W połączeniu z tym pierwszym terminem (który będzie tylko TRUE
wtedy, gdy ten wiersz ma duplikaty) oznacza to, że tylko pierwsze wystąpienie zostanie podświetlone.
=AND(COUNTIF(C:C, C1) > 1, NOT(COUNTIF(C$1:C1, C1) = 1), COUNTIF(C1:C, C1) >= 1)
Objaśnienie: Pierwsze wyrażenie jest takie samo jak zawsze (TRUE
jeśli aktualnie oceniany wiersz jest w ogóle duplikatem).
Drugi termin jest dokładnie taki sam jak poprzedni, z tą różnicą, że jest negowany: ma NOT()
wokół siebie. Dlatego ignoruje to pierwsze wystąpienie.
Wreszcie trzeci termin zbiera duplikaty 2, 3 itd. COUNTIF(C1:C, C1) >= 1
Rozpoczyna zakres wyszukiwania w aktualnie ocenianym wierszu ( C1
w C1:C
). Następnie ocenia tylko TRUE
(zastosuj wyróżnienie), jeśli pod tym (a także tym) znajduje się jeden lub więcej duplikatów: >= 1
(nie może być >=
tak, >
że ostatni duplikat jest ignorowany).
Wypróbowałem wszystkie opcje i żadna nie działała.
Pomogły mi tylko skrypty aplikacji Google.
źródło: https://ctrlq.org/code/19649-find-duplicate-rows-in-google-sheets
U góry dokumentu
1. - przejdź do narzędzia> edytor skryptów
2. - ustaw nazwę swojego skryptu
3. - wklej ten kod:
function findDuplicates() {
// List the columns you want to check by number (A = 1)
var CHECK_COLUMNS = [1];
// Get the active sheet and info about it
var sourceSheet = SpreadsheetApp.getActiveSheet();
var numRows = sourceSheet.getLastRow();
var numCols = sourceSheet.getLastColumn();
// Create the temporary working sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var newSheet = ss.insertSheet("FindDupes");
// Copy the desired rows to the FindDupes sheet
for (var i = 0; i < CHECK_COLUMNS.length; i++) {
var sourceRange = sourceSheet.getRange(1,CHECK_COLUMNS[i],numRows);
var nextCol = newSheet.getLastColumn() + 1;
sourceRange.copyTo(newSheet.getRange(1,nextCol,numRows));
}
// Find duplicates in the FindDupes sheet and color them in the main sheet
var dupes = false;
var data = newSheet.getDataRange().getValues();
for (i = 1; i < data.length - 1; i++) {
for (j = i+1; j < data.length; j++) {
if (data[i].join() == data[j].join()) {
dupes = true;
sourceSheet.getRange(i+1,1,1,numCols).setBackground("red");
sourceSheet.getRange(j+1,1,1,numCols).setBackground("red");
}
}
}
// Remove the FindDupes temporary sheet
ss.deleteSheet(newSheet);
// Alert the user with the results
if (dupes) {
Browser.msgBox("Possible duplicate(s) found and colored red.");
} else {
Browser.msgBox("No duplicates found.");
}
};
4. - zapisz i uruchom
W mniej niż 3 sekundy mój zduplikowany wiersz został pokolorowany. Wystarczy skopiować skrypt.
Jeśli nie wiesz o skryptach aplikacji Google, te linki mogą pomóc:
https://zapier.com/learn/google-sheets/google-apps-script-tutorial/
https://developers.google.com/apps-script/overview
Mam nadzieję, że to pomoże.
=COUNTIFS(A:A; A1; B:B; B1)>1