Giúp mình công thức tự động cách dòng

hoanganh1987vn · 17/1/16

Chào các bác, công ty em hiện đang dùng 1 phần mềm Excel để bán hàng
Em mô tả ngắn gọn như sau:
- Có 1 sheet để điền hàng xuất bán cho từng đại lý trong ngày

- Có 1 sheet tự động tổng hợp danh sách đại lý kèm với model và số lượng tương ứng (phục vụ in phiếu xuất kho). Thứ tự các cột: Ngày - Tên đại lý - Model - Số lượng - Imei

=> Cột Imei là cột điền Imei (số máy) xuất ra của model đó. Ví dụ model X có 5 máy, đi kèm 5 số imei khác nhau

Hiện nay sau khi xuất được file này ra, em phải insert bằng tay số dòng tương ứng với số lượng hàng để dưới kho họ điền imei vào

=> Vì vậy em muốn hỏi có cách nào cho excel tự động chèn số dòng, hoặc tự động cách dòng tương ứng với giá trị trên cột đó không ạ?

Em gửi kèm hình và file, mong các bác giúp đỡ với ạ

killitmore · 17/1/16

Bác kiểm tra xem file có vấn đề j ko mà sao em cứ mở là Excel lag ko bật lên được @@ **~**

hoanganh1987vn · 17/1/16

Bác thông cảm. File của em để công thức hơi nhiều dòng nên mỗi lần mở nó đơ đơ mất khoảng 2 phút ạ !$@!!

SA_DQ · 18/1/16

Cái này không thể xài công thức mà ra kết quả được; Chỉ có thể là VBA mà thôi;
Macro cho bạn đây & báo trước: Chạy khá lâu đó nha.
(Mình xóa còn để lại khoảng 500 dòng mà chạy hơn 2 fút)

PHP:

Option Explicit
Sub InsertRowsxlUp()
 Dim Rws As Long, jJ As Long, NumFrom As Long, Tmr As Double
  
 Tmr = Timer()
 Rws = [E8].End(xlDown).Offset(-1).Row
 Application.ScreenUpdating = False
 For jJ = Rws To 2 Step -1
    With Cells(jJ, "E")
        On Error Resume Next
        NumFrom = .Value
        .Offset(1, -4).Resize(NumFrom).EntireRow.Insert
    End With
 Next jJ
 Application.ScreenUpdating = True
 Msgbox Timer()-Tmr
End Sub

Phuocam · 18/1/16

hoanganh1987vn đã viết:
em muốn hỏi có cách nào cho excel tự động chèn số dòng, hoặc tự động cách dòng tương ứng với giá trị trên cột đó không ạ?

Bạn xem thử cách này:

Mã:

Public Sub AAA()
Dim i As Long, k As Long, j As Long, DongCuoi As Long, DL, KQ
DongCuoi = Range("A65536").End(xlUp).Row
DL = Range("A8:E" & DongCuoi)
If DongCuoi < 8 Then Exit Sub
ReDim KQ(1 To 50000, 1 To 5)
    For i = 1 To UBound(DL)
    If DL(i, 1) <> "" Then
    k = k + 1
        For j = 1 To 5
            KQ(k, j) = DL(i, j)
        Next
    If IsNumeric(DL(i, 5)) Then k = k + DL(i, 5)
    End If
    Next
Range("A8").Resize(k, 5) = KQ
End Sub

hoanganh1987vn · 18/1/16

phuocam đã viết:

Bạn xem thử cách này:

Mã:

Public Sub AAA()
Dim i As Long, k As Long, j As Long, DongCuoi As Long, DL, KQ
DongCuoi = Range("A65536").End(xlUp).Row
DL = Range("A8:E" & DongCuoi)
If DongCuoi < 8 Then Exit Sub
ReDim KQ(1 To 50000, 1 To 5)
    For i = 1 To UBound(DL)
    If DL(i, 1) <> "" Then
    k = k + 1
        For j = 1 To 5
            KQ(k, j) = DL(i, j)
        Next
    If IsNumeric(DL(i, 5)) Then k = k + DL(i, 5)
    End If
    Next
Range("A8").Resize(k, 5) = KQ
End Sub

Cảm ơn bác nhiều, mình đã làm được theo Macro của bác
Tuy nhiên, phát sinh vấn đề về đánh số thứ tự đầu dòng
Mình muốn cột STT chỉ tự động đánh số khi cột B #0, tuy nhiên khi các cột khác #0, nó vẫn tự động đếm

Mình đã thử 1 số công thức đánh số như sau

IF(AND(B9<>"",B9<>B8),MAX($A$8:A8)+1,"")
IF( B171<>"",COUNTA($B$8:B171),"")

nhưng nó vẫn tự động đánh số ngay cả khi cột C#0

Mong bác giúp đỡ thêm phần này để em hoàn thiện file ạ

bebo021999 · 18/1/16

Tại B9:
=IF(TRIM(B9)="","",SUMPRODUCT(--(LEN($B$8:B8)>3))+1)
Vì cột B có những ô có 1 khoảng trắng (độ dài =1) nên mình đếm những ô có độ dài >3 cho chắc ăn.

Phuocam · 18/1/16

hoanganh1987vn đã viết:
Cảm ơn bác nhiều, mình đã làm được theo Macro của bác
Tuy nhiên, phát sinh vấn đề về đánh số thứ tự đầu dòng
Mình muốn cột STT chỉ tự động đánh số khi cột B #0, tuy nhiên khi các cột khác #0, nó vẫn tự động đếm
Mong bác giúp đỡ thêm phần này để em hoàn thiện file ạ

Bạn muốn đánh Số TT tự động thì thay lại code này:

Mã:

Public Sub AAA()
Dim i As Long, k As Long, j As Long, DongCuoi As Long
Dim SoTT As Long, DL, KQ(1 To 50000, 1 To 5)
DongCuoi = Range("D65536").End(xlUp).Row
If DongCuoi < 8 Then Exit Sub
DL = Range("A8:E" & DongCuoi)
    For i = 1 To UBound(DL)
        If DL(i, 4) <> "" Then
             k = k + 1
                If Len(DL(i, 2)) > 2 Then
                    SoTT = SoTT + 1
                    KQ(k, 1) = SoTT
                End If
                For j = 2 To 5
                    KQ(k, j) = DL(i, j)
                Next
             If IsNumeric(DL(i, 5)) Then k = k + DL(i, 5)
        End If
    Next
Range("A8:E" & DongCuoi).ClearContents
Range("A8").Resize(k, 5) = KQ
End Sub

hoanganh1987vn · 18/1/16

Chào bác phuocam
Không hiểu sao em chỉnh thêm 1 số thứ cho file chuẩn thì macro bị lỗi bác ạ
Em gửi hình bên dưới, bác xem sửa giúp em với

Tức là như ở post trên thì em đã làm đc macro chạy đúng như ý
Nhưng sau khi căn chỉnh vài chi tiết, thì macro không cách dòng đều nữa
Mà nó chỉ nhảy dòng của hàng đầu tiên của mỗi đại lý, và xóa luôn các dòng bên dưới

Ba Tê · 18/1/16

hoanganh1987vn đã viết:
Chào bác phuocam
Không hiểu sao em chỉnh thêm 1 số thứ cho file chuẩn thì macro bị lỗi bác ạ
Em gửi hình bên dưới, bác xem sửa giúp em với

Tức là như ở post trên thì em đã làm đc macro chạy đúng như ý
Nhưng sau khi căn chỉnh vài chi tiết, thì macro không cách dòng đều nữa
Mà nó chỉ nhảy dòng của hàng đầu tiên của mỗi đại lý, và xóa luôn các dòng bên dưới

Chạy thử cái này coi sao.

PHP:

Public Sub GPE()
Dim sArr(), dArr(), I As Long, J As Long, K As Long, STT As Long
sArr = Range("A8", Range("E65536").End(xlUp)).Resize(, 7).Value
ReDim dArr(1 To UBound(sArr, 1) * 10, 1 To 7)
For I = 1 To UBound(sArr, 1)
    If Len(sArr(I, 4)) > 1 Then
        K = K + 1
        For J = 2 To 6
            dArr(K, J) = sArr(I, J)
        Next J
        If Len(sArr(I, 2)) > 1 Then
            STT = STT + 1: dArr(K, 1) = STT
        End If
        K = K + sArr(I, 5)
    End If
Next I
Range("A8:G1000").ClearContents
Range("A8:G8").Resize(K) = dArr
End Sub

ppc0312 · 18/1/16

hoanganh1987vn đã viết:
Chào các bác, công ty em hiện đang dùng 1 phần mềm Excel để bán hàng
Em mô tả ngắn gọn như sau:
- Có 1 sheet để điền hàng xuất bán cho từng đại lý trong ngày

- Có 1 sheet tự động tổng hợp danh sách đại lý kèm với model và số lượng tương ứng (phục vụ in phiếu xuất kho). Thứ tự các cột: Ngày - Tên đại lý - Model - Số lượng - Imei

=> Cột Imei là cột điền Imei (số máy) xuất ra của model đó. Ví dụ model X có 5 máy, đi kèm 5 số imei khác nhau

Hiện nay sau khi xuất được file này ra, em phải insert bằng tay số dòng tương ứng với số lượng hàng để dưới kho họ điền imei vào

=> Vì vậy em muốn hỏi có cách nào cho excel tự động chèn số dòng, hoặc tự động cách dòng tương ứng với giá trị trên cột đó không ạ?

Em gửi kèm hình và file, mong các bác giúp đỡ với ạ

Nếu file của bạn có cả sheet banhang gốc của VLOOKUP,
"những thông tin này là Vlookup lấy từ các sheet khác san" - Phần này có sheet nguồn

Thì khi đó có thể lập lại các công thức Vlookup đó để có thể cách dòng - hoàn toàn bằng công thức

hoanganh1987vn · 18/1/16

phuocam đã viết:

Bạn muốn đánh Số TT tự động thì thay lại code này:

Mã:

Public Sub AAA()
Dim i As Long, k As Long, j As Long, DongCuoi As Long
Dim SoTT As Long, DL, KQ(1 To 50000, 1 To 5)
DongCuoi = Range("D65536").End(xlUp).Row
If DongCuoi < 8 Then Exit Sub
DL = Range("A8:E" & DongCuoi)
    For i = 1 To UBound(DL)
        If DL(i, 4) <> "" Then
             k = k + 1
                If Len(DL(i, 2)) > 2 Then
                    SoTT = SoTT + 1
                    KQ(k, 1) = SoTT
                End If
                For j = 2 To 5
                    KQ(k, j) = DL(i, j)
                Next
             If IsNumeric(DL(i, 5)) Then k = k + DL(i, 5)
        End If
    Next
Range("A8:E" & DongCuoi).ClearContents
Range("A8").Resize(k, 5) = KQ
End Sub

Tuyệt vời quá. Chạy đúng như ý em. Cảm ơn bác nhiều lắm ạ. Bác ở đâu em hẹn mời bác 1 chầu cafe ạ
Ngoài ra, em cũng xin cảm ơn tất cả các bác trên diễn đàn đã nhiệt tình góp ý và giúp đỡ ạ

Em có thêm 1 đề xuất nữa, mong bác xem xét và không nghĩ là được voi đòi tiên ạ
Có 1 số model không cần thiết phải cách dòng vì không có số máy, liệu có thể thêm 1 hàm Vlookup vào danh bạ model có quy định model nào phải cách dòng (để điền imei) và model nào không phải cách dòng ko ạ?

Phuocam · 19/1/16

hoanganh1987vn đã viết:
Tuyệt vời quá. Chạy đúng như ý em. Cảm ơn bác nhiều lắm ạ. Bác ở đâu em hẹn mời bác 1 chầu cafe ạ
Ngoài ra, em cũng xin cảm ơn tất cả các bác trên diễn đàn đã nhiệt tình góp ý và giúp đỡ ạ

Em có thêm 1 đề xuất nữa, mong bác xem xét và không nghĩ là được voi đòi tiên ạ
Có 1 số model không cần thiết phải cách dòng vì không có số máy, liệu có thể thêm 1 hàm Vlookup vào danh bạ model có quy định model nào phải cách dòng (để điền imei) và model nào không phải cách dòng ko ạ?

Nếu phân biệt được loại nào không có số máy thì có thể làm được, như cột Emei/SN có ký tự "X", hoặc cột model có ký tự đặc biệt ở đầu chuỗi ,cuối chuỗi.

hoanganh1987vn · 19/1/16

phuocam đã viết:
Nếu phân biệt được loại nào không có số máy thì có thể làm được, như cột Emei/SN có ký tự "X", hoặc cột model có ký tự đặc biệt ở đầu chuỗi ,cuối chuỗi.

Thưa bác, em cần cách dòng cho các sản phẩm model có kí tự đầu tiên là: UA, RT, WW, WA, AR, WD, MG, VC, DVD
Mong bác giúp đỡ hoàn thiện Macro ạ

Phuocam · 19/1/16

hoanganh1987vn đã viết:
Thưa bác, em cần cách dòng cho các sản phẩm model có kí tự đầu tiên là: UA, RT, WW, WA, AR, WD, MG, VC, DVD
Mong bác giúp đỡ hoàn thiện Macro ạ

Bạn thay thử code này:

Mã:

Public Sub AAA()
Dim i As Long, k As Long, j As Long, DongCuoi As Long
Dim SoTT As Long, DL, KQ(1 To 50000, 1 To 5)
DongCuoi = Range("D65536").End(xlUp).Row
If DongCuoi < 8 Then Exit Sub
DL = Range("A8:E" & DongCuoi)
    For i = 1 To UBound(DL)
        If DL(i, 4) <> "" Then
             k = k + 1
                If Len(DL(i, 2)) > 2 Then
                    SoTT = SoTT + 1
                    KQ(k, 1) = SoTT
                End If
                For j = 2 To 5
                    KQ(k, j) = DL(i, j)
                Next
            If Left(DL(i, 4), 3) = "DVD" Or InStr(" UA, RT, WW, WA, AR, WD, MG, VC,", Left(DL(i, 4), 2) & ",") > 0 Then
                If IsNumeric(DL(i, 5)) Then k = k + DL(i, 5)
            End If
        End If
    Next
Range("A8:E" & DongCuoi).ClearContents
Range("A8").Resize(k, 5) = KQ
End Sub

hoanganh1987vn · 19/1/16

Cảm ơn bác phuocam
Em đã thay code mới và ra kết quả đúng ý rồi
Giờ lại có thêm 1 nguyện vọng nữa là các model không chứa kí tự đặc biệt thì sẽ tự động cách 1 dòng, mục đích là viết thêm khuyến mại của sản phẩm đó vào dòng bên dưới
Còn các model có kí tự đặc biệt như trên thì cách theo cột số lượng

Tiện đây bác có thể giúp em là tạo thêm 1 VBA nữa hoặc thêm chức năng cho VBA hiện tại với mục đích sử dụng như sau: Sau khi chạy VBA tự động chèn dòng, thì VBA tiếp tục Vlookup sang sheet Danh bạ KM rồi tự động điền khuyến mại tương ứng của model đó vào dòng bên dưới (vừa được VBA trước tạo ra). Số lượng KM (cột F) tương ứng với số lượng bán ra của model đó (cột E). Mã KM lấy từ sheet Danh bạ KM

Thực sự là trong quá trình làm mới nảy sinh thêm vấn đề, mong bác chiếu cố và giúp đỡ nốt ạ

Phuocam · 20/1/16

hoanganh1987vn đã viết:
Cảm ơn bác phuocam
Em đã thay code mới và ra kết quả đúng ý rồi
Giờ lại có thêm 1 nguyện vọng nữa là các model không chứa kí tự đặc biệt thì sẽ tự động cách 1 dòng, mục đích là viết thêm khuyến mại của sản phẩm đó vào dòng bên dưới
Còn các model có kí tự đặc biệt như trên thì cách theo cột số lượng

Tiện đây bác có thể giúp em là tạo thêm 1 VBA nữa hoặc thêm chức năng cho VBA hiện tại với mục đích sử dụng như sau: Sau khi chạy VBA tự động chèn dòng, thì VBA tiếp tục Vlookup sang sheet Danh bạ KM rồi tự động điền khuyến mại tương ứng của model đó vào dòng bên dưới (vừa được VBA trước tạo ra). Số lượng KM (cột F) tương ứng với số lượng bán ra của model đó (cột E). Mã KM lấy từ sheet Danh bạ KM

Thực sự là trong quá trình làm mới nảy sinh thêm vấn đề, mong bác chiếu cố và giúp đỡ nốt ạ

....................................

Giúp mình công thức tự động cách dòng

Thành viên mới

File đính kèm

Thành viên hoạt động

Thành viên mới

/(hông là gì!

Thành viên mới

File đính kèm

Thành viên mới

File đính kèm

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

Thành viên mới

Thành viên mới

File đính kèm

Cạo Rồi Khỏi Gội

File đính kèm

whom?

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

Thành viên mới

File đính kèm

Thành viên mới

Đếm ngược thời gian