Jak porównać dwie tabele ze zduplikowanym odniesieniem do klucza

Mam dwa arkusze robocze, aw środku jest wiele kolumn i wierszy. Ten sam kluczowy odnośnik to „numer zamówienia” z dwoma arkuszami. Niestety numery zamówień są zduplikowane. czy jest jakiś sposób, aby porównać dwa arkusze w jednym arkuszu i jeden raz?

Arkusz A

ordernumber cost QTY    date ......
   A10       50   2   12/05/2018
   A18       20   2   12/05/2018
   A10       50   3   12/01/2018
   A18       20   10  12/02/2018
   A13       50   7   12/04/2018

Arkusz B

ordernumber cost QTY    date ......
  A10        50   3    12/01/2018
  A13        50   7    12/04/2018
  A18        20   5    12/05/2018 
  A18        20   2    12/02/2018

porównaj arkusz

ordernumber  QTYA     QTY B  GAPQTY
  A10          5       3      2
  A18         12       7      5
  A13         7        7      0

microsoft-excel worksheet-function

— dora chen
źródło

numery zamówień są zduplikowane Użyj SUMIF () zamiast pojedynczej wartości uzyskanej za pomocą VLOOKUP () ...

— Akina

Jak to działa:

Napisać tej tablicy formułę w komórce A2z porównania arkusz , zakończyć Ctrl+Shift+Enteri wypełnić go.

 {=IFERROR(IFERROR(INDEX(Sheet1!$A$2:$A$6, MATCH(0, COUNTIF($A$1:A1, Sheet1!$A$2:$A$6), 0)), INDEX(Sheet2!$A$2:$A$5, MATCH(0, COUNTIF($A$1:A1, Sheet2!$A$2:$A$5), 0))), "")}

Wprowadzić tę tablicę formułę w komórce B2z porównania arkusz , wykończenie z Ctrl+Shift+Enter& Fill dół.
```
 {=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*(Sheet1!$C$2:$C$6))}
```
Napisać tę formułę w komórce C2z porównania arkusz , zakończyć Ctrl+Shift+Enteri wypełnić go.
```
 {=SUMPRODUCT((Sheet2!$A$2:$A$5=$A2)*(Sheet2!$C$2:$C$5))}
```
Wreszcie w komórki D2o Porównaj Karcie napisać tę formułę i wypełnić dół.
```
 =B2-C2
```

— Rajesh S.
źródło

Dla 2.3.4 są w porządku, ale 1. nie działa dobrze. każde zamówienie powiela dwie linie, po co wstawić tutaj 0 dopasowania? a jeśli mam więcej niż 10000 wierszy, czy zajmie to dużo czasu? czy jest jakiś sposób na użycie tabeli przestawnej lub zapytania?

— dora chen,

@dorachen ,, Zero z dopasowaniem wykonaj, Countifaby utworzyć unikalne wiersze, a zrzut ekranu jest częścią aktywnego arkusza, nad którym pracowałem ze wszystkimi działającymi formułami przed opublikowaniem tutaj. Problem jest na twoim końcu! Istnieje kilka metod, ale sugerowana metoda jest lepsza i szybsza ,, może działać dla ton rzędów.

— Rajesh S

Zgadzam się z Tobą. Problem jest po mojej stronie, naprawdę nie znam tablicy, ale próbuję wiele razy i nadal nie działa. Poniżej znajduje się moja formuła, czasami trzy komórki działają, a czasami tylko dwie komórki. nie mam pojęcia dlaczego? = IFERROR (IFERROR (INDEKS („M10”! L: L, MATCH (0, COUNTIF ($ A $ 1: A6, „M10”! L: L), 0))), INDEX („Wszystkie zamówienia”! A: A , MATCH (0, COUNTIF ($ A $ 1: A6, „All order”! A: A), 0))), ””) BTW, naprawę i zapisanie zajmuje mi naprawdę dużo czasu.

— dora chen,

@dorachen, pomyłka znalazłem jest to, że popełnił błąd, z Countif data Range,. Myślę, że używasz tej formuły w arkuszu M10. Napisz oba w Countiften sposób COUNTIF($A$1:A1, rozwiąże problem.

— Rajesh S

Kontynuuj ... moją sugestią jest najpierw wypróbowanie ćwiczenia w mniejszym zakresie danych, jak pokazałem wtedy na prawdziwym zakresie.

— Rajesh S,