Как разбить числа на столбцы с отрицательными и положительными значениями, без пустых ячеек?

Question

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

Однако я не хочу пустых ячеек в столбцах B и C.

Итак, я НЕ хочу

   A     B     C
----------------
 All   >=0    <0
 100   100
-300        -300
-800        -800
 900   900
 700   700
-200        -200
 900   900

Но вместо этого я хочу:

   A     B     C
----------------
 All   >=0    <0
 100   100  -300
-300   900  -800
-800   700  -200
 900   900
 700
-200
 900

Answer 1

Не на коробке окна. Бьюсь об заклад, есть формула, которая сделает это. VBA наверняка: в VB Editor нажмите «Вставить модуль», затем введите:

Sub posInBnegInC(ByRef aList as range)
    Dim cll as Range
    For each Cll in AList
        If cll.value2 > 0 then
            Sheet2.Range("B65000").end(xlup).offset(1,0).value2 = cll.value2
        Else
            Sheet2.Range("C65000").end(xlup).offset(1,0).value2 = cll.value2
        End If
    Next cll
End Sub

Вы можете добавить логику для обработки значения ровно 0, если таковое существует. Так как это код скопирует нули в столбец C. Если в столбце A есть нечисловые данные, он не будет работать, но я предполагаю, что все A числовые. Тогда вы можете работать различными способами, в том числе:

Sub DoIt()
    call posInBnegInC(Sheet1.Range("A2:A65000"))
End Sub

Answer 2

Вы в основном спрашиваете, как получить n-ое отфильтрованное значение из некоторого столбца в n-ую строку другого столбца.

Microsoft объясняет получение n-го отфильтрованного значения в Excel: Нахождение n-го значения, соответствующего условию. При этом используются "формулы массива" для выполнения расчетов над элементами в массиве (списке). Пример Microsoft может быть расширен до получения значения, соответствующего номеру строки. Если ваш массив в A2:A8 , и вы хотите показать только значения >= 0 , то в B2 можно использовать следующее B2:B8 (не проверено):

=iferror(index(A2:A8, small( if(A2:A8 >= 0, row()-1, ""), row()-1) ), "")

Будьте внимательны, чтобы ввести это как формулу массива: сначала выделите ячейки с B2 по B8 , затем вставьте вышеуказанное в строку формул и сохраните его, нажав Ctrl+Shift+Enter (или Command+Return in Office на Mac, или Command+Shift+Return в OpenOffice на Mac).

Для C2:C8 повторите с условием >= 0 замененным на < 0 .

Чтобы расшифровать этот шаг за шагом, я добавил следующие формулы в таблицу OpenOffice Calc, результаты которой приведены ниже. Обратите внимание, что в OpenOffice нужна точка с запятой для разделения параметров функции, в то время как в Excel может понадобиться запятая, поэтому точки с запятой в следующих формулах.

В D2:D8:

=if(A2:A8 >= 0; row()-1; "")

Это показывает номер строки минус 1, если значение в той же строке в A2:A8 является положительным, или пустая ячейка в противном случае. Вычитание 1 преобразует строки 2-8 в положение от 1 до 7, чтобы игнорировать первую строку заголовка.

Итак, теперь мы знаем, что 1-е, 4-е, 5-е и 7-е значения из A2:A8 являются положительными.

Далее в E2:E8:

=small( if(A2:A8 >= 0; row()-1; ""); row()-1 )

Он принимает более ранние результаты в качестве входных данных для small(..., k) который принимает k-е наименьшее число, показанное ранее в D2:D8 . (Этот список сортируется от 1 до 7, но это даже не требуется для small функции.) Здесь, положит k текущей строке числа минус 1 игнорировать строку заголовка снова. Таким образом, для 3-го ряда мы получаем позицию 2-го положительного значения в A2:A8 . Но для E6 и вниз значение не найдено, показывая ошибку.

В F2:F8:

=index(A2:A8; small( if(A2:A8 >= 0; row()-1; ""); row()-1 ))

Это принимает позиции 1, 4, 5 и 7 (и ошибки) из предыдущего шага в качестве входных данных для index(..., k) , который находит k-е значение из A2:A8 , или ошибки, если k недействительным.

Теперь F2:F8 прежнему показывает ошибку для последних строк. В Excel 2007 и более поздних версиях можно использовать iferror чтобы ничего не показывать в случае ошибки, как в самой первой формуле выше. OpenOffice не поддерживает это, но нуждается в if(iserror(...); ""; ...) , заменяя оба ... формулой из F2:F8 . Не хорошо.

В качестве альтернативы используйте index(A2:A9; ...) чтобы включить ячейку A9 при получении фактического значения из первого столбца, а затем каким-то образом заставить OpenOffice использовать small(...; 8) если больше значений не найдено. Как в G2:G8:

=if(A2:A8 >= 0; row()-1; 8)

Это все еще ищет положительные числа в 7 значениях из A2:A8 , но теперь возвращает 8 если не положительное.

В H2:H8:

=small( if(A2:A8 >= 0; row()-1; 8); row()-1 )

И наконец в I2:I8:

=index(A2:A9; small( if(A2:A8 >= 0; row()-1; 8); row()-1 ))

Здесь значение 8 отображается на значение в A9 . В примере снимка экрана A9 содержит несколько точек, которые затем отображаются в H6:H8 как больше не найдено положительных чисел.

Как пишет Microsoft: если вы действительно хотите освоить формулы в Excel, вам нужно знать, как использовать формулы массива. Наслаждаться.

Answer 3

Арджан уже упоминал об этом дважды, поэтому я пытаюсь опубликовать это как реальный ответ, а не как комментарий. Он сказал, что сообщение заблокировано, но я настолько свеж для Stack Exchange, что не понимаю, почему оно заблокировано, но я все еще могу опубликовать это как ответ. В любом случае, у Microsoft есть точное решение для этого. Я бы только слегка изменил его, обернув его в IFERROR, чтобы он возвращал пустое значение вместо #NUM! ошибки.

---

http://support.microsoft.com/kb/101167 Как говорится в статье базы знаний, обязательно вводите их в виде формул массива

Для позитива:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8>0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}

Для негативов:

{=IFERROR(INDEX($A$2:$A$8,SMALL(IF($A$2:$A$8<0,ROW($A$2:$A$8),""),ROW()-1)-ROW($A$2:$A$8)+1),"")}