使用 Excel Countif 函數比對兩份資料



當比對兩方的數據時,不應該因為其中一方擁有較多的資料而認定其為母體值,而將較少的一方當作樣本值。

舉例來說,如果 A 是包含全班名單 40 人的清單,而 B 則是手動填寫的清單,其中僅包含 38 人,很多人會直接使用 A 清單,刪除 B 清單中重複的資料,以期望找到消失的兩個人。

但在現實中,你得到的結果可能不會完全剩下兩個人,而可能多或少。這主要是因為需要考慮以下因素:A 清單本身可能存在重複的數值、B 清單本身也可能存在重複的數值、以及 B 清單可能與 A 清單並非包含關係,而只是兩者的交集。

這時候最好的方法,就是使用 EXCEL 的 COUNTIF 函數,先對 A 清單自我檢查 =COUNTIF(A:A,A2),確定沒有自我重複;再對 B 清單自我檢查=COUNTIF(B:B,B2),確定沒有自我重複;然後是用雙欄分別為 =COUNTIF(B:B,A2) 與 =COUNTIF(A:A,B2) 來做檢查。

When comparing data from two sources, one should not assume that the one with more data is the population value and the one with less data is the sample value.

For example, if A is a list containing the names of 40 students in a class, and B is a manually filled list containing only 38 students, many people would directly use the A list, delete duplicate data from the B list, and hope to find the two missing students.

However, in reality, the result you get may not be exactly two students remaining, but could be more or less. This is mainly because the following factors need to be considered: the A list itself may contain duplicate values, the B list itself may also contain duplicate values, and the B list may not be a subset of the A list, but rather their intersection.

At this point, the best method is to use the COUNTIF function in Excel, first self-check the A list with =COUNTIF(A:A,A2) to make sure there are no duplicates; then self-check the B list with =COUNTIF(B:B,B2) to make sure there are no duplicates; and then use the two columns =COUNTIF(B:B,A2) and =COUNTIF(A:A,B2) to perform the check.

小泉

喜愛用文字說明自己眼中所見的一切

張貼留言

較新的 較舊

نموذج الاتصال