У Роберта в столбце A есть ряд чисел от 1 до 100. Он хотел бы извлечь только значения от 65 до 100 включительно и поместить их в столбец B. Он задается вопросом, есть ли способ сделать это легко.

Короткий ответ заключается в том, что есть очень простой способ сделать это, если вы не против сортировки списка чисел. Выполните следующие действия:

  1. Выберите ячейку в столбце A. (На самом деле не имеет значения, какую ячейку вы выберете, если это одна из ячеек, содержащих число.)

  2. Откройте вкладку «Данные» на ленте.

  3. Щелкните инструмент Сортировать от наименьшего к наибольшему в группе Сортировка и фильтр.

Excel сортирует все числа в столбце.

  1. Выберите числа, которые вы хотите поместить в столбец B.

  2. Нажмите Ctrl + X, чтобы вырезать ячейки в буфер обмена.

  3. Выберите ячейку B1 (или первую ячейку в столбце B, в которой должны отображаться значения).

  4. Нажмите Ctrl + V, чтобы вставить ячейки в столбец.

Это оно; теперь у вас есть нужные ячейки в столбце B. Если вы просто хотите скопировать ячейки, то на шаге 5 вы могли бы вместо этого нажать Ctrl + C.

Если вам нужно сохранить значения в столбце A в их исходном порядке (за вычетом значений, которые вы хотите переместить), вы можете сделать это, используя столбец B в качестве столбца «фиксатора места». Справа от первого значения в столбце A поместите значение 1. Затем под этим значением в столбце B поместите 2, затем 3 и так далее, пока каждое значение в столбце A не будет иметь соответствующее значение в столбце B, которое указывает расположение номеров. Затем выполните следующие действия:

  1. Выберите ячейку в столбце A. (На самом деле не имеет значения, какую ячейку вы выберете, если это одна из ячеек, содержащих число.)

  2. Откройте вкладку «Данные» на ленте.

  3. Щелкните инструмент Сортировать от наименьшего к наибольшему в группе Сортировка и фильтр.

Excel сортирует все числа в столбце.

  1. Выберите числа в столбце A, которые вы хотите переместить, а также числа справа от них в столбце B.

  2. Нажмите Ctrl + X, чтобы вырезать ячейки в буфер обмена.

  3. Выберите ячейку D1. (Важно выбрать ячейку D1, потому что столбец C нужно оставить пустым.)

  4. Нажмите Ctrl + V, чтобы вставить ячейки в столбцы D и E.

  5. Выберите ячейку в столбце B.

  6. Откройте вкладку «Данные» на ленте.

  7. Щелкните инструмент Сортировать от наименьшего к наибольшему в группе Сортировка и фильтр.

Excel сортирует все числа в столбце на основе значений в столбце B.

  1. Выберите ячейку в столбце E.

  2. Вкладка «Данные» на ленте по-прежнему должна отображаться.

  3. Щелкните инструмент Сортировать от наименьшего к наибольшему в группе Сортировка и фильтр.

Excel сортирует все числа в столбце на основе значений в столбце E.

  1. Удалить столбцы B, C и E.

На этом этапе значения в столбцах A и B отражают их первоначальный порядок, когда все они находились в столбце A.

Другой способ переместить ячейки — использовать возможности фильтрации Excel. Выполните следующие действия:

  1. Выберите ячейку в столбце A. (На самом деле не имеет значения, какую ячейку вы выберете, если это одна из ячеек, содержащих число.)

  2. Откройте вкладку «Данные» на ленте.

  3. Щелкните инструмент «Фильтр». Excel добавляет стрелку раскрывающегося списка фильтра справа от заголовка столбца A.

  4. Щелкните стрелку вниз и выберите Числовые фильтры | Между. Excel отображает диалоговое окно «Пользовательский автофильтр». (См. Рис. 1.)

  5. В поле «Больше или равно» введите 65.

  6. В поле «Меньше или равно» введите 100.

  7. Щелкните ОК. Excel ограничивает отображаемые строки только теми строками, которые соответствуют критериям, указанным на шагах с 4 по 6.

  8. Выберите отображаемые ячейки.

  9. Нажмите Ctrl + C, чтобы скопировать ячейки в буфер обмена.

  10. Выберите ячейку B1 (или первую ячейку в столбце B, в которой должны отображаться значения).

  11. Нажмите Ctrl + V, чтобы вставить ячейки в столбец.

  12. Выберите одну из ячеек в столбце A.

  13. Снова щелкните инструмент «Фильтр». (Вкладка «Данные» на ленте по-прежнему должна отображаться.) Excel удалит ранее примененный фильтр.

Вы также можете использовать формулы в столбце B для извлечения значений, находящихся в желаемом диапазоне. Легкий способ сделать это — поместить эту формулу в ячейку B1:

=IF(AND(A1>=65, A1<=100),A1,"")

Скопируйте формулу вниз, насколько это необходимо в столбце B, и вы получите любые значения в диапазоне от 65 до 100 включительно, которые будут «скопированы»

в столбец B. Если значение выходит за пределы этого диапазона, тогда ячейка в столбце B остается пустой.

Предполагая, что вам не нужны пустые ячейки в столбце B, вы можете использовать формулу массива для получения значений. Если ваши значения находятся в диапазоне A1: A500, поместите в ячейку B1 следующее:

=IFERROR(INDEX(A$1:A$500,SMALL(IF(A$1:A$500>=65,ROW($1:$500)),ROW())),"")

Введите его, используя Ctrl + Shift + Enter, а затем скопируйте формулу вниз, насколько хотите.

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

Sub ExtractValues1()

Dim x As Integer

x = 1     For Each cell In Selection         If cell.Value >= 65 And cell.Value <= 100 Then             Cells(x, 2) = cell.Value             x = x + 1         End If     Next cell End Sub

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

Для большей гибкости вы можете рассчитывать на запрос у пользователя нижнего и верхнего значений, как показано в этом макросе:

Sub ExtractValues2()

Dim iLowVal As Integer     Dim iHighVal As Integer

iLowVal = InputBox("Lowest value wanted?")

iHighVal = InputBox("Highest value wanted?")



For Each cell In Range("A:A")

If cell.Value <= iHighVal And cell.Value >= iLowVal Then             ActiveCell.Value = cell.Value             ActiveCell.Offset(1, 0).Activate         End If     Next End Sub

Перед запуском макроса выберите ячейку в верхней части диапазона, в которую вы хотите поместить извлеченные значения. Ни на что в столбце A не влияет; в новое место копируются только значения между нижним и верхним диапазоном.

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

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

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