Loại bỏ các ô trống và xắp xếp lại dữ liệu?! (2 người xem)

[omertaj123]

2 Pro! sau khi tính toán thì em thu được 1 bảng dữ liệu nhưng trong bảng đó có chứa các ô trống, giờ em muốn loại bỏ các ô trống đó và xắp xếp lại sao cho các ô trống đó sẽ được lấp đầy bởi các ô có dữ liệu ở ngay dưới nó, kết quả là em sẽ có 1 bảng mà các cột dữ liệu sẽ liên tục từ trên xuống (em muốn xắp xếp bảng A thành bảng B như trong ví dụ). Còn đây là file đính kèm và 1 câu hỏi nữa. mong các Pro chỉ giáo. Thank.

 

[Phanhanhdai]

Bạn chọn vùng cần copy, ấn Ctrl+G\Special\Costants, OK như vậy dữ liệu trên cột đã được chọn (bôi đen), chọn copy và paste vào nơi bạn muốn. Về câu kiểm tra bạn dùng công thức sau:IF(COUNTIF(E3:E12;">=4")>=1;1;0) là OK.

 

[Phanhanhdai]

Bài này có thể sử dụng hàm Excel thông thường giải quyết được không?

Dĩ nhiên với VBA bài này chắc quá đơn giản đối với các sư phụ trên diễn đàn, em biết ngoài cách thủ công trên trước kia em có được thày ndu chỉ cho cách dùng name để giải quyết những bài toán lọc kiểu này. Nhưng em băn khoăn là nếu chỉ sử dụng công thức Excel thông thường (không thông qua đặt name) thì liệu có giải quyết được bài toán này không, xin các sư phụ cho ý kiến chỉ bảo giúp.

 

[ndu96081631]

Dĩ nhiên với VBA bài này chắc quá đơn giản đối với các sư phụ trên diễn đàn, em biết ngoài cách thủ công trên trước kia em có được thày ndu chỉ cho cách dùng name để giải quyết những bài toán lọc kiểu này. Nhưng em băn khoăn là nếu chỉ sử dụng công thức Excel thông thường (không thông qua đặt name) thì liệu có giải quyết được bài toán này không, xin các sư phụ cho ý kiến chỉ bảo giúp.

Xóa dữ liệu ở bên dưới bảng rồi gõ vào cell K3 công thức sau:

=IF(ROWS($1:1)>COUNT(IF(E$3:E$102<>"",ROW($1:$100))),"",INDEX(E$3:E$102,SMALL(IF(E$3:E$102<>"",ROW($1:$100),""),ROWS($1:1))))

Bấm tổ hợp Ctrl + Shift + Enter rồi kéo fill xuống dưới và sang phải
Chẳng có name nào hết nha

 

[omertaj123]

Master

Xóa dữ liệu ở bên dưới bảng rồi gõ vào cell K3 công thức sau:

=IF(ROWS($1:1)>COUNT(IF(E$3:E$102<>"",ROW($1:$100))),"",INDEX(E$3:E$102,SMALL(IF(E$3:E$102<>"",ROW($1:$100),""),ROWS($1:1))))

Bấm tổ hợp Ctrl + Shift + Enter rồi kéo fill xuống dưới và sang phải
Chẳng có name nào hết nha

Excellent. Nhưng em đọc mãi rồi mà không hiểu, nếu gặp phải vấn đề tương tự thì chắc em không làm được nên anh có thể giải thích rõ hơn cách làm (thuật toán) của anh được không ạ?
Trong trường hợp em muốn loại bỏ ô trống và xắp xếp lại dữ liệu theo hàng thì phải làm sao ạ? ( Ví dụ em muốn biến Bảng C thành Bảng D trong file đính kèm)

 

[ndu96081631]

Excellent. Nhưng em đọc mãi rồi mà không hiểu, nếu gặp phải vấn đề tương tự thì chắc em không làm được nên anh có thể giải thích rõ hơn cách làm (thuật toán) của anh được không ạ?
Trong trường hợp em muốn loại bỏ ô trống và xắp xếp lại dữ liệu theo hàng thì phải làm sao ạ? ( Ví dụ em muốn biến Bảng C thành Bảng D trong file đính kèm)

Kéo fill theo chiều dọc ta dùng hàm ROW, vậy đương nhiên kéo fill theo chiều ngang ta dùng hàm COLUMN rồi
Đầu tiên phải bố trí lại dữ liệu tí xíu, kéo bảng D xuống dưới bảng C (để tạo khoảng trống bên phải bảng C)




Tại cell R12, gõ vào công thức:

=IF(COLUMNS($A:A)>COUNT(IF($R3:$DM3<>"",COLUMN($A:$CV),"")),"",INDEX($R3:$DM3,1,SMALL(IF($R3:$DM3<>"",COLUMN($A:$CV),""),COLUMNS($A:A))))

Cũng bấm tổ hợp phím Ctrl + Shift + Enter rồi kéo fill qua phải và xuống dưới
Lưu ý những điểm tương đồng giữa 2 công thức trên:
- ROWS($1:1) là tính từ dòng 1 sẽ tương đương với COLUMNS($A:A) là tính từ cột 1
- ROW($1:$100) là 100 dòng sẽ tương đương với COLUMN($A:$CV) là 100 cột
- E$3:E$102 là dữ liệu 100 dòng sẽ tương đương với $R3:$DM3 là dữ liệu 100 cột

 

[omertaj123]

Good luck for you!^^

 

[dongducnhiem]

Gởi anh ndu trong bài này nếu ta muốn đặt name thì ta phải thiết lập công thức như thế nào? nhờ anh hướng dẫn. Cảm ơn nhiều

 

[hoamattroicoi]

Gởi anh ndu trong bài này nếu ta muốn đặt name thì ta phải thiết lập công thức như thế nào? nhờ anh hướng dẫn. Cảm ơn nhiều

Muốn đặt name thì bạn đưa cái IF đó vào name là Ok, ví dụ trong công thức tại bài #6 :

=IF(COLUMNS($A:A)>COUNT(IF($R3:$DM3<>"",COLUMN($A:$CV),"")),"",INDEX($R3:$DM3,1,SMALL(IF($R3:$DM3<>"",COLUMN($A:$CV),""),COLUMNS($A:A))))

Tạo name tên là THH bằng cách nhấm Ctrl+F3 và đưa đoạn IF vào :

=IF(Sheet1!$R3:$DM3<>"",COLUMN(Sheet1!$A:$CV),"")

Và công thức đầu tiên sẽ trở thành :

=IF(COLUMNS($A:A)>COUNT(THH),"",INDEX($R3:$DM3,1,SMALL(THH,COLUMNS($A:A))))

Kết thúc bằng phím Enter!

 

[sanzin111085]

Format của ô bị biến đổi

Mình thấy dùng lệnh sort cho từng cột cũng hay! mỗi tôi mất thời gian làm trên từng cột.