Заполнить ячейки в excel значениями по порядку
Обновлено: 21.11.2024
Некоторые рабочие листы Microsoft Excel содержат ячейки, оставленные пустыми, чтобы облегчить чтение заголовков и подзаголовков. Однако, если вы хотите отсортировать или отфильтровать список, вам необходимо заполнить пробелы, скопировав значение из первой заполненной ячейки над пробелом. Следуйте инструкциям на этой странице, чтобы заполнить пустые ячейки вручную или с помощью макроса Excel.
Примечание. Чтобы заполнить пустые ячейки в заголовках сводной таблицы, узнайте, как повторять метки элементов в Excel 2010 и более поздних версиях.
Заполнить пустые ячейки сверху
На снимке экрана ниже ячейки оставлены пустыми, поэтому заголовки и подзаголовки отчета легче читать. Если вам нужно отсортировать или отфильтровать эти данные, вам нужно заполнить пустые ячейки, скопировав значение из первой заполненной ячейки над пустым
Чтобы заполнить пустые ячейки вручную, выберите все пустые ячейки, введите простую формулу в каждую ячейку, а затем преобразуйте формулы в значения. Посмотрите видео ниже, чтобы увидеть шаги, а письменные шаги находятся под видео.
Видео: заполнение пустых ячеек вручную
В этом видео показано, как вручную выбрать и заполнить пустые ячейки значением из ячейки выше. Затем с помощью ярлыка мыши измените формулы на значения, чтобы вы могли безопасно сортировать и фильтровать данные.
Примечание. В конце страницы есть стенограмма видео.
Как заполнить пустые ячейки вручную
Чтобы заполнить пустые ячейки вручную, необходимо выполнить 3 основных шага:
1) Выберите пустые ячейки
Первый основной шаг — выделить все пустые ячейки, которые вы хотите заполнить.
Чтобы выбрать пустые ячейки с помощью встроенной в Excel функции "Перейти к специальной", выполните следующие действия:
- Выберите столбцы A и B, содержащие пустые ячейки.
- Используйте команды ленты:
- На вкладке "Главная" ленты Excel в группе "Редактирование" нажмите "Найти и выбрать".
- Нажмите команду "Перейти к специальному"
- Нажмите Ctrl + G, чтобы открыть окно "Перейти".
- Нажмите Alt + S, чтобы открыть диалоговое окно "Перейти к специальному"
Перейти к специальному диалоговому окну
- В диалоговом окне "Перейти к специальному" нажмите "Пробелы"
- ИЛИ нажмите Alt + K (подчеркнутая буква в поле "Пробелы").
Выбраны пустые ячейки
После закрытия диалогового окна "Перейти к специальному":
- выбираются пустые ячейки в столбцах A и B
- первая пустая ячейка, A3, является активной ячейкой.
- выбранные ячейки находятся только в пределах используемого диапазона данных, даже если были выбраны целые столбцы
2) Создать простую формулу
Затем выполните следующие шаги, чтобы создать простую формулу, которая будет копировать значение из ближайшего заголовка над каждой пустой ячейкой.
В настоящее время выбраны пустые ячейки в столбцах A и B, а ячейка A3 является активной.
Чтобы построить формулу, выполните следующие действия:
- Выделив пустые ячейки, введите знак равенства, чтобы начать формулу.
- На клавиатуре нажмите клавишу со стрелкой вверх
- Это вводит относительную ссылку на ячейку выше.
- Ячейка A3 активна, поэтому она ссылается на ячейку A2, ячейку непосредственно над ней.
На снимке экрана ниже вы можете увидеть формулу в ячейке A3 и в строке формул:
Введите формулу во все выделенные ячейки
На последнем шаге эта формула будет введена во все выделенные пустые ячейки
- НЕ нажимайте клавишу Enter, как обычно, чтобы ввести формулу.
- Вместо этого нажмите Ctrl + Enter
- Нажатие клавиш Control и Enter введет формулу активной ячейки во все выделенные ячейки.
Поскольку в формуле используется относительная ссылка, формула каждой ячейки ссылается на ячейку непосредственно над ней.
- На этом снимке экрана показана формула в ячейке B4, которая ссылается на ячейку B3.
- Если вы проверите ячейку B3, вы увидите, что ее формула относится к ячейке B2.
3) Изменить формулы на значения
Последний шаг перед сортировкой или фильтрацией данных — преобразование формул "пустых ячеек" в значения, чтобы зафиксировать их.
- ВНИМАНИЕ! Если вы пропустите эти шаги и оставите формулы ссылки на ячейку в ячейках, результаты формулы будут отображать неверные данные позже, если вы отсортируете список.
- используйте команды ленты, чтобы изменить формулы на значения (шаги ниже)
- ИЛИ используйте быструю комбинацию клавиш мыши (видео ниже)
Использование команд ленты для вставки значений
Выполните следующие действия, чтобы изменить формулы "пустой ячейки" на значения
- Выберите все столбцы, в которых вы заполнили пробелы формулами — в этом примере столбцы A:B
- Примечание. Не выделяйте столбцы целиком, если в других ячейках этих столбцов содержатся формулы, которые необходимо сохранить. Вы же не хотите случайно изменить эти другие формулы на значения.
Видео: вставка ярлыков мыши как значений
Чтобы быстро преобразовать формулы в значения, вы можете использовать это сочетание клавиш мыши, показанное в видео ниже. Письменные инструкции находятся на странице Советы по вводу данных в Excel.
Значения во всех выбранных ячейках
После выполнения вышеуказанных шагов для использования команды "Вставить значения" пустые ячейки заполняются значениями, а не формулами ссылок на ячейки.
Если значения ячеек "заблокированы", вы можете безопасно сортировать или фильтровать список.
Заполнение пустых ячеек — макросы
Если вам часто приходится заполнять пустые ячейки, вы можете предпочесть использовать макрос. Следующие примеры кода Excel VBA заполняют пустые ячейки в активном столбце. В каждом примере используются разные методы поиска последней строки и программного заполнения пустых ячеек
Совет. Дополнительные сведения о поиске последней строки см. на странице Рона де Брюйна: Поиск последней строки, столбца или последней ячейки. Рон объясняет преимущества и недостатки каждого метода.
— Макрос 3 — Дэйв Петерсон и Рон де Брюин
Макрос заполнения пустых ячеек — пример 1
В этом макросе заполнения пустых ячеек от Дэйва Петерсона:
- использует формулу для заполнения ячеек
- вставляет результаты как значения.
- использует метод .SpecialCells(xlCellTypeLastCell) для поиска последней строки
- заполняет пробелы от строки 2 до последней строки
Вы можете скопировать приведенный ниже код и вставить его в обычный модуль кода в своем собственном файле Excel. Или загрузите образец файла Excel и скопируйте оттуда код.
Макрос заполнения пустых ячеек — пример 2
Макрос заполнения пустых ячеек от Рика Ротштейна
- использует метод .Find для вычисления последней строки
- Вместо использования формулы для заполнения сверху каждая ячейка получает свое значение из ячейки над первой ячейкой области, в которой она находится, с помощью свойства "Смещение".
Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.
Макрос заполнения пустых ячеек — пример 3
В этом примере код Дейва Петерсона (пример 1) сочетается с тестом специальных ячеек от Рона де Брюина.
- В Excel 2007 и более ранних версиях возникает проблема со специальными ячейками, если в диапазоне специальных ячеек более 8192 различных областей.
- ПРИМЕЧАНИЕ. Эта проблема была устранена в Excel 2010.
Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.
Макрос заполнения пустых ячеек — пример 4
Этот пример макроса принадлежит Алексу Блэкенбургу, который вводил числа в виде текста в свои данные и не хотел, чтобы они преобразовывались в действительные числа. Кроме того, его данные не всегда начинались с первой строки рабочего листа.
В этом макросе Алекс решил следующие проблемы:
- макрос начинается с активной ячейки
- останавливается на последней строке в активном столбце после сброса последней ячейки
- использует .FillDown вместо .Value = .Value
- использование .Value = .Value преобразует текстовые числа в действительные числа
Вы можете скопировать приведенный ниже код и вставить его в свой файл Excel. Или загрузите образец файла Excel и скопируйте оттуда код.
Скрыть повторяющиеся заголовки
После того, как вы заполните пустые ячейки значениями, указанными выше, вы можете использовать прием условного форматирования, чтобы скрыть повторяющиеся заголовки.
Это облегчит чтение списка, как это было с пустыми ячейками.
В этом видео показаны шаги, а письменные инструкции находятся на странице примеров условного форматирования.
Заполнить пустые ячейки стенограммы видео
Вот стенограмма видео "Заполнить пустые ячейки" в верхней части этой страницы.
Введение
Иногда в Excel вы получаете такие данные, возможно, экспортированные из другой системы, где у вас есть заголовки, но пустые ячейки под этими заголовками.
Здесь мы видим регион, и он указан только один раз, а затем пуст до конца региона и сотрудников в каждом регионе. У вас может быть один или несколько сотрудников, но опять же, пробел под именами этих сотрудников.
Это удобно для чтения списка, но если вы хотите работать с данными, например, фильтровать их или сортировать, вам нужно заполнить эти пустые ячейки. Вот быстрый способ сделать это.
Выбрать пустые ячейки
Сначала мы выберем столбцы A и B, в которых есть пробелы.
- Затем на вкладке "Главная" ленты выберите "Найти и выбрать", "Перейти к специальному".
- В окне "Перейти к специальному" нажмите "Пробелы", а затем нажмите "ОК".
Добавить формулу
Теперь все пустые ячейки выделены, и мы собираемся ввести очень простую формулу, которая говорит, что нужно получить значение из ячейки выше.
- Введите знак равенства, а затем нажмите стрелку вверх на клавиатуре. Вы можете видеть, что формула теперь изменилась на A2.
- Чтобы заполнить этой формулой все выделенные ячейки, нажмите клавишу Ctrl, а затем нажмите Enter.
Это помещает одну и ту же формулу во все выбранные ячейки.
Изменить значения
Теперь мы хотим преобразовать эти формулы в значения, чтобы мы могли перемещать элементы без изменения значений.
- Я выберу столбцы A и B, в которых мы заполнили пробелы, а затем укажу на границу выделенного. Там вы увидите четырехконечную стрелку.
- Нажмите правую кнопку мыши и слегка перетащите вправо. Затем перетащите обратно туда, где вы начали.
- Отпустите правую кнопку мыши, и во всплывающем меню появится несколько вариантов. Нажмите «Копировать сюда только как значения».
Теперь все здесь является значением, а не формулой, и вы можете без проблем сортировать или фильтровать.
Получить образец файла
Чтобы просмотреть отчет с пустыми ячейками и протестировать макросы «Заполнить пустые ячейки», вы можете загрузить образец файла книги Excel. Файл заархивирован, имеет формат xlsm и содержит макросы.
Вывод. В этом посте мы рассмотрим 3 способа копирования значений в пустые ячейки столбца. Эти методы включают использование формулы, Power Query и макроса VBA.
Уровень квалификации: средний
Посмотреть обучающее видео
Загрузить файлы Excel
Вы можете скачать файлы BEGIN и FINAL, если хотите попрактиковаться.
Методы заполнения BEGIN.xlsx (323,3 КБ)
Методы заполнения FINAL.xlsm (465,5 КБ)
Заполнение пустых ячеек
Excel упрощает заполнение или копирование значения в ячейки ниже. Вы можете просто дважды щелкнуть или перетащить маркер заполнения для ячейки, которую вы хотите скопировать, чтобы заполнить ячейки под ним одним и тем же значением.
Но есть ли простой способ воспроизвести этот процесс сотни раз для отчетов с большими объемами данных?
В этом посте мы рассмотрим три способа автоматизации этого процесса с помощью простой формулы, Power Query и макроса VBA.
1. Заполнение с помощью формулы
Первый способ решить эту проблему — использовать очень простую формулу во всех пустых ячейках, которые ссылаются на ячейку выше. Вот шаги.
Шаг 1. Выберите пустые ячейки
Чтобы выбрать пустые ячейки в столбце и заполнить их формулой, мы начинаем с выбора всех ячеек (включая заполненные ячейки). Это можно сделать разными способами, в том числе удерживать нажатой клавишу Shift при переходе к нижней части столбца или, если данные находятся в таблице Excel, с помощью сочетания клавиш Ctrl + Пробел .
Выделив все эти ячейки, мы можем сократить выделение, включив в него только пустые ячейки. Это делается с помощью окна «Перейти к специальному» в меню «Найти и выбрать» на вкладке «Главная» ленты.
Нажмите, чтобы увеличить
В качестве альтернативы можно открыть окно «Перейти» с помощью F5 или Ctrl + G . Затем нажмите специальную кнопку, чтобы открыть окно «Перейти к специальному».
Открыв окно «Перейти к специальному», вы можете выбрать вариант с надписью «Пробелы». При этом будут выбраны только пустые ячейки из текущего выделения.
Когда вы нажмете OK, вы заметите, что ячейки со значениями больше не выделены.
Шаг 2. Напишите формулу
Здесь вы можете начать вводить формулу, что очень просто. Введите знак равенства ( = ), а затем укажите ссылку на ячейку выше (в нашем примере это B2). Это все, что вам нужно для формулы!
Шаг 3. Ctrl+Введите формулу
После написания формулы не нажимайте Enter. Вместо этого используйте Ctrl + Enter, чтобы заполнить все выделенные ячейки одной и той же формулой. Удерживая нажатой клавишу Ctrl, нажмите Enter.
Поскольку ссылка на формулу является относительной (B2), а не абсолютной ($B$2), каждая ячейка будет просто копировать значение ячейки непосредственно над ней.
Шаг 4. Скопируйте и вставьте значения
Сейчас рекомендуется скопировать весь столбец данных, а затем вставить его с помощью параметра «Вставить значения», чтобы данные были жестко запрограммированы. Тогда, если вы сделаете какую-либо сортировку, данные не изменятся. Вы можете найти параметр «Вставить значения» на вкладке «Главная» в раскрывающемся меню «Вставить»:
Есть и другие способы вставки значений, в том числе контекстное меню или сочетания клавиш. Эти сообщения объясняют эти варианты:
2. Заполнение с помощью Power Query
Для этого варианта ваши данные должны быть в формате таблицы Excel. В любом месте таблицы вы можете выбрать вкладку Данные или Power Query, а затем выбрать Из таблицы/диапазона. Вы также можете создать запрос, который подключается к другому источнику данных, например к базе данных или Интернету.
Откроется редактор Power Query. В Power Query пустые ячейки помечаются как нулевые в каждой ячейке.
Чтобы заполнить, просто щелкните правой кнопкой мыши заголовок столбца и выберите "Заполнить", а затем "Вниз".
Power Query заполнит каждый раздел пустых ячеек в столбце значением из ячейки над ним.
При нажатии кнопки "Закрыть и загрузить" в книгу будет добавлен новый лист с этими изменениями.
Этот процесс заполнения можно выполнить для нескольких столбцов одновременно, сначала выбрав несколько столбцов с помощью Ctrl или Shift, а затем выполнив операцию «Заполнить > Вниз».
Если Power Query для вас в новинку, я приглашаю вас ознакомиться с этой статьей: Обзор Power Query: введение в самый мощный инструмент данных Excel.
Бесплатный веб-семинар по Power Query, VBA и многому другому
Прямо сейчас у меня проходит бесплатный веб-семинар, посвященный введению в Power Query и другим инструментам Excel, таким как Power Pivot, Power BI, макросы и VBA, сводные таблицы и т. д.
3. Заполнение с помощью макроса
Чтобы заполнить с помощью макроса, начните с открытия редактора VB. Вы можете сделать это, перейдя на вкладку «Разработчик» и нажав кнопку Visual Basic или используя сочетание клавиш Alt + F11. Вставьте новый модуль кода, а затем напишите свой макрос.
Этот макрос перебирает все ячейки в выбранном диапазоне и выполняет метод FillDown, если ячейка пуста. Метод FillDown копирует значение из ячейки выше.
Если вы часто используете этот макрос, возможно, вы захотите добавить его в свою личную книгу макросов и создать для него настраиваемую кнопку. Это то, что я сделал, и это выглядит так на моей ленте.
Чтобы узнать, как создать личную книгу макросов и настраиваемые кнопки ленты, ознакомьтесь с этим руководством: Как создать личную книгу макросов (серия видео).
За и против
Итак, какой из этих трех методов вам подходит? Подумайте об этом:
- Метод формулы прост и удобен в применении. Это можно сделать без знания Power Query или VBA. Однако это не так быстро, как решение с одним нажатием кнопки.
- Power Query требует исходных данных, которые выводятся на отдельный лист, поэтому это больше процесс, чем просто заполнение пустых ячеек. Если вы уже регулярно очищаете данные для других целей, добавление этого в существующий процесс может иметь больше смысла.
- После того как первоначальный макрос настроен, VBA становится быстрым и простым в использовании решением — просто нажмите кнопку. Однако у этого метода есть большой недостаток. Вы не сможете отменить свое действие после нажатия этой кнопки, поэтому вам необходимо заранее сохранить резервную копию, если вас это беспокоит.
Заключение
Существует три разных метода для очень распространенной задачи Excel. Я что-то пропустил? Пожалуйста, оставьте комментарий ниже, если у вас есть другой способ сделать это или у вас есть какие-либо вопросы. Спасибо! 🙂
Обычно созданные нами последовательности восходят. В этой статье мы покажем вам методы заполнения рядов в обратном или убывающем порядке в Excel.
Заполнить серию в обратном порядке или в порядке убывания с помощью маркера заполнения
Для заполнения только ряда чисел в обратном порядке или в порядке убывания, например, для заполнения ряда 100, 99, 98..., вам может помочь функция "Дескриптор заполнения".
<р>1. Введите первое число серии в ячейку (здесь я набираю число 100 в ячейку A1). <р>2. Выберите ячейку A1, поместите курсор в правом нижнем углу ячейки, чтобы отобразить крестик, нажмите и удерживайте правую кнопку мыши, а затем перетащите маркер заполнения вниз к столбцу, пока он не достигнет нужной ячейки. Отпустите кнопку мыши, а затем нажмите Серии во всплывающем меню. Смотрите скриншот:<р>3. В диалоговом окне «Серия» введите убывающее число в поле «Шаг» (здесь мы вводим в поле -1). Затем нажмите кнопку ОК. Смотрите скриншот:
Теперь указанный столбец заполнен убывающими номерами серий.
Примечания:
<р>1. Кроме описанных выше шагов, вы также можете ввести первые два нужных числа в порядке убывания, например 100, 99, в ячейки A1 и A2, выбрать данные, а затем перетащить маркер заполнения вниз к нужным ячейкам.< /p> <р>2. Этот метод работает только для чисел. Если вы хотите создать серию номеров в обратном порядке с текстом, попробуйте метод, описанный ниже.Заполнить серию в обратном порядке или в порядке убывания с помощью Kutools for Excel
В этом разделе мы представим утилиту «Вставить порядковый номер» в Kutools for Excel. С помощью этой утилиты вы можете легко создать нисходящий ряд и заполнить указанный диапазон так, как вам нужно.
Перед применением Kutools for Excel сначала загрузите и установите его.
<р>1. Нажмите Kutools > Вставить > Вставить порядковый номер. Смотрите скриншот:<р>2. В диалоговом окне "Вставить порядковый номер" нажмите кнопку "Создать", чтобы развернуть диалоговое окно, а затем выполните следующую настройку.
<р>3. Теперь серия создана, для заполнения этой серии выберите диапазон и щелкните серию, которую вы создали, в диалоговом окне «Вставить порядковый номер», затем нажмите кнопку «Заполнить диапазон». Затем номер серии (с текстом) сразу же заполняется в выбранном диапазоне.
<р>4. После заполнения ряда закройте диалоговое окно.
Примечание. Все числа в этой нисходящей серии уникальны. В следующий раз при повторном использовании этой серии в диалоговом окне "Вставить порядковый номер" начальный номер будет уменьшаться на единицу по сравнению с последним номером предыдущей вставленной серии.
Если вы хотите получить бесплатную пробную версию (30 дней) этой утилиты, нажмите, чтобы загрузить ее, а затем перейдите к выполнению операции в соответствии с указанными выше шагами.
Мы часто хотим скопировать и вставить или реплицировать данные ячейки в другие ячейки листа, но как мы можем сделать это быстро?С помощью очень мощной функции Microsoft Excel, называемой автозаполнением.
В этом всеобъемлющем учебном пособии пользователю Excel показано, как автоматически заполнять соседние ячейки, выбранные несмежные ячейки или диапазон ячеек.
Обучающие разделы
Как автоматически заполнить столбец или строку (смежные ячейки)
Есть два быстрых способа, которыми пользователь Excel может заполнить соседние ячейки в столбце или строке одними и теми же числовыми или текстовыми данными. В этом разделе показаны оба метода.
- Введите данные в ячейку листа и нажмите Enter.
- Щелкните ячейку с данными и, удерживая нажатой левую кнопку мыши, перетащите, чтобы выбрать остальные ячейки в строке или столбце, которые вы хотите заполнить автоматически.
- Отпустите кнопку мыши.
- Нажмите Ctrl + D (клавиша Ctrl удерживается, пока нажата клавиша D), и ячейки будут заполнены.
Ручка заполнения — это мощный инструмент Excel для автозаполнения линейных рядов, рядов роста и многих других типов данных.
- Введите данные в ячейку электронной таблицы.
- Поместите курсор в правый нижний угол ячейки, которую вы только что ввели, пока не увидите знак "плюс". Удерживая левую кнопку мыши, нажмите и перетащите маркер заполнения (знак плюс), чтобы выделить все ячейки, которые вы хотите заполнить.
- Отпустите кнопку мыши, и ячейки будут заполнены значением, введенным в первую ячейку.
Автозаполнение выбранных несмежных ячеек
Иногда нам нужно автоматически заполнить несмежные ячейки в строке или столбце. Например, у нас может быть пустая строка между каждой строкой данных для удобства чтения, но теперь мы хотим автоматически заполнить часть столбца. Используйте описанную ниже технику для автозаполнения отдельных выбранных ячеек рабочего листа.
- Выберите отдельные ячейки, которые вы хотите заполнить автоматически, нажав и удерживая клавишу Ctrl при щелчке внутри ячеек. На рис. 1 показано, что мы выбрали ячейки A1, A3, A5 и A7.
- Нажав на последнюю ячейку, введите число или текстовое значение, которое должно быть автоматически заполнено в последней ячейке (рис. 2).
- Нажмите клавиши Ctrl + Enter, и выбранные ячейки будут заполнены автоматически (рис. 3).
Как автоматически заполнить диапазон ячеек одними и теми же данными
Методы из первого раздела этого руководства не работают, если вы хотите автоматически заполнить диапазон ячеек, охватывающий несколько столбцов и строк (двумерных). Используйте технику, описанную ниже.
Читайте также: