按共享值對資料列進行分組

Question

我不知道如何正確描述我需要做什麼，所以我舉一個例子。一位同事在 Excel 中有一個資料集，如下所示：

Col A    Col B    Col C
aaaaa    aaaaa    bbbbb
bbbbb    ccccc    ccccc
ccccc    ddddd    eeeee

最終結果應該是這樣的：

Col A    Col B    Col C
aaaaa    aaaaa
bbbbb             bbbbb
ccccc    ccccc    ccccc
         ddddd
                  eeeee

甚至：

      Col A    Col B    Col C
aaaaa Yes      Yes      No
bbbbb Yes      No       Yes

ETC。

（如果有幫助的話，列是蛋白質提取方法，字母是蛋白質 ID - 我們需要確定哪些蛋白質是通過哪種方法提取的）

我的同事正在手動完成此操作，但有足夠的數據，將其自動化將非常有幫助。

Excel中有沒有公式可以自動執行此操作？

Answer 1

這不是一個「交鑰匙」解決方案，但如果您有數千行，這可能會節省您一些精力。 （在文件的草稿副本中執行此操作，以防萬一有什麼東西爆炸或崩潰，因為「撤消」並不總是有效。）注意：此過程是為 Excel 2007 開發的（但我已在Excel 2013 ）。

首先，將所有資料複製到臨時列中；我們稱之為 V。

現在轉到“資料”選項卡，“排序和過濾”群組，然後單擊“進階”：

                       

這將彈出“高級過濾器”對話框：

                 

驗證「清單範圍」是否在第五列顯示您的資料。在“複製到”字段中鍵入“W1”，或按一下該字段，然後按一下 W1（有多種技術可以獲得相同的結果）。按一下“確定”。你應該得到這樣的東西：

                             

即您的唯一資料值的清單。您可能需要對 W 列進行排序。

現在=NOT(ISNA(VLOOKUP($W2,A$2:A$4,1,FALSE)))輸入 X2（將 替換 4為包含資料的最後一行的編號），然後向下拖曳/填充以符合 W 列（即，原始資料中的每個唯一值佔一行）並向右拖曳到 Z 列（即資料中的列數）。

                              

這將為您提供與問題中所需結果的第二種形式相對應的真值表（但使用“TRUE”和“FALSE”而不是“Yes”和“No”）。例如，

• X2 為 TRUE，因為 A 列包含“aaaaa”，
• X3 為 TRUE，因為 A 列包含“bbbbb”，
• Y2 為 TRUE，因為 B 列包含“aaaaa”，
• Y3 為 FALSE，因為 B 列不包含「bbbbb」等。

刪除第 V 列，並在閒暇時修改標題（在第 1 行）。如果您不想在電子表格中保留 AC 列，請複製 WZ 列並貼上值。

---

對公式的一些解釋：我上面給出的公式用於 X 列， 對應 A 列。  由於我使用了，這是對 W 列的絕對引用， 當公式被拖曳/填充到行時 $W2它將引用單元格Wnn任何列的。相較之下，A$2:A$4是對第 2 行到第 4 行的絕對引用，而是對 A 列的相對引用 B$2:B$4。當公式被拖曳到 Z 列時，該引用將自動變更為 C$2:C$4。