Loại trừ khoảng trắng khi data bị Replace

[phitruong141]

Chào các bác !
Em có 1 câu hỏi be bé .
Tình hình là em có 1 sheet như sau : Cột A có các giá trị :A1= "CP13-01 " , A2="CP13-02 "

Em muốn viết 1 cái name làm sao cho khi viết công thức ở Sheet khác nó hiểu các giá trị A1, A2 đó như là Substitute (A1) .
đó là 1 phần ,
Câu hỏi 2 là : Hàm Substitute có thể viết nó theo dạng mảng được ko ? Em muốn loại trừ tất cả khoản trắng trong cột đó mà ko phải thêm cột phụ.
Cám ơn mọi người đã đọc ! bạn nào hông hỉu câu hỏi vui lòng nhắc lại mình giải thích nhe !

 

[dat_butmuc]

Chào các bác !
Em có 1 câu hỏi be bé .
Tình hình là em có 1 sheet như sau : Cột A có các giá trị :A1= "CP13-01 " , A2="CP13-02 "

Em muốn viết 1 cái name làm sao cho khi viết công thức ở Sheet khác nó hiểu các giá trị A1, A2 đó như là Substitute (A1) .
đó là 1 phần ,
Câu hỏi 2 là : Hàm Substitute có thể viết nó theo dạng mảng được ko ? Em muốn loại trừ tất cả khoản trắng trong cột đó mà ko phải thêm cột phụ.
Cám ơn mọi người đã đọc ! bạn nào hông hỉu câu hỏi vui lòng nhắc lại mình giải thích nhe !

1. Chưa hiểu bạn đặt name để làm gì - chưa hiểu lắm (cần file kèm, yêu cầu, kết quả mong muốn)
2. Hàm SUBSTITUTE() có thể viết theo dạng mảng được, tùy vào trường hợp nào thôi?
Còn việc loại trừ khoảng trắng trong cột thì dùng Find and Replace (Ctrl+H) -> gõ khoảng trắng trong mục Find what sau đó nhấn Replace all.

 

[phitruong141]

Ý mình là cái Sheet đó ! Sau khi mình đổ data ra rồi chép đè luôn ! làm động tác Replace hơi thủ công !
mình đổ data từ Phần mềm ! nên nó có cả khoảng trắng đó
file em gởi đây ! nhờ các bác
Nhớ là Sheet 1 ko thêm cột hay thêm động tác như Replace nhe ! hi hi !
ay dà ! em hông biết Attack file hjc !

 

[sealand]

Mình xem file có lẽ bạn định thế này thì phải, bạn chép công thức sau vào ô C2 rồi chép sang ngang và down thử xem. Name là gì nữa?

=SUMPRODUCT(--(TRIM(Sheet1!$B$2:$B$8)= Sheet2!$B2),--(Sheet1!$A$2:$A$8=Sheet2!C$1),Sheet1!$C$2:$C$8)

 

[ptm0412]

Nhớ là Sheet 1 ko thêm cột hay thêm động tác như Replace nhe ! hi hi !

Giả sử dữ liệu của bạn cả ngàn dòng.
Bạn so sánh 2 việc:
Find and replace 1 lần cho cả ngàn dòng đó, bằng 1 cái click.
Viết công thức dựa vào 1 name, Name này tính toán cả nhiều ngàn lần, hoặc viết công thức mảng cho nhiều ngàn ô.

VIệc nào thủ công hơn? Việc nào làm cho máy chạy nặng hơn?

Ngoài ra, có 1 cách mà không cần loại bỏ khoảng trắng gì cả, đó là để nguyên xi khoảng trắng như vậy bên sheet 2.

Và còn 1 cách không dùng công thức, cũng không cần loại bỏ khoảng trắng, đó là Pivot table.

 

[sealand]

Ptm 0412 à, nhìn vào cấu trúc của sheet2 mình đoán chừng tác giả muốn tổng hợp chi phí theo từng khoản và từng đơn vị. Dữ liệu ở sheet1 lấy từ việc kết xuất từ phần mềm nên tác giả không muốn Replace... vì như vậy mỗi lần lại phải hiệu đính nó. Như sau khi kết xuất ta cứ đổ thẳng vào Excel

 

[ptm0412]

Ptm 0412 à, nhìn vào cấu trúc của sheet2 mình đoán chừng tác giả muốn tổng hợp chi phí theo từng khoản và từng đơn vị. Dữ liệu ở sheet1 lấy từ việc kết xuất từ phần mềm nên tác giả không muốn Replace... vì như vậy mỗi lần lại phải hiệu đính nó. Như sau khi kết xuất ta cứ đổ thẳng vào Excel

Tại tác giả nói replace là thủ công. Mình khẳng định là replace 1 lần cho cả ngàn dòng mà chỉ nhấn nút 1 cái thì không thủ công tí nào. Một tháng mới lập báo cáo chi phí 1 lần, mà cứ cho là mỗi ngày lấy dữ liệu 1 lần, thì bỏ 3 giây ra để replace có hao tốn gì đâu? Trong khi đó với công thức sumproduct vốn đã ì ạch rồi lại cưu mang thêm cái Trim nữa.

Mình cũng đã gợi ý 2 cách giải quyết nếu vẫn không muốn replace:
- Danh sách chi phí bên sheet 2 cũng để nguyên khoảng trắng như dữ liệu lấy về, có sao để vậy
- Dùng Pivot table.

 

[sealand]

Đúng là phương án của Ptm 0412 quá OK rồi, trong đó Pivot mình thấy hiệu quả nhất về tốc độ cũng như thuận tiện sử lý. Ở đây mình cũng như mọi người vẫn đang đoán ý tác giả là gì cơ. Với câu hỏi thế này khó có ai dám chắc mình hiểu câu hỏi.

 

[phitruong141]

Ptm 0412 à, nhìn vào cấu trúc của sheet2 mình đoán chừng tác giả muốn tổng hợp chi phí theo từng khoản và từng đơn vị. Dữ liệu ở sheet1 lấy từ việc kết xuất từ phần mềm nên tác giả không muốn Replace... vì như vậy mỗi lần lại phải hiệu đính nó. Như sau khi kết xuất ta cứ đổ thẳng vào Excel

Bạn SEALAND nói đúng ý mình rồi ! Sorry nhe ! tại lần đầu mình hỏi trên diễn đàn nên hông biết cách trình bày đúng ý tưởng .
Các bạn cho mình hỏi nếu dùng Pivot thì dùng ntn ? Mình hơi gà về khoản này !
Thứ 2 nữa mình nói thủ công là do mình có nhiều file excel link công thức báo cáo . Nên khi đổ trong phần mềm ra , mình chỉ việc paste vào folder đó để nó link thôi ! nếu viết được công thức mà nhẹ thì tốt quá ! Còn ko thì dùng cthức của Sealand :
=SUMPRODUCT(--(TRIM(Sheet1!$B$2:$B$8)= $B2),--(Sheet1!$A$2:$A$8=C$1),Sheet1!$C$2:$C$8)
như thế này cũng ổn rồi ! nhưng cho mình hỏi cái dấu -- để làm cái rì vậy bạn

Cám ơn các bạn rất rất nhều !

 

[Hai Lúa Miền Tây]

Bạn SEALAND nói đúng ý mình rồi ! Sorry nhe ! tại lần đầu mình hỏi trên diễn đàn nên hông biết cách trình bày đúng ý tưởng .
Các bạn cho mình hỏi nếu dùng Pivot thì dùng ntn ? Mình hơi gà về khoản này !
Thứ 2 nữa mình nói thủ công là do mình có nhiều file excel link công thức báo cáo . Nên khi đổ trong phần mềm ra , mình chỉ việc paste vào folder đó để nó link thôi ! nếu viết được công thức mà nhẹ thì tốt quá !

Bạn vào trang sau tham khảo thêm nhé

Quà tặng thành viên nhân dịp SN4: Pivot Table 2003
Cách tạo Pivot Table và các vấn đề liên quan