Excel вставка в отфильтрованные строки

Вставка в отфильтрованные строки

Очень распространенная ситуация, вопрос, который мне задают почти на каждой тренировке. Есть таблица, где несколько строк выбираются Фильтром (Данные — Фильтр). Задача — вставить все нужные нам значения в видимые отфильтрованные строки, пропуская скрытые. Обычное копирование и вставка не будет работать, данные будут вставлены не только в видимые ячейки, но и скрыты. Посмотрим, как обойти эту проблему.

Способ 1. Вставка одинаковых значений или формул

Если вам нужно ввести одинаковые значения во все отфильтрованные строки списка, то все просто. Предположим, у нас есть такой список предложений:

и в нее необходимо ввести фиксированную скидку 1000 рублей на каждый «Ашан».

Мы фильтруем наш список с помощью Автофильтра, оставляя на экране только «Ашаны». Введите желаемое значение в первую ячейку и перетащите (скопируйте в правый нижний угол ячейки) вниз:

Smart Excel в этом случае понимает, что вы хотите вставить значения в отфильтрованные ячейки, и делает то, что вам нужно:

Этот метод подходит как для ввода значений, так и для ввода формул. Например, если скидка на «Ашан» не фиксированная, а составляет 10% от суммы транзакции, в первой отфильтрованной строке можно ввести не константу (1000), а формулу (= C2 * 10%) и скопируйте его тоже.

Способ 2. Макрос вставки любых значений

другой вопрос, нужно ли вводить в отфильтрованные ячейки не одни и те же значения или формулы, а разные и даже брать их из другого диапазона. Поэтому вам нужно использовать простой макрос. Нажмите комбинацию клавиш Alt + F11, в открывшемся окне Visual Basic вставьте новый пустой модуль через меню Insert — Module и скопируйте туда этот код:

Как вы можете легко представить, макрос запрашивает у пользователя по очереди два интервала: скопировать и вставить. Затем убедитесь, что их размеры совпадают, так как разница в размерах позже приведет к ошибке вставки. Затем макрос прокручивает все ячейки в диапазоне вставки и передает туда данные из диапазона копирования, если строка видна (т. Е. Не фильтруется).

Как вставить скопированные ячейки только в видимые/отфильтрованные ячейки

В общем, смысл статьи уже, как мне кажется, понятен из названия. Я немного расширилась.

Не секрет, что Excel позволяет выделять только видимые строки (например, если некоторые из них скрыты или применен фильтр).

если кто не знает, как это сделать: выберите диапазон — Alt +; (для английской раскладки); Alt + f (для русского). Более подробную информацию можно найти здесь.

Итак, если вы копируете только видимые ячейки таким образом, они будут скопированы должным образом. Но когда вы пытаетесь вставить скопированное в отфильтрованный диапазон (или содержащий скрытые строки), результат вставки будет не совсем таким, как вы ожидали. Данные также будут вставлены в скрытые строки.

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

Option Explicit Dim rCopyRange As Range ‘С помощью этого макроса мы копируем данные Sub My_Copy () Если Selection.Count> 1 Затем Set rCopyRange = Selection.SpecialCells (xlVisible) Иначе: Set rCopyRange = ActiveCell End If End Sub’ С помощью этого макроса мы вставляем данные, начиная с выбранных ячеек. Sub My_Paste (), если rCopyRange — ничего, выход из Sub, если rCopyRange.Areas.Count> 1, то MsgBox «Вставляемый диапазон не должен содержать более одной области!», vbCritical, «Недопустимый диапазон «: Exit Sub Dim rCell As Range, li As Long, le As Long, lCount As Long, iCol As Integer, iCalculation as Integer Application.ScreenUpdating = False iCalculation = Application.Calculation: Application.Calculation = -4135 For iCol = 1 To rCopyRange .Columns.Count li = 0: lCount = 0: le = iCol — 1 для каждого rCell в rCopyRange.Columns (iCol) .Cells Do If ActiveCell.Offset (li, le) .EntireColumn.Hidden = False и _ ActiveCell. Offset (li, le) .EntireRow.Hidden = False Then rCell.Copy ActiveCell.Offset (li, le): lCount = lCount + 1 End If li = li + 1 Loop While lCount> = rCell.Row — rCopyRange.Cells (1) .Row Next rCell Next iCol Application.ScreenUpdating = True: Application.Calculation = iCalculation End Sub

Для полноты картины лучше всего назначить эти макросы горячим клавишам (в приведенных ниже кодах это делается автоматически при открытии книги с кодом). Для этого просто скопируйте следующие коды в модуль ThisWorkbook):

Явный параметр ‘Отменить назначение горячих клавиш перед закрытием частной вспомогательной книги_BeforeClose (Отменить как логическое значение) Application.OnKey «^ q»: Application.OnKey «^ w» End Sub’ Назначить горячие клавиши при открытии частной вспомогательной книги_Open () Application.OnKey «^ Q», «My_Copy»: Application.OnKey «^ w», «My_Paste» End Sub

Скопируйте только видимые ячейки и вставьте только в видимые
По просьбе посетителей сайта я решил изменить эту процедуру. Теперь вы можете копировать любой диапазон: со скрытыми строками, скрытыми столбцами, а также вставлять скопированные ячейки в любой диапазон: со скрытыми строками, скрытыми столбцами. Он работает точно так же, как и предыдущий: нажав клавиши Ctrl + q, скопируйте нужный диапазон (со скрытыми/отфильтрованными или не скрытыми строками и столбцами) и вставьте его с помощью комбинации клавиш Ctrl + w. Вставка также выполняется в скрытые/отфильтрованные строки и столбцы или без скрытых.
Если скопированный диапазон содержит формулы, то во избежание перемещения ссылок можно копировать только значения ячеек, т.е при вводе значений будут вставлены не формулы, а результат их вычисления. Или, если вам нужно сохранить форматы ячеек, в которые происходит вставка, будут скопированы и вставлены только значения ячеек. Для этого нужно заменить строку в коде (в файле ниже):

Основы работы с фильтрами

Применение фильтров к таблице

Щелкните значок в столбце «Менеджер». Откроется следующее меню:

В этом меню с помощью флажков вы можете отметить элементы, по которым вы хотите фильтровать данные.

Если в таблице много значений, используйте строку поиска. Для этого начните набирать ту часть слова, которую вам нужно найти в нем. Список опций автоматически сократится.

Недостатком этого метода является то, что вы можете отметить только одно значение или несколько значений, содержащих нужную фразу, но невозможно найти абсолютно разные значения. То есть, например, таким способом нельзя сразу найти менеджеров с именами Сергей и Александр, но можно найти все значения, содержащие «Серг»: Сергей, Сергеев, Сергиенко и т.д.

Например, вам просто нужно отметить 2 значения из нескольких десятков. Снятие флажка вручную из любого положения, кроме необходимых, занимает много времени. Чтобы ускорить этот процесс, снимите флажок (Выбрать все). Это снимет все остальные флажки. Теперь вы можете отмечать только те предметы, которые вам нужны.

MS Excel поддерживает несколько фильтров, например фильтрацию по нескольким столбцам одновременно.

Например, вам нужно найти все заказы от менеджера Иванова от 18.01.2014.

Сначала щелкните столбец «Менеджер» и выберите Иванова.

Теперь щелкните в столбце «Дата отправки», снимите флажок «(Выбрать все)» и выберите 18 января 2014 г или введите 18 в строке поиска и нажмите «ОК».

Таблица будет выглядеть так:

Точно так же вы можете продолжить фильтрацию данных на основе столбцов «Описание», «Кол-во» и т.д.

Обратите внимание, что в столбцах, которые были отфильтрованы, значок меняется на .

Таким образом, вы всегда будете знать, из каких столбцов фильтруются данные.

Отмена фильтра

Чтобы удалить сразу все фильтры, перейдите на вкладку «Данные» и нажмите кнопку «Очистить».

Если вам нужно удалить фильтр только из одного столбца, оставив фильтры для других, щелкните значок этого столбца, например «Дата отгрузки», и щелкните запись «Удалить фильтр из »:

или

Если вам нужно полностью отказаться от фильтров в таблице, перейдите во вкладку «Данные» и нажмите кнопку «Фильтр». Он перестанет подсвечиваться, значки исчезнут из строки с заголовками и все данные отобразятся в таблице.

После

Дополнительные настройки фильтров

У фильтров есть дополнительные параметры в зависимости от типа содержимого столбцов.

Текстовые фильтры

Щелкните значок в столбце «Управление», наведите указатель мыши на «Текстовые фильтры», дождитесь появления меню и выберите один из критериев выбора или пункт «Пользовательский фильтр…». Появится следующее окно:

• 1. Условия «равно» или «не равно» предполагают, что искомое выражение на сто процентов равно содержимому ячейки. Критерий «равно» оставляет только те строки таблицы, которые содержат выбранное значение. В результате критерий «не равно» оставляет все значения, кроме одного, выбранным. Чтобы упростить задачу, вы можете выбрать нужное значение из выпадающего списка:

• 2. Условия «больше» и «меньше» предполагают, что таблица будет содержать значения, которые начинаются в алфавитном порядке с предшествующей или следующей буквы. Например, если вы выберете значение «Иванов» с опцией «другое», в таблице останутся только ячейки, начинающиеся с буквы «Y» (Картов, Йогуртов и т.д.). А с опцией «минус» — значения на букву «Z» (Захаров, Букин).
• 3. Единственная разница между условиями «больше или равно» и «меньше или равно» в предыдущем абзаце состоит в том, что фильтр также включает выбранное значение.
• 4. Если вам нужно найти все значения, начинающиеся с «Willow», используйте условие «начинается с», а если вы хотите узнать, сколько значений в таблице оканчивается на «rovich», выберите «заканчивается на» вариант».
• 5. Следовательно, условия «не начинается на» и «не заканчивается на» предполагают, что вам не нужно отображать значения, содержащие поисковую фразу в таблице.
• 6. Если вы выбрали условия «содержит» или «не содержит», вы можете указать любую фразу или комбинацию букв, которые вы хотите включить или исключить из фильтра. Разница между этим элементом и элементами 1, 4 и 5 заключается в том, что искомую фразу можно найти в любом месте ячейки. Например, если вы установите фильтр «Ива», результатом будут «Иванов Алексей», «Сергей Иваровский», «кривые» и т.д.

Числовые фильтры

Большинство условий такие же, как и для текстовых фильтров. Рассматривайте только новые.

• 1. Условие «между». При выборе этого условия в появившемся окне сразу же устанавливаются необходимые критерии, что облегчает вам задачу:

• Вам просто нужно ввести значения с клавиатуры или выбрать их из списка.
• 2. Состояние «10 лучших». Этот элемент имеет следующие параметры:< >Покажите наименьшие или наибольшие значения.Сколько значений отображать.Этот абзац требует пояснения по второму значению:% от количества элементов. Например, у вас есть 15 строк с числовыми значениями в вашей таблице. Если вы выберете 20%, в таблице останется только 15/100 * 20 = 3 строки.

   

• 3. Если вы выберете «Выше среднего» или «Ниже среднего», Excel автоматически вычислит среднее арифметическое в столбце и отфильтрует данные на основе критерия.

Фильтр по дате

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

И еще немного о фильтрах

Есть еще один способ отфильтровать данные. Немного преобразим нашу таблицу:

Как видите, мы его раскрасили.

Теперь, например, нам нужно найти все строки с Красоткиным. Щелкните правой кнопкой мыши ячейку с этим человеком и выберите в появившемся меню пункт «Фильтр». В новом меню появилось несколько новых опций. В этом примере нам понадобится элемент «Фильтр по значению…».

Если выбрать условие «Фильтровать по цвету…», таблица будет содержать строки с ячейками того же цвета, что и активная ячейка (желтая заливка).

Если вы нажмете «Фильтровать по цвету шрифта…», в нашей таблице останутся только ячейки с красным или черным шрифтом, в зависимости от того, какая ячейка активна в данный момент.

Последний элемент фильтра применим только в том случае, если в таблице используется условное форматирование с помощью значков.

Как скопироавть отфильтрованные строки в новый Excel файл

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

Как макрос работает

Этот макрос захватывает диапазон автоматической фильтрации, открывает новую книгу, а затем заполняет данные.

1. Свойство AutoFilterMode используется для проверки наличия на листе автоматических фильтров. Если нет, то выходим из процедуры.
2. Каждый объект AutoFilter имеет свойство Range. Это свойство Range возвращает строки, к которым применяется автофильтр, то есть возвращает только строки, которые появляются в отфильтрованном наборе данных.
3. Далее мы используем метод копирования, чтобы захватить эти строки, а затем вставить их в новую книгу. Обратите внимание, что мы используем Workbooks.Add.Worksheets, это говорит Excel вставить данные в первый лист вновь созданной книги.
4. Следующий шаг дает команду Excel изменить размер столбцов в соответствии с вновь введенными данными.

Как использовать

Чтобы реализовать этот макрос, вы можете скопировать и вставить его в стандартный модуль:

1. Активируйте редактор Visual Basic, нажав ALT + F11.
2. Щелкните правой кнопкой мыши имя проекта / рабочей папки в окне проекта.
3. Выберите Вставить форму.
4. Введите или вставьте код.