Giúp tạo công thức lọc dữ liệu (1 người xem)

[thong_xanh2003]

Tôi có file tổng hợp nhập xuất hàng hoá gửi kèm,bình thường có thể vào cả hàng nhâp và xuất trên cùng một sheet rồi dùng autofilter để lọc theo tên từng mặt hàng, nhưng ở đây số lượng mặt hàng lên tới gần 600 nên làm như vậy sheet rất nặng và dễ nhầm lẫn.Tôi chưa biết gì về VBA nên chỉ sử dụng được các hàm của Excel,các bác có thể giúp lập công thức ở sheet SCTVL để rút trích dữ liệu từ hai sheet : hang nhap và hang xuat vào SCTVL theo tên từng mặt hàng, tôi xin cảm ơn nhiều.File này tôi chỉ làm ví dụ một vài mặt hàng cho đỡ dung lượng.

 

[anhtuan1066]

Người ta nhập chung vào 1 sheet, lúc cần thì trích ra... còn bạn thì lại chơi ngược lại cho khó... Tôi ko hiểu tại sao nhập chung 1 sheet thì lại "nặng" hơn là chia ra 2 sheet như bạn... Bạn dựa vào đâu mà nói nó "nặng" hơn?...
Về nguyên tắc và kỹ thuật thì hoàn toàn làm dc theo yêu cầu của bạn, nhưng do tôi ko phải chuyên ngành nên nhìn vào sheet SCTVL có sẳn của bạn tôi chẳng hiểu gì cả... có thể giải thích thêm 1 tí về sheet này dc ko? Nếu bắt tay vào làm thì nội dung của sheet SCTVL hiện đang có phãi xử lý thế nào đây? Đễ nguyên và thêm vào dử liệu từ nhập và xuất? Hay xóa đi làm lại mới?
Mến
ANH TUẤN

 

[thong_xanh2003]

SCTVL là kết quả tôi muốn có sau khi lọc số liệu từ 2 sheet hang nhap và hang xuat, hiện giờ tôi đang nhặt và nhập trực tiếp vào ô bảng tính , có 600 mặt hàng mỗi tháng tôi phải lập 600 trang sổ theo tên từng mặt hàng (Như sheet SCTVL)Bác nghiên cứu giúp với.

 

[thong_xanh2003]

Vì nhiều mặt hàng quá nên tôi mới làm thế này để trành nhầm lẫn. Chỉ cần nhầm một dòng hàng nhập sang xuất hoặc ngược lại thì tìm cả ngày chẳng ra , có lúc nhầm cũng không biết nên mới phải tách ra.

 

[thong_xanh2003]

Các bác xem giúp , em làm thủ công thế này oải lắm rồi.

 

[anhtuan1066]

Tôi cảm thấy nó ko khó nhưng thật sự tôi chẳng hiểu gì cả (vì tôi ko làm trong ngành kinh doanh và kế toán)... Nhìn hoài cũng ko thấy sự liên hệ giửa các sheet, nhất là sheet cuối.. hổng biết mấy số 111, 632 là cái gì? Ở đâu mà ra... Hic... (chắc mai mốt phải đăng ký đi học 1 khóa kế toán hay quản trị kinh doanh gì đó mới mong biết dc)
Nói thế thôi chứ bạn yên tâm đi, vì hôm nay là cuối tuần, mọi người đi chơi cả, sang thứ 2 chắc sẽ có người giúp bạn...
ANH TUẤN

 

[hacan]

Không sài VBA cũng được, nhưng tui không có thời gian nên chỉnh sửa một chút thôi. Xem nhé
Sao không gởi File được vậy ???

 

[ThuNghi]

Bạn xem thử file kèm nhé, do chưa biết có ok không nên tôi chưa hòan chỉnh về name, (sợ edit). Nếu OK tôi sẽ hệ thống lại các name.
Cốt yếu nhất của bài này là đưa ra sổ CT mà có hệ thống theo ngày (No sort). Giải pháp là dùng index, match và countif.
Nhớ đùng xóa các cột phụ và sheet tmp.
Đúng ra dùng VBA thì dễ hơn, file này chỉ dùng công thức.

 

[khanhnth02]

theo như Hacan thi nếu như sheet hàng nhập chỉ có một mã hàng là S, nếu như có đến 2 (V1) hoặc 3 mã hàng cùng xuất hoặc nhập xen kẽ nhau thì làm thế nào

 

[khanhnth02]

hàm tính & liệt kê

nhờ anh tính giúp các kết quả ở cột E (?). tôi còn có nhiều mặt hàng và dữ liệu rất dài.
Cảm ơn

 

[ThuNghi]

Bạm xem qua file mà vận dụng. Tôi chỉ làm 2 PP.

 

[khanhnth02]

vlookup

cảm ơn Thunghi! đúng là cái tôi đang cần nhưng
xin hãy giúp thêm: khi ta gỏ ở cột B18 là mặt hàng nào(A hoặc B) thì sẽ cho ra kết quả ở những dấu hỏi (?)

 

[Mr Okebab]

Bạn xem thử file kèm nhé, do chưa biết có ok không nên tôi chưa hòan chỉnh về name, (sợ edit). Nếu OK tôi sẽ hệ thống lại các name.
Cốt yếu nhất của bài này là đưa ra sổ CT mà có hệ thống theo ngày (No sort). Giải pháp là dùng index, match và countif.
Nhớ đùng xóa các cột phụ và sheet tmp.
Đúng ra dùng VBA thì dễ hơn, file này chỉ dùng công thức.

Đúng là dùng công thức không thì thấy vất vả thật.
Sumproduct là một công thức mạnh về tổng hợp lại không thấy bác dùng đến.
1 File cho 1 tháng à bác hay cả năm mà sao không có chỗ chọn ngày tháng ???


Thân!

 

[ThuNghi]

Bạn xem qua file sau, cái này rất nhiều trên 4R.
To Hiếu:
Do dữ liệu nguồn của bạn ấy nên mình mới làm thử, nghiên cứu hàm rank kết hợp count. Việc tổ chức DL như thế không chuẩn lắm.

 

[hacan]

Theo tui thấy thì có thể được chứ! Nhờ bạn sửa lại công thức tại cột "Thành tiền hàng nhập" (=SUMIF(Ma_nhap;Ma_gia;Tien_nhap) (Tui copy từ File gốc nên không để ý).
Sửa bất kỳ nghiệp vụ nhập, xuất cho các mặt hàng V1, V2 rồi chạy sổ chi tiết xem sao Nếu dữ liệu trong tháng dài thì ta tạo lại bảng dữ liệu thôi vậy, vì đó là bảng ví dụ thôi mà, nếu OK thì phải hệ thống lại bảng dữ liệu nguồn.
Ở đây, Yêu cầu đưa ra là trích dữ liệu từ 2 Sheet hàng nhập và hàng xuất vào SCTVL cho từng mặt hàng như vậy là ổn rồi.Còn số dư thì phải đúng chớ.
Thanks.

 

[hacan]

Theo tui thấy thì có thể được chứ! Nhờ bạn sửa lại công thức tại cột "Thành tiền hàng nhập" (=SUMIF(Ma_nhap;Ma_gia;Tien_nhap) (Tui copy từ File gốc nên không để ý). Sorry nha!
Sửa bất kỳ nghiệp vụ nhập, xuất mặt hàng S thành V1 hoặc V2 rồi chạy sổ chi tiết xem sao. Nếu dữ liệu trong tháng nhiều thì ta tạo lại bảng dữ liệu thôi vậy vì đó là bảng ví dụ thôi mà, nếu OK thì phải hệ thống lại bảng dữ liệu nguồn.
Ở đây, Yêu cầu đưa ra là trích dữ liệu từ 2 Sheet hàng nhập và hàng xuất vào SCTVL cho từng mặt hàng như vậy là ổn rồi.Còn số dư thì phải đúng chớ.
Thanks.

 

[nguyeenvd]

Bạn có thể tham khảo các công thức trong bảng tính ở file mình gửi kèm theo.

 

[thong_xanh2003]

Bạn xem thử file kèm nhé, do chưa biết có ok không nên tôi chưa hòan chỉnh về name, (sợ edit). Nếu OK tôi sẽ hệ thống lại các name.
Cốt yếu nhất của bài này là đưa ra sổ CT mà có hệ thống theo ngày (No sort). Giải pháp là dùng index, match và countif.
Nhớ đùng xóa các cột phụ và sheet tmp.
Đúng ra dùng VBA thì dễ hơn, file này chỉ dùng công thức.

Cảm ơn Thu Nghi , đây đúng là kết quả tôi mong muốn. Tôi muốn hỏi thêm để hiểu rõ cách làm của bạn, ở ô L2 của Sheet SCTVL tôi thấy có công thức:=MAX(Code_xuat) và cho kết quả là 16.Bạn có thể giải thích giúp tác dụng của ô L2 này trong Sheet SCTVL là ntn.Ở ô K6 của Sheet SCTVL bạn đã đặt validation nhưng lại dùng hàm OFFSET, tôi chuyển thử sang mặt hàng khác thì không được mà phải gõ trực tiếp vào,bạn xem lại giúp nhé.

 

[ThuNghi]

ô K6 của Sheet SCTVL bạn đã đặt validation nhưng lại dùng hàm OFFSET, tôi chuyển thử sang mặt hàng khác thì không được mà phải gõ trực tiếp vào

Sorry, do tôi bỏ name, tính hệ thống luôn lại name mà sợ khó hiểu, bạn sửa lại trong validation là list=OFFSET(Ma_hang,0,0,3,1), Ma_hang này bạn đã có tạo.

ở ô L2 của Sheet SCTVL tôi thấy có công thức:=MAX(Code_xuat) và cho kết quả là 16

Cell L1 chớ không phải L2, đây là cốt lõi của bài này, nhằm tạo ra L2->Li
Ý nghĩa là số record thỏa điều kiện cần trích xuất trong số CTVL, ie mahh, ngày ...(nếu có). Ta thấy số record thỏa dk là mahh="S" là : Nhập: 9 + Xuất: 7 =16. Ta chỉ cần tríh xuất đủ số record trên.
Còn tại sh temp, cell L4: =IF($A4<>"",RANK($E4,$E$4:$E$100,1)+COUNTIF($E$4:$E4,$E4)-1,"")
ie sắp xếp 1 record theo dk thỏa rên theo ngày tháng nhập xuất.
Bạn nắm bắt khá nhanh. Vậy bạn thử hệ thống lại các name, các name bạn đặt khá đặc thù và nhiều quá. Nếu mà thêm dòng thì khó, nên tạo như sau:
=offset($A$1,0,0,counta($A:$A),i) i có thể tùy biến.
PS: Sao không thấy nhấn thanks nhỉ

 

[khanhnth02]

hàm tính(thunghi)

hãy giúp tôi thêm một điều kiện nữa.
Nếu ở cột A18 và B18 cho thêm điều kiện từ ngày 2 đến ngày 4 thì công thức sẽ như thế nào, lọc ra sao??? (vì dữ liệu của tôi rất nhiều....)
cảm ơn nhiều

 

[ThuNghi]

Giúp bao nhiêu bài nhớ Thank bấy nhiêu. Tòan là "đúng ý mình" mà No Thanks, buồn quá.

 

[thong_xanh2003]

=offset($A$1,0,0,counta($A:$A),i) i có thể tùy biến.
Đã giúp thì giúp cho trót _ rồi nhận thanks một thể, Thu Nghi giải thích hộ là tôi nên đặt công thức này tại ô nào của sheet nào không ? Riêng phần dùng công thức tôi học ở các bài viết của bạn rất nhiều , xin cảm ơn! . Nếu có thể được bạn thiết kế lại file giúp theo ý bạn, mỗi tháng tôi thường phải nhập khoảng 1.000->1.500 dòng cho mỗi bảng nhập hoặc xuất hàng và danh mục khoảng gần 600 mặt hàng. Tôi vừa học vừa làm nên nhiều khi biết là rườm rà nhưng không biết cắt bớt ở phần nào cho gọn mà không làm ảnh hưởng tới bảng tính.

 

[ThuNghi]

To ThongXanh
Đã làm lại file theo yêu cầu của bạn, chưa test, bạn gắn dữ liệu vào thử, còn hơi "sạn", sẽ hòan thiện thêm sau. Có thêm và bớt một số name.
File này chỉ dùng cho 1 tháng. Hiểu rồi chuyển qua VBA làm cho 1 năm.
Cơ bản là nguyên lý.
Chúc thành công!

 

[thong_xanh2003]

To ThongXanh
Đã làm lại file theo yêu cầu của bạn, chưa test, bạn gắn dữ liệu vào thử, còn hơi "sạn", sẽ hòan thiện thêm sau. Có thêm và bớt một số name.
File này chỉ dùng cho 1 tháng. Hiểu rồi chuyển qua VBA làm cho 1 năm.
Cơ bản là nguyên lý.
Chúc thành công!

Bác xem có thể chuyển hộ tôi dòng tổng cộng xuống dưới cùng , chứ đưa lên đầu thế này thì không đúng theo mẫu sổ quy định của nhà nước.

 

[ThuNghi]

Theo tôi được biết, dòng tổng nằm ở đầu không sai theo quy định. Nhưng nếu muốn đưa xuống dưới, if làm = ct thì nên manual thôi. Chớ làm thêm hàm if dòng là dòng max thì sum, nó thế nào và rườm rà.
Nhớ là khi bỏ dòng tổng cộng phía trên nhớ xem lại định nghĩa name, còn không chỉ cần xóa mà không delete dòng thì OK.

 

[thong_xanh2003]

Theo tôi được biết, dòng tổng nằm ở đầu không sai theo quy định. Nhưng nếu muốn đưa xuống dưới, if làm = ct thì nên manual thôi. Chớ làm thêm hàm if dòng là dòng max thì sum, nó thế nào và rườm rà.
Nhớ là khi bỏ dòng tổng cộng phía trên nhớ xem lại định nghĩa name, còn không chỉ cần xóa mà không delete dòng thì OK.

Gửi Thu Nghi và các bác trong diễn đàn, tôi đã dùng file Thu nghi làm giúp chỉnh sửa chút xíu và dùng tương đối tốt, nhưng còn một vấn đề nữa nếu trong EXCEL có thể làm được các bác chỉ hộ. Đó là ở sheet SCTVL khi in có mặt hàng cả tháng chỉ có vài dòng, nhưng có mặt hàng lại vài chục dòng, vì vậy khi in ra không đảm bảo mỹ thuật và lôgic cho sổ. Nếu ít dòng thì thừa một khoảng dòng trắng ở phía cuối trang, nhưng nhiều dòng thì lại thành 2 trang(mà trang 2 không có các ô tiêu đề). Tất nhiên nếu làm ẩn dòng trống đi cũng được nhưng mất thời gian lắm.Tôi muốn có công thức nào sử dụng để khi tổng số dòng hiển thị số liệu bằng Max (Code_xuat) thì các dòng còn lại sẽ tự động ẩn đi, các bác nghiên cứu giúp tôi nhé.

 

[thong_xanh2003]

Các bác xem giúp có cách nào giải quyết được không?

 

[ThuNghi]

Làm công thức phế quá, tôi thử làm = VBA theo yêu cầu của bạn. In ngang và số dòng tối đa là 36. Bạn có thể chỉnh lạo theo máy in của mình. Hơi "rừng".
Bạn xem thử.

 

[Thien]

Thunghi này.
Bạn chưa bẫy lỗi thì phải.
Nếu Tôi chọn mã D thì code báo lỗi. Khi báo lỗi rùi dù chọn mã đúng vẫn cứ báo lỗi.
Bạn thong_xanh2003 chưa tét đã thank rùi hả.

TC.

 

[tedaynui]

Thunghi này.
Bạn chưa bẫy lỗi thì phải.
Nếu Tôi chọn mã D thì code báo lỗi. Khi báo lỗi rùi dù chọn mã đúng vẫn cứ báo lỗi.
Bạn thong_xanh2003 chưa tét đã thank rùi hả.
TC.

Ac ac, theo đồng chí thì Test OK mới click Thanks người sẵn lòng giúp mình sao ??? còn nếu không OK thì "quăng nguyên cục lơ" à !!!

Thân!

 

[Thien]

Ac ac, theo đồng chí thì Test OK mới click Thanks người sẵn lòng giúp mình sao ??? còn nếu không OK thì "quăng nguyên cục lơ" à !!!

Thân!

Trời ơi giải đáp giúp là tốt, nhưng sự giải đáp của bạn có giúp giải quyết được vấn đề chưa, nếu chưa thì nhờ giúp tiếp chứ "quăng nguyên cục lơ" là bạn có tội với mình là biết người ta sai mà không chịu chỉ cho người ta biết và có tội với người ta khi mang ơn mà không chịu trả ơn à (giống vô tình vô nghĩa thì phải????).

Từ trước tời giờ mỗi khi ai giúp mình giải đáp vấn đề mình đều rất quý tình cảm của họ dù điều họ giải đáp chưa giải quyết triệt để. Bạn thử xem lại các bài mình viết đi xem có phải vậy không?.

Bạn này vui tính thế chứ.

Thân chào.

 

[tedaynui]

Bạn thong_xanh2003 chưa tét đã thank rùi hả.
TC.

Hi hi, thì như bạn nói đấy, Thong_xanh đã Thanks sự nhiệt tình của Bác ThuNghi đó thôi !

Thân!

 

[ThuNghi]

Sorry, đúng là chưa test vì tự tạo ra dl nên o ktra.
Tạo nên 1 code kiemtra nữa là OK

Sub kiemtra()
With Application
.Calculation = xlCalculationAutomatic
End With
If WorksheetFunction.VLookup(Range("MaHH"), Range("Ma_BQ"), 8, 0) <> "" Then
Call LayDL
Else
MsgBox "Ma hang nay chua co ps"
Exit Sub
End If
End Sub

Và sửa lại

Private Sub CommandButton1_Click()
Call kiemtra
End Sub

Cám ơn Thiện nhé. Small thing.

 

[thong_xanh2003]

Chào các bác, nói thật là về VBA tôi là Gà mờ nên tải file Thu Nghi làm hộ bằng VBA về mà chưa dám nhập thử số liệu. Tôi chỉ quen làm bằng công thức , có gì trục trặc còn biết đường mò ra.Nhưng file của Thu Nghi rất có ích cho tôi để học VBA(tôi đang học mót mà), vậy sao không cảm ơn Thu Nghi được. Cảm ơn tất cả các bác, chúc thành công!

 

[ThuNghi]

Chào các bác, nói thật là về VBA tôi là Gà mờ nên tải file Thu Nghi làm hộ bằng VBA về mà chưa dám nhập thử số liệu. Tôi chỉ quen làm bằng công thức , có gì trục trặc còn biết đường mò ra.Nhưng file của Thu Nghi rất có ích cho tôi để học VBA(tôi đang học mót mà), vậy sao không cảm ơn Thu Nghi được. Cảm ơn tất cả các bác, chúc thành công!

Bạn cứ mạnh dạn test, if unknown chỗ nào, nếu bạn thực sự thích thi tự dưng bạn tìm hiểu thôi, cam kết sẽ HD bạn cụ thể.
Còn như đã nói, làm công việc trên mà = ct thì hơi khó. Cùng lắm chỉ áp dụng autofilter.
Tôi cũng làm KT nhưng hiện tại chưa hình dung mình nên bắt đầu từ đâu.

 

[ThuNghi]

To Thong_Xanh
Tôi chỉnh lại file của bạn = công thức, (bán tự động, có VBA 1 ít), in khỏang 2 trang có footer.
Bạn nhớ xem và chỉnh sửa I1 trong sh hangnhap và sh hangxuat. Tôi đã chỉnh lại. Lúc đầu chưa Test phần này = ct (có nhiều > 1 mặt hàng)
Trong file tôi có 2 button thực hiện 2 code hide và unhid khi in thôi, dẽ lắm.

 

[thong_xanh2003]

To Thong_Xanh
Tôi chỉnh lại file của bạn = công thức, (bán tự động, có VBA 1 ít), in khỏang 2 trang có footer.
Bạn nhớ xem và chỉnh sửa I1 trong sh hangnhap và sh hangxuat. Tôi đã chỉnh lại. Lúc đầu chưa Test phần này = ct (có nhiều > 1 mặt hàng)
Trong file tôi có 2 button thực hiện 2 code hide và unhid khi in thôi, dẽ lắm.

To Thu Nghi
Bác chỉ giúp tôi làm thế nào để tạo được 2 hộp hide và unhide trong sheet, cái này trong Excel gọi là gì,không biết mới hỏi bác đừng cười nhé.

 

[ThuNghi]

Để tôi nôm na cho bạn nhé:
+ Những hộp đó là những CommandButton sẽ thực thi 1 công việc (code) nào đó khi ta nhấn vào đó.
+ Để tạo nó:
- Vào menu View, Toolbars, đánh dấu vào Control Toolbox lúc này hiện ra 1 số icon.
- Bạn rê mouse đến ô chữ nhật, có hiện CommandButton, click vào, trở về bảng tính click cái nữa sẽ hiện 1 hình chữ nhật có chữ CommandButton và có 8 hình tròn xung quanh.
- Right Click trên hình CN, chọn properties, chọn thuộc tính caption, đánh vào MyButton (ví dụ, tên gì cũng được). Đóng lại (Nhấp chuột bên ngòai), ta thấy điều kinh ngạc. Ta đã làm được 1 phần.
- Bây giờ ta muốn khi ta nhấn vào MyButton, thì nó sẽ làm cái gì...
- Bạn có để ý trên menu Control ToolBox có 1 icon hình như cái ê ke, khi rê muose vào sẽ có hiện Exit Design Mode, bạn nhấp vào, khi icon này sáng lên tức là ta đang ở chế độ thiết kế. Nhấp đúp vào MyButton, ta sẽ thấy màn hình VBA hiện ra như sau:

Private Sub CommandButton1_Click()

End Sub

Trên chữ end sub sẽ là những lệnh hay code ta yêu cầu thực hiện. Cụ thể trong bài này là Hide và UnHide
Đính kèm 2 code trên

Private Sub CommandButton1_Click()
Call Hide
End Sub
Private Sub CommandButton2_Click()
Call UnHide
End Sub

Bạn muốn xem Code Hide thế nào pls nhấn Alt F11, vào module sẽ thấy những code trên.
Hy vọng bạn hiểu.
Nhờ ttphong và các bạn tạo phần này = video hay hd 1 cách mô phạm. hơn.

 

[thong_xanh2003]

Để tôi nôm na cho bạn nhé:
+ Những hộp đó là những CommandButton sẽ thực thi 1 công việc (code) nào đó khi ta nhấn vào đó.
+ Để tạo nó:
- Vào menu View, Toolbars, đánh dấu vào Control Toolbox lúc này hiện ra 1 số icon.
- Bạn rê mouse đến ô chữ nhật, có hiện CommandButton, click vào, trở về bảng tính click cái nữa sẽ hiện 1 hình chữ nhật có chữ CommandButton và có 8 hình tròn xung quanh.
- Right Click trên hình CN, chọn properties, chọn thuộc tính caption, đánh vào MyButton (ví dụ, tên gì cũng được). Đóng lại (Nhấp chuột bên ngòai), ta thấy điều kinh ngạc. Ta đã làm được 1 phần.
- Bây giờ ta muốn khi ta nhấn vào MyButton, thì nó sẽ làm cái gì...
- Bạn có để ý trên menu Control ToolBox có 1 icon hình như cái ê ke, khi rê muose vào sẽ có hiện Exit Design Mode, bạn nhấp vào, khi icon này sáng lên tức là ta đang ở chế độ thiết kế. Nhấp đúp vào MyButton, ta sẽ thấy màn hình VBA hiện ra như sau:

Trên chữ end sub sẽ là những lệnh hay code ta yêu cầu thực hiện. Cụ thể trong bài này là Hide và UnHide
Đính kèm 2 code trên

Bạn muốn xem Code Hide thế nào pls nhấn Alt F11, vào module sẽ thấy những code trên.
Hy vọng bạn hiểu.
Nhờ ttphong và các bạn tạo phần này = video hay hd 1 cách mô phạm. hơn.

Cảm ơn Thu Nghi , bạn hướng dẫn như vậy là rất cụ thể và dễ hiểu , tôi sẽ tập làm , khi nào thành công sẽ trả bài cho "thầy" nhé!!!