Автоматическое заполнение ячеек в excel по условию
Спасибо большое, я так понял формулами совсем не гибко, дело в том что данные будут вноситься постоянно и будет неудобно. Может у вас есть похожий код в VBA? Я не силен в макросах, но может получится переделать из похожего. Еще раз спасибо.
Спасибо большое, я так понял формулами совсем не гибко, дело в том что данные будут вноситься постоянно и будет неудобно. Может у вас есть похожий код в VBA? Я не силен в макросах, но может получится переделать из похожего. Еще раз спасибо. toxin0777
Еще понаглею), какие координаты изменить в коде, чтобы список компонентов заполнялся под ячейкой с продуктом?))
Еще понаглею), какие координаты изменить в коде, чтобы список компонентов заполнялся под ячейкой с продуктом?)) toxin0777
Сорри за много вопросов, но самому мне не доработать табличку, изначально не совсем правильно поставил вопрос. Надо чтобы поиск происходил по разным диапазонам и находил количество нужных компонентов. Пример в файле)
Сорри за много вопросов, но самому мне не доработать табличку, изначально не совсем правильно поставил вопрос. Надо чтобы поиск происходил по разным диапазонам и находил количество нужных компонентов. Пример в файле) toxin0777
Приведите больше примеров разных вариантов и заполните вручную таблицу на первом листе, чтобы не переделывать опять
Приведите больше примеров разных вариантов и заполните вручную таблицу на первом листе, чтобы не переделывать опять Pelena
Добрый день, в процессе заполнения таблицы столкнулся с проблемой что при вводе данных не содержащихся в листе с рецептурой макрос прекращает работу и приходится переоткрывать файл чтобы макрос заработал. Подскажите пожалуйста, что изменить в коде, чтобы он игнорировал отличные от рецептуры данные?
Заранее спасибо)
Добрый день, в процессе заполнения таблицы столкнулся с проблемой что при вводе данных не содержащихся в листе с рецептурой макрос прекращает работу и приходится переоткрывать файл чтобы макрос заработал. Подскажите пожалуйста, что изменить в коде, чтобы он игнорировал отличные от рецептуры данные?
Заранее спасибо) toxin0777
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim x, y, i&, j&
With Sheets("Рецептура")
x = .Range("B3").CurrentRegion.Value
y = .Range("P2").CurrentRegion.Value
End With
Application.EnableEvents = False
For i = 1 To UBound(x)
If x(i, 1) = Target.Value Then
For j = 2 To UBound(x, 2)
If x(i, j) <> "" Then
Target.Offset(j - 1) = x(i, j)
Target.Offset(j - 1, 1) = y(i, j - 1)
End If
Next j
End If
Next i
Application.EnableEvents = True
End Sub
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim x, y, i&, j&
With Sheets("Рецептура")
x = .Range("B3").CurrentRegion.Value
y = .Range("P2").CurrentRegion.Value
End With
Application.EnableEvents = False
For i = 1 To UBound(x)
If x(i, 1) = Target.Value Then
For j = 2 To UBound(x, 2)
If x(i, j) <> "" Then
Target.Offset(j - 1) = x(i, j)
Target.Offset(j - 1, 1) = y(i, j - 1)
End If
Next j
End If
Next i
Application.EnableEvents = True
End Sub
Option Explicit
Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim x, y, i&, j&
With Sheets("Рецептура")
x = .Range("B3").CurrentRegion.Value
y = .Range("P2").CurrentRegion.Value
End With
Application.EnableEvents = False
For i = 1 To UBound(x)
If x(i, 1) = Target.Value Then
For j = 2 To UBound(x, 2)
If x(i, j) <> "" Then
Target.Offset(j - 1) = x(i, j)
Target.Offset(j - 1, 1) = y(i, j - 1)
End If
Next j
End If
Next i
Application.EnableEvents = True
End Sub
На одном из листов рабочей книги Excel, находиться база информации регистрационных данных служебных автомобилей. На втором листе ведется регистр делегации, где вводятся личные данные сотрудников и автомобилей. Один из автомобилей многократно используют сотрудники и каждый раз вводит данные в реестр – это требует лишних временных затрат для оператора. Лучше автоматизировать этот процесс. Для этого нужно создать такую формулу, которая будет автоматически подтягивать информацию об служебном автомобиле из базы данных.
Автозаполнение ячеек данными в Excel
Для наглядности примера схематически отобразим базу регистрационных данных:
Как описано выше регистр находится на отдельном листе Excel и выглядит следующим образом:
Здесь мы реализуем автозаполнение таблицы Excel. Поэтому обратите внимание, что названия заголовков столбцов в обеих таблицах одинаковые, только перетасованы в разном порядке!
Теперь рассмотрим, что нужно сделать чтобы после ввода регистрационного номера в регистр как значение для ячейки столбца A, остальные столбцы автоматически заполнились соответствующими значениями.
Как сделать автозаполнение ячеек в Excel:
- На листе «Регистр» введите в ячейку A2 любой регистрационный номер из столбца E на листе «База данных».
- Теперь в ячейку B2 на листе «Регистр» введите формулу автозаполнения ячеек в Excel:
- Скопируйте эту формулу во все остальные ячейки второй строки для столбцов C, D, E на листе «Регистр».
В результате таблица автоматически заполнилась соответствующими значениями ячеек.
Принцип действия формулы для автозаполнения ячеек
Главную роль в данной формуле играет функция ИНДЕКС. Ее первый аргумент определяет исходную таблицу, находящуюся в базе данных автомобилей. Второй аргумент – это номер строки, который вычисляется с помощью функции ПОИСПОЗ. Данная функция выполняет поиск в диапазоне E2:E9 (в данном случаи по вертикали) с целью определить позицию (в данном случаи номер строки) в таблице на листе «База данных» для ячейки, которая содержит тоже значение, что введено на листе «Регистр» в A2.
Третий аргумент для функции ИНДЕКС – номер столбца. Он так же вычисляется формулой ПОИСКПОЗ с уже другими ее аргументами. Теперь функция ПОИСКПОЗ должна возвращать номер столбца таблицы с листа «База данных», который содержит название заголовка, соответствующего исходному заголовку столбца листа «Регистр». Он указывается ссылкой в первом аргументе функции ПОИСКПОЗ – B$1. Поэтому на этот раз выполняется поиск значения только по первой строке A$1:E$1 (на этот раз по горизонтали) базы регистрационных данных автомобилей. Определяется номер позиции исходного значения (на этот раз номер столбца исходной таблицы) и возвращается в качестве номера столбца для третьего аргумента функции ИНДЕКС.
Благодаря этому формула будет работать даже если порядок столбцов будет перетасован в таблице регистра и базы данных. Естественно формула не будет работать если не будут совпадать названия столбцов в обеих таблицах, по понятным причинам.
Большинство таблиц Excel составлены так, что наиболее важные и уникальные данные для каждой строки находятся в крайнем левом столбце. Как правило в левой стороне наименование, а в правой – показатели. Excel предлагает много разных функций поиска значений, но функция ВПР особенно хорошо приспособлена для такого рода задач.
Как пользоваться формулой ВПР в Excel
Ниже на рисунке представлен список сотрудников. Допустим нам необходимо составить простой формуляр выплат, который можно будет автоматически заполнять данными выбранными с этой таблицы. Все значения из таблицы будут выбираться на основе указанного идентификатора сотрудника.
Пользователь будет иметь возможность ввести в ячейке B14 идентификатор сотрудника. На его основе все остальные данные: имя, фамилия, адрес и т.д. будут заполнятся автоматически. Формулы, использованные в формуляре показана на рисунке:
Все формулы, считывающие с таблицы данные о сотрудниках, используют функцию ВПР. Она содержит 4 аргумента:
- Искомое значение.
- Таблица.
- Номер столбца.
- Интервальный просмотр (не обязательный для заполнения) – определяет точное (ЛОЖЬ) или приблизительное (ИСТИНА) совпадение искомых и найденных значений.
Если же функция ВПР найдет искомое значение, тогда она возвращает текущее значение ячейки, но из указанного в ее третьем аргументе номера столбца исходной таблицы. В данном примере третий аргумент с номером содержит значение 2, значит функция вернет имя и фамилию работника находящиеся во втором столбце таблицы.
В данном примере 5 формул для автозаполнения данных используют функцию ВПР с небольшими отличиями. Формулы для поиска адреса:
подобны формулам, которые ищут имя и фамилию сотрудника, но считывают данные с других столбцов таблицы.
В формуле для вычисления выплаты используется сразу две функции ВПР, потому как результат вычисления одной формулы разделен на результат второй. Значение годовой премии считывается с пятого столбца таблицы и разделено на частоту выплат с четвертого столбца. Таким образом рассчитывается сумма разовой выплаты brutto:
Формула для вычисления суммы отчисления в пенсионный фонд считывает процент пенсионных отчислений с восьмого, последнего столбца таблицы и умножает на общую сумму выплаты:
В пятой формуле вычисляется сумма налога от общей суммы выплаты отнимаются значения страховки и отчисления в пенсионный фонд. Результат умножается на процентную ставку налога, найденную в шестом столбце таблицы с помощью функции ВПР.
Итоговая сумма всех вычетов в ячейке G20 вычисляется формулой: =СУММ(G17:G19).
Выплата по факту в ячейке G14 рассчитывается путем вычитания от общей выплаты минус вычеты: =C17-G20.
Естественно в реальности зачастую для расчета выплат используются еще более сложные вычисления, чем в описанном выше примере. Но если освоить принцип построения формул на основе функции ВПР можно смело браться за самые сложные модели итоговых расчетов с авто заполнением полей формуляров.
Ключевым предназначением многих формул Excel является поиск данных по таблице. Программа Excel предлагает своим пользователям много функций существенно упрощающих поиск данных в вертикальных или горизонтальных таблицах. От левой к правой стороны и в обратном направлении, например, формулой из двух функций ИНДЕКС и ПОИСКПОЗ или одной функцией ПРОСМОТР. Соединив некоторые функций поиска с другими функциями, можно создавать формулы, которые будут искать данные даже после изменения положения таблиц или их транспонирования.
Функция мгновенного заполнения автоматически подставляет данные, когда обнаруживает закономерность. Например, с помощью мгновенного заполнения можно разделять имена и фамилии из одного столбца или объединять их из двух разных столбцов.
Примечание: Функция мгновенного заполнения доступна только в Excel 2013 и более поздних версий.
Предположим, что столбец A содержит имена, столбец B — фамилии, а вы хотите заполнить столбец C сочетаниями имен и фамилий. Если ввести полное имя в столбец C, функция мгновенного заполнения заполнит остальные ячейки соответствующим образом.
Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.
Начните вводить следующее полное имя в ячейке C3. Excel определит закономерность и отобразит предварительное изображение остальной части столбца, заполненной объединенным текстом.
Для подтверждения предварительного просмотра нажмите клавишу ВВОД.
Если вариант заполнения не выводится, вероятно, эта функция не включена. Вы можете выбрать Данные > Мгновенное заполнение, чтобы применить заполнение вручную или нажать клавиши CTRL+E. Чтобы включить мгновенное заполнение, выберите Сервис > Параметры > Дополнительно > Параметры правки и установите флажок Автоматически выполнять мгновенное заполнение.
Предположим, что столбец A содержит имена, столбец B — фамилии, а вы хотите заполнить столбец C сочетаниями имен и фамилий. Если ввести полное имя в столбец C, функция мгновенного заполнения заполнит остальные ячейки соответствующим образом.
Введите полное имя в ячейке C2 и нажмите клавишу ВВОД.
Выберите Данные > Мгновенное заполнение или нажмите клавиши CTRL+E.
Excel определит закономерность в ячейке C2 и заполнит ячейки ниже.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Автозаполнение ячеек Excel – это автоматический ввод серии данных в некоторый диапазон. Введем в ячейку «Понедельник», затем удерживая левой кнопкой мышки маркер автозаполнения (квадратик в правом нижнем углу), тянем вниз (или в другую сторону). Результатом будет список из дней недели. Можно использовать краткую форму типа Пн, Вт, Ср и т.д. Эксель поймет. Аналогичным образом создается список из названий месяцев.
Автоматическое заполнение ячеек также используют для продления последовательности чисел c заданным шагом (арифметическая прогрессия). Чтобы сделать список нечетных чисел, нужно в двух ячейках указать 1 и 3, затем выделить обе ячейки и протянуть вниз.
Эксель также умеет распознать числа среди текста. Так, легко создать перечень кварталов. Введем в ячейку «1 квартал» и протянем вниз.
На этом познания об автозаполнении у большинства пользователей Эксель заканчиваются. Но это далеко не все, и далее будут рассмотрены другие эффективные и интересные приемы.
Автозаполнение в Excel из списка данных
Ясно, что кроме дней недели и месяцев могут понадобиться другие списки. Допустим, часто приходится вводить перечень городов, где находятся сервисные центры компании: Минск, Гомель, Брест, Гродно, Витебск, Могилев, Москва, Санкт-Петербург, Воронеж, Ростов-на-Дону, Смоленск, Белгород. Вначале нужно создать и сохранить (в нужном порядке) полный список названий. Заходим в Файл – Параметры – Дополнительно – Общие – Изменить списки.
В следующем открывшемся окне видны те списки, которые существуют по умолчанию.
Как видно, их не много. Но легко добавить свой собственный. Можно воспользоваться окном справа, где либо через запятую, либо столбцом перечислить нужную последовательность. Однако быстрее будет импортировать, особенно, если данных много. Для этого предварительно где-нибудь на листе Excel создаем перечень названий, затем делаем на него ссылку и нажимаем Импорт.
Жмем ОК. Список создан, можно изпользовать для автозаполнения.
Помимо текстовых списков чаще приходится создавать последовательности чисел и дат. Один из вариантов был рассмотрен в начале статьи, но это примитивно. Есть более интересные приемы. Вначале нужно выделить одно или несколько первых значений серии, а также диапазон (вправо или вниз), куда будет продлена последовательность значений. Далее вызываем диалоговое окно прогрессии: Главная – Заполнить – Прогрессия.
В левой части окна с помощью переключателя задается направление построения последовательности: вниз (по строкам) или вправо (по столбцам).
Посередине выбирается нужный тип:
- арифметическая прогрессия – каждое последующее значение изменяется на число, указанное в поле Шаг
- геометрическая прогрессия – каждое последующее значение умножается на число, указанное в поле Шаг
- даты – создает последовательность дат. При выборе этого типа активируются переключатели правее, где можно выбрать тип единицы измерения. Есть 4 варианта:
- день – перечень календарных дат (с указанным ниже шагом)
- рабочий день – последовательность рабочих дней (пропускаются выходные)
- месяц – меняются только месяцы (число фиксируется, как в первой ячейке)
- год – меняются только годы
- автозаполнение – эта команда равносильная протягиванию с помощью левой кнопки мыши. То есть эксель сам определяет: то ли ему продолжить последовательность чисел, то ли продлить список. Если предварительно заполнить две ячейки значениями 2 и 4, то в других выделенных ячейках появится 6, 8 и т.д. Если предварительно заполнить больше ячеек, то Excel рассчитает приближение методом линейной регрессии, т.е. прогноз по прямой линии тренда (интереснейшая функция – подробнее см. ниже).
Нижняя часть окна Прогрессия служит для того, чтобы создать последовательность любой длины на основании конечного значения и шага. Например, нужно заполнить столбец последовательностью четных чисел от 2 до 1000. Мышкой протягивать не удобно. Поэтому предварительно нужно выделить только ячейку с одним первым значением. Далее в окне Прогрессия указываем Расположение, Шаг и Предельное значение.
Результатом будет заполненный столбец от 2 до 1000. Аналогичным образом можно сделать последовательность рабочих дней на год вперед (предельным значением нужно указать последнюю дату, например 31.12.2016). Возможность заполнять столбец (или строку) с указанием последнего значения очень полезная штука, т.к. избавляет от кучи лишних действий во время протягивания. На этом настройки автозаполнения заканчиваются. Идем далее.
Автозаполнение чисел с помощью мыши
Автозаполнение в Excel удобнее делать мышкой, у которой есть правая и левая кнопка. Понадобятся обе.
Допустим, нужно сделать порядковые номера чисел, начиная с 1. Обычно заполняют две ячейки числами 1 и 2, а далее левой кнопкой мыши протягивают арифметическую прогрессию. Можно сделать по-другому. Заполняем только одну ячейку с 1. Протягиваем ее и получим столбец с единицами. Далее открываем квадратик, который появляется сразу после протягивания в правом нижнем углу и выбираем Заполнить.
Если выбрать Заполнить только форматы, будут продлены только форматы ячеек.
Сделать последовательность чисел можно еще быстрее. Во время протягивания ячейки, удерживаем кнопку Ctrl.Этот трюк работает только с последовательностью чисел. В других ситуациях удерживание Ctrl приводит к копированию данных вместо автозаполнения.
Если при протягивании использовать правую кнопку мыши, то контекстное меню открывается сразу после отпускания кнопки.
При этом добавляются несколько команд. Прогрессия позволяет использовать дополнительные операции автозаполнения (настройки см. выше). Правда, диапазон получается выделенным и длина последовательности будет ограничена последней ячейкой.
Чтобы произвести автозаполнение до необходимого предельного значения (числа или даты), можно проделать следующий трюк. Берем правой кнопкой мыши за маркер чуть оттягиваем вниз, сразу возвращаем назад и отпускаем кнопку – открывается контекстное меню автозаполнения. Выбираем прогрессию. На этот раз выделена только одна ячейка, поэтому указываем направление, шаг, предельное значение и создаем нужную последовательность.
Очень интересными являются пункты меню Линейное и Экспоненциальное приближение. Это экстраполяция, т.е. прогнозирование, данных по указанной модели (линейной или экспоненциальной). Обычно для прогноза используют специальные функции Excel или предварительно рассчитывают уравнение тренда (регрессии), в которое подставляют значения независимой переменной для будущих периодов и таким образом рассчитывают прогнозное значение. Делается примерно так. Допустим, есть динамика показателя с равномерным ростом.
Для прогнозирования подойдет линейный тренд. Расчет параметров уравнения можно осуществить с помощью функций Excel, но часто для наглядности используют диаграмму с настройками отображения линии тренда, уравнения и прогнозных значений.
Чтобы получить прогноз в числовом выражении, нужно произвести расчет на основе полученного уравнения регрессии (либо напрямую обратиться к формулам Excel). Таким образом, получается довольно много действий, требующих при этом хорошего понимания.
Так вот прогноз по методу линейной регрессии можно сделать вообще без формул и без графиков, используя только автозаполнение ячеек в экселе. Для этого выделяем данные, по которым строится прогноз, протягиваем правой кнопкой мыши на нужное количество ячеек, соответствующее длине прогноза, и выбираем Линейное приближение. Получаем прогноз. Без шума, пыли, формул и диаграмм.
Если данные имеют ускоряющийся рост (как счет на депозите), то можно использовать экспоненциальную модель. Вновь, чтобы не мучиться с вычислениями, можно воспользоваться автозаполнением, выбрав Экспоненциальное приближение.
Более быстрого способа прогнозирования, пожалуй, не придумаешь.
Автозаполнение дат с помощью мыши
Довольно часто требуется продлить список дат. Берем дату и тащим левой кнопкой мыши. Открываем квадратик и выбираем способ заполнения.
По рабочим дням – отличный вариант для бухгалтеров, HR и других специалистов, кто имеет дело с составлением различных планов. А вот другой пример. Допустим, платежи по графику наступают 15-го числа и в последний день каждого месяца. Укажем первые две даты, протянем вниз и заполним по месяцам (любой кнопкой мыши).
Обратите внимание, что 15-е число фиксируется, а последний день месяца меняется, чтобы всегда оставаться последним.
Используя правую кнопку мыши, можно воспользоваться настройками прогрессии. Например, сделать список рабочих дней до конца года. В перечне команд через правую кнопку есть еще Мгновенное заполнение. Эта функция появилась в Excel 2013. Используется для заполнения ячеек по образцу. Но об этом уже была статья, рекомендую ознакомиться. Также поможет сэкономить не один час работы.
На этом, пожалуй, все. В видеоуроке показано, как сделать автозаполнение ячеек в Excel.
Читайте также: