Check-moscow.ru

Финансы и учет
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как заполнить пустые ячейки значением выше / ниже / слева / справа в Excel?

Заполнение пустых ячеек значениями из соседних ячеек

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

изfill-blanks1.pngсделатьfill-blanks2.png

Важно заполнять не только вниз, но и вверх, влево и т.д. Вот несколько способов сделать это.

Способ 1. Без макросов

Выделите диапазон ячеек в первом столбце для заполнения (A1:A12 в нашем примере).

Нажмите F5, затем нажмите кнопку (Специальный) и в появившемся окне выберите Заготовки :

fill-blanks3.png

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

fill-blanks4.png

Наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки, нажмите Ctrl + Enter вместо обычного Enter . И это все. Просто и красиво.

В завершение я бы предложил заменить все созданные вами формулы значениями, поскольку сортировка или добавление/удаление строк может повлиять на формулу. Выделите все ячейки в первом столбце, скопируйте и вставьте в контекстном меню, выбрав «Значения» из «Специальной вставки». Это будет хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если вам приходится часто выполнять эту операцию, имеет смысл создать для нее отдельный макрос, чтобы не повторять всю описанную выше цепочку действий вручную. Нажмите Alt + F11 или кнопку Visual Basic на вкладке Developer, чтобы открыть редактор VBA. Затем вставьте новый пустой модуль через меню Вставка — Модуль и скопируйте или введите следующий короткий код:

Читайте так же:
Как выделить четные или нечетные числа в Excel?

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

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

Способ 3. Power Query

Power Query — это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными практически все, что угодно, в том числе решать нашу проблему заполнения пустых ячеек в таблице. У этого метода есть два основных преимущества:

  • Если у вас много данных, ручной метод с использованием формул или макросов может значительно замедлить работу. Power Query сделает все намного быстрее.
  • Если вы измените исходные данные, вам просто нужно будет обновить Power Query. Если вы используете первые два метода, вам придется начинать все сначала.

Если вы хотите использовать Power Query для загрузки нашего диапазона данных, вам нужно либо дать имя диапазону (с помощью вкладки Формулы — Менеджер имен), либо превратить его в «умную» таблицу (с помощью команды Главная — Формат как таблица или комбинации клавиш CTRL + T):

Превращаем таблицу в умную

После этого на вкладке Данные нажмите кнопку Из таблицы/диапазона. Если у вас установлены отдельно Excel 2010-2013 и Power Query, вкладка будет называться Power Query.

В появившемся редакторе запросов выберите столбец (или несколько столбцов, удерживая клавишу Ctrl) и на вкладке Transform (Трансформация) выберите Fill — Fill Down (Трансформация — Fill — Fill Down):

Заполнение пустых ячеек в Power Query

Вот и все 🙂 Теперь осталось только выгрузить готовую таблицу обратно в лист Excel, используя Home — Close&Load — Close&Load To. (Главная — Закрыть&Загрузить — Закрыть&Загрузить в. )

Читайте так же:
Как добавить начальный ноль к фиксированной длине числа в Excel?

Если исходная таблица изменится в будущем, вы можете просто обновить запрос, щелкнув правой кнопкой мыши или на вкладке Данные — Обновить все.

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

Выберите диапазон пустых ячеек, которые нужно заполнить.

Установите флажок Unmerge cells (Объединить ячейки).

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

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

После нажатия кнопки OK все пробелы в таблице будут заполнены значениями.

Как заполнить пустые ячейки и отменить объединение ячеек в таблице

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector