Mam dwie listy (unikalne zestawy) w arkuszu kalkulacyjnym i chcę uzyskać ich przecięcie.
Czy jest na to łatwy sposób?
Oto przykład tego, co chcę osiągnąć:
Lista 1 Lista 2 Oczekiwany wynik a 1 e b 2 f ce d 4 ef f 6
Odpowiedzi:
Nie ma potrzeby wskakiwania do GAS, standardowe funkcje arkuszy kalkulacyjnych radzą sobie z tym łatwo.
Wklej następujące elementy do komórki w swoim przykładzie
=iferror(filter($A$2:$A, match($A$2:$A, B2:$B , false)), "no match")
Funkcja dopasowania generuje iloczyn kartezjański dwóch pełnych zakresów błędów z wyjątkiem przypadków znalezienia dopasowania. Zwraca wskaźnik do 2. Zakres gdy mecz jest znaleziony.
Funkcja filtrowania wyrzuca wszystkie bzdury i zwraca tylko poprawnie zindeksowane wartości.
IFERROR pomaga uzyskać schludny wynik, jeśli nie znaleziono żadnego meczu w ogóle.
Zakresy mogą mieć dowolną długość, jak wskazuje idiom $ A $ 2: $ A.
Ten mały skrypt porównuje dwa zakresy jako formułę:
function COMPARE(array1, array2) {
var array = [];
for(i=0; i<array1.length; i++) {
for(j=0; j<array2.length; j++) {
if(array1[i][0] == array2[j][0]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
array.push([array1[i][0]]);
}
}
}
return array;
}
W swoim arkuszu możesz dodać w komórce D2 następującą formułę:
=COMPARE(A2:A7,B2:B7)
Dodaj skrypt za pomocą menu narzędzi, edytora skryptów.
Jeśli szukasz sposobu odjęcia dwóch zakresów (elementów zakresu 1, które nie znajdują się w zakresie 2), takich jak:
List 1 List 2 Expected result
a 1 a
b 2 b
c e c
d 4 d
e f
f 6
Oto formuła dla Ciebie:
=iferror(filter($A$2:$A, iserror(match($A$2:$A, B2:$B , false))), "no match")
Ten mały skrypt porównuje dwa zakresy jako formułę:
function intersect(array1, array2) {
var array = [];
for(i=0; i<array1.length; i++) {
for(j=0; j<array2.length; j++) {
if(array1[i][0] == array2[j][0]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
array.push([array1[i][0]]);
}
}
}
return array;
}
Dodatkowy:
function extersect(array1, array2) {
var array = [];
var tmp = true;
for(i=0; i<array1.length; i++) {
for(j=0; j<array2.length; j++) {
if(array1[i][0] == array2[j][0]) {
// the extra square brackets will make it a 2D array,
// aligning it vertically
tmp = false;
}
}
if(tmp == true)
{
array.push([array1[i][0]]);
}
tmp = true;
}
return array;
}
W swoim arkuszu możesz dodać w komórce D2 następującą formułę:
=intersect(A2:A7,B2:B7)
Dodaj skrypt za pomocą menu narzędzi, edytora skryptów.
= przecięcie (A2: A7, B2: B7) zwraca wyniki, które występują na obu tablicach tablica 1, tablica 2.
= extersect (A2: A7, B2: B7) zwraca wartości z tablicy 1, która nie istnieje w zakresie B2: B7