У Димитриса есть ряд целочисленных значений в столбце A. Он хотел бы иметь способ показать нечетные значения в столбце B и четные значения в столбце C. Он хочет, чтобы значения находились в смежных ячейках, без пробелов, а также чтобы они были в порядке возрастания. Димитрис задается вопросом, нужен ли ему макрос для такой обработки данных.

Короткий ответ заключается в том, что вы действительно можете сделать это, не прибегая к макросам. Однако, если вам нужно много выполнять этот тип обработки, вы можете найти более выгодным фактическое использование макроса.

Во-первых, давайте посмотрим на немакро подходы. При желании вы можете написать простые формулы в столбцах B и C, которые просто проверяют, является ли значение в столбце A четным или нечетным, и, если оно подходит для столбца, копируют значение заново. Например, в столбце B может быть следующее:

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

В столбце C все, что вам нужно сделать, это заменить ISODD на ISEVEN. Когда вы копируете эти формулы вниз, столбец B содержит только нечетные значения, а столбец C содержит только четные значения. Проблема, конечно, в том, что результат не соответствует тому, что ищет Димитрис: ему нужны значения в смежных ячейках (без пробелов), и он хочет, чтобы они были в порядке возрастания.

Разумеется, вы можете предпринять дополнительные шаги для получения желаемых результатов — например, вы можете скопировать результаты в столбцы B и C и вставить обратно значения (чтобы формулы были удалены), а затем отсортировать результаты. Это добавляет к вашей работе дополнительные шаги.

Есть способ получить более «чистый» результат, просто используя формулу массива. Предположим, ваши значения находятся в ячейках A1: A100. Выделив ячейки B1: B100, введите в панель формул следующее:

=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()),"")

Помните, что это все одна формула. Поскольку он разработан как формула массива, завершите его, введя Ctrl + Shift + Enter. В результате у вас есть нечетные значения в столбце B в смежных ячейках в порядке возрастания. Чтобы получить четные значения в столбце C, сначала скопируйте B1: B100 в C1: C100. Затем выберите диапазон C1: C100. Нажмите F2, чтобы войти в режим редактирования, и измените «= 1» в середине формулы на «= 0».

Снова завершите формулу, нажав Ctrl + Shift + Enter.

Обратите внимание, что эта формула не будет работать должным образом, если в диапазоне A1: A100 есть пробелы или есть какие-либо текстовые значения в диапазоне. Причина, по которой пробелы не работают, заключается в том, что они обрабатываются как 0 по формуле, а 0 считается четным, таким образом, отображаясь в столбце C. Альтернативная формула для определения нечетных значений (столбец B) заключается в использовании следующей формулы массива в ячейке B1:

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

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

Чтобы справиться с потенциальной проблемой «пустой ячейки», вы можете использовать следующую формулу массива в ячейке C1:

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

Скопируйте B1: C1 вниз на столько ячеек, сколько необходимо, чтобы получить результаты.

Ранее я упоминал, что вам может быть более выгодно использовать макрос для обработки ваших значений. Причина проста — вы можете легко избавиться от повторяющихся значений (при необходимости) и игнорировать пробелы и текстовые значения. Существует множество способов разработки такого макроса; Я выбрал подход, который требует от вас выбора ячеек, которые вы хотите обработать, очищает два столбца справа от этих ячеек, а затем помещает в эти столбцы шансы и равны.

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

Макрос выполняет свою работу, вставляя значения из выбранных ячеек в любой из двух массивов (iEvens и iOdds). Это сделано для того, чтобы макрос мог легко проверять наличие дубликатов в значениях. Значение будет добавлено в соответствующий массив только в том случае, если ячейка не пуста, а содержит число и это число не является дубликатом. Затем значения вставляются обратно в два столбца, и эти значения сортируются.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (6767) применим к Microsoft Excel 2007, 2010, 2013 и 2016.