Ngưng tụ các giá trị tuần tự thành một hàng (Microsoft Excel)
Rusty có một danh sách các Mã ZIP trong một cột của trang tính. Anh ấy muốn một cách để “nén” các mã sao cho các phạm vi giá trị tuần tự nằm trên một hàng. Vì vậy, chẳng hạn, thay vì 35013, 35014 và 35015 chiếm ba hàng, chúng sẽ xuất hiện trên một hàng duy nhất là 35013-35015.
Có một số cách để thực hiện điều này — có hoặc không có macro. Ở phía “không có macro” của hàng rào, có một số cách tiếp cận khác nhau và tất cả chúng đều liên quan đến việc sử dụng các cột bổ sung để giữ các kết quả trung gian.
Ví dụ: giả sử rằng bạn có dữ liệu của mình trong cột A, bắt đầu từ ô A2 và ô A1 đó trống (thậm chí không có văn bản tiêu đề trong đó). Trong trường hợp này, bạn có thể nhập công thức sau vào ô B2:
=IF(NOT(A2-A1=1),A2,IF(A3-A2=1,B1,A2))
Sau đó, trong ô C2, nhập công thức dài sau:
=IF(NOT(A3-A2=1),IF(A2-A1=1,TEXT(B1,"00000") &" - "&TEXT(B2,"00000"),TEXT(A2,"00000")),"")
Bây giờ bạn có thể sao chép các công thức trong ô B2: C2 xuống các cột tương ứng của chúng. Những gì bạn kết thúc với cột C là chuỗi mã ZIP cô đọng. Bạn có thể sao chép các giá trị này — sử dụng Dán Đặc biệt để bỏ qua các ô trống — sang bất kỳ nơi nào khác bạn muốn.
Nếu bạn muốn sử dụng cách tiếp cận macro, thì không cần cột trung gian. Một macro có thể được viết về cơ bản thu gọn danh sách Mã ZIP tại chỗ. Macro sau đây lặp lại qua bất kỳ dải ô nào bạn đã chọn và tạo ra danh sách cô đọng:
Sub CombineValues() Dim rng As Range Dim rCell As Range Dim sNewArray() As String Dim x As Long Dim y As Long Dim sStart As String Dim sEnd As String Set rng = Selection sStart = rng.Cells(1) sEnd = sStart y = 1 For x = 1 To rng.Count - 1 If rng.Cells(x + 1) - _ rng.Cells(x) > 1 Then 'End ReDim Preserve sNewArray(1 To y) If sStart = sEnd Then sNewArray(y) = sStart Else sNewArray(y) = sStart & "-" & sEnd End If sStart = rng.Cells(x + 1) y = y + 1 End If sEnd = rng.Cells(x + 1) ReDim Preserve sNewArray(1 To y) If sStart = sEnd Then sNewArray(y) = sStart Else sNewArray(y) = sStart & "-" & sEnd End If Next rng.ClearContents For x = 1 To y rng.Cells(x) = "'" & sNewArray(x) Next Set rng = Nothing Set rCell = Nothing End Sub
_Lưu ý: _
Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.
ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.
Mẹo này (11977) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.
Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: