Dùng công thức hay VBA?

LikeIt · 2/8/07

Các bác giúp em cái này thì dùng công thức nào tốt hay nên dùng VBA. Nếu đc mong các bác cho em VD trong file đính kèm nhé. Em phải kiểm tra lượng NVL này hàng ngày, cứ đến 100 cái trở xuống thì phải biết để đặt với khách hàng. Mong nhận đc chỉ giáo của các bác thêm để em học hỏi.

anhtuan1066 · 2/8/07

Lở dại miệng nói "làm dc" nên phải ráng... chứ làm xong thấy .. đuối luôn...
Ko khó mà oải...
Trình tự như sau: Sheet 3 tạo 1 array gồm những đứa <=100... Một array nữa dùng đễ xếp hạng "nó" so với những "người bạn" cùng ROW nó... Sheet 2 dùng INDEX để lấy ra thằng nào hang 1, hạng 2, hang 3.... van... van... Rồi kết hợp từng thằng 1, phân cách nhau bằng dấu phầy... Hic...
Củ chuối ghê...
ANH TUẤN

phamduylong · 2/8/07

Nguyễn Hương Thơm đã viết:
Các bác giúp em cái này thì dùng công thức nào tốt hay nên dùng VBA. Nếu đc mong các bác cho em VD trong file đính kèm nhé. Em phải kiểm tra lượng NVL này hàng ngày, cứ đến 100 cái trở xuống thì phải biết để đặt với khách hàng. Mong nhận đc chỉ giáo của các bác thêm để em học hỏi.

Bài này dùng công thức để liệt kê hết các ô dưới 100 chắc là không thể được ! Bạn có 52 code hàng, để tìm 1 code hàng <100 công thức đã dài, nếu cả 52 code hàng đều như vậy thì công thức để liệt kê 52 code sẽ như thế nào? Mà công thức Excel cũng giới hạn số ký tự, số hàm IF thì làm sao viết !
Tôi chỉ tìm được 1 code đầu tiên thỏa mãn bằng cách các ô dòng cuối cùng đánh số từ 1 đến 52 tương ứng với cột. Dùng hàm MIN để tìm số nhỏ nhất, dùng HLOOKUP tìm cột, dùng INDEX tìm code hàng.
Viết bằng VBA sẽ đơn giản hơn.

voda · 2/8/07

anhtuan1066 xem thử ở cột BA nhập công thức này vào có được không?
=IF(MIN(A6:AZ6)>100,"",SMALL(A6:AZ6,1)&","&SMALL(A6:AZ6,2)&","&SMALL(A6:AZ6,3))...tiếp tục

Mr Okebab · 2/8/07

Bạn thử nhé, cái này mình dùng VBA.
Bạn thử click mouse vào cột BA xem thế nào.

Thân!

ThuNghi · 2/8/07

Theo tôi nên tổ chức lại dữ liệu theo dạng khác, ví dụ:
ngay---mahang---ton
Thì 200 mã x 31 ngày <65000 dòng
Có 1 sh khác để tổng hợp hàng <=100 theo ngày, vẫn dùng công thức
Còn tổ chức vậy chỉ làm VBA thì đơn giản còn không thì làm như sau, hàm if vẫn cho 200 hàm nối nhau
BA02=IF(A2<=100,A2&";","")&IF(B2<=100,B2&";","")&...&IF(AZ2<=100,AZ2,"")
BB03=IF(A2<=100,A$1&";","")&IF(B2<=100,B$1&";","")&...&IF(AZ2<=100,B$1,"")
Cứ làm theo tôi bạn sẽ mau giỏi hơn và sẽ tới thiên đàng.
Còn 1 cách nữa là thêm từng ấy cột phụ, phê hơn nữa.

LikeIt · 2/8/07

ThuNghi đã viết:
Theo tôi nên tổ chức lại dữ liệu theo dạng khác, ví dụ:
ngay---mahang---ton
Thì 200 mã x 31 ngày <65000 dòng
Có 1 sh khác để tổng hợp hàng <=100 theo ngày, vẫn dùng công thức
Còn tổ chức vậy chỉ làm VBA thì đơn giản còn không thì làm như sau, hàm if vẫn cho 200 hàm nối nhau
BA02=IF(A2<=100,A2&";","")&IF(B2<=100,B2&";","")&...&IF(AZ2<=100,AZ2,"")
BB03=IF(A2<=100,A$1&";","")&IF(B2<=100,B$1&";","")&...&IF(AZ2<=100,B$1,"")
Cứ làm theo tôi bạn sẽ mau giỏi hơn và sẽ tới thiên đàng.
Còn 1 cách nữa là thêm từng ấy cột phụ, phê hơn nữa.

Cám ơn Bác thunghi đã cho những lời khuyên quí báu ,thực tế cái database của em nó do một người khác thiết kế liên quan đến nhiều sheet khác nên em không thể thay đổi sắp xếp nó theo cách của em được, do em mới bị phân theo dõi cái này nên nhìn lúc ban đầu ớn quá, hai ngày trước em vừa bị một vố là ko update đc cái nào cần đặt thêm, nên hôm nay em bị thiếu một vài code, nếu áp dụng cách anh Hiếu thì hàng ngày sẽ biết cái nào cần đặt thêm một cách chính xác.

anhtuan1066 · 3/8/07

Thơm ơi, có công thức này cũng tương đối gọn nè... (Theo gợi ý của Voda):
Từ cell BC1 đến DB1, đánh số thứ tự 1--->52
Cell BC2, gõ công thức:
=IF(SMALL($A2:$AZ2,BC$1)>100,"",INDEX($A$1:$AZ$1,1,MATCH(SMALL($A2:$AZ2,BC$1),$A2:$AZ2,0)))
Rồi kéo fill sang phải và xuống dưới, em sẽ dc 1 bảng lọc như ý
Từ bảng này, thích làm gì thì cứ việc
Mến
ANH TUẤN

anhtuan1066 · 3/8/07

Sai ở hàm MATCH... nó chỉ cho ra 1 kết quả đầu tiên tìm thấy trong mãng, nếu có 1 trị dò trùng nằm ở phía sau thì sao?... VD trong 1 hàng có 2 số lương trùng nhau <100 thì sao? Em thừ cho 2 cell = 30 xem... hi... hi.. Tóm lại nếu muốn chính xác phải thêm hàm RANK + COUNTIF đễ phân biệt dc 2 dử liệu trùng
Cái này nên cẩn thận nha! Xài tạm trước trong khi tìm cách mới...
Thật ra cách thì đã có rồi đây, gọn hơn lần trước 1 chút... nhưng anh chưa hài lòng lắm... Đễ hoàn tất xong anh post lên...
ANH TUẤN

phamduylong · 3/8/07

Một cách khác dùng VBA cho bảng tính có dữ liệu có số cột, số dòng thay đổi. Cho phép:
- thêm cột code hàng mới, miễn 2 cột cuối phải là "SL cần đặt", "Code hàng cần đặt".
- thêm dòng mới, miễn là số dòng có dữ liệu liên tục (không có dòng trống)

Mã:

[FONT=Verdana]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT]
[FONT=Verdana]rc = Cells(1, 1).End(xlDown).Row[/FONT]
[FONT=Verdana]cc = Cells(1, 1).End(xlToRight).Column[/FONT]
[FONT=Verdana]If Left(Cells(1, cc - 1), 3) <> "SL " Or Left(Cells(1, cc), 5) <> "Code " Then Exit Sub[/FONT]
[FONT=Verdana]Range(Cells(2, cc - 1), Cells(rc, cc)).ClearContents[/FONT]
[FONT=Verdana]For r = 2 To rc[/FONT]
[FONT=Verdana]For c = 1 To cc - 2[/FONT]
[FONT=Verdana]  If Cells(r, c) <> "" And Cells(r, c) <= 100 Then[/FONT]
[FONT=Verdana]    Cells(r, cc - 1) = Cells(r, cc - 1) & Cells(r, c) & ", "[/FONT]
[FONT=Verdana]    Cells(r, cc) = Cells(r, cc) & Cells(1, c) & ", "[/FONT]
[FONT=Verdana]  End If[/FONT]
[FONT=Verdana]Next[/FONT]
[FONT=Verdana]If Cells(r, cc - 1) <> "" Then Cells(r, cc - 1) = Left(Cells(r, cc - 1), Len(Cells(r, cc - 1)) - 2)[/FONT]
[FONT=Verdana]If Cells(r, cc) <> "" Then Cells(r, cc) = Left(Cells(r, cc), Len(Cells(r, cc)) - 2)[/FONT]
[FONT=Verdana]Next[/FONT]
[FONT=Verdana]End Sub[/FONT]

LikeIt · 4/8/07

Cám ơn bạn Long, mình đã thử insert số cột thì OK, nhưng thêm khoảng 5 dòng theo như cách của bạn chỉ dẫn, nhưng kết quả chỉ cho thêm 1 dòng nữa thôi (dòng 13), còn các dòng khác thì KQ vẫn là giữ nguyên kết quả của dòng 12 copy xuống. VD bây giờ bạn có thể cho mình khoảng 5000 dòng và mỗi dòng cho cho 1 con số vào thôi (số nhỏ hơn 100, để tránh file quá nặng) để xem nó có ra KQ ở cột cuối cùng không? Cám ơn bạn.

anhtuan1066 · 4/8/07

Cái này hình như ko đúng... Vì theo Hương Thơm thì phải sắp code nào có số lượng nhỏ hơn nằm trước... VD, dòng 12 thì phải sắp theo trật tự sau: 82001007, 82001001, 82001003, 82001004, 82001006...
Thật ra thì việc lọc ra theo d/k <=100 ko khó, sắp xếp lại trật tự từ nhỏ đến lớn mới tê... Còn nữa, phải nghĩ rằng đây chỉ là dử liệu có tính tham khảo thôi, thực tế dử liệu phải nhiều hơn... Vậy phải nghĩ luôn đến vấn đề mở rông dử liệu: Thêm dòng, thêm cột cũng ko ảnh hưởng đến kết quả!
Mến
ANH TUẤN

kietbui · 4/8/07

bài toán của bạn hình như giống bài toán quản lý tồn kho NPL, nếu vậy bạn đưa về bài toán tồn kho, mỗi ngày cập nhật xuất,nhập và cho báo cáo tồn kho, cột tồn kho bạn sort từ thấp đến cao và lọc lấy những code tồn dưới 100, tất cả việc này nếu bạn viết macro auto_open thì chỉ cần khi mở file này ra nó sẽ cho bạn tất cả những code còn tồn dưới 100 từ thấp đến cao, không biết có đúng ý bạn không.

ThuNghi · 4/8/07

Bác Long đã ghi chú là không có cell rỗng, nên khi bạn thêm dòng mà dòng đó có nhiều ô rỗng =>
rc = Cells(1, 1).End(xlDown).Row
cc = Cells(1, 1).End(xlToRight).Column Tính không ra.
Nên sửa lại 1 chút như sau:
rc = ActiveCell.CurrentRegion.Rows.Count
cc = ActiveCell.CurrentRegion.Columns.Count
Dùng sự kiện change trong file này không nên, file sẽ nặng. Tôi sửa lãi dùng command button.
Theo tôi phần sort các sl nhỏ -> lớn, không cần thiết nên không làm
Bản thân tôi chưa hiểu mục đích thực của file này
Nếu muốn lấy số mã hàng cần order, ie tồn kho <=100, thì file này chỉ có giá trị trong ngày. Chớ cuối tháng lấy dl không có nghĩa
Nên chăng những file nhờ help nên có mục đích sd, ng hd cũng hưng phấn hơn. Ít ra là phục vụ công việc chính họ.

Sub DangKyHang()
Dim rc As Integer, cc As Integer
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.DisplayAlerts = False
End With
S01.Cells(1, 1).Select
rc = ActiveCell.CurrentRegion.Rows.Count
cc = ActiveCell.CurrentRegion.Columns.Count
'rc = Cells(1, 1).End(xlDown).Row
'cc = Cells(1, 1).End(xlToRight).Column
Range(Cells(2, cc - 1), Cells(rc, cc)).ClearContents
For r = 2 To rc
For c = 1 To cc - 2
If Cells(r, c) <= 100 And Cells(r, c) <> "" Then
Cells(r, cc - 1) = Cells(r, cc - 1) & Cells(r, c) & ", "
Cells(r, cc) = Cells(r, cc) & Cells(1, c) & ", "
End If
Next
If Cells(r, cc - 1) <> "" Then Cells(r, cc - 1) = Left(Cells(r, cc - 1), Len(Cells(r, cc - 1)) - 2)
If Cells(r, cc) <> "" Then Cells(r, cc) = Left(Cells(r, cc), Len(Cells(r, cc)) - 2)
Next
Cells(2, cc - 1).Select
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.DisplayAlerts = True
End With
End Sub

LikeIt · 4/8/07

ThuNghi đã viết:
Bác Long đã ghi chú là không có cell rỗng, nên khi bạn thêm dòng mà dòng đó có nhiều ô rỗng =>
rc = Cells(1, 1).End(xlDown).Row
cc = Cells(1, 1).End(xlToRight).Column Tính không ra.
Nên sửa lại 1 chút như sau:
rc = ActiveCell.CurrentRegion.Rows.Count
cc = ActiveCell.CurrentRegion.Columns.Count
Dùng sự kiện change trong file này không nên, file sẽ nặng. Tôi sửa lãi dùng command button.
Theo tôi phần sort các sl nhỏ -> lớn, không cần thiết nên không làm
Bản thân tôi chưa hiểu mục đích thực của file này
Nếu muốn lấy số mã hàng cần order, ie tồn kho <=100, thì file này chỉ có giá trị trong ngày. Chớ cuối tháng lấy dl không có nghĩa
Nên chăng những file nhờ help nên có mục đích sd, ng hd cũng hưng phấn hơn. Ít ra là phục vụ công việc chính họ.

Gửi Thunghi,

Cám ơn bạn đã chỉ dẫn, như mình đã nêu ở các phần trc, thì mục đích của em là chỉ sử dụng trong ngày thôi, vì hàng ngày em phải kiểm tra và nếu thấy SL ít của một code nào đó là phải đặt hàng với nhà cung cấp ngay. Thực tế đây là một dạng để em kiểm tra ,vì nó nhiều dữ liệu quá nên em ko biết làm thế nào,mong các bác giúp em.Tks

LikeIt · 4/8/07

kietbui đã viết:
bài toán của bạn hình như giống bài toán quản lý tồn kho NPL, nếu vậy bạn đưa về bài toán tồn kho, mỗi ngày cập nhật xuất,nhập và cho báo cáo tồn kho, cột tồn kho bạn sort từ thấp đến cao và lọc lấy những code tồn dưới 100, tất cả việc này nếu bạn viết macro auto_open thì chỉ cần khi mở file này ra nó sẽ cho bạn tất cả những code còn tồn dưới 100 từ thấp đến cao, không biết có đúng ý bạn không.

Đúng bạn à, nó là một dạng tính tồn kho của một loại NVL, nhưng do nó quá nhiều dữ liệu nên mình phải làm riêng 1 file để ktra, nếu có thể bạn kietbui làm cho mình một VD nhé. tks in adv.

phamduylong · 4/8/07

Nguyễn Hương Thơm đã viết:
Cám ơn bạn Long, mình đã thử insert số cột thì OK, nhưng thêm khoảng 5 dòng theo như cách của bạn chỉ dẫn, nhưng kết quả chỉ cho thêm 1 dòng nữa thôi (dòng 13), còn các dòng khác thì KQ vẫn là giữ nguyên kết quả của dòng 12 copy xuống.

Nguyên nhân là tại cột A, các dòng mới thêm vào chưa có dữ liệu. Đây là do cách tôi viết tìm dòng cuối cùng có dữ liệu tại cột A để xác định số lần lặp cho For Next. Để xác định chính xác dòng cuối, tôi đề nghị thêm cột đầu tiên (A) là STT.
Để sắp xếp theo số lượng từ nhỏ đến lớn, tôi mượn tạm 3 cột cuối cùng (IT, IU, IV) ghi dòng, cột, số lượng để sắp xếp. Do đó, dữ liệu của bạn thêm tối đa đến cột IR.
Để tính lại, bạn nhấn tổ hợp Ctrl+Shif+Z thay cho sự kiện Change.

Mã:

Sub DangKyHang()
'xac dinh dong cuoi, cot cuoi
rc = Cells(1, 1).End(xlDown).Row
cc = Cells(1, 1).End(xlToRight).Column
If Left(Cells(1, cc - 1), 3) <> "SL " Or Left(Cells(1, cc), 5) <> "Code " Then Exit Sub
Range(Cells(2, cc - 1), Cells(rc, cc)).ClearContents
Columns("IT:IV").ClearContents
'Loc du lieu thoa man sluong <=100
rmin = 1
For r = 2 To rc
  For c = 2 To cc - 2
    If Cells(r, c) <> "" And Cells(r, c) <= 100 Then
      Cells(rmin, 254) = r
      Cells(rmin, 255) = Cells(r, c)
      Cells(rmin, 256) = c
      rmin = rmin + 1
    End If
  Next
Next
'Sap xep sluong tu nho den lon
Columns("IT:IV").Sort Key1:=Range("IT1"), Order1:=xlAscending, Key2:=Range("IU1"), _
vHeader:=xlNo, OrderCustom:=1
'Ghi du lieu vao 2 cot "SL can dat" va "Code hang can dat"
rc = Cells(1, 256).End(xlDown).Row
rsl = 2
dong = Cells(1, 254)
r = 1
Do While Cells(r, 254) <> ""
  If Cells(r, 254) = dong Then
    Cells(dong, cc - 1) = Cells(dong, cc - 1) & Cells(r, 255) & ", "
    Cells(dong, cc) = Cells(dong, cc) & Cells(1, Cells(r, 256)) & ", "
    r = r + 1
  Else
    If Cells(dong, cc - 1) <> "" Then Cells(dong, cc - 1) = Left(Cells(dong, cc - 1), Len(Cells(dong, cc - 1)) - 2)
    If Cells(dong, cc) <> "" Then Cells(dong, cc) = Left(Cells(dong, cc), Len(Cells(dong, cc)) - 2)
    dong = Cells(r, 254)
  End If
Loop
If Cells(dong, cc - 1) <> "" Then Cells(dong, cc - 1) = Left(Cells(dong, cc - 1), Len(Cells(dong, cc - 1)) - 2)
If Cells(dong, cc) <> "" Then Cells(dong, cc) = Left(Cells(dong, cc), Len(Cells(dong, cc)) - 2)
Columns("IT:IV").ClearContents
Cells(1, cc - 1).Select
End Sub

anhtuan1066 · 4/8/07

File này công thức cũng tương đối đơn giãn đây, nhưng tuyệt đối chính xác... Việc thêm dòng thì ko có vấn đề, còn nếu muốn thêm cột thì chỉ việc sữa 3 Name cho phù hợp (hiện tại địa chỉ tham chiếu của name dc tính từ cột A đến cột AZ).... Tiếp theo, kéo fill công thức là xong
Mến
ANH TUẤN

Mr Okebab · 5/8/07

Nguyễn Hương Thơm đã viết:
Gửi Thunghi,

Cám ơn bạn đã chỉ dẫn, như mình đã nêu ở các phần trc, thì mục đích của em là chỉ sử dụng trong ngày thôi, vì hàng ngày em phải kiểm tra và nếu thấy SL ít của một code nào đó là phải đặt hàng với nhà cung cấp ngay. Thực tế đây là một dạng để em kiểm tra ,vì nó nhiều dữ liệu quá nên em ko biết làm thế nào,mong các bác giúp em.Tks

Bạn xem nhé., File này mình thử làm 5000 dòng, chạy hơi lâu. Bất kể là có dòng trống hay không thì cũng không sao cả.
Bạn chú ý : Bạn có thể thêm hàng hoặc thêm cột nhưng phải đảm bảo :
- Cột code là cột cuối cùng
-Cột SL là cột kế ngay sau cột Code.

Muốn tính hàng nào thì bạn chọn ô cuối của hàng đó (cột SL)
Nếu muốn tính tất cả thì bạn chọn ô đầu tiên của cột SL (hàng 1)

Tạm thời là như vậy nhé. Có gì sửa sau

Thân!

Dùng công thức hay VBA?

Thành viên tiêu biểu

File đính kèm

Thành viên gạo cội

File đính kèm

-

File đính kèm

Thành viên tiêu biểu

Ngon Ngất Ngây

File đính kèm

Hãy cho rồi sẽ nhận!

Thành viên tiêu biểu

Thành viên gạo cội

Thành viên gạo cội

-

File đính kèm

Thành viên tiêu biểu

Thành viên gạo cội

Thành viên mới

Hãy cho rồi sẽ nhận!

Thành viên tiêu biểu

Thành viên tiêu biểu

-

File đính kèm

Thành viên gạo cội

File đính kèm

Ngon Ngất Ngây

File đính kèm

Facebook

Group