Заполнение таблицы эксель на основе действующих значений
Как заполнить строки на основе указанного значения ячейки в Excel?
Предположим, у вас есть таблица проектов с именем соответствующего человека, который отвечает за каждый проект, как показано на скриншоте ниже. И теперь вам нужно перечислить все названия проектов в строках в зависимости от конкретного человека, как этого добиться? На самом деле формула массива из этой статьи может помочь вам решить проблему.
Заполнение строк на основе указанного значения ячейки с помощью формулы массива
Выполните следующие действия, чтобы заполнить строки соответствующей записью на основе заданного значения в Excel.
1. Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите Ctrl + Shift + Enter ключи.
=IFERROR(INDEX(Sheet2!A$1:A$10,SMALL(IF(Sheet2!B$1:B$10=D$2,ROW(A$1:A$10)),ROWS(D$2:D2))),"")
Внимание: в формуле, Sheet2 это имя текущего рабочего листа, 1 доллар: 10 австралийских долларов содержит ли диапазон столбцов все имена проектов (включая заголовок), B $ 1: B $ 10 диапазон столбцов содержит все имена людей (включая заголовок), и D $ 2 содержит ли ячейка имя человека, на основе которого вы будете заполнять строки. Пожалуйста, измените их по своему усмотрению.
2. Выберите первую ячейку результата, перетащите маркер заполнения вниз, чтобы заполнить все строки соответствующими именами задач. Смотрите скриншот:
Легко выбирать целые строки на основе значения ячейки в столбце сертификата:
Выбрать определенные ячейки полезности Kutools for Excel может помочь вам быстро выбрать целые строки на основе значения ячейки в столбце сертификата в Excel, как показано ниже. После выбора всех строк на основе значения ячейки вы можете вручную переместить или скопировать их в новое место по мере необходимости.
Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)
При работе в электронных таблицах Excel бывает нужно постоянно вводить одни и те же значения (из списка). Стандартными списками являются последовательности дней недели или месяцев, но порой хочется создать свой список автозаполнения. Например, список класса, возраст учеников, размеры одежды или любых других данных, к которым постоянно приходится обращаться. Чтобы не вводить каждый раз эти значения вручную, можно создать свой список автозаполнения, а затем использовать его — в Excel есть такая возможность.
Для создания своего списка автозаполнения выполните следующие действия.
Если используется Excel версии 2003, то нужно выбрать меню Сервис — Параметры — Списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
Если используется Excel версии 2007 (2010), то нужно выбрать Файл — Параметры — Дополнительно — в Общие Изменить списки — Новый список — вводим элементы списка через клавишу Enter — выбираем Добавить — ОК.
После создания своего списка автозаполнения достаточно в нужную ячейку таблицы ввести первое значение из списка (в примере Иванов Антон) и протянуть маркер заполнения ячейки в нужном направлении. Смотрите подробнее Как пользоваться списками автозаполнения и вводить стандартные последовательности.
Что делать, если нет маркера автозаполнения?
Если маркер (курсор) заполнения отсутствует, то нужно настроить Excel так, чтобы маркер отображался.
Для этого, если Вы используете версию 2003, выбираем Сервис — Параметры — на вкладке Параметры устанавливаем галочку Перетаскивание ячеек.
Если Вы используете версию 2007 или 2010, Файл (кнопка Офис) — Параметры — Дополнительно — Разрешить маркеры заполнения и перетаскивания ячеек — ОК.
Ввод данных экспресс-методом
Если таблица содержит в нескольких ячейках одинаковые данные, для быстрого ввода этих данных можно использовать экспресс-метод.
Используя клавишу Ctrl, выделим ячейки, в которые нужно ввести одинаковые значения.
В строку формул введем нужное значение и нажмем на клавиатуре сочетание клавиш Ctrl+Enter. Все выделенные ячейки автоматически заполнятся нужными данными.
Кратко об авторе:
Шамарина Татьяна Николаевна — учитель физики, информатики и ИКТ, МКОУ "СОШ", с. Саволенка Юхновского района Калужской области. Автор и преподаватель дистанционных курсов по основам компьютерной грамотности, офисным программам. Автор статей, видеоуроков и разработок.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
ВПР - одна из наиболее востребованных функций Excel. И это неудивительно, она освобождает нас от рутинной операции, которая часто встречается на практике при работе с таблицами, а именно, при помощи функции ВПР мы можем сформировать новую таблицу на основе исходной, взяв только нужные данные из первой таблицы.
Попробуем разобраться на конкретном примере: предположим, некоторой организации требуется составить список на новогодние подарки детям сотрудников. У нас есть исходная таблица из бухгалтерии, а нужно создать новую таблицу, в которой будут требуемые данные, но не будет лишних (которые есть в исходной). То есть с помощью функции ВПР найдем в первом столбце ТАБЛИЦЫ 1 нужную фамилию, выберем в нужной строке требуемое значение (количество детей) и заполним этим значением третий столбец ТАБЛИЦЫ 2.
Итак, нам понадобятся две таблицы. Одна — справочная (обычно они уже сформированы отделом кадров или бухгалтерией), где собрана основная информация о сотрудниках. Назовем ее ТАБЛИЦА 1.
Первый столбец этой таблицы (в нашем случае ФИО) должен быть отсортирован по возрастанию.
ТАБЛИЦА 2 в итоге работы функции ВПР должна содержать результат — список сотрудников с количеством детей.
В ТАБЛИЦЕ 2 фамилии сотрудников могут располагаться в любом порядке. Например, в соответствии стабельными номерами как в данном примере.
Функция ВПР будет располагаться в третьем столбце, который пока пустой.
Шаг 1
- Щелчком выберем первую ячейку третьего столбца D4.
- Щелкнем кнопку Мастера функций
- Щелчком выберем функцию ВПР из списка.
Шаг 2
Следующий шаг — заполнение полей в окне функции ВПР:
Поле «Искомое значение» заполнить,щелкнув ячейку с фамилией Светлов.
В поле появится имя этой ячейки С4.
Чтобы временно скрыть/отобразить окно Аргументы функции, щелкнуть кнопку.
Чтобы заполнить поле Таблица, надо выделить данные Таблицы 1 (без шапки). В данном примере это ячейки F5:K10.
В поле Номер столбца указываем порядковый номер нужного нам столбца ТАБЛИЦЫ 1.
В поле Интервальный просмотр ставится 1 (приблизительное совпадение) или 0 (точное совпадение). В данном простом случае можно выбрать любое.
Нажимаем ОК — формула готова.
ВАЖНО: необходимо адрес таблицы сделать абсолютным.
Для этого выделяем в строке ввода формул F5:K10 и нажимаем F4 на клавиатуре.
Первая ячейка столбца с количеством детей заполнена.
Шаг 3
Осталось растиражировать формулу по всему столбцу. Для этого выделяем ячейку D4 и протащим мышкой маленький угловой маркер вниз до D9.
После этого третий столбец ТАБЛИЦЫ 2 заполнится данными из ТАБЛИЦЫ 1 в точном соответствии с формулой.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.
Кстати, такая возможность есть
на всех страницах нашего сайта
0 Спам
1 Леночка555 • 17:10, 27.06.2019
Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.
Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.
О работе с сайтом
Мы используем cookie.
Публикуя материалы на сайте (комментарии, статьи, разработки и др.), пользователи берут на себя всю ответственность за содержание материалов и разрешение любых спорных вопросов с третьми лицами.
При этом редакция сайта готова оказывать всяческую поддержку как в публикации, так и других вопросах.
Если вы обнаружили, что на нашем сайте незаконно используются материалы, сообщите администратору — материалы будут удалены.
Как автоматически заполнять другие ячейки при выборе значений в раскрывающемся списке Excel?
Допустим, вы создали раскрывающийся список на основе значений в диапазоне A2: A8. При выборе значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне B2: B8 автоматически подставлялись в определенную ячейку. Например, когда вы выбираете Наталию из раскрывающегося списка, соответствующий балл 40 будет заполнен в E2, как показано на скриншоте ниже. В этом руководстве представлены два метода, которые помогут вам решить проблему.
Выпадающий список автоматически заполняется функцией ВПР.
Пожалуйста, сделайте следующее, чтобы автоматически заполнить другие ячейки при выборе в раскрывающемся списке.
1. Выберите пустую ячейку, в которую вы хотите автоматически подставить соответствующее значение.
2. Скопируйте и вставьте в нее приведенную ниже формулу, а затем нажмите Enter ключ.
=VLOOKUP(D2,A2:B8,2,FALSE)
Внимание: В формуле D2 это выпадающий список ЯЧЕЙКА, A2: B8 диапазон таблицы включает значение поиска и результаты, а также число 2 указывает номер столбца, в котором находятся результаты. Например, если результаты находятся в третьем столбце диапазона таблицы, измените 2 на 3. Вы можете изменить значения переменных в формуле в зависимости от ваших потребностей.
3. С этого момента, когда вы выбираете имя в раскрывающемся списке, E2 будет автоматически заполняться определенной оценкой.
Легко выбирайте несколько элементов из раскрывающегося списка в Excel:
Вы когда-нибудь пробовали выбрать несколько элементов из раскрывающегося списка в Excel? Здесь Выпадающий список с множественным выбором полезности Kutools for Excel может помочь вам легко выбрать несколько элементов из раскрывающегося списка в диапазоне, на текущем листе, в текущей книге или во всех книгах. См. Демонстрацию ниже:
Загрузите Kutools для Excel прямо сейчас! (30-дневная бесплатная трасса)
Выпадающий список автоматически заполняется с помощью Kutools for Excel
Y вы можете легко заполнить другие значения на основе выбора из раскрывающегося списка, не запоминая формулы с Найдите значение в списке формула Kutools for Excel.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейку для поиска значения автозаполнения (говорит ячейка C10), а затем щелкните Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:
3. В Помощник по формулам диалоговом окне укажите следующие аргументы:
- В Выберите формулу коробка, найдите и выберите Найдите значение в списке;
Советы: Вы можете проверить Фильтр введите определенное слово в текстовое поле, чтобы быстро отфильтровать формулу. - В Таблица_массив поле, щелкните кнопка для выбора диапазона таблицы, который содержит значение поиска и значение результата;
- В Look_value поле, щелкните кнопку, чтобы выбрать ячейку, содержащую искомое значение. Или вы можете напрямую ввести значение в это поле;
- В Колонка поле, щелкните кнопку, чтобы указать столбец, из которого вы вернете совпадающее значение. Или вы можете ввести номер столбца в текстовое поле, если вам нужно.
- Нажмите OK.
Теперь соответствующее значение ячейки будет автоматически заполнено в ячейке C10 на основе выбора раскрывающегося списка.
Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Демо: раскрывающийся список автоматически заполняется без запоминания формул
Статьи по теме:
Автозаполнение при вводе текста в раскрывающемся списке Excel
Если у вас есть раскрывающийся список проверки данных с большими значениями, вам нужно прокрутить список вниз только для того, чтобы найти нужное, или введите все слово напрямую в поле списка. Если есть способ разрешить автозаполнение при вводе первой буквы в выпадающем списке, все станет проще. В этом руководстве представлен метод решения проблемы.
Создать раскрывающийся список из другой книги в Excel
Создать раскрывающийся список проверки данных среди листов в книге довольно просто. Но если данные списка, необходимые для проверки данных, находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как подробно создать раскрывающийся список из другой книги в Excel.
Создайте раскрывающийся список с возможностью поиска в Excel
Для раскрывающегося списка с многочисленными значениями найти подходящий - непростая задача. Ранее мы ввели метод автоматического заполнения раскрывающегося списка при вводе первой буквы в раскрывающемся списке. Помимо функции автозаполнения, вы также можете сделать раскрывающийся список доступным для поиска для повышения эффективности работы при поиске правильных значений в раскрывающемся списке. Чтобы сделать раскрывающийся список доступным для поиска, попробуйте метод, описанный в этом руководстве.
Как создать раскрывающийся список с несколькими флажками в Excel?
Многие пользователи Excel, как правило, создают раскрывающийся список с несколькими флажками, чтобы выбирать несколько элементов из списка за раз. На самом деле вы не можете создать список с несколькими флажками с проверкой данных. В этом руководстве мы покажем вам два метода создания раскрывающегося списка с несколькими флажками в Excel. В этом руководстве представлен метод решения проблемы.
Предположим, вам необходимо классифицировать список данных на основе значений, например, если данные больше 90, он будет отнесен к категории Высокий, если больше 60 и меньше 90, он будет отнесен к категории Средний, если менее 60, классифицируется как Низкое, как показано на следующем снимке экрана. Как бы вы могли решить эту задачу в Excel?
In your daily work, to combine multiple worksheets, workbooks and csv files into one single worksheet or workbook may be a huge and headachy work. But, if you have Kutools for Excel, with its powerful utility – Combine, you can quickly combine multiple worksheets, workbooks or csv files into one worksheet or workbook.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Классифицируйте данные на основе значений с помощью функции If
Чтобы применить следующую формулу для классификации данных по значению, как вам нужно, сделайте следующее:
Введите эту формулу: = ЕСЛИ (A2> 90; «Высокий»; IF (A2> 60; «Средний»; «Низкий»)) в пустую ячейку, в которую вы хотите вывести результат, а затем перетащите маркер заполнения вниз к ячейкам, чтобы заполнить формулу, и данные были классифицированы, как показано на следующем снимке экрана:
Классифицируйте данные на основе значений с помощью функции Vlookup
Если существует несколько оценок, которые необходимо отнести к категории, как показано на скриншоте ниже, функция If может быть проблемной и сложной в использовании. В этом случае функция Vlookup может оказать вам услугу. Пожалуйста, сделайте следующее:
Введите эту формулу: = ВПР (A2; $ F $ 1: $ G $ 6,2,1) в пустую ячейку, а затем перетащите дескриптор заполнения вниз к ячейкам, которые вам нужны, чтобы получить результат, и все данные были распределены по категориям сразу, см. снимок экрана:
Внимание: В приведенной выше формуле B2 это ячейка, в которой вы хотите получить оценку по категориям, F1: G6 это диапазон таблицы, который вы хотите найти, число 2 указывает номер столбца таблицы поиска, который содержит значения, которые вы хотите вернуть.
Читайте также: