Dimitris có một loạt các giá trị nguyên trong cột A. Anh ấy muốn một cách để hiển thị các giá trị lẻ trong cột B và các giá trị chẵn trong cột C. Anh ấy muốn các giá trị nằm trong các ô liền nhau, không có khoảng trắng và cũng theo thứ tự tăng dần. Dimitris tự hỏi liệu anh ta có cần macro để xử lý dữ liệu của mình theo cách này không.

Câu trả lời ngắn gọn là bạn có thể thực sự làm điều này mà không cần dùng đến macro. Tuy nhiên, nếu bạn cần thực hiện kiểu xử lý này nhiều, bạn có thể thấy thực sự sử dụng macro sẽ có lợi hơn.

Đầu tiên, hãy xem xét các phương pháp tiếp cận không vĩ mô. Nếu muốn, bạn có thể viết các công thức đơn giản trong cột B và C để kiểm tra xem giá trị trong cột A là lẻ hay chẵn và nếu nó phù hợp với cột, hãy sao chép giá trị đó. Ví dụ, bạn có thể có thông tin sau trong cột B:

=IF(ISODD(A1),A1,"")

Trong cột C, tất cả những gì bạn cần làm là thay thế ISODD bằng ISEVEN. Khi bạn sao chép các công thức này xuống, cột B chỉ chứa các giá trị lẻ và cột C chỉ chứa các giá trị chẵn. Tất nhiên, vấn đề là kết quả không khớp với những gì Dimitris đang tìm kiếm: Anh ta muốn các giá trị trong các ô liền nhau (không có ô trống) và anh ta muốn chúng theo thứ tự tăng dần.

Được cho là, bạn có thể thực hiện các bước bổ sung để có được kết quả mong muốn — ví dụ: bạn có thể sao chép kết quả trong cột B và C và dán lại các giá trị (để các công thức bị xóa) rồi sắp xếp kết quả. Điều này thêm các bước bổ sung cho công việc của bạn.

Có một cách để có được kết quả “gọn gàng” hơn nhiều bằng cách sử dụng công thức mảng. Giả sử giá trị của bạn nằm trong các ô A1: A100. Với các ô B1: B100 được chọn, hãy nhập thông tin sau vào Thanh Công thức:

=IFERROR(SMALL(IFERROR(INDEX($A$1:$A$100,SMALL( IF(MOD($A$1:$A$100,2)=1,ROW($A$1:$A$100)),ROW( $A1:$A$100))),""),ROW()),"")

Hãy nhớ rằng đây là một công thức duy nhất. Vì nó được thiết kế để trở thành một công thức mảng, hãy kết thúc nó bằng cách nhấn Ctrl + Shift + Enter. Kết quả là bạn có các giá trị lẻ trong cột B, trong các ô liền kề, theo thứ tự tăng dần. Để nhận các giá trị chẵn vào cột C, trước tiên hãy sao chép B1: B100 sang C1: C100. Sau đó, chọn phạm vi C1: C100. Nhấn F2 để vào chế độ chỉnh sửa và thay đổi “= 1” ở giữa công thức thành “= 0”.

Một lần nữa, kết thúc công thức bằng cách nhấn Ctrl + Shift + Enter.

Bạn nên lưu ý rằng công thức này sẽ không hoạt động đúng nếu có bất kỳ khoảng trống nào trong phạm vi A1: A100 hoặc nếu có bất kỳ giá trị văn bản nào trong phạm vi. Lý do mà các ô trống sẽ không hoạt động là vì chúng được coi là 0 theo công thức và 0 được coi là chẵn, do đó hiển thị trong cột C. Một công thức thay thế để xác định các giá trị lẻ (cột B) là sử dụng công thức mảng sau trong ô B1:

=IFERROR(SMALL(IF(MOD($A$1:$A$100,2)>0,$A$1:

$A$100,"x"),ROW()),"")

Để giải quyết vấn đề tiềm ẩn “ô trống”, bạn có thể sử dụng công thức mảng sau trong ô C1:

=IFERROR(SMALL(IF((MOD($A$1:$A$100,2)=0)*NOT( ISBLANK($A$1:$A$100)),$A$1:$A$100,"x"),ROW()),"")

Sao chép B1: C1 xuống nhiều ô nếu cần để nhận kết quả của bạn.

Trước đó, tôi đã đề cập rằng bạn có thể thấy lợi ích hơn khi sử dụng macro để xử lý các giá trị của mình. Lý do rất đơn giản — bạn có thể dễ dàng loại bỏ các giá trị trùng lặp (nếu cần) và bạn có thể bỏ qua các giá trị văn bản and trống. Có nhiều cách để phát triển một vĩ mô như vậy; Tôi đã chọn một cách tiếp cận yêu cầu bạn chọn ô bạn muốn xử lý, xóa hai cột ở bên phải của các ô đó, sau đó đặt tỷ lệ cược và tỷ lệ cược vào các cột đó.

Sub OddsEvens()

Dim rSource As Range     Dim c As Range     Dim sTemp As String     Dim iVal As Integer     Dim bGo As Boolean     Dim sCols As String     Dim vMsg As Variant     Dim lOddCol As Long     Dim iOddPtr As Integer     Dim lEvenCol As Long     Dim iEvenPtr As Integer     Dim iOdds(999) As Integer     Dim iEvens(999) As Integer     Dim J As Integer

Set rSource = Selection     If rSource.Columns.Count = 1 Then         lOddCol = rSource.Column + 1         lEvenCol = rSource.Column + 2         sCols = Chr(lOddCol + 64) & ":"

sCols = sCols & Chr(lEvenCol + 64)



sTemp = "The contents of columns " & sCols         sTemp = sTemp & " will be deleted. Ok to proceed?"

vMsg = MsgBox(sTemp, vbYesNo, "Odds and Evens")

If vMsg = vbYes Then             Application.ScreenUpdating = False             Range(sCols).Clear             iOddPtr = 0             iEvenPtr = 0             For Each c In rSource                 bGo = True                 ' Is the cell empty?

If IsEmpty(c.Value) Then bGo = False                 ' Does the cell contain non-numeric value?

If Not IsNumeric(c.Value) Then bGo = False                 If bGo Then                     iVal = c.Value                     If Int(iVal / 2) * 2 = iVal Then                         ' Even number                         ' Check to see if duplicate                         For J = 1 To iEvenPtr                             If iEvens(J) = iVal Then bGo = False                         Next J                         If bGo Then                             iEvenPtr = iEvenPtr + 1                             iEvens(iEvenPtr) = iVal                         End If                     Else                         'Odd number                         ' Check to see if duplicate                         For J = 1 To iOddPtr                             If iOdds(J) = iVal Then bGo = False                         Next J                         If bGo Then                             iOddPtr = iOddPtr + 1                             iOdds(iOddPtr) = iVal                         End If                     End If                 End If             Next c

' Stuff values into proper columns             For J = 1 To iOddPtr                 Cells(rSource.Row + J - 1, lOddCol) = iOdds(J)

Next J             For J = 1 To iEvenPtr                 Cells(rSource.Row + J - 1, lEvenCol) = iEvens(J)

Next J

' Sort values in Odd column             sTemp = Chr(lOddCol + 64) & rSource.Row & ":"

sTemp = sTemp & Chr(lOddCol + 64) & rSource.Row _               + iOddPtr - 1             Range(sTemp).Select             Selection.Sort key1:=Range(Chr(lOddCol + 64) _               & rSource.Row), Order1:=xlAscending

' Sort values in Even column             sTemp = Chr(lEvenCol + 64) & rSource.Row & ":"

sTemp = sTemp & Chr(lEvenCol + 64) & rSource.Row _               + iEvenPtr - 1             Range(sTemp).Select             Selection.Sort key1:=Range(Chr(lEvenCol + 64) _               & rSource.Row), Order1:=xlAscending

rSource.Select             Application.ScreenUpdating = True         End If     End If End Sub

Macro thực hiện công việc của nó bằng cách nhồi các giá trị từ các ô đã chọn vào một trong hai mảng (iEvens và iOdds). Điều này được thực hiện để macro có thể dễ dàng kiểm tra các bản sao trong các giá trị. Chỉ khi ô không trống, nó chứa một số và số đó không phải là số trùng lặp thì giá trị mới được thêm vào mảng thích hợp. Các giá trị sau đó được nhồi trở lại hai cột và các giá trị đó được sắp xếp.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (6767) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.