Trong Excel, tính năng Merge Cells (gộp ô) tưởng chừng rất tiện lợi để trình bày bảng tính gọn gàng, đẹp mắt. Tuy nhiên, đằng sau sự “đẹp” ấy lại tiềm ẩn hàng loạt rắc rối khi xử lý dữ liệu, sắp xếp, lọc, hay xuất báo cáo. Nhiều người chỉ nhận ra “tác hại” của Merge Cells khi bảng tính trở nên khó kiểm soát hoặc bị lỗi công thức.

Trong bài viết này mình sẽ trình bày 1 Case Study thực tế mà người dùng đang tự làm khó mình bằng Merge Cells.

Hình ảnh phía trên là dữ liệu thực tế về BOM của khách hàng. Chúng ta có thể hiểu 1 cách đơn giản ví dụ như mã hàng 500554422 thì được tạo nên bởi 3 mã hàng được quy định trên phần mềm SAP là 500080653 (Ty M8 105), 500081980 (Long đền), 500083130 (Ty nối MA) với số lượng lần lượt là 2, 2 và 2.

Giờ người dùng muốn làm 1 việc đó là nếu bạn gõ công thức vào ô G2, … Thì sẽ tra cứu được Mã SAP, Tên và số lượng của mã hàng tương ứng.

Vậy giờ chúng ta cần phải bắt đầu từ đâu?

Trước tiên các bạn cần phải trả lời câu hỏi đó là với vùng dữ liệu A2:A10 thì những ô nào chứa mã sản phẩm?

Câu trả lời rất đơn giản đó là A2, A5, A8 vì bạn phải biết rằng đó là khi bạn đã sử dụng Merge Cells thì dữ liệu của bạn sẽ chỉ có ở ô đầu tiên của Vùng Merge mà thôi.

Vậy giờ làm thế nào để viết công thức dựa mà mã sản phẩm rồi tra cứu các thông tin SAP, Tên và Số lượng?

Ở đây chúng ta có 2 phương pháp.

Phương pháp 1 đó là bạn tạo 1 công thức để Fill dữ liệu mất do Merge vào (Trong trường hợp bạn vẫn muốn giữ Form mẫu ban đầu)

Phương pháp 2 khó khăn hơn đó là bạn sẽ tạo ra 1 Array chứa tất cả dữ liệu đã Fill trong đó bằng công thức (Đây là 1 phương pháp khó nhưng trong khuôn khổ bài viết này mình vẫn sẽ cố gắng giải đáp cho các bạn)

Với phương pháp 1 thì bạn sẽ có rất nhiều cách nhưng ở đây mình sẽ chỉ trình bày 2 phương pháp cho ngắn gọn như sau.

Đầu tiên bạn gõ công thức =A2 vào 1 ô bất kỳ ở dòng thứ 2.

Sau đó từ dòng thứ 3 trở đi gõ công thức =IF(A3<>"",A3,N2) xong kéo công thức xuống hết.

Ta sẽ có kết quả như hình dưới.

Bạn có thể sử dụng công thức sau.

=LOOKUP(2,1/($A$2:A2<>""),$A$2:A2)

Sau đó kéo hết cho các ô bên dưới.

Bạn có thể sử dụng cách nào cũng được miễn thu được kết quả như hình trên là được.

Với phương pháp thứ 2 thì bạn sẽ tạo 1 Array Fill toàn bộ dữ liệu trên. Điểm đặc biệt của phương pháp 2 này đó là kết quả như hình trên sẽ được lưu trong 1 Array, được hiểu là nó được lưu bên trong 1 công thức chứ không lưu trên Sheet như vừa rồi nữa. Phương pháp này tuy khó hơn nhưng lại vô cùng thích hợp cho những bạn thích trò không chơi với bảng phụ.

Ở đây mình phải nói rõ là nếu bạn là người sử dụng Office 365 hay các phiên bản Excel sau này thì Microsoft đã cải thiện Excel Engine khá nhiều nên việc có bảng phụ hay không có bảng phụ cũng không khác biệt quá nhiều về tốc độ xử lý nên bạn không thích khó khăn thì bạn có thể dừng ngay ở phương pháp 1 nhé.

Nếu bạn không sử dụng Office 365 thì bạn sử dụng công thức dưới đây.

=LOOKUP(ROW($A$1:$A$9),ROW($A$1:$A$9)/($A$2:$A$10<>""),$A$2:$A$10)

Còn nếu bạn sử dụng Office 365 hoặc Office của bạn đang sử dụng có hàm SCAN thì bạn có thể sử dụng như dưới

=LET(r, $A$2:$A$10,SCAN("", r, LAMBDA(a,b, IF(b <> "", b, a))))

Mình tin đây là lần đầu tiên bạn sử dụng các công thức như thế này nhưng tin mình đi, nó chạy tốt đấy.

Bạn có thể xem kết quả mình đã Demo như hình dưới (Lưu ý là việc Spill Range chỉ bắt đầu có từ Office 2021, Office 2024, Office 365 và Office Online nên nếu các bạn sử dụng các phiên bản Office 2019 trở về trước thì sẽ không có tính năng này)

Vậy bây giờ coi như bạn đã Fill được dữ liệu vào các ô trống do Merge Cells. Giờ chúng ta sẽ làm gì tiếp?

Đương nhiên sẽ là viết hàm tìm kiếm để lấy kết quả về thôi.

Mình sẽ chia sẻ cho các bạn công thức mình đã viết.

=INDEX($B$2:$D$10,AGGREGATE(15,6,ROW($B$1:$B$9)/($E$2:$E$10=LOOKUP(2,1/($G$2:$G2<>""),$G$2:$G2)),MOD(ROW(A1)-1,3)+1),MATCH(H$1,$B$1:$D$1,0))

=INDEX($B$2:$D$10,AGGREGATE(15,6,ROW($B$1:$B$9)/(LOOKUP(ROW($A$1:$A$9),ROW($A$1:$A$9)/($A$2:$A$10<>""),$A$2:$A$10)=LOOKUP(2,1/($G$17:$G17<>""),$G$17:$G17)),MOD(ROW(A1)-1,SUMPRODUCT(--(LOOKUP(ROW($A$1:$A$9),ROW($A$1:$A$9)/($A$2:$A$10<>""),$A$2:$A$10)=LOOKUP(2,1/($G$17:$G17<>""),$G$17:$G17))))+1),MATCH(H$1,$B$1:$D$1,0))

Công thức ở trên là áp dụng cho vùng H2:J10 và làm có thông qua cột phụ.

Còn không thông qua cột phụ mà muốn động hơn. Tức là bạn có thấy dữ liệu demo mẫu thì mỗi mã sản phẩm chỉ được tạo nên bởi 3 mã SAP?

Nếu nhiều hơn thì sao? Đó chính là lý do mình áp dụng công thức công thức thứ 2 đấy.

Lưu ý mình đã cố gắng sử dụng các hàm của Office 2019 trở về trước để giải quyết bài toán nhưng thực sự mình tin các bạn có cố làm như mình thì sẽ rất là đau đầu, rất là vất vả, nhiều lúc mình thề là các bạn nản đến mức bảo thôi copy tay cho nhanh chứ viết công thức làm gì nữa?

Đấy là lúc mình tự nghĩ là nếu mình sử dụng các hàm mới trên Office 365 xem có nhàn hơn hay không nhỉ?

=LET(arr_result, FILTER($B$2:$D$10,LOOKUP(ROW($A$1:$A$9),ROW($A$1:$A$9)/($A$2:$A$10<>""),$A$2:$A$10)=LOOKUP(2,1/($G$29:$G29<>""),$G$29:$G29)), CHOOSEROWS(arr_result, MOD(ROW(A1),ROWS(arr_result))+1))

Đương nhiên là được và công thức dễ dàng hơn nhiều so với việc viết sử dụng INDEX, AGGREGATE bên trên.

Nhưng mà mình tin là kể cả đã sử dụng đến các hàm Excel 365 thì bạn vẫn thấy nó thực sự khó đúng không?

Bạn sẽ phải trăn trở là có cách nào dễ dàng hơn không. Bạn sẽ không muốn sử dụng mấy cái hàm dài dằng dặc và rất khó hiểu như LOOKUP đâu. Đó là lúc mà Power Query xuất hiện.

Ta sẽ tiến hành chuẩn hoá dữ liệu Power Query trước.

Ta sẽ đi tới Tab Data > Chọn 1 ô bất kỳ trong vùng dữ liệu này. Sau đó ấn vào From Table/Range ở trên thanh công cụ trong nhóm Get & Transform Data.

Bạn chọn như hình và nhớ tích vào My table has headers nhé (Điều này đảm bảo Power Query sẽ nhận biết dòng trên cùng của bạn là dòng tiêu đề)

Dữ liệu của bạn sẽ được load ra Power Query như hình.

Nhiệm vụ của bạn giờ khá đơn giản đó là chọn cột Mã. Sau đó chuột phải và chọn Fill Down.

Và đây là kết quả.

Đến đây rồi thì bạn đã thấy các ô Merge được Fill rất nhanh thông qua Power Query.

Giờ ta sẽ tới Tab Home > Chọn Close and Load > Close and Load to…

Tại đây bạn cho như hình và ấn OK.

Giờ bạn sẽ có dữ liệu dạng Table như này. Bạn muốn build 1 báo cáo như bên trên thì chỉ cần Pivot Table là xong.

Bạn sẽ kéo báo cáo Pivot Table ra như hình.

Sau đó bạn vào Tab Design > Chọn Report Layout > Chọn Show in Tabular Form

Đến đây bạn vào Design > Sub Total và Grand Total tắt hết đi.

Tiếp theo bạn sẽ sang Tab PivotTable Analyze và trong nhóm Show cuối cùng bên tay phải bạn sẽ tắt +/- button đi.

Giờ bạn muốn Merge Cells?

Rất đơn giản bạn hãy chuột phải vào Pivot Table chọn PivotTable Options…

Tại đây bạn chọn Merge and Center Cells with Labels.

Và đây là kết quả cuối cùng.

Không cần bất cứ công thức nào nữa. Quá đơn giản đúng không nào.

Đến tận đây của bài viết thì mình tin chắc là các bạn sẽ thích cái phương pháp cuối cùng này hơn. Nhiều bạn có thể sẽ nói tại sao cuối bài dài dằng dặc mình mới viết phương pháp tối ưu nhất?

Đó là nếu mình không chỉ cho bạn nếu không làm thì bạn sẽ vất vả sao thì các bạn có nghĩ phương án mình làm là tối ưu nhất không? Đương nhiên là không.

Đến đây bài viết chia sẻ của mình đã hết rồi. Hẹn gặp lại các bạn trong các bài chia sẻ khác nhé.