ĐỘC ĐÁO từ các cột không liền kề - Mẹo Excel

Hôm trước, tôi đã định tạo một kết hợp duy nhất của hai cột không liền kề trong Excel. Tôi thường làm điều này với Xóa bản sao hoặc với Bộ lọc nâng cao, nhưng tôi nghĩ tôi sẽ thử làm điều đó với chức năng UNIQUE mới sắp có trên Office 365 vào năm 2019. Tôi đã thử một số ý tưởng và không có ý tưởng nào hoạt động. Vì vậy, tôi đã tìm đến bậc thầy của Dynamic Arrays, Joe McDaid, để được hỗ trợ. Câu trả lời khá hay, và tôi chắc chắn rằng tôi sẽ quên nó, vì vậy tôi đang ghi lại nó cho bạn và cho tôi. Tôi chắc chắn rằng, hai năm nữa, tôi sẽ Google làm thế nào để làm điều này và nhận ra "Ồ, xem này! Tôi là người đã viết bài báo về điều này!"

Trước khi đến với hàm UNIQUE, hãy xem những gì tôi đang cố gắng thực hiện. Tôi muốn mọi kết hợp duy nhất giữa Đại diện bán hàng từ cột B và Sản phẩm từ cột C. Thông thường, tôi sẽ làm theo các bước sau:

Sao chép các tiêu đề từ B1 và D1 vào một phần trống của trang tính
Từ B1, chọn Dữ liệu, Bộ lọc, Nâng cao
Trong hộp thoại Bộ lọc Nâng cao, hãy chọn Sao chép Đến Vị trí Mới
Chỉ định các tiêu đề từ Bước 1 làm phạm vi Đầu ra
Chọn hộp Chỉ dành cho Giá trị Duy nhất
Bấm OK

Sao chép hai tiêu đề vào một phần trống trở thành phạm vi đầu ra

Kết quả là mọi sự kết hợp duy nhất của hai trường. Lưu ý rằng Bộ lọc Nâng cao không sắp xếp các mục - chúng xuất hiện theo trình tự ban đầu.

Nhận danh sách duy nhất là một trong những cách sử dụng yêu thích của tôi cho Bộ lọc nâng cao

Quá trình này trở nên dễ dàng hơn trong Excel 2010 nhờ lệnh Loại bỏ các bản sao trên tab Dữ liệu của Ruy-băng. Làm theo các bước sau:

Chọn B1: D227 và Ctrl + C để sao chép
Dán vào một phần trống của trang tính.

Tạo bản sao của dữ liệu vì Xóa bản sao có tính chất phá hủy
Chọn dữ liệu, loại bỏ trùng lặp
Trong hộp thoại Loại bỏ các bản sao, bỏ chọn Ngày. Điều này cho biết Excel chỉ xem xét Đại diện và Sản phẩm.
Bấm OK

Yêu cầu Xóa bản sao để chỉ xem xét Đại diện và Ngày

Kết quả gần như hoàn hảo - bạn chỉ cần xóa cột Ngày.

Xóa cột bổ sung

Câu hỏi: Có cách nào để hàm UNIQUE chỉ nhìn vào các cột B & D không? (Nếu bạn chưa thấy hàm UNIQUE mới, hãy đọc: Hàm UNIQUE trong Excel.)

Yêu cầu =UNIQUE(B2:D227)sẽ giúp bạn có được mọi sự kết hợp độc đáo giữa Đại diện, Ngày tháng và Sản phẩm không phải là thứ chúng tôi đang tìm kiếm.

Làm thế nào chúng ta có thể chuyển hai cột không liền kề vào hàm UNIQUE?

Khi Mảng động được giới thiệu vào tháng 9, tôi đã nói rằng chúng ta sẽ không bao giờ phải lo lắng về sự phức tạp của Ctrl + Shift + Enter công thức nữa. Nhưng để giải quyết vấn đề này, bạn sẽ sử dụng một khái niệm gọi là Lifting. Hy vọng rằng bây giờ, bạn đã tải xuống sách điện tử Excel Dynamic Arrays Straight To The Point của tôi. Lật trang 31-33 để xem giải thích đầy đủ về Lifting.

Xem sách của tôi để biết giải thích đầy đủ về Nâng (và sau đó, khi bạn sắp xếp kết quả, Nâng theo cặp)

Lấy một hàm Excel mong đợi một giá trị duy nhất. Ví dụ: =CHOOSE(Z1,"Apple","Banana")sẽ trả về Apple hoặc Banana tùy thuộc vào việc Z1 chứa 1 (đối với Apple) hay 2 (đối với Banana). Hàm CHOOSE mong đợi một đại lượng vô hướng làm đối số đầu tiên.

Nhưng thay vào đó, bạn sẽ chuyển một hằng số mảng (1,2) làm đối số đầu tiên để CHỌN. Excel sẽ thực hiện thao tác Nâng và tính CHỌN hai lần. Đối với giá trị là 1, bạn muốn các đại diện bán hàng trong B2: B227. Với giá trị của 2, bạn muốn các sản phẩm trong D2: D227.

Yêu cầu CHOOSE trả lại hai câu trả lời

Thông thường, trong Excel cũ, giao điểm ngầm sẽ làm sai lệch kết quả. Nhưng bây giờ Excel có thể đổ kết quả cho nhiều ô, công thức trên trả về thành công một mảng tất cả các câu trả lời trong B và D:

Sự thành công! Tất cả đều xuống dốc từ đây

Tôi cảm thấy như tôi sẽ xúc phạm trí thông minh của bạn để viết phần còn lại của bài báo, bởi vì từ đây nó là siêu đơn giản.

Kết hợp công thức từ ảnh chụp màn hình trước đó trong UNIQUE và bạn chỉ nhận được các kết hợp duy nhất giữa Đại diện bán hàng và Sản phẩm bằng cách sử dụng =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Vẫn chưa được sắp xếp

Để kiểm tra sự hiểu biết của bạn, hãy thử thay đổi công thức trên để trả về tất cả các kết hợp duy nhất của ba cột: Đại diện bán hàng, Sản phẩm, Màu sắc.

Đầu tiên, thay đổi hằng số mảng để tham chiếu đến (1,2,3).

Sau đó, thêm đối số thứ tư vào CHỌN để trả về màu từ E2: E227 : =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Trả lại sự kết hợp duy nhất của ba cột

Sẽ rất hay khi sắp xếp các kết quả đó, vì vậy chúng ta chuyển sang Sắp xếp bằng công thức sử dụng SORT và SORTBY.

Thông thường, hàm sắp xếp theo cột đầu tiên tăng dần sẽ là =SORT(Array)hoặc =SORT(Array,1,1).

Để sắp xếp theo ba cột, bạn cần thực hiện một số thao tác nâng từng đôi với =SORT(Array,(1,2,3),(1,1,1)). Trong công thức này, khi bạn đến đối số thứ hai của SORT, Excel muốn biết cột nào cần sắp xếp. Thay vì một giá trị duy nhất, hãy gửi ba cột bên trong một hằng số mảng: (1,2,3). Khi bạn đến đối số thứ ba, trong đó chỉ định 1 cho Tăng dần hoặc -1 cho Giảm dần, hãy gửi một hằng số mảng với ba chữ 1 để biểu thị Tăng dần, Tăng dần, Tăng dần. Ảnh chụp màn hình sau đây cho thấy =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Để biết thêm về cách nâng theo cặp, hãy xem trang 34 của Mảng động Excel Thẳng đến Điểm.

Ít nhất cho đến cuối năm 2018, bạn có thể tải xuống miễn phí sách Mảng động Excel bằng liên kết ở cuối trang này.

Tôi được khuyến khích nhận thấy rằng câu trả lời cho câu hỏi hôm nay hơi phức tạp. Khi Mảng động ra mắt, tôi ngay lập tức nghĩ đến tất cả các công thức tuyệt vời được đăng trên Bảng tin bởi Aladin Akyurek và những người khác và làm thế nào những công thức đó sẽ trở nên đơn giản hơn nhiều trong Excel mới. Nhưng ví dụ hôm nay cho thấy vẫn cần những thiên tài công thức để tạo ra những cách mới để sử dụng Mảng Động.