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

Используем начало последовательности

Если начало последовательности уже задано (т. задан первый элемент и шаг последовательности), то создать последовательность 1, 2, 3,. можно следующим образом:

  • пусть в ячейке

    А2

    введено значение

    1

    , а в ячейке

    А3

    значение

    2

    ;

  • выделяем ячейки

    A

    2

    и

    A

    3

    ;

  • беремся за правый нижний угол и

    Маркером заполнения

    протягиваем вниз.

Получаем результат как в предыдущем случае. Если в ячейке

А3

введено значение

3

, т. задан шаг последовательности равный 2, то мы получим последовательность нечетных чисел.

Создадим последовательность вида

1, 2, 3, 1, 2, 3,. для этого введем в первые три ячейки значения 1, 2, 3, затем

маркером заполнения

, удерживая клавишу

CTRL

, скопируем значения вниз.

Для того, чтобы в Exel заполнить столбец числами от одного до 1000, можно

1) использовать шаблон «Пустая книга», в котором в крайнем левом столбце таблицы уже имеются цифры;

2) в двух ячейках столбца указать цифры «1» и «2», затем выделить обе ячейки и, удерживая левой кнопкой мыши маркер автозаполнения (зелёный квадратик, находящийся в правом нижнем углу), протянуть его вниз;

3) в одной ячейке столбца указать цифру «1», протянуть её (в результате чего должен получиться столбец с единицами), открыть квадратное окошко, появившееся в правом нижнем углу, и выбрать пункт «Заполнить»;

4) в одной ячейке столбца указать цифру «1» и протянуть её, удерживая клавишу «Ctrl».

  • Проверка данных
  • Расширенный фильтр
  • Создание и Заполнение таблиц данными

Планируйте размещение данных в книге, на листе, в таблице, так как этого ожидает

EXCEL. Похоже, что разработчики

EXCEL

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

Ниже приведены правила «хорошего поведения» при построении таблиц. Действуйте по правилам и EXCEL Вас поймет (что существенно упростит работу).

Организовывайте таблицы по их предназначению:

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

-для отображения информации (здесь включайте условное форматирование, название таблицы, объединенные ячейки и другие украшения). По сути это отчет.

Аналогичным образом в EXCEL организована работа с диаграммами: имеется таблица с исходными данными, а есть их отображение в виде нужной диаграммы. Причем одни и теже данные можно отобразить по разному.

Таблица в формате EXCEL:

-обязательно имеет заголовок

-содержит строку итогов с наиболее популярными функциями (сложение, подсчет и др

-поддерживает автозаполнение строк при их добавлении

-поддерживает предустановленные стили форматирования (чрезстрочное выделение, выделение заголовков)

На основе таких таблиц удобно создавать диаграммы и Сводные таблицы.

заголовок должен занимать одну строку;

-заголовок должен быть текстовым значением (числовые заголовки могут быть восприняты формулами как значение, что может привести к ошибкам);

-заголовок должен иметь отличное от данных форматирование, например жирным шрифтом;

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

Часто при использовании функций для работы с базами данных (БДСУММ(), БИЗВЛЕЧЬ()) наличие заголовка вообще является обязательным условием.

Например, не стоит разбивать информацию о сотруднике на 2 таблицы, если в одной перечислены ФИО, № паспорта и должность, а в другой ФИО, номер трудового договора, стаж, домашний адрес. Подсчитать количество сотрудников со стажем менее 5 лет, занимающих определенную должность в этом случае будет гораздо сложнее;

Если на листе несколько таблиц, то разделяйте их, по крайней мере, одной пустой строкой или пустым столбцом (см. статью

Текущая область

)

;

EXCEL автоматически определяет диапазоны с данными (

текущую область

) при построении формул, графиков,

Сводных таблиц

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

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

;

Объединенные ячейки могут стать причиной некорректной работы

фильтра

,

Сводных таблиц

,

структуры

и др. , например, см. статью

Автоподбор ширины столбца и высоты строки по содержимому ячеек

);

  • Избегайте излишнего форматирования,

    т.к. «на вкус и цвет – товарищей нет» и это, к тому же, увеличивает размер файла.

  • Если предполагается использовать

    Расширенный фильтр

    или функции БДСУММ() , БСЧЕТ() , то желательно оставить 3-5 строк над таблицей для размещения формул, пояснений и критериев;

  • По возможности

    сортируйте данные

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

    Поиск ЧИСЛА ближайшего к заданному. ЧАСТЬ 1. Сортированный список

    );

  • В ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, не рекомендуется в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе для того, чтобы

    отфильтровать

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

  • В каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате

    Дата

    ; столбец «Поставщик» — названия компаний только в текстовом формате);

  • Прежде чем заполнять таблицу данными подумайте какими средствами и функциями EXCEL Вы будете анализировать данные. Если формулы получаются слишком громоздкими, то, возможно, стоит перепроектировать таблицу.
  • Заполняйте пустые ячейки повторяющимися данными (не оставляйте ячейки пустыми)

    . При наличии пустых ячеек затруднено использование

    фильтра

    и построение формул (см. пример №2).

  • Не распределяйте логически связанные данные по нескольким книгам;
  • По возможности избегайте ссылок на данные из других книг

    ;

Лично я уверен, что правильно сформированная исходная таблица — это 80% решения задачи. Ко мне ни разу не обращались за помощью люди, у которых была грамотно сформированная исходная таблица с данными и они не могли на ее основе сделать, например,

Сводную таблицу

или сделать

отчет

с помощью формул.

Ниже приведу несколько примеров неправильно сформированных таблиц (не делайте так!).

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

  • Как быстро выделить пустые ячейки
  • Заполняем значениями сверху или снизу при помощи формулы
  • Как заменить пустые ячейки нулями либо произвольными значениями
  • Используем простой макрос VBA
  • Как быстро заполнить пустые ячейки не используя формулы.

Есть разные способы решения этой проблемы. Я покажу вам несколько быстрых и один ОЧЕНЬ быстрый способ заполнить пустые ячейки значениями. Как выделить пустые ячейки на листах Excel. Перед тем, как заполнить пустоты в таблице Excel, сначала нужно их выделить. Если у вас большая таблица с десятками незаполненных областей, разбросанными по ней, то потребуется много времени, чтобы сделать это вручную. Вот быстрый приём для выбора пустых ячеек.

  • Нажмите Ctrl + G или же F5 для отображения диалогового окна “Перейти”.
  • Щелкните по кнопке «Выделить».
  • Выберите «Пустые ячейки».

Теперь выделены только пустые ячейки из выбранного диапазона, и вы готовы к следующему шагу. Формула Excel для заполнения пустых ячеек значениями, стоящими выше / нижеВыбрав пустые ячейки в таблице, вы можете заполнить их значениями, стоящими сверху или снизу, или же просто вставить какое-то определенное содержимое. Если вы собираетесь заполнить пробелы значением из ближайшей заполненной ячейки выше или ниже, вам нужно ввести очень простую формулу в одну из пустых ячеек. Затем просто скопируйте ее во все остальные. Вот как это сделать.

  • Выделите все незаполненные ячейки, как описано выше.
  • Нажмите F2 или просто поместите курсор в строку формул, чтобы начать писать формулу в активной ячейке.

Как видно на скриншоте ниже, активная ячейка – A3, то есть по умолчанию это самая левая верхняя из всех незаполненных.

  • Введите знак равенства (=).
  • Наведите курсор на ячейку, находящуюся выше или ниже, с помощью клавиши со стрелкой вверх или вниз или просто кликните по ней мышкой.

Формула (=A2) показывает, что A3 получит значение из A2, и будет заполнена предыдущим значением.

Ну вот! Теперь каждая выделенная ячейка ссылается на ячейку, находящуюся над ней. Поэтому рекомендую не останавливаться и сразу после ввода формул заменить их на значения. Выполните следующие простые шаги:

  • У вас выделены все ячейки с формулами, которые вы только что ввели и хотите преобразовать.
  • Нажмите Ctrl + C или же Ctrl + Ins, чтобы копировать формулы и их результаты в буфер обмена.
  • Нажмите Shift + F10 а потом V, чтобы вставить обратно в выделенные позиции только значения.Shift + F10 + V — это самый быстрый способ использовать диалог Excel «Специальная вставка».
  • Выделите пустые ячейки, как мы уже делали.
  • Нажмите F2 для активации режима редактирования в строке формул. Или просто кликните туда мышкой.
  • Введите желаемое число или текст.
  • Нажмите Ctrl + Enter.
  • Выделите диапазон с пустыми ячейками.
  • Нажмите Ctrl + H для отображения диалогового окна «Найти и заменить». Или используйте меню.
  • В этом окне перейдите на вкладку «Заменить».
  • Оставьте поле «Найти» пустым и введите необходимое значение в текстовое поле «Заменить на».
  • Щелкните » Заменить все».

Sub Fill_Blanks()For Each cell In SelectionIf IsEmpty(cell) Then cell. Value = cell. Offset(-1, 0). ValueNext cellEnd Sub

Как легко можно сообразить, этот макрос проходит последовательно по всем выделенным ячейкам и, если они не пустые, то заполняет их значениями из предыдущей ячейки сверху. Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы он был доступен при работе в любом вашем файле Excel. Какой бы способ вы ни выбрали, заполнение таблицы Excel займет у вас буквально минуту. Как быстро заполнить пустые ячейки без использования формул. Если вы не хотите иметь дело с формулами каждый раз, когда заполняете пустоты в вашей таблице, то можете использовать очень полезную надстройку Ultimate Suite для Excel, созданную разработчиками Ablebits. Входящая в неё утилита «Заполнить пустые ячейки» автоматически копирует в пустые клетки таблицы значение из первой заполненной ячейки снизу или сверху. Далее мы рассмотрим, как это работает. Вот наши данные о продажах в разрезе менеджеров и регионов. Некоторые из продавцов работали в нескольких регионах, сведения об их продажах записаны друг под другом. Также объединены ячейки месяцев. Таблица выглядит достаточно читаемо. Однако, если нужно будет отфильтровать или просуммировать данные по менеджерам, или же найти сумму продаж по региону за определенный месяц, то сделать это будет весьма затруднительно. Этому будут мешать пустые и объединенные ячейки. Поэтому постараемся привести таблицу к стандартному виду, заполнив все пустоты и разъединив ранее объединенные области.

Перейдите на ленте на вкладку AblebitsTools.

  • Установите курсор в любую ячейку таблицы, в которой вам нужно заполнить пустые ячейки.
  • Щелкните значок «Заполнить пустые ячейки (Fill Blank Cells)».

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

  • Снимите отметку со столбцов, в которых нет пустых ячеек.
  • Выберите действие из раскрывающегося списка в правом нижнем углу окна.

Если вы хотите заполнить пустые поля значением из ячейки, находящейся выше, выберите параметр «Заполнить ячейки вниз (Fill cells downwards)». Если вы хотите скопировать содержимое из ячейки ниже, выберите в этом же выпадающем списке «Заполнить ячейки вверх (Fill cells upwards)». В нашем случае выбираем заполнение вниз.

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

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

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

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

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

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

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

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

Как посчитать количество пустых и непустых ячеек в Excel

Советы по построению таблиц в MS EXCEL

Рассмотрим пример таблицы для учета денежных средств (приход/ расход).

Исходная таблица пользователя выглядит так.

Просьба пользователя состоит в том, чтобы помочь сделать отчет по типам расходов.

Уникальность примера состоит в том, что здесь практические все «не так».

  • Столбец В (приход) практически не заполняется;
  • Столбец D — лишний;
  • Строка итогов находится под заголовком, а не над ним или под таблицей.

На основе этой таблицы невозможно построить Сводную таблицу или применить Автофильтр. Например, при применении Автофильтра среди сумм расходов фигурирует итоговая сумма расходов!

Теперь избавимся от лишних столбцов.

Заметим, что в статьи расходов попали суммы прихода денежных средств. Чтобы не ввести в замешательство пользователей можно либо вводить суммы расходов со знаком минус, а приходов с +, либо сделать еще один столбец «Тип платежа», где указывать Платеж это или Расход. В нашем случае просто переименуем заголовок «Статья расходов» в «Тип платежа».

Теперь переделаем расчет итогов.

Как видно из рисунка выше, мы преобразовали таблицу в

формат таблиц EXCEL 2007

, а итоги разместили над таблицей.

И, наконец, заключительный аккорд — составление

Сводной таблицы.

Сводная таблица получается в несколько кликов и позволяет провести простейший анализ по статьям расходов. Строку Приход можно скрыть с помощью фильтра (см. ячейку

А3

).

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

Пример №2 неправильного построения таблицы (наличие в строках пустых ячеек)

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

Как видно из рисунка выше, у одного сотрудника может быть несколько предыдущих мест работы. Тот, кто делал таблицу, в целях экономии времени, решил оставить пустыми некоторые ФИО, предполагая, что и так понятно к кому относится место работы. Незаполненные ячейки выделены розовым.

Чтобы отобразить все места работы сотрудника

Козлов

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

Фамилия, Имя, Отчество, Код_сотрудника.

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

Совет:

О быстром способе заполнения значениями из соседних ячеек читайте

здесь.

Пример №3 неправильного построения таблицы (повторяющиеся столбцы)

Спроектируем таблицу для случая учета приобретения материалов в целях ремонта (за этот пример отдельное спасибо одному из пользователей нашего сайта).

Ремонт происходит в здании в 6 разных комнатах №№41-46. В каждой комнате нужно разместить 5 типов розеток (трех-, двухфазные, разного цвета и др. ) К каждому типу розетки нужен свой кабель (с разным количеством жил, диаметром).

Сначала создадим таблицу, где номера комнат расположены в столбцах.

Вроде бы все правильно: легко подсчитать количество розеток и кабеля для каждой комнаты =СУММ(B5:B9). Подсчет общего количества розеток для всех комнат ( =B10+D10+F10+H10+J10+L10 ), вроде тоже легко осуществить, хотя и не совсем удобно, при вводе формулы можно ошибиться столбцом или забыть просуммировать нужную ячейку.

Теперь подсчитаем общее количество розеток каждого типа и длину кабеля для каждого типа розетки. Для этого создадим другую таблицу (см. рисунок ниже). В ячейку

B16

введем формулу =B5+D5+F5+H5+J5+L5

Далее,

Маркером заполнения

, формулу нужно скопировать в ячейки ниже и вправо.

Если комнат в нашем примере было бы не 6, а, например, 18 или 50, то стало бы очевидно, что такой подход не годится (таблица разрастается вправо, а не вниз, формулы становятся сложнее). Конечно, для подсчета розеток определенного типа вместо формулы =B5+D5+F5+H5+J5+L5 можно написать формулу =СУММПРОИЗВ(ЕЧЁТН(СТОЛБЕЦ(B5:M5))*B5:M5) , но как правило, пользователь способный написать такую формулу, изначально проектирует таблицу по другому.

Более корректно спроектировать таблицу, разместив номера комнат в строках:

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

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

Например, с помощью

фильтра

(

CTRL+SHIFT+L

) и функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ() можно быстро подсчитать количество розеток для каждой комнаты.

Стандартная и общеизвестная функция СУММЕСЛИ() быстро позволяет подсчитать количество розеток определенного типа =СУММЕСЛИ($A$6:$A$35;$A42;B$6:B$35) и соответствующую длину кабеля =СУММЕСЛИ($A$6:$A$35;$A42;C$6:C$35).

Формула теперь не сильно зависит от количества комнат, меняется лишь размер диапазона ячеек.

При желании, можно с помощью

Проверки данных

организовать

Выпадающий список

и получить детальные отчеты по каждому типу розетки или номеру комнаты.

Определенной платой за удобство является повторяющийся ввод номеров комнат (столбец D) и наименований типов розеток (столбец А).

Используем формулы

Сформируем последовательность 1, 2, 3,. Пусть в ячейке

A2

введен первый элемент последовательности — значение

1. В ячейку

А3

, вводим формулу

=А2+1

и копируем ее в ячейки ниже (см. файл примера

).

Так как в формуле мы сослались на ячейку выше с помощью

относительной ссылки

, то EXCEL при копировании вниз модифицирует вышеуказанную формулу в

=А3+1

, затем в

=А4+1

и т. , тем самым формируя числовую последовательность 2, 3, 4,.

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

B2

и копировать ее нужно не вниз, а вправо.

Чтобы сформировать последовательность нечетных чисел вида 1, 3, 7,. необходимо изменить формулу в ячейке

А3

на

=А2+2. Чтобы сформировать последовательность 100, 200, 300,. необходимо изменить формулу на

=А2+100

, а в ячейку

А2

ввести 100.

Другим вариантом создания последовательности 1, 2, 3,. является использование формулы

=СТРОКА()-СТРОКА($A$1)

(если первый элемент последовательности располагается в строке

2

). Формула

=СТРОКА(A2)-СТРОКА($A$1)

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

=ЧСТРОК($A$1:A1)

,

=СТРОКА(A1)

и

=СТРОКА(H1). Формула

=СТОЛБЕЦ(B1)-СТОЛБЕЦ($A$1)

создает последовательность, размещенную горизонтально. Тот же результат дают формулы

=ЧИСЛСТОЛБ($A$1:A1)

,

=СТОЛБЕЦ(A1).

Чтобы сформировать последовательность

I, II, III, IV

,. начиная с ячейки

А2

, введем в

А2

формулу

=РИМСКОЕ(СТРОКА()-СТРОКА($A$1))

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

РИМСКОЕ()

возвращает текст. Таким образом, сложить, например, числа

I+IV

в прямую не получится.

Другим видом числовой последовательности в текстовом формате является, например, последовательность вида

00-01

,

00-02,. Чтобы начать нумерованный список с кода

00-01

, введите формулу

=ТЕКСТ(СТРОКА(A1);»00-00″)

в первую ячейку диапазона и перетащите

маркер заполнения

в конец диапазона.

Выше были приведены примеры арифметических последовательностей. Некоторые другие виды последовательностей можно также сформировать формулами. Например, последовательность

n2+1

((n в степени 2) +1) создадим формулой

=(СТРОКА()-СТРОКА($A$1))^2+1

начиная с ячейки

А2.

Создадим последовательность с повторами вида

1, 1, 1, 2, 2, 2,. Это можно сделать формулой

=ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/3+1). С помощью формулы

=ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2

получим последовательность

2, 2, 2, 2, 4, 4, 4, 4,. , т. последовательность из четных чисел. Формула

=ЦЕЛОЕ((ЧСТРОК(A$2:A2)-1)/4+1)*2-1

даст последовательность

1, 1, 1, 1, 3, 3, 3, 3,.

Примечание. Для выделения повторов использовано

Условное форматирование.

Формула

=ОСТАТ(ЧСТРОК(A$2:A2)-1;4)+1

даст последовательность

1, 2, 3, 4, 1, 2, 3, 4,. Это пример последовательности с периодически повторяющимися элементами.

Примечание. Последовательности с повторами использованы, например, в статьях

Перебор всех возможных Сочетаний с повторениями в MS EXCEL

и

Перебор всех возможных целочисленных комбинаций в MS EXCEL.

Используем клавишу CTRL

ВНИМАНИЕ!

Если на листе часть строк скрыта с помощью

фильтра

, то этот подход и остальные, приведенные ниже, работать не будут. Чтобы разрешить нумерацию строк с использованием клавиши

CTRL

, выделите любую ячейку с заголовком

фильтра

и дважды нажмите

CTRL

+

SHIFT

+

L

(сбросьте фильтр).

Используем правую клавишу мыши

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

  • вводим в ячейку

    А2

    значение

    1

    ;

  • выделяем диапазон

    A

    2:А6

    , в котором будут содержаться элементы последовательности;

  • вызываем инструмент

    Прогрессия

    (

    ), в появившемся окне нажимаем ОК.

Использование в работе

:

Подходы для создания числовых последовательностей можно использовать для

нумерации строк

,

сортировки списка с числами

,

разнесения значений по столбцам и строкам.

СОВЕТ:

О текстовых последовательностях вида

первый, второй,. , 1), 2), 3),. можно прочитать в статье

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

01. 09, 01. 09, 01. 09,. , янв, апр, июл,. , пн, вт, ср,. можно прочитать в статье

Последовательности дат и времен. О массивах значений, содержащих последовательности конечной длины, используемых в

формулах массива

, читайте в статье

Массив значений (или константа массива или массив констант).