Автоматическое заполнение ячеек в excel по условию

Обновлено: 21.11.2024

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

Что бы это ни было, требуется много времени, чтобы все это перечислить и правильно отформатировать…

… Но Excel может сделать это намного проще и быстрее!

Быстрое заполнение и автоматическое заполнение — это мощные инструменты, которые могут значительно ускорить вашу работу в Excel.

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

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

Оглавление

Получите БЕСПЛАТНЫЙ файл с упражнениями

Прежде чем мы начнем, загрузите приведенный ниже пример книги.

Он содержит электронные таблицы, которые вы можете использовать, чтобы следить за тем, как мы узнаем о мгновенном заполнении и автозаполнении. И это значительно облегчает обучение!

Загрузите БЕСПЛАТНЫЙ файл упражнения

Объединение данных с быстрой заливкой

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

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

Сначала мы вручную введем адрес. Начнем с первого сотрудника, Уинфреда Лоакса из Fivespan.

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

Каспер Лангманн, соучредитель Spreadsheeto

Теперь мы воспользуемся мощью Excel.

Нажмите кнопку "Быстрая заливка" на вкладке "Данные" ленты.

После того как вы это сделаете, Excel сделает все возможное, чтобы сохранить шаблон.

Давайте посмотрим, как это было в нашем случае:

Очень впечатляет! Excel объединил имя и фамилию с названием компании для создания адресов электронной почты.

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

Очистите столбец D и щелкните ячейку D4.

Мы удалили пробел между двумя словами, но будет ли Excel достаточно умен, чтобы сделать то же самое для других названий компаний, состоящих из двух слов?

И будет ли он по-прежнему работать с именами, состоящими из одного слова? Давайте узнаем.

Нажмите кнопку "Быстрая заливка".

Конечно!

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

Эффективное использование мгновенного заполнения часто связано с поиском правильного способа показать Excel то, что вам нужно.

Каспер Лангманн, соучредитель Spreadsheeto

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

Скачать рабочую тетрадь

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

Автоматическое заполнение ячейки на основе другой ячейки (5 методов)

В этом разделе мы покажем вам пять наиболее эффективных способов автоматического заполнения ячеек.

1. Автозаполнение до конца данных в Excel

Шаг 1:

В следующем примере у нас есть набор имен в столбце «Имя». Нам нужно поставить их «ID» последовательно.

Шаг 2:

Теперь мы можем вручную вставить их «ID» или сделать это, перетащив курсор мыши. Но когда вы работаете с 3000 или 40000 данных, это может занять целый день. Чтобы сделать это с умом, зажмите клавишу «SHIFT» на клавиатуре. Теперь, когда вы подведете мышь к углу ячейки, вы заметите этот значок курсора с двумя горизонтальными параллельными линиями

Шаг 3:

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

И наше автозаполнение ячеек готово.

2. Использование функции СЦЕПИТЬ для автозаполнения ячейки

Шаг 1:

Рассмотрите новый пример, в котором у нас есть «Имя» и «Фамилия» какого-то кандидата. Теперь нам нужно автоматически заполнить столбец «Полное имя» на основе этих двух других столбцов.

Шаг 2:

Теперь мы воспользуемся функцией «СЦЕПИТЬ» для автоматического заполнения этих полных имен.

Выберите ячейку «D4» и примените функцию «СЦЕПИТЬ».

Окончательная формула:

Нажмите «Ввод», чтобы применить функцию. И автоматически заполненный результат здесь основан на этих двух столбцах.

Шаг 3:

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

3. Автозаполнение пустых ячеек на основе другой ячейки

Рассмотрите ситуацию, когда вам нужно заполнить пустые ячейки в таблице данных. Эта задача может быть очень утомительной, если база данных большая. Но вы можете легко заполнить эти пробелы автоматически. Чтобы узнать, выполните следующие действия.

Шаг 1:

В следующем примере мы указали «Имя» и «Тема» некоторых учащихся. Большинство из них с «факультета естественных наук» и получают «ДА». Тем, кто не с «факультета естественных наук», выдаются пустые клетки. Нам нужно автоматически заполнить эти ячейки соответствующими словами.

Шаг 2:

Выберите таблицу данных, перейдите в меню «Главная», затем нажмите «НАЙТИ И ВЫБРАТЬ» и выберите «Перейти к специальным».

Выбрать таблицу данных → Главная страница → Найти и выбрать → Перейти к специальному

Появилось новое окно. Выберите «Пробелы» и нажмите «ОК»

Шаг 3:

Теперь мы выбрали все наши пустые ячейки.

В одну из пустых ячеек вставьте текст «НЕ ПРИМЕНИМО».

На последнем шаге нажмите «CTRL+ENTER», чтобы применить то же самое к остальным ячейкам.

Вот как наши пустые ячейки заполняются автоматически.

4. Использование функции ЕСЛИ для автозаполнения ячейки

Функция "ЕСЛИ" также полезна, когда нам нужно автоматически заполнить ячейки на основе других ячеек.

Шаг 1:

В следующем случае указываются «Имя» некоторых студентов, их «Предмет», «Факультет» и «Наличие» этих предметов. Нам говорят, что доступны только предметы, связанные с наукой. Поэтому мы должны автоматически заполнить эти ячейки соответствующими текстами.

Шаг 2:

В ячейке «Е4» примените функцию «ЕСЛИ». Формула

Нажмите «Ввод», чтобы продолжить. Таким образом, предмет, связанный с наукой, получит ответ «ДА», а остальные получат ответ «Н/П».

Шаг 3:

Выберите и перетащите курсор мыши, чтобы применить то же самое к остальным ячейкам.

5. Использование функции ВПР для автозаполнения ячейки

Шаг 1:

Предположим, у нас есть случайный порядковый номер некоторых кандидатов. Нам нужно расположить их последовательно. Теперь сделайте еще одну таблицу с порядковыми номерами. Мы автоматически заполним столбцы «Имя» в соответствии с этими сериалами, используя функцию «ВПР».

Шаг 2:

В ячейке «F4» примените функцию ВПР. Вставьте значения. Окончательная формула:

Нажмите «Ввод». Имя относительно серийного номера находится здесь

Шаг 3:

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

Что нужно помнить

➤ При автоматическом заполнении до конца данных сначала заполните 2 или 3 числа вручную, чтобы автоматически заполнить серию. Если вы вставите только одни данные, Excel не поймет серию.

➤Когда вы выбираете свой «Table_Array», вы должны использовать абсолютные ссылки на ячейки ($), чтобы заблокировать массив.

Заключение

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

Это продолжается для 38 000 строк, и мне нужно автоматически заполнить пустые ячейки в столбце B начальным значением в столбце B, связанным с уникальными значениями в столбце A, если таковой существует, и вставить "N/A", если есть не имеет значения, связанного с уникальным номером в столбце A. Результат будет выглядеть следующим образом.

Есть ли способ сделать это в Excel? Если нет, то любое руководство по другим программам также будет полезно. Исходные данные находятся в формате .CSV, но они неаккуратны. Лист Excel аккуратный, но неполный, как показано выше.

3 ответа 3

Скопируйте эти два столбца и вставьте их на другой лист.
Фильтр для пустых мест во втором столбце
Удалить отфильтрованные пустые ячейки Теперь у вас остались буквы справа и соответствующие цифры слева. Это справочная таблица для вас.
Поместите функцию vlookup (либо на букву А, либо (чтобы быть осторожным) в 3-й колонке рядом с буквой А) и перетащите ее вниз - вот и все, просто и легко

Вы можете использовать Index,Match или VLookup (я предпочитаю Index,Match)

В основном, как я смотрю на вашу проблему, у вас может быть еще один лист в книге с данными, которые вам нужно автозаполнить, и страница с фактическим выводом. Пример (мы назовем его «Лист2», я добавил заголовки, чтобы не запутаться):

Затем на странице, где вам нужно автозаполнение данных (Лист1):

Вы можете написать в B1 только 1A, как вы написали, или =A1, оно будет иметь такое же значение, в B2 вы должны написать эту формулу:
=IF(LEFT(A2,1)=LEFT(A1, 1),B1,IF(LEN(A2)=1,A2&" N/A",A2))
Если ваши данные похожи на то, что написано выше, без пустых ячеек, у вас будет тот же результат, и вы можете перетащите формулу вниз, она автоматически изменит ссылки. Эта формула проверяет, является ли первый символ между двумя последовательными строками одинаковым, она запишет одно и то же значение в столбце B, если нет второго, если проверьте, находится ли в ячейке только один символ, она добавит N/A, иначе это больше, чем один он напишет это в B

Не тот ответ, который вы ищете? Просмотрите другие вопросы с меткой microsoft-excel или задайте свой вопрос.

Связанные

Горячие вопросы о сети

Чтобы подписаться на этот RSS-канал, скопируйте и вставьте этот URL-адрес в программу для чтения RSS.

Если вы хотите сэкономить время и силы на вводе данных, мы покажем вам, как использовать автозаполнение в Excel для текста, дат, формул и многого другого.

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

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

Что такое автозаполнение в Excel?

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

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

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

Автозаполнение текстовой серии или списка

Начнем с нашего базового примера. Вот как заполнить ряд месяцев в столбце.

Введите в ячейку первый элемент серии. Здесь мы входим в январь.
Выделив ячейку, переместите курсор в правый нижний угол, пока не появится маркер заполнения (черный знак плюса).
Удерживая левую кнопку мыши, перетащите оставшиеся ячейки в столбце, чтобы они автоматически заполнились. Вы заметите, что при перетаскивании отображается небольшое поле с данными, которые нужно поместить в каждую ячейку.
Отпустите кнопку мыши, когда закончите.

Заполнение этого типа текстовой серии в основном осуществляется с помощью списка в Excel; будь то список месяцев в году или дней в неделе. Но в Excel вы также можете создавать и автозаполнять пользовательские списки. Это полезно, если вы снова и снова вводите одни и те же элементы в свои листы. Это может быть список продуктов, цветов или даже людей.

Если у вас есть пользовательские списки, настроенные в Excel, используйте те же шаги автозаполнения, что и выше, и ваш список должен распознаваться и правильно заполняться при перетаскивании.

Автозаполнение серии дат

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

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

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

Автозаполнение шаблонной серии

Вы уже видите, что Excel — это умный инструмент. Поэтому, когда дело доходит до использования автозаполнения для серии, он может распознавать шаблоны в серии. Допустим, у вас есть суммы в долларах, которые увеличиваются на 50 долларов США для отслеживания сбережений.

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

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

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

Формулы автозаполнения

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

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

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

Форматирование автозаполнения

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

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

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

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

Автозаполнение в Excel позволяет сэкономить время

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

Сообщите нам, если это работает для вас! Вы можете поделиться своими мыслями с нами в Twitter или Facebook.

Читайте также: